How to create a Business Expenses Spreadsheet - [with PDF links], by Mr.SpreadSheet on YouTube. 13 min 45 s.
- Video length
- 13 min 45 s
- Time to do it
- 25 minutes
- Difficulty
- Easy
- Steps
- 10
What you need
Steps
Open Excel and create a blank workbook. Add two sheets: name one Expenses and the other Lists. Save the file straight away so you do not lose work.
On the Expenses sheet, type these headers across row 1: Date, Description, Category, Amount, Receipt. Keep the names short and leave no blank columns.
Select the header row and the empty rows below it, then go to Insert, Table and tick My table has headers. This lets the table grow as you add rows.
Go to the Lists sheet and type your category names in one column, one per row, such as Travel, Software, Office and Meals. Select those cells, click the Name Box, type Categories and press Enter.
Back on the Expenses sheet, select the Category column cells and open Data, Data Validation. Choose List as the allow option and type =Categories as the source, then click OK.
Format the Amount column as currency through Home, Number Format. Type amounts as plain numbers only, with no currency symbols or text.
Build a summary block beside your table. List each category and use a SUMIF formula like =SUMIF(C:C,F2,D:D), where F2 holds the category name. Copy it down the list.
Add a total cell with =SUM(D:D) and a tax cell that multiplies the total by your own rate. Label both clearly so the figures are easy to read.
To link a receipt, select the Receipt cell, go to Insert, Link, pick the PDF file on your computer and add short display text. Repeat for each row.
Test the sheet with two sample rows, check the totals update, then save a clean copy through File, Save As and choose Excel Template.
Tips and common mistakes
Enter one row per receipt so the SUMIF totals stay accurate.
Keep every PDF in the same folder as the workbook, otherwise the links stop working when files move.
Use absolute references like $C$2:$C$500 in SUMIF if you copy formulas across cells.
Delete your sample rows before saving the template so next month starts empty.
Questions
Do the receipt links still work if I email the file?
Only if the recipient also has the PDFs in the same folder structure. For sharing, store receipts in cloud storage and link to those files instead.
Can I use this layout in Google Sheets?
Yes. Tables and named ranges work differently, but data validation drop-downs and SUMIF formulas carry over with small changes.
How many categories should I use?
Around 8 to 12 covers most small businesses. Fewer categories make the summary easier to read at tax time.
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.
