OmniDocFix Greyed-Out Conditional Formatting in Excel
Learn why Excel conditional formatting appears greyed out, how to tell whether protection, sharing or Protected View is the cause, and how to fix each one.
When the conditional formatting option is greyed out in Excel, you cannot add highlight rules, color scales or data bars to the cells you selected. The button is not broken. Excel is telling you that something about the sheet, the file or your current selection does not allow formatting changes. This is a common problem for people who work with shared budgets, downloaded templates and files a colleague protected.
This guide helps you find the cause in a few minutes. You will learn how to tell sheet protection, Protected View, read-only files and shared workbooks apart, and what to do for each one. Some fixes are yours to make. Others need the file owner, and this article explains when that is the case.
Why is Excel conditional formatting greyed out? The short answer
Excel conditional formatting greyed out is almost always caused by one of four things: the sheet is protected, the file is opened in Protected View or as read-only, the workbook is in shared mode, or your selection is an object Excel does not format this way. Start by clicking a plain cell and checking the message bar at the top of the window. That narrows the cause quickly.
| Your situation | What to do | Why |
|---|---|---|
| The file came from email, a download or the internet | Click the enable editing button in the yellow message bar | Protected View blocks changes until you trust the file |
| You cannot type in any cell on the sheet | Check whether the sheet is protected and ask the owner for the password or permission | Protected sheets block formatting unless the owner allowed it |
| Only some cells are affected, or a chart or PivotTable is selected | Click a plain cell outside the object and try again | Formatting commands depend on what is selected |
| The file is shared with other people | Stop sharing, or make your changes in a copy | Shared mode limits some formatting changes |
| The file is marked read-only | Save a copy under a new name and work there | You cannot save formatting changes to a read-only file |
Figure out which problem you have
Use this table to match what you see with the likely cause. Run each check in order, because the first one that matches is often enough to explain the problem.
| Symptom | Likely cause | How to confirm |
|---|---|---|
| Greyed out on every sheet, and a yellow bar appears at the top | Protected View | The yellow bar says the file came from the internet or is in Protected View |
| You cannot type in any cell of one sheet | Sheet protection | Type a letter in a cell. If nothing happens, the sheet is locked |
| Greyed out only when a chart, shape or PivotTable is selected | Wrong selection | Click a plain cell away from the object and try the command again |
| Works for the owner but not for you | Shared workbook or permissions | Ask the owner whether the file is shared and whether protection is on |
| Greyed out in an older .xls file | Compatibility mode | The title bar or file name shows an older format, such as .xls |
| The file is read-only | Read-only file or file open elsewhere | Check the file's properties and ask whether someone else has it open |
If you see more than one symptom, fix Protected View first, since it can hide the others.
Fix it step by step
Work through these steps in order. The earlier steps take seconds and often solve the problem.
- Click a plain cell that contains ordinary data. Do not select a chart, a shape, a text box or a cell inside a PivotTable. Then try the conditional formatting command again.
- If the file came from an email attachment or a download, look for the yellow message bar at the top of the window. Click the enable editing button. Excel will then allow changes, including formatting.
- Test whether the sheet is protected. Try to type in an unlocked cell. If you can't type anywhere, open the sheet protection setting in Excel's ribbon. If protection is on and a password is set, enter the password. If you do not know it, ask the person who protected the sheet. Do not try to remove protection with a password-cracking tool, since that can violate the owner's permissions and may corrupt the file.
- If the sheet is protected but you are allowed to edit, check the protection options. The owner may have left formatting cells off the list of permitted actions. The owner can turn that option on, then reprotect the sheet.
- If the workbook is shared, ask the owner to turn off sharing. Shared workbook mode is an older co-authoring setting, and it blocks some formatting changes. Once sharing is off, the command should return. Keep a backup before the owner makes the change, since sharing changes can affect how other people's edits merge.
- If the file is read-only, save a copy with a new name in a folder you control. The copy is a normal editable file. Apply the conditional formatting there, then check that the original owner is happy with the change before replacing their file.
- If the file is an older .xls workbook opened in compatibility mode, save a copy in the current .xlsx format. Some features are limited in older formats, and conditional formatting can be one of them. Test the copy before you rely on it.
- If none of the steps above work, copy the values into a new blank workbook and apply the conditional formatting there. This is a last resort. Check that formulas still calculate correctly and that no links or named ranges were lost.
When the option is available again, apply the rule to a small test range first. Confirm that the highlighting looks right before you apply it to the full data set.
Prevent it next time
A few habits will keep this from happening again, especially with files that many people share.
- Enable editing as soon as you trust the source. Check where a file came from before you open it. If it is from a trusted colleague, enable editing right away so you don't have to troubleshoot later.
- Record protection passwords with the file owner. Ask for the password when you receive a protected file, and keep it in your team's shared password record, not in the file itself.
- Turn off sharing when you finish co-editing. Shared mode is useful for quick collaboration, but it limits some formatting. Once the group is done, switch it off.
- Work in .xlsx files. Newer formats support more features and open without compatibility limits. Convert older .xls files early in a project.
- Keep a working copy. If you need to format a file you don't own, make a copy first. You can test rules there without risking the original.
❓ FAQ
1. Why is conditional formatting greyed out even though I own the file?
Ownership does not override protection or sharing. If you protected the sheet, you may need to remove protection before formatting. If the file is shared, turn sharing off first. The file may also be in Protected View, which you can end by enabling editing.
2. Can I apply conditional formatting to a protected sheet?
Yes, but only if the person who protected the sheet allowed formatting cells in the protection options. If that option is off, ask the owner to turn it on or to unprotect the sheet for you. Do not try to bypass the protection.
3. Will saving the file as .xlsx fix the greyed-out option?
It can, if the file is an older .xls workbook running in compatibility mode. It will not fix protection, sharing or Protected View. Check those causes first.
4. Why does the option work on one sheet but not another in the same file?
Protection is set sheet by sheet. The sheet that works may be unprotected, or it may allow formatting while the other sheet does not. Check each sheet's protection separately.
📝 Summary
Excel conditional formatting greyed out usually points to Protected View, sheet protection, a shared workbook, read-only access or the wrong selection. Start by clicking a plain cell and enabling editing, then check protection and sharing with the file owner. With the right cause identified, the fix is usually quick.
