Microsoft Excel: Learn Modern Excel for Data Analysis

Introduce Microsoft Excel, create and save your first workbook, navigate the Excel interface, and align on language preferences for the course.

Explore Microsoft Excel as a versatile data tool across desktop, web, and mobile, with 1 billion users, covering data analysis, dashboards, and financial modeling across online, 365, and 2024 versions.

Create your first Excel workbook with a blank workbook or ctrl+n. Change the default save location from OneDrive to your computer in File options.

Explore the Excel interface, including the ribbon, quick access toolbar, formula bar, worksheets, and essential tools for data analysis like pivot tables and charts.

Align your Excel interface by setting the display language to English to follow along with demos and formulas. If English isn't available, install it via add a language and restart.

Navigate cells, add delete rows and columns, and enter with text, numbers, dates, dropdowns and checkboxes. Remove duplicates, apply conditional formatting, and sort and filter data in tables.

Explore a practical Excel project for Flame and Maven, a Phoenix food truck, using restaurant checks to format data, apply validation, and use custom filters for earnings.

Identify cells, ranges, rows, and columns in a spreadsheet, understand cell addresses and ranges, and distinguish fields, records, and tables for data in Excel.

Master fast data navigation in Excel using shortcuts for moving and inserting rows and columns, hiding and grouping, and quick navigation across cells and sheets with Ctrl and Alt keys.

Learn to enter and edit data in Excel, copy from above with Ctrl+D, and use drag to fill dates and numbers, plus freeze the top row.

Explore Excel data types—numeric, date/time, binary, and text—and how default alignments signal type, how spaces affect values, and how formatting like 1000 separators and dollar signs display.

Explore how Excel stores dates as numeric values, with January 1, 1900 as day 1. Times appear as decimals of a day, noon 0.5, and 6 p.m. 0.75.

Discover how to apply number formats in Excel, including currency, percentages, dates, and thousands separators, while the underlying values stay the same, using format cells, the format painter, and Ctrl+1.

Apply data validation in Excel to restrict inputs to whole numbers, dates, or text, customize input messages and error alerts, and leverage tables for scalable rules.

Learn to enhance data validation with checkboxes and drop-down lists, toggle true/false values, and build dynamic sources using tables and the indirect function in Excel.

Remove duplicates in Excel to keep unique records in a table, choosing to check the server column or all columns, with examples across date, server, and tip.

Learn how to use conditional formatting in Excel to reveal patterns with color scales, data bars, and highlight rules, including duplicates, top/bottom ranks, and custom thresholds.

Filter data in Excel tables to display only matching records using manual and criteria-based options. Learn date, number, and text filters, plus keyboard shortcuts like ctrl shift l.

Sort data in Excel by numbers, dates, and text with ascending or descending orders, and speed up workflows using Ctrl+Shift+L and Alt arrows for filtering.

Learn how Microsoft Copilot enhances Excel 365 tasks, from conditional formatting suggestions to quick shortcuts for tables, dates, borders, and array formulas.

Explore how a spreadsheet grid uses cell addresses and data types like numbers, dates, text, or binary, with data validation, dropdowns, checkboxes, and conditional formatting, plus sorting and filtering.

Master essential formulas and functions in Excel by understanding function syntax, reference types, and common errors, then apply diverse function types to solve real-world business problems.

Explore the LAX flight data project in Excel, creating calculated columns, joining related tables, and summarizing delays and cancellations across a workbook (flights, airports, 2016 forecast) to inform FAA reports.

Master the difference between formulas and functions in Excel and learn the universal function syntax: equals sign, function name, comma-separated arguments in parentheses, with optional brackets.

Explore the three types of Excel functions: traditional, single-value functions that return a value; array functions that spill into adjacent cells; and volatile functions that recalculate when the sheet changes.

Learn to write formulas in Excel, starting with the equal sign and referencing cells, divide minutes by 60 to hours, and use sum, average, and max functions on flights.

Explore fixed, relative, and mixed references in Excel, learn to lock columns or rows with dollar signs, and apply formulas and 3% growth in a 2016 forecast model using F4.

Master spill ranges in modern Excel with dynamic arrays, where a single formula spills across cells. Learn to perform sum, average, and max checks using the spill range.

Discover explicit vs structured references in Excel, comparing A1 cell references with table-structured references that auto extend sums as you add new rows, using a flights table example.

Explore common Excel error types—name error, value error, divide by zero, ref error, NA, and spill error—and learn practical fixes and how these errors will be addressed in upcoming lectures.

Master counting functions in Excel, including count, counta, and countblank, and apply them to tables to count numeric values, non-empty cells, or blanks using the flights table.

Learn to use sort and unique dynamic array functions in Excel to count unique destinations, list airlines, and compare with the old remove duplicates approach.

Explore how Excel logical functions act as decision-making tools by evaluating true or false conditions with operators, including dates, and learn how if, and, or, and iferror functions drive results.

Explore how the if function evaluates a logical test and returns a value when true or false, with examples showing meal decisions and flight delay status in Excel.

Master nested if functions in Excel to run multiple logical tests, determining meal eligibility by flight duration and dynamically flagging canceled, delayed, or on-time flights.

Learn to use and/or operators in Excel to test multiple conditions in an if statement, including airline and meal checks and compensation logic.

Use the iferror function to evaluate a formula and return a substitute value when an error occurs, such as blanks instead of a div zero error in flight data.

Explore how to use Excel's conditional math functions to analyze data by criteria, applying sumifs, countifs, averageifs, and maxifs to real airline delay scenarios.

explore two new excel functions, groupby and pivotby, to aggregate and summarize data by airline or by status, including counting flights, totals, and customizable sort options.

Explore how lookup functions find a code in a table and return related city from the airport table, using dimension and fact tables with primary and foreign keys; preview Xlookup.

Learn how the Xlookup function replaces Vlookup and Index Match by returning values from a return array based on matches in a lookup array, with optional if not found.

Explore basic date and time functions in Excel, including today, now, year, month, day, hour, minute, second, weeknum, and weekday, with practical examples.

Explore basic text functions in Excel, including upper, lower, proper, trim, left, right, mid, text before/after, text join, and text split, with examples for names, emails, and addresses.

Use Flash Fill to quickly format and combine names in adjacent columns into a full name with proper case, removing spaces, and fall back to a formula approach when needed.

Copilot demonstrates creating formula columns in Excel, calculating total delay as departure plus arrival delay, and flags flights as on time, delayed, or canceled based on arrival delay.

Master formulas and functions in cells, control fixed and relative references with dollar signs and F4, and use iferror to handle errors while exploring Excel's function types.

Explore pivot tables with a drag and drop interface, requiring no formulas, and learn source data requirements, sorting, filtering, and basic calculated fields to maximize insights.

Analyze the CRM data of sales opportunities in Excel to examine the pipeline, create calculated fields, and uncover insights to boost your team's performance.

Structure your data as a single tabular table with clear headers and consistent data types, convert binary fields to 1/0, and avoid calculated fields so pivot tables analyze accurately.

Discover how pivot tables filter, summarize, and analyze data without changing the source. Build pivot tables from a single data range by dragging fields into rows, columns, values, and filters.

Modify pivot table views in Excel by pivoting data, moving fields between rows and columns, and using layouts and filters to reveal deeper sales insights.

Learn how pivot tables auto group dates into months to analyze monthly sales trends, with optional quarter and year grouping and simple filters for closed deals.

Explore how to summarize values by options in pivot tables, using sum, count, average, min, and max to analyze sales data, win rates, and opportunities with practical examples.

Apply show values as calculations in pivot tables to view data as percent of column or row totals, running totals, and differences, then analyze stages and deals won.

Learn to use slicers and timelines to filter pivot tables, link slicers to multiple reports with report connections, and tailor views by location, sector, and date.

Create calculated fields in pivot tables to measure performance, such as price realization (sum of close value divided by sum of sales price), shown as a percentage.

Learn to use Copilot to analyze data with pivot tables, create and edit pivots in OneDrive, compare averages versus sums, and highlight top accounts by close value.

Use clean, tabular source data with clear headers to drive pivot tables. Explore dimensions and measures with slicing, sorting, filtering, and calculated fields that adapt to your data.

Learn how to visualize data with charts in Excel, choose the right chart type, format visuals, and tell a compelling data story using basic line, bar, and pie charts.

Explore why data visualization brings raw numbers to life, revealing clear patterns and trends through charts that make complex data easy to understand.

Create a basic bar chart in Excel from a pivot table of health expenditure per capita by region, then fine-tune formatting for readability.

Choose chart types based on data type and communication goal, using bar charts for comparing categorical data, avoiding line charts for categories and pie charts for simple composition.

Create and format line charts to visualize time series trends, then use 100% stacked area charts to show composition changes in Excel.

Learn to create and polish bar charts in Excel to compare health expenditure per capita by country, focusing on the top ten and highlighting the United States with data labels.

Discover combo charts that use a line and a column to compare health expenditure and life expectancy over time, with a secondary axis for clarity.

Explore how to create and optimize pie and donut charts in Excel, visualize part-to-whole data, and manage slices, labels, and legends for clear GDP by country comparisons.

Explore how histograms reveal the distribution of life expectancy across 174 countries in 2021, and learn to adjust bin width and underflow bins in Excel to interpret the data.

Plot scatter plots to reveal the relationship between health expenditure per capita and life expectancy across 174 countries, annotate the United States as an outlier, and label the axes.

Create and customize map visuals in Excel using geographic data with one row per location. Insert a filled map, adjust color scales and legends, and highlight life expectancy by country.

Use data visualization to bring data to life with charts. Choose chart type for your data and message, and format to reduce noise, color with purpose, and tell a story.

Explore a traffic safety analysis project in Excel using NYC accident data from 2021 to 2023. Identify seasonal patterns, visualize weekly trends, and analyze contributing factors to aid NYPD prevention.

Identify seasonal patterns by counting collisions by year and month and visualizing with a line chart in Excel, then filter April 2023 and separate lines by year.

Learn to visualize weekly trends in Excel by extracting weekday and hour, counting collisions by these values with a pivot table, and building a white-white-red heat map to reveal hotspots.

Analyze New York City collision data by contributing factor using pivot tables to count, filter top ten, compute percent dangerous (injury or fatality), and visualize with data bars.