Excel Spreadsheet Formula | How to use Spreadsheet Formula? (2023)

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (1)

Table of Contents

  • Spreadsheet Formulas in Excel
  • How to Use Spreadsheet Formulas in Excel?

Spreadsheet Formulas in Excel

A spreadsheet is full of formulas. Firstly don’t get confused with the spreadsheet and worksheet; both are the same. This article will talk about the most important formulas in excel and how do we use them in our day-to-day activities.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (2)

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)Price View Courses120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access 4.9 (60,568 ratings)

How to Use Spreadsheet Formulas in Excel?

Spreadsheet Formulas in Excel are very simple and easy to use. This is the guide to Formulas in Excel with Detailed Spreadsheet Formulas Examples. Let’s understand how to use Spreadsheet Formulas in Excel with some examples.

You can download this Spreadsheet Formulas Excel Template here –Spreadsheet Formulas Excel Template

Example #1

SUMPRODUCT Formula in Excel Spreadsheet

If we want to do unit price * unit sold calculation, we will do an individual calculation and finally add the total to get the total sales. For example, look at the below example.

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (11)

Firstly we calculate the total sales by multiplying Units Sold to Unit Price, as I have shown in the below image.

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (12)

Finally, we add the total of sales to get the final total sales amount.

(Video) Excel Formulas and Functions Tutorial

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (13)

This includes many steps to get the final sales amount. However, using the SUMPRODUCT function, we can get the total in a single formula itself.

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (14)

In the first array, select Units Sold range.

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (15)

We need to do the multiplication, so enter an asterisk (*) symbol and select Unit Price.

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (16)

Oh yes, we got our end result in a single formula in a single cell itself. How cool is it?

Example #2

Formula to SUM Top N Values in Excel Spreadsheet

We all work on the SUM function in Excel Spreadsheet in Excel day in day out in an Excel Spreadsheet; this is not a strange thing for us. But how do we sum the top 3 values or top 5 values, or top X values?

It looks like a new thing isn’t it? Yes, we can SUM Top N values from the list. For example, look at the below data.

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (17)

With the combination of SUM & LARGE function in an Excel Spreadsheet, we can sum the top n values from the list. The LARGE function helps us to find the top largest value.

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (18)

In order to find the top 3 values, I applied the SUM function formula for this SUM function. I have entered one more function, LARGE, to get the top N values. A LARGE function in Excel Spreadsheet can return only one largest in order to find the top N values. We need to supply the numbers in curly brackets ({). Now LARGE will return the top 3 largest values, and the SUM function will add these 3 numbers and give us the total.

Similarly, if I want the sum of the top 5 values from the list in the Excel Spreadsheet, I need to supply the numbers to 5 in curly brackets. Now LARGE will return the top 5 largest values, and the SUM function will add these 5 numbers and give the total.

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (19)

Example #3

SUMIF Formula with Operator Symbols in Excel Spreadsheet

You must have worked with the SUMIF function while using Excel Spreadsheet, and you are perfectly alright as well. But we can pass the criteria to the SUMIF function formula with operator symbol as well. For an example, look at the below example.

(Video) Top 10 Most Important Excel Formulas - Made Easy!

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (20)

We need to do the addition of all the values which are greater than 400. For sure, we need to use the SUMIF function, but in criteria, we need to supply an operator symbol greater than (>) and mention the criteria as 400.

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (21)

Yes, we need to supply the criteria operator symbol in double quotes (” “) and combine the other criteria with the ampersand (&) symbol. Now formula will read it as greater than 400 and returns the total.

I have marked all the values which are greater than 400 with yellow color; you can add those values manually you will get the same total as 2190.

Example #4

Table for All kind of Calculations in Excel Spreadsheet

Often we need to do the summation of values; often, we need the count, often the Average of the numbers, and many other things. How do you deal with all these requirements in a single formula?

Assume below is the data you have in your Excel Spreadsheet.

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (22)

Step 1: Convert this range to thetable by pressing Ctrl + T.

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (23)

Step 2: Place a cursor inside the table > go to Design > Under Table Style Options check the option Total Row.

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (24)

Step 3: Now, we have a total of the table row at the end of the table.

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (25)

Step 4: But if you observe, it is not the only formula; rather, it has a drop-down list. Click on the drop-down list to see what more has in it.

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (26)

Using this drop-down, we can use Average, Count, find the Max value, find the Min value, and many other things as well. Based on the selection we make from the drop-down, it will show the results.

Example #5

AVERAGEIF Formula in Excel Spreadsheet

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

AVERAGE function is not a strange thing for us. At the point of time, if we need the average of values, we would apply the AVERAGE function formula in Excel Spreadsheet and get the result. For example, if I need the average of the below numbers, I would apply the AVERAGE function.

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (27)

What if we need an Average only for the quarter Q1? How do we do? We can do with manual work, but we all hate manual work isn’t it?

Nothing to worry about; we have a function called AVERAGEIF in Excel Spreadsheet.

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (28)

The range is nothing but our Quarter Range, so select Quarter range from A2 to A19.

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (29)

Criteria are what you want to do from this range. Our criteria are Q1.

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (30)

The average Range is nothing but the sales column to select the Sales column and close the bracket. We will get Q1 Average Only.

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (31)

Example #6

IFERROR Formula to Avoid Errors in Excel Spreadsheet

I am sure you have encountered many errors while applying formulas. Often times we need to ignore these errors and clear these error values as per our own values. For example, look at the below illustrations.

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (32)

In the above image, we got error values. I want to convert all the error values to Not Found. Apply the below formula to get the result.

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (33)

If VLOOKUP returns an error values IFERROR convert this to Not Found text value.

Example #7

TEXT Function to Concatenate Date Values in Excel Spreadsheet

Using CONCATENATE function, we can combine many cell data into one. But this does not work a similar way if you are combining dates with text in Excel Spreadsheet. Look at the below image.

(Video) Excel Formulas Between Sheets

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (34)

When I combined cells A1 & A2, I got the result of these two cell values. However, a date is not in the correct format; in order to make this a perfect format, we need to use the TEXT function to apply our date format.

Below is the formula which can make this a proper sentence.

Example #8

TEXT Function to Concatenate Time Values in Excel Spreadsheet

Like how we got an incorrect format for date similarly for time format, also we get incorrect format.

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (36)

We need to pass the time cell with the TEXT function and convert it to “hh:mm:ss” format in Excel Spreadsheet.

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (37)

Things to Remember

  • Using CONCATENATE function, we can combine many cell data into one.
  • In order to make the Date Format correct, we need to use the TEXT function to apply our date format.
  • By using the SUMPRODUCT function, we can get the total in the single formula itself.

Recommended Articles

This has been a guide to Spreadsheet Formulas in Excel. Here we discussed different Spreadsheet formulas in Excel, How to use Spreadsheet Formulas in Excel, along with practical examples and downloadable excel template. You can also go through our other suggested articles-

  1. Advanced Formulas in Excel
  2. Excel Spreadsheet Examples
  3. Create Spreadsheet in Excel
  4. Worksheets in Excel

Excel Spreadsheet Formula | How to use Spreadsheet Formula? (38)

Popular Course in this category

Excel Advanced Training (16 Courses, 23+ Projects)16 Online Courses|23 Hands-on Projects |140+ Hours |Verifiable Certificate of Completion 4.8 Price View Course

Related Courses

Excel Training (23 Courses, 9+ Projects)4.9

FAQs

How can you use a spreadsheet to solve an absolute value equation? ›

We can use SUM ARRAY along with ABS to get the absolute value of a series of numbers in column or row. Suppose we are given a few numbers as below, so in this scenario, the SUM array formula for absolute values would be =SUM(ABS(A2:A6)). Now, select cell A7 in your spreadsheet, and enter the formula '=SUM(ABS(A2:A6))'.

Can you use a spreadsheet to calculate anything? ›

Instead of using a calculator, use Microsoft Excel to do the math! You can enter simple formulas to add, divide, multiply, and subtract two or more numeric values. Or use the AutoSum feature to quickly total a series of values without entering them manually in a formula.

What is the most useful formula in Excel? ›

Sum. The SUM function in Excel is among the most commonly used formulas. This formula adds the values ​​of the cells within it.

What is the most useful formula function in Excel? ›

7. VLOOKUP. The VLOOKUP function is one of the most commonly used and recognizable functions in Excel. It will look for a value in a table and return information from another column relating to that value.

How do you make a cell reference absolute in a formula in Excel? ›

If you want to maintain the original cell reference in this example when you copy it, you make the cell reference absolute by preceding the columns (B and C) and row (2) with a dollar sign ($). Then, when you copy the formula =$B$4*$C$4 from D4 to D5, the formula stays exactly the same.

Can you do absolute sum in Excel? ›

To sum absolute values in Excel, you can use the ABS function. This function returns the absolute value of a number, which is a number without its sign. So, if a number is positive, the ABS function will return the same number. If a number is negative, the ABS function will return the positive version of that number.

How do you absolute value a cell in Excel? ›

We can use SUM ARRAY along with ABS to get the absolute value of a series of numbers in column or row. Suppose we are given a few numbers as below, so in this scenario, the SUM array formula for absolute values would be =SUM(ABS(A2:A6)). Now, select cell A7 in your spreadsheet, and enter the formula '=SUM(ABS(A2:A6))'.

How do you create a formula for Excel? ›

For example, if cells A1 and A2 contain numbers, you can use the formula =PRODUCT(A1, A2) to multiply those two numbers together. You can also perform the same operation by using the multiply (*) mathematical operator; for example, =A1 * A2. The PRODUCT function is useful when you need to multiply many cells together.

What are the two ways to do calculations in a spreadsheet? ›

Answer:
  • type the equal symbol in the cell.
  • type the equation you want to calculate.
  • press the enter key to complete your calculation.
15 Jun 2020

Can a spreadsheet be used to document or list items or calculate formulas? ›

A spreadsheet is a file that exists of cells in rows and columns and can help arrange, calculate and sort data. Data in a spreadsheet can be numeric values, as well as text, formulas, references and functions.

Can spreadsheets be used for what if analysis? ›

By using What-If Analysis tools in Excel, you can use several different sets of values in one or more formulas to explore all the various results. For example, you can do What-If Analysis to build two budgets that each assumes a certain level of revenue.

What are the 10 formulas? ›

Class 10 Maths Formulas for Algebra And Quadratic Equations
  • (a+b)2 = a2 + b2 + 2ab.
  • (a-b)2 = a2 + b2 – 2ab.
  • (a+b) (a-b) = a2 – b. ...
  • (x + a)(x + b) = x2 + (a + b)x + ab.
  • (x + a)(x – b) = x2 + (a – b)x – ab.
  • (a + b)3 = a3 + b3 + 3ab(a + b)
  • (a – b)3 = a3 – b3 – 3ab(a – b)
  • (x – a)(x + b) = x2 + (b – a)x – ab.
19 Jul 2022

What are the 5 most commonly used functions in Excel? ›

5 Functions of Excel/Sheets That Every Professional Should Know
  • VLookup Formula.
  • Concatenate Formula.
  • Text to Columns.
  • Remove Duplicates.
  • Pivot Tables.
23 Jan 2019

What are the 5 powerful Excel functions that make work easier? ›

These 10 Excel Functions Can Make Your Work Easier
  • 1) PIVOT A TABLE TO SEE IT FROM A DIFFERENT PERSPECTIVE. ...
  • 2) REPEAT YOUR LAST ACTION WITH F4. ...
  • 3) COPY ONLY WHAT YOU WANT WITH PASTE SPECIAL. ...
  • 4) LOOKUP DATA WITH INDEX & MATCH. ...
  • 5) FIX MISTAKES EASILY WITH CTRL+Z & CTRL+Y. ...
  • 6) GET TO THE BOTTOM OF A TABLE FAST WITH CTRL+ARROW.

Which function most commonly used in the spreadsheet? ›

The main topics are arithmetic calculations and order of precedence; working with dates and times; some of the most commonly used functions (SUM, AVERAGE, MAX, MIN, IF); and named ranges. All ages are welcome. After word processing, spreadsheets are probably the most important and widespread computer application.

What are the 7 common uses for Excel? ›

The 7 Most Popular Uses Of Excel For Your Business
  • Data Entry and Storage. At its most basic level, Excel is an excellent tool for both data entry and storage. ...
  • Collection and Verification of Business Data. ...
  • Administrative and Managerial Duties. ...
  • Accounting and Budgeting. ...
  • Data Analysis. ...
  • Reporting + Visualizations. ...
  • Forecasting.

What are the 3 types of cell references in Excel? ›

Now there are three kinds of cell references that you can use in Excel: Relative Cell References. Absolute Cell References. Mixed Cell References.

How do you create a dynamic cell reference in Excel? ›

To create an Excel dynamic reference to any of the above named ranges, just enter its name in some cell, say G1, and refer to that cell from an Indirect formula =INDIRECT(G1) .

What is absolute cell referencing in Excel? ›

In contrast, the definition of absolute cell reference is one that does not change when it's moved, copied or filled. This way, the reference points back to the same cell, no matter where it appears in the workbook. It's indicated by a dollar sign in the column or row coordinate.

What does absolute sum mean? ›

Supposing you have a list of data which contains both positive numbers and negatives, and now you want to sum their absolute values which means all the negatives will be calculated as positives. For example, you want 10+(-10) would be 20 instead of 0.

How does Sumproduct work Excel? ›

The SUMPRODUCT function returns the sum of the products of corresponding ranges or arrays. The default operation is multiplication, but addition, subtraction, and division are also possible.
...
Remarks
  1. The array arguments must have the same dimensions. ...
  2. SUMPRODUCT treats non-numeric array entries as if they were zeros.

What is the shortcut for absolute value in Excel? ›

Fortunately, there's a handy shortcut you can use to calculate absolute values in Excel. To use the absolute value shortcut, simply select the cell or range of cells you want to calculate. Then, press the F4 key on your keyboard. This will automatically insert the absolute value symbol into your formula.

How do you find the absolute value of a set of data? ›

To find the mean absolute deviation of the data, start by finding the mean of the data set. Find the sum of the data values, and divide the sum by the number of data values. Find the absolute value of the difference between each data value and the mean: |data value – mean|.

What is the most difficult function in Excel? ›

  • The most complicated functions in Excel are date & time functions and formulas.
  • It is very difficult to learn these functions in the beginning. ...
  • They are used the most by a mis-executive in Excel. ...
  • And while learning them, we also have a lot of mistakes.
  • That's why date & time functions are the most complicated in Excel.

What are the 20 Excel functions? ›

Advanced Excel formula and functions
  • VLOOKUP. The function is used to look up for a piece of information in a large segment of data and pull that data to your newly formed table. ...
  • Sum Function. ...
  • MAX MIN function. ...
  • IF Function. ...
  • SUMIF Function. ...
  • COUNTIF Function. ...
  • AND Function. ...
  • OR function.

What is the fastest way to create a formula in Excel? ›

Fill formulas into adjacent cells

Select the cell with the formula and the adjacent cells you want to fill. Click Home > Fill, and choose either Down, Right, Up, or Left. Keyboard shortcut: You can also press Ctrl+D to fill the formula down in a column, or Ctrl+R to fill the formula to the right in a row.

What are two examples of formulas in a spreadsheet? ›

Formulas that you create in your Excel spreadsheets can be simple or complex: Simple Excel formulas perform just one mathematical operation, for example =10*5 or =SUM(A1:A10) Complex (advanced) Excel formulas include more than one calculation, for example =10*5+20 or =SUM(A1:A10)/2.

Which of the following is an example of a spreadsheet formula? ›

By far, the most frequently used spreadsheet program is Microsoft Excel, but other spreadsheet applications exist as well. Examples include: Lotus 1-2-3, Microsoft Works Spreadsheet, Open Office Calc and Google Drive Spreadsheet.

What are the three parts of a spreadsheet formula? ›

The parts of a function

The basic syntax for a function is an equals sign (=), the function name (SUM, for example), and one or more arguments. Arguments contain the information you want to calculate. The function in the example below would add the values of the cell range A1:A20.

What is the difference between a spreadsheet formula and a spreadsheet function? ›

What is a Formula vs Function in Excel? A Formula is an equation designed by a user in Excel, while a Function is a predefined calculation in the spreadsheet application.

Which data type can be used in calculations in a spreadsheet? ›

Number data, also called values, is used in calculations. By default, numbers are right-aligned in a cell. In addition to actual numbers, Excel also stores dates and times as numbers.

What is the difference between Excel and spreadsheet? ›

Microsoft Excel is a spreadsheet application that has been developed and maintained by Microsoft. Using Excel, you can perform a multitude of functions such as run calculations, make lists, and charts. It also helps analyze and sort information, track financial data, and much more.

How can you evaluate the accuracy of spreadsheets? ›

One useful way to think about the quality of a spreadsheet is to assess it along three dimensions. Specifically, a spreadsheet model should be correct, flexible, and documented. A spreadsheet model should produce the correct answer for the information given.

How do you automate what if analysis in Excel? ›

Record a macro
  1. Click Record Macro in the Developer.
  2. (Optional) Give a friendly name and click OK to start recording.
  3. Open the Goal Seek window by following Developer > What-If Analysis > Goal Seek.
  4. Enter your inputs: ...
  5. Click OK to run Goal Seek.
  6. Click OK on Goal Seek Status dialog.
8 Jul 2021

Are spreadsheets accurate? ›

Spreadsheet results can be unreliable when working with large datasets and/or performing complex calculations. If absolute accuracy is required, a spreadsheet may not suffice. Instead, a different, more reliably accurate tool should be considered.

What are 5 basic Excel functions? ›

5 Functions of Excel/Sheets That Every Professional Should Know
  • VLookup Formula.
  • Concatenate Formula.
  • Text to Columns.
  • Remove Duplicates.
  • Pivot Tables.
23 Jan 2019

What are the 5 basic Excel skills? ›

Five basic Excel skills to include in your resume
  • Data recording. Basic Excel users must know how to navigate and record data. ...
  • Formulas. There are some formulas you need to know for basic Excel skills: ...
  • Charts and graphs. ...
  • Data organization. ...
  • Pivot tables.

Where is Excel mostly used? ›

Excel is most commonly used in business settings. For example, it is used in business analysis, human resource management, operations management and performance reporting. Excel uses a large collection of cells formatted to organize and manipulate data and solve mathematical functions.

What cool things can Excel do? ›

Here are 15 fun Excel project ideas for you to try:
  • Attendance tracking project. ...
  • Daily expense tracking. ...
  • To-do list. ...
  • Beautiful artwork creation. ...
  • Game development in Excel. ...
  • Creating Sudoku puzzles. ...
  • Making web games. ...
  • Creating a 3D pendulum.

What are the 3 common uses for Excel? ›

The main uses of Excel include: Data entry. Data management. Accounting.

How many formulas are in Excel? ›

Excel has over 475 formulas in its Functions Library, from simple mathematics to very complex statistical, logical, and engineering tasks such as IF statements (one of our perennial favorite stories); AND, OR, NOT functions; COUNT, AVERAGE, and MIN/MAX.

What is formula give an example? ›

Formulas are equations that perform calculations on values in your sheet. All formulas begin with an equal sign (=). You can create a simple formula by using constant and calculation operator. For example, the formula =5+2*3, multiplies two numbers and then adds a number to the result.

What is the difference between formula and Function in Excel? ›

A Formula is an equation designed by a user in Excel, while a Function is a predefined calculation in the spreadsheet application.

What does '!' Mean in Excel formula? ›

When entered as the reference of a Named range , it refers to range on the sheet the named range is used on. For example, create a named range MyName refering to =SUM(!B1:!K1) Place a formula on Sheet1 =MyName . This will sum Sheet1! B1:K1.

How can I learn Excel quickly? ›

How to master Excel quickly in 11 steps
  1. How to navigate the interface. ...
  2. Learn some useful shortcuts. ...
  3. Freeze panes. ...
  4. Learn how to master Excel formulas. ...
  5. Create a simple drop-down list. ...
  6. Visualize key data with conditional formatting. ...
  7. Flash fill. ...
  8. Summarize data with PivotTables.

How can I practice Excel at home? ›

Popular
  1. Top 10 formulas and functions in Excel.
  2. Free Excel Online Exercises.
  3. Excel Basics – Zero to Hero.
  4. Vlookup – Tutorial with Example and Exercise Sheet.
  5. Pivot Tables Tutorial.
  6. Excel Shortcuts – Windows and Mac.
  7. SUM.

Videos

1. Excel Basic Formulas and Functions
(Excel Functions)
2. 15 Spreadsheet Formulas Working Professionals Should Know!
(Jeff Su)
3. Google Sheets Formulas Tutorial
(Kevin Stratvert)
4. Excel Tutorial - Use FORMULAS across worksheets
(LinkedIn Learning)
5. Excel Formulas and Functions | Full Course
(Kevin Stratvert)
6. Intermediate Excel Skills, Tips, and Tricks Tutorial
(Technology for Teachers and Students)
Top Articles
Latest Posts
Article information

Author: Terence Hammes MD

Last Updated: 03/22/2023

Views: 6126

Rating: 4.9 / 5 (49 voted)

Reviews: 88% of readers found this page helpful

Author information

Name: Terence Hammes MD

Birthday: 1992-04-11

Address: Suite 408 9446 Mercy Mews, West Roxie, CT 04904

Phone: +50312511349175

Job: Product Consulting Liaison

Hobby: Jogging, Motor sports, Nordic skating, Jigsaw puzzles, Bird watching, Nordic skating, Sculpting

Introduction: My name is Terence Hammes MD, I am a inexpensive, energetic, jolly, faithful, cheerful, proud, rich person who loves writing and wants to share my knowledge and understanding with you.