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.
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.
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.
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.
- 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:
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: 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.
- 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.
- 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.
You can download the following free template and use it for your purpose.
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.
- How to Make Daily Activity Report in Excel
- How to Create an Income and Expense Report in Excel
- How to Make Production Report in Excel
- How to Make Daily Production Report in Excel
- How to Make Monthly Report in Excel
- How to Make a Monthly Expense Report in Excel