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"
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.
But, if you're still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts:
