Excel Data Analysis: From Beginner to Advanced Techniques

Explore data types and cell formatting in excel, learn how formatting cells assigns general, number, text, currency, date, time, percentage, boolean types, and control decimals.

Master basic formulas and functions in Excel data analysis, including max, min, sum, and count, to analyze employee ages, salaries, and bonuses.

Explore basic Excel functions like sum, count, max, min, and average on a sample data range, and learn to compute totals, identify oldest and youngest, and handle text versus numbers.

Learn to clean data in Excel by removing duplicates and blanks, using filters, and converting to tables to ensure complete rows for accurate analysis.

Discover how to clean and extract text in Excel using trim, left, right, mid, concatenate, and text join. See practical demonstrations that show removing spaces, pulling characters, and joining names.

Explore date and time functions in Excel, using today and now to capture dates and calculate durations between dates in months or years, including birthday age calculations.

Create drop down lists in Excel with data validation. Use active and inactive options, or reference a cell, to ensure consistent data entry.

Master flash fill to automatically extract first and last names from full names and patterns using Ctrl+E, then split data into columns with text to columns by spaces or dots.

Explore how to apply conditional formatting in Excel to visualize data with data bars, color scales, and icon sets, highlighting oldest and youngest ages, top salaries, and retirement thresholds.

Learn to apply excel's if, and, or, not, and iferror functions to evaluate employee scores, determine pass or fail, assess active status, and decide bonus eligibility.

Master nested formulas in Excel to build complex logic using if, and, or, not, and if error. Create status and bonus calculations with conditional thresholds and activity.

Create structured tables in Excel to automatically expand, sort, and filter data with structured formulas. Format tables, use a total row, and add a structured column to calculate bonuses.

Name and manage data groups with named ranges to simplify calculations, enabling sum and average on selected records such as the top three scores.

Explore how offset and index create dynamic ranges in Excel that update charts and sums as you add or remove data, using named ranges and count a to skip blanks.

Learn how to create a summary report in Excel using pivot tables and pivot charts, add slicers and timelines to filter data, and explore department and sales insights.

Explore pivot tables in Excel to summarize sales by region and department, use timeline and slicers, and build interactive dashboards with charts. Learn to filter data and visualize results effectively.

Create and customize pivot tables in Excel to count employees by region and department. Use slicers to filter and sort by employee, timeline, and cells, revealing tree-like pivot layout.

Learn to create calculated fields and value fields in a pivot table to summarize department sales, and compute profit as sales minus cost with value field settings.

Create and customize pivot charts from a pivot table, selecting chart types, colors and themes, adding titles, data labels, legends, and trend lines for dynamic updates.

Discover real world Microsoft Excel use cases in sales analytics with charts and pivot tables, customer feedback analysis, inventory monitoring, KPI tracking (hours worked and project delivery), and data-driven decisions.

Learn how to use vlookup, hlookup, and index & match to find salaries by employee id in vertical and horizontal tables, with exact and approximate match options.

Master xlookup in Excel 365 and Excel 2021 to look up salaries in vertical and horizontal tables without using vlookup or hlookup. Learn by example with Emily Johnson's salary.

Master advanced text and date functions in Excel, including trim, left, right, mid, proper, len, find, search, text join, substitute, and data def.

Explore dynamic arrays in Excel, using unique to extract department lists, sort to order names, and filter to display the it department data.

Explore indirect, offset, and choose to build dynamic ranges for tables and charts in Excel, with practical examples.