Contextures News 20180320

Excel Pivot Table Mystery

March 20, 2018

Solve the pivot table mystery, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.

Note: For some products mentioned below, I earn a commission on sales. That supports the free info on my site!

Chart Colours

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).

  • The bar lengths and pie slices show the order amounts.
  • The chart colours show the percent invoiced -- green is high and red is low.

See the details on my blog, and download the sample file to see how it works. Also, see more conditional formatting examples, such as highlighting weekend dates.

chart colour scale macro

Missing Data

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.

missing data in pivot table

Here are the troubleshooting steps that I used:

  • Right-click on the pivot table, click Refresh. (nothing changed)
  • Right-click, click PivotTable Options, on Data tab, make sure there is check mark for "Refresh data when opening the file" (it was checked)
  • On the Excel Ribbon's Analyze tab, click Change Data Source (source included all the rows)
  • Finally, I noticed the filter on WkOf field -- I had unchecked the first week, because it didn't have 7 days of data. That created a "manual filter" for the field, and when the new weeks were added in the data, they didn't get through that filter. So, I checked all the new weeks, to have them appear.

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.

  • Right-click a date in WkOf, and click Field Settings
  • On the Subtotals & Filters tab, add a check mark for "Include new items in manual filter"

Read more about the pivot table field settings on my Contextures site.

Excel Articles

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)

Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour

Fashion Forward

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!

weekly photo

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:
I'll also post any article updates or corrections there.

Debra Dalgleish
ddalgleish @

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.



Get weekly Excel tips from Debra


pivot power free






Last updated: May 4, 2018 3:26 PM