How to Create a Simple KPI Dashboard in Excel

Learning all the important metrics and choosing the right KPIs for your business (departments, teams, and projects included) is no cakewalk. That’s not to mention that once you’ve covered those steps, you still need to implement a plan to effectively track your metrics and visualize your KPIs.

Excel is a ubiquitous tool for data tracking, calculations, and visualizations. If you’re looking for something even more accessible, Google Sheets is free to use and very similar, although there are some differences in appearance and function. Either way, using a simple spreadsheet is an easy and approachable way to tackle your metrics.

Let’s Start with an Example

While there is a wealth of readily available articles on KPIs and how to choose them, and examples of the interactive dashboards you can build for them; there seems to be far less (free) content that provides concrete direction on how to build a dashboard yourself.

Screen-shot of Janz’s dashboard template

For that reason, I’d like to share an excellent spreadsheet shared by Christoph Janz on The Angel VC which can serve as a KPI dashboard template for early-stage startups. Follow the link to find the downloadable excel file. In the video below, I’ll go over how the table and connected charts are constructed, and why it’s important.

Here are a few quick notes:

• Data entry cells are highlighted.
• All numbers and formulas are clearly labeled, and each formula references other cells within the sheet.
• Most of the table is dedicated to calculating MRR (monthly recurring revenue) and CAC (customer acquisition cost) and the relevant sub-categories for each one.
• The example charts highlight key comparisons and metrics.

How to Build a Dashboard

Now that we’ve looked at a great example and taken note of the key elements, it’s time to see how to make our own. If you don’t want to build a spreadsheet from scratch, you can download a template like the one above and enter your data. However, it’s important to remember that the metrics there might not perfectly match your needs, and it’s always a good idea to understand how to set up your calculations from scratch.

Great news! You don’t need to be an Excel wizard to do this on your own. In fact, almost all of the calculations used in the dashboard above are very simple and don’t involve complex formulas. As long as you have accurate data to work with and you know what you’re looking for, you’re on your way.

Here are some tips to help you along the way. Watch the video below for additional details on using excel.

• Double-check your manually entered data (take a tip from the example and highlight those fields too!)
• Utilize the correct number format for each field
• Have a plan! Know what metrics you need beforehand, and choose your KPIs strategically before you start building a dashboard.
• Don’t bury the formula! Make it easy to understand how each cell is calculated.