Large corporations like IBM and Oracle are using Excel dashboards and reports as a Business Intelligence tool, and many other smaller businesses are looking to these tools in order to cut costs for budgetary reasons. An effective analyst not only has to have the technical skills to use Excel in a productive manner but must be able to synthesize data into a story, and then present that story in the most impactful way. Microsoft shows its recognition of this with Excel.  In Excel, there is a major focus on business intelligence and visualization.  Data Visualization with Excel Dashboards and Reports fills the gap between handling data and synthesizing data into meaningful reports.  This title will show readers how to think about their data in ways other than columns and rows. Most Excel books do a nice job discussing the individual functions and tools that can be used to create an "Excel Report".  Titles on Excel charts, Excel pivot tables, and other books that focus on "Tips and Tricks" are useful in their own right; however they don't hit the mark for most data analysts.  The primary reason these titles miss the mark is they are too focused on the mechanical aspects of building a chart, creating a pivot table, or other functionality.  They don't offer these topics in the broader picture by showing how to present and report data in the most effective way. What are the most meaningful ways to show trending?  How do you show relationships in data?  When is showing variances more valuable than showing actual data values?   How do you deal with outliers?  How do you bucket data in the most meaningful way?  How do you show impossible amounts of data without inundating your audience?  In Data Visualization with Excel Reports and Dashboards, readers will get answers to all of these questions. Part technical manual, part analytical guidebook; this title will help Excel users go from reporting data with simple tables full of dull numbers, to creating hi-impact reports and dashboards that will wow management both visually and substantively. This book offers a comprehensive review of a wide array of technical and analytical concepts that will help users create meaningful reports and dashboards. After reading this book, the reader will be able to: Analyze large amounts of data and report their data in a meaningful way Get better visibility into data from different perspectives Quickly slice data into various views on the fly Automate redundant reporting and analyses Create impressive dashboards and What-If analyses Understand the fundamentals of effective visualization Visualize performance comparisons Visualize changes and trends over time

Table of Contents:
Introduction xxi Part I Display Data on a Dashboard 1 Chapter 1 Dashboard Basics 3 Determining When to Use a Dashboard 3 What Is a Dashboard? 5 Key Performance Indicators 6 Establishing User Requirements 6 Types of End Users 7 Assembling the Data 8 PivotTables 8 The GETPIVOTDATA Worksheet Function 13 Worksheet Functions 14 The VLOOKUP Function 14 The XLOOKUP Function 15 The INDEX and MATCH Functions 16 The SUMPRODUCT Function 17 Array Formulas 19 Tables 20 Structured Table Referencing 23 Text to Columns 24 Removing Duplicates 26 Building the Dashboard 28 Organizing Elements 28 Varying Elements 30 Showing Trends 31 Formatting the Dashboard 33 Number Formats 36 Chapter 2 Dashboard Case Studies 39 Monitoring Progress 39 Case Study: Monitoring a Software Project 40 Planning and Layout 40 Collecting the Data 42 Building the Visual Elements 43 Laying Out the Dashboard 54 Displaying Key Performance Indicators 55 Case Study: Human Resources KPIs 55 Planning and Layout 56 Collecting the Data 57 Building the Visual Elements 58 Laying Out the Dashboard 69 Reporting Financial Information 72 Case Study: Financial Information and Ratios 72 Planning and Layout 72 Collecting the Data 73 Building the Visual Elements 75 Laying Out the Dashboard 83 Chapter 3 Organizing Data for Dashboards 87 Separating Data Layers 87 Source Data Layer 89 Staging and Analysis Layer 90 Presentation Layer 91 Working with External Data 92 Power Query vs. Power Pivot 92 Text Files 92 Excel Files 98 Access Databases 105 SQL Server Databases 111 Transforming Data in Power Query 114 Managing Columns and Rows 116 Transforming Columns 119 Transforming Data Types 119 Transforming Numbers 121 Splitting Columns 123 Part II Visualization Primer 127 Chapter 4 The Fundamentals of Eff ective Visualization 129 Creating an Effective Visualization 129 Keep It to a Single Screen 130 Make It Attractive 131 Tell the Story Quickly 131 Make the Story Consistent with the Data 133 Choose the Proper Chart 135 Driving Meaning with Color 137 How to Use Color 137 Varying Color as Data Values Vary 137 Using Sharp Contrast to Highlight Data 138 Grouping Data with Color 139 Tips on Color Use 140 Use White Space 140 Use a Simple Color Pallet 141 Use Colors That Are Consistent with the Data 141 Use Enough Contrast 141 Use Non-data Pixels When Necessary 142 Focusing Attention on Text 142 Fonts 142 Legends 143 Axes 144 Data Labels 145 Showing Insights with Charts 146 Comparisons 146 Compositions 147 Relationships 149 Chapter 5 Non-chart Visualizations 151 Understanding Custom Number Formats 151 The Four Sections of a Format 152 Special Characters 153 Digit Placeholders 153 Commas and Periods 154 Text 154 Underscore 155 Asterisk 156 Escaping Special Characters 156 The Accounting Number Format 156 Date and Time Formats 158 Conditional Custom Number Formats 159 Using Icons 160 Color Scales 160 Data Bars 165 Icon Sets 167 Creating Sparklines 170 Types of Sparklines 170 Creating a Sparkline 171 Sparkline Groups 172 Customize a Sparkline 172 Changing the Source Data 173 Changing the Color and Thickness 174 Adjusting the Axis 175 Chapter 6 Using Shapes to Create Infographics 179 Working with Shapes 179 Inserting Shapes 180 Customizing Shapes 182 Framing Data with Shapes 185 Creating a Banner 186 Creating a Binder Tab 188 Working with Multiple Shapes 191 Creating Simple Charts with Shapes 193 Creating Custom Infographics 195 Adding Other Illustrations 196 Part III Tell a Story with Visualization 203 Chapter 7 Visualizing Performance Comparisons 205 Single Measurements 206 Column Charts 207 Case Study: Sales by Quarter 210 Bullet Charts 212 Case Study: Expenses vs. Budget 212 Clustered Column Charts 216 Case Study: Production Defects 217 Funnel Charts 218 Case Study: Sales Conversion 219 XY Charts 221 Case Study: Temperature vs. Sales 222 Bubble Charts 225 Case Study: Home Mortgages 226 Dot Plot Charts 228 Case Study: Production Output 229 Chapter 8 Visualizing Parts of a Whole 239 Pie Charts 239 Doughnut Charts 241 Case Study: Sales by Region 242 Waffle Charts 244 Case Study: Employee Participation by Benefit 245 Sunburst Charts 249 Case Study: Manufacturing Process Time Study 250 Histograms 252 Case Study: Restaurant Ticket Totals 254 Treemap Charts 256 Case Study: Insurance Policy Averages 257 Waterfall Charts 259 Case Study: Net Income 261 Chapter 9 Visualizing Changes Over Time 265 Line Charts 266 Case Study: Sales by Product Category 268 Column Charts with Variances 273 Case Study: Houses Sold by Month 274 Combination Charts 280 Case Study: Freight Revenue vs. Miles 281 Line Charts with Differences 284 Case Study: Current vs. Prior Quarter Revenue 285 Side-by-Side Box Plots 288 Case Study: Salaries by Department 290 Animated Charts 292 PivotCharts 293 Staging Area Formulas 295 Chart Animation Macros 299 Chart Automation 302 Manipulating Chart Objects 302 Creating Panel Charts 307 Index 317