8 Excel Automations That Make Your Spreadsheet Work for You

Hi ,

How many times have you manually updated a chart, recoloured a bar, searched for a file, or scanned a task list to see what is overdue?

Most Excel users manually repeat tasks every week that Excel can handle automatically.

In this guide, you will learn 8 simple Excel automations that eliminate repetitive work, reduce errors, and make your spreadsheets update themselves.

1. Make Your Data Auto-Expand Using Excel Tables

The Problem

Charts, formulas, and PivotTables stop updating when new data is added because they are built on fixed ranges.

The Solution

Convert your data range into an Excel Table.

How to Do It

Now when you add new rows, your:

Why this matters for automation: Excel Tables create dynamic data structures. Anything built on top of them becomes self-updating.

Best practice: Always build dashboards and reports on Excel Tables, not static ranges.

2. Automatically Highlight the Highest and Lowest Values in a Chart

The Problem

Users manually recolour the highest bar in a chart.

When the data changes, the highlight is wrong.

The Solution

Use helper columns with formulas to dynamically identify high and low values.

Step 1: Add a High Column

This formula returns the sales value only if it equals the maximum. Otherwise, it returns blank.

Step 2: Add a Low Column

This returns the minimum value dynamically.

Step 3: Add These as Chart Series

Now Excel automatically highlights the highest and lowest values in the chart whenever data changes.

3. Let Excel Write Formulas for You with Copilot

If you are using Microsoft 365 with Copilot, Excel can suggest or generate formulas automatically.

How It Works

With the first formula entered, Copilot is even quicker to write the formula for Lowest:

Copilot performs well with:

Important: It does not replace understanding formulas. It accelerates writing them.

4. Open Files Instantly from Excel Using the HYPERLINK Function

The Problem

You repeatedly navigate to folders to open documents related to the Excel file you're working on.

The Solution

Use the HYPERLINK function to generate clickable file links.

Example Formula

How it works:

Tip: If your data is in an Excel Table, the formula copies down automatically.

Use cases:

Now you can easily use Excel hyperlinks to open files automatically.

5. Make Tasks Highlight Themselves with Conditional Formatting

The Problem

You manually scan for overdue tasks.

The Solution

Use Conditional Formatting with the TODAY function to highlight tasks that need your attention.

Basic Overdue Rule

Note: be sure to remove the dollar sign before the row number so each row is evaluated individually.

Format with red font and light red fill.

Now Excel highlights overdue tasks automatically.

Add a 3-Day Warning Window

Edit the formula to:

This includes tasks due within the next three days.

The TODAY function updates automatically every time you open the workbook.

6. Use Python Inside Excel for Instant Statistical Analysis

Microsoft 365 now includes Python directly inside Excel for eligible licenses.

This allows you to perform advanced analysis without complex formulas.

Step 1: Enter Python Mode

Type:

Press Tab to put Excel into Python mode:

Step 2: Create a DataFrame

Select the cells in the table you want to analyse and Excel will enter the Python reference:

Press Ctrl + Enter.

Step 3: Generate Summary Statistics

Type =PY → tab, then click in the cell containing the dataframe:

Then type .describe() You should see this in the formula:

Press Ctrl+Enter to complete the Python formula.

Switch the output display to "Excel value" to view:

Now you should see the

Example: Get Totals

Want totals for each region? Use the sum() function to automatically return them. For a horizontal output, follow it with to_frame() to transpose them.

Python reduces multi-step analysis to a single expression.

Now Python statistics in Excel are super quick and easy.

7. Send Personalised WhatsApp Messages from Excel

The Concept

Use Excel to generate WhatsApp message links using the official wa.me API.

Important: Phone numbers must include country code and no spaces.

Download the WhatsApp desktop app. Then use a formula to automatically generate the messages.

Example Formula

What this does:

When clicked, it opens WhatsApp Desktop with the message pre-filled.

Use cases:

Now you're all set to send WhatsApp messages from Excel.

8. Use Copilot Agent to Clean Data Automatically

Data cleaning often takes longer than analysis.

Copilot Agent can:

How to Use It

Let's look at using Copilot Agent to clean this messy data:

"Review the data in this worksheet and clean it ready for analysis. Also advise of any issues or anomalies I should look into further."

Copilot:

Now you're all set to use Excel Copilot Agent to clean data automatically. Note: It does not replace judgment, but it significantly reduces manual preparation time.

Why Excel Automation Matters

Automation in Excel is not about complexity.

It is about eliminating repetitive tasks. The more your spreadsheet updates itself, the less time you spend maintaining it.

Automation reduces:

It increases:

Have a great day,

Mynda Treacy

Co-founder My Online Training Hub