🛠️ Build an Expense Sheet in 10 Minutes with AI

For anyone who dreads Excel the most: no functions to memorize, no typing speed to train. Just follow along, and in 10 minutes you'll have an expense sheet that's ready to fill in and calculates the amounts for you.

1. The Pain: Month-End Expense Reports — Who Hasn't Cracked Under the Pressure?

Every month-end, filling out the expense report turns into a small disaster:

• Manual entry is slow. Typing serial numbers, dates, and amounts row by row — your hands ache after just 20 rows.
• The total is always wrong. The dreaded "Total" at the bottom — you add things up, somehow slip in an extra zero or skip an item, and Finance sends the whole thing back for a redo.
• Ugly formatting. Hand-drawn tables end up with messy borders and crooked alignment; printed out, they look like scratch paper — not something you can hand in with confidence.

You're not incapable — you just don't want to burn your time on repetitive grunt work. This article uses AI to kill all three pain points in one pass: no racking your brain for headers, no totaling by hand, no fiddling with styles.

2. What You'll Build: An Expense Sheet That "Does the Math for You"

When you're done, you'll have a sheet with this column layout:

Column	Content	Notes
A	Serial No.	1, 2, 3… typed by hand
B	Date	When the money was spent
C	Expense Item	High-speed rail ticket, accommodation, meals, etc.
D	Unit Price	How much one item costs
E	Quantity	How many items
F	Amount	= Unit Price × Quantity (calculated automatically)
Last row	Total	Sum of all amounts (from the Excel Table's Total Row, calculated automatically)

A few terms to know up front — they'll come up again and again:

• Formula: a small instruction that makes the spreadsheet do math for you. For example, =D2*E2 means "multiply this row's unit price by its quantity."
• Excel Table: the feature in Excel / WPS that turns a block of data into a smart table (shortcut Ctrl+T). Its best trick: add new rows below, and the total keeps up automatically — nothing gets missed.
• Total Row: a summary row built into the Excel Table. Tick it once and it sums the entire column automatically — and it is the only source of totals in this guide.

3. Hands-On: Build the Auto-Calculating Expense Sheet in 5 Steps

The workflow below works for both WPS Spreadsheets and Excel 365 (Copilot). Their entry points and prerequisites differ, but the approach — "AI drafts, you build the sheet yourself" — is exactly the same.

Step 1: Create a Blank Workbook

Steps

1. Open WPS Spreadsheets (or Excel), click New → Blank Workbook.
2. Rename the default Sheet1 to "Expense Sheet" (just double-click the tab at the bottom).

Key point

• Don't write any headers yet. Let the AI propose the column names first, then enter them yourself — much easier than staring at an empty grid trying to brainstorm.

Step 2: Use AI to Draft the Headers and Sample Rows (Then Enter Them Manually)

This step lets the AI think through "which columns do we need, and what do the samples look like" — but you perform the actual entering. That's the most reliable route, immune to differences between AI versions.

WPS Spreadsheets entry point: Inside WPS Spreadsheets, click "WPS AI" in the top-right corner to open the chat panel, then type straight into the input box.

Heads-up: what WPS AI can do depends on your installed version/membership; different accounts may see different features.

Excel 365 entry point: Requires an active Microsoft 365 Copilot license — without one, the Copilot button simply won't appear in Excel. Its location varies by version: usually to the right of the Home tab, or as a dynamic Copilot button in the bottom-right corner of the window. When prompted, confirm inside the Copilot panel (button wording varies slightly between versions) before letting it touch your table.

Prompt (copy and paste):

List the header columns for an employee expense sheet: Serial No., Date, Expense Item, Unit Price, Quantity, Amount, plus 2 rows of sample data.

Steps

1. Paste the prompt above into the chat panel and press Enter. The AI returns six column names and two sample rows.
2. Note that the AI may return text only, without actually filling anything into cells. Plan to enter the headers (Serial No./Date/Expense Item/Unit Price/Quantity/Amount) and the two sample rows into the grid manually; even if the AI does write them in, you may still need to copy or adjust the result by hand.
   • Why: this capability depends on your specific software version and subscription — not every account gets one-click table filling, which is why this guide sticks to the reliable "AI drafts + manual entry" approach.
3. At this step, ask the AI for "headers + sample data" only — do NOT ask it to add a total row. Totals will be handled automatically by the Excel Table later, avoiding two conflicting ways of counting.

Key points

• Go by the columns as they actually landed on your screen; the formula in the next step follows wherever the columns ended up.
• Set expectations accordingly: don't expect the AI to produce a perfect finished table in one click. It's more like a colleague who sketches a draft for you — entering the data and finishing the math are still your job.

Step 3: Enter the Amount Formula (=D2*E2) Only, Then Fill Down

Sometimes the AI hands you a draft with no formulas, or you'd rather verify things yourself. Here we write only the "per-row amount"; the total is left to the Excel Table in the next step.

First confirm the column mapping (using this guide's default layout):

• Column D = Unit Price
• Column E = Quantity
• Column F = Amount

Amount formula (enter it in cell F2):

=D2*E2

Meaning: multiply row 2's unit price (D2) by its quantity (E2), and the result is that row's amount.

Steps

1. Click cell F2 (row 2 of the Amount column), type =D2*E2, and press Enter.
2. Grab the small square at the bottom-right corner of F2 (the fill handle) and drag down to about row 10 — the formula auto-adjusts to =D3*E3, =D4*E4… one row, one calculation.

Key points

• In a formula, letters are column numbers and numbers are row numbers; mismatch them and the math goes wrong (see the Pitfall Guide).
• While you're here, format the Amount column as currency: select column F → right-click → Format Cells → Currency. Amounts then display as ¥553.00, which looks properly official.
• This step computes each row's amount only — no total yet. The total belongs exclusively to the Excel Table's Total Row in the next step.

Step 4: Convert to an Excel Table and Check the Total Row

Formulas alone aren't pretty, let alone smart. This step makes the table beautify itself and guarantees the total never misses a row.

Steps

1. Select your data range (including the header row, all the way down to the last row of data).
2. Press Ctrl+T (or Home → Format as Table and pick a style), then click OK in the confirmation dialog — the plain range becomes an Excel Table.
3. Once converted, a table-specific tab appears on the ribbon — Table Design in Excel 365 (Table Tools in WPS). In that tab, tick Total Row.
   • A new row appears automatically at the bottom of your data, and the Amount column shows =SUBTOTAL(109,[Amount]), meaning "sum the Amount column — and automatically include any rows added later."
4. In the same tab, open Table Styles and pick a clean-looking one — borders and colors sorted in one go.

Key points

• There is exactly one source for the total: the Excel Table's Total Row. Clean and never duplicated — no hand-written =SUM anywhere, and no AI-generated total row.
• Insert new expense rows in the middle, and the total recalculates automatically — nothing slips through.

Step 5: Clear the Samples, Then Test with Two Rows

Done building? Prove the "auto-math" actually works with fake data before trusting it.

Steps

1. First clear the sample data entered back in Step 2 (keep the header row, the Amount formula column, and the Excel Table structure), leaving a clean, empty table.
2. Into the empty table, enter these two test rows:

Serial No.	Date	Expense Item	Unit Price	Quantity	Amount
1	2024-05-08	Travel — High-Speed Rail Ticket	553	1	553
2	2024-05-09	Travel — Accommodation	320	2	640

1. The Amount column calculates 553 and 640 on its own; check the Total Row: it should read 1193.
2. Now casually add another row (Serial No. 3, unit price 100, quantity 1) — the total should instantly become 1293. Auto-update confirmed.

Key points

• Always clear the samples before testing. Otherwise the Step 2 sample rows get summed along with everything else, and the numbers won't match — don't scare yourself.
• Once the test passes, clear the test data too, leaving a clean empty table for colleagues (or future you) to fill with real expenses.

4. How It Works: Why the Money Adds Itself Up

In one sentence: formulas handle "per-row accounting," and the Excel Table provides the "safety net." =D2*E2 tells the spreadsheet "every row's amount equals unit price times quantity," so all you ever enter are prices and quantities — the amounts appear on their own. Meanwhile, the Table's Total Row uses a smart reference over the entire column, recalculating in real time no matter how many rows you later add or delete. Crucially, the total comes from exactly one place — the Total Row. There's no hand-written =SUM(F2:F10) and no AI-generated total row, so double-counting simply cannot happen. You've swapped error-prone manual labor for rules a machine executes consistently — faster, and immune to slips.

5. Pitfall Guide (Where Beginners Fall Most Often)

1. Formula references the wrong column or row. Writing =C2*E2 instead of =D2*E2 (when unit price actually lives in column D) computes "Expense Item × Quantity," and everything falls apart. Before writing any formula, confirm which columns your Unit Price and Quantity really occupy — follow your own screen, not the tutorial's column letters.

2. AI-generated fields don't match your company's actual template. The AI defaults to generic columns, but your company may need "Department," "Employee ID," or "Invoice No." Don't settle — just ask the AI to revise: "Add a Department column and an Employee ID column after Expense Item," or insert the columns yourself (right-click → Insert) and type the headers in.

3. Amount column not formatted as Currency. To Finance, 553 and ¥553.00 read very differently. Select the Amount column and apply the Currency format — clearer and more professional, and it keeps the figures from being mistaken for plain numbers elsewhere.

4. Don't expect AI to build the whole table in one shot. Both the WPS AI chat panel and Excel Copilot require subscriptions, and their reliability at "writing directly into cells" is limited — behavior varies across versions and accounts. That's why this guide uses "AI drafts + you enter manually": let the AI produce column names and sample data, then finish the entering and the math yourself. Most stable.

5. WPS AI and Excel Copilot have different entry points and prerequisites. WPS AI opens its chat panel via the "WPS AI" button in the top-right corner, with features depending on version/membership; Excel's Copilot button appears only with an active Microsoft 365 Copilot license, usually to the right of the Home tab or as a dynamic button in the window's bottom-right corner. Prompts are interchangeable between the two — but none of that matters if you can't find the entrance. Just follow Step 2 above and pick your path.

6. Where to Go Next: Making This Sheet Even Smarter

• B2: Clean Messy Data in One Click. Once you're comfortable letting AI handle spreadsheets, the next article shows how to take the messy expense sheets colleagues send over — merged cells, stray blank rows, inconsistent date formats — and tidy them up in one pass, ready to pour into today's template.
• A3: Prompt Engineering for Beginners. Want the AI to give you "exactly what you asked for" every single time? A3 explains how to phrase a one-line request precisely — add the three ingredients of "field list," "sample data," and "output format," and the AI stops improvising and drifting off-spec.

---

This is B1, the Level-1 spreadsheets article of the "AI-Powered Office: From Beginner to Expert" series. Master this one expense sheet, and you've already cleared the first hurdle — being afraid of Excel.