Best way to contact me *

    Course Length: 6 hours (1 day)
    Excel has many new features that can be used to quickly analyze large amounts of financial information. Power Pivot enables users to combine data from multiple sources; Power View allows users to generate professional looking maps that indicate the location of data. Excel’s use of the VBA language enables users to create simple yet effective commands that enhance functionality, improve performance and perform repetitive tasks for you.

    Learning Objectives:
    In this course you will combine, analyze, and display data using Excel’s powerful features, and learn the basics of how to use macros to further extend Excel’s capabilities. You will:

    • Perform a What-If analysis by creating scenarios and using data analysis tools such as Goal Seek and Solver
    • Use Data Validation to restrict data entry and create drop-downs
    • Use outlining tools to group and subtotal your data
    • Audit your formulas to help track down any errors
    • Analyze data with Pivot Tables
    • Record and delete macros
    • Edit basic macros using the Visual Basic Editor
    • Link and consolidate data across multiple workbooks
    • Import and export data and run web queries

    Target Student:
    This course is for users who are familiar with Excel and who wish to expand their knowledge and use of advanced data analysis and time-saving features.

    Course Outline:
    Section 1: Enhancing Workbooks
    Section 1.1: Customizing Workbooks
    Background Pictures

    Section 1.2: Preparing a Workbook for Multiple Audiences
    Add Alternative Text to Objects
    Modify Worksheets Using the Accessibility Checker
    Manage Fonts

    Section 1.3: Managing Themes
    About Themes
    Customize Themes

    Section 1.4: Creating and Using Templates
    Create a Template
    Modify a Template

    Section 2: Working with Multiple Workbooks
    Section 2.1: Consolidating Data
    Data Consolidation
    The Consolidate Dialog Box
    Consolidation Functions

    Section 2.2: Linking Cells in Workbooks
    Understand External References
    Link Individual Cells
    Link Groups of Cells

    Section 2.3: Merging Workbooks
    The Compare and Merge Workbooks Feature

    Section 3: Analyzing and Presenting Data
    Section 3.1: Create Sparklines
    What is a Sparkline?
    Types of Sparklines
    The Sparkline Tools – Design Tab

    Section 3.2: Create Scenarios
    What is a Scenario?
    The Scenario Manager Dialog Box

    Section 3.3: Perform A What-if Analysis
    Add-in Types
    Goal Seek Feature
    The Solver Tool

    Section 3.4: Perform A Statistical Analysis with the Analysis Toolpak
    Add and Review the Analysis Toolpak

    Section 4: Importing and Exporting Excel Data
    Section 4.1: Exporting Excel Data
    The Export Process

    Section 4.2: Importing a Delimited Text File
    The Import Process
    The Get External Data Group
    Delimited Text Files
    Methods of Importing Text Files

    Section 4.3: Integrating Excel Data with the Web
    The File Publishing Process
    Publish as Web Page Dialog Box

    Section 4.4: Creating a Web Query
    Web Queries
    The New Web Query Dialog Box

    Section 5: Analyzing Data with Pivot Tables, Slicers, and Pivot Charts
    Section 5.1: Creating a Pivot Table
    Pivot Tables
    The Create Pivot Table Dialog Box
    The Pivot Table Fields Pane
    Summarize Data in a Pivot Table
    The “Show Values As” Functionality of a Pivot Table
    Create a Calculated Field
    Refresh the Pivot Table Data
    Add Style to a Pivot Table
    Work with Subtotals and Grand Totals
    Introduction to Power Pivot

    Section 5.2: Filtering Pivot Table Data
    Use the Field Headers
    Use the Pivot Table Dialog Box
    Use Slicers

    Section 5.3: Analyzing Data with Pivot Charts
    Create Pivot Charts
    Filter with Pivot Charts

    Section 6: Automating Worksheet Functionality
    Section 6.1: Updating Workbook Properties
    Workbook Properties

    Section 6.2: Creating and Editing a Macro
    What are Macros?
    The Record Macro Dialog Box
    Name Macros
    Visual Basic for Application
    Copying Macros Between Workbooks
    Macro Security Settings
    Difference between a Relative and Absolute Macro

    Section 6.3: Applying Conditional Formatting
    What is Conditional Formatting?
    The Conditional Formatting Rules Manager Dialog Box
    Clear Rules

    Section 6.4: Adding Data Validation Criteria
    Data Validation
    The Data Validation Dialog Box


    Online Event


    Live Online Training

    Get the same training you expect in the classroom without leaving your office or home. These are NOT recorded classes. They are LIVE sessions with an expert instructor. We use the latest in video conferencing technologies and audio so you can confidently participate in any class just like being right there in person. We guarantee the effectiveness of our online training delivery approach that we will give you your money back if you are not totally satisfied.  Ask us for a demo.

    Online class requirements:

    • Moderate to fast Internet
    • A phone or computer headset is required in order to hear the instructor/moderator). You can use Computer Audio (VoIP) or you can dial in from a regular phone.  For convenience, we recommend a hands-free headset or phone.
    • Training software must be installed on your computer (trial versions are acceptable)
    • RECOMMENDED: Dual Monitors or computers. For optimal online learning experience, we recommend participants have dual monitors or two computers. Your online classroom credentials allow you to join multiple times from multiple computers. Participants should use one monitor or computer to view the instructor’s shared screen and another monitor or computer to work with the software.

    What happens when you enroll in an online class

    When you register for an online class, you will receive a welcome email followed by login access to the Citrix GoToTraining virtual classroom. A workbook (printed copy or eBook) will be sent to you prior to the start of class.

    Online Training Advantages

    Convenience: You don’t have to travel and can attend from your home, office or anywhere with an internet connection. Our online classes are conducted using GoToTraining, a more robust version of the popular GoToMeeting screen sharing and conferencing platform. To accommodate multiple time zones, courses are typically scheduled from 10am – 5pm Eastern with a one-hour lunch break at 12:30 – 1:30 pm Eastern and a 10-minute break in the morning and afternoon. When conducting custom online course for your group, class times can be modified to accommodate your timezone.

    Interactive Learning: Our online training is fully interactive. You can speak and chat with the instructor and classmates at any time. Various interactive techniques are used in every class. Our small class sizes (typically 4 – 8 students), allow our instructors to focus on individual performance and issues and to work closely with you to meet your unique needs. Classes are designed to be a hands-on learning experience, providing opportunities for you to try your new skills while the instructor is available for review, questions, and feedback. You have the option to give the instructor permission to view your computer to provide one-on-one assistance when needed.

    GSA Schedule: 47QTCA19D008F