Creating an Expense report in microsoft excel 2013 Lasted Updated 2/2/2015 Mr

Creating an Expense report in microsoft excel 2013
Lasted Updated 2/2/2015
Mr Craig Morrison
ENG 235 Instructional Assignment
Mr Craig Morrison
ENG 235 Instructional Assignment
Important Safety Information
-Do not have food or drinks around or while using your computer.
-Do not restrict airflow around your computer – this can cause overheating.
Important Safety Information
-Do not have food or drinks around or while using your computer.
-Do not restrict airflow around your computer – this can cause overheating.
This manual will assist you in creating a monthly expense report using Microsoft excel. In order to follow these instructions you will need the following:
Basic Computer Skills
Microsoft Excel 2013
Financial Documents
One month of all expenses and income
Step 1: Open Microsoft Excel
Step 2: Select the “Blank workbook”
Double click on the tab labeled blank workbook.
Note: The image on the right is what your workbook will look like. The workbook is divided into columns (labeled using letters) and rows (labeled using numbers). A single box where a row and a column intersect is called a cell. A cell is named by its column letter and row number. The figure 2.1 shows cell B4 highlighted.
Figure 2.1
Figure 2.1Step 3: Saving the Workbook
Highlight cell B2. Type “Monthly Expense Report”.
Note: Do not worry about formatting at this point. We will format the workbook after we have filled in all information.
In the upper left corner of Microsoft Excel there is a FILE tab. Click on the word FILE.
A drop down menu will appear on the left side of your screen.
Click on Save As.
Click on Computer then select desktop by clicking on it.
A window will pop up and will give you the option to name the report.
Type “Monthly Expense Report” for your file name.
This is highlighted in figure 3.1.
Figure 3.1
Figure 3.1Now Click on Save.
Note: From this point forward, we will save the document by clicking on the save icon () in the top left corner of the screen.
Step 4: Create Categories
Highlight cell B4. Type “Payee”. The payee is the person or company that will be paid.
Press the Tab Key. Type “Amount Due”.
Press the Tab Key. Type “Due Date”.
Press the Tab Key. Type “Paid?”.
Step 5: Enter Expenses
Note: For this step, we will be taking the company name, amount due and the due date from each your financial documents (expenses). The titles and numbers that you fill in on your report will be specific to your expenses. All examples will be generic.
Figure 4.1
Figure 4.1Press the Enter key. This will highlight cell B5. If pressing the Enter key did not select cell B5, please click on cell B5 before beginning to type. Type the Payee from you first financial document. Figure 5.1 will show Mortgage.
Press the Tab key. Type in the Amount Due from the same financial document. Figure 5.1 will show 1500.
Note: Excel will not show the amount as currency. We will format once we have entered all information.
Press the Tab key. Type in the Due Date from the same financial document. Figure 5.1 will show 1/1/2015.
Press the Tab key. In the “Paid?” column you will type yes if you have already paid the payee for this expense and no if you have not paid it. Figure 5.1 will show Yes.
Highlight cell B6 by clicking on it. Type the Payee from your next financial document. Figure 5.1 will show Insurance.
Repeat Steps 5.2 through 5.4. Press the Enter key.
Type the Payee for your next expense. Repeat Step 5.6 for all remaining financial documents.
Save the workbook.
Figure 5.1
Figure 5.1Step 6: Adding Monthly Income
After all documents have been entered press the enter key two more times.
This will give space between the expenses and the totals by skipping a row.
Type “Monthly Income”.
Press the Tab key. From you financial documents find your monthly income. Type this amount into the cell. The example will use 4000.
Save the workbook.
Step 7: Totaling Expenses
Highlight the cell directly below “Monthly Income” by clicking on it. Type “Total Expenses”.
Press the Tab key.
Figure 7.1
Figure 7.1Click on the FORMULA tab located on the top of the screen.
This is highlighted in figure 7.1
Click on Auto Sum tab.
This is highlighted in figure 7.1.
Highlight all of the cells under the “Amount Due” category by clicking on the first expense (listed in cell C5) and dragging the cursor to the bottom of the list of expenses. The figure 7.2 will show cell C5 through cell C9 highlighted in blue. Your expenses will look the same.
Press the Enter key. A total will appear in cell next to “Total Expenses”.
Save the workbook.
Step 8: Determining Unused Income
Figure 7.2
Figure 7.2Click on the cell directly below “Total Expenses”. Press the Enter key. Type “Unused Income”.
Press the Tab key.
Click on the Auto Sum tab.
Click on the cell where you entered you monthly income.
This is cell C12 in figure 8.1 and is 4000.
Press the minus key (-).
Click on the cell to the right of “Total Expenses”
This is cell C13 in figure 8.1 and is 2375.
Press the Enter key. A total will appear in the cell to the right of “Unused Income”.
Save the workbook.
Step 9: Formatting the Report
Step 9.1 Formatting Column Widths
Figure 8.1
Figure 8.1Right Click on the letter “B” labeling the column.
A drop down list will appear.
Click on Column Width.
Type “15”.
Click OK.
Right Click on the letter “C” labeling the column.
A drop down list will appear.
Type “12”.
Click OK.
Save the workbook.
Step 9.2 Formatting Report Title
Highlight Cell B2 through E2 by clicking on cell B2 and dragging your cursor to cell E2.
Right click in the gray shaded area of one of these cells.
A drop down list will appear.
Click on Format Cells.
A window will pop up once you have clicked on this.
Click on the Alignment tab towards the top left hand side of the window.
This is highlighted in figure 9.1.
Check the box next to Merge Cells.
Figure 9.1
Figure 9.1This is highlighted in figure 9.1.
Click on the drop down menu under horizontal and click on center.
This is highlighted in figure 9.1.
Click on OK.
Save the workbook.
Step 9.3 Formatting Dollar Amounts
Highlight all of the cells below “Amount Due” by clicking on cell C5 and dragging your cursor all the way down to the amount to the right of “Unused Income”.
Figure 9.2
Figure 9.2Click on the HOME tab toward the top of your screen.
This is highlighted in figure 9.2.
Click on the Dollar sign located in the number section of the HOME tab.
This is highlighted in figure 9.2.
Save the workbook.
Your monthly expense report is now complete.
I