Excel Spreadsheet Automation Tricks

We've all been there - it's the start of a new month, and you're faced with the overwhelming task of updating your Excel files and reports. It can take hours, if not days, to complete. But it doesn't have to.

In this post, I'll show you five Excel spreadsheet automation tricks for common tasks like updating drop-down lists, ranges, consolidating data, and creating dynamic text labels. These tips will help you get your work done ten times faster!

Watch the Video

 

Get the Practice Workbook & Cheat Sheet

5 Essential Excel Spreadsheet Automation Tricks

(1) Auto-Expanding Data Validation

Scenario: Classifying data/data entry - let's say one of the monthly tasks required to update your reports is to classify bank transactions into sub-categories. To ensure consistency, a data validation drop-down list is set up for choosing sub-categories:

However, new sub-categories may be needed over time. Here's how to make your data validation lists automatically include new items:

1. Format Data in a Table

Place your sub-categories list in an Excel Table via the Insert tab > Table or the shortcut CTRL+T. On the Table Design tab (a), give the table a more useful name (b).

2. Define a Name for Sub-Categories

Select the sub-categories column in the table (excluding the header) and on the Formulas tab > Define Name:

Enter the name 'subcategories' in the New Name dialog box:

3. Use Defined Name in Data Validation

Go to the Transactions table, select the sub-category column, and set the data validation list source to the defined name.

Any new sub-categories added to the Categories table will automatically appear in the drop-down list without any extra effort.

(2) Importing Data with Power Query

If your report update process involves new data from CSV, text, or Excel files, you can automate the data consolidation with Power Query. For example, below I have 5 Excel files that I need to extract the data from and place in a single table for my report:

1. Get Data from Folder

On the Data tab > Get Data > From File > From Folder.

Select the folder containing your files.

2. Combine & Transform Data

Combine the files into a single table.

Use the query editor to clean and transform data as needed, such as removing duplicates or adding calculated columns. See video for step-by-step instructions.

3. Load Data

On the Power Query Editor Home tab > Close & Load To…:

Choose to load the data to a Table, PivotTable, or Pivot Chart.

Tip: Loading directly to a PivotTable or Pivot Chart is more efficient and keeps the file size smaller.

4. Update Data

When next month's data is available, simply add the file to the folder and on the Data tab of the ribbon, Refresh All to update your tables, PivotTables, and charts.

(3) Structured References

Excel Tables' structured references automatically expand and contract to include new data. This feature eliminates the need to edit cell references in formulas, PivotTables, or charts:

1. Insert PivotTables and Pivot Charts

Use the table data to insert a PivotTable and chart. Notice the table name automatically appears in the Table Range field:

2. Reference Table in Formulas

Use the Table's structured references in formulas to ensure they automatically pick up any changes in the table size:

3. Update Data

When new data is added, refresh the table and all associated formulas, PivotTables, and charts will update automatically.

(4) Dynamic Named Ranges

If you can't use tables for some reason, dynamic named ranges are a great alternative. They grow and contract with your data just like structured references for Tables.

For example, some charts like the treemap aren't compatible with PivotTables:

But with dynamic named ranges we can trick them into referencing the PivotTable as the source data.

1. Create Dynamic Named Ranges

Use the OFFSET function to create a dynamic named range for both the series values and axis labels.

2. Define Names

Convert the OFFSET formulas to defined names, such as `treemapAxis` and `treemapValues`.

3. Edit Chart Source

Replace cell references in the chart data source with the dynamic named ranges.

This allows your charts to update dynamically when data changes.

(5) Dynamic Labels

Creating dynamic text labels that update automatically can save a lot of manual work. For example, the title of this chart automatically updates as new data is added to the Actual column of the table:

1. Extract Label Data

Use INDEX and MATCH functions to return the month and cumulative variance for the latest data:

Latest month:

=INDEX(Table1[Month], MATCH(1E+10,Table1[Actual $k],1))

Latest cumulative variance:

= INDEX(Table1[Cumulative Variance $k], MATCH(1E+10,Table1[Actual $k],1))

2. Format and Combine Text

Use the TEXT function to format the date and number values appropriately, then combine them into a single formula.

=TEXT(

INDEX(Table1[Month],

MATCH(1E+10,Table1[Actual $k],1)),

"mmmm")

&" variance to date: "&

TEXT(

INDEX(Table1[Cumulative Variance $k],

MATCH(1E+10,Table1[Actual $k],1)),

"$#,###0k;-$#,##0k")

3. Link Label to Chart

Link the cell containing the dynamic label to the chart title, so it updates automatically with new data.

Next Steps

With these tricks, your Excel spreadsheets will update themselves, freeing you from endless manual updates and giving you more time to focus on what really matters.

Unlock your potential with our Excel and Power BI courses! Join thousands who are mastering essential skills, getting noticed, and earning promotions with our expert tips and tricks.