1

I have a line graph whose source data come from a pivot table. However, the number of rows in this pivot table may change and the chart's horizontal axis do not adjust to that. How can I adjust my chart so it adjust?

Here's my data.

enter image description here

Here's the chart from the above data.

enter image description here

Here's the same pivot table updated (filtered).

enter image description here

Here's the corresponding graph. As you can see the horizontal axis did not adjust itself. It kept to the original formatting.

enter image description here

Improve this question

1 Answer 1

Reset to default
1

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.

enter image description here

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .