Table of Contents
What You Need to Know What You Need to Have Conventions in This Book Keyboard conventions Mouse conventions What the icons mean How This Book Is Organized Part I: Getting Started with Excel Dashboards Part II: Introducing Charts into Your Dashboards Part III: Advanced Dashboarding Concepts Part IV: Pivot Table Driven Dashboards Part V: Working with the Outside World About the Companion Website About the Power Utility Pak Offer Reach Out
Keyboard conventions Mouse conventions What the icons mean
Part I: Getting Started with Excel Dashboards Part II: Introducing Charts into Your Dashboards Part III: Advanced Dashboarding Concepts Part IV: Pivot Table Driven Dashboards Part V: Working with the Outside World
Chapter 1: Introducing Dashboards What Are Dashboards and Reports? Defining reports Defining dashboards Establish the User Requirements Define the message(s) Establish the audience Define the performance measures List the required data sources Define the dimensions and filters Determine the need for drill-down details Establish the update schedule A Quick Look at Dashboard Design Principles Rule number 1: Keep it simple Use layout and placement to draw focus Format numbers effectively Use titles and labels effectively Key Questions to Ask Before Distributing Your Dashboard Does my dashboard present the right information? Does everything on my dashboard have a purpose? Does my dashboard prominently display the key message? Can I maintain this dashboard? Does my dashboard clearly display its scope and shelf life? Is my dashboard well documented? Is my dashboard user-friendly? Is my dashboard accurate? Chapter 2: Table Design Best Practices Table Design Principles Use colors sparingly De-emphasize borders Use effective number formatting Subdue your labels and headers Enhancing Reporting with Custom Number Formatting Number formatting basics Formatting numbers in thousands and millions Hiding and suppressing zeros Applying custom format colors Formatting dates and times Adding conditions to customer number formatting Chapter 3: Using Excel Sparklines Understanding Sparklines Applying Sparklines Creating Sparklines 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 4: Chartless Visualization Techniques Enhancing Reports with Conditional Formatting Applying basic conditional formatting Adding your own formatting rules manually Show only one icon Show Data Bars and icons outside of cells Representing trends with icon sets Using Symbols to Enhance Reporting Using Excel’s Camera Tool Finding the Camera tool Using the Camera tool Enhancing a dashboard with the Camera tool
What Are Dashboards and Reports? Defining reports Defining dashboards Establish the User Requirements Define the message(s) Establish the audience Define the performance measures List the required data sources Define the dimensions and filters Determine the need for drill-down details Establish the update schedule A Quick Look at Dashboard Design Principles Rule number 1: Keep it simple Use layout and placement to draw focus Format numbers effectively Use titles and labels effectively Key Questions to Ask Before Distributing Your Dashboard Does my dashboard present the right information? Does everything on my dashboard have a purpose? Does my dashboard prominently display the key message? Can I maintain this dashboard? Does my dashboard clearly display its scope and shelf life? Is my dashboard well documented? Is my dashboard user-friendly? Is my dashboard accurate?
Defining reports Defining dashboards
Define the message(s) Establish the audience Define the performance measures List the required data sources Define the dimensions and filters Determine the need for drill-down details Establish the update schedule
Rule number 1: Keep it simple Use layout and placement to draw focus Format numbers effectively Use titles and labels effectively
Does my dashboard present the right information? Does everything on my dashboard have a purpose? Does my dashboard prominently display the key message? Can I maintain this dashboard? Does my dashboard clearly display its scope and shelf life? Is my dashboard well documented? Is my dashboard user-friendly? Is my dashboard accurate?
Table Design Principles Use colors sparingly De-emphasize borders Use effective number formatting Subdue your labels and headers Enhancing Reporting with Custom Number Formatting Number formatting basics Formatting numbers in thousands and millions Hiding and suppressing zeros Applying custom format colors Formatting dates and times Adding conditions to customer number formatting
Use colors sparingly De-emphasize borders Use effective number formatting Subdue your labels and headers
Number formatting basics Formatting numbers in thousands and millions Hiding and suppressing zeros Applying custom format colors Formatting dates and times Adding conditions to customer number formatting
Understanding Sparklines Applying Sparklines Creating Sparklines 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
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
Enhancing Reports with Conditional Formatting Applying basic conditional formatting Adding your own formatting rules manually Show only one icon Show Data Bars and icons outside of cells Representing trends with icon sets Using Symbols to Enhance Reporting Using Excel’s Camera Tool Finding the Camera tool Using the Camera tool Enhancing a dashboard with the Camera tool
Applying basic conditional formatting Adding your own formatting rules manually Show only one icon Show Data Bars and icons outside of cells Representing trends with icon sets
Finding the Camera tool Using the Camera tool Enhancing a dashboard with the Camera tool
Chapter 5: Excel Charting for the Uninitiated What Is a Chart? How Excel Handles Charts Embedded charts Chart sheets Parts of a Chart 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 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 6: Working with Chart Series Specifying the Data for Your Chart Adding a New Series to a Chart Adding a new series by copying a range Adding a new series by extending the range highlight Adding a new series using the Select Data Source dialog box Adding a new series by typing a new SERIES formula Deleting a Chart Series Modifying the Data Range for a Chart Series Using range highlighting to change series data Using the Select Data Source dialog box to change series data Editing the SERIES formula to change series data Understanding Series Names Changing a series name Deleting a series name Adjusting the Series Plot Order Charting a Noncontiguous Range Using Series on Different Sheets Handling Missing Data Controlling a Data Series by Hiding Data Unlinking a Chart Series from Its Data Range Converting a chart to a picture Converting a range reference to arrays Working with Multiple Axes Creating a secondary value axis Creating a chart with four axes Chapter 7: Formatting and Customizing Charts Chart Formatting Overview Selecting chart elements Common chart elements UI choices for formatting Adjusting Fills and Borders: General Procedures About the Fill tab Formatting borders Formatting Chart Background Elements Working with the chart area Working with the plot area Formatting Chart Series Basic series formatting Using pictures and graphics for series formatting Additional series options Working with Chart Titles Adding titles to a chart Changing title text Formatting title text Linking title text to a cell 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 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 Working with Gridlines Adding or removing gridlines Working with Data Labels Adding or removing data labels Editing data labels Problems and limitations with data labels Working with a Chart Data Table Adding and removing a data table Problems and limitations with data tables Chapter 8: Components That Show Trending 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 Comparative Trending Creating side-by-side time comparisons Creating stacked time comparisons Trending with a secondary axis Emphasizing Periods of Time Formatting specific periods Using dividers to mark significant events Representing forecasts in your trending components Other Trending Techniques Avoiding overload with directional trending Smoothing data Chapter 9: Components That Group Data 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 Using a pivot table to create a histogram Emphasizing Top Values in Charts CHapter 10: Components That Show Performance Against a Target Showing Performance with Variances Showing Performance Against Organizational Trends Using a Thermometer-Style Chart Using a Bullet Graph Creating a bullet graph Adding data to your bullet graph Final thoughts on formatting bullet graphs Showing Performance Against a Target Range
What Is a Chart? How Excel Handles Charts Embedded charts Chart sheets Parts of a Chart 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 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
Embedded charts Chart sheets
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
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
Specifying the Data for Your Chart Adding a New Series to a Chart Adding a new series by copying a range Adding a new series by extending the range highlight Adding a new series using the Select Data Source dialog box Adding a new series by typing a new SERIES formula Deleting a Chart Series Modifying the Data Range for a Chart Series Using range highlighting to change series data Using the Select Data Source dialog box to change series data Editing the SERIES formula to change series data Understanding Series Names Changing a series name Deleting a series name Adjusting the Series Plot Order Charting a Noncontiguous Range Using Series on Different Sheets Handling Missing Data Controlling a Data Series by Hiding Data Unlinking a Chart Series from Its Data Range Converting a chart to a picture Converting a range reference to arrays Working with Multiple Axes Creating a secondary value axis Creating a chart with four axes
Adding a new series by copying a range Adding a new series by extending the range highlight Adding a new series using the Select Data Source dialog box Adding a new series by typing a new SERIES formula
Using range highlighting to change series data Using the Select Data Source dialog box to change series data Editing the SERIES formula to change series data
Changing a series name Deleting a series name
Converting a chart to a picture Converting a range reference to arrays
Creating a secondary value axis Creating a chart with four axes
Chart Formatting Overview Selecting chart elements Common chart elements UI choices for formatting Adjusting Fills and Borders: General Procedures About the Fill tab Formatting borders Formatting Chart Background Elements Working with the chart area Working with the plot area Formatting Chart Series Basic series formatting Using pictures and graphics for series formatting Additional series options Working with Chart Titles Adding titles to a chart Changing title text Formatting title text Linking title text to a cell 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 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 Working with Gridlines Adding or removing gridlines Working with Data Labels Adding or removing data labels Editing data labels Problems and limitations with data labels Working with a Chart Data Table Adding and removing a data table Problems and limitations with data tables
Selecting chart elements Common chart elements UI choices for formatting
About the Fill tab Formatting borders
Working with the chart area Working with the plot area
Basic series formatting Using pictures and graphics for series formatting Additional series options
Adding titles to a chart Changing title text Formatting title text Linking title text to a cell
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
Value axis versus category axis Value axis scales Using time-scale axes Creating a multiline category axis Removing axes Axis number formats
Adding or removing gridlines
Adding or removing data labels Editing data labels Problems and limitations with data labels
Adding and removing a data table Problems and limitations with data tables
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 Comparative Trending Creating side-by-side time comparisons Creating stacked time comparisons Trending with a secondary axis Emphasizing Periods of Time Formatting specific periods Using dividers to mark significant events Representing forecasts in your trending components Other Trending Techniques Avoiding overload with directional trending Smoothing data
Using chart types appropriate for trending Starting the vertical scale at zero Leveraging Excel’s logarithmic scale Applying creative label management
Creating side-by-side time comparisons Creating stacked time comparisons Trending with a secondary axis
Formatting specific periods Using dividers to mark significant events Representing forecasts in your trending components
Avoiding overload with directional trending Smoothing data
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 Using a pivot table to create a histogram Emphasizing Top Values in Charts
Organizing source data Using pivot tables to get top and bottom views
Adding formulas to group data Adding a cumulative percent Using a pivot table to create a histogram
Showing Performance with Variances Showing Performance Against Organizational Trends Using a Thermometer-Style Chart Using a Bullet Graph Creating a bullet graph Adding data to your bullet graph Final thoughts on formatting bullet graphs Showing Performance Against a Target Range
Creating a bullet graph Adding data to your bullet graph Final thoughts on formatting bullet graphs
Chapter 11: Developing Your Data Model Building a Data Model Separating the data, analysis, and presentation layers 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 Excel Functions for Your Data Model Understanding lookup tables The VLOOKUP function The HLookup function The SUMPRODUCT function The Choose function Working with Excel Tables Converting a range to an Excel table Converting an Excel table back to a range Chapter 12: Adding Interactive Controls to Your Dashboard Getting Started with Form Controls Finding Form controls Adding a control to a worksheet Using the Button Control Using the Check Box Control Check box example: Toggling a chart series on and off Using the Option Button Control Option button example: Showing many views through one chart Using the Combo Box Control Combo box example: Changing chart data with a drop-down selector Using the List Box Control List box example: Controlling multiple charts with one selector Chapter 13: Macro-Charged Reporting Why Use a Macro? Recording Your First Macro Running your macros Assigning a macro to a button Enabling Macros in Excel 2013 Viewing the new Excel security message Setting up trusted locations Excel Macro Examples Building navigation buttons Dynamically rearranging pivot table data Offering one-touch reporting options
Building a Data Model Separating the data, analysis, and presentation layers 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 Excel Functions for Your Data Model Understanding lookup tables The VLOOKUP function The HLookup function The SUMPRODUCT function The Choose function Working with Excel Tables Converting a range to an Excel table Converting an Excel table back to a range
Separating the data, analysis, and presentation layers
Avoid storing excess data Use tabs to document and organize your data model Test your data model before building presentation components
Understanding lookup tables The VLOOKUP function The HLookup function The SUMPRODUCT function The Choose function
Converting a range to an Excel table Converting an Excel table back to a range
Getting Started with Form Controls Finding Form controls Adding a control to a worksheet Using the Button Control Using the Check Box Control Check box example: Toggling a chart series on and off Using the Option Button Control Option button example: Showing many views through one chart Using the Combo Box Control Combo box example: Changing chart data with a drop-down selector Using the List Box Control List box example: Controlling multiple charts with one selector
Finding Form controls Adding a control to a worksheet
Check box example: Toggling a chart series on and off
Option button example: Showing many views through one chart
Combo box example: Changing chart data with a drop-down selector
List box example: Controlling multiple charts with one selector
Why Use a Macro? Recording Your First Macro Running your macros Assigning a macro to a button Enabling Macros in Excel 2013 Viewing the new Excel security message Setting up trusted locations Excel Macro Examples Building navigation buttons Dynamically rearranging pivot table data Offering one-touch reporting options
Running your macros Assigning a macro to a button
Viewing the new Excel security message Setting up trusted locations
Building navigation buttons Dynamically rearranging pivot table data Offering one-touch reporting options
Chapter 14: Using Pivot Tables Introducing the Pivot Table Anatomy of a pivot table Creating the basic pivot table 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 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 15: Using Pivot Charts 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 Alternatives to Pivot Charts Disconnecting charts from pivot tables Create standalone charts that are connected to your pivot table Chapter 16: Adding Interactivity with Slicers Understanding Slicers Creating a Standard Slicer Formatting slicers Controlling multiple pivot tables Creating a Timeline Slicer Using Slicers as Form Controls Chapter 17: Using the Internal Data Model and Power View Understanding the Internal Data Model Building out your first data model Using your Data Model in a pivot table Using external data sources in your internal Data Model Creating a Power View Dashboard Creating and working with Power View charts Visualizing data in a Power View map Changing the look of your Power View dashboard
Introducing the Pivot Table Anatomy of a pivot table Creating the basic pivot table 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 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
Anatomy of a pivot table Creating the basic 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
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
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 Alternatives to Pivot Charts Disconnecting charts from pivot tables Create standalone charts that are connected to your pivot table
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
Disconnecting charts from pivot tables Create standalone charts that are connected to your pivot table
Understanding Slicers Creating a Standard Slicer Formatting slicers Controlling multiple pivot tables Creating a Timeline Slicer Using Slicers as Form Controls
Formatting slicers Controlling multiple pivot tables
Understanding the Internal Data Model Building out your first data model Using your Data Model in a pivot table Using external data sources in your internal Data Model Creating a Power View Dashboard Creating and working with Power View charts Visualizing data in a Power View map Changing the look of your Power View dashboard
Building out your first data model Using your Data Model in a pivot table Using external data sources in your internal Data Model
Creating and working with Power View charts Visualizing data in a Power View map Changing the look of your Power View dashboard
Chapter 18: Integrating External Data into Excel Reporting Importing Data from Microsoft Access The drag-and-drop method The Microsoft Access Export Wizard The Get External Data icon Importing Data from SQL Server Passing Your Own SQL Statements to External Databases Manually editing SQL statements Running stored procedures from Excel Using VBA to create dynamic connections Chapter 19: Sharing Your Work with the Outside World Securing Your Dashboards and Reports Securing access to the entire workbook Limiting access to specific worksheet ranges Protecting the workbook structure Linking Your Excel Dashboards to PowerPoint Creating the link between Excel and PowerPoint Manually updating links to capture updates Automatically updating links Distributing Your Dashboards via a PDF Distributing Your Dashboards to SkyDrive Limitations when publishing to the web
Importing Data from Microsoft Access The drag-and-drop method The Microsoft Access Export Wizard The Get External Data icon Importing Data from SQL Server Passing Your Own SQL Statements to External Databases Manually editing SQL statements Running stored procedures from Excel Using VBA to create dynamic connections
The drag-and-drop method The Microsoft Access Export Wizard The Get External Data icon
Manually editing SQL statements Running stored procedures from Excel Using VBA to create dynamic connections
Securing Your Dashboards and Reports Securing access to the entire workbook Limiting access to specific worksheet ranges Protecting the workbook structure Linking Your Excel Dashboards to PowerPoint Creating the link between Excel and PowerPoint Manually updating links to capture updates Automatically updating links Distributing Your Dashboards via a PDF Distributing Your Dashboards to SkyDrive Limitations when publishing to the web
Securing access to the entire workbook Limiting access to specific worksheet ranges Protecting the workbook structure
Creating the link between Excel and PowerPoint Manually updating links to capture updates Automatically updating links
Limitations when publishing to the web