Exploratory Data Analysis: An introduction using Excel

This course takes the participants through the data analysis flow: from ingesting and transforming data, to data visualization and exploration, using Excel’s functions to implement the split-apply-combine paradigm and to compute the statistical metrics that describe the data, and using the visualization options in Excel (box plots, histograms, bars and scatters).

The principles of EDA are reviewed an applied using various Excel functions applied to data case studies using univariate and bivariate data, numerical and categorical.

Students should have interest in working with and processing data, and motivation to learn to use Excel to answer questions using data.

Since the course introduces descriptive statistics and EDA, no a-priori statistics knowledge is required.  The course introduces advanced Excel functionality useful for “data wrangling”, which benefits from good foundations in Excel.

What is Exploratory Data Analysis (1 hours)

Overview of key Excel functions and features useful for EDA (8 hours)

This section introduces the key concepts and advanced 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)

-   Filtering arrays and selecting data: logical indexing, INDEX, FILTER, XLOOKUP, CHOOSECOLS, HSTACK

-   Split-apply-combine: Grouping and segmenting (FILTER, GroupBy, PivotBy)

-   Aggregation using REDUCE

-   Adding functionality to Excel with LAMBDA and LET

Describing data and relationship between variables (8 hours)

This section focuses on descriptive statistics and statistical visualizations.

-    Univariate analyses:

• Measures of central tendency and spread
• Quantiles and Empirical Cumulative distributions (ECDFs)
• Measures of frequency for categorical data and bar charts
• Examples using 100m sprint times and the FAA bird strike database data
• Histograms and Q-Q plots

   Examples using the Palmer Archipelago Penguin data

-    Bivariate analyses:

• Relationship between variables: covariance and correlation. Correlation matrix. Example using CAR PRICES dataset
• Contingency tables and conditional counts. Examples using the Titanic survivors data set

The Data Analysis Flow: ingest (2.5 hours)

-    Start the Data Analysis flow : Ingest

• Getting data from CSV or TXT files
• Getting data from images of tables
• Getting data from the web

Your data is not perfect : Clean and transform (5.5 hours)

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.

- Learn the principles of descriptive statistics and EDA for univariate and bivariate data exploration

- Learn to use EXCEL as part of the data analysis cycle

• Excel for data ingestion
• EXCEL for split-apply-combine actions (data segmentation, Boolean indexing, conditional operations and aggregation)
• EXCEL for descriptive statistics: descriptive statistics functions
• EXCEL visualizations: Learn when to use and how to customize key visualisations: scatter plots, bar plots, histograms, and cumulative frequency plots

Classroom delivery and explanations with hands-on application via use cases in highly interactive sessions.