Creating a Dashboard in Excel

In this article, I am going to explaining in more details how could you create a dashboard in excel?

This is a screen shot of data which I will create a dashboard based on it.

This is a hospital data and specifically OVR “ Occurrence/Variance Report “ for the last quarter in 2020.

We have three main stages in our dashboard.

• Creating a Table and Dashboard Space
• Creating Charts ( Bar, Pie, Doughnut Charts)
• Creating Indicators

Each stage has several steps, ok let us start.

1st Stage: Creating Table and Dashboard Space

Why we should create a table? we should board our data with table for allowing to update dashboard when we add new values.

Two steps here:

1st step creating a table.

Select all data or cmd + A, go to main list, click on Insert then select Table and choose your Style.

2nd step creating space.

Select the first eleven rows (indexes) and WriteClick then select Insert, you will see a new space added on the head of data. Fill the space with color and expand the size of rows and columns.

2nd Stage: Creating Charts ( Bar, Pie, Doughnut Charts)

- Bar Chart

1st step

Select all data or cmd + A, go to main list and click on Insert then select PivotTable and click OK.

Note: When you select pivot table, new icon will appear. Select New worksheet as you see in the screen shot below then OK.

2nd step

After we selected pivot table, new work sheet will be added. Go to PivotTable Fields on the right side, from FIELD NAME select the following features.

Months **** as column

Risk Level **** as rows

OVR ID**** as values

From Values go to OVR ID and WritClick then select field setting and from Summarize by select Count.

3rd step

After we selected features we will create a Bar Chart. Go to main list and select Insert then from bar chart icon select first style.

4th step

Copy the bar chart and past it on the dashboard space then adjust the following:

• font and font size
• Bar Chart and legend color
• Removing background and outline Charts as well as horizontal lines

Note: we can adjust number of OVR above the bar by select the style which has bar numbers then double click on the number, new list will appear on the right. From bar icon select label options and label position then select Outside end.

After we adjusted the Bar Chart, the dashboard will be the same picture in below.

- Pie Chart

1st step

This step, it is the same as we did in the Bar Chart.

2nd step

This step, it is the same as we did in the Bar Chart but here we will select two features.

Event Section **** as rows

OVR ID **** as values

From Values go to OVR ID and WritClick then select field setting and from Summarize by select Count.

From table select Row Labels, then click on filter icon, new list will appear, from sort select Descending and Count of OVR ID from sort by. Then from filter go to by value and select Top 10 then adjust 10 to 5 items.

3rd step

After we selected top five section of OVR, we will create a Pie Chart. Go to main list and select Insert then from pie chart icon select first style.

4th step

This step, it is the same as we did in the Bar Chart.

After we created Bar and Pie Charts, the dashboard will look like as below

- Doughnut Charts

We need to create a table on the same sheet with Status Values (Close and Under Process)

We will name columns of that table with percentage and count while rows with Close, Under Process and Total. We compute the count, total and percentage of each categories (Close and Under Process) to create Doughnut Chart.

We use COUNTIF function to compute the count of each categoris, then we compute the total to find the percentage.

Note: select the percentage values and go to main list and click on percentage sign to convert it to integer.

Now, we can create Doughnut Chart by select the status ( Close and Under Process) and only their percentage.

As you saw above, the Doughnut Chart without legend and numbers, but we need to put the percentage of the high rate which is Close inside the Chart as a percentage.

From the main list select Insert then Text and Text Box, create a text box by click and drag then go to function bar and tab “=” and select Close Percentage “70%”

3rd Stage: Creating Indicators

We already create the percentage of Chart. Now we will create the indicators with its labels, they are the count of Close and Under Process. We will put one on the right and other on the left from Doughnut Chart.

From the main list select Insert then Text and Text Box, create a text box by click and drag then go to function bar and tab “=” and select cell of Close.

Copy the text box of Close and past it three times and change the value with cell of Under Process, and the count of Close and Under Process. It will directly update with Status values change. Now we have two Indicators with thier labels.

Select the first row alongside the dashboard from cell A to J, then go to the main list and select Merge & Center and write the title name.

Note: We can reorder the categories of each month in the bar chart to be more consistent to compare each risk level per month.

Finally, we created an amazing dashboard. It will update when we add new rows. If we add new rows we need to go to main list then select data and click on Refresh All