Topics for Custom Excel Class
For private classes and one-on-one sessions we can customize the curriculum to emphasize the topics of greatest interest to you. Here are some suggested Excel topics.
Interface and Navigation
| Topic | Description |
|---|---|
| Overview | Overview of Excel ribbons and features, navigating the worksheet and tabs, entering and editing data. |
| Mouse Shortcuts |
Using the mouse to move and copy data (drag and drop), using the mouse to enter a series automatically (autofill), creating a custom autofill series. |
| Navigation | Arranging multiple windows, opening multiple windows for same workbook, freezing panes. |
| Customizing Ribbons | Customizing existing ribbons and creating your own, adding groups and buttons to the ribbon. |
Formatting
| Topic | Description |
|---|---|
| Worksheet Formatting | Formatting rows and columns, formatting cells and their content, adding borders and shading, formatting numbers, alignment, text wrap and text rotation, fonts. |
| Cell Styles | Creating and defining a cell style, choosing which attributes the style governs, applying styles to cells, modifying cell styles, transferring styles from one workbook to another. |
| Conditional Formatting | Defining conditional formatting, using Excel’s built-in conditions, defining custom conditions, formatting one cell based on entries in another, defining custom number formats. |
| Print Formatting |
Designating rows and columns to repeat, defining a print area, setting margins, creating and formatting custom headers and footers, specifying page alignment, output scaling, printing grid lines and row and column headings. |
Formulas and Functions
| Topic | Description |
|---|---|
| Formulas | Formula basics, entering and editing a formula, how to autofill formulas and when it is appropriate, absolute cell references. |
| Date Calculations |
How dates are recorded in Excel, how date calculations work, making simple date calculations (time between dates). using date functions such as TODAY, MONTH, DAY, YEAR and NETWORKDAYS. |
| Time Calculations |
How time is recorded in Excel, how time calculations work, making simple time calculations (duration, elapsed time), using time functions such as NOW, SECOND, MINUTE and HOUR. |
| Text Calculations |
Working with text in formulas, combining text entries, using text formulas such as LEFT, RIGHT, UPPER, TRIM, DOLLAR and FIXED. |
| Linking | Writing formulas that reference cells in other workbooks (external cell reference), updating links, tips to avoid breaking links. |
| Functions Overview | Difference between functions and formulas, function syntax, using the Function wizard |
| Function Details | Using specific functions, such as SUM, COUNT, VLOOKUP and IF. Can cover Financial, Date and Time, Statistical, Logical, Text and Lookup functions depending on your needs. |
| Error Checking | Using Excel's formula auditing features to locate errors in formulas. |
Data Manipulation
| Topic | Description |
|---|---|
| Overview | How data must be organized for the data features to work. |
| Sorting & Filtering | Turning on autofilter, using autofilter and custom number and date filters, sorting with autofilter, using the sort command (multi-level sort). |
| Advanced Filter | Using advanced filter to copy specified data to a new cell range, setting up the advanced filter, rules for specifying criteria. |
| Remove Duplicates | How to decide what fields to specify when using the remove duplicates command, which records are retained after removal. |
| Tables | Defining a table, table options, formatting the table, using tables for data manipulation. |
Charts and Graphs
| Topic | Description |
|---|---|
| Essentials | Structuring data to facilitate charting, choosing the correct chart type, creating a chart, changing chart type, specifying series and categories, formatting chart elements, adding labels. |
| Advanced | Creating overlay charts, creating a chart template, moving chart to a different sheet, linking chart labels to worksheet cells. |
Pivot Tables
| Topic | Description |
|---|---|
| Essentials | How data must be organized to use as data source for Pivot Table, creating a pivot table, specifying row and column headings and calculations, types of calculations a pivot table can make, options for displaying calculated results, refreshing data. |
| Advanced | Pivot table option settings, moving pivot table to a different sheet, pivot charts, using Slicers, creating calculated Pivot Table fields, changing data source. |
Macros and VBA
| Topic | Description |
|---|---|
| Recording & Editing | Recording macros, how to decide what the macro should do, reading a macro’s VBA code, editing a macro, difference between absolute and relative cell references, specifying whether recording should use relative or absolute cell references, security settings. |
| Macro Authoring | VBA syntax, declaring procedures, variables and constants, using IF and other conditional statements to control program flow, creating loops. |
Miscellaneous
| Topic | Description |
|---|---|
| Named Ranges | Defining and updating range names, using names for worksheet navigation, writing formulas with names, defining names from selection, benefits of using names. |