Customised Formatting in Excel

May 31, 2019 | Excel Hints and Tips

Have you ever found that the number, date or time format that you want to use isn’t available?  These can be customised to provide exactly what you need.  These options are found from the format cells option, selecting number, then custom.

Number Formats

Customised number formats can consist of up to 4 parts.  Each part represents a different type of information.  Some or all of these can be used as required.  By looking at examples already created by default some idea of the structure required can be learnt.  These preset options can be selected then modified or the structure of the format can be typed from scratch.

The structure of number formats is:

Positive number; negative number; zero, text

For example:

#,##0.00;[Red](#,##0.00);”ZERO”;[Blue]

Image of Custom Number Format
  • Positive numbers are shown with a thousand separator and rounded to 2 decimal places
  • Negative numbers will have the same format but in red and in brackets
  • Zeros will show the word ZERO
  • Text will be coloured blue

There are other options that can be applied if required.

Date Formats

These use differing numbers of d’s, m’s and y’s to determine how days months and years are shown.

Days

d – day of the month as 1 figure if possible if not 2

dd – day of the month always as 2 figures

ddd – short version of the day of the week

dddd – long version of the day of the week

Months

m – month number shown as 1 figure if possible if not 2

mm – month number always as 2 figures

mmm – short version of the month name

mmmm – long version of the month name

Years

yy – year shown as 2 figures

yyyyy – year shown as 4 figures

For example:

If the date is 1st April 2018 then dddd dd m yy will change the format of the date to Monday 01 Apr 18

Image of Custom Date Formats

Time Formats

In a similar way to dates h’s, m’s and s’s can be used to format hours, minutes and seconds

Hours

h – hour using 1 figure if possible if not 2

hh – hour always as 2 figures

Minutes

m – minute using 1 figure if possible if not 2

mm – minute always as 2 figures

Seconds

s – second using 1 figure if possible if not 2

ss – second always as 2 figures

If AM/PM are added then the time shows as AM or PM or without this it shows as a 24 hour clock time

If the h’s are in square brackets they can go over 24.  For example if you are trying to work out the number of hours between 2 times.

For example:

If the time was 8:25 in the afternoon then hh:mm AM/PM would show as 08:25 PM while hh:mm would show as 20:25.

If the time was 28:25 then hh:mm would show as 04:25 but [hh]:mm would remain as 28:25

Image of Custom Time Formats

Want to learn more about Excel then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Excel Courses I run.