Office hacks: three-dimensional display with a good Excel matrix

In order to better show the connection between the data, we often use bar charts, pie charts to show multi-dimensional data. However, these charts can only show at most two-dimensional data, if you need to show three-dimensional data, at this time you can use Excel’s matrix chart to achieve.

Tip: What is a matrix chart?

From the event of a multidimensional problem, find out the pair of factors, arranged into a matrix, and then analyze the problem according to the matrix, so as to synthesize a variety of indicators for judgment of a multidimensional visual chart. For example, the common Boston chart and McKinsey matrix analysis method use the matrix chart.

Generally we judge each product, often based on market share, growth rate and total profit to make a comprehensive judgment. The following table shows the index data corresponding to the four products A, B, C and D respectively, in which the judgment criteria for different products are as follows.

Star products – high growth rate, high market share

Cash cow products – low growth rate, high market share

Problem products – high growth rate, low market share

General products – low growth rate, low market share

In order to see more intuitively what criteria a product meets, a matrix chart now allows for a quick comparison of product properties (Figure 1).

201815ejz1

Figure 1 Profitability indicator data

Select the data in the example, click “Insert→Chart→All Charts→XY Scatter Chart”, select the “Bubble Chart→Growth Rate”, insert a regular bubble chart in the table (Figure 2).

201815ejz2

Figure 2 Insert a bubble chart

Select the bubble icon for Product A, click “Chart Tools→Design→Select Data” in the menu bar, check Product A in the window that opens, click “Edit”, and make the following settings in the editing window that opens.

Select A2 for the series name (i.e. the name of product A), C2 for the X-axis series value (i.e. the growth rate value), B2 for the Y-axis series value (i.e. the market share value), and D2 for the series bubble size (i.e. the profit value).

In this way we can represent the three index values corresponding to Product A through a bubble combined with the X-axis and Y-axis (Figure 3).

201815ejz3

Figure 3 Edit data

As above, add the numerical bubbles of products B, C and D in turn, so that the three indicators of different products can be expressed in one coordinate plane using different bubbles. For easy identification, select the bubbles, right-click and select “Fill” to fill them with different colors in order to differentiate them (Figure 4).

201815ejz4

Figure 4 Filling the bubbles with color

In order to make the final matrix more intuitive to represent the characteristics of the product, the data of the axes need to be set here. For example, the vertical coordinate represents the market share, and the maximum value in the case is 22%, so the maximum value can be set to 25%. Right click on the vertical coordinate and select “Set Axis Format” to set the maximum value to 0.25 (if there is a negative number, set the minimum value to the corresponding negative number). Similarly, set the value of the horizontal coordinate so that the product is more evenly distributed after adjustment (Figure 5).

201815ejz5

Figure 5 Adjusting coordinate values

Tip.

A lot of data can be displayed in a matrix chart for multi-dimensional display. Here it is important to note that when using the four quadrant approach to display multi-dimensional data, you must first plan the criteria for classification (such as the product classification in the above example), and at the same time use the appropriate coordinate scale to make the distribution of bubbles more even. For example, some data values span in several orders of magnitude, if the Y-axis scale using conventional settings, data fluctuations are not convenient to display in the matrix, you can set the axis scale to “logarithmic values”, you can set according to the actual data situation.

Because the subsequent operation needs to superimpose the matrix on the bubble icon, so the chart needs to be further optimized. Right-click the chart and select “Set Grid Line Format”, set it to “No Lines”, continue to select the horizontal lines and set them to “No Lines”, remove the grid lines, and add data labels and coordinate text for the Add data labels and coordinate text descriptions for the bubbles. Finally, the background of the chart is set to “no fill”, so that the matrix image can be used as its background image (Figure 6).

201815ejz6

Figure 6 graph beautification

Click “Insert→Graphics→Rectangle”, insert 4 rectangles as prompted, and arrange the inserted rectangles in the form of plane coordinates (set the appropriate spacing and size, fill with different colors). Insert text boxes as above, and enter the corresponding text descriptions according to the above product standards as a backup, while setting the outline of the text box to “none” and the fill color to “none”, and subsequently use it as a text description for different products (Figure 7).

201815ejz7

Figure 7 Rectangles and text boxes

Move the four rectangles to the chart area in turn and set them to “to the bottom”, while adjusting the size and spacing of each rectangle, so that each product falls exactly on a different rectangle. Continue to select different text boxes, according to the actual performance of the product moved to the corresponding rectangle above, and finally insert arrows, art words, so that through the matrix chart we can visually see the type of each product (Figure 8).

201815ejz8

Figure 8 Matrix chart

Through the matrix picture combined with the X-axis, Y-axis can easily complete the production of the matrix chart, if there are other products, we just enter the data and regenerate the corresponding bubble data, so that as long as the view bubble falls in the matrix chart quadrant location, we can intuitively see through the location of the product belongs to which nature, so that the three-dimensional matrix chart is not better to use?

Leave a Comment