CODEWITHSUNDEEP
excelvba
Cauder
Cauder

Reputation: 2567

Chart only a subsection of data in my table

I have a table and I want to show only a part of that data as a chart.

This is my data

pets       age
coco       3
rover      3
clifford   8
rugrat     7
doug       1

I want to make a chart that shows pets on the x-axis and age on the y-axis.

Importantly, I want to show data for pets that are at least four years old (clifford and rugrat).

In my use case, I cannot delete those rows from my table because it would adversely affect other tables in my worksheet. I would also rather avoid pivot tables. My band-aid fix is to select the data and manually select, in this example, clifford and rugrat.

Upvotes: 0

Views: 1038

Answers (2)

Bryan Rock
Bryan Rock

Reputation: 632

You mentioned you prefer to avoid pivot tables. This solution utilizes pivot charts, NOT pivot tables.

  1. Click anywhere in the table and go to insert->pivot chart

  2. (Optional) In the Create PivotChart menu, select the check box to Add this data to the Data Model. This will prevent a Pivot Table from being created in the workbook. enter image description here

  3. Drag Age into the values section and Pets into the Axis (Categories) section. enter image description here

  4. Click the dropdown button on the Pets field and add a value filter. In this case you want the sum of age to be greater than 4. (Alternatively, you can use the Pets button on the pivot chart.)

enter image description here enter image description here

Below is the final product. You may wish to adjust the y-axis, add titles, etc. from here. enter image description here

Upvotes: 3

Abdullah Bayounis
Abdullah Bayounis

Reputation: 265

In Case you have Office 365, Try this:

Let assume that the following:

  • "pets" is on column A
  • "age" is on column B
  • You are Working in "Sheet5"
  • File Name is Book2

From the name Manager, create a named range called "Titles" and with value as:

=FILTER(INDIRECT("Sheet5!$A$2:"&"$A$"&COUNTA(Sheet5!$A:$A)),INDIRECT("Sheet5!$B$2:"&"$B$"&COUNTA(Sheet5!$B:$B))>4)

From the name Manager, create another named range called "Values" and with value as:

=FILTER(INDIRECT("Sheet5!$B$2:"&"$B$"&COUNTA(Sheet5!$B:$B)),INDIRECT("Sheet5!$B$2:"&"$B$"&COUNTA(Sheet5!$B:$B))>4)

For Chart do the below:

  1. Insert the chart type you need not based on the data in Column A & B and then
  2. Click on the chart
  3. From the Chart Design Tab click on "Select Data"
  4. Under "Legend Entries (Series)" click "Add"
  5. In The "Series Value" Box type Book2!Values (Values is the named range created before)
  6. Under "Horizontal (Category) Axis Labels" click "Edit"
  7. In The "Axis label range" Box type Book2!Titles (Titles is the named range created before)

Upvotes: 2

Related Questions