Here’s the thing: The scale used on each axis must have equal intervals. It’s an easy mistake to make.

Excel automatically spaces your intervals and labels equidistant from one another but it is assuming that your intervals actually are equidistant. In this graph, that’s not the case. We are missing the months of March, April, July, and August, when either no one was enrolled in the study or we have some missing data. But we can’t just gloss over those months. It isn’t truthful and it distorts the data display.

To make the graph display correctly, you have to adjust your table in your spreadsheet so that you have space in the table for those missing months, even if there’s no data there. Excel will add the months to the axis. You then have a choice about how to handle the gap months. Should you just skip over them and connect the points in the line? Should you report them as zeros? Your call, but make that decision in the Select Data window.

Right-click on your graph and click Select Data. In that window, look for the button in the lower left that says Hidden and Empty Cells and click it. In that new window, you’ll get options for how to handle the missing data.

When I have data collected at irregular intervals, its one of the only times I think its appropriate to use place markers along the line. Usually markers are a little too much extra noise but in this case, they serve a clear purpose to identify where data is actually reported, while still maintaining an equidistant axis.

This post is an excerpt from Presenting Data Effectively, 2nd edition, where you’ll find a lot more guidance on making sure your data story is clear.

This post is also a checkpoint on the Data Visualization Checklist, where you can run a pic of your graph-in-progress to get feedback on places to improve.