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.

Master links and external references to keep data in its original location, avoid copies, and reduce errors in complex worksheets by linking data across worksheets and workbooks.

Connect a cell to data in another cell so updates propagate automatically, and distinguish internal links to worksheets from external links to workbooks using absolute and relative references.

Use the edit links dialog box to manage external links in your workbook. It lists linked workbooks and offers update values, change source, and break link options.

Create external references in formulas to link cells and ranges across worksheets and workbooks, displaying results in different locations and using absolute references when pulling data from another workbook.

Learn how to create internal and external links in Excel to pull total sales from multiple worksheets and workbooks, and build a quarterly sales summary.

Learn how to use 3-D references in Excel to summarize data across multiple worksheets and workbooks, consolidate results in one location, and analyze totals beyond simple summation.

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.

Learn how to use 3-d references to pull the same cell from multiple worksheets and apply a function across all instances, such as b-3 on three sheets.

Master 3-D references in Excel 2016 by using summary functions to aggregate data across multiple worksheets, specifying the sheet range with exclamation point and the target cell.

demonstrate how to use 3-D references in Excel to total and average quarterly sales across multiple identical sheets, using sum and average functions with a single 3-D formula.

Master 3-d references to summarize datasets with identical layouts, and apply data consolidation when sources differ across worksheets and workbooks.

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.

Access the consolidate dialog box from the data tab to select a summary function and define source ranges. Add or remove references and set top row and left column labels.

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.

Learn to work with multiple worksheets and workbooks to analyze and consolidate data from internal and external sources, using 3-D references for efficient cross-sheet summaries.

Master look-up functions in Excel to reference a value from another dataset based on criteria, understand syntax, and audit, troubleshoot, and correct workbook errors with increasingly complex formulas.

Explore how to use lookup functions in Excel 2016 to quickly access specific data in large datasets, enabling efficient analysis.

Explore the VLOOKUP function and other lookup functions that search a dataset using a lookup value, a table array, and a column index, with exact or approximate matches.

Use lookup functions to identify employee details by ID and row position, applying match and index to fetch name, department, region, manager, and extension.

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.

Use trace precedence and trace dependence commands to visualize connections among cells, worksheets, and workbooks with arrows showing feed and fed-by links; run cell at a time from formulas tab.

Explore trace arrows in excel, visualizing precedence and dependence from a starting dot to the dependent cell, with blue for same-workbook relations, red for errors, and dashed for cross-workbook links.

Explore how the go to dialog box navigates precedent and dependent cells, examines them for errors, and corrects issues using the black dashed trace arrows and the home tab.

Access the go to special dialog box from the home tab's editing group to access precedents or dependence on the same worksheet by selecting a target cell.

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 how the watch window and evaluate formula tools watch formulas and their results, breaking down complex functions argument by argument to quickly pinpoint errors across interconnected cells.

Use the watch window to monitor errors across worksheets, displaying each watched cell’s location, value, and formulas, updating in real time as precedents change.

Explore the evaluate formula dialog box to audit a formula, understand the order of operations, highlight each evaluated part, show results, and debug complex Excel functions using auditing utilities.

Observe how Excel evaluates formulas using the watch window and evaluate formula features, as you set watches on ranges and step through lookups in real time to troubleshoot without errors.

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.

Balance collaboration and security in Excel workbooks by sharing and protecting worksheets and workbooks, enabling others to review or input data while safeguarding links.

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.

Learn how comments act as a collaborative worksheet markup in Excel, enabling issue tagging and multi-user replies via the red triangle and Review tab.

Collaborate in real time by sharing workbooks, enabling multiple users to edit simultaneously from a central location such as sharepoint, while noting tables aren't supported and certain features are restricted.

Enable change tracking in Excel shared workbooks to mark edits, track changes to cell data and rows or columns, and approve or reject changes; past changes aren’t tracked by markup.

Enable change tracking with the highlight changes dialog. Choose who to track, when to track, and whether to highlight on screen or list changes on a separate sheet.

Filter changes with the select changes to accept or reject dialog boxes, set options by when, who, or where changes appear, then approve or reject individual edits.

See who changed data, when, and what was tracked in the accept or reject changes dialog box, once change tracking is enabled, with options to accept or reject all.

Use the compare and merge workbooks command to merge copies of a shared workbook into a single file not saved to a central location, prioritizing the most recent change.

Explore sharing Excel workbooks via backstage view, the share tab, and inviting people to edit a OneDrive saved workbook or email as attachments in different file formats.

OneDrive requires a Microsoft account or Office 365; it lets you access and share files from nearly anywhere, with two versions, OneDrive and OneDrive for Business, offering the same functionality.

Share your workbook with others who don't have Excel installed by using Excel online, included with Office 365 or a free Microsoft account, and view other users editing the file.

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.

Protect workbook and worksheet elements at levels. Workbook protection prevents adding or deleting sheets and renaming; worksheet protection locks cells, hides formulas, and applies via protection tab in Format Cells.

Use the protect sheet command to open the protect sheet dialog box, select allowable actions with checkboxes, and set a password to disable worksheet protection.

Explore the protect workbook command and its dialog for protect structure and windows. Learn how this feature prevents the addition of sheets and blocks other windows from opening.

Learn to use protect workbook options in Excel, including mark as final, password encryption, protect current sheets and workbook structure, restrict access, and add a digital signature to verify integrity.

Learn how to use the document inspector in Excel to scan workbooks for metadata, comments, personal information, and document properties before sharing externally.

Learn to protect Excel workbooks and sheets by locking and hiding cells, encrypting with a password, and restricting changes at both sheet and workbook levels.

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

Automate large and complex Excel workbooks with data validation and macros to reduce repetitive tasks, minimize errors, and save time.

Implement data validation to ensure only valid data enters a worksheet, preventing errors in formulas and linked references when multiple users input information.

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.

Enable the input message tab to prompt users with a pop-up that shows data validation guidance, including a title and a message presenting possible values when they select validated cells.

Configure data validation in Excel by selecting stop, warning, or information alerts to control invalid data, using the error alert tab to set the title and message.

Demonstrates how to apply data validation in Excel to prevent errors by restricting inputs to a drop-down list and decimal ranges with custom input messages and error alerts.

Discover techniques to scan an entire worksheet for errors using Excel's error checking and invalid data markup features, enabling quick detection of faulty formulas and data without tracing each cell.

Discover how the circle invalid data command flags cells that violate data validation by scanning the worksheet and briefly highlighting them with a red circle in Excel's data tools.

Explore how the error checking dialog box identifies and navigates common worksheet errors in Excel 2016, including division by zero, N.A. errors, null errors, value errors, and name errors.

Automate repetitive workbook tasks in Excel 2016 with macros built in visual basic for applications, creating reusable instructions to format data, apply formulas, standardize reports, and manage macro security.

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.

Explore how the macro dialog box manages macros, allowing view, run, delete, and edit via the Visual Basic Editor, with naming rules that start with a letter and avoid spaces.

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.

Learn to automate repetitive tasks in Excel by recording and editing macros with Visual Basic for Applications. Save as a macro-enabled workbook, run macros, and tailor code to format sheets.

Prevent invalid data and reduce error chain reactions with data validation; automate repetitive tasks with macros and use error checking to streamline workbook workflows.

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

Learn to use sparklines in Excel to visualize trends and relationships in large data sets, providing quick at-a-glance insights for sales performance without cluttered charts.

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.

Create spark lines in a sales workbook to visualize regional trends, leveraging a pivot table and conditional formatting, then customize line type, color, and markers for each state.

Visualize geographic data in Excel 2016 using the built-in 3D map to reveal regional relationships beyond pivot tables and charts.

Use the tour editor, field list, and layer pane to plot geographic data in the 3-D map, and control data value visualization, categories, and time frames across tour scenes.

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.

Plot regional sales over time with the 3-D map in Excel, adding state and month fields, and switch to region visualization to reveal per-state trends.

Explore advanced data visualization in Excel 2016 using a 3-D map and SPARC lines to present large datasets beyond tables and charts.

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.

Explore how two-variable data tables in Excel substitute two inputs to forecast outcomes, showing how expanding value ranges affects table size and projected expenses.

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.

Explore how to determine potential outcomes using what-if analysis with scenarios in Excel, enabling analysis for many variables without taking up excessive worksheet space.

Explore scenarios as a what-if analysis that changes displayed values for variables and formulas in Excel, without creating data sets, and learn to manage copies and 32 changing values limit.

Explore the scenario manager dialog box in Excel's what-if analysis to add, edit, delete, and merge scenarios, view changing cells and comments, and generate a summary of scenario results.

Click the add button to open the add scenario dialog box, name the scenario, select the changing cells, insert a comment, and prevent others from editing or showing it.

Define changing cell values using the scenario values dialog box, inputting individual values by clicking add up to 32 times to cover all possible scenarios.

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.

Discover how the Goal Seek feature uses what-if analysis to find the input needed to reach a predetermined outcome. Avoid manual re-entry and complex formulas.

Explore the Goal Seek feature, a one-variable what-if analysis that calculates a single input to reach a desired result using the dialog box on the Data tab.

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

Explore how Excel analyzes time-based data—such as sales by region, product performance, or server utilization—identifying seasonal patterns and using the Forecast Sheet in Excel 2016 to predict future trends.

Learn to create a forecast sheet in Excel to project monthly sales for 2017 using what-if analysis, selecting data, and choosing a line or column chart with confidence intervals.

Explore how Excel 2016 uses what-if analysis, data tables, scenarios, Goal Seek, and forecast sheet to forecast data and visualize trends.

Summarize the advanced Excel techniques covered in course closure, including look up functions, auditing and error checking, and cell types. Highlight sparklines, data mapping, forecasting, workbook protection, and change tracking.