Skip to content
Start learning
Menu

How to automate repetitive tasks in Excel (macros, Power Query and when to use a workflow)

Match the method to the task. For the same formatting on a weekly report, record a macro on the Developer tab and save as .xlsm. To combine a folder of similar files, use Power Query From Folder, then choose Refresh All each time. For work that crosses apps, such as email in and file out, use a workflow tool.

Runs in your browser. Your numbers stay here unless you press Send this estimate. Free, no sign-up.

01Methods

Which free method fits your setup

Pick by what repeats: the same steps, the same kind of file, or work that crosses apps.
Ways to automate repetitive tasks in Excel
MethodWhen it fitsCost and versionsWhere the data goes
Record a macroThe same steps on a sheet with the same layout every time, such as formatting a weekly report, adding headers or setting column widths.Included with desktop Excel for Windows and Mac; no extra purchase.Stored in the workbook or your Personal Macro Workbook and runs on your computer.
Power Query From FolderCombining a folder of weekly or monthly files that share the same column headers, with the same cleanup applied on every Refresh.Built into Excel under Data > Get Data; no add-in needed.Reads files from a folder you choose; the data stays in your workbook.
Office Scripts with Power AutomateRecording steps as a script in Excel on the web, Excel for Windows (version 2210 or later) or Excel for Mac, or running a script from a Power Automate flow, when your organization uses Microsoft 365 for business.Needs a Microsoft 365 business, enterprise or education license that includes Office Scripts, plus OneDrive for Business.Scripts are saved in your OneDrive for Business, inside your organization's Microsoft 365 storage, so they follow its settings.
A workflow toolAnything that crosses apps: a file arrives by email, gets saved, cleaned in Excel and sent on to someone else.Varies by tool and plan; check the pricing page before you build.Data passes through whatever service runs the workflow, so check where it is hosted.

02Steps

Step by step

  1. 01Check the task is worth automating

    Note how long the task takes and how often you do it. Put those numbers into the free Todkett Automation ROI calculator to see if the setup time pays back.

  2. 02Pick the method with the decision table

    Same steps on the same layout: record a macro. Many files with the same columns: Power Query From Folder. Work that starts or ends outside Excel, such as email in and file out: a workflow tool. If the data starts as a PDF, extract the tables to Excel first.

  3. 03Record a macro

    Show the Developer tab: File > Options > Customize Ribbon, tick Developer and confirm. Then choose Developer > Record Macro, give it a name with no spaces, store it in This Workbook, confirm, do the steps once, and choose Stop Recording.

  4. 04Save the file so the macro survives

    When Excel asks about saving a macro-free workbook, choose No. In Save As, pick Excel Macro-Enabled Workbook (*.xlsm) and save. Test the macro on a copy first, because Microsoft notes macros cannot be undone.

  5. 05Combine a folder with Power Query

    Put only the files you want in one folder. Choose Data > Get Data > From File > From Folder, pick the folder and select Open. Choose Combine, then the option that opens the Power Query editor, clean the data there, then choose Close & Load to put it on a sheet.

  6. 06Refresh instead of redoing

    Next time, drop the new file into the same folder and choose Data > Refresh All, or press Ctrl+Alt+F5. You can also tick Refresh data when opening the file in the query's Properties.

  7. 07Use Office Scripts or a workflow for cross-app work

    Open the Automate tab and use the Action Recorder to capture steps as a script. To run it from a Power Automate flow, choose Automate a Task on the Automate tab or in the script's More options menu, or build the same chain in another workflow tool.

03Worked example

Example: a weekly sales export that gets the same cleanup every Monday

  1. Every Monday a sales export lands in one folder, and each file has the same column headers.
  2. Week one: Data > Get Data > From File > From Folder, pick the folder, then use Combine and open the Power Query editor.
  3. In the editor, remove blank rows and unused columns, set the date and amount column types, then load the result to a sheet.
  4. Each later Monday: save the new export into the same folder, open the workbook and choose Data > Refresh All.
  5. The new week's rows appear with the same cleanup applied, so nothing is redone by hand.
  6. This is an illustration of the steps, not a measured result: your file names, columns and timings will differ.

04Mistakes

Common problems and their fixes

The recorded macro formats the wrong cells the next week because the data starts in a different place or has more rows.

Before recording, turn on Developer > Use Relative References so the macro works from whichever cell you select. Keep the report layout the same each week, and test on a copy of the file first.

Saving as a normal workbook and losing the macro, because Excel offers to save a macro-free workbook.

Choose No at that prompt and save as Excel Macro-Enabled Workbook (*.xlsm).

Refresh fails or columns go missing after someone renames a column in the source export.

Keep headers identical in every file, since Power Query matches columns by name. If a name must change, update the matching step in the Power Query editor.

Extra files in the folder end up in the combined table.

Use a folder that holds only these exports, or filter the Extension column in the Power Query editor before you combine. Power Query includes every file in the folder and any selected subfolders.

05FAQ

Questions people ask

Can I record a macro in Excel online?

No. The Macro Recorder is in desktop Excel for Windows and Mac. In Excel on the web, the Action Recorder on the Automate tab records Office Scripts instead, and it needs a Microsoft 365 license that includes Office Scripts.

Why did my macro disappear after I saved the file?

The file was most likely saved as a macro-free workbook. Save it as Excel Macro-Enabled Workbook (*.xlsm) instead.

Is there a shortcut to repeat my last action?

Yes. Press F4 or Ctrl+Y in Excel for Windows to repeat the last action, such as applying a format or inserting a row. For a column that follows a pattern, such as first names split from full names, type one example and press Ctrl+E for Flash Fill. For more than one action, record a macro.

Do I need to know how to code to automate Excel?

No. The Macro Recorder writes the code for you while you do the steps, and Power Query lets you clean and combine data without writing code.

Will Power Query pick up new files I add to the folder?

Yes. The query reads the whole folder each time you refresh, so a new file with the same column headers is included. Microsoft notes that every file should have the same columns, with the same headers and data types.

Is it worth automating something I only do once a month?

It depends on how long it takes and how long the setup takes. Run the numbers through the free Todkett Automation ROI calculator before you start.

06Sources

Sources, with the date we checked them

What changed

  1. Published

    First version: macro, Power Query From Folder and workflow methods with steps, example and mistakes.

07Next

Every free tool in one place: Free tools that run in your browser