Workaround to Implement Modern Chart Gridlines

[EricRex]

Hey there,

Hopefully this post will help someone else out. According to my web searches, Microsoft has not seen fit to implement Gridlines in Modern Charts yet.



I just discovered a way to do it, although it's not particularly flexible unless you create some parameters and/or some VBA code to make it so.

In my chart query, I simply put a constant into the SELECT statement which shows up as a horizontal gridline on the chart when you select the query as the chart data source.

Example query:
SELECT MyDate AS CatDate, 0 AS Zero, YValue AS DataVal FROM MyTable;

In my real chart, I need/want Zero as a line on the chart because the data value crossing above or below Zero is a significant event.

The example could be expanded with more constants, or create VBA code that examines the dataset and creates several appropriate constants in the final query. Or, you could create a Parameter Query that you can pass in the gridline constants as arguments.

Guessing that you could implement Vertical Gridlines by creating a column in the table for the same query that has some maximum values and the rest are the bottom of the appropriate scale. This might work in some circumstances and not others. If you put it in a cluster column chart, you can give that dataset a column width of 1, or something like that.

Hope that helps!
Eric

[isladogs]

Gridlines will be available in the forthcoming improvement to modern charts due for release to the current channel at the end of August (A365 only)
The feature is I believe already available for those in the Beta Channel

There will also be additional chart types that may help your scenario