Excel Certification Study Guide Exam Questions And All Correct Answers.

Excel Certification Study Guide Exam
Questions And All Correct Answers.
Import the data from the source file MoreRentals.xml located in the GMetrixTemplates folder. Load the
Diving_Rentals data to a new worksheet. (Accept all other defaults) - Answer 1. Click the Data tab.

2. In the Get & Transform Data group, click Get Data, then select From File, then select From XML.

3. Browse to the GMetrixTemplates folder.

4. Select the MoreRentals.xml file and click the Import button.

5. In the Navigator pop-up window, select Diving_Rentals.

6. At the bottom of the pop-window, click the dropdown next to Load and click Load To...

7. In the Import Data pop-up window, below the "Where do you want to put the data?" section, select
New Worksheet.

8. Click OK.



On the Surf_Rentals worksheet, clear the formatting in cell range A4:D4 - Answer 1. At the bottom of
the workbook window, click on the Surf_Rentals worksheet to select it.

2. Select cell range A4:D4

3. On the Home tab, in the Editing group, click Clear and select Clear Formats.



On the Surf_Rentals worksheet In cell C15, enter the rental cost of a Full Day Boogie Board Package using
the SUM function and the named range Boogie_Package. - Answer 1. In cell C15, enter the formula
=SUM(Boogie_Package).

2. Press the Enter key to commit the formula and display the answer.(Hint: the value $50 should display.)



Change the orientation of the Surf_Rentals worksheet to Landscape. - Answer 1. At the bottom of the
workbook window, click on the Surf_Rentals worksheet to select it.

2. On the Page Layout tab, in the Page Setup group, click the Orientation dropdown and select Landscape
Orientation.



Inspect the workbook for compatibility issues. Copy the results to a new worksheet. - Answer 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.



Join cells A1:E1 of the Fencing worksheet. Do not change the alignment of the contents. - Answer 1.
Select cell range A1:E1 on the Fencing worksheet.

2. On the Home tab, in the Alignment group, click the Merge & Center dropdown and select Merge
Across.



On the Carriers & Cages worksheet, link the contents in cell C19 to cell B5 on the Fencing worksheet. -
Answer 1. Select cell C19 on the Carriers & Cages 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 B5

5. In the Or select a place in this document field, under Cell Reference, select Fencing

6. Click OK.



On the Carriers & Cages worksheet, apply the 3 Flags Icon Set Conditional Formatting to the contents in
the Inventory column. - Answer 1. Select cell range D4:D19 on the Carriers & Cages 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.



On the Carriers & Cages worksheet, expand the chart data range to include the 20 ft. Outdoor Run. -
Answer 1. On the Carriers & Cages worksheet, click in the center of the chart to select it.

2. Click the Chart Tools contextual tab select Chart Design.

3. In the Data group, click Select Data.

4. In the Select Data Source pop-up window, in the Chart data range field, change the last value to
$D$10. (Hint: the entire data range should be ='Carriers & Cages'!$C$4:$D$10 )

5. Click OK.

, Apply Style 4 to the chart located on the Carriers & Cages worksheet. - Answer 1. On the Carriers &
Cages worksheet, click on the chart to select it.

2. Click the Chart Tools Design contextual tab.

3. In the Styles group, click the More dropdown to open the Chart Styles gallery.

4. Select Style 4.



Simultaneously replace all occurrences of the word Splurge with Tea in the entire workbook. - Answer
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: SplurgeReplace
with: Tea

3. Click the Options button and set the Within: field to Workbook.

4. Click Replace All (Hint: A message should appear stating that 6 replacements were made)

5. Click Close to exit the pop-up window.



On the Costs worksheet, repeat the rows containing the company name and column headings so they
appear on all printed pages. - Answer 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 auto populate this field by selecting the rows with your mouse.)

3. Click OK



Simultaneously apply the $ format to columns B through G on the Profits worksheet. - Answer 1. On
the Profits worksheet, select columns B through G.

2. On the Home tab, in the Number group, click the $ icon.



Modify the chart on the Profits worksheet so the Flavors are displayed as Horizontal Axis Labels and
Expense and Income are the Legend Series. - Answer 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 Design and Format tabs.

3. On the Chart Design tab, in the Data group, click Switch Row/Column.