Excel 2016 Intermediate

Learn to tailor the Excel ribbon by hiding tabs, moving command groups, and creating custom tabs like Ed's Favorites, with options to import, export, and reset customizations.

Customize the quick access toolbar by adding frequently used commands and rearranging items. Right-click commands in the ribbon to add them, and place the toolbar above or below the ribbon.

Customize Excel by adjusting general and formula options in the backstage view. Choose automatic or manual calculations, formula auto complete, and options for error checking and default workbook settings.

Explore how to customize AutoCorrect in Excel 2016, enable options like capitalizing sentences and replacing text as you type, and manage entries to fix common misspellings.

Set Excel 2016 save defaults to the default format (xlsx or 97–2003), auto recover options, and default file and template locations to streamline saving and ensure compatibility.

Discover how to customize Excel options in the 2016 intermediate course, from move-after-enter and decimal defaults to paste behavior, image compression, charts, and custom lists, with careful change guidance.

Link values across sheets and workbooks with formulas and the proper syntax, including workbook names in brackets, sheet names with exclamation points, and the relevant cell references.

Learn to create 3d references that total the same cell across multiple worksheets in Excel 2016. Use a range of sheets to pull cell values and build a summed total.

Master consolidating data across multiple sheets using Excel 2016's Consolidate feature, applying sum or average, leveraging leftmost-column or top-row labels, and linking to source data.

Master named ranges in Excel by naming a continuous cell block, using it in formulas as absolute references and navigation bookmarks, and applying basic creation rules.

Create named ranges in excel using the name box and define name tool, with examples like Annex and Annex West. Manage scope and edits in name manager.

Learn how to create multiple named ranges from top row using the create from selection tool in Excel 2016 intermediate, then use those named ranges in formulas to sum columns.

Explore how logical functions in Excel 2016 intermediate compare criteria and return true or false, enabling dynamic results with if, and, or, and related formulas.

Use the AND function to test multiple conditions; it returns true only if all tests are true, with comma-separated arguments and examples like 1.5 million and five continuing education credits.

Explore how the or function in Excel 2016 evaluates data by returning true when any condition is met, with practical examples comparing or to and and showing real-world filters.

Master the IF function by understanding its syntax and applying conditional logic to calculate commissions, using logical tests, value if true, and value if false.

Learn to nest and/or inside an if function to build powerful Excel formulas, using and/or logic to determine education bonuses based on sales totals and continuing education credits.

Learn how to retrieve data from other sheets or workbooks using lookup functions. Compare horizontal and vertical lookups, HLOOKUP and VLOOKUP, and when to apply each based on headings.

Learn how VLOOKUP and HLOOKUP work, focusing on lookup value, table array, column index, and exact versus approximate matches using named ranges. Handle missing data with clear NA errors.

Learn to use HLOOKUP in Excel 2016 Intermediate to assign percentage grades with an approximate match, using a grade table and a row index of 2.

Discover how to sort data in Excel 2016 intermediate by last name or sales amount, and filter to show only records meeting specific criteria on the data tab.

Sort data quickly with quick sort or multi-level sorts, relying on Excel to recognize a continuous range, then use the custom sort window to order by agent and sale amount.

Master filtering data in Excel 2016 by turning on filters, using dropdowns to apply multi-criteria filters on agent, line, customer satisfaction, and sales value, then clear filters when needed.

Learn how to turn a data list into a table in Excel 2016, using insert table or Ctrl+T, and enjoy automatic filters, sticky headers, structured references, and quick totals.

Explore the elements of a table in Excel 2016 intermediate, including the table tools contextual tab. Name a table to create a named range and extend formulas with new data.

Format a table in Excel using premade styles, enable banded rows and columns, and customize elements such as the header, first and last column, or create your own table styles.

Sort excel 2016 tables by single or multiple fields using quick sort options, color sorting, or the data tab's custom sort to arrange by A to Z, oldest to newest.

learn to filter Excel tables in 2016 intermediate, using top filters, custom text filters, and search to select agents; apply single or multiple filters and clear them when needed.

Use slicers to visually filter data in a table, selecting product codes and agents, with multi-select, clear filters, and customization through slicer tools such as captions and themes.

Execute dynamic calculations in tables using structured references that extend formulas down the column, then use the total row to quickly apply functions like average or count.

Remove duplicates in Excel with the remove duplicates tool on the table tools design tab. Sort first, then select one column or any combination of columns before removing.

Manage external data in Excel 2016 by refreshing a single table or the whole workbook, unlinking sources, exporting ranges, and converting tables back to ranges.

Explore conditional formatting in Excel 2016 intermediate by applying automatic formatting based on set conditions and multiple rules. Use it to highlight data and produce professional, easy-to-read reports.

Learn to use conditional formatting in Excel 2016 intermediate, highlighting cells and top/bottom rules to emphasize sales data. Formats auto-update as values change, with clear rules and top percent options.

Explore conditional formatting options in Excel: data bars, color scales, and icon sets, with customizable rules and breaking points to visualize high and low values.

Explore custom conditional formatting in Excel 2016 using new rule options, color scales, data bars, and formulas to format cells by values, text, dates, blanks, and duplicates.

Explore how to use the rule manager to view, create, edit, delete, and reorder conditional formatting rules; learn about precedents and clearing rules from cells or the entire sheet.

Apply subtotals to a regular range to show itemized totals for unique lines, and use outline groups to collapse similar items for easier navigation, found on the data tab.

Master subtotals in Excel 2016 intermediate by sorting data, calculating per-agent sums, and adding averages for customer satisfaction, using outline controls to view totals, details, or both.

Group and ungroup data in Excel 2016, create manual outlines and subtotals by rows or columns, and collapse or expand groups across fields such as line info, dates, and times.