Description
Maximizing Pivot Table With Excel
Course Duration: 2 Days
Pre- Requisites
Must possess the following basic Microsoft Windows knowledge is essential with the following pre requisites:
-
Able to handle the mouse
-
Able to switch between task applications
-
Able to ''Create'', ''Save'', ''Open'', and ''Print'' spreadsheet
-
Able to use ''Undo'' or ''Redo'' function
-
Able to print a spreadsheet with headers and footers added
-
Able to identify Excel's cell addresses
-
Able to create simple formulas (additional, subtraction, multiplication, and division)
-
Able to create simple functions (Sum, Average, Count, Max & Min)
Benefits
Upon completion of this programme, the participants will be able to:
-
Have a better comprehension in using COUNTIFS, SUMIFS functions.
-
Use IF Function to make decision.
-
Use Nested IF Function to make multiple decisions.
-
Calculate duration between dates using DATEDIF Function.
-
Extracting data using VLOOKUP Function.
-
Visualizing Data with PivotTable and PivotChart.
-
Perform What IF Analysis using Scenario Manager.
Key Contents
Unit 1: Evaluate Data Using Statistical and Math Functions
-
Using COUNTIFS Function
-
Using SUMIFS Function
Unit 2: How to Ensure Data Consistency?
-
Using TEXT Function
-
Using UPPER Function
-
Using LOWER Function
-
Using PROPER Function
Unit 3: Performing Date Calculation
-
Text to Columns Wizard to correct wrong date format
-
Using DATE Function
-
Using the EDATE Function
-
Using DATEIF Function
Unit 4: Making Decision with Logical Functions
-
Using IFERROR Function
-
Using IF Function
-
Using NESTED IF statements
Unit 5: Handling Text Data
-
Segregate Data Using the Text to Columns Wizard
-
Using TRIM Function
-
Using SUBSTITUTE Function
-
Using CONCATENATE Function
Unit 6: Searching Relevant Data ith LOOKUP Functions
-
Using VLOOKUP Function
-
How to Find an Exact Match with VLOOKUP
-
Finding an Approximate Match with VLOOKUP
Unit 7: Create Interactive Report with Pivot Table
-
What is Pivot table?
-
Start with Questions End with Structure
-
Common Pivot Table Practice
-
Creating Pivot Table
-
Formatting a PivotTable
-
Refreshing a PivotTable
-
Grouping PivotTable Data
-
Show Values As Functionality of a PivotTable
-
Creating PivotChart
-
Using Slicers to Manipulate PivotTables (Excel 2010 onward)
-
Using Timelines to Filter Dates (Excel 2013 onward)
Unit 8: Exploring Various Possibilities with Scenarios
-
What is Scenario?
-
Creating a Scenario
-
Saving Multiple Scenarios
More detail about Iconic Training Solutions Sdn Bhd