Microsoft Excel 2010: Advanced Training

Learn the if statement syntax in Excel and VB, including the condition, value if true, and value if false, with nested ifs, absolute references, and divide by zero checks.

Learn to nest if statements in Excel 2010, placing an if inside another, up to 64 levels. Apply pass rate, merit rate, and fail rate with syntax and flow diagram.

Explore how to reduce nested if statements in Excel by using the and operator to test multiple conditions for a pass, creating a concise true/false result.

Learn to replace multiple nested ifs with the or operator to evaluate several criteria, so any true condition yields an okay outcome while keeping formulas concise.

Use the not operator with and/or to build comprehensive if statements in Excel 2010, checking not below minimum or not above maximum to classify a healthy diet.

Harness the sumif function to add values by criteria in Excel 2010, using range, criteria, and optional sum range with named ranges and example sales data.

Explore how to use countif to count items by month and to evaluate values, including exact matches and greater-than criteria, using ranges and cell references.

Learn to use the averageif function in Excel 2010 to compute the mean of cells meeting a criteria, using a month named range and a value range.

Learn to use sumifs, countifs, and averageifs to satisfy multiple criteria across month and day, with examples like sundays in January and dragging formulas with absolute and relative references.

Master vlookup in Excel by understanding its four parts: criteria, lookup table, column to return, and exact versus approximate match.

Master vlookup techniques in excel by applying lookup tables, exact matches, and named ranges across multiple examples, including single-column, month-name, and multi-column lookups.

Master hlookup in Excel by applying its four-parameter lookup: criteria in the first row, a horizontal lookup table, and returning a specific row with exact or approximate matches.

Explore hlookup in action, using horizontal lookups to map week numbers to seasons and salesmen to commissions. Build dynamic calculations with named ranges, exact matches, and minimum commissions.

Master near-match lookups in excel using vlookup with the true option, treating thresholds in an ascending table to determine the bonus amounts.

Learn to trap vlookup and hlookup errors with if and isna, returning zero to keep totals accurate and keep spreadsheets tidy and error-free.

Extend the lookup table by using named ranges and updating the destination range in Name Manager to include new rows for vlookup; future-proof the formulas by enlarging the lookup area.

Learn to nest vlookup and hlookup to cross-match a date and a job role in a shift data table, retrieving the employee and validating shifts with a count if.

Explore how the match function locates a value in a list and returns its position. Choose exact, nearest largest, or nearest smallest matching, and use wildcards for text searches.

Understand the index function syntax and how it returns a cell value from a range or table using row and column numbers, including multi-range lookups with area numbers.

Learn how to trap index lookup errors in Excel 2010 by using IF and ISERROR to display friendly messages like out of data range when row or column doesn't exist.

Master the choose function in Excel 2010 to pick a value from a list, not a range, with indexes drawn from anywhere on the sheet.

Learn how Excel treats time, including adding and subtracting, with 5:00 as hours or 0:5:00 for five minutes, and display total hours beyond 24 using h brackets.

Learn to use Excel's round function to convert prices to fractional values by rounding to the nearest dollar, quarter, or nickel, using divide and multiply steps for precision.

Learn how to use Excel's mod function to obtain remainders, apply int to get the quotient, and combine with IF to test even or odd numbers.

Excel's RAND function generates numbers between 0 and 1, recalculating on open or with Shift+F9; multiply to scale, or use the between function for whole numbers in a range.

Learn to randomly pick a list item in Excel using randbetween and index, selecting from a customer ID list and optionally retrieving corresponding names via a lookup.

Use the pmt function in Excel to calculate loan repayments from a principal, annual rate, and term. See how changing the rate, periods, or amount alters the payment.

Use the PMT function to determine monthly investments needed to reach a target of 10,000 over 36 months at a 5% rate, with payments at the start of each month.

Use Excel's db depreciation function to model year-by-year asset losses for tax write-offs, feeding initial cost, salvage value, useful life, and period (with optional first-year months).

Explore how to use Excel 2010 functions to compute loan components, including PMT, rate, nper, and PV, to determine payments, interest, and the amount borrowed.

Explore how Excel arrays multiply item quantities by prices and sum the results. Enter an array formula with ctrl+shift+return to apply the calculation to all items.

Learn to create array formulas in Excel 2010 by multiplying two arrays and summing or averaging the results, using ctrl+shift+enter to preserve the array status.

Explore conditional evaluation with array formulas in Excel 2010, using multiple arrays, criteria like quantity greater than 2, and array-aware sums to calculate total spends.

Master the transpose array function in Excel 2010 to convert vertical ranges to horizontal, entering as an array with control+shift+enter and linking the result to a named range.

Explore the len and trim functions in Excel to count characters and remove leading or trailing spaces, using a 5,000 customers dataset for demonstration.

Master extracting characters with left and right functions in Excel, and learn to trim spaces and calculate dynamic lengths using len for flexible text manipulation.

Use the mid and find functions together to extract the domain from an email by locating the @ symbol and the period as start and end points.

Combine title, first name, middle name, last name, and suffix from multiple cells into one string using concatenate or the ampersand, with spaces and trimming.

Master Excel text case functions upper, lower, and proper to transform text in cells, including concatenation and applying cases within other formulas for ranges or single entries.

Learn how replace and substitute work in Excel 2010, using strings to replace characters at a fixed position or all occurrences of a substring, with instance numbers and case sensitivity.

Learn to format numeric values inside a text string in Excel using the text function, combining first names in proper case with surnames in upper case and bonuses as currency.

Discover how to extract values from text functions by pasting values in Excel 2010, preserving formatting, and reusing static results for mail merges or data systems.

Explore core is functions in Excel, including is blank, is err, is error, is even, is logical, is n/a, is non text, is number, is odd, and is text.

Learn to trap and handle Excel errors with iserr, iserror, isna, and iferror, preventing divisions by zero and na results while simplifying formulas.

Learn how the offset function references a starting cell, moves rows and columns with positive or negative values, and expands into dynamic ranges inside formulas such as sum.

Learn dynamic named ranges with offset in Excel 2010 to automatically expand data ranges and enable robust lookups and pivot table integration.

Learn to use the indirect function to build dynamic cell references, create dynamic ranges for summing, and concatenate strings to form references in Excel.

Handle ref errors in Excel by using indirect for dynamic references and iferror to display a hyphen when the indirect result isn't a valid cell reference.

Explore using Excel's cell function to display the current file path and sheet name, and extract the file name or sheet name from the path with mid and find.

Create sparklines in Excel 2010 by inserting mini-graphs, selecting the data range, and placing beside the data, with line, column, or win/loss options, including positive and negative values.

Learn to customize sparkline design in Excel 2010 by changing type, colors, markers, and styles; group or un-group lines to apply uniform or individual edits.

Explore how spark lines handle zero values and empty cells in win/loss, bar, and line representations, and learn to edit data and set gaps or zeros using the design tab.

Compare sparklines in Excel 2010 by setting the same minimum and maximum axis across all sparklines, enabling data to be read in relation to each other.

Learn to remove sparklines in Excel 2010 by clearing individual sparklines or entire sparkline groups, using the sparkline tools design tab to clear or delete cells.

Explore how outlining in Excel contracts and expands data by hiding rows and columns, using outline symbols, subtotals, and grand totals, then learn how to apply outlining to your sheets.

Automatically generate outlines in Excel 2010 by selecting data and using the data ribbon's group auto outline option to collapse rows and columns into subtotals and totals.

Create an outline manually in Excel 2010 to group rows and columns, enabling hide and unhide with subtotals and grand totals when auto outline falls short.

Learn how to manually remove data from an outline by ungrouping rows and columns on the data ribbon, exploring first level groupings and visibility of items.

Remove outlining from a worksheet by selecting data, choosing un-group, and clicking clear outline to return to a normal grid of rows and columns.

Learn to refine automatic outlining in Excel by manually adjusting groups, including or excluding specific rows and columns through the data ribbon, to preserve accurate labels and headings.