Business Intelligence: Data Analysis and Reporting Techniques

Course Info

Length: 1 Week

City: London

Type: In Classroom

Available Dates

  • Dec-30-2024

    London

  • Feb-03-2025

    London

  • Apr-07-2025

    London

  • June-16-2025

    London

  • Aug-04-2025

    London

  • Oct-06-2025

    London

  • Dec-15-2025

    London

Dates in Other Venues

  • Dec-30-2024

    Barcelona

  • Jan-06-2025

    Singapore

  • Jan-06-2025

    Istanbul

  • Jan-06-2025

    Paris

  • Jan-06-2025

    Barcelona

  • Jan-06-2025

    Amsterdam

  • Jan-06-2025

    Dubai

  • Jan-06-2025

    Kuala Lumpur

  • Mar-17-2025

    Dubai

  • Apr-07-2025

    Barcelona

  • Apr-07-2025

    Amsterdam

  • Apr-07-2025

    Paris

  • Apr-07-2025

    Istanbul

  • Apr-07-2025

    Kuala Lumpur

  • Apr-07-2025

    Singapore

  • May-05-2025

    Dubai

  • July-07-2025

    Dubai

  • July-07-2025

    Istanbul

  • July-07-2025

    Barcelona

  • July-07-2025

    Singapore

  • July-07-2025

    Kuala Lumpur

  • July-07-2025

    Amsterdam

  • July-07-2025

    Paris

  • Sep-15-2025

    Dubai

  • Oct-06-2025

    Singapore

  • Oct-06-2025

    Kuala Lumpur

  • Oct-06-2025

    Barcelona

  • Oct-06-2025

    Istanbul

  • Oct-06-2025

    Paris

  • Oct-06-2025

    Amsterdam

  • Nov-03-2025

    Dubai

Course Details

Course Outline

5 days course

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

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

Course Video