How to Make Office Expense Sheet in Excel (with Easy Steps)

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.

How to Make Office Expense Sheet in Excel


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.

Sample Data

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.

 Sample Data

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

How to Make Office Expense Sheet in Excel 3

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.

How to Make Office Expense Sheet in Excel 4

  • Next, the Data Validation dialog box will appear.
  • Afterward, select List.
  • Then, type “Cash, Credit” inside the Source box.
  • Lastly, press OK.

How to Make Office Expense Sheet in Excel 5

  • So, we can use the dropdown list to select the payment method in the office expense sheet.

How to Make Office Expense Sheet in Excel 6


Similar Readings


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.

How to Make Office Expense Sheet in Excel 7

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

How to Make Office Expense Sheet in Excel 8

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

How to Make Office Expense Sheet in Excel 9

  • Therefore, our office expense sheet will look like this.

How to Make Office Expense Sheet in Excel 10

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)

How to Make Office Expense Sheet in Excel 11

  • Then, press ENTER.
  • Thus, we will get the total amount.

How to Make Office Expense Sheet in Excel 12

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.

How to Make Office Expense Sheet in Excel 13

  • Then, from Insert >>> select Module. We’ll type our code here.

Insert Module

  • 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

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)

Amount in Words

  • Then, press ENTER.
  • So, this formula converts the number to words as per the VBA code breakdown.

Amount in Words 2

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.

Comments

  • Finally, our office expense sheet is complete. This is what the whole sheet looks like.

How to Make Office Expense Sheet in Excel


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. Moreover, you can visit our site ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!


Related Articles

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo