Excel 2016 (Mac) Session 5

In this half-day session, you will create sophisticated charts and complex formulas using functions. You will learn to chart noncontiguous data, create dual-axis charts for comparing vastly disparate data sets, and combine column and areas markers on the same chart.

You will learn how to make 3-D charts more effective by controlling the stacking order of the series, and add trendlines to 2-D charts.

You will also expand your ability to write complex formulas that include financial, text, logical, lookup and database functions, and gain essential formula auditing and troubleshooting skills.

Course Outline

Advanced Charting Capabilities
- charting non-adjacent ranges of cells
- rotating and exploding pie charts
- optimizing Excel charts for use in other programs
- creating dual-axis combination charts
- adding and rearranging series of data
- altering the view of 3-D charts
- adding trendlines and forecasts

Writing Formulas Using Functions
- PMT and PV for financial calculations
- LEFT, MID and RIGHT to extract text
- nesting functions inside functions
- VALUE to convert strings to values
- IF to construct conditional logic
- SEARCH to aid in parsing text strings
- VLOOKUP to retrieve values from a table
- INDEX and MATCH for more dynamic lookups
- creating in-cell drop-down menus
- DSUM to sum table data filtered by criteria

Troubleshooting Advanced Formulas
- editing formulas
- auditing your formulas
- tracing precedents and dependents
- inserting comments
- using the Watch window

Pre-requisites: This class is ideally suited for those who have attended up to Session 3 or who are already comfortable creating basic formulas (SUM, AVERAGE, COUNT, etc) and simple charts.