Wiley Excel 2007 Dashboards & Reports For Dummies
Contents
1. 16 9M 21 22 Part I Making the Move to Dashboards Use titles and labels effectively It s common sense but many people often fail to label items on dashboards effectively If your manager looks at your dashboard and asks you What is this telling me you likely have labeling issues Here are a few guidelines for effective labeling in your dashboards and reports 1 Always include a timestamp on your reporting mechanisms This minimizes confusion when distributing the same dashboard or report in monthly or weekly installments Always include some text indicating when the data for the measures was retrieved In many cases timing of the data is a critical piece of information when analyzing a measure YY Use descriptive titles for each component in your dashboard This allows users to clearly identify what they re looking at Be sure to avoid cryptic titles with lots of acronyms and symbols 1 Although it may seem counterintuitive it s generally good practice to de emphasize labels by formatting them to hues lighter than the ones used for your data Lightly colored labels give your users the informa tion they need without distracting them from the information displayed Ideal colors to use for labels are colors that are commonly found in nature soft grays browns blues and greens2. Oct 05 14 6 Sep 06 11 9 Decos 14 5 Octo 11 4 eh ign aim ee Aug 05 14 5 Nov 06 11 3 Jul 05 14 3 Dec 06 11 2 11 2 Part I Making the Move to Dashboards Preparing for Greatness Imagine your manager asks you to create a dashboard that tells him everything he should know about monthly service subscriptions Do you jump to action and slap together whatever comes to mind Do you take a guess at what he wants to see and hope it s useful These questions sound ridiculous but such situations happen more that you think I m constantly called to action to create the next great reporting tool but am rarely provided the time to gather the true requirements for it Between limited information and unrealistic deadlines the end product often ends up being unused or having little value This brings me to one of the key steps in preparing for dashboarding collecting user requirements In the non IT world of the Excel analyst user requirements are practically useless because of sudden changes in project scope constantly changing pri orities and shifting deadlines The gathering of user requirements is viewed to be a lot of work and a waste of valuable time in the ever changing business environment But as I mention at the start of this chapter it s time to get into the dashboard state of mind Consider how many times a manager has asked you for an analysis and then said No I meant this Or Now that I see it I r
3. 16 906 714 you can format the number to read 17M You can easily format large numbers in Excel by using the Format Cells dialog box shown in Figure 1 5 Here you can specify a custom number format by selecting Custom in the Category list and entering the desired number format code in the Type input box In Figure 1 5 the format code 0 M ensures the numbers are formatted to millions with an M appendage Figure 1 5 Select Custom in the Category list and enter a number format code in the Type input box i Chapter 1 Getting in the Dashboard State of Mind Format Cells ax Number Alignment Font Border Fill Protection Category General Sample Number 17M Currency Accounting Type Date ear Time Percentage General A Fraction 0 Scientific 0 00 Text 0 Special 0 00 Custom 0_ 020 0_ Red 0 0 00_ 0 00 0 00_ Red 0 00 0_ 4 440 0_ Red 0 bd Delete Type the number format code using one of the existing codes as a starting point cance Table 1 1 lists some common format codes and how they affect numbers Table 1 1 Number Format Codes Number Format Code How 16 906 714 Would be Displayed 0 16907 0 0 16 907 0 00 16 906 71 0 K 16907K 0 0 K 16 907K 0 00 K 16 906 71K 0 0 K 16 907K 0 17 0 M 17M 0 0 M 16 9M 0 00 M 16 91M 0 0 M
4. A B C D E F G 1 National Park Overnight Visitor Stats Backcountry Concessioner Concessioner 2 Period Campers RV Campers Tent Campers Logding Campgrounds Total 3 Jan 05 2 397 098 4 446 370 3 934 114 3 235 039 838 932 14 851 553 4 Feb 05 2 395 236 4 378 491 4 221 920 3 386 345 814 734 15 196 726 5 Mar 05 2 329 845 4 663 020 4 153 999 3 312 100 650 088 15 109 052 6 Apr 05 2 424 227 4 596 036 3 601 198 3 317 010 644 543 14 583 014 7 May 05 2 579 716 4 232 793 3 747 293 3 486 041 725 979 14 771 822 8 Jun 05 1 978 867 3 943 183 3 586 062 3 527 045 733 121 13 768 278 9 Jul 05 1 680 414 3 759 296 3 460 005 3 538 176 743 256 13 181 145 10 Sep 05 1 644 691 3 788 528 3 955 795 3 726 504 786 102 13 901 620 11 Oct 05 1 574 706 4 043 206 3 921 104 3 787 463 852 820 14 179 299 12 Nov 05 1 617 706 3 937 271 3 929 665 3 880 622 865 694 14 230 958 49 nar l a raneaa a a4 aa anr ann ras aa arn anal Chapter 1 Getting in the Dashboard State of Mind e Figure 1 2 Dashboards provide at a glance views into key measures relevant to a particular objective or business process ee Defining dashboards A dashboard is a visual interface that provides at a glance views into key measures relevant to a particular objective or business process Dashboards have three main attributes Dashboards are typically graphical in nature providing visualizations that help focu
5. Chapter 1 Getting in the Dashboard State of Mind In This Chapter Comparing dashboards to reports Getting started on the right foot Dashboarding best practices his song New York State of Mind Billy Joel laments the differences between California and New York In this homage to the Big Apple he implies a mood and a feeling that comes with thinking about New York I admit it s a stretch but I ll to extend this analogy to Excel don t laugh In Excel the differences between building a dashboard and creating standard table driven analyses are as great as the differences between California and New York To approach a dashboarding project you truly have to get into the dashboard state of mind As you ll come to realize in the next few chapters dashboarding requires far more preparation than standard Excel analyses It calls for closer communication with business leaders stricter data modeling techniques and the following of certain best practices It s beneficial to have a base familiarity with fundamental dashboarding concepts before venturing off into the mechanics of building a dashboard In this chapter you get a solid understanding of these basic dashboard con cepts and design principles as well as what it takes to prepare for a dash boarding project 10 Part I Making the Move to Dashboards Defining Dashboards and Reports Saat Figure 1 1 Reports present data for viewing but don t lead
6. I discuss earlier in this chapter only measures that support the dash board s utility and purpose should be included in the dashboard However it should be said that just because all measures on your dashboard are signifi cant they may not always have the same level of importance In other words you ll frequently want one component of your dashboard to stand out from the others Instead of using bright colors or exaggerated sizing differences you can lever age location and placement to draw focus to the most important components on your dashboard Various studies have shown that readers have a natural tendency to focus on particular regions of a document For example researchers at the Poynter Institute s Eyetracker III project have found that readers view various regions on a screen in a certain order paying particular attention to specific regions on the screen They use the diagram in Figure 1 4 to illustrate what they call priority zones Regions with the number 1 in the diagram seem to have high prominence attracting the most attention for longer periods of time Meanwhile priority 3 regions seem to have low prominence 20 Part I Making the Move to Dashboards ar You can leverage these priority zones to promote or demote certain compo nents based on significance If one of the charts on your dashboard warrants special focus you can simply place that chart in a region of prominence Note that surro
7. d design Your typical Excel analyst has no background in visual design and is often left to rely on his own visual instincts to design his dashboards As a result most Excel based dashboards have little thought given to effective visual design often resulting in overly cluttered and ineffective user interfaces Chapter 1 Getting in the Dashboard State of Mind 7 air The good news is that dashboarding has been around for such a long time there s a vast knowledge base of prescribed visualization and dashboard design principles Many of these principles seem like common sense even so these are concepts that Excel users don t often find themselves thinking about Because this chapter is about getting into the dashboard state of mind I break that trend and review a few dashboard design principles that improve the design aspect of your Excel dashboards Many of the concepts in this section come from the work of Stephen Few visualization expert and author of several books and articles on dashboard design principles As this book is primarily focused on the technical aspects of building reporting components in Excel this section offers a high level look at dashboard design If you find that you re captivated by the subject feel free to visit Stephen Few s Web site at www perceptualedge com Rule number 1 Keep it simple Dashboard design expert Stephen Few has the mantra Simplify Simplify Simplify The basic idea is
8. ealize I need this As frustrating as that can be for a single analysis imagine running into this during the cre ation of a complex dashboard with several data integration processes The question is would you rather spend your time on the front end gathering user requirements or spend time painstakingly redesigning the dashboard you ll surely come to hate The process of gathering user requirements doesn t have to be an overly complicated or formal one Here are some simple things you can do to ensure you have a solid idea of the purpose of the dashboard Establish the audience and purpose for the dashboard Chances are your manager has been asked to create the reporting mechanism and he has passed the task to you Don t be afraid to clarify the source of the initial request and talk to them about what they re really asking for Discuss the purpose of the dashboard and the triggers that caused them to ask for a dash board in the first place You may find after discussing the matter that a simple Excel report meets their needs foregoing the need for a full on dashboard If a dashboard is indeed warranted talk about who the end users are Take some time to meet with some of the end users and talk about how they d use the dashboard Will the dashboard be used as a performance tool for regional Chapter 1 Getting in the Dashboard State of Mind 13 MBER et amp managers Will the dashboard be used to share data with exter
9. entation they often just look tacky Limit each dashboard to one printable page Dashboards in general should provide at a glance views into key measures relevant to particular objectives or business processes This implies that all the data is immediately viewable on the one page Although including all your data on one page isn t always the easiest thing to do there s much benefit to being able to see everything on one page or screen You can compare sections more easily you can process cause and effect relationships more effectively and you rely less on short term memory When a user has to scroll left right or down these benefits are diminished Furthermore users tend to believe that when information is placed out of normal view areas that require scrolling it s somehow less important But what if you can t fit all the data on one sheet First review the measures on your dashboard and determine if they really need to be there Next format your dashboard to use less space format fonts reduce white space and adjust column and row widths Finally try adding interactivity to your dash board allowing users to dynamically change views to show only those measures that are relevant to them Chapter 1 Getting in the Dashboard State of Mind g Figure 1 4 Studies show that users pay particular attention to the upper left and middle left ofa document E Use layout and placement to draw focus As
10. hat is the quarterly trend for Tent trend for RV Campers Camper Which are the Which are the top 10 months bottom 10 months What is the quarterly What is the quarterly trend for Backcountry trend for Concessioner Campers Lodging What is the variance What is last between What is the P year s average current month s average revenue revenue by revenue by last year service s service and the offering offereing current month s revenue Each box in this dashboard layout mockup represents a component on the dashboard and its approximate position The questions within each box provide a sense of the types of data required to create the measures for the dashboard Catalog the required data sources When you have the list of measures that need to be included on the dash board it s important to take a tally of the available systems to determine if the data required to produce those measures are available Ask yourself the following questions 1 Do you have access to the data sources necessary 1 How often are those data sources refreshed 1 Who owns and maintains those data sources 1 What are the processes to get the data from those resources Y Does the data even exist Chapter 1 Getting in the Dashboard State of Mind 15 air These are all questions you need answered when negotiating development time refresh intervals and phasing Conventional wisdom says that the mea
11. ing the Move to Dashboards or formatting You ll find that the lack of fancy formatting only serves to call attention to the actual data Focus on the data and not the shiny happy graphics Here are a few guidelines y Avoid using colors or background fills to partition your dashboards Colors in general should be used sparingly reserved for providing infor mation about key data points For example assigning the colors red yellow and green to measures traditionally indicates performance level Adding these colors to other sections of your dashboard only serves to distract your audience 1 De emphasize borders backgrounds and other elements that define dashboard areas Try to use the natural white space between your com ponents to partition your dashboard If borders are necessary format them to hues lighter than the ones you ve used for your data Light grays are typically ideal for borders The idea is to indicate sections without distracting from the information displayed y Avoid applying fancy effects such as gradients pattern fills shadows glows soft edges and other formatting Excel 2007 makes it easy to apply effects that make everything look shiny glittery and generally happy Although these formatting features make for great marketing tools they don t do your reporting mechanisms any favors Y Don t try to enhance your dashboards with clip art or pictures Not only do they do nothing to further data pres
12. ion of nice to know data into your dashboards simply to fill white space or because the data is available If the data doesn t support the core purpose of the dashboard leave it out Here s another tip When gathering the measures required for the dashboard I find that it often helps to write a sentence to describe the measure needed For example instead of simply adding the word Revenue into my user require ments I write what I call a component question such as What is the overall revenue trend for the last two years I call it a component question because I intend to create a single component such as a chart or a table to answer the question For instance if the component question is What is the overall revenue trend for the last two years you can imagine a chart component answering that question by showing the two year revenue trend I sometimes take this a step further and actually incorporate the component questions into a mock layout of the dashboard to get a high level sense of the data the dashboard will require Figure 1 3 illustrates an example Part I Making the Move to Dashboards Sa Figure 1 3 Each box in this dashboard layout mockup represents a component and the type of data required to create the measures aaa What is the overall trend for the last two years What is the breakout of service offering What is the quarterly W
13. nal customers Talking through these fundamentals with the right people helps align your thoughts and avoids the creation of a dashboard that doesn t fulfill the necessary requirements Delineate the measures for the dashboard Most dashboards are designed around a set of measures or key performance indicators KPIs A KPI is an indicator of the performance of a task deemed to be essential to daily operations or processes The idea is that a KPI reveals performance that is outside the normal range for a particular measure so it therefore often signals the need for attention and intervention Although the measures you place into your dashboards may not officially be called KPIs they undoubtedly serve the same purpose to draw attention to problem areas The topic of creating effective KPIs for your organization is a subject worthy of its own book and is out of the scope of this endeavor For a detailed guide on KPI development strategies pick up David Parmenter s Key Performance Indicators Developing Implementing and Using Winning KPIs Wiley This book provides an excellent step by step approach to developing and implementing KPIs The measures used on a dashboard should absolutely support the initial pur pose of that dashboard For example if you re creating a dashboard focused on supply chain processes it may not make sense to have human resources headcount data incorporated It s generally a good practice to avoid inclus
14. process definitely saves you headaches Along those same lines you want to get a clear sense of the types of filters that are required In the context of dashboards filters are mechanisms that allow you to narrow the scope of the data to a single dimension For example you can filter on Year Employee or Region Again if you don t account for a particular filter while building your dashboarding process you ll likely be forced into an unpleasant redesign of both your data collection processes and your dashboard If you re confused by the difference between dimensions and fields think about a simple Excel table A dimension is like a column of data such as a column containing employee names in an Excel table A filter then is the mechanism that allows you to narrow your table to show only the data for a particular employee For example if you apply Excel s AutoFilter to the employee column you are building a filter mechanism into your table 6 Part I Making the Move to Dashboards Determine the need for drill down features Many dashboards provide drill down features that allow users to drill into the details of a specific measure You want to get a clear understanding of the types of drill downs your users have in mind To most users drill down feature means the ability to get a raw data table supporting the measures shown on the dashboard Although getting raw data isn t always practical or possible discussing
15. readers to conclusions A It isn t difficult to use report and dashboard interchangeably In fact the line between reports and dashboards frequently gets muddied I ve seen count less reports that have been referred to as dashboards just because they included a few charts Likewise I ve seen many examples of what could be considered dashboards but have been called reports Now this may all seem like semantics to you but it s helpful to clear the air a bit and understand the core attributes of what are considered to be reports and dashboards Defining reports Reports are probably the most common application of business intelligence A report can be described as a document that contains data used for reading or viewing It can be as simple as a data table or as complex as a subtotaled view with interactive drilling similar to Excel s Subtotal functionality The key attribute of a report is that it doesn t lead a reader to a predefined conclusion Although a report can include analysis aggregations and even charts reports often allow for the end user to apply his own judgment and analysis to the data To clarify this concept Figure 1 1 shows an example of a report This report shows the National Park overnight visitor statistics by period Although this data can be useful it s clear this report isn t steering the reader in any predefined judgment or analysis it s simply presenting the aggregated data
16. s attention on key trends comparisons and exceptions 1 Dashboards often display only data that are relevant to the goal of the dashboard Because dashboards are designed with a specific purpose or goal they inherently contain predefined conclusions that relieve the end user from performing his own analysis Figure 1 2 illustrates a dashboard that uses the same data shown in Figure 1 1 This dashboard displays key information about the National Park overnight visitor stats As you can see this presentation has all the main attributes that define a dashboard First it s a visual display that allows you to quickly recognize the overall trending of the overnight visitor stats Second you can see that not all the detailed data is shown here only the key pieces of information that s relevant to support the goal of this dashboard Finally by virtue of its objective this dashboard effectively presents you with analysis and conclusions about the trending of overnight visitors Onsite Overnight Facility Utilization 27 1 Tent Campers 26 7 Concessioner Logding Yeacover Weer be ileal PPR Q Variance Variance pe a a ae ford ford a ae ad 24 7 RV Campers 14 8 Backcountry Campers Dec05 Apr 05 2 fear a a a a a a ae oe mifore mifore Oct 04 15 2 Mar 06 13 0 Nov 04 15 1 Apr 06 13 0 6 6 concessioner Sep 05 149 May 06 128 Camperounds Sep 04 14 9 Jun 06 12 3 Jan 05 14 8 Jul 06 12 3 Nov 05 147 Aug 06 122
17. sures on your dashboard shouldn t be governed by the availability of data Instead you should let dashboard KPIs and measures govern the data sources in your organization Although I agree with the spirit of that statement I ve been involved in too many dashboard projects that have fallen apart because of lack of data Real world experience has taught me the difference between the ideal and the ordeal If your organizational strategy requires that you collect and measure data that is nonexistent or not available press pause on the dashboard project and turn your attention to creating a data collection mechanism that will get the data you need Define the dimensions and filters for the dashboard In the context of reporting a dimension is a data category used to organize business data Examples of dimensions are Region Market Branch Manager or Employee When you define a dimension in the user requirements stage of development you re determining how the measures should be grouped or distributed For example if it s determined that your dashboard should report data by employee you need to ensure that your data collection and aggregation processes include employee detail As you can imagine adding a new dimension after the dashboard is built can get complicated especially when your processes require many aggregations across multiple data sources The bottom line is that locking down the dimensions for a dash board early in the
18. that dashboards cluttered with too many measures or too much eye candy can dilute the significant information you re trying to present How many times has someone told you that your reports look busy In essence this complaint means that too much is going on in the page or screen making it hard to see the actual data Here are a few actions you can take to ensure simpler and more effective dashboard designs Don t turn your dashboard into a data repository Admit it You include as much information onto a report as possible primarily to avoid being asked for additional information We all do it But in the dash board state of mind you have to fight the urge to force every piece of data available onto your dashboards Overwhelming users with too much data can cause them to lose sight of the primary goal of the dashboard and focus on inconsequential data The measures used on a dashboard should support the initial purpose of that dashboard Avoid the urge to fill white space for the sake of symmetry and appearances Don t include nice to know data just because the data is available If the data doesn t support the core purpose of the dashboard leave it out Avoid the fancy formatting The key to communicating effectively with your dashboards is to present your data as simply as possible There s no need to wrap it in eye candy to make it more interesting It s okay to have a dashboard with little to no color 18 Part I Mak
19. these requests will at a mini mum allow you to talk to your users about additional reporting links to other data sources and other solutions that may help them get the data they need Establish the refresh schedule A refresh schedule refers to the schedule by which a dashboard is updated to show the latest information available Because you re the one responsible for building and maintaining the dashboard you should have a say in the refresh schedules Your manager may not know what it takes to refresh the dashboard in question While you re determining the refresh schedule keep in mind the refresh rates of the different data sources whose measures you need to get You can t refresh your dashboard any faster than your data sources Also negotiate enough development time to build macros that aid in automation of redundant and time consuming refresh tasks A Quick Look at Dashboard Design Principles When collecting user requirements for your dashboarding project there s a heavy focus on the data aspects of the dashboard The types of data needed the dimensions of data required the data sources to be used and so on This is a good thing without solid data processes your dashboards won t be effective or maintainable That being said here s another aspect to your dashboarding project that calls for the same fervor in preparation the design aspect Excel users live in a world of numbers and tables not visualization an
20. unding colors borders fonts and other formatting can affect the viewing patterns of your readers de emphasizing a previously high prominence region Format numbers effectively There will undoubtedly be lots of numbers in your dashboards Some of them will be in charts and others will be in tables Remember that every piece of information on your dashboard should have a reason for being there It s important that you format your numbers effectively to allow your users to understand the information they represent without confusion or hindrance Here are some guidelines to keep in mind when formatting the numbers in your dashboards and reports Always use commas to make numbers easier to read For example instead of 2345 show 2 345 Only use decimal places if that level of precision is required For instance there s rarely benefit for showing the decimal places in a dollar amount such as 123 45 Likewise in percentages use only the minimum number of decimals required to represent the data effectively For example instead of 43 21 you may be able to get away with 43 YY Only use the dollar symbol when you need to clarify that you re referring to monetary values If you have a chart or table that contains all revenue values and there s a label clearly stating this you can save rooms and pixels by leaving out the dollar symbol Format very large numbers to the thousands or millions place For instance instead of displaying
Download Pdf Manuals
Related Search