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 the tool
Open the Excel books you want to work with and click the Cross-Sheet Operations icon on the Ablebits Tools tab:
To work with same cells, run the Cross-Sheet Cell Operations utility.
To perform operations with same ranges, click on the Cross-Sheet Range Operations icon.
The cell or range address is displayed at the top of the add-in's window:
To perform cross-sheet cell and range operations, use the tool's elements:
- The utility remembers the cells and ranges you select. You can use these buttons to move to the previous or to the next selected cell.
- Click the Paste Vertically button if your task is to paste the values from the same cell or range in a column. You are to choose the paste option:
You can simply paste values, select to pull formulas, paste links to create references, or insert without borders.
Once the paste option is selected, you will choose the place where to insert the cell values:
You can either enter the cell address into the field or select it in your worksheet.
Note. The cell you select in the dialog window is the first one in the range where the values will be pulled. Say, if I have 5 sheets, want to paste same cells in a row, and select the A$1$ cell address, the values will occupy the $A$1:$E$5 range.
- Clicking the Paste Horizontally button, you will copy the cell or range values and paste them in a row:
You can select a paste option and a place to paste the result as well.
Use the next two buttons to aggregate your cells or ranges.
If you are working with Cell Operations, click this button to aggregate the same cell and paste only the result value. Select the function you need:
If you are dealing with the Range Operations utility, click this button to aggregate the same range and paste only the result values without formulas. Decide if you want to aggregate all ranges together or each range separately and, if you've selected the latter, how to paste the results: in a row or in a column.
Mouse over the chosen option and click on the function to perform:
Click here to aggregate the same cell or range values and paste the result formulas.
This is the list of all open Excel worksheets, you can see the same cell values across the sheets right here. If you are working with Range Operations, you can see only the top left cell values. You can check all the sheets or only some of them to perform operations with.
To edit a cell, double-click a value, enter a new one, and press Enter.
Note. You can edit cell values only if you use the Cross-Sheet Cell Operations utility. This option is unavailable in Cross-Sheet Range Operations.
You can see all the values you've entered before and select any of them in the drop-down list:
Note. If you edit a cell, the values will be changed in all the checked sheets. If your task is to edit a cell value in only one worksheet, make sure you have unchecked all the others.
Edit the same cell across open workbooks
With the tool, you can edit the same cell in all open sheets or only the selected ones. You can undo one cell entry or all of them using special buttons.
Edit same cells in all open books or only the selected ones
By default, all the open sheets are checked in the tool's window, so if you do not need to edit all sheets, start with unchecking them by clicking the box near Workbooks:
If you have several books and need to select all sheets from one book only, check the box next to the book's name.
To choose the sheets you want to edit, you can check boxes next to each one. Another option is to select the worksheets of interest using the Ctrl key and check the box next to any single sheet name - all the other selected sheets will be checked automatically:
In the window, double-click a value next to any sheet, enter the value you need, and press the Enter key.
You will get the same values inserted into the same cell in all the checked sheets:
Note. If you are working with Cross-Sheet Range Operations, this procedure is not available.
Undo one cell entry or edits to all cells
Once you edit a cell, two new buttons in the tool's window will appear. These are the Undo buttons:
To undo one cell entry, click the Undo button next to the value:
To undo edits to all sheets, click the Undo button in the toolbar:
Copy the same cell or range from multiple sheets
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 cell, or pull formulas.
The first part is the same for all these actions:
Open all worksheets that contain the cell or the range you need.
Select the cell or the range you want to copy values from in any of these worksheets.
Click the Cross-Sheet Operations icon on the Ablebits Tools tab and select the utility you need:
In the add-in window, make sure that all boxes near the names of the sheets you are going to work with are checked.
Decide if you want the values to be pasted in a row or in a column and click the corresponding button:
Click the Paste vertically button to paste your values in a column:
Or Paste horizontally to paste them in a row:
And now you can choose your task:
To simply copy the same cell or range values, click on the first paste option:
In the dialog window, enter the first cell to paste your values or simply select the cell in your worksheet:
How to reference same cells
To create references to the same cell from multiple worksheets, click on the Paste Link option:
You will see the dialog window. Select a cell to paste references right in your worksheet:
How to pull formulas from same cells
To pull formulas from same cells across several sheets, click the Paste Formulas option:
In the dialog window, enter the first cell to paste formulas or simply select the cell in your sheet:
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 the value only, click the Paste Values button in the toolbar:
If your task is to insert the result formula, click the Paste Formulas button:
How to sum same cells
In the drop-down menu, click on the function you need:
Pick a cell to insert the result and click the OK button:
The tool uses the standard Excel functions (SUM, AVERAGE, COUNT, MAX, MIN, PRODUCT, STDEV.P, STDEV.S), so if you face certain difficulties, please consult a corresponding section here
How to aggregate all same ranges together
To aggregate all same ranges, mouse over the Paste the Result to One Cell option and select the function:
In the dialog window, 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, mouse over the chosen option, and pick the function to apply:
In the dialog window, select the top left cell to paste the results and click OK: