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.
Download Practice Workbook
Office Expense Sheet
An office expense report 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.
But 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.
8 Easy Steps to Make Office Expense Sheet in Excel
In this section, we will describe the step-by-step process to make 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:
- Payment Method
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.
- How to Keep Track of Small Business Expenses in Excel (2 Easy Ways)
- How to Create Daily Expense Sheet Format in Excel (With Easy Steps)
- How to Make Personal Expense Sheet in Excel (with Easy Steps)
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 box will 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
- First, type the following formula in cell F18.
- 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.
- 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.
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. Moreover, you can visit our site ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!