Excel 2016 Advanced

Learn sumif and sumifs to sum values by single or multiple criteria using ranges or optional sum_range, with named ranges and an annex production example.

Learn how to use averageif to compute the average of cells that meet a criterion. Explore averageifs for multiple criteria and verify results with named ranges and practical examples.

Learn to count cells that meet specific criteria using COUNTIF, explore COUNTIFS for multiple criteria, and apply these techniques to count items in rows.

Explore how to use VLOOKUP with TRUE in the range_lookup argument to perform approximate matches, finding the next highest value in a commission tier table.

Learn how to use HLOOKUP with an approximate match (true) to assign the correct letter grade from a grades table named range by looking up percent in the second row.

Master using index and match together to retrieve values anywhere in a data set, overcoming vlookup and hlookup limits, with a product id example.

Explore the Excel MATCH function to find a value in a range, return its position, and compose it with INDEX for dynamic exact-match lookups.

Learn to combine index and match to create dynamic lookups that retrieve product data anywhere in a table, using match for rows (and optionally columns) with absolute references.

Discover how to use VLOOKUP to compare two lists in Excel 2016 advanced. Identify items in list two that are missing from list one with exact match.

Explore how to compare two lists in Excel 2016 using VLOOKUP and handle NA errors with ISNA, IF, and IFERROR to display not found messages rather than errors.

Explore how to build a nested if function in Excel 2016 to handle multiple conditions, using value_if_true and value_if_false with absolute references to calculate tiered commissions.

Learn how to use the iferror function to control errors in formulas, replacing divide-by-zero with a meaningful result like no sales and preventing errors from affecting other formulas.

Explore how the len function counts characters, then subtract four from the total length and use the right function to extract the four-digit office code from employee IDs.

Clean text data in Excel 2016 advanced by removing all extra spaces from a text string while preserving single spaces between words. Apply the TRIM function to a cell, then copy the formula down to obtain cleaned data from databases or other sources.

Use the substitute function in Excel 2016 Advanced to replace text within a string, choosing the old and new text and optionally the instance to replace, preserving the original data.

Show formulas with the formulas tab or Ctrl+tilde to display all formulas in cells at once. Toggle the option to hide them again.

Master tracing precedents and dependents in Excel to see which cells a formula uses and which cells it affects, and learn to clear the arrows after auditing.

Monitor formula values in real time with the Excel watch window. Add watches to key cells across sheets and anchor the window to keep them visible while you work.

Explore how to locate and fix errors with trace precedents and trace error, and use the error checker to review your entire sheet for issues and how to fix them.

Master the scenario manager in Excel 2016 advanced to store multiple budget values in one worksheet, create original and operating scenarios, switch between them, and generate a summary report.

Use goal seek in Excel 2016 to set a formula's result to a target value by adjusting a single input cell via what-if analysis.

Explore how to analyze loan payments across different loan amounts and years using Excel data tables, the PMT function, and row and column input cells.

Discover Excel 2016 advanced protection options, including locking sheets, protecting workbook structure, password encryption, and digital signatures. Find these protections in backstage view under file info and in review tab.

Protect worksheets and the workbook with passwords, choosing where to apply protection—from individual sheets to the entire workbook—and understand password handling to guard edits and access.

Protect a worksheet in Excel 2016 advanced and tailor allowed changes such as formatting cells, inserting columns or rows, sorting, filters, pivot tables, charts, and editing objects.

Protect specific cells by locking or unlocking them and hiding formulas, then shield the worksheet to control visibility and edits in Excel 2016 advanced.

Explore additional protection features in Excel 2016, including mark as final to make the workbook read-only, restricted access by user or group, and digital signatures to detect workbook changes.

Learn to enable and use the PivotTable and PivotChart Wizard in Excel 2016 advanced, add it to the quick access toolbar, and create a pivot table on a new worksheet.

Create a calculated field in a pivot table to compute commissions by multiplying sales totals by 12%, add it to the values area, and rename it to commission.

Create calculated items in a pivot table to compute team totals, such as team A (AD+CM+DS) and team B (NC+PT), without changing the field groupings.

Apply conditional formatting to a pivot table using icons and data bars on sales totals, and see how formatting updates with filters and table expansion.

Learn to filter pivot table data in Excel 2016 using the field list or label dropdowns, filter both rows and columns, disable dropdowns for aesthetics, and clear filters.

Create separate filter pages for a pivot table by applying the report filter to the product code, generating a distinct sheet for each filtered item.

Enable the Power Pivot add-in in Excel 2016 to create a miniature database inside Excel by linking lists, building relations, measures, and KPI indicators for pivot tables.

Automate tasks in Excel by recording macros that generate VBA code you can replay across sheets and workbooks, while learning basic macro rules.

Display and use the developer tab in Excel 2016 advanced, enabling VBA and macros, while understanding macro security and the default disable macros with notification.

Record a basic formatting macro in Excel, named Format_Totaux, assign Ctrl+Shift+F, and save it in this workbook to apply number formatting, fill color, and borders.

Learn how to run a macro in Excel 2016 advanced, using Ctrl+Shift+F or the Alt+F8 macro dialog, duplicate formatting, and understand that undo isn’t available after execution.

attach a macro to a form button to simplify macro execution for non-experts, using the developer tab, insert controls, and the assign macro option to link later.

Unlock dynamic, reusable macros by enabling relative references and recording new macros in Excel 2016 advanced. Explore VBA code to expand formatting capabilities for totals across sheets.

Navigate the VBA editor to view and edit macros, inspect Project Explorer and modules, and modify sub procedures like range selections; learn to save workbooks as macro-enabled to preserve macros.

Attach a macro to the Quick Access Toolbar to run it in any workbook, customize its icon, and store it in the Personal Macro Workbook for universal access.

Explore form controls and ActiveX controls in Excel 2016 advanced, comparing easy data entry without VBA to flexible design with VBA, and enable developer tab to insert and customize controls.

Explore inserting form controls in Excel 2016 advanced, including check boxes and spin buttons, using the Developer tab, resizing, aligning, copying, and assigning macros to actions.

Add a combo box in Excel 2016 using the developer tab. Configure its input range and link cell, then drive values and lookup formulas such as VLOOKUP and INDEX.