Excel Dashboard and Reports Advanced Skills

PREREQUISITES

To ensure your success, it is recommended you have completed Microsoft® Excel Expert or possess equivalent knowledge.

WHAT YOU NEED TO HAVE

The ideal candidate for this course will have the following:

• Some experience working with data and familiarity with the basic concepts of data analysis such as working with tables, aggregating data, and performing calculations
• Experience using Excel with a strong grasp of concepts such as table structures, filtering, sorting, and using formulas. 

PART I: GETTING STARTED WITH EXCEL DASHBOARDS

Chapter 1: Introducing Dashboards

Module 1: What Are Dashboards and Reports?

• Defining reports
• Defining dashboards

Chapter 2: Using Excel Sparklines

Module 1: Understanding Sparklines

Module 2: Applying Sparklines

Module 3: Creating Sparklines

Module 4: Customizing Sparklines

• Sizing and merging sparkline cells
• Handling hidden or missing data
• Changing the sparkline type
• Changing sparkline colors and line width
• Using color to emphasize key data points
• Adjusting sparkline axis scaling
• Faking a reference line
• Specifying a date axis
• Auto-updating sparkline ranges

Chapter 3: Chartless Visualization Techniques

Module 1: Using Symbols to Enhance Reporting

PART II: INTRODUCING CHARTS INTO YOUR DASHBOARDS

Chapter 4: Excel Charting for the Uninitiated

Module 1: What Is a Chart?

Module 2: How Excel Handles Charts

• Embedded charts
• Chart sheets

Module 3: Parts of a Chart

Module 4: Basic Steps for Creating a Chart

• Creating the chart
• Switching the row and column orientation
• Changing the chart type
• Applying chart styles
• Applying a chart style
• Adding and deleting chart elements
• Moving and deleting chart elements
• Formatting chart elements

Module 5: Working with Charts

• Moving and resizing a chart
• Converting an embedded chart to a chart sheet
• Copying a chart
• Deleting a chart
• Copying a chart’s formatting
• Renaming a chart
• Printing charts

Chapter 5: Working with Chart Series

Module 1: Specifying the Data for Your Chart

Module 2: Understanding Series Names

• Changing a series name
• Deleting a series name

Module 3: Adjusting the Series Plot Order

Module 4: Charting a Noncontiguous Range

Module 5: Using Series on Different Sheets

Module 6: Handling Missing Data

Module 7: Controlling a Data Series by Hiding Data

Module 8: Unlinking a Chart Series from Its Data Range

• Converting a chart to a picture
• Converting a range reference to arrays

Module 9: Working with Multiple Axes

• Creating a secondary value axis
• Creating a chart with four axes

Chapter 6: Formatting and Customizing Charts

Module 1: Chart Formatting Overview

• Selecting chart elements
• Common chart elements
• UI choices for formatting

Module 2: Adjusting Fills and Borders: General Procedures

• About the Fill tab
• Formatting borders

Module 3: Formatting Chart Background Elements

• Working with the chart area
• Working with the plot area

Module 4: Formatting Chart Series

• Basic series formatting
• Using pictures and graphics for series formatting
• Additional series options

Module 5: Working with Chart Titles

• Adding titles to a chart
• Changing title text
• Formatting title text
• Linking title text to a cell

Module 6: Working with a Chart’s Legend

• Adding or removing a legend
• Moving or resizing a legend
• Formatting a legend
• Changing the legend text
• Deleting a legend entry
• Identifying series without using a legend

Module 7: Working with Chart Axes

• Value axis versus category axis
• Value axis scales
• Using time-scale axes
• Creating a multiline category axis
• Removing axes
• Axis number formats

Module 8: Working with Gridlines

• Adding or removing gridlines

Module 9: Working with Data Labels

• Adding or removing data labels
• Editing data labels
• Problems and limitations with data labels

Module 10: Working with a Chart Data Table

• Adding and removing a data table
• Problems and limitations with data tables

Chapter 7: Components That Show Trending

Module 1: Trending Dos and Don’ts

• Using chart types appropriate for trending
• Starting the vertical scale at zero
• Leveraging Excel’s logarithmic scale
• Applying creative label management

Module 2: Comparative Trending

• Creating side-by-side time comparisons
• Creating stacked time comparisons
• Trending with a secondary axis

Module 3: Emphasizing Periods of Time

• Formatting specific periods
• Using dividers to mark significant events
• Representing forecasts in your trending components

Chapter 8: Components That Group Data

Module 1: Listing Top and Bottom Values

• Organizing source data
• Using pivot tables to get top and bottom views
• Using Histograms to Track Relationships and Frequency
• Adding formulas to group data
• Adding a cumulative percent

Module 2: Using a pivot table to create a histogram

Module 3: Emphasizing Top Values in Charts

PART III: ADVANCED DASHBOARDING CONCEPTS

Chapter 9: Developing Your Data Model

Module 1: Building a Data Model

• Separating the data, analysis, and presentation layers

Module 2: Data Model Best Practices

• Avoid storing excess data
• Use tabs to document and organize your data model
• Test your data model before building presentation components

Module 3: Excel Functions for Your Data Model

• Understanding lookup tables
• The VLOOKUP function
• The HLookup function
• The SUMPRODUCT function
• The Choose function

Module 4: Working with Excel Tables

• Converting a range to an Excel table
• Converting an Excel table back to a range

Chapter 10: Adding Interactive Controls to Your Dashboard

Module 1: Getting Started with Form Controls

• Finding Form controls
• Adding a control to a worksheet

Module 2: Using the Button Control

Module 3: Using the Check Box Control

• Check box example: Toggling a chart series on and off

Module 4: Using the Option Button Control

• Option button example: Showing many views through one chart

Module 5: Using the Combo Box Control

• Combo box example: Changing chart data with a drop-down selector

Module 6: Using the List Box Control

• List box example: Controlling multiple charts with one selector

Chapter 11: Macro-Charged Reporting

Module 1: Why Use a Macro?

• Recording Your First Macro
• Running your macros
• Assigning a macro to a button

Module 2: Enabling Macros in Excel 2013

• Viewing the new Excel security message
• Setting up trusted locations

Module 3: Excel Macro Examples

• Building navigation buttons
• Dynamically rearranging pivot table data
• Offering one-touch reporting options

PART IV: PIVOT TABLE DRIVEN DASHBOARDS

Chapter 12: Using Pivot Tables

Module 1: Introducing the Pivot Table

• Anatomy of a pivot table
• Creating the basic pivot table

Module 2: Customizing Your Pivot Table

• Changing the pivot table layout
• Renaming the fields
• Formatting numbers
• Changing summary calculations
• Suppressing subtotals
• Removing all subtotals at one time
• Removing the subtotals for only one field
• Removing grand totals
• Hiding and showing data items
• Hiding or showing items without data
• Sorting your pivot table

Module 3: Examples of Filtering Your Data

• Producing top and bottom views
• Creating views by month, quarter, and year
• Creating a percent distribution view
• Creating a YTD totals view
• Creating a month-over-month variance view

Chapter 13: Using Pivot Charts

Module 1: Getting Started with Pivot Charts

• Creating a pivot chart
• Understanding the link between pivot charts and pivot tables
• Limitations of pivot charts
• Using conditional formatting with pivot tables
• Customizing conditional formatting

Module 2: Alternatives to Pivot Charts

• Disconnecting charts from pivot tables
• Create standalone charts that are connected to your pivot table

Chapter 14: Adding Interactivity with Slicers

Module 1: Understanding Slicers

Module 2: Creating a Standard Slicer

• Formatting slicers
• Controlling multiple pivot tables

Module 3: Creating a Timeline Slicer

Module 4: Using Slicers as Form Controls