03-08-2016 - Advanced Excel Training - 2 Day Seminar - Washington, DC

DAY 1

I. Manipulate and Manage Your Data to Provide Better Business Answers

• Advanced Sorting
  • Multi-level sorting
  • Sorting by color, font, or icon
  • Establishing custom sort lists
• Advanced Filtering
  • Create custom text filters with multiple criteria
  • Filtering by color
  • Advanced filter for complex OR criteria
  • Advanced filter to create unique lists from repeating field data
• Eliminating Duplicate Data
  • Using the Remove Duplicate command
  • Array formula to locate duplicate data
• Database Analysis Tools
  • Using SUMIF, COUNTIF, and other data analysis functions
  • Using DSUM, DAVERAGE, and DMAX to analyze data
• Data Validation
  • Create drop-down lists
  • Controlling number data
  • Date/time controls
  • Preventing duplicate entries

II. Creating Smarter Named Ranges

• Creating range names
• Creating names from selections
• Modifying ranges
• Apply Names command
• Using range names in formulas
• Using Name Manager to delete, modify, and edit range names
• 3-D name range

III. Advanced Text Manipulation Made Easy

• Using Text to Columns
• Find and Replace special characters and text
• Converting dates with text functions
• Converting text data to values/numbers
• CONCATENATE and joining text
• Text to Columns feature
• Formatting special numbers and leading zeroes in zip, numerical, and text
• Formatting text with PROPER, UPPER, LOWER, MID, LEFT, and RIGHT
• Using Copy and Paste to transpose data

IV. Everything You Ever Wanted to Know About Tables (But Didn"t Know What to Ask!)

• Converting a spreadsheet to a table
• Formatting a table
• Effect of inserting/deleting rows and columns in a table
• Structured referencing
• Working with table names
• Using functions in tables
• Using [#This Row] function

V. Advanced Formatting Techniques That Bring Your Data to Life

• Conditional formatting
• Custom conditional formatting
• Adding pictures and shapes
• Inserting SmartArt
• Themes
  • Create a theme
  • Modify a theme
• Templates
  • Create a template
  • Use a template

VI. Creating Perfect Charts for Explaining Your Data Visually

• Format axes
  • Adjust scale
  • Data label
• Format titles
• Format data points
• Combination charts
• Add graphics/pictures to a chart
• Add trendlines

DAY 2

I. Building More Intelligent and Flexible PivotTables

• Creating a PivotTable
• Modifying PivotTables
• PivotTable calculations
• Creating separate sheets for PivotTables
• Using slicers in PivotTables
• Summarizing pivot data
• Sorting and filtering data
• Conditional formatting in PivotTables
• Creating PivotCharts

II. Manipulating and Managing Multiple Workbooks

• Adding/deleting sheets
• Copying sheets between workbooks
• Linking workbooks
• Applying changes to multiple sheets
• Linking formulas between workbooks
• Merging workbooks
• Creating a workspace

III. Making Dashboards Your Most Powerful Visual and Analytical Tool

• Creating a dashboard
• Linking to data
• Modifying a dashboard
• Create hyperlinks

V. How to Make the Most Out of Your Macros

• Define and record a macro
• Run the macro
• Assign a shortcut to the macro
• Inspect the macro
• Step through a macro
• Import a file while running a macro
• Fill in blank spaces on a worksheet
• Record a relative movement
• Record a macro that runs other macros
• Edit with Visual Basic
• Debug with Visual Basic

VI. Foolproof Auditing and Error-Checking Shortcuts

• Error tracing
• Creating/editing comments
• Deleting comments
• Protecting a worksheet
• Protecting a workbook