Excel Intermediate Live Virtual Training in Cleveland, OH

In this course, you'll go beyond the basics of data analysis by learning to sort and filter your data and then apply subtotals and grand totals to the results. You'll learn to protect the integrity of your data with data validation. In addition, you’ll present data with charts, sparklines, PivotTables, and conditional formatting. Using VLOOKUP and other functions, you'll be able to connect data between tables.  

Excel Intermediate Outline

Protecting data integrity with data validation

• Limit data entry of numbers, dates, and times
• Limit data entry with drop down lists
• Limit data entry based on a formula
• Modify or remove data validation
• Use Input Messages and Error Alerts to help end users correct mistakes
• Search for Invalid Data

Using lookup functions to return answers from other tables

• Use VLOOKUP functions to return exact and approximate matches
• Use HLOOKUP functions to return exact and approximate matches
• Locate data with the MATCH function
• Display data with the INDEX function
• Use the INDEX and MATCH functions when VLOOKUP and HLOOKUP functions are insufficient

Sorting and filtering data

• Apply single and multi-column sorts and add subtotals to the resulting list
• Understand the difference between a standard list or table and an Excel table
• Convert a list to an Excel table to quickly format and analyze data
• Activate the filter command
• Filter text, numbers and dates
• Clear filters for individual fields or the entire table
• Use the SUBTOTAL function to apply various totals to filtered data
• Deactivate the filter

Presenting and analyzing data with charts

• Create charts to present data graphically
• Change chart types and source data
• Modify the chart layout and formats
• Move and copy charts
• Create Sparklines as alternatives to charts

Creating PivotTable reports

• Create a PivotTable
• Modify the structure of a PivotTable
• Apply totals and subtotals
• Modify the layout of a PivotTable
• Filter data with standard filters, page fields, and Slicers
• Update data and change the data source

Applying conditional formatting to data

• Format cells based on the data they contain
• Remove conditional formatting
• Apply multiple conditional formats
• Use data bars, color scales, and icon sets
• cells based on a formula