Advanced Microsoft® Excel®-Macros, PivotTables, Charts and More

9:00 am - 4:00 pm

Check in at 8:30 am

$199/Person

$189 for groups of 5 or more

Continuing Education Credits: CEU: 0.6 CPE: 6.00 PDU: 6.00 

Choose an Event

Events Near

Ashburn, VA 20149

Change Location

4 More Dates and Locations

Advanced Microsoft® Excel® — Diving Deeper

6:22 am - 6:22 am

Check in at 6:22 am

$199/Person

$189 for groups of 5 or more

Continuing Education Credits: CEU: 0.6 CPE: 6.00 PDU: 6.00 

Choose an Event

Events Near

Ashburn, VA 20149

Choose a New Location

- or -


Overview

Building familiarity with Excel’s advanced features can help you boost productivity, give you better insight into your data, save you time, and allow you to create vibrant, persuasive reports.

This seminar is appropriate for the Intermediate Excel user looking to expand their knowledge of Excel’s hundreds of features and to use familiar tools at a deeper, richer level. Anyone who tracks and analyzes data - such as teachers, researchers, marketers and small business owners - will benefit from a greater comfort level with Excel’s sorting, filtering and analysis tools. Team leads and managers can save valuable time by creating efficient, re-usable reporting tools with macros and forms. The home office user will appreciate the usefulness of Excel’s many functions that make accounting, budgeting and shopping research quick and easy.

Agenda

Macros and VBA Coding

  • Get comfortable with the Visual Basic Editor so creating and editing macros is easier and less intimidating
  • Understand the basic structure of all macro programs so you’ll know what you can edit and what you can’t
  • Learn the basics of Visual Basic syntax
  • Add a message box notification to your macro
  • Apply looping to a macro to perform tasks as many times as needed
  • Create macros that will execute commands based on specific actions

Advanced Calculation

  • Use all of Excel’s built-in Error Checking tools to make sure your calculations will work and remain accurate
  • Track down errors using Excel Formula Auditing
  • Learn the tips and tricks for building complicated formulas using nested functions
  • Create and use your own custom function
  • Learn the ins and outs of Excel’s most useful and common functions
  • Harness the power of Excel’s “IF” functions to apply calculations based on specific criteria
  • Compare the results of varying inputs with Scenarios
  • Find out the values you’ll need to achieve a specific calculation result using Goal Seek
  • Calculate multiple values with several dependencies using Solver

PivotTable Troubleshooting and Analysis

  • Style and Format your PivotTables for the best presentation and at-a-glance analysis
  • Sort and Group PivotTable results to get the clearest picture of your data
  • Handle common PivotTable headaches such as blank lines or cells, unwanted Grand Totals and managing filters
  • Get the analysis you need by adjusting Summarize Values By and Show Values As
  • Take PivotTables to the next level with Calculated Fields and Items
  • Learn how to drill into the data behind your PivotTable analysis
  • Explore Excel’s Advanced Data Modeling to create relationships between tables in your Workbook
  • Create a PivotTable from Multiple data tables using Excel Data Model
  • Turn your PivotTables into beautiful charts and graphs

Sharing Data Internally and Externally

  • Apply the power of Excel’s analytic features to data produced on external sources
  • Connect Excel to a variety of external data sources for convenient download and upload
  • Master the full potential of Excel’s Printing and reporting features
  • Discover how to create different display and print settings for the same worksheet — or apply the settings to multiple worksheets
  • Protect your data and your computer from malicious viruses using the Trust Center

Personalize Your Application

  • Save time by customizing the Excel Ribbon to include exactly the commands and Macros you need
  • Put frequent commands and macros in easy reach by customizing the Excel user interface
  • Create, display, print and delete custom views
  • Use custom views to change the look of your worksheets and workbooks

Overview

Harness all the power and potential Excel has to offer …

The basics of Excel aren't difficult to grasp … but mastering advanced techniques can be trickier. In fact, unless you are adept at deciphering complex manuals, have loads of extra time for tutorials or possess a strong motivation for self-learning, you may find boosting your skills and knowledge of Excel is easier said than done. Until now, that is! Advanced Microsoft Excel training gives you an exceptional amount of concentrated information in just one extraordinary day of training.

Attend this extraordinary, information-packed program, and learn how to:
  • Slash the time it takes to build your worksheets with power tips and surprising shortcuts
  • Get the most out of macros and easily automate repetitive tasks
  • Use Excel's robust collaboration tools to share data across teams and workgroups
  • Create, manage and modify PivotTables with ease to produce sophisticated reports and data analysis
  • Take your charts, graphs, tables and reports to the next level with eye-catching graphics and other design elements
  • Integrate Excel with the Internet and your company's intranet — with tools and techniques that allow you to create Internet-compatible workbooks, spreadsheets, charts and other documents
  • And much, much more
Kick Your Excel Skills Up a Notch …

If you're comfortable building a worksheet, using cells, entering data and creating basic formulas, this program will take you to the next level of Excel expertise. Instead of hovering at the "tip of the iceberg," you'll drill down through Excel's complex capabilities and discover how easily you can expand your knowledge base to become a more confident, capable user.

This course is jam-packed with skills, tools, ideas and strategies gathered from professional users, tech experts and Excel gurus who know this application inside and out. You won't waste time rehashing how to create spreadsheets, input data, perform basic mathematical calculations, save workbooks or edit cells. Instead, this training builds on what you already know, focusing on the advanced techniques that will take your skills to the next level. Your trainer is an accomplished Excel expert who will walk you through dozens of time-saving shortcuts, smart strategies and more with a helpful mix of computer-screen projections, demonstrations, instructor-led exercises and plenty of Q & A time. You'll get practical, real-world answers to your toughest Excel questions and challenges, and come away from this course ready and able to put your new skills to work the very next day.

Gain all this and more in just one day of concentrated learning!

Agenda

Master Advanced Macros

  • Use the Visual Basic® editor to modify and manage your macros
  • Learn to create customized macro toolbars featuring your most frequently used tools

Manage Your Data

  • Learn how to create data forms for viewing and entering information
  • Enter — or allow others to enter — data into a user-friendly form
  • Review individual records easily, or view only records that meet specific criteria
  • Discover the power of Backstage view

Filter, Query and Analyze Data

  • Learn tips, tools and tricks for getting the most out of AutoFilters, Custom AutoFilters and Advanced Filters
  • Find out how to search for spreadsheet data that fits within specific parameters
  • Discover menu options that allow you to select filtering criteria from your data set with just a few simple clicks
  • Specify a single criterion, or combine two or more to create a custom search of your data
  • Use AND/OR commands to extract data that meets your criteria in a whole new range
  • Create new data ranges on the fly to use with advanced filters so you can extract exactly the information you need
  • Add sparklines to show trends in a series of values, such as seasonal increases or decreases

Use Lookup and Database Functions

  • Add power and flexibility to your formulas by enabling Excel to search out data for you
  • Manage large data lists by performing calculations only on the data you specify
  • Use Excel to perform new lookups and dynamically update your calculation results

Put PivotTables to Work

  • Discover how PivotTables can help you get the most out of your data
  • Create PivotTables from single spreadsheets, external data sources or other PivotTables
  • Use AutoFormat to create your PivotTable quickly and easily
  • Learn how to modify or update a PivotTable
  • Group data in a PivotTable for better organization
  • Control how and where your PivotTable data is displayed
  • Display your data using a PivotChart®

Get to Know the Analysis Toolpak

  • Find out what tools are included in this valuable resource
  • Learn to use Excel's advanced data analysis tools to return a multitude of statistics about your data in just a few keystrokes
  • Use Excel's built-in utilities to analyze correlations in your data

Share Excel Across Workgroups

  • Use Excel's robust collaboration tools to control access to shared spreadsheets
  • Understand the different ways to share your spreadsheets with associates and coworkers
  • Comment on specific cells within a given sheet
  • Activate workbook sharing and distribute shared workbooks
  • Maintain control of versions of a shared workbook
  • Easily track and review changes made by multiple users
  • Seamlessly integrate Excel with other applications, the Internet and your organization's intranet
  • Create macros that work with the Web to automatically update your Web charts and graphs at regular intervals

Explore Custom Views, Formatting and Report Manager

  • Discover how to create different display and print settings for the same worksheet — or apply the settings to multiple worksheets
  • Manage saved custom views
  • Use custom views to change the look of your worksheets and workbooks
  • Create, display, print and delete custom views
  • Understand and use the Report Manager to print, edit and manage your reports
  • Expand your knowledge of charts and graphs to create eye-popping graphic images
  • Master formatting effects to make your pages look like you spent hours — when all it took was a few mouse clicks!
  • Apply different Data Bars, Color Scales and Icon Sets to your cell selections using conditional formatting

*NOTE: This is not a hands-on workshop. Instead, you'll learn via "computer-less" training that makes every moment count.