Excel 2016 Functions Bootcamp With 150+ Examples & 1 Project

Explore and, or, and not logical tests in Excel, learn their syntax and how to combine criteria like online and in-store and price thresholds.

Explore excel's if, ifs, and iferror functions by building logical tests with value if true or false, nested conditions, and error handling, illustrated with discounts and shipping rules.

Master Excel 2016 logical functions through practice with logical tests, or not, and if error handling; learn to combine tests and complete six homework assignments to solidify understanding.

Master Excel 2016 joining tools, including text join, ampersand, concatenate, and flash fill, to combine first and last names with delimiters while handling empty cells.

Master extracting functions in Excel, including left, right, mid, search, and len, to extract text by length and delimiters, demonstrated with location IDs, city names, and codes.

Explore excel data cleaning tools—proper, upper, trim, text, and substitute—to correct capitalization, remove extra spaces, format numbers via text, and replace characters (dash to slash).

Practice eight Excel 2016 function homework using text join, concat, left, right, mid, search, and cleaning tools like proper, upper, trim, substitute. Check answers in the provided sheet.

Explore today, now, and workday functions in Excel 2016 to manage dates and times. Learn dynamic date calculations, add or subtract dates, and exclude weekends with holidays.

Learn to use eomonth, days, and edate to compute end of month, shift dates by months, and measure days between dates, including 0 for current/previous/next month and us/european method options.

Learn to create dates in Excel with the date function, extract year, month, and day, and format full month and day names using left, mid, and right.

Master networkdays and datedif in Excel 2016 to compute work days between dates, excluding weekends and holidays, using start date, end date, holidays, and date unit (d, m, y).

Practice Excel 2016 functions bootcamp date and text functions, including workday, end of month, networkdays, and date formatting; redo problems until confident, then complete six homework assignments and check answers.

Learn how to use the choose function in Excel 2016 to return values by index, map months to quarters, and apply it with data validation and combo boxes.

Learn how row and column functions identify references, return row and column numbers, and count rows and columns in a range, with practical practice expanding ranges.

Master vlookup and hlookup in excel 2016 by learning the lookup value, table array, column index, and exact or approximate match.

Learn to use match and index for exact and dynamic lookups, perform two-way lookups, and retrieve department names from employee IDs, with practice problems and data validation insights.

learn to use the indirect function to return references from text strings and drive lookups across multiple named tables, including defining names and data validation lists for category-based selections.

Learn how the offset function creates a dynamic reference from a base by rows, columns, height, and width for adaptive formulas using counta, match, and index to analyze evolving data.

Learn how the transpose function converts vertical ranges to horizontal (and vice versa) using an array function, by selecting the target area, entering =transpose(array), and pressing ctrl-shift-enter.

Master powerful Excel functions like CHOOSE, MATCH, INDEX, INDIRECT, and TRANSPOSE by building and combining formulas, completing nine homework assignments, and validating your answers against provided solutions.

Learn to use sumifs, averageifs, countifs, maxifs, and minifs in Excel 2016 to perform conditional totals, averages, and counts with criteria ranges.

Learn to build a summary table in Excel 2016 using named ranges and the sumifs function, with fixed and relative references, drag-down and drag-across techniques, and grand totals.

Learn to perform conditional calculations with an array of criteria using sumifs and sumproduct, aggregating results via control-shift-enter, and calculating averages with multiple origins.

Learn to use Excel's large and small functions to find the largest, second largest, third largest, and smallest values in an array using K.

Populate an array of results in Excel 2016 by selecting the area, entering =, and pressing Ctrl+Shift+Enter, using array constants with curly braces, semicolons for rows, and commas for columns.

Revisit problem 9 from chapter 4 to explore lookup functions, create unique project and task identifiers, use exact match in vlookup, and transition to array calculations with index and match.

Learn to use the frequency function in Excel to count shipments by kilotons within defined bins, enter the array formula with ctrl-shift-enter, and verify results using range criteria.

Utilize sumproduct to compute total revenue and profit by multiplying quantities sold by prices, then filter results by category using criteria and booleans.

Explore excel's rand and randbetween for generating random data, then master rounding functions—round, roundup, rounddown, floor, and ceiling—to format numbers by digits and significance.

Learn to apply Excel’s aggregate and subtotal functions to ignore errors and hidden rules, explore 19 aggregate functions, and see how filtering affects subtotals.

Learn to return multiple values for a selected category by filtering records, sorting numbers, and using aggregate and index functions with if error checks to display dates, sales, and costs.

Explore conditional functions and arrays of criteria, build summary tables, and apply frequency, product, aggregate, and subtotal to return multiple values.

Explore excel database functions DSUM, DAVERAGE, DCOUNT, and DMAX to compute sums, averages, and counts. Learn to define the database, field, and criteria, and build criteria tables for and/or conditions.

Apply the dget function to extract a record from an Excel database by building a criteria table with a phone number and identifying the owner via index and match.

Learn to visualize data in Excel 2016 with conditional formatting, using built-in rules and formula based techniques to highlight top or bottom values, duplicates, and entire rows.

Explore how wildcard characters in Excel 2016 functions use the question mark, asterisk, and tilde to match patterns and sum sales by category IDs.

Explore Excel 2016 functions through 150+ examples and a project, from creating criteria tables and complex criteria to using dget, conditional formatting, and text functions such as left and match.

Master Excel functions and formulas by completing a 13-assignment project that uses a large data set to practice data cleaning, joining items, extracting parts, lookups, naming ranges, and data validation.

Master Excel 2016 functions by building lookup and text formulas, creating joined fields, cleaning data, naming ranges, calculating lead times with networkdays, applying conditional formatting, and performing frequency analysis.

Learn to build a dynamic Excel 2016 project: map subcategories to categories with lookups, analyze sales with average and max via choose, validate data, calculate profit, and track monthly shipments.