Excel Formulas: Data Transformation and Analysis Done Right

Master Excel essentials and best practices to support data transformation and analysis. Use the zero one worksheets folder and the zero one Excel essentials file as you watch the videos.

Learn to split data into separate columns with Excel's text to columns feature in delimited mode, using a comma from the data tab, with preview and finish steps.

Learn how to format cells in Excel, identify whether values are text or numbers via the formula bar, and understand data behind the display with currency, custom, and date formats.

Explore autofill and flash fill in Excel, mastering fill series for numeric sequences and alphanumeric data, and using flash fill to extract first and last names without formulas.

Learn to remove duplicates in a column and extract unique industries in Excel, using a copy-paste workflow and the remove duplicates tool to reveal 116 unique values from 20,005 entries.

Learn to create dropdown lists from a named range in Excel with data validation, and manage the range with the name manager to adjust the source and prevent typing.

Explore cell references in Excel, comparing relative, absolute, and mixed types, and learn to lock rows or columns when dragging formulas using dollar signs and the F4 toggle.

Turn raw order data into an Excel table named orders, with headers and defined boundaries. Use table references like orders[Quantity] and ROWS(orders) to total orders, sales, and profit.

Learn how to use the left and right functions to extract five-character employee IDs from cells with junk characters, regardless of alignment, and apply the formulas by dragging down.

Use the mid function to extract a five-character employee ID from a cell pattern of three hashes, starting at a known position, and drag the formula to fill other rows.

Learn to extract an employee ID from irregular cells in Excel by combining the find and mid functions, locating the start position with a case-sensitive search and extracting five characters.

Learn how the search function is not case sensitive, find starting positions, and use wildcards with mid to extract or filter data.

Learn to use the substitute function in Excel to remove characters, replace dashes with blanks, and control the first instance with the optional instance number.

Explore six simple text functions to clean and transform data, including concatenate and the ampersand method for joining names, plus trim, len, and case conversions (proper, lowercase, uppercase).

Convert text to numbers by using substitute to remove unwanted characters and the value function to convert to a number, handling spaces so sums reflect true values.

Apply the if statement to classify revenue as high or low by comparing it to the average revenue calculated with aggregate, with rounding up as needed, updating dynamically.

Explore how and/or logic with Excel formulas evaluates multiple criteria—revenue versus average and profit margin above 15%—and uses the IF function to tag results as high or low.

Use the iferror function to handle errors in Excel. Wrap the value function around revenue to convert text to numbers, showing 'invalid' for errors and aiding filtering in transactions.

Use the sumif function to total revenues for a single date criterion in an Excel table, referencing the date column and the revenue column.

learn how to use the sumifs function to total revenue by multiple criteria, specifying the sum range and multiple criteria ranges such as sales rep and product.

Identify profit or loss and classify profits as low, medium, or high; then populate a summary sheet with customer name, segment dropdowns, and sales, quantity, profit, and profit percentage.

Convert string-based dates to actual date formats using the date value function, handling spaces, and format results in your preferred date style in Excel.

Explore edate and eomonth to calculate dates from a start date and elapsed months, returning exact dates or month-end dates for a six-month window.

Learn to compute date differences in Excel using subtraction or the date difference function, by supplying start date, end date, and D, M, or Y for days, months, or years.

Explore workday and workday international functions to compute 30 working days from April 19, 2023, accounting for weekends and UAE holidays, with weekend codes and seven zeros.

Use the network days function to calculate working days between two dates, accounting for holidays with an optional holidays range, demonstrated with UAE 2023 holidays and 78 working days.

Use vlookup to pull a sales rep's region from the master table into the transactions sheet, with table array set to the master, column index 2, and range lookup false.

Master vlookup dynamics with column and indirect to fetch fields from a shipments data table. Understand the leftmost column rule and common drawbacks to build robust lookups.

Learn how index and match overcome vlookup drawbacks by dynamically locating rows and fetching values from a data table, using indirect for flexible column references.

Explore XLOOKUP, the successor to VLOOKUP, to fetch a region by the lookup value (salesperson name) across a lookup array returning region, with exact match (0) and not found handling.

Explore what-if analysis with the scenario manager in Excel, learning to define scenarios, adjust price shares, and see total profit changes via a scenario summary or pivot table report.

Learn how goal seek in what-if analysis determines the values to change to reach a target profit.

Explore how data tables analyze gross profit under varying exchange rate and labor cost, using a two-variable data table with row and column inputs to forecast outcomes.

Plan and build pivot tables to analyze loan data by status, using counts of customers, with home ownership as columns and filters by loan purpose.

Learn to format pivot tables with the design tab, applying styles and controlling subtotals and grand totals. Discover layout options like compact, outline, and tabular, plus repeating labels.

Learn to create pivot charts from a pivot table, apply filters, expand and drill down, and group dates to months, quarters, or years, switch to a line chart for timelines.

Explore how to build dashboards in Excel using pivot tables and direct formulas, with interactive slicers that update charts and tables for year 2019 loan data.

Create and place a slicer on the loan dashboard to filter by issue date, use a pivot table, display all 12 months, and drive updates across components.

Use pivots to populate a dashboard with total disbursements and good versus bad loan percentages, by renaming pivots and wiring them to slicers for dynamic updates.

Create and place interactive charts on a dashboard using pivot data, including line charts with markers and box-and-whisker plots, with slicers to explore loan trends and outliers.

Learn to build a top n pivot that shows the top three customers by loan amount, filtered for fully paid loans, and connect it to a dashboard with slicer-driven updates.

Create inline bar charts in cells on a dashboard using the repeat function to show percentages, then switch to playbill font and adjust color for clarity with slicers.