OmniDocWhere is the Excel chart filter and how to only display part of the data?
Explain the location and use of Excel chart filters and how to adjust the panel if it is too small. The data is not deleted, only the unnecessary series or categories in the chart are hidden, and several common troubleshooting ideas are introduced.
Key points of this article
- After selecting the chart, the funnel-shaped button in the upper right corner of the chart is the chart filter. Click it to select the series and categories to be displayed.
- The chart filter only controls chart display and does not delete the data in the cell. It can be restored at any time after unchecking it.
- The panel itself cannot be dragged as wide as the window. If you can't see clearly, you should enlarge the chart first. If there are many items, scroll inside the panel.
- If hidden rows and columns still appear or disappear in the chart, you need to check the settings for hidden and empty cells.
When making Excel charts, we often encounter the following situation: there are more than a dozen lines or dozens of months in a chart, but only a few of them are displayed during the report. Deleting data directly is not only troublesome, but may also affect other calculations. At this time, Excel's built-in chart filter is a more suitable method.
This article is written for people who need to streamline chart content in Excel. You'll see where the chart filter buttons are, how to check series and categories, what to do if the panel is unclear, and what to check if the chart and table data don't match up. The entire process does not require deletion of any original data.
Where are the chart filters? One sentence conclusion
The chart filter is located in the upper right corner of the chart. After selecting the chart, three small icons will appear next to the chart border: the top is a "+" shaped chart element button, the middle is a pen-shaped chart style button, and the bottom is a funnel-shaped chart filter button. Click Funnels to open the filter panel.
| your situation | Recommended practices | reason |
|---|---|---|
| Want to hide only a few lines or columns | Uncheck the chart filter's Series options | The operation is straightforward and the original data is completely retained. |
| Only want to display certain months or regions | Check the required items in the category options | Horizontal axis items can be controlled individually |
| The chart is not a regular line or bar chart | First confirm whether the chart type supports filtering, and then decide which method to use | Filter items for individual chart types are displayed differently |
| Content that needs to be displayed permanently | Create another table containing only the target data, and then make a separate chart | Avoid confusing filtering status during subsequent changes |
Before you start: Confirm these points first
First make sure that the chart is selected, not the cell. Only when the chart object is selected, the three icons in the upper right corner will appear. If you don't see the funnel button, click on an empty area of the chart to view it again.
Confirm Excel version. Chart filters are more common in newer Excel versions, the interface and button placement may be different in earlier versions. If you can't find the Funnel button, you can use the Select Data feature in the Chart Design tab to manage your series instead.
Confirm whether the data source is continuous. Chart filters only process the data range referenced by the current chart. If the data is scattered in multiple areas, or the reference range is incomplete, the items in the filter panel may be incomplete. You need to check the data range in the select data function first.
Confirm whether original data needs to be retained. The chart filter only hides the chart display and does not affect cell content and formula calculation. If you need to completely remove some data, you should do it in the table, not just the chart.
Confirm who the chart will be shown to. Filter status is saved with the workbook. If you want to send the same file to multiple people, remember to remind each other: the chart currently displays the filtered results, not all the data.
Steps to display only some series and categories in Excel
- Select the chart. Click on a blank area of the chart to make sure the chart is selected and three small icons will appear in the upper right corner.
- Click the funnel button. This is the chart filter button. After clicking, the filter panel will pop up, and the series options will be displayed by default.
- Check the series to be displayed. The panel lists each line or group of columns in the chart. Check it to show it, and uncheck it to hide it.
- Switch to the category option. List the items on the horizontal axis here, such as month, region or product name. Check the reserved items as needed.
- Confirm to apply changes. There is usually an Apply button at the bottom of the panel. After clicking it, the chart will be updated according to the check result. Closing the panel directly may not take effect.
- Click on a blank space outside the chart. Exit the editing state and check whether the chart only displays the target content.
- When recovery is needed, reopen the filter panel. Check all items again and apply, and the chart will be restored to full display.
What to do if the filter panel is too small: enlarge the chart to see the items clearly
Many people search for "how to enlarge the chart filter" because there are too many items in the panel and they are crowded together and cannot be seen clearly. The size of the panel usually follows the chart itself and cannot be dragged and enlarged at will like a normal window.
A more practical approach is to zoom in on the chart first. After selecting the chart, drag the control points on the four corners or sides of the chart to pull the chart to a larger size, and then open the filter panel. As the chart becomes larger, the display range of the panel increases accordingly. If there are too many items, the panel content can be scrolled up and down to view and check one by one.
If the chart itself is already large and you still feel that the panel is too crowded, you can consider temporarily reducing the display ratio, or copying the chart to a new worksheet and processing it separately. It should be noted that the panel performance of different Excel versions may be different. The actual performance seen on your computer shall prevail.
Check each item after completion
After the screening is completed, do not rush to copy or send the chart, first check the following content one by one:
| Check item | What do you think? | What if there's a problem? |
|---|---|---|
| Whether the series shown in the chart is correct. | Against the legend, make sure that only the required lines or columns are left | Reopen the filter panel and check the check status of the series |
| Whether the horizontal axis project is complete | Check the horizontal axis and make sure that all the required months or categories are in | Select the missing items in the category options, and then confirm the application |
| Is the value consistent with the table? | Randomly select several data points and compare them with the values in the cell | Check whether the data source scope is correct, and modify the reference area in the data selection function if necessary |
| Whether hidden rows or columns are handled as expected | If the rows are hidden in the table, see if the chart disappears or is still displayed | Find the hidden and empty cell settings in the Select data function, and choose whether to show the data of hidden rows and rows. |
| Does the title and legend need to be modified synchronously? | See if the title also describes the full content. | Manually modify the chart title to avoid discrepancies between the title and the filtered content |
# "Core comparison: chart filter, select data and hide rows and rows
There are three common ways to control chart content in Excel, each of which has different applicable scenarios and points for attention.
| Method | An appropriate situation | Pay attention |
|---|---|---|
| Chart filter | Temporarily hide certain lines or columns, or retain only part of the month | Only affect the current chart display, the operation is fast, uncheck it and restore it. |
| Select data function | Need to redefine the data source of the series, or add or subtract the series | The scope of reference is modified. Try to write down the original settings before operation. |
| Hide the rows and rows | Want to simplify tables and charts at the same time | Whether the chart changes depends on the hidden and empty cell settings, and needs to be confirmed item by item. |
The three methods can be used in combination. For example, first use a chart filter to quickly remove unwanted lines, then check whether the data source is accurate by selecting the data function, and finally confirm that the hidden rows in the table do not accidentally affect the chart.
# frequently asked questions (FAQ)
1. Does the chart filter delete the original data?
No, I won't. The chart filter controls only which series and categories the chart displays, and the data, formulas, and calculations in the cell remain the same. After unchecking the box, the content can be restored at any time.
2. Why do I hide the rows in the table, the chart or show the data?
The chart does not necessarily follow the hidden rows by default. You need to find the hidden and empty cell settings in the Select data function, and choose whether or not to display the data in the hidden rows and rows as needed. Set up and then check to see if the chart meets expectations.
3. What if I can't find the funnel button?
First make sure that the chart object is selected, not a cell. If it is still not available, maybe the Excel version is earlier or the chart type does not support it, you can use the Select data feature in the Chart Design tab to add or subtract the series, or manually adjust the data region.
4. After the chart is filtered, does someone else open the file and see the same result?
Right. The filter status is saved with the workbook. Before sending the file, it is recommended to check the current display status of the chart and state in the title or comment that the chart shows only part of the data to avoid misunderstanding.
# Summary
Excel Chart filter in the funnel button in the upper right corner of the chart, check the series and categories to confirm the application, you can display only part of the data, the original table is not affected. If the panel is too small, first enlarge the chart and then look at it. If the chart does not match the table, check the settings for hidden rows and data source ranges.
