Understand Your Data and Find Insights: Exploratory Data Analysis with Excel

This course is designed for professionals who work with data and want to develop a structured approach to exploring and understanding datasets using Microsoft Excel.

Participants will learn how to work through a typical data exploration process: importing data, preparing it for analysis, visually examining data distribution and relationships, and using summary measures to describe what is observed. The emphasis is on learning techniques that help reveal patterns, irregularities, and potential relationships.

Through hands-on exercises and real-world examples, participants will practice:

• Examining how data is distributed,
• Observe how the distribution changes after applying conditions to subdivide data
• Exploring relationships between variables and determine strength of potential connections
• Identifying data quality issues and unusual values

 The course focuses on building analytical habits and practical skills that help participants ask better questions of their data and interpret results with appropriate caution.

 No prior knowledge of statistics is required. The course is suitable for Excel users who want to move beyond tables and formulas toward a more systematic and thoughtful exploration of data. The course introduces advanced Excel functionality useful for “data wrangling”, which benefits from good foundations in Excel.

Getting started with data exploration: what is Exploratory Data Analysis ?

Explore data, describe it and observe relationship between variables

This section focuses on descriptive statistics and statistical visualizations.

Explore your data

• First overview: identify typical values, unusual values, and understand tendency and data spread

How different visual representations highlight different aspects of the same data.

• Charts to observe individual variables: Bar, line, Box plots, Histograms,

• Advanced plots to observe data distribution: swarm plots, histograms

 Explore potential relationships between data variables

• The concepts of Co-variation and co-relation, and building a correlation heatmap

• Charts to observe relationships between variables: scatter plots for numerical data and trend-lines

 Working with discrete data and data in categories:

• Using conditions and contingency tables,

• Using stacked bar charts to observe categorical data dependencies

Overview of key Excel functions and features useful for EDA

This section introduces Excel functions that the EDA activities will rely upon.

Identify cell contents (ISxx functions), and counting cells in a range

Logical operations and developing conditional decision making. (IF, SWITCH and the conditional aggregation functions)

Sorting and finding data (SORT, SORTBY, SMALL, LARGE)

Selecting data with conditions: logical indexing, FILTER, XLOOKUP, CHOOSECOLS, HSTACK

Grouping and segmenting (GroupBy, PivotBy)

Aggregation using REDUCE

Adding functionality to Excel with LAMBDA and LET

The Data Analysis Flow: ingest

Start the Data Analysis flow : Ingest

• Importing data from a CSV or TXT files
• Importing data when it is on an image
• Importing data by creating basic queries to websites

Your data is not perfect: Clean and transform

This section introduces excel functionality useful to clean and transform numerical and non-numerical data

The elements of a data quality report

Missing observations, data cleaning

Basics of Regular Expressions for string manipulation and cleaning

CASE STUDY: UCS Satellite database infographic data

• Apply Excel functions to transform data types (VALUE / TEXT) and to work with string/text data (LEFT/RIGHT/MID/FIND, TEXTSPLIT, TEXTAFTER, CLEAN, TRIM) and extract the data required to populate a sample of infographics.

Apply a structured approach to exploring datasets using Microsoft Excel
Inspect data using structured exploratory techniques to understand distributions, variability, and data quality issues
Apply basic statistical concepts to support data interpretation
Select and use appropriate charts to visualise individual variables and data distributions, describe variation, patterns, and unusual observations
Apply 

• EXCEL to accomplish tasks in the data analysis cycle
• EXCEL for data ingestion
• EXCEL data segmentation, conditional operations and aggregation
• EXCEL for data description: basic statistics functions

EXCEL visualizations: Select or construct appropriate chart types (such as scatter plots, bar charts, histograms, and cumulative plots) based on the analytical question