Formulas for adding multiple cells in Microsoft Excel

By SpreadCheaters
Page last updated: 29/06/2023 | Next review date: 29/06/2025
close

Author bio


SpreadCheaters
View More north_east

In Excel, the act of combining adjacent cells to create a larger cell or range of cells is known as adding multiple cells. This is frequently carried out to conduct calculations on a group of cells or to apply formatting or other actions to a particular section of the worksheet.

In this article, we will cover the formulas in Microsoft Excel that users can utilize to add multiple cells, whether they contain numeric values or text strings.  Let’s say we have a simple scenario in which we aim to add up the sales of some products to extract the total revenue generated.

Formula 1: SUM Function

The SUM function is a basic function in Excel that is extensively used to add a range of cells or individual cells.

The Syntax of the SUM function to add multiple cells is:

Case 1: Contiguous Range

  • For a Contigious range: SUM(C2:C11), where the range C2:C11 is the range to be added.
  • Hit the Enter key to add multiple cells.

Case 2: Non-Contiguous Range

  • For a Non-Contiguous range of cells, SUM(C2, C4, C6, C8, C10), where the input references are the cells that are not adjacent.
  • We can add the cell references manually or we can select each cell and place a comma after each reference.

Formula 2: AutoSum

Utilizing AutoSum is an efficient method to add multiple cells. We can add multiple cells with just one click.

Step 1 – Locate the Formulas Tab

  • Locate the “Formulas” tab in the menu bar.

Step 2 – Utilize the AutoSum Function

  • Utilize the AutoSum function located in the “Formulas” tab.

Formula 3: Addition Operator

Multiple cells can be added by utilizing the “Addition Operator (+)”, however, it is not a very efficient method for adding a large range of cells.


Step 1 – Utilize the Addition Operator

  • To use the “Addition Operator” for adding multiple cells, we simply need to add the reference of each cell to be added with an “Addition Operator” in between.
  • Then press the Enter key to get the result.

Formula 4: Adding Multiple Cells Containing Text Utilizing the CONCATENATE Function

In some cases, we may need to add text together. For this, users can utilize some built-in functions including the CONCATENATE Function.

In this case, we will use another scenario. Let’s say we have a sheet showing the designation and name of some employees individually. We aim to add them in a single cell.

Step 1 – Utilize the CONCATENATE Function

  • The structure of the CONCATENATE function is:

            CONCATENATE(A2,B2)

  • The cell A2 and B2 contain the designation and the name of the employee respectively.

Step 2 – Utilize Autofill to Apply the Function on Each Cell

  • Utilize Autofill to apply the function on each cell.

Formula 5: Utilizing the Ampersand Operator

The Ampersand operator is also a useful operator that we can utilize to add text from multiple cells in Microsoft Excel.

Step 1 – Utilize the Ampersand Operator

  • To add text from multiple cells utilizing the “&” operator, we just have to put the cell references and place the operator in between.

Step 2 – Utilize Autofill to Apply the Function on Each Cell

  • Utilize Autofill to apply the formulae on each cell.