Microsoft Excel: Mastering Excel Functions and Formulas

Master Excel text functions to convert text case using proper, lower, and upper to standardize names and phrases.

Extract months and years from text with left and right functions; use three characters for months and four for years, using the function arguments dialog.

Master mid to extract dates from the middle of a text string and trim to remove extra spaces, enabling clean, reliable data with Excel functions.

Learn to combine first and last names in Excel using four methods: ampersand, concatenate, concat, and text join with a space delimiter.

Explore how Excel's unichar and unicode functions encode and decode characters, turning numbers into characters like A and dollar sign, and back for dynamic, practical use.

Master counting characters with len and finding positions with find and search. Learn how case sensitivity affects results and how spaces indicate first-name lengths in Excel.

Explore how to use the date function in Excel to construct date values from day, month, and year, understand date as a numeric data type, and format dates for analysis.

Extract day, month, and year from a date in Excel using the day, month, and year functions; feed a date serial number and drag the formula down.

Master Excel weekday and weeknum functions to extract a date’s day of week and its week number, choosing start days and return types with practical date examples.

Master the Excel text function to convert dates into custom text formats using D, M, and Y codes to display day names, month names, and year.

Learn to convert dates written as text into proper Excel dates using the datevalue function, enabling accurate calculations and proper date formatting.

Master the EOMONTH function in Excel to obtain the end date of any month from a start date and offset by a specified number of months.

Master the e date function to move dates by months and land on the day, not end of month, using start date and offsets like five months or 36 months.

Learn to calculate working days between dates using networkdays and networkdays.intl, adjust weekends and holidays, and apply country-specific weekend patterns.

Calculate age as the duration between two dates in Excel using the hidden date diff (datediff) function, specifying start date, end date, and unit (D, M, or Y).

Master the datedif function in Excel to compute ages in years, months, and days, including remaining months after full years (y m) and days after complete months (m d).

Learn to solve excel tasks by combining multiple functions into one formula, breaking problems into chunks to boost efficiency.

Learn to combine two text functions, proper and trim, to fix name capitalization and remove extra spaces in a single formula.

Extract first names from a list using find to locate the space and left to return characters before it, combining these functions to handle varying name lengths in Excel.

Use the date diff function with today to compute ages from birth dates, then concatenate years and months into a single 'x years and y months old' result.

Combine concatenate and date diff formulas in a single cell to display age as years and months, using a birth date in B5 and today as the end date.

Learn how cell referencing affects formula behavior when you copy or drag formulas across cells, and how to control references to keep results accurate.

Explore the four Excel cell referencing styles, starting with relative referencing, which lets formulas move with drag across columns or rows, while locking specific rows or columns.

Lock the exchange rate with absolute references to keep $H$2 constant when copying formulas, using F4 to apply the dollar signs.

Explore mixed referencing in Excel, locking either the row or the column while dragging formulas to compute fixed amounts against monthly exchange rates.

Master six logical operators in Excel to compare values, including equal to, greater than, less than, less than or equal to, greater than or equal to, and not equal to.

Explore using the countif function in Excel to count staff by department in a data set, leveraging a dynamic range and a criteria cell for automatic updates.

Apply the countif function to count missing files across yearly ranges, using dynamic cell ranges and a missing criterion to tally file status for each year.

Master countifs to count values across multiple criteria ranges, such as marketing department staff in the city of Miami, using two or more conditions.

Summarize department salaries in Excel using sumif and averageif to compute total and average pay. Learn to lock ranges and keep criteria and sum ranges the same size.

Explore how to use sumifs to summarize data when multiple criteria apply, such as calculating the total salary for staff in marketing and based in Seattle by locking ranges.

Explore logical functions in Excel by using logical operators to compare values for true or false results, including equal to, less than or equal to, and greater than.

Apply the if function in Excel to categorize KPI scores as met or missed expectations using a logical test of less than 50%, automating labeling across staff data.

Apply the if function to compute bonuses: pay 10% of salary when KPI exceeds 85%, and restrict payments to finance department employees.

Combine the if function with and or to test multiple logical conditions in Excel. Verify if both B3 and C3 equal yes with and, or if either equals yes.

Learn to combine the if and and functions in Excel to test multiple conditions, such as awarding a 10% bonus to female marketing staff based on salary.

Explore using if with or to compute bonuses from multiple conditions, such as department and gender, and learn how all handles multi-column and single-column checks.

Explore using nested if statements to assign department bonuses based on role. Build multi-branch logic in Excel to pay finance 5%, marketing 8%, IT 3%, or zero for others.

Learn to use the VLOOKUP function to retrieve data by matching an ID in the first column, with a defined table array, column index, and exact match.

Learn to use Vlookup to fetch salaries by matching unique employee IDs in a properly highlighted table array, lock ranges, choose the correct column index, and ensure exact matches.

Master VLOOKUP by selecting the lookup column and starting the range from that column to retrieve positions from the HR data bank with an exact match (range_lookup set to 0).

Explore how to use the match function to locate the position of a value within a list and combine it with vlookup using exact matches for precise, reusable lookups.

Automate vlookup across multiple columns by using match to derive the column index, while locking references and dragging formulas across rows and headers.

Wrap VLOOKUP in IFERROR to gracefully handle missing lookups, turning #N/A into blank or a custom message such as no data.

Apply vlookup to convert salaries from US dollars to naira by dynamically retrieving the monthly exchange rate from a two-column table and locking references for drag-down.

Learn how to use xlookup to perform lookups across any column, defining lookup value, lookup array, and return array, with optional if not found and match mode.

Explore how the index function retrieves values from a chosen array, using specified row and column numbers. See how index pairs with match to perform a lookup.

Use the index and match functions to retrieve an employee salary from the HR data bank by matching the employee ID, locking ranges with F4, and returning the correct column.