question

Xin-9239 avatar image
0 Votes"
Xin-9239 asked VikiJi-MSFT commented

Excel text only table/chart and automated refresh

Wondering if there is any suggestion to achieve the below:

121412-screenshot-2021-08-08-090900.png
1. Currently I am putting together a dash board like one example of the below

  1. There is a background photo

  2. Each module has an "Object - Shape" above the background photo with transparent colour

  3. Each module has a chart that shows the analysis, data is linked to pivot table

Problem

  1. All the table with quantitative data are all fine. Chart linked to pivot table in another tab and pivot just needs refresh when someone updated underlying data.

  2. However there is one table with only text and, the current approach is, I created a classic pivot to replicate same format as the underlying text table. The copy and paste into image to include in the below dashboard.

  3. Reason been - I cant link cell within this dashboard to the underlying table because these transparent shapes are above cells and will cover text

End goal

Wondering what's the best approach to create a "text only" table and place within one of these modules within the dashboard where table is linked to another table in another tab where people update that table and this dashboard will get refreshed.


office-excel-itprooffice-scripts-excel-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

VikiJi-MSFT avatar image
0 Votes"
VikiJi-MSFT answered VikiJi-MSFT commented

@Xin-9239,
Welcome to Q&A forum!

Per my research, you could try to change the Data Source of PivotTable to achieve this.

  • Please back up the file first.

  • Copy the "text only" table to a blank worksheet, then go to the PivotTable linked to the chart.

  • Click Change Data Source in PivotTable Analyze tab and select the range of "text only" table.

121490-image.png
Besides, there is an official article about Dashboard, pelase have a check.

Hope the information is helpful to you.


If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


image.png (70.6 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi thanks for your reply, apologies if I wasnt 100% clear but linking data part is all fine.

My question is more around, how do you link chart to pivot on the actual dashboard when the pivot table is intend to show qualitative data.

For example I have got this pivot table and just want to show exact same content and format in the dashboard (other than line chart or column chart but just a chart in this table format).

121592-image.png


0 Votes 0 ·
image.png (59.9 KiB)

@Xin-9239,
Thanks for your reply and clarification.

According to my test, if the background picture is inserted as a picture, you will not be able to create an editable table("text only" table/PivotTable) above the picture.
Only pictures or charts could be added.

121653-test.gif

Thanks for your understanding!

1 Vote 1 ·
test.gif (1.7 MiB)