Business Intelligence: Data Analysis and Reporting Techniques


Course Objectives

By the end of the course, participants will be able to:

 
  • Boost Excel Business Intelligence (BI) expertise in data slicing and dicing, data massaging, and data aggregation and modeling
  • Perform data normalization, consolidation, report writing, analysis and reconciliation
  • Develop dynamic BI models, dashboards, scorecards and flash management reports by linking-up Excel with Access, Web, Text, Internet, SQL, ERPs and other databases
  • Advance and enhance the look and feel of reports using dynamic visualization techniques
  • Acquire numerous tips and tricks that enable them to work efficiently
Course Outline
  • Data analysis tools and techniques
    • Consolidating data from separate files and sheets
    • Advanced data validation using lists, dates and custom validation
    • Powerful array functions
    • 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 and len
    • Managing texts and numbers using replace, find, and substitute
    • Text change functions
  • Mastering data reporting: the 20 must learn pivot-tables tools
    • 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
    • Drill down option
    • 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
    • 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
    • Designing reports using the getpivotdata
  • Data modeling and integration
    • Spinner
    • Check box data modeling with if function
    • Option button data modeling with if function
    • List box data modeling with choose function
    • Scenario manager
    • Linking Excel with text files
    • Linking Excel with databases (Access)
    • Linking Excel with SQL
    • Linking Excel with internet
    • Linking Excel with Excel
  • The look and feel: charting and visualization techniques
    • Using the camera tool
    • Working with formula-driven visualizations
    • Using fancy fonts
    • Leveraging symbols in formulas
    • Working with sparklines
    • Creating unconventional style charts
  • Tips and tricks
    • Controlling and protecting your reports, worksheets and workbooks
    • Data entry form
    • Custom list
    • Text-to-speech
    • Advanced conditional formatting
    • Shortcuts for your daily work

المواعيد المتاحة

دبى
من 12-05-2025    الى 16-05-2025
ابو ظبي
من 14-07-2025    الى 18-07-2025
دبى
من 01-09-2025    الى 05-09-2025
دبى
من 08-12-2025    الى 12-12-2025