Excel Certification Study
Add Open to the Quick Access Toolbar.
1. Above the ribbon, click the Customize Quick Access Toolbar dropdown. (If the Quick Access Toolbar is not visible, go to File > Options > Quick Access Toolbar and check the box to Show Quick Access Toolbar, and select the Toolbar Position as Above Ribbon.) 2. Click Open.
Change the orientation of the Surf_Rentals worksheet to Landscape.
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.
On the Surf_Rentals worksheet, clear the formatting in cell range A4:D4
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 Food Inventory worksheet, enter a function in cell G2 that counts the number of Food Items.
1. At the bottom of the workbook, click the Food Inventory tab. 2. Select cell G2. 3. On the Formulas tab, in the Function Library, click Insert Function. 4. In the Search for a function: field, type Count and click Go. 5. In the Select a function field, click COUNTA and click OK. 6. In the Function Arguments pop-up window for COUNTA, configure the following:Value1: A3:A59 7. Click OK.
On the Food Inventory worksheet, change the style to Dark Red, Table Style Medium 16.
1. At the bottom of the workbook, click the Food Inventory worksheet tab. 2. Click anywhere within the table to select it. 3. On the Home tab, in the Styles group, click Format as Table and select Dark Red, Table Style Medium 16.
Sort the Location Sales worksheet alphabetically (from A to Z), first by Location, then by Product, and then by Season (Spring, Summer, Fall).
1. At the bottom of the workbook, click the Location Sales tab. 2. Click any cell within the table to select it. 3. On the Home tab, in the Editing group, click Sort & Filter and select Custom Sort... 4. In the Sort pop-up window, configure the fields as shown below:Column Sort by: Location Sort on: Cell Values Order: A to Z 5. Click Add Level 6. Then by: Product Sort on: Cell Values Order: A to Z 7. Click Add Level 8. Then by: Season Sort on: Cell Values Order: Custom List ... 9. In the Custom Lists pop-up box, select NEW LIST and type the following List Entries: Spring, Summer, Fall 10. Click Add. 11. Click OK. 12. Click OK.
On the Parts worksheet, change the style to Aqua, Table Style Medium 20.
1. At the bottom of the workbook, click the Parts worksheet tab. 2. Click anywhere within the table to select it. 3. On the Home tab, in the Styles group, click Format as Table and select Aqua, Table Style Medium 20.
Insert Page 1 of ? page numbering in the header of the Q1 Sales worksheet. When finished, return to Normal view.
1. At the bottom of the workbook, click the Q1 Sales worksheet tab to select it. 2. On the Insert tab, in the Text group, click Header & Footer. 3. In the Header & Footer group, click Header and select Page 1 of ? 4. On the View tab, in the Workbook Views group, click Normal to close the Page Layout View (a.k.a., Header and Footer View).
Copy the data from cell range A4:E9 on the Q1 Sales worksheet and transpose it to the cell range beginning at A4 on the Seedling Sales worksheet.
1. At the bottom of the workbook, click the Q1 Sales worksheet tab to select it. 2. Select all cell range A4:E9. (Hint: Do not include the Total column). 3. Right-click and select Copy. 4. At the bottom of the workbook, click the Seedling Sales worksheet tab to select it. 5. Click on cell A4. 6. Right-click and below the Paste Option section, click Transpose (4th icon).
On the Revenue worksheet, in cell I7, insert a function that lists the unique locations in cell range A3:A37.
1. At the bottom of the workbook, click the Revenue tab. 2. Select cell I7. 3. In the Formula Bar, type =UNIQUE(A3:A37) 4. Press the Enter key on your keyboard to commit the formula.
On the Revenue worksheet, enter a formula in cell J3 that uses a function to calculate the average revenue from the January column.
1. At the bottom of the workbook, click the Revenue tab.1. Select cell J3. 2. In the Formula Bar, type =AVERAGE, then press the tab key on your keyboard. 3. To the left of the Formula Bar, click fx to open the Function Arguments wizard. 4. In the Function Arguments wizard, configure the following:Number1: C3:C37 5. Click OK.
On the Sales by Club worksheet, configure the table to display only records of the Blazing Bikes club members by filtering by the cell color Blue.
1. At the bottom of the workbook, click the Sales by Club worksheet. 2. On the table, click the autofilter dropdown of any header. 3. Select Filter by Color and click the Blue icon.
Remove the table formatting from the Small Tree Sales worksheet, but keep the cell formatting.
1. At the bottom of the workbook, click the Small Tree Sales worksheet tab. 2. Click anywhere within the table to select it (Hint: You must click within cell range A4:F14.) 3. Click on the Table Design tab. 4. In the Tools group, click Convert to Range. 5. If prompted, "Do you want to convert the table to a normal range?" Click, Yes.
Change the margins of the Summary worksheet to 0.75" on the top, bottom, left, right, and a 0.5" header and footer. Center the worksheet horizontally within the margins.
1. At the bottom of the workbook, click the Summary tab to make this the active worksheet. 2. On the Page Layout tab, in the Page Setup group, click Margins and select Custom Margins ... 3. Configure the margins as follows: Top: 0.75 Header: 0.5Left: 0.75 Right: 0.75 Bottom: 0.75 Footer: 0.5 4. Check the box to Center on Page: Horizontally 5. Click OK.
Save the Summary worksheet into the GMetrixTemplates folder as a PDF file with the name, Summary.
1. At the bottom of the workbook, click the Summary tab to make this the active worksheet. 2. Select File the tab. 3. In the left pane, select Save As and browse to the GMetrixTemplates folder. 4. In the Save As pop-up window, configure the following:File name: SummarySave as type: PDF (*.pdf) 5. Accept all other defaults and click Save.
Apply the Pattern Color Light Gray, Background 2, Darker 10% with the Pattern Style Thin Diagonal Crosshatch to cell range A3:A8 on the Total Cookie Sales worksheet.
1. At the bottom of the workbook, click the Total Cookie Sales worksheet. 2. Select cell range A3:A8. 3. On the Home tab, click the dialog box launcher located in the lower-right corner of the Font group. 4. In the Format Cells dialog box, click the Fill tab. 5. On the Fill tab, beneath Pattern Color, select Light Gray, Background 2, Darker 10%. 6. On the Fill tab, beneath Pattern Style, select Thin Diagonal Crosshatch. 7. Click OK.
In the Grain Production worksheet, modify the table style to remove the shading that appears on every other row.
1. Click anywhere in the first table to select it. 2. On the Table Design contextual tab, in the Table Styles Options group, click the box Banded Rows to clear it.
Add a function to cell G4 in the Wheat or Rice? column that will display the word Wheat if the amount of Wheat (F4) is greater than the amount of Rice (E4), else display the word Rice. Copy the formula to the remaining cells in the column.
1. Click cell G4. 2. In the Formula Bar, type =IF, then press the tab key on your keyboard. 3. To the left of the Formula Bar, click fx to open the Function Arguments wizard. 4. In the Function Arguments wizard, configure the following:Logical_test: F4>E4Value_if_true: "Wheat" (Hint: Text values must be enclosed in quotes.)Value_if_false: "Rice" 5. Click OK.
Use the structured reference in a formula to calculate the number of rows containing Phones on sale. Display the result in cell H5.
1. Click cell H5. 2. In the Formula Bar, type =ROWS(Phones) 3. Press Enter on your keyboard to commit the formula.
Adjust column G to a width of 14.
1. Click column G to select it. 2. On the Home tab, in the Cells group, select Format and click Column Width... 3. In the Column Width pop-up window, enter the value 14. 4. Click OK.
On the Recent Releases worksheet, select all of the video game titles in the cell range named No_Platform and delete their rows. Retain all other cells on the worksheet.
1. Click on the Recent Releases worksheet to select it. 2. Above the worksheet to the left of the formula bar, click the Name Box dropdown. 3. Select No_Platform. 4. Right-click any of the several selected cells down the sheet, like A6, and select Delete. If the option is presented, select Table Rows.
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)
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.
Add the Status Draft to the document properties.
1. Click the File tab. 2. In the bottom right corner of the Info page, click the Show All Properties link. 3. In the Status field, type Draft. 4. Click the return arrow in the upper-left corner of the Info window to display the document.
Modify the scaling of the Sales Commissions worksheet so it fits on a single page when printed.
1. Click the File tab. 2. In the category column, click Print. 3. At the button of the Settings section, click the dropdown to change No Scaling to Fit Sheet on One Page. 4. Click the circled arrow located in the upper-left of the File tab to return to the Sales Commissions worksheet.
Add the value New Rates to the Title property of the document.
1. Click the File tab. 2. Locate the list of Properties on the right side of the Info page. 3. In the Title property field, type, New Rates. 4. Click the return arrow in the upper-left corner of the Info window to return to the worksheet.
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.
Remove all hidden properties and personal information from the workbook.
1. Click the File tab. 2. On the default Info page, click the Check for Issues button. 3. Select Inspect Document. (Hint: If prompted to save your changes, click Yes) 4. In the Document Inspector pop-up window, accept all of the default selections and click Inspect. 5. On the results page, next to Document Properies and Personal information, click Remove All. 6. Click Close. 7. Click the circled arrow located in the upper-left of the File tab to return to the Grain Production worksheet.
In the Qtr 1 worksheet change the configuration of the Q1 Location Revenue chart so that it displays the months on the x-axis and the revenue on the y-axis.
1. Click the Qtr 1 worksheet tab to select it. 2. Click the chart to reveal the Chart Tools tabs. 3. On the Chart Design tab, in the Data group, click Switch Row/Column.
Group the Profits, Costs, and Chart worksheets and change the tab color to Pink, Accent 4.
1. Hold down the CTRL key and select the Profits, Costs, and Charts worksheets. 2. Release the CTRL key. The three worksheets should all be selected. 3. Right click one of the worksheet tabs 4. Select Tab Color and under Theme Colors select Pink, Accent 4
Modify the chart on the Profits worksheet so the Flavors are displayed as Horizontal Axis Labels and 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 Design and Format tabs. 3. On the Chart Design tab, in the Data group, click Switch Row/Column.
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 Design tab, in the Chart Layouts group, click Add Chart Element, select Legend and click Top.
Switch the rows and column source data of the chart on the Seedling Sales Chart worksheet, so the Legend is displayed as Horizontal Axis Labels.
1. If the Seedling Sales Chart worksheet Is not already displayed, click the Seedling Sales Chart worksheet tab to select it. 2. Click the chart to reveal the Chart Tools tabs. 3. On the Chart Design tab, in the Data group, click Switch Row/Column.
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.
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.)
On the Average Call Time worksheet, use an Excel feature to copy the sparkline into all the vacant cells of the Trend column.
1. On the Average Call Time worksheet, select cell G4. 2. In the lower-right corner of cell G4, click-drag the fill handle down the Trend column beginning with cell G4 through G16.
On the Average Call Time worksheet, create a 3-D Clustered Column chart that shows the call times for each Salesperson from Mon through Fri. Insert the Chart Title Average Call Time. Position the new chart below the table.
1. On the Average Call Time worksheet, select cell range A3:F16. 2. Click the Insert tab. 3. In the Charts group, click the Insert Column or Bar Chart icon and select 3-D Clustered Column. 4. Double-click Chart Title. Type in the title, Average Call Time. 5. Click-drag the chart to position it below the table.
On the Carriers & Cages worksheet, expand the chart data range to include the 20 ft. Outdoor Run.
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.
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.
Configure the Catalog worksheet so rows 1 through 3 remain visible as you scroll vertically.
1. On the Catalog worksheet, select row 4. 2. On the View tab, in the Window group, click the Freeze Panes dropdown and select Freeze Panes.
On the Chart worksheet, include the Total Profit data on the Flavor Expenses, Income and Profit chart.
1. On the Chart 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. 4. In the Select Data Source pop-up window, in the left window pane click the box Total Profit to enable it. 5. Click OK.
Import NewFeeds.txt located in the GMetrixTemplates folder as a table on a new worksheet.
1. On the Data tab, in the Get & Transform Data group, click From Text/CSV.(Hint: Comma-Delimited files and Tab-Delimited files are types of Text files.) 2. In the Import Data pop-up window, browse to the GMetrixTemplates folder. 3. Select the NewFeeds.txt file and click the Import button. 4. In the NewFeed.txt pop-up window, configure the following: File Origin: accept the default - 1252: Western European (Windows)Delimiter: TabData Type Detection: accept the default - Base on first 200 rows 5. Click the dropdown to the right of the Load button and select Load To... 6. In the Import Data pop-up window, configure the following:Select how you want to view this data in your workbook: TableWhere do you want to put the data? New Worksheet 7. Click OK.
On the Employee Bonuses worksheet, remove the row containing the salesperson named Allen.
1. On the Employee Bonuses worksheet, right-click row 11 and select Delete.
On the Employee Bonuses worksheet, insert a mixed reference in cell F4 so the formula will correctly calculate the commission.
1. On the Employee Bonuses worksheet, select cell F4. 2. Insert the mixed reference =G$2*E4 in cell F4.
Add a function to the Overtime column cell H4 on the Employee Hours worksheet that will display the word Yes if the value in cell J4 is higher than 40 hours, otherwise display the word No. Copy the function from H4:H16.
1. On the Employee Hours worksheet, click cell H4. 2. In the Formula Bar, type =IF, then press the tab key on your keyboard. 3. To the left of the Formula Bar, click fx to open the Function Arguments wizard. 4. In the Function Arguments wizard, configure the following:Logical_test: J4>40Value_if_true: YesValue_if_false: No 5. Click OK.
On the Employee Hours worksheet, add a row to the table that automatically calculates total hours worked by all employees.
1. On the Employee Hours worksheet, click on any cell in the table to select the table. 2. On the Table Design tab, in the Table Style Options group, click the Total Row to enable it.
In cell I4 of the Employee Hours sheet, use a function to copy the name from cell A4 and format the name so all letters are upper case.
1. On the Employee Hours worksheet, select cell I4.(Hint: Always put your formula in the cell where you want the result displayed.) 2. In the Formula Bar, type =UPPER, then press the tab key on your keyboard. 3. To the left of the Formula Bar, click fx to open the Function Arguments wizard. 4. In the Function Arguments wizard, configure the following:Text: A4 5. Click OK.
Remove the conditional formatting from the table on on the Feed Inventory worksheet.
1. On the Feed Inventory worksheet, click anywhere on the table. 2. On the Home tab, in the Styles group, click Conditional Formatting. 3. Select Clear Rules and click Clear Rules from This Table.
On the Feed Inventory worksheet, apply Style 5 to the chart.
1. On the Feed Inventory worksheet, click on the chart to select it. 2. Click the Chart Design contextual tab. 3. In the Styles group, click the More dropdown arrow to open the Chart Styles gallery. 4. Select Style 5
On the Feed Inventory worksheet, remove the hyperlink functionality, but leave the text in cell C27.
1. On the Feed Inventory worksheet, select cell C27. 2. Right-click and select Remove Link.
On the Food Inventory worksheet, insert a new row before row 10, which contains the content Lettuce.
1. On the Food Inventory worksheet, select row 10. 2. Right-click and select Insert.
Simultaneously replace all occurrences of the word Splurge with Tea in the entire 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: 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.
Remove all conditional formatting from this worksheet.
1. On the Home tab, in the Styles group, click Conditional Formatting. 2. Select Clear Rules and click Clear Rules from Entire Sheet.
Insert a Footer that displays today's date on the right and then return to Normal view.
1. On the Insert tab, in the Text group, click Header & Footer. 2. In the Navigation group, click Go to Footer. 3. Click the cell on the right most cell in the footer to select it. 4. On the Header & Footer tab, in the Header & Footer Elements group, click Current Date. 5. Click anywhere outside the Footer to commit the change. 6. On the View tab, in the Workbook Views group, click Normal to close the Page Layout View (a.k.a., Header & Footer View).
On the Organic Feed worksheet, format the data range A3:F10 as a table that has headers. Apply the Lime, Table Style LIght 14 format.
1. On the Organic Feed worksheet, select cell range A3:F10 2. On the Home tab, in the Styles group, click Format as Table to open the gallery. 3. Under the Light section, click Lime, Table Style Light 14. 4. In the Format As Table pop-up window, do the following: a. Confirm the data field contains =$A$3:$F$10. b. Confirm the My table has headers box is enabled. c. Click OK.
On the Costs worksheet, repeat the rows containing the company name 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 auto populate this field by selecting the rows with your mouse.) 3. Click OK
On the Parts worksheet, Use Autofill to copy the formula in cell E4 to calculate the Total for January through March for each employee.
1. On the Parts worksheet, select cell E4. 2. In the lower-right corner of cell E4, click-drag the fill handle down the Total column, beginning with cell E4 through E11.
On the Price List worksheet, simultaneously rotate the text Storage, Price, Sale 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.
Filter the Profits worksheet to display only flavors with Total Profit that are Above Average.
1. On the Profits worksheet, click the autofilter dropdown on the Total Profit column heading. 2. Hover over Number Filters and click Above Average.
Use a builtin Excel feature to copy all of the formatting of cell range A2:G2 located on the Profits worksheet and apply that formatting to cell A2 on the Costs worksheet.
1. On the Profits worksheet, select cell range A2:G2. 2. On the Home tab, in the Clipboard group, click the Format Painter.(Hint: The cursor will change to a Paintbrush.) 3. At the bottom of the worksheet, click the tab of the Costs worksheet. 4. On the Costs worksheet, place the Paintbrush cursor on cell A2 and click to apply the formatting.
Simultaneously apply the $ format to columns B through G on the Profits worksheet.
1. On the Profits worksheet, select columns B through G. 2. On the Home tab, in the Number group, click the $ icon.
On the Qtr 1 worksheet, add an option to the table that automatically calculates Total Row.
1. On the Qtr 1 worksheet, click on any cell in the table to select the table. 2. On the Table Design tab, in the Table Style Options group, click the Total Row to enable it.
On the Qtr 1 worksheet, insert a function in Cell A15 below the heading, Attendance Ranking (Jan), that sorts the data in cell range A5:B9 in decending order.
1. On the Qtr 1 worksheet, select Cell A15 2. In the formula field, type =SORT(A5:B9,2,-1) 3. Press Enter
On the Qtr 1 worksheet, in the Maximum row, insert a formula in cell E12 that returns the largest number of visitor attendance for the quarter.
1. On the Qtr 1 worksheet, select cell E12. 2. In the formula field, type =MAX(E5:E9) 3. Click Enter on the keyboard to display the result.(Hint: The result of 351,695 should be displayed.)
On the Qtr 1 worksheet, apply a number format to display the numbers in columns B through E to two decimal places with the US Dollar symbol ($) left-aligned and the decimal points aligned.
1. On the Qtr 1 worksheet, select columns B through E. 2. On the Home tab, in the Number group, click the $ symbol (this will align the symbols).
Apply Layout 2 to the pie chart on the Qtr 2 worksheet.
1. On the Qtr 2 worksheet, click anywhere on the chart to select it. 2. On the Chart Tools Design tab, in the Chart Layouts group, click Quick Layout and select Layout 2.
Without using the New Sheet button, move the pie chart on the Qtr 2 worksheet to its own chart sheet named Qtr 2 Chart.
1. On the Qtr 2 worksheet, click anywhere on the pie chart to select it. 2. On the Chart Design contextual tab, in the Location group, click Move Chart. 3. In the Move Chart pop-up window, select New sheet: and type the name Qtr 2 Chart. 4. Click OK.
On the Qtr 2 worksheet, convert cell range A4:E9 to a table with headers. Apply Orange, Table Style Medium 18.
1. On the Qtr 2 worksheet, select cell range A4:E9 2. On the Home tab, in the Styles group, click Format as Table to open the gallery. 3. Under the Medium section, click Orange, Table Style Medium 18. 4. In the Format As Table pop-up window, do the following: a. Confirm the data field contains =$A$4:$E$9. b. Confirm the My table has headers box is enabled. c. Click OK.
Display the formulas that are in the cells of the Qtr 2 worksheet.
1. On the Qtr 2 worksheet, select the Formulas tab. 2. On the Formulas tab, in the Formula Auditing group, click Show Formulas.
Convert the table on the Recent Releases worksheet to a cell range.
1. On the Recent Releases worksheet, click anywhere on the table to select it. 2. Select the Table Design tab. 3. In the Tools group, click Convert to Range. 4. If prompted, Do you want to convert the table to a normal range? Click Yes.
On the Rental Revenue worksheet, clear the formatting in cell range A3:A7
1. On the Rental Revenue worksheet, select cell range A3:A7 2. On the Home tab, in the Editing group, click Clear and select Clear Formats.
On the Rentals worksheet, configure the heading row in the table so that entries wider than the column wrap to multiple lines.
1. On the Rentals worksheet, select the heading row, (A2:F2). 2. On the Home tab, in the Alignment group, click Wrap Text.
Create the named range, Blazing_Bikes, from all of the blue cells in the Total Cookies Sold column on the Sales by Club worksheet.
1. On the Sales by Club worksheet, hold down the CTRL key on your keyboard and select all the blue cells in the Total Cookies Sold column, which are cell range I17:I20, I24:I25, and I36:I37. 2. With these cells selected, on the Formulas tab, in the Defined Names group, click Name Manager. 3. On the Name Manager pop-up window, click the New... button. 4. In the New Name pop-up window, enter the Name: Blazing_Bikes. 5. Verify that the Refers To: field contains, ='Sales by Club'!$I$17:$I$20,'Sales by Club'!$I$24:$I$25,'Sales by Club'!$I$36:$I$37 If needed you can widen the New Name Window to fully view the Refers To: field 6. Click OK. 7. Click Close.
In cell B4 on the Sales worksheet, insert a function that joins the Description and Style located on the Catalog worksheet. Separate the Description and Style with a hyphen and a space on both sides of the hyphen. (Example: Cross Country - Hardtail).
1. On the Sales worksheet, select cell B4. 2. Click the Formulas tab and click Insert Function. 3. In the Search for a function field, type the keyword, Join. 4. In the Select a function field, select CONCAT. Click OK. 5. In the Function Arguments pop-up window for CONCAT, do the following: a. Click in the Text1 field. b. Click the Catalog worksheet tab. In the Description column, select cell D4. c. Click in the Text2 fieldd. Type a set of quotes containing a space, a hypen, and a space. Example: " - " e. Click in the Text3 field.f. Click the Catalog worksheet tab. In the Style column, select cell C4. 6. Click OK.
Configure Excel to always print cell range A1:F17 on the Sales worksheet.
1. On the Sales worksheet, select cell range A1:F17. 2. Click the Page Layout tab. 3. In the Page Setup group, click Print Area and select Set Print Area.
On the Seedling Sales Chart worksheet, apply Style 5 to the chart.
1. On the Seedling Sales Chart worksheet, click on the chart to select it. 2. Click the Chart Design contextual tab. 3. In the Styles group, click the More dropdown to open the Chart Styles gallery. 4. Select Style 5.
Perform a multiple column sort on the data in the Smartphone table. First sort alphabetically by the Smartphone column (from A to Z), then sort by Storage (from Z to A).
1. On the Smartphones 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:
On the Sold worksheet, format the table so that every other row is shaded. Use a technique that automatically updates the formatting if you insert a new row.
1. On the Sold worksheet, click anywhere within the table to select it. 2. On the Table Design contextual tab, in the Table Styles Options group, click the box Banded Rows to enable it.
On the Sold worksheet, insert a new column before column A.
1. On the Sold worksheet, select column A by clicking the A at the top of the column. 2. Right-click and select Insert.
Configure the Sold worksheet so rows 1 through 3 remain visible as you scroll vertically.
1. On the Sold worksheet, select row 4. (Hint: When freezing frames vertically, select the row below the last row you want to remain visible.) 2. On the View tab, in the Window group, click the Freeze Panes dropdown and select Freeze Panes.
Display all of the formulas on the Summary worksheet.
1. On the Summary worksheet, select the Formulas tab. 2. On the Formulas tab, in the Formula Auditing group, click Show Formulas.
On the Top 20 Videos worksheet, change the width of column C to 9.
1. On the Top 20 Videos worksheet, click column C to select it. 2. On the Home tab, in the Cells group, click Format and select Column Width ... 3. Change the width value to 9. 4. Cliick OK.
On the Top 20 Videos worksheet, apply the cell style 60% - Accent 1 to the top 5 video games located in cell range A4:C8..
1. On the Top 20 Videos worksheet, select cell range A4:C8. 2. On the Home tab, in the Styles group, click the Cell Styles dropdown. 3. In the drop-down menu beneath the Themed Cell Styles section, click 60% - Accent 1.
On the Total Cookie Sales worksheet, in cell B3, use structured references in a formula to calculate the sum of the Chocolate Mint Chip cookies sold.
1. On the Total Cookie Sales worksheet, click on cell B3. 2. In the Formula Bar, type =SUM( 3. Click on the Sales by Club worksheet and hover and then click the top edge of the column heading cell, Chocolate Mint Chip. 4. In the formula bar, the following structured reference should automatically populate: =SUM(Table1[Chocolate Mint Chip]) 5. Press Enter on your keyboard to commit the formula.
On the Vehicles worksheet, in the Code column cell B4, create a formula that returns the two leftmost letters of the vehicle style displayed in cell D4.
1. On the Vehicles worksheet, select cell B4.(Hint: Always put your formula in the cell where you want the result displayed.) 2. In the Formula Bar, type =LEFT, then press the tab key on your keyboard. 3. To the left of the Formula Bar, click fx to open the Function Arguments wizard. 4. In the Function Arguments wizard, configure the following:Text: D4Num_chars: 2 5. Click OK.
Open a second window and view the two instances of the workbook side-by-side.
1. On the View tab, in the Window group, click New Window. 2. In the same Window group, click View Side by Side.
Remove the cell containing the data Projected Sales in the Sales Commissions worksheet.
1. Select cell A13. 2. Right-click and select Delete ... 3. On the Delete pop-up window, accept the default selection and click OK.
In the Grain Production worksheet, apply the Title style to cell A2.
1. Select cell A2. 2. On the Home tab, in the Styles group, click Cell Styles. 3. In the dropdown menu beneath the Titles and Headings section, click Title.
On the Sales worksheet, randomly generate numbers between 1000 and 2000 to populate the Auction ID column from A4:A17.
1. Select cell A4 on the Sales worksheet. 2. Enter the formula =RANDBETWEEN(1000,2000), press the Enter key.
In cell B13, insert a formula using the named range Barley to calculate the total Barley produced.
1. Select cell B13. 2. In the Formula Bar, type =SUM(Barley) 3. Press the Enter key on your keyboard to commit the formula.
On the Carriers & Cages worksheet, link the contents in cell C19 to cell B5 on the Fencing worksheet.
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 Qtr 2 worksheet, in cell F10, insert a column sparkline showing the sales trend from Apr through Jun. Apply Gold, Sparkline Style Accent 2, Darker 25% to the sparkline. Copy the sparkline to the remaining cells in the column, F11:F14.
1. Select cell F10 on the Qtr 2 worksheet. 2. Select the Insert tab. 3. In the Sparklines group, click Column. 4. In the Create Sparklines pop-up window, configure the fields as follows:Data range: B10:D10Location range: $F$10 5. Click OK. 6. On the Sparkline Design tab, in the Style group, click the More dropdown to open the Style gallery. Select Gold, Sparkline Style Accent 2, Darker 25%. 7. In the lower-right corner of the cell, click-drag the Fill Handle through the remaining cells F11 through F14 to create a sparkline for each location.
On the Parts worksheet, insert a sparkline in cell F4 that graphs the trend of sales from Jan through Mar.
1. Select cell F4 on the Parts worksheet. 2. Select the Insert tab. 3. In the Sparklines group, click Line. 4. In the Create Sparklines pop-up window, configure the fields as follows:Data range: B4:D4Location range: $F$4 5. Click OK.
In the Sales Commissions worksheet, insert a formula in cell G4 that references the data in the Total Sales column and the Commission Rate in cell J3 to calculate the values in the Commissioncolumn. Copy the formula into all the cells in the Commission column.
1. Select cell G4. 2. In the Formula Bar, type the following: =F4*J$3 (Hint: The result should be $ 4,599.) 3. Press Enter.
Join cells A1:E1 of the Fencing worksheet. Do not change the alignment of the contents.
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.
Set cells A1:F11 so they will be the only cells that print.
1. Select cell range A1:F11. 2. On the Page Layout tab, in the Page Setup group, click Print Area and select Set Print Area. (Hint: You can check your print area setting under the File tab by selecting Print and viewing it in the Preview pane.)
Join cell I1 to the merged cells A1:H1 of the Sales by Club worksheet. Do not change the alignment of the contents.
1. Select cell range A1:I1 on the Sales by Club worksheet. 2. On the Home tab, in the Alignment group, click the Merge & Center dropdown and select Merge Across.
On the Carriers & Cages worksheet, apply the 3 Flags Icon Set Conditional Formatting to the contents in the Inventory column.
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.
In the Sales Commissions worksheet, use an automatic formatting method to apply Orange Gradient Fill Data Bars to the Total Sales column that changes the cell formatting whenever the cell values change.
1. Select cell range F4:F10 on the Sales Commissions worksheet, 2. On the Home tab, in the Styles group, click Conditional Formatting to open the menu. 3. Select Data Bars and beneath the Gradient Fill section, click Orange Data Bar.
Create one named range from the cell ranges F5:F15 and F18:F26. Name the range, Support.
1. Select cell range F5:F15. 2. While holding down the CTRL key, also select cell range F18:F26. 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 Support. 6. Verify that the Refers To: field contains, ='Price List'!$F$5:$F$15,'Price List'!$F$18:$F$267. Click OK. 8. Click Close.
Display the Costs worksheet in Page Break Preview.. Then insert a page break between row 20 Cracker Jack and row 21 Raspberry Chocolate. Leave the worksheet in Page Break Preview.
1. Select the Costs worksheet and click the View tab. 2. In the Workbook Views group, click Page Break Preview. 3. Scroll down the page and select row 21 Raspberry Chocolate. 4. On the Page Layout tab, in the Page Setup group, click Breaks. 5. Select Insert Page Break.
Save the Rentals worksheet as a CSV (comma delimited) file named Rentals.csv to the GmetrixTemplates folder.
1. Select the File tab. 2. In the left pane, select Save As and browse to the GMetrixTemplates folder. 3. In the Save As pop-up window, configure the following:File name: RentalsSave as type: CSV (comma delimited) (*csv) 4. Click Save
