16 Corporate Woods Boulevard | Albany, New York 12211 | Tel: 518.437.1600

Excel Beyond the Basics

Available Dates: Call for Availability
Class Length: 3-days day
Cost: $1,690

Class Outline:

Description:
This 3 day course will teach you to work faster and smarter with Excel. It’s frustrating to know what Excel can do, but not understand the techniques to accomplish it. You will learn not only the formulas, database techniques and macro commands to make work go faster, but you will also learn the tips and keystroke commands the pros rely on to make data more useful and worksheets easier to manipulate. Complicated charts and graphs that once took hours to compile will now only take minutes to set up and assemble. You’ll learn when it makes sense to use a macro, how to debug a macro and how to create and manage a PivotChart. Time spent now will save hours of frustration later!

Course Outline:

Lesson 1: Becoming Acquainted with Excel
Topic A: What is Excel?
Topic B: History of Spreadsheets
Topic C: Excel Navigation Basics
Topic D: Creating, Saving, and Opening Workbooks
Topic E: Getting to Know the Ribbon
Topic F: Quick Access Toolbar
Topic G: Identifying the Current Cell
Topic H: Entering Data into a Worksheet
Topic I: Getting Help

Lesson 2: Navigating and Working with Worksheets
Topic A: Moving Between Cells Using the Keyboard
Topic B: Selecting Cells
Topic C: Worksheets

Lesson 3: Best Ways to Enter and Edit Data
Topic A: Data Types
Topic B: Inserting Special Characters
Topic C: How to Change Column Widths
Topic D: How to Change Row Heights
Topic E: Correcting Typing Mistakes
Topic F: Shortcuts for Entering and Correcting Data
Topic G: Creating, Viewing, Editing, Deleting, and Formatting Cell Comments

Lesson 4: Formatting and Aligning Data
Topic A: Formatting Your Text Using the Font Group
Topic B: Formatting Numeric Data Using the Number Group
Topic C: Aligning Data Using the Alignment Group
Topic D: Using Format Painter to Copy Formatting
Topic E: Using the Mini-Toolbars and the Context Menu
Topic F: Inserting, Deleting, Hiding, and Unhiding Rows and Columns
Topic G: Inserting and Deleting Cells

Lesson 5: Different Ways of Viewing and Printing your Workbook
Topic A: Views
Topic B: Printing
Topic C: Dividing the Excel Window into Panes
Topic D: Freezing Rows and Columns
Topic E: Synchronizing Scrolling
Topic F: Custom Views Hot to Create, Show, and Delete

Lesson 6: Understanding Backstage
Topic A: Backstage Overview
Topic B: Info Group-Viewing, Adding, and Editing Information About the Workbook
Topic C: New Group–Creating a New Workbook
Topic D: Open Group–Open a Workbook
Topic E: Save and Save As groups–Saving a Workbook Using Save or Save As
Topic F: Print Group–Printing a Workbook
Topic G: Share Group–Sharing Workbooks
Topic H: Account Group

Lesson 7: Creating and Using Formulas
Topic A: Formulas
Topic B: AutoCalculate Tools
Topic C: Viewing Formulas
Topic D: Creating Named Ranges and Constants
Topic E: Absolute Cell References
Topic F: Mixed Cell References

Lesson 8: Excel’s Pre-existing Functions
Topic A: Excel’s Built-in Functions
Topic B: Function Construction
Topic C: Functions That Sum Values
Topic D: IF–Returns Different Values Depending upon if a Condition Is True or False Topic E: Absolute Cell References
Topic F: OR–Returns TRUE if Any Argument is TRUE
Topic G: Date Functions

Lesson 9: Auditing, Validating, and Protecting Your Data
Topic A: Validating Your Data and Preventing Errors
Topic B: Evaluating Formulas
Topic C: Formula Auditing
Topic D: Proofreading Cell Values–Have Excel Read Back Your Entries
Topic F: Spell Checking
Topic G: Thesaurus
Topic H: Protect Worksheets and Cells from Accidental or Intentional Changes

Lesson 10: Using Hyperlinks, Combining Text, and Working with the Status Bar
Topic A: Working with Hyperlinks
Topic B: Concatenation and Flash Fill
Topic C: Using the Status Bar

Lesson 11: Transferring and Duplicating Data to Other Locations
Topic A: Moving and Copying Data
Topic B: Paste Special
Topic C: Inserting Copied or Moved Cells
Topic D: Using the Microsoft Office Clipboard
Topic F: Entering Data into Multiple Worksheets at the Same Time

Lesson 12: Working with Tables
Topic A: Creating and Formatting Tables
Topic B: Sort and Filter a Table
Topic C: Adding to the Excel Table
Topic D: Filtering Data with a Slicer
Topic F: Using Themes
Topic G: Applying and Defining Cells Styles
Topic H: Conditional Formatting
Lesson 13: Working with Charts
Topic A: Chart Types
Topic B: Creating and Modifying Charts
Topic C: Pie Charts
Topic D: Combination Chart
Topic F: Hierarchical Charts
Topic G: Sparklines

Lesson 14: Importing Data
Topic A: Importing Data into Excel
Topic B: Importing Text Files
Topic C: Importing Data from an Access Database
Topic D: Importing Data from a Web Site
Topic F: Importing Data Using a Query

Lesson 15: Using PivotTables and PivotCharts
Topic A: Working with PivotTables
Topic B: Creating a PivotChart
Topic C: Creating PivotTable on a Relational Database

Lesson 16: Enhancing Workbooks with Multimedia
Topic A: Adding Pictures to the Worksheet
Topic B: Using Screenshot
Topic C: Working with WordArt
Topic D: Adding and Modifying Shapes
Topic E: Using SmartArt
Topic F: Inserting Sound into a Worksheet
Topic G: Inserting Video into a Worksheet
Topic H: Using Bing Maps

Lesson 17: Automating Task with Macros
Topic A: Creating (Recording) a Macro
Topic B: The Problem with Absolute Cell References
Topic C: Saving a Macro-Enabled Workbook
Topic D: Creating a Macro Using Relative Cell References
Topic E: Adding Macros to the Quick Access Toolbar and Other Objects
Topic F: Sharing the Personal Workbook with Others
Topic G: Looking at VBA Code
Topic H: Creating Macros from Code