Excel Practice Test 1
Excel Practice Test 1
Project 1/7 - Overview
Snow Rentals
You manage the rental shop at a large winter resort. It is your... [Show More] responsibility to update the list of rental rates.
Project 1/7 - Task 1
Import the comma delimited file Accessories.csv located in the GMetrixTemplates folder into a new worksheet. Add the import to the data model and accept all other defaults.
1. Click the Data tab.
2. In the Get & Transform group, click From Text/CSV.
3. Browse to the GMetrixTemplates folder.
4. Select the Accessories.csv file and click the Import button.
5. In the Accessories.csv pop-up window, ensure that Delimiter is set to Comma.
6. At the bottom of the pop-up window, click the dropdown arrow next to Load and click Load To...
7. In the Import Data pop-up window, under the Where do you want to put the data? section, make sure New worksheetis checked.
8. Click the checkbox for Add this data to the Data Model.
9. Click OK.
Project 1/7 - Task 2
Clear the formatting in cell range A4:D4 on the Rental Rates worksheet.
1. Select cell range A4:D4 on the Rental Rates worksheet.
2. On the Home tab, in the Editing group, click Clear and select Clear Formats.
Project 1/7 - Task 3
Rename Table1 to Rates.
1. On the Formulas tab, in the Defined Names group, click Name Manager.
2. In the Name Manager pop-up window, make sure Table1 is selected and click the Edit button.
3. In the Edit Name pop-up window, replace the name Table1 with Rates.
4. Click OK.
5. Click Close.
Project 1/7 - Task 4
Change the orientation of the Rental Rates worksheet to landscape.
1. Select the Rental Rates worksheet.
2. In the Page Layout tab, Page Setup group, select the Orientation dropdown.
3. Select Landscape.
Project 1/7 - Task 5
Inspect the workbook for compatibility issues. Copy the results to a new worksheet.
1. Click the File tab.
2. On the default Info page, click the Check for Issues button.
3. Select Check Compatibility.
4. At the bottom of the Microsoft Excel - Compatibility Checker pop-up window, click Copy to New Sheet.
diff
diff
Project 2/7 - Overview
Camping Supplies
A discount camping supply store is creating an inventory report. They need help with charts, hyperlinks, and conditional formatting. You've been asked to assist.
Project 2/7 - Task 1
Join cells A1:E1 of the Fishing worksheet. Do not change the alignment of the contents.
1. Select cell range A1:E1 on the Fishing worksheet.
2. On the Home tab, in the Alignment group, click the Merge & Center dropdown arrow and select Merge Across.
Project 2/7 - Task 2
On the Tents worksheet, link the contents in cell B13 to cell A4 on the Fishing worksheet.
1. Select cell B13 on the Tents worksheet.
2. On the Insert tab, in the Links group, click Link.
3. In the Link to: section, select Place in This Document.
4. In the Type the cell reference field, type A4
5. In the Or select a place in this document field, under Cell Reference, highlight Fishing.
6. Click OK.
Project 2/7 - Task 3
On the Tents worksheet, apply Conditional Formatting using the 3 Flags Icon Set to the values in the Inventory column.
1. Select cell range C4:C11 on the Tents worksheet.
2. On the Home tab, in the Styles group, click Conditional Formatting to open the menu.
3. Select Icon Sets and click 3 Flags.
Project 2/7 - Task 4
On the Tents worksheet, expand the chart data range to include the Giant Truck Bed Tent in cell B7.
1. On the Tents worksheet, click in the center of the chart to select it.
2. Click the Chart Design contextual tab.
3. In the Data group, click Select Data.
(Hint: The view will switch to the data source on the worksheet, cell range B4:C6.)
4. Hold down the Ctrl key and select cell range B7:C7.
(Hint: In the Select Data Source pop-up window, in the left window pane, Giant Truck Bed Tent should now appear below 4-Person Tent.)
5. Click OK.
(Hint: Giant Truck Bed Tent should now be added to the legend representing the last stack on the column chart.)
Project 2/7 - Task 5
...
diff
diff
Project 3/7 - Overview
Taffy
A shop you manage sells custom-flavored taffy. You are tracking the cost and profit of each flavor and summarizing the information in a workbook.
Project 3/7 - Task 1
Simultaneously replace all occurrences of the word Choco with Chocolate in the workbook.
1. On the Home tab, in the Editing group, click Find & Select and choose Replace.
2. In the Find and Replace pop-up window, type the following in the fields:
- Find what: Choco
- Replace with: Chocolate
3. Click the Options button and set the Within: field to Workbook.
4. Click Replace All.
5. Click Close to exit the pop-up window.
Project 3/7 - Task 2
On the Costs worksheet, repeat the rows containing the company logo and column headings so they appear on all printed pages.
1. On the Page Layout tab, in the Page Setup group, click Print Titles.
2. In the Page Setup pop-up window, on the Sheet tab, type the following in the fields:Rows to repeat at top: $1:$3
(Hint: You can also autopopulate this field by selecting the rows with your mouse.)
3. Click OK.
(Hint: You can see the result by clicking Print Titles again and then clicking Print Preview. The first page will display in the Print Preview window. At the bottom of the window, advance to the second page by clicking the Next Page arrow located to the right of 1 of 2. You should see the column headings displayed at the top of the table on both pages. Click the return arrow in the upper-left corner of the window to go back to the spreadsheet.)
Project 3/7 - Task 3
Simultaneously apply the Number format to columns B through E on the Costs worksheet.
1. On the Costs worksheet, select column headings B, C, D, and E. Shift + Click cell E26 to select the data.
2. On the Home tab, in the Number group, click the down arrow in the Number Format window and select Number.
Project 3/7 - Task 4
Modify the chart on the Profits worksheet so that the Flavors are displayed as Horizontal Axis Labels and so that Expense and Income are the Legend Series.
1. If the Profits worksheet is not already displayed, click the Profits worksheet tab to select it.
2. Click the chart to reveal the Chart Tools tabs.
3. On the Chart Tools Design tab, in the Data group, click Switch Row/Column.
Project 3/7 - Task 5
Modify the chart on the Profits worksheet so the Legend appears at the Top.
1. If the Profits worksheet is not already displayed, click the Profits worksheet tab to select it.
2. Click the chart to reveal the Chart Tools tabs.
3. On the far left of the Chart Tools Design tab, in the Chart Layouts group, click Add Chart Element, select Legend, and click Top.
diff
diff
Project 4/7 - Overview
Network Equipment
You are a local retailer who sells, installs, and supports computer networking equipment. You are updating your price list.
Project 4/7 - Task 1
Create one named range from the cell ranges D5:D15 and D18:D26. Name the range Prices.
1. Select cell range D5:D15.
2. While holding down the CTRL key, also select cell range D18:D26.
3. With both cell ranges selected, on the Formulas tab, in the Defined Names group, click Name Manager.
4. On the Name Manager pop-up window, click the New... button.
5. In the New Name pop-up window, enter the Name Prices.
6. Verify that the Refers To: field contains ='Price List'!$D$5:$D$15,'Price List'!$D$18:$D$26.
7. Click OK.
8. Click Close.
Project 4/7 - Task 2
In cell H5, enter a formula referencing the named ranges Price_10G, Install_10G, and Support_10G to display the total sum value of the 10 Gbps Routers.
1. Click cell H5.
2. In the Formula Bar, type =SUM(Price_10G,Install_10G,Support_10G).
3. Press Enter on your keyboard to commit the formula.
Project 4/7 - Task 3
Remove all conditional formatting on the worksheet.
1. On the Home tab, in the Styles group, click Conditional Formatting.
2. Select Clear Rules and click Clear Rules from Entire Sheet.
Project 4/7 - Task 4
On the Price List worksheet, simultaneously rotate the text Port Size, Band Size, Price, Install, and Support in both tables to Angle Counterclockwise.
1. On the Price List worksheet, select cell range C4:F4, and then while holding Ctrl on your keyboard, select C17:F17.
2. On the Home tab, in the Alignment group, click Orientation and select Angle Counterclockwise.
Project 4/7 - Task 5
Perform a multiple column sort on the data in the Wired Equipment table. First sort alphabetically by Wired Equipment (from A to Z), then sort by Port Size (from A to Z).
1. On the Wired Equipment table, click on any cell in the table.
2. On the Home tab, in the Editing group, click Sort & Filter and select Custom Sort...
3. In the Sort pop-up window, configure the fields as shown below
- Column Sort by: Wired Equipment
- Sort on: Cell Values
- Order: A to Z
4. Click Add Level.
5. Configure the second row of fields as shown below:
- Then by: Port Size
- Sort on: Cell Values
- Order: A to Z
6. Click OK. [Show Less]
