Apple Event: May 7th at 7 am PT
Hi, I'm looking for a way to set up a bar chart that reflects only one category in a large categories table ideally the chart would only show the first category "Not sold" in the example below.
I am aware that I can manually choose the cells but I am hoping I can set it up in a way that it automatically reflects and adjusts when a new line is created in the "not sold" above section. Any help is greatly appreciated!
You can set up a separate table for the chart that duplicates the info in your main table but hides the "sold" rows. By default, hidden rows are not included on charts (you can select to include them, though). Or, if you don't mind filtering your main table temporarily to view the chart, skip making a second table and, instead, filter the main table. Filter it to show rows where column A is FALSE (not sold), view the chart, then un-filter it when you're done looking at the chart.
First screenshot shows the second table with a filter on column B to show only the correct rows. The second screenshot shows the second table with the filter off (the chart is still there, I did not include in the screenshot).
I assume your main table will keep growing (more rows) so I made the "For Chart" table much longer so it will grow along with it (for a while at least). The error triangles are reference errors. They will turn into good references as you add rows to your main table.
Column A formula =Table 1::A
Column B formula =IF(Table 1::A,"sold",Table 1::C)
You have to use "entire column" references, like Table 1::A. If you use references like =Table 1::A1, those references don't seem to repair themselves later.
Create your chart then set up a filter on the table to only show rows where text is not sold.
You can cut/paste the table to another sheet or put a big white rectangle over it, move both out of the way and/or Arrange/Send to Back to put them behind everything else, and lock them. Or put the chart and table on a separate sheet together. Whatever works best for you.
There are more sophisticated and complicated ways to do this that would put only the unsold items in the second table but that does not seem necessary.
You can set up a separate table for the chart that duplicates the info in your main table but hides the "sold" rows. By default, hidden rows are not included on charts (you can select to include them, though). Or, if you don't mind filtering your main table temporarily to view the chart, skip making a second table and, instead, filter the main table. Filter it to show rows where column A is FALSE (not sold), view the chart, then un-filter it when you're done looking at the chart.
First screenshot shows the second table with a filter on column B to show only the correct rows. The second screenshot shows the second table with the filter off (the chart is still there, I did not include in the screenshot).
I assume your main table will keep growing (more rows) so I made the "For Chart" table much longer so it will grow along with it (for a while at least). The error triangles are reference errors. They will turn into good references as you add rows to your main table.
Column A formula =Table 1::A
Column B formula =IF(Table 1::A,"sold",Table 1::C)
You have to use "entire column" references, like Table 1::A. If you use references like =Table 1::A1, those references don't seem to repair themselves later.
Create your chart then set up a filter on the table to only show rows where text is not sold.
You can cut/paste the table to another sheet or put a big white rectangle over it, move both out of the way and/or Arrange/Send to Back to put them behind everything else, and lock them. Or put the chart and table on a separate sheet together. Whatever works best for you.
There are more sophisticated and complicated ways to do this that would put only the unsold items in the second table but that does not seem necessary.
How to set up a bar chart based on only one category section.
