Add Sparklines to pivot table
- Thread starter lewgoofy
- Start date
Sorry, I celebrated little early.. actually, so much confidence I have with chandoo's article that it covers all the aspects of one functionality, so I thought this is also covered.
Well, I have learned alot of tricks from Chandoo, and suggesting one such trick to your problem as well.
Trick 1:
assuming you have raw data of salesman, date of sale and amount
1. create a pivot table to show salesName in row label and month on month as column Label and sum of amt in values
2. Insert a calculated field with =0 formula in values above sum of amt
3. This will show 0 for each data row, custom format this column ;;; to hide value
4. rename the column heading in pivot as Graph
5. simply insert sparkline by selecting the data month on month in pivot table as data source, and location as the calculated field column in pivot table.
6. repeat the same for all rows.
7. Thats it!!
Ps: In case you need to show pivot table of total and not month on month for each salesman, then you need to create two pivot tables, one hidden pivot table to act as data source for sparkline. The other pivot table (dashboard) with calculated empty column like explained above as placeholder for sparkline.
Well, I have learned alot of tricks from Chandoo, and suggesting one such trick to your problem as well.
Trick 1:
assuming you have raw data of salesman, date of sale and amount
1. create a pivot table to show salesName in row label and month on month as column Label and sum of amt in values
2. Insert a calculated field with =0 formula in values above sum of amt
3. This will show 0 for each data row, custom format this column ;;; to hide value
4. rename the column heading in pivot as Graph
5. simply insert sparkline by selecting the data month on month in pivot table as data source, and location as the calculated field column in pivot table.
6. repeat the same for all rows.
7. Thats it!!
Ps: In case you need to show pivot table of total and not month on month for each salesman, then you need to create two pivot tables, one hidden pivot table to act as data source for sparkline. The other pivot table (dashboard) with calculated empty column like explained above as placeholder for sparkline.