Important Functions with Microsoft Excel

Explore the today and now functions in excel to display current date and date-time, format as time, fix values with control+semicolon and control+shift+colon, and understand their recalculation on reopening.

Learn how to use Excel's year, month, day, and date functions to extract year, month, and day from dates, handle different data formats, and reconstruct dates with the date function.

Explore how to use the hour, minute, second, and time functions in Excel to build and parse times, convert between 12-hour and 24-hour formats, and format results for display.

Master the workday and network days functions to calculate the next working day and the days between dates, accounting for weekends, holidays, and a named holidays range.

Learn to use Excel's WEEKDAY and WEEKNUM functions to derive the day of the week and week number from a date, with Monday or Sunday start options.

Discover how to use the Excel convert function to switch values between measurement systems, from Fahrenheit to Celsius and kilometers to miles, and apply it to mass, length, and volume.

Explore Excel information functions to check cell values, using isnumber, istext, isblank, isformula, isodd, and iseven to identify numbers, text, blanks, formulas, and how date and time are numbers.

Learn how excel's logical functions and, or, and not evaluate arguments to produce true or false, with examples showing comparison results and how not affects outcomes.

Learn to use the IF and IFS functions in Excel to determine pass or fail with a 55 threshold and compare nested IFs with the IFS approach.

Learn to use the IFERROR function to pre-check formulas for errors and display a custom message instead of Excel error messages, handling division by zero and misspelled functions.

Learn how to use vlookup and hlookup to look up data in vertical and horizontal tables, apply approximate matches, lock ranges with dollar signs, and name ranges for easy formulas.

Learn how to use index and match together to retrieve values at row and column intersections in large Excel tables, with practical examples of monthly employee commissions.

Explore how the row and column functions return row and column numbers, and how rows and columns derive counts for a range or table, with practical examples.

Learn to use Excel's hyperlink function to create external and internal links, navigate to sheets and cells, and handle file paths with concrete examples.

Learn how to use the transpose function in Excel to convert data between vertical and horizontal layouts, including dynamic ranges, named ranges, and one-time paste transpose options.

Explore how to use count, counta, and countblank in Excel to count numbers, text, and blanks within a range, and learn what each counts.

Learn to use countif and countifs in Excel to count cells by single or multiple criteria, with practical examples like blue and USA, Canada, and after the 15 November.

Learn to use the sum, sumif, and sumifs functions to total values and perform conditional calculations with ranges, criteria, and multiple conditions in Excel.

Learn how to use the average, averageif, and averageifs functions to compute averages from data tables, filtering by color such as blue and day like Friday.

Apply min, max, large, and small in Excel to compute minimum, maximum, largest, and smallest values within a score range, using a named range.

Learn how to use maxifs and minifs to calculate maximum and minimum scores by gender, applying range and criteria ranges with multiple criteria.

Learn how to use the mode function to find the most commonly occurring number in a range, and understand that it applies to numbers, not text.

Learn to rank numbers in excel with rank and rank.eq, using descending or ascending order, and apply with a named range like population for cross-version comparison.

Master rand and randbetween in Excel to generate random values within min and max ranges, choosing decimals or integers, creating arrays, and locking results by pasting values.

Learn to apply the round, round up, round down, and int functions in Excel to control decimal precision, discard decimals, and convert numbers to integers.

Explore how the m round, ceiling math, and floor math functions round numbers to a chosen multiple, whether decimal or integer, using criteria to produce precise multiples.

Learn how to calculate modulus in Excel using the mod function, including step-by-step division, removing decimals with floor or int, and deriving the remainder for various numbers.

Compare subtotal and aggregate in excel, showing how aggregate ignores hidden rows and errors, has 19 built-in functions vs 11 for subtotal, and supports dynamic updates.

Explore how to use Excel's left, mid, right, and len functions to extract characters from text and numbers, control start positions, and count total characters including spaces and punctuation.

Compare the find and search functions to locate text and return the character position, with find case sensitive and search more forgiving, both returning errors if not found.

Learn to use the replace and substitute functions in Excel to remove or replace characters, including replacing with an empty string and targeting specific positions.

Explore how to use the clean and trim functions in Excel to tidy text by removing line breaks and extra spaces, turning wrapped strings into a single readable sentence.

Learn how to use concat and textjoin to combine text from ranges. Discover delimiters, ignoring empty cells, and how textjoin differs from the older concatenate function.

Explore upper, lower, and proper functions in Microsoft Excel to convert text strings to uppercase, lowercase, or proper case with simple formulas.

Learn to use the TEXT function to format numbers as text, applying currency, decimals, thousands separators, percentages, and long date formats.

Construct a price matrix in excel that prices window shades by width and height, using index with match for intersections and ceiling math to round to the nearest hundred.