This article describes how to go from a crosstab or a grid summary table in Q...
...to an Editable PowerPoint Bubble Chart, where the size of the bubbles indicates the percentage:
Note: You can create a Bubble chart in Q 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 made from two variables or a summary table using a grid-type question.
Method - Create and export an editable chart
1. Create a crosstab using two variables or a summary table from 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. Go to Create > R Output.
3. Update and input the below code into Properties > R Code.
You must update the table name (table.Brand.Association) from Step 1 in Line 2 of the code. You can find the table's name by right-clicking on the table in the Report tree and selecting Reference Name...
# 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 click on the PowerPoint icon in the toolbar.
6. Set Format to To PowerPoint as Excel Chart.
7. Set Chart to Bubble.
8. Ensure the Use legacy exporter (slower) box is unchecked.
9. Select OK.
10. Select Create New Presentation > OK.
11. Open the exported PowerPoint file and reformat the chart.
Method - Create and export the table with % values
1. Select the table used as input for the above code.
2. Select the output, and click on the PowerPoint icon in the toolbar.
3. Set Format to To PowerPoint as PowerPoint Table.
4. Ensure the Use legacy exporter (slower) box is unchecked.
5. Click OK.
6. Select Create New Presentation > OK.
If you have already exported the bubble chart, select Update other presentation > select the exported PowerPoint file > Open > Save.
7. Open the exported/ updated 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.