How to use Cross-Sheet Operations

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
• Edit same cell across open workbooks
  • Edit same cells in the selected books
  • Undo one cell entry or edits to all cells
• Copy same cell or range values
  • Extract data
  • Reference same cell
  • Pull formulas from the same cell
• Aggregate same cells and ranges
  • How to sum same cells
  • How to aggregate all same ranges together
  • How to sum each range separately
• Related links

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:

1. The tool remembers the cells and ranges you select. You can use these buttons to move to the previous or to the next selected cell.
2. Click Paste Vertically if your task is to paste values from the same cell or range in a column. Then choose the paste options:
   

   You can select to paste values, pull formulas, paste links to create references, or insert without borders.
   Once the paste option is selected, choose the location for cell values:
   
   You can either type the cell address into the field or select it in your worksheet.

   Note. The cell you select is the uppermost cell in the range where the values will be inserted. Say, if you have 5 sheets and want to paste same cells in a column, select the cell A1 and the values will occupy the range A1:A5.
3. Clicking Paste Horizontally will copy the cell or range values and paste them in a row:
   
4. Use the next two options to aggregate the values of 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:
     
   • When dealing with Range Operations, 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 with the results pasted in a column or in a row. Hover over the chosen option and click on the function to perform:
     
5. Click here to aggregate the same cell or range values and paste the result formulas.
6. This is the list of all open Excel worksheets. You can see the same cell values across all your sheets right here. If you are working with Range Operations, you can see only the top left cell values. You can select all the sheets or only some of them to perform operations with.
7. To edit a cell, double-click the value, enter a new one, and press Enter.
   Note. You can edit cell values only if you use the Cross-Sheet Cell Operations tool. This option is unavailable in Cross-Sheet Range Operations.
   Tip.You can see all the values you've entered before and select any of them in the dropdown list:
   
   Note. If you edit a cell, the values will be changed in all the selected sheets. If your task is to edit a cell value in only one worksheet, make sure you have unchecked all the others.

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

1. 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:
   
2. 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:

1. Open all worksheets that contain the cell or the range you need.
2. In any of the opened worksheets, select the cell or the range you want to copy values from.
3. Click Cross-Sheet Operations on the Ablebits Tools tab and select the option you need:
   
4. In the add-in pane, make sure that all the sheets you are going to work with are selected.
5. 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

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

How to reference same cells

1. To create references to the same cell from multiple worksheets, click the Paste Link option:
   
2. 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

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

How to aggregate all same ranges together

1. To aggregate all same ranges, hover over the Paste the Result to One Cell option and select the function:
   
2. 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

1. 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:
   
2. In the dialog window that appears, select the top left cell to paste the results and click OK.

Related links

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