Number Formats

Quick reference

• 00:04 Number formats are used to control how cells with numeric data appear in Excel.
• 00:10 Numeric data can mean dates, time, money,
• 00:12 percentages, and anything else that looks like a number.
• 00:16 The most important thing to understand about number formats is that
• 00:20 they only affect how the number looks.
• 00:22 They don't change the actual value of the number that Excel uses in calculations.
• 00:27 Let's take a look.
• 00:29 The most common number formats are available on the Home tab of the ribbon
• 00:33 in the Number command group.
• 00:35 There are some shortcuts for the really common ones, and a drop down for
• 00:39 the others.
• 00:42 To apply a number format, just select one or more cells and choose a format.
• 00:47 Remember that number formats will only change the way numbers look.
• 00:51 They have no effect on text.
• 00:54 On a worksheet we have the unit price and the sales totals of each item entered
• 00:58 in columns F and G, but they're not looking the way that we expect.
• 01:03 These figures don't look like currency.
• 01:06 Updating their appearance is as simple as selecting the cells that we want adjusted
• 01:10 and choosing the number format that we want.
• 01:13 In our case, we can just click on the dollar sign here if we want dollars.
• 01:17 Or we can click the drop down for more options.
• 01:21 We could go with euros, yen or pounds, but it says dollars here.
• 01:26 So let's select dollars.
• 01:29 We see that it changes our number format from general to accounting.
• 01:34 We could have also selected a format from the drop down here.
• 01:39 There's also this option called currency.
• 01:42 And the main difference between currency and accounting
• 01:45 is that the currency format places the dollar sign right next to the number.
• 01:49 Let's take a look.
• 01:51 So the dollar signs are here, and zero dollar values show up as 0.00.
• 01:57 Let's quickly do that here.
• 02:01 That's what the currency format looks like.
• 02:04 Let's Ctrl + Z to undo and see what the accounting format does.
• 02:07 Remember the shortcut for accounting is just to click on the dollar sign.
• 02:11 And if we did zero here, it displays as a dash and
• 02:15 places the dollar sign to the far left.
• 02:18 So let's undo, our unit prices 90 cents, and
• 02:22 we'll select the entire area and make them one format, accounting.
• 02:28 If you want to see all possible number formats,
• 02:32 you could just go to our drop down and click on More Number Formats here.
• 02:36 And there are lots of categories with different ways
• 02:41 that you can display your numbers.
• 02:43 Of course, this is Excel, so
• 02:45 there's a quicker way to display all the number formats.
• 02:48 The very quickest way is to do Ctrl + 1 if you're using a PC, or
• 02:53 Command + 1 if you're using a Mac, and the exact same dialog box shows up.
• 02:59 We escape.
• 03:00 This needs to be changed to accounting also.
• 03:05 And let's get our date entered here.
• 03:07 March 31 is the end of this quarter, and
• 03:11 it shows 31-Mar but what if we didn't like that format?
• 03:15 We can do Ctrl + 1 or Command + 1, and we want this formatted as a date.
• 03:22 And let's choose the format that we like.
• 03:26 And it now says March 31, 2020.
• 03:29 So just remember that number formats only affect the display of a cell value.
• 03:34 It doesn't affect the value itself.
• 03:37 So don't be scared if a number doesn't look the way you expect when you enter it.
• 03:41 Check the format and fix it if needed.

Lesson notes are only available for subscribers.