Maximizing Pivot Table With Excel
Maximizing Pivot Table With Excel
Course Duration: 2 DaysPre- 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
Inquiry - Maximizing Pivot Table With Excel