How to use Copilot in Excel for Bookkeeping - Automate Invoice Entry, by BodmasBooks on YouTube. 14 min 55 s.
- Video length
- 14 min 55 s
- Time to do it
- 20 minutes
- Difficulty
- Medium
- Steps
- 10
What you need
Steps
Put your raw invoices on one sheet named Raw_Invoices. Give each column a clear header such as Supplier, Invoice No, Invoice Date, Due Date, Amount and VAT.
Select the data range and press Ctrl+T to make it a table. Copilot works far more reliably on a named table than on loose cells.
Open the Copilot pane from the Home tab. Ask it to create an invoice entry register from Raw_Invoices with structured columns and a VAT column.
Check the generated register against your raw data. Confirm the VAT amounts and totals are correct before you build anything on top of it.
To add a new invoice, type the supplier and details in the next empty row of the register. Ask Copilot to add the invoice to the existing register without retyping the rows above.
Ask Copilot to validate the register for duplicate invoice numbers, VAT errors, missing fields and dates outside the expected range. Fix each issue it flags.
Prompt Copilot to build an age analysis with buckets for Current, 1-30, 31-60, 61-90 and 90+ days, plus a risk column.
Select the Risk column and use Home, Conditional Formatting to colour cells green, yellow, orange and red by risk level.
Ask Copilot to create a dashboard showing total outstanding, overdue percentage and average days to payment. Place it on its own sheet.
Apply conditional formatting to the register so overdue invoices stand out, and to the dashboard KPI cells so weak figures are easy to spot.
Tips and common mistakes
Always spot-check Copilot's VAT and total calculations by hand on a few rows before trusting the register.
Keep the raw invoice sheet untouched so you can rebuild the register if a prompt gives a poor result.
Use clear, consistent column headers, because vague names like Amount1 confuse Copilot and produce wrong formulas.
Save a copy before running validation prompts, since Copilot can overwrite cells in the active sheet.
Questions
Do I need a paid Microsoft 365 Copilot licence?
Yes. Copilot in Excel requires a Microsoft 365 Copilot subscription. The free Excel web version does not include these prompts.
Can I use this for any currency and VAT rate?
Yes. Tell Copilot your currency and VAT rate in the prompt, for example 15 percent VAT, so the calculated columns match your country.
What if Copilot gives a wrong or broken formula?
Undo with Ctrl+Z and rephrase the prompt with the exact column names and the result you want. Smaller, single-task prompts usually work better.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
