Excel – How to force time series in pivot chart — force break in line

microsoft excelpivot table

I have a housing transaction dataset that looks like this:

geography | date | housing type | sq.ft. | sale price | price/sq.ft.

I can make a pivot chart showing the change in, say, price/sq.ft. over time, and filter that by one of the 20 geographies I have. Here's an example of what I can make below:

enter image description here

I've added two more fields (saleyear and salequarter) and calculated them based on the date field. The problem is that not all geographies had a transaction in each quarter. Instead of showing missing data (i.e., a break in the line), the x-axis shortens and throws off the pattern. You can see this in year 1999 in the image above.

TL;DR: How do I make the pivot chart show the date for a missing value and just show a break in the line?

Best Answer

Pivot tables (and pivot charts) only show data that is present in the data source. If you want to make sure that all quarters are showing on the X axis, you must have all quarters present in the source data. They don't have to have values against them.

enter image description here

Use the "Select Data source > Hidden and Empty Cells" settings to control whether the gap is showing or closed by connecting the data points with a line.

enter image description here

Related Question