Filetype Excel XLSX | Posted on 17 Aug 2022 | 3 years ago
Authentication
digital resources
216x Filetype XLSX File size 0.48 MB Source: moodle2.units.it
Sheet 1: Start
| Get started with formulas |
| In just a few steps, you'll be up and running with building formulas and functions in Excel, the world's most powerful spreadsheet app. |
| Go back to top by pressing CTRL+HOME. To start the tour, press CTRL+PAGE DOWN. |
| Basics: doing math with Excel | ||||||||||||
| You can Add, Subtract, Multiply, and Divide in Excel without using any built-in functions. You just need to use some basic operators: +, -, *, /. All formulas start with an equals (=) sign. |
Numbers to use: | Operation: | Formulas: | Answers: | ||||||||
| To Add, select cell F3, type =C3+C4, then press Enter. |
1 | Addition (+) | 3 | |||||||||
| To Subtract, select cell F4, type =C3-C4, then press Enter. | 2 | Subtraction (-) | -1 | |||||||||
| To Multiply, select cell F5, type =C3*C4, then press Enter. |
Multiplication (*) | 2 | ||||||||||
| To Divide, select cell F6, type =C3/C4, then press Enter. |
Division (/) | 0.5 | ||||||||||
| Check this out: change the numbers in cells C3 and C4, and watch the formula results automatically change. | Power (^) | 1 | ||||||||||
| EXTRA CREDIT: You can raise a value to a power by using the carat (^) symbol, like =A1^A2. Enter it with Shift+6. In cell F7, enter =C3^C4. | ||||||||||||
| Dive down for more detail | ||||||||||||
| Next step | ||||||||||||
| More about formulas, cells, and ranges | ||||||||||||
| Excel is made up of individual cells that are grouped into rows and columns. Rows are numbered, and columns are lettered. There are over 1 million rows and 16,000 columns, and you can put formulas in any of them. |
||||||||||||
| Formulas can contain cell references, ranges of cell references, operators, and constants. The following are all examples of formulas: =A1+B1 =10*20 =SUM(A1:A10) |
||||||||||||
| You'll notice that in our third example above, we used the SUM function. A function is a pre-built command that takes a value or values, calculates them in a certain way, and returns a result. For instance, the SUM function takes the cell references or ranges you specify, and totals them. In this example it takes the cells A1 through A10, and totals them. Excel has over 400 functions, which you can explore on the Formulas tab. |
||||||||||||
| Formulas with functions start with an equals sign, then the function name follows with its arguments (the values a function uses to calculate) wrapped in parentheses. |
||||||||||||
| You confirm a formula by pressing Enter. Once you do that the formula will calculate, and the result will be displayed in the cell. To see the formula itself, you can look at the formula bar beneath the Ribbon, or press F2 to enter Edit mode, where you'll see the formula in the cell. Press Enter again to finalize the formula and calculate the result. |
||||||||||||
| Some formula explanations | ||||||||||||
| =10*20 is a formula, where 10 and 20 are constants, and the * sign is the operator. | ||||||||||||
| =SUM(A1:A10) is a formula, where SUM is the function name, the opening and closing parentheses contain the formula arguments, and A1:A10 is the cell range for the function. | ||||||||||||
| =SUM(A1:A10,C1:C10) is a formula, where SUM is the function name, the opening and closing parentheses contain the formula arguments, and A1:A10,C1:C10 are the cell ranges for the function separated by a comma. | ||||||||||||
| GOOD TO KNOW: Constants are values that you enter in cells or formulas. While =10+20 might calculate the same as =A1+B1, constants aren't a good practice. Why? Because you can't easily see the constant without selecting the cell and looking for it. That can make it hard to change later. It's much easier to put your constants in cells, where they can be easily adjusted, and referenced in your formulas. For example: Select the yellow cell with 12 below. You'll see we used the SUM function with a range of cells. We didn't type in "4" or "8" directly into the formula. |
||||||||||||
| Previous | ||||||||||||
| Next | ||||||||||||
| More information on the web | ||||||||||||
| Use Excel as a calculator | ||||||||||||
| Overview of formulas in Excel | ||||||||||||
| Excel functions (by category) | ||||||||||||
| Excel functions (alphabetical) | ||||||||||||
| Free Excel training online | ||||||||||||
| Values | ||||||||||||
| 4 | ||||||||||||
| 8 | ||||||||||||
| 12 | ||||||||||||
| Introduction to functions | |||||||||
| Functions give you the ability to do a variety of things, like perform mathematical operations, look up values, or even calculate dates and times. Let's try a few ways to add up values with the SUM function. | Fruit | Amount | Meat | Amount | |||||
| Under the Amount column for Fruit (cell D7), enter =SUM(D3:D6), or type =SUM(, then select that range with the mouse, and press Enter. This will sum the values in cells D3, D4, D5, and D6. Your answer should be 170. |
Apples | 50 | Beef | 50 | |||||
| Now let's try AutoSum. Select the cell under the column for Meat (cell G7), then go to Formulas > AutoSum > select SUM. You'll see Excel automatically enter the formula for you. Press Enter to confirm it. The AutoSum feature has all of the most common functions. |
Oranges | 20 | Chicken | 30 | |||||
| Here's a neat keyboard shortcut. Select cell D15, then press Alt =, then Enter. This automatically enters SUM for you. | Bananas | 60 | Pork | 10 | |||||
| Dive down for more detail | Lemons | 40 | Fish | 50 | |||||
| EXTRA CREDIT Try the COUNT function using any of the methods you've already tried. The COUNT function counts the number of cells in a range that contain numbers. |
SUM > | 170 | SUM > | ||||||
| Next step | |||||||||
| More about functions | Item | Amount | Item | Amount | |||||
| Go to the Formulas tab and browse through the Function Library, where functions are listed by category, like Text, Date & Time, etc. Insert Function will let you search for functions by name, and launch a function wizard that can help you build your formula. When you start typing a function name after you press =, Excel will launch Intellisense, which will list all of the functions starting with the letters you type. When you find the one you want, press Tab, and Excel will automatically finish the function name and enter the opening parenthesis for you. It will also display the optional and required arguments. Now let's look at the anatomy of a few functions. The SUM function is structured like this: |
Bread | 50 | Bread | 50 | |||||
| If the SUM function could talk, it would say, return the sum of all the values in cells D38 to D41, and all of column H. SUM is the function name, D38:D41 is the first range argument, which is almost always required, and H:H is the second range argument, separated by a comma. Now, let's try one that doesn't require any arguments. |
Donuts | 100 | Donuts | 100 | |||||
| The TODAY function returns today's date. It will automatically update when Excel recalculates. | Cookies | 40 | Cookies | 40 | |||||
| CHECK THIS OUT Select these cells. Then in the lower-right corner of the Excel window, look for SUM: 170 in the bottom bar. That's called the Status Bar, and it's just another way to quickly find a total and other details about a selected cell or range. |
Cakes | 50 | Cakes | 50 | |||||
| IMPORTANT DETAIL Double-click this cell. You'll notice the 100 toward the end. Although it's possible to put numbers in a formula like this, we don't recommend it unless it's absolutely necessary. This is known as a constant, and it's easy to forget that it's there. We recommend referring to another cell instead, like cell F51. That way it's easily seen and not hidden inside a formula. |
Pies | 20 | Pies | 20 | |||||
| More information on the web | SUM > | COUNT > | |||||||
| All about the SUM function | |||||||||
| Use AutoSum to sum numbers | |||||||||
| All about the COUNT function | |||||||||
| Free Excel training online | |||||||||
| Back to top | |||||||||
| Next step | |||||||||
| Fruit | Amount | ||||||||
| Apples | 50 | ||||||||
| Oranges | 20 | ||||||||
| Bananas | 60 | ||||||||
| Lemons | 40 | ||||||||
| SUM > | 170 | ||||||||
| Item | Amount | ||||||||
| Cars | 20 | ||||||||
| Trucks | 10 | ||||||||
| Bikes | 10 | ||||||||
| Skates | 40 | ||||||||
| Total: | Additional Value | New Total | |||||||
| 180 | 100 | 180 | |||||||
| Free Excel training online | |||||||||
| Go back to top by pressing CTRL+HOME. To proceed to the next step, press CTRL+PAGE DOWN. | |||||||||
no reviews yet
Please Login to review.
