Graphed LogoGraphed
Login

How to Create an Employee Dashboard in Excel with ChatGPT

Cody Schneider•

Thinking about building an employee dashboard in Excel can feel like a huge undertaking, but using a tool like ChatGPT can turn hours of formula fumbling into a few simple prompts. This guide will walk you through setting up your data, asking ChatGPT for the right formulas and chart recommendations, and putting together a functional and interactive HR dashboard. We'll build a complete dashboard from a blank spreadsheet, step-by-step.

What is an Employee Dashboard, Anyway?

An employee dashboard is a one-page report that gives you a high-level view of your most important human resources metrics. Instead of digging through huge spreadsheets, you get a clear, visual summary of things like headcount, performance ratings, and salary distributions. It helps you spot trends, make informed decisions, and answer questions from leadership without having to manually pull new data every single time.

The main goal is to turn raw data into meaningful insights you can act on quickly.

Step 1: Get Your Raw Data Organized

Before you can build anything, you need a solid foundation. Both Excel and ChatGPT work best with clean, structured data. Trying to build a dashboard from a messy spreadsheet is a recipe for frustration.

Create a new tab in your Excel workbook and name it something like "RawData". Your data should be in a simple table format. Each column represents a specific attribute (like name or department), and each row represents a single employee. Format this range as an Excel Table by selecting any cell within your data and pressing Ctrl + T. Name your table "EmployeeData" in the "Table Design" tab that appears.

Here’s a sample structure to get you started:

  • EmployeeID: A unique identifier for each person.

  • FullName: The employee's full name.

  • HireDate: The date they started.

  • Department: (e.g., Sales, Marketing, Engineering)

  • JobTitle: Their specific role.

  • Salary: Annual salary figure.

  • PerformanceRating (1-5): Most recent performance score.

  • TrainingHours: Total training hours completed this year.

A well-organized table is the most important part of this entire process. Good data in means good data out.

Step 2: Use ChatGPT to Calculate Key HR Metrics

Next, let's calculate the core metrics for our dashboard. Instead of trying to remember complex formulas, we’ll ask ChatGPT to generate them for us. Open up a second sheet in your workbook and name it "Calculations." This is where our primary formulas will live.

Finding Total Headcount

Let's start with a simple one: the total number of employees. Give ChatGPT this prompt:

“I have an Excel Table named ‘EmployeeData’. What formula can I use to count the total number of rows, which represents my total employee headcount?”

ChatGPT will likely give you this formula:

=ROWS(EmployeeData)

In your "Calculations" worksheet, type "Total Employees" in cell A1 and paste this formula into cell B1. Now you have a live count of your staff.

Calculating Average Performance Rating

Now, let's find the average performance rating across the whole company.

Your prompt:

“In my Excel Table 'EmployeeData', I have a column named '[PerformanceRating (1-5)]'. What's the formula to calculate the average of all numbers in that column?”

The response should be:

=AVERAGE(EmployeeData[PerformanceRating (1-5)])

Label cell A2 as "Avg. Performance Rating" and put this formula in B2.

Getting Department-Specific Headcounts

This is where things get more interesting. We want to see how many employees are in each department. A Pivot Table is best, but first, let's try a dynamic formula approach.

Your prompt:

“My ‘EmployeeData’ table has a column called ‘Department’. I want to count how many employees are in the 'Sales' department. Can you give me the formula for that?”

ChatGPT will give you the COUNTIF formula:

=COUNTIF(EmployeeData[Department], "Sales")

You can use this formula in your "Calculations" sheet for each department (e.g., 'Sales', 'Marketing', 'Engineering') to get a quick summary. This will be the data source for your first chart.

Step 3: Creating Pivot Tables for Deeper Analysis

While individual formulas are useful, PivotTables are the true power behind any great Excel dashboard. They allow you to summarize and group huge amounts of data without writing complex formulas. ChatGPT can’t build the PivotTable for you, but it can give you precise, step-by-step instructions.

Ask ChatGPT:

“I have an Excel table named ‘EmployeeData’. Can you give me the step-by-step instructions to create a PivotTable that shows the total employee count, average salary, and average performance rating, broken down by department?”

ChatGPT will guide you through the process:

  • Click any cell inside your data on the "RawData" sheet.

  • Go to the Insert tab on the Ribbon and click PivotTable.

  • Excel will automatically select your table (‘EmployeeData’) and recommend placing the PivotTable in a new worksheet. Click OK.

  • A "PivotTable Fields" pane will appear on the right side. Now, drag and drop the fields:

    • Drag Department into the Rows area.

    • Drag EmployeeID into the Values area. It will likely default to 'Sum of EmployeeID'. Click on it, choose 'Value Field Settings', and change it to Count. Rename it 'Employee Count'.

    • Drag Salary into the Values area. Change its setting to Average and format as currency.

    • Drag PerformanceRating (1-5) into the Values area. Change its setting to Average and format to one decimal place.

After following these steps, you'll have a summarized table ready to power your dashboard's visuals. Rename this sheet "PivotData".

Step 4: Designing and Building the Dashboard

Now for the fun part. Create a new sheet and name it "Dashboard". This sheet is your canvas. A good practice is to use a dark grey or another soft color for the background to make your charts and numbers pop.

Create Summary Cards (KPIs):

At the top of your dashboard, you'll want some headline numbers, often called Key Performance Indicators (KPIs). These are the metrics we calculated back in Step 2.

  1. Create a few shapes (like rounded rectangles) using the Insert > Shapes menu. These will be the background for our KPIs.

  2. Add a text box on top of the first shape.

  3. Click on the text box, then go to the formula bar and type =.

  4. Navigate to your "Calculations" worksheet and click on the cell containing your "Total Employees" count (cell B1). Press Enter.

  5. Do not type the equals sign inside the text box itself — it must be in the formula bar.

  6. Style the text to be large and bold. Now, this number will update automatically whenever your raw data changes.

  7. Repeat this process for your "Avg. Performance Rating."

Step 5: Visualizing Your Data with Charts

A wall of numbers is hard to interpret. Charts make the data easy to understand at a glance. We'll use our PivotTable as the source for these charts and guide you through chart choices.

Headcount by Department Chart

Your prompt to ChatGPT:

“What is the best Excel chart type to show a comparison of employee counts across different departments? My data is in a Pivot Table with departments as rows and 'Employee Count' as values.”

ChatGPT will most likely recommend a Bar Chart or a Column Chart, as they are perfect for comparing categories.

How to create it in Excel:

  • Go to your "PivotData" sheet and click anywhere inside your PivotTable.

  • Go to the Insert tab and click PivotChart.

  • Choose a Clustered Column or Clustered Bar. A Bar Chart (with horizontal bars) often works better if department names are long.

  • This will create a chart tied to your PivotTable. Cut (Ctrl + X) the new chart and paste (Ctrl + V) onto your "Dashboard" sheet.

  • Clean it up! Remove unnecessary elements like the legend and the field buttons (right-click and select 'Hide all field buttons on chart'). Give it a descriptive title like "Headcount by Department."

Average Salary by Department Chart

Follow similar steps for your average salary data. Select the PivotTable, insert a new PivotChart, and choose a chart type. A bar or column chart works well here to compare salary figures for each department. Cut and paste onto your dashboard and style to match.

Step 6: Make Your Dashboard Interactive with Slicers

Slicers are interactive filters that make your dashboard dynamic. With one click, your team can filter the entire dashboard to look at data for just one department or job title.

Your prompt to ChatGPT:

“I have a dashboard in Excel with several PivotCharts all based on the same PivotTable. How can I add a slicer to filter all the charts at once by Department?”

ChatGPT will tell you to:

  • Click on any PivotChart on the dashboard.

  • Go to the PivotChart Analyze or PivotTable Analyze tab on the Ribbon.

  • Click Insert Slicer.

  • Check the box for Department (and any other fields like 'JobTitle'). Click OK.

  • A slicer panel for 'Department' will appear. You can move and resize it. When you click a department name in the slicer, all charts connected to that data source will filter accordingly.

This simple addition transforms your static report into a powerful analytical tool anyone can use to explore the data.

Final Thoughts

By pairing the organizing power of Excel with the conversational intelligence of ChatGPT, you can build a powerful employee dashboard without needing deep technical skills. You can ask for formulas, get step-by-step guidance for PivotTables, and receive advice on visualization, which speeds up the process dramatically.

Building dashboards this way cuts down manual research, but it still requires you to assemble everything. At Graphed, we automate this entire workflow. Connect your data sources—whether Excel, Google Sheets, or your HR platform—and describe the dashboard you want in plain English. We instantly generate a complete, live dashboard with charts and metrics, eliminating the need to manually handle PivotTables and cell references.