Proudly serving the D.C. metro
area for over 25 years.


Softek Services, Inc.
1156 15th Street, NW, Suite 100
Washington, DC 20005
(202) 747-5000

Computer Training • Software Consulting • Database Programming

Get tips on our Facebook Find us on Google+ Tweet Us Read Reviews on Yelp Softek on Linkedin

  • Instructor-led Training Instructor-led Training
  • Join a Public Class Join a Public Class
  • Private Group Classes Private Group Classes
  • Transition Classes Transition Classes
  • Virtual Training Virtual Training
  • Customization & Documentation Customization & Documentation
  • Web and Database Development Web and Database Development
  • We Have Classrooms We Have Classrooms
     
About Us
Course Listing/Agendas
View Schedule/Register
Cancel/Reschedule
Quote Request
Assessments
Student Resources
Classroom Rentals
Directions
Contact Us
Inclement Weather

Advanced Microsoft Excel

Version 2010 • 2013 • 2016

1 Day, 9:00-4:00, 6 Hours

This instructor-led course, through hands-on exploration, will teach students the skills needed to work with large lists of data, PivotTables, outlining, named ranges, conditional formatting, macros, data validation, and protection.  Students will also learn how to write additional formulas that use the VLOOKUP function.

Course objectives:

  • Work with larges lists of data, apply filters, and use tables
  • Create PivotTables to analyze and summarize data
  • Perform What-If Analysis on a range of data
  • Automate tasks with conditional formatting and macros
  • Use the VLOOKUP function to locate values from a table or range
  • Apply data validation and protection

 

Working with Large Lists of Data

  • Design Concepts for Structuring Large Lists
  • Header Row (Fields)
  • Data Range (Records)
  • Filtering Lists
  • Applying Filters
  • Applying Top 10 and Custom Filters
  • Working with Advanced Filters
  • Criteria Range
  • Applying Advanced Filters
  • Converting Lists of Data to Excel Tables
  • Creating and Naming Tables
  • Using Table Elements and Features

Creating PivotTables to Analyze Data

  • Creating PivotTables
  • Adding and Removing Fields
  • Adjusting the PivotTable Field List View
  • Grouping Data and Rearranging Fields
  • Using Report Filters
  • Changing Value Field Settings
  • Refreshing Data in PivotTables
  • Adding Slicers to Visually Filter
  • Creating PivotCharts from PivotTables

Using Data Analysis Tools

  • Creating Scenarios to Perform What-if Analysis
  • Showing and Editing Scenarios
  • Summarizing Scenarios
  • Using the Subtotal Feature
  • Outlining Worksheets
  • Creating Manual and Automatic Outlines
  • Showing or Hiding Details
  • Ungrouping Columns and Rows
  • Working with Custom Views
  • Creating Custom Views
  • Showing Custom Views
  • Deleting Custom Views

 

 

Automating Tasks with Conditional Formatting and Macros

  • Applying Conditional Formatting
  • Applying Preset Conditional Formatting Rules
  • Editing Conditional Formatting Rules
  • Applying Conditional Formatting to Rows
  • Creating Macros Using the Macro Recorder
  • Using Relative vs. Absolute References
  • Running Macros
  • Editing Macros
  • Deleting Macros
  • Adding Macro Buttons to the Quick Access Toolbar

Using VLOOKUP to Locate Values

  • Examining the VLOOKUP Function Syntax
  • Creating Lookup Tables
  • Creating Named Ranges
  • Writing VLOOKUP Formulas
  • Understanding Common Formula Errors
  • Using the IFERROR Function to Hide Errors

Setting Data Validation and Protection

  • Validating Data Entry
  • Applying Data Validation
  • Testing Data Validation
  • Understanding Worksheet Protection
  • Unlocking Cells
  • Protecting Worksheets
  • Unprotecting Worksheets

 

View Upcoming Schedule or Register for a Class

Not Sure What Level to Take?

Before taking the Intermediate or Advanced level class for this application, we ask that you determine whether you are comfortable with the topics covered in the Introductory level class. Or, feel free to contact our office at (202) 747-5000 to speak with a representative to discuss the prerequisites.

Back to Agenda

 

Investment

Public Class

$275 per student
Or one voucher

View Schedule or Register

Private Group Classes

Conducted at Your Office

$850 flat fee for up to 8 students
$950 for up to 12 students
$50 per additional student

Conducted at Softek’s Facility

$1000 flat fee for up to 8 students
$1100 for up to 12 students
$50 per additional student

Have more or fewer students? Or, need to schedule more than one training day? Fill out our quote request form for a price quote based on your training needs. 

Softek also offers a 3-hour series for this application. Visit our agendas page  to view our 3-hour classes. 

Why choose Softek?

  • Live, in-person training
  • Instructors with 10+ years of experience
  • Detailed training guide
  • Certificate of completion
  • Money-back guarantee

©2014 Softek Services, Inc. All Rights Reserved. - 1156 15th Street, NW, Suite 100, Washington, DC 20005 - (202) 747-5000