Create Your Own Receipt Book in Excel: A Step-by-Step Guide

Ever found yourself in need of a receipt book for tracking expenses, but didn't want to shell out for a physical one? Microsoft Excel offers a simple and cost-effective solution. In this guide, we'll walk you through the process of creating a receipt book in Excel, complete with customizable templates and automated calculations.

Import Receipts to Excel (+ other documents too!) - with Video Tutorial
Import Receipts to Excel (+ other documents too!) - with Video Tutorial

Before we dive in, ensure you have Microsoft Excel installed on your computer. We'll be using Excel 2016 or later for this guide, but the process is similar in earlier versions. Let's get started!

Creating Professional Invoice in Excel | Receipt Making Tutorial | Free Template
Creating Professional Invoice in Excel | Receipt Making Tutorial | Free Template

Setting Up Your Receipt Book

First, open a new workbook in Excel. This will be the foundation of your receipt book. Name it something descriptive, like "Receipt Book" or "Expense Tracker".

How to Create DAY BOOK in Excel | Receipt & Payment Account with Day Book| Learning Center
How to Create DAY BOOK in Excel | Receipt & Payment Account with Day Book| Learning Center

Next, let's set up the basic structure. In the first row, enter the following headers: A1: "Date", B1: "Description", C1: "Amount", and D1: "Category". These will serve as the basis for recording your receipts.

Formatting Your Receipt Book

Four Free Receipt Forms for Cash Payments
Four Free Receipt Forms for Cash Payments

To make your receipt book visually appealing and easy to read, apply some basic formatting. Select the headers (A1:D1) and change the font to something bold and large, like Arial or Calibri, size 14. Then, fill the cells with a light color, such as light grey or light blue, to make them stand out.

Now, freeze the top row for easy navigation. Click anywhere in the data range (A2:D2), then go to the "View" tab, click "Freeze Panes", and select "Freeze Top Row". Your headers will now remain visible as you scroll down.

Customizing Your Receipt Book

an excel spreadsheet is shown with the text field highlighted in red and green
an excel spreadsheet is shown with the text field highlighted in red and green

You can customize your receipt book further by adding more columns for details like payment method, vendor, or notes. To do this, simply enter the new headers in the appropriate columns and widen the columns as needed.

For example, to add a "Payment Method" column, enter "Payment Method" in E1, and widen column E. You can also add a dropdown list of payment methods (Cash, Card, Check, etc.) using data validation to keep your entries consistent.

Automating Calculations

How to Design a Professional Receipt Book in MS Word: Step-by-Step Guide
How to Design a Professional Receipt Book in MS Word: Step-by-Step Guide

To save time and reduce errors, let's automate some calculations. We'll add a "Total" column that automatically sums up the amounts for each category.

In cell E2, enter the formula "=SUMIF(D$2:D2, E2, C$2:C2)". This formula uses the SUMIF function to add up all the amounts (C2:C2) where the category (D$2:D2) matches the current category in E2.

Free Sales Receipt Template for Small Business
Free Sales Receipt Template for Small Business
√ Free Printable Cash Receipt Template
√ Free Printable Cash Receipt Template
Cash Receipt Template - Excel Word Template
Cash Receipt Template - Excel Word Template
Blank Receipt Templates | 10+ Free Word, Excel & PDF Formats, Samples, Examples, and Forms
Blank Receipt Templates | 10+ Free Word, Excel & PDF Formats, Samples, Examples, and Forms
Professional Customer Bill Receipt Template in Excel | Easy & Printable Format
Professional Customer Bill Receipt Template in Excel | Easy & Printable Format
an invoice form with the words receipt written on it, and a black and white background
an invoice form with the words receipt written on it, and a black and white background
A Better Excel Checkbook Register Spreadsheet - Part 1
A Better Excel Checkbook Register Spreadsheet - Part 1
Free Microsoft Word Receipt Templates | Smartsheet
Free Microsoft Word Receipt Templates | Smartsheet
Cash Receipt Template | 11+ Free Word, Excel & PDF Formats, Samples, Examples, and Forms
Cash Receipt Template | 11+ Free Word, Excel & PDF Formats, Samples, Examples, and Forms
Blank Receipt Form Template - 10 Free PDF Printables | Printablee
Blank Receipt Form Template - 10 Free PDF Printables | Printablee
Free Receipt Tracker Template
Free Receipt Tracker Template
How to Make a Receipt Using ExpressExpense Receipt Maker - ExpressExpense - How to Make Receipts
How to Make a Receipt Using ExpressExpense Receipt Maker - ExpressExpense - How to Make Receipts
Payment Receipt Excel Template - Model 3
Payment Receipt Excel Template - Model 3
a receipt form for payment is shown in this image
a receipt form for payment is shown in this image
Invoice Template Simple - Download Printable Receipt Forms Now
Invoice Template Simple - Download Printable Receipt Forms Now
How To Make A Receipt Template, Printable Receipt Form, Key Receipt Template, Free Printable Receipt Template, Free Receipt Template Download, Receipts Template Word, Office Receipt Sample, Printable Receipt Examples, Free Receipt Examples
How To Make A Receipt Template, Printable Receipt Form, Key Receipt Template, Free Printable Receipt Template, Free Receipt Template Download, Receipts Template Word, Office Receipt Sample, Printable Receipt Examples, Free Receipt Examples
40 Free Bookkeeping Templates (Excel) - TemplateArchive
40 Free Bookkeeping Templates (Excel) - TemplateArchive
Editable Receipt Template MS Excel Google Sheets | Small Business Receipt | Printable Simple Receipt
Editable Receipt Template MS Excel Google Sheets | Small Business Receipt | Printable Simple Receipt
16+ Editable Excel Receipt Templates - Word Excel Fomats
16+ Editable Excel Receipt Templates - Word Excel Fomats
receipt form with two receipts on it
receipt form with two receipts on it

Applying Conditional Formatting

To highlight important information, apply conditional formatting to the "Amount" column. Select column C, then go to the "Home" tab, click "Conditional Formatting", and select "Highlight Cells Rules". Choose "Greater Than" and set the value to 0. This will fill positive amounts with one color and negative amounts with another, making it easy to see at a glance.

You can also apply conditional formatting to the "Total" column to highlight when the total for a category exceeds a certain amount. Select column E, go to "Conditional Formatting", and select "Highlight Cells Rules". Choose "Greater Than" and set the value to your desired threshold.

Sorting and Filtering Your Receipts

To make the most of your receipt book, learn to sort and filter your receipts. Select any cell within your data range, then go to the "Data" tab. Click "Sort & Filter" and choose how you want to sort your receipts. You can sort by date, amount, category, or any other column.

To filter your receipts, click the filter icon in the header of the column you want to filter. This will display a dropdown list of unique values in that column. Deselect the values you don't want to see, and only the selected receipts will remain visible.

And there you have it! Your custom receipt book in Excel is now ready to use. With a bit of practice, you'll be recording and tracking your receipts like a pro. Happy expense tracking!