microsoft EXCEL DASHBOARDS & REPORTS FOR DUMMIES

Description

Title Page Copyright Page Table of Contents Introduction About This Book Foolish Assumptions Icons Used in This Book Beyond the Book Where to Go from Here Part 1 Getting Started with Excel Dashboards and Reports Chapter 1 Getting in the Dashboard State of Mind Defining Dashboards and Reports Defining reports Defining dashboards Preparing for Greatness Establish the audience for, and purpose of, the dashboard Delineate the measures for the dashboard Catalog the required data sources Define the dimensions and filters for the dashboard Determine the need for drill-down features Establish the refresh 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 Chapter 2 Building a Super Model Data Modeling Best Practices Separating data, analysis, and presentation Starting with appropriately structured data Avoiding turning your data model into a database Using tabs to document and organize your data model Testing your data model before building reporting components on top of it Excel Functions That Really Deliver The VLOOKUP function The HLOOKUP function The SUMPRODUCT function The CHOOSE function Using Smart Tables That Expand with Data Converting a range to an Excel table Converting an Excel table back to a range Introducing Dynamic Arrays Getting the basics of dynamic arrays Understanding spill ranges Referencing spill ranges Exploring Dynamic Array Functions The SORT function The SORTBY function The UNIQUE function The FILTER function The XLOOKUP function Chapter 3 The Pivotal Pivot Table An Introduction to the Pivot Table The Four Areas of a Pivot Table Values area Row area Column area Filter area Creating Your First Pivot Table Changing and rearranging your pivot table Adding a report filter Keeping your pivot table fresh Customizing Pivot Table Reports Changing the pivot table layout Customizing field names Applying numeric formats to data fields Changing summary calculations Suppressing subtotals Showing and hiding data items Hiding or showing items without data Sorting your pivot table Creating Useful Pivot-Driven Views Producing top and bottom views Creating views by month, quarter, and year Creating a percent distribution view Creating a month-over-month variance view Chapter 4 Using External Data for Your Dashboards and Reports Leveraging Power Query to Extract and Transform Data Reviewing Power Query basics Understanding query steps Importing Data from Files Getting data from Excel workbooks Getting data from CSV and text files Importing Data from Database Systems Importing data from Microsoft Access Managing data source settings Part 2 Building Basic Dashboard Components Chapter 5 Dressing Up Your Data Tables Table Design Principles Use colors sparingly De-emphasize borders Use effective number formatting Subdue your labels and headers Getting Fancy with Custom Number Formatting Number formatting basics Formatting numbers in thousands and millions Hiding and suppressing zeroes Applying custom format colors Formatting dates and times Chapter 6 Sparking Inspiration with Sparklines Introducing Sparklines Understanding Sparklines Creating sparklines Understanding sparkline groups 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 Autoupdating sparkline ranges Chapter 7 Formatting Your Way to Visualizations Enhancing Reports with Conditional Formatting Applying basic conditional formatting Adding your own formatting rules manually Showing only one icon Showing Data Bars and icons outside of cells Representing trends with Icon Sets Using Symbols to Enhance Reporting Wielding the Magical Camera Tool Finding the Camera tool Using the Camera tool Enhancing a dashboard with the Camera tool Enhancing Excel Reports with Shapes Creating visually appealing containers with shapes Layering shapes to save space Constructing your own infographic widgets with shapes Part 3 Adding Charts to Your Dashboards Chapter 8 Charts 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 Grouping and Bucketing Data Creating Top and Bottom Displays Incorporating top and bottom displays into dashboards Using pivot tables to get top and bottom views Top Values in Charts Using Histograms to Track Relationships and Frequency Using Excel’s Histogram statistical chart Creating a formula-driven histogram Adding a cumulative percent Using a pivot table to create a histogram Chapter 10 Displaying 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 Part 4 Advanced Reporting Techniques Chapter 11 Giving Users an Interactive Interface Introducing Macros Why use a macro? Recording your first macro Running your macros Enabling and trusting macros Understanding macro-enabled file extensions Enabling macro content Setting up trusted locations Examining some macro examples Building navigation buttons Dynamically rearranging pivot table data Offering one-touch reporting options Getting Started with Form Controls Finding Form controls Adding a control to a worksheet Using the Button Control Using the Check Box Control Toggling a Chart Series On and Off Using the Option Button Control Showing Many Views through One Chart Using the Combo Box Control Changing Chart Data with a Drop-Down Selector Using the List Box Control Controlling Multiple Charts with One Selector Chapter 12 Adding Interactivity with Pivot Slicers Understanding Slicers Creating a Standard Slicer Getting Fancy with Slicer Customizations Size and placement Data item columns Other slicer settings Creating your own slicer style Controlling Multiple Pivot Tables with One Slicer Creating a Timeline Slicer Using Slicers as Form Controls Using Slicers on Excel Table Objects Chapter 13 Sharing Your Workbook with the Outside World Protecting 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 a link between Excel and PowerPoint Manually updating links to capture updates Turning off automatic updating of links Distributing Your Dashboards via a PDF Distributing Your Dashboards to OneDrive Limitations When Publishing to the Web Part 5 The Part of Tens Chapter 14 Ten Chart Design Principles Avoid Fancy Formatting Skip the Unnecessary Chart Junk Format Large Numbers Where Possible Use Data Tables Instead of Data Labels Make Effective Use of Chart Titles Sort Your Data before Charting Limit the Use of Pie Charts Don’t Be Afraid to Parse Data into Separate Charts Maintain Appropriate Aspect Ratios Don’t Be Afraid to Use Something Other Than a Chart Chapter 15 Ten 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 Overwhelmed with Formatting and Graphics? Does My Dashboard Overuse Charts When Tables Will Do? Is My Dashboard User-Friendly? Is My Dashboard Accurate? Index EULA