How to Create a Cash Memo in Excel

Katherine Jul 11, 2026

In today's fast-paced business environment, keeping track of cash transactions is more important than ever. While traditional methods may seem cumbersome, leveraging the power of Excel can streamline this process significantly. One such way is by creating a cash memo, a simple yet effective tool for recording and managing cash transactions. In this guide, we'll walk you through the steps of how to make a cash memo in Excel, ensuring you stay organized and efficient.

FREE 8+ Cash Memo Templates in Google Docs | MS Word | Apple Pages
FREE 8+ Cash Memo Templates in Google Docs | MS Word | Apple Pages

Before we dive into the specifics, let's briefly understand what a cash memo is. A cash memo is a document that records the details of cash transactions, including the date, amount, and a brief description. It's an essential tool for maintaining accurate financial records, especially for small businesses or individuals dealing with frequent cash transactions.

how to make cash memo in excel
how to make cash memo in excel

Setting Up Your Cash Memo in Excel

Excel, with its user-friendly interface and robust features, is an ideal platform for creating a cash memo. Here, we'll discuss how to set up a basic cash memo template that you can customize according to your needs.

📈 Manage Cash Flow with Excel Like a Pro
📈 Manage Cash Flow with Excel Like a Pro

First, open a new or existing Excel workbook. In the first row, create headers for the following columns: 'Date', 'Transaction Type' (e.g., Income, Expense), 'Description', and 'Amount'. You can also add a 'Category' column for further classification of transactions.

Formatting Your Cash Memo

Ready-To-Use Cash Book Template in Excel - MSOfficeGeek
Ready-To-Use Cash Book Template in Excel - MSOfficeGeek

To make your cash memo visually appealing and easy to read, apply some basic formatting. You can change the font style and size, add borders, and use different colors for different transaction types. For instance, you can use green for income and red for expenses.

To apply formatting, select the range of cells you want to format, then use the 'Home' tab in the ribbon to access various formatting options. You can also use conditional formatting to automatically apply formatting based on certain conditions, such as transaction type.

Adding Data Validation

the money management spreadsheet is shown in green and white
the money management spreadsheet is shown in green and white

Data validation is a useful feature that helps maintain data integrity by restricting the type of data that users can enter into a cell. For instance, you can use data validation to ensure that the 'Transaction Type' column only accepts 'Income' or 'Expense'.

To add data validation, select the cell where you want to apply the rule, then click on 'Data' in the ribbon, and select 'Data Validation'. In the 'Settings' tab, choose 'List' from the 'Allow' dropdown, then enter the list of acceptable values (e.g., 'Income', 'Expense').

Recording Cash Transactions

Free Monthly Budget Excel Spreadsheet
Free Monthly Budget Excel Spreadsheet

Now that your cash memo template is set up, it's time to start recording your cash transactions. Here's how you can do it efficiently:

For each transaction, enter the date, transaction type, a brief description, and the amount in the respective columns. If you've added a 'Category' column, use it to further classify your transactions (e.g., 'Food' under 'Expense', 'Sales' under 'Income').

Company Cash Memo
Company Cash Memo
Download Business payment Cash memo invoice vector template design, Sale invoce template for free
Download Business payment Cash memo invoice vector template design, Sale invoce template for free
Money Tracker for Excel
Money Tracker for Excel
How to Make a Monthly Budget Excel Spreadsheet | Cashflow, Income, Fixed and Variable Expenses
How to Make a Monthly Budget Excel Spreadsheet | Cashflow, Income, Fixed and Variable Expenses
a cash memo with the words,'cash memo'and an image of a receipt
a cash memo with the words,'cash memo'and an image of a receipt
an invoice form is shown on a green background
an invoice form is shown on a green background
daily cash sheet template excel | monthly petty cash format
daily cash sheet template excel | monthly petty cash format
a spreadsheet showing the cost and budget for a business invoicer
a spreadsheet showing the cost and budget for a business invoicer
Printable Cash Credit Memo Bill Design in Ms Word Tutorial
Printable Cash Credit Memo Bill Design in Ms Word Tutorial
Hotel Invoice Template for Easy Cash Management | Download Free Now
Hotel Invoice Template for Easy Cash Management | Download Free Now
Monitor your cash flow | Make money online
Monitor your cash flow | Make money online
Csh memo design
Csh memo design
Download Cash Book Excel Template - ExcelDataPro
Download Cash Book Excel Template - ExcelDataPro
Cash Memo (Meaning, Example) | Sample Format of a Cash Memo
Cash Memo (Meaning, Example) | Sample Format of a Cash Memo
Cash Sheet Templates | 17+ Free Docs, Xlsx & PDF Formats, Samples, Examples, and Forms
Cash Sheet Templates | 17+ Free Docs, Xlsx & PDF Formats, Samples, Examples, and Forms
Cash Flow Worksheet - Monthly - Microsoft Excel Organizer
Cash Flow Worksheet - Monthly - Microsoft Excel Organizer
Download Free Vectors, Images, Photos & Videos | Vecteezy
Download Free Vectors, Images, Photos & Videos | Vecteezy
10 Personal Finance Excel Spreadsheet Templates for Managing Money
10 Personal Finance Excel Spreadsheet Templates for Managing Money
Free printable cash drawer count sheet pdf - Free Download
Free printable cash drawer count sheet pdf - Free Download
Excel 365 Budget | Financial Planner Budget Spreadsheet | Excel Cash Envelope Budget Template | Spending & Cash Tracker Budgeting System
Excel 365 Budget | Financial Planner Budget Spreadsheet | Excel Cash Envelope Budget Template | Spending & Cash Tracker Budgeting System

Sorting and Filtering Transactions

As your cash memo grows, you can use sorting and filtering to organize and analyze your transactions. Sorting arranges your transactions in ascending or descending order based on a specific column, while filtering allows you to display only the transactions that meet certain criteria.

To sort your transactions, select the range of cells you want to sort, then click on 'Home' in the ribbon, and select 'Sort & Filter'. To filter, select the header of the column you want to filter, then click on the 'Filter' icon that appears in the header. This will display a dropdown menu with various filtering options.

Calculating Totals

To get a quick overview of your cash flow, you can add a total row at the bottom of your cash memo. Use the 'SUM' function in Excel to calculate the total income and total expenses. You can also use the 'IF' function to calculate the net cash flow (total income - total expenses).

To use the 'SUM' function, select the cell where you want the total to appear, then type '=SUM(' and select the range of cells you want to sum. To use the 'IF' function, the syntax is '=IF(logical_test, value_if_true, value_if_false)'. For instance, '=IF(B2="Income", C2, 0)' will add the amount to the total income if the transaction type is 'Income', and 0 otherwise.

As your business grows, you might want to consider using more advanced features in Excel, such as pivot tables and charts, to analyze your cash flow in more detail. You might also want to consider using accounting software that integrates with Excel for more robust financial management.

Creating and maintaining a cash memo in Excel is a simple yet powerful way to keep track of your cash transactions. It's a tool that can save you time, help you stay organized, and provide valuable insights into your cash flow. So, why not give it a try today? Start with the basics, and as you get more comfortable, explore the more advanced features that Excel has to offer. Happy tracking!