Inside The Web
    Facebook Twitter Instagram
    Inside The Web
    • Office
      • Excel
      • Ms Word
      • Gmail
    • Productivity
      • Time Management
      • Task Automation
    Inside The Web
    Home»Excel»How to Create or Use Formula in Excel

    How to Create or Use Formula in Excel

    AsmiBy AsmiMarch 14, 2023 Excel 5 Mins Read

    Formulas are the essence of using Excel. From simple arithmetic operations to manipulating arrays, you can use formulas to get almost everything done. You can use formulas in Excel to perform calculations, set a condition for formatting cells, and even create links between worksheets.

    With all the uses it offers, I think it’s a prerequisite for all Excel users to learn how to create and use formulas. If you’re just starting out with Excel, don’t sweat it. Formulas are pretty simple to figure out.

    In this article, we will be covering everything you need to know when creating and using formulas, including syntaxes, functions, arguments, and so much more!

    How to Create Or Use Formulas in Excel?

    Let’s get to the basics. When you enter a formula, you command Excel to perform a certain calculation. Instead of manually calculating values, you can create a function using a logic and let Excel perform the task for you.

    Formulas are either created using operators, with or without the use of functions. Operators include arithmetic, comparison, text, and reference operators.

    OperatorTypeDefinitionExample with Result
    +ArithmeticAdd=1+1=2
    –ArithmeticSubtract=2-3=-1
    /ArithmeticDivide=6/2=3
    %ArithmeticPercentage=20%–
    ^ArithmeticAssign an Exponent=4^2=16
    =ComparisonEqual to=1=1TRUE
    >ComparisonGreater than=1>2FALSE
    <ComparisonLess than=1<2TRUE
    >=ComparisonGreater than or equal to2>=3FALSE
    <=ComparisonLess than or equal to2<=3TRUE
    <>ComparisonNot equal to3<>3FALSE
    &TextAmpersand=“Lion” & “King”LionKing
    :ReferenceSpecify Range=A1:A4=A1,A2,A3,A4
    ,ReferenceComma.  Combine References in a function=SUM(1,2)=3
    (space)ReferenceIntersection Operator=SUM(A2:A3 A2:B3)=A2
    #ReferenceError/ Spilled Range• #NAME?
    •###
    •=SUM(J3#)
    –
    @ReferenceShow a Reference=SUM(Table2[@[ValueA]:[ValueZ]])–

    I would love to list the functions out just like I listed the operators, but that would take a whole new article! There are more than 300 functions in Excel that are categorized under Financial, Logical, Text, Date & Time, Lookup & Reference, Math & Trig, Statistical, Engineering, Cube, Information, Compatibility, and Web.

    Creating Formulas Using Only Operators

    Take, for example, you want to calculate the contents in cells A2, A3, and A4. I don’t know about you but I’m terrible are mental maths, so this would take me some time to count. However, I could use a simple formula to let Excel do it for me. In the formula bar, I entered the following formula:

    =A2+A3+A4

    Use operator to sum data in Excel

    Here, a task that would take me at least five minutes, is all done in about five seconds! Let’s take a closer look at this formula. See how I’ve started the formula with an equal to sign. When you start a value with an equal to (=) or a hyphen (-), Excel registers it as a formula.

    Next, I entered A2, A3, and A4 in my formula. These are cell references. Excel uses these references to fetch data that are entered into the cells. In my sheet, I have 123, 456, and 789 in cells A2, A3, and A4 respectively. The formula can access these values through the references I passed.

    The plus sign (+) that I used between the references is an arithmetic operator. This operator commands Excel to add up the values that are next to the operator.

    When you hit Enter, voila! There’s your value: 1368.

    Added Value Excel

    Creating Formulas Using Functions and Operators

    Operators are great however, they are quite limited. Say, you wanted to count the number of cells that hold a specific value. You could use the equals to (=) operator to check if the criteria are met but, how would you make your formula count the cell with the value? You would need to use a function; in this case, the COUNTIF function.

    As we have already mentioned, there are a lot of functions. As a new Excel user, I always found it a hassle looking for the exact function to perform a calculation in my worksheet. That was until I came across the Insert Function tool. This tool is a lifesaver for beginner-level Excel users that are just starting to use functions.

    The Insert Function allows you to look for a function by either describing it or looking it up by category. Not only that, but it also helps you while building the formula using arguments. 

    What I found most intriguing is that if the formula results in an error, the Insert Function tool will indicate the error next to the argument causing this error. This way, you won’t have to scratch your head around the formula for hours, trying to understand what argument is causing the error in Excel.

    Let me demonstrate the use of the Insert Function tool with an example.

    Excel Range

    Take a look at this spreadsheet. We will be counting cells that hold the value “Pending” in column F.

    1. On your keyboard, use Shift + F3. If you don’t like using shortcuts, you can head to the Formulas ribbon and select Insert Function.
      Insert Function tool
    2. In the Insert Function window, either describe or search by category:
    • Description: As our description, we entered “count cell”
      Describing function Insert Function
    • Search by category: Select Statistical.
      Select functioin by category Insert Function
    1. Under Select a function, choose COUNTIF > OK.
    2. On the next window, specify the arguments. For COUNTIF, we need to specify the range and enter the condition.
      Enter arguments in Insert Function window
    3. If you’re having issues with the function, go ahead and click on Help on this function for assistance.
    4. Click OK.

    The best part about using the Insert Function tool is that you leave no room for syntax errors. This is because Excel applies the syntaxes for you. I highly recommend using this tool while creating formulas with functions in Excel if you’re new to Excel.

    Excel Functions
    Asmi

      Asmi is a content writer specializing in MS Office. She loves writing Excel-related content to assist other users to learn their way around the program. She started using Excel when she first entered the industry as a marketing intern. Soon, she realized that learning the often-overlooked spreadsheet program was no piece of cake. In her articles, she attempts to brief out complex topics to help beginner to intermediate-level Excel users. Asmi uses Excel for everything, including creating a grocery list to managing her personal finance. She’s a bit dramatic when it comes to the Flash Fill feature; so much so that could write a eulogy out of it. When she’s not working, you’ll find her learning a new recipe, or cleaning up her mess (out of the failed recipe).

      Add A Comment
      Table of Contents
      • How to Create Or Use Formulas in Excel?
        • Creating Formulas Using Only Operators
        • Creating Formulas Using Functions and Operators
      Recent Posts
      • How to Lock a Cell in Excel Formula
      • How to Make Gridlines Darker in Excel
      • How to Apply Cell Style in Excel
      • How to Apply Top and Bottom Border in Excel
      • How to Retrieve Last Value in Column Excel
      • Home
      • Privacy Policy
      © 2023 Inside The Web

      Type above and press Enter to search. Press Esc to cancel.