Excel Intermediate: Data Management & Analysis Tools

Mastery of Excel is developed in stages: first you learn basic formatting, functions and formulas, and build a few spreadsheets yourself. Easy enough. But moving to the next level of proficiency is hard! To create truly professional-looking spreadsheets, you'll need to know how to organize data into elegant, interactive tables that dynamically generate colorful charts and graphs. To bring you up to an intermediate-level usage of Excel, we'll train you in how to use these core features:

• Advanced Tables. Beyond organizing data in rows and columns, we'll show you how to create filters that display only certain rows: for example, all the rows where the "City" column is set to "New York." You'll also learn how to sort entire tables based on values in one or more columns: for example, sorting customers by annual revenue. You'll learn how to instantly colorize, stripe, filter and subtotal a simple table of data using Excel's ever-popular "Format As Table" feature.
• Charts. Excel has 10 major chart types, everything from basic line charts to scatter plots. We'll show you how to add impressive data visualizations to your spreadsheets: line charts, bar graphs, pie charts, and more.
• Pivot Tables. One of Excel's most powerful — and least understood — features, pivot tables auto-magically convert static rows and columns of data into a responsive table that will re-organize itself to instantly display subtotals, averages, or other aggregate functions — depending on which fields you choose. Instead of creating 3 different tables to see sales by customer rep, customers by city, and revenue by state, you'll learn how to create a single pivot table to display all 3 sets of information in one place, based on your selection of different field groupings. You'll also learn how to generate pivot charts from the data.
• Tips & Tricks. The training also covers a few techniques we've selected as the most useful in your journey toward Excel mastery. These include "conditional formatting": coloring cells based on their values (e.g., heat maps); removing duplicates (common in data cleanup); data validation: preventing anything except certain data types (e.g., dates) being entered in a cell; and cell protection: preventing accidental typing into a cell with a formula.

After Intermediate Excel, you'll be ready for advanced Excel training.

Working with Lists, Tables & Charts

·      What is a well-defined list?

·      Inserting Tables

·      Flash Fill

·      Multi-Level Sorting

·      Removing Duplicates

·      Filtering Records

·      Using Slicers to filter

·      The Quick Analysis Tools

·      Inserting Data Charts

·      Recommended Charts

·      Editing Chart Elements

·      Adding/Removing Data From A Chart

·      Creating Custom Chart Templates

·      Printing Charts

·      Inserting Sparklines

PivotTables, Data Validation & Protection

·      Creating PivotTables from Lists or Tables

·      Office 365 PivotTable Enhancements

·      Filtering PivotTables

·      Multi-Select Slicers

·      Working with Pivot Charts

·      Data Validation Rules

·      Applying Built-in Conditional Formatting

·      Creating Custom Conditional Formats

·      Comments

·      Linking Data