Excel in Microsoft Excel 2: Intermediate to Expert advanced

Edit your custom Excel templates, save as an Excel template, and override the previous version to apply changes to new workbooks; locate templates in AppData Roaming Microsoft Templates.

Learn to reference a cell in another sheet within the same workbook using sheet names, exclamation marks, and apostrophes for spaces. Master when to use indirect references for flexible formulas.

Learn how to refresh and manage links between workbooks in Excel, including update values, refresh all, change source, break links, and startup prompts.

Discover how to use structured references in Excel tables, referencing the table name and column headers, including this row (@), the header vs data, and totals with COUNTA.

Protect and control edits in Excel by applying sheet protection and password to lock cells. Learn to enable read-only access, manage locked versus unlocked cells, and unprotect sheets when needed.

Master locking and unlocking cells by protecting worksheets, selecting which cells are usable, and controlling formatting, inserting, sorting, and pivot table or chart capabilities.

Explore how to share Excel workbooks and enable multiple users to edit simultaneously, track and date changes, and manage update frequency, with considerations for conflicts, personal view, and print settings.

Learn to use Excel's if and not functions to perform logical tests, reverse conditions with not, and navigate locale differences such as si/no and comma or semicolon.

Master complex if statements in Excel by using and, or, and not to test multiple conditions like file size and mp3 extensions.

discover how countifs expands countif by allowing multiple criteria ranges and criteria, enabling and-style filtering; see examples with file extensions and set conditions.

Explore how the frequency function counts values less than or equal to a threshold within a range, mirroring countif and countifs with two and three million examples.

Explore error handling in Excel with IFERROR and ISERROR, noting their argument differences. Wrap a formula to return the text 'error' when division by zero or other errors occur.

Practice activity 3 reinforces building and testing Excel formulas using NOT, OR, IF, AND, COUNTIFS, SUMIFS, and IFERROR to classify regions such as East Midlands and Midlands.

Create your first pivot table by selecting the data, choosing where to place it, and dragging fields into rows, columns, and values to summarize the data.

Master pivot table options to switch sums to counts, rename fields, move items, and apply subtotals, grand totals, and report layouts from compact to tabular.

Filter massive data sets in Excel with standard filters and pivot table report filters, then apply slicers to visually filter across multiple pivot tables and connect them for synchronized results.

Learn to use get pivot data to retrieve measures from a pivot table and create calculated fields like size doubled and size divided by filename length, refreshing as data changes.

Create a pivot table from the HPIRegions spreadsheet, set region in rows and sales volume in values. Change sum to max and group the date by quarters.

Master custom formatting for numbers in Excel, applying number format options in PivotTables and charts, using 0, #, decimal separators, thousands separators, and fractions.

Learn to convert Excel date serial numbers into readable dates, and master custom date and time formats, including hours as durations and text and currency formatting.

Learn how to use Excel's custom formatting with four sections—positive, negative, zero, and text—to color numbers, hide zeros, and create condition-like rules for pivots.

Convert pivot charts to normal charts, then add trendlines in Excel using linear, exponential, moving average, linear forecast, or polynomial options, and display equations and r-squared on the chart.

Save a chart as a template to reuse future charts, creating a custom template from the design tab and applying it to pivot charts for quick, consistent visuals.

Master PivotCharts and advanced charting by building a PivotChart with dates on the axis, dual axes for sales volume and detached price, adding a trendline, and tidying with hidden field labels.

Learn to extract year, month, day, hour, minute, second, and weekday from dates or times in Excel, with flexible weekday starting days and optional arguments.

Practice activity 7 demonstrates extracting months with the month function from the HPIAdmins data and calculating date differences using today or now, with simple subtraction and proper formatting.

Trace precedents and dependents to see how a formula relies on other cells, using color-coded indicators in the formula bar. Audit and print the arrows to share logic.

Use the watch window to monitor key cells and formulas across workbooks, add or remove watches, and observe values, dependencies, and cell locations in real time.

Explore Excel error detection with error checking and trace errors, identify circular references, and correct formula issues like division by zero through evaluation steps.

Learn how to handle circular references by enabling iterative calculations in Excel, adjusting iteration limits, and using practical examples to approximate solutions.

Explore formula auditing and trace dependence in a real spreadsheet, use goal seek to set AA20 to one million, check errors, and switch between automatic and manual recalculation.

Prepare Excel workbooks for international audiences by using starred date and time formats tied to system locale, adjusting regional settings, and enabling language accessory packs for display and help language.

Learn to manage body and heading styles in Excel using home styles and cell styles, modify options, and apply custom theme fonts to set body and heading fonts.