RecalculateRange command: automatically recalculate a selected Excel range

XLTools Automation tool allows you to automate tasks in Excel by creating automation commands in a table, without having to create VBA macros.

This tutorial will take you through the setup of the RecalculateRange automation command. Use it when you need to regularly recalculate specific ranges as a part of your routine process.

For non-recurring tasks, use the standard Recalculate Range shortcut.

Before you begin, add the Automation tool to Excel

Automation is one of the 20+ features within XLTools Add-in for Excel. Works in Excel 2019, 2016, 2013, 2010, desktop Office 365.

– free trial gives you 14 days of full access to all features.

Step-by-step example: how to use the RecalculateRange command

Say, you are doing a lot of calculations in multiple interconnected spreadsheets. Recalculating all spreadsheets takes a long time and slows you down, so you’ve switched to manual mode. But at some point you want to refresh some specific range.

You can do this with the RecalculateRange automation command. Follow the steps below to execute the command yourself.

Feel free to download the sample file used in this tutorial.

1. Insert the RecalculateRange command

  1. Let’s take a look at the sample Excel spreadsheet with invoices. Columns "Outstanding" and "Payment Status" are based on formulas.

    Switch to manual calculations mode:

    Formulas tab in the Calculation group, find Calculation Options Open the drop down and select Manual.

    Say, you have received payments from some customers and updated the payment information.

    • See the green cells – the payment information that was updated.
    • Note the yellow cells – the values displayed are incorrect now, because the spreadsheet is in manual calculations mode, so the formulas in yellow cells didn’t refresh.
  2. Add a new worksheet Name it, for example, “AUTO”. This worksheet will hold automation commands.
    Tip: it’s best to keep automation commands on a separate worksheet, not to interfere with data.
  3. Select a starting cell where to place the command, e.g. A1.
  4. Open XLTools tab In the Automation group, open the drop-down list Select RecalculateRange.
  5. See the table with the automation command is now added on the worksheet.

2. Specify parameters and define how to recalculate range

Once you’ve inserted the RecalculateRange command, specify its parameters. This automation table specifies which range to refresh.

XLTools.RecalculateRange
Range:Invoices!D:E

What the line in this command reads:

  • Range: take the range D:E on the sheet Invoices and recalculate this specific range.

See other available options to use in parameters.

3. Execute the command and review the updated range

  1. Select the table of the automation command > In the Automation group, click Execute Commands.
  2. Review the updated range. The values in yellow cells are now correct, the formulas were recalculated.

Done! Now you can assign a custom button to this command.

4. Assign a custom button to the command

If you need to run the command over and over again, add a custom button on the ribbon. So that next time you can execute the command in a click.

  1. In the Automation group, click Add Custom Buttons.
  2. Name the button, e.g. "Split by paid/due" Specify the range of the RecalculateRange command Click Save.
  3. Done! The custom button will appear on XLTools tab. Now every time you click this button, you will execute the command.

Parameters and available options

When you create automation commands in Excel, this file with commands must be located in the same destination as the source data:

  • Add automation commands in the same workbook as your source data. We recommend that you place the commands on a separate worksheet not to interfere with your data.
  • Or, add automation commands in a separate workbook. Save this automation file in the same folder as the source data.

Below are all parameters and available attributes for the RecalculateRange command.

Tip: select a cell and see a helpful tooltip. Some attributes also have a drop-down list of available options to choose from.


Range:

This parameter specifies which data range should be processed.

  • Required parameter, cannot be blank
  • If you refer to a workbook, it can be open or closed
  • The source workbook must be located in the same destination as the automation file
OptionsMeaning
Sheet1!A1:B2Range A1:B2 on Sheet1 in the active workbook
Sheet1!A:CEntire A, B, C columns on Sheet1 in the active workbook
Sheet1!1:3Entire 1, 2, 3 rows on Sheet1 in the active workbook
A1:B2Range A1:B2 on the current worksheet
Sheet1!Entire Sheet1 in the active workbook
Sheet1!Table1Table1 on Sheet1 in the active workbook, without table headers
Sheet1!Table1[Column1]Column1 in Table1 on Sheet1 in the active workbook, without table headers
Sheet1!Table1[[Column1]:[Column3]Columns 1, 2, 3 in Table1 on Sheet1 in the active workbook, without table headers
Sheet1!Table1[#All]Table1 on Sheet1 in the active workbook, with table headers
Sheet1!Table1[[#All],[Column1]]Column1 in Table1 on Sheet1 in the active workbook, with table headers
Sheet1!Table1[[#All],[Column1]:[Column3]]Columns 1, 2, 3 in Table1 on Sheet1 in the active workbook, with table headers
[Book.xlsx]Sheet1!A1:B2

'[Book One.xlsx]Sheet1'!A1:B2
Range A1:B2 on Sheet1 in the workbook Book. Book.xlsx must be located in the same destination as the automation file.
'C:\Documents\[Book.xlsx]Sheet1'!A1:B2Range A1:B2 on Sheet1 in the workbook Book. Book.xlsx must be located in the specified file path location.

Use standard rules of range referencing in Excel, for example:

E.g. if names of worksheets or workbooks contain spaces, enclose the name in single quotes, e.g.'Orders April'!A1:H10, or '[All Orders]Orders April'!A1:H10.

Note: in order for automation commands to identify references, do not use spaces in the names of worksheets, workbooks, or tables.


Any questions or suggestions?
How can we help?