Microsoft 365 Excel - Intermediate

Learn to pull values from other worksheets and workbooks with formulas, creating linked totals across months and weeks. Use the workbook links feature to manage, refresh, and protect links.

Link workbooks with 3D references to sum the same cell across multiple sheets, maintain the sheet range, and use workbook protection to prevent structural changes.

Use the consolidate feature to combine regional values from multiple worksheets or workbooks into a single summary, summing or averaging by left labels and creating optional links to source data.

Create named ranges to simplify formulas and navigate worksheets, using descriptive names like January values and starting with a letter, avoiding spaces or cell references.

Master creating named ranges in Excel using the name box or define name, applying to headers or values, and setting workbook scope with absolute references.

Select a continuous range, choose create from selection to name ranges from the top row or rows from the left column. Then use these named ranges in formulas for totals.

Explore how sorting arranges data by order or grouping, and how filtering narrows results by criteria, such as sales by salesperson or northeast region, or greater than $10,000, in worksheets.

Learn to sort lists in Excel using quick sorts and the multi-level sort dialog to order data by agent, line, and sale amount with ascending or descending options.

apply and manage filters in excel to hide nonmatching data, using drop downs, text and number filters, and the clear filter option to reveal the full dataset.

Learn to create subtotals in Excel by sorting by the grouping field, using the subtotal tool, choosing sum or average, and managing replacements and outline collapses.

Convert data lists into Excel tables to enable filters, persistent headings, automatic formatting, structured references, quick totals, and pivot table readiness.

Create a table from a continuous range using the insert tab or Ctrl+T, ensure headers, rename it to a named range, and resize or expand with rows or columns.

Turn data into a table to easily apply styles and alternate row colors. Configure header row, total row, banded rows or columns, first and last column accents, and filter buttons.

Sort by date oldest to newest and by line number, and filter for wait time greater than or equal to one, customer satisfaction less than four, and issue D.

Insert slicers into Excel tables to provide visual filters for agent, line, and issue, enabling quick single- or multi-select filtering with clear buttons and a tidy dashboard layout.

Learn to use a table's total row to compute a rating score as a percentage, using wait time and call length, with divide and IFS to auto fill down.

Learn how to use Excel's remove duplicates from the data tab or a table, select fields to check, and copy unique values to a new sheet for further analysis.

Export, refresh, and convert tables to external formats like SharePoint lists or Visio diagrams, manage external data relationships, and convert back to ranges when needed.

Learn how conditional formatting applies formats to cells or ranges based on criteria, including blank cells, such as turning sales values green when goals are met.

Apply conditional formatting in Excel with highlight cells rules to flag values below a threshold. Use top bottom rules to mark top or bottom performers, with formatting updating automatically.

Explore data bars, color scales, and icon sets in conditional formatting, using breaking points, named ranges, and goal-based rules to visualize data effectively.

Apply custom conditional formatting in Excel using highlight cells rules. Format all T-400 resolution codes with an orange fill and borders.

Create custom conditional formatting rules using formulas and apply them across a column. Use mixed references to copy formatting while basing rules on a cell's value.

Modify or remove conditional formats across the entire worksheet, manage rules, edit or delete icon-set rules, adjust breaking points, stop rules when true, reorder or duplicate rules, and clear rules.

Explore how Excel charts visually represent data from cells, reflect value changes, and help you choose appropriate types like line, column, pie, or bar, then create, manipulate, and format charts.

Highlight the data and labels, then use the insert tab and recommended charts to create charts like column, line, map charts, funnel, or pie, adding labels when needed.

Learn how chart elements differ by chart type, from simple pie charts to column charts with axes, grid lines, data labels, and legend, accessed via the chart design context.

Explore how to customize chart elements in Excel: adjust pie and funnel charts with quick layouts, add titles, legends, data labels, axes, grid lines, and more options for clear visuals.

Learn how to move charts between sheets, reposition and resize them, switch chart types (column to donut), and update data labels and regions.

Filter a chart directly using the chart filters option to selectively display series or categories without altering the underlying data, keeping other charts unchanged.

Apply pre-made chart styles and color options to format charts in Excel. Manually format axes, legends, and chart areas, with automatic legend updates and background fills.

Create a dual axis chart in Excel by using a combo chart with a secondary axis for percentages, enabling simultaneous display of total production and percentage of total.

Learn to forecast future values with trend lines in Excel by applying a linear trend to a selected series, and forecast two periods ahead using the chart's trend line options.

Save your customized clustered column chart 2D as a template to reuse its formatting with different data, then insert it from the templates folder via recommended charts.

Display trends in a data range by inserting sparklines inside a cell, choose line, column, or win loss, and customize high, low, first, and last points.

Master pivot tables and pivot charts to query, organize, and summarize data in interactive worksheets, arranging sales by region and product in a matrix.

Turn the data into a table to enable automatic expansion and easy refresh of a pivot table, then create the pivot on a new worksheet using the field list.

Master the pivot table field list to build, sort, and filter pivot tables, placing descriptive fields in rows, columns, and values to summarize sales by agent, line, and issue.

Use the pivot table analyze tab to name pivot tables, connect slicers to multiple tables or charts, refresh data, move pivots, and toggle lists and labels.

Format pivot tables with the design tab, adjusting subtotals and grand totals, selecting compact, outline, or tabular layouts, and applying slicers and styles for clear, spaced results.

Learn to create a pivot chart from an existing pivot table, or as a pivot chart pivot table combination, then move and hide source data to keep charts clean.

Format the pivot chart to complement the pivot table, rename the sum to sales total, switch to a three-dimensional bar chart, format axis units in millions, and apply a style.

Insert slicers and a timeline to create interactive filters for a pivot table. Filter by agent and product code with slicers and date-based timeline controls to analyze monthly data.

Format slicers in Excel, adjust their display, and set report connections so a slicer filters multiple pivot tables and charts on a dashboard.

Explore the Analyze Data tool on the home tab to generate pivot tables and pivot charts, and ask questions like 'average wait time per agent' to quickly analyze data.

Access pivot table and pivot chart wizard via quick access toolbar or Alt+D, P, then follow steps from data source to place the pivot table on a new worksheet.

Create a calculated field in a pivot table to explicitly compute a 12% commission from sales, replacing implicit calculations; edit, reuse, and delete the field across pivot tables.

Create calculated items in a pivot table to combine descriptive items into agent group a and agent group b, enabling consolidated data without collapsing groups.

Apply conditional formatting in pivot tables with a scope that targets only sales total values for each agent, using icon sets to track goals such as 8 million.

Explore filter pages in pivot tables to generate separate worksheets based on a report filter. See how pre-filtered sheets for each selected item enable focused analysis.

Use PowerPivot to build a data model that combines multiple tables into a single analysis. Create pivot tables and pivot charts from related tables, with relationships and slicers.

Explore pivot tables built on a data model to power dashboards, and convert lists to tables to unlock powerful features. Use slicer for filtering to boost interactivity and engagement.