MS Excel 2016/19 – Course Outline

SUMMARIZING DATA

• Advanced Subtotals
• Formatting the Subtotal Rows
• Adding and Copying with Subtotals
• Consolidating Data
• Introducing the SUMIF Command
• Working with the SUMIF Command
• Looking at AutoSum Tricks
• Utilizing Fill Handle Tricks

WORKING WITH PIVOT TABLES

• Creating a Pivot Table
• Rearranging Fields in a Pivot Table
• Explaining the Report Layout Options
• Using the Report Filters Feature
• Using Top 10 & Date Filters
• Handling Blank Cells
• Drilling Down in the Pivot Table
• Sorting a Pivot Table
• Formatting a Pivot Table
• Creating Custom Formats
• Explaining the Grouping Options
• Adding Formulas to a Pivot Table
• Changing a Calculation in a Pivot Table
• Replicating a Pivot Table
• Counting with a Pivot Table

CHARTING IN EXCEL
• Charts Refresher
• Moving, Sizing & Copying Charts
• Formatting Charts
• Formatting a Series
• Exploring the Home & Format Ribbons
• Deciding What Chart Format to Use
• Show a Time Series with Column or Line Charts
• Using Combination Charts
• Using Line Chart Accessories
• Using Bar Charts to Show Comparisons
• Using Component Charts
• Using Correlation Charts
• Exploring Other Charts
• Chart Lies Revealed & Advanced Chart Types
• Creating a Custom Layout

GETTING VISUAL
• Using Conditional Formatting
• Using Advanced Conditional Formatting
• Highlighting, Sorting & Filtering Columns
• Using SmartArt Functions
• Using Different SmartArt Graphics

CREATING POWERFUL FORMULAS
• Auditing Formulas
• Shrinking the Formula Bar
• Understanding Errors
• Joining Text Columns
• Using Custom Formatting Codes
• Pasting Values
• Using Paste Special
• Breaking Apart Text
• Flashfill
• Converting Text to Numbers
• Using Date Functions
• Using the VLOOKUP and HLOOKUPFunction
• Using Index Match
• Using the Convert Function
• Looking at Interesting but Obscure Functions
• Using the Rand Function
• Using rounding Formulas
• Ranking & Sorting Formulas

TIPS & TRICKS

• Showing Off New Tips in Excel (Eg: Speak on Enter)
• Tips for Handling Worksheets
• Best Practice
• Validating Data
• Grouping
• Multi-Sheet and Dashboard
• Modelling Eg: Costing Sheet, Budgeting, Manhour tracking and Any model that may be useful

Fast Track Computer Solutions in WA since 1985!
Call Ivor Now on (08) 9275-9188.