How To Use ChatGPT To Make An Interactive Excel Dashboard

Download FREE Dashboard and Project Files Here 👇
Dashboard:
https://docs.google.com/spreadsheets/...

Prompts:
https://docs.google.com/document/d/1G...

In this tutorial, we will learn how to use ‪@OpenAI‬'s ChatGPT and Claude AI to make an interactive excel dashboard.

First of all, we will analyze the data and decide how to design the dashboard layout. Next, we will use create dashboard layout using excel shapes or cards. Then, we will summarize and group the data using excel pivot tables.

After this, we will build charts, graphs and visuals for our dashboard. And finally, we will use pivot table slicers to make the dashboard fully dynamic.

But instead of doing it manually, we will use AI tools to generate VBA code and automate the entire process.

#excel #exceltutorial #excelautomation #chatgpt

Chapters:
0:00 - What You'll Learn and Steps
0:52 - Analyzing Dataset
2:31 - Dashboard Layout (ChatGPT)
6:03 - Dashboard Pivot Tables (ChatGPT)
9:51 - Dashboard Charts/Graphs (Claude AI)
13:14 - Making Dashboard Interactive / Dynamic
14:21 - Conclusion


Prompts Used In This Tutorial:

(a) Prompt 1 - ChatGPT
I want to build an excel dashboard. I have provided you the sample data. Carefully analyze this data and suggest what information we should display on the dashboard and how? I want to display some key numbers as well as different graphs that makes it easier for the audience to quickly visualize key metrics and trends.

(b) Prompt 2 - ChatGPT
I want to create cards in excel on which I will place the charts to create dashboard.
Use “Rounded Rectangle” inside excel [Insert Shapes] menu to create cards as follows;
Create cards 1,2,3,4,5,6 horizontally side by side in a single row.
Card 1: Height 0.75 Inch Width 1.65 inch
Card 2: Height 0.75 Inch Width 2.0 inch
Card 3: Height 0.75 Inch Width 2.0 inch
Card 4: Height 0.75 Inch Width 2.0 inch
Card 5: Height 0.75 Inch Width 3.8 inch
Card 6: Height 5.30 Inch Width 2.5 Inch
Create cards 7,8,9 below cards 1,2,3,4,5 horizontally side by side in a single row.
Card 7: Height 2.1 Inch Width 6.6 inch
Card 8: Height 2.1 Inch Width 2.6 inch
Card 9: Height 2.1 Inch Width 2.6 inch
Create cards 10,11,12 below cards 7,8,9 horizontally side by side in a single row.
Card 10: Height 2.1 Inch Width 4.1 inch
Card 11: Height 2.1 Inch Width 2.5 inch
Card 12: Height 2.1 Inch Width 5.0 inch
Rename Cards:
Using “Selection Pane” menu, rename each card as follows;
Card1 = cfilters
Card2 =ctsales
Card3 =ctmargin
Card4 =cpmargin
Card5 = ccustcount
Card6 = ctop10
Card7 = csalestrend
Card8 = ccustsource
Card9 = csalescity
Card10 = csalesservice
Card11 = cdeptmargin
Card12 = cnewrepeat
Sheet Formatting:
Remove all gridlines from entire sheet and fill entire sheet with rgb 217,217,217 color.
Ignore print area / margins and let the cards overlap.
Leave row 1 – 4 and start creating cards from row 5.
To do:
Write me a VBA code so I can put it into the module and click the button to generate these cards.

(c) Prompt 3 - ChatGPT
I am building an excel dashboard and I have the dataset on the sheet named “Data”. Inside this I have a table named “salesdata”.
I have created another blank sheet named “Pivot”.
You need to use the table “salesdata” inside “Data” sheet and create the following pivot tables for me.
Table 1: Place “Sales Amount” in values field. Rename the pivot table to “totalsales”.
Table 2: Place “Margin Amount” in values field. Rename the pivot table to “totalmargin”.
Table 3: Place “Sale Type” in Row field and “Customer Name” in values vield. Rename the pivot table to “customerscount”.
Table 4: Place “Year” and “Month” in row field and “Sales Amount” in the values field. Rename the pivot table to “salestrend”.
Table 5: Place “Year” in row field, “Customer Source” in column field and “Sales Amount” in the values field. Rename the pivot table to “customersource”.
Table 6: Place “City” in row field and “Sales Amount” in the values field. Rename the pivot table to “salesbycity”.
Table 7: Place “Customer Name” in row field and “Sales Amount” in the values field. Rename the pivot table to “top10”.
Table 8: Place “Service” in row field and “Sales Amount” in the values field. Rename the pivot table to “salesbyservice”.
Table 9: Place “Department” in row field and “Margin Amount” in the values field. Rename the pivot table to “departmentmargin”.
Table 10: Place “Year” and “Month” in row field, “Sale Type” in column field and “Sales Amount” in values field. Rename the pivot table to “newvsrepeat”.
Write VBA to create pivot tables that I can insert in module and click the button to generate pivot tables.
Create all these pivot tables on the existing sheet named “Pivot”. Get all the data from the sheet named “Data”.