MS Excel Data, Reporting & Analysis

"The ability to Organise, Visualise and Explore Data, in a way that provides useful information and insights, is a core skill that all businesses need today."

Course Outline

Using Excel’s, Tables, Pivot Tables and Pivot Charts, along with other Excel formulas & features, this course will equip you with the skills to derive value from your data.

  • 1

    Data, Reporting & Analysis Introduction

    • Course Introduction

  • 2

    Section 1 - Reporting vs. Analysis

    • 01 - The difference between Reporting & Analysis

    • 02 - Types of Reports - Examples

    • 03 - Types of Analysis - Examples

  • 3

    Section 2 - The Value Chain

    • 01 - Access the Data you Need

    • 02 - Organize Data into Meaningful & Useful Information

    • 03 - Visualize Data

    • 04 - Explore Data

    • 05 - Make Decisions & Take Action

  • 4

    Section 3 - Accessing Data

    • 01 - Introduction to Data Types

    • 02 - Capturing and Copying Data

    • 03 - Importing Data from Files & Databases

    • 04 -Excel Tables, the Correct Layout!

    • 05 - Excel Features for Organising Data into the Correct Layout

    • 06 - Brief Introduction to our Excel Automation Course

  • 5

    Section 4 - Validating Data

    • 01 - Rubbish in Rubbish Out

    • 02 - Finding and Handling Duplicates

    • 03 - Data Consitency

    • 04 - Data Completness

    • 05 - Data Accuracy

  • 6

    Section 5 - Joining Data

    • 01 - Maintaining Information in 1 Place Only!

    • 02 - VLOOKUP (With True and False)

    • 03 - INDEX MATCH

    • 04 - XLOOKUP

    • 05 - What happens when Lookup List Values Change Over Time?

  • 7

    Section 6 - Adding Fields & Golden Rules

    • 01 - Calculated Fields

    • 02 - Adding Data Flags - Using IF & Nested IF Statements

    • 03 - Handling Formula Errors

    • 05 - Averages of Averages

    • 06 - No Value vs Zero Value

  • 8

    Section 7 - Creating Summary Reports Using SUMIFS & COUNTIFS

    • 01 - Business Case Examples

    • 02 - SUMIFS

    • 03 - COUNTIFS

    • 04 - Advantage and Disadvantage vs. Pivot Tables

  • 9

    Section 8 - Creating Pivot Tables & Pivot Charts

    • 01 - Inserting a Pivot Tables and Pivot Charts

    • 02 - Adding Fields

    • 03 - Summarizing and Formatting Values

    • 04 - Styles & Layouts

    • 05 - Filters, Grouping, Expand & Collapse

    • 05 - Show Value as, e.g. % of Column Total

    • 06 - Adding Calculated Fields

    • 07 - Pivot Table Options

    • 08 - Applying Conditional Formatting

    • 09 - Insert Slicer & Time Line

    • 10 - Refreshing & Adding Data

  • 10

    Section 9 - Visualising Data, Using the Appropriate Charts

    • 01 - Trends and Patterns

    • 02 - Making Like for Like Comparisons

    • 03 - Part to Whole, e.g. % of Target or Total

    • 04 - Deviation from, e.g. % Over or Under

    • 05 - Ranking, e.g. Best/Worst, Top or Bottom

    • 06 - Frequency Distributions & Percentiles

    • 07 - Data Relationships between Values

  • 11

    Section 10 - Visualising Data to Answer - What is Happening?

    • 01 - Business Use Case Examples

    • 02 - Descriptive Statistics: SUM, COUNT, MEAN, MEDIAN, MAX, MIN & STDEV

    • 03 - Knowing What is Happening?

  • 12

    Section 11 - Exploring Data to Answer - Why is it Happening?

    • 01 - Business Use Case Examples

    • 02 - "Slicing and Dicing"

    • 03 - "Dragging and Dropping"

    • 04 - Exploring Data to Find out What is Happening

  • 13

    Section 12 - Dashboards

    • 01 - Business Use Case Examples

    • 02 - Creating Dashboards

    • 03 - Important Design Considerations