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

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

  • 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:
See the cell address in the add-in's window.

To perform cross-sheet cell and range operations, use the tool's elements:
The Cross-Sheet Cell Operations pane.

  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:
    Paste same cell values in a column.

    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:
    Select a cell to paste the result.
    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:
    Paste the same cell across multiple sheets 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:
      Aggregate same cell across multiple sheets and paste values only.
    • 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:
      Aggregate same range across multiple sheets and paste values only.
  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:
    All entered values.
    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:
    Select several sheets.
  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:
    Enter the same value into the same cell across multiple sheets.

    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:
    Undo one cell entry.
  • To undo edits in all sheets, click the Undo button on the add-in toolbar:
    Undo edits to all cells.

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:
    Click on the Cross-Sheet Cell Operations icon.
  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:
      Extract data and paste in a column.
    • Or Paste horizontally to have them in a row:
      Extract data and paste in a row.

How to extract data

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

How to reference same cells

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

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:
    Aggregate same cell and paste value only.
  • To insert formulas, click Paste Formulas:
    Aggregate same cell and paste formula.

How to sum same cells

  1. In the dropdown menu, click the function of interest:
    Sum same cell across multiple sheets.
  2. Then pick a cell to insert the result into and click OK:
    Select a cell to paste the result.
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

  1. To aggregate all same ranges, hover over the Paste the Result to One Cell option and select the function:
    Aggregate all ranges together.
  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:
    Aggregate ranges separately and paste the results in a row or in a column.
  2. In the dialog window that appears, select the top left cell to paste the results and click OK.

Responses

MARY AU says:
October 1, 2020 at 1:49 am

Is there a way to have a formula look for the same values in multiple worksheets and display them along a row. I've used the cross sheet function to pull values but need it to look up a value (like a H Lookup across the worksheets or a sumif). I do have the Ablebits ultimate suite.

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.