Excel Tutorial: How to Build an Expense Category KPI Button (Step-by-Step Guide)

In this tutorial, I will guide you on creating an interactive KPI button in Excel to track expenses across different categories. The main goal is to build a KPI button that cycles through four main expense categories—Food, Utilities, Shopping, and Travel—and dynamically calculates the total amount spent in each category. Additionally, the highest expense category will be highlighted in green using conditional formatting to quickly identify areas with the highest spending.

Table of Contents Goal 1 Categories and Functions. 1 Step 1: Create Sample Data Table. 1 Step 2: Create Toggle Cell for KPI Button.. 2 Step 3: KPI Label 3 Step 4: KPI Result Formula. 3 Step 6: Add Macro to Cycle Categories. 4 Step 7: Conditional Formatting (Highest Expense) 4 Step 8: Test KPI Button.. 4 Step 9: Optional Enhancements. 5

 

Goal

The goal of this tutorial is to provide you with a step-by-step process to create a dynamic KPI button that can cycle through various expense categories. You will learn to use Excel functions such as SUMIFS, CHOOSE, and SEQUENCE, and apply conditional formatting to highlight the highest category automatically. By following this tutorial, you will have a fully functional expense tracking KPI button that updates dynamically when interacting with a button in Excel.

Categories and Functions

The categories we will track are Food, Utilities, Shopping, and Travel. The main Excel functions used are SUMIFS for calculating category totals, CHOOSE for selecting the active category, and SEQUENCE for optional dynamic array calculations. Conditional formatting (CF) will be applied to highlight the highest expense category in green, making it easy to identify significant spending.

Step 1: Create Sample Data Table

To begin, open a new Excel file and create a sample data table to work with. Follow these steps:

• Open a new Excel file.
• Create headers in cells A1 to D1 as follows: Date, Category, Amount, Remarks.
• Enter sample expense entries in the table starting from row 2 to row 9. Here is an example dataset:

• Once all data is entered, convert the range into a Table using Ctrl+T. This allows for dynamic referencing and makes formulas easier to manage.

Step 2: Create Toggle Cell for KPI Button

Next, we need a toggle cell that will allow the KPI button to cycle through the expense categories:

• In cell F1, enter the number 1. This will serve as the toggle cell.

• 1 represents Food
• 2 represents Utilities
• 3 represents Shopping
• 4 represents Travel
• Format cell F1 with bold text and a border to make it easily visible. This cell will dynamically change when the KPI button is pressed.

Step 3: KPI Label

To display the currently active category, we will use the CHOOSE function:

• In cell F2, enter the formula: =CHOOSE(F1, "Food", "Utilities", "Shopping", "Travel")

• Press Enter. This formula will automatically update the label in F2 whenever the value in F1 changes. F2 will now display the active category that is being analyzed by the KPI button.

Step 4: KPI Result Formula

Now we will calculate the total expenses for the active category using the SUMIFS function:

• In cell F3, enter the formula: =SUMIFS(C2:C16, B2:B16, CHOOSE(F1, "Food", "Utilities", "Shopping", "Travel"))

• Press Enter. Cell F3 now shows the total expense amount for the category currently selected in F1.

For an optional dynamic version using SEQUENCE and INDEX, you can use: =SUMIFS(C2:C16, B2:B16, INDEX({"Food","Utilities","Shopping","Travel"}, F1))

This approach allows the formula to dynamically pick the category based on the toggle cell.

Step 5: Add Button (Form Control)

To make the KPI interactive, we will add a button:

• Enable the Developer tab in Excel by going to File → Options → Customize Ribbon → Check Developer.

• Go to the Developer tab, click Insert, and select a Form Control Button.

• Place the button near cells F1 to F3.
• Name the button “Next Category” for clarity.

This button will be used to cycle through categories when clicked.

Step 6: Add Macro to Cycle Categories

To make the button functional, we need a simple VBA macro:

• Press Alt+F11 or "Right Click on Sheet" to open the VBA editor.
• Insert a new Module.

• Paste the following code:

"Sub CycleExpenseKPI()

With ThisWorkbook.Sheets("Sheet1") ' Change sheet name if needed

If .Range("F1").Value = 4 Then

.Range("F1").Value = 1

Else

.Range("F1").Value = .Range("F1").Value + 1

End If

End With

End Sub"

 

• Save the workbook as a Macro-Enabled Workbook (.xlsm).
• Assign the macro to the button by right-clicking it, selecting Assign Macro, and choosing CycleExpenseKPI.

Now, each click of the button will cycle F1 from 1 to 4, updating the KPI label and result automatically.

Step 7: Conditional Formatting (Highest Expense)

To highlight the highest expense in green:

• Select the Amount column, C2 to C16.

• Go to Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.

• Enter the formula: =C2=MAX(C$2:C$16)

• Click Format, choose a green fill with white font, and click OK.
• Click OK again. The highest amount in the table is now highlighted in green, providing a visual cue of the largest expense.

Step 8: Test KPI Button

Test the functionality of your KPI button:

• Click the “Next Category” button.
• Observe that cell F1 cycles through 1, 2, 3, 4, and back to 1.
• F2 updates to show the currently active category.
• F3 updates to display the total expenses for the selected category.
• The highest amount in column C remains highlighted in green, regardless of the category selected.

This dynamic interaction allows you to quickly review spending across different categories.

Step 9: Optional Enhancements

To make the KPI more visually appealing and insightful, consider the following enhancements:

• Add a dynamic bar chart for total expenses per category, which can visually show the distribution of spending.
• Style F2 and F3 as a KPI card by merging cells, applying bold text, and adding background colors for better visual appeal.
• If using a table, use structured references like Table1[Amount] to make formulas more readable and maintainable.
• Display the percentage of total expenses for each category using a formula like F3/SUM(C2:C16) for additional insights.

By following these steps, you now have a fully functional Expense Category KPI Button in Excel. This setup uses SUMIFS, CHOOSE, and SEQUENCE functions effectively while applying conditional formatting to highlight the highest expense category. The interactive button allows users to cycle through categories effortlessly, making expense tracking more intuitive and visually informative.