Tag Archives: Hands-on Training

Microsoft Excel 2010: Creating a Mileage Log

If you drive to client sites for an organization, the company expects you to record the miles covered during the site visit, so that you can submit the mileage log with your expense report.

It is easy to create a template in Excel to manage a log of the miles covered through driving. Here is a step-by-step instruction for creating a Mileage Log Form using the Excel spreadsheet.

Step 1. Start Microsoft Excel.

Step 2. In cell B2 type ‘Mileage Log’.

Step 3. Select cell B1 to E1 and click on Merge and Center Justify icon  in the toolbar to merge and center the text. Repeat for cells B2 through E2 and B3 through E3.

Step 4. Select cell B1 through E3, use fill color to add black color to the cell, set the text size 20 and color to white.

Step 5. Type the information in the following cells:

 B4: Date
C4: Time
D4: Purpose
E4: Description
F4: Location
H4: Odometer Reading
J4: Mileage
F5: From
G5: To
H5: Start
I5: Finish 

Select cell F4 and G4 and use merge and center align  icon. Again select cell H4 and I4 and use merge and center align  icon to merge the cells and center align the text.

Step 6. Select the cells B2 through J5, center align the text, set the cell color to blue and text color to white.

Step 7. The next step is to add the picture to the Mileage log. To insert the picture, click on the Insert tab in the Ribbon, and then click on Picture in the Illustrations group.  This will pop up the Insert Picture Window.

Step 8. Select the picture you wish to insert into the Excel file and click on the Insert button. This will insert the selected picture into you working spreadsheet.

Step 9. Right click on the image in the spreadsheet and select size and Properties… option from the drop down menu. In the Size and Properties Window, click on the size tab and adjust the size of the picture by specifying the height and width of the picture and click on the Close button, or dragging the corner image to resize I to the desire width and height. Place the image between F1 and J3.

Step 10. Add border around the cells by selecting cell B6 through J15 and clicking on all border in the Font Group on the Home Tab.

Step 11. In column J, we will add a formula that gives us the difference between the odometer Reading from start and finish odometer readings. To add the formula, click on cell J6 and type ‘=I6-H6’. Copy the formula in cell J6 to cell J7, J8, J9 up to J15.

Step 12. To test the formula, type following data in each of the cells listed below

Cell B6: 3/15/2003
C6: 1:30 PM
D6: Deliver goods
E6: Business
F6: Framingham, MA
G6: Boston, MA
H6: 33,489.10
I6: 33521.4
B7: 3/15/2003
C7: 3:00 PM
D7: Shop for gift
E7: Personal
F7: Boston, MA
G7: Cambridge, MA
H7: 33,521.40
I7: 33,523.80

Step 13. Go to the Quick Access Toolbar and click on the Print Preview icon  to preview the document or click on Microsoft File Tab and select Print from the drop down menu and then select Print Preview from the Print and Preview the document dropdown options.

Step 14. Since the data fits on more than one page, click on Page Setup icon  in the Print Preview Tab. In the Page Setup dialog box, select the Page tab and select the Landscape option.

Step 15. Click OK, when done. This will change the orientation of the page to landscape, thus giving you more space for your column width. The Form should now fit in one page.

Step 16. To set the Header and Footer, click on the Print Preview and then click on the page setup icon. It will popup the Page Setup Window. Click on the Header/Footer tab in the Page Setup Window.

Click on the Custom Header … button. Click on the center section and type the following:

NR Computer Learning Center
702 El Camino Real
Tustin, CA 92780

Step 17. When done, click OK button.

Step 18. Click now on the Custom Footer… button.

Step 19. Click on the Left section, and the click on the date icon . Next click on the Right section and then click on the time icon .

Step 20. Click OK button when done. Click on OK button again to close the Page Setup window.

Step 21. You should now have a similar worksheet as displayed below with both the header information on the top of the page and footer information at the bottom of the page.

If you want to change the page layout then click on the Page Setup icon. In case, you are satisfied with the document and want to print it, then click on the Print icon  to print the spreadsheet.

For more information on courses, visit us at www.nrclc.com

Doing A Monthly Payment Calculation in Excel

Below is an instructional video on using calculations and formulas in Excel 2010, accompanied with a step by step guide that walks you through the exercise.

Step 1: Create a spreadsheet with following information:

Cell Address Content
A1 “Car Payment Calculation”
A2 Add a picture of a car
A3 “Enter Your information Below”
A4 “Price of car”
A5 “Down payment”
A6 “Amount to finance”
A7 “Interest rate”
A8 “Term (years)”
A9 “Your monthly payment”

Step 2: Save the file as autoPayment.xls.

Step 3: Go to cell B9, and invoke the formula window. Select PMT function under the Financial list. This will popup the Function Arguments window.

Step 4: Type the following information:
Rate: B7/12
Nper: B8*12
Pv: -B6

Click Okay.
In the formula bar, you should see following formulas:
=IF(B8=0, 0, PMT(B7/12,B8*12,-B6))

Step 5: To get the total amount to be financed, Add the formula “=B4-B5”

Step 6: To test the spreadsheet, enter following values:
Cell B4: 20,000
B5: 5,000
B7: 11
B8: 60

Results: The cell B6 should display $15,000 for Amount to Finance and the Monthly Payment should be $137.69 at interest rate of 11% for 60 months.

Step 7: Save the worksheet as AutoPayment.xls on your computer.

For more information on similar courses, or other learning experiences, visit us at www.nrclc.com

April 23, 2011 – Magic of Being Found Online: Internet Marketing for Business

Learn how to be found online by promoting, monitoring, and developing interactive communication through LinkedIn, Facebook, WordPress, SEO, and Web Analytics.  Presenters include: Tanya Salcido, Dean Soto, Steve Wiideman and Vazi Okhandiar.

Location: 2100 W. Orangewood, Suite 110, Orange, CA

For more information, click here.