The Create named range instruction allows a user to name a specified cell range for use inside an Excel workbook.

Creating the step

Firstly, create an action step to manipulate data and select Create named range.

Configuring this step is quite simple.

First, you will need to link to an Excel workbook as well as the relevant worksheet.

Next, specify the cell or cell range you wish to name.

Simply enter the desired name in the "Range Name" textbox and the configuration is done.

Note: once this step has been run, this name can be used in future instructions directly by choosing "Name" in the drop-down list in the "Use Cell" section in most Excel-related instructions.

Tips & tricks

This instruction is extremely useful to create "dynamic ranges" in Excel.

Dynamic ranges are ranges that expand automatically, as new data is added or data is removed from a data source.

This can be implemented quite easily in SolveXia by using the "Last Cell in Col" feature when creating a named range.

There are many scenarios where this can be useful:

1. When optimising the performance of an Excel workbook, the speed of calculations in Excel can be heavily reduced if formulas aren't based on entire columns.

For example, when using the SUMIFS formula, it is common to use an entire column as a parameter for this function such as =SUMIFS(A:A, B:B, "Include"). Normally, such formulas on its own does not affect the performance of an Excel workbook noticeably.

However, as the formulas become more complicated in a large Excel workbook, these begin to add up and it may take up to 30 seconds to refresh a workbook.

In times like this, formulas can be optimised by using SUMIFS only on the range of the column that actually contains data. So, if we know in advance that the data reaches 50,000 rows, we could easily use the formula =SUMIFS(A1:A50000, B1:B50000, "Include").

However, more often than not, the number of rows is not known in advance, since the number of rows changes as more data is added or removed.

This is where dynamic ranges can be used, by utilising the "Last Cell in Col" feature in SolveXia.

This can now be used in the SUMIFS function as =SUMIFS(data_source, info_source, "Include").

2. Another useful situation for dynamic named ranges is in pivot tables.

If a pivot table is configured to have a data source range for an entire column, then any blank rows will also be fed into the pivot tables and create an unnecessary pivot. 

And so, to avoid including such blank rows in the source data range, we can use a dynamic named range to include all the rows up to the last filled-in row to circumvent this problem.