Master Microsoft Excel: Advanced Excel Formulas & Functions

Learn to sort data in Excel using the data tab, with alphabetical or numerical orders in ascending or descending. Master basic sorting, including sorting by numbers or values.

Sort an Excel worksheet by any column using a single cell, then apply ascending or descending order in the data tab to sort first names, last names, or numbers.

Learn basic filtering in Excel by ensuring a header row and using the Data tab filter with drop-down arrows to filter by ID or product name.

Apply multiple filters in Excel to narrow results across columns, selecting specific products, suppliers, and price, and clear filters with the funnel icons.

Learn to clear and remove filters in Excel, including price, suppliers, and product name, by selecting cells, clicking the filter icon, and choosing clear filter to reveal all data.

Learn to perform advanced filtering in Excel using the search within the filter menu. Apply data filters from the data tab, select headers, and narrow results by supplier or product.

Master essential advanced text filters in Excel to refine data using equals, begins with, ends with, contains, and does not contain criteria, and combine conditions with and/or logic.

Master advanced number filters in Excel to filter by price or quantity using the number filters dialog, with equal, greater than, and/or conditions.

Master advanced date filters in Excel to view data by year, month, and day, apply between dates, last year, this year, and other date-based criteria.

Learn how to reference specific cells and ranges, compare absolute and relative references, understand fixed and dynamic references for flexible formulas, and distinguish formulas from functions for powerful Excel calculations.

Explore the difference between a single cell reference like A1 and a multi-cell range such as A1:D5, and learn how to identify, reference, and name cell ranges in Excel.

Master named ranges in Excel to simplify formulas and reference cells by descriptive names. Use sum with named ranges and manage names with define name and name manager.

Explore relative cell references in Excel, where formulas adjust automatically when copied, as shown by adding A1 and B1 to populate C1, with default references being relative.

Learn how to use absolute cell references in Excel to lock a cell in formulas, ensuring constants like tax rates stay fixed when copying calculations.

Explore how formulas offer customizable calculations using cell references and operators, while functions provide predefined operations with specific syntax and arguments like the sum function.

Master Excel formula syntax by starting with the equal sign, using cell references like A2 and B2, applying built-in functions such as sum and Vlookup, and Pemdas.

Explore case sensitivity in Excel formulas, noting case-insensitive cell references and function names, the role of required and optional arguments plus how functions return values with pi, rand, randbetween, sum.

Explore how to use arithmetic operators in Excel, including addition, subtraction, division, multiplication, and modulus, alongside comparison and logical operators to build boolean conditions.

Explore how Excel comparison operators return boolean values (true or false) and apply =, >, >=, <, <=, and not equal with example data like A1 and B1.

Explore logical operators in Excel, including and, or, and not, and learn to create conditional statements using these functions.

Use the Excel function wizard to insert functions and arguments, exploring concat, textjoin, sum, average, round, and ifs, with hands-on practice on student data and grades.

Master Excel's formula text function to display a cell's formula as text, using a single reference cell to reveal the exact formula and its arguments, illustrated with sum and totals.

Master the sum function in Excel to add ranges or multiple arguments up to 255 inputs, using numbers, cell references, ranges, or named ranges, while ignoring text and blank cells.

Master the max function in Excel to identify the largest value across numbers or ranges, while ignoring blanks and text. Apply it to financial analysis, data comparison, and performance tracking.

Master the countblank function to count truly empty cells and zero-length strings within a range, like a1:a10, and note it ignores spaces or formula-empty results.

Explore Excel average function to compute arithmetic mean by summing values and dividing by count, ignoring text and blanks, including zero values, and supporting up to 255 arguments across ranges.

Use the product function in Excel to multiply numbers and ranges, up to 255 arguments. It ignores text and blanks, and returns zero if any value is zero.

Use the round function to round a number to a specified number of decimal places. Apply standard rounding for positive and negative values, nearest integer, financial calculations and data presentation.

Master the round up function in Excel, which increases a number to the next higher value by a set decimal place, with uses in financial calculations, pricing, and time tracking.

Understand the round down function in Excel, which always rounds down to chosen decimal places, with zero digits yielding a whole number, and contrast it with round and round up.

Learn how the trunc function in Excel removes the decimal portion without rounding, preserves the sign, and works with positive and negative numbers, with a second argument to keep decimals.

Learn the power function in Excel to raise a base to a given exponent, with examples like two cubed and five square, and notes on negative and fractional exponents.

master the large function in Excel to return the kth largest value from a range or array, ignoring blanks and text and handling both positive and negative numbers for ranking.

Master the small function in Excel to return the nth smallest value from a range, ignoring blanks, and use it to identify ranked values, including outliers.

Explore how rand and round functions generate random decimal numbers from 0 to 1, extend to 0–10 or 0–100, and update on worksheet recalculation for simulation, sampling, and test data.

Explore the randbetween function in Excel to generate a random integer between two specified whole numbers, including both bounds, and use it for sampling, simulation, and test data.

Master the ABS function in Excel to convert negative numbers to their absolute values, while keeping positives unchanged, demonstrated with syntax and practical examples.

Use the base function in Excel to convert numbers between bases, from decimal to binary, using radix 2-36 and an optional min length, returning a text string.

Learn to convert numbers to roman numerals using the roman function and revert roman numerals to numbers with the arabic function in Excel, including form options and practical examples.

Learn the square root function in Excel (sqrt) and its syntax, see 16 yields 4, and use auto fill and formula text to display the function.

Master the subtotal function in Excel to return subtotals in a list or database, excluding hidden rows and other subtotals, using function numbers 1–11 and 101–111.

Learn how the sum product function in Excel multiplies corresponding elements in arrays and sums the results to compute the grand total, using price and quantity examples.

Explore conditional statements and logical operators in Excel to automate decisions, using if, nested if, and, or, not, IFS, and switch functions for real-world spreadsheet problems.

Use the if function as the core of conditional logic in Excel, returning true or false based on a condition such as A1 > 75 or age ≥ 18.

Learn how to replace nested ifs with the IFS function to evaluate multiple conditions, using syntax, examples, and true values in Excel.

Master the switch function in Excel to map values to results using a list of expressions, with a default option, illustrated by region names and grade remarks.

Explore advanced conditional statements and logical operators in Excel, mastering if, nested if, and, or, not, ifs, and switch to build dynamic, real-world spreadsheet solutions.

Learn how the and function returns true only when all specified conditions are true, using syntax =and(condition1, condition2, ...). See examples checking A1>5 and B1<10, with outcomes depending on values.

Learn how the or function evaluates multiple conditions to return true when any condition is met, with examples like A1 > 15 or B1 < 0 in Excel.

Master the not function in excel to reverse logical outcomes, using =not(logical). It turns true to false and false to true, and applies to logical tests for advanced tasks.

Learn to combine logical operators in Excel using the if function with the and operator and parentheses to decide who is allowed to enter, based on age and paid.

Explore conditional statistical functions in Excel, including sum if, sum ifs, count if, count ifs, and average if, average ifs, and learn single versus multiple conditions.

Master the countif function in Excel by counting cells with a specific range and criteria, including using greater than 50 and dynamic cell references for items like tablets and products.

Learn how to use the countifs function to count items across multiple ranges with multiple criteria, using dynamic criteria and real-world examples like cameras by Sony.

Learn how the sum if function adds values in a range based on a condition, using criteria such as greater than 50 or less than 50 in Excel.

Learn how the sumifs function sums a price range based on multiple criteria, using dynamic criteria cells for product and supplier to calculate totals, such as Samsung camera totaling $608.

Learn to use the average if function to compute the mean of values meeting a single condition, using range, criteria, and optional average range, with a practical example.

Use the averageifs function in Excel to calculate the average cost with multiple criteria across price, product, and supplier ranges, demonstrated with Samsung cameras and dynamic criteria.

Learn how to use text functions in Excel to change the case of strings with upper, lower, and proper functions, and apply them to data cleaning and formatting tasks.

Discover how to join text in excel using concat/concatenate and textjoin, apply delimiters like spaces and commas, ignore empty cells, and build full names from first, middle, and last names.

Discover how to convert numbers to text and text to numbers in Excel using Text and Value, with formats like leading zeros and currency.

Discover how to extract characters from text strings in excel using the left, mid, and right functions, with practical examples from hello world and varying start positions.

Use the len function in excel to get the length of a text string, counting all characters including spaces, with examples like hello world and banana.

Master Excel teaches cleaning text data with trim and clean, showing how trim removes leading, trailing, and extra spaces while clean eliminates non-printable characters.

Explore Excel's replace and substitute functions to modify text strings, replacing substrings and using wildcards. Learn to specify old text, start number, and number of characters with replacement text.

Apply the exact function to compare two text strings in Excel, perform case sensitive matching, and return true or false based on exactness.

Learn how to use the repeat function in Excel to repeat a text string a specified number of times, understand the syntax, and see practical examples with asterisks.

Find text in Excel using the find function to return the starting position of a match within another string. It is case sensitive and accepts the optional start number argument.

Explore how the search function finds text within another and returns the starting position with case-insensitive matching, including its syntax (find_text, within_text, start_num).

Explore Excel's info function to retrieve environment details like directory, OS version, release, system, and worksheet counts, using different info types and live recalculation examples.

Learn to create clickable links in Excel using the HYPERLINK function by specifying the link location and optional friendly name. Display the linked text and test clicks to open urls.

Learn to count words in sentences using Excel formulas by combining the length, substitute, and trim functions for text analysis, data validation, and text cleaning.

Learn to extract the username and domain from email addresses in Excel using text functions like left, right, len, find, and search, as part of the email parser project.

Explore EAS functions in Excel: is even, is odd, is blank, is number, is text, is logical, and is error, and how they return booleans for formatting and validation.

Explore how to use the ISEVEN and ISODD functions in Excel to check whether a number is even or odd. Learn the syntax, true/false results, and practice with examples.

Use the isblank function to check if a cell is empty in Excel. Understand true or false results and that a formula yielding an empty string is not blank.

Explore how the islogical function in Excel identifies boolean values, returning true for booleans and false for non-booleans, with practical examples using cells, formulas, and strings.

Learn how to identify text and numbers in Excel using ISTEXT, ISNUMBER, and their non-text counterparts, with practical examples and formula text insights.

Learn how to use isna, iserr, and iserror to identify specific and any Excel errors, including lookup errors, with practical examples in Vlookup, Hlookup, and division by zero.