Excel Mod 10 MindTap

Create a forecast sheet. —> Create a forecast sheet based on range A1:B6.

1. Click and drag to select range A1:B6. 2. Click the Data tab on the ribbon. 3. In the Forecast group (dropdown), click the Forecast Sheet button. 4. Click the Create button.

Create a 3D (Power) Map. —> Create a 3D (Power) Map for the CurrentClients table. In the Layer pane of the 3D Maps window, first place the Zip Code field in the Location box, the 2020 Sales field in the Height box, and the Branch field in the Category box. Zoom in once on the map, then Tilt up one setting. Resize the Layer 1 box to just fit its contents, then close the Tour Editor pane and Field List box. Close the Power Map window when done.

1. Click cell A3 (top left category cell of table with arrow). 2. Click the Insert tab on the ribbon. 3. In the Tours group, click the 3D Map button (symbol button above the dropdown). 4. In the Field List dialog box, click and drag the Zip Code field from the CurrentClients table to the Location box in the Layer pane. 5. Click and drag the 2020 Sales field from the CurrentClients table to the Height box in the Layer pane. 6. Click and drag the Branch field from the CurrentClients table to the Category box in the Layer pane. 7. At the lower-left corner of the map, click the Zoom in button. 8. Click the Tilt up button. 9. Click and drag the corner of the Layer 1 box to resize it and get rid of the empty space in the box. 10. In the View group (dropdown) on the Home tab, click the Tour Editor button. 11. In the View group (dropdown), click the the Field List button (closes the dialog box). 12. Click the File tab on the ribbon. 13. Click Close (3D map window closes, returns to worksheet).

Add a field to the ROWS area of the PivotTable Fields task pane. —> Add the Region field to the Rows area of the PivotTable Fields pane.

1. Click cell A3 (top left category cell of table with arrow). 2. In the PivotTable Fields Pane, in the Choose fields to add to report area, click the Region check box to select it (automatically goes to Rows area).

Import an Access table into a workbook. —> Import the Clients table from the Access file Rockland.accdb starting in cell A3 of the current worksheet.

1. Click cell A3. 2. Click the Data tab on the ribbon. 3. In the Get & Transform Data group, click the Get Data button (dropdown). 4. On the Get Data menu, click From Database. 5. On the submenu, click From Microsoft Access Database. 6. In the Import Data dialog box, click Rockland.accdb. 7. Click Import. 8. In the Navigator window, click Clients. 9. In the lower-right corner of the Navigator window, click the Load arrow. 10. On the Load menu, click Load To. 11. In the Import Data dialog box, click the Existing worksheet option (don't change anything else, accept the preset defaults). 12. Click OK.

Move fields between areas of the PivotTable Fields task pane. —> In the PivotTable Fields pane, move the Project Category field from the Columns area to the Rows area, placing it above the Branch field. Move the System Type field from the Columns area to the Rows area, placing it below the Branch field.

1. Click cell A4 (blank category cell below the top left title cell of the table). 2. In the PivotTable Fields pane, click and drag the Project Category field from the Columns area to the Rows area (*place it above the Branch field in the Rows area). 3. In the PivotTable Fields pane, click and drag the System Type field from the Columns area to the Rows area (*place it below the Branch field in the Rows area).

Insert a slicer into a PivotTable. —> Add a slicer to the Regional Sales PivotTable to filter the data by Region.

1. Click cell A4 (top left category cell of table with arrow). 2. Click the PivotTable Tools Analyze tab (green text) on the ribbon. 3. In the Filter group (dropdown), click the Insert Slicer button. 4. In the Insert Slicers dialog box, click the Region check box to select it. 5. Click OK.

Insert a PivotTable. —> Use the PivotTable command on the Insert tab to insert a PivotTable in a new worksheet using the BranchSales table as the source range.

1. Click cell A4 (top left category cell of table). 2. Click the Insert tab on the ribbon. 3. In the Tables group (dropdown), click the PivotTable button. 4. In the Create PivotTable dialog box, click OK (to accept the default settings).

Add a field to the VALUES area of the PivotTable Fields task pane. —> In the PivotTable Fields pane, add the February and March fields to the PivotTable, placing the fields in the VALUES area of the pane.

1. Click cell A5 (any cell within the table). 2. In the PivotTable Fields pane, in the Choose fields to add to report area, click the February check box to select it (automatically goes to Values area). 3. Click the March check box to select it (automatically goes to Values area).

Change the number format of a PivotTable VALUE field. —> In the PivotTable, change the number format of the Sum of January field to the Accounting number format with "0" (zero) decimal places.

1. Click cell A5 (any cell within the table). 2. In the PivotTable Fields pane, in the Values area, click the Sum of January field button (arrow). 3. On the menu, click [Value] Field Settings. 4. At the bottom of the dialog box, click the Number Format button. 5. In the Category list, click Accounting. 6. In the Decimal places box, replace the current value with "0" (zero). 7. Click OK. 8. Click OK.

Add a timeline slicer to a PivotTable. —> Add a timeline slicer to the PivotTable based on the Date field in the 2020 Client Contracts table.

1. Click cell B4 (any category cell of table). 2. Click the PivotTable Tools Analyze tab (green text). 3. In the Filter group (dropdown), click the Insert Timeline button. 4. In the Insert Timelines dialog box, click the Date check box to select it. 5. Click OK.

Drill down into a PivotTable. —> Starting in cell B5 in the PivotTable, Drill Down one level to display data for System Types, then expand the Limited label to show the data outline. Drill Down another level to display data for Project Teams, then Drill Up two levels.

1. Click cell B5 (first cell under top left category cell — says "+ Installation"). 2. Click the PivotTable Tools Analyze tab. 3. In the Active Field group, click the Drill Down button. (cells change to display System Types data; B5 now says "+ Limited".) 4. To the left of the Limited label, click the Expand (+) button (shows the data outline). 5. With the Limited label still selected, click the Drill Down button again. (cells change again to display Project Teams data.) 6. In the Active Field group, click the Drill Up button twice ("+ Installation" is back in cell B5.)

Change the custom name of a PivotTable VALUE field. —> In the PivotTable, change the custom name of the VALUE field Sum of Q1 to "1st Quarter Sum" as the new field name.

1. Click cell E5 (the Sum of Q1 category cell). 2. Click the PivotTable Tools Analyze tab (green text). 3. In the Active Field group, click the Field Settings button. 4. In the Custom Name text box, rename the field as "1st Quarter Sum". 5. Click OK.

Insert into a chart a trendline that forecasts future trends. —> Insert a Linear forecast trend line in the Column chart that forecasts forward by "2.0" future periods.

1. Click the Column chart. 2. Click the Chart Tools Design tab on the ribbon (green text). 3. In the Chart Layouts section, click the Add Chart Element button (dropdown). 4. On the menu, point to (hover over) Trendline. 5. Click More Trendline Options. 6. In the Format Trendline pane under the Forecast section, in the Forward box replace the current setting with "2.0". 7. In the pane, click the Close button (top right X).

Create a new 3D map scene in a tour. —> Create a new 3D map scene in the Tour 1 map. In the 3D Maps window, expand the Deerfield table fields, then add the Zip field to the Location section of the Layer task pane, and the 2020 Sales field to the Height section. Add Map Labels to the scene, adjust the view using the Zoom in and Tilt down buttons, then close the 3D Maps window when done.

1. Click the Insert tab on the ribbon. 2. In the Tours group, click the 3D Map button (symbol button above the dropdown). 3. In the Launch 3D Maps dialog box, click the Tour 1 icon. 4. In the Scene group on the home tab, click the New Scene button (symbol button above the dropdown). 5. In the Field List box, click the arrow to the left of the Deerfield table. 6. Click and drag the Zip field from the Deerfield table to the Location section in the Layer pane. 7. Click and drag the 2020 Sales field to the Height section in the Layer pane. 8. In the Map group on the home tab, click the Map Labels button. 9. In the lower-right area of the map, click the Zoom in button. 10. Click the Tilt down button. 11. Click the File tab on the ribbon. 12. Click Close (3D map window closes, returns to worksheet).

Transform a query by grouping values using the Query Editor. —> Transform the BranchSales Query by grouping rows by "Month". Use Count Rows as the grouping operation and Count as the New column name. Close & Load the transformed query.

1. Click the Transform tab on the ribbon. 2. In the Table group (dropdown), click the Group By button. 3. In the dialog box, click the Group by arrow. 4. Click Month. 5. Click OK 6. Click the Home tab. 7. In the Close group, click the Close & Load button (the button above the dropdown).

Create a new column using the Date format in a query using the Power Query Editor. —> Using the Power Query Editor, add a new column based on the "Order Date" column, using the "Date" format of "Month", and then "End of Month". Rename the column by typing "Month" as the new column name, then Close & Load the query.

1. In the Power Query Editor, click the Order Date column (the text, not the arrow). 2. Click the Add Column tab on the ribbon. 3. In the From Date & Time group, click the Date button (dropdown). 4. On the menu, point to (hover over) Month. 5. Click End of Month. 6. Right-click the End of Month column heading. 7. On the menu, click Rename. 8. Type "Month" as the new column name, and press ENTER. 9. Click the Home tab. 10. In the Close group, click the Close & Load button (the button above the dropdown).

Load Query Data into an Excel table. —> Load the query data into an Excel table at cell "A3".

1. In the Power Query Editor, on the Home tab, click the Close & Load arrow. 2. On the menu, click the Close & Load To option. 3. In the Import Data dialog box, click the Table option button to select it. 4. Click the Existing worksheet option button to select it. 5. In the Existing worksheet box, type "A3". 6. Click OK.

Delete columns from a query using the Power Query Editor. —> Using the Query Editor, delete the "Manager ID" and "Manager ID + Contact Last" columns from the "Client" query.

1. In the Queries & Connections pane, point to (hover over) the Client query. 2. In the query preview box, click EDIT (at the bottom). 3. In the Query Editor, click the Manager ID column heading (the text, not the arrow). 4. Press & hold CTRL, then click the Manager ID + Contact Last column heading (this selects both of the columns). 5. On the Home tab, in the Manage Columns group (dropdown), click the Remove Columns button (the "x" button above the dropdown arrow). 6. On the Home tab, click the Close & Load button (the button above the dropdown arrow).

Filter data in a PivotTable using a Slicer —> Use the Region slicer to filter the PivotTable to display only data for the Southern and Western regions.

1. In the Region slicer, click the Southern button. 2. Press and hold the CTRL key, click the Western button, then release the CTRL key (to select both regions).

Change the scene effect in a tour. —> Change the effect used in the 2020 Sales scene to the Fly Over effect.

1. In the Tour Editor pane (on the lefthand panel), click the 2020 Sales scene. 2. In the Scene group on the Home tab, click the Scene Options button. 3. In the dialog box, click the Effect dropdown button. 4. On the menu, click Fly Over.

Change the scene name used in a tour. —> Change the name of Scene 2 by entering "2020 Sales" as the new scene name.

1. In the Tour Editor pane (on the lefthand panel), click the Scene 2 scene. 2. In the Scene group on the Home tab, click the Scene Options button. 3. Type "2020 Sales" in the Scene Name text box.

Use the Outline feature to collapse or expand data views. —> Use a row level button to collapse worksheet rows to display only subtotals and the grand total. Then, use a show details button to display individual records for the Eastern Region subcategory of data (the top show details (+) symbol).

1. In the fully expanded outline area (left hand side of row numbers), click the row level 2 symbol (at the top). (*The worksheet rows collapse to display only the subtotals and the grand total.) 2. In outline level 2, click the top show details symbol (+). (*The worksheet rows expand to display the individual records for the Eastern Region subtotal data.)

Filter a PivotChart. —> Filter the PivotChart on the Branch field to display only records with a value of New York.

1. On the PivotChart, click the Branch arrow (category arrow) at the lower-left corner of the chart. 2. On the Branch AutoFilter menu, deselect the (Select All) check box. 3. Click the New York check box to select it. 4. Click OK.

Set the operation used in a query column using the Query Editor. —> Using the Query Editor, in the Group By dialog box, enter "Monthly Sales" as the New column name. Specify Sum as the Operation and 2020 Sales as the Column that contains the values to be summed. Clos & Load the query.

1. Within the Group By dialog box, in the New column name box, type "Monthly Sales". 2. Click the Operation arrow, and click Sum. 3. Click the Column arrow, and click 2020 Sales. 4. Click OK. 5. Click the Home tab. 6. In the Close group, click the Close & Load button (the button above the dropdown).

Change the scene duration in a tour. —> Change the duration of the Current Clients scene to "6" seconds.

1. Within the Tour Editor pane, with the Current Clients scene selected (on the lefthand panel): On the Home tab in the Scene group, click the Scene Options button. 2. In the dialog box, type "6" in the Scene duration (sec) box. 3. Click the Close button.