Monday Morning Mulling: May 2021 Challenge

On the final Friday of each month, we set an Excel / Power Pivot / Power Query / Power BI problem for you to puzzle over for the weekend. On the Monday, we publish a solution. If you think there is an alternative answer, feel free to email us. We’ll feel free to ignore you.

This month, we just wanted you to change the font colour of a cell. Easy, yes?

The Challenge

All you had to do was change the font colour of this text to blue – with no VBA help.

Our starter file was all yours.

Suggested Solution

As you probably suspected, all was not as it seemed and it wasn’t quite the trivial exercise it might have appeared on first glance. Changing the font to blue in the usual way did nothing. It appeared to go blue…

…but once the operation had completed…

Drat.

You might recall from our article on custom number formatting that number formatting is prioritised over text formatting and decide to take a look at the number formatting in the cell (CTRL + 1):

That didn’t yield the answer either.

Since the file is an Excel workbook (.xlsx) rather than a macro-enabled Excel workbook (e.g. .xlsm), then macros were not behind this devious behaviour either. So what the heck is causing the issue..?

Clearly, the problem must be number formatting is text formatting is not changing the font colour. But this can be defined somewhere else too – conditional formatting.

If you checked conditional formatting (ALT + O + D),

you would note that conditional formatting had been applied to all cells. Format “38718” is not very descriptive (!), but if you click on ‘Edit Rule…’ it leads you to realise all non-blank cells have been mandated to have this formatting:

If you click on the ‘Format…’ button, the Number tab of the resulting ‘Format Cells’ dialog explains everything:

Clearing this custom number format type will allow you to change the font colour.

If you fell for this example, you might be seeing ‘Red’ in more ways than one (Groan – Ed.).

Until next month!

The Final Friday Fix will return on Friday 25 June 2021 with a new Excel Challenge. In the meantime, please look out for the Daily Excel Tip on our home page and watch out for a new blog every business workday.