Most freelancers sell time. This lesson shows you how to build an Excel and Power Query automation service that earns recurring revenue from work you've already done. Learn to identify, build, package, and price workflow automations as a monthly retainer offer.

Picture this: a small logistics company has an operations manager who spends every Monday morning doing the same thing. She downloads three separate CSVs from their transport management system, copies the data into a master Excel file, manually calculates route efficiency percentages, reformats supplier names to match their internal codes, and builds a summary table for the weekly ops meeting. The whole process takes about three hours. She's been doing it every week for two years.
That's 312 hours — roughly eight full work weeks — spent on a process that you could automate in a single afternoon.
This is the heartbeat of a data automation freelance service. It's not glamorous. It's not building a machine learning model or architecting a data warehouse. But it's deeply valuable, it's everywhere, and because it recurs every week or month, it becomes the foundation of a recurring revenue model that most project-based freelancers never figure out how to build. When you package this kind of automation properly — with clean deliverables, a maintenance commitment, and a sensible pricing structure — you stop trading hours for dollars and start earning money on work you've already done.
By the end of this lesson, you'll know exactly how to identify these opportunities, build the automation using Excel macros and Power Query, package it as a productized service, and price it for recurring monthly revenue.
What you'll learn:
You should be comfortable with:
If you're just starting to think about the business side of freelancing, Starting a Data Freelancing Business: Essential Tools, Pricing Strategies, and Landing Your First Clients provides a solid foundation to build from before diving into service packaging.
The highest-value thing you can do at the start of any client relationship is listen for pain disguised as routine. When a client says "we do this every week," your ears should perk up. When they say "it's annoying but it's just how it is," that's your opening.
The sweet spot for Excel/Power Query automation services is a workflow that meets three criteria:
You're not looking for one-off data cleaning jobs. You're looking for processes — things the client will need to do again next month regardless of whether you're involved.
Tip
During discovery conversations, ask "Walk me through your Monday morning routine" or "What's the first thing you do when you get the sales data at the end of the month?" These prompts surface repeating workflows far better than "do you have any automation needs?"
Here's a quick diagnostic framework. During a discovery call, listen for these trigger phrases:
| What they say | What it signals |
|---|---|
| "We download this every week from [system]" | Repeatable data extraction |
| "Then we copy it into our master file" | Manual consolidation step |
| "We have to rename things because..." | Data normalization need |
| "Then I build a table for the meeting" | Report generation opportunity |
| "It takes a few hours but it is what it is" | High-value automation target |
Once you've identified the workflow, your next move is to map it. Ask the client to send you a sample of the raw input files and a sample of the finished output. That gap between input and output is where you live.
Before you open Excel, you need a workflow map. This sounds like overhead, but it does two things: it prevents you from building the wrong thing, and it becomes your documentation later.
Let's work through a realistic example throughout this lesson. Our client is a mid-size wholesale distributor. Every month, their sales coordinator receives:
orders_MMYYYY.csv)customer_accounts.xlsx)pricing_current.csv)She manually combines these into a monthly sales performance report that shows:
She spends 4-5 hours on this every month. She's been doing it for 18 months.
Your workflow map looks like this:
INPUT FILES
├── orders_MMYYYY.csv → Raw transaction records
├── customer_accounts.xlsx → Account tiers, regions, rep assignments
└── pricing_current.csv → Cost and list prices by SKU
TRANSFORMATION STEPS
├── 1. Load and clean orders data
│ ├── Parse order date, extract month/year
│ ├── Standardize product codes (remove legacy prefixes)
│ └── Filter out cancelled orders (Status = "VOID")
├── 2. Load customer accounts
│ └── Merge account tier and region onto orders
├── 3. Load pricing
│ └── Join cost price to calculate gross margin per line
├── 4. Build summary tables
│ ├── Sales by account (current month)
│ ├── Prior month comparison
│ ├── Margin by category
│ └── Flag accounts with >20% drop
└── 5. Format and distribute
├── Apply company formatting
├── Add conditional formatting to flagged rows
└── Save formatted output for email
OUTPUT
└── Monthly_Sales_Report_MMYYYY.xlsx (ready to send)
This map tells you exactly what Power Query handles (steps 1-4) and what a VBA macro handles (step 5). The division of responsibility matters: Power Query is your data pipeline; VBA is your automation glue.
Note
Some freelancers try to do everything in VBA. Resist this urge. Power Query is vastly more maintainable, debuggable, and client-friendly than VBA-based data transformations. Use VBA for what it's genuinely good at: triggering refreshes, formatting outputs, saving files, and sending emails.
Open a new Excel workbook. This will become your automation workbook — the file the client uses every month. Set up a dedicated folder structure on the client's machine (or a shared drive) like this:
/Monthly_Sales_Automation/
├── /inputs/ ← Client drops fresh files here each month
├── /outputs/ ← Completed reports land here
├── /archive/ ← Previous months' inputs
└── SalesReport_MASTER.xlsm ← Your automation workbook
In Power Query, connect to each input file using a folder-relative path. This is critical — if you hardcode C:\Users\Sarah\Desktop\orders.csv, the query breaks the moment Sarah gets a new laptop.
Go to Data → Get Data → From File → From Text/CSV. When Power Query opens, go into Advanced Editor and modify the Source step to use a relative or parameterized path.
The cleanest approach for client deployments is to use a named cell in your workbook as the base path. Create a named range called BasePath on a "Settings" sheet (something like C:\Monthly_Sales_Automation\inputs\). Then in Power Query, reference it via a parameter:
// In Power Query: "fnGetBasePath" function query
let
BasePath = Excel.CurrentWorkbook(){[Name="BasePath"]}[Content]{0}[Column1]
in
BasePath
Now your orders query looks like this:
let
BasePath = fnGetBasePath(),
FilePath = BasePath & "orders_" & Date.ToText(Date.From(DateTime.LocalNow()), "MMyyyy") & ".csv",
Source = Csv.Document(
File.Contents(FilePath),
[Delimiter=",", Columns=12, Encoding=65001, QuoteStyle=QuoteStyle.None]
),
PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
// Standardize column types
TypedTable = Table.TransformColumnTypes(PromotedHeaders, {
{"OrderDate", type date},
{"OrderID", type text},
{"CustomerCode", type text},
{"ProductCode", type text},
{"Quantity", Int64.Type},
{"UnitPrice", type number},
{"Status", type text}
}),
// Remove voided orders
FilteredOrders = Table.SelectRows(TypedTable, each [Status] <> "VOID"),
// Standardize product codes (remove legacy "LGC-" prefix)
CleanProductCodes = Table.TransformColumns(FilteredOrders, {
{"ProductCode", each Text.Replace(_, "LGC-", ""), type text}
}),
// Extract month/year for grouping
AddedMonthYear = Table.AddColumn(CleanProductCodes, "ReportMonth",
each Date.ToText([OrderDate], "yyyy-MM"), type text)
in
AddedMonthYear
Warning
The filename pattern orders_MMyyyy.csv only works if your client's ERP system generates consistent filenames. If the filename format varies, build a step that lists all CSV files in the input folder and filters to the most recent one using Folder.Files(). Never assume consistent filenames — always verify with the client first.
Once all three source queries are built, create a final SalesData query that merges them:
let
Orders = OrdersClean,
// Merge customer accounts
MergedAccounts = Table.NestedJoin(
Orders, {"CustomerCode"},
CustomerAccounts, {"AccountCode"},
"AccountDetail", JoinKind.LeftOuter
),
ExpandedAccounts = Table.ExpandTableColumn(
MergedAccounts, "AccountDetail",
{"AccountName", "Region", "AccountTier", "SalesRep"},
{"AccountName", "Region", "AccountTier", "SalesRep"}
),
// Merge pricing
MergedPricing = Table.NestedJoin(
ExpandedAccounts, {"ProductCode"},
ProductPricing, {"SKU"},
"PricingDetail", JoinKind.LeftOuter
),
ExpandedPricing = Table.ExpandTableColumn(
MergedPricing, "PricingDetail",
{"CostPrice", "Category"},
{"CostPrice", "Category"}
),
// Calculate line-level gross margin
AddedMargin = Table.AddColumn(ExpandedPricing, "GrossMargin",
each ([UnitPrice] - [CostPrice]) * [Quantity], type number),
AddedMarginPct = Table.AddColumn(AddedMargin, "MarginPct",
each if [UnitPrice] * [Quantity] = 0 then null
else ([UnitPrice] - [CostPrice]) / [UnitPrice], type number)
in
AddedMarginPct
Load each of your summary tables (Sales by Account, Margin by Category, MoM comparison) as separate queries that reference SalesData as their source. This way, refreshing the workbook re-runs the entire pipeline from raw inputs to formatted summaries in one shot.
Power Query handles the data. VBA handles the ceremony — the formatting, file-saving, and distribution that turns a refreshed data model into a finished deliverable. The macro the client ultimately runs is a single button click.
In the VBA editor (Alt+F11), insert a new module and write your master procedure:
Sub RunMonthlySalesReport()
Dim ws As Worksheet
Dim reportMonth As String
Dim outputPath As String
Dim outputFile As String
' Grab settings from the Settings sheet
reportMonth = Format(Date, "MMyyyy")
outputPath = ThisWorkbook.Sheets("Settings").Range("OutputPath").Value
outputFile = outputPath & "Monthly_Sales_Report_" & reportMonth & ".xlsx"
' Step 1: Refresh all Power Query connections
Application.StatusBar = "Refreshing data connections..."
Call RefreshAllQueries
' Step 2: Format the output sheets
Application.StatusBar = "Applying formatting..."
Call FormatSalesReport
' Step 3: Flag underperforming accounts
Application.StatusBar = "Flagging accounts..."
Call FlagDroppedAccounts
' Step 4: Save output file
Application.StatusBar = "Saving report..."
Call SaveReportOutput(outputFile)
' Step 5: Done
Application.StatusBar = False
MsgBox "Monthly Sales Report complete!" & vbCrLf & _
"File saved to: " & outputFile, vbInformation, "Report Complete"
End Sub
Sub RefreshAllQueries()
Dim conn As WorkbookConnection
Dim startTime As Double
Dim timeoutSeconds As Integer
timeoutSeconds = 120 ' 2-minute timeout
' Refresh all Power Query connections synchronously
For Each conn In ThisWorkbook.Connections
If conn.Type = xlConnectionTypeOLEDB Or _
conn.Type = xlConnectionTypePowerQuery Then
conn.Refresh
End If
Next conn
' Wait for background refresh to complete
startTime = Timer
Do While ThisWorkbook.Queries.Count > 0 And _
(Timer - startTime) < timeoutSeconds
DoEvents
' Check if any connection is still refreshing
Dim stillRefreshing As Boolean
stillRefreshing = False
For Each conn In ThisWorkbook.Connections
If conn.OLEDBConnection.Refreshing Then
stillRefreshing = True
Exit For
End If
Next conn
If Not stillRefreshing Then Exit Do
Loop
End Sub
Tip
Set BackgroundQuery = False on each connection before triggering the refresh. This forces Power Query to complete before the next VBA line executes, which prevents race conditions where your formatting macro runs before the data is loaded.
Sub FlagDroppedAccounts()
Dim ws As Worksheet
Dim lastRow As Long
Dim flagRange As Range
Dim dropThreshold As Double
Set ws = ThisWorkbook.Sheets("MoM Comparison")
dropThreshold = -0.2 ' Flag accounts with >20% drop
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Clear existing conditional formatting on this range
ws.Range("A2:H" & lastRow).FormatConditions.Delete
' The MoM Change % is in column G (adjust to your layout)
Set flagRange = ws.Range("A2:H" & lastRow)
With flagRange.FormatConditions.Add(Type:=xlExpression, _
Formula1:="=$G2<" & dropThreshold)
.Interior.Color = RGB(255, 199, 206) ' Light red fill
.Font.Color = RGB(156, 0, 6) ' Dark red text
.Font.Bold = True
End With
' Add a "REVIEW" flag in column I for filtered exports
Dim i As Long
For i = 2 To lastRow
If ws.Cells(i, 7).Value < dropThreshold Then
ws.Cells(i, 9).Value = "REVIEW"
Else
ws.Cells(i, 9).Value = ""
End If
Next i
End Sub
Sub SaveReportOutput(outputFile As String)
Dim outputWB As Workbook
' Copy only the output sheets to a new workbook
' (don't send clients the raw data or Settings sheet)
Dim sheetsToExport As Variant
sheetsToExport = Array("Sales by Account", "Margin by Category", _
"MoM Comparison", "Executive Summary")
' Copy sheets to a new workbook
ThisWorkbook.Sheets(sheetsToExport).Copy
Set outputWB = ActiveWorkbook
' Remove any lingering connections from the copy
Dim conn As WorkbookConnection
For Each conn In outputWB.Connections
conn.Delete
Next conn
' Save as xlsx (not xlsm - no macros in the output file)
Application.DisplayAlerts = False
outputWB.SaveAs Filename:=outputFile, _
FileFormat:=xlOpenXMLWorkbook
outputWB.Close SaveChanges:=False
Application.DisplayAlerts = True
End Sub
The output file is a clean .xlsx with no connections, no macros, and no backend complexity. The client can open it, forward it, print it — it just works. The .xlsm master file is yours.
Your technical solution needs to be invisible to the client. What they see is a simple, non-threatening interface. Create a "Control Panel" sheet in your workbook with:
RunMonthlySalesReport)BasePath and OutputPathHide all other sheets from the client's view using xlSheetVeryHidden via VBA (not just the standard hide — that way they can't accidentally unhide them):
Sub LockWorkbookView()
Dim ws As Worksheet
Dim visibleSheets As Variant
visibleSheets = Array("Control Panel")
For Each ws In ThisWorkbook.Sheets
If Not IsInArray(ws.Name, visibleSheets) Then
ws.Visible = xlSheetVeryHidden
End If
Next ws
End Sub
Key insight
The cleaner your user interface, the more professional your service feels, and the less likely the client is to tinker with the internals. A button on a clean sheet feels like software. A complicated workbook with 12 visible tabs feels like a liability.
Protect the VBA project with a password (Tools → VBAProject Properties → Protection). This isn't about being secretive — it's about protecting the client from accidentally breaking something and protecting you from unexpected support requests at 11pm.
Here's the part most freelancers skip, and it's also the part that justifies your recurring fee: documentation.
Deliver a one-page "Automation Guide" with every implementation. It doesn't need to be elaborate. It needs to answer four questions:
Example error table:
| Error Message | Likely Cause | What to Do |
|---|---|---|
| "File not found: orders_112024.csv" | Input file not dropped in /inputs/ folder, or filename doesn't match expected format | Check the filename matches orders_MMYYYY.csv exactly, then rerun |
| "Query refresh timed out" | Input file is unusually large or network is slow | Try running again; if it persists, contact [you] |
| "Could not load customer_accounts.xlsx" | Customer accounts file has been renamed or moved | Confirm the file is in the /inputs/ folder with correct name |
| Blank output file | All rows filtered out — possibly wrong month's data dropped | Verify the input files are for the correct reporting period |
This document does something subtle but powerful: it frames you as an ongoing service provider, not a one-time contractor. The existence of an SLA and a contact number for "when something breaks" plants the expectation of a continued relationship.
This connects directly to the broader principle of building recurring revenue with data retainer clients — you're not just delivering a file, you're delivering operational continuity.
This is where you shift from "technical contractor" to "service provider." The automation workbook is an asset. The monthly retainer is what you charge to maintain, update, and support that asset.
Structure your offer in three parts:
Part 1: The Build Fee (one-time) This covers discovery, mapping the workflow, building the Power Query pipeline and VBA macros, testing, documentation, and handover. Price this based on complexity. For the wholesale distributor example above, a realistic build fee is £800–£1,500 depending on your market and experience level.
Part 2: The Monthly Retainer This covers:
Price this at 15–25% of the build fee per month. For a £1,200 build, that's £180–£300/month. At £240/month, the client pays back the build fee value within five months — and you're earning money every month for maintaining something you built once.
Part 3: Expansion Projects (optional add-ons) Once the client trusts your work, they'll naturally bring you new workflows. Each new automation module can be scoped and priced as an additional build, potentially increasing their retainer when you add it to your monthly support scope.
Key insight
The build fee covers your time to create the asset. The retainer covers the ongoing value the asset delivers, not your time to maintain it. This distinction matters enormously for how you frame the conversation. If the automation saves the client 5 hours a month at £60/hour of their own time, they're saving £300/month in productivity — your £240/month retainer is a clear net win.
If you're new to structuring this kind of offer, Building Productized Services with Power BI and Excel: From Custom Consultant to Product Owner covers the productization mindset in depth, including how to scope what's included and what isn't.
The monthly retainer needs clear boundaries or it becomes unlimited free support. Define this in writing before you start.
Included in monthly retainer:
Not included (quoted separately):
This isn't about being rigid — it's about being sustainable. If you don't define scope, the retainer quietly expands into a part-time job. For detailed guidance on managing this dynamic, Client Management: Scope, Communication, and Revisions for Data Freelancers covers exactly how to frame and enforce these conversations professionally.
Pricing this service correctly is a function of three inputs: the value delivered, the market rate for similar work, and your positioning.
For a workflow that saves 4–5 hours per month, the value case is easy to make. Even a conservative internal cost of £50/hour for the sales coordinator's time means the automation is worth £200–£250/month in direct productivity savings. That's before you account for consistency, reduced errors, and the time the report now frees up for analysis rather than data wrangling.
A sensible pricing structure for this type of service:
| Component | Price Range | Notes |
|---|---|---|
| Build fee (simple, 1-2 sources) | £600 – £900 | Under 8 hours of work |
| Build fee (medium, 3-4 sources) | £1,000 – £1,800 | The wholesale distributor example |
| Build fee (complex, 5+ sources or complex logic) | £2,000 – £4,000+ | Multiple systems, complex business rules |
| Monthly retainer | 15–25% of build fee | Minimum £150/month |
Don't undercharge the build fee to get the retainer. The build fee validates that your work has real value. If you give it away, you train the client to see you as cheap, which makes the retainer harder to justify and harder to increase later.
For a deeper treatment of how to structure pricing across your freelance practice, Pricing Data Projects: Hourly vs Fixed vs Value-Based - A Complete Guide for Data Professionals is the companion read to this lesson.
How you hand over the automation determines whether the client sees it as a one-time purchase or an ongoing service. Most freelancers send over a file and a Loom recording. That's fine, but it positions you as done.
Instead, do a live handover session (60 minutes, video call). Walk the client through:
At the end of this call, ask: "Would you like me to handle the first few months' runs with you to make sure everything's working smoothly?" Almost every client says yes. That "yes" is the retainer starting. Document it in a simple email confirmation and invoice for month one.
Tip
Offer to run the first month's report yourself — meaning you do the refresh, QA the output, and deliver the finished file. This removes all anxiety from the client's side and demonstrates the ongoing value of your retainer immediately. You can transition them to running it themselves at month three or four once trust is established.
Build a minimal version of the wholesale distributor automation using sample data. Here's what to build:
Setup: Create three sample input files (you can generate them with simple Excel formulas or use any sales dataset):
orders_[currentmonth].csv — 500 rows, columns: OrderID, CustomerCode, ProductCode, Quantity, UnitPrice, OrderDate, Statuscustomer_accounts.xlsx — 50 rows: AccountCode, AccountName, Region, AccountTierpricing_current.csv — 30 rows: SKU, CostPrice, CategoryBuild targets:
/inputs/, /outputs/, /archive/)BasePath and OutputPath named rangesfnGetBasePath function query in Power QuerySalesData merge query with margin calculationsRunMonthlySalesReport macro that refreshes queries and saves the output fileStretch goal: Add a step that archives the input files to the /archive/[MMYYYY]/ subfolder after the report is generated, so the /inputs/ folder is always clean for next month.
Symptom: The automation works on your machine, fails on the client's. Fix: Always use parameterized paths via a Settings sheet named range, or use relative paths. Test by running the workbook from a different user account or folder location before handover.
Symptom: When a column name changes slightly (e.g., the ERP updates CustomerCode to Customer_Code), the entire pipeline crashes with an obscure error.
Fix: Add a validation step at the top of each source query that checks expected column names exist. Surface friendly error messages rather than technical Power Query errors:
let
Source = Csv.Document(...),
Headers = Table.PromoteHeaders(Source),
// Validate required columns exist
RequiredColumns = {"OrderID", "CustomerCode", "ProductCode", "Quantity", "UnitPrice", "OrderDate", "Status"},
MissingColumns = List.Select(RequiredColumns, each not List.Contains(Table.ColumnNames(Headers), _)),
ValidationCheck = if List.Count(MissingColumns) > 0
then error "Missing columns: " & Text.Combine(MissingColumns, ", ")
else Headers
in
ValidationCheck
Symptom: Report generates but shows last month's data in some tables.
Fix: Set BackgroundQuery = False on all connections before refreshing, or use ActiveWorkbook.RefreshAll followed by Application.CalculateFullRebuild and a DoEvents loop check.
Symptom: Client accidentally breaks queries while exploring, then blames you.
Fix: The master .xlsm with Power Query and macros never leaves your control if possible. Alternatively, password-protect the VBA project and use xlSheetVeryHidden on all sheets except the Control Panel. The output file they receive is always a clean .xlsx copy.
Symptom: Client emails you every week with "just a quick question" and gradually the retainer becomes unlimited consulting. Fix: Define scope in writing at handover. When an out-of-scope request comes in, acknowledge it warmly and quote it separately: "Happy to add that — it's outside the monthly scope but I can get it done for £X. Want me to send a quote?" Client management practices for data freelancers covers how to handle these conversations without damaging the relationship.
Symptom: Twelve months in, you're doing twice the work for the same price. Fix: Build an annual review into your retainer agreement. After 12 months of demonstrated value, a 10–15% rate increase is entirely reasonable. The client has sunk cost in the relationship and switching cost is high — this works in your favour.
You've now got the full playbook: how to find automation opportunities in client workflows, how to build a Power Query pipeline with parameterized paths and proper error handling, how to write the VBA layer that turns a refreshed data model into a finished deliverable, and — critically — how to package, price, and maintain the whole thing as a recurring monthly service.
The business logic here is clean. You build an asset once. You charge a one-time build fee that covers your creation time. You then earn a monthly retainer for maintaining and supporting that asset — work that takes a fraction of your original build time. Scale this across three or four clients and you have a meaningful, predictable monthly income that doesn't require you to constantly find new work.
Where to go from here:
The operations manager who spends every Monday morning wrestling with spreadsheets is out there right now, in hundreds of companies, waiting for someone to show up and solve her problem. Build the solution, package it well, and she'll happily pay you every month to keep it running.