Advanced Cell Range - Examples

Advanced Cell Ranges for Excel instructions allow you to dynamically create ranges when copying and inserting data into spreadsheets:

This is a powerful feature to learn as it will allow you to create processes that can deal with varying volumes of data in your spreadsheets. Below, we have listed the most common configurations of the Advanced Cell Range for when you are dealing with a Source range and a Target range. 

Source Ranges

Below, are some examples of common Advanced Cell Ranges for source data.

Copying a known volume of data:

When the number of rows you are trying to copy is pre-defined and unlikely to change, you can set a range with specific cells such as:

Copying an unknown volume of data:

When the number of rows you need to copy is unknown or likely to change, use the "Last Cell In Col" setting as shown below:

The configuration above will dynamically set the source range to however many rows of data appear in column A. Note, think of "Last Cell" as "Last Populated Cell" in a column.

Target Ranges

Below, are some examples of common Advanced Cell Ranges for the target range - i.e. where you are trying to insert or paste your data.

Inserting a known volume of data:

When the number of rows you are trying to insert is pre-defined and unlikely to change, you can set a range with specific cells such as:

Inserting an unknown volume of data:

When the number of rows you need to insert is unknown or likely to change, use the "Last Cell In Col" setting as shown below:

In the example above, data is inserted at cell C1. Column A is then used as an anchor to determine how far down the sheet that data is copied to. The anchor column should always be the most complete column in your data set (such as an ID).

Appending data:

To append data to an existing table, use the "Last Cell + 1 in Col" feature as shown below:

In the example above, data is inserted directly one cell below the last populated cell in column A.