Looking for ways to make an Office Expense Sheet in Excel? Then you are in the right place. In this article, we are going to show you 8 easy steps to make an office Expense Sheet in Excel.
Office Expense Sheet
An Office Expense Sheet is the documentation of all expenses of an office. The common elements of an Office Expense report are given below:
- Date of Expense.
- Expense Category (Salary, Bill, Equipment, miscellaneous, etc.).
- Amount of Expense.
- Purposes of Expense.
- Responsible Department.
However, this format will not be the same for all organizations. Each organization will add the elements according to their types and needs.
Moreover, we get the following advantages of using an expense report:
- Tracks expenses and makes you efficient in cost control.
- Provides information for making a budget.
- Makes it easier to pay taxes and get tax deductions.
Now, the Office Expense Sheet means tracking the office expenses inside an Excel spreadsheet. Moreover, this will be the final output of our Office Expense Sheet.
How to Make Office Expense Sheet in Excel: 8 Easy Steps
In this section, we will describe the step-by-step process of making an Office Expense Sheet in Excel.
Step 1: Turn Off Gridlines
For the first step, we will turn off Gridlines in our Sheet. This will make the Office Expense Sheet better.
- To begin with, from the View tab, deselect Gridlines.
Step 2: Add Basic Information
Now, we will add basic company information at the top of the expense sheet.
- Firstly, include this information about the company:
- Company Logo.
- Company Name.
- Company Department.
- Company Phone Number.
- Company Email Address.
- Then, add the duration of the expense sheet:
- From: 6-June-2022.
- To: 28-July-2022.
Step 3: Add Column Headers
Then, we will add the column headers to the Office Expense Sheet.
- Firstly, insert these columns:
- Date
- Description
- Payment Method
- Category
- Amount
Step 4: Insert Dropdown List for Payment Method
In this step, we will insert a dropdown list for the “Payment Method” column.
- To do this, select the cell range D12:D17.
- Then, from the Data tab, select Data Validation.
- Next, the Data Validation dialog box will appear.
- Afterward, select List.
- Then, type “Cash, Credit” inside the Source box.
- Lastly, press OK.
- So, we can use the dropdown list to select the payment method in the Office Expense Sheet.
Step 5: Data Entry and Proper Formatting
For the fifth step, we will type all the other data into the Office Expense Sheet, and then we will format the data.
- Firstly, type these data in the “Date”, “Description”, “Category”, and “Amount” columns.
- Now, we will change the format of the Date values.
- So, select the cell range B12:B17 and press CTRL+1.
- Then, the Format Cells dialog box will pop up.
- Next, select the desired Type from the box and press OK.
- Similarly, we will change the format of the values from the Amount column.
- So, select the cell range F12:F17 and press CTRL+1.
- Then, again, the Format Cells dialog boxwill appear.
- Afterward, select Currency from the Category.
- Then, set Decimal places to 0.
- Lastly, press OK.
- Therefore, our Office Expense Sheet will look like this.
Step 6: Apply SUM Function to Get Total
Now, we will use the SUM function to calculate the total amount of the expense.
- First, type the following formula in cell F18.
=SUM(F12:F17)
- Then, press ENTER.
- Thus, we will get the total amount.
Step 7: Automating Amount in Words Using VBA
We can type the total amount in words. However, we can easily do it using Excel VBA. In this step, we will use that to create a User Defined Function (UDF) to return numbers to words.
- To begin with, press ALT+F11 to bring up the VBA window.
- Alternatively, we can do it by selecting Visual Basic from the Developer tab.
- Then, from Insert >>> select Module. We’ll type our code here.
- Then, type the following code.
Option Explicit
Function Number_To_Words(ByVal xNumber)
Dim xDollars, xCents, xT
Dim xPlace, xCount
ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "
xNumber = Trim(Str(xNumber))
xPlace = InStr(xNumber, ".")
If xPlace > 0 Then
xCents = xTens(Left(Mid(xNumber, xPlace + 1) & "00", 2))
xNumber = Trim(Left(xNumber, xPlace - 1))
End If
xCount = 1
Do While xNumber <> ""
xT = xHundreds(Right(xNumber, 3))
If xT <> "" Then xDollars = xT & Place(xCount) & xDollars
If Len(xNumber) > 3 Then
xNumber = Left(xNumber, Len(xNumber) - 3)
Else
xNumber = ""
End If
xCount = xCount + 1
Loop
Select Case xDollars
Case ""
xDollars = "No Dollars"
Case "One"
xDollars = "One Dollar"
Case Else
xDollars = xDollars & " Dollars"
End Select
Select Case xCents
Case ""
xCents = ""
Case "One"
xCents = " and One Cent"
Case Else
xCents = " and " & xCents & " Cents"
End Select
Number_To_Words = xDollars & xCents
End Function
Function xHundreds(ByVal xNumber)
Dim xResult As String
If Val(xNumber) = 0 Then Exit Function
xNumber = Right("000" & xNumber, 3)
If Mid(xNumber, 1, 1) <> "0" Then
xResult = xDigit(Mid(xNumber, 1, 1)) & " Hundred "
End If
If Mid(xNumber, 2, 1) <> "0" Then
xResult = xResult & xTens(Mid(xNumber, 2))
Else
xResult = xResult & xDigit(Mid(xNumber, 3))
End If
xHundreds = xResult
End Function
Function xTens(tt)
Dim xResult As String
xResult = ""
If Val(Left(tt, 1)) = 1 Then
Select Case Val(tt)
Case 10: xResult = "Ten"
Case 11: xResult = "Eleven"
Case 12: xResult = "Twelve"
Case 13: xResult = "Thirteen"
Case 14: xResult = "Fourteen"
Case 15: xResult = "Fifteen"
Case 16: xResult = "Sixteen"
Case 17: xResult = "Seventeen"
Case 18: xResult = "Eighteen"
Case 19: xResult = "Nineteen"
Case Else
End Select
Else
Select Case Val(Left(tt, 1))
Case 2: xResult = "Twenty "
Case 3: xResult = "Thirty "
Case 4: xResult = "Forty "
Case 5: xResult = "Fifty "
Case 6: xResult = "Sixty "
Case 7: xResult = "Seventy "
Case 8: xResult = "Eighty "
Case 9: xResult = "Ninety "
Case Else
End Select
xResult = xResult & xDigit(Right(tt, 1))
End If
xTens = xResult
End Function
Function xDigit(dd)
Select Case Val(dd)
Case 1: xDigit = "One"
Case 2: xDigit = "Two"
Case 3: xDigit = "Three"
Case 4: xDigit = "Four"
Case 5: xDigit = "Five"
Case 6: xDigit = "Six"
Case 7: xDigit = "Seven"
Case 8: xDigit = "Eight"
Case 9: xDigit = "Nine"
Case Else: xDigit = ""
End Select
End Function
VBA Code Breakdown
- First, we are creating a custom function named Number_To_Words.
- Next, we define the variable types.
- Then, we use the VBA Case statement to apply the word converter to our numbers.
- Afterward, we create 3 more functions – xHundreds, xTens, and xDigit to get the hundred, tens, and single-digit values respectively.
- Thus, this code works to convert numbers to words.
- Then, Save and close the Module.
- Afterward, type the following formula in cell B20.
=Number_To_Words(F18)
- Then, press ENTER.
- So, this formula converts the number to words as per the VBA code breakdown.
Step 8: Insert Space for Comments and Signature
In the last step, we will add blank spaces to insert comments and the signature of the concerned authority.
- Finally, our Office Expense Sheet is complete. This is what the whole sheet looks like.
Download Practice Workbook
Conclusion
We have shown you in 8 easy steps how to make an Office Expense Sheet in Excel. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Thanks for reading, keep excelling!
Related Articles
<< Go Back To How to Make Expense Sheet in Excel | Excel For Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Hello
I am currently working as Admin Officer.
Using Excel to prepare my reports is crucial.
I’m really enjoying your articles.
Hello, Michelet!
Thanks for your appreciation. Stay in touch with ExcelDemy.
Regards
ExcelDemy