Excel 2019 Intermediate

Learn to reference cells across sheets and workbooks in Excel, using brackets for external files, spaces needing quotes, and exclamation points, plus how to manage links.

Learn to create three-dimensional references in Excel by summing the same cell across multiple sheets using the pswk22:pswk25!N3 syntax, with same layout and quotes for spaces.

Learn to use the consolidate feature as an alternative to three-dimensional references when data categories are out of order, and bundle sums across files with or without linking.

Name cells or ranges as aliases to reference them easily in formulas across sheets, using absolute references in vlookup or index match, and follow naming rules starting with a letter.

Learn to create and manage aliases in Excel using the name box and define name, then apply them in formulas and navigate cells with ease using the name manager.

Use create from selection to auto generate aliases from the left column or top row for adjacent cells, with underscores replacing spaces when needed.

Learn how sorting rearranges rows in Excel to order data and how filtering hides records by criteria to reveal only what you need, with multi-level sorts.

Learn to sort lists in Excel 2019 by turning on filter buttons or converting data to a table, selecting a single cell, and using single or multi-level custom sorts efficiently.

Learn to filter lists in Excel by turning on the filter, choosing unique records or specific criteria, and applying text, date, and number filters, with advanced filtering to copy results.

Learn how to add subtotals in Excel 2019, choose sum or average, and place totals after each change in sort order, while avoiding pivot table disruption.

Learn how to create and use Excel tables to enable automatic filters, keep header visibility, preserve formatting with insertions, auto-fill formulas, use structured references, and streamline pivot table workflows.

Learn how to work with Excel tables by naming, resizing, and using the table tools design tab; understand headers, auto filter, total row, and how tables expand with new data.

Explore how to format an Excel 2019 table using the design tab and table styles, including header row, total row, filter buttons, banded rows/columns, and creating or setting default style.

Learn to sort and filter Excel tables using the auto filter at the top or by right-clicking, including sort by color, custom sorts, and contextual filters like begins with.

Learn to filter a table with slicers in Excel, beyond standard filters. Insert multiple slicers from the Design tab, resize and arrange them, and use clear filters and multi-select.

Turn on the Total Row to sum, average, or count in a table with subtotals that update when you filter. Explore how tables auto expand and integrate with other formulas.

Remove duplicates in Excel tables while preserving header awareness. Decide precision to keep unique records by date and agent.

Refresh data to update a SharePoint table, adjust formatting, preserve row numbers, unlink to stop updates, or convert to range, losing table features, branding, and Power Pivot compatibility.

Define conditional formatting as formatting driven by cell content, and apply it using two types: range-insensitive and range-based, with color scales, data bars, or icon sets.

Apply conditional formatting in Excel 2019 using highlight cells and top and bottom rules, adjust thresholds, colors, rule order, add data bars, color scales, and icon sets for insights.

Explore Excel 2019 conditional formatting with data bars, color scales, and icon sets, using proportional bars, gradients, and KPI icons, and customize minimums, maximums, and rule ranges.

Create custom conditional formats in Excel 2019 intermediate, adjusting fill, border, and font to highlight values and use rules for numbers or dates based on thresholds.

Create custom conditional formatting rules in Excel 2019 using formulas like D2 > F2 to highlight when wait time exceeds call length, and apply across rows with mixed references.

Explore how charts visually represent Excel cell data, from line, column, and pie to advanced types like sunburst and funnel, including pivot table limitations and practical uses.

Learn to create charts in Excel 2019 by selecting data and labels, using insert or quick analysis to visualize with recommended charts.

Identify and customize chart components using the design and format tabs, chart elements, and the format pane. Adjust axes, titles, data labels, grid lines, legend, and trendlines to tailor charts.

Modify chart elements in Excel by toggling titles, data labels, data tables, data callouts, legend, and gridlines on or off, using the plus button or add chart element.

Learn to change chart types and move charts in Excel 2019 with the design tab, including clustered column options and moving charts to new sheets or other programs.

Filter charts in Excel 2019 using the filter button to temporarily hide regions or quarters. Use select data to permanently remove items by adjusting the chart range.

Explore formatting charts in Excel 2019 by selecting styles, colors, and layouts, then fine-tune fonts, fills, and axis options, and save as a template.

Format chart numbers in Excel by setting the vertical axis to currency or accounting, adjusting decimals, and applying display units such as millions, customizing data labels, gridlines, and tick marks.

Create dual axis charts in Excel by pairing a clustered column for total production per line with a line for escalations on a secondary axis to show two scales.

Explore how to create trendlines in Excel and forecast data with linear, exponential, and moving average methods, and use the built-in forecast sheet for future projections.

Save and reuse chart templates in Excel 2019 to quickly reproduce styled charts with new data, using right-click, save as template, and manage templates for easy sharing.

Explore sparklines in Excel 2019 intermediate to display quick trend visuals in a single cell using line, column, or win/loss types, with adjustable axis and grouping options.

Explore how pivot tables in Excel 2019 intermediate summarize large data into a compact table by dragging fields, emphasizing repetition and numeric data for sums and counts.

Create pivot tables from data sources, using tables for easy refresh. Use the data model with Power Pivot to connect related tables and quickly reuse pivots by copy and paste.

Master pivot table manipulation using the fields pane to arrange data in rows, columns, filters, and values. Set custom names for calculations and control the display with number formats.

Explore managing and analyzing data with pivot tables in Excel 2019, using the Analyze tab, field list, refreshing data, and grouping by months and years.

Explore how to style pivot tables using the design tab, apply preset styles, show or hide headers, adjust subtotals and grand totals, and choose compact, outline, or tabular layouts.

Create a pivot chart from a pivot table using the Analyze tab's PivotChart button, choose standard chart types, and note that multiple charts require separate pivot tables.

Learn to format and customize a pivot chart by using the analyze and design tabs, turning off field buttons and the field list, and applying filters like a pivot table.

Insert slicers and timeline slicers to filter pivot tables and pivot charts, then connect them across multiple pivots using report connections.

Master the pivot table wizard to create pivot tables from an Excel list or external data, consolidate multiple ranges, and leverage Power Pivot for complex sources.

Create a calculated field in a pivot table to compute values like a commission from original sales amount, and learn to modify or delete it.

Create a calculated item to combine row labels like Main40, Main41, and Main42. Manage the resulting extra item and consider a relational data model with Power Pivot for efficiency.

Apply conditional formatting to a PivotTable with color scales or data bars, then observe how filtering and refreshing adjust the formatting.

Create filter pages from a pivot table to generate a sheet for each value in a field using show report filter pages.

Power Pivot add-in in Excel 2019 enables analyzing multiple tables with relationships, calculated fields, and DAX functions to build pivot tables, charts, and KPIs from large data sets.

Maximize Excel 2019 intermediate skills by mastering range names, leveraging tables for data protection and better pivot tables, and embracing Power Pivot for large data modeling and BI.