Excel automation: how to stop copying data between spreadsheets by hand

Take a hypothetical distribution company with six branches. Every Monday, a finance specialist opens six sales spreadsheets, copies the data into a summary file, fixes date formats and deletes empty rows. Then she recalculates the totals and pastes the results into an email for the management board.

None of these tasks needs her expertise. They need only time and attention, and copying makes it easy to introduce an error nobody notices. In most companies, the same types of work keep coming up:

Instead of general statistics, use your own data. The formula is simple: hours per week × working weeks per year × fully loaded hourly cost, including social security contributions and holidays.

Assume the report in our example takes 2 hours a week and the year has 46 working weeks. That gives 92 hours a year. At PLN 70 an hour, that comes to about PLN 6,440 a year – for one report by one person.

On top of that come costs that are harder to quantify: an error in a pasted range, a late report when its author is ill, and decisions made on out-of-date data. Do not add them to the total without evidence, but do mention them to the management board.

Not every Excel problem is solved by the same tool. The table below maps typical tasks to Microsoft 365 tools and shows whether you need an additional licence. Power Query is usually the first choice.

You will find Power Query in Excel on the Data tab, in the “Get & Transform Data” group. It works like a recorded recipe: it saves the steps you perform on the data and repeats them on new files when you refresh.

In the branch example, all that is needed is for each branch to save its spreadsheet in one folder in OneDrive or SharePoint. Power Query takes all the files from the folder, combines them into one table, removes empty rows and standardises the dates. On Monday, the specialist clicks “Refresh All” and the data is ready.

Power Query also pulls data from databases, CSV files and web pages. Connecting to an accounting system or ERP depends on how the system makes its data available – sometimes a scheduled export to a folder is enough.

There is one condition: the source files must have a fixed column layout. If the branches change the headings every month, start by standardising the template.

Power Query prepares the data, but someone still has to click “Refresh”. If the report is to be produced automatically and sent to the management board, you need a second component – and you need to know one of its limitations.

Office Scripts work in Excel for the web and in Excel for Microsoft 365 on the desktop. You record your actions, and Excel saves them as a script that can be run with a button or from Power Automate. Power Automate can run it, for example, every Monday at 7:00 and email the result.

The limitation is this: a script run by Power Automate does not refresh Power Query queries. According to Microsoft’s documentation, data refresh in such a flow works only for Power BI sources. The flow can therefore format and distribute data that is already in the file, or pull rows from tables in the branch files itself. A Power Query-based report that needs to refresh without human involvement is better moved to Power BI with scheduled refresh.

VBA macros – older programs stored in an Excel file – still work in desktop Excel. However, Microsoft blocks them by default in files from the internet and from emails, and they do not work in the browser. New automations are therefore better built on Office Scripts and Power Automate.

Power Automate for desktop is useful where data can only be extracted by clicking through an old program. Such a robot repeats a person’s actions, but it is sensitive to any change in the program’s appearance. Treat it as a last resort, not a first choice.

Copilot in Excel writes formulas from a description, creates charts and pivot tables, sorts and filters data and points out trends. It helps most those who use Excel often but do not know its advanced features.

Copilot does not replace Power Query. It helps with one-off analysis, not with a process that has to produce the same result every week. Its output also needs checking, because Copilot may misunderstand the instruction or the data layout.

Copilot is a paid add-on. Microsoft 365 Copilot Business costs USD 21 per user per month, and the version for large companies costs USD 30. Buy it for the people who do a lot of analysis, not for a whole department to begin with.

Excel in Microsoft 365 lets several people edit a file at the same time and has version history and data validation. Even so, it is not suitable for a register that several people maintain over years. It is easy to overwrite someone else’s row, change a formula or sort just one column.

Microsoft Lists, available in Microsoft 365 and in Teams, looks like a table but works like a simple database. Every entry has a change history, fields can require a specific format, and you can add a form and notifications to a list.

Consider moving a spreadsheet to Lists when it meets at least two of the conditions below. When a register becomes the heart of a process, such as order handling, it is worth thinking about an app in Power Apps or a specialist system for that process.

The first project should be small, visible and measurable. One person and one report that takes them between one and three hours a week are enough. The plan might look like this:

The tools are simple, so mistakes usually stem from how the work is organised rather than from the technology. These come up most often: