Master Excel and Solve 500 Problems (Beginner - Advanced)

Learn the fundamentals of Excel formulas, including addition, subtraction, multiplication, and division, and master absolute and relative references (dollar signs) to unlock full formula functionality.

Write and fill Excel formulas using relative references to calculate transaction values with a unit price of $3, enable auto recalculation, and fill down across the table.

Explore using relative references and formulas in Excel to calculate transaction values across two tables with a unit price of 3, then fill down using the double-click method.

Explore field operation and relative references in Excel by calculating transaction values and filling formulas to the right across a row.

Multiply quantities by prices to calculate sales values across retail, wholesale, and tender channels using a single formula filled across and down in Excel.

calculate sales values by multiplying product quantities by unit prices across retail, wholesale, and tender channels. use absolute references with dollar signs to freeze prices; allow relative references to adjust.

Learn to build a multiplication table using absolute and relative references in Excel, applying the same formula across rows and columns with proper dollar signs.

Master Excel's paste special to copy only values from a total cell, avoiding formulas and formatting, and apply comma style for clear large-number display.

Copy and paste formulas in Excel while preserving table formats using paste special, ensuring totals and formatting remain intact across multiple year tables.

Learn to use copy and paste special in Excel to apply same comment to all yellow cells by filtering by color, selecting visible cells only, and pasting comments and notes.

Convert euro values to USD in the value table using paste special values and multiply by the exchange rate, preserving existing adjustments and formatting.

Learn to convert euro values to USD in a value table using paste special: copy the monthly exchange rates, apply values and multiply across the table, preserving adjustments.

Learn to use paste special transpose to align monthly exchange rates with euro values and build a formula that converts euro to USD using correct absolute and relative references.

Use paste special transpose to align the price table with the quantity and value tables. Then multiply quantity by price to fill the value USD table.

Master Excel techniques to convert euro values to USD by using a transpose with paste special, then multiply quantities by prices and apply monthly exchange rates.

Perform paste special transpose in Excel to multiply the value USD quantity table by the price USD table, producing sales values with no formulas.

Use paste special transpose to create the price usd table by dividing values from the quantity table and pasting values only, after renaming the table.

Demonstrate paste special in Excel to sum January and February quantities, multiply by prices, and compare results to verify identical total sales values across tables.

Copy formatting from one table to another using paste special formats and the format painter, ensuring data stays intact while applying consistent formatting across ranges.

Master Excel basics by applying sum, average, count, mean, and max functions, while learning to work with multiple worksheets and write formulas referencing other worksheets.

Learn to use the sum function in Excel to calculate the January total and the table total. See how specifying a range like B2:B13 streamlines calculations and reduces errors.

Learn to use Excel sum and average to compute totals and averages across retail and wholesale tables, including selecting ranges and avoiding double counting.

Learn to compute cumulative total and cumulative average values in excel using sum and average with dollar signs, anchoring the starting cell to fill across months.

Master Excel basics by applying sum and average functions with absolute and relative references to compute cumulative totals and cumulative averages.

Write a multi-worksheet sum formula that references B2 across support one to four sheets to calculate monthly totals, and fill formulas across rows and columns using relative references.

Sum values across worksheets using the sum function, with a range from support one to support four and relative references to B2, for totals per month and per product.

Select multiple worksheets to perform a cross-worksheet calculation of cappuccino sales differences between wholesale and other channels on row 20, using as few operations as possible, excluding the retail sheet.

Learn how the count function tallies numeric cells in a table to reveal total transactions by counting number values in a numeric column such as quantity or price.

Learn to use count A on table one to count working employees and sum on table two to total monthly sales, then compute average sales per employee.

Discover how to apply countA across four worksheets, using relative references to count nonempty cells, and compute the average amount sold per day with active employees.

Learn the count blank function to calculate the number of empty cells in a store’s shift data and determine days with no employees, as shown in the store-by-day example.

Master Excel formulas using counta, count, and sum to determine total employees across ten stores from a date-by-store table, by counting text and summing numbers in the B2:K91 range.

Combine sum, counta, and subtraction to total daily employees across ten stores, then apply a filter to show dates with more than seven employees.

Learn to compute daily minimum and maximum sales by applying the min and max functions across a rolling B:D range, using absolute and relative references to fill down.

Compute minimum and maximum values per month and product across four supporting worksheets by selecting all worksheets, applying min and max to B2, and filling down and to the right.

Learn to use min and max functions across four supporting worksheets to compare retail, wholesale, franchise, and tender sales by referencing the same table ranges across sheets.

Learn how the abs function converts negative differences (actual minus planned) into positive values and why summing absolute daily differences reveals true planning accuracy in Excel.

master the abs function and paste special values to convert all numbers to negative in Excel, using a minus abs approach.

Compute daily stock prices by multiplying the previous price by the daily rate, using the product function and multiplication operator, with absolute and relative references for filling down the table.

Explore basic text formulas in Excel, including concatenate, len, left, right, substitute, and replace, and learn how to handle textual values effectively in worksheets.

Learn to concatenate first name and last name in Excel using the and operator, inserting a space symbol with double quote marks, and recognizing text values vs numbers.

Master the concatenate function to combine first name and last name with a space in Excel, using text values and cell references to build a full name.

Combine text and numbers from multiple cells into a single sentence using concatenate or the ampersand, including fixed phrases like 'sold' and 'cups of' with proper spaces.

Use nested left and right functions in Excel to extract the middle four digits of a text (the patient number) by taking the left eight symbols, then the right four.

Master the Excel search function to locate spaces and dashes in text, using within text and optional start number to find first, second, and third dash positions.

Learn how to use the find function in Excel, compare it with search, and handle case sensitivity to identify who should get medical check today.

Master the mid function in Excel to extract the middle four digits from a visit code by building a formula with text, start position, and the number of characters.

Learn to use the LEN function in Excel to count characters in cell C3, determine if ID lengths are 9 or 12, and flag others in column D for filtering.

Use len to count symbols in each cell and sum to total the offers across the sales table, where plus marks indicate accepted and x marks indicate rejected, yielding 1436.

Learn to manipulate text in Excel using left, mid, find, and concatenate to build new emails with first initial, dot, last name, and the sky ambulance.com domain.

Update patient numbers in visit codes using the replace, find, and right functions. Find dash positions, measure the middle segment, and apply right to extract six digits.

Learn to use the substitute function in Excel to add full name: and ID: prefixes to patient data, using the first space as the substitution trigger.

Master Excel's Len and substitute functions to count symbols in a single cell, compare total characters with and without stars, and apply this method to a practical monthly stars example.

Calculate player total points in Excel by counting plus, equal, and x symbols with LEN and SUBSTITUTE, then apply three, one, and negative two points per symbol.

Learn how to clean text in Excel using the trim function to remove extra spaces, leaving a single space between words and removing leading or trailing spaces.

Learn how to clean emails in Excel by using substitute and trim to remove extra at symbols and restore a single @, with a step-by-step formula.

Convert text to uppercase, lowercase, and proper case in Excel using the upper, lower, and proper functions. Learn with an example transforming employee names.

Learn to clean messy text in Excel by using trim to remove extra spaces and proper to fix capitalization, producing correctly formatted names.

Learn to use the REPT function in Excel to repeat stars, dots, and vertical dashes according to win, loss, and draw counts, and apply the formula in column E.

Learn to create simple shapes in Excel using text functions, specifically rept and concatenate with the & symbol, to build a Christmas tree graphic with stars and dashes.

Learn to separate text values and number values from a single column in Excel using the T and N functions. Apply formulas in adjacent columns to extract names and IDs.

learn to split text into separate Excel columns using the text to columns tool with a slash delimiter, yielding first name, last name, birth date, and identification number.

Master text to columns in Excel to split employee data by a slash delimiter, and format as text to preserve leading zeros and dates.

Learn to split employee data into first name, last name, identification number, and badge number using text to columns with a delimited slash, preserving leading zeros and skipping unused columns.

Use Excel's text to columns tool to split data by slash, starting from column d, producing first name, last name, birth date, identification number, and country of birth.

Split a visit code into patient number, clinic code, and ticket code with text to columns using fixed width and breaks at 4,3,8, preserving zeros in d3.

Use flash fill to combine first and last names into a full name with a space. Excel's data tools guide this with an example from data > flash fill.

Learn to split employee data with Flash Fill in Excel, creating examples for first names, last names, birth dates, and more, then fill cells with Ctrl+E.

Learn to use flash fill in Excel to extract first and last names from text with identification numbers, using Ctrl+E and sample-based guidance.

Learn to convert numbers stored as text with the value function and sum them to calculate the total quantity sold.

Learn to extract numbers from text in Excel using the mid, find, and value functions, then sum the results to compute the total quantity.

Learn to verify Excel records by calculating total values for entries and branches, extract numeric values from text using mid and value, and sum visible filtered results.

Reveal cell formulas in Excel by toggling formulas with Ctrl+` or viewing them in the formula bar. Display a cell's formula with formula text function, noting availability since Excel 2013.

Convert hire date values to date format, then calculate end dates by adding years and weeks converted to days, ignoring leap years, to the hire date.

Calculate the calendar days between hire date and contract end date by subtracting dates in Excel. Enter the formula =E2-D2 in cell F2 to get the days worked.

Calculate the number of calendar days worked in the hire year by composing a date from day, month, and year, then using date(year,12,31) and subtracting the hire date.

Learn Excel date functions to compute probation end dates using two methods: build next-month date with year, month, day, and use edate to shift by one month.

Use the eomonth function to find the end of the hire month and subtract the hire date to calculate calendar days worked within that month in Excel.

Compute employee ages in Excel using the today function and birth date, dividing the difference by 365 to reflect ages with automatic recalculation.

Use today() to fill empty contract end dates, filter to display blanks, paste only visible cells, and calculate days worked as end date minus hire date.

Automate invoice dates in Excel using today, eomonth, and edate, set up absolute references with $, and ensure recalculation for current date, five-day due dates, and delivery dates.

Explore formatting dates in Excel with the text function to render weekday name, day, month, and year, using absolute references and the date keywords D, M, Y.

Master Excel date and text operations with a formula using the text function and & operator to yield February 24th, Charlotte sold 795 units of espresso.

Explore the weekday function to identify weekend hires in Excel by applying it to hire dates, understanding returntype options, mapping days 1 to 7, and filtering the table for weekends.

Learn to use the workday.intl function to calculate an employee's contract end date from the hire date and 23 working days, assuming weekends on Saturdays and Sundays.

Use the workday.intl function to add working days to a hire date, excluding weekends and national holidays, converting text to dates and making holidays range absolute.

Use workday.intl to compute the contract end date by adding working days to the hire date, using a 7-symbol pattern to mark Tuesday, Friday, and Sunday as non-working.

Compute working days between two dates using networkdays.intl, excluding tuesday, friday, and sunday, plus national holidays listed in the table.

Use Excel's networkdays.intl to compute March 2022 working days, then calculate salary payable by dividing monthly salary by 23 and multiplying by days worked from hire date to March 31.

Compute March 2022 salary by dividing monthly pay by working days and multiplying by days worked using the networkdays.intl function with a custom Monday, Wednesday, Thursday weekend pattern.

Learn to calculate employee working days in Excel using NETWORKDAYS.INTL with TODAY function nested inside, selecting Friday and Sunday as non-working days, starting from hire dates in column D.

Use the time function to combine hours, minutes, and seconds from D, E, and F into column G with time formatting; Excel stores time as a fraction of a day.

learn to combine arrival date, hour, minute, and second into a single date-time value in Excel, format as date and time, and customize the display.

Extract hour, minute, and second from a date time value in Excel using hour, minute, and second. Apply these formulas to a table to populate separate columns and fill down.

Calculate employee salaries in excel by subtracting arrival from leave to obtain hours worked, convert the time difference to hours by multiplying by 24, and multiply by $15 per hour.

Master nested functions in Excel by applying logical comparison operators and functions like if, and, or, building complex formulas with up to ten levels in a single cell.

Master the if function by building a formula with a logical test that compares two shifts and returns 'same shifts' or 'no match'.

Learn to use the Excel IF function to apply a 7% discount only for Rome transactions, generating the discounted value in USD while keeping other rows unchanged.

Learn to use nested if functions in Excel to calculate city-based discounts, assigning 1% for Boston, 3% for Rome, 2% for Madrid, 4% for London, and 0.5% for others.

Master the or function in Excel by applying two comparisons to flag Doha or London, returning true when either condition holds and converting booleans to 1s and 0s.

Use if and or in Excel to apply a 5% discount when city is Madrid or Rome or channel is wholesale or B2B, otherwise keep the original value.

Master nested ifs and or in Excel, using the year function to apply a 7% discount for 2015–2016 purchases over 8000 units.

learn to build a nested if with or in excel to apply quantity and city based discounts, computing discounted value with either a percentage or a fixed amount.

Learn to apply the Excel and function to identify transactions that meet two criteria—Hamburg city and b2b channel—by building simple logical tests.

Identify June 2018 transactions in Rome on the B2C channel by applying the and function to test four criteria using the year and month functions.

Explore logic in Excel with the and and or functions to compare shift types across three shops, determining dates with all three matches or any two.

Use an Excel if and and formula to apply an 8% discount on B2B sales when dates fall between April 14, 2014 and January 27, 2017.

Master a nested if with and in Excel to apply date threshold discounts by city and channel, May 25, 2014, using Boston B2B 12%, Hamburg 11%, Madrid 10%, Doha 9%.

Master Excel logic functions by building a double if with nested and and or to apply date-based discounts for city and channel combinations and calculate discounted values.

Explain nesting if with and or inside an if to compute discounts for 2013–2016 transactions using year, applying 5% Americano B2B/wholesale, 6% Boston and Madrid Espresso, and 5% London Doppio.

Master subtotal and aggregate, and learn mathematical and logical functions like sum, ifs, average, and mean, plus name manager and sumproduct for multiply, filter, and sum data in a cell.

Learn how to use subtotal and aggregate functions in Excel to compute quarterly totals and annual sales, with practical steps and examples on ignoring nested subtotals and hidden rows.

Learn to use Excel's subtotal and aggregate functions to calculate weekly and monthly total sales, apply filters, and ignore nested subtotals and aggregate functions for accurate ranges.

Master subtotal and aggregate functions to compute totals and averages on filtered Excel tables, understanding how hidden rows affect results and how to filter, group, and manage visible values.

Master subtotal and aggregate functions to compute cumulative total sales for a filtered table, ignoring hidden rows. Use a B2:H2 range and apply ctrl+shift+l to see sums for visible data.

Learn to apply Excel rounding functions—round, roundup, and round down—to format value usd data with one digit after the comma, using mathematical rounding and cell references.

Master the sumifs function to total the quantity by city in Excel, using criteria range and criteria, demonstrated with Boston, resulting in 3,645,229 units sold.

Explore how to use the sumifs function to total sales quantity by city, setting the quantity range and city criteria, filling down formulas, and verifying totals.

Compute total quantity sold per city and product using sum_range and criteria_range selections on a horizontally structured table; learn how to handle absolute versus relative references and merged cells.

Learn to use name manager in Excel to define names like price, apply them in formulas, and understand workbook scope and absolute references.

Name manager enables you to define names for months and products from selection, then use sum and average to calculate total sales.

Use sumifs with name manager to total value fc by product, currency code, and channel, using defined names created from the top row.

Use the averageifs function with named ranges from the name manager to calculate average sales quantity per city and channel. Define average range and criteria ranges to drive the calculation.

Use the name manager to create a year named range, then apply averageifs to compute average rates by currency and year using year on the date column.

Master the countifs function to count transactions by year and payment method using defined names. Build multi-criteria formulas with criteria ranges and absolute references, then fill results across the table.

Learn to use name manager, minifs, and maxifs to calculate minimum and maximum quantities per city in an Excel table, with step-by-step formulas and filling down.

Master minifs to calculate minimum quantities by city, payment method, and year, excluding steel products, using defined names, absolute references, and > & < symbols.

Learn to use sumifs and maxifs in Excel to calculate quantity by city, product, channel, and payment method with defined names, then determine max quantities per channel and payment method.

Learn to use sumproduct to total the results of multiplying quantity, price FC, and exchange rate, converting to local currency in Excel.

Explore how to use sumproduct to multiply quantity, price fc, and exchange rate, then sum by channel. Learn how to apply a true-false test for channel equals retail inside sumproduct.

Learn to use the sumproduct function to calculate total sales value in local currency by city and channel, filtering out plastic products by multiplying quantity, price, and exchange rate.

Define named ranges from the two-worksheet payroll table and apply sumproduct with the department and actual values to compute total actual payroll by department using the name manager.

Explore advanced logical functions such as LAMBDA, SCAN, SWITCH, LET, and IFS, and learn how to use form controls with these functions.

This lesson demonstrates using sumproduct with the exact function to sum quantities by city and channel while enforcing case sensitivity, and compares results with sumifs.

Learn to use the iferror function in Excel to handle dividing value USD by the exchange rate and return no exchange rate when errors occur.

Learn to use the IFS function in Excel 2016+ to assign city discounts and apply IFERROR to return 0% for nonmatching entries.

Learn to use the ifs function in Excel to compute city and channel based discounts, nesting functions inside ifs and handling errors with iferror.

Learn to build an ifs formula in Excel by nesting or and, using the month function, and handling errors with iferror to assign discounts for Boston, Madrid, Rome, and Hamburg.

Explore using the switch function in Excel to map sales channels to discounts—4% for B2C, 7% for B2B, 8% for wholesale, with a 0% default, introduced in Excel 2016.

Learn to use Excel's switch function to determine discounts by channel and product, nesting end functions for multiple cases and a default zero.

learn to implement an Excel lambda function named net of VAT that takes gross value and calculates net value after 20% VAT, applying it across a table.

Define a lambda function named net of VAT rate to compute net value by dividing gross value by one plus VAT rate, then apply it to table 15% VAT.

Learn to build a lambda function in Excel to compute payroll net using amount and pension fund flag, deducting 20% income tax and 5% pension fund when applicable.

Learn to use Excel's scan and lambda with an if condition to compute each employee's net salary (80% of gross over 8000, otherwise full amount) and sum total net payroll.

Learn to extract numbers from text in the id or passport column using reduce, lambda, sequence, substitute, and char, with trim to remove spaces.

Apply let, sumifs, and ifs to compute total quantity by city and channel and display a 1–5 star rating in a report, using a star-based visualization.

Apply let with total quantity and total value per employee to calculate per-transaction bonuses using sumifs, ifs, and iferror with 3%, 2%, or 1% rules.

Learn to use a form control checkbox to toggle 18% vat on sales value by linking the checkbox to a cell and applying an IF formula to quantity times price.

Learn to use checkbox form controls in Excel to apply income tax, social tax, and military tax to net salary, calculating gross salary with an IF-based formula.

Master dynamic Excel formulas with let, sumifs, and ifs, plus a form control checkbox to toggle star ratings versus numbers by city, channel, and quantity.

Apply vlookup and hlookup to pull prices and exchange rates from supporting tables, using exact matches and absolute references, then fill down to complete the price fc and rate columns.

Explore how to use vlookup and hlookup to pull prices and exchange rates from supporting tables, adjust for column positions, and handle horizontal lookups for currencies.

Learn to use Excel's lookup to extract the last transaction from a sales table, retrieving employee, city, channel, and value via a lookup vector and a corresponding result vector.

Explore how to use lookup functions in Excel to retrieve Matilda Smith's last transaction details, including city, channel, and amount, by constructing robust one divided by condition expressions.

Learn to apply lookup functions using match and vlookup to rank students by final percentage and retrieve their final percentage values and grades from a sorted table.

Explore how to use lookup functions and the match function to determine a factory's quality rank for each product, using a table and exact-match settings.

Master index and match for Excel lookups to extract student names, final percentages, and grades from a two-table setup, using a single formula to return every fifth student by rank.

Master Excel techniques with index and match to look up prices from cash or bank tables by product and channel, using an if condition to choose the correct table.

Learn how to fetch product prices and currency exchange rates with the XLOOKUP function in Excel 2021, including alternatives such as index and match or VLOOKUP/HLOOKUP for earlier versions.

Apply xlookup with a spill operation to pull price, currency, and rate by city from a supporting table, filling three result columns and propagating down the sheet.

Create a payment date with xlookup against a period table (day, week, month), then extract the term and quantity with left/right, multiply, and add to the transaction date.

Learn to use xlookup to fetch prices from a supporting table by product and channel, combining fields into a lookup value and matching against a concatenated lookup array.

Learn how to use xlookup to perform lookup functions and map project codes by month to employee names, solving the May 2020 lookup and dynamic updates.

Learn to use the indirect and address functions in Excel to perform lookups, converting textual addresses like B1 into actual country names from row one.

Learn to pull monthly totals across multiple worksheets by building dynamic references with the address and indirect functions, targeting cell L14 on each Jan–Jun sheet.

Combine indirect and address functions to pull total sales by product and month from January through June sheets, using row to derive dynamic references to column L.

Master Excel lookups by nesting the address function inside indirect to pull monthly country totals from multiple worksheets. Use row to map columns and create dynamic references.

Learn to dynamically populate a sales table by product and country using a month selector combo box linked to six monthly worksheets, via indirect and address lookup functions.

Learn how to use the offset function to retrieve a value from a table by specifying a reference cell and row and column offsets, with examples returning 758 and 580.

Explore combining sum and offset to build a dynamic range from the quantity column and calculate the total quantity for the first N transactions, where N is defined in H7.

Apply lookup functions with offset and match to compute a total value over a country and date range. Use match to locate dates and countries, then offset and sum.

Discover how to compute quarterly totals in excel using sum and offset, creating dynamic ranges across months and products with absolute and relative references.