We use the Sort & Filter options in Excel to sort selected data from the smallest to largest, largest to smallest, perform a custom sort, or filter out specified values. This makes it easier for us to analyze our data.
Why are Sort and Filter Greyed
However, sometimes we may find that we cannot use the Sort & Filter drop-down because it is greyed out as below:
This tutorial gives possible reasons why the Sort & Filter drop-down may be greyed out in Excel and possible solutions.
Reason 1: Many worksheets are selected
If many worksheets are selected in the workbook the Sort & Filter control becomes greyed out.
If you see the word Group next to the name of the workbook in the title bar, it means multiple sheets are selected in the workbook.
Solution
Enable the Sort and Filter control by ensuring that only one worksheet is active. You do this by right-clicking the grouped sheets at the bottom of the Excel window and selecting Ungroup Sheets on the shortcut menu that appears.
Reason 2: The worksheet is protected
Protecting a worksheet prevents unwanted changes by limiting the ability to make edits. The Sort and Filter drop-down is normally disabled in protected worksheets.
You can tell a worksheet is protected if the Unprotect Sheet option in the Protect group on the Review tab is enabled.
Solution
Unprotect the worksheet by clicking the Unprotect Sheet option. You may be required to provide a password.
Reason 3: Part of the selected data is within a table and the other is not
If part of the data you are trying to sort is contained in a table and the other is not the Sort and Filter control is greyed out.
For example, in the following dataset, range A1:C6 is a table and range D1:D6 is a normal data range.
If we select the dataset A1:D6 and head over to the Sort and Filter control we find that it is disabled.
Solution
You can consider sorting the data outside the table separately from the table data.
You can also convert the table to a normal data range by selecting it, right-clicking the selection, and selecting Convert to Range on the Table flyout menu.
You can also consider extending the table to include the data that is outside the table. You can do this by selecting any cell in the table to activate the Table Design tab, selecting Resize Table in the Properties group,
Then input the new data range in the Resize Table dialog box that appears and click OK.
The table extends to include the data that was outside the table:
Reason 4: Data originates from an external source
The data displayed in the worksheet may be residing in an external source such as a SQL server. In most cases, Excel does not have a problem sorting such data but sometimes it may encounter difficulties, and the Sort & Filter control is greyed out.
Solution
Copy the data and paste it into Excel as values before you sort it.
After copying the data, press Ctrl + Alt + V to activate the Paste Special dialog box, select Values, and click OK.
Conclusion
In this tutorial, we have looked at 4 reasons why Sort and Filter may be greyed out in Excel. We hope you found the information useful.