I have a source table that shows a list of stocks and the total amount invested in each (specified in both dollars and percentages relative to the entire portfolio)
I'd like to display the percentage figures of the stocks in a doughnut chart, displaying those with a percentage figure of 5% or more as individual "slices" and any with a figure of less than this to all be combined in a single slice named "Other".
Ideally I'd like to achieve this by using an amended version of the source data that has already summarized these small percentage figures together (e.g. as a single row).
The table on the left is the original source data. My attempt at the "modified" source data to feed the chart is shown on the far right.
I'm using a helper column of [Combine as Single Pie Slice?] in the original source table to determine which stocks have a percentage figure below 5%, and those that have a figure above it. This is then used to help construct the 'modified' source data shown on the right.
In the modified source data (which isn't actually a table), the formula for [Amount Invested] and [Relative % in Portfolio] cells of the "Other" row can be seen in the Xl2bb output.
Below this is a filtered list of the stocks that should display individually in the chart. That list is a dynamic spill array (formula is in first cell of that array, where "AMZN" resides).
Collectively, all these rows (except the blank row between them) form the actual data I want the chart to use (specifically the [Relative % in Portfolio] column for the data, and [Stock] for the data labels)
Currently this isn't working brilliantly as the chart is giving two doughnut rings rather than just the one and there are are also blank entries in the chart's Legend which is a bit off. I suspect this all could be due to the chart's source data being a combination of a standalone row of data - the "Other" row - plus the dynamic spill array. If I get rid of the empty row between the "Other" row and the dynamic spill array, this still give two doughnut rings in the chart. I only need the one!
I think I just need some way to construct the modified source data so that the chart sees it all as one (and ideally as a single table).
Chart:
I'd like to display the percentage figures of the stocks in a doughnut chart, displaying those with a percentage figure of 5% or more as individual "slices" and any with a figure of less than this to all be combined in a single slice named "Other".
Ideally I'd like to achieve this by using an amended version of the source data that has already summarized these small percentage figures together (e.g. as a single row).
The table on the left is the original source data. My attempt at the "modified" source data to feed the chart is shown on the far right.
I'm using a helper column of [Combine as Single Pie Slice?] in the original source table to determine which stocks have a percentage figure below 5%, and those that have a figure above it. This is then used to help construct the 'modified' source data shown on the right.
In the modified source data (which isn't actually a table), the formula for [Amount Invested] and [Relative % in Portfolio] cells of the "Other" row can be seen in the Xl2bb output.
Below this is a filtered list of the stocks that should display individually in the chart. That list is a dynamic spill array (formula is in first cell of that array, where "AMZN" resides).
Collectively, all these rows (except the blank row between them) form the actual data I want the chart to use (specifically the [Relative % in Portfolio] column for the data, and [Stock] for the data labels)
Currently this isn't working brilliantly as the chart is giving two doughnut rings rather than just the one and there are are also blank entries in the chart's Legend which is a bit off. I suspect this all could be due to the chart's source data being a combination of a standalone row of data - the "Other" row - plus the dynamic spill array. If I get rid of the empty row between the "Other" row and the dynamic spill array, this still give two doughnut rings in the chart. I only need the one!
I think I just need some way to construct the modified source data so that the chart sees it all as one (and ideally as a single table).
| Pie Chart Data.xlsx | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | Max. [Relative % in Portfolio] value for combining assets as single "other" pie slice: | 5 | ||||||||
| 2 | ||||||||||
| 3 | Original Source Data | Modified Source Data for Pie Chart | ||||||||
| 4 | ||||||||||
| 5 | Stock | Amount Invested | Relative % in Portfolio | Combine as Single Pie Slice? | Stock | Amount Invested | Relative % in Portfolio | |||
| 6 | APPL | $2,500.00 | 3.29 | Y | Other | 16,000.00 | 21.05 | |||
| 7 | AMZN | $10,000.00 | 13.16 | N | ||||||
| 8 | COST | $2,500.00 | 3.29 | Y | AMZN | 10,000.00 | 13.16 | |||
| 9 | DIS | $2,500.00 | 3.29 | Y | GOOG | 10,000.00 | 13.16 | |||
| 10 | GOOG | $10,000.00 | 13.16 | N | MA | 10,000.00 | 13.16 | |||
| 11 | HD | $2,500.00 | 3.29 | Y | MSFT | 10,000.00 | 13.16 | |||
| 12 | JNJ | $1,000.00 | 1.32 | Y | TSLA | 10,000.00 | 13.16 | |||
| 13 | KO | $1,000.00 | 1.32 | Y | V | 10,000.00 | 13.16 | |||
| 14 | MA | $10,000.00 | 13.16 | N | ||||||
| 15 | META | $2,500.00 | 3.29 | Y | ||||||
| 16 | MSFT | $10,000.00 | 13.16 | N | ||||||
| 17 | PG | $500.00 | 0.66 | Y | ||||||
| 18 | TSLA | $10,000.00 | 13.16 | N | ||||||
| 19 | UNH | $1,000.00 | 1.32 | Y | ||||||
| 20 | V | $10,000.00 | 13.16 | N | ||||||
| 21 | Total | $76,000.00 | 100 | |||||||
Sheet1 | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C6:C20 | C6 | =IFERROR( ([@[Amount Invested]]/tblOrigSourceData[[#Totals],[Amount Invested]])*100,"") |
| D6:D20 | D6 | =IF([@[Relative % in Portfolio]]<$F$1,"Y","N") |
| G6 | G6 | =SUMIFS(tblOrigSourceData[Amount Invested],tblOrigSourceData[Combine as Single Pie Slice?],"=Y") |
| H6 | H6 | =SUMIFS(tblOrigSourceData[Relative % in Portfolio],tblOrigSourceData[Combine as Single Pie Slice?],"=Y") |
| F8:H13 | F8 | =FILTER(FILTER(tblOrigSourceData,(tblOrigSourceData[Combine as Single Pie Slice?]="N")),{1,1,1,0}) |
| B21 | B21 | =SUBTOTAL(109,[Amount Invested]) |
| C21 | C21 | =SUBTOTAL(109,[Relative % in Portfolio]) |
| Dynamic array formulas. | ||
Chart:
Last edited:
