What is a formula?
A formula in Excel is an expression that returns a specific result. For example:
Note: All formulas in Excel must begin with an equal sign (=).
cell references
In the examples above, the values are "encoded". That means the results won't change unless you edit the formula again and change a value manually. This is generally considered bad form because it hides information and makes a table difficult to maintain. Instead, use cell references so that values can be changed at any time. On the next screen, C1 contains the following formula:
Please note that we are using cell references for A1, A2 and A3, these values can be changed at any time and C1 will still show an accurate result.
All formulas return a result.
All formulas in Excel return a result even if the result is an error. A formula is used below to calculate the percent change. The formula returns a correct result in D2 and D3, but returns a #DIV/0! Error in D4 because B4 is empty:
There are different ways to deal with errors. In this case, you could supply the missing value in B4, or "catch" the error with the IFERROR function and display a friendlier message (or nothing at all).
Copy and paste formulas
The beauty of cell references is that they update automatically when a formula is copied to a new location. This means you don't have to keep entering the same basic formula over and over again. In the following screen, the formula in E1 was copied to the clipboard using Ctrl + C:
Below: Formula pasted into cell E2 using Ctrl + V. Notice that the cell references have changed:
The same formula stayed with E3. The cell addresses are updated again:
Relative and absolute references
Previous cell references are called relative references. This means that the reference is relative to the cell it lives in. The formula in E1 above is: Literally, this means "cell left 3 columns" + "cell left 2 columns" + "cell left 1 column". So if the formula is copied to cell E2, it will continue to work the same way. Relative references are extremely useful, but sometimes you don't want a cell reference to change. A cell reference that does not change when copied is called an absolute reference. To make an absolute reference, use the dollar sign ($): For example, in the following screen, we want to multiply by 10 each value in column D that is entered in A1. By using an absolute reference for A1, we "lock" that reference to change when the formula is copied to E2 and E3:
These are the final formulas in E1, E2, and E3: Note that when the formula is copied, the reference to D1 is updated, but the reference to A1 never changes. Now we can easily change the value in A1 and all three formulas will be recalculated. Then the value in A1 has changed from 10 to 12:
This simple example also shows why it makes no sense to hardcode values into a formula. By storing the value in A1 in one place and referencing A1 with an absolute reference, the value can be changed at any time and all associated formulas are updated immediately. Tip: You can use the F4 key to switch between relative and absolute syntax.
How to enter a formula
To enter a formula: Instead of typing cell references, you can point and click on them as shown below. References are colour-coded:
All formulas in Excel must begin with an equal sign (=). No equal sign, no formula:
How to change a formula
To edit a formula you have 3 options: whichever option you use, press Enter to confirm your changes when you're done. If you want to cancel and leave the formula as it is, click the escape key.Video: 20 tips for entering formulas
What is a function?
When working in Excel, the words "formula" and "function" are used frequently, and sometimes interchangeably. They're closely related, but they're not exactly the same. Technically, a formula is any expression that begins with an equal sign (=). A function, on the other hand, is a formula with a specific name and purpose. In most cases, functions have names that reflect their intended use. For example, you're probably already familiar with the SUM function, which returns the sum of the given references: The AVERAGE function, as expected, returns the average of the given references: And the MIN and MAX functions return the minimum and maximum values, respectively. :Excel includes hundreds of specific functions. To get started, check out 101 Excel Key Features.
function arguments
Most functions require input to return a result. These entries are called "arguments". A function's arguments appear in parentheses after the function name, separated by commas. All functions require matching opening and closing brackets (). The pattern looks like this: For example, the COUNTIF function counts the cells that meet the criteria and takes two arguments, range and criteria: In the screen below, the range is A1:A5 and the criteria is "red" . The formula in C1 is: Video: Using the COUNTIF function Not all arguments are required. Arguments in square brackets are optional. For example, the YEARFRAC function returns the fractional number of years between a start date and an end date and accepts 3 arguments: start date and end date are required arguments, base is an optional argument. Below is an example of using YEARFRAC to calculate current age based on date of birth.
How to enter the function
If you know the name of the function, just start typing. Here are the steps:
- Type the equal sign (=) and start typing. Excel creates a list of matching functions as you type:
When you see the feature you want in the list, use the arrow keys to select it (or just keep typing).2. Type Tab to accept a function. Excel completes the function:
Enter the required arguments:
Press Enter to confirm the formula:
(Video) Top 10 Most Important Excel Formulas - Made Easy!
Combining Functions (Nesting)
Many Excel formulas use more than one function, and functions can be "nested" within one another. For example, below we have a birth date in B1 and we want to calculate the current age in B2:
The YEARFRAC function calculates years with a start date and an end date:
We can use B1 for the start date and then use the TODAY function to specify the end date:
If we press enter to confirm, we get the current age based on today's date:
Notice that we use the TODAY function to send an end date to the YEARFRAC function. In other words, the TODAY function can be nested inside the YEARFRAC function to provide the end date argument. We can take the formula one step further and use the INT function to split the decimal value:
Here the original YEARFRAC formula returns 20.4 to the INT function, and the INT function returns a final result of 20. Notes: Tip: The result of any formula or function can be entered directly into another formula or function.
Mathematical Operators
The following table shows the standard mathematical operators available in Excel:
logical operators
Logical operators support comparisons such as greater than, less than, etc. The logical operators available in Excel are listed in the table below: Video: How to create logical formulas
order of operations
When solving a formula, Excel follows a sequence called the "order of operations." First, each expression in parentheses is evaluated. Next, Excel solves for any exponent. After the exponents, Excel performs multiplication and division, then addition and subtraction. If the formula involves concatenation, this is done using standard mathematical operations. Finally, Excel evaluates the logical operators, if any. Tip: You can use the Evaluate function to see how Excel solves formulas step by step.
Convert formulas to values
Sometimes you want to get rid of formulas and just leave values in their place. The easiest way to do this in Excel is to copy the formula and then paste it using Paste Special > Values. This overwrites the formulas with the values they return. You can use a keyboard shortcut to paste values or use the Paste menu on the Home tab of the ribbon. Video: Insert special links
What's next?
Below are guides to help you learn more about Excel formulas and functions. We also offer online video training.
29 tips for working with formulas and functions (video version here) 500 formula examples with full explanations 101 essential Excel functions Guide to all Excel functions (in progress) Excel formula errors (examples and corrections) Criteria for formula: 50 example formulas for condition formatting Using F9 to Debug a Formula (Video) Excel Formula Errors and Fixes (Video)
David Bruns
Hi, I'm Dave Bruns operating an Exceljet with my wife Lisa. Our goal is to help you work faster in Excel. We create short videos and illustrative examples on formulas, functions, pivot tables, conditional formatting and charts.
FAQs
How do you show formulas and answers in Excel? ›
Switch between displaying formulas and their results from the keyboard. Press CTRL + ` (grave accent).
How can I pass my Excel Exam? ›- Step 1: Know what you need to know. Microsoft certification exams are usually hard, really hard. ...
- Step 2: Find good study material. ...
- Step 3: Take notes. ...
- Step 4: Practice. ...
- Step 5: Relax. ...
- Step 6: Manage your time when taking the exam.
You may have inadvertently activated the Show Formulas mode by clicking the corresponding button on the ribbon, or pressing the CTRL+` shortcut. To get the calculated results back, just toggle off the Show Formulas button or press CTRL+` again.
What is the fastest way to see every formula in your Excel spreadsheet? ›Display formulas using the Show Formulas command
To show formulas in Excel, you can click on the Formulas tab and then click Show Formulas. This will show all formulas in the Excel worksheet.
- Saving and Opening a Workbook. ...
- Managing Worksheets. ...
- Formatting Cells. ...
- Printing. ...
- Excel Functions (Basic) ...
- Charts. ...
- Sorting Data. ...
- Find and Replace Option.
- AutoSum;
- IF function;
- LOOKUP function;
- VLOOKUP function;
- HLOOKUP function;
- MATCH function;
- CHOOSE function;
- DATE function;
- VBA, Macros & Automation. VBA is the most struggling area of Excel. ...
- Writing Formulas. Excel has hundreds of functions. ...
- Making Charts. ...
- Pivot Tables. ...
- Conditional formatting. ...
- Array Formulas. ...
- Dashboards. ...
- Working with data.
Here are some of the other entry-level Excel skills employers are looking for: Basic Excel functions, including SUM, AVERAGE, MIN, MAX, COUNT, and IF. Ability to use conditional formatting to automatically format cells. Knowledge of keyboard shortcuts to make work more efficient.
What is the fastest way to improve Excel skills? ›- Master keyboard shortcuts. Excel comes with a slew of keyboard shortcuts to help you work faster and more productively. ...
- Conditional formatting. ...
- Macros. ...
- PivotTables. ...
- Charts. ...
- Filters/ AutoFilter. ...
- COUNTIF. ...
- Flash Fill.
XLOOKUP is the king of advanced functions (Microsoft hasn't given this title to any function, but I am sure no one deserves it more than this function). It is a new function that is available in Excel 2021 and Excel for Microsoft 365. This means people using the earlier versions of Excel won't be able to use it.
What are some cool Excel functions? ›
- IF. The IF function is extremely useful. ...
- SUMIFS. SUMIFS is one of the most useful Excel functions. ...
- COUNTIFS. The COUNTIFS function is another mega function for Excel data analysis. ...
- TRIM. ...
- CONCATENATE. ...
- LEFT/RIGHT. ...
- VLOOKUP. ...
- IFERROR.
We often hear that you want to make data easier to understand by including text in your formulas, such as "2,347 units sold." To include text in your functions and formulas, surround the text with double quotes ("").
How do you show formulas on a sheet? ›The quickest way to show formulas in Google Sheets is to use a keyboard shortcut. To do this, open your Google Sheets spreadsheet and make sure the sheet containing your data is active. Next, press Ctrl + ` (grave accent) on your keyboard. This will show all of the formulas currently in use on your spreadsheet.
How do I display formulas instead of values in Excel quizlet? ›To show formulas instead of values, click on the Formulas tab in the ribbon, then in the Formula Auditing group, click on the Show Formulas option.
Which one is not a function in Excel? ›B) AVG There is no function in excel like AVG, at the time of writing, but if you mean Average, then the syntax for it is also AVERAGE and not AVG.
Can you put text and a formula in the same cell in Excel? ›You can include both a formula and text in the same cell in Excel. You can do this in many ways, depending on what you are trying to accomplish. The first screenshot below shows an example of when it may be useful to combine a formula and text in the same cell.