# Charts and Dashboards: The Risk Bubble Chart – Part 1

13 January 2023

*Welcome back to our Charts and Dashboards blog series. This week, we’re going to look at how to make
a Risk Bubble chart.*

* *

**The Risk Bubble Chart**

Have you ever wanted to organise your risks and consequences based on the likelihood of occurrence and the potential damage? Today, we will introduce you to a Risk Bubble chart which helps you to keep track of the likelihood of any given problem, the consequences of the problem, and the size of said problem.

Here, this chart categorises types of risk in
two dimensions, *i.e.* both the impact of the risk and the likelihood
of it occurring. The size of the bubble
shows how many categories meet the same criteria, but this could be replaced by
a monetary value impact, the number of customers affected, *etc.* And yes, I do
appreciate that using bubbles (circles) may not be the best way to depict the
number of risk types due to the area of a circle being proportional to the
square of its radius! The aim of this
article is not to defend such practice, but to show how to create such a chart
given they are used for risk assessment / analysis.

A key element of this chart is the risk input table,
which is automatically updated based upon your input(s) and the Risk Bubble
chart. To assist us, there are a few key
inputs we will be using here. The first
table we have is a table we shall name **LU_Matrix** (*i.e.* the “**L**ook**U**p **Matrix**”), viz.

In this table, we will name the row headings (in
grey) **LU_Likelihood** and the column / field headings (also in grey) **LU_Consequences**. With these inputs, we can create a risk table
that changes colour automatically.

The next input we will need is the risk category table which determines the likelihood and consequence of a risk:

We name this table **Risk_Category**.

In this table, we can enter the **Risk Category**, **Likelihood**, and **Consequence**.
These inputs will be used to make the Risk Bubble chart, but we will
need to refine this data for it to be usable.

With these inputs available, the question now is: how do we make the Risk Bubble chart? Well, let’s get started with the risk table…

**Risk Table**

The first step is to construct the axis for this
chart. For this task, we will need to employ
the **COUNTA**, the **SEQUENCE**,** **and the **INDEX **functions. **COUNTA **as you guessed will “count”
every cell that is not blank. Therefore,
we will count the number of **Likelihood** categories:

**=COUNTA(LU_Likelihood)**

The output for this function will be five [5] as
we have five [5] **Likelihood **categories in our example. As we generate a sequence of numbers, we will
wrap up the **COUNTA** formula inside the **SEQUENCE** function**:**

**=SEQUENCE(COUNTA(LU_Likelihood))**

This will generate an array from one [1] to five [5]:

However, we want this array to generate in
reverse order, *i.e.*, five [5] to one [1].
This can be achieved by setting the third argument of the **SEQUENCE **function to our number of **Likelihood** categories and the fourth argument
to negative one [-1]:

**=SEQUENCE(COUNTA(LU_Likelihood),,COUNTA(LU_Likelihood),-1)**

The third argument of the **SEQUENCE **function will specify the starting value and the fourth argument will specify
the increment for each subsequent value.
This will give us the following:

(If dynamic arrays are not available, alternative formulae can generate the same desired result).

Finally, we apply our **INDEX **function here
to make the **y**-axis (vertical, or dependent, axis) of the chart:

**=INDEX(LU_Likelihood,SEQUENCE(COUNTA(LU_Likelihood),,COUNTA(LU_Likelihood),-1))**

The **INDEX** function will take the fifth
item of the **LU_Likelihood** and put it on the top row, the fourth item put
on the second row, and we repeat this process until we have the first item in
the fifth row:

Then, we will go one cell below and one cell to the right of the word “Rare” here to enter our second, much simpler, formula:

**=LU_Consequences**

After entering the formula in that cell (again, assuming
you have __Dynamic Arrays__ in your version of Excel), we
will have the following visual:

Next, we apply some formatting:

We have formatted each of the **Likelihood **and **Consequence** values to make them look more like headings, whilst
rotating the text in the **y**-axis.
We have also resized all cells within the graph area to dimensions of
100x100 pixels (a column width of 13.57 and a row height of 75).

Assuming the grid is positioned as in the
graphic (*above*), in cell **L89**, we will enter the
following formula, and then copy this throughout the entire table:

**=INDEX(LU_Matrix[[Minimal]:[Catastrophic]], MATCH($K89,LU_Likelihood,0),
MATCH(L$94,LU_Consequences, 0))**

These **MATCH **functions will match the row
position of the item on the left of the **LU_Matrix** and the column
position of the item on the bottom of the **LU_Matrix**. Then the **INDEX** function will return
the item in the exact row and column that we match on the **LU_Matrix**. After entering the formula and populating the
table we will have the following visual:

With the headings and shape of the chart in place, let’s take a break. Next week, we’ll look at making this a little more colourful.

* *

*That’s it for this week, come back next week for more Charts and
Dashboards tips.*