Subscriber only lesson.

Sign up to this course to view this lesson.

View pricing

About this lesson

It is important how numerical data is presented in Excel. Learn how to effectively use number formatting.

Exercise files

Download this lesson’s related exercise files.

Number Formatting Part 1.xlsx
9 KB Number Formatting Part 1 - Solution.xlsx
9 KB

Quick reference

Number Formatting Part 1

Number Formatting explained using the CRaFT methodology.

When to use

Number Formatting can assist in building a financial model in Excel which is easy to understand and navigate through.

Instructions

  • Use Number Format to edit the type of data that is entered. Open the Number Format dialog box by clicking on the dialog launcher on the Number section of the Home tab, or press Ctrl + 1.
  • Examples:
    • Number : Specify how many decimal points and how the negative should be displayed
    • Date : Specify how the date should be displayed from a list
  • Quick number formatting:

  • 00:05 Before I return to layout tips, let's continue talking about formats and.
  • 00:11 And particularly about one that confuses people, number formatting.
  • 00:16 Number formatting is used to edit the type of data that's entered
  • 00:21 with regards to numbers.
  • 00:23 You can look at general number, currency, accounting, blah, blah, blah.
  • 00:27 We are to look at the custom one, which is the one that's complicated,
  • 00:31 in the next session.
  • 00:33 But before I do, let's go for a quick win.
  • 00:38 Quick number formatting.
  • 00:40 Quick number formatting is done quickly, surprise, surprise.
  • 00:45 Using the CTRL and Shift buttons on the keyboard at the same time and
  • 00:49 the numbers 1 to 6.
  • 00:52 That will actually modify it to make a number two decimal places, or the time,
  • 00:56 the date, currency, percentage, or exponential.
  • 01:00 What do I mean?
  • 01:02 Let's have a look at an example.
  • 01:06 Quick number formatting in a nutshell.
  • 01:09 In cell C4 I've got my input,
  • 01:12 the number 47,777, whatever that might be.
  • 01:18 And then what I've done is I've simply used a formula to actually put C4 into
  • 01:23 all of these cells.
  • 01:25 Let's see what Ctrl+Shift+1 does here.
  • 01:29 It puts it to two decimal places, as discussed.
  • 01:33 Ctrl+Shift+2 turns it to a time.
  • 01:37 Now, that's all done by what is after the decimal point.
  • 01:41 See this naught naught?
  • 01:43 Naught naught is midnight, 0.5 would be midday.
  • 01:46 0.75 would be 6 PM, and so on.
  • 01:52 Ctrl+Shift+3 turns it to a date.
  • 01:57 All numbers in Excel that are dates, are actually called serial numbers.
  • 02:02 It's time for a joke.
  • 02:04 If I deleted the serial number here, does that make me a serial killer?
  • 02:09 Sorry, the reason we have serial numbers is because of what
  • 02:14 was known as the year 2000 or millennium bug that we
  • 02:19 thought we'd be dividing by 0 for the year 2000.
  • 02:23 Microsoft and other spreadsheet programmers all decided to get around that
  • 02:28 by actually defining day one as the first of January, 1900.
  • 02:31 Day two as the 2nd of January, 1900.
  • 02:34 And therefore, day 47,777 is going to be the 21st of October 2030.
  • 02:42 Ctrl+Shift+4, you might be looking, if you look at a typical QUERTYUIOP keyboard.
  • 02:49 You'll see that you have an exclamation mark above the number 1 on
  • 02:52 the line above the QUERTYUIOP keys.
  • 02:55 You have the @ symbol above the number 2 and the # sign or
  • 02:58 hash sign above the number 3.
  • 03:00 Now the @ symbol is the Excel denomination for time.
  • 03:05 The # sign or the hash sign is for date.
  • 03:07 And if you look above the number 4, depending on what keyboard you've got.
  • 03:11 I've got $ here, that's currency.
  • 03:15 Above the number 5, Ctrl+Shift+5, percentage symbol, no surprise there.
  • 03:21 And above the number 6, the sort of two sides of a triangle,
  • 03:24 what's called a caret, C-A-R-E-T symbol.
  • 03:27 That's for exponential or scientific notation.
  • 03:31 4.78E+04.
  • 03:35 These are very quick ways to format numbers should you wish.
  • 03:39 Now obviously I still maintain that styles win over numbers formatting.
  • 03:43 But it is a personal preference.
  • 03:45 And therefore it is worth knowing these six tips here that will
  • 03:49 actually help format your spreadsheets just that much quicker.
  • 03:54 Next time out I'm going to look at some more long winded number formatting.

Lesson notes are only available for subscribers.

Gift this course