Excel 2013 Advanced

Learn to use sumif to analyze a range and include values in the sum when the criteria are met, and distinguish it from sumifs for multiple conditions.

Compute conditional production averages with averageif in Excel 2013 advanced. Copy with absolute references and verify results; note the syntax matches sumif and countif.

Learn to use countif to count cells in a range that meet a specific criterion, with the two-argument syntax (range and criteria), and contrast with countifs for multiple conditions.

Use vlookup with true to find an approximate match and return the next closest value, illustrated with sales commissions and a rate table.

Explore how to use hlookup with true to perform an approximate match, compare it with vlookup, and assign letter grades from a grade table.

Explore the index function in Excel 2013 and its use with match to pull data from anywhere in a table, overcoming vlookup and hlookup limits.

Learn how the match function returns an item's position in a range using look up value, look up array, and exact or approximate match types, with a product id example.

Combine index and match to dynamically pull data from anywhere in a data set, nesting match for row and column numbers, returning values like stock status.

Use VLOOKUP to compare two lists for exact matches, revealing items in List 2 not in List 1 by returning N/A for missing entries; this works across sheets or workbooks.

Learn to use VLOOKUP with the ISNA function to detect #N/A errors, nest it in an IF statement to show the item or not found, and explore optional conditional formatting.

Learn to create nested if statements in Excel 2013 by placing an if inside another if to implement if-else logic, use descending tests, and apply commission multipliers.

Learn how to use the IFERROR function to replace any Excel error with a custom message or blank, wrapping around formulas like price per unit to prevent cascading errors.

Learn how to use the len function to count characters and extract office codes from employee IDs with the right function. Nest len inside right for varying code lengths.

Use the trim function to remove extra spaces from a text string, preserving single spaces between words, and clean imported data by copying results and pasting values.

Learn how to use the substitute function in Excel to replace text in specific cells, specify old and new text, and choose which instance to replace.

Show formulas tool reveals all formulas in cells from the formulas tab, using ctrl+~ to toggle them for auditing and printing with formulas visible, then turn off before printing.

Explore trace precedents and trace dependents to visualize how formulas reference cells, reveal dependencies across worksheets or workbooks, and audit impacts, including removing arrows.

Add a watch window to monitor a formula’s value across worksheets or a workbook, using the Formulas tab; add and dock or float the window to keep values visible.

Explore Excel 2013 advanced formula auditing with error checking, trace error, and evaluate formula to identify, trace, and correct errors and verify calculation steps in multi-step formulas.

Explore Excel 2013 advanced scenario manager within what-if analysis to store multiple value sets in one sheet, create scenarios like original values and increased training, and view summaries.

Learn to use goal seek in Excel 2013 advanced to set a formula's result to a specific value, such as 15000, by changing input cell in a what-if analysis.

Explore how to use Excel's data table under what-if analysis to display varying monthly payments based on loan amount and years, driven by the PMT function.

Explore protection features for cells, worksheets, and workbooks in Excel 2013 advanced, including encryption, password protection, and digital signatures. Learn how to set permissions, restrict access, and safeguard workbook structure.

Protect sheets and the workbook structure with passwords to prevent changes. Encrypt the entire workbook to open or modify with separate passwords via save-as options.

Protect a worksheet with the protect current sheet feature, choosing what users may do, such as inserting rows or columns, formatting, and using filters, pivot tables, and scenarios.

Unlock the worksheet, then lock selected cells and protect the sheet to prevent changes; use format cells to set locked and hidden, and hide formulas from the formula bar.

Explore advanced protection features in Excel 2013, including mark as final as an honor-system indicator, restrict access via rights management, and a digital signature that ensures workbook integrity.

Use the PivotTable and PivotChart Wizard to create PivotTables from a list or database, add it to the quick access toolbar, and place results in a new worksheet.

Create a calculated field in a pivot table to compute earned commission by multiplying the original sale amount by 12 percent, then add, edit, or remove as needed.

Create a calculated item in a pivot table to form a permanent group of agents and display their total, without having to expand or contract it.

Apply conditional formatting to a pivot table using icon sets and data bars to visualize totals and commissions, with manage rules to edit or remove formatting as data changes.

Explore how to use the filters area in the pivot table field pane to filter data by line and product codes, including multiple selections.

Create filter pages from a pivot table using show report filter pages, outputting separate worksheets for each filtered item, enabling independent manipulation and targeted agent reports.

Enable the Power Pivot add-in to combine multiple lists into an Excel data model and create pivot tables from related data, with custom formulas and KPIs.

Learn how macros automate tasks in Excel by recording actions and generating VBA code, and explore naming rules and how to run macros across workbooks.

Enable the developer tab in Excel to access the VBA editor, macros, and macro security settings, with the default to disable all macros with notification and enable content when needed.

Record a basic formatting macro in Excel 2013, name it, assign a shortcut, and store it in the workbook. Stop recording to avoid looping.

Learn how to run a macro in Excel 2013 advanced using a keyboard shortcut, the macros window, the developer tab, or the view tab. Alt+F8 provides another run option.

Learn to assign a macro to a form button in Excel 2013 advanced using the developer tab, enabling a user-friendly button that runs a macro from the workbook.

Demonstrate advanced Excel 2013 macros using relative references to format names across rows. Understand limitations like no undo and editing via VBA, plus basics of import/export, pivots, and charts.

Open the macro window from the developer or view tab to view and edit existing macros, then use the VBA editor to modify module code and sub-procedures.

Add a macro to the quick access toolbar in Excel 2013, customize its symbols and header, and run it across all workbooks via the personal macro workbook.

form controls add interactive buttons and objects to spreadsheets, making data entry easier and the interface more user-friendly. ActiveX controls offer deeper VBA customization for flexible design.

Explore adding spin buttons and check boxes in Excel 2013 advanced, comparing regular form controls with Active X controls, and using macros or custom VBA code to respond to interactions.

Insert a combo box into a spreadsheet to create a dropdown list from input range. Link it to a cell and, using ActiveX with VBA, trigger actions based on selections.