Monday Morning Mulling: June 2018 Challenge
2 July 2018
On the final Friday of each month, we set an Excel 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.
Final Friday Fix: June Challenge Recap
This month’s challenge was very simple to convey – did you find it easy to figure out?
Where did the row headings go in the attached Excel file for the even-numbered rows?
If you downloaded the attached Excel file, did you notice it’s surprisingly large? That might give you a clue. When you have large, bloated files (our last non-empty cell is in cell I12), it’s usually attributable to sheet-wide formatting.
Well, it’s formatting that’s the problem here too: not sheet-wide, just in one column – in our case, column K. Click on cell K2:
Notice the font size: 120!! That’s
Every second cell in column K has been formatted this way – hence the file size. If you format a cell to size 120, this happens:
All that’s been done now is change the row height to the Excel default size of 26.25:
This makes the row look as follows:
See what’s happened? The row shows no row number as the number was not bottom-aligned. That’s it. File size and missing row numbers explained!
To try and throw people off, I did align number in each row to be bottom aligned so that the numbers in the cells would be visible. To make this formatting change, simply format cells (CTRL + 1), go to the ‘Alignment’ tab and select ‘Bottom’ for the ‘Vertical:’ text alignment (circled):
To bring the row headers back, all you have to do is highlight either the column (or the sheet if you don’t know where the font size has been increased) and make the font size more reasonable once more.
Word to the Wise
So what? It seems like a trivial trick with no practical application. Well, you might be right, but I wanted to make a key point about Excel – you can’t always take everything at face value. As model auditors, we have checked / reviewed many models and we have learned that people sometimes try to take advantage of element we tend to take at face value. If you figured out, what was wrong here, well done; if not, hopefully, it’s taught you to scratch beneath the surface a little more.
The Final Friday Fix will return on Friday 27 July 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.