Module 7 Training
EX9727 Create a formula using the OR function.
Click cell Formulas tab Function Library, Insert Function click arrow Logical click OR function OK type Logicals in textbox Click OK
EX10860 Create a formula using the IFS function.
Click cell Formulas tab, Function Library, Logical click IFS type in textbox G5>500000 "M1" G5>250000 "M2" TRUE "M3" use quotation marks in Value_is_true1 and 2 and
EX9705 Create a formula using the HLOOKUP function.
Click cell Formulas tab, Function library Insert function click arrow Lookup & Reference HLOOKUP click OK type text
EX9726 Create a formula using the AVERAGEIF function.
Click cell Formulas tab, Function library, more functions, statistical, AVERAGEIF type range, tab type criteria, tab type average range click ok
EX9386 Add a calculated field to a PivotTable.
Click cell Pivot Table appears Analyze tab caluculations, fields items & set calculated field type Name Formula: '=January+February+March Click OK
EX9394 Add a field to the VALUES area of the PivotTable Fields task pane.
Click cell click February and March checkbox
EX10824 Create a formula using the MATCH function.
Click cell formulas tab function library, lookup & reference MATCH type RELICO type cells type 0 click OK
EX9725 Create a formula using the AND function.
Click cell formulas tab, function library, logical click AND type in Logical1 textbox TAB type in Logical2 textbox Click OK
EX10848 Create a formula using the MAXIFS function.
Click cell type =MAXIFS(E4:E21,C4:C21,"Construction",D4:D21,2) ENTER E4:21 = Salary C4:C21 =.Position in cell H3 for some reason it doesnt specify what cell in practice
EX9818 Create a formula using the RATE function.
Click cell type '=RATE(c6,c7,c3,c8) ENTER
EX10851 Create a formula using the AVERAGEIFS function.
Click cell type =AVERAGEIFS( click and drag e5:e18 then type comma click and drag c5:c18 type , "Blue", click and drag d5:d18 type , "FIRE")
EX9979 Create a formula using the INDEX function.
Click cell j7 type =INDEX(A3:G17,15,3) ENTER row 15, column 3
EX9385 Filter data in a PivotTable using a Slicer.
click Souther button CTRL and click Western
EX10835 Consolidate data from multiple ranges.
click and drag cells Data tab, Data tools consolidate click New York sheet tab and select cells click Add click Dallas sheet tab and click add do so for other sheet tabs click OK
EX9225 Insert a Line chart.
click and drag cells insert tab insert line or area line button click line chart option
EX9925 Create custom number format for a range of cells.
click and drag cells B3:D3 'Cells' under Home tab, Format, Format Cells... click Custom under category select type box and type yyyy click ok
EX10127 Drill down into a PivotTable.
click cell Analyze tab Drill Down click the (+) button next to the cell click drill down again click drill up button click drill up again
EX9390 Apply a style to a PivotTable.
click cell Design tab PivotTable Styles, more click option
EX9630 Add a field to the ROWS area of the PivotTable Fields task pane.
click cell click 'region' checkbox
EX9389 Add a field to the COLUMNS area of the PivotTable Fields task pane.
click cell click Analyze tab Show, Field List click and drag 'Branch' to columns
EX9982 Display and hide the PivotTable Field list.
click cell click Analyze tab Show, field list Show, field list again
EX9399 Add a field to the FILTERS area of the PivotTable Fields pane.
click cell click and drag 'Region' to Filters area
EX9698 Create a formula using a nested IF function.
click cell click insert function button by formula bar click arrow logical IF click OK type D5=3 in Logical_test box TAB type E5*.1 in Value if true box type IF(D5=2, E5*.05,0) OK
EX9640 Create a formula using the SUMIF function.
click cell type =SUMIF(D5:D22,3,F5:F22)
EX10833 Create a nested formula using the INDEX and MATCH functions.
click cell type "=INDEX(A5:J18,MATCH("M-07",C5:C18,0),6) A5:J18 is yhe whole table M-07 is on instructions C5:C18 is Manager ID (next to M-07 on instructions)
EX9639 Create a formula using the COUNTIF function.
click cell type =COUNTIF(G5:G18,"TX") ENTER
EX9392 Move fields between areas of the PivotTable Fields task pane.
click cell (PivotTable Fields pane) click and drag 'Project Category' from Columns to Rows category aboveg branch click and drag 'System type' as well below branch
EX9393 Insert a PivotTable.
click cell (PivotTable) insert tab, Tables, PivotTable click OK
EX9396 Change the number format of a PivotTable VALUE field.
click cell (inside table) click Sum of January under Values click Field Settings click Number Format click Accounting type 0 in decimal places click OK click OK
EX10125 Add a timeline slicer to a PivotTable.
click cell (pivottable) Analyze tab Filter, Insert Timeline select Date checkbox OK
EX9387 Insert a slicer into a PivotTable.
click cell A4 (pivottable) Analyze tab Filter, insert slicer click Region click OK
EX9397 Change the custom name of a PivotTable VALUE field.
click cell E5 (PivotTable field Sum of Q1) Analyze tab click Field Settings, under Active field Rename Custom Name Click OK
EX10823 Create a formula using the OFFSET function.
click cell F1 formulas tab function library lookup & reference OFFSET A5 2 3 click OK
EX10849 Create a formula using the MINIFS function.
click cell H3 type =MINIFS(E4:E21,C4:C21,"Construction",D4:D21,2)
EX9398 Insert a Recommended PivotTable.
click cell inside pivot table insert tab, tables, recommended, Blank PivotTable
EX9391 Change the layout of a PivotTable.
click cell inside the pivottable Design tab Report Layout, Show in Outline Form
EX10830 Add a data series to a chart.
click chart Design tab Data, Select data click and drag A5:D8 click OK
EX10117 Edit the number format of data labels in a chart.
click chart click chart elements button (+) point to data label and click the arrow click More Options click Label Options arrow (section collapses) click Number arrow click category arrow currency type 0 in decimal places ENTER close button
EX9400 Filter a PivotTable using a Report filter.
click filter arrow in a privottable click 'southern' click OK
EX10075 Apply a PivotChart Layout.
click pivotchart design tab quick layout, select option
EX9865 Change the chart type of a PivotChart.
click pivotchart design tab type, change chart type click pie click 3-d pie click OK
EX9844 Move a slicer on a worksheet.
click slicer's title bar move to desired cell
EX9383 Refresh PivotTable data.
click worksheet tab click cell A3 (pivottable) Analyze tab, data, refresh
EX9357 Filter a table based on a single field.
on city column, click the arrow click 'select all' to deselect all click chicago checkbox click ok
EX9812 Filter a PivotChart.
on pivot chart, click 'Branch' arrow click 'select all' to deselect select New York checkbox click OK
EX9395 Change the summary function used by a PivotTable VALUE field.
right click cell under pivot table point to Summarize Values by click Average
EX10288 Add alt text to an object.
right click chart Edit Alt Text... type close
