Microsoft Office 2016 Excel : Advanced

Master consolidating data across multiple worksheets and workbooks in Excel by summarizing in a single sheet and consolidating into reports using formulas and 3D references.

Group worksheets to apply formatting and edits across multiple sheets at once, enabling 3-D references for consistency; select contiguous tabs with shift or noncontiguous with control, then ungroup when finished.

Consolidate data from multiple locations in Excel by grouping data via relative positions or by categories using row and column labels, then summarize with Excel's summary and statistical functions.

Learn to consolidate data across multiple Excel workbooks using the consolidate function to summarize quarterly sales and quantities for Q1–Q4, with links that update when source data changes.

Trace cells to identify exactly which cells feed erroneous data and test related formulas. Use a clear graphical cell tracing view to see instant workbook relationships and isolate issues quickly.

Learn how precedent cells feed data into formulas and how dependent cells rely on information from outside of themselves, with mixed cells illustrating both roles in a single worksheet.

Trace precedent and dependent cells in Excel to visualize how lookups feed into calculations, identify data sources across worksheets, and diagnose errors using trace arrows and the go to dialog.

Explore Excel techniques with lookup functions to retrieve data and adjacent information, and master formula auditing tools like trace dependents and precedents, watch dialog box, and evaluate formula for debugging.

Explore how teams collaborate on Excel workbooks, track versions, and consolidate input from multiple locations to produce error-free sales reports awaiting manager review and approval.

Excel's accessibility checker scans workbooks for issues that hinder users with disabilities, flagging errors, warnings, and tips for improving accessibility, such as adding alternate text for tables and charts.

Learn how Excel 2016 enables collaborative workbooks, track changes, comments, and protect workbooks, plus review, accept or reject edits, and compare or merge versions.

Learn how to protect worksheets and workbooks in Excel to guard against unauthorized access and changes while sharing files safely with colleagues and external users.

Leverage track changes, comments, password protections, and digital signatures to collaborate with others on workbooks while safeguarding against unauthorized edits.

Learn to restrict Excel cell entries with data validation, including thresholds, type checks, and lists, using the Data Validation dialog box with Settings, input message, and error alert.

Explore how the settings tab defines data validation criteria to control entries in worksheet cells. Excel offers seven criteria: date, whole number, decimal, time, text length, list, and custom formulas.

Save workbooks as macro-enabled (.xlsm). The trust center blocks macros by default with notification and offers options like digitally signed macros or disabling all macros.

Record macros with the macro recorder or write them in VBA; view and edit the generated code in the Visual Basic for Applications window using the Project Explorer.

Record macro dialog box lets you name a macro, assign a shortcut, and specify storage, while recording steps like selecting cells and entering data, with absolute references.

Save macros to the personal workbook (Personal.xlsb) to use them in all macro-enabled workbooks, ensuring they open with Excel via the hidden personal workbook.

Discover how sparklines and mapping data transform complex information into instant visual insights in Excel, using charts and maps for engaging presentations.

Insert sparklines into cells as miniature charts that become the cell background, displaying line, column, or win/loss trends; copy with the fill handle and apply pre-formatted styles.

Discover how to create sparklines using the create sparklines dialog box on the insert tab, selecting the data range and the location range for placement.

Explore sparkline tools contextual tab in Excel, use design tab to apply styles, colors, and sparkline types, and toggle data markers while grouping or ungrouping sparklines for visual emphasis.

Launch 3D maps to visualize a tour showing time-based relationships between geographic locations, population numbers, and sales value, with default tours and options to customize.

Explore how Excel 2016 forecasts data using data tables, scenarios, Goal Seek, and forecasting data trends to model multiple outcomes and plan for changing variables.

Discover how to use Excel's data tables and other what-if features to determine potential outcomes as inputs change.

Explore what-if analysis in Excel by using Scenario Manager, Goal Seek, and Data Tables to compute results across different values for variables like interest rates, down payments, and commissions.

Explore one-variable data tables as a first what-if analysis tool that shows outcomes for a formula across a set of values, with options for column or row orientation.

Use the data table dialog box to define the input cells for your data tables by specifying the row input cell and the column input cell references.

Demonstrates creating one- and two-variable data tables in Excel to model what-if scenarios for sales and expenses. Adjust growth rate and expense inputs to project outcomes in a forecast workbook.

Learn to run Excel scenarios quickly using the scenario command, which appears in a drop-down after adding it to the quick access toolbar or a custom ribbon, bypassing scenario manager.

Explore how to use the scenario manager to create what-if projections for advertising campaigns in Excel, compare original and revised revenue and expenses, and view a side-by-side scenario summary.

Use the Goal Seek feature to determine the units needed to break even by setting a zero profit target and adjusting the units sold.