Excel Beginners

Data Types and Formatting: Step 2 To Mastering Spreadsheets

The Rest of the time personalization matters

Using Custom Formatting

The custom section allows users to create their own formats based on personalised requirements. Using the same symbols and notations, which users can see in the standard formats, new and unique formats can be created.

Custom Format Symbols and Characters

To simplify the breakdown of what constitutes a format, this section explains what different symbols represent, but does not cover what can be found within the square brackets ‘[]’ as those are system or regional formats, something which is not required for custom formatting.

Symbols will be grouped by format due to certain characters being repeated between formats.

Time Formatting

(s) seconds – only displays digits as needed

(ss) seconds – always display two digits

(m) minutes – only displays digits as needed

(mm) minutes – always display two digits

(h) hours – only displays digits as needed

(hh) hours – always display two digits

(AM/PM) including this will display time on the 12-hour format. Excluding this will display time on the 24-hour format.

Date Formatting

(d) day – only display digits as needed

(dd) day – always display two digits

(ddd) day – shorthand text for the day of the week

(dddd) day – always write out the day of the week

(m) month – only display digits as needed

(mm) month – always display two digits

(mmm) month – always write out the month of the year

(yy) year – only display the last two digits of the year

(yyyy) year – always display the full four digits of the year

Number Formatting

(#) significant number – only display number if necessary

(0) non-significant number – force this digit to appear regardless of necessity

(?) spacer – force additional spaces to numbers in order to align values (only significant numbers are displayed)

(/) fraction – decimal values will be converted to a fractional format

(“ “) insert – will display its contents in the specified position regardless of other formatting

Custom Formatting Examples

Six examples will be used to demonstrate the interactions between some of the previously mentioned formatting options. It is important to test and understand the interactions between format styles as they can be quick and effective tools.

Example 1: Display Long Date/Time

This example will show the format notation to display a date/time value which includes the day of the week, the day/month/year in number form, as well as the time in 24-hour format.

Source value: 43897.86743

Format notation: dddd, dd/mm/yyyy hh:mm:ss

Intended output: Saturday, 07/03/2020 20:49:06

Example 2: Display Reporting Date

This example will show the format notation to display the long text form of a date, which includes the day of the week, the name of the month, and the day and year. This is a clean look that can be inserted as a header to a report.

Source value: 43897.86743

Format notation: dddd - mmmm d, yyyy

Intended output: Saturday – March 7, 2020

Example 3: Display Duration

This example will show the format notation to display a clean stopwatch style format to use when modeling duration or any kind (remember with duration values – the date will default to its original value).

Source value: 0.6489

Format notation: hh"h"mm"m"ss"s"

Intended output: 15h34m25s

Example 4: Display Aligned Fractions

This example will show the format notation to display decimal values as fractions. Only significant digits will be displayed, and the fractions will reduce themselves as much as possible, however they will display themselves so that when right aligned, consecutive cells will line up their values nicely. Try placing and formatting both examples (right aligned) one above the other.

Source values: 13.652 & 13.659

Format notation: # ####/????

Intended outputs: 13 163/250 & 13 659/1000

Example 5: Display Trailing Zeros

This example will show the format notation to display six decimal places to compare consistent measurements between values. Larger numbers will be formatted with a comma (,) as a thousand’s separator.

Source value: 285724.3582

Format notation: #,###.000000

Intended output: 285,724.358200

Example 6: Create Custom Currency Display

This example will show the format notation for numbers in its four components, using colour codes and text parameters. The four parameters are 1. Positive numbers, 2. Negative numbers, 3. Zeros, 4. Text values.

Source values: 103.02 & -23.40 & 0.00 & “packaging cost”

Format notation: [Blue] $ #,###.00; [Red] -$ #,###.00; [Black] $ 0.00; [Black] "Order includes: "@

Intended outputs: $ 103.02 (in blue), -$ 23.40 (in red), $0.00 (in black), “Order includes: packaging cost” (in black)

Details: sections are divided through the use of a semicolon (;), using the square brackets enables you to override the default colour of a cell, and the at symbol (@) appends the text format of the cell value into that portion of the format.