You can make visualizations from subsets of spreadsheet data. To do this, use the "Filter Data" field when you create your visualization:
- Either create or edit your visualization
- Learn how to create a new visualization and edit a visualization
- Click inside the "Filter Data" field
- Find this field below the "Data Source URL"
A data filter toolbox will open
- Enter your filter criteria
- See Filters below for details
- Click OK
You'll see a subset of your data on the chart
Filters
There are three sections in the data filter toolbox: an "If", a "Show," and "Order By" section.
- If sets which data values appear on the visualization
- Show sets which data categories appear on the visualization
- Order by shows the order in which categories appear on the visualization
Let's say you have a list of countries, and a count of bronze, silver, and gold Olympic medals for each country in the following sheet:
Bronze | Silver | Gold | |
USA | 321 | 158 | 333 |
Soviet Union | 211 | 147 | 25 |
UK | 125 | 225 | 451 |
France | 52 | 147 | 255 |
China | 298 | 32 | 197 |
Japan | 12 | 56 | 125 |
"If" Statement
You want to only show countries with more than 100 bronze medals. To do this, you can use an if statement. Choose options for the three drop down menus to make this statement:
IF Bronze > 100
This hides France and Japan from your final graph, to give:
Bronze | Silver | Gold | |
USA | 321 | 158 | 333 |
Soviet Union | 211 | 147 | 25 |
UK | 125 | 225 | 451 |
China | 298 | 32 | 197 |
You can also stack criteria. In addition to showing countries where bronze medals > 100, you want to only show countries that also have fewer than 200 gold medals.
Click + next to the first filter, then enter the following statement:
ALSO Gold < 200
This hides the Soviet Union from your final graph, to give:
Bronze | Silver | Gold | |
USA | 321 | 158 | 333 |
UK | 125 | 225 | 451 |
China | 298 | 32 | 197 |
Visuals uses the headers from your spreadsheet to determine what categories appear in the first drop down menu. Make sure to format your data according to the guidelines in the Visualization Index for that graph.
Your options in the second drop down menu include:
- <: less than
- >: greater than
- =: equal to
- !=: is not equal to
- <=: less than or equal to
- >=: greater than or equal to
"Show" Statement
You want to only show silver medals on your graph, but have no criteria for the number of medals you want to show. To do this, you can use a show statement. Simply choose the category next to "SHOW" that you want to display:
SHOW Silver
"Order" Statement
You want your bar chart to sort the categories according to gold medals. You want the country with the most Gold medals to be at the top of the graph, and the country with the fewest Gold medals at the bottom. Simply choose the category next to "ORDER BY" that you want to display:
ORDER BY Gold