Excel automation – how to stop manual copying between sheets

The most common annoying Excel activities everyone knows firsthand. First: combining 5–10 sheets into one summary report. Open each, copy, paste, format. Second: cleaning data after import from a system (CRM, ERP, shop). Third: repetitive analyses (sums, VLOOKUP, sort, filter) on new data every week. Fourth: pulling specific numbers into a leadership email report.

Each of these activities really takes 30–120 minutes depending on scale. In total for a typical business analyst, financial controller or operations specialist that is 5–10 hours per week. Between 25% and 50% of all spreadsheet hours. For a company with 5 such people that is 25–50 hours per week, 1300–2600 hours per year saved after automation.

Important: we are not removing Excel from the company. Excel is great for ad-hoc analysis, financial models, calculations. The problem is repetitive mechanical activities – and those are the automation candidates.

Power Query has been part of Excel since 2016. Most people never tried it because they do not know it exists. A big loss. Power Query solves 60% of the causes of manual sheet copying – durably, without a developer, in an hour of learning.

What Power Query can do. First: automatically combines all sheets from a folder into one table. You drop branch files into a OneDrive folder, Power Query combines them into one report that refreshes with one click. Second: cleans data (removes duplicates, joins columns, transforms formats) based on saved steps. The next report cleans itself. Third: pulls data from systems (CRM, ERP) via a connector, without manual export.

For a typical Excel user a 4-hour Power Query training pays back in 2–3 weeks. Training materials are free (Microsoft Learn, YouTube). Most companies never organised such training, even though the impact can be spectacular.

Most common first project: report from branch sheets. Classically someone copies data from 5 sheets weekly, joins, formats – 2 hours of work. With Power Query: one refresh click.

Power Automate Desktop is a free RPA version Microsoft includes in Windows 11 and the M365 package. It works like an activity recorder – you show it how to do a sequence (open Excel, copy a column, open another Excel, paste, save), then it repeats this every day for you.

What Power Automate Desktop automates well. Activities between systems that Power Query cannot connect (old applications, programs without API, websites). Automatically running a report every morning. Entering the same record into three systems. Printing reports to PDF, sending by email.

What we do not automate with Power Automate Desktop. Activities requiring real human judgement (approval, substantive evaluation). Processes that change every month (RPA breaks on every interface change). Rare activities (once every six months) – they do not pay back.

For 80% of scenarios in an average company Power Automate Desktop is enough instead of UiPath, Automation Anywhere or Blue Prism. With a simple rationale: it is in the M365 price, works locally or in the cloud (Power Automate Premium), managed by the same platform as the rest of automation.

Microsoft Copilot in Excel (available from 2024 in the Microsoft 365 Copilot package, ca. EUR 28/user/month) introduces a new layer of spreadsheet work. It does not replace Power Query or Power Automate – it complements them.

What Copilot in Excel can do. First: generates formulas from descriptions. You show Copilot a table and say add a margin column, Copilot writes =(B2-C2)/B2 or a more complex formula. Second: analyses trends. Show sales data, Copilot writes: product X sales grew 18% YoY in Q3, mainly in segment B. Third: suggests charts. Show data, Copilot proposes the best-fit chart. Fourth: writes data summaries in natural language for the leadership team.

Most useful for people who use Excel often but are not specialists. Junior controllers, store managers, project managers. For advanced analysts (advanced formulas, Power Pivot, DAX) Copilot only matches their level, does not yet exceed it.

Some of what we do in Excel should not be in Excel at all. Registers, lists, case tracking, records – this is database data, not calculation data. Excel is a bad tool here because it does not support many users at once, has no input validation, no change history.

Microsoft Lists (included in M365, in Teams as an app) is a lightweight database that replaces such sheets. Looks like Excel, but works like a database. Many people edit at once, every change saved, you can add a form for users, validation, automations.

When we migrate from Excel to Lists. First: a register (vendors, equipment, projects, clients) used by 3+ people. Second: records (leave, travel, expenses) needing validation. Third: any list with 50+ rows edited by more than one person.

Lists do not replace Excel for calculations, financial models, ad-hoc analyses. They are an alternative only for registers. That is a huge part of typical Excel use in companies – and the area where automation by migration delivers immediate impact.

How to organise a first Excel automation project. Five working days, one person, one specific spreadsheet.

Day 1: choose the sheet. Pick a report or process in Excel that annoys you the most and takes 1–3 hours per week. Ideal criteria: you repeat it at least weekly, the output is read by someone else (manager, client, leadership), the data source is digital (not scans).

Day 2: study what happens. Open Excel, run the entire process once carefully, noting every action. Most people never do this and are surprised how many steps they do by habit. Output: a list of 15–30 micro-steps.

Day 3: pick the tool. If the process is mainly joining and cleaning data – Power Query. If you pull data from an app without an API – Power Automate Desktop. If the activity needs ad-hoc judgement – Copilot in Excel. If the sheet is a register – Lists. Most first projects are Power Query.

Day 4: build the automation. 3–6 hours of work with YouTube open alongside. The first time hurts, but it is a once-and-for-all skill. Power Query can be learnt decently in 4 hours.

Day 5: test and deploy. Run the automation on new data. Check results. Show it to the person who reads your report. Write down how to refresh (usually: one button).

Four repeating mistakes in European companies. Each can be avoided.

Mistake 1: trying to automate everything at once. Someone learns about Power Query, wants to automate 15 reports in a month, burns out, drops the topic. Correction: one report to start, then the next.

Mistake 2: automation without technical support. Power Query, Power Automate Desktop can be learnt alone, but every first project with a consultant (2–4 hours) goes 3x faster and without frustration.

Mistake 3: no automation documentation. Someone builds an automation, goes on leave, nobody knows how to refresh it. Correction: a short instruction in a file or on a SharePoint page for every automation.

Mistake 4: staying in Excel when you should clearly migrate to Lists or a dedicated system. If the sheet grows to 10k rows and 8 people edit it, Excel will not cope even with the best automation. Correction: conscious migration.

Is Power Query in my Excel version? In Excel 2016 and newer – yes, as a built-in add-in (Data tab → Get & Transform Data). In older – no. Office 365 and Microsoft 365 always have Power Query.

Do I need Microsoft Copilot in Excel to automate work? No. Power Query and Power Automate Desktop are free and enough for 80% of cases. Copilot adds comfort and speed but is not essential.

How much does Excel automation deployment cost in a company? In-house: EUR 0, just time. With a consultant for 5 processes: EUR 3.5–9k (audit, training, building first 5 automations). Full programme for a 50-person company: EUR 7–18k one-off.

Can I automate reports from systems (SAP, Sage, accounting) into Excel? Yes. Power Query has connectors to most popular systems. SAP, Sage, dedicated integrations require connector configuration (typically 1–3 days of consultant work).

Will Power Query replace BI (Power BI, Tableau)? No. Power Query is data preparation. Power BI is visualisation and reporting. Many companies use Power Query for preparation, then load data into Power BI for the leadership team.

Excel will stay in companies for a long time. We do not try to remove it. We try to free people from 5–10 hours per week of manual copying they do in it today. Power Query handles most of those hours. Power Automate Desktop – the rest. Copilot adds comfort.

The worst moment for Excel automation is yesterday. The best moment is now – with one spreadsheet you hate the most. 5 working days, EUR 0, one person. After a week you have the first impact and the organisation starts thinking differently about spreadsheet work.

A fuller picture of automation tool choice in our article on Power Automate vs n8n vs Make. A fuller picture of the first automation project in our article on business automation – where to start.