Practical Excel 2013 – Intermediate & Advanced

Explore countif, sumif, and averageif to count, sum, or average values by a condition, using a criterion range, a sum range, and concatenation for cutoffs across gender, age, and scores.

learn to use countifs, sumifs, and averageifs with multiple criterion pairs, using criterion ranges and criteria with concatenation to count, sum, and average.

Explore common math functions in Excel, from absolute values and rounding to logarithms and exponential values, accessed via the formulas ribbon and the more functions dropdown's engineering group.

Explore Excel's int, round, ceiling, and floor functions to round numbers to specified decimals or multiples, extract integers, and handle positive and negative values.

Explore the abs, sqrt, and sumsq functions to perform distance calculations and statistical tasks, including handling complex numbers in Excel and applying the distance formula.

Explore the ln and exp functions in Excel, including natural log base e, log base 10, domain limits, inverses, and examples of exponential growth and very large values.

Learn how RAND and RANDBETWEEN generate random numbers for simulations in Excel, including decimal values and integers within specified ranges, and how to freeze results by copying as values.

Explore Excel text functions that manipulate text and save hours compared with manual work, including bahttext that converts numbers to Thai text.

Master Excel 2013 text functions lower, upper, and proper to convert text to lowercase, uppercase, or proper case. Apply these functions to cell references and names for practical data formatting.

Learn to clean data in Excel with trim to remove leading and trailing spaces and value to convert text to numbers, saving hours on large datasets.

Master concatenating in Excel using ampersand or the concatenate function to join cell references and literals into clean labels, with practical examples for names and city–state–zip.

Explore how to parse text into columns using Excel's text to columns tool, identify delimiters like spaces or commas, and preview results to split data into separate columns.

Learn to parse text in Excel 2013 using Len, Left, Right, Mid, and Find by spotting patterns to split cells. Explore name parsing examples and when Flash Fill falls short.

Explore flash fill in Excel 2013 to quickly parse names, numbers, and dates using pattern recognition, with left-to-right methods and keyboard shortcuts, while noting its limitations.

Explore how Excel stores dates as serial numbers and times as fractions of a day, and format date time values using format cells and custom formats.

Explore Y2K from two-digit years and Excel's rule that 29 or less is current century and 30 or more is previous; type four-digit years to avoid ambiguity.

Use today and now to access current date and time, format now for time-only display, and subtract dates to compute days since birthdate.

Master Excel date functions—year, month, day, and weekday—and learn to format months and days with lookups and custom formats, handle invalid dates, and copy formulas using absolute addresses.

Explore the datedif function in Excel 2013 for calculating the number of days, months, or years between two dates, despite it not being an official Excel function.

Use the Date function to create dates from year, month, and day, format serial values as dates, and use DateValue to convert legacy labels into dates.

Compute workdays and next start dates for projects using networkdays and workday, excluding weekends and holidays. Learn practical examples and note the Excel 2010 international versions that extend holiday options.

Explore how Excel's statistical functions support data analysis, including averages, standard deviations, and correlations, and compare old and new versions while using the formulas ribbon.

Explore min and max in Excel 2013 to find smallest and largest values in numeric lists, and learn why they're defined only for numeric values, not text, in alphabetical order.

Learn how median, quartile, and percentile describe data distribution, identify the 50th, 25th, and 75th percentiles, and compare median with mean as a measure of central tendency.

Explore standard deviation and variance as measures of variability; variance is the square of the standard deviation, and for normal data, observations lie within one to three standard deviations.

Explore how correlation and covariance reveal relationships between variables. Interpret a correlation matrix, including diagonal ones, compare sample and population covariance, and use Excel 2010 functions.

Explore how the rank, large, and small functions rank observations in ascending or descending order, and identify the five largest or smallest values, including handling ties.

Discover the new statistical functions in excel 2010, identified by periods in their names, compare them to old versions, and locate them in formulas ribbon and compatibility category.

Explore Excel's financial functions, learn the most common ones used by financial analysts, and discover how to access them by clicking the financial category on the formulas ribbon.

Use the PMT function in Excel to compute monthly loan payments from the monthly rate, loan term, and principal, noting the last argument is negative cash outflow.

Master the time value of money in Excel by calculating net present value and XNPV for streams of cash flows with a discount rate, including handling outflows today.

Illustrate how net present value and internal rate of return relate, using Excel's IRR and XIRR functions to determine discount rate that matches the initial outlay with future cash flows.

Explore four reference functions—index, match, offset, and indirect—in the look up and reference group on the formulas tab, and learn why they can power your Excel skills.

Master the index function with its three arguments to locate an item in a rectangular range by row and column, including single-row and single-column cases, with practical shipping cost examples.

Learn how the match function locates an exact match and returns its index, enabling index-based lookups with the index function to identify profits and corresponding order quantities in Excel 2013.

Master the offset function in Excel to return a single-cell reference offset from a start cell using arguments for row and column offsets; formulas update automatically if delays change.

Leverage the indirect function to reference named ranges in Excel 2013, enabling copyable formulas for averages, standard deviations, correlations, and time series data.

Discover how Excel's logical functions test true or false and return boolean results, and explore error-checking and is functions such as iseven and isodd to evaluate conditions.

Explore Excel's is functions in the information group, such as isnumber, iseven, istext, and isref, which return true or false; use them with IF to handle lookup errors.

Use iferror and ifna to streamline error handling in lookups, returning not found instead of errors and ensuring the lookup is evaluated only once for efficiency.

Master range names in Excel 2013, including absolute references and applying names to formulas for revenue based on units sold and unit price, and grasp scope with Name Manager.

Discover the R1C1 notation for Excel formulas, using row and column references with relative and absolute addressing. Learn to switch between R1C1 and A1 style in Excel options.

Explore how to unravel complex spreadsheets using Excel's formula auditing tools—trace dependence, remove arrows, show formulas, and precedence—to identify inputs, formulas, and dangling constants.

Discover how the evaluate formula tool, part of Excel's formula auditing, unravels a complex nested if formula by stepping through its pieces, validating logic and spotting errors.

Explore how to create external references to cells in worksheets or workbooks, using sheet names with exclamation points, brackets for workbooks, quotes for spaces, and paths when files are closed.

Explore array formulas in Excel to perform multiple calculations, enter with ctrl-shift-enter, and apply matrix multiplication and inversion to solve linear systems using functions like mmult and minv.