Work in Excel, do either of the following: Print this page of instructions or move back and forth between this page and Excel by clicking each application's button on the Windows taskbar
1. Click on this link Expense Report, save it on your computer in your usual work folder, and then open it in Excel. This partially completed workbook contains an incomplete expense report that needs formatting changed, boxes filled in with appropriate information and formulas created for correct calculations.
2. First, click on the small box that is to the left of the 1 and above the letter A in the column, row indicator areas. This will highlight the entire spreadsheet.
3. Next, position your cursor in between columns A and B so that your cursor looks like a plus sign with arrows on the left and right. Double click here to make the columns fit around the existing data. To finish, click anywhere in the worksheet once to remove the highlight from the entire page.
4. Create a border around the data entry portion of this spreadsheet. This is done by selecting cells A8:M12. Put a border around the outside of this area only.
5. Fill in all blank boxes with appropriate data within the A8:M12 data range. For text or date information, provide appropriate text or date entries. Reasonable numbers can be put in, or the number 0 is valid. For example, lunch should not cost 678 dollars. That is unreasonable.
6. Format all of the dollar figure numbers as 2 digit United States currency.
7. In cell I4, type the words, "Allowance Per Mile." In cell L4, type the number 0.40. Format this number as currency. Select cells I4:K4 and use the merge and center button to format this.
8. In the Daily Totals column, calculate the cost for each day. To do this, type this formula: =SUM(C8:H8,J8:M8). Copy this formula down to the other appropriate rows below.
9. Next, calculate to totals for each category. In row 14 under the Brkfast column, add all of the numbers above it using a SUM formula, then copy it across under each and every column.
10. In cell J8, calculate the cost allowance per mile under the mileage$ column for each date. You need to multiply the mileage by the mileage allowance. Be sure to use an absolute reference when referring to the mileage allowance since it is not in the table and only occurs once at the top of the sheet.
11. Copy this formula down to each of the four columns directly below cell J8.
12. Make all headings Bold.
13. Create a report title by typing the words, "Expense Report" in cells E2. Select cells E2:J2 and use the Merge and Center button to center the title across the report. Change its font size to 14. Also, use the Merge and Center button to place the words "Transportation" above "Air and Ground" and "Meals" above "Brkfast, Lunch, and Dinner." Don't forget to format these words to match the other headings.
14. Format the dates into the full month name, day and year format.
15. Finally, repeat steps 2 and 3 so that the spreadsheet has all of the data showing in it.
16. Save your completed report as Expense Report 1.
I have saved in Excel 2007 and Excel 2003 version.
To write any ...
The expert examines an expense report for the Excel spreadsheet.
Creating an excel spreadsheet
Details: In this project, you will create an expense report for a company's sales force, then save it. To view these instructions while you work in Excel, do either of the following: Print this page of instructions or move back and forth between this page and Excel by clicking each application's button on the Windows taskbar
Retrieve Expense Report, save it on your computer, and open it in Excel. This partially completed workbook contains the column headings and several expense items.
Enter a SUM function in cells C12: N12 that totals the figures in rows 8 through 11. To do so, enter =SUM(C8:C11) in cell C12, then use the copy and paste feature to copy the formula to columns D through N. (Remember that you can paste more than once without copying again).
In cell M4, put the label "Allowance." In cell M5, type "0.32" (the amount paid per mile). In cell M8, put a formula that multiplies the mileage in cell L8 by the mileage allowance in cell M5. (Remember to use an absolute reference to cell M5 and a relative reference to L8.) Copy the formula to cells M9:M11.
In cell N8, sum the range C8:K8 and M8 (omitting column L, which is not an expense). Hint: One way is to use the SUM function for the contiguous range and add in the noncontiguous cell. Another way is to enter the range as two separate arguments of the SUM function.) Copy the formula to cells N9:N11.
Select the column headings in row 6 and format them as bold.
Create a report title by typing Expense Report in A2. Select cells A2:N2 and use the Merge and Center button to center the title across the report. Change its font size to 14. Also, use the Merge and Center button to place "Transportation" above "Air and Ground" and "Meals" above "Brkfast, Lunch, and Dinner." Don't forget to format these words to match the other headings.
Select the date in cells A8:A11 and change the format to day-month.
Highlight the range A8:M11. Change the background to Light Turquoise; add borders around each cell in the range. (Hint: You can use the All Borders button on the Formatting toolbar.)
You may need to change the column widths.
Using Page Setup, create a custom footer with "Created by Your Name" at the right side. Change the page orientation to Landscape, and select the Fit to One Page option.
Save the finished workbook as Expense Report 1.