Mastering Microsoft Excel Formulas and Functions

Explore the universal order of operations, compare Pemdas and Bodmas, and apply parentheses, exponents, division, multiplication, addition, subtraction, and left-to-right rules in Excel formulas.

Mastering Microsoft Excel formulas and functions shows calculating a weighted final grade from assignment, quiz, and final exam scores using order of operations and parentheses with 40%, 20%, 40% weights.

Explore how relative cell references adjust when copying a sum formula across rows, using four quarterly budgets to automatically total each department.

Learn how to use Excel AutoFill to copy formulas with relative references, including dragging and double-clicking to fill down across many rows.

Master absolute cell references in Excel by locking the commission rate in a single cell, demonstrate with a 5% rate in D2, and ensure formulas copy correctly across rows.

Master mixed cell references by locking the column or row with dollar signs, copying formulas across cells, and applying relative and absolute references to a price, quantity, and discount scenario.

Learn how to quickly make cell references absolute in Excel using the F4 shortcut, cycling through absolute, column absolute, row absolute, and relative references to save time.

Explore the basics of excel function syntax, learn how to use the equals sign, function names, and arguments to sum ranges and perform pre-built calculations.

Learn how to quickly insert the sum formula with the Alt + = shortcut in Excel, ensuring the correct range of adjacent cells and adjusting ranges as needed.

Learn the Microsoft Excel mod function, which returns the remainder after division. Use row() with mod to spot even or odd rows and explore nesting functions.

Master rounding numbers with the ROUND() function, specifying the target value and digits to keep, then compare zero-digit rounds vs two-digit precision using practical examples.

Explore how to use the round function in Microsoft Excel to round numbers to the nearest ten or hundred, including using negative digits and practical examples.

Learn to nest Excel functions by placing one function inside another to create single, efficient calculations in one cell, such as sum inside round, and understand the evaluation order.

Learn how to nest Excel functions by combining average, round, and mod in one formula, using a seven-day sales example to analyze rounded averages.

Master nesting in Excel formulas by combining round, sum, and product to calculate a weighted final score, using absolute references and the function arguments window for clarity.

Learn to use the CONCAT function to join text values, such as first and last names, into a single string, and compare it with the older concatenate function.

Mastering Microsoft Excel Formulas and Functions teaches how the text function formats date values as text with a year-month-day format. Explore quotes for format strings and nesting in other functions.

Explore the Excel search() function to locate a substring in text, using search text, target text, and an optional start position; demonstrates locating 'discount' in messages.

Learn how to use the left, right, and mid functions to extract characters from product codes, producing the product prefix, suffix, and batch number.

Learn how to use the Microsoft Excel RIGHT() function to extract the last two characters from a product code by specifying the text and the number of characters.

Use the trim function in Excel to clean up a string by removing extra spaces and non-printable characters.

Learn how the proper function cleans up text by converting a string to proper case, capitalizing the first letter of each word to unify data entry.

Learn how to convert a text string to uppercase using the Excel upper function. Place =upper(B5) in a cell and drag down to apply the conversion across data.

Learn how the substitute function searches a string and replaces specified text with new text, including handling multiple occurrences and the optional instance number, demonstrated with galaxy to star field.

Learn to nest text-based functions in Microsoft Excel to clean and standardize feedback by trimming spaces and applying proper case, using len to compare character counts.

Use the now function to return the current date and time in Excel. Recalculate on each refresh to stay current, unlike a static value.

Format the results of today and now using the text function to customize date and time formats through nesting, including day, abbreviated month, year, and time with seconds and am/pm.

Explore adding days and hours to Excel dates using today and now, treating dates as numeric values and using simple addition to shift dates and times.

Insert a static date or time in Excel using Ctrl+; and Ctrl+Shift+; keyboard shortcuts to prevent today/now from updating, keeping values fixed when opening workbooks.

Use the day function to extract the day from a complete date by feeding it a date’s serial number, then drag to apply to other dates.

Apply the month function in Excel to extract the month from a date or serial number, using a single argument just like the day function.

Learn how to use the year function to extract the year from a date, alongside day and month functions, for sorting, filtering, and grouping.

Learn how the weekday function converts a date to the day of the week in excel, with an optional return type 1–17 and a default sunday start for grouping date data.

Master the eomonth function to find the last day of a month from a start date, with optional month offsets, and format results using text for clear date displays.

Master how the Excel workday function adds workdays to a start date, excludes weekends, and can exclude holidays with absolute references for precise scheduling.

Combine hours, minutes, and seconds with the time function to create a single time value, mirroring the date function approach for sorting and filtering.

Extract the hour from a complete time value using the hour function, with 24-hour examples like 8:00 and 15:00, and explore minutes and seconds alongside day, month, and year functions.

Extract seconds from a time value with the SECOND() function in Excel, building on hour and minute concepts, to aid data analysis by isolating time components and enabling nesting.

Master the art of nesting date-time functions in Excel by combining network days with today and eomonth to calculate the number of working days to the end of the month.

Master the Microsoft Excel if function, powerful logical tool that tests a condition with three arguments and returns pass or fail based on score greater than or equal to 80.

Explore the Excel 2019 iFS function, which replaces nested ifs with a single multi-test formula, handling up to 127 tests and returning the first true result.

Use the and function in Excel to test conditions (up to 255) and return true or false; nest it in an if to output yes or no for bonus eligibility.

Master the Microsoft Excel isnumber function to verify numeric values within datasets, using it with left, right, and mid to validate extracted product codes.

Nest multiple if functions in Excel to assign letter grades from scores. Use thresholds 90, 80, 70, and 60, handling nested arguments.

Explore the Microsoft Excel istext() function to identify text values. Compare it with isnumber, test real examples like C4, and return true for text and false for non-text values.

Master nesting excel logical functions to categorize stock levels by text or numeric values, using is number, is text, and ifs to label overstock, low stock, or out of stock.

Learn how to use vlookup, the vertical lookup, to find exact matches for product IDs in a data table and return the corresponding product names.

Master the hlookup function to perform horizontal lookups in Excel, using the first row of a table, absolute references, row index, and exact match to retrieve temperatures.

Explore the Excel index function and its array concept, learning to specify a row and optional column to retrieve intersection values, and how match enhances dynamic lookups.

Explore the Excel match function: search a value in a lookup array, return its position, and use exact match with zero; see how it pairs with index for dynamic lookups.

Make HLOOKUP dynamic by replacing the fixed row index with a MATCH, so you can retrieve March temperatures by city (Chicago, Denver, New York) using HLOOKUP and MATCH.

Use the indirect function to convert month labels into sheet references and pull data from multiple worksheets, building dynamic references across months.

Discover xlookup, a newer alternative to vlookup that uses separate lookup and return arrays, supports exact matching, and handles not found values, introduced in Excel 2019.

Explore the Microsoft Excel count function in the statistical functions category to count numeric values in a range, such as C4 to C7, including count, counta, and countifs.

Explore the COUNTA() function to count all non-blank cells across a range, including numbers and text, while excluding blanks.

Nest index, match, and max functions to dynamically return the salesperson who has the maximum value, demonstrating how to look up the exact row for the top value.

Master the min function in Excel, returning the smallest value from a range of city temperatures, and learn to identify the city with that minimum value.

Learn to use the average if function in Excel by combining average and if to compute conditional averages based on subject criteria such as math, science, or history.

Compute subtotals for each category using the sumif function, with electronics, fashion, and appliances as examples. Use absolute references and copy the formula down to generate each subtotal.

Learn how to use the sumifs function to total sales by applying multiple criteria, such as electronics category and north region, summing the sales range that meets those criteria.

Learn how to use the countif function to count records that match a specific category, using a range and a criteria, demonstrated with electronics in the category column.

Learn how the countifs function counts data based on multiple criteria, using category and a derived month from dates to count electronics in March.

Use the DSUM function with multiple criteria to create an OR condition that sums total sales for furniture or clothing categories.

Master the dsum function with multiple criteria, applying and/or logic to sum sales by category (furniture or clothing) with quantity thresholds, and apply the same criteria to other database calculations.

Explore the d average database function to compute conditional averages, using department criteria (such as IT) and years of experience, with rounding to whole years.

Use dcount to count numeric values in a list column, and dcounta to count non-blank cells, by applying criteria for course name and grade to reveal credits earned.

Demonstrate the excel dmin function to return the minimum price for electronics from a database using category criteria, mirroring dsum, daverage, and dcount, with multi-criteria options.

Master the dmax function by identifying the maximum value in the price column for a given property type within a property listings table.