excel module 10: training

Create a new column using the date format in a query using the power query editor

"Add a new column based on the order date column , using the date format and then end of month. Rename by typing month, then close and load" Click order date column header Add column tab Date Month arrow, end of month Right click end of month heading Rename Type Month and then enter Home tab Click close and load button

Change the custom name of a pivot table value field

"Change the custom name of the value field sum of q1 to ........ as new field name" Click e5 Analyze tab Field settings Type 1st Quarter Sum in box Ok

Delete columns from a query using the power query editor

"Delete the manager id and manager id + contact list columns from the client query" click client query Click edit Click manager id heading Hold control and click manager id + co... Click remove columns button Click close and load button

Import an access table into a worksheet

"Import the clients table from access file starting in a3" Click a3 Data tab Get data From database, from Microsoft access base Click rockland acb Import Select Clients Load arrow, load to Check existing worksheet Ok

Load query data into an excel table

"Load query data to cell A3" click close and load Close and load to Click table Click existing worksheet Type A3 into box Ok

Transform a query by grouping values using the query editor

"Transform the query by month. Use count rows as the grouping operation as the new column name. Close and load new" Transform tab Group by Group by arrow Month, then ok Home Close and load

Filter a pivot chart

Branch arrow on chart Deselect select all Check New York Ok

Use the outline feature to collapse or expand data views

Click 2 in outline levels Click top + sign

Create a 3d power map

Click a3 Insert Click 3d map button Drag zip code to location box Drag 2020 sales to height box Drag branch to category box Use + to zoom in map Click tilt up, top button on direction Use corner of box to shrink box size to see map Tour editor Field list File, close

Move fields between areas of the pivot table fields task pane

Click a4 Drag project cat box from column to above branch in rows Drag system type to under branch in rows

Insert a pivot table

Click a4 Insert tab Pivot table Ok

Add a field to the values area of the pivot table fields task pane

Click a5, New York Check February box Check march

Change the number format of a pivot table value field

Click a5, New York Click sum of Jan Field settings Number format Accounting 0 decimal places Ok

Drill down into a pivot table

Click b5 Analyze tab Drill down Press expand button (+) next to limited Drill down Drill up Drill upf

Add a timeline slicer to a pivot table

Click cell b4 Analyze tab Insert timeline Check date Ok

Insert into a chart a trend line that forecasts future trends

Click chart Design tab Add chart element Trendline, more option Forecast, type 2.0

Insert a slicer into a pivot table

Click row labels a4 Analyze tab Insert slicer Check region Ok

Add a field to the rows area of the pivot table fields task pane

Click row labels, a3 Check region box

Change the scene effect in a tour

Click scene 2 Scene options Effect arrow Fly over

Change the scene name used in a tour

Click scene 2 Scene options Type 2020 Sales in scene name box

Filter data in a pivot table using a slicer

Click southern Hold control and click western

Set the operation used in a query column using the query editor

In group by box Type Monthly Sales Operations arrow Sum Column arrow, 2020 sales Ok Home Close and load

Create a new 3d map scene in a tour

Insert 3d map button Click tour 1 Click new scene button, picture with plus sign in corner Click arrow to expand Deerfield Drag zip to location Drag 2020 sales to height Click Map labels Click zoom button Tilt down, arrow down on map File, close

Change the scene duration in a tour

Scene options Type 6 in duration box Close

Create a forecast sheet

Select a1 to b6 Data tab Forecast sheet Create button