Excel Pivot Table Mystery
March 20, 2018
Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!
If you create a chart in Excel, it uses the workbook's theme colours. You can manually change the chart or theme colours, but someone asked if conditional formatting colours could be applied automatically.
I love an Excel challenge, and created a macro to colour a bar chart or pie chart, based on Excel's colour scales. It's a quick way to see which of the larger accounts are falling behind with invoicing (red).
Pivot tables are awesome, most of the time, but I ran into a pivot table mystery yesterday. A report with weekly totals was missing the latest few weeks (in the WkOf field).
Here's a link to the sample file, if you'd like to troubleshoot it, and my solution is below the screen shot.
Here are the troubleshooting steps that I used:
WARNING: The problem will occur again, unless you change a setting on the WkOf field. Sometimes you don't want new items to appear after filtering, but in this case I did want them to appear.
Read more about the pivot table field settings on my Contextures site.
Here are a couple of Excel articles that you might find useful or interesting.
Dashboards - Audry Loper shows how she changed boring 6-page reports into informative 2-page reports. Her tips are useful for Excel dashboards too. And if you want to make those dumbbell charts, Mynda Treacy has instructions. (Level - All)
Planning - Nick Orso has an interesting use for Excel - planning the layout of a mobile cabin. And if that's not cool enough, he used old skateboards to line the kitchen bar. (Level - All)
It's our granddaughter's 8th birthday this week, so I took her shopping on the weekend, to pick out a couple of outfits. Her favourite thing is this pink flamingo shirt, with reversible sequins - brush them down to show pink, and brush up to show the silver side. Apparently this is a hot fashion trend for girls her age, so I'll have to check the Microsoft store, to see if they have one with an Excel logo!
That's it for this week! If you have any comments or questions, send me an email.
NOTE: For the online version, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2018/20180320ctx.html
I'll also post any article updates or corrections there.
ddalgleish @ contextures.com
Last updated: May 4, 2018 3:26 PM