Excel Reporting, Data Analysis & Dashboards Course

Derive Value from your Data Quickly & Effectively

The ability to understand, communicate and derive value from data is a core skill that all businesses need today. This includes using data to; measure, monitor, manage and improve an organisations performance.

Successful reporting and data analysis depend on:

  • Access to accurate and up to date data

  • Organising data into a meaningful and useful format

  • Creating effective reports

  • Interrogating data to gain insights into your business

 

Using Excel’s Tables, Pivot Tables and Pivot Charts, along with other Excel features, this course will train and equip you with the skills to do this. Actual business case studies and practical exercises are used in the course.

 

There are 2 versions of this course:

A 2-day Public Course and a 2 day Onsite Course.

In both cases, the full course content is covered in 2 days by using generic solutions.

The benefit of the onsite course is that on an additional day we will focus on your

company’s actual requirements & reports.

What you'll learn: MS Excel Reporting, Data Analysis & Dashboards Course 

Acquiring Data

  • Start with the end in mind - what data do you need access to?

  • Different data types, their formats and their uses when creating reports and analysing data

  • Acquiring and updating data from different data sources, including different File Types and Databases

  • Validating and cleaning your data to ensure there are no duplicate records and that your data; is complete, accurate and correct - including using Pivot Tables to achieve this.

 

Organising Data

  • Preparing your data in a user-friendly format - making it easy to create Pivot Tables, Pivot Charts and Dashboards

  • Joining Data Sets

  • Creating New Fields

  • Calculated Fields – best practices, tips and things to be aware of

  • Data Flags – identifying potential problem data or a specific condition in your data

  • Adding Performance Target Data and or Thresholds

  • Grouping, Sorting, Filtering, and Summarising Data

  • Performing Data Reconciliations

  • Adding New Data

Creating Pivot Tables, Pivot Charts and Dashboards

  • Create your first Pivot Table and Pivot Chart

    • Pivot Tables vs. using SUMIFS & COUNTIFS – the benefits and downsides

    • Adding Fields – Filters, Rows (Axis Categories), Columns (Series) and Values

    • Filtering, Sorting (incl. custom sorting), Grouping (including custom grouping), Expanding and Collapsing Fields

    • Changing Field Names

    • Value Summary Options (Sum, Count, Average, Max, Min and StdDev)

    • Formatting Values

    • Showing Values as a; % of Totals, % Difference From, Running Total or % Running

  • Formatting and Layout Options

    • Pivot Table Options – auto resizing and handling errors

    • Styles, Layouts, including Sub Total and Grand Total options

    • Conditional Formatting

  • Adding Calculated Fields and Items

  • Adding Slicers and Timelines – including linking to multiple tables and charts

  • Moving and Copying Pivot Tables and Charts

  • Creating Dashboards using Pivot Tables and Charts

  • Refreshing and Updating Source Data (including Automating Refresh)

  • Tips, Tricks and what to watch out for when using Pivot Tables and Charts

  • Present Charts in PowerPoint

Analysing Data

  • What is the difference between Reporting on and Analysing Data?

  • Summarising Performance using Descriptive Statistics (Sum, Count, Min, Max, Mean, Median, Standard Deviation) and why is this useful

  • Using the appropriate visualisation for the insight you require:

    • Identify trends and patterns in your performance

    • Compare performance

    • Identify deviations/variances from expected performance

    • Evaluate percentage contributions to performance

    • Create and use distributions to evaluate performance

    • Rank performance

    • Identify relationships between different measures

    • Make performance forecasts

 

Excel Tools and Features covered in this course include:

  • Excel Tables

  • Excel Pivot Tables and Pivot Charts

  • Excel Slicers and Timelines

  • Get Data options

  • Go to Special

  • Data: Text to Columns, Remove Duplicates and Data Validation

  • Conditional Formatting, including Identifying Duplicates and Conditional Formats on Pivot Tables

  • SUM, COUNT, AVERAGE, MAX, MIN, STDEV, SUMIFS, COUNTIFS, IF, AND, OR, VLOOKUP, INDEX, MATCH, XLOOKUP, IFERROR, GETPIVOTDATA, DATE Functions

  • Nested Functions

  • Linking to PowerPoint

Public Courses

Johannesburg - Sandton: 

  • 12 & 13 May '20

  • 28 & 29 Jul '20

  • 22 & 23 Sept '20

Durban: 

18 & 19 Jun '20

04 & 05 Aug'20

20 & 21 Oct '20

Course Cost: R5 500 (excl. VAT)

FLEXIBLE

PUBLIC & CORPORATE ONSITE EXCEL TRAINING

20 YEARS

DATA ANALYSIS, REPORTING  & EXCEL SPECIALISTS

COMPREHENSIVE

DEMO VIDEOS & RECAP SHEETS SUPPLIED

SUPPORT

ACCESS TO WEBINAR'S

& EXCEL TIPS

RELEVANT

SOLUTIONS BASED TRAINING WITH REAL LIFE EXAMPLES

Training Offices:

Johannesburg

106 Johan Avenue, Sandton

Durban

1 Tamarind Close, Umhlanga

Pietermaritzburg

400 Old Howick Road, Hilton

Corporate Onsite Training Available Countrywide

  • improve my skills on microsoft excel
  • excel training for businesses
  • excel training help in Durban
  • learn the features of excel

Tel: 086 167 3923

Email: info@summitsolutions.co.za 

Head Office: Hilton Quarry Office Park, 400 Old Howick Road, Hilton.

2020 | Copyright | Summit Solutions