Build an Advanced Excel Dashboard with Gemini AI for Expense Analysis |

In today's article, you'll learn how to use Gemini AI to create a working hours and expenses analysis dashboard using Microsoft Excel. This dashboard is designed to help you accurately track employee hours and manage expenses with clarity and precision.

Exploring the Dashboard's Powerful Features

Let's begin by exploring the powerful features built into this Excel template. You'll find a total budget allocated tracker that supports your financial planning efforts. The dashboard provides a summary of the total agreed hours with employees along with a calculation of the actual hours worked, giving you a clear percentage comparison between the two.

A dedicated section covers total OPEX invoice hours paid and the corresponding percentage of paid hours, ensuring full visibility into your operating expenditures. The template also includes a comprehensive analysis of extra and missing hours, showing both the extra paid amounts and hours, as well as the missing hours and their deducted amounts.

You'll gain a complete overview of your workforce through the employee analysis section, which displays the total number of employees and their gender distribution with percentages. To better understand payment trends, a donut chart visually compares paid versus unpaid salaries, clearly showing the percentages for each.

The dashboard also identifies the highest monthly expenses, helping you stay focused on the most critical cost drivers. A dynamic line chart allows you to compare actual working hours against invoiced hours over time, providing actionable insights into operational efficiency. For flexibility and data exploration, you can use slicers to filter and view details by employee name, making your analysis more focused and interactive. All of these components come together in one powerful and user-friendly Excel dashboard.

Building the Dashboard with Gemini AI

Let's start building this great dashboard with a big assist from Gemini AI. Take a look at the data set in front of us. We have a wide range of columns. This data might have been created manually or exported from a specific system. If we want to use this table as the foundation for our dashboard, we'll need to generate some additional values that aren't directly available. These values can only be calculated using Excel formulas.

Instead of spending time trying to figure out what formula to use or how to write it, we're going to let Gemini AI do the heavy lifting. With just a few clicks, we'll have the correct formulas generated in seconds.

To get started with Gemini, we'll copy a portion of this table, specifically the section starting from invoice January through to December final amount. This will help Gemini AI understand the structure of our data. Now, let's head over to Google Gemini AI and start a new chat. We'll ask it this simple question:

I need Excel formulas for total agreed hours and actual hours from this data table. Please note that invoice January is in column R.

As you can see, Gemini responds instantly with a working formula. Rather than thinking it through manually, you now have the correct syntax ready to copy. Just return to your Excel file, paste the formula into the appropriate column, press enter, and it's done. The formula will automatically apply across all rows. It's that fast and efficient.

Calculating Working Hours and Missing Hours

Now let's move to the actual working hours amount column. This one is quite simple. All we need to do is calculate it by multiplying the actual hours by the per-hourly rate.

Type the equal sign (=) in the cell.
Select the cell for actual hours.
Multiply by the per hourly rate.
Press Enter.

That's working great. So now let's move on to the next part: calculating the missing hours. Here's where things get a little tricky. We only want to show the number of missing hours if there are any. If an employee hasn't missed any hours, the cell should remain completely blank, not even a zero. To do that, we'll let Gemini AI handle it for us. So, I'll type this prompt into Gemini:

I need an Excel formula that shows the missing hours only if they exist. If there are no missing hours, I want the cell to be blank. And here's the relevant data set. For your reference, total agreed hours is in column BB.

As you can see, Gemini processes this almost instantly and gives us the formula we need. Just copy it, head back to Excel, and paste it into the missing hours column. The result will display the missing hours only if the agreed hours are more than the actual hours. And if not, the cell will stay clean and blank just the way we want it. That's another powerful assist from Gemini AI, making things faster, easier, and much smarter.

We can follow this same process to calculate all the remaining values we need for the dashboard.

Enhancing the Dashboard with Key Insights

Now, I want to take it a step further. I'll ask Gemini AI to analyze the data set and suggest the most important values and insights we should include in the final dashboard. To do this, I'll copy the entire data set and simply ask Gemini:

Based on this table, what are the key metrics and analyses I should include in an Excel dashboard?

As you can see, Gemini gives us a well-organized list of valuable insights and recommended metrics that would make the dashboard more meaningful and action-oriented. Now, together we'll begin applying those recommendations by creating the corresponding calculations and visuals inside Excel.

Using Pivot Tables for Centralized Data

Let's start by creating a new sheet and naming it pivot table sheet. This sheet will serve as a centralized space where we build all of our pivot tables and key summary tables. Having everything in one place will make it much easier to manage and update the dashboard source data later on.

Heading back to the data set, we'll select it, then go to the Insert tab and choose PivotTable. Once the table is created, we'll adjust the destination and place it directly inside our new pivot table sheet.

Now let's ask Gemini how to calculate the sum of total budget allocated using a pivot table. Gemini's answer is clear and straightforward:

Drag the total budget allocated field from the field list into the values area of the pivot table and Excel will automatically calculate the sum.

This method can be applied for other fields as well and it forms the foundation of our dashboard. With Gemini's help, we're speeding up every step of the process while making sure the analysis is accurate and focused. Let's continue creating the rest of the insights and bring this dashboard to life. We can calculate Total agreed hours and total actual hours simply by dragging the correct field names into the values section of the pivot table. It's fast, reliable, and ensures everything stays dynamic and easy to update.