Excel formulas and functions (2023)

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:

(Video) Excel Formulas and Functions Tutorial

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:

(Video) Excel Formulas and Functions | Full Course

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:

  1. 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:

  1. Enter the required arguments:

  2. 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:

(Video) Basic Excel Formulas and Functions You NEED to KNOW!

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.

Excel formulas and functions (1)Excel formulas and functions (2)Excel formulas and functions (3)Excel formulas and functions (4)Excel formulas and functions (5)Excel formulas and functions (6)Excel formulas and functions (7)Excel formulas and functions (8)Excel formulas and functions (9)Excel formulas and functions (10)Excel formulas and functions (11)Excel formulas and functions (12)Excel formulas and functions (13)Excel formulas and functions (14)Excel formulas and functions (15)Excel formulas and functions (16)Excel formulas and functions (17)Excel formulas and functions (18)

(Video) Excel Basic Formulas and Functions

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? ›

Six Tips for Passing Microsoft Certification Exam (from Someone Who Hates Taking Tests)
  1. Step 1: Know what you need to know. Microsoft certification exams are usually hard, really hard. ...
  2. Step 2: Find good study material. ...
  3. Step 3: Take notes. ...
  4. Step 4: Practice. ...
  5. Step 5: Relax. ...
  6. Step 6: Manage your time when taking the exam.

Why is my formula not showing answer in Excel? ›

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.

What are the 5 basic Excel skills? ›

Below you have the top ten basic Excel skills to learn by anyone who is just starting out with Excel needs to learn.
  • Saving and Opening a Workbook. ...
  • Managing Worksheets. ...
  • Formatting Cells. ...
  • Printing. ...
  • Excel Functions (Basic) ...
  • Charts. ...
  • Sorting Data. ...
  • Find and Replace Option.

What are 5 main functions used in Excel? ›

The most frequently used functions in Excel are:
  • AutoSum;
  • IF function;
  • LOOKUP function;
  • VLOOKUP function;
  • HLOOKUP function;
  • MATCH function;
  • CHOOSE function;
  • DATE function;

What is the hardest thing to learn in Excel? ›

Top 10 things we struggle to do in Excel & awesome remedies for...
  • 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.
Jun 18, 2014

What do employers look for in Excel test? ›

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? ›

Following are 10 ways to improve your Excel skills in 2022:
  1. Master keyboard shortcuts. Excel comes with a slew of keyboard shortcuts to help you work faster and more productively. ...
  2. Conditional formatting. ...
  3. Macros. ...
  4. PivotTables. ...
  5. Charts. ...
  6. Filters/ AutoFilter. ...
  7. COUNTIF. ...
  8. Flash Fill.
Jun 14, 2022

What is the most powerful Excel function? ›

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? ›

12 Most Useful Excel Functions for Data Analysis
  • 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.

How do you display text and formulas in Excel? ›

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.

Videos

1. How to use Formulas and Functions in Microsoft Excel
(Teacher's Tech)
2. Master the MOST POPULAR Excel Formulas and Functions in ONLY 1 HOUR!
(MyExcelOnline.com)
3. Excel IF Formula: Simple to Advanced (multiple criteria, nested IF, AND, OR functions)
(Leila Gharani)
4. TOP 10 Excel Formulas to Make You a PRO User
(Kenji Explains)
5. 5 Excel Functions YOU NEED in 2021 & 2022
(Leila Gharani)
6. Top 10 Excel Formulas & Functions - Tutorial 1 | (VLOOKUP, INDEX, MATCH, XLOOKUP)
(MyExcelOnline.com)
Top Articles
Latest Posts
Article information

Author: Dr. Pierre Goyette

Last Updated: 04/02/2023

Views: 6534

Rating: 5 / 5 (70 voted)

Reviews: 85% of readers found this page helpful

Author information

Name: Dr. Pierre Goyette

Birthday: 1998-01-29

Address: Apt. 611 3357 Yong Plain, West Audra, IL 70053

Phone: +5819954278378

Job: Construction Director

Hobby: Embroidery, Creative writing, Shopping, Driving, Stand-up comedy, Coffee roasting, Scrapbooking

Introduction: My name is Dr. Pierre Goyette, I am a enchanting, powerful, jolly, rich, graceful, colorful, zany person who loves writing and wants to share my knowledge and understanding with you.