How to Create an Expense Report in Excel (With Easy Steps)

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:

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.

Read More: How to Make Production Report in Excel (2 Common Variants)

📌 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.

Read More: How to Make Daily Production Report in Excel (Download Free Template)

📌 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.

Read More: How to Create a Summary Report in Excel (2 Easy Methods)

📌 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.

Read More: How to Make Daily Sales Report in Excel (with Quick Steps)

📌 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.

Read More: Create a Report That Displays Quarterly Sales in Excel (with Easy Steps)

Similar Readings

• 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.

Read More: Create a report that displays the quarterly sales by territory

📌 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.

Read More: How to Make Sales Report in Excel (with Easy Steps)

📌 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.

Read More: How to Automate Excel Reports Using Macros (3 Easy Ways)

📌 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.

Conclusion

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.

Related Articles

• 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)