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 the tool

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

  • 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:
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 utility 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 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:
    Paste same cell values in a column.
    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:
    Select a cell to paste the result.
    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.
  3. Clicking the Paste Horizontally button, you will copy the cell or range values and paste them in a row:
    Paste the same cell across multiple sheets in a row.
    You can select a paste option and a place to paste the result as well.
  4. 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:
      Aggregate same cell across multiple sheets and paste values only.
    • 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:
      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 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.
  7. 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.
    Tip.You can see all the values you've entered before and select any of them in the drop-down list:
    All entered values.
    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

  1. 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:
    Uncheck all sheets.
    If you have several books and need to select all sheets from one book only, check the box next to the book's name.
  2. 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:
     Edit same cell across the selected Excel sheets.
  3. 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:
    Enter the same value into the same cell across multiple 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 button next to the value:
    Undo one cell entry.
  • To undo edits to all sheets, click the Undo button in the toolbar:
    Undo edits to all cells.

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:

  1. Open all worksheets that contain the cell or the range you need.
  2. Select the cell or the range you want to copy values from in any of these worksheets.
  3. Click the Cross-Sheet Operations icon on the Ablebits Tools tab and select the utility you need:
    Click on the Cross-Sheet Cell Operations icon.
  4. In the add-in window, make sure that all boxes near the names of the sheets you are going to work with are checked.
  5. 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:
       Extract data and paste in a column.
    • Or Paste horizontally to paste them in a row:
       Extract data and paste in a row.

And now you can choose your task:

How to extract data

  1. To simply copy the same cell or range values, click on the first paste option:
    Create a list of the same cell values.
  2. In the dialog window, enter the first cell to paste your values or simply select the cell 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 on the Paste Link option:
     Create references to the same cell.
  2. You will see the dialog window. Select a cell to paste references right in your worksheet:
    Select a cell to paste the result.

How to pull formulas from same cells

  1. To pull formulas from same cells across several sheets, click the Paste Formulas option:
    Pull formulas from the same cell.
  2. In the dialog window, enter the first cell to paste formulas or simply select the cell in your sheet:
    Select a cell to paste 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 the value only, click the Paste Values button in the toolbar:
    Aggregate same cell and paste value only.
  • If your task is to insert the result formula, click the Paste Formulas button:
    Aggregate same cell and paste formula.

How to sum same cells

  1. In the drop-down menu, click on the function you need:
    Sum same cell across multiple sheets.
  2. Pick a cell to insert the result and click the OK button:
    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), so if you face certain difficulties, please consult a corresponding section here.

How to aggregate all same ranges together

  1. To aggregate all same ranges, mouse over the Paste the Result to One Cell option and select the function:
    Aggregate all ranges together.
  2. In the dialog window, 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, mouse 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, select the top left cell to paste the results and click OK:
    Select a cell to paste the result.

Ask a question (posted publicly)

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.
Privacy policy Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.

Ultimate Suite 2018.5 for Excel
60+ professional tools for Excel 2019-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard