Category: excel Dashboards

Excel Dashboards – Build a Dashboard ”; Previous Next In the previous chapters, you have learnt about various Excel features that come handy in setting up your dashboard. In this chapter, you will learn how to build a dashboard, i.e., the steps that are required to get the dashboard in place. You will also get to know the dos and don’ts regarding dashboards. As any dashboard is based on the specific intent of what the audience is most interested about, dashboard components and dashboard layout varies from case to case. Initial Preparation The first step in building a dashboard is the initial preparation. Take some time in understanding the following − Why do you need the dashboard? − Is this dashboard for a specific task, like showing the status of a project, or does it need to achieve a broader goal, like measuring business performance? Understanding why you are building the dashboard will guide you in the design. What purpose the dashboard will serve?− Your dashboard should highlight only the data that adds value. You should understand the data that is required. Anything outside that is unnecessary. What is the source of data? − You should understand from where the data comes. It can just be an Excel worksheet or it can be through data connections to your Excel workbook from various dynamic data sources. Who is the audience for the dashboard? − Is this for a manager, an executive, a stakeholder, an external vendor or general audience? Understand their requirements and preferences such as how much time do they have to look at the dashboard, the level of detail they expect and how they would like to digest the information. For example, while choosing a chart type, knowing the audience helps you to decide on whether you have to show relations between values or you have to draw a specific comparison. Does the dashboard need to be static or dynamic? − Can the dashboard be updated periodically, say, weekly or monthly, or does it require to get updated to continuously streamline the data changes that happen at the backend? This choice will change the way you build the dashboard. Does the dashboard need to be just a display or is it to be interactive? − Can the dashboard have read-only access or do you have to provide interactive controls / features that enable certain people to explore the data as required? This choice also will change the way you build the dashboard. Once you have answers to these questions, decide on what Excel features you need and you don’t need. This is because your goal and expertise is to produce an effective dashboard that suits the purpose. Next, identify the components of the dashboard. These can be text, tables, charts, interactive controls, etc. Decide on the dashboard layout with these components. Mockup your Excel dashboard on a PowerPoint slide. Draw boxes for each component to get a sense of the layout and add quick sketches of the components that you want to include. You can also do this on a piece of paper. Get approval for this mockup from the management and/or the key audience before you start working on the actual dashboard. This will save time on rework. However, it is quite possible that you might have to tweak in some changes to the dashboard as the dashboard gets into usage and you receive feedback. But, the approved dashboard mockup is a real good starting for your work. Organize the Data Source for the Excel Dashboard Before building the dashboard in Excel, you need to organize the data source. In Excel, this is possible in various ways − If the data is just an Excel table, establish a link to your workbook from the workbook where the data will get updated. If the data is from multiple Excel tables, or if it is from various data sources, it is a good option to build the Data Model in your workbook. You can either import the data into the workbook periodically or establish data connections so as to refresh the data as when it gets updated, based on whether the dashboard has to be static or dynamic. Set Up the Excel Dashboard Workbook Once you have organized the data, you need to structure your workbook. Insert two to three worksheets in the workbook − one worksheet for your dashboard and one or two worksheets for the data (data or PivotTable/PivotCharts or Power View Reports, which you can hide). This will help you to organize and maintain your Excel workbook. Prepare the Data for the Excel Dashboard Based on the choices you have, i.e. the answers you have for the questions in the Initial Preparation step, prepare the data for the Excel dashboard. The data can be any of the following − Results from data analysis Results from data exploration Data resulting from computations on the input data Data summarization from PivotTables or PowerPivot Tables Select the Dashboard Components You have learnt about the various Excel features that you can use in a dashboard. Based on your requirements for the dashboard at hand, select any of the following Excel features for the dashboard components. Tables Sparklines Conditional Formatting. Charts Slicers Interactive Controls PivotTables PivotCharts PowerPivot Tables PowerPivot Charts Power View Reports KPIs Selecting the dashboard components will help you to align to your approved dashboard mockup layout. Identify the static and dynamic components and the components that are to be grouped for Slicers, if any. Identify Parts of the Dashboard for Highlighting Identify those parts of the dashboard that require immediate attention, such % Complete or the Current Status. You can use bigger font and a striking font and font color for these. Decide on how much color you want to incorporate in your dashboard. This decision can be based on the audience for the dashboard. If the dashboard is for executives and/or managers, choose colors that impact the visualization of the results being displayed. You

Excel Dashboards – Interactive Controls ”; Previous Next If you have more data to display on the dashboard that does not fit into a single screen, you can opt for using Excel controls that come as a part of Excel Visual Basic. The most commonly used controls are scrollbars, radio buttons, and checkboxes. By incorporating these in the dashboard, you can make it interactive and allow the user to view the different facets of the data by possible selections. You can provide interactive controls such as scroll bars, checkboxes and radio buttons in your dashboards to facilitate the recipients to dynamically view the different facets of data being displayed as results. You can decide on a particular layout of the dashboard along with the recipients and use the same layout then onwards. Excel interactive controls are simple to use and does not require any expertise in Excel. The Excel interactive controls will be available in the DEVELOPER tab on the Ribbon. If you do not find the DEVELOPER tab on the Ribbon, do the following − Click on Customize Ribbon in the Excel Options box. Select Main Tabs in the Customize the Ribbon box. Check the Developer box in the Main Tabs list. Click the OK. You will find the DEVELOPER tab on the Ribbon. Scroll Bars in Dashboards One of the features of any dashboard is that each component in the dashboard is as compact as possible. Suppose your results look as follows − If you can present this table with a scroll bar as given below, it would be easier to browse through the data. You can also have a dynamic Target Line in a Bar chart with scroll bar. As you move the scroll bar up and down, the Target Line moves up and down and those bars that are crossing the Target Line will get highlighted. In the following sections, you will learn how to create a scroll bar and how to create a dynamic target line that is linked to a scroll bar. You will also learn how to display dynamic labels in scroll bars. Creating a Scrollbar To create a scrollbar for a table, first copy the headers of the columns to an empty area on the sheet as shown below. Insert a scrollbar. Click on the DEVELOPER tab on the Ribbon. Click on Insert in the Controls group. Click on Scroll Bar icon under Form Controls in the dropdown list of icons. Take the cursor to the column I and pull down to insert a vertical scroll bar. Adjust the height and width of the scroll bar and align it to the table. Right click on the scroll bar. Click on Format Control in the dropdown list. Format Control dialog box appears. Click on the Control tab. Type the following in the boxes that appear. Click the OK button. The scroll bar is ready to use. You have chosen the cell O2 as the cell link for the scroll bar, which takes values 0 – 36, when you move the scroll bar up and down. Next, you have to create copy of the data in the table with a reference based on the value in the cell O2. In the cell K3, type the following − = OFFSET(Summary[@[S. No.]],$O$2,0). Hit the Enter button. Fill in the cells in the column copying the formula. Fill in the cells in the other columns copying the formula. Your dynamic and scrollable table is ready to be copied to your dashboard. Move the scroll bar down. As you can observe, the value in the cell – scroll bar cell link changes, and the data in the table is copied based on this value. At a time, 12 rows of data is displayed. Drag the scroll bar to the bottom. The last 12 rows of the data is displayed as the current value is 36 (as shown in the cell O2) and 36 is the maximum value that you have set in the Form Control dialog box. You can change the relative position of the dynamic table, change the number of rows to be displayed at a time, cell link to scroll bar, etc. based on your requirement. As you have seen above, these need to be set in the Format Control dialog box. Creating a Dynamic and Interactive Target Line Suppose you want to display the sales region-wise over the last 6 months. You also have set targets for each month. You can do the following − Create a column chart showing all this information. Create a Target Line across the columns. Make the Target Line interactive with a scroll bar. Make the Target Line dynamic setting the target values in your data. Highlight values that are meeting the target. Create a column chart showing all this information Select the data. Insert a clustered column chart. Create a Target Line across the columns Change the chart type to combo. Select chart type as Line for the Target series and Clustered Column for the rest of the series. Create a base table for the Target Line. You will make this dynamic later. Change the data series values for the Target Line to the Target column in the above table. Click the OK button. Change the color scheme for the Clustered Column. Change the Target Line into a green dotted line. Make the Target Line interactive with a scroll bar Insert a scroll bar and place it below the chart and size it to span from January to June. Enter the scroll bar parameters in the Format Control dialog box. Create a table with two columns − Month and Target. Enter the values based on the data table and scroll bar cell link. This table displays the Month and the corresponding Target based on the scroll bar position. Make the Target Line dynamic setting the target values in your data Now, you are set to make your Target Line dynamic. Change the Target column values

Excel Dashboards – Excel Charts ”; Previous Next If you choose charts for visual display of data, Excel charts help you to pick up and change the different views. Excel provides several chart types that enable you to express the message you want to convey with the data at hand in your dashboard with a graphical representation of any set of data. In addition, there are certain sophisticated charts that are useful for some specific purposes. Some of these are available in Excel 2016. But, they can also be built from the built in chart types in Excel 2013. In this chapter, you will learn about the chart types in Excel and when to use each chart type. Remember that in one chart in the dashboard, you should covey only one message. Otherwise, it may cause confusion in the interpretation. You can size the charts in such a way that you can accommodate more number of charts in the dashboard, each one conveying a particular message. Apart from the chart types that are discussed in this chapter, there are certain advanced charts that are widely used to depict the information with visual cues. You will learn about the advanced chart types and their usage in the chapter – Advanced Excel Charts for Dashboards. Types of Charts You can find the following major chart types if you have Excel 2013 − Column Charts Line Charts Pie Charts Doughnut Chart Bar Charts Area Charts XY (Scatter) Charts Bubble charts Stock Charts Surface Charts Radar Charts To learn about these charts, refer to the tutorial − Excel Charts. Combo Charts When you have mixed type of data, you can display it with Combo (Combination) charts. The charts can either have only the Primary Vertical Axis or a combination of Primary Vertical Axis and Secondary Axis. You will learn about Combo charts in a later section. Selecting the Appropriate Chart Type To display the data by a chart in your dashboard, first identify the purpose of the chart. Once you have clarity on what you want to represent by a chart, you can select the best chart type that depicts your message. Following are some suggestions on selecting a chart type − If you want to compare data values, you can choose a bar chart, pie chart, line chart, or scatter chart. If you want to show distribution, you can do so with a column chart, scatter chart or line chart. If you want to show trends over time, you can use a line chart. If you want to represent parts of a whole, a pie chart can be an option. But, while you use a pie chart, remember that only two to three different data points with very different data values can be effectively depicted with the varying sizes of the Pie slices. If you try to depict more number of data points in a Pie chart, it can be difficult to derive the comparison. You can use Scatter chart if any of the following is the purpose− You want to show similarities between large sets of data instead of differences between data points. You want to compare many data points without regard to time. The more data that you include in a Scatter chart, the better the comparisons you can make. Recommended Charts in Excel helps you to find a chart type that is suitable to your data. In Excel, you can create a chart with a chart type and modify it later any time easily. Showing Trends with Sparklines in Tables Sparklines are tiny charts placed in single cells, each representing a row of data in your selection. They provide a quick way to see trends. In Excel, you can have Line Sparklines, Column Sparklines or Win/Loss Sparklines. You can add Sparklines to your table quickly with the Quick Analysis tool. Identify the data for which you want to add Sparklines. Keep an empty column to the right side of the data and name the column. Sparklines will be placed in this column. Select the data. Quick Analysis tool button appears at the bottom right corner of your selected data. Click on the Quick Analysis button. Quick Analysis tool appears. Click on SPARKLINES. Chart options appear. Click on Line. Line Charts will be displayed for each row in the selected data. Click on Column. Column Charts will be displayed for each row in the selected data. Win/Loss charts are not suitable for this data. Consider the following data to understand how Win/Loss charts look. Using Combo Charts for Comparisons You can use Combo charts to combine two or more chart types to compare data values of different categories, if the data ranges are varying significantly. With a Secondary Axis to depict the other data range, the chart will be easier to read and grasp the information quickly. Fine Tuning Charts Quickly You can fine tune charts quickly using the three buttons , and that appear next to the upper-right corner of the chart. With Chart Elements, you can add or remove axis, axis titles, legend, data labels, gridlines, error bars, etc. to the chart. With Chart Styles, you can customize the look of the chart by formatting the chart style and colors. With Chart Filters, you can dynamically edit the data points (values) and names that are visible on the chart being displayed. You can select / deselect Chart Elements. You can format the Gridlines to show the depth axis. You can set a Chart Style. You can choose a color scheme for your chart. You can dynamically select values and names for display. Values are the data series and the categories. Names are the names of the data series (columns) and the categories (rows). Using Aesthetic Data Labels You can have aesthetic and meaningful Data Labels. You can place Data Labels at any position with respect to the data points. You can format Data Labels with various options, including effects. You can change Data Labels to