How to Create an Editable Bubble Chart

This article describes how to go from a crosstab or a grid summary table in Displayr...

...to an Editable PowerPoint Bubble Chart, where the size of the bubbles indicates the percentage: 

Note: You can create a Bubble chart in Displayr and export it to PowerPoint as an image. PowerPoint doesn't support this type of Bubble Chart output. Nevertheless, this type of output is often used in reporting. Researchers have found a workaround involving restructuring the data and charting it as an XY Scatter plot > Bubbles. This article contains the steps to automate the process.

Requirements

• A crosstab of two variables or a summary table using a grid variable.

Method - Create and export an editable chart 

1. Create a crosstab using two variables or a summary table using a grid-type question. The data shown in the table rows will appear on the y-axis of the bubble chart, and the columns on the x-axis.

2.  From the toolbar, go to Calculation > Custom Code and click on the Page to create an output.

3. Update and input the code below into the Code  panel. You must update the table name (table.Brand.Association) in Line 2 of the code. You can find the table's name by selecting the table and going to General > General > Name in Properties . 

# Define source tables. 
mytab = table.Brand.Association

#Remove NET column and Row
mytab <- mytab[rownames(mytab) != "NET", colnames(mytab) != "NET"] / 100 

# Set parameters
num_statements <- nrow(mytab)
num_brands <- ncol(mytab)

# Build columns
X_Values <- rep(1:num_brands, each = num_statements)  # 1..4, each 5 times
Y_Values <- rep(seq(num_statements, 1), times = num_brands)  # 5..1, repeat 4 times
Size <- as.vector(as.matrix(mytab))  # convert to vector in correct order

# Combine into a data frame
final_table <- data.frame(
  X_Values = X_Values,
  Y_Values = Y_Values,
  Size = Size
)

# Print result
print(final_table)

4. The above code will reformat the table into the correct format for the desired chart type. 

5. Select the output, and from Properties , go to Appearance > PowerPoint Export > Format, and select Microsoft Chart.

6. Set Export as to Bubble. 

7. Go to Share > Export Report > PowerPoint > Create New Document > Export. 

8. Open the exported file in PowerPoint and reformat the chart. 

Method - Create and export the table with % values

1. Select the table used as input for the above code.

2. Go to Share > Export Report > PowerPoint > Create New Document > Export. 

3. Open the exported PowerPoint file, reformat the table, and position it over the bubble chart.  

In the PowerPoint example below, the exported table is overlaid on top of the bubble chart. The table's rows and columns were resized to position the table cells with data labels on each bubble. In PowerPoint, Table Design > Borders is set to No borders, and Table Layout > Center Vertically is used for columns to center the labels.

Next

Exporting and Updating PowerPoint Reports Video Series