portfolio diagrams

Portfolio diagrams can be created with the Excel „bubble“ diagram modus for many applications. What makes this type of diagram special is that a total of three data dimensions can be visualised: X-position (horizontal), Y-position (vertical) and the bubble size (area).

Portfolios are primarily used if a quantity of data are to be segmented. In an investment portfolio, for example, factors such as productivity and risk index can be inserted on the axes, whilst the bubble size represents the investment volume. Typical portfolio applications include product/market observations, employee performance and strategic project management. In all such cases, visualisation enables easily recognisable sub-categorisation of data (products, projects etc.) in certain groups, combined with evaluation options for good/bad or preferred/not preferred. For example, high-risk projects with a low yield can be identified at a glance.

As in all diagrams, portfolios should be concise and informative. Easy to read labelling and precise highlighting play an important role. Unfortunately, standard functions for this are quite limited in the Excel bubble diagrams. We have thought up some good ideas to create professional and informative representation with a low outlay. Find out how to do this in the following.

Create a bubble diagram with a single data set (names of countries may not be marked)
Set axis length and gridlines according to requirements
Set data set colour to grey (neutral, no highlighting)

Develop formulae for automatic display/hiding of differently coloured data sets (grey, red, green) Hide using #NV

Change the data source of the first data sets, so that the bubble size is used from the previously calculated formula area
Insert and format two additional data sets with the corresponding source areas for red and green, only a few bubbles are visible in each of these data sets

Tip: Bubbles with a semi-transparent filling and white frame line are also easily identifiable when several data points intersect.

Insert an additional data set on the basis of the original source area and format it invisibly (no filling, no line), all bubbles in this data set are visible
Display data labelling for this data set and link with the cells containing names of countries

Tip: Linking works with the method from the article “clever data labelling”. For bubble diagrams in particular, it´s best to first click on one of the data labels and then press the cursor button (arrow) on the right. In this way, the first label field is accurately marked, which can then be linked with the first country names using “=” and a mouse click. Then move the cursor button to the right to the second label field and so on. As „column labelling” is not foreseen for the bubble diagram, the linked labels you made yourself are the only option. The labelling option “value from cells” is available from Excel 2013 onwards, allowing the same result to be achieved faster.