Learn Advanced Excel Formulas taught by Googler

This lecture covers Excel's SUM, SUMIF, and SUMIFS functions for basic addition and conditional summing with single or multiple criteria.

This lecture will equip you with a comprehensive understanding of Excel's core averaging functions: AVERAGE, AVERAGEIF, and AVERAGEIFS. You'll learn to calculate simple means with AVERAGE, apply single-condition averages using AVERAGEIF, and tackle complex, multi-criteria averaging with AVERAGEIFS. We'll cover syntax, practical examples, and common pitfalls to ensure you can confidently derive meaningful insights from your data. Get ready to enhance your data analysis skills!

This lecture will teach you how to use Excel's COUNT functions. You'll learn to count all numbers with COUNT, count based on one condition with COUNTIF, and count with multiple conditions using COUNTIFS. We'll cover syntax, practical examples, and essential tips for accurate data analysis.

The COUNTA function in Excel is a fundamental statistical function that counts the number of cells in a range that are not empty. Unlike COUNT, which only counts numerical values, COUNTA counts any cell that contains any type of data, including numbers, text, logical values (TRUE/FALSE), error values, and even empty text strings ("") resulting from formulas.

This lecture will guide you through the powerful world of conditional logic in Excel, focusing on the IF function and its advanced applications. You'll learn to make your spreadsheets perform different actions based on specific conditions.

The IFERROR function in Excel is designed to catch and handle errors that might occur in your formulas. Instead of displaying a standard error message (like #DIV/0!, #N/A, #VALUE!, etc.), IFERROR

The IFNA function is a specialized error-handling function that allows you to specify a custom value to return only when a formula results in an #N/A error. It's a more targeted alternative to IFERROR

The IFS function (available in Excel 2019, Excel for Microsoft 365, and Google Sheets) is a modern and powerful alternative to complex Nested IF statements. It allows you to test multiple conditions and return a value corresponding to the first condition that evaluates to TRUE, without nesting IF functions inside each other.

Learn about Length formula end to end

Learn about Trim formula end to end

Learn about MID Text to Columns formula end to end

Learn about LOWERCASE formula end to end

Learn about PROPER CASE formula end to end

Learn about TEXT formula end to end

Learn about SUBSTITUTE formula end to end

Learn about REPT formula end to end

Master Excel’s SEARCH and FIND functions to locate text within strings, enabling precise data extraction and improved text analysis in your sheets.

Master Excel’s ROUND function to round numbers to a specified number of digits, balancing accuracy and simplicity in your data calculations.

Master Excel’s ROUNDUP function to round numbers up to a specified digit, helping you control precision and avoid underestimation in calculations.

Master Excel’s ABS function to return the absolute value of a number, helping you handle positive values in calculations and data analysis.

Master Excel’s SQRT function to calculate the square root of numbers, essential for mathematical, statistical, and engineering calculations.

Master Excel’s RAND function to generate random decimal numbers between 0 and 1, perfect for simulations, random sampling, and probabilistic models.

Master Excel’s POWER function to raise numbers to a specified exponent, enabling advanced calculations in finance, science, and data analysis.

Master Excel’s RANDBETWEEN function to quickly generate random numbers between two values, ideal for simulations, testing, and data sampling.

Master Excel’s CEILING and FLOOR functions to round numbers up or down to the nearest specified multiple, helping control precision and simplify calculations.

Master Excel’s MOD function to find the remainder after division, useful for patterns, cyclic calculations, and solving numeric problems efficiently.

Master Excel’s OR operator to check if any condition is TRUE, helping you build flexible formulas for complex decision-making and data analysis.

Master Excel’s AND operator to check if any condition is TRUE, helping you build flexible formulas for complex decision-making and data analysis.

Master Excel’s NOT operator to reverse logical values, turning TRUE to FALSE and vice versa, enhancing your formula logic and decision-making.

Master Excel’s XOR operator to return TRUE when an odd number of conditions are TRUE, helping you create advanced logical tests and decision rules.

Master Excel’s CHOOSE function to select values from a list based on an index number, allowing flexible decision-making and dynamic formula building.

Master Excel’s HLOOKUP function to search for values horizontally across a row, helping you retrieve data quickly in wide tables and reports.

Master Excel’s VLOOKUP function to search vertically in tables, enabling fast data retrieval and simplified lookup tasks across your spreadsheets.

Master Excel’s XLOOKUP function for versatile, efficient lookups. Replace VLOOKUP/HLOOKUP with dynamic, easy-to-use searches across rows and columns.

Master Excel’s MATCH function to find the position of a value within a range, enabling dynamic lookups and smarter data retrieval in your sheets.

Master Excel’s INDEX with MATCH functions to perform powerful, flexible lookups and retrieve data dynamically across rows and columns with precision.

Master Excel’s INDIRECT function to create dynamic references from text, enabling flexible formulas that adjust based on input or sheet structure changes.

Master Excel’s OFFSET function to create dynamic ranges by shifting a reference, enabling flexible data analysis and advanced formula building.

Master Excel’s ROW and COLUMN functions to find the row or column number of a cell, helping you build dynamic formulas and navigate spreadsheets efficiently.

Learn about ADDRESS function

Master Excel’s ROWS and COLUMNS functions to count the number of rows or columns in a range, aiding dynamic formula creation and data analysis.

Master Excel’s LEFT and RIGHT functions to extract specific characters from text, enabling efficient data cleaning and text manipulation.

Master Excel’s ROUNDDOWN function to round numbers down to a specified number of digits, helping you control precision and simplify calculations.

Master Excel’s DATE and TODAY functions to create dates and get the current date dynamically, improving time-based data analysis and automation.

Master Excel’s DATETIME and NOW functions to work with current date and time values dynamically, enabling real-time data tracking and time calculations.

Master Excel’s DATEDIF function to calculate the difference between two dates in days, months, or years for precise age, duration, or timeline analysis.

Master Excel’s EDATE function to add or subtract months from a date, simplifying future or past date calculations for planning and scheduling.

Master Excel’s EOMONTH function to find the last day of a month, helping you with financial, billing, and date calculations efficiently.

Master Excel’s NETWORKDAYS function to calculate working days between dates, excluding weekends and holidays, for accurate project and time management.

Learn about WORKDAY which is essential when planning for projects

Master Excel’s DAY, MONTH, and YEAR functions to extract specific date parts, helping you analyze and manipulate dates with ease and accuracy.

Master Excel’s HOUR, MINUTE, and SECOND functions to extract time components from timestamps, enabling precise time analysis and calculations.

Master Excel’s WEEKDAY and WEEKNUM functions to extract the day of the week and week number from dates, enhancing your time-based data analysis.

Learn to use Excel’s CHAR function to convert numbers into characters, helping you work with ASCII codes and create custom text outputs easily.

Master Excel’s CONCAT function to join text from multiple cells quickly, streamlining data merging without extra delimiters or complex formulas.

Learn to use Excel’s CODE function to find the numeric ASCII code of a character, aiding in data analysis, text processing, and custom formula creation.

Master Excel’s TEXTJOIN function to combine text from multiple cells with custom delimiters, simplifying data consolidation and reporting tasks.

Learn to use Excel’s VALUE function to convert text that appears as numbers into actual numeric values for accurate calculations and data analysis.

Learn to use Excel’s T function to extract text from a value, helping you handle mixed data types and clean your spreadsheets effectively.

Master Excel’s Normal Filter and FILTER formula to quickly display data subsets. Learn interactive filtering and dynamic formula-based filtering techniques.

Learn to use UNIQUE formula, Remove Duplicates tool, and Highlight Duplicates in Excel to identify, clean, and visualize duplicate data efficiently.

Learn the difference between Excel’s SORT formula and manual sorting to dynamically organize data with formulas or use traditional static sorting methods.

Learn the difference between Multiple Sorts and the SORTBY function in Excel to organize data efficiently using built-in tools or dynamic formulas.

Learn to use Excel’s SEQUENCE function to quickly generate lists of numbers or dates, simplifying data entry and enhancing dynamic formula creation.

Learn to use Excel’s TRANSPOSE function to switch rows to columns (or vice versa), making it easy to reorganize and reshape your data efficiently.

Learn to use Excel’s MMULT function to perform matrix multiplication, enabling advanced calculations and data analysis in your spreadsheets.

Learn to use Excel’s ARRAYFORMULA to apply formulas to entire ranges at once, boosting efficiency and automating repetitive calculations.