Microsoft Excel 2016: Customized Skills

Session 1: Making Your Worksheets Look Professional

• Format dates
• Format numbers using built-in number formats
• Create custom number formats
• Use cell styles and change themes
• Use simple conditional formatting
• Manage multiple conditional formats using the Rules Manager
• Bring data alive with visualizations
• Create a formula driven conditional format

Session 2: Tables and Ranges

• Apply a simple filter to a range
• Apply a top 10 and custom filter to a range
• Apply an advanced filter with multiple OR criteria
• Apply an advanced filter with complex criteria
• Extract unique records using an advanced filter
• Convert to range into a table and add a total row
• Format a table using table styles and convert a table into a range
• Sort a range or table by rows
• Sort a range or table by custom list

Session 3: Subtotals

• Automatically subtotal a range
• Create nested subtotals

Session 4: Advanced Functions

• Use the IF logic function
• Use the AND and OR functions to construct complex Boolean criteria
• Concatenate string using the concatenation operator (&)
• Use the TEXT function to format numerical values as strings
• Extract text from fixed width strings using the LEFT, RIGHT and MID functions
• Extract text from delimited strings using the FIND and LEN functions
• Use TODAY, DAY, MONTH and YEAR
• Time calculations (mid-night)
• Use Time(), hours, minutes and seconds
• Use the SUMIF & COUNTIF functions to create conditional totals
• Use the SUMIFS & COUNTIFS functions to create conditional totals
• Use a VLOOKUP function for an exact lookup
• Use an IFERROR function to suppress error messages
• Use a VLOOKUP function for an inexact lookup
• Use INDEX, MATCH function

Session 5: PivotTables & Pivot Charts

• Create a one-dimensional pivot table report from a table
• Create a grouped pivot table report
• Understand pivot table rows and columns
• Understand the PivotTable data cache
• Apply a simple filter and sort to a pivot table
• Use report filter fields
• Filter a pivot table visually using slicers
• Add a timeline to a PivotTable
• Use slicers to create a data-driven interface
• Use report filter fields to automatically create multiple pages
• Format a pivot table using PivotTable styles
• Understand pivot table report layouts
• Add/remove subtotals and apply formatting to pivot table fields
• Display multiple summations within a single pivot table
• Group by Text
• Group by Date
• Group by numeric value ranges
• Show row data by percentage of total rather than value
• Create a pivot chart from a pivot table
• Embed multiple pivot tables onto a worksheet
• Use slicers to filter multiple pivot tables