Business organizations use bubble charts as a tool for decision making through business intelligence. Bubble charts used with quadrants help users get intelligent information in a single view with a clear demarcation of business initiative and project categories. A classic example is comparing projects that are being executed by an organization on parameters such as cost, value, and risk.
This article describes a robust and easy way to develop bubble charts in Microsoft Excel 2010 that can be published with Excel Services. It provides step-by-step guidance for developing a bubble chart in which series data can be added dynamically without using macros. Various examples on the web provide macros and scripts to create dynamic series; however, those methods are seldom sufficient when you want to publish the Excel sheets by using Excel Services with SharePoint 2010 because Excel Services does not support macros. The example provided here uses built-in Excel functions to generate the series data dynamically and then categorize the series information. Because it doesn’t use macros, it is completely compatible with Excel Services. This example uses a business scenario with real customer requirements to help you apply it to your own similar solutions.
|Department||Risk Value||Financial Value||Cost||Project Name|
|Finance||0.11||220||125||Profit and Loss|
|Information Technology||0.52||70||170||Intranet Web|
|Information Technology||0.8||195||150||Public Facing Web|
|Information Technology||0.9||350||150||Asset Management|
The following are the details for each column in Table 1.
- Department: Represents the entity for which the project is being executed. Each department executes multiple projects, and the data is grouped by department in the PivotTable. On the bubble chart, departments are used as categories and are represented as series data on the graph.
- Risk Value: Represents data that indicates the risk associated with each project. The value is calculated based on the risk assessment done by the project managers. On the graph, it is plotted on the x-axis with value ranges from 0 to 1.
- Financial Value: Represents the value of the project in terms of ROI. The financial value ranges from 0 to 1000. The data is represented in thousands and is plotted on the y-axis of the graph.
- Cost: Represents the cost incurred in executing the project. The cost is represented on the graph as the size of the bubble. The larger the bubble, the higher the cost incurred for that project.
- Project Name: The name of the project. The data is not used in plotting the graph; however this data can be used to filter and compare specific projects or to analyze the data for a particular project.
- Chart contains the bubble chart and the PivotTable data.
- Calculations contains the formulas for calculations used to create the chart.
- Raw Data contains the raw data that is used to generate the PivotTable.
Creating the Excel Workbook for the Bubble Chart
In this section, you create the workbook that contains the bubble chart and its data.
To create the workbook
- Create a workbook in Excel 2010.
- In the workbook, create three worksheets named Chart, Calculations, and Raw Data, and then save the workbook.
Creating the Chart Worksheet
In this section, you create the worksheet that contains the chart.
To set up the Chart worksheet
- Create the table that contains the data shown in Table 1 in the Raw Data worksheet.
- In the Chart worksheet, insert a PivotTable in column A. Leave room at the top of the column for the chart that will be added later. Select the data range for the PivotTable as the table you created in the Raw Data worksheet. For simplicity, use the Raw Data worksheet as the data source for the PivotTable. In real customer scenarios, the data would typically come from a RDBMS database or other external data source.
- For the PivotTable to display the data in the format represented in Table 1, you have to change some settings and properties. Select the PivotTable, and then click the PivotTable Tools Design tab on the ribbon.
- On the ribbon, click the Subtotals drop-down arrow, and then select Do Not Show Subtotals. Repeat this procedure for Grand Totals.
- On the ribbon, click the Report Layout drop-down arrow, and then select Show in Tabular Format.
- Clear the Row Headers check box.
- On the ribbon, click the PivotTable Tools Options tab, and then in the Show group, clear the +/- Buttons option.
- Next, add a slicer to make the chart easy to use. Click the Insert tab on the ribbon, and in the Filter group, click Slicer. In the list, choose Department as the field. This enables users to easily filter the data. The PivotTable should appear similar to Figure 1.
Creating the Calculations Worksheet
In this section, you create the formulas for the worksheet to make the series dynamic and compatible with Excel Services. You can copy and paste the formulas from the sample Excel workbook that accompanies this article, or you can create your own formulas by using the following steps.
To set up the Calculations worksheet
- In the workbook, click the Calculations worksheet.
- In cell A1, enter Departments.
- In cells B1, C1, and D1, enter all the department names (shown in Table 1) for which you want to plot the graph.
- In cells A2, A3, A4, A5, and A6, enter the titles shown in Table 2.
Cell Title A2 Find Department in List A3 Find number of rows A4 Risk Range A5 Project Cost Range A6 Financial Value Range
- In cell B2, add the formula =IFERROR(MATCH(B1,Chart!$A:$A,0),0). This formula uses the Match function to find the row in the PivotTable that corresponds to the department name in cell B1. In this example, you are looking for Purchase. Using the IFERROR function is a good coding practice and helps avoid breaking the chart if a match is not found.
- In B3, add the formula =COUNTIF(Chart!$A:$A,B1). In step 5, you found the PivotTable row where the Purchase department started. In this step, you are looking for the number of rows where the department is set to Purchase.
Now that you know where a particular department begins in the PivotTable and how many times it is repeated, you can create the ranges needed to plot the bubble chart. Bubble charts, unlike other types of charts, need three values to plot the graph: the x-axis value, the y-axis value, and the size value. The size value determines the size of the bubble and helps visually compare any two data points. The greater the size value, the larger the bubble. In this example, you are using the project cost as the bubble size. In the next steps, you create the x-axis, y-axis, and size value ranges for the bubble chart.
To complete the Calculations worksheet
- For the Purchase department, the range of the x-axis (Risk) is Chart!$B$37:$B$39. To set this, enter the formula =IF(B2+ B3=0,0,”Chart!$B$” & B2 & “:$B$” & B2 + B3 -1) in cell B4.
- The range of the size (Cost) is Chart!$D$37:$D$39. To set this, enter the formula =IF(B2+ B3=0,0,”Chart!$D$” & B2 & “:$D$” & B2 + B3 -1) in cell B5.
- The range of the y-axis (Financial Value) is Chart!$C$37:$C$39. To set this, enter the formula =IF(B2+ B3=0,0,”Chart!$C$” & B2 & “:$C$” & B2 + B3 -1) in cell B6.
- You now have all the formulas in place for the x-axis, y-axis, and size for the Purchase department. To add values in the cells for all the other departments, select the rows B2 to B6, and then drag the square at the bottom-right of the selection until the rows from row 1 to row 6 are filled for all the departments. The Calculations worksheet should look similar to Figure 2.
Setting up the Named Ranges
Named ranges are a feature of Excel that allows you to create names for a range or referenced cells and then use the names in any formula or function. You need named ranges to plot the bubble chart because the INDIRECT function cannot specify a range in the axis of a chart. You are using the INDIRECT function to dynamically create ranges for the axis in this example. Because new departments will probably rarely be added, the process of creating named ranges is also seldom needed. Initially, you create named ranges for all existing departments.
In the following steps, you create named ranges for the formulas contained in the Calculations worksheet. For each new department, you have to define three names.
To create the named ranges
- On the Formulas tab on the ribbon, click the Name Manager button.
- In the Name Manager dialog box, click New.
- To create a name value for the x-axis (Risk), in the Name box, enter PurchaseDeptRisk. In the Refers to box, enter the formula =IF(Calculations!$B$4<>0,INDIRECT(Calculations!$B$4),0).
The INDIRECT function creates a range from a given string value.
- To create a name value for the y-axis (Financial Value), in the Name box, enter PurchaseDeptFinancialValue. In the Refers to box, enter the formula =IF(Calculations!$B$6<>0,INDIRECT(Calculations!$B$6),0).
- To create a name value for the size (Cost), in the Name box, enter PurchaseDeptCost. In the Refers to box, enter the formula =IF(Calculations!$B$5<>0,INDIRECT(Calculations!$B$5),0).
- Repeat steps 1 through 3 for each department that you want to plot. In this example, there are four departments, so you will have twelve named ranges in total. The worksheet should now look similar to Figure 3.
Creating the Chart Series
Now you can start plotting the bubble chart. Any chart in Excel has a set of series data to plot as does the bubble chart. Each series in the bubble chart is represented by a different color. You use this feature to represent each department in the chart. A series in a bubble chart has four values:
- Name of the series
- X-axis range
- Y-axis range
- Size of the range
To set up the chart series
- Right-click the chart, and then click Select Data.
- Under the legend entries, click Add.
- For the Series Name box, click the Calculations worksheet, and then click the name of the department.
- Next, click cell B1 in the Calculations worksheet. The value Purchase is displayed next to the text box.
- To fill in the x-axis for the series, click in the Series X values box, and then press the F3 function key. This displays a dialog box with all the named ranges you have created. Choose the appropriate named range; in this instance, choose PurchaseDeptRisk.
- Next, qualify the named range with the name of the Excel worksheet. In this example, qualify it as DynamicBubbleChart.xlsx. Finally the Series X values box should have the value DynamicBubbleChart.xlsx!PurchaseDeptRisk, and the value displayed beside the box should be a range of the x-axis values. For more information, refer to Figure 4.
- Repeat step 4 for the Series Y values and Series bubble size boxes by selecting appropriate named ranges.
- Repeat steps 1 through 5 for each department that you want to plot a series for.
- Now, set up the bubble chart to be a four-quadrant chart. To add the quadrants, first, create the lines in the center. Click the bubble chart, and then, in the Chart Tools group on the ribbon, click the Layout tab.
- Next, create the horizontal quadrant line. Click the Axes button, point to Primary Horizontal Axis, and then click More Primary Horizontal Axis Options. In the Format Axis dialog box, select the properties as displayed in Figure 4.
- To create the vertical line in the center, click the Layout tab on the ribbon. Click the Axes button, point to Primary Vertical Axis, and then click More Primary Vertical Axis Options. Select the properties as displayed in Figure 5.
Now the bubble chart should have all the data plotted appropriately, as shown in Figure 6.
- Pearls: The top-left quadrant in the bubble chart represents projects that are of immense value. These are the projects that have a high ROI and very low risk associated with them.
- Oysters: The top-right quadrant represents projects that have a high ROI but are also prone to higher risks.
- Bread and Butter: The bottom-left quadrant represents projects that have a relatively low ROI and are also less risky to execute. These are usually the most common projects and are usually larger in number and smaller in size and complexity.
- White Elephants: The bottom-right quadrant represents projects that have a low ROI and are highly prone to risks. These are the projects that are very complex in nature to execute and are not very high on value.
With all the quadrants in place, the Chart worksheet will look similar to Figure 7.
You can use the slicer and the PivotTable filters to filter a specific set of projects or departments for comparisons. The bubble chart is then dynamically populated to match the filter criteria. For example, filtering the department value on Finance and Purchase, as shown in Figure 8, dynamically changes the bubble chart.