DATA MASSAGING TOOLS AND TECHNIQUES
- Merge and consolidate data
- Data validation using numbers, lists, dates, and text length
- The magical select, shift, select
- The surgeon functions: left, right, mid, concatenate, value
- To name or not to name: Naming, editing, and managing cells and ranges
- Sum and brothers: Subtotal, sumif, sumifs, sumproduct, Count and sisters: count, countif, countifs
- Finding things around: Looking-up data, texts, and values using vlookup
- The incredible table tools technique
- Slicing dates into day names, weeks, week numbers, month names, years and quarters
- Text to columns and dynamic trimming using find, substitute, trim, len, and replace
- Make me look professional: Text change functions
PIVOT TABLES: THE ONE AND ONLY!
- The 19 Rules
- Design Rules
- Designing Pivot Tables
- Number formatting techniques
- Designing report layout
- Sorting in ascending, descending and more sort options
- Filtering labels and values
- Expanding and collapsing reports
- Analytics Rules
- Summarize values by sum, average, minimum, maximum, count
- Show values as % of total and % of
- Pivot table options
- Inserting formulas
- Date analysis
- Copying pivot tables
- Visualization Rules
- Creating pivot charts
- Dynamic chart labeling
- Mastering the slicer
- Showing report filter pages
- Linking pivot tables with PowerPoint
- Conditional formatting with pivot tables
- Extracting data using the ‘GetPivotData’
DATA MODELING
- Goal Seek
- Scenario Manager
- Creating new scenarios, editing & deleting scenarios
- Summarizing scenarios in Pivot TablesScenario summary reports
- ‘what-if’ analysis using spinner
- Designing three types of spinners
- Number spinners
- Percent spinners
- Text Spinners
- Working around spinner restrictions
- Check box data modeling
- Performing selective and comparative analysis
- Linking the check box to reports and graphs
- Designing visualization
- Option box data modeling with ‘if’ function
List box- Linking data from different sources
- Creating graphs
- Designing a dynamic dashboard using list box
DATA INTEGRATION USING POWERQUERY
- Linking Excel with text filesUsing get and transform data
- Splitting columns
- Perform data transformation
- Data properties
- Performing automatic refresh of data’
- Linking Excel with databases (Access)
- Linking Excel with multiple Excel files
- Append data
- Files
- Folders
ADVANCED REPORTING USING POWER PIVOT
- The “ETL” Extract, Load, Transform
- Get data from various data sources (Data extract)
- Transform Data using PowerQuery tools
- Direct load data as tables
- Load data to the Data Model
- Create Relationship between data sources
- Manage relationships
- Advanced reporting using PowerPivot
AUTOMATION USING MACROS
- Macro basics
- Planning a macro
- Designing your control board
- Recording macro
- Testing macro
- Editing macro
- Macro workshops
- Advanced filter with macro
