How to Create an Expense Report in Excel, by ExcelDemy on YouTube. 9 min 59 s.
- Video length
- 9 min 59 s
- Time to do it
- 20 minutes
- Difficulty
- Easy
- Steps
- 10
What you need
Steps
Open a blank workbook. Go to the View tab and uncheck Gridlines to give the report a clean white background.
Add basic information at the top: a report title, your name or company, the date range, and the currency. Leave a few rows for this header.
Create columns for Date, Description, Expense Type, and Amount. Add a header row and keep each column wide enough to read.
Select the header and data range. Go to Insert, click Table, and tick My table has headers. This makes ranges expand as you add rows.
In Table Design, click Total Row to add a summary row. Then click Filter Button to hide the filter arrows if you want a cleaner look.
Select the Date column and set it to Date format. Select the Amount column and set it to Currency. Use Home, then Number Format.
In the Total Row, click the Amount cell and use the SUM function to add every amount in the column. Press Enter to see the grand total.
Enter each expense on its own row. Fill in the date, a short description, the expense type, and the amount. Add rows as needed.
For each expense type, add a subtotal formula with SUMIF. Point the criteria range at the Expense Type column and the sum range at Amount.
Add a couple of rows below the table for final calculations. Label them clearly, such as Total Expenses, and reference the total cell.
Tips and common mistakes
Keep expense type names identical, because SUMIF matches exact text and small spelling differences create extra categories.
Save a blank copy as an Excel template so you can reuse the same layout each month without rebuilding it.
Check that dates are real Excel dates, not text, or sorting and monthly totals will not work correctly.
Do not type totals by hand. Let the SUM and SUMIF formulas update when you add new rows.
Questions
Do I have to use an Excel table?
No, but a table makes the report easier to update. Formulas and formatting extend automatically when you type in the row below.
How do I add a new expense after the table is made?
Type in the first empty row under the table. Excel expands the table and updates the totals if your formulas refer to the table column.
Can I change the currency symbol?
Yes. Select the amount cells, open Format Cells, choose Currency, and pick the symbol you need.
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.
