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.

Master the fill down operation and relative references in Excel to calculate transaction values. Multiply quantity by price using B2 and C2, then fill down.

Master excel's fill down and relative references to calculate transaction values with formulas across two tables, using b2 times f6 and auto fill that updates when inputs change.

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.

Explore relative references and field operations in Excel by constructing a price table from revenue and quantity, then identify and mark three mistakes across shops.

Master fill down and absolute versus relative references in Excel formulas. Lock price cell with dollar signs while applying the same formula across rows for values A, B, and C.

This tutorial explains fill operations and absolute vs relative references in Excel, showing how to lock a column with a dollar sign when copying a formula across a row.

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 use absolute and relative references in Excel to compute euro values from quantities, price, and exchange rate, and master fill-down with $ signs and F4 toggling.

Explore absolute and relative references with $ signs to fix B6 while copying formulas across three vertical tables, using F4 to apply quantity and discount calculation on row ten.

Learn how absolute and relative references work in Excel by calculating sales values, multiplying quantities by prices, and using $ signs when filling formulas across and down.

apply absolute and relative references in excel by multiplying quantities by prices across dates and channels, using dollar signs to lock references.

Apply Excel fill to compute discounted sales using absolute and relative references, multiplying quantity by (price minus discount) and locking key cells with dollar signs.

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 calculate monthly total sales with the sum function and average per product using absolute and relative references, and automate results with Excel’s fill handle and the Alt+Equals shortcut.

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.

Learn to perform a sum across multiple worksheets in Excel by selecting all four supporting worksheets, writing a single formula for January totals, and filling across the table.

Select multiple worksheets simultaneously to apply the average formula across B2:M2 into N2 on all supporting sheets, then fill down to propagate the results.

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.

Master cross-worksheet calculations by comparing each channel's monthly total to the across-sheets average, using a single formula on all four supporting worksheets and returning results on row 15.

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.

Apply count, counta, and countblank to identify available transactions (not blocked or deleted), deleted rows, and blocked entries in a 2016–2017 sales table using Excel formulas.

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.

This tutorial demonstrates using the counta function with references to multiple supporting worksheets to count active employee days by payment method and calculate the average sales per active day.

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.

Use the count function in Excel to tally days a store has more than one employee by counting numeric cells in the shifts table.

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.

Compute daily total employees using sum and counta, subtract the count of numeric cells, and paste special values before sorting the table from largest to smallest.

Compute the minimum and maximum values across the retail sales table, apply min and max formulas to monthly ranges, then sum monthly minima and average product maxima.

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.

Learn to combine numbers and text in Excel using sum and average inside concatenate and the & symbol. Build nested functions for row totals and monthly averages.

Use left and right functions in Excel to extract the clinic code (first three symbols) and the ticket code (last eight symbols) from a visit code, with practical formulas.

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.

Learn to build Excel email addresses using the left function and the & operator, combining first and last names with a domain like sky ambulance.com.

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.

Demonstrate nesting the right and len functions in Excel to extract a password from the employee key, using the password mask length to determine the number of characters.

Master splitting full names into first and last names in Excel using left, right, len, and find, by locating the space symbol and creating robust formulas.

Learn how to split a patient identification string into first name, last name, and identification number using mid and find functions in Excel, with colon, space, and dash as delimiters.

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.

This tutorial shows using Excel's replace function to update visit codes by replacing four-digit patient numbers with six-digit ones, using C3 as original text and D3 as new text.

Update visit codes in Excel by using replace and right functions to convert four-digit patient numbers to six-digit ones, applying the formula on yellow cells.

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 replace dashes with slashes in visit codes using Excel's substitute function, including handling three parts and optional instance numbers for precise text manipulation during data transfer.

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.

Count accepted offers in a sales table by using len and substitute to count plus signs, fill the formula across the table, then sum to get the total.

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.

Master Excel text manipulation by using trim to clean spaces, substitute to remove dashes, and concatenate or & to assemble first name, last name, and a dash-free identification number.

Master Excel techniques to remove identification numbers from text using substitute and trim, leaving only the first and last names with a clean single space.

Learn to extract an email address from a delimited text in Excel using substitute, find, and mid to locate the ninth and tenth dash.

Explore Excel text functions such as formula, text, concat, and text join, plus text to column and flashfill tools, and learn to handle number values stored as text.

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 generate repeated symbols in Excel using the REPT function by repeating the star character according to the numbers in column B, automating the creation of star ratings.

Learn to repeat the star symbol in Excel with the rept function and count ones across monthly data using count, then display the result in column n.

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 combine text from different cells into one cell using the concat function in Excel, replacing concatenate, and apply it to the range A3:G3 with a single formula.

Learn how to use Excel's text join to combine multiple cells into one with a slash delimiter, including handling ranges like A3:G3 and ignoring empties.

Use the textjoin function to combine employee fields into a single cell with a slash delimiter, choosing to ignore or include empty cells in Excel 2019 and later.

Split a full name into first and last names using the text to columns tool from the data menu, choosing space as the delimiter.

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.

Master excel and solve 500 problems: learn to split data with text to columns, choose delimited, handle slash delimiters, and leave consecutive delimiters unchecked to preserve missing data.

Split employee data by using the text to columns tool, handling double slashes and consecutive delimiters as one to correctly separate first name, last name, birth date, and identification number.

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.

Use Excel's text to columns to split a column into multiple columns by slash, handling consecutive delimiters, starting in column D and skipping date and country fields.

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.

Convert numbers stored as text to numbers using Excel's convert to number tool, then use the sum function to total the quantity column.

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.

Extract numbers from formulas with formula text, mid, find, and value to compute the sales returns quantity. Then use the sum function to obtain the total sales returns quantity.

Learn to compute sales in Excel by converting formulas to text with formula text, extracting values via mid and find, converting with value, multiplying by five and three and summing.

Explore eight date functions like day, month, year, and date, and calculate salaries by working days in a month. Learn date entry in Excel cells in the first three tutorials.

Format dates correctly in Excel, understand that dates are stored as numbers, convert serial numbers to dates, and use a formula to add 90 days for probation end dates.

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.

Combine day, month, and year into a date using Excel's date function by referencing year, month, and day cells, and avoid text joins to preserve proper date formatting.

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.

Calculate the number of calendar days an employee worked during their hire month using date calculations, start of next month minus one, and end-of-month logic.

Extract day, month, and year from a date using Excel's day, month, and year functions, then fill down to split the hire date into separate columns.

Learn to create a 2023 renewal date in Excel by using the hire date's month and day with year 2023, via the DATE, MONTH, and DAY functions, and fill down.

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.

Calculate probation end dates in Excel by adding 30 days to hire date, then derive Sunday of the week with weekday return type 2, displaying the result in column E.

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.

Master workday.intl and concat to calculate contract end dates by counting working days, applying per-employee schedules, and auto-filling results.

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.

Learn to use Excel comparison operators to compare values across columns, producing true or false results and applying them to questions like shop quantity comparisons and shift matches.

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

Learn how to use the if function to award bonuses based on combined shop quantities exceeding 10,000, summing shop values, and calculating 5% of the total; handle true/false outcomes.

Compare two similarly structured tables in Excel using logical comparison, convert true/false to 1/0, and sum the results to count differences, revealing five discrepancies.

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.

Craft a nested if formula, i.e., a double if, to assign discounts by channel: B2C 2%, B2B 5%, wholesale 7%, retail no discount.

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 the OR function in Excel to flag potentially incorrect transactions by checking if city is Boston, channel is B2C, or currency is euro, and apply the formula across rows.

Learn to use nested if statements with or to assign discount percentages by city, applying 4% for Boston, Madrid, and London and 0.5% for other cities in Excel.

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.

Use nested ifs and or to assign city discounts: 1% for Boston, Madrid, Hamburg; 4% for Rome and London; 0.5% for others, and apply the discount to USD values.

Apply a 7% discount to transactions from 2015 or 2016 using if, or, and year functions in excel, yielding the discounted value or keeping the original for other years.

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.

Master Excel demonstrates nesting the if function with and to identify transactions that meet channel retail, product not espresso, and quantity above 7500, yielding a $500 bonus.

Learn to nest a function inside an if statement with and to evaluate date after 25 May 2015, channel B2B, product espresso, and quantity above 5000, returning 300 or 0.

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.

Learn to nest and or inside an if function to calculate discounts from date criteria, using year and month checks to return 7% or zero for discounted values.

Apply double if with and in Excel to compute conditional discounts from city, product, and channel. Boston wholesale yields 12% and Madrid Americano with B2B yields 11%, otherwise 0%.

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.

Explore using nested if with and/or and year in Excel to compute discounts: 5% for Boston or Hamburg in B2B, 6% for Rome or Madrid Wholesale, 3% otherwise (Doha rule).

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.

Apply nested if, or and to compute year-based discounts in a table. In 2013, apply 4% unless Rome B2C or Madrid retail yields 0%, otherwise no discounts.

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.

Apply Excel's large and small functions to extract the top five and bottom five salaries from a salary table, using an array range, k values, and absolute references.

Learn how to use rank.average and rank.eq in Excel to count how many employees have salaries higher than a given amount, from two salary tables, exploring ties and absolute references.

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.

Learn to use the sumifs function to calculate total sales quantity by channel (retail, B2B, B2C, wholesale), fix ranges with dollar signs, and apply channel criteria.

Learn to build a two-criteria sumifs formula to compute total sales quantity by city and channel, and apply absolute references to reliably fill across the table.

Master sumifs with references on another worksheet by summing quantity per city and payment method, using absolute references and multi-criteria criteria ranges in Excel.

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.

Learn to build an averageifs formula with defined names to compute average sales quantity by city and payment method for a date range, using correct date handling and absolute references.

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.

Master Excel's countifs to count transactions by channel and city where quantity exceeds 8000, using defined names, absolute/relative references, and quoted comparisons.

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 maxifs with defined names to calculate the maximum quantity by year and channel, for transactions with payment terms ending in weeks, including wildcard criteria.

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.

master sumproduct by multiplying quantity by FC price and summing the results to get the total sales value, then compare this with the manual quantity times price approach.

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.

Use name manager and the sumproduct function to calculate actual minus budget by payroll element code. Leverage defined names to perform row-wise calculations and verify totals across all payroll elements.

Learn to use sumproduct with the name manager to compute actual minus budget payroll by department and gender, applying defined names and absolute references for precise aggregation.

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

Use Excel's exact and proper functions to enforce case-sensitive comparisons and format checks, ensuring names are in proper format (capitalized first letters) across the employee table.

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 compute sales price in Excel by dividing value by quantity and use iferror to return the value column when division by zero occurs.

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 complex logical formulas with IFS, OR, and IFERROR to apply 2% discounts to Americano, Doppio, and Irish Coffee, and handle errors for other items.

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 use Excel's switch function to assign discount percentages by product, mapping Americano, ristretto, and espresso to 5%, doppio and macchiato to 3%, and flat white to 8%.

Learn to use the switch function with nested and or logic in Excel to assign discount percentages (1%, 5%, 11%, 0%) for B2C/B2B products like Americano and Affogato.

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.

Develop a lambda function using ifs and iferror to calculate bonus amount USD from channel, quantity, and value USD, and apply it to the historical sales table.

Explore using scan with lambda to compute cumulative totals in Excel, and compare it with the mathematical operation, sum, and scan-lambda approaches.

Learn to compute net salaries in a payroll table with scan and lambda in Excel, applying an if rule to keep 80% of gross for wages above 8000.

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 apply scan with lambda to compute a running total, and use reduce with lambda to get the final value, understanding the difference between final and cumulative results in Excel.

Use reduce and lambda in Excel to strip digits 0–9 from text with substitute, aided by sequence to generate 0–9, and apply trim to reveal first and last names.

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 a lambda, reduce, and substitute workflow to remove symbols and replace them with spaces in the original text using a symbols array from D8:D13.

Learn how to use Excel's let function to define a total value calculation from sales and apply nested if logic to compute quarterly bonuses, reducing repeated calculations.

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.

Learn to move data between sheets using lookup formulas, from basics like index match to advanced techniques such as x lookup and indirect address offset combined with other functions.

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 how to use excel's lookup function to map final percentages to grades, selecting the closest lower match with lookup vectors and absolute references, and fill down across the sheet.

Master excel lookups by calculating final percentage from the main table and retrieving final grades from the supporting table using a lookup function.

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.

Explore how to use the Excel index function to retrieve student names, final percentages, and grades from a sorted table, and to pull every fifth entry.

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 two-dimensional lookups by nesting match inside index to pull prices by product and channel from a price table, using index and match instead of vlookup.

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 to fetch prices from a supporting table using index and match and xlookup, compare results, and understand a nested xlookup that spills the channel-specific price column.

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 to use lookup functions with indirect, address, and match to pull quantities by product, country, and month across six worksheets, then validate results with sums.

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.

Use offset and sum to compute the total quantity of transactions within a dynamic range in Excel, updating automatically as the defined start and end rows change.

Explore Excel lookup functions using Index and Match, XLOOKUP, and Offset to fetch prices by product and channel from a supporting table; compare results and understand spill behavior.

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.