Cover
Title Page
Introduction
1. What Does This Book Cover?
2. Companion Download Files
3. How to Contact the Publisher
Part I: Display Data on a Dashboard
1. CHAPTER 1: Dashboard Basics
  1. Determining When to Use a Dashboard
  2. Establishing User Requirements
  3. Assembling the Data
  4. Building the Dashboard
  5. Formatting the Dashboard
2. CHAPTER 2: Dashboard Case Studies
  1. Monitoring Progress
  2. Displaying Key Performance Indicators
  3. Reporting Financial Information
3. CHAPTER 3: Organizing Data for Dashboards
  1. Separating Data Layers
  2. Working with External Data
  3. Transforming Data in Power Query
Part II: Visualization Primer
1. CHAPTER 4: The Fundamentals of Effective Visualization
  1. Creating an Effective Visualization
  2. Driving Meaning with Color
  3. Focusing Attention on Text
  4. Showing Insights with Charts
2. CHAPTER 5: Non-chart Visualizations
  1. Understanding Custom Number Formats
  2. Using Icons
  3. Creating Sparklines
3. CHAPTER 6: Using Shapes to Create Infographics
  1. Working with Shapes
  2. Framing Data with Shapes
  3. Creating Custom Infographics
  4. Adding Other Illustrations
Part III: Tell a Story with Visualization
1. CHAPTER 7: Visualizing Performance Comparisons
  1. Single Measurements
  2. Column Charts
  3. Bullet Charts
  4. Clustered Column Charts
  5. Funnel Charts
  6. XY Charts
  7. Bubble Charts
  8. Dot Plot Charts
2. CHAPTER 8: Visualizing Parts of a Whole
  1. Pie Charts
  2. Doughnut Charts
  3. Waffle Charts
  4. Sunburst Charts
  5. Histograms
  6. Treemap Charts
  7. Waterfall Charts
3. CHAPTER 9: Visualizing Changes Over Time
  1. Line Charts
  2. Column Charts with Variances
  3. Combination Charts
  4. Line Charts with Differences
  5. Side-by-Side Box Plots
  6. Animated Charts
  7. Chart Automation
Index
Copyright
Dedication
About the Author
About the Technical Editor
Acknowledgments
End User License Agreement

List of Illustrations

Chapter 1
1. Figure 1.1: Data shown raw, aggregated, and analyzed and presented
2. Figure 1.2: Raw data for a PivotTable
3. Figure 1.3: A PivotTable
4. Figure 1.4: The PivotTable Fields task pane
5. Figure 1.5: Nested fields in the Columns area
6. Figure 1.6: The PivotTable Options dialog box
7. Figure 1.7: The Field Settings dialog box
8. Figure 1.8: A PivotTable with no grand totals or subtotals
9. Figure 1.9: The VLOOKUP function
10. Figure 1.10: The XLOOKUP function
11. Figure 1.11: The INDEX and MATCH functions
12. Figure 1.12: The SUMPRODUCT function
13. Figure 1.13: Filter values with SUMPRODUCT
14. Figure 1.14: An array formula to find the largest value
15. Figure 1.15: A column chart based on a table
16. Figure 1.16: The Create Table dialog
17. Figure 1.17: The table expands for new data.
18. Figure 1.18: The chart adjusts for new data.
19. Figure 1.19: Excel's formula entry drop-down
20. Figure 1.20: Options for table formula completion
21. Figure 1.21: Data that is lumped together in a cell
22. Figure 1.22: The Convert Text to Columns Wizard
23. Figure 1.23: Excel splits the data on the delimiter.
24. Figure 1.24: Data split into separate columns
25. Figure 1.25: Data with duplicate records
26. Figure 1.26: The Remove Duplicates dialog
27. Figure 1.27: Excel displays the number of deleted values.
28. Figure 1.28: A dashboard with different sized elements
29. Figure 1.29: A dashboard of the same chart type
30. Figure 1.30: A trendline that adds little information
31. Figure 1.31: A trendline helps when data varies.
32. Figure 1.32: The Format Trendline task pane
33. Figure 1.33: Too much formatting detracts from the chart's message.
34. Figure 1.34: Clean and simple formatting keeps the message front and center....
35. Figure 1.35: Excel's color pallet
36. Figure 1.36: Excel's theme gallery
37. Figure 1.37: Data labels can be used to highlight unusual variations.
38. Figure 1.38: Formatted large numbers in the value axis
39. Figure 1.39: The Number section of the Format Axis task pane
Chapter 2
1. Figure 2.1: A rough layout of the dashboard
2. Figure 2.2: Collecting data for hours and tasks
3. Figure 2.3: Collecting data for code commits
4. Figure 2.4: Collecting data for overall progress
5. Figure 2.5: The Select Data Source dialog
6. Figure 2.6: An intermediate hours bar chart
7. Figure 2.7: Move the Actual series to the secondary axis.
8. Figure 2.8: Overlapping bar charts
9. Figure 2.9: Bullet charts with elements removed
10. Figure 2.10: Two bullet charts positioned on the dashboard
11. Figure 2.11: Create a PivotChart on the Dashboard worksheet.
12. Figure 2.12: The initial PivotChart for commits by sprint
13. Figure 2.13: Filter the PivotTable to hide zeros.
14. Figure 2.14: The completed Code Commits by Sprint line chart
15. Figure 2.15: Create the PivotTable data for the Commits by Developer chart....
16. Figure 2.16: Filter the PivotChart for the current sprint.
17. Figure 2.17: The first four visual elements
18. Figure 2.18: The initial phases bar chart and table
19. Figure 2.19: Reverse the order of the categories.
20. Figure 2.20: A stacked bar chart to simulate a Gantt chart
21. Figure 2.21: Add the green formatting rule.
22. Figure 2.22: Conditionally formatting a cell for green or red
23. Figure 2.23: The final project progress dashboard
24. Figure 2.24: A rough layout of the HR dashboard
25. Figure 2.25: Recruiting cost Data provided by HR
26. Figure 2.26: An employee list with separation reasons
27. Figure 2.27: The new hire log
28. Figure 2.28: The initial recruiting cost column chart and data
29. Figure 2.29: The final recruiting cost column chart
30. Figure 2.30: Choose the clustered column chart with year in the category axi...
31. Figure 2.31: The chart with only two series
32. Figure 2.32: Change the Cost per Applicant series to a line chart.
33. Figure 2.33: The two recruiting cost charts
34. Figure 2.34: The PivotTable Fields task pane for the Gender chart
35. Figure 2.35: Doughnut charts for the diversity hire KPIs
36. Figure 2.36: Building the PivotChart for the 90-Day Quit Rate chart
37. Figure 2.37: Change the aggregate to count the EmployeeID field.
38. Figure 2.38: The final 90-Day Quit Rate chart
39. Figure 2.39: The voluntary separation chart filtered
40. Figure 2.40: Sort the PivotChart by Count of EmployeeID.
41. Figure 2.41: The final Voluntary Separations bar chart
42. Figure 2.42: The final Average Days to Fill Vacancies chart
43. Figure 2.43: Rename a chart with the Name box.
44. Figure 2.44: Insert a module into the project.
45. Figure 2.45: The final HR dashboard
46. Figure 2.46: A rough layout of the financial dashboard
47. Figure 2.47: Balance sheet and income statement data
48. Figure 2.48: Reversing signs via Paste Special ➪ Multiply
49. Figure 2.49: The initial waterfall chart
50. Figure 2.50: Set the totals data points.
51. Figure 2.51: The Current Year Income Statement waterfall chart
52. Figure 2.52: Stage the data for the gauge charts.
53. Figure 2.53: The initial current ratio doughnut chart
54. Figure 2.54: Resize the plot area to fill More of the chart area.
55. Figure 2.55: Set the fill colors for each data point.
56. Figure 2.56: The Current Ratio gauge chart
57. Figure 2.57: Four balance sheet ratio gauge charts
58. Figure 2.58: Gross and net margin calculations
59. Figure 2.59: The six-chart gauge panel
60. Figure 2.60: The five-year revenue chart
61. Figure 2.61: Three five-year income statement charts
62. Figure 2.62: Pasting a new series to an existing chart
63. Figure 2.63: Point the series name to a cell.
64. Figure 2.64: Six trend charts for financial data
65. Figure 2.65: The financial data dashboard
Chapter 3
1. Figure 3.1: The three data layers
2. Figure 3.2: A well formatted data file
3. Figure 3.3: A poorly formatted data file
4. Figure 3.4: Data formatted as delimited and fixed width
5. Figure 3.5: The Get Data drop-down on the Ribbon
6. Figure 3.6: The import delimiter choices
7. Figure 3.7: The text file in a table
8. Figure 3.8: The import screen for comma delimited
9. Figure 3.9: The legacy Text Import Wizard
10. Figure 3.10: Include legacy import wizards on the Ribbon in Excel Options.
11. Figure 3.11: The Import Data dialog
12. Figure 3.12: Sales transaction data in an Excel file
13. Figure 3.13: Import an Excel file from the Ribbon.
14. Figure 3.14: The Power Query Navigator window
15. Figure 3.15: Excel data imported and the Queries & Connections task pane
16. Figure 3.16: Excel's warning when adding an existing data set to the Data Mo...
17. Figure 3.17: Enable the Power Pivot add-in in Excel.
18. Figure 3.18: The Power Pivot interface
19. Figure 3.19: Creating a relationship in Power Pivot
20. Figure 3.20: Power Pivot's Diagram View shows relationships.
21. Figure 3.21: Create a PivotTable based on the Data Model.
22. Figure 3.22: A PivotTable based on the Data Model shows all data sets.
23. Figure 3.23: A PivotTable summing sales by salesperson's last name
24. Figure 3.24: The Power Query Navigator dialog for importing data from an Acc...
25. Figure 3.25: The Orders table from the Northwind database
26. Figure 3.26: Select multiple data sets in the Navigator dialog.
27. Figure 3.27: Existing Access relationships are already created in the Data M...
28. Figure 3.28: Loading a single table from the Queries & Connections task pane...
29. Figure 3.29: The Import Data dialog
30. Figure 3.30: Create a PivotTable from the Data Model.
31. Figure 3.31: A PivotTable based on the Data Model
32. Figure 3.32: Power Query selects all related tables.
33. Figure 3.33: Selecting related tables creates all the relationships in the D...
34. Figure 3.34: Excel's dialog for entering the SQL Server name
35. Figure 3.35: The Navigator dialog for SQL Server
36. Figure 3.36: Power Pivot's Diagram View for the SalesOrderHeader and SalesOr...
37. Figure 3.37: Click the Transform Data button on the Navigator dialog.
38. Figure 3.38: The Power Query Editor
39. Figure 3.39: The Advanced Editor
40. Figure 3.40: Remove a column from a query.
41. Figure 3.41: Remove rows from a query.
42. Figure 3.42: Filter orders with January order dates.
43. Figure 3.43: Edit a step by clicking the gear icon.
44. Figure 3.44: Changing the shipped date to a date data type
45. Figure 3.45: Changing to an improper data type results in errors.
46. Figure 3.46: Editing a step in the formula bar
47. Figure 3.47: Replace values in a column.
48. Figure 3.48: Multiplying the Shipping Fee column by a fixed value
49. Figure 3.49: Round Up rounds to the nearest higher whole number.
50. Figure 3.50: Statistics functions return a single value.
51. Figure 3.51: The Split Column By Delimiter dialog
52. Figure 3.52: Splitting a value by digit to non-digit
53. Figure 3.53: Adding columns from examples
54. Figure 3.54: Power Query uses an example to determine how to split the remai...
55. Figure 3.55: The new column is added to the end of the data set.
Chapter 4
1. Figure 4.1: Scale a worksheet to fit on a single page.
2. Figure 4.2: The Scale tool displays the actual scaling percentage.
3. Figure 4.3: An out-of-balance dashboard due to one dense element
4. Figure 4.4: Eliminating clutter makes the chart easier to understand.
5. Figure 4.5: A chart with too much data is hard to read.
6. Figure 4.6: A chart with three layers of complexity
7. Figure 4.7: Five-year revenue in three chart types
8. Figure 4.8: A typical stock chart
9. Figure 4.9: Darker colors represent higher values.
10. Figure 4.10: Use color to highlight a data point.
11. Figure 4.11: A bar chart of office supply subcategories
12. Figure 4.12: Grouping a bar chart with color
13. Figure 4.13: A small number of legend entries are easy to remember.
14. Figure 4.14: Merging the legend and chart title
15. Figure 4.15: A legend and data labels serve the same function.
16. Figure 4.16: Showing first and last data labels provides context.
17. Figure 4.17: An anomalous data point labeled for clarity
18. Figure 4.18: A chart to mine data to find anomalies
19. Figure 4.19: A pie chart with too many data points
20. Figure 4.20: A bar chart can show more data points.
21. Figure 4.21: A 100% stacked bar chart focuses on the components.
22. Figure 4.22: A stacked bar shows the components and the total.
23. Figure 4.23: A scatter plot shows the relationship between two variables.
Chapter 5
1. Figure 5.1: A one-section number format applies to all numeric conditions an...
2. Figure 5.2: The second section governs negative numbers.
3. Figure 5.3: The third section controls zero values.
4. Figure 5.4: The fourth section affects text entries.
5. Figure 5.5: The # character shows only digits that exist.
6. Figure 5.6: Characters in a number format can cause numbers to not line up....
7. Figure 5.7: The underscore character helps align values.
8. Figure 5.8: Use an asterisk to align a currency symbol.
9. Figure 5.9: Select the Custom category to see how a built-in category is con...
10. Figure 5.10: The Color Scales gallery on the Ribbon
11. Figure 5.11: A range with the Green - Yellow - Red Color Scale applied
12. Figure 5.12: The Conditional Formatting Rules Manager
13. Figure 5.13: The Edit Formatting Rule dialog
14. Figure 5.14: A 2-Color Scale formatting rule
15. Figure 5.15: Two ranges with the Percent and Percentile format styles applie...
16. Figure 5.16: Use the same color for Minimum and Maximum to highlight extreme...
17. Figure 5.17: Data Bars applied to a range
18. Figure 5.18: The New Formatting Rule dialog
19. Figure 5.19: The Negative Value and Axis Settings dialog
20. Figure 5.20: Negative Data Bars extending to the left and differently colore...
21. Figure 5.21: A directional Icon Set applied to a range
22. Figure 5.22: The New Formatting Rule dialog for Icon Sets
23. Figure 5.23: The Icon drop-down allows you to create custom Icon Sets.
24. Figure 5.24: Three types of Sparklines
25. Figure 5.25: The Create Sparklines dialog
26. Figure 5.26: A newly inserted Sparkline group is selected
27. Figure 5.27: The Edit Sparkline Data dialog allows you to change the source ...
28. Figure 5.28: Use the Clear tool to delete individual Sparklines or the whole...
29. Figure 5.29: Change the group's source data in the Edit Sparklines dialog.
30. Figure 5.30: The Hidden and Empty Cell Settings dialog
31. Figure 5.31: Change a Sparkline's color using the Style Gallery.
32. Figure 5.32: Change a Sparkline's thickness with the Weight tool.
33. Figure 5.33: Comparing the General and Date Axis Types
34. Figure 5.34: Sparklines with positive and negative values will show an axis...
35. Figure 5.35: Comparing the scaling options for Sparklines
Chapter 6
1. Figure 6.1: Insert a shape from the Shapes Gallery.
2. Figure 6.2: Insert a shape by clicking and dragging.
3. Figure 6.3: Selecting a shape displays its sizing handles.
4. Figure 6.4: Hold down the Ctrl key when resizing to maintain the center posi...
5. Figure 6.5: Excel names each shape inserted.
6. Figure 6.6: Change a shape's style from the Shape Styles Theme Style gallery...
7. Figure 6.7: Change the gradient with a preset gradient from the gallery.
8. Figure 6.8: Use the alignment tools to help aligning shapes.
9. Figure 6.9: A simple gauge chart from a financial dashboard
10. Figure 6.10: Position a rectangle across the top of the chart.
11. Figure 6.11: Position a triangle below the rectangle.
12. Figure 6.12: Position another triangle on the other side of the chart.
13. Figure 6.13: Use a formula to show text in a shape.
14. Figure 6.14: Combined shapes to frame a chart title
15. Figure 6.15: Replacing the chart title and legend with shapes
16. Figure 6.16: An exploded view of the binder tab chart
17. Figure 6.17: An invisible chart title keeps the other elements from filling ...
18. Figure 6.18: Make the text box larger than necessary to ensure the text alig...
19. Figure 6.19: You can rename shapes to better manage them.
20. Figure 6.20: Use the Selection task pane to make selecting shapes easier.
21. Figure 6.21: Hold down the Ctrl key to select multiple shapes in the Selecti...
22. Figure 6.22: Group shapes to work with them as a single unit.
23. Figure 6.23: House sales for a real estate office
24. Figure 6.24: Format the shapes to complement the chart.
25. Figure 6.25: Group shapes to make them easier to move.
26. Figure 6.26: Some shapes have additional sizing points.
27. Figure 6.27: Use additional sizing points to create custom shapes.
28. Figure 6.28: Use edit points to further customize a shape.
29. Figure 6.29: Right-click on a segment to modify it.
30. Figure 6.30: Right-click on an edit point to modify it.
31. Figure 6.31: The dialog for inserting icons and other images
32. Figure 6.32: Limit the icons using the search box
33. Figure 6.33: Search in the Cutout People section.
34. Figure 6.34: Add a text box to the sign.
35. Figure 6.35: Resize and rotate the text box to better fit the sign.
36. Figure 6.36: Wrap the text within the text box.
37. Figure 6.37: Make the text box transparent.
Chapter 7
1. Figure 7.1: A single percentage in a dashboard
2. Figure 7.2: A single percentage showing change
3. Figure 7.3: Total sales dollars shown as raw and rounded
4. Figure 7.4: Bar charts are column charts shown horizontally.
5. Figure 7.5: Start the values axis at zero.
6. Figure 7.6: Grouping categories reduces noise in your chart.
7. Figure 7.7: Table headings for Q1 sales
8. Figure 7.8: Product categories for Q1 sales
9. Figure 7.9: A completed Q1 sales table
10. Figure 7.10: A column chart for Q1 sales
11. Figure 7.11: A typical bullet chart
12. Figure 7.12: Bullet chart data
13. Figure 7.13: A clustered bar chart
14. Figure 7.14: Changing the axis for one series
15. Figure 7.15: The Gap Width setting on the Format Data Series task pane
16. Figure 7.16: Bullet chart after gap width and color adjustments
17. Figure 7.17: Hide an axis using the on-object chart customization buttons.
18. Figure 7.18: The completed bullet chart
19. Figure 7.19: A stacked column chart and a clustered column chart
20. Figure 7.20: Defects per facility per quarter
21. Figure 7.21: Clustered column chart of production defects
22. Figure 7.22: Clustered column chart with data labels
23. Figure 7.23: A funnel chart
24. Figure 7.24: Sales process data
25. Figure 7.25: Steve's initial funnel chart
26. Figure 7.26: Nonselected data points are lighter.
27. Figure 7.27: Adding color to a funnel chart
28. Figure 7.28: A funnel chart for Steve and one for all salespeople
29. Figure 7.29: An XY chart with random values
30. Figure 7.30: An XY chart (left) compared to a line chart (right)
31. Figure 7.31: Daily temperature and sales data
32. Figure 7.32: An XY chart comparing temperature to sales
33. Figure 7.33: Separated units sold data
34. Figure 7.34: Smoothie type vs. temperature
35. Figure 7.35: A bubble chart
36. Figure 7.36: Home mortgage data
37. Figure 7.37: Add branch names to the data labels.
38. Figure 7.38: Mortgages by branch
39. Figure 7.39: A dot plot chart of bird observations
40. Figure 7.40: Raw data for units produced
41. Figure 7.41: Dummy series for the category axis
42. Figure 7.42: Staging area for X values
43. Figure 7.43: Staging area for Y values
44. Figure 7.44: A column chart with the dummy series
45. Figure 7.45: Adding a new series
46. Figure 7.46: Changing the series chart type
47. Figure 7.47: Setting the X and Y series values
48. Figure 7.48: Setting the X and Y value for the next series
49. Figure 7.49: All series have been added to the chart.
50. Figure 7.50: Adding an axis title
51. Figure 7.51: The formatted chart
52. Figure 7.52: Four charts aligned vertically
53. Figure 7.53: A zoomed-out view of the staging areas
54. Figure 7.54: The final four dot plot charts
Chapter 8
1. Figure 8.1: A pie chart
2. Figure 8.2: Grouping smaller data points
3. Figure 8.3: Placing text inside a doughnut chart
4. Figure 8.4: Sales dollars by region
5. Figure 8.5: The Insert Chart dialog box
6. Figure 8.6: The on-chart Chart Styles button
7. Figure 8.7: Sales by Region doughnut chart
8. Figure 8.8: A waffle chart
9. Figure 8.9: Retail outlets by fuel type
10. Figure 8.10: Employee benefit use data
11. Figure 8.11: The 10x10 grid of formulas
12. Figure 8.12: Set the border color to dark blue.
13. Figure 8.13: Create the conditional formatting rule.
14. Figure 8.14: The conditionally formatted range
15. Figure 8.15: The first two waffle charts
16. Figure 8.16: Employee benefits waffle charts
17. Figure 8.17: A sunburst chart
18. Figure 8.18: A sunburst chart with an incomplete hierarchy
19. Figure 8.19: Data layout for a sunburst chart
20. Figure 8.20: Time-motion study results
21. Figure 8.21: Excel's initial sunburst chart
22. Figure 8.22: A histogram chart
23. Figure 8.23: The Format Axis task pane for a histogram
24. Figure 8.24: A histogram formatted by category
25. Figure 8.25: A partial list of tickets for a month
26. Figure 8.26: Histogram of restaurant tickets
27. Figure 8.27: Custom bins on a histogram
28. Figure 8.28: A treemap chart
29. Figure 8.29: A treemap with an incomplete hierarchy
30. Figure 8.30: Displaying labels with the Banner option
31. Figure 8.31: Insurance policy data by class and type
32. Figure 8.32: Insurance policy breakdown treemap
33. Figure 8.33: A waterfall chart
34. Figure 8.34: A waterfall chart to show the change in sales
35. Figure 8.35: A waterfall chart of a mortgage loan
36. Figure 8.36: Income statement data
37. Figure 8.37: The waterfall chart with totals set
38. Figure 8.38: The final income statement waterfall chart
39. Figure 8.39: The income statement waterfall chart with percentage
Chapter 9
1. Figure 9.1: A line chart
2. Figure 9.2: The Format Data Series task pane for a line chart
3. Figure 9.3: Use transparency to highlight a series.
4. Figure 9.4: A line chart with arrows
5. Figure 9.5: Sales data by product category
6. Figure 9.6: The Insert Line Or Area Chart drop-down
7. Figure 9.7: The initial line chart
8. Figure 9.8: The onscreen dialog
9. Figure 9.9: The completed line chart
10. Figure 9.10: Date staging areas for projected line chart
11. Figure 9.11: The chart's Paste Special dialog
12. Figure 9.12: Newly pasted series on the line chart
13. Figure 9.13: A line chart with projected sales
14. Figure 9.14: A column chart with variances
15. Figure 9.15: House closings by month
16. Figure 9.16: Data staging area for a column chart with variances
17. Figure 9.17: A clustered column chart of house sales
18. Figure 9.18: The Paste Special dialog for charts
19. Figure 9.19: The Change Chart Type dialog
20. Figure 9.20: The variance series added to the column chart
21. Figure 9.21: Error bars on the variance series
22. Figure 9.22: The Custom Error Bars dialog
23. Figure 9.23: The formatted Increase series
24. Figure 9.24: The formatted variance series
25. Figure 9.25: The final column chart with variances
26. Figure 9.26: A column and line combination chart
27. Figure 9.27: Related data at different scales on the same axis
28. Figure 9.28: Freight data by month
29. Figure 9.29: Insert a combination chart
30. Figure 9.30: Deadhead miles added to the data
31. Figure 9.31: The Paste Special dialog
32. Figure 9.32: The completed combination chart
33. Figure 9.33: A Line chart with differences
34. Figure 9.34: Weekly revenue data
35. Figure 9.35: The initial line chart
36. Figure 9.36: The on-chart Chart Elements tool
37. Figure 9.37: The chart with positive differences
38. Figure 9.38: The Custom Error Bars dialog for the Prior series
39. Figure 9.39: The Final Line Chart with Differences
40. Figure 9.40: A side-by-side box plot
41. Figure 9.41: The Format Data Series task pane for a box plot
42. Figure 9.42: Salaries data
43. Figure 9.43: The initial box plot
44. Figure 9.44: Renaming a series
45. Figure 9.45: The final box plot
46. Figure 9.46: A partial data set for a PivotChart
47. Figure 9.47: A PivotChart with no data
48. Figure 9.48: A basic PivotChart
49. Figure 9.49: Field button options
50. Figure 9.50: Changing how dates are grouped in a PivotChart
51. Figure 9.51: Annual revenue data
52. Figure 9.52: The staging area for five years of data
53. Figure 9.53: A dynamic chart changes as the data changes.
54. Figure 9.54: The Format Control dialog for a scroll bar
55. Figure 9.55: A slider that animates a line chart
56. Figure 9.56: A new code window in the Visual Basic Editor
57. Figure 9.57: A line chart with newly pasted series
58. Figure 9.58: Two formatted projection series
59. Figure 9.59: A line chart and panel chart comparison
60. Figure 9.60: A chart with source data
61. Figure 9.61: Eight panel charts
62. Figure 9.62: The final panel chart

Introduction

Businesses are collecting and storing more data than ever before. It's not just very large businesses either. Small and medium-sized businesses have unprecedented access to data and storage. It's management's job to use that data in decision making, but they simply can't consume all of it in its raw form. Business intelligence (BI) is the process of turning raw data into useful information.

BI has been around in some form for a long time. But recently the increase in quality and accessibility of BI tools have increased its popularity. These tools, coupled with a new widespread availability of data, have fueled an environment where it seems that everyone is creating dashboards.

Excel is becoming the standard for BI tools (if it's not already). Microsoft has invested heavily in the BI tools built in to Excel and some that are outside Excel. They have created the PowerBI family of tools (PowerQuery, PowerPivot, and PowerBI) and have added many more chart types than were available just a few versions ago.

What was once highly specialized software soon became a feature in Excel and available to anyone. In the past, you may have needed an IT project to get the data and the tools to create a dashboard. Now, you likely have it all on your computer already. And at the center of those tools is Excel, a program you probably already have regardless of the size of your business.

Maybe you've been wanting to create a dashboard but never thought you had the skills. Or maybe management has asked you to create one. This book will guide you through Excel's data visualization features from shapes to conditional formatting to charts. I include several realistic case studies so you can see how a business question can turn into a chart or dashboard.

What Does This Book Cover?

The chapters in this book are divided into three parts. In Part I, I discuss dashboards as a whole, including three case studies that result in a full dashboard. Part II focuses on how to get the most of out of the individual elements that make up a dashboard and introduces you to some non-chart data visualization elements. In Part III, I discuss individual charts in detail and provide case studies for many different chart types.

Chapter 1: Dashboard Basics This chapter covers the very basics of dashboarding, including when a dashboard is appropriate and the big-picture steps for building and formatting a dashboard.
Chapter 2: Dashboard Case Studies This chapter includes three case studies. Each case study provides background for the business need, the details around the request for a dashboard, and the construction of the dashboard elements.
Chapter 3: Organizing Data for Dashboards This chapter is all about data. It covers best practices for organizing your data into layers. I also discuss several external data sources and how to get them into Excel.
Chapter 4: The Fundamentals of Effective Visualization This chapter is for users who are new to creating visualizations. In it, I cover what makes an effective visualization, how to use elements like color and text, and how to choose a chart type for the data you want to present.
Chapter 5: Non-chart Visualizations Not all dashboard elements are charts. In this chapter, I discuss visualization features in Excel, and dive deeply into custom number formatting.
Chapter 6: Using Shapes to Create Infographics This chapter covers the basics of shapes in Excel. It also covers how you can use shapes to frame your data in interesting ways.
Chapter 7: Visualizing Performance Comparisons This chapter discusses all the chart types that are appropriate for comparing performance data, including case studies for many of the chart types.
Chapter 8: Visualizing Parts of a Whole This chapter includes sections for chart types that you use when you want to tell a story about how component parts make up a whole. It also includes several case studies with step-by-step instructions.
Chapter 9: Visualizing Changes over Time This chapter reviews the chart types for displaying data that changes over time. In addition to the case studies, it includes a section on how to control charts with the Visual Basic for Applications programming language.

Companion Download Files

As you work through the examples in this book, the workbooks and supporting files you need are all available for download from www.wiley.com/go/datavizwithexcel/.

How to Contact the Publisher

If you believe you've found a mistake in this book, please bring it to our attention. At John Wiley & Sons, we understand how important it is to provide our customers with accurate content, but even with our best efforts an error may occur.

In order to submit your possible errata, please email it to our Customer Service Team at wileysupport@wiley.com with the subject line “Possible Book Errata Submission.”

Data Visualization with Excel^® Dashboards and Reports

About the Author

About the Technical Editor

Acknowledgments

Introduction

What Does This Book Cover?

Companion Download Files

How to Contact the Publisher

Part I
Display Data on a Dashboard