How to Create an IT Dashboard in Excel with ChatGPT

Building a dashboard to track your IT performance doesn't have to be a monumental task reserved for data scientists. By combining the familiar power of Excel with the conversational smarts of ChatGPT, you can create a simple but powerful IT dashboard to monitor key metrics. This article will walk you through preparing your data, crafting the right prompts, and generating the charts and formulas needed to bring your dashboard to life.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

First, Decide What to Track

An effective dashboard tells a story at a glance. Before you touch any data, you need to know which characters are in that story. For an IT dashboard, you're monitoring the health and efficiency of your systems and support processes. Trying to track everything will only create noise, so focus on a few key metrics that matter most.

Key IT Metrics for Your Dashboard

Here are a few common and high-impact metrics that are perfect for a starter dashboard:

• Help Desk Ticket Volume & Resolution Time: This is the classic measure of your support team’s workload and efficiency. You can break it down by priority (High, Medium, Low) to see if urgent issues are being handled quickly.
• System Uptime: What percentage of the time are your critical systems operational? This is a fundamental measure of reliability. Even tracking this for a main website or internal server is a great start.
• First-Contact Resolution (FCR): How many support tickets are solved with a single interaction? A high FCR means your team is effective, and your users are getting their problems fixed without a long back-and-forth.
• Security Incidents: Simply tracking the number of security-related events (like phishing attempts reported or malware detected) can provide a baseline for your security posture.

For this tutorial, we will focus on building visuals for help desk tickets and system uptime using a sample dataset.

Preparing Your Data for Success

ChatGPT is incredibly smart, but it's not a mind reader. Its ability to give you useful code and formulas depends entirely on the quality and structure of the data you provide. The phrase "garbage in, garbage out" has never been more true.

The Golden Rule: Clean, Tidy Data

Before you even think about writing a prompt, get your data into a "tidy" format. This just means organizing it into a simple, predictable table:

• Each column represents a single variable (e.g., Ticket ID, Status, Priority, Resolution Time).
• Each row represents a single observation (e.g., a single help desk ticket).
• Keep headers clear and simple (use "Resolution_Time_Hours" instead of "How long did it take us to fix this thing?").

Here is an example of a good, clean dataset for IT tickets you might export from your help desk software into a CSV or Excel sheet. We’ll use this as our foundation.

Ticket_ID,Date_Created,Status,Priority,Category,Resolution_Time_Hours
101,2023-10-01,Closed,High,Hardware,2
102,2023-10-01,Closed,Medium,Software,8
103,2023-10-02,Open,High,Network,
104,2023-10-03,Closed,Low,Access,26
105,2023-10-03,Closed,Medium,Software,12
106,2023-10-04,In Progress,High,Network,
107,2023-10-05,Closed,Low,Access,48
108,2023-10-05,Closed,High,Hardware,4
...and so on.

With clean data like this, you’re ready to start building.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Building Your IT Dashboard in Excel with ChatGPT: A Step-by-Step Guide

We’ll build our dashboard piece-by-piece, using ChatGPT to do the heavy lifting. Open a new Excel workbook, place your clean data into a worksheet named "Data," and create a second, blank worksheet named "Dashboard."

Step 1: Summarize Your Data with Questions

Raw data is too detailed for a dashboard. First, we need to generate summaries. This is where ChatGPT shines. Copy a small sample of your data (around 10-20 rows is fine) and paste it into ChatGPT as context.

Example Prompt for Summarization:

"I have this sample IT ticket data. First, give me a simple table that shows the total count of tickets for each 'Status' (Closed, Open, In Progress). Then, create another table showing the average 'Resolution_Time_Hours' for each 'Priority' level."

ChatGPT will process your request and give you back formatted tables, like this:

Ticket Status Count:

Average Resolution Time by Priority:

Now you have the summarized data you need for your charts. Copy these simple tables into your "Dashboard" worksheet in Excel.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 2: Generate VBA Code for Charts

Instead of manually creating charts in Excel, you can have ChatGPT write the VBA code to do it for you. This is surprisingly powerful and fast.

Let's make a bar chart for our average resolution times. Let’s assume you pasted the resolution time summary table into cells A1:B4 of your "Dashboard" sheet.

Example Prompt for VBA Chart Code:

"Excellent. Now write me a complete Excel VBA macro script that creates a column chart based on the 'Average Resolution Time by Priority' data. Use the data in cells A1:B4 on the worksheet named 'Dashboard'. The chart title should be 'Average Resolution Time by Priority (Hours)' and it should not have a legend."

ChatGPT will generate a block of VBA code:

Sub CreateResolutionTimeChart()
    Dim ws As Worksheet
    Dim cht As Chart
    Dim rng As Range

    ' Set the worksheet to your Dashboard
    Set ws = ThisWorkbook.Sheets("Dashboard")

    ' Set the data range for the chart
    Set rng = ws.Range("A1:B4")

    ' Create a new chart object
    Set cht = ws.Shapes.AddChart2(227, xlColumnClustered).Chart

    ' Set the source data for the chart
    ' The plot by argument is important in this type of data
    cht.SetSourceData Source:=rng, PlotBy:=xlColumns

    ' Set chart title
    cht.HasTitle = True
    cht.ChartTitle.Text = "Average Resolution Time by Priority (Hours)"

    ' Remove the Legend
    cht.HasLegend = False

End Sub

Don't be intimidated by the code. You just need to know how to use it:

1. In Excel, press Alt + F11 to open the VBA editor.
2. Go to Insert > Module to create a new module.
3. Copy and paste the entire code block from ChatGPT into the module window.
4. Press F5 to run the code.

Go back to your Excel window, and you’ll see a brand new, perfectly formatted bar chart on your dashboard sheet. Drag it into position.

Step 3: Repeat for More Visuals

The process is the same for other visuals. Let's create a pie chart for the ticket status distribution. Assume you’ve placed the status count data in cells D1:E4.

Example Prompt for Pie Chart:

"Thanks! Now write another VBA macro to create a pie chart from the 'Ticket Status Count' data located in cells D1:E4 of the 'Dashboard' sheet. The title should be 'Ticket Status Distribution'."

Paste the new Sub into the same module (below the first one), place your cursor inside the new sub, and press F5 to run it. You now have a pie chart to place alongside your bar chart.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 4: Create Simple Key Performance Indicators (KPIs)

Dashboards often feature big-number callouts for critical metrics. Let's create one for system uptime. This time, we'll ask for an Excel formula instead of a VBA script.

Example Prompt for a KPI Formula:

"I need an Excel formula for a KPI. I want to calculate my system uptime percentage. I'll put the total number of minutes in a month in cell G1 and the total minutes of downtime in cell G2. Give me a formula for cell G3 to show the uptime percentage, formatted nicely."

ChatGPT will provide the formula and an explanation:

The formula for cell G3 would be:

=(G1 - G2) / G1

After entering the formula, format cell G3 as a Percentage to display it correctly (e.g., 99.95%). You can then increase the font size and make it bold to act as a KPI card on your dashboard.

Step 5: Arrange and Design Your Dashboard

The final step is creative. Move your charts and KPIs around on your "Dashboard" sheet. Add headers, format your cells with background colors, and adjust chart sizes to create a clean, organized view. You now have a functional, data-driven IT dashboard built with Excel and a little AI help!

Advanced Tips for Better Prompts

• Provide Context: Tell ChatGPT about your data. For example, "This column represents time in hours" or "A ticket with a 'high' priority is the most urgent." The more context it has, the better its suggestions will be.
• Be Explicit About Your Goal: Instead of "make a chart," say, "Create a line chart showing the number of tickets created per day to spot trends over time."
• Iterate and Refine: Your first prompt may not be perfect. You can follow up with refinements like, "Can you change the above VBA code to make the bars green?" or "Update that formula to handle cases where there is zero downtime without an error."

Final Thoughts

Pairing Excel with ChatGPT lets you quickly transform raw data into a visual IT dashboard without needing deep technical expertise in Excel formulas or VBA scripting. By preparing clean data and asking clear, specific questions, you can generate summaries and charts that give you an immediate understanding of your team's performance. It’s a powerful way to make an impact without a month-long training course.

Of course, this manual process of exporting CSVs, pasting data into ChatGPT, and running code is powerful but has its limits. The data is static until you repeat the entire workflow. That’s actually why we built Graphed. We connect directly to your tools like help desk services and databases, so your dashboards are always live and update in real-time. Since we handle the data connection, you can simply ask questions in plain English like, "show me a dashboard of IT support ticket trends for this quarter" and a fully interactive dashboard is created in seconds, no CSVs or code required.