Dear all,
I am building an Excel Dashboard and therefore have to use quite many pivot tables. I am now creating a top 10 list of potential customers based on the potential sales amount and am using the top 10 filter that is available in the filter section. However, I have now two problems:
No. 1: When using the top 10 filter it does not only display the top 10 customers based on the sum of sales amount, but does also show all other entries corresponding to that customer. (see row 8) Clearly the sales amount of this entry does not belong in the top 10 range. How can I get rid of this? I found no customization option in the top 10 filter unfortunately.
No. 2:
I have set up the pivot table in a manner to easily select the corresponding sales company. Since one company does not have enough top customers that correspond to the filter, some rows are shown empty. I formatted the pivot table corresponding my dashboard color, since I use the camera tool to display the needed section of the pivot table on my dashboard. But obviously the regions where the top 10 customers would be look now the following if there is no data filling it:
I tried to set the background color of the whole sheet to the right color, but whenever I update the pivot table, the empty range appears as above. --> How can I set the background color of row 4 - 13 (here top 10 would show) corresponding to my dashboard color and keep the color fixed also when updating the pivot table?
Thank you for your help!
Cheers,
Rea
P.S. If the pictures don't show, they can be found here:
https://www.dropbox.com/sh/pmrdptg4y6ytmib/vqsfLVAyRq
I am building an Excel Dashboard and therefore have to use quite many pivot tables. I am now creating a top 10 list of potential customers based on the potential sales amount and am using the top 10 filter that is available in the filter section. However, I have now two problems:
No. 1: When using the top 10 filter it does not only display the top 10 customers based on the sum of sales amount, but does also show all other entries corresponding to that customer. (see row 8) Clearly the sales amount of this entry does not belong in the top 10 range. How can I get rid of this? I found no customization option in the top 10 filter unfortunately.
No. 2:
I have set up the pivot table in a manner to easily select the corresponding sales company. Since one company does not have enough top customers that correspond to the filter, some rows are shown empty. I formatted the pivot table corresponding my dashboard color, since I use the camera tool to display the needed section of the pivot table on my dashboard. But obviously the regions where the top 10 customers would be look now the following if there is no data filling it:
I tried to set the background color of the whole sheet to the right color, but whenever I update the pivot table, the empty range appears as above. --> How can I set the background color of row 4 - 13 (here top 10 would show) corresponding to my dashboard color and keep the color fixed also when updating the pivot table?
Thank you for your help!
Cheers,
Rea
P.S. If the pictures don't show, they can be found here:
https://www.dropbox.com/sh/pmrdptg4y6ytmib/vqsfLVAyRq
