Source: https://oguzerol.com/en/excel-automation/ # Excel Automation: Which Job Needs Formulas, Power Query, VBA, Office Scripts or a Web App? [Oğuz Erol](https://oguzerol.com/en/about-me/) | 18 min read | Published: 29 July 2026 | Updated: 24 October 2026 ## Quick summary Formulas, Power Query, VBA, Office Scripts or a web app: which one fits your repeating Excel job? A four-question decision table and a worked sample month. Excel automation is not one tool but five routes: formulas, Power Query, VBA macros, Office Scripts with Power Automate, and a custom web app. Four questions decide whether you can stay in Excel: how often the job repeats, how many people touch it, whether some data must stay hidden from some people, and whether you need to know later who changed what. If the last two answers are no, Excel and Microsoft 365’s own tools are usually enough. In the sample month below, a fictional distributor’s six repeating Excel jobs take 51.6 hours a month. Only one, the sales commission, needs a web app; 69% of the hours saved come from formulas, Power Query, VBA, and Office Scripts with Power Automate. I build VBA macros, Python scripts, AI-assisted workflows and, where needed, custom web apps. For Power Query, Office Scripts and Power Automate I tell you which is enough; if you want me to set it up, the scope is agreed in writing first. ## Which Excel job should be automated with which tool? The four questions decide whether you stay in Excel; two more criteria pick the tool inside it: one file or many, and someone at the computer or nobody. The table is my starting point, not a measured rule. The permissions row decides on its own; if a job fits several other rows, pick the simplest route covering all of them. | the job | my starting point | where it breaks | | --- | --- | --- | | Monthly or rarer, needs judgment each time | By hand, with a fixed template | The template goes stale if nobody updates it | | One person, a calculation or lookup in one file | Formulas and dynamic arrays (XLOOKUP, FILTER) | A source column moves and the result is silently wrong | | One person, combining same-layout files | Power Query | Stops when a column name changes; not enough for mixed layouts | | One person at the computer, desktop actions: open, save, export PDF, print | VBA macro | Doesn’t run on the web; blocked on Windows in files from the internet | | Nobody at the computer, at a set time or on an event, file in OneDrive or SharePoint | Office Scripts + Power Automate | Timeouts on long jobs; can’t react to events inside Excel (such as a cell change) | | Several people enter and approve, each sees only their own data, you need who changed what | Custom web app | Scope grows, so the first version stays small | Touching and seeing differ: a report six leaders only read is one person’s job; a commission sheet three people touch but 43 see, each rep only their own row, is where permissions start. **Where does the file live?** Read each row alongside file location: local workbooks depend on desktop conditions; OneDrive/SharePoint files depend on cloud connections and permissions. The “Where does the Excel data come from?” section below explains the difference. ## Sample month: six Excel jobs at a 190-person distributor *Sample data, fictional.* A distributor of hydraulic and pneumatic equipment has 190 employees, three warehouses, 34 sales reps in six teams and 48 dealers. The finance manager asked the team to log a month’s time on every repeating Excel job: | job | frequency · hands-on · permissions/audit | before (h/month) | route | after (h/month, assumption) | | --- | --- | --- | --- | --- | | 1. Selling prices from the supplier price list | weekly · 1 person · none | 8.0 | Formula | 2.0 | | 2. Critical-stock list from three warehouse exports | weekly · 1 person · none | 12.0 | Power Query | 1.0 | | 3. Open-orders report | every working day · 1 person · none | 8.8 | Office Script + Power Automate | 1.8 | | 4. One-page sales summary PDF for 48 dealers | monthly · 1 person · none | 4.8 | VBA macro | 0.5 | | 5. Sales rep commission | monthly · 3 people, 43 see it · yes | 16.0 | Web app | 3.5 | | 6. Supplier scoring | quarterly · 3 people · none | 2.0 | Template, by hand | 2.0 | | **Total** | | **51.6** | | **10.8** | “Before” is the time logged in the sample data; “after” is my assumption of the checks and exceptions left (about 15 minutes a week in Job 2, 5 minutes a day in Job 3). That saves 40.8 hours a month (79%): 28.3 hours (69%) from Excel and Microsoft 365 tools, 12.5 hours (31%) from the web app. Job 1 prices 1,240 products. In Job 3 the ERP drops an export into SharePoint each morning. Job 5’s 43 viewers are 34 reps, 6 team leaders, the preparer, the sales manager and HR (management and auditor excluded); sample rule: a rep whose net sales (sales minus returns) exceed target earns 1.2% of them. Job 6 is a judgment, not a rule. ### The decision at month end Last month 5 of 34 reps disputed their commission, and the sheet held 2 hand-typed corrections nobody could attribute. Rep T17’s dispute settled it. T17 sold 1,340,000 with returns of 46,500: correct net 1,293,500, 103.5% of the 1,250,000 target, commission 15,522. The sheet had deducted the return twice, so net fell to 1,247,000, 99.8% of target, and commission came out at zero. Only T17 noticed. The finance manager moves commission to a web app: 43 people see the same data, each rep should see only their own row, and corrections must record who made them. Four jobs go to Excel and Microsoft 365 tools; the sixth stays manual. The stock list, not commission, was chosen as the first job. The most common mistake I see in automation projects is picking the most complex process to automate first. The stock list is one person’s fixed-column job and saves 11 hours a month alone. I recalculated these figures in Python; try them with your own. ## Where are formulas and dynamic arrays enough, and where do they break? Formulas are enough for one person’s calculations and lookups in one file. They break when the source’s column order changes, usually with a wrong number instead of an error. The first formula called supplier columns by letter (A product code, C list price in EUR, D discount); in the price sheet B is the product code, C the markup rate (the percentage added on top of cost), H1 the exchange rate: `=ROUND(XLOOKUP(B2,Supplier!A:A,Supplier!C:C)*(1-XLOOKUP(B2,Supplier!A:A,Supplier!D:D))*$H$1*(1+C2),2)` One week the supplier inserted a “Campaign %” column between C and D, and the formula read it as the discount: all 1,240 prices came out 48% too high on average, at least 27%, with no error. I suggest finding columns by header name. Three helper cells locate the headers: - J1: `=MATCH("Product code",Supplier!$1:$1,0)` - J2: `=MATCH("List price (EUR)",Supplier!$1:$1,0)` - J3: `=MATCH("Discount %",Supplier!$1:$1,0)` Selling price: `=ROUND(XLOOKUP(B2,INDEX(Supplier!$A:$Z,0,$J$1),INDEX(Supplier!$A:$Z,0,$J$2))*(1-XLOOKUP(B2,INDEX(Supplier!$A:$Z,0,$J$1),INDEX(Supplier!$A:$Z,0,$J$3)))*$H$1*(1+C2),2)` With row number 0, INDEX returns a reference to the whole column ([Microsoft Support](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)), so added or reordered columns don’t matter. A renamed header turns a J cell into #N/A and no prices are calculated: a visible error, not a silent one. A code missing from the list also returns #N/A ([Microsoft Support](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929), 16 October 2026). If your Excel has no XLOOKUP, INDEX with MATCH does the same lookup. Depending on your regional settings, the argument separator may be a semicolon instead of a comma. I recalculated both in Python on the sample products, not in Excel; try them on a copy of your file: | case | columns by letter | columns by header | | --- | --- | --- | | Supplier list as usual | 1,240 correct | 1,240 correct | | “Campaign %” column inserted | 1,240 wrong, no error | 1,240 correct | | Only the “List price (EUR)” header renamed | 1,240 correct, nobody notices the rename | #N/A, calculation stops | Two dynamic-array limits (read 16 October 2026): spilled formulas aren’t supported inside Excel tables ([Microsoft Support](https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531)); arrays linked to another workbook work only when both are open, returning #REF! on refresh otherwise ([Microsoft Support](https://support.microsoft.com/en-us/excel/functions/filter-function)). So Job 1 pastes the supplier list into the same workbook. ## Where is Power Query enough, and where does it break? Power Query is enough for combining files of the same type with the same columns from a folder into one table ([Microsoft Learn](https://learn.microsoft.com/en-us/power-query/combine-files-overview), 16 October 2026); Job 2 is exactly that. When a column name changes, the query stops with “The column … of the table wasn’t found” ([Microsoft Learn](https://learn.microsoft.com/en-us/power-query/dealing-with-errors)), which beats producing a wrong list. For files from different systems in different layouts it isn’t enough; I cover that case on the [controlled Excel import screen page](https://oguzerol.com/en/controlled-excel-import-screen/). ## Where is a VBA macro enough, and where does it break? A macro is enough for actions one person runs at the computer in desktop Excel: open, filter, save as PDF, print. In Job 4 it saves 48 dealer PDFs; 4.8 hours become half an hour. Microsoft documents three limits (read 16 October 2026): - **No web.** In Excel for the web you can’t create, run or edit VBA macros; they stay in the workbook but don’t run in the browser ([Microsoft Support](https://support.microsoft.com/en-us/excel/work-with-vba-macros-in-excel-for-the-web)). - **Blocked on Windows in files from the internet.** Office on Windows blocks macros by default in files from the internet or email attachments; Mac, Android, iOS and the web aren’t affected. Files on a network share are blocked too unless the share is in the Local intranet or Trusted sites zone (a share opened by IP address, for example, isn’t) ([Microsoft Learn](https://learn.microsoft.com/en-us/microsoft-365-apps/security/internet-macros-blocked)). Keep macro files in one location instead of emailing them, and have IT confirm it is in a trusted zone. - **Not unattended.** VBA has no Power Automate connector; every supported scenario involves a user attending the run ([Microsoft Learn](https://learn.microsoft.com/en-us/office/dev/scripts/resources/vba-differences)). Which jobs suit a macro is on my [Excel macro (VBA) page](https://oguzerol.com/en/excel-macro-vba-development/). ## When are Office Scripts with Power Automate the right choice? They are right when a job must run at a set time or on an event with nobody at the computer: scripts run from Power Automate, so scheduled or event-driven flows update a workbook without opening Excel ([Microsoft Learn](https://learn.microsoft.com/en-us/office/dev/scripts/resources/vba-differences), 16 October 2026). Job 3 therefore needs Power Automate: a script alone runs only when someone starts it, and the flow runs it each morning to flag late orders. A script only accesses the workbook, so a CSV export needs an extra flow step to bring it into one. Limits from Microsoft’s platform limits page (read 16 October 2026; [Microsoft Learn](https://learn.microsoft.com/en-us/office/dev/scripts/testing/platform-limits)): - Using Office Scripts in Power Automate requires a Microsoft 365 business licence. - A **120-second timeout** for synchronous Power Automate operations; long scripts must be optimised or split. - **1,600 “Run script” calls** per user per day. Job 3 uses one. - Scripts run through Power Automate can’t make external API calls. - No Excel-level events such as a cell change ([Microsoft Learn](https://learn.microsoft.com/en-us/office/dev/scripts/resources/vba-differences)). An admin may have turned scripts off; check for the Automate tab. Without it, a macro still works, but someone must run it each morning. ## Where does the Excel data come from? File location and the route by which data arrives matter as much as the operation itself. Before using the decision table, identify which of these situations applies: 1. **A OneDrive or SharePoint workbook.** With appropriate permissions, a cloud flow can reach it while your computer is off. The [Excel Online (Business) connector](https://learn.microsoft.com/en-us/connectors/excelonlinebusiness/) works with those document libraries. Table actions such as adding or listing rows require an Excel Table created through Insert → Table; a plain cell range is different. 2. **A local workbook on one computer.** The standard cloud Excel connection cannot directly open a file under `C:\`. The workbook can move to cloud storage, or use desktop automation, a macro or a suitable local connection. If it stays local, machine availability, session requirements and scheduling must be agreed in writing. 3. **An email attachment.** Define the sender, subject, file type and duplicate-file behaviour first. Save the attachment to a staging folder, validate headers and data types, then process it. Moving a column must not silently send values to the wrong field. 4. **Data visible only in an older application’s screen.** Look for reports, CSV exports or a supported API first. Screen automation is a later option: changes to a window, button or session can break it. Verify the resulting destination record as well as a completion message. [Microsoft’s remedy for a missing Excel table](https://learn.microsoft.com/en-us/troubleshoot/power-platform/power-automate/flow-creation/unable-to-find-excel-online-table-in-microsoft-flow) is to format the data as an actual table. Colouring the header row does not do that. For an initial discussion, bring the full file path, who writes the data and when, and who will notice a failure. Ownership is part of the workflow: removing an employee’s access can break its connections. Plan organisational ownership, a backup maintainer and reauthorisation; sharing a password is not a handover process. ## When do you need a custom web app? You need one when several people enter or approve the same data, each should see only their own part, and who changed what must be on record. Only Job 5 meets all three. What Excel offers (read 16 October 2026): - **Show Changes** shows who changed what, where and when, for the past 365 days ([Microsoft Support](https://support.microsoft.com/en-us/excel/show-changes-that-were-made-in-a-workbook)). Formatting, filtering, hiding and PivotTable operations aren’t shown, values changed by VBA or an add-in can lose their previous value, and full tracking needs everyone on an Excel that supports co-authoring ([Microsoft Support](https://support.microsoft.com/en-us/excel/get-help-with-show-changes-in-excel)). - **Worksheet protection** isn’t a security feature; it only prevents changes to locked cells ([Microsoft Support](https://support.microsoft.com/en-us/office/protect-a-worksheet-3179efdb-1285-4d49-a9c3-f4ca36276de6)). It can’t make each rep see only their own commission; the Excel way is 34 separate files. **Aren’t Power BI, Power Apps or a SharePoint list enough?** Power BI row-level security can let each rep see only their own rows in a report. Microsoft says it only restricts users with Viewer permissions, not workspace Admin, Member or Contributor roles, so anyone who can edit the report sees every row ([Microsoft Learn](https://learn.microsoft.com/en-us/fabric/security/service-admin-row-level-security), 16 October 2026). As a reporting layer, it still needs somewhere for disputes and corrections (my inference). If someone in-house can build and maintain Power Apps or a SharePoint list, consider those too; the test is the same: is the rule applied when data is entered, does each person see only their own row, is who changed what on record? T17’s questions stayed open in Excel: where was the return deducted twice, who typed the correction? In a web app the business rule is applied when data is entered, not when someone checks the sheet at month end. Rules I suggest: - **No return deducted twice.** Returns carry document numbers; a repeated number isn’t imported and the uploader sees it. - **Corrections need a reasoned record** tied to a dispute number; nobody types into a cell. - **Approved months lock.** Later errors go into next month as a “prior-month correction” line. Screens and flows can be set up as needed; which go into the first version is set in writing in the first step. The path from spreadsheet to app is in my [guide to turning Excel into a web app](https://oguzerol.com/en/turning-excel-into-a-web-app-with-ai/). [![Forms, business systems and spreadsheets feed shared team panels and reports through matching and data checks.](https://oguzerol.com/gorseller/hizmet-infografikleri/09-veri-entegrasyonu-en.svg)](https://oguzerol.com/gorseller/hizmet-infografikleri/09-veri-entegrasyonu-en.svg) Depending on the access your systems provide, I can validate and transfer data into shared records and reports. ### Who sees what and who approves in the commission app? My basic rule: nobody approves their own row. - **Rep:** sees only their own sales, returns and commission; opens disputes. - **Team leader:** sees the team’s commissions, comments on disputes, changes nothing. - **Preparer** (sales operations): uploads the export, matches returns to invoices, opens corrections. - **Approver** (sales manager): decides disputes, approves the month, can’t approve a correction they opened; their own commission is approved by the general manager or finance. - **Payroll** (HR): sees and downloads only the approved month’s name-and-amount list. - **Management and auditor:** see everything, change nothing. Everyone signs in with their own account, or the rule means nothing. ### What does the audit trail record? The audit trail I suggest keeps a line for every action touching commission: who, when, which rep’s row, old and new value, reason, dispute number. How it is stored, and whether nobody can delete it, is set in writing in the first step. T17’s dispute gets a number, the preparer reverses the double deduction under it, the manager approves, and commission goes from 0 to 15,522: three lines that answer “why did this change?” months later. ## What does AI do here, and where does it stop? AI suggests; a person decides. The commission rule, duplicate-return check and month lock are the app’s own rules. AI helps in two places: 1. **Finding the rows behind a dispute.** T17 writes “I think last month’s return was deducted twice”; the model suggests the return and invoice from T17’s rows, and the preparer confirms. 2. **Matching free-text return notes to invoices.** For a note like “return on F-2291, 3 valves” the model suggests the invoice; nothing enters the commission until the preparer approves. No row goes to the model without passing the permission filter first: on T17’s dispute it sees only T17’s rows, and its answer names only people that user can see. Jobs 1 to 4 don’t need AI; their rules are fixed. As for Copilot or Python in Excel: I haven’t assessed them on this page, so I make no claim; a job needing permissions and an audit trail raises the same questions in any tool. **Commission is personal data;** under the UK GDPR it must be limited to what the purpose needs ([Art. 5(1)(c)](https://www.legislation.gov.uk/eur/2016/679/article/5)). The model gets a rep code, not a name; I list which fields reach it and give no legal advice. **In Turkey**, commission is personal data under KVKK Article 3(1)(d), and sending such a row to a service abroad may bring in Article 9, as amended by [Article 34 of Law No. 7499](https://www.resmigazete.gov.tr/eskiler/2024/03/20240312-1.htm) (Official Gazette 12 March 2024, No. 32487, in force 1 June 2024). I read the current text on mevzuat.gov.tr on 16 October 2026; confirm with your legal adviser. ## If Excel itself is enough, don’t pay for automation Excel and Microsoft 365 tools handled four jobs; leaving one manual was right. I wouldn’t suggest paying for automation when: - **The job is monthly or rarer and needs judgment,** like Job 6. A template with fixed columns and drop-downs is enough. - **One person does it and uses the result.** Without roles or an audit trail, formulas or Power Query are enough, and a colleague who knows Excel can build them. - **Your ERP’s report or scheduled export already does it.** Use that first. - **The time saved is small next to upkeep,** like a half-hour monthly job that breaks whenever the source changes. ## How do we start, and who owns the data and code? In the first step we list your repeating Excel jobs with frequency, who touches and sees each, what must stay hidden and whether you need an audit trail; ideally the team logs a month’s time first. The output puts in writing each job’s route and order and, for a web app, its roles, first-version scope and estimated timeline. Formulas, queries and macros live inside your Excel files. Which account scripts and flows run under is set in writing in the first step. For a job that needs a web app: the app runs on a server (a VPS) that can be opened in your company’s name. The data is yours, exportable as Excel or CSV whenever you want. In a small or mid-sized team there is no per-user licence; the cost is the server’s monthly fee plus maintenance. The source code sits in a repository you can access, with run instructions, a dependency list and known limitations. Because commission data is personal data, I assess access, security, hosting and maintenance separately at the start. **How do users sign in, and what if the ERP changes?** Sign-in, how earlier months and the audit trail are kept after an ERP change, and which queries then change are all set in writing in the first step. Send a short list of your repeating Excel jobs and their monthly time through the [contact page](https://oguzerol.com/en/contact/); in the first meeting we sort which stay in Excel. ## Frequently Asked Questions ### Isn’t a Python script also an option? Yes. A Python script outside Excel can read and write files on a schedule, helping where Power Query isn’t enough. The criteria don’t change: if several people enter and approve data and you need an audit trail, a script doesn’t replace a web app. ### What happens to the automation if the person who knows Excel leaves? Formulas, queries and macros keep working, but when they break nobody may know why. Add a “How it works” sheet: where sources come from, what each query does, where the check cells (like Job 1’s J cells) are. ### How do I know the automation is producing wrong results? Add a control total: do the result’s row count and amount total match the source? In Job 2 the three warehouse files’ row counts should add up to the combined list’s. It catches silent errors like Job 1’s: the dangerous automation is the one that is wrong without an error. [Back to top ↑](https://oguzerol.com/en/excel-automation/#icerik) ## You may also find these useful [Excel Macro (VBA) Development: Which Jobs Suit a Macro and What Makes One RobustIs your repetitive Excel job a good fit for a macro? A decision table, a 12-region report example, annotated VBA code and the security settings.](https://oguzerol.com/en/excel-macro-vba-development/)[ChatGPT for Excel: Writing Formulas, Catching Errors, AnalysisHow to prompt ChatGPT for Excel formulas and catch separator, shifting-range and blank-cell errors before they reach a report. Four worked examples.](https://oguzerol.com/en/chatgpt-for-excel/)[Excel Multi-Select Dropdown: VBA Code and AlternativesMultiple selections in an Excel dropdown: why data validation picks one value, a working VBA macro, a macro-free separate-rows method and how to choose.](https://oguzerol.com/en/excel-multi-select-dropdown/) [Browse all articles →](https://oguzerol.com/en/articles/)