In business organizations or any kind of office, we often prepare various kinds of expense reports. In this article, we will show you how to create a sample expense report in Excel in quick and easy steps.
Download Free Template
You can download the following free template and use it for your purpose.
What Is an Expense Report?
An Expense Report is the documentation of all expenses of an organization. The common elements of an expense report are given below:
- Date of Expense
- Expense Type (Hotel, Transport, Meal, miscellaneous, etc.)
- Amount of Expense
- Subtotal of every expense type
- Amount of Due and Advance Payment
- Purpose of Expense
- Responsible Department
But this format will not be the same for all organizations. Each organization will add the elements according to their types and needs.
Advantages of Using Expense Report
We get the following advantages by using an expense report.
- Tracks expenses and makes you efficient in cost control
- Provides information for making budget
- Makes it easier to pay taxes and tax deduction
Steps to Create an Expense Report in Excel
In this section, we will discuss the whole process to create an expense report in Excel step-by-step.
📌 Step 1: Turn Off Gridlines
First, open an Excel file and turn off Gridlines. To remove Gridlines follow the steps.
- Go to the View tab.
- Unmark the Gridlines checkbox from the Show group.
📌 Step 2: Add Basic Information
Now, we will add the basic information rows to the worksheet.
- First, we add a Title for the report, e.g. Expense Report.
- Then, add Purpose, Employee Name, Employee ID, and Time Period. Look ta the following image to get a more clear idea.
📌 Step 3: Add Columns for Date, Description, and Expenses
In this step, we will add the Data Columns according to expense types.
For example, hotel expenses, transportation costs, phone bills, other expenses, etc.
📌 Step 4: Turn Data into a Table
Now, turn your data into a table following the steps below.
- Select Cells B9:I19 (that is your whole dataset).
- Now, go to the Insert tab.
- Choose Table from the Tables group.
The Create Table window will appear.
- The Table range will be shown here.
- Mark the ‘My table has headers’ checkbox.
- Finally press OK.
- Look at the worksheet now.
📌 Step 5: Introduce Subtotal Row and Turn Off Filter Button
In this step, we will introduce a new row for calculating subtotal. Besides, we will turn off the filter button as we don’t need it now. For that-
- Go to the Table Design tab.
- Unmark the Filter Button and mark the Total Row option.
- Look at the following picture. A Sub Total row is added to the last row of the table.
- How to Generate PDF Reports from Excel Data (4 Easy Methods)
- Prepare MIS Report in Excel (2 Suitable Examples)
- How to Make MIS Report in Excel for Accounts (with Quick Steps)
📌 Step 6: Convert Cells to Suitable Data Format (Date, Accounting, etc. Format)
In this step, we will change the cell data format to corresponding format. For example, expense data to Accounting format, and expense date to Date format. Other cells will remain in General format. To do this-
- First, select the date formatted cells from Time Period and Date column.
- Then press Ctrl+1.
- The Format Cells window will appear.
- Choose the Date segment from the Number tab.
- Select the desired date format from the Type box. Finally, press OK.
- Similarly, select all Cells of the Hotel, Transport, Meal, Phone, Others, and Total column and choose Accounting from the Number tab.
📌 Step 7: Apply SUM Function in Total Column
We will use a formula to get the total cost of each date on the Total column. We will sum the values from the Hotel to Others column.
- Now, we will put a formula on the first cell of the Total column. The formula is:
In fact, you don’t have to manually type the formual. If you do as the GIF image below suggests, it will automatically generate the totals in each row. That’s a specilaity of using Excel tables in such cases.
- Press the Enter button and the formula will spread to the rest of the Cells of that column.
📌 Step 8: Input Expense and Other Data and Get Expense of Each Day
You are almost done. Now is time to input your data.
- Input the expense and other data into the Data columns.
- After inputting data we can see the row-wise totals in the Total column.
📌 Step 9: Get Subtotal for Each Type of Expense
For this, execute the following tasks.
- Go to the Subtotal row of the Hotel column.
- Press on the down arrow and will see a list of operations.
- Choose Sum operation.
- Now, drag the Fill Handle icon towards the right side.
All subtotals are shown on that row.
📌 Step 10: Add Two More Rows for Final Calculation
In this section, we will add rows for the final bill calculation. Before any program, the employees can take some advance money. We will settle this here.
- Add Advances and Reimbursement rows below the subtotal row.
- Put the advance amount on Cell I21.
- And put the following formula on Cell I22.
- Finally, press Enter to get the result.
📌 Final Step: Keep a Space for Authorization
We can add a space for Authorization too, which means this expense report will be accepted after the authorization of a responsible person.
Also need to remember that you have to attach all expense documents while submitting this report to the authority.
In this article, we showed the step by step process of how to make an Expense report in Excel. This is a sample template. According to company or departmental needs, you can modify the report format. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.
- How to Generate Report in PDF Format Using Excel VBA (3 Quick Tricks)
- Make Daily Activity Report in Excel (5 Easy Examples)
- How to Make Report Card in Excel (Download Free Template)
- Make MIS Report in Excel for Sales (with Easy Steps)
- How to Make Inventory Aging Report in Excel (Step by Step Guidelines)