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 might disappear!

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 shortcut

    pivotfilldisappears03

  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!

    __________________