This post may contain affiliate links. Please read my disclosure for more info:

Spreadsheet Day 2017

Spreadsheet Day 17 October

Spreadsheet Day 17 October

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"

Custom Number Format - add text

 

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);-_)

Custom Number Format - aligning brackets

 

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,,);-_)

Custom Number Format - divide millions

 

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);-_)

Custom Number Format - colors 1

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]-_)

Custom Number Format - colors 2

 

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.

*-@

Custom Number Format - fill cell

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"

Custom Number Format - conditions + colors

 

The Flip Add-in – the custom number formatting tool

Custom number formats have a variety of shortcomings:

  • They only exist within each individual workbook.  When you start a new workbook, you have to create them all over again.
  • They are easy to get wrong, even if you apply the same formats over and over again.  It just takes a small typing error and the format will display incorrectly.
  • You almost need to be an Excel expert just to apply custom number formats.

To stop this problem, I created the Flip Add-in.  Click here to get your hands on this time-saving today.

Save

Save

Leave a Reply

Your email address will not be published. Required fields are marked *