Advanced MS Excel for Professional Use

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

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
• Date 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

Modeling and integration techniques

• Perform ‘what-if’ analysis using the spinner
• Check box data modeling with the ‘if’ function
• Option button data modeling with the ‘if’ function
• List box data modeling with the ‘Choose’ function
• Linking Excel with text files
• Linking Excel with databases (Access)
• Linking Excel with multiple Excel files and SQL
• Linking Excel with the 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