Happy Spreadsheet Day!
What are your feelings about allocating days to celebration or awareness of certain things? Like International Talk Like a Pirate Day (19th September), Oatmeal Nut Waffles Day (11th March) or Bathtub Day (7th October)? Well, the 17 October is International Spreadsheet day. Normally, I would think these days are pointless, but I love spreadsheets, so I’m going with it.
Excel v1.0
A few weeks ago, Debra Dalgleish of Contextures.com posted about Excel version 1.0. It’s come a long way since it’s 1985 launch. However, at its core, it’s still trying to achieve the same things.
Within the images of the post there was a screenshot showing custom number formatting. Wow, even Excel v1.0 had one of my favorite features! It is now 32 years later and still too many users do not use custom number formats.
As it is Spreadsheet day, in honor of Excel v1.0, I decided to share my favorite custom number formatting tricks. Most of them would have worked in Excel v1.0 and they certainly work in the modern versions.
Custom Number Formatting Tricks
This list is not complete, or comprehensive in any way, it’s just some interesting little tricks.
Adding text into the format
The custom number format below will display numbers with one decimal place, but more importantly, will display the word “profit” or “loss” depending on the signage of the number.
$###.0"m profit";$#,###.0"m loss";"nil profit"
Aligning numbers with parentheses
In the accounting world (and maybe other worlds) some numbers are displayed with parentheses. This results in right aligned numbers not being vertically aligned with each other. The custom number format below adds an empty space the same size as closed parentheses to the end of any positive or zero value and at the same time will add parentheses to negative numbers. Now the digits are perfectly aligned.
#,##0_);(#,##0);-_)
Dividing by thousands or millions
The source number and display number do not have the same. With custom number formats it is possible to display 1,234,567 as 1.2 without changing the source number. A comma ( , ) will divide the number by 1,000, two commas ( ,, ) will divide the number by 1,000,000
#,##0.0,,_);(#,##0.0,,);-_)
Changing text color based on value
Changing the color based on the value is easy, just insert the name of the color in square brackets at the start of each section.
[Blue]#,##0_);[Red](#,##0);-_)
The challenge is knowing which colors you can use. Therefore, it is often easier to use color numbers, which are based on the color palette.
[Color22]#,##0_);[Color5](#,##0);[Color48]-_)
Filling a cell
The example below fills the empty cell with hyphens before the value. The asterisk in the code fills space with any character directly after it. The @ symbol represents a text value.
*-@
It does not matter how wide the cell is, the characters will always fill it.
Formatting conditions
It is possible to create True and False conditions where numbers are formatted based on whether they meet a condition. In the example below, the Pass mark is 50, any value greater than or equal to 50 will display “Pass” in green, otherwise it will display “Fail” in red.
[Green][>=50]"Pass";[Red]"Fail"
Discover how you can automate your work with our Excel courses and tools.
The Excel Academy
Make working late a thing of the past.
The Excel Academy is Excel training for professionals who want to save time.