Course Outline
DAY 1
- Data massaging: tools and techniques
- Consolidating data from separate files and sheets
- Advanced data validation using lists, dates and custom validation
- Cell management tools: left, right, mid, concatenate, value
- Naming, editing, and managing cells and ranges
- Subtotal, Sumif, Sumifs, Sumproduct, Count, Countif, Countifs
- 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 Trim, Len
- Managing texts and numbers using replace, find, and substitute
- Text change functions
DAY 2
- Reporting, analysis and reconciliations using pivot tables
- The 20 must learn rules
- Creating pivot tables
- Number formatting techniques
- Designing report layout
- Sorting in ascending, descending and more sort options
- Filtering labels and values
- Expanding and collapsing reports
- Summarize data by sum, average, minimum, maximum, count
- Show values as % of total and % of
- Pivot table options
- Drill down option
- Showing report filter pages
- Inserting formulas
- Data analysis
- Copying pivot tables
- Creating pivot charts
- Dynamic chart labeling
- Mastering the slicer
- Linking pivot tables and pivot graphs with PowerPoint
- Conditional formatting with pivot tables
- Designing reports using GetPivotData
DAY 3
- Modeling and integration techniques
- Perform ‘what-if’ analysis using spinner
- Checkbox data modeling with ‘if’ function
- Option button data modeling with ‘if’ function
- List box data modeling with ‘Choose’ function
- Linking Excel with text files
- Linking Excel with databases (Access)
- Linking Excel with multiple Excel files and SQL
- Linking Excel with internet
- Linking Excel with Excel
- Scenario manager
- Introduction to learning the ultimate tool in Excel: ”Macros”
- Macro basics
- Planning a macro
- Designing your control board
- Recording macro
- Testing macro
- Editing macro
- Macro workshops
- Advanced filter with macro
- Tips and tricks in Excel
- Data entry form
- Custom list
- Camera tool
- Text to speech
- Protecting worksheets and workbooks