Horizontal Axis of Line Chart Does Not Adjust - Excel

If you have data in a pivot table and data outside the pivot table, then the latter won't be filtered when you filter the pivot table. If you only want to include the number of data points as in the pivot table, then you need a dynamic range name that feeds the cost series.

Create these three range names (cell references are WRT the screenshot below. Adjust to your file):

chtLabels   =OFFSET(Sheet1!$E$4,0,0,COUNT(Sheet1!$F:$F)-1,1)
chtCost =OFFSET(chtLabels,0,2)
chtHours    =OFFSET(chtLabels,0,1)

Plug them into the chart data source for the series and the chart labels. When you enter them, you must precede them with the sheet name or the workbook name, e.g. Sheet1!chtLabels

Now you can filter the pivot table and the X axis will adjust.

Recent Questions...