Excel Data Analytics: Formatting, Functions & Pivot Tables

Take your Excel skills beyond basic totals and learn how to extract meaningful, actionable insights from raw data. This course covers the essential data analytics tools in Excel—from dynamic tables and conditional logic to visual charts and Pivot Tables.

📂 Course Materials

📥 Download Exercise Files: Click here to access practice workbooks

🎯 Who Should Take This Course

• Data Analysts & Professionals who need to summarize, present, and draw insights from workplace data.

• Intermediate Excel Users looking to level up from simple formulas to automated reporting and interactive dashboards.

• Prerequisites: Basic familiarity with Excel (e.g., cell referencing, navigating workbooks, and standard functions like SUM and AVERAGE).

💡 What You Will Learn

• Data Structuring: Design efficient lists and utilize Excel Tables to filter and calculate data dynamically.

• Conditional Logic: Master conditional formatting to highlight trends, and leverage IF, SUMIF, AVERAGEIF, and SUMIFS for targeted analysis.

• Visual Storytelling: Build polished charts and embedded Sparklines to communicate key trends visually.

• Pivot Table Mastery: Summarize massive datasets, analyze counts and aggregations, and create interactive Pivot Charts.

⏱️ Course Outline & Timestamps

Module 1: Data Structuring & Table Analysis

• 0:00 – Start

• 0:09 – Introduction

• 1:25 – List Design Best Practices

• 4:59 – Converting Data into Tables for Analysis

• 9:57 – Advanced Filtering in Tables

• 15:10 – Dynamic Calculations with the Total Row

Module 2: Conditional Analysis & Summary Functions

• 18:57 – Conditional Formatting for Visual Insights

• 25:23 – Logical Calculations: The IF Function

• 31:52 – Single-Criterion Analysis: SUMIF & AVERAGEIF

• 39:22 – Multi-Criteria Analysis: SUMIFS

Module 3: Data Visualization & Charting

• 42:37 – Inserting Recommended Charts

• 48:24 – Fine-Tuning & Customizing Charts

• 50:41 – Micro-Visuals: Using Sparklines

Module 4: Pivot Tables & Interactive Reports

• 57:25 – Building Your First Pivot Table

• 1:05:18 – Data Summarization: Displaying Counts & Aggregations

• 1:09:06 – Filtering & Sorting Pivot Tables

• 1:17:47 – Creating Interactive Pivot Charts

• 1:22:04 – Course Conclusion