WEBVTT Kind: captions Language: en 00:00:08.559 --> 00:00:13.430 I've been working on my monthly budget and finally things look the way that I want them 00:00:13.430 --> 00:00:14.430 to. 00:00:14.430 --> 00:00:19.840 Making formatting decisions such as what columns to include, what text to include, and the 00:00:19.840 --> 00:00:23.970 text style and size can be difficult. 00:00:23.970 --> 00:00:29.429 I've already listed all my bills, but now I need to include my income information so 00:00:29.429 --> 00:00:32.199 I can do a couple of calculations. 00:00:32.199 --> 00:00:41.540 Calculating and analyzing numerical information Calculating and analyzing numerical information 00:00:41.540 --> 00:00:45.320 is really what Excel is intended to be used for. 00:00:45.320 --> 00:00:51.520 You can use it at home to create budgets like this one, or in the workplace to create complex 00:00:51.520 --> 00:01:01.830 spreadsheets that keep track of company finances, inventory, and more. 00:01:01.830 --> 00:01:06.830 To be able to use Excel to accomplish these tasks, it's important that you understand 00:01:06.830 --> 00:01:08.030 formulas. 00:01:08.030 --> 00:01:18.549 In school, you used formulas to calculate math problems like 2+2 =4 or 20% off of $100. 00:01:18.549 --> 00:01:25.289 Microsoft Excel uses these same formulas to perform spreadsheet calculations. 00:01:25.289 --> 00:01:32.060 A formula is an equation that performs a calculation using values in the worksheet. 00:01:32.060 --> 00:01:38.299 You're probably used to seeing equations like 2 + 2 with an equal sign after it, but in 00:01:38.299 --> 00:01:45.130 Excel, every formula starts with the equal sign. 00:01:45.130 --> 00:01:49.859 This may be a little confusing, but I promise it will make sense. 00:01:49.859 --> 00:01:59.179 In C3 you can see I have my net pay of $1500 listed and in C4 the money I made from my 00:01:59.179 --> 00:02:01.649 extra job that month. 00:02:01.649 --> 00:02:06.270 Let's say I want to add those two numbers together so the total income for the month 00:02:06.270 --> 00:02:08.890 appears in C5. 00:02:08.890 --> 00:02:16.610 Let's select cell C5 since that is where I want the total income for January to appear. 00:02:16.610 --> 00:02:21.830 Since Excel formulas all begin with an equal sign, use your keyboard to insert an equal 00:02:21.830 --> 00:02:26.580 sign in the cell. 00:02:26.580 --> 00:02:31.720 As you insert the equal sign, notice how the formula bar changes. 00:02:31.720 --> 00:02:37.010 You see, entering the equal sign makes the formula bar active. 00:02:37.010 --> 00:02:43.040 You can now see the Cancel icon, which removes information from the formula bar, the Enter 00:02:43.040 --> 00:02:48.470 icon, which will accept what you type, and the Function icon that provides you with some 00:02:48.470 --> 00:02:51.680 other mathematical options. 00:02:51.680 --> 00:02:55.950 Let's go back to C5 and finish our formula. 00:02:55.950 --> 00:03:04.909 We want to add my 1500 and 200, so we'll just type the first number and then use the keyboard 00:03:04.909 --> 00:03:14.010 to insert the addition sign and now type the second number. 00:03:14.010 --> 00:03:19.959 The addition sign lets Excel know that an add operation is to be performed. 00:03:19.959 --> 00:03:25.690 Ok, now that I have my formula entered in the cell, I can press the Enter key on my 00:03:25.690 --> 00:03:34.220 keyboard or click the Enter command on the formula bar and now I can see that Excel added 00:03:34.220 --> 00:03:42.469 $1500 and $200 and placed the answer in cell C5 where I typed the formula. 00:03:42.469 --> 00:03:48.430 Another way you can create formulas is by using cell references. 00:03:48.430 --> 00:03:57.530 Instead of typing =$1500+$200 in cell C5 to calculate my total income that month, I can 00:03:57.530 --> 00:04:00.340 use a cell reference in the formula. 00:04:00.340 --> 00:04:06.879 A cell reference is when you use the cell address in a formula and refer to that specific 00:04:06.879 --> 00:04:09.590 cell by name. 00:04:09.590 --> 00:04:15.439 For example, the cell address of this cell is C3. 00:04:15.439 --> 00:04:21.160 This cell is where column C and row 3 intersect. 00:04:21.160 --> 00:04:26.640 Now, let's write a formula using cell references. 00:04:26.640 --> 00:04:32.280 Select the cell where you want the answer to appear. 00:04:32.280 --> 00:04:38.410 Type the equal sign to let Excel know we're writing a formula. 00:04:38.410 --> 00:04:45.490 Type the cell number that contains the first number to be added, and then type the addition 00:04:45.490 --> 00:04:47.530 sign. 00:04:47.530 --> 00:04:53.069 Now type the cell number that contains the second number to be added. 00:04:53.069 --> 00:04:58.220 Press Enter on your keyboard or click the Enter button on the formula bar. 00:04:58.220 --> 00:05:04.740 So, you're probably wondering why we might want to use cell references, it doesn't seem 00:05:04.740 --> 00:05:10.340 any easier to type the cell address rather than the numbers, does it? 00:05:10.340 --> 00:05:14.750 Let's say that I realized I entered my Net Pay incorrectly. 00:05:14.750 --> 00:05:24.240 All I have to do is change the numbers in cell C3 and as I change it, my total income 00:05:24.240 --> 00:05:28.770 in cell D5 automatically changes. 00:05:28.770 --> 00:05:37.949 If I changed the amount in cell C3, I would not see a change in C5 because I didn't use 00:05:37.949 --> 00:05:46.760 a cell reference, but instead typed the numbers in the formula. 00:05:46.760 --> 00:05:50.900 Cell references are especially important to know about when working with columns that 00:05:50.900 --> 00:05:54.410 contain many numbers that change regularly. 00:05:54.410 --> 00:05:59.390 As you've probably noticed in most Microsoft Office applications, there is usually more 00:05:59.390 --> 00:06:02.300 than one way to accomplish a task. 00:06:02.300 --> 00:06:09.490 In this formula, I typed in the cell reference as I wrote the formula, but you can also use 00:06:09.490 --> 00:06:14.370 the point and click method to select cells so that you don't have to type the cell reference 00:06:14.370 --> 00:06:18.880 each time. 00:06:18.880 --> 00:06:24.610 Let's see how the point and click method works while creating a simple subtraction formula. 00:06:24.610 --> 00:06:31.090 I've entered the total amount of my January expenses in C29. 00:06:31.090 --> 00:06:37.610 Now to calculate how much money I have after paying all my January bills I just need to 00:06:37.610 --> 00:06:45.570 write a formula in C30 that subtracts my January expenses from my January income. 00:06:45.570 --> 00:06:52.620 So, we'll select the cell where we want the answer to appear and then insert the equal 00:06:52.620 --> 00:06:53.770 sign. 00:06:53.770 --> 00:07:05.419 Now, all I need to do is left-click the first cell that is included in the formula. 00:07:05.419 --> 00:07:11.600 The border of the cell changes to the marching ants and the cell reference C5 appears in 00:07:11.600 --> 00:07:15.250 the formula here. 00:07:15.250 --> 00:07:22.160 You can see the cursor is in C30, so now I can enter the subtraction sign to let Excel 00:07:22.160 --> 00:07:26.770 know that a subtraction operation needs to be performed. 00:07:26.770 --> 00:07:33.120 Now, just left- click the next cell in the formula. 00:07:33.120 --> 00:07:40.419 Since we want to subtract our January expenses, we'll select C29. 00:07:40.419 --> 00:07:42.750 Now it appears in my formula. 00:07:42.750 --> 00:07:47.340 If we have more cells to subtract, we would just repeat these steps until the formula 00:07:47.340 --> 00:07:49.270 is complete. 00:07:49.270 --> 00:07:56.380 I'm ready to click Enter.And now the formula shows me how much money I have remaining after 00:07:56.380 --> 00:07:59.830 paying all my bills. 00:07:59.830 --> 00:08:04.280 You'll find the point and click method of selecting cells is really helpful because 00:08:04.280 --> 00:08:12.050 often it's just easier and faster to use the mouse instead of the keyboard. 00:08:12.050 --> 00:08:17.130 We've created formulas using the addition and subtraction signs, but you can also use 00:08:17.130 --> 00:08:21.470 the multiplication and division signs in your formulas. 00:08:21.470 --> 00:08:26.940 All four of these mathematical signs or symbols are called operators because they tell Excel 00:08:26.940 --> 00:08:32.990 what type of mathematical operation needs to be performed. 00:08:32.990 --> 00:08:38.360 You need to know this term because if you have to use the Excel help tool, that is how 00:08:38.360 --> 00:08:41.130 it refers to these mathematical signs.