Microsoft Excel for Mac 2: Intermediate to Expert advanced

Convert an existing workbook into a reusable template by stripping data and saving as a template (.xltx or .xltm) in the template folder; then create new workbooks from this template.

Overwrite your existing Excel for Mac custom template by using save as template, choosing the template name (like My CD Collection), left-justify file names, and adjust the zoom.

Open both workbooks, use home > cell styles > merge styles to copy workbook two's styles into workbook one; replace existing styles and observe changes to normal and heading styles.

Learn to reference cells in another spreadsheet with equals, use sheet names and an exclamation mark, and enclose names with apostrophes for spaces, plus indirect for dynamic references.

Explore how to reference data in another workbook, create external links to a cell like B5, and see how absolute references and paths update as you rename or close workbooks.

Learn to refresh and edit external workbook links in Excel for Mac, change sources from workbook 4 to 5, and break links to keep current values.

Explore structured references in Excel tables to refer to columns and headers with table names and the @ syntax. See how formulas auto extend as you add data.

Protect the sheet in the review tab, set a password to unprotect, and control edits by locking cells and allowing selection of locked or unlocked cells.

Explore how locking and unlocking cells work in Excel for Mac, then protect the sheet to restrict edits to unlocked cells and allow only specific actions like hyperlinks.

Lock and unlock objects in Mac Excel and learn how sheet protection governs clicking and editing of shapes, text, and locked versus unlocked items.

Learn how to hide formulas on a protected Excel for Mac sheet, keeping formulas unseen while hyperlinks still work, and manage locked, hidden, and mixed cell states.

Protect the workbook to secure its structure and window layout, separate from sheet protection; protect structure blocks moving, deleting, renaming, or inserting sheets, while protect windows locks size and position.

Learn to track changes in Excel for Mac by sharing workbooks, highlighting edits, and reviewing changes with accept/reject options and a change history.

Learn to encrypt a workbook with a password in Excel for Mac, controlling open access, modify permissions, read-only prompts, and backup options.

Unlock columns A and B, hide formulas, and protect cds spreadsheet with a password to allow unprotected cells; enable sharing and track changes, save with a password to modify.

Master logical operations in Excel for Mac by using the if and not functions to test conditions, reverse true/false results, and handle greater than and less than or equal comparisons.

Learn to craft advanced if statements in Excel for Mac using and and or, placing and at the start of conditions, and testing file size and file extension.

Master countifs, a multi-criteria extension of countif, by counting mp3 files in column d while checking indent 2 in column j, with validation via filters.

Learn how sumifs and averageifs extend sumif and countifs, with the summing range at the start and multiple criteria; apply to mp3s and wmas to compute totals and averages.

Discover how the frequency function counts values by a range, returning how many are less than or equal to a maximum, as an alternative to countif.

Explore IFERROR and ISERROR in Excel for Mac, comparing arguments and usage. Wrap formulas with IFERROR to return a user-friendly result on error.

Practice activity 3 reinforces building Excel formulas using not, countifs, and sumifs, with examples in East Midlands and West Midlands, plus error handling and fill down.

Create pivot tables from a selected data range to quickly summarize data by dragging fields to rows, columns, and values in the PivotTable Builder, usually in a new workbook.

Explore pivot table options in Microsoft Excel for Mac to count files by extension, switch from sum to count, rename fields, adjust layout, and refresh data.

Explore filtering large data in Excel for Mac using filters, field lists, and slicers, including applying a slicer to multiple pivot tables and filtering by file extension and date accessed.

Learn to group pivot table data by year, quarter, month, date, or numeric ranges to create focused summaries without relying on slicers.

Master GetPivotData to reference pivot table data by field, location, and filters such as quarters and years, then use calculated fields like Double Total Bytes to analyze totals.

Create a PivotTable from the HPI regions, show date, region, and sales volume, and set the values to max, then group the date by quarters for comparison.

Format numbers in PivotTables efficiently by using field settings to choose number, currency, accounting, or custom formats, set decimals and thousand separators, and apply formatting across tables and cells.

Learn to apply custom number formatting in Excel for Mac, including chart axis formatting, thousands or millions separators, and displaying fractions with 1 to 3 digits.

Master custom number formatting in pivot tables using zero and hash placeholders, decimal and thousands separators, and see effects of patterns like 0.00 and 0,000 while preserving the full value.

Master custom date and text formatting in Excel for Mac, using ds and ms codes, hard brackets for durations, locale-aware dates, and text or currency annotations.

Explore custom formatting in Excel for Mac, using four sections: positive, negative, zeros, and text, with colors, the @ symbol, and hard brackets to tailor display and hide zeros.

Create and manage custom conditional formatting rules in Excel for Mac, including colour scales, data bars, icon sets, and top/bottom rules.

Learn to create conditional formatting rules with formulas in Excel for Mac, using relative and absolute references, and apply to ranges or entire rows.

Master advanced formatting in Excel for Mac by applying mmmm yy date formats, custom number formats, and conditional rules that color red above 5,000 and green when H exceeds 6,000.

Create a PivotChart from a PivotTable, linking both so changes in one update the other, and use the formula bar to make dynamic chart titles while filtering region.

Create dual-axis charts by assigning one data series to a secondary axis, enabling simultaneous reading of house prices and number of houses sold on a single graph.

Learn how to add and customize trendlines in Excel for Mac, including linear, exponential, moving averages, forecasts, and interpreting R-squared values for chart fits.

Learn how to save a chart as a template on Mac Excel, create and manage custom chart templates, and reuse them across workbooks, including editing data ranges when applying templates.

Explore indirect chart animations in Excel for Mac by linking a PivotTable to a Slicer to animate months. Fix axis ranges to keep the display stable while switching charts.

Master advanced charting in Mac Excel by building bar charts from data, using a pivot table to select dates, sales volume, and detached price, add a trend line.

Master the now and today volatile functions to track time and date, and use them to compute days to due dates with conditional formatting for overdue tasks.

Extract year, month, day, hour, minute, second, and weekday from dates and times in Excel for Mac, with weekday returning numbers and an optional start-day setting.

Convert numbers to dates and back using date, time, datevalue, and timevalue; manipulate with eomonth and 0th-day tricks, and note leap year and locale caveats.

Explore how to use Excel's financial functions, focusing on the PMT function to calculate loan payments for mortgages or car loans, and avoid traps with rate and period units.

Extract the month from a date using the month function and copy the result down; compute days between dates by subtracting today() from a date, recognizing dates as numbers.

Troubleshoot Excel formulas by tracing precedents and dependents, and using trace dependence and trace precedence in the formulas auditing tools to document flows with arrows.

Explore how to identify and fix errors in Excel formulas, including division by zero and circular references, using error checking, tracing, and auditing tools.

Enable iterative calculations in Mac Excel to resolve circular references by setting the iterations limit and convergence threshold, using file, preferences, and calculation options.

Use Goal Seek in Excel for Mac to find a value by changing a cell, ensuring the target is a formula and the changing cell a value.

Learn how to enable or disable automatic calculation in Excel for Mac, switch to manual calculation for large spreadsheets, and recalculate with F9 while understanding calculation options.

Demonstrates tracing dependencies with formulas, including A8, A7, A9, and D15. Uses goal seek to make AA20 a million by adjusting AA3, then explores error checking and manual calculation.

Prepare a workbook for internationalism and accessibility by applying format cells and custom date formatting that adapt to locale and region, such as Spain and Japan.

Explore international currency formats in Excel for Mac, adjusting symbols, decimal places, and placement to reflect country-specific conventions such as euro, United States dollar, yen, and CHF.

Select body and heading fonts in the format cells dialog and apply them across the workbook with themes; save the theme for consistent styling.

Modify spreadsheets for accessibility using macOS tools like VoiceOver, zoom, and display options. Rename sheets, add alt text, place shapes accessibly, and run check accessibility in Tools or Review.

Format column i to Japanese yen in Excel for Mac, set zero decimals, then run accessibility checks via Tools or Review, and note date and time formats for internationalism.