{"id":768,"date":"2018-07-03T11:35:25","date_gmt":"2018-07-03T01:35:25","guid":{"rendered":"https:\/\/www.dynamicwebtraining.com.au\/blog\/?p=768"},"modified":"2024-11-14T16:23:09","modified_gmt":"2024-11-14T05:23:09","slug":"how-to-create-a-formula-in-excel-2016","status":"publish","type":"post","link":"https:\/\/www.dynamicwebtraining.com.au\/blog\/how-to-create-a-formula-in-excel-2016","title":{"rendered":"How to Create a Formula in Excel 2016"},"content":{"rendered":"\n

In case you are looking to maximize the power of Excel 2016, then you ought to explore its most powerful feature \u2013 The Ability to Calculate Numerical Using Formulas<\/em>.<\/p>\n\n\n\n\n\n\n\n

\n
\"How
Click image to enlarge <\/i><\/figcaption><\/figure>\n<\/div><\/div><\/div>\n\n\n\n

Yes, you heard it right!! Just like a calculator, Excel has the power to Subtract, Add, Divide and Multiply numerical to produce results. So, you no more need to get distracted from your work and look for a calculator when working on Excel. You can do it quickly on Excel with some simple tips and tricks.<\/p>\n\n\n\n

Talking of formulas, they are nothing but simple statements constructed of three important entities \u2013 Operands, Operator, and Result<\/em>. The operator is used to equate the two operands to produce a result as follows:<\/p>\n\n\n\n

\n

[Operand A] \u201cOperator\u201d [Operand B] = Result<\/p>\n<\/blockquote>\n\n\n\n

Working with Formula in Microsoft Excel <\/h2>\n\n\n\n

Which are the common Mathematical Operators in Excel?<\/h4>\n\n\n\n

Excel makes use of standard mathematical operators to calculate formulas. These operators can quickly be selected from the keyboard using shift.<\/p>\n\n\n\n

For Addition<\/span><\/b><\/td>Plus Sign (+)<\/span><\/td>Shift+(+)<\/span><\/td><\/tr>
For Subtraction<\/span><\/b><\/td>Minus Sign (-)<\/span><\/td>Shift+(-)<\/span><\/td><\/tr>
For Multiplication<\/span><\/b><\/td>Asterisk (*)<\/span><\/td>Shift+8<\/span><\/td><\/tr>
For Division<\/span><\/b><\/td>Forward Slash (\/)<\/span><\/td>Shift+(\/)<\/span><\/td><\/tr>
For Exponents<\/span><\/b><\/td>Caret (^)<\/span><\/td>Shift+6<\/span><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

An important thing to remember however when creating formulas in Excel is that, unlike the usual structure of formulas where equal sign is used at the END, every equation in Excel must compulsorily BEGIN<\/strong> with an EQUAL sign (=)<\/strong>. It is because, the cell is equal to, or holds, the resultant (value) of the formula it calculates.<\/p>\n\n\n\n

How to Refer a Cell In Excel When Creating Formulas?<\/h4>\n\n\n\n

While it is possible to create simple formulas in Microsoft Excel by using only numbers (Eg: = 6*6<\/strong> or = 10+3<\/strong>), if you have too many calculations to make, it is preferable to use cell addresses to create the formulas. Concerning Excel, this practice is called \u201cCell Reference<\/strong>.\u201d<\/p>\n\n\n\n

The best thing about using cell references is that the results of your formulas are accurate. It is because; cell reference allows you to change the value of the referenced cell, without the need to rewrite the equation.<\/p>\n\n\n\n

In the below-mentioned example, cell A5 multiplies the value of cells A1(4) and B1(5) by making cell references.<\/p>\n\n\n\n

\n
\"1
Click image to enlarge <\/i><\/figcaption><\/figure>\n<\/div><\/div><\/div>\n\n\n\n

To create the formula, all you need to do is to follow these simple steps:<\/p>\n\n\n\n

    \n
  • Click inside cell reference A5<\/strong><\/li>\n\n\n\n
  • Type the Equal sign (=)<\/strong> since, formulas in Excel always begin with this sign. Notice how the formula is also visible on the formula bar, allowing you to make modifications directly in formula bar.<\/li>\n\n\n\n
  • Select the cell you want to be referenced first in the formula (A1<\/strong> in this example).<\/li>\n\n\n\n
  • Type the operator you wish to use (Multiplication sign * in this example).To select * sign, click Shift+8<\/strong> on top row of keyboard.<\/li>\n\n\n\n
  • Next select the cell you want to be referenced second in the formula (B1<\/strong> in this example).<\/li>\n\n\n\n
  • As soon as you press Enter<\/strong>, Excel automatically calculates the equation and displays the result in cell A5<\/strong> as follows:<\/li>\n<\/ul>\n\n\n\n
    \n
    \"2
    Click image to enlarge <\/i><\/figcaption><\/figure>\n<\/div><\/div><\/div>\n\n\n\n

    A significant strength of Excel spreadsheets is that, if you change any of the values in the referenced cells (A1 or B1<\/strong>), Excel automatically recalculates the formula to display a new result in cell A5 as follows:<\/p>\n\n\n\n

    \n
    \"3
    Click image to enlarge <\/i><\/figcaption><\/figure>\n<\/div><\/div><\/div>\n\n\n\n

    Thus, by combining cell references with various available mathematical operators, you can quickly create a variety of formulas in Excel. You can also develop methods by using a combination of cell reference and numerical as below:<\/p>\n\n\n\n

    \n
    \"4
    Click image to enlarge <\/i><\/figcaption><\/figure>\n<\/div><\/div><\/div>\n\n\n\n
    \n
    \"5
    Click image to enlarge <\/i><\/figcaption><\/figure>\n<\/div><\/div><\/div>\n\n\n\n

    How to Copy Formula to Adjacent Cells<\/h4>\n\n\n\n

    Excel sheets can prove significantly handy when you have numerous calculations to make within a short span of time, and you don\u2019t want to compromise on the accuracy of results.<\/p>\n\n\n\n

    Another capability of Excel that can be useful is the ability to copy formulas to adjacent cells. In simple words, if you have various values to be calculated using the same formula, all you need to do is to use the fill handle<\/strong> feature, to copy the formula in adjacent cells. It can save a lot of extra efforts and time, otherwise spent in typing the same formula multiple times in the Excel sheet.<\/p>\n\n\n\n

    Steps for copying a formula using the fill handle<\/strong><\/p>\n\n\n\n

      \n
    • Choose the cell with the formula that you wish to copy in adjacent cells. (C2<\/strong> in this example)<\/li>\n<\/ul>\n\n\n\n
      \n
      \"1
      Click image to enlarge <\/i><\/figcaption><\/figure>\n<\/div><\/div><\/div>\n\n\n\n
        \n
      • Next, click and drag the fill handle<\/strong> (a small square at right bottom corner of selected cell) over the cells where you want to copy the same formula, without releasing the mouse.<\/li>\n<\/ul>\n\n\n\n
        \n
        \"2
        Click image to enlarge <\/i><\/figcaption><\/figure>\n<\/div><\/div><\/div>\n\n\n\n
          \n
        • Once you release the mouse, the same formula will be copied across all the selected cells as follows:<\/li>\n<\/ul>\n\n\n\n
          \n
          \"3
          Click image to enlarge <\/i><\/figcaption><\/figure>\n<\/div><\/div><\/div>\n\n\n\n
          \n
          \"4
          Click image to enlarge <\/i><\/figcaption><\/figure>\n<\/div><\/div><\/div>\n\n\n\n

          How To Edit or Modify A Formula?<\/h4>\n\n\n\n

          We have already seen how changing values in cell references, can automatically recalculate the formula and change the value in cell holding resulting value. However, sometimes you may feel the need to modify the formula itself, in case you have selected an incorrect cell address while creating the equation.<\/p>\n\n\n\n

            \n
          • Select the cell that contains the equation that you wish to edit. In this example, consider changing the formula in reference cell C9<\/strong>.<\/li>\n<\/ul>\n\n\n\n
            \n
            \"1
            Click image to enlarge <\/i><\/figcaption><\/figure>\n<\/div><\/div><\/div>\n\n\n\n
              \n
            • Just click the formula bar<\/strong> to view and edit the formula. A colored border corresponding with the colors in the formula bar will automatically appear around the referenced cells.<\/li>\n<\/ul>\n\n\n\n
              \n
              \"2
              Click image to enlarge <\/i><\/figcaption><\/figure>\n<\/div><\/div><\/div>\n\n\n\n
                \n
              • In this example, we will change the third value, reference cell B4 to B2<\/strong> in the formula. You can see the automatic removal of the colored border from reference cell B4<\/strong>.<\/li>\n<\/ul>\n\n\n\n
                \n
                \"3
                Click image to enlarge <\/i><\/figcaption><\/figure>\n<\/div><\/div><\/div>\n\n\n\n
                  \n
                • Alternatively, to view and edit the formula directly within the cell (C9), double-click the cell<\/strong>.<\/li>\n<\/ul>\n\n\n\n
                  \n
                  \"4
                  Click image to enlarge <\/i><\/figcaption><\/figure>\n<\/div><\/div><\/div>\n\n\n\n
                    \n
                  • Once you modified the formula, press Enter<\/strong>, to recalculate the equation and display the new result in reference cell C9<\/strong>.<\/li>\n<\/ul>\n\n\n\n
                    \n
                    \"5
                    Click image to enlarge <\/i><\/figcaption><\/figure>\n<\/div><\/div><\/div>\n\n\n\n

                    Thus, creating and modifying formulas in Excel can be a child\u2019s play, once you have thoroughly understood the essential tips and tricks around these formulas.<\/p>\n\n\n\n

                    Few Quick Tricks to Remember<\/h4>\n\n\n\n

                    1.<\/strong> In case you accidentally make changes to your formula and are worried about your calculations getting wrong, all you need to do is to press the Esc key<\/strong> on your keyboard or click the Cancel<\/strong> command visible in the formula bar.<\/p>\n\n\n\n

                    \n
                    \"1
                    Click image to enlarge <\/i><\/figcaption><\/figure>\n<\/div><\/div><\/div>\n\n\n\n

                    2.<\/strong> If you wish to see all the formulas you have used in your Excel sheet, merely click Ctrl+` (grave accent key)<\/strong>. This key is on the top left corner of your keyboard just below the Esc key. To switch back to normal view, press Ctrl+`<\/strong> again. So, next time you face the challenge of making too many calculations in your Excel 2016, DO NOT<\/strong> panic, as Excel has the power to solve your issues within minutes!!!<\/p>\n\n\n\n

                    \n
                    \"2
                    Click image to enlarge <\/i><\/figcaption><\/figure>\n<\/div><\/div><\/div>\n","protected":false},"excerpt":{"rendered":"

                    In case you are looking to maximize the power of Excel 2016, then you ought to explore its most powerful feature \u2013 The Ability to Calculate Numerical Using Formulas.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-768","post","type-post","status-publish","format-standard","hentry","category-microsoft-excel"],"yoast_head":"How to Create a Formula in Excel 2016 - Dynamic Web Training<\/title>\n<meta name=\"description\" content=\"View and Share Dynamic Web Training Blog Archives. This is a great source of articles and posts on Computer and IT training, tutorials and insights\" \/>\n<meta name=\"robots\" content=\"index, follow\" \/>\n<meta name=\"googlebot\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<meta name=\"bingbot\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dynamicwebtraining.com.au\/blog\/how-to-create-a-formula-in-excel-2016\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Create a Formula in Excel 2016 - Dynamic Web Training\" \/>\n<meta property=\"og:description\" content=\"Maximize the power of Excel 2016 with its most powerful feature \u2013 Learn to Calculate Numerical Using Formulas in Excel 2016.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dynamicwebtraining.com.au\/blog\/how-to-create-a-formula-in-excel-2016\" \/>\n<meta property=\"og:site_name\" content=\"Dynamic Web Training Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DynamicWebTraining\/\" \/>\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/DynamicWebTraining\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-07-03T01:35:25+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-11-14T05:23:09+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-content\/uploads\/2018\/07\/how-to-create-a-formula-in-excel-2016.jpg\" \/>\n<meta name=\"twitter:card\" content=\"summary\" \/>\n<meta name=\"twitter:creator\" content=\"@dynamicwebtrain\" \/>\n<meta name=\"twitter:site\" content=\"@dynamicwebtrain\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#organization\",\"name\":\"Dynamic Web Training\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/\",\"sameAs\":[\"https:\/\/www.facebook.com\/DynamicWebTraining\/\",\"https:\/\/www.linkedin.com\/company\/dynamic-web-training\",\"https:\/\/twitter.com\/dynamicwebtrain\"],\"logo\":{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#logo\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-content\/uploads\/2016\/02\/logo.png\",\"width\":361,\"height\":109,\"caption\":\"Dynamic Web Training\"},\"image\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#logo\"}},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#website\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/\",\"name\":\"Dynamic Web Training Blog\",\"description\":\"The Ultimate Training Experience.\",\"publisher\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/?s={search_term_string}\",\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/how-to-create-a-formula-in-excel-2016#primaryimage\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-content\/uploads\/2018\/07\/how-to-create-a-formula-in-excel-2016.jpg\",\"width\":1200,\"height\":628},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/how-to-create-a-formula-in-excel-2016#webpage\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/how-to-create-a-formula-in-excel-2016\",\"name\":\"How to Create a Formula in Excel 2016 - Dynamic Web Training\",\"isPartOf\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/how-to-create-a-formula-in-excel-2016#primaryimage\"},\"datePublished\":\"2018-07-03T01:35:25+00:00\",\"dateModified\":\"2024-11-14T05:23:09+00:00\",\"description\":\"Maximize the power of Excel 2016 with its most powerful feature \\u2013 Learn to Calculate Numerical Using Formulas in Excel 2016.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/how-to-create-a-formula-in-excel-2016#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dynamicwebtraining.com.au\/blog\/how-to-create-a-formula-in-excel-2016\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/how-to-create-a-formula-in-excel-2016#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"item\":{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\",\"name\":\"Training Blog\"}},{\"@type\":\"ListItem\",\"position\":2,\"item\":{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/category\/training\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/category\/training\",\"name\":\"Training\"}},{\"@type\":\"ListItem\",\"position\":3,\"item\":{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/category\/training\/microsoft-excel\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/category\/training\/microsoft-excel\",\"name\":\"Microsoft Excel\"}},{\"@type\":\"ListItem\",\"position\":4,\"item\":{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/how-to-create-a-formula-in-excel-2016\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/how-to-create-a-formula-in-excel-2016\",\"name\":\"How to Create a Formula in Excel 2016\"}}]},{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/how-to-create-a-formula-in-excel-2016#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/how-to-create-a-formula-in-excel-2016#webpage\"},\"author\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#\/schema\/person\/c94653aed4a6decc8e357af0a1082233\"},\"headline\":\"How to Create a Formula in Excel 2016\",\"datePublished\":\"2018-07-03T01:35:25+00:00\",\"dateModified\":\"2024-11-14T05:23:09+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/how-to-create-a-formula-in-excel-2016#webpage\"},\"publisher\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/how-to-create-a-formula-in-excel-2016#primaryimage\"},\"articleSection\":\"Microsoft Excel\",\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#\/schema\/person\/c94653aed4a6decc8e357af0a1082233\",\"name\":\"Dynamic Web Training\",\"image\":{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#personlogo\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0a14e92e62ad4eee0843f5cf7da3a00e1df4c9763922d4d20ba3ed2402a6896d?s=96&d=mm&r=g\",\"caption\":\"Dynamic Web Training\"},\"description\":\"Dynamic Web Training is Australia's leading provider of instructor led software training. We offer training courses in Adobe, Web Design, Graphic Design, Photoshop, InDesign, Dreamweaver and many more.\",\"sameAs\":[\"https:\/\/www.facebook.com\/DynamicWebTraining\/\",\"https:\/\/www.linkedin.com\/company\/dynamic-web-training\",\"https:\/\/twitter.com\/dynamicwebtrain\"]}]}<\/script>","_links":{"self":[{"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/posts\/768","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/comments?post=768"}],"version-history":[{"count":15,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/posts\/768\/revisions"}],"predecessor-version":[{"id":1904,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/posts\/768\/revisions\/1904"}],"wp:attachment":[{"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/media?parent=768"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/categories?post=768"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/tags?post=768"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}