Business Intelligence: Data Analysis and Reporting Techniques

• Advanced data validation using lists, dates, and custom validation
• The incredible table-tools technique
• Text functions, e.g., Left, Right, Mid, TextSplit, TextJoin
• Naming, editing, and managing cells and ranges
• Subtotal and Aggregate
• Looking-up data, texts, and values using Xlookup
• Slicing dates into day names, weeks, week numbers, month names, years and quarters
• Error handling functions and formula auditing

• Creating pivot tables
• Number formatting techniques
• Designing report layout
• Copying pivot tables
• Sorting in ascending, descending and more sort options
• Filtering labels and values
• Expanding and collapsing reports
• Drill down option
• Summarize values by sum, average, minimum, maximum, count
• Show values as % of total and % of
• Date analysis
• Pivot table options
• Inserting formulas and new fields
• Creating pivot charts
• Dynamic chart labeling
• Mastering the slicer
• Showing report filter pages
• Linking pivot tables and pivot graphs with PowerPoint
• Conditional formatting with pivot tables
• GetPivotData feature

Power Query: A Must-have Skill

• Get data from: Tables, files and folders
• Power Query to transform and clean up data
• Practical examples
• Import Excel files
• Get tables from .pdf files
• Get data from Website
• Consolidate multiple sheets or files
• Get data from folder

• Spinner
• Check box data modeling with If function
• Option button data modeling with If function
• List box data modeling with Choose function
• Scenario manager

• Visualization with sparklines
• Using fancy fonts
• Protecting cells, sheets and workbooks
• Tables with slicers
• Focus cell
• Useful shortcuts
• Flash fill