Let’s say you work in a government agency where you need to quickly assess the impact of different types of services across various states based on population size.
Your Excel sheet contains columns for agency, state, type, population, and contributions. Now to analyze this data you can filter multiple columns at the same time. Yes that, right. When you apply a filter to a continuous data range, Excel allows you to filter multiple columns.
In the above data, we must filter the “type” and “contributed_by” columns. This is easy if the data has multiple columns and you want to filter more than one column (two, three, or more). This tutorial will look at easy-to-follow steps for this…
Steps to Filter Multiple Columns Simultaneously in Excel
- Select the range of data you want to filter: First, select the range or the table where you have the data on which you want to filter multiple columns. You can also click on the header of the first column, holdd the Shift key, and then click on the header of the last column to select the entire range of columns.
- Apply Filter: Use the keyboard shortcut (Alt ⇢ D ⇢ F ⇢ F) to apply the filter, or you can also go to the Home ⇢ Sort & Filter ⇢ Filter.
- Open Filter: Next, click on the filter arrow button in the column “type,” and then untick all the values other than “City.”
- Apply Filter to the First Column: After doing this, click OK to apply the filter. This will filter the entire data based on the column “City” from the column “type.”
- Apply Filter to the Second Column: Next, go to the “contributed_by” column, open the filter dropdown, and untick all the values other than the “Kenny Cunanan”. Click Ok.
Once you do this, multiple filters will be applied to your data. One is in the column “type” and the other is in the column “contributed_by”.
You can apply multiple filters to as many columns as you want, not just two. You can apply one more filter to the column “state”.
In the above example, we have the third filter on the column “state”, where we have filtered all the values with CA. Excel offers a variety of filters that you can utilize to sort and analyze your data, regardless of the number of columns you have.
- Number Filters: When your column contains numerical data, number filters can be your best ally. They allow you to filter data based on a variety of conditions, such as “equal to,” “greater than,” “less than,” “between,” and more. This is helpful when analyzing data within a certain numerical range or meeting a specific numerical condition.
- Text Filters: This type of filter is useful when working with columns that contain textual data. Text filters allow you to sift through and present your data more meaningfully. You can filter for exact matches and partial matches and even use operators like “begins with,” “ends with,” and “contains.” This is especially handy when you want to isolate data that shares a common text attribute.
- Date Filters: Date filters come into play when dealing with columns containing dates. They provide a simple way to filter your data based on a specific period, such as a particular date, week, month, quarter, or year.
- Color Filters: Color filters can be extremely useful if you’ve assigned colors to your cells based on certain conditions using conditional formatting. They allow you to filter your data based on those colors.