Number Formatting Part 1

Quick reference

• 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.