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

Get FREE Advanced Excel Exercises with Solutions!

In business organizations or any kind of office, we often prepare various kinds of expense reports. In this article, we will discuss the whole process to create an expense report in Excel.


Watch Video – Create an Expense Report in Excel



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

Step 1: Turning Off Gridlines in Excel

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: Adding Basic Information to Create an Expense Report

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.

Add Basic Information in the Expense Report


Step 3: Adding 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.

Add Columns for Expense Report


Step 4: Converting Data into an Excel 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.

Turn the Data into a Table of Expense report

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: Introducing Subtotal Row and Turning 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.

Introduce Subtotal Row and Turn off Filter Button

  • Look at the following picture. A Sub Total row is added to the last row of the table.


Step 6: Converting 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: Applying the Excel SUM Function in the 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:
=SUM(Table1[@[Hotel]:[Others]])

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.

SUM Function in Total Column to Calculate Expense Report

  • Press the Enter button and the formula will spread to the rest of the Cells of that column.

Step 8: Inserting Expenses and Other Data and Get Expenses of Each Day

You are almost done. Now is the 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: Generating Subtotal for Each Type of Expense in the Report

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.

Get Subtotal for Each Type of Expense in the Report

  • Now, drag the Fill Handle icon towards the right side.

All subtotals are shown on that row.


Step 10: Adding 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.
=Table1[[#Totals],[Total]]-I21

Add Two More Rows for Final Calculation of Expense Report

  • Finally, press Enter to get the result.

Final Step: Keeping a Space for Authorization in the Expense Report

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.


Download Free Template

You can download the following free template and use it for your purpose.


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.


Related Articles


<< Go Back to Report in ExcelLearn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo