Creating an IT Risk Dashboard in Excel

Dashboard exampleOne of the most valuable tools in my “IT Audit Arsenal” is the ability to easily identify and communicate risk patterns with a Risk Dashboard. A Risk Dashboard helps drive decisions (like what projects you take on, where company risk resides) and has become an easy way to communicate status and progress reports to the executive team. Over the years my dashboard has become increasingly complex (because it stretches across business units, locations, risk types, and tracks risk remediation), but starting a risk dashboard is easy and completely customizable based on your needs!

Tip: If you don’t know how to use excel I have linked to the necessary tutorials at the end of this post.

Step 1: Create a Risk Catalog

The first step to creating a risk dashboard is to decide what data you want to capture and how that data is organized. For example, I know that when I capture risks (or findings) I want to track certain key data points (i.e., category, rank, description, owner, etc.) and that forms the groundwork for my Risk Catalog.

It is important to think through what elements you want to capture and how you want to organize the data. I always think to myself: What questions will I have about the data? Then I ensure that I capture enough information to answer those questions and represent that data in my dashboard.

Tip: Standardize data elements to improve data tracking (i.e., create drop-down lists for fixed risk categories).

Click to enlarge.

Click to enlarge.

Step 2: Create Pivot Tables

Remember those questions you have about the data? Well this is the part you create pivot tables to represent the data you have collected in your Risk Catalog. These tables will drive your dashboard and dynamically update your dashboard as you add data to your Risk Catalog.

Tip: If you have data that resides outside of Excel (for example, in a SQL database), or you wish to use data from multiple locations, you can use PowerPivot to pull in that data into your workbook and pivot off of as well.

Tip: Create a new tab in your excel spreadsheet labelled “data”. Label your pivot tables based on the information contained in the table. This will help you stay organized as your dashboard grows.

Click to enlarge.

Click to enlarge.

Step 3: Create Your Dashboard

Once you have your pivot tables set up you can create charts that update automatically as you add data to your risk catalog. Your dashboard should bring to light nuanced information from mass amounts of data created by your team during audits or risk assessments. For example, via the charts below we can see that which risk areas of greatest concern and present the most risk to the company.

Tip: When you create your dashboard pretend you are making a website. Colors and labels should be consistent. You can even share this dashboard on SharePoint.

Click to enlarge.

Click to enlarge.

Helpful Tutorials:

Excel drop-down lists: Here
Create Pivot Tables: Here
Create Charts from Pivot Tables: Here
Learn more about PowerPivot: Here

6 thoughts on “Creating an IT Risk Dashboard in Excel

Leave a Reply