Pivot Table Fill Colour Disappears

Microsoft just announced the winner of their Excel World Champ data visualization contest. Congratulations to Ghazanfar Abidi, from Canada! I found his website today, and learned something new from his latest blog post – depending on how you apply it, pivot table fill colour disappears!

Excel World Champ Contest

In Microsoft’s Excel World Champ data visualization contest, there were 14 country finalists. Each finalist submitted a data visualization file, and everyone was invited to vote for their favourites. You can see the 14 submissions on the Tech Community blog.

The voting ended yesterday, and they announced the winner and the two runners up today. I don’t see names or countries listed with the submissions, but here’s a screen shot of the one with the most votes. Maybe that’s the overall winner, but other factors were included too, I think.

pivotfilldisappears01

The World Excel Champ Winner

I wasn’t familiar with the contest winner — Ghazanfar Abidi, from Canada – but Google helped me find his website.

And that’s where I saw his latest blog post, where he showed a problem with pivot table fill colour. I’d never noticed this problem before.

Pivot Table Fill Colour Disappears

Here are the steps to recreate the problem:

  1. Use the Ribbon command to add fill colour to a pivot table cell
  2. Select another cell in the pivot table, and press F4 (or Ctrl+Y) to repeat that command. In the screen shot below, cells D5 and A8 were coloured with the F4 shortcutpivotfilldisappears03
  3. Refresh the pivot table

Surprise! The cells that were formatted with the Ribbon command are still coloured, but the cell where I used the shortcut lost their fill colour!

pivotfilldisappears04

Testing Other Cell Formatting

I wanted to see if other types of cell formatting disappeared to, so I applied bold, italic, underline, font colour and font size:

  • with the Ribbon commands in column C
  • with F4 in column D
  • with the Format Painter in column E – I copied the “F4” formatting from column D

pivotfilldisappears05

Refresh the Pivot Table

Then, I refreshed the pivot table, and the only formatting that disappeared was the fill colour in cell D5, where the F4 shortcut had been used.

Surprisingly, the fill colour was still in E5, even though it was a copy of the D5 formatting.

pivotfilldisappears06

Pivot Table Fill Colour

So, the moral of the story, if there is one, is to use the Fill Colour or Format Painter Ribbon commands to apply fill colour, if you’re formatting a pivot table cell.

Don’t use the keyboard shortcuts F4 or Ctrl+Y to apply colour to a pivot table cell.

P.S. I added the Repeat command to the Ribbon, and tested that – the fill colour didn’t stick with that method either.

Use Your Knowledge for Evil

Or, if you like practical jokes, you can use the awesome revenge formatting trick that Ghazanfar Abidi showed in his blog post. But you’ll have to figure out how he did it!

__________________

Save

You may also like...

6 Responses

  1. Wow, I’m starstruck! You mentioned me and visited my goofy blog. It was an amazing experience participating in the Excel World Champ competitions. I’m overwhelmed to have been named the winner. I came across this formatting issue at work recently.. I’m surprised I didn’t run into this sooner.

    • Thanks, it was my pleasure, and congratulations on the awesome job that you did in the competition! Pivot table formatting is notoriously finicky, but I’d never seen that fill colour problem either. I’ll have a link to your blog in my newsletter this week too, so that sends a few more visitors.

  2. Prerit Ahuja says:

    Nice! The visualisation you mentioned in your post is mine, I won Silver in the championship. Public voting had only 5% weight in the final score.

  3. Sira Ekabut says:

    I’ve tried this trick. it’s disappeared only for F4 and Ctrl+Y. (Not for Format painter)

Leave a Reply

Your email address will not be published. Required fields are marked *