Excel Formulas: Data Transformation and Analysis Done Right

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.

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 how the search function is not case sensitive, find starting positions, and use wildcards with mid to extract or filter data.

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.

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.

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.

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.

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 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.

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.