Download Excel Associate 2019 Practice Exam Questions and Answers and more Exams Biology in PDF only on Docsity!
Excel Associate 2019 Practice
Exam 1 and 2 Training Answers
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. - answer Click the Data tab. In the Get & Transform group, click From Text/CSV. Browse to the GMetrixTemplates folder. Select the Accessories.csv file and click the Import button. In the Accessories.csv pop-up window, ensure that Delimiter is set to Comma. At the bottom of the pop-up window, click the dropdown arrow next to Load and click Load To... In the Import Data pop-up window, under the Where do you want to put the data? section, make sure New worksheet is checked. Click the checkbox for Add this data to the Data Model. Click OK. Clear the formatting in cell range A4:D4 on the Rental Rates worksheet. - answer Select cell range A4:D4 on the Rental Rates worksheet. On the Home tab, in the Editing group, click Clear and select Clear Formats. Rename Table1 to Rates. - answer On the Formulas tab, in the Defined Names group, click Name Manager. In the Name Manager pop-up window, make sure Table1 is selected and click the Edit button. In the Edit Name pop-up window, replace the name Table1 with Rates.
Click OK. Click Close. Change the orientation of the Rental Rates worksheet to landscape.
- answer Select the Rental Rates worksheet. In the Page Layout tab, Page Setup group, select the Orientation dropdown. Select Landscape. Inspect the workbook for compatibility issues. Copy the results to a new worksheet. - answer Click the File tab. On the default Info page, click the Check for Issues button. Select Check Compatibility. At the bottom of the Microsoft Excel - Compatibility Checker pop-up window, click Copy to New Sheet. Join cells A1:E1 of the Fishing worksheet. Do not change the alignment of the contents. - answer Select cell range A1:E1 on the Fishing worksheet. On the Home tab, in the Alignment group, click the Merge & Center dropdown arrow and select Merge Across. On the Tents worksheet, link the contents in cell B13 to cell A4 on the Fishing worksheet. - answer Select cell B13 on the Tents worksheet. On the Insert tab, in the Links group, click Link. In the Link to: section, select Place in This Document. In the Type the cell reference field, type A In the Or select a place in this document field, under Cell Reference, highlight Fishing. Click OK.
Click Replace All. Click Close to exit the pop-up window. On the Costs worksheet, repeat the rows containing the company logo and column headings so they appear on all printed pages. - answer On the Page Layout tab, in the Page Setup group, click Print Titles. 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.) 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. Simultaneously apply the Number format to columns B through E on the Costs worksheet. - answer On the Costs worksheet, select column headings B, C, D, and E. Shift + Click cell E26 to select the data. On the Home tab, in the Number group, click the down arrow in the Number Format window and select Number. 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. - answer If the Profits worksheet is not already displayed, click the Profits worksheet tab to select it. Click the chart to reveal the Chart Tools tabs. 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. - answer If the Profits worksheet is not already displayed, click the Profits worksheet tab to select it. Click the chart to reveal the Chart Tools tabs. On the far left of the Chart Tools Design tab, in the Chart Layouts group, click Add Chart Element, select Legend, and click Top. Create one named range from the cell ranges D5:D15 and D18:D26. Name the range Prices. - answer Select cell range D5:D15. While holding down the CTRL key, also select cell range D18:D26. With both cell ranges selected, on the Formulas tab, in the Defined Names group, click Name Manager. On the Name Manager pop-up window, click the New... button. In the New Name pop-up window, enter the Name Prices. Verify that the Refers To: field contains ='Price List'! $D$5:$D$15,'Price List'!$D$18:$D$26. Click OK. Click Close. 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. - answer Click cell H5. In the Formula Bar, type =SUM(Price_10G,Install_10G,Support_10G). Press Enter on your keyboard to commit the formula. Remove all conditional formatting on the worksheet. - answer On the Home tab, in the Styles group, click Conditional Formatting. Select Clear Rules and click Clear Rules from Entire Sheet. On the Price List worksheet, simultaneously rotate the text Port Size, Band Size, Price, Install, and Support in both tables to Angle Counterclockwise. - answer On the Price List worksheet, select cell
Configure Excel to always print cell range A1:F17 on the Q1 Sales worksheet. - answer On the Q1 Sales worksheet, select cell range A1:F17. Click the Page Layout tab. In the Page Setup group, click Print Area and select Set Print Area. On the Q1 Sales worksheet, insert a function in cell B19 that calculates all sales from the Total column. - answer On the Q1 Sales worksheet, select cell B19. In the formula field, type =SUM(F4:F17). Click Enter on the keyboard to display the result.(Hint: The result displayed should be $251,422.00.) In cell B4 on the Q1 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 (e.g., Cross Country - Hardtail). - answer On the Q Sales worksheet, select cell B4. Click the Formulas tab and click Insert Function. In the Search for a function field, type the keyword CONCAT. In the Select a function field, select CONCAT. Click OK. In the Function Arguments pop-up window for CONCAT, do the following:Click in the Text1 field.Click the Catalog worksheet tab. In the Description column, select cell D4. (Hint: Cross Country should display to the right of the field.)Click in the Text2 field.Type a set of quotes containing a space, a hyphen, and a space (e.g., " - ").Click in the Text3 field.Click the Catalog worksheet tab. In the Style column, select cell C4. (Hint: Hardtail should display to the right of the field.) Click OK.(Hint: The final value displayed in cell B4 of the Q1 Sales worksheet should be Cross Country - Hardtail.)
On the Qtr 1 worksheet, add a row to the table that automatically calculates Total Entries. - answer On the Qtr 1 worksheet, click on any cell in the table to select the table. On the Table Tools Design tab, in the Table Style Options group, click the box next to Total Row to enable it. On the Qtr 1 worksheet, in the Maximum row, insert a formula in column E that returns the largest number of recipe entries for the quarter - answer On the Qtr 1 worksheet, select the cell in column E in the Maximum row. In the Formulas tab, Function Library group, click the More Functions down arrow, select Statistical then select MAX. In the Function Arguments window, Number1 field, enter E3:E Click OK. (Hint: The result of 80 should be displayed.) On the Qtr 1 worksheet, use the data contained only in the Entries and Total columns to create a 2D Pie chart. Position the new chart below the table. - answer On the Qtr 1 worksheet, select cell range A2:A10. While holding down the Control key, select cell range E2:E10. Click the Insert tab. In the Charts group, click the Insert Pie or Doughnut Chart icon and select 2D Pie. Click and drag the new chart and position it to be below the table. On the Qtr 2 worksheet, convert cell range A2:E10 to a table with headers. Apply Red, Table Style Light 14. - answer On the Qtr 2 worksheet, select cell range A2:E10. On the Home tab, in the Styles group, click Format as Table to open the gallery. Under the Light section, click Red, Table Style Light 14.
On the Parts worksheet, insert a Line Sparkline in cell F4 that graphs the trend of sales from Jan through Mar. - answer Select cell F4 on the Parts worksheet. Select the Insert tab. In the Sparklines group, click Line. In the Create Sparklines pop-up window, configure the fields as follows:Data range: B4:D4Location range: $F$ Click OK. Navigate to the named cell range AppHeading2 and delete only the contents, leaving the empty cells. - answer Above the worksheet to the left of the formula bar, click the Name Box down arrow. Select AppHeading2.(Hint: Alternatively, on the Home tab, in the Editing group, click Find & Select, and select Go To... Select AppHeading2, then click OK.) Cell range E1:F3 should be selected. Right-click on the selected cells and select Clear Contents. Click OK. On the Downloads worksheet, adjust the height of row 27 to 78. - answer On the Downloads worksheet, click row 27 to select it. On the Home tab, in the Cells group, click Format and select Row Height. In the Row Height pop-up box, type 78. Click OK. Apply the cell style Light Blue, 40% - Accent 2 to cell A27. - answer Select cell A27. On the Home tab, in the Styles group, click the More dropdown (this could also be the Cell Styles dropdown depending on your Excel window width). In the Themed Cell Styles section, click Light Blue, 40% - Accent 2.
Create a table with headers from cell range A3:B24 by applying the Blue, Table Style Light 10. - answer On the Downloads worksheet, click anywhere within the cell range A3:B24. On the Home tab, in the Styles group, click Format as Table. In the section, select Blue, Table Style Light 10. In the Format As Table pop-up window, do the following:Confirm the data field contains =$A$3:$B$24.Confirm the My table has headers box is enabled. Click OK. Insert a Footer that displays today's date on the right, and then return to Normal view. - answer On the Insert tab, in the Text group, click Header & Footer. In the Header & Footer Design tab, in the Navigation group, click Go to Footer. Click the rightmost cell in the Footer. On the Header & Footer Tools Design tab, in the Header & Footer Elements group, click Current Date. Click outside of the Footer cells. On the View tab, in the Workbook Views group, click Normal to close the Page Layout View (header and footer view). Import PetFoods.txt located in the GMetrixTemplates folder as a table on a new worksheet. - answer 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.) In the Import Data pop-up window, browse to the GMetrixTemplates folder. Select the PetFoods.txt file and click the Import button. In the PetFoods.txt pop-up window, configure the following:File Origin: accept the default - 1252: Western European
On the Feed Inventory worksheet, apply Style 11 to the chart. - answer On the Feed Inventory worksheet, click on the chart to select it. Click the Chart Tools Design contextual tab. In the Styles group, click the More down arrow to open the Chart Styles gallery. Select Style 11. Display the Costs worksheet in the Page Layout view. Then insert a page break between row 20 Cracker Jacker and row 21 Raspberry Chocolate. - answer Select the View tab. In the Workbook Views group, click Page Layout. Scroll down the page and select row 21 Raspberry Chocolate. On the Page Layout tab, in the Page Setup group, click Breaks. Select Insert Page Break.(Hint: The first page should now end with the flavor Cracker Jacker, and the next page should begin with the flavor Raspberry Chocolate.) On the View tab, in the Workbook Views group, click Normal to return to the normal view. Use a built-in 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. - answer On the Profits worksheet, select cell range A2:G2. On the Home tab, in the Clipboard group, click the Format Painter. (Hint: The cursor will change to a paintbrush.) At the bottom of the worksheet, click the tab of the Costs worksheet. On the Costs worksheet, place the paintbrush cursor on cell A2 and click to apply the formatting. On the Profits worksheet, enter a formula in cell A29 that uses an Excel function to return the average costs of the cookie dough
flavors based on values in the Cost column. - answer On the Profits worksheet, select cell A29.(Hint: Always put your formula in the cell where you want the result to be displayed.) In the Formula Bar, type =AVERAGE, then press the Tab key on your keyboard. To the left of the Formula Bar, click fx to open the Function Arguments wizard. In the Function Arguments wizard, configure the following:Number1: B4:B Click OK.(Hint: The formula in cell A29 should be =AVERAGE(B4:B26). The result displayed should be 1.73.) On the Profits worksheet, include the Total Profit data on the Flavor Expenses, Income, and Profit chart. - answer On the Profits worksheet, click in the center of the chart to select it. Click the Chart Tools Design contextual tab. In the Data group, click Select Data. In the Select Data Source pop-up window, in the left window pane, click the box Total Profit to enable it. Click OK.(Hint: Total Profit should now be added to the legend representing the third column on the chart.) Filter the Profits worksheet to display only flavors with a Cost that is Above Average. - answer On the Profits worksheet, click the autofilter down arrow on the Cost heading. Hover over Number Filters and click Above Average. Add Open to the Quick Access Toolbar. - answer Above the ribbon, click the Customize Quick Access Toolbar down arrow. (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.) Click Open.
bottom pane. - answer On the Sold worksheet, select row 7.(Hint: When splitting views, select the row below the last row you want to appear in the top pane.) On the View tab, in the Window group, click Split. Configure the Vehicles worksheet so rows 1 through 3 remain visible as you scroll vertically. - answer On the Vehicles worksheet, select row four (Hint: When freezing frames vertically, select the row below the last row you want to remain visible.) On the View tab, in the Window group, click the Freeze Panes down arrow and select Freeze Panes. On the Sold worksheet, format the table so that every other row is shaded similar to the table on the Vehicles worksheet. Use a technique that automatically updates the formatting if you insert a new row. - answer On the Sold worksheet, click anywhere within the table to select it. On the Table Tools Design contextual tab, in the Table Styles Options group, click the Banded Rows box to enable it. On the Vehicles worksheet, insert a function into cell B4 of the Code column that extracts the two leftmost letters of the vehicle style displayed in cell D4. - answer 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: 25. Click OK. (Hint: The result should be Se.) 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). - answer Select cell F on the Qtr 2 worksheet. Select the Insert tab.
In the Sparklines group, click Column. In the Create Sparklines pop-up window, configure the fields as follows:Data range: B10:E10Location range: $F$ Click OK. On the Sparkline Tools Design tab, in the Style group, click the More down arrow to open the Style gallery. Select Gold, Sparkline Style Accent 2, Darker 25%. In the lower-right corner of the cell, click-drag the Fill Handle from cell F11 through cell F14 to create a sparkline for each location. Using the Move Chart feature, move the pie chart on the Qtr 2 worksheet to its own chart sheet named Qtr 2 Chart. - answer On the Qtr 2 worksheet, click anywhere on the pie chart to select it. On the Chart Tools Design contextual tab, in the Location group, click Move Chart. In the Move Chart pop-up window, select New sheet: and type the name Qtr 2 Chart. Click OK. 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. - answer 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).(Hint: Alternatively, click the dialog-box launcher in the lower-right corner of the Number group. On the Number tab, in the Category pane, select Accounting and configure Decimal places: 2 and Symbol: $. Then click OK.) In the Qtr 1 worksheet, change the configuration of the Qtr 1 Location Revenue chart so that it displays the months on the x-axis and the revenue on the y-axis. - answer Click the Qtr 1 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.
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: A45. Click OK.(Hint: The result displayed should be EMILIO.) On the Average Call Time worksheet, create a 3-D Clustered Column chart that shows only the Call Time for Friday by each Salesperson. Position the new chart to the right of the table, and change the colors of the chart to Colorful Palette 4. - answer On the Average Call Time worksheet, click the Insert tab.2. In the Charts group, click the Insert Column Chart icon and select 3-D Clustered Column.3. In the Chart Tools Design tab, in the Data group, click Select Data.4. Enter the Chart Data Range: =Parts[Fri] and click the arrow at end of field twice.5. Under Horizontal (Category) Axis Labels, click Edit. Then select cells A4 through A16 and click OK twice.6. On the Chart Tools Design tab, in the Chart Styles group, click Change Colors and select Colorful Palette 4.7. Move the chart to the right side of the table.
