Data Visualization with Excel Dashboards and Reports

Häftad, Engelska, 2021

389 kr

Beställningsvara. Skickas inom 5-8 vardagar. Fri frakt för medlemmar vid köp för minst 249 kr.

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 wayGet better visibility into data from different perspectivesQuickly slice data into various views on the flyAutomate redundant reporting and analysesCreate impressive dashboards and What-If analysesUnderstand the fundamentals of effective visualizationVisualize performance comparisonsVisualize changes and trends over time

Produktinformation

Utgivningsdatum2021-04-01
Mått185 x 229 x 23 mm
Vikt590 g
FormatHäftad
SpråkEngelska
Antal sidor352
FörlagJohn Wiley & Sons Inc
ISBN9781119698722

Tillhör följande kategorier

Matematik inom Naturvetenskap och teknik

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