, nnnnnnnnnnn 111,730 views Jan 14, 2025

Prompt 1 - ChatGPT ChatGPT

II wa nt to build an excel dashboard. I have provided you with 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.

Prompt 2: Create Dashboard Layout (Cards)

I want to create cards in excel on which I will place the charts to create a 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

Cards formatting:

- White Fill
- No Border
- Border radius = 0.03
- Margin Top, Bottom, Left, Right = 12 pixels.

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 the entire 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.

ChatGPT Prompt 3: Generate Pivot Tables for Dashboard

I am building an excel dashboard and I have the dataset on the sheet named “Data”. Inside this I have a table
named “ emissionsdata salesdata ”.

I have created another blank sheet named “Pivot”.

You need to use the " emissions data " table inside the table “ emissionsdata salesdata” inside “Data” sheet and
create the following pivot tables for me.

Pivot Table 1: Place “Sales Amount” in values field. Get “Sales Amount” from Column I in the
“ emissionsdata salesdata ” table inside the “Data” sheet. Rename the pivot table to “total Emissions sales ”.

Pivot Table 2: Place “Margin Amount” in values field. Get “Margin Amount” from Column K in the
“ emissionsdata salesdata ” table inside the “Data” sheet. Rename the pivot table to “totalmargin”.

Pivot Table 3: Place “Sale Type” in Row field and “Customer Name” in values vield. Get “Sale Type” from
Column L and “Customer Name” from Column D in the “ emissionsdata salesdata ” table inside the “Data” sheet.
Rename the pivot table to “customerscount”.

Pivot Table 4: Place “Year” and “Month” in row field and “Sales Amount” in the values field. Get “Year” from
Column C, “Month” from column B and “Sales Amount” from column I in the “ emissionsdata salesdata ” table
inside the “Data” sheet. Rename the pivot table to “salestrend”.

Pivot Table 5: Place “Year” in row field, “Customer Source” in column field and “Sales Amount” in the values
field. Get “Year” from Column C, “Customer Source” from Column M and “Sales Amount” from column I in the
“ emissionsdata salesdata ” table inside the “Data” sheet. Rename the pivot table to “customersource”.
Pivot Table 6: Place “City” in row field and “Sales Amount” in the values field. Get “City” from column H, “Sales
Amount” from column I in the “ emissionsdata salesdata ” table inside the “Data” sheet. Rename the pivot table
to “salesbycity”.

Pivot Table 7: Place “Customer Name” in row field and “Sales Amount” in the values field. Get “Customer
Name” from Column D, and “Sales Amount” from column I in the “ emissionsdata salesdata ” table inside the
“Data” sheet. Rename the pivot table to “top10”.

Pivot Table 8: Place “Service” in row field and “Sales Amount” in the values field. Get “Service” from column F
and “Sales Amount” from column I in the “ emissionsdata salesdata ” table inside the “Data” sheet. Rename the
pivot table to “salesbyservice”.

Pivot Table 9: Place “Department” in row field and “Margin Amount” in the values field. Get “Department” from
column G and “Margin Amount” from column K in the “ emissionsdata salesdata ” table inside the “Data” sheet.
Rename the pivot table to “departmentmargin”.

Pivot Table 10: Place “Year” and “Month” in row field, “Sale Type” in column field and “Sales Amount” in values
field. Get “Year” from Column C, “Month” from column B and “Sale Type” from column L in the
“ emissionsdata salesdata ” table inside the “Data” sheet. 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”.

- After creating first pivot table, start creating next pivot table after the gap of one row.

- I am using excel 2013 so please use supported objects, classes and properties.

- Name the sub GeneratePivotTables.

Prompt 4 (Claude AI): Generate Dashboard Charts.

I am building an excel dashboard and I have created cards using excel shapes on the sheet named
“Dashboard”.

ng the selection pane in format tab, I have named these cards as follows. All of these cards are on the sheet named “Dashboard”

ng the selection pane in format tab, I have named these cards as follows. All of these cards are on the sheet named “Dashboard”

ccustsource
csalescity
ctop10
csalesservice
cdeptmargin
cnewrepeat

On the sheet named “Pivot” I have pivot tables with following names;

salestrend
customersource
salesbycity
top10
salesbyservice
departmentmargin
newvsrepeat

You need to create the following charts using pivot tables on the sheet named “Pivot” and place
them on the shapes inside the “Dashboard” sheet as follows;
(a) Use pivot table named “salestrend” on “Pivot” sheet and create a “Line with Markers” chart with
drop lines. Then place this chart on the shape named “csalestrend” inside “Dashboard” sheet.
Specific Formatting for the chart
- Chart Line (Shape): fill color: RGB(0, 32, 96), width = 1.75 pt
- Line Marker: fill color = white, Border color = RGB(0, 32, 96) width = 1 pt, style: rounded.
- Resize and fit the chart based on the height and width of the shape named “csalestrend” as
follows: Width = “csalestrend” width * 90%, Height = “csalestrend” height* 80%

(b) Use pivot table named “customersource” on “Pivot” sheet and create a “100% Stacked Bar
Chart” Then place this chart on the shape named “ccustsource” inside “Dashboard” sheet.
Specific Formatting for the chart
- Add data labels.
- Apply varying blue colors to the chart shape.
- Resize and fit the chart based on the height and width of the shape named “ccustsource” as
follows: Width = “ccustsource” width * 90%, Height = “ccustsource” height* 80%

(c) Use pivot table named “salesbycity” on “Pivot” sheet and create a doughnut chart. Then place
this chart on the shape named “csalescity” inside “Dashboard” sheet.
Specific Formatting for the chart
- Add data labels.
- Doughnut explosion = 3%
- Apply varying blue colors to the chart shape.
- Resize and fit the chart based on the height and width of the shape named “csalescity” as follows:
Width = “csalescity” width * 90%, Height = “csalescity” height* 80%

(d) Use pivot table named “top10” on “Pivot” sheet and create a Clustered Bar Chart. Then place
this chart on the shape named “ctop10” inside “Dashboard” sheet.
Specific Formatting for the chart
- Chart Shape fill color: RGB(0, 32, 96)
- Resize and fit the chart based on the height and width of the shape named “ctop10” as follows:
Width = “ctop10” width * 90%, Height = “ctop10” height* 80%
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
(e) Use pivot table named “salesbyservice” on “Pivot” sheet and create a “3-D Clustered Column”
chart without perspective. Then place this chart on the shape named “csalesservice” inside
“Dashboard” sheet.
Specific Formatting for the chart
- Chart Shape fill color: RGB(0, 32, 96)
- Resize and fit the chart based on the height and width of the shape named “csalesservice” as
follows: Width = “csalesservice” width * 90%, Height = “csalesservice” height* 80%.

(f) Use pivot table named “departmentmargin” on “Pivot” sheet and create a “Simple Pie Chart”.
Then place this chart on the shape named “cdeptmargin” inside “Dashboard” sheet.
- Add data labels.
- Pie explosion = 3%
- Apply varying blue colors to the chart shape.
- Resize and fit the chart based on the height and width of the shape named “cdeptmargin” as
follows: Width = “cdeptmargin” width * 90%, Height = “cdeptmargin” height* 80%

(g) Use pivot table named “newvsrepeat” on “Pivot” sheet and create a “Stacked Line with Markers
Chart”. Then place this chart on the shape named “cnewrepeat” inside “Dashboard” sheet.
Specific Formatting for the chart
- Chart Line (Shape): fill color: Varying blue colors.
- Line Marker: fill color = white, Border color = RGB(0, 32, 96) width = 1 pt
- Resize and fit the chart based on the height and width of the shape named “csalestrend” as
follows: Width = “csalestrend” width * 90%, Height = “csalestrend” height* 80%
- Resize and fit the chart based on the height and width of the shape named “cnewrepeat” as
follows: Width = “cnewrepeat” width * 90%, Height = “cnewrepeat” height* 80%.

General Formatting for all charts:

Remove chart title.
Hide all pivot table field buttons on chart
Remove chart legends
Remove all chart gridlines
Chart area: Fill = No fill, Border = No Line
Chart Plot area: fill = automatic, Border = no line
Horizontal axis: Border line = no line, Text color = RGB(0, 32, 96), Font = Calibri, Font size = 7 Bold.
Vertical axis: Border line = no line, Text color = RGB(0, 32, 96), Font = Calibri, Font size = 7 Bold.
Number format = thousands

To Do: Generate me a complete and working VBA code that I can put into module and execute by
clicking the button.