Excel from Beginner to Advanced – From Starty to Smarty

Explore statistical functions in Excel to calculate totals and averages, rank data, identify highest and lowest values, plus the most frequent value, uncovering the story behind your numbers.

Calculate the total inventory value in Excel by multiplying sell price by stock quantity to create value in stock, then use the sum function or AutoSum to total the column.

Compute the minimum, maximum, and average of the sell price using Excel functions Min, Max, and Average to give the marketing team a clear price range and mean.

Round prices to the nearest dollar using the round function, then identify the most frequent price with mode dot single or mode dot multi.

Master inventory ranking with rank equal and rank average to order products by quantity, handle ties fairly, and use the function arguments window for complex formulas.

Master count functions in Excel to quantify products: use count, countA, and countBlank to verify 726 sold of 731 total, with 5 not sold.

Master text data in Excel by using text formulas to transform dirty, unorganized data into clean, consistent information and stop tedious manual edits.

Explore cleaning and splitting names in Excel using TRIM, SEARCH, and FIND to locate the space and separate first and last names, and note the difference in case sensitivity.

Discover how to split full names into first and last names in Excel using len, left, and right, guided by space position; explore nesting and hiding helper columns.

Learn two powerful ways to split names in Excel: copy as values, then text to columns, and the newer textsplit function that spills across cells, supporting dynamic data.

Extract the email provider name with text after and text before, apply the upper function to convert to uppercase, and learn nesting to form a formula for provider data segmentation.

Discover how Excel treats time as a serial number, where each day is a single unit. Use this time math to measure gaps and transform days into clear, professional insights.

Break down dates in Excel using year, month, and weeknum functions to create birth month, birth week, and join year columns for weekly tracking and segmentation.

Create two new columns from the join date—the day of the month and the day of the week—and format them with text to show full or abbreviated day names.

Use today function to compute ages from birth dates, treating dates as serial numbers; build a months-based tool to forecast current and future ages for event planning.

Learn to use the EDATE function to predict customer ages at future dates by building a dynamic adjusted age column that updates with the months you specify.

Discover how Excel's today and now functions provide dynamic date and time, extract hour, minute, and second, and build times with the time function for real-world analysis.

Master the foundation of logic in Excel by learning conditionals and logical operators like if, and, or, and not, translating everyday reasoning into computer instructions.

Learn to use the if function in Excel to classify products as high value or low value by sell price over $50, and add a value classification column with filtering.

Master nested if statements in Excel to classify prices into three tiers: high value over $50, medium value $20–$50, and low value under $20. Apply the formula across the column.

Learn to centralize product data by listing unique categories, counting active products per category with countif and countifs, and calculating average prices with averageif, averageifs, and sumif.

Learn to build an IF formula with AND, OR, and NOT to mark In Campaign when stock exceeds 100, price is above 60 or below 10, excluding Health and Grooming.

Explore essential data management tools in Excel to turn messy datasets into organized workspaces, gaining full control over your data for a faster, more professional Excel experience.

Advanced filters let you filter by stock quantity, price ranges, above/below average, top/bottom percent, and category text filters; learn fast data filtering in Excel.

Master advanced sorting in Excel by using header arrows for sorts, using the SKU column to restore order, and applying the custom sort to order by category and stock quantity.

Master locking the header row and the SKU column in Excel to keep them in view as you scroll through 700 rows of inventory, reducing errors.

Master grouping and subtotals in Excel to reduce visual noise in large data, reveal detail on demand, and automatically create category sums and grand totals.

Learn to clean HR data in Excel by removing duplicates, splitting name and email with flash fill, applying wage-based conditional formatting, and adding a status drop-down list.

Clean hiring data in Excel by removing duplicates with the Remove Duplicates tool, choosing between full info or all fields, and reducing rows to 52.

Split the full name and email in the full info column using Flash Fill, a pro-level shortcut with Ctrl-E that automatically fills patterns and deletes the column.

Apply conditional formatting to color-code hourly rates, using data bars, color scales, and icon sets to distinguish cheaper from more expensive candidates, helping Linda make decisions.

Create a status column in Excel using data validation to enforce a professional drop-down list with immediate approval, manager review, and not relevant, preventing typos and messy filters.

Explore dynamic array functions that spill results from one cell into full lists, using unique, filter, sort, and sort by to analyze data.

Create and use smart tables in Excel to automate design, filtering, and dynamic ranges; leverage calculated columns, sticky headers, and named tables for error-free analysis.

Use slicers to filter data in Excel tables and dashboards with category buttons. Convert data to a table, insert slicers for categories, enable multi-select, and customize layout for faster dashboards.

Master data connections in Excel by unifying tables and using lookups. Learn primary and foreign keys, VLOOKUP, HLOOKUP, and XLOOKUP to retrieve data efficiently.

Connect tables in Excel by defining primary, foreign, and composite keys to build a unified reporting table; use vlookup or xlookup to pull customers, products, and orders into one row.

Learn how to use vlookup and xlookup to pull product data into a base order table, matching skus exactly and locking references for accurate data across sheets.

Learn how iferror serves as a safety net to stop formulas from crashing, wrapping vlookup with a backup plan to replace not found results with zero, blank, or text.

Master horizontal lookups with hlookup, using a sku as the lookup value to retrieve the product name from row 2 with an exact match, then drag the results across.

Xlookup, the modern replacement for vlookup and hlookup, searches in any direction with exact match and links orders to customers via a phone number key to build a table.

Use vlookup and xlookup to build a single table, remove the stock quantity column, and paste as values to lock data, creating a lightweight, faster master database.

Prepare data with helper tables and formulas to build professional charts; think like an analyst to match chart types and distill insights from large datasets into a compelling data story.

Master how to build a single column profit-by-year chart in Excel by creating a profit column, extracting the year, summarizing with SUMIF, and polishing with axis labels and data labels.

Master revenue and profit analysis by product category with a two-metric clustered column chart, building a helper table, correcting unit prices for quantity, and polishing formatting.

Learn to create pie and donut charts in Excel to visualize revenue and profit distribution by category, display percentage labels, build a quick percentage table, and compare distributions.

Master micro charts and sparklines to track monthly and yearly profits with a sumifs-based helper table. Lock and drag formulas to fill data and create year-specific sparklines in cells.

Create year-by-year bar charts in Excel using clustered bar charts with data labels, flipped axes, and a months y-axis title, coloring each year for clear, professional annual summaries.

Master pivot tables to transform raw data into a powerful management tool by dragging categories to one side and numbers to the other to reveal profitable customers and top products.

Identify date imposters by recognizing text dates and convert them to real dates with text-to-columns. Then refresh the pivot table to group by year, quarter, and month.

Master pivot tables by creating one on a new worksheet, configuring the four areas—filters, columns, rows, and values—and choosing sum or count calculations to turn raw data into insights.

Discover how to count unique products in each category using the distinct count option via PowerPivot, while weighing performance and sticking with standard pivot tables when needed.

Master three strategies to keep a pivot table current when adding data: extend the data source manually, use a column-based range, or convert to a dynamic excel table.

Create a pivot table that lists products with 'food' in their names and shows 2025 profit only. Group date fields by year and month to filter for 2025.

Master layered analysis in pivot tables by applying a contains filter for the word food and a greater than 1000 profit filter for 2025, then enable multiple filters per field.

Master calculating gross profit percentage in pivot tables by creating a calculated field, avoiding incorrect averages, and using external calculations as needed for accurate results.

Master pivot table layouts by using design tab options to insert blank rows after each category, adjust subtotals and grand totals, and switch to tabular form with repeat item labels.

Learn to analyze yearly profits using pivot tables and value calculations to compute year-over-year differences and percent changes, with practical steps and trend insights.

Rank categories by total profit and products by their category percentages, year by year, using show values as percent of grand total and percent of parent total.