How to Simplify Your Calculations in Excel Using Array Formulas

Updated on October 7, 2016

Perform Your Calculations in One Step and Lose the Extra Columns.

If you do a lot of number crunching in Excel that involve arrays and hate the thought of having to create extra columns of numbers to display intermediate results, then you have come to the right place. As it turns out, there is a simpler and more elegant way to carry out multi-step calculations involving arrays. Excel has long enabled the use of user-designed formulas that can accept arrays as arguments, although these features still remain relatively unknown except to intermediate and advanced users. Known as array formulas, they are also sometimes referred to as CSE formulas, because in order for them to be invoked, the user must hit CTRL SHIFT ENTER (rather than the usual ENTER) after entering the formula. Depending on the types of calculations you need to perform, an array formula may either reside in one cell or in more than one if the results consist of more than one value.

As will be shown below, array formulas enable you to perform, in fewer steps, many different types of calculations not covered by Excel's list of native functions. If this is your first time using array formulas and you want to get familiar with their use, this introductory tutorial will walk you through the basic steps of performing some simple one step calculations using single-cell array formulas. Also, this article illustrates ways to construct your own array formulas using some simple examples.

Have you ever heard of array formulas in Excel?

See results

Step One: Check Excel's List of Native Functions.

Array formulas enable you to bypass the process of having to create extra columns of intermediate results in your workbook so that you can get at the final result of your calculations more directly. They not only make it easier to organize your workbook but also cut down on its size. There is a tradeoff, however, since the information in (what used to be) those extra columns is stored temporarily in Excel's memory. For less powerful computers, array formulas can sometimes result in a substantial performance hit. This is more problematic if you work with very large data sets or use many array formulas and have your workbook set for automatic updates.

Therefore, the first step is to double check Excel's list of native functions to see if there is already one that can perform the calculations you have in mind. One way to do this is to check the functions lists within the "Insert Function" dialog window, as illustrated above. A native function will not only save you the extra work of typing in a custom-designed array formula, but it will most likely execute faster. For example, let's say you want to calculate the sum of squares of a column of numbers. In the old days (ca. 1990s), you would have had to create an extra column of numbers consisting of the squares of each number in your original data set, then use these new values as your array argument in Excel's SUM function. In later versions of Excel, the SUMSQ function finally became available so that you no longer have to perform this extra step.

Although later versions of Excel are increasingly incorporating more native functions of greater complexity that accept arrays as arguments, they cannot possibly anticipate all prospective user needs. For example, if you want to perform the sum of cubes, or the sum of some higher-order polynomial with more than one term, Excel's native functions do not, as yet, have these capabilities. For situations such as these, array formulas are pretty much the only viable alternative.

For Steps 2-4, I will use as an example taking the sum of cubes in an array since for Excel 2007, the version I will be using throughout this tutorial, a native function is not yet available.

Step Two: Choose the Range of Data on Which You Want to Perform Your Calculations.

In an array formula, you can either enter your data array as a range (e.g., A2:A7), or you can give it a name and use that as the argument.

When I use array formulas, my personal preference is to give my data array a name, which is the method I will be using throughout this tutorial. When you do it this way, if you should ever have to change the number of rows or columns, all you would have to do is make these changes only once within "Name Manager," rather than having to retype and re-enter the array formula. (This also minimizes the possibility of introducing errors during editing, especially for more complicated array formulas in which the data range may appear as an argument more than once.)

To name your data array,

  1. Select the array of data on which you want to perform your calculations.
  2. Click on the Formulas Tab at the top.
  3. Within the "Defined Names" area, click on "Define Name."
  4. In the dialog window, enter your array name (e.g., MyArray).

Note: The above instructions apply to Excel 2007, the version used for this tutorial, and may differ slightly from one version of Excel to the next.

Step Three - Enter Your Array Formula.

Here, we will be computing the sum of cubes of the numbers in the data array, A2:A7, which I have named MyArray (see Step Two above). The array formula calculations shown here make use of the Excel native functions, SUM and POWER.

  1. Select the cell in which you want your array formula to reside. (This is where the final result will be displayed.)
  2. Enter the equals sign (=).
  3. Right after the = sign, enter your formula in the formula bar. Since we are computing the sum of cubes for each element in MyArray, enter the following: SUM(POWER(MyArray,3))
  4. Press the CTRL+SHIFT+ENTER buttons simultaneously. You should see the results in the cell in which you entered your array formula (shown above outlined in red). You should also see curly brackets around the formula you just entered in the formula bar as shown in the screenshot above.

Step Four: Test Your Array Formula.

  1. Click on the cell in which you entered your array formula and verify that it is surrounded by curly brackets in the formula bar.
  2. Make some changes in one or more cells within your range and verify that the correct final answer is displayed in the cell in which you entered your array formula.

As shown in the above screenshot, by increasing the value of A7 from 6 to 60, the sum of cubes, as shown in Cell B2, updates correctly from 441 (the original value shown above in Step Three) to 216225.

A Warning

If you forget to press CTRL+SHIFT+ENTER and accidentally press ENTER instead, you will either get an error message or, worse, erroneous results, depending on your version of Excel. Since it is easy to forget, always double check the formula bar for those curly brackets!

How Do I Compute the Total Number of Characters in a Collection of Cells?

For this example, the data reside in Cells A1-A6 (see screenshot), so MyArray = A1:A6. The array formula for computing the total number of characters in these cells (and the final result) reside in Cell B1 (outlined in red).

The number of characters for any string residing in cell A1 is easily found from the LEN function, i.e.,

LEN(A1)

To find the total number of characters for each member of an array, substitute the array name or range for "A1" in the formula above and use it as the argument for the SUM function, i.e.,

SUM(LEN(MyArray))

Entering the above as an array formula and you should see the following in the formula bar:

{=SUM(LEN(MyArray))}

How Do I Determine the Total Number of Words in a Collection of Cells With Strings?

For this example, the data reside in Cells C1-C3, so MyArray = C1:C3. The array formula for computing the total number of words in these three cells (and the final result) reside in Cell D1 (outlined in red).

For a single cell, C1, the number of words can be found by counting the number of times the space character occurs and adding 1, i.e.,

LEN(C1)-LEN(SUBSTITUTE(C1," ",""))+1

To find the total number of words for each cell in an array, substitute the array name or range for "C1" in the formula above and use that as the argument for the SUM function, i.e.,

SUM(LEN(MyArray)-LEN(SUBSTITUTE(MyArray," ",""))+1)

Entering the above as an array formula using CTRL+SHIFT+ENTER, you should see the following in the formula bar (as shown in the screenshot above):

{=SUM(LEN(MyArray)-LEN(SUBSTITUTE(MyArray," ",""))+1)}

I Cannot Enter Anything in the Formula Bar Because Excel's "Insert Function" Dialog Box Keeps Getting in the Way!

Do not click on the "Insert Function" icon to the left of the formula bar (shown here crossed out in red). Instead, select the cell in which you want your formula to reside and type in an equals sign (=).

The equals sign will then appear in the formula bar, so that you can type in whatever formula you wish.

Questions & Answers

    Do you think Excel array formulas could be useful in your work?

      0 of 8192 characters used
      Post Comment

      • profile image

        othellos 

        5 years ago

        Very good lens on Excel formulas. Your suggested method is very helpful to me since I use formulas a lot in my work. I never thought of doing them this way. Nice tips. Thanks a lot:=)

      • SusanRDavis profile image

        Susan R. Davis 

        5 years ago from Vancouver

        Yes, and I appreciate the great tutorial.

      • JaneEyre9999 profile image

        JaneEyre9999 

        6 years ago

        Yes! Thank you, no matter how long we have used spreadsheet software we all get stuck in our old "tricks".

      • nhotdeals profile image

        nhotdeals 

        6 years ago

        The hardest one are offset and indirect. Bill Jelen had the best Excel books

      • profile image

        anonymous 

        6 years ago

        All Excel users must love this lens and you.. dear Mary Stuart :) Another gorgeous and helpful work of you. 5 stars. Have wonderful times.. always.. dear Mary :D

      • artbyrodriguez profile image

        Beverly Rodriguez 

        6 years ago from Albany New York

        Very well done and thorough article!

      • SophiaStar LM profile image

        SophiaStar LM 

        6 years ago

        I am going to have to keep this handy, Excel is not my strongest point! Thank you for these great tips!

      • SoniaCarew profile image

        SoniaCarew 

        6 years ago

        Thank you! This is easily my favorite lens because I don't like Excel but I have to use it on a daily basis. This lens is going to help me so much.... Thank you, thank you!

      • profile image

        anonymous 

        6 years ago

        This is good I had a friend talking to me yesterday about Excel

      • ItayaLightbourne profile image

        Itaya Lightbourne 

        6 years ago from Topeka, KS

        Great article and will definitely come in handy! :)

      • SusanDeppner profile image

        Susan Deppner 

        6 years ago from Arkansas USA

        Looks like very complete information!

      • Scriber1 LM profile image

        Scriber1 LM 

        6 years ago

        Yes, I work with Excel often and this lens is very informative and helpful. Thanks and great job!

      working

      This website uses cookies

      As a user in the EEA, your approval is needed on a few things. To provide a better website experience, turbofuture.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

      For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://turbofuture.com/privacy-policy#gdpr

      Show Details
      Necessary
      HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
      LoginThis is necessary to sign in to the HubPages Service.
      Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
      AkismetThis is used to detect comment spam. (Privacy Policy)
      HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
      HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
      Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
      CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
      Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
      Features
      Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
      Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
      Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
      Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
      Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
      VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
      PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
      Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
      MavenThis supports the Maven widget and search functionality. (Privacy Policy)
      Marketing
      Google AdSenseThis is an ad network. (Privacy Policy)
      Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
      Index ExchangeThis is an ad network. (Privacy Policy)
      SovrnThis is an ad network. (Privacy Policy)
      Facebook AdsThis is an ad network. (Privacy Policy)
      Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
      AppNexusThis is an ad network. (Privacy Policy)
      OpenxThis is an ad network. (Privacy Policy)
      Rubicon ProjectThis is an ad network. (Privacy Policy)
      TripleLiftThis is an ad network. (Privacy Policy)
      Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
      Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
      Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
      Statistics
      Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
      ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
      Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)