presentation on excel formulas and functions

Excel Formulas and Functions

Mar 26, 2019

1.26k likes | 2.83k Views

Excel Formulas and Functions. What is a formula in Excel?. A formula is statement written by the user to be calculated. Formulas can be as simple or as complex as the user wants. A formula can contain values, references to cells, defined names, and functions. What is a Formula?.

Share Presentation

• function name
• count functions
• a1 a2 a3 a4
• excel formulas

Presentation Transcript

What is a formula in Excel?

A formula is statement written by the user to be calculated. Formulas can be as simple or as complex as the user wants. A formula can contain values, references to cells, defined names, and functions.

What is a Formula? • It is a mathematical equation used to caluculate a value. • In Excel formulas must begin with an equal ( = ) sign.

Examples of Mathematical Operators • Operator Performs • + (plus sign) = Addition • - (minus sign) = Subtraction • * (asterisk) = Multiplication • / = (slash) Division

Examples of Mathematical Operators • ( ) (parentheses) = Controls the order of mathematical operations; calculations within parentheses are performed first • % (percent) = Converts a number into a percentage; for example, when you type 10%, Excel reads the value as .10 • ^ (caret) Exponentiation; for example, when you type 2^3, Excel reads the value as 2*2*2

Enter Formulas • 1. Select the cell into which you want to enter the formula. • 2. Type an equal sign (=) to begin the formula. • 3. Enter the first cell referenced in the formula. • 4. Enter the first mathematical operator. • 5. Enter the next cell referenced in the formula. • 6. Continue entering cell references and mathematical operators as needed. • 7. When you have finished creating the formula, press [Enter].

Examples of Formulas • = A1+A2+A3+A4 – will add all the values in the four cells • = (8*7)+2 is 58 • = 8*(7+2) is 72 • = 2^2 is 8

What is a Function in Excel ?

What is a function? • A function is a preset formula in Excel. Like formulas, functions begin with the equal sign ( = ) followed by the function's name and its arguments. The function name tells Excel what calculation to perform. The arguments are contained inside round brackets.

What is a Function in Excel? • A function is a piece of code designed to calculate specific values and are used inside formulas.

What are Functions? • Functions are typed alongside parenthesizes, where in the arguments if any are listed in between. To use functions in a formula, for example :=COS(3.14) will return the calculated cosine. =NOW() returns the current time. =SUM(1+2+3) *2 will multiply the sum by 2

Some Functions • TheSUM, AVERAGE, MAX, MIN, and COUNT functions are entered with the same syntax, including beginning the function with an equal sign (=) and then typing the name of the function and an open parenthesis. You then enter the cell range by dragging to select the cells or by typing the first and last cells in the range. These functions are defined in the following table:

Most common used Functions • The SUM function in Excel is specifically designed to add values from different ranges. The SUM Function can be typed into a cell in Excel, or inserted via the Insert Function tool (fx) to the left of your Formula bar. The syntax of the SUM Function is SUM(number1,number2, ...).  SUM is the function name, and contained within the brackets are "arguments", or the pieces of information that Excel requires to complete the Function.  The SUM function allows from 1 to 30 arguments (number 1, number ....) for which you require the total value or SUM.

Using Ctrl to Mark Cells • If you wish to add cells that are non-contiguous (not joined together), type in your function =SUM( click in the first cell you wish to add.  Hold down your Ctrl key and click in all other cells you wish to add up, then type in a ).  Typing in a comma instead of selecting with your Ctrl key also works just as efficiently as well.

Using SUM to Add a Range from a Different Worksheet • You can easily use SUM to add up the same range in different worksheets.  Click in the cell you want the result of your addition in, then holding down the Shift key, click on the next worksheet that you wish to include in your calculation and highlight the range to be used, then click Enter.

Excel’s AutoSum Function • Because adding numbers is probably the most common function that Excel is used for, Excel has a built-in Feature called AutoSum located on the Standard toolbar.  AutoSum is represented as the Greek Capital letter Sigma Σ found under the home tab editing group.  You can use AutoSum to sum a range of cells.  A Range can be one single cell, or many cells.  You can sum cells in a contiguous (no gaps) range of cells, or a non-contiguous (cells not joined together) range.

AutoSum • To use AutoSum  you must click in the cell that you wish your result, or addition to appear in.  As a default, AutoSum looks up a column for figures immediately above it to add together. • This works great, unless it encounters a blank row or text.  If it does, then it stops at the last cell with a number in it.  If there are no numbers above it, AutoSum will automatically go to the left looking for numbers to add up, but will again stop at a blank column or text.  

• More by User

FORMULAS AND FUNCTIONS IN EXCEL

FORMULAS AND FUNCTIONS IN EXCEL. Mrs. Brownfield Smith-Cotton High School Sedalia, MO. FORMULAS. A mathematical operation performed in a spreadsheet Can be entered directly in the cell Can be entered in the formula bar. FORMULAS. All formulas must start with the equal sign =

495 views • 13 slides

02. Excel Formulas

02. Excel Formulas. File -> Open -> 02b-datastart.xlsx. Find and Replace. Click Find & Select -> Replace. Find What: N.A. Click Replace All. Click OK. Click Close. All instances of N.A. have been replaced . Delete Rows. Click on Cell A2. Click Delete -> Delete Sheet Rows.

832 views • 67 slides

Excel Formulas

Excel Formulas. File -> Open -> 02b-datastart.xlsx. Delete Rows. Select cell A2. Click Delete -> Delete Sheet Rows. Find and Replace. Click on any blank cell then Click Find & Select -> Replace. Find what: N.A. Click Replace All. Click OK. Select Cell I1. Type Number of Shares.

782 views • 48 slides

Excel Tutorial 3 Calculating Data with Formulas and Functions

Excel Tutorial 3 Calculating Data with Formulas and Functions. Objectives. Make a workbook user friendly Translate an equation into an Excel formula Understand function syntax Enter formulas and functions with the Quick Analysis tool

795 views • 47 slides

Formulas and Functions 1

Formulas and Functions 1. Microsof t Office 2010 Excel Seminar Xander Jackson. Getting Familiar with Microsoft Office 2010: Excel. The Ribbon Laid out with multiple Tabs File, Home, Insert, Page Layout, Formulas, Data, Review, View Each tab has multiple Groups of Functions

236 views • 10 slides

Using Formulas and Functions in Excel 2010

Using Formulas and Functions in Excel 2010. www.ptraining.com. Formulas. Start with an equals sign (=) Operators. The Order of Operations. What is the right answer? =2+3*4 =2+3*4 = 2 + 12 = 14. Parentheses Exponent Multiplication Division Addition Subtraction. Please Excuse My

365 views • 6 slides

Excel Core Objective 5: Applying Formulas and Functions

Excel Core Objective 5: Applying Formulas and Functions. Microsoft Office 2010 Certification Prep: Microsoft Office Specialist 2010 Exam Practice. Story/Walls. Create Formulas. Mouse and Keyboard Methods: Use basic operators Revise formulas. 2. Enforce Precedence. Ribbon Methods:

210 views • 8 slides

Formulas and Functions

Formulas and Functions. An Introduction. Formula Basics. All formulas begin with “=“ Simple/common formulas use: * for multiplication / for division + for addiction - for subtraction. Order of Operations. Parenthesis or brackets

350 views • 17 slides

Excel Using Formulas and Functions

Excel Using Formulas and Functions. Microsoft Office 2010 Fundamentals. Introduction. The real power of a spreadsheet program such as Excel is its ability to perform simple and complex calculations on worksheet data.

678 views • 15 slides

Excel – Chapter 2 Formulas and Functions

Excel – Chapter 2 Formulas and Functions. ISA-B. A djust cell height and width. Open chap2_ho1_payroll.xlsx and save it as chap2_ho1_payroll_solution.xlsx Drag the border between two column headings. Double-click on the border between two column headings ( two row numbers). Wrap Text.

495 views • 16 slides

Excel Tutorial 3: Working with Formulas and Functions

Excel Tutorial 3: Working with Formulas and Functions. Understanding Cell References. To record and analyze data Enter data in cells in a worksheet Reference the cells with data in formulas that perform calculations on that data Types of cell references Relative Absolute Mixed.

437 views • 27 slides

Excel Functions

Excel Functions. Part 1. Introduction. An Excel function is a formula or a procedure that is performed in the Visual Basic environment, outside the Excel spreadsheet. For example, the sum of two variables can be evaluated in an Excel spreadsheet as follows :.

677 views • 36 slides

3.7 Formulas and Functions

3.7 Formulas and Functions. Goal 1: Solving Formulas. Goal 2: Rewrite an equation in function form. Formula – an algebraic equation that relates two or more real-life quantities. Use this given formula to solve the first example: A = lw Example 1: Find a formula for w, in terms of A and l.

273 views • 9 slides

Excel Tutorial 3: Working with Formulas and Functions. What You Will Do In Tutorial 3. Copy formulas Build formulas containing relative, absolute, and mixed references Review function syntax Insert a function with the Insert Function dialog box Search for a function

375 views • 20 slides

Excel Functions and Formulas

Excel Functions and Formulas. Marty Williams Training Specialist. Fundamentals. All formulas start with the equal sign = All functions must have parenthesis Parentheses must match ( ) Separate function arguments by a comma, with no space

944 views • 28 slides

Excel 2010 Formulas and Functions

483 views • 29 slides

Excel Tutorial 3: Working with Formulas and Functions. Objectives. Copy formulas Build formulas containing relative, absolute, and mixed references Review function syntax Insert a function with the Insert Function dialog box Search for a function Type a function directly in a cell.

611 views • 40 slides

Formulas, Ranges, and Functions

Formulas, Ranges, and Functions. Formulas. Formulas perform operations such as addition, multiplication, and comparison on worksheet values. Formulas can refer to other cells on the same worksheet, cells on other worksheets in the same workbook, or even cells on worksheets in other workbooks.

448 views • 40 slides

Excel: Formulas and Functions

Excel: Formulas and Functions. Objectives. Name a cell or range Locate and use Excel’s predefined functions Debug formulas that contain errors Record and run a macro. Referencing Cells. Absolute, relative & mixed Cell Ranges A2:A10 (row) A2:D10 (column) A2:B6 (range)

202 views • 7 slides

Microsoft Excel Tutorial For Beginners-1 | Excel Formulas & Functions | Excel Training | Simplilearn

This presentation on Excel Tutorial for beginners part-1 will help you get started with Excel and explore the features it offers. You will take a tour to understand Excel basics and look at how to write functions, sort, and filter data. You will understand how to import data and split data into multiple columns. You will learn about sumif and countif functions. Finally, you will get to know all about conditional formatting in Excel. Let's begin. This Data Analyst Masteru2019s Program in collaboration with IBM will make you an expert in data analytics. In this Data Analytics course, you'll learn analytics tools and techniques, how to work with SQL databases, the languages of R and Python, how to create data visualizations, and how to apply statistics and predictive analytics in a business environment. Why become Data Analyst? By 2020, the World Economic Forum forecasts that data analysts will be in demand due to increasing data collection and usage. Organizations view data analysis as one of the most crucial future specialties due to the value that can be derived from data. Data is more abundant and accessible than ever in todayu2019s business environment. In fact, 2.5 quintillion bytes of data are created each day. With an ever-increasing skill gap in data analytics, the value of data analysts is continuing to grow, creating a new job and career advancement opportunities. The facts are that professionals who enter the Data Science field will have their pick of jobs and enjoy lucrative salaries. According to an IBM report, data and analytics jobs are predicted to increase by 15 percent to 2.72 million jobs by 2020, with the most significant demand for data analysts in finance, insurance, and information technology. Data analysts earn an average pay of $67,377 in 2019 according to Glassdoor. Who should take up this course? Aspiring professionals of any educational background with an analytical frame of mind are best suited to pursue the Data Analyst Masteru2019s Program, including: 1. IT professionals 2. Banking and finance professionals 3. Marketing managers 4. Sales professionals 5. Supply chain network managers 6. Beginners in the data analytics domain 7. Students in UG/ PG programs ud83dudc49Learn more at: https://bit.ly/2SECA5r

353 views • 27 slides

• Ablebits blog
• Excel formulas

Basic Excel formulas & functions with examples

The tutorial provides a list of Excel basic formulas and functions with examples and links to related in-depth tutorials.

Being primarily designed as a spreadsheet program, Microsoft Excel is extremely powerful and versatile when it comes to calculating numbers or solving math and engineering problems. It enables you to total or average a column of numbers in the blink of an eye. Apart from that, you can compute a compound interest and weighted average, get the optimal budget for your advertising campaign, minimize the shipment costs or make the optimal work schedule for your employees. All this is done by entering formulas in cells.

This tutorial aims to teach you the essentials of Excel functions and show how to use basic formulas in Excel.

The basics of Excel formulas

Before providing the basic Excel formulas list, let's define the key terms just to make sure we are on the same page. So, what do we call an Excel formula and Excel function?

• Function is a predefined formula already available in Excel. Functions perform specific calculations in a particular order based on the specified values, called arguments, or parameters.

For example, instead of specifying each value to be summed like in the above formula, you can use the SUM function to add up a range of cells: =SUM(A2:A4)

Clicking the function's name will turn it into a blue hyperlink, which will open the Help topic for that function.

Tip. You don't necessarily have to type a function name in all caps, Microsoft Excel will automatically capitalize it once you finish typing the formula and press the Enter key to complete it.

10 Excel basic functions you should definitely know

What follows below is a list of 10 simple yet really helpful functions that are a necessary skill for everyone who wishes to turn from an Excel novice to an Excel professional.

The first Excel function you should be familiar with is the one that performs the basic arithmetic operation of addition:

In the syntax of all Excel functions, an argument enclosed in [square brackets] is optional, other arguments are required. Meaning, your Sum formula should include at least 1 number, reference to a cell or a range of cells. For example:

=SUM(B2:B6) - adds up values in cells B2 through B6.

=SUM(B2, B6) - adds up values in cells B2 and B6.

If necessary, you can perform other calculations within a single formula, for example, add up values in cells B2 through B6, and then divide the sum by 5:

=SUM(B2:B6)/5

To sum with conditions, use the SUMIF function: in the 1st argument, you enter the range of cells to be tested against the criteria (A2:A6), in the 2nd argument - the criteria itself (D2), and in the last argument - the cells to sum (B2:B6):

=SUMIF(A2:A6, D2, B2:B6)

Useful resources:

• Excel Sum formula examples - formulas to total a column, rows, only filtered (visible) cells, or sum across sheets.
• Excel AutoSum - the fastest way to sum a column or row of numbers.
• SUMIF in Excel - formula examples to conditionally sum cells.
• SUMIFS in Excel - formula examples to sum cells based on multiple criteria.

The Excel AVERAGE function does exactly what its name suggests, i.e. finds an average, or arithmetic mean, of numbers. Its syntax is similar to SUM's:

Having a closer look at the formula from the previous section ( =SUM(B2:B6)/5 ), what does it actually do? Sums values in cells B2 through B6, and then divides the result by 5. And what do you call adding up a group of numbers and then dividing the sum by the count of those numbers? Yep, an average!

The Excel AVERAGE function performs these calculations behind the scenes. So, instead of dividing sum by count, you can simply put this formula in a cell:

=AVERAGE(B2:B6)

To average cells based on condition, use the following AVERAGEIF formula, where A2:A6 is the criteria range, D3 is he criteria, and B2:B6 are the cells to average:

• Excel AVERAGE - average cells with numbers.
• Excel AVERAGEA - find an average of cells with any data (numbers, Boolean and text values).
• Excel AVERAGEIF - average cells based on one criterion.
• Excel AVERAGEIFS - average cells based on multiple criteria.
• How to calculate weighted average in Excel
• How to find moving average in Excel

MAX & MIN

The MAX and MIN formulas in Excel get the largest and smallest value in a set of numbers, respectively. For our sample data set, the formulas will be as simple as:

=MAX(B2:B6)

• MAX function - find the highest value.
• MAX IF formula - get the highest number with conditions.
• MAXIFS function - get the largest value based on multiple criteria.
• MIN function - return the smallest value in a data set.
• MINIFS function - find the smallest number based on one or several conditions.

COUNT & COUNTA

If you are curious to know how many cells in a given range contain numeric values (numbers or dates), don't waste your time counting them by hand. The Excel COUNT function will bring you the count in a heartbeat:

While the COUNT function deals only with those cells that contain numbers, the COUNTA function counts all cells that are not blank , whether they contain numbers, dates, times, text, logical values of TRUE and FALSE, errors or empty text strings (""):

For example, to find out how many cells in column B contain numbers, use this formula:

=COUNT(B:B)

To count all non-empty cells in column B, go with this one:

=COUNTA(B:B)

In both formulas, you use the so-called "whole column reference" (B:B) that refers to all the cells within column B.

• Excel COUNT function - a quick way to count cells with numbers.
• Excel COUNTA function - count cells with any values (non-empty cells).
• Excel COUNTIF function - count cells that meet one condition.
• Excel COUNTIFS function - count cells with several criteria.

Judging by the number of IF-related comments on our blog, it's the most popular function in Excel. In simple terms, you use an IF formula to ask Excel to test a certain condition and return one value or perform one calculation if the condition is met, and another value or calculation if the condition is not met:

For example, the following IF statement checks if the order is completed (i.e. there is a value in column C) or not. To test if a cell is not blank, you use the "not equal to" operator ( <>) in combination with an empty string (""). As the result, if cell C2 is not empty, the formula returns "Yes", otherwise "No":

• IF function in Excel with formula examples
• How to use nested IFs in Excel
• IF formulas with multiple AND/OR conditions

If your obviously correct Excel formulas return just a bunch of errors, one of the first things to check is extra spaces in the referenced cells (You may be surprised to know how many leading, trailing and in-between spaces lurk unnoticed in your sheets just until something goes wrong!).

There are several ways to remove unwanted spaces in Excel, with the TRIM function being the easiest one:

For example, to trim extra spaces in column A, enter the following formula in cell A1, and then copy it down the column:

• Excel TRIM function with formula examples
• How to delete line breaks and non-printing characters
• How to remove non-breaking spaces (&nbsp;)
• How to delete a specific non-printing character

Whenever you want to know the number of characters in a certain cell, LEN is the function to use:

Wish to find out how many characters are in cell A2? Just type the below formula into another cell:

Want to get the total count of characters in a range or cells or count only specific characters? Please check out the following resources.

• Excel LEN formulas to count characters in a cell
• Count the number of characters in cells and ranges

AND & OR

These are the two most popular logical functions to check multiple criteria. The difference is how they do this:

• AND returns TRUE if all conditions are met, FALSE otherwise.
• OR returns TRUE if any condition is met, FALSE otherwise.

While rarely used on their own, these functions come in very handy as part of bigger formulas.

For example, to check the test results in columns B and C and return "Pass" if both are greater than 60, "Fail" otherwise, use the following IF formula with an embedded AND statement:

=IF(AND(B2>60, B2>60), "Pass", "Fail")

If it's sufficient to have just one test score greater than 60 (either test 1 or test 2), embed the OR statement:

• Excel AND function with formula examples
• Excel OR function with formula examples

CONCATENATE

In case you want to take values from two or more cells and combine them into one cell, use the concatenate operator (&) or the CONCATENATE function:

For example, to combine the values from cells A2 and B2, just enter the following formula in a different cell:

=CONCATENATE(A2, B2)

To separate the combined values with a space, type the space character (" ") in the arguments list:

• How to concatenate in Excel - formula examples to combine text strings, cells and columns.
• CONCAT function - newer and improved function to combine the contents of multiple cells into one cell.

TODAY & NOW

To see the current date and time whenever you open your worksheet without having to manually update it on a daily basis, use either:

=TODAY() to insert the today's date in a cell.

=NOW() to insert the current date and time in a cell.

• How to insert today's date in Excel - different ways to enter the current date in Excel: as an unchangeable time stamp or automatically updatable date and time.
• Excel date functions with formula examples - formulas to convert date to text and vice versa, extract a day, month or year from a date, calculate the difference between two dates, and a lot more.

Best practices for writing Excel formulas

Now that you are familiar with the basic Excel formulas, these tips will give you some guidance on how to use them most effectively and avoid common formula errors.

Do not enclose numbers in double quotes

Any text included in your Excel formulas should be enclosed in "quotation marks". However, you should never do that to numbers, unless you want Excel to treat them as text values.

For example, to check the value in cell B2 and return 1 for "Passed", 0 otherwise, you put the following formula, say, in C2:

=IF(B2="pass", 1, 0)

Copy the formula down to other cells and you will have a column of 1's and 0's that can be calculated without a hitch.

Now, see what happens if you double quote the numbers:

=IF(B2="pass", "1", "0")

Don't format numbers in Excel formulas

Match all opening and closing parentheses, copy the same formula to other cells instead of re-typing it.

Note. After copying the formula, make sure that all cell references are correct. Cell references may change depending on whether they are absolute (do not change) or relative (change).

How to delete formula, but keep calculated value

When you remove a formula by pressing the Delete key, a calculated value is also deleted. However, you can delete only the formula and keep the resulting value in the cell. Here's how:

• Select all cells with your formulas.
• Press Ctrl + C to copy the selected cells.
• Right-click the selection, and then click Paste Values > Values to paste the calculated values back to the selected cells. Or, press the Paste Special shortcut: Shift+F10 and then V .

Make sure Calculation Options are set to Automatic

If all of a sudden your Excel formulas have stopped recalculating automatically, most likely the Calculation Options somehow switched to Manual . To fix this, go to the Formulas tab > Calculation group, click the Calculation Options button, and select Automatic .

This is how you make and manage basic formulas in Excel. I how you will find this information helpful. Anyway, I thank you for reading and hope to see you on our blog next week.

You may also be interested in

• How to make formulas in Excel
• Microsoft Excel formulas with examples
• Excel formulas not working, not updating, not calculating

Table of contents

409 comments

I need the Excel formula number combination

One hundred and ten units (HTF) : 345,345,678,890,567

Summary of analysis results from 5 data If there is no incorrect information 036-037-046-047-056-057-058-158-258-358 368-369-378-379-458-468-469-478-479-558 568-569-578-579-588-589 (26 sets / total 26 sets)

Sorry, I have no idea exactly what the task is. It's hard to tell exactly what you're asking for as it's currently written.

For me personally It's clear that actually "seeing" something written down is easier to understand and remember than "hearing" it, but both together is even better.

Post a comment

Excel functions (by category)

Worksheet functions are categorized by their functionality. Click a category to browse its functions. Or press Ctrl+F to find a function by typing the first few letters or a descriptive word. To get detailed information about a function, click its name in the first column.

Our 10 most popular functions

Here are the 10 functions that people read about most.

Compatibility functions

In newer versions of Excel, these functions were replaced with new functions that provide improved accuracy and have names that better reflect their usage. You can still use them for compatibility with earlier versions of Excel, but if backward compatibility isn't required, you should start using the new functions instead. For more information about the new functions, see Statistical functions (reference) and Math and trigonometry functions (reference) .

Cube functions

Database functions, date and time functions, engineering functions, financial functions, information functions, logical functions, lookup and reference functions, math and trigonometry functions, statistical functions, text functions, user defined functions that are installed with add-ins.

If add-ins that you install contain functions, these add-in or automation functions will be available in the User Defined category in the Insert Function dialog box.

User-defined functions (UDFs) are not available in Excel for the web.

Web functions

Web functions are not available in Excel for the web.

Version markers indicate the version of Excel a function was introduced. These functions aren't available in earlier versions.

Important:  The calculated results of formulas and some Excel worksheet functions may differ slightly between a Windows PC using x86 or x86-64 architecture and a Windows RT PC using ARM architecture. Learn more about the differences .

Top of Page

Need more help?

You can always ask an expert in the Excel Tech Community  or get support in  Communities .

Excel functions (alphabetical)

Overview of formulas in Excel

How to avoid broken formulas

Detect errors in formulas

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

Excel formulas and functions

Most people never receive proper Excel training and spend years frustrated by the simplest tasks, especially tasks that involve formulas. To use Excel with confidence, you must have a good understanding of formulas and functions. This article introduces the basic concepts you need to know to be proficient with formulas in Excel.

Formulas and functions are the bread and butter of Excel. They drive almost everything interesting and useful you will ever do in a spreadsheet. This article introduces the basic concepts you need to know to be proficient with formulas in Excel. More examples here .

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 equals sign (=).

Cell references

In the examples above, values are "hardcoded". That means results won't change unless you edit the formula again and change a value manually. Generally, this is considered bad form, because it hides information and makes it harder to maintain a spreadsheet.

Instead, use cell references so values can be changed at any time. In the screen below, C1 contains the following formula:

Notice because 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 when the result is an error. Below a formula is used to calculate 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 of handling errors. In this case, you could provide the missing value in B4, or "catch" the error with the IFERROR function and display a more friendly message (or nothing at all).

Copy and paste formulas

The beauty of cell references is that they automatically update when a formula is copied to a new location. This means you don't need to enter the same basic formula again and again. In the screen below, the formula in E1 has been copied to the clipboard with Control + C:

Below: formula pasted to cell E2 with Control + V. Notice cell references have changed:

Below is the formula pasted to E3. Cell addresses are updated again:

Relative and absolute references

The cell references above are called relative references. This means the reference is relative to the cell it lives in.  The formula in E1 above is:

Literally, this means "cell 3 columns left "+ "cell 2 columns left" + "cell 1 column left". That's why, when the formula is copied down to cell E2, it continues to work in the same way.

Relative references are extremely useful, but there are times when you don't want a cell reference to change. A cell reference that won't change when copied is called an absolute reference .  To make a reference absolute, use the dollar symbol ($):

For example, in the screen below, we want to multiply each value in column D by 10, which is entered in A1. By using an absolute reference for A1, we "lock" that reference so it won't change when the formula is copied to E2 and E3:

Here are the final formulas in E1, E2, and E3:

Notice the reference to D1 updates when the formula is copied, but the reference to A1 never changes. Now we can easily change the value in A1, and all three formulas recalculate. Below the value in A1 has changed from 10 to 12:

This simple example also shows why it doesn't make sense to hardcode values into a formula. By storing the value in A1 in one place, and referring to A1 with an absolute reference , the value can be changed at any time and all associated formulas will update instantly.

Tip: you can toggle between relative and absolute syntax with the  F4 key .

How to enter a formula

To enter a formula:

• Select a cell
• Enter an equals sign (=)
• Type the formula, and press enter.

Instead of typing cell references, you can point and click, as seen below. Note references are color-coded:

All formulas in Excel must begin with an equals sign (=). No equals sign, no formula:

How to change a formula

To edit a formula, you have 3 options:

• Select the cell, edit in the formula bar
• Double-click the cell, edit directly
• Select the cell, press F2 , and edit directly

No matter which option you use, press Enter to confirm changes when done. If you want to cancel, and leave the formula unchanged, click the Escape key.

Video: 20 tips for entering formulas

What is a function?

Working in Excel, you will hear the words "formula" and "function" used frequently, sometimes interchangeably. They are closely related, but not exactly the same. Technically, a formula is any  expression that begins with an equals sign (=).

A function, on the other hand, is a formula with a special name and purpose. In most cases, functions have names that reflect their intended use. For example, you probably know the SUM function already, which returns the sum of given references:

The AVERAGE function , as you would expect, returns the average of given references:

The MIN and MAX functions return minimum and maximum values, respectively:

Excel contains hundreds of specific functions . To get started, see  101 Key Excel functions .

Function arguments

Most functions require inputs to return a result. These inputs are called "arguments". A function's arguments appear after the function name, inside parentheses, separated by commas.  All have a name, and an opening and closing parentheses (). The inputs inside the parentheses are called "arguments".The pattern looks like this:

For example, the COUNTIF function counts cells that meet criteria, and takes two arguments, range and criteria :

In the screen below, range is A1:A5 and criteria is "red". The formula in C1 is:

Video: How to use the COUNTIF function

Not all arguments are required. Arguments shown 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 takes 3 arguments:

Start_date and end_date are required arguments, basis is an optional argument. See below for an example of how to use YEARFRAC to calculate current age based on birthdate.

How to enter a function

If you know the name of the function, just start typing. Here are the steps:

1. Enter an equals sign (=) and start typing. Excel will make a list of matching functions based as you type:

When you see the function you want in the list, use the arrow keys to select (or just keep typing).

2. Type the Tab key to accept a function. Excel will complete the function:

3. Fill in required arguments:

4. Press Enter to confirm formula:

Combining functions (nesting)

Many Excel formulas use more than one function, and functions can be " nested " inside each other. For example, below we have a birthdate in B1 and we want to calculate the current age in B2:

The YEARFRAC function will calculate years with a start date and end date:

We can use B1 for the start date, and then use the TODAY function to supply the end date:

When we press Enter to confirm, we get current age based on today's date:

Notice we are using the TODAY function to feed 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 chop off 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.

• The screens above were created on February 22, 2019.
• Nested IF functions are a classic example of nesting functions . 
• The TODAY function is a rare Excel function with no required arguments.

Key takeaway: The output of any formula or function can be fed directly into another formula or function. 

Math Operators

The table below shows the standard math operators available in Excel:

• Logical operators

Logical operators provide support for comparisons such as "greater than", "less than", etc. The logical operators available in Excel are shown in the table below:

Video: How to build logical formulas

Order of operations

When solving a formula, Excel follows a sequence called "order of operations". First, any expressions in parentheses are evaluated. Next Excel will solve for any exponents. After exponents, Excel will perform multiplication and division, then addition and subtraction. If the formula involves concatenation , this will happen after standard math operations. Finally, Excel will evaluate logical operators , if present.

• Parentheses
• Multiplication and Division
• Addition and Subtraction
• Concatenation

Tip: you can use the Evaluate feature to watch Excel solve formulas step-by-step.

Convert formulas to values

Sometimes you want to get rid of formulas and leave only values in their place. The easiest way to do this in Excel is to copy the formula, then paste, using Paste Special > Values. This overwrites the formulas with the values they return. You can use a keyboard shortcut for pasting values, or use the Paste menu on the Home tab on the ribbon.

Video: Paste Special Shortcuts

What's next?

Below are guides to help you learn more about Excel's 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 important Excel functions
• Guide to all Excel functions (work in progress)
• Excel formula errors (examples and fixes)
• Formula criteria - 50 examples
• Formulas for conditional formatting
• How to use F9 to debug a formula (video)
• Excel formula errors and fixes (video)

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Get Training

Quick, clean, and to the point training.

Learn Excel with high quality video training. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Each video comes with its own practice worksheet.

Help us improve Exceljet

Your email address is private and not shared.

15 Most Common Excel Functions You Must Know + How to Use Them

Microsoft Excel is one of the most well-known computer applications. It has changed the way people and companies work with data.

Thus, learning Excel can help with both your career and your personal needs.

Excel runs using functions and there are roughly 500 of them! These range from basic arithmetic to complex statistics.

If you’re a new Excel user, this sheer quantity can be quite daunting.

So we are here to help you! 🤝

We have rounded up 15 of the most common and useful Excel functions that you need to learn. We also prepared a practice workbook for you to follow along with the examples. Download it here .

Let’s get started!

Table of Contents

What are Excel functions?

Basic Math Functions

Logical Functions

Text Functions

Lookup and Reference Functions

Excel is used to calculate and manipulate numbers and text. To do this, you use formulas !

Formulas are expressions that tell Excel what you want to do with the data. They begin with the equal symbol (=) followed by a combination of operators and functions .

What are operators?

These are symbols that specify the type of calculation you want to perform on the elements of a formula.

For example, to add two numbers, you can type “=1+1” into a cell. Once you hit Enter , Excel will run the formula and return the result which is 2.

Here are some examples of common operators:

Excel automatically treats cell contents that start with (=) as formulas. This also applies when you begin a cell with the plus (+) or minus (-) symbols.

You can bypass this by adding a leading apostrophe (‘) . This is how you can show formulas as text like in the table above.

Order of operation and using parentheses in Excel formulas

Generally, Excel follows PEMDAS when calculating formulas. PEMDAS means parentheses first, then exponents, then multiplication and division, then addition and subtraction .

What are functions?

These are predefined processes in Excel. Each function in Excel has a unique name and specific input(s) . The function takes these inputs and performs the corresponding calculation.

The inputs or arguments of an Excel function are always enclosed in parentheses.

For example, this is the syntax for the MAX function:

=MAX(number1, [number2], …)

The list of numbers where you want to find the maximum value is placed inside the parentheses.

Using a cell or a range as input

As you learn more about Excel, you’ll find that Excel formulas rarely consist of individual numbers only like in the formula “=1+1”.

Thus, referencing cells is important in Excel and you can learn more by clicking here .

Alright! You’ve just learned how a function in Excel works.

Let’s dive right into the list! 🤿

We will start with basic Excel functions and then move on to more advanced functions.

Basic Math Functions (Beginner Level ★☆☆)

This is the first function in Excel that most new users need. As the name implies, the SUM function adds up all the values in a specified group of cells or range.

Syntax: =SUM(number1, [number2], …)

Try it out in the practice workbook.

If you want to get the total quiz score for each student, you can use the SUM function. In this case, the input range will be all four quiz scores for each student.

1. Type this formula into cell F2 :

=SUM(B2:E2)

You can also type “=SUM(B2,C2,D2,E2)” but “=SUM(B2:E2)” is much simpler.

2. Press Enter . Excel then evaluates the formula and the cell returns the number for the total which is 360.

3. Copy this for the rest of the students or drag down the fill handle .

Notice that the SUM function ignores the cells containing text . (“X” meaning the student was unable to take the quiz)

Most of the basic math functions in Excel ignore non-numeric values such as text, date, and time.

Next up is the COUNT function . It returns the number of cells containing numeric values within the input range.

Syntax : =COUNT(value1, [value2], …)

1. To get the number of quizzes taken by each student, use this formula in cell G2 :

=COUNT(B2:E2)

2. Hit Enter and fill in the rows below.

If you would like to include non-numeric values in the count, you can use the COUNTA function. To count the number of blank cells, you can use the COUNTBLANK function.

Learn more about the COUNT function and its variants here .

The average of a list of numbers is just the total divided by how many numbers there are in that list.

This is easy enough to calculate the quiz scores. You already have the SUM and the COUNT of quizzes for each student.

But, it gets even easier using the AVERAGE function in Excel.

Syntax : =AVERAGE (value1, [value2], …)

1. Type this into cell H2 :

=AVERAGE(B2:E2)

The AVERAGE function also has variants for more advanced calculations. Click here to learn more .

Logical Functions (Intermediate Level – ★★☆)

Let’s raise the difficulty level a little bit.

A logical function in Excel allows you to make comparisons and use the results to change how a formula calculates.

The IF function is a very popular function in Excel and it is actually quite easy to learn .

Syntax : =IF(logical_test, value_if_true, [value_if_false])

This function checks if a logical test is either TRUE or FALSE. It then returns the specified value based on the result.

Using the average score of each student, try to assign PASS or FAIL grades. Assume that the passing score for this class is 60.

1. Begin the formula in cell C2 with “=IF(“

The logical_test is to check if the average score in Column B is greater than or equal to (>=) the passing score of 60.

2. So, the formula becomes:

=IF(B2>=60,

If the comparison returns TRUE , then the formula should return the text “PASS”. Thus, the value_if_true argument should be “PASS”.

And if it returns FALSE , then the value_if_false argument should be “FAIL”.

3. Thus, the formula becomes:

=IF(B2>=60,”PASS”,”FAIL”)

4. Hit Enter and fill in the rows below.

What if you needed to assign grades according to a scale instead of just “PASS” and “FAIL”?

For that, you have to use multiple criteria or logical tests. While this is possible using nested IF functions , it can get messy very quickly. Instead, you can use the IFS function .

5. IFS function

The IFS function was introduced in Excel 2016 to replace nested IF functions .

This function works by evaluating the first logical test or criteria . It returns the corresponding value if it is TRUE. But if it is FALSE, the function proceeds to evaluate the second criteria , and so on.

📖 In other words, the IFS function outputs the value that corresponds to the first specified criteria that is true .

Syntax : =IFS(logical_test1, value_if_true1, [logical_test2], [value_if_true2],..)

1. First, the formula should check if the average score (column B) is above or equal to 90. If yes, it should return “A” .

=IFS(B2>=90,”A”,

2. If not, it should then check if the average score is greater than or equal to 80. If yes, it should return “B” . If you do this up to grade D, the formula becomes:

=IFS(B2>=90,”A”,B2>=80,”B”,B2>=70,”C”,B2>=60,”D”,

3. For the last grade “F”, put “TRUE” for the logical test.

The IFS function will only evaluate the last specified criteria if all of the previous logical values were FALSE. Thus, you can set the last criteria to always be TRUE thus making it a “ catch all ” statement.

The final formula is then:

=IFS(B2>=90,”A”,B2>=80,”B”,B2>=70,”C”,B2>=60,”D”,TRUE,”F”)

You can use absolute cell references and a reference table when working with long formulas.

That way, you don’t have to revisit all of the arguments in the formula if you need to change some values.

For example, using the table and formula shown below, you can easily change the grading scale in use.

=IFS(B2>=$H$2,$F$2,B2>=$H$3,$F$3,B2> =$H$4,$F$4,B2>=$H$5,$F$5,TRUE,$F$6)

Text Functions (Intermediate Level – ★★☆)

In this next section, you will see how Excel can also be used to manipulate text.

In the “Class List” worksheet of the practice workbook, the full name of each student is listed in Column A . Your goal is to rearrange these from “first name last name” to “last name_first name” in Column F .

To do this, you first have to extract the first name and the last name from Column A.

The names are separated by a space character ” “ . So, you have to identify the position of the space within each text string in Column A.

The FIND function in Excel returns the number or position of a specified character or substring within another text string.

Syntax : =FIND(find_text, within_text, [start_num])

To get the position of the space ” “ , type this formula:

=FIND(” “,A2)

Next, take a look at the LEN function .

This function returns the number of characters in a text string.

Syntax : =LEN(text)

To get the number of characters in each student’s name:

Now you can move on to extracting the first and last name using the MID function in Excel.

This function extracts a given number of characters from the middle of a text string.

Syntax : = MID(text, start_num, num_chars)

It is one of three text functions that are used to extract text. The other two are LEFT and RIGHT which extract text from the start and end of a text string respectively.

The first name starts at the very first character of the text string. So, you extract starting from position 1 . Then the length of the first name is given by the position of the space character minus 1 .

So, the formula to extract the first name or first word from a text string is:

=MID(A2,1,B2-1)

Or, you can express it directly using the FIND formula earlier.

=MID(A2,1,FIND(” “,A2)-1)

For the last name , you can extract it starting from the position of the space character plus 1 . Its length is just the length of the entire text string minus the position of the space character .

=MID(A2,B2+1,C2-B2)

Or, using the FIND and LEN formulas earlier:

=MID(A2,FIND(” “,A2)+1,LEN(A2)-FIND(” “,A2))

Now you can combine the last name and the first name in the desired order using the CONCAT function.

Like IFS , CONCAT is another newly introduced function in Excel 2016. It replaced the old CONCATENATE function .

Syntax : =CONCAT(text1, [text2],…)

Combine the last name and the first name with a comma and space character “, ” in between.

=CONCAT(E2,”, “,D2)

In the above example, you used helper columns for FIND , LEN , and MID to help build the final formula and visualize how it works.

In real-world applications, you can use a single long formula to get the results like this:

=CONCAT(MID(A2,FIND(” “,A2)+1,LEN(A2) -FIND(” “,A2)),”, “,MID(A2,1,FIND(” “,A2)-1))

Lookup and Reference Functions (Advanced Level – ★★★)

In this final section, we will focus on functions that allow you to look for specific data points and refer to them.

Take a look at the “Schedule” worksheet .

The COLUMN function in Excel returns the column number of a given cell.

Syntax : =COLUMN([reference])

Let’s try to assign specific dates for each quiz. For example, you may want the quizzes to be held every Monday. This means that the first quiz date should be offset by 1 week or 7 days for each succeeding quiz date.

You can use the column number to multiply the 7 days offset for each week like this:

=$B$2+(COLUMN()-2)*7

Two (2) is subtracted from the column number so that the sequence starts at 1.

You can also get this result using the much simpler “=B2+7” since you are only adding a fixed number of days to each date. 🤔

But, using the COLUMN function , you can create complex patterns.

Take this pattern for example:

The quizzes are still held every Monday. But every third week, they are held on Wednesday instead.

Here is the formula for this pattern:

=$B$2+(COLUMN()-2)*7+IF(MOD(COLUMN()-1,3)=0,2,0)

The MOD function in Excel returns the remainder after a number is divided by a given divisor. It’s part of the Math & Trig group of functions. This group includes other fun functions such as ABS which returns the absolute value of a number and ROUND which rounds a number to a specified number of digits. Learn more about the function groups towards the end of this article!

Next, take a look at the ROW function . It works exactly like COLUMN but it returns the row number instead.

Syntax : =ROW([reference])

In this next example, you will assign the seating plans. You can try different seating arrangements using the ROW function .

Assume R1C1 is the seat closest to the teacher’s desk.

1. You can have the students seated one seat after another and in two columns:

=CONCAT(“R”,MOD(ROW()-6,3)*2+1,”C”,INT((ROW()-6)/3)*2+1)

2. Or they can sit in rows of 3 and columns of 2

=CONCAT(“R”,MOD(ROW()-6,2)*2+1,”C”,INT((ROW()-6)/2)*2+1)

3. You can also sit them in the farthest rows:

=CONCAT(“R”,MOD(ROW()-6,2)*4+1,”C”,INT((ROW()-6)/2)*2+1)

4. Or in the farthest columns:

=CONCAT(“R”,MOD(ROW()-6,3)*2+1,”C”,INT((ROW()-6)/3)*4+1)

Manually creating seating patterns for small sets like this one is easy. But a formula like those shown above definitely helps especially for larger sets like 50, 100, or even more.

The COLUMN and ROW functions are rarely used on their own. Like IF and IFS , you use them with other functions to change how the formula is calculated.

Now, open up the “Lookup” worksheet .

In the next few examples, you will create a search feature that allows students to look up their names. They can then see their scores from past quizzes and their assigned seats for the next quizzes.

To start, you will use the MATCH function . It searches for a specified item within a given range of cells. It then returns the relative position of the first match.

Syntax : =MATCH(lookup_value, lookup_array, [match_type])

• The lookup_value is the item you want to search for. So, set this to cell B2 .
• The lookup_array is the range or table array where you want to search. Use F2:F7 from the “Class List” worksheet .
• For the match_type , set this to zero so that the function searches for an exact match . (Learn more about MATCH and the different match types in this article )

The formula then becomes:

=MATCH(B2,’Class List’!F2:F7,0)

However, it only works correctly if the name is entered exactly as it is written in Column F of the Class List .

To fix this, you can use the asterisk “*” wildcard character so that searching for either first or last name works.

You can also enclose the formula in an IFNA function . This way, if the formula cannot find the given name in the table, it will return a phrase like “No result found”.

The INDEX function retrieves a value from a given table array based on the provided row and column numbers.

Syntax : =INDEX (array, row_num, [col_num])

Similar to the MATCH example, you need to specify where the range or array lookup is.

For row_num , you can use the earlier MATCH result at Cell B5 . Then for col_num , use 1 for the First Name :

=INDEX(‘Class List’!D2:E7,B5,1)

And set col_num to 2 for the Last Name .

=INDEX(‘Class List’!D2:E7,B5,2)

Just like that, you have a working search 🔍 formula!

This is just a small example of the countless possibilities using the INDEX and MATCH combination . Click here for more examples !

14. VLOOKUP

The VLOOKUP function in Excel works similarly to the INDEX and MATCH combination. It is faster to set up but it is less versatile. VLOOKUP also only works if your lookup array is at the leftmost of the reference table.

Syntax : =VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

This time, you will use the First Name result (cell B6) as the lookup_value . Use this and VLOOKUP to retrieve the given student’s scores from the “Quiz Scores” worksheet .

=VLOOKUP($B$6,’Quiz Scores’!$A$2:$E$7,COLUMN(),FALSE)

For the seat assignment, use the Last Name result followed by the asterisk wild character.

=VLOOKUP($B$7&”*”,Schedule!$A$6:$E$11,COLUMN(),FALSE)

15. INDIRECT

The last function that you will be learning about today is also one of the most powerful in Excel.

INDIRECT allows you to specify cell references using text strings.

SYNTAX : =INDIRECT(ref_text, [a1])

For example, instead of typing “=A1” , you can type “=INDIRECT(“A”&1) . This means you can dynamically change references.

Let’s take the INDEX & MATCH formula you used to retrieve the Last Name. You can get the same result using this formula:

=INDIRECT(“‘Class List’!”&”E”&(B5+1))

The INDIRECT function opens up so many possibilities with dynamic references in Excel. I highly this article for an in-depth tutorial on INDIRECT .

That’s it – Now what?

As you have just learned, Excel offers so many different functions to choose from. Luckily, Excel has brought them all together in the Formulas tab .

You can look for an Excel function using search keywords or you can also select from the categorical dropdowns.

For example, click on the Financial group to find functions that can help you calculate items like net present value, future value, cumulative interest paid, cumulative principal paid, etc.

You can also click on More Functions which opens up even more possibilities for advanced Excel formulas.

For example, the Statistical group is useful if you need to calculate a statistical value. This includes functions for maximum value, minimum value, forecast value, gamma function value, etc. You can insert a cumulative distribution function and other useful tools for data analysis .

Learn how to use these formulas and more by signing up for my free online Excel course .

We will help you make the most out of your Excel experience! 📈

Other relevant resources

If you enjoyed this article, you can visit my YouTube channel for more in-depth tutorials and other fun stuff!

Did you know that the Flash Fill feature can help speed up your work by automatically filling a repetitive pattern Excel detects from your data? Learn more here .

Thanks for reading! 😄

List Your Business in Our Directory Now!  →

• Business Directory

Excel Formulas and Functions

Excel countif function.

Learn how to master the Microsoft Excel COUNTIF function to easily count cells that meet a specific condition, improving your data analysis skills.

Excel COUNTA Function

“Learn how to use the Microsoft Excel COUNTA Function to efficiently count cells with non-empty values in your spreadsheet.”

Excel VAR.P Function

Learn how to use the Microsoft Excel VAR.P function for calculating population variance, a key tool for statistical analysis in your spreadsheets.

Excel MODE.MULT Function

Learn how to use the Microsoft Excel MODE.MULT function to find the most frequently occurring values in your data set efficiently.

Excel MEDIAN Function

Learn how to use the Microsoft Excel MEDIAN Function to easily find the middle value in a data set, enhancing your data analysis skills.

Excel QUARTILE.EXC Function

“Learn how to expertly use the Microsoft Excel QUARTILE.EXC function to analyze data sets by dividing them into quartiles. Perfect for statistical analysis.”

Excel CORREL Function

Discover how the Microsoft Excel CORREL Function can help you find the correlation between datasets, simplifying your data analysis process.

Excel KURT Function

Explore the powerful Microsoft Excel KURT function to measure the kurtosis of a dataset and enhance your data analysis skills.

Excel SKEW Function

Learn how the Microsoft Excel SKEW function helps in measuring asymmetry in data distribution, making your data analysis more accurate and insightful.

Excel QUARTILE.INC Function

Learn how to use the Microsoft Excel QUARTILE.INC function to easily calculate quartiles in your data sets, enhancing your data analysis skills.

Excel PERCENTILE.INC Function

Discover how to master the Microsoft Excel PERCENTILE.INC function to calculate statistical data with precision and ease in our latest blog post.

Excel PERCENTILE.EXC Function

Discover how to master the Microsoft Excel PERCENTILE.EXC function to calculate exclusive percentiles in large datasets efficiently.

Excel T.TEST Function

Explore the capabilities of the Microsoft Excel T.TEST Function, a powerful tool for statistical analysis and hypothesis testing in your spreadsheets.

Excel NORM.DIST Function

Discover the power of the Microsoft Excel NORM.DIST function in statistical analysis, making complex calculations straightforward and efficient.

Excel COUNT Function

Learn how to master the Microsoft Excel COUNT function, a key tool for efficiently managing numerical data in your spreadsheets.

Featured Companies

Learn powerpoint.

Explore the world of Microsoft PowerPoint with LearnPowerpoint.io, where we provide tailored tutorials and valuable tips to transform your presentation skills and clarify PowerPoint for enthusiasts and professionals alike.

Your ultimate guide to mastering Microsoft Word! Dive into our extensive collection of tutorials and tips designed to make Word simple and effective for users of all skill levels.

Resultris Marketing

Boost your brand's online presence with Resultris Content Marketing Subscriptions. Enjoy high-quality, on-demand content marketing services to grow your business.

Other Categories

Basic excel operations, excel add-ins, excel and other software, excel basics and general knowledge, excel cell references and ranges, excel charts and graphs, excel data analysis, excel data manipulation and transformation, excel data validation and conditional formatting, excel date and time functions, excel errors, excel file management, excel formatting and visual adjustments, excel integration and conversion, excel linking and merging, excel macros and vba, excel printing, excel settings, excel tips and shortcuts, excel training, excel versions, form controls and user interaction, pivot tables, working with text, learn excel: categories..

Explore the vast landscape of Excel with our carefully curated categories. From learning basic Excel operations, harnessing the power of add-ins, to becoming a pro in data analysis and manipulation, our blog posts cover every aspect of this versatile tool.

An introduction to Excel's fundamental features.

Enhance Excel's functionality with add-ins.

Learn how Excel interacts with other software.

Back to basics with Excel's core functionality.

Understand and work with Excel cell references and ranges.

Visualize data using charts and graphs in Excel.

Analyze your data in Excel.

Manipulate and transform data in Excel.

Maintain data integrity and improve readability in Excel.

Work with dates and times in Excel.

Handle and troubleshoot errors in Excel.

Manage your Excel files effectively.

Enhance your worksheets with Excel's formatting tools.

Unlock the power of Excel with formulas and functions.

Integrate Excel with other platforms and convert files.

Link and merge data across Excel.

Automate your tasks in Excel with macros and VBA.

Prepare and print your Excel worksheets.

Customize Excel according to your preference.

Quick tips and shortcuts for efficient Excel use.

Courses and resources for all levels of Excel proficiency.

Understand differences across various Excel versions.

Create interactive worksheets with form controls.

Step-by-step guides for specific Excel tasks.

Techniques for summarizing and analyzing data.

Manage and manipulate text data in Excel.

Table of Contents

What is excel formula , formulas in excel: an overview, excel formulas and functions, choose the right program, top 30 excel formulas and functions you should know.

Microsoft Excel is the go-to tool for working with data. There are probably a handful of people who haven’t used Excel, given its immense popularity. Excel is a widely used software application in industries today, built to generate reports and business insights. Excel supports several in-built applications that make it easier to use. 

One such feature that allows Excel to stand out is - Excel sheet formulas. Here, we will look into the top 25 Excel formulas that one must know while working on Excel. The topics that we will be covering  in this article are as follows:

• What is Excel Formula?

In Microsoft Excel, a formula is an expression that operates on values in a range of cells. These formulas return a result, even when it is an error. Excel formulas enable you to perform calculations such as addition, subtraction, multiplication, and division. In addition to these, you can find out averages and calculate percentages in excel for a range of cells, manipulate date and time values, and do a lot more.

Become an AI-powered Business Analyst

• Choose a cell.
• To enter an equal sign, click the cell and type =.
• Enter the address of a cell in the selected cell or select a cell from the list.
• You need to enter an operator.
• Enter the address of the next cell in the selected cell.
• Press Enter.

There is another term that is very familiar to Excel formulas, and that is " function ". The two words, "formulas" and "functions" are sometimes interchangeable. They are closely related, but yet different. A formula begins with an equal sign. Meanwhile, functions are used to perform complex calculations that cannot be done manually. Functions in excel have names that reflect their intended use.

The example below shows how we have used the multiplication formula manually with the ‘*’ operator.

Sample Formula: "=A2*B2" 

Fig: Microsoft Excel Formula

This example below shows how we have used the function - ‘PRODUCT’ to perform multiplication. As you can see, we didn’t use the mathematical operator here. 

Sample Formula: "=PRODUCT(A2,B2)" 

Fig: Microsoft Excel Function

Excel formulas and functions help you perform your tasks efficiently, and it's time-saving. Let's proceed and learn the different types of functions available in Excel and use relevant formulas as and when required.

Become The Highest-Paid Business Analysis Expert

Build your career in Data Analytics with our Data Analyst Master's Program! Cover core topics and important concepts to help you get started the right way!

There are plenty of Excel formulas and functions depending on what kind of operation you want to perform on the dataset. We will look into the formulas and functions on mathematical operations, character-text functions, data and time, sumif-countif, and few lookup functions. 

Let’s now look at the top 25 Excel formulas you must know. In this article, we have categorized 25 Excel formulas based on their operations. Let’s start with the first Excel formula on our list.

The SUM() function, as the name suggests, gives the total of the selected range of cell values. It performs the mathematical operation which is addition. Here’s an example of it below:

Sum "=SUM(C2:C4)" 

Fig: Sum function in Excel

As you can see above, to find the total amount of sales for every unit, we had to simply type in the function “=SUM(C2:C4)”. This automatically adds up 300, 385, and 480. The result is stored in C5. 

The AVERAGE() function focuses on calculating the average of the selected range of cell values. As seen from the below example, to find the avg of the total sales, you have to simply type in:

AVERAGE =AVERAGE(C2, C3, C4)

Fig: Average function in Excel

It automatically calculates the average, and you can store the result in your desired location.

The function COUNT() counts the total number of cells in a range that contains a number. It does not include the cell, which is blank, and the ones that hold data in any other format apart from numeric. 

COUNT =COUNT(C1:C4)

Fig: Microsoft Excel Function - Count

As seen above, here, we are counting from C1 to C4, ideally four cells. But since the COUNT function takes only the cells with numerical values into consideration, the answer is 3 as the cell containing “Total Sales” is omitted here. 

If you are required to count all the cells with numerical values, text, and any other data format, you must use the function ‘COUNTA()’. However, COUNTA() does not count any blank cells.

To count the number of blank cells present in a range of cells, COUNTBLANK() is used.  

4. SUBTOTAL

Moving ahead, let’s now understand how the subtotal function works. The SUBTOTAL() function returns the subtotal in a database. Depending on what you want, you can select either average, count, sum, min, max, min, and others. Let’s have a look at two such examples.

Fig: Subtotal function in Excel

In the example above, we have performed the subtotal calculation on cells ranging from A2 to A4. As you can see, the function used is

SUBTOTAL =SUBTOTAL(1, A2: A4)

In the subtotal list “1” refers to average. Hence, the above function will give the average of A2: A4 and the answer to it is 11, which is stored in C5. Similarly,

“=SUBTOTAL(4, A2: A4)”

This selects the cell with the maximum value from A2 to A4, which is 12. Incorporating “4” in the function provides the maximum result. 

Fig: Count function in Excel

The MOD() function works on returning the remainder when a particular number is divided by a divisor. Let’s now have a look at the examples below for better understanding.

• In the first example, we have divided 10 by 3. The remainder is calculated using the function

        MODULUS =MOD(A2,3)

• The result is stored in B2. We can also directly type “=MOD(10,3)” as it will give the same answer. 

Fig: Modulus function in Excel

• Similarly, here, we have divided 12 by 4. The remainder is 0 is, which is stored in B3. 

The function “Power()” returns the result of a number raised to a certain power. Let’s have a look at the examples shown below:

Fig: Power function in Excel

As you can see above, to find the power of 10 stored in A2 raised to 3, we have to type:

Power =POWER (A2,3)

This is how power function works in Excel.

Next, we have the ceiling function. The CEILING() function rounds a number up to its nearest multiple of significance. 

Fig: Ceiling function in Excel

The nearest highest multiple of 5 for 35.316 is 40.

Contrary to the Ceiling function, the floor function rounds a number down to the nearest multiple of significance.

Fig: Floor function in Excel

The nearest lowest multiple of 5 for 35.316 is 35.

9. CONCATENATE

This function merges or joins several text strings into one text string. Given below are the different ways to perform this function.

• In this example, we have operated with the syntax:

  CONCATENATE  =CONCATENATE(A25, " ", B25)

Fig: Concatenate function in Excel

• In this example, we have operated with the syntax: 

        "=CONCATENATE(A27&" "&B27)"

Those were the two ways to implement the concatenation operation in Excel.

Also Read: How to Use Concatenate in Excel?

The function LEN() returns the total number of characters in a string. So, it will count the overall characters, including spaces and special characters. Given below is an example of the Len function.

Fig: Len function in Excel

Let’s now move onto the next Excel function on our list of this article.

11. REPLACE

As the name suggests, the REPLACE() function works on replacing the part of a text string with a different text string. 

The syntax is “=REPLACE(old_text, start_num, num_chars, new_text)”. Here, start_num refers to the index position you want to start replacing the characters with. Next, num_chars indicate the number of characters you want to replace. 

Let’s have a look at the ways we can use this function.

• Here, we are replacing A101 with B101 by typing

      REPLACE =REPLACE(A15,1,1,"B")

Fig: Replace function in Excel

• Next, we are replacing A102 with A2102 by typing:

        “=REPLACE(A16,1,1, "A2")” 

• Finally, we are replacing Adam with Saam by typing:

        “=REPLACE(A17,1,2, "Sa")” 

Let’s now move to our next function. 

12. SUBSTITUTE

The SUBSTITUTE() function replaces the existing text with a new text in a text string. 

The syntax is “=SUBSTITUTE(text, old_text, new_text, [instance_num])”.

Here, [instance_num] refers to the index position of the present texts more than once. 

Given below are a few examples of this function:

• Here, we are substituting “I like” with “He likes” by typing: 

        “=SUBSTITUTE(A20, "I like","He likes")” 

Fig: Substitute function in Excel

• Next, we are substituting the second 2010 that occurs in the original text in cell A21 with 2016 by typing “=SUBSTITUTE(A21,2010, 2016,2)”.

• Now, we are replacing both the 2010s in the original text with 2016 by typing “=SUBSTITUTE(A22,2010,2016)”.

That was all about the substitute function, let’s now move on to our next function. 

13. LEFT, RIGHT, MID

The LEFT() function gives the number of characters from the start of a text string. Meanwhile, the MID() function returns the characters from the middle of a text string, given a starting position and length. Finally, the right() function returns the number of characters from the end of a text string.  

Let’s understand these functions with a few examples.

• In the example below, we use the function left to obtain the leftmost word on the sentence in cell A5.

Fig: Left function in Excel

Shown below is an example using the mid function.

Fig: Mid function in Excel

• Here, we have an example of the right function.

Fig: Right function in Excel

14. UPPER, LOWER, PROPER

The UPPER() function converts any text string to uppercase. In contrast, the LOWER() function converts any text string to lowercase. The PROPER() function converts any text string to proper case, i.e., the first letter in each word will be in uppercase, and all the other will be in lowercase.

Let’s understand this better with the following examples:

• Here, we have converted the text in A6 to a full uppercase one in A7.

Fig: Upper function in Excel

• Now, we have converted the text in A6 to a full lowercase one, as seen in A7.

Fig: Lower function in Excel

• Finally, we have converted the improper text in A6 to a clean and proper format in A7.

Fig: Proper function in Excel

Now, let us hop on to exploring some date and time functions in Excel.

The NOW() function in Excel gives the current system date and time.

Fig: Now function in Excel

The result of the NOW() function will change based on your system date and time.

16. TODAY()

The TODAY() function in Excel provides the current system date.

Fig: Today function in Excel

The function DAY() is used to return the day of the month. It will be a number between 1 to 31. 1 is the first day of the month, 31 is the last day of the month.

Fig: Day function in Excel

The MONTH() function returns the month, a number from 1 to 12, where 1 is January and 12 is December.

 Fig: Month function in Excel

The YEAR() function, as the name suggests, returns the year from a date value.

Fig: Year function in Excel

The TIME() function converts hours, minutes, seconds given as numbers to an Excel serial number, formatted with a time format.

Fig: Time function in Excel

18. HOUR, MINUTE, SECOND

The HOUR() function generates the hour from a time value as a number from 0 to 23. Here, 0 means 12 AM and 23 is 11 PM.

Fig: Hour function in Excel

The function MINUTE(), returns the minute from a time value as a number from 0 to 59.

Fig: Minute function in Excel

The SECOND() function returns the second from a time value as a number from 0 to 59.

Fig: Second function in Excel

19. DATEDIF

The DATEDIF() function provides the difference between two dates in terms of years, months, or days.

Below is an example of a DATEDIF function where we calculate the current age of a person based on two given dates, the date of birth and today’s date.

Fig: Datedif function in Excel

Now, let’s skin through a few critical advanced functions in Excel that are popularly used to analyze data and create reports.

20. VLOOKUP

Next up in this article is the VLOOKUP() function . This stands for the vertical lookup that is responsible for looking for a particular value in the leftmost column of a table. It then returns a value in the same row from a column you specify. 

Below are the arguments for the VLOOKUP function:

lookup_value - This is the value that you have to look for in the first column of a table. table - This indicates the table from which the value is retrieved. col_index - The column in the table from the value is to be retrieved. range_lookup - [optional] TRUE = approximate match (default). FALSE = exact match.

We will use the below table to learn how the VLOOKUP function works.

If you wanted to find the department to which Stuart belongs, you could use the VLOOKUP function as shown below:

Fig: Vlookup function in Excel

Here, A11 cell has the lookup value, A2: E7 is the table array, 3 is the column index number with information about departments, and 0 is the range lookup. 

If you hit enter, it will return “Marketing”, indicating that Stuart is from the marketing department.

21. HLOOKUP

Similar to VLOOKUP, we have another function called HLOOKUP() or horizontal lookup. The function HLOOKUP looks for a value in the top row of a table or array of benefits. It gives the value in the same column from a row you specify.

Below are the arguments for the HLOOKUP function:

lookup_value - This indicates the value to lookup. table - This is the table from which you have to retrieve data. row_index - This is the row number from which to retrieve data. range_lookup - [optional] This is a boolean to indicate an exact match or approximate match. The default value is TRUE, meaning an approximate match.

Given the below table, let’s see how you can find the city of Jenson using HLOOKUP.

                                                     Fig: Hlookup function in Excel

Here, H23 has the lookup value, i.e., Jenson, G1:M5 is the table array, 4 is the row index number, 0 is for an approximate match.

Once you hit enter, it will return “New York”.

Our Data Analyst Master's Program will help you learn analytics tools and techniques to become a Data Analyst expert! It's the pefect course for you to jumpstart your career. Enroll now!

22. IF Formula

The IF() function checks a given condition and returns a particular value if it is TRUE. It will return another value if the condition is FALSE.

In the below example, we want to check if the value in cell A2 is greater than 5. If it’s greater than 5, the function will return “Yes 4 is greater”, else it will return “No”.

                     Fig: If function in Excel

In this case, it will return ‘No’ since 4 is not greater than 5.

‘IFERROR’ is another function that is popularly used. This function returns a value if an expression evaluates to an error, or else it will return the value of the expression.

Suppose you want to divide 10 by 0. This is an invalid expression, as you can’t divide a number by zero. It will result in an error. 

The above function will return “Cannot divide”.

23. INDEX-MATCH

The INDEX-MATCH function is used to return a value in a column to the left. With VLOOKUP, you're stuck returning an appraisal from a column to the right. Another reason to use index-match instead of VLOOKUP is that VLOOKUP needs more processing power from Excel. This is because it needs to evaluate the entire table array which you've selected. With INDEX-MATCH, Excel only has to consider the lookup column and the return column.

Using the below table, let’s see how you can find the city where Jenson resides.

                                                       Fig: Index-Match function in Excel

Now, let’s find the department of Zampa.

24. COUNTIF

The function COUNTIF() is used to count the total number of cells within a range that meet the given condition. 

Below is a coronavirus sample dataset with information regarding the coronavirus cases and deaths in each country and region.

Let’s find the number of times Afghanistan is present in the table.

Fig: Countif function in Excel

The COUNTIFS function counts the number of cells specified by a given set of conditions.

If you want to count the number of days in which the cases in India have been greater than 100. Here is how you can use the COUNTIFS function.

The SUMIF() function adds the cells specified by a given condition or criteria.

Below is the coronavirus dataset using which we will find the total number of cases in India till 3rd Jun 2020. (Our dataset has information from 31st Dec 2020 to 3rd Jun 2020).

 Fig: Sumif function in Excel

The SUMIFS() function adds the cells specified by a given set of conditions or criteria.

Let’s find the total cases in France on those days when the deaths have been less than 100. 

26. Goal Seek

Goal Seek is a function in-built in Advanced Excel Functions that allows you to get the desired output by changing the assumptions. The process is dependent on the trial and error method to achieve the desired result. 

Let’s look at an example to understand it better.

In this example, we aim to find what will be the rate of interest if the person wants to pay 

$5000 per month to settle the loan amount.

PMT function is used when you want to calculate the monthly payment you need to pay to settle the loan amount.

Let’s go through this problem in steps to see how we can calculate the interest rate that will settle a loan of $400,000 by $5,000 a month payment.

• PMT formula should now be entered in the cell that is the Payment cell adjacent. Currently, there is no value in the rate of interest cell, Excel gives us the payment of $3,333.33 because it assumes the rate of interest to be 0%. Ignore it.

• Go to Data > What - If Analysis > Goal Seek

• Set the monthly payment to -5,000. The deduction in amount signifies the negative value. Set rate of interest as the changing cell.

• Click OK. You will see the goal seek function automatically gives the interest rate that is required to pay the loan amount. 

Go to Home > Number and change the value to Percentage .

Your outcome will look like below:

27. What-If Analysis with Solver

What-If Analysis is the method of changing the values to try out different scenarios for formulas in Advanced excel.

Several different sets of values can be used in one or multiple of these Advanced excel formulas to explore the different results.

A solver is ideal for what-if analysis. It is an add-in program in Microsoft Excel and is helpful on many levels. The feature can be used to identify an optimal value for a formula in the cell known as the objective cell. Some constraints or limits are however applicable on other formula cell values on a worksheet. 

Solver works with decision variables which are a group of cells used in computing the formulas in the objective and constraint cells. The solver adjusts the value of decision variable cells to work on the limits on constraint cells. This process aids in determining the desired result for the objective cell.

Also Read: The Best Guide to Build an Excel Dashboard

Activating Solver Add-in

• On the File tab, click Options.
• Go to Add-ins, select Solver Add-in, and click on the Go button.

• Check Solver Add-in and click OK.

•  In the Data tab, in the Analyze group, you can see the Solver option is added.

How to Use Solver in Excel

In this example, we will try to find the solution for a simple optimization problem.

Problem: Suppose you are the business owner and you want your income to be $8000.

Goal: Calculate the units to be sold and price per unit to achieve the target.

For example, we have created the following model:

• On the Data tab, in the Analysis group, click the Solver button.
• In the set objective, select the income cell and set its value to $8000.
• To Change the variable cell, select the C5, C6, and C10 cells.

• Click Solve. 

Your data model will change according to the conditions.

28. If-Else

IF function is used to test the condition and return a value if the condition is indeed true and a predetermined different value if it turns out to be false.

If-Else =IF(test, true result, false result)

29. If-Error

The Excel IFERROR function returns an alternative result when a formula generates an error and an expected result when no error is detected. 

If-Error =IFERROR (value, value_if_error)

For example, Excel returns a divide by zero error when a formula tries to divide a number by 0.

By using the IFERROR function, you can add a message if the formula evaluates to an error.

Strategies for Leadership Excellence

30. Index and Match

This is an Advanced Excel function. MATCH function is designed to return the position of a value in a specified range, while the INDEX function returns a specific value present in a uni-dimensional range.

The MATCH function returns the position of the ID you are looking for. The INDEX function will return the value of the salary corresponding to the position.

31. Offset Function

The OFFSET function returns a reference to a range of cells  that is a specified number of rows and columns from a cell or range of cells.

Offset Function =OFFSET(reference, rows, cols, [height], [width])

Consider the following data: 

 To reference C4 starting at A1,  reference is A1, rows is 3 and cols is 2:

SUM Function With OFFSET

In this example we have a monthly sales data of two years. The goal is to find the sum of sales for a specific month.

The OFFSET function returns a 1x2 range, 8 rows below cell A2, and 1 column right of cell A2. The SUM function then calculates the sum of this range.

Looking to build a career in the exciting field of data analytics? Our Data Analytics courses are designed to provide you with the skills and knowledge you need to excel in this rapidly growing industry. Our expert instructors will guide you through hands-on projects, real-world scenarios, and case studies, giving you the practical experience you need to succeed. With our courses, you'll learn to analyze data, create insightful reports, and make data-driven decisions that can help drive business success.

Program Name Data Analyst Post Graduate Program In Data Analytics Data Analytics Bootcamp Geo All Geos All Geos US University Simplilearn Purdue Caltech Course Duration 11 Months 8 Months 6 Months Coding Experience Required No Basic No Skills You Will Learn 10+ skills including Python, MySQL, Tableau, NumPy and more 10+ skills including Data Analytics, Statistical Analysis using Excel, Data Analysis Python and R, and more 8+ Data Visualization with Tableau, Linear and Logistic Regression, Data Manipulation and more Additional Benefits Applied Learning via Capstone and 20+ industry-relevant Data Analytics projects Purdue Alumni Association Membership Free IIMJobs Pro-Membership of 6 months Resume Building Assistance Access to Integrated Practical Labs Caltech CTME Circle Membership Cost $$ $$$$ $$$$ Explore Program Explore Program Explore Program

Excel is a really powerful spreadsheet application for data analysis and reporting. After reading this article, you would have learned the important Excel formulas and functions that will help you perform your tasks better and faster. We looked at numeric, text, data-time, and advanced Excel formulas and functions. Needless to say, Excel knowledge goes a long way in shaping many careers.

Looking to advance your career in Data Science or Business Analytics? Simplilearn's Caltech Post Graduate Program in Data Science and Business Analyst can help you achieve your goals! These comprehensive programs are designed to provide you with the skills and knowledge needed to excel in the field. Led by industry experts, the courses offer hands-on training, real-world projects, and personalized mentorship. Plus, upon completion, you'll receive a prestigious certification from one of the world's top universities. Don't wait, enroll now and take your career to the next level!

Do you have any questions related to this article on Excel Formulas? If yes, then please let us know in the comments section of the article. Our team of experts will help you solve your queries right away. 

To kick-start your fruitful career in Excel , please click on the following link: Business Analytics with Excel .

1. What are the basic formulas in Excel?

Basic formulas in Excel include arithmetic operations like addition, subtraction, multiplication, and division—for example, SUM, AVERAGE, COUNT, and PRODUCT.

2. What is MS Excel formulas and functions?

MS Excel formulas and functions are expressions used to perform calculations or manipulate data in Excel. Formulas start with an equal sign (=) and can contain functions, mathematical operations, cell references, and constants.

3. How to write a formula in Excel?

To write a formula in Excel, start with an equal sign (=), followed by the formula expression. For example, to add two numbers in cells A1 and B1, write "=A1+B1" in another cell.

4. What are basic Excel skills?

Basic Excel skills include:

• Entering data.
• Formatting cells.
• Using basic formulas and functions.
• Creating simple charts.
• Sorting and filtering data.
• Understanding cell references.

5. What is VLOOKUP in Excel?

VLOOKUP is a function in Excel used to search for a value in the first column of a table range and return a related value from a specified column. It's commonly used for data lookup and retrieval.

6. What is a formula in Excel?

An Excel formula is a mathematical expression that works with values in a specific range of cells. These formulas yield a result, even if it's an error. They empower you to carry out addition, subtraction, multiplication, and division calculations within Excel.

7. What are the 5 important formulas in Excel?

Some important Excel formulas include:

• SUM: The SUM formula adds the values in a range of cells.
• AVERAGE: The AVERAGE formula averages the values in a range of cells.
• COUNT: The COUNT formula calculates the quantity of cells that hold numeric values within a designated range of cells.
• IF: The IF formula assesses a condition and provides a specific output if the condition is true, or an alternative output if the condition is false.
• VLOOKUP: The VLOOKUP formula searches for a value in a table and returns the corresponding value from another column in the table.

Data Science & Business Analytics Courses Duration and Fees

Data Science & Business Analytics programs typically range from a few weeks to several months, with fees varying based on program and institution.

Recommended Reads

Top Job Roles in the Field of Artificial Intelligence

Excel Percentage Formula: The Best Introduction

DAX in Excel: The Complete Guide for DAX Functions and Formulas in Excel

An Introduction to Cyber Security: A Beginner's Guide

How to Calculate Percentage in Excel?[With Examples]

Your One-Stop Solution to Calculate Age in Excel

Get Affiliated Certifications with Live Class programs

Data analyst.

• Industry-recognized Data Analyst Master’s certificate from Simplilearn
• Dedicated live sessions by faculty of industry experts

Post Graduate Program in Business Analytics

• PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.

• Generative AI
• Office Suites
• Collaboration Software
• Productivity Software
• Augmented Reality
• Emerging Technology
• Remote Work
• Artificial Intelligence
• Operating Systems
• IT Leadership
• IT Management
• IT Operations
• Cloud Computing
• Computers and Peripherals
• Data Center
• Enterprise Applications
• Vendors and Providers
• Enterprise Buyer’s Guides
• United States
• Netherlands
• United Kingdom
• New Zealand
• Newsletters
• Foundry Careers
• Terms of Service
• Privacy Policy
• Cookie Policy
• Copyright Notice
• Member Preferences
• About AdChoices
• E-commerce Affiliate Relationships
• Your California Privacy Rights

Our Network

• Network World

How to use Excel formulas and functions

Formulas are powerful tools for performing calculations and analyzing data in excel. in this beginner’s guide, you’ll learn how to use formulas and explore some popular built-in functions..

One of the most commonly used Microsoft programs, Excel is highly useful for data collecting, processing, and analysis. To fully harness Excel’s powers, though, you need to make use of formulas.

Excel formulas allow you to perform calculations, analyze data, and return results quickly and accurately. The usefulness of formulas is even greater once you start dealing with large data sets. With the correct formula, Excel can process vast amounts of information in a matter of seconds.

What is a formula in Excel?

A formula is an expression that operates on values in a range of cells in Excel. Using formulas, you can perform calculations and data analysis on the contents of the cells. Formulas can be as simple as adding a column of numbers together or as complex as returning the kurtosis of a data set. They can be incredibly useful when you want to turn spreadsheet data into meaningful information for driving business decisions.

What is a function in Excel?

A function is a built-in formula in Excel — basically, a shortcut for performing a calculation or other operation on cell data. There are nearly 500 Excel functions, and the list continues to grow every year. Fortunately, most of the actions that a typical business user would want to perform can be done with just a handful of functions.

In this article we’ll look at five useful types of formulas and functions that will get you started performing data analysis in Excel. Along the way, you’ll learn several different ways to enter formulas and functions in Excel.

We’ll demonstrate using Excel for Windows under a Microsoft 365 subscription. If you’re using a different version of Excel, you might not have exactly the same interface and options, but the formulas and functions work the same.

1. Basic mathematics formulas and functions

We’re going to group these formulas together since they are very simple and have similar syntax. All formulas in Excel start with the equal sign (=) and build from there.

Adding, subtracting, multiplying, and dividing

To add the numbers in two cells together, first click the on the target cell where you want the total to appear. Then type = in the cell to start the formula.

Starting a formula in Excel.

Next, click on the cell that contains the first number you want to add, and its cell reference (such as A2) will appear next to the equal sign in the formula.

When you select a cell when building a formula, its cell reference appears in the formula.

Type + next to the first cell reference. Then click the cell that contains the second number you want to add, and its cell reference (such as A3) will appear next to the + sign. The full syntax for the formula to add the values in cells A2 and A3 is:

The complete addition formula appears in both the target cell and the formula bar above.

Note that in addition to appearing in the target cell, the formula also appears in the formula bar directly above the worksheet. Once you’ve inserted the initial = sign in the target cell, you can type your formula in the formula bar. It’s sometimes easier to see the whole formula and work with it in the formula bar than down in the worksheet page.

If you wanted to add additional numbers to your total, you’d type another + sign, select another cell, and so on. Once your formula is complete, press Enter , and the result appears in the target cell.

Press Enter , and the result appears in the target cell.

Subtraction, multiplication, and division calculations work the same way. You simply change the operator — the symbol that tells Excel what math operation you want to perform — from the + sign to the – sign for subtraction, the * sign for multiplication, or the / sign for division.

Subtraction, multiplication, and division actions. The formula for each is shown in the formula bar; the result in the target cell.

Adding numbers with the SUM function

There’s a quicker way to add together a group of numbers. This is where Excel’s built-in SUM function comes in.

First click on the target cell where you want the total to appear. Then type =SUM to start the function.  A list of options will come up. Click the first option, SUM . You’ll now see =SUM( in the target cell.

Starting a SUM function.

Just underneath the cell with the SUM function is a tooltip showing the SUM syntax:

=SUM(number 1, [number2],…)

To add individual cells together, select a cell, type a comma, select another cell, and so on. (Alternatively, you can type a cell reference, type a comma, type another cell reference, and so on.)

If you want to add consecutive cells (such as in a row or column), select the first cell, then hold down the Shift key and select the final cell in the group. (Or you can type in the cell references for the first and last cells separated by a colon — for instance, A2:A7 selects A2, A7, and all the cells in between.)

Select the range of cells you want to add together.

Once all the cells you want to add together are selected, hit Enter .

Now you should see the final result, which is the sum of the numbers you highlighted. If you highlight that target cell again, you’ll see the full formula in the formula bar — in our example, it’s:

=SUM(A2:A7)

The SUM function is shown in the formula bar; the result appears in the target cell.

One important thing to note for all Excel formulas is that they produce relative values. This simply means that if any of the values in the selected cells changes, then the final number will change to reflect that.

If the value of a cell used in a formula changes, the result also changes.

If you want to make it an absolute value, a number that will not change even if the cells that were used to calculate it change, then you need to right-click the cell and select Copy from the pop-up menu. Then right-click the cell again and, under Paste Options, select the Values button (the icon of a clipboard with 123).

Copy and paste the value into the cell to prevent the value from changing if one of the source cell’s values changes.

Now when you select that cell you’ll just see the plain number, not a formula, in the formula bar.

The cell now contains an absolute value, not a formula.

Tip: Excel provides a SUM shortcut in certain circumstances. If you have a series of numbers in a row or a column, Excel assumes you want to add them together. So if you place your cursor in the cell to the right of a row of numbers and click the AutoSum (Σ) button toward the right end of the Ribbon’s Home tab, Excel automatically selects the numbers in the row, then adds them together when you press Enter . Likewise, if you place your cursor in the cell below a column of numbers, click AutoSum , and hit Enter , Excel totals up the numbers in the column.

AutoSum is a shortcut for adding a row or column of numbers. (Click image to enlarge it.)

Calculating the average with the AVERAGE function

To calculate the average of a group of numbers, repeat the same steps but using the syntax =AVERAGE and highlighting the cells containing the numbers that you want to use in the calculation.

To quickly calculate the average of a group of numbers, use the AVERAGE function.

Tip: As with SUM, there’s a shortcut for using the AVERAGE function if you have a series of numbers in a row or a column. Place your cursor in the cell to the right of a row of numbers or in the cell below a column of numbers. Click the tiny down arrow at the right side of the AutoSum button, select Average from the menu that appears, and hit Enter . Excel calculates the average of the values in that row or column.

There’s an AutoSum shortcut for the AVERAGE function as well.

Find more details, examples, and best practices for these functions at Microsoft’s SUM function and AVERAGE function support pages.

2. The IF function

This function helps you automate the decision-making process by applying if-then logic to your data. Using this function, you can have Excel perform a calculation or display a certain value depending on the outcome of a logical test. For example, you can create a test that checks if the value of a cell is greater than or equal to the value of 18 and enter “Yes” or “No” accordingly.

While we’re learning this function, we’ll cover another way to enter functions in Excel: by using the Formulas tab on the Ribbon. Here you’ll find buttons that provide quick access to functions by category: AutoSum, Financial, Logical, Text, Date & Time, and so on. Being able to browse through functions by category can be helpful if you can’t remember the exact name of a function or aren’t sure how to spell it.

To enter the IF function, select the target cell, and on the Formulas tab, click the Logical button, then select IF from the list of functions that appears.

Alternatively, you can click the Insert Function button all the way to the left of the Formulas tab. An “Insert Function” pane appears, showing a list of commonly used functions.

Instead of typing = to start a function, you can go to the Formulas tab and select Insert Function . (Click image to enlarge it.)

Select IF from the list and click OK . (If the function you want isn’t in the “Commonly Used” list, select a different category or All to see all available functions.)

The Function Arguments pane appears, and you’ll see =IF() in the target cell.

You can use the Function Arguments pane to build a function. (Click image to enlarge it.)

The IF function syntax is as follows:

=IF(logical_test,”value_if_true”,”value_if_false”)

You’ll notice that the Function Arguments pane for the IF function has fields for Logical_test, Value_if_true, and Value_if_false. In our “greater than or equal to 18” example, the logical test is whether the number in the selected cell is greater than or equal to 18, the value if true is “Yes,” and the value if false is “No.” So we’d enter the following items in the pane’s fields like so:

Logical_test: B2>=18

Value_if_true: “Yes”

Value_if_false: “No”

or just type the full formula into the target cell:

=IF(B2>=18,”Yes”,”No”)

This tells Excel that if the value of cell B2 is greater than or equal to 18, it should enter “Yes” in the target cell. If the value of cell B2 is less than 18, it should enter “No.”

The IF function in action.

Tip: When using functions like this, rather than entering the function repeatedly for each row, you can simply click and drag the tiny square on the bottom right of the cell that contains the function. Doing so will autofill each of the rows with the formula, and Excel will change your cell references to match. For example, when the formula we used in cell C2 that references cell B2 is autofilled into cell C3, it changes to reference cell B3 automatically.

Autofilling a formula to subsequent rows in the column.

Find more details at Microsoft’s IF function support page.

Next page: SUMIF, COUNTIF, CONCAT, and VLOOKUP →

3. The SUMIF and COUNTIF functions

SUMIF is a more advanced SUM function that allows you to add up only the values in a range that meet the criteria you specify. To use this function, you must specify the range of cells to apply the criteria to, the criteria for inclusion, and, optionally, the sum range , which is the range of cells to total if that’s different from the initial range. The syntax is as follows:

=SUMIF(range,criteria,[sum_range])

Note that any criteria with text or mathematical or logical symbols must be enclosed in double quotes.

In the sales spreadsheet shown below, for example, suppose you want to total up only the sales that are more than $100. The criteria range is C2 to C9, and the criteria is “greater than 100.” Since you’re adding up the values in that same cell range (C2 to C9), you don’t need to supply a separate sum range. So your formula is:

=SUMIF(C2:C9,”>100″)

Using the SUMIF function.

What if instead you want to find the total for all sales in the East region only? To do that you’ll have to specify both the criteria range (cells B2 to B9) and the sum range (cells C2 to C9). This is the formula:

=SUMIF(B2:B9,B2,C2:C9)

Note that you don’t have to type out “ East” for the criteria. You can simply type B2 or click the cell B2 to have Excel search for the text it contains.

Using SUMIF with both a criteria range and a sum range.

There is a similar function called COUNTIF that lets you create a count of values that meet specified criteria. The syntax is as follows:

=COUNTIF(range,criteria)

So to count the total number of sales in the West region, for instance, you supply the range of cells to apply the criteria to (B2 to B9), followed by the criteria (“West” or cell B3). The formula is:

=COUNTIF(B2:B9,B3)

The COUNTIF function can instantly count up items that meet your criteria.

What if you want to apply multiple criteria to your data, such as calculating total sales for books in the East region, or counting the number of sales over $100 in the West region? Excel can do that too, via functions called SUMIFS and COUNTIFS. These functions use more complex syntax than SUMIF and COUNTIF. For more details, use cases, and best practices for all four of these functions, see Microsoft’s SUMIF , SUMIFS , COUNTIF , and COUNTIFS support pages.

4. The CONCAT function

This function is useful for piecing together text from different cells into one complete string. For instance, maybe you have a worksheet with different columns for people’s first and last names, but you want to put first and last names together. Other common use cases are completing an address, reference number, file path, or URL. The syntax is as follows:

=CONCAT(text1,text2,text3,…)

In this example we will use CONCAT to combine a list of first names and last names into a full name with a space in between. To do so we simply place the cursor in cell C2, type =CON and select CONCAT from the list of options that appears. Next, select the cell that contains the first name (A2) and add a comma, a blank space surrounded by quotation marks, and another comma. Then add the last name by selecting the adjacent cell (B2) and hit Enter. Here’s the full formula:

=CONCAT(A2,” “,B2)

Next, click and drag the bottom right of cell C2 to autofill the formula in all the other rows.

The CONCAT function combined the values from column A with those from column B.

For more details and examples, see Microsoft’s CONCAT function support page.

5. The VLOOKUP function

This is one of the most commonly used functions in Excel and a valuable data analysis tool. VLOOKUP lets you look up a value in a table and return information from other columns related to that value. It’s very useful for combining data from different lists or comparing two lists to find matching items. To use this function, you need to provide three to four pieces of information:

• The value you want to look for. This is known as the lookup value .
• The range of cells to look in. This is known as the table array .
• The column that contains the information you want to return, called the column index number .
• Optionally, the type of lookup you want to perform: TRUE or FALSE. This is known as the range lookup . FALSE means you want an exact match for the lookup value, while picking TRUE returns the best approximate match. If you don’t specify a range lookup, VLOOKUP defaults to TRUE.

The syntax is as follows:

=VLOOKUP(lookup_value,table_array,column_index_number,[range_lookup])

The lookup value must be in the first column of cells you specify in the table array. The leftmost column in the table array has a column index number of 1, with subsequent columns numbered 2, 3, and so on.

In this example, we’ll look up what region our employees work in. To do so, we first need to specify the value that we are going to search for: the employee name Mike (cell A2). Next, we need to highlight the entire cell range (table array) that we want to look in: cells F2:G8.

Then we specify which column holds the information that we want. Rather than picking the column itself, we count from left to right within the table array. Since the column that contains the region is the second from the left, type in 2 .

Lastly, we enter the TRUE (best approximate match) or FALSE (exact match) option. TRUE is typically only used with numbers or when you aren’t sure if the value you want is in the table. Since we know the value we want is in the table, we will pick FALSE . Generally, FALSE is the better option, as it returns more accurate results.

This is the full formula:

=VLOOKUP(A2,F2:G8,2,FALSE)

Use VLOOKUP to find values linked to other values in large data sets.

This is an oversimplified example using a small data set, but when you need to search through a spreadsheet with thousands (or tens of thousands) of cells, VLOOKUP is a huge time-saver and reduces the possibility of errors. For more details and examples, see Microsoft’s VLOOKUP function support page.

You’re just getting started

In this story you’ve seen how powerful formulas and functions can be in Excel — and we’ve only the scratched the surface of what they can do. Once you get comfortable using them, you can explore some of the myriad prebuilt functions Excel offers and learn how to build more complex formulas (including nesting functions). That’s all beyond the scope of this article, but a great place to start is Microsoft’s “ Overview of formulas in Excel ” support page, which includes links to several helpful tutorials.

Related content

Google can’t seem to quit cookies, delays killing them again, apple reportedly cuts vision pro production due to low demand, windows 11: a guide to the updates, windows 10: a guide to the updates, from our editors straight to your inbox.

Shimon Brathwaite is a cybersecurity professional, consultant, and writer at SecurityMadeSimple . He is a graduate of Ryerson University in Toronto, Canada and has worked in several businesses in security-focused roles. His professional certifications include GCIH, Security+, CEH, and AWS Security Specialist. Contact him for writing engagements, consulting, or to ask questions!

More from this author

How to use pivottables and pivotcharts in excel, microsoft forms cheat sheet: how to get started, how (and why) to use conditional formatting in excel, show me more, the end of non-compete agreements is a tech job earthquake.

Meta opens its mixed-reality Horizon OS to other headset makers

A crafty new Android notification power-up

After 10 years of progress, does mixed reality (XR) have a future? | Ep. 147

More tech layoffs as AI takes hold | Ep. 146

Why the world will be wearing more technology in the future

After 10 years of progress, does mixed reality (XR) have a future?

More tech layoffs as AI takes hold

101 Advanced Excel Formulas & Functions Examples

Advanced Excel Formulas & Functions are a must know feature in Microsoft Excel that allows you to... read more

Steps To Follow

Get Trainings

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Watch our free training video on how to master the most popular excel formulas.

Advanced Excel Formulas & Functions are a must know feature in Microsoft Excel that allows you to quickly analyze your data in many forms.  Once you learn how to construct Excel Formulas & Functions, your Excel skills & level will increase exponentially.

Below you will find 101 Excel formulas & functions examples for key formulas & functions like VLOOKUP, INDEX, MATCH, IF, SUMPRODUCT, AVERAGE, SUBTOTAL, OFFSET, LOOKUP, ROUND, COUNT, SUMIFS, ARRAY, FIND, TEXT, and many more. You will also find Excel formulas with examples in Excel sheet free download!

See how easy it is to learn the Top Excel Formulas here…

Formulas vs functions.

You most probably have heard the words Formulas & Functions both being used in Excel. What is the difference between them?

A Formula is an expression which calculates the value of a cell and a Function is a predefined formula that is made available for you to use in Excel.  Below is an example of both but we use both terms (Formulas & Functions) interchangeably:

DATE & TIME DateDif EndOfMonth Time Weekday Weeknum Workday Year

LOGICAL And If If, And Iferror

  MATH Average Count CountA CountBlank CountIf CountIfs Mathematical Formulas Mod Percentage Rand Randbetween Round Subtotal Sumifs Sumproduct

  TEXT Between Clean Concatenate Data Cleansing, Trim Extract, Find, Left Left Len, Length Proper Remove Remove, Substitute Replace Replace, Cleanup Right Substitute Substitute, Trim Text Upper

  OFFICE 365 Filter Sort Sortby Unique

DOWNLOAD OUR FREE EXCEL GUIDES

Click on any Excel formula & function link below and it will take you to the advanced Excel formulas with examples in Excel sheet free download for you to practice!

Table of Contents

Array, Lookup 

Concatenate , convert, values , countblank , data cleansing, trim , endofmonth , extract, find, left , find & select .

DOWNLOAD OUR FREE EXCEL FORMULA DOWNLOAD AND GUIDES

Iferror, Vlookup 

Index, match , len, length , mathematical formulas , multiple criteria, vlookup , percentage , pivot table, getpivotdata , randbetween , remove, substitute , replace, cleanup , show, hide , substitute , substitute, trim , sum, lookup , sumproduct .

Bryan is a best-selling book author of the 101 Excel Series paperback books.

Related Articles

How to Use IF Function in Excel – Step by Step Guide

How to Use VLOOKUP and IF Statement in Excel

How to Find Matches with Excel Fuzzy Lookup Setup Fast

Get Video Training

Dramatically Reduce Repetition, Stress, and Overtime! Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job! Learn in as little as 5 minutes a day or on your schedule.

20 Advanced Excel Functions and Formulas (for Excel Pros)

Excel has 450+ functions that can do a range of awesome things. If you’ve used Excel even for a few days, I am sure you have heard of functions like VLOOKUP, SUMIF, COUNTIF, and so on.

And what is more awesome is that one Excel function – a formula that consists of two, three, or more functions.

With a combination of functions, you can create some advanced Excel formulas that can do some incredibly advanced things with a press of a key.

So if you’re an Excel aficionado like me, I am sure you’re going to love this article. In this article, I will cover the 20 advanced Excel functions you should know.

I will also share examples of some advanced formulas you can create with these advanced functions.

A quick note about advanced functions/formulas: By advanced, I mean functions that would need some know-how and are not usually used by basic Excel users (such as SUM or COUNT).

So let’s get started!

This Tutorial Covers:

XLOOKUP 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.

XLOOKUP is a refined version of VLOOKUP, that addresses some of the drawbacks that VLOOKUP has.

You can use this function in situations where you need to scan a list of values in rows or columns and find out if the list contains the value you’re looking for (called the lookup value).

It also allows you to find the value, and then return any other value from the same row or column.

If you’re still confused about what this function does, consider this – you go to a newly opened café close to your house and you want to eat something. so you get their menu and start scanning the menu from top to bottom.

When you identify an item you would want to order, you look to the right to check the prices.

And that’s what XLOOKUP does for you. It can scan the list, find out the position of the lookup value, and return the value from any corresponding column in the same row.

There are many amazing things you can do with XLOOKUP, and I have covered some in the video below:

Note : If you use XLOOKUP and share the file with someone who is using an older version of Excel, the formulas won’t work in their workbooks. So if that’s the case, it’s best not to use XLOOKUP

VLOOKUP Function

VLOOKUP had been the undisputed king of Excel functions, till the XLOOKUP function arrived. Since many people are still not using Excel 2021 or Excel for Microsoft 365, they don’t have access to XLOOKUP.

So, for them, VLOOKUP is the function they must know. For advanced formula users, a complete understanding of using VLOOKUP can really make a difference in their work.

Just like XLOOKUP, VLOOKUP also scans a list in a column and can return the matching lookup value or any value from any column in the same row.

If you want to learn how to best use the VLOOKUP function, check out this detailed tutorial on this advanced formula: 10 VLOOKUP Examples For Beginner & Advanced Users

Here are some advanced formula examples of using VLOOKUP:

• How to Use VLOOKUP with Multiple Criteria
• Use VLookup to Get the Last Number in a List in Excel
• Avoid Nested IF Function in Excel…VLOOKUP to Rescue
• Use IFERROR with VLOOKUP to Get Rid of #N/A Errors

INDEX / MATCH Function

The combination of INDEX and MATCH functions can do wonders. So if you desire to learn about advanced formulas in Excel, you must learn how this magical combination works.

Till the time XLOOKUP arrived, many advanced Excel users preferred using INDEX/MATCH over VLOOKUP (and this combination can take care of some of VLOOKUP’s drawbacks). There used to be a hot debate on which formula is better VLOOKUP vs INDEX/MATCH .

Even with XLOOKUP, I highly recommend you learn using INDEX/MATCH.

While individually these functions aren’t that useful, when you combine them, it’s a powerful tool for advanced Excel formula users.

You can read the following guide where I cover some basic and advanced usage of the INDEX/MATCH formula – INDEX & MATCH Functions Combo in Excel (10 Easy Examples)

SUMPRODUCT Function

Outside the realm of lookup and reference formulas (i.e., XLOOKUP, VLOOKUP, and INDEX/MATCH), SUMPRODUCT is one of those functions that advanced Excel users love.

Its name may fool you into thinking that it’s a math or stat formula that can add multiple numbers, but it’s way more than that.

Initially, the Excel team wanted this function to do calculations where it can give you the sum of the product in two columns.

While this is a decent use case, Excel users got crafty and found out that it can be a lot more than this.

Advanced Excel users often use the SUMPRODUCT formula for conditional sums or conditional calculations.

Suppose you have a dataset of salespeople, their region, and their sales numbers. Then you can use SUMPRODUCT to:

• Only add sales value for a specific region (or more than one region)
• Only add sales values above a specific value
• Add sales values above or below a specific value (OR condition)

You can check the video below where I show SUMPRODUCT examples:

Note: The SUMPRODUCT function is quite popular among Excel users who create dashboards and reports. Excel has introduced a new SUMIFS function that can do some of what the SUMPRODUCT function does, but not everything. So if you want to learn advanced Excel formulas, I would suggest you learn this one too.

SUMIF/COUNTIF & SUMIFS/COUNTIFS Functions

I have clubbed these four functions together as they have the same modus-operandi.

You can use this to conditionally sum or count from your dataset.

For example, if you have a list of names, and you want to quickly count how many times a specific name appears in the list, you can use the COUNTIF formula.

And similarly, if you have a data set where you have the Sales Rep names and their sales values, and you want to get the sum of all the sales done by a specific sales Rep, you can use the SUMIF function.

And in case you need to check for multiple conditions, you can use the COUNTIFS or SUMIFS formulas.

For example, if I have a data set with a Date column, a Sales Rep name column, and their Sales values, I can use the SUMIFS formula to get the sum of all the sales done by a specific sales Rep in a specific month.

Since there are two conditions to check (the sales Rep name and the date),  you need to use the SUMIFS formula. Similarly, if you have multiple conditions to check when getting the count in a dataset, use the COUNTIFS formula.

FILTER Function

FILTER is a new function in Excel 2021 and Excel for Microsoft 365, and it’s amazing.

As the name suggests, you can use it to filter a dataset in Excel and extract the results.

This was one of the most requested functions in Excel, as the workaround without the FILTER function is quite long and complex.

To give you a simple example to showcase the FILTER function utility, suppose you have a dataset with Sales Rep names, their regions/countries, and their sales values.

With the FILTER function, you can quickly filter all the records for any specific country. And to make it even more powerful, instead of hard-coding the name of the country in the formula, you can put it in a cell and refer to that cell in the formula.

This way, you can simply change the name of the country in the cell, and the formula would instantly give you all the records for that country.

Below is a video I made showing some advanced examples of the FILTER function.

SORT & SORTBY Function

Again, two new functions in Excel 2021 and Excel for Microsoft 365.

Just like the FILTER function, these were much needed as there was no easy way to sort the data dynamically with a formula.

Before the introduction of this formula, most of this data sorting was done using the sort feature in Excel which gives you a static result.

This means that in case you change your original data set, the resulting sorted data wouldn’t update and you’ll have to sort it again.

But not anymore.

Using the SORT function, you can quickly sort any data set based on the specified row or column. It also gives you the flexibility to choose the sort order (i.e., ascending or descending).

So if you have a data set and you want to sort this data based on one specific column, you can use the SORT function.

The SORTBY function provides a little more functionality while sorting the data using a formula. With this function, you can sort based on multiple columns.

For example, if you have 2 columns, where the first column has the name of the region (such as East, West, North, South), and the other column has the sales values, then you can use the SORTBY function to first sort this based on the region name and then based on the sales values.

UNIQUE Function

UNIQUE Is again a new function that is available only to the users of Microsoft 365.

Just like the SORT and the SORTBY function, this one was also desperately needed.

As the name suggests, the UNIQUE function will give you a list of unique names or items in a dataset.

Before this function, the way to do this was by using the remove duplicate functionality , or the advanced filter functionality .

But even with these inbuilt functionalities, with large data sets and more conditions, it used to become complicated.

And of course, the result used to be static, which meant that in case the original data changes, the resulting data would not update and you will have to repeat the process again.

TEXTJOIN Function

TEXTJOIN is a relatively new function that was introduced in Excel 2019, edit solves a major problem.

TEXTJOIN allows you to quickly combine the content of a selected range of cells without creating along concatenate formula or using the & sign a gazillion times.

So if you have multiple items in different cells in a row or column, and you want to quickly combine them, you can do that using one single formula.

It also allows you to specify a delimiter such as a space character or a comma, so that all the combined cells would have that delimiter between the content of the cells.

To be honest, this is a very simple formula that even the most basic Excel user can use, but if you are an advanced excel user, it is unforgivable for you to use concatenate and & combine cells (you ought to know better)

IFS Function

IFS is another function that was introduced in Excel 2019 (way too late if you ask me).

I love the IF function and it has done a lot of heavy lifting for me over the years. But the dude has limitations.

To begin with, you can only check for two conditions, so if you need to check for three or more, you end up with a long nested if formula .

And God forbid if you have to check for 5, 10, or 15 conditions, I can imagine what horror your formulas would look like.

To ease the pressure on the overworked IF formula, Microsoft introduced the IFS function which can check for multiple conditions.

So if you’re a teacher grading students or a manager who needs to decide how much Commission your sales reps get based on their sales value, you don’t need to create long convoluted IF formulas, instead, you can use the IFS function.

IFERROR Function

You can’t call yourself an advanced Excel formula user if you’re Excel sheet is full of errors such as #N/A or #REF! or #DIV/0!.

While getting these errors may be out of your control sometimes, IFERROR allows you to handle these errors by replacing them with something more meaningful.

For example, if you’re using the VLOOKUP formula and it cannot find the lookup value, it is going to return the NA error. with the IFERROR formula , you can replace this NA error with something more meaningful such as “Look-up value not found” or “Data not available”.

OFFSET Function

OFFSET function can be used in very specific situations, and most advanced Excel users would rarely need to use it.

OFFSET function allows you to offset the reference by this specified number of rows or columns.

For example, I can use the OFFSET function to offset the reference A1 by two rows and two columns so that it gives me C3 (which is the reference of a cell that is two rows below and two columns to the right)

If you’re interested in learning how the OFFSET function works, I have a detailed tutorial where I cover some examples of how to use the OFFSET formula .

One important thing you need to know about the OFFSET function is that it is volatile , which means that anytime there is a change in your worksheet the function would be re-calculated.

While you won’t notice any changes if you are using the OFFSET function in a couple of cells, if you’re using it inner bigger data set or an entire row or column, then it could slow down your Excel file .

FIND / SEARCH Function (With Wildcard Characters)

FIND and SEARCH are two similar functions that allow you to search for a given text in a cell and return the starting position of the string that you searched for.

You cannot claim to be an advanced Excel formula user if you do not know how to use the full power of FIND/SEARCH functions.

While these functions won’t be of much help on their own, when you combine these with other text functions LEFT/RIGHT/MID, you can do some serious text manipulation.

For example, if you have a column full of e-mail addresses, and you want to get the user ID and not get everything after the @ symbol, you can do that using the FIND or the SEARCH function.

To do this, we’d first have to use one of these functions to find the position of the add the rate in the cell, and once you have this position you can use this value within the left function to extract everything which is to the left of the @ symbol.

Note that you cannot use wildcard characters in the FIND formula however you can use them in the SEARCH formula

RIGHT / LEFT / MID Function

Working with text data is a regular part of many advanced Excel users’ day-to-day work.

While there are many text manipulation options available in Excel (such as Text to Columns or Power Query), many advanced Excel users still prefer going the formula way.

RIGHT , LEFT , and MID functions allow you to extract a part of the text string.

For example, if you have an e-mail ID such as [email protected], you can use the LEFT function to extract only the username from this e-mail ID, or you can use the RIGHT function to extract the domain name (which would be email.com).

With the MID function, you can extract text from the middle of the text string. For example, if I want to extract the domain name between @ and the . (dot), I can easily do this using the MID function.

These text functions are not quite useful on their own, but when you combine them with other formulas such as LEN or FIND, you can do some incredibly advanced stuff with them.

Here are some advanced formula examples of using RIGHT/LEFT/MID functions:

• Remove Characters From Left in Excel (Easy Formulas)
• Extract Last Name in Excel (5 Easy Ways)
• How to Extract the First Word from a Text String in Excel (3 Easy Ways)
• How to Extract a Substring in Excel (Using TEXT Formulas)

REPLACE / SUBSTITUTE Function

REPLACE and SUBSTITUTE , as the name clearly suggests, allows you to find some text in a text free, and replace or substitute it with what you want.

With the REPLACE function, you can specify the starting position and the total number of characters that you want to replace with the specified text string.

On the other hand, with the SUBSTITUTE function, you can specify the text string that you want to replace with another text string. For example, if you have a data set where you want to replace the word ‘Private’ with ‘Pvt’, you can easily do that using the SUBSTITUTE function.

While these functions are quite useful on their own themselves, they’re often used with other text functions such as LEN, TRIM, or RIGHT/LEFT/MID

IMAGE Function

IMAGE is a brand new function in Microsoft 365 (at least at the time of writing this article).

For a long time, I wished Excel had a function that would allow me to use a URL and insert an image from that URL into a cell in Excel.

And finally, my wish has been granted.

With the IMAGE function, you can use a URL of an image within the cell, and it would insert the image into the cell itself.

Earlier, to place an image inside a cell , you had to manually add the image to the worksheet and then position it so that it fits in the cell.

While I don’t expect this function to be used by many average Excel users, some advanced Excel users can use these while creating dashboards.

For example, you can create a template, where the function fetches the image of the product or the logo of the company based on the URL.

At the risk of sounding disloyal to Excel, we need to thank Google Sheets for this. IMAGE function has been a part of Google Sheets for many years, and many Excel users (such as myself) I’ve been asking for it to be added to Excel well. So in the interest of humanity, Excel copied this function from Google Sheets (and yayy that they did)

SMALL / LARGE Function

SMALL and LARGE are two simple formulas that do one simple thing – give you the Kth smallest or largest value from a data set.

For example, I can use the SMALL function to give me the second smallest or the third smallest value from a range of cells.

And why include these two formulas as part of my best-advanced formula tutorial – because I have used them in situations where no other function could do what these could do.

For example, I have used these to manage outliers in my data set, use them with formulas that return an array (such as the INDEX function), and get the top three or top five values from it.

I agree that these two formulas have limited usage in advanced Excel formulas, but I would still urge you to keep them in your back pocket in case you need them.

SEQUENCE Function

SEQUENCE is also a new function that’s available in Excel 2021 and Excel with Microsoft 365.

SEQUENCE function would fill a range of cells with a sequence of numbers. you can specify how many rows and columns you want to fill, the starting number, and the step between each number.

For example, if I enter the below formula in cell A1, it is going to fill cells A1 to A10 with a sequence of numbers starting from 1 to 10.

While there were other methods to do this earlier in Excel (such as manually entering the data and then using the fill handle, or using the fill series method), having a formula do it makes it not only more convenient But necessary in some situations.

One advanced use case where this function could be useful is when you’re creating a calendar where you want to fill a grid of cells with a sequence of numbers.

You can check out the Excel calendar template I created using this function.

But more importantly, I’ve often come across situations where I needed a sequence of numbers as a part of my formula. Earlier, I had to rely on tricks such as using the ROWS or COLUMNS formulas, now I can easily do that using the SEQUENCE function.

WORKDAY / NETWORKDAYS Function

WORKDAY and NETWORKDAYS functions are amazing. When I started using Excel and I was learning these formulas, I thought of them as just another formula that could be useful in some situations.

But the more I have learned about these functions, the more I believe you need to know how to use them well to be called an advanced formula user.

At the outset, these formulas do simple stuff:

• WORKDAY – It calculates the date after the specified number of working days
• NETWORKDAYS – It total number of working days between two given dates

And since we have working days and non-working days in a week (as well as holidays), these functions automatically account for weekend days and you can also specify the holidays that would be ignored.

And since this was not enough, as many people had different weekend days than Saturday and Sunday, Excel released two add-on functions, WORKDAY.INTL and NETWORKD A YS.INTL .

These international functions allowed users to choose the days that would be considered weekends.

But wait, there is more.

What if you were working in a part-time job and you want to calculate data after a specific number of work days or the total number of work days between two given dates?

You can do that as well.

The international versions of these functions allow you to specify all the working days and not working days and then it will do the calculation based on it.

For example, you can specify that only Mondays and Thursdays are working for you and all the other days are non-working days.

TEXTAFTER / TEXTBEFORE Function

While Excel already had some really amazing text functions, they added TEXTBEFORE and TEXTAFTER in the Microsoft 365 version of Excel to make things even easier.

Earlier if you had to extract the first name from the full name, you had to use a combination of the LEFT function with the FIND function. But with the TEXTBEFORE function, you can easily extract all the text before a given specific delimiter.

Similarly, with TEXTAFTER, you can extract all the text after a given delimiter instead of using a combination of formulas.

I have recently started using these two functions and I can tell you that these are a great improvement over the existing text functions and simplifies things to a great extent.

In this tutorial, I have covered the top 20 Advanced Excel functions based on my understanding and years of experience in working with Excel.

While this list could be slightly different for you based on the kind of work you do, in general, if you master these 20 advanced Excel functions and how to use them in advanced formulas, you’ll be way ahead of the curve.

I hope this article has been useful to you.

Other Excel tutorials you may also find useful:

• FREE Online Excel Training (12+ Hours) | Learn Excel (Basic/Advanced)
• 10 Advanced Excel Charts that You Can Use In Your Day-to-day Work
• Excel LAMBDA Function
• Excel Advanced Filter – A Complete Guide with Examples
• How to Hide or Show Formula Bar in Excel?
• Remove Characters From Left in Excel
• Formula vs Function in Excel – What’s the Difference?

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Leave a Comment Cancel reply

BEST EXCEL TUTORIALS

Best Excel Shortcuts

Conditional Formatting

Excel Skills

Creating a Pivot Table

Excel Tables

INDEX- MATCH Combo

Creating a Drop Down List

Recording a Macro

© TrumpExcel.com – Free Online Excel Training

Privacy Policy  | Sitemap

Twitter | Facebook | YouTube | Pinterest | Linkedin

FREE EXCEL E-BOOK

What Is Copilot Pro and How Do You Use It?

Quick links, what is copilot pro, how to use copilot pro to generate ai images, how to use copilot pro in microsoft 365 apps.

• Copilot Pro offers GPT-4 priority access and integration with Microsoft 365 apps for $20/month.
• Copilot Pro also gives you access to DALL-E 3 and 100 boosts a day for image generation.
• You can utilize Copilot in Microsoft 365 to write content in Word, generate formulas in Excel, draft emails in Outlook, and plan presentations in PowerPoint.

Copilot Pro comes with extra features that make you more creative and productive. Let’s dive into what it is and how to use it to create images and do more in Microsoft 365 apps. Here’s what you need to know.

Copilot Pro is the premium version of Copilot with enhanced performance and more features. The subscription costs $20 per month, and you can subscribe from the Microsoft website.

In terms of enhanced performance, you get priority access to GPT-4, an advanced version of GPT-3 , during peak times. Peak times are when people use Copilot the most, leading to reduced performance and access for regular users.

The same goes for GPT-4 Turbo, which is faster and more advanced than GPT-4. To access it, you need to change Copilot's conversation style to "Creative" or "Precise" before typing in your prompts.

A notable added feature of Copilot Pro is integration with Microsoft 365 apps . You can use Copilot directly within apps like Office and Excel to be more productive.

When it comes to creativity, you have advanced image-creation tools like DALL-E 3 and 100 boosts per day. The former improves Copilot’s ability to understand content and context, leading to more impressive results. The latter are credits you spend on increasing the speed at which Copilot generates the images (free users get 15 per day).

You can also train your own GPT in Copilot Pro. That means you can create a GPT tailored to your needs by giving it a name, description, and set of instructions.

Generating an image using Copilot Pro is easy. Just open the Copilot website in your browser, and then describe the image you want it to create. For example, we asked it to create a cool scene where The Avengers and Men in Black fend off an alien invasion.

Since this is AI, the results will have some obvious issues that humans can easily avoid (Tony Stark being an MIB agent), which is why AI can’t replace human creativity.

To use Copilot Pro in Microsoft 365 apps, you’ll also need a Microsoft 365 subscription, which costs $6.99 per month. Also, make sure you’re using the latest version of the 365 apps. Afterward, you’re good to go.

How to Add Copilot to 365 Apps

When you open a Microsoft 365 app like Word or Excel, you should be able to see the “Copilot” button on the ribbon of the “Home” tab (except for Outlook).

If it’s grayed out, ensure the “AutoSave” toggle in the top-left corner is turned on. Copilot Pro requires that this feature be enabled.

If the Copilot button is still grayed out, updating the license can fix the issue. To do that, click “File” on the left side of the top menu.

Then, click “Account” in the bottom left corner.

Afterward, click “Update License” below Product Information in the right panel.

Click “Sign In” and follow the instructions to sign into your Microsoft account. Make sure it’s the one with the Microsoft 365 and Copilot Pro subscriptions.

You should now be able to see the “Copilot” button in the ribbon of the Home tab. Clicking it, will open a panel on the right where you can prompt Copilot.

Keep in mind that any AI-generated content might be incorrect, so always make sure to double-check it. If you want to find out which prompts you can use, click the “View Prompts” button—the book icon—at the bottom of the Copilot panel.

You’ll see that Copilot can help you with a lot of tasks. This includes summarizing the document, checking for specific information (i.e., deadlines and key points), or expanding certain sections.

Writing Documents in Word

In Word, Copilot can help you draft and rewrite content. To draft something with Copilot, select a new line and then click the Copilot icon on the left or press Alt+i.

In the prompt, type what you want Copilot to draft, and click "Generate" or press Enter.

After it generates the output, you can further tweak it from within the prompt by asking Copilot to, for instance, “make it longer” or “make it formal.” Then, click the right arrow button to regenerate Copilot’s response based on the tweaks you want.

Make sure to click “Keep It” —the blue button—if you like the output.

You can also rewrite text by highlighting it, clicking the Copilot icon on the left, and choosing “Rewrite with Copilot” in the menu.

Generate Formulas and Analyze Data in Excel

You can use Copilot to be productive in Excel by generating formulas. In our sample sheet, we have a Budget and Revenue column for each campaign. We can then prompt Copilot for the profit using these columns.

Copilot will create a new column based on your prompt. To see a preview of the output, hover your mouse pointer over the “Insert Column” button. If it’s accurate, click the button to add it to the sheet.

You can also analyze data in your sheet by typing something like: What is the average revenue of each campaign type as a pie chart? You can even insert the resulting chart into the document by clicking the “Insert to a New Sheet” button.

These are just a few examples of how to generate formulas and analyze data. You can even do some basic editing with Copilot, such as highlighting cells and sorting or filtering items within the sheet.

Creating Presentations in PowerPoint

Copilot is one of the tools that can help you plan a presentation. Click the “Copilot” button in the ribbon of the Home tab. Then, describe the presentation you want it to create. In our example, we typed: Create a presentation about procrastination, including what it is, what causes it, and how to overcome it.

Copilot will create the presentation, complete with content, images, and notes.

You can also prompt Copilot to add more slides by typing something like i nclude a slide about [topic] . You can also add an image by typing something such as a dd an image about [topic].

Drafting Emails in Outlook

You need to use the new Outlook or the web version instead of the classic Outlook if you want to be able to use Copilot. The “Copilot” button only shows up when you’re drafting or responding to an email. Once you click the “Copilot” button in the ribbon of the Message tab, select “Draft with Copilot” in the menu.

Tell Copilot what you want to draft and then click “Generate” in the bottom right of the prompt.

Then, you can choose to keep, discard, or regenerate the response. Alternatively, you can prompt Copilot further by clicking the text box that says “Anything You’d Like to Change?”

From there, you can, for instance, ask Copilot to “make it longer” or “make it sound more direct,” based on your needs.

Copilot Pro has even more advanced features that you can dig into, too. This includes things like enhanced code generation, collaboration tools, and the ability to create your own GPTs.

However, you should be able to use it now to be creative when creating images and being productive in Microsoft 365 apps. Keep in mind that Copilot cannot do everything a human does. In some cases, it might be faster and more accurate to do certain things manually.