Microsoft Excel Tutorial Formulas
- Excel For Beginners Free Tutorials
- Microsoft Excel Tutorial Formulas
- Microsoft Excel 2016 Formulas Tutorial
- Microsoft Excel Training Formulas
- Microsoft Excel Basic Formulas For Beginners
- Microsoft Excel Advanced Formulas Tutorial Pdf Free Download
A formula is an expression which calculates the value of a cell. Functions are predefined formulas and are already available in Excel. For example, cell A3 below contains a formula which adds the value of cell A2 to the value of cell A1. For example, cell A3 below contains the SUM function which. Download our Formulas tutorial workbook. We've put together a Get started with Formulas workbook that you can download. If you’re new to Excel, or even if you have some experience with it, you can walk through Excel’s most common formulas in this tour. Microsoft Excel Tutorial: – In Microsoft Excel Tutorial, we explain the number or shortcut keys, function and formula of excel. With this tutorial, you will be able to work very much effective in the excel.
It’s easy to use Excel for many of your day-to-day number-crunching tasks, like determining your business’s average sale, computing classroom grades, or forecasting college expenses. Use this handy Cheat Sheet to discover great functions and tips to help you get the most out of Excel.
Excel Functions You’re Likely to Use
Some Excel functions apply to specific subject areas, but others are general and apply to all needs. The following list shows an array of Excel functions used by one and all. Check here for a quickie reference to the purpose of each Excel function.
| Excel Function | Description |
|---|---|
| SUM | Calculates the sum of a group of values |
| AVERAGE | Calculates the mean of a group of values |
| COUNT | Counts the number of cells in a range that contains numbers |
| INT | Removes the decimal portion of a number, leaving just the integer portion |
| ROUND | Rounds a number to a specified number of decimal places or digit positions |
| IF | Tests for a true or false condition and then returns one value or another |
| NOW | Returns the system date and time |
| TODAY | Returns the system date, without the time |
| SUMIF | Calculates a sum from a group of values, but just of values that are included because a condition is met |
| COUNTIF | Counts the number of cells in a range that match a criteria |
Excel Text Functions You’ll Find Helpful
Excel’s text functions are very helpful when you’re working with names, addresses, customer lists, or any other text-based data. Here is list of Excel functions associated with text, along with a description of what each function does:
| Function | Description |
|---|---|
| LEFT | Extracts one or more characters from the left side of a text string |
| RIGHT | Extracts one or more characters from the right side of a text string |
| MID | Extracts characters from the middle of a text string; you specify which character position to start from and how many characters to include |
| CONCATENATE | Assembles two or more text strings into one |
| REPLACE | Replaces part of a text string with other text |
| LOWER | Converts a text string to all lowercase |
| UPPER | Converts a text string to all uppercase |
| PROPER | Converts a text string to proper case |
| LEN | Returns a text string’s length (number of characters) |
Excel Order of Operations to Keep in Mind
Mathematics dictates a protocol of how formulas are interpreted, and Excel follows that protocol. The following is the order in which mathematical operators and syntax are applied both in Excel and in general mathematics. You can remember this order by memorizing the mnemonic phrase, “Please excuse my dear aunt Sally.”
Excel For Beginners Free Tutorials
Parentheses
Exponents
Multiplication and division
Addition and subtraction
Excel Cell References Worth Remembering
In Excel formulas, you can refer to other cells either relatively or absolutely. When you copy and paste a formula in Excel, how you create the references within the formula tells Excel what to change in the formula it pastes. The formula can either change the references relative to the cell where you’re pasting it (relative reference), or it can always refer to a specific cell.
You can also mix relative and absolute references so that, when you move or copy a formula, the row changes but the column does not, or vice versa.
Preceding the row and/or column designators with a dollar sign ($) specifies an absolute reference in Excel.
| Example | Comment |
|---|---|
| =A1 | Complete relative reference |
| =$A1 | The column is absolute; the row is relative |
| =A$1 | The column is relative; the row is absolute |
| =$A$1 | Complete absolute reference |
Excel Error Messages to Get to Know
If you create a formula in Excel that contains an error or circular reference, Excel lets you know about it with an error message. A handful of errors can appear in a cell when a formula or function in Excel cannot be resolved. Knowing their meaning helps correct the problem.
| Error | Meaning |
|---|---|
| #DIV/0! | Trying to divide by 0 |
| #N/A! | A formula or a function inside a formula cannot find the referenced data |
| #NAME? | Text in the formula is not recognized |
| #NULL! | A space was used in formulas that reference multiple ranges; a comma separates range references |
| #NUM! | A formula has invalid numeric data for the type of operation |
| #REF! | A reference is invalid |
| #VALUE! | The wrong type of operand or function argument is used |
An Excel spreadsheet is helpful for displaying information in an organized way but Excel can be used for so much more. Its powerful calculation ability enables you to enter, manipulate, and analyze numbers. To take advantage of this function, you need to learn about formulas, which are essentially math equations. Here we describe how to create basic and slightly more complex formulas in Excel.
This article applies to Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel for Office 365.
Excel Formula Basics
Writing a spreadsheet formula is different from writing an equation in math class. The most notable difference is that Excel formulas start with the equal sign (=) instead of ending with it.
Excel formulas look like =3+2 instead of 3 + 2 =.
The equal sign indicates that what follows is part of a formula and not just a word or number that you want to appear in the cell. After you type the formula and press Enter on your keyboard, the result of the formula appears in the cell.
For example, if you type the formula above, =3+2 into a cell and press Enter, the result, 5, appears in the cell. The formula is still there, but it doesn't appear in your spreadsheet. If you select the cell, though, the formula appears in the formula bar at the top of the Excel screen.
Improve Formulas with Cell References
Excel formulas can also be developed using cell references. Continuing with our example, you would not enter the numbers 3 and 2, but instead would name cells where these numbers have been entered (see Using Cell References below for more on cell naming). When you write a formula this way, the formula cell always shows the sum of the numbers in those cells, even if the numbers change.
Microsoft Excel Tutorial Formulas
Here's a real-life example of how this approach can be useful. Say you lead a team of salespeople and are tracking their monthly and quarterly sales. You want to calculate their total sales for the year. Instead of entering every quarterly sales value into a formula, you use cell references to identify the cells where those values can be found within the spreadsheet.
Using Cell References
Each cell in Excel is part of a row and a column. Rows are designated with numbers (1, 2, 3, etc.) shown along the left side of the spreadsheet, while columns are designated with letters (A, B, C, etc.) shown along the top. To refer to a cell, use the column letter and row number together, such as A1 or W22 (the column letter always comes first). If you have a cell selected, you can see its reference at the top of the screen in the Name Box next to the formula bar.
In the image above, notice the cell references in the formula bar: E2, I2, M2, and Q2. They refer to the quarterly sales numbers for the salesperson named Jean. The formula adds those numbers together to come up with the annual sales number. If you update the numbers in one or more of those cells, Excel will recalculate and the result will still be the sum of the numbers in the referred cells.
Create a Formula With Cell References
Try creating a simple formula using cell references.
Microsoft Excel 2016 Formulas Tutorial
First, you must populate the spreadsheet with data. Open a new Excel file and select cell C1 to make it the active cell.
Type 3 in the cell, then press Enter on your keyboard.
Cell C2 should be selected. If it's not, select cell C2. Type 2 in the cell and press Enter on your keyboard.
Now create the formula. Select cell D1 and type =C1+C2. Notice that when you type each cell reference, that cell becomes highlighted.
Press Enter to complete the formula. The answer 5 appears in cell D1.
If you select cell D1 again, the complete formula =C1+C2 appears in the formula bar above the worksheet.
Enter Cell References With Pointing
Pointing is yet another way to refer to the values you want to include in your formula; it involves using your pointer to select cells to include in your formula. This method is the fastest of those we've discussed; it's also the most accurate because you eliminate the risk of making a mistake in typing in numbers or cell references. Here's how to do it (starting with the spreadsheet from the examples above):
Select cell E1 to make it the active cell and type in the equal sign (=).
Use your pointer to select cell C1 to enter the cell reference in the formula.
Type a plus sign (+), then use your pointer to select C2 to enter the second cell reference into the formula.
Press Enter to complete the formula. The result appears in cell E1.
To see how altering one of the formula values alters the result, change the data in cell C1 from 3 to 6 and press Enter on your keyboard. Notice that the results in cells D1 and E1 both change from 5 to 8, though the formulas remain unchanged.
Mathematical Operators and Order of Operations
Now we turn to operations besides addition, including subtraction, division, multiplication, and exponentiation. The mathematical operators used in Excel formulas are similar to those you may remember from math class:
- Subtraction – minus sign ( - )
- Addition – plus sign ( + )
- Division – forward-slash ( / )
- Multiplication – asterisk ( * )
- Exponentiation – caret ( ^ )
Microsoft Excel Training Formulas
If more than one operator is used in a formula, Excel follows a specific order to perform the mathematical operations. An easy way to remember the order of operations is to use the acronym BEDMAS.
- Brackets
- Exponents
- Division
- Multiplication
- Addition
- Subtraction
Microsoft Excel Basic Formulas For Beginners
Excel actually considers division and multiplication to be of equal importance. It performs these operations in the order in which they occur, from left to right. The same is true for addition and subtraction.
Here's a simple example of the order of operations in use. In the formula =2*(3+2) the first operation Excel completes is the one inside the brackets (3+2), with the result of 5. It then performs the multiplication operation, 2*5, with the result of 10. (The values in the formula could be represented by cell references rather than numbers, but Excel would perform the operations in the same order.) Try entering the formula into Excel to see it work.
Microsoft Excel Advanced Formulas Tutorial Pdf Free Download
Enter a Complex Formula
Now let's create a more complex formula.
Open a new spreadsheet and populate it with data as follows:
- 7 in cell C1
- 5 in cell C2
- 9 in cell C3
- 6 in cell C4
- 3 in cell C5
Select cell D1 to make it the active cell and type the equal sign followed by a left bracket (=().
Select cell C2 to enter the cell reference in the formula, then type the minus sign (-).
Select cell C4 to enter this cell reference into the formula, then type a right bracket ()).
Type the multiplication sign (*), then select cell C1 to enter this cell reference into the formula.
Type the plus sign (+), then select C3 to enter this cell reference into the formula.
Type the division sign (/), then select C5 to enter this cell reference into the formula.
Press Enter to complete the formula. The answer -4 appears in cell D1.
How Excel Calculated the Result
In the above example, Excel arrived at the result of -4 using the BEDMAS rules as follows:
- Brackets. Excel first carried out the operation within the brackets, C2-C4 or 5-6 for a result of -1.
- Exponents. There are no exponents in this formula, so Excel skipped this step.
- Division and Multiplication. There are two of these operations in the formula and Excel performed them from left to right. First, it multiplied -1 by 7 (the content of cell C1) to get a result of -7. It then performed the division operation, C3/C5 or 9/3, for a result of 3.
- Addition and Subtraction. The last operation Excel performed was the addition of -7+3 for the final result of -4.