Why is data greyed out on Excel?

Sometimes Excels sorting and filtering options become grayed-out and unusable for no apparent reason. This problem doesnt arise very often, but it can be very confusing when it does.

Nội dung chính

Why are the sort and filter options greyed out?
The worksheet may be Protected
A worksheet group may be selected
The data may be partially contained within a Table
The data may originate from an external data source
Share this article
Recent Articles
VLOOKUP exact match
Cannot group that selection error in Excel pivot tables
Adding images to tables
Table Tools, Design Tools Group
24 Responses
Leave a Reply Cancel reply

Adding to the confusion, there are several reasons why this can happen! This article explains the reasons sorting and filtering can be disabled, and how to fix the problem.

The worksheet may be Protected

One of Excels features is the ability to protect a worksheet, which can prevent it from being sorted, and even prevent it from being changed in any way.

If this is the cause of the problem, you should be able to resolve it by unprotecting the worksheet.

Worksheet protection is covered in depth in our Expert Skills course.

A worksheet group may be selected

Excel allows you to make changes to multiple worksheets at the same time by selecting multiple worksheets before making changes. This is called a worksheet group.

You cannot use sorting and filtering when multiple worksheets are selected, so if this is the cause of the problem you can fix it by simply selecting a single worksheet.

Worksheet groups are covered in much greater depth in our Essential Skills course.

The data may be partially contained within a Table

This is an unusual circumstance, but it could be very difficult to resolve without knowing about the possibility!

Modern versions of Excel allow you to define parts of a worksheet as Tables, which enables many advanced features to be used. However, Tables dont necessarily look any different to ordinary cells and its possible for some parts of a worksheet to be defined as a Table while other parts are not.

If you try to sort a range of cells that are partially within a table and partially outside a table, youll find that the Sort and Filter options are grayed-out and unusable, even though the cells might look completely ordinary!

The only way to tell for certain if a cell is contained within a Table is to click on it and look at the Ribbon.

If the cell is contained within a Table, the Table Tools > Design group will appear on the Ribbon.

Removing the Table should re-enable the sorting options (although its likely that whoever created the Table did so for a reason). You can remove the Table by clicking the Table Tools > Design > Convert to Range button on the Ribbon.

You can learn much more about Tables in our Expert Skills course.

The data may originate from an external data source

Some Excel data may originate from an external data source, such as a SQL Server database. This isnt a problem in most cases, as Excel is able to apply sorting to most external data sources, but there are some that Excel may be unable to sort.

If the data is held in a data source that Excel cannot sort, you may need to copy it and paste it as Values before you can sort it.

Pasting values is explained in depth in our Essential Skills course.

We also explain external data sources in great depth in our Expert skills course.

Last updated on July 23, 2018

Share on facebook

Share on twitter

Share on linkedin

Share on stumbleupon

Share on email

Recent Articles

VLOOKUP exact match

Simple VLOOKUP tutorial with sample file that will teach you everything there is to know when creating a VLOOKUP function with an exact match.

Cannot group that selection error in Excel pivot tables

This article shows how to resolve the Cannot group that selection error message that can appear when attempting to group data in Excel pivot tables.

Adding images to tables

This article explains how to insert pictures into Excel workbooks and Excels current image features, including cell backgrounds.

Table Tools, Design Tools Group

This article explains the Table Tools > Design tab on the Excel Ribbon, how to access it and how to reset the Ribbon if the tab has been disabled.

24 Responses

Anonymous says:
August 11, 2018 at 9:43 pm
August 2018 Excel 2016 Found a group of cells outside the Range then clicked in a cell, the Design pop into Ribbon, Clicked on Design>Convert to Range> Popup Window do you want to Convert the Table to a Normal range? YES / NO. similar as stated above.
Reply
1. Simon Smart says:
  August 12, 2018 at 11:24 am
  Yes, this is exactly the process shown above, converting a Table into an ordinary Range.
  Reply
2. Anonymous says:
  November 20, 2018 at 11:33 pm
  OMG Thank you so much!! I just spent about 2 hours working on the spreadsheet and did not want to start over.. You are a life saver!!!
  Reply
joe says:
August 22, 2018 at 5:37 pm
Convert to Range is also ghosted out, so this is not a fix. The worksheet is not grouped or protected. What else could it be?
Reply
1. Simon Smart says:
  August 23, 2018 at 9:24 am
  If the worksheet isnt protected, its most likely that the table is retrieving its data from an external data source or Get & Transform query. If this is the case, you may need to change the sorting options within the query itself or copy and paste the data elsewhere so that it is no longer connected to another data source.
  Reply
  1. Anonymous says:
    April 10, 2019 at 4:57 pm
    This is just what I needed. Never realized that before but it makes sense.
    Reply
Anonymous says:
February 19, 2019 at 6:14 pm
Thank you!!!!!
Reply
Anonymous says:
February 21, 2019 at 9:48 pm
Thank you!! Worked in Excel for years and this was driving me nuts!
Reply
Meg says:
March 16, 2019 at 3:52 am
I had this problem. Could not find the solution until I carefully scanned the cells and noticed in the last row a TINY black corner on the lower right corner of the first cell of that row. Hovering the mouse pointer over the corner made the cursor change from a pointer to a square outline. Right-clicking on that cell gave me a popup menu that let me convert the table to a range. Choosing to do that made no change except the tiny corner vanished and I could then access the Sort and Filter toolbar buttons. Beats me why the creator of this table did their table that way but Im glad to have the sort/filter buttons available again! Writing this, I also discovered I could have right-clicked on any cell in that column to see Convert to range present in the right-click menu. Any other column, that option did not appear.
Reply
1. Simon Smart says:
  March 17, 2019 at 1:26 pm
  Hi Meg,
  
  It looks like you were having the problem mentioned in the The data may be partially contained within a Table section above. It most likely happened because the workbooks creator added or removed rows without realizing that they werent being included in their table.
  As you observed, you can convert tables to ranges using the right-click menu, but the easiest way to tell if a cell is part of a table is usually to select the cell and see if the Table Tools > Design tab appears on the Ribbon.
  Im glad you were able to solve the problem; perhaps Microsoft will make it easier to spot Tables in a future version of Excel!
  Reply
2. Anonymous says:
  October 23, 2019 at 6:09 pm
  OH thank you so much! This fixed my issue!
  Reply
3. Liz says:
  June 28, 2020 at 6:54 am
  Thank you for this explanation! Thats exactly what was going on with my document, and I had just spent quite a lot of time compiling a database so that I could sort it according to my preferences
  Reply
4. Barnes says:
  July 22, 2021 at 11:53 pm
  AWESOME Help!
  Reply
Stavros says:
May 8, 2019 at 6:58 am
Thank you. Saved me from a headache
Reply
Michael LaGasse says:
May 16, 2019 at 3:16 pm
None of the above was working, then I noticed that there were 3 sheets in my spreadsheet. It should have only been one. The second and third sheets both had miscellaneous data from the original sheet scattered throughout. After deleted both sheets, I was able to sort the original. How could this have happened?
Reply
1. Simon Smart says:
  May 17, 2019 at 7:52 pm
  Hi Michael,
  The only explanation I can think of is that you might have had the extra sheets selected without realizing it, giving you the problem described in the A worksheet group may be selected section above. After deleting the other sheets it was only possible to have one sheet selected so sorting and filtering became available again.
  Its also not unheard of for Excel workbooks to become corrupted; what you said about the extra sheets having scattered data from the first suggests this possibility. If you suspect that your workbook has become corrupt it can usually be solved by copying the data into a new workbook that has not been corrupted.
  
  Reply
Kish says:
April 28, 2020 at 7:18 pm
saver ! phew, i had no idea why not filtering.. thanks a lot. converting range worked. Big thanks.
Reply
John R says:
May 20, 2020 at 2:29 am
I had no filter buttons on top of one of my table worksheets + Filer Button was greyed out.
Select Data/Click anywhere on table/click Filter Button -and all Filter Buttons appeared on top of the table.
Worked for me and I hope this may help others with this problem
Excel 2016
Reply
T says:
July 17, 2020 at 7:59 pm
This has been my bane for such a long time. Your solution worked easy. Thanks Simon Smart!
Reply
Ada says:
July 23, 2020 at 2:22 pm
Thank you!!! The data may be partially contained within a Table < this. I don't even know how I created the embedded table, but this info was all I needed.
Reply
Zakir says:
April 8, 2021 at 5:51 am
life saver. Thanks you very much
Reply
s says:
April 14, 2021 at 9:47 pm
THANKS
Reply
Misho says:
April 19, 2021 at 8:41 am
Thank you very much.
Reply
Yeguwala says:
October 4, 2021 at 2:23 am
Got this thank you
Reply