sum and copy the same cell from multiple sheets (2023)

Cross-Sheet Operations helps to sum, copy, and reference the same cell or range across multiple Excel sheets. You can paste the values vertically or horizontally, aggregate your data, and edit same cell values in one window. Use the Cross-Sheet Range Operations tool to work with the same range or Cross-Sheet Cell Operations to edit and sum the same cell.

Video: How to use Cross-Sheet Operations

Start Cross-Sheet Operations

Open the Excel workbooks you want to work with and click Cross-Sheet Operations on the Ablebits Tools tab:

To work with same cells, run Cross-Sheet Cell Operations.
To perform operations with same ranges, click Cross-Sheet Range Operations.

The cell or range address is displayed at the top in the add-in pane:

To perform cross-sheet cell and range operations, use the tool's elements:

Edit same cell across open workbooks

With the tool, you can edit the same cells in all open worksheets or only in the selected ones. You can undo one cell entry or all of them using special options.

Edit same cells in all open workbooks or only in the selected ones

By default, all the open sheets are selected in the tool pane. Clear the checkboxes next to the worksheets which you don’t want to edit. To quickly select or deselect all worksheets in a workbook, select or clear the checkbox next to the workbook's name:
In the add-in pane, double-click a value next to any selected sheet, enter the value you need, and press the Enter key.
You will get the same values inserted into the same cell in all selected worksheets:
Note. This option is not available for Cross-Sheet Range Operations.

Undo one cell entry or edits to all cells

Once you edit a cell, two new buttons in the tool pane will appear. These are the Undo buttons:

To undo one cell entry, click the Undo button next to the value:
To undo edits in all sheets, click the Undo button on the add-in toolbar:

Copy same cell or range values

The tool provides several opportunities to copy the same cell or range values from multiple Excel worksheets. You can extract data, create references to the same cells, or pull formulas.

The first steps are the same as above:

Open all worksheets that contain the cell or the range you need.
In any of the opened worksheets, select the cell or the range you want to copy values from.
Click Cross-Sheet Operations on the Ablebits Tools tab and select the option you need:
In the add-in pane, make sure that all the sheets you are going to work with are selected.
Decide if you want the values to be pasted in a row or in a column and click the corresponding button:
- Click Paste vertically to have your values organized in a column:
- Or Paste horizontally to have them in a row:

How to extract data

To simply copy the same cell or range values, click the first paste option:
Enter the cell address into the field in the dialogue window or select it in your worksheet:

How to reference same cells

To create references to the same cell from multiple worksheets, click the Paste Link option:
And then either enter the cell address into the field in the dialog window or select the cell in your worksheet.

How to pull formulas from same cells

To pull formulas from the same cells across several sheets, click the Paste Formulas option and then select where to place the result:

Aggregate same cells and ranges

The tool lets you apply a function to same cells and ranges in multiple Excel sheets. Run the Cross-Sheet Cell Operations tool to work with same cells or Cross-Sheet Range Operations to aggregate same ranges.
Decide if you want to paste the result value or the formula:

If you need values only, click the Paste Values button:
To insert formulas, click Paste Formulas:

How to sum same cells

In the dropdown menu, click the function of interest:
Then pick a cell to insert the result into and click OK:

Note. The tool uses the standard Excel functions (SUM, AVERAGE, COUNT, MAX, MIN, PRODUCT, STDEV.P, STDEV.S). If you are new to Excel functions, please consult a corresponding section here.

How to aggregate all same ranges together

To aggregate all same ranges, hover over the Paste the Result to One Cell option and select the function:
In the dialog window that appears, pick a cell to paste the result value or formula and click OK.

How to sum each range separately

To perform an operation with each same range separately, select to paste the result in a row or in a column, hover over the chosen option, and pick the function to apply:
In the dialog window that appears, select the top left cell to paste the results and click OK.

How to count and sum by color
How to get chars and word count in Excel
How to do quick calculations in Excel