Creating Excel 2010 Bubble Charts for Use with Excel Services

Summary:  Learn how to create bubble charts in Microsoft Excel 2010 that are compatible with Excel Services.

Applies to: Excel 2010 | Office 2010 | SharePoint Server 2010 | VBA

In this article
Developing Bubble Charts for Business Intelligence
Business Scenario Example
Creating the Bubble Chart
Putting It All Together
Conclusion
Additional Resources

Provided by:  Richa Yadav, Microsoft Corporation

Contents

• Developing Bubble Charts for Business Intelligence

• Business Scenario Example

• Creating the Bubble Chart

• Putting It All Together

• Conclusion

• Additional Resources

 Download the Excel 2010 sample workbook from the Microsoft Download Center

Developing Bubble Charts for Business Intelligence

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.

Business Scenario Example

Consider a scenario in which a company that is executing a portfolio of projects wants to visually evaluate projects based on parameters such as Project Cost, Return on Investment (ROI), and Risk. Table 1 shows data that pertains to projects that the company is currently executing.

Table 1. Sample of the company’s project data

Column Descriptions

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.

Creating the Bubble Chart

This section provides details to create the bubble chart. Three worksheets are used to plot the bubble chart:

• 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

1. Create a workbook in Excel 2010.

2. 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

1. Create the table that contains the data shown in Table 1 in the Raw Data worksheet.

2. 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.

3. 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.

4. On the ribbon, click the Subtotals drop-down arrow, and then select Do Not Show Subtotals. Repeat this procedure for Grand Totals.

5. On the ribbon, click the Report Layout drop-down arrow, and then select Show in Tabular Format.

6. Clear the Row Headers check box.

7. On the ribbon, click the PivotTable Tools Options tab, and then in the Show group, clear the +/- Buttons option.

8. 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.

   Figure 1. PivotTable with slicer

   

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

1. In the workbook, click the Calculations worksheet.

2. In cell A1, enter Departments.

3. In cells B1, C1, and D1, enter all the department names (shown in Table 1) for which you want to plot the graph.

4. In cells A2, A3, A4, A5, and A6, enter the titles shown in Table 2.

   Table 2. Worksheet cell content

   Cell	Title
   A2	Find Department in List
   A3	Find number of rows
   A4	Risk Range
   A5	Project Cost Range
   A6	Financial Value Range

5. 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.

6. 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

1. 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.

2. 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.

3. 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.

4. 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.

   Figure 2. Calculations worksheet

   

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

1. On the Formulas tab on the ribbon, click the Name Manager button.

2. In the Name Manager dialog box, click New.

3. 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.

4. 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).

5. 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).

6. 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.

   Figure 3. Named ranges for each axis

   

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

1. Right-click the chart, and then click Select Data.

2. Under the legend entries, click Add.

3. For the Series Name box, click the Calculations worksheet, and then click the name of the department.

4. Next, click cell B1 in the Calculations worksheet. The value Purchase is displayed next to the text box.

5. 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.

6. 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.

7. Repeat step 4 for the Series Y values and Series bubble size boxes by selecting appropriate named ranges.

8. Repeat steps 1 through 5 for each department that you want to plot a series for.

9. 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.

10. 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.

    Figure 4. Format horizontal axis property settings

    

11. 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.

    Figure 5. Format vertical axis property settings

    

Now the bubble chart should have all the data plotted appropriately, as shown in Figure 6.

Figure 6. Bubble chart with dynamic values

Putting It All Together

Now that the bubble chart is ready and functional, the remaining step is to name the quadrants. The information displayed by the bubble chart is of immense value for a business portfolio owner running multiple projects in parallel. To add more value to the chart, name the four quadrants in the bubble chart to the following values:

• 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.

Figure 7. Completed bubble chart with quadrants

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.

Figure 8. Bubble chart with filters applied

Conclusion

A bubble chart is a variation of a scatter chart in which the data points are replaced with bubbles. You can use a bubble chart if your data has three data series, each of which contains a set of values. In this article, you saw an example of creating a bubble chart that can be changed dynamically by applying filters. By avoiding the use of macros, you make the chart compatible with Excel Services. Experimenting with the example will provide you with the information you need to create your own charts.

Additional Resources

For more information about the topics discussed in this article, see the following:

• Available chart types: Bubble charts

• Excel Services Overview