Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Adobe Photoshop Elements 11 – 12 Hours Video Training Course
        • Adobe Photoshop Elements 10
        • Adobe Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • PowerPoint
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010 – 9 Hours Video Training
      • Windows
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Pro 2021
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Introduction to Python
      • Java
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
    • Data Analysis
      • Introduction to Alteryx
      • Power BI
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Sign Up
Home > Microsoft Excel > How to Make a Formula in Excel – Excel Basics

How to Make a Formula in Excel – Excel Basics

This How to Make a Formula in Excel tutorial is suitable for users of Excel 2013/2016/2019 and Microsoft 365.  

OBJECTIVE 

Explore Excel functions and create simple formulas from scratch to perform calculations.  

FUNCTIONS AND FORMULAS EXPLAINED 

If Excel is known for one thing, it’s formulas. Being able to perform simple to very complex calculations by learning how to make a formula in Excel is critical for beginners to understand.  

There are over 450 functions in Excel that can be used independently or in combination with other functions to construct simple to complex formulas. Functions are stored in the Functions Library on the Formulas tab, organized by category.  

Formulas can be used to add up a list of numbers, find the average of values, count items in a list, make decisions, lookup information and format data. However, there is so much more too them than this.  

It can seem intimidating to dive into the world of formulas if you are new to Excel. There are so many that it can seem overwhelming to learn how they work and which will be best for the task. This is something that will come over time with continued practice. However, understanding the basics of how a formula is constructed will give you a great foundation.  

HOW TO MAKE A FORMULA – BASIC TERMINOLOGY 

First, let’s understand the difference between Formulas and Functions.  

Formulas A formula is an expression that performs calculations on values in a cell or range of cells. For example,  =A1+A2+A3+A4, which finds the sum of the cell range values A1 to A4.   
Functions  Functions are predefined formulas in Excel. There are 450 functions in Excel. They eliminate the need for the manual entry of formulas.  The calculation listed above expressed with a function looks like this: =SUM(A1:A4). The function sums the values in cells A1 to A4. This is a more concise way of writing a formula.   

OPERATORS IN EXCEL

Formulas perform calculations. Much like calculations you used to do in math class, you need to understand how operators work in Excel. An operator is a symbol that denotes if you are adding, subtracting, dividing, or multiplying, amongst other things.  

There are many different operators in Excel, so I will highlight the most important ones.  

To see a full list visit the Microsoft Support site.  

Operator Meaning Example 
+ Addition =10+5 
– Subtraction =10-5 
* Multiplication =10*5 
/ Division =10/5 
> Greater Than =A1>B1 
< Less Than =A1<B1 
: Reference to all the cells between to cell references =A1:D3 

INSERTING FORMULAS INTO CELLS 

All formulas must begin with an equals (=) sign. That is how Excel knows you want to perform a calculation. Formulas can be typed directly into the cell or in the Formulas Bar.  

USING CONSTANTS 

In this example, I want to add up the figures for Jan, Feb, and Mar. I could do this using constants, as in, typing the values directly into the cell.  

This calculation will produce the correct answer, but it has several drawbacks. 

If you are trying to sum lots of numbers, this formula could get very long and difficult to manage. Also, if any of the figures change, the formula would need to be edited.  

A much more efficient way of performing this calculation is to use cell references.  

USING CELL REFERENCES 

In this example, I have replaced constant numbers with cell references. These are the cells that contain the values we want to calculate.  

The advantage here is that if any of the figures change, we refer to the cell instead of the actual value so that the result will update automatically.   

However, we still have the same problem. If we want to add up many values, this formula will get very long and unwieldy.  

A much better way of doing this is to use the SUM function.  

USING FUNCTIONS 

In the below example, we are adding up the figures in cells B1 to B3 using the SUM function in the cell.  

Alternatively, you can type the formula into the Formulas Bar and either click the tick or press Enter to confirm.  

INSERTING FORMULAS USING THE FUNCTIONS LIBRARY 

If you don’t feel confident entering formulas directly into a cell, you can use the functions dialog box instead.  

  1. Click on the Formulas tab. 
  1. Click Insert Function or press the keyboard shortcut Shift+F3. 
  1. Search for the function you want to use by typing it into the search box, click Go. 
  1. Select the function you want to use.  
  1. Click OK. 
  1. Enter in the cell range you want to sum.  
  1. Click OK. 

USING AUTOSUM 

A quick way of performing a sum calculation is to use the AutoSum button.  

  1. Click the Home tab.  
  1. From the Editing group, click the AutoSum button. Alternatively, you can press the keyboard shortcut ALT+= 
  1. Press Enter to accept the calculation.  

THE BODMAS PRINCIPLE (BIDMAS/PEDMAS) 

The BODMAS principle defines the order in which formulas are calculated.  

NOTE: BODMAS and BIDMAS are interchangeable names for this rule in the UK. If you are in the US, you may hear this rule referred to as PEDMAS. I learned BODMAS, so that I will be using this in my examples.  

Consider the following calculation: 

=2+2*10  

Without any order of calculation specified, you might assume that you calculate from left to right. In this case, 2+2 = 4, then multiply by 10 to give the result of 40. But this calculation could also be calculated as 2, then add 2 multiplied by 10 to give the result of 22. In its current form, there are two possible answers to this calculation.  

We use the BODMAS rule to tell Excel what we want to calculate first.  

  • Excel will calculate whatever is in brackets (parenthesis) first.  
  • Next, it will calculate the orders of, square roots, indices, etc.  
  • It will then calculate divisions or multiplications. If both appear in a formula, it will calculate from left to right.  
  • Lastly, it will perform any addition or subtractions. If both appear in a formula, it will calculate from left to right.  

Let’s modify our formula and add in some brackets (parenthesis).  

=(2+2)*10  

This time Excel will calculate what’s in the brackets first and then do the multiplication. Written out like this, the answer is 40.  

=2+(2*10) 

If we move the brackets, we get the answer of 22.  

In a more complex calculation where there are two or more pairs of brackets, Excel will calculate the brackets first from left to right.  

=(A4+20)/SUM(D5:F5) 

So, A4+20 will be calculated first, and then Excel will work out the sum of the values in D5 to F5. Finally, it will divide the result of the sum by the result of A4+20.  

In calculations where no brackets are specified, the BODMAS rule will be invoked.  

=5+10/2*4 

So, according to BODMAS, additions, and subtractions are done at the end. As we also have both a division and a multiplication in this formula, we work from left to right.  

First, we calculate 10 divided by 2 to give the result of 5. Then we multiply by 4 to get 20. Finally, we add 5 to give a final result of 25.  

We could have made this simpler, by adding brackets.  

=5+(10/2)*4 

OTHER BASIC FORMULAS 

Let’s take a look at some other useful, basic formulas in Excel.  

AVERAGE  The AVERAGE formula calculates the average of the select cell range.   
MAX  The MAX formula displays the maximum value in a cell range.  
MIN  The MIN formula displays the minimum value in a cell range.  
COUNT  The COUNT formula counts the number of items in a selected cell range. 
LOGICAL  A formula that uses logical operators (>,<,=) will display TRUE or FALSE depending on the logical test result. In this example, I am testing if the value in cell B2 is greater than 25. It is, so the result is TRUE.
IF  IF formulas are part of the logical functions group. They perform a logical test and then produce one result if the test is TRUE and one result of the test is FALSE. In this example, I am testing if the value in cell B2 is greater than 25. If it is, then the text ‘Yes’ will be returned. If it isn’t, then the text ‘No’ will be returned’.   

COMBINING FUNCTIONS 

In the following example of a more complex calculation, I am combining two functions: SUM and COUNT. Excel works this formula out as follows: 

First, if performs a COUNT of cells B1:B3, to give the result of 3.  

It then SUM’s cells B1 to B3, to give the result of 60. 

Finally, it multiplies the results of the COUNT by the result of the SUM. So, 3 multiplied by 60 to give the result of 180.  

Another important point to note when working with formulas is that you must always close as many brackets (parenthesis) as you open. If you forget, Excel will prompt you with a warning message and make the correction for you.  

In the above screenshot, notice I have two closing brackets at the end of the formula. The first closes off the B1:B3 argument, and the second closes off the COUNT function.  

ABSOLUTE REFERNCING 

It’s vital to understand the difference between absolute and relative referencing when working with basic formulas in Excel.  

By default, all formulas are calculated using relative referencing.  

In the example, I have used the SUM formula to multiply the Sales Amount ($) by the Tax Rate to get the total tax payable for each fruit. I have then used the AutoFill handle to copy the formula down for the rest of the fruits.  

Notice that as I drag the formula down by default, Excel modifies the cell references, so the calculation for each fruit is correct. This is called relative referencing.   

However, as the tax rate is the same each time, the Tax Rate column takes up space. I could put the tax rate in a separate cell on its own and use absolute referencing instead. 

In this example, I have deleted the tax rate column and put the tax rate in cell G11. As I want the formula to always refer to the tax rate in cell G11, I have fixed the cell reference using a $ sign in front of the row and the column. You can also use the keyboard shortcut F4 to fix cell references.  

As I drag the SUM formula down, the cell reference for the sales will change because I have left that as relative, whereas the cell reference that holds the tax rate will be the same each time I have fixed it (absolute).  

These are just a few examples of how to make a formula in Excel. The skills learned here give you a great foundation from which to explore the wonderful world of formulas. If you would like to read more about basic formulas, please check out the following links:

  • VLOOKUP for Dummies
  • How to use the SUMIF Function
  • Ten Ways to Clean Data in Excel

Take a look at the full range of Excel courses from Simon Sez IT

Most Popular Posts

  • How To Use Microsoft To Do: Microsoft To Do Tutorial
  • Microsoft Teams Tutorial – Getting Started.
  • HLOOKUP in Excel
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15
  • How to Create Charts and Graphs in Microsoft Excel 2016

Join our list to receive 4 Free Courses!

Simon Sez Business Banner

Similar Posts

How to Use Excel 2010 Pivot Tables and Charts – Part 4

Creating a Company File in QuickBooks Pro 2013

Advanced PivotTables – 1 Hour Crash Course

Microsoft Teams Tutorial – Getting Started.

How to Record a Bank Transfer in QuickBooks 2019

How to Enter Bills for Vendors in QuickBooks 2019

Course Categories

  • Web Development
  • QuickBooks
  • Microsoft
  • Adobe
  • Data Analysis

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebook
© 2021 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)