This project covers the following MOS Objectives:
Adjust row height and column width (EX21-1.3.2)
Freeze worksheet rows and columns (EX21-1.4.3
Search for data within a workbook (EX21-1.2.1)
Display and modify workbook content in different views (EX21-1.4.2)
Customize headers and footers (EX21-1.3.3)
Modify page setup (EX21-1.3.1)
Set a print area (EX21-1.5.1)
Configure print settings (EX21-1.5.3)
Change window views (EX21-1.4.4)
Navigate to named cells, ranges, or workbook elements (EX21-1.2.2)
Insert and remove hyperlinks (EX21-1.2.3)
Display Formulas (EX21-1.4.6)
Import data from .txt files (EX21-1.1.1)
Import data from .csv files (EX21-1.1.2)
Customize the Quick Access toolbar (EX21-1.4.1)
Inspect workbooks for issues (EX21-1.5.4)
Modify basic workbook properties (EX21-1.4.5)
Save workbooks in alternative file formats (EX21-1.5.2)
Important: Download the resource files needed for this project from the Resources link. If the files download in a zipped folder, make sure to extract the files after downloading the resources zipped folder. Visit the SIMnet instant help for step-by-step instruction.
Steps to complete this project:
Mark the steps as checked when you complete them.
Open the start file: EX2021-MOS-Ch1 If the file opens in Protected View, click the Enable Editing button in the Message Bar at the top of the file so you can modify it.
The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor, and save it.
On the Kia data worksheet, make the following modifications:
Autofit column I to fit the data.
Modify the row height of row 1 to exactly 78.
Apply Freeze Panes so the first row is always visible as you scroll down the worksheet.
Find the cell with the underhood ID MKMXV01.6J13 and apply Bold formatting. (Hint: Verify that the top row of the worksheet is no longer selected, or the Find feature may be limited to the highlighted cells.)
Throughout the workbook, review headers and footers and make the following modifications:
From Normal view, on the Kia data worksheet, add a custom header to the center section: Kia Data
On the 2020 Green Cars Federal worksheet, use Layout view to delete the text in the left footer section and replace it with: Source: fueleconomy.gov
On the 2020 Green Cars Federal worksheet, add the sheet name to the footer center section. Hint: Do not type the sheet name in the footer. Insert a code so Excel will display the name of the current worksheet.
On the Kia data worksheet, make the following page and print settings modifications:
Using Page Break Preview view, move the page break between pages one and three to before the Stnd Description column (between columns H and I).
Define the print area to print only the Kia Forte data (cells A1:R7).
On the 2020 Green Cars Federal worksheet, make the following page and print settings modifications:
Change the orientation to landscape.
Modify the scaling so all columns will fit on one page.
Modify the print settings to repeat row 1 at the top of every page.
Set the margins to the Narrow option.
Split the worksheet view into two vertical panes at cell H1.
On the Kia data worksheet, navigate to the named range Sportage_data. When you navigate to the range, Excel will highlight the range for you. Copy the data and paste starting in cell A10 on the Summary worksheet.
Add hyperlinks to the Summary worksheet.
On the Summary worksheet, insert a hyperlink in cell A1 to navigate to cell A1 on the Kia data worksheet.
On the Summary worksheet, insert a hyperlink in cell A2 to navigate to the web site: https://www.fueleconomy.gov/feg/download.shtml
On the Summary worksheet, display formulas.
Import from a text file.
Import the tab-delimited text file SmartWay Vehicle List for MY 2020.txt to a new worksheet.
Rename the sheet: SmartWay
Import from a CSV file.
Import the CSV file 2022 FE Guide preliminary data.csv to a new worksheet.
Rename the sheet: 2022 Prelim
Add the Quick Print option to the Quick Access Toolbar.
Inspect the workbook for issues and remove all document properties and personal information.
Modify the workbook properties to add the keyword: fuel
Save and close the file.
Upload and save your project file.
Submit project for grading.
If directed to do so by your instructor: open the workbook file and save it in the .xls format compatible with older versions of Excel. Provide this file to your instructor for additional grading.
