Search code examples
excelmacoschartspivot-tableexcel-2011

Dynamic charts from pivot

I have a data set that I am using as an input to a pivot table. I have transformed that data set into a table, so that every-time I add something to the data set, the pivot gets updated automatically. I have also added a line of code, so that every-time a user clicks on the sheet containing the pivot table, the table gets refreshed automatically. Now I need to have a chart (linked to the pivot table) that updates automatically every-time the pivot refreshes (like rows or columns also increase/decrease).

Solution

  • You could use a dynamic named range in order to get the chart data to update automatically. Without knowing what your data looks like, I'll just provide you with a generic version assuming your data is in A1:C5.

    =OFFSET(A1;0;0;COUNT(A:A);3)

    This will create a range that is 3 columns wide and goes until the last row with data in column A.