Understanding Excel Custom Filter Hierarchy for Columns

Understanding Excel Custom Filter Hierarchy Columns

Excel custom filter hierarchy columns are a powerful feature that allows users to filter data based on a hierarchical structure. This can be particularly useful when working with large datasets that contain multiple levels of categorization. In this article, we will explore the key concepts of custom filter hierarchy columns in Excel and provide detailed examples to help you understand how to use them effectively.

What are Custom Filter Hierarchy Columns?

Custom filter hierarchy columns are a type of custom filter in Excel that allow users to filter data based on a hierarchical structure. This structure can have multiple levels, with each level representing a different category or subcategory. For example, you could have a hierarchy column with levels for country, state, and city, allowing you to filter data based on any combination of these categories.

How to Create Custom Filter Hierarchy Columns

To create a custom filter hierarchy column, follow these steps:

1. Select the column you want to use as the hierarchy.
2. Go to the Data tab in the Excel ribbon and click on Filter.
3. Click on the drop-down arrow for the column and select Text Filters or Number Filters, depending on the type of data in the column.
4. Select Does Not Equal and enter a value in the box provided.
5. Click OK to apply the filter.
6. Right-click on the column header and select Filter by Selection.
7. In the dialog box that appears, select the levels you want to include in the hierarchy and click OK.

Example of Custom Filter Hierarchy Columns

Let's say you have a dataset containing information about sales transactions, including the date, product, and sales amount. You want to create a custom filter hierarchy column that allows you to filter the data based on the year, quarter, and month.

To do this, follow these steps:

1. Select the Date column and apply a filter.
2. Right-click on the column header and select Filter by Selection.
3. In the dialog box that appears, select Year, Quarter, and Month and click OK.
4. You should now see a custom filter hierarchy column with three levels: Year, Quarter, and Month.

Using Custom Filter Hierarchy Columns

To use a custom filter hierarchy column, simply click on the drop-down arrow for the column and select the levels you want to filter by. You can select multiple levels by holding down the Ctrl key while selecting.

Advanced Tips for Using Custom Filter Hierarchy Columns

Here are some advanced tips for using custom filter hierarchy columns:

• You can rearrange the levels in the hierarchy by dragging and dropping them.
• You can remove levels from the hierarchy by right-clicking on the level and selecting Remove Level.
• You can create multiple custom filter hierarchy columns for different columns in your dataset.

References

• Filter data in a table or range
• Filter data
• Filter data by selection in Excel

Note: The above references are not endorsements of any particular product or service, and are provided for informational purposes only.