Excel 365 Intermediate Features

Overview

This lecture covers intermediate Excel 365 features for organizing, analyzing, and visualizing data, including tables, sorting, filtering, functions, charts, pivot tables, data validation, conditional formatting, and linking data.

Creating and Using Tables

• Tables enhance data readability and allow features like locked headers and alternating row colors.
• To create a table: select data (Ctrl+A), go to Insert > Table, and confirm headers.
• Table tools enable formatting options, total rows, and easy expansion with new entries.

Flash Fill

• Flash Fill extracts or combines data in columns by following user-provided examples.
• Access via Data > Flash Fill or shortcut Ctrl+E.
• Useful for splitting names, extracting company domains, or separating letters/numbers.

Sorting Data

• Sorting organizes data by columns; headers should be identifiable (e.g., bolded).
• Single-level sorts use A-Z or Z-A commands; multi-level sorts use the Sort dialog.
• Custom sorts arrange data by custom lists (e.g., days of the week).

Sorting and Unique Functions

• SORT function syntax: =SORT(array, sort_index, sort_order).
• SORTBY sorts data by another range: =SORTBY(array, by_array, [order]).
• UNIQUE returns unique values or those appearing exactly once in a list.

Filtering Data

• Use Data > Filter to add filters to columns; icons indicate active filters.
• Multiple filters can be applied by column or using slicers for tables.
• Slicers provide interactive filtering buttons for tables and pivot tables.

Subtotals and Quick Analysis

• Subtotal outlines (Data > Subtotal) give summary calculations for categories.
• Quick Analysis Tool offers rapid formatting, charts, and totals on selected data.

Charts and Chart Templates

• Recommended Charts suggest chart types based on selected data.
• Chart Design and Format tabs allow customization (titles, colors, backgrounds).
• Save chart templates for reuse; printing can include only the chart or with data.

Sparklines

• Sparklines display small trend charts within cells.
• Insert via Insert > Sparklines (Line, Column, Win/Loss); can autofill for multiple rows.
• Sparkline tools allow styling and highlighting of high/low points.

Pivot Tables and Pivot Charts

• Pivot tables summarize and analyze large data sets with drag-and-drop interface.
• Numbers go in Values; categories in Rows/Columns/Filters.
• Pivot charts visualize pivot table data and synchronize automatically.
• Combine pivot tables, pivot charts, and slicers for interactive dashboards.

Data Validation

• Data validation restricts entries (e.g., number ranges, lists, date formulas).
• Input messages and error alerts guide or block users from invalid entries.
• Custom validations use formulas (e.g., for phone number length and format).

Conditional Formatting

• Conditional formatting changes appearance based on cell values or formulas.
• Use pre-set rules for highlighting cells (e.g., equal to, greater than).
• Custom formulas highlight entire rows based on multiple conditions (e.g., bedrooms and bathrooms).

Linking Data

• Cells can link to others within the same sheet, different sheets, or even other workbooks using formulas (e.g., =A3).

Key Terms & Definitions

• Table — A structured range with enhanced features and formatting.
• Flash Fill — Tool that auto-fills values based on detected patterns.
• Slicer — Visual filter tool providing clickable buttons for tables and pivot tables.
• Subtotal Outline — Automatically summarizes grouped data with totals.
• Sparkline — Mini charts embedded in worksheet cells for trend visualization.
• Pivot Table — Tool to summarize and analyze large data sets in table format.
• Pivot Chart — Chart linked to a pivot table for dynamic data visualization.
• Data Validation — Rules set to control what can be entered in cells.
• Conditional Formatting — Changes cell appearance when certain criteria are met.

Action Items / Next Steps

• Practice creating tables, using Flash Fill, sorting, and filtering sample datasets.
• Try building and customizing a chart or pivot table with slicers.
• Set up data validation and conditional formatting rules on a worksheet.
• Explore using the SORT, SORTBY, and UNIQUE functions on your own data.