How to Create a Service Desk Dashboard in Google Sheets with ChatGPT

Trying to make sense of your service desk performance can feel like wrestling with a firehose of tickets, statuses, and agent assignments every day. You know the answers are buried in your support software, but getting them into a clear, shareable format usually means spending hours fighting with CSV exports and complex spreadsheet formulas. This guide will show you how to skip the headache by using the familiar interface of Google Sheets and the power of ChatGPT to build a functional, insightful service desk dashboard without needing to be a formula wizard.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

We'll walk through organizing your data, creating prompts that get ChatGPT to write the exact formulas you need, and arranging everything into a dashboard that gives you an at-a-glance view of what’s really happening.

First, Get Your Data House in Order

Before you can build anything useful, you need a solid foundation. In data analysis, there's a timeless saying: "garbage in, garbage out." If your data is messy and inconsistent, your dashboard will be too. Setting this up correctly from the start will save you countless hours of troubleshooting later.

Why Start with Google Sheets?

While dedicated BI tools are powerful, they often come with a steep learning curve and a high price tag. Google Sheets is a fantastic starting point because:

• It's Free and Accessible: Pretty much everyone has access to it and knows the basics.
• It's Collaborative: Easily share your dashboard with your team for feedback or to delegate updates.
• It's Flexible: It can handle a surprising amount of data analysis and visualization.

Standardize Your Raw Data

The goal is to have one sheet that serves as your "single source of truth." Create a new Google Sheet and dedicate the first tab to your raw data. Name it something simple like 'RawData'.

Next, you'll need to export your ticket data from your service desk platform (like Jira Service Management, Zendesk, Freshdesk, etc.), likely as a CSV file. Import this data into your 'RawData' tab. Your sheet should look like a clean, structured table with clear headers. Make sure you have at least these essential columns:

• Ticket ID: A unique identifier for each ticket.
• Date Created: When the ticket was submitted.
• Date Resolved: When the ticket was closed. Can be blank for open tickets.
• Agent: The team member assigned to the ticket.
• Category: The type of issue (e.g., "Hardware," "Software," "Password Reset").
• Priority: The urgency level (e.g., "High," "Medium," "Low").
• Status: The current state of the ticket (e.g., "Open," "In Progress," "Closed").
• CSAT Score: The customer satisfaction score, often on a 1-5 scale, if you collect it.

Pro Tip: Keep this 'RawData' tab pure. Don't add formulas or weird formatting here. All your calculations will live on your dashboard tab, pulling from this clean source.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Use ChatGPT as Your Expert-Level Formula Assistant

Now for the fun part. Instead of searching Google for obscure formula syntax, we're going to ask ChatGPT to write them for us. The key is to be clear and specific in your prompts. A well-crafted prompt gives you the perfect formula on the first try.

How to Write an Effective Prompt for Spreadsheet Formulas

Treat ChatGPT like a new analyst who needs a bit of direction. Always provide three pieces of context:

1. The Goal: What are you trying to calculate? (e.g., "I want to count the number of open tickets.")
2. The Location of the Data: Which sheet and columns are involved? (e.g., "My data is on a sheet named 'RawData'. The ticket status is in Column G.")
3. The Desired Output: Specifically ask for the Google Sheets formula. (e.g., "Give me the Google Sheets formula to do this.")

Let's use this method to build out the core metrics for our dashboard. We’ll create a new tab in your sheet and call it ‘Dashboard’.

Example #1: Counting Total Open Tickets

This is a fundamental metric for any service desk. It tells you the immediate workload your team is handling.

Your Prompt to ChatGPT:

"I am building a service desk dashboard in Google Sheets. My raw data is in a tab called 'RawData'. I want to count the total number of tickets that have the status 'Open'. The status for each ticket is in Column G. What formula should I use?"

ChatGPT’s Likely Formula:

=COUNTIF('RawData'!G:G, "Open")

Putting it to Use:

In your 'Dashboard' tab, pick a cell (like B2). Type "Total Open Tickets" in cell A2, and paste this formula into B2. You'll instantly see the total count.

Example #2: Average Ticket Resolution Time

This KPI is crucial for understanding team efficiency. Calculating intervals between two dates can be tricky, especially if you only want to count business days. This is a perfect task for ChatGPT.

Your Prompt to ChatGPT:

"In my 'RawData' sheet, the ticket creation date is in Column B, and the resolution date is in Column C. I need a formula that calculates the average number of business days it takes to resolve a ticket. The formula should only consider rows where a resolution date exists and ignore empty cells in Column C."

ChatGPT’s Likely Formula:

=ARRAYFORMULA(AVERAGE(IF(ISNUMBER('RawData'!C2:C), NETWORKDAYS('RawData'!B2:B, 'RawData'!C2:C), "")))

What this formula does: NETWORKDAYS calculates the working days between two dates. Wrapping it in ARRAYFORMULA and AVERAGE(IF...)) applies this logic down the entire column and then computes the average, skipping unresolved tickets. Paste this into your dashboard, and you’ll have a powerful efficiency metric.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Example #3: Creating a Summary Table of Tickets by Category

A simple count is good, but a breakdown by category tells you where your team is spending its time. This is where a QUERY formula comes in handy, which is incredibly powerful but has a syntax that's tough to remember. No need to memorize it.

Your Prompt to ChatGPT:

"Using my 'RawData' sheet, I want to create a two-column summary table. The first column should list the unique ticket categories from Column E. The second column should show the count of tickets for each category. Give me a single Google Sheets formula to do this."

ChatGPT’s Likely Formula:

=QUERY('RawData'!E:E, "SELECT E, COUNT(E) WHERE E IS NOT NULL GROUP BY E LABEL COUNT(E) 'Total Tickets'")

Putting it to Use:

Paste this single formula into a cell on your dashboard (maybe A10), and it will automatically generate the entire table for you. This table can then be the data source for a pie chart, which we'll create next.

Step 3: Bringing Your Dashboard to Life with Charts

Now that you have your key metrics calculated, it's time to visualize them. A good dashboard communicates information quickly and requires very little explanation.

Set Up Your Dashboard Layout

Organize your 'Dashboard' tab for clarity. A common layout is to have "scorecards" — large, prominent numbers — at the top for your most important metrics, followed by charts and tables below for more detailed breakdowns.

1. Create Your Scorecards

You’ve already calculated the numbers for your scorecards (like "Total Open Tickets" and "Average Resolution Time"). Now, just make them stand out.

• Select the cells containing your formulas (B2, B3, etc.).
• Increase the font size significantly (e.g., to 48pt).
• Center the text and give it a bold weight.
• Give each scorecard a clear label in the cell next to it or above it.

2. Build a Pie Chart for Ticket Categories

A pie chart is perfect for showing a parts-to-whole relationship, making it ideal for visualizing tickets by category.

• Highlight the summary table that your QUERY formula produced (e.g., A10 through B15).
• Go to the menu and click Insert > Chart.
• Google Sheets will likely default to a pie chart. If not, you can change it in the Chart Editor on the right.
• Give your chart a clear title like "Tickets by Category."

You now have a dynamic chart. When you add new data to your 'RawData' tab, both the summary table and the pie chart will update automatically.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

3. Create a Bar Chart for Agent Performance

Let's use the same QUERY trick to build an agent leaderboard showing how many tickets each person has closed.

Your Prompt to ChatGPT:

"I want to create a summary table from my 'RawData' sheet showing the total count of 'Closed' tickets for each agent. The agent names are in Column D and the status is in Column G. List the agents and their closed ticket counts."

ChatGPT's Likely Formula:

=QUERY('RawData'!D:G, "SELECT D, COUNT(D) WHERE G = 'Closed' GROUP BY D LABEL COUNT(D) 'Closed Tickets'")

Paste this into your dashboard. Then, just like before:

• Highlight the generated table.
• Click Insert > Chart.
• Choose a bar chart or column chart in the Chart Editor.
• Title it "Closed Tickets by Agent."

Final Thoughts

By following this process, you've created a genuinely useful service desk dashboard in Google Sheets. You've structured your data for analysis, successfully prompted ChatGPT to handle the heavy lifting of formula creation, and translated those numbers into clear, accessible visualizations. This approach empowers you to build reports that previously seemed out of reach without needing to spend weeks learning complex functions.

The only remaining manual step in this workflow is repeatedly exporting data and pasting it into your 'RawData' tab. We created Graphed because we believe generating insights shouldn’t involve any manual data wrangling at all. Instead of copying and pasting CSVs, you can connect directly to your data sources (like Jira, Zendesk, HubSpot, and dozens more). Then, you just describe the dashboard you want in plain English — like "create a dashboard showing average ticket resolution time and tickets closed by agent this quarter" — and our AI builds a real-time, shareable dashboard for you instantly. No formulas, no spreadsheets, just answers.