Keep the same number of rows in a pivot table to create a chart
Deb
-
Deb -
Deb -
Hello,
I have searched everywhere (Excel help, forums, ...) but I can't find a solution to my problem... so I'm turning to you!
I want to create charts from a pivot table that will be updated regularly.
I need to track the number of audits to be performed each month. Since I apply filters in my pivot table, a row may disappear for a given month. However, I would like all the months of the year to appear on my chart. Is there a solution (according to Excel help, I understand that this option is only possible if I have an OLAP data source)?
This would help me a lot because I created this pivot table to make my life easier and I feel like I will have to redo all my graphs "by hand"...
Thank you in advance
Deborah
P.S.: I am using Windows XP Professional
I have searched everywhere (Excel help, forums, ...) but I can't find a solution to my problem... so I'm turning to you!
I want to create charts from a pivot table that will be updated regularly.
I need to track the number of audits to be performed each month. Since I apply filters in my pivot table, a row may disappear for a given month. However, I would like all the months of the year to appear on my chart. Is there a solution (according to Excel help, I understand that this option is only possible if I have an OLAP data source)?
This would help me a lot because I created this pivot table to make my life easier and I feel like I will have to redo all my graphs "by hand"...
Thank you in advance
Deborah
P.S.: I am using Windows XP Professional
3 answers
-
Hello,
Questions:
Are the charts generated from the same pivot table or are there several?
Is removing the filtering before generating the chart and putting it back afterward not enough to solve the problem?
WeaponEDGE -
Hello,
So to do that, I personally got clever! I also searched and didn’t find anything, so I cobbled together a solution that works wonderfully.
Explanations:
1: Create a column next to your pivot table where the cell value will be set to 1 when there is data in the row of the summary of the combined rows if you have multiple filters on your pivot table.
2: Create a new sheet with the headers from the pivot table and below, for each row, write the command: if your cell=1 then point to the value otherwise "" (leave it empty).
3: Apply a filter on the headers of your new table (select many rows below in order to accommodate a lot of data). The filter is a filter to hide empty rows.
4: Create your chart from this table using the data range of your whole selection made above. For example from A2 to D10000. Tell the chart to ignore empty rows (this is normally the default).
5: In order to update your charts, you can create a mini macro that when activated:
-clears the filter
-re-applies the empty filter
-applies a sorting filter by date or any other if desired
_____
I hope that’s clear ^^
In the worst case, upload your Excel file and I’ll do these commands for you.
Have a nice day
Ps: I’m sure there’s a simpler solution... -
Hello,
you should try making your charts with dynamic ranges.
An example to display different columns: https://www.cjoint.com/?CFkpSYvmi8n
You will have to adjust the height of the offset() in the names (4th parameter).
eric
--
Never respond to an unsolicited DM...
Well, that's done.
