Details

Excel Dashboards & Reports For Dummies


Excel Dashboards & Reports For Dummies


4. Aufl.

von: Michael Alexander

25,99 €

Verlag: Wiley
Format: EPUB
Veröffentl.: 28.02.2022
ISBN/EAN: 9781119844419
Sprache: englisch
Anzahl Seiten: 384

DRM-geschütztes eBook, Sie benötigen z.B. Adobe Digital Editions und eine Adobe ID zum Lesen.

Beschreibungen

<p><b>It’s time for some truly “Excel-lent” spreadsheet reporting</b> <p>Beneath the seemingly endless rows and columns of cells, the latest version of Microsoft Excel boasts an astonishing variety of features and capabilities. But how do you go about tapping into some of that power without spending all of your days becoming a spreadsheet guru? <p>It’s easy. You grab a copy of the newest edition of <i>Excel Dashboards & Reports For Dummies</i> and get ready to blow the pants off your next presentation audience! <p>With this book, you’ll learn how to transform those rows and columns of data into dynamic reports, dashboards, and visualizations. You’ll draw powerful new insights from your company’s numbers to share with your colleagues – and seem like the smartest person in the room while you’re doing it. <p><i>Excel Dashboards & Reports For Dummies</i> offers: <ul> <li>Complete coverage of the latest version of Microsoft Excel provided in the Microsoft 365 subscription</li> <li>Strategies to automate your reporting so you don’t have to manually crunch the numbers every week, month, quarter, or year</li> <li>Ways to get new perspectives on old data, visualizing it so you can find solutions no one else has seen before</li> </ul> <p>If you’re ready to make your company’s numbers and spreadsheets dance, it’s time to get the book that’ll have them moving to your tune in no time. Get <i>Excel Dashboards & Reports For Dummies </i>today.
<p><b>Introduction 1</b></p> <p>About This Book 2</p> <p>Foolish Assumptions 4</p> <p>Icons Used in This Book 4</p> <p>Beyond the Book 4</p> <p>Where to Go from Here 5</p> <p><b>Part 1: Getting Started with Excel Dashboards and Reports</b><b> 7</b></p> <p><b>Chapter 1: Getting in the Dashboard State of Mind</b><b> 9</b></p> <p>Defining Dashboards and Reports 10</p> <p>Defining reports 10</p> <p>Defining dashboards 11</p> <p>Preparing for Greatness 12</p> <p>Establish the audience for, and purpose of, the dashboard 12</p> <p>Delineate the measures for the dashboard 13</p> <p>Catalog the required data sources 14</p> <p>Define the dimensions and filters for the dashboard 15</p> <p>Determine the need for drill-down features 16</p> <p>Establish the refresh schedule 16</p> <p>A Quick Look at Dashboard Design Principles 16</p> <p>Rule number 1: Keep it simple 17</p> <p>Use layout and placement to draw focus 19</p> <p>Format numbers effectively 20</p> <p>Use titles and labels effectively 20</p> <p><b>Chapter 2: Building a Super Model</b><b> 23</b></p> <p>Data Modeling Best Practices 24</p> <p>Separating data, analysis, and presentation 24</p> <p>Starting with appropriately structured data 27</p> <p>Avoiding turning your data model into a database 30</p> <p>Using tabs to document and organize your data model 31</p> <p>Testing your data model before building reporting components on top of it 33</p> <p>Excel Functions That Really Deliver 34</p> <p>The VLOOKUP function 34</p> <p>The HLOOKUP function 38</p> <p>The SUMPRODUCT function 40</p> <p>The CHOOSE function 43</p> <p>Using Smart Tables That Expand with Data 45</p> <p>Converting a range to an Excel table 46</p> <p>Converting an Excel table back to a range 49</p> <p>Introducing Dynamic Arrays 49</p> <p>Getting the basics of dynamic arrays 49</p> <p>Understanding spill ranges 51</p> <p>Referencing spill ranges 53</p> <p>Exploring Dynamic Array Functions 54</p> <p>The SORT function 55</p> <p>The SORTBY function 56</p> <p>The UNIQUE function 57</p> <p>The FILTER function 58</p> <p>The XLOOKUP function 61</p> <p><b>Chapter 3: The Pivotal Pivot Table</b><b> 65</b></p> <p>An Introduction to the Pivot Table 65</p> <p>The Four Areas of a Pivot Table 66</p> <p>Values area 66</p> <p>Row area 67</p> <p>Column area 67</p> <p>Filter area 68</p> <p>Creating Your First Pivot Table 69</p> <p>Changing and rearranging your pivot table 72</p> <p>Adding a report filter 73</p> <p>Keeping your pivot table fresh 74</p> <p>Customizing Pivot Table Reports 76</p> <p>Changing the pivot table layout 76</p> <p>Customizing field names 78</p> <p>Applying numeric formats to data fields 79</p> <p>Changing summary calculations 80</p> <p>Suppressing subtotals 81</p> <p>Showing and hiding data items 84</p> <p>Hiding or showing items without data 86</p> <p>Sorting your pivot table 88</p> <p>Creating Useful Pivot-Driven Views 89</p> <p>Producing top and bottom views 89</p> <p>Creating views by month, quarter, and year 93</p> <p>Creating a percent distribution view 95</p> <p>Creating a month-over-month variance view 97</p> <p><b>Chapter 4: Using External Data for Your Dashboards and Reports</b><b> 101</b></p> <p>Leveraging Power Query to Extract and Transform Data 102</p> <p>Reviewing Power Query basics 102</p> <p>Understanding query steps 109</p> <p>Importing Data from Files 111</p> <p>Getting data from Excel workbooks 111</p> <p>Getting data from CSV and text files 113</p> <p>Importing Data from Database Systems 114</p> <p>Importing data from Microsoft Access 114</p> <p>Managing data source settings 116</p> <p><b>Part 2: Building Basic Dashboard Components</b><b> 119</b></p> <p><b>Chapter 5: Dressing Up Your Data Tables</b><b> 121</b></p> <p>Table Design Principles 122</p> <p>Use colors sparingly 122</p> <p>De-emphasize borders 123</p> <p>Use effective number formatting 126</p> <p>Subdue your labels and headers 127</p> <p>Getting Fancy with Custom Number Formatting 129</p> <p>Number formatting basics 129</p> <p>Formatting numbers in thousands and millions 132</p> <p>Hiding and suppressing zeroes 134</p> <p>Applying custom format colors 135</p> <p>Formatting dates and times 136</p> <p><b>Chapter 6: Sparking Inspiration with Sparklines</b><b> 139</b></p> <p>Introducing Sparklines 139</p> <p>Understanding Sparklines 141</p> <p>Creating sparklines 142</p> <p>Understanding sparkline groups 144</p> <p>Customizing Sparklines 145</p> <p>Sizing and merging sparkline cells 145</p> <p>Handling hidden or missing data 146</p> <p>Changing the sparkline type 147</p> <p>Changing sparkline colors and line width 147</p> <p>Using color to emphasize key data points 147</p> <p>Adjusting sparkline axis scaling 148</p> <p>Faking a reference line 149</p> <p>Specifying a date axis 151</p> <p>Autoupdating sparkline ranges 152</p> <p><b>Chapter 7: Formatting Your Way to Visualizations</b><b> 153</b></p> <p>Enhancing Reports with Conditional Formatting 154</p> <p>Applying basic conditional formatting 154</p> <p>Adding your own formatting rules manually 162</p> <p>Showing only one icon 166</p> <p>Showing Data Bars and icons outside of cells 169</p> <p>Representing trends with Icon Sets 171</p> <p>Using Symbols to Enhance Reporting 173</p> <p>Wielding the Magical Camera Tool 176</p> <p>Finding the Camera tool 176</p> <p>Using the Camera tool 177</p> <p>Enhancing a dashboard with the Camera tool 179</p> <p>Enhancing Excel Reports with Shapes 180</p> <p>Creating visually appealing containers with shapes 180</p> <p>Layering shapes to save space 182</p> <p>Constructing your own infographic widgets with shapes 182</p> <p><b>Part 3: Adding Charts to Your Dashboards</b><b> 185</b></p> <p><b>Chapter 8: Charts That Show Trending</b><b> 187</b></p> <p>Trending Dos and Don’ts 188</p> <p>Using chart types appropriate for trending 188</p> <p>Starting the vertical scale at zero 190</p> <p>Leveraging Excel’s logarithmic scale 192</p> <p>Applying creative label management 193</p> <p>Comparative Trending 196</p> <p>Creating side-by-side time comparisons 196</p> <p>Creating stacked time comparisons 198</p> <p>Trending with a secondary axis 199</p> <p>Emphasizing Periods of Time 202</p> <p>Formatting specific periods 202</p> <p>Using dividers to mark significant events 203</p> <p>Representing forecasts in your trending components 204</p> <p>Other Trending Techniques 206</p> <p>Avoiding overload with directional trending 206</p> <p>Smoothing data 207</p> <p><b>Chapter 9: Grouping and Bucketing Data</b><b> 211</b></p> <p>Creating Top and Bottom Displays 211</p> <p>Incorporating top and bottom displays into dashboards 212</p> <p>Using pivot tables to get top and bottom views 213</p> <p>Top Values in Charts 216</p> <p>Using Histograms to Track Relationships and Frequency 220</p> <p>Using Excel’s Histogram statistical chart 220</p> <p>Creating a formula-driven histogram 223</p> <p>Adding a cumulative percent 226</p> <p>Using a pivot table to create a histogram 228</p> <p><b>Chapter 10: Displaying Performance against a Target</b><b> 231</b></p> <p>Showing Performance with Variances 231</p> <p>Showing Performance against Organizational Trends 233</p> <p>Using a Thermometer-Style Chart 234</p> <p>Using a Bullet Graph 235</p> <p>Creating a bullet graph 236</p> <p>Adding data to your bullet graph 239</p> <p>Final thoughts on formatting bullet graphs 241</p> <p>Showing Performance against a Target Range 243</p> <p><b>Part 4: Advanced Reporting Techniques</b><b> 247</b></p> <p><b>Chapter 11: Giving Users an Interactive Interface</b><b> 249</b></p> <p>Introducing Macros 249</p> <p>Why use a macro? 250</p> <p>Recording your first macro 251</p> <p>Running your macros 254</p> <p>Enabling and trusting macros 257</p> <p>Understanding macro-enabled file extensions 258</p> <p>Enabling macro content 258</p> <p>Setting up trusted locations 258</p> <p>Examining some macro examples 259</p> <p>Building navigation buttons 260</p> <p>Dynamically rearranging pivot table data 261</p> <p>Offering one-touch reporting options 262</p> <p>Getting Started with Form Controls 263</p> <p>Finding Form controls 263</p> <p>Adding a control to a worksheet 264</p> <p>Using the Button Control 266</p> <p>Using the Check Box Control 266</p> <p>Toggling a Chart Series On and Off 268</p> <p>Using the Option Button Control 270</p> <p>Showing Many Views through One Chart 272</p> <p>Using the Combo Box Control 274</p> <p>Changing Chart Data with a Drop-Down Selector 275</p> <p>Using the List Box Control 277</p> <p>Controlling Multiple Charts with One Selector 279</p> <p><b>Chapter 12: Adding Interactivity with Pivot Slicers</b><b> 283</b></p> <p>Understanding Slicers 283</p> <p>Creating a Standard Slicer 286</p> <p>Getting Fancy with Slicer Customizations 288</p> <p>Size and placement 288</p> <p>Data item columns 288</p> <p>Other slicer settings 289</p> <p>Creating your own slicer style 289</p> <p>Controlling Multiple Pivot Tables with One Slicer 293</p> <p>Creating a Timeline Slicer 294</p> <p>Using Slicers as Form Controls 296</p> <p>Using Slicers on Excel Table Objects 300</p> <p><b>Chapter 13: Sharing Your Workbook with the Outside World</b><b> 303</b></p> <p>Protecting Your Dashboards and Reports 303</p> <p>Securing access to the entire workbook 304</p> <p>Limiting access to specific worksheet ranges 307</p> <p>Protecting the workbook structure 310</p> <p>Linking Your Excel Dashboards to PowerPoint 311</p> <p>Creating a link between Excel and PowerPoint 312</p> <p>Manually updating links to capture updates 313</p> <p>Turning off automatic updating of links 314</p> <p>Distributing Your Dashboards via a PDF 316</p> <p>Distributing Your Dashboards to OneDrive 318</p> <p>Limitations When Publishing to the Web 321</p> <p><b>Part 5: The Part of Tens</b><b> 323</b></p> <p><b>Chapter 14: Ten Chart Design Principles</b><b> 325</b></p> <p>Avoid Fancy Formatting 325</p> <p>Skip the Unnecessary Chart Junk 327</p> <p>Format Large Numbers Where Possible 329</p> <p>Use Data Tables Instead of Data Labels 330</p> <p>Make Effective Use of Chart Titles 332</p> <p>Sort Your Data before Charting 333</p> <p>Limit the Use of Pie Charts 333</p> <p>Don’t Be Afraid to Parse Data into Separate Charts 334</p> <p>Maintain Appropriate Aspect Ratios 336</p> <p>Don’t Be Afraid to Use Something Other Than a Chart 337</p> <p><b>Chapter 15: Ten Questions to Ask Before Distributing Your Dashboard</b><b> 339</b></p> <p>Does My Dashboard Present the Right Information? 339</p> <p>Does Everything on My Dashboard Have a Purpose? 340</p> <p>Does My Dashboard Prominently Display the Key Message? 340</p> <p>Can I Maintain This Dashboard? 341</p> <p>Does My Dashboard Clearly Display Its Scope and Shelf Life? 341</p> <p>Is My Dashboard Well Documented? 341</p> <p>Is My Dashboard Overwhelmed with Formatting and Graphics? 342</p> <p>Does My Dashboard Overuse Charts When Tables Will Do? 343</p> <p>Is My Dashboard User-Friendly? 343</p> <p>Is My Dashboard Accurate? 344</p> <p>Index 345</p>
<p><B>Michael Alexander </b>is a senior consultant at Slalom Consulting with more than 15 years’ experience in data management and reporting. He is the author of more than a dozen books on business analysis using Microsoft Excel, and has been named Microsoft Excel MVP for his contributions to the Excel community. </p>
<p><b>In go the data, out come valuable insights</b></p> <p>Got data? <i>Excel<sup>®</sup> Dashboards & Reports For Dummies</i> will help you whip it up into information that people can understand—and it’ll look pretty, too. Use your data or someone else’s to craft summaries, visualizations, and charts. We’ll even show you how to make it interactive, so users can update, dice, and slice the data as needed. If you think it’s nice to have a bunch of numbers lying around, wait until you see what you can learn from those numbers with Excel<sup>®</sup>. Dummies will help you take your knowledge beyond the basics. <p><b>Inside…</b> <ul><b><li>The lowdown on dashboard design principles</li> <li>Advanced reporting techniques you should know</li></li> <li>Tips for incorporating external data into your reporting</li> <li>Ways to create interactive reporting with macros and pivot slicers</li></b></ul>

Diese Produkte könnten Sie auch interessieren:

AutoCAD 2013 For Dummies
AutoCAD 2013 For Dummies
von: Bill Fane, David Byrnes
PDF ebook
23,99 €
IBM Workplace Services Express For Dummies
IBM Workplace Services Express For Dummies
von: Stephen R. Londergan, Michael Loria
PDF ebook
16,99 €
Crystal Xcelsius For Dummies
Crystal Xcelsius For Dummies
von: Michael Alexander
PDF ebook
23,99 €