Welcome to our free Introduction to Microsoft Excel tutorial. This tutorial is based on Webucator's Introduction to Microsoft Excel 2019 Training course.
This is the math lesson, and whether you love or hate math, by the end of this lesson you will agree that Excel makes math easy. We will start by explaining some of the basic concepts you need to understand regarding how Excel does math and then will get into entering formulas.
Lesson Goals
The six mathematical operators you need to know to enter basic formulas in Excel are:
When a formula uses more than one operator, the order of operations in Excel is as follows:
The simplest (not always the easiest) way to enter a formula in Excel is to:
If you are using a touch-enabled device, you can "write" equations using a stylus or your finger and Excel will convert it to text. If you are not using a touchscreen device, you can do the same using your mouse.
To use Ink Equations, from the Insert tab of the Ribbon, in the Symbols group, select Equation.
From the drop-down, select Ink Equations.
A dialog box opens where you can insert your equation and then click Insert to add it to the worksheet.
Some formulas can be entered simply by clicking a button in Excel. Excel will even guess which data the formula applies to, meaning that if your spreadsheet is properly laid out, you don't have to select the cells.
To quickly sum a row or column of data:
To quickly count the number of cells in a row or column that have data:
If you select the More Functions option from the AutoSum command, you can also use the COUNTA
and COUNTBLANK
functions. COUNTA
will count all cells that contain data (not just numbers). COUNTBLANK
will count the empty cells in a range of cells.
To quickly average the numbers in a row or column:
To quickly find the minimum number in a row or column:
To quickly find the maximum number in a row or column:
Formulas can be copied from one cell to other cells in Excel. To copy formulas in Excel:
Another way to copy formulas is by using Autofill, or dragging the Fill Handle in the cell containing the formula to the other cell or cells in which you wish to copy the formula. Autofill, which was introduced earlier in this course, is especially useful for copying formulas.
To use Autofill to copy formulas:
When working with formulas, if you wish to display the formula within its associated cell in a worksheet, select the Formula tab and in the Formula Auditing group, select Show Formulas.
You can toggle this option on and off by selecting and de-selecting this option.
Note that in the example above, when we copied the formula from one cell to others, the formula automatically changed to add the cells in the corresponding rows:
This happened because the formula we entered contained Relative cell references.
Relative cell references are the default in Excel. They are called "relative" because the actual cell row and column used in the formula are not significant. Instead, the significant thing is the location of the cell used in the formula relative to the location of the cell in which the formula is entered.
The way to think of the formula E2 = B2 + C2 + D2 is Result = (cell 3 places to the left) + (cell 2 places to the left) + (cell 1 place to the left) of the cell in which the Result is entered.
Sometimes you want a formula to refer to a specific cell regardless of where in your worksheet the formula is executed. In the above example, the formulas in column F all refer to cell B1.
The way to tell Excel that a formula should always refer to a specific cell is to use dollar signs ($). In the above example, the formula we used in cell F4 is =E4 / $B$1. No matter where we put this formula in our worksheet, it will divide the cell one to the left of the cell in which we enter the formula by cell B1.
Finally, notice that we entered the dollar sign twice in the above formula, before the "B" and before the "1". The first $ made the column an absolute reference and the second made the row an absolute reference. In this example, it was not actually necessary to make the column an absolute reference. The takeaway here is that when you refer to a cell in a formula, you can make either or both of the column and row references Absolute or Relative.
Mixed cell references are a combination of relative and absolute references.
Just like in absolute cell references, in mixed cell references the dollar sign ($) is used to refer to a specific cell that is fixed.
An example of a mixed cell reference would be: $F4. This would mean that the the column letter, F, is fixed, while the row number is allowed to be copied from cell to cell.
In this exercise, you will use formulas to enter additional data into the quarterly profit and loss statement we are creating for Dave's Lemonade Stand.