1 00:00:00,05 --> 00:00:02,07 - [Instructor] Let's talk about data types in Excel 2 00:00:02,07 --> 00:00:04,07 and why they're important. 3 00:00:04,07 --> 00:00:06,03 This sheet is a great example, 4 00:00:06,03 --> 00:00:09,04 because it contains all sorts of data types. 5 00:00:09,04 --> 00:00:13,04 There's text, there's numbers, there's dates. 6 00:00:13,04 --> 00:00:16,02 Excel does a really good job at figuring out 7 00:00:16,02 --> 00:00:19,08 what data type to use when you're entering data into a cell. 8 00:00:19,08 --> 00:00:21,09 For example, if I type a word, 9 00:00:21,09 --> 00:00:26,02 I'll use my trusty word Apples and hit the Tab key, 10 00:00:26,02 --> 00:00:28,03 it's left formatted. 11 00:00:28,03 --> 00:00:29,04 This is telling me 12 00:00:29,04 --> 00:00:32,06 that it's just treating it as a text string, 13 00:00:32,06 --> 00:00:37,00 but watch what happens when I put in a number. 14 00:00:37,00 --> 00:00:38,01 I'll hit the Enter key, 15 00:00:38,01 --> 00:00:41,00 and the text stays to the right. 16 00:00:41,00 --> 00:00:44,05 That's how Excel knows that it's a number or a value field. 17 00:00:44,05 --> 00:00:48,02 And by that, I mean a date, an integer, 18 00:00:48,02 --> 00:00:50,02 a time, a currency, 19 00:00:50,02 --> 00:00:53,03 any sort of value that's not a straight text field. 20 00:00:53,03 --> 00:00:55,02 And the reason it's important 21 00:00:55,02 --> 00:00:57,02 is because it's going to decide 22 00:00:57,02 --> 00:01:01,04 what kind of formula or function you can do on that cell. 23 00:01:01,04 --> 00:01:06,07 For example, if I move over to the Formulas ribbon tab, 24 00:01:06,07 --> 00:01:09,05 there's Financial functions that I can do. 25 00:01:09,05 --> 00:01:11,08 There's functions that you can do on text, 26 00:01:11,08 --> 00:01:15,08 for example, to transform text directly into a cell. 27 00:01:15,08 --> 00:01:19,09 You can do calculations based on date and time cells. 28 00:01:19,09 --> 00:01:23,08 But it needs to know what is what. 29 00:01:23,08 --> 00:01:26,04 Let's take a look at these date cells. 30 00:01:26,04 --> 00:01:29,07 You can enter dates in different ways. 31 00:01:29,07 --> 00:01:35,00 For example, I can type this. 32 00:01:35,00 --> 00:01:37,05 I can see that it's right-aligned. 33 00:01:37,05 --> 00:01:40,00 In fact, if I go over to the Formula bar, 34 00:01:40,00 --> 00:01:43,04 I can see that it's completely expanded the date. 35 00:01:43,04 --> 00:01:45,06 Depending on what local you're set to, 36 00:01:45,06 --> 00:01:48,04 Excel will adjust the dates accordingly. 37 00:01:48,04 --> 00:01:51,05 For example, it may swap these two values. 38 00:01:51,05 --> 00:01:56,09 I can also write a time. 39 00:01:56,09 --> 00:01:59,00 And again, it's right-aligned, 40 00:01:59,00 --> 00:02:01,00 and if I go to the Formula bar, 41 00:02:01,00 --> 00:02:05,03 I can see that it is in fact a time. 42 00:02:05,03 --> 00:02:06,09 You can choose and format 43 00:02:06,09 --> 00:02:09,03 the type of data cell that you want. 44 00:02:09,03 --> 00:02:12,03 I'm going to click and drag and highlight these cells, 45 00:02:12,03 --> 00:02:14,07 which are clearly currency. 46 00:02:14,07 --> 00:02:18,01 If I go back to the Home ribbon tab, 47 00:02:18,01 --> 00:02:20,02 right there in the middle of the tab, 48 00:02:20,02 --> 00:02:23,03 I can format the data type and set it. 49 00:02:23,03 --> 00:02:25,07 It's set to general right now, 50 00:02:25,07 --> 00:02:28,01 but I can click the down arrow next to this button 51 00:02:28,01 --> 00:02:30,00 and set it to currency. 52 00:02:30,00 --> 00:02:33,04 In fact, I can even choose what type of currency I want, 53 00:02:33,04 --> 00:02:38,02 though, it will default to whatever locale I use. 54 00:02:38,02 --> 00:02:41,02 In fact, I can click this dropdown 55 00:02:41,02 --> 00:02:45,01 and choose Currency or Accounting. 56 00:02:45,01 --> 00:02:46,05 Here's Currency. 57 00:02:46,05 --> 00:02:48,06 And if I choose Accounting, 58 00:02:48,06 --> 00:02:52,03 Accounting is much more formal and standard. 59 00:02:52,03 --> 00:02:54,08 I can even format the way it looks. 60 00:02:54,08 --> 00:02:57,03 For example, I can increase or decrease 61 00:02:57,03 --> 00:02:59,05 the amount of decimals used, 62 00:02:59,05 --> 00:03:03,03 I can add a comma as a thousand separator, 63 00:03:03,03 --> 00:03:05,04 I can add a percentage sign, 64 00:03:05,04 --> 00:03:07,08 I can set all of these cell formats. 65 00:03:07,08 --> 00:03:12,01 I can do it per cell, I can do it per column or row, 66 00:03:12,01 --> 00:03:13,09 whatever is going to work for me 67 00:03:13,09 --> 00:03:17,07 to do the type of calculation that I need. 68 00:03:17,07 --> 00:03:20,09 For example, this cell is reporting the days 69 00:03:20,09 --> 00:03:23,06 since the gift card was issued. 70 00:03:23,06 --> 00:03:24,08 I can see up here, 71 00:03:24,08 --> 00:03:28,02 it's a simple formula that's calculating today's date 72 00:03:28,02 --> 00:03:30,09 and subtracting it from the value in this cell 73 00:03:30,09 --> 00:03:33,01 when the card was issued. 74 00:03:33,01 --> 00:03:35,05 These are the types of calculations you can do 75 00:03:35,05 --> 00:03:38,00 when you have the type properly set. 76 00:03:38,00 --> 00:03:41,00 So always be on the lookout in the back of your mind 77 00:03:41,00 --> 00:03:43,06 if something doesn't look quite right. 78 00:03:43,06 --> 00:03:48,05 For example, I'll put in the time again, 79 00:03:48,05 --> 00:03:51,09 but, here, I can see that it's still left-aligned. 80 00:03:51,09 --> 00:03:54,03 That's because it didn't quite recognize 81 00:03:54,03 --> 00:03:56,03 that I was putting in a time. 82 00:03:56,03 --> 00:03:59,02 Although, if I had put a space in here after the PM 83 00:03:59,02 --> 00:04:00,07 and hit the Enter key, 84 00:04:00,07 --> 00:04:04,00 now it figures it out and adjusts accordingly.