• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Problem with pivot chart losing format



I have been trying to teach my daughter some more advanced Excel techniques than she learned in school and she suggested using some of her exercise data to provide a real life example.

Her thinking was a dynamic chart , but I thought that we could be more creative, which is where I perhaps fell down. Without understanding all the consequences I chose to create a dashboard with my daughter's required chart, but with other data and crucially a timeline to select the time period analysed.

I enter the daily data in the "Data" tab, which also contains a cell for the daily target. The "Chart" tab contains a pivot table based on the data in the "Data" tab together with various calculations to generate the data for the dashboard. The pivot table is updated automatically when the data changes via a macro. I've had to use a workaround by creating a range called "Steps" which will allow 1,500 data points because I don't think there is a way to treat a pivot table column in the same way as a table.

The main chart is a combination chart with the actual data displayed in bars and a target and period average displayed as lines. This works fine, but if I select a time period which contains no data and then reselect a period with data, the chart type changes to a bar chart.

Question 1: Is there any way I can fix the format (type) of the chart even if the timeline has called for no data?
Question 2: Can I analyse a pivot table in the same way as a table? i.e. referring to the pivot table and a column in a formula.

I attach a copy of the spreadsheet.



I have left this problem for a couple of weeks and have now returned to it. I have managed a workaround which might be of potential interest to others.

1. I am now fairly certain that it is not possible to restrict the scope of the timeline.
2. I can use dummy data to add an insignificant number for the first day of every month covered by the scope of the timeline.
3. I have changed the TARGET field to report only the MAX values to avoid double counting of data for the first of each month.
4. I have used a calculated pivot table field (using AVERAGEIF) to correct the average steps to exclude the insignificant numbers from the average calculation.

It is JUST possible that this may be of some interest or use to others.


I just wish that Microsoft would let users restrict the scope of the timeline. Unless I am missing something the upper limit appears to be December of the last year for which there is data, but the lower limit is 1 January 1900. That's one heck of a lot of redundant dummy data to have to use to fix the problem.

If anyone believes that my understanding is incorrect then PLEASE let me know.