OmniDocWhere to find conditional formatting in Excel: Detailed explanation of function location
Explain the location of the Excel conditional formatting button in the ribbon, covering Windows, Mac, web version and old version menus, and explain how to retrieve existing rules and locate formatted cells.
Key points of this article
- Conditional formatting in the new version of Excel is located in the Styles group on the Home tab, and the entry points for Windows, Mac and web versions are basically the same.
- Data bars, color scales, and icon sets are all subtypes of conditional formatting, and are collectively collected in the conditional formatting drop-down list.
- Manage rules are used to view and modify existing rules, and clear rules are used to delete them. Both are in the same drop-down list.
- To find cells with conditional formatting in the entire table, you can use the positioning criteria in search and selection.
Many people open Excel sheets and want to add background color to numbers and mark data that exceeds the target, but they can't find the entrance to conditional formatting. Where to set conditional formatting in Excel? The answer is actually very fixed: it is in the Styles group on the Home tab. The interfaces of different versions are slightly different, and the old version hides the entrance in a completely different menu.
This article only talks about the location: where is the conditional formatting button on the ribbon, how to find it in Windows, Mac, web version and old version of Excel respectively, how to retrieve the rules that have been set, and how to find out which cells in the worksheet are formatted. How to create specific rules will not be discussed here. The focus is on finding them.
How to find conditional format? One sentence conclusion: Click Conditional Formatting in the Styles group of the Home tab
Excel 2007 and later versions (including Windows, Mac, and web versions) place conditional formatting on the Home tab. After selecting the cells to be processed, click Conditional Formatting to expand all entries. The table below gives the locations according to your environment.
| your situation | Recommended practices | reason |
|---|---|---|
| Excel for Windows 2013 and later | Home tab→Styles group→Conditional formatting | The entrance is fixed and consistent with the layout of the new functional area |
| Excel for Mac | Find Conditional Formatting in the Home tab | The ribbon layout is basically the same as the Windows version |
| Excel for the web | First switch to the Home tab and then check whether there is conditional formatting | The interface of the web version is more streamlined, and some entrances may be tighter. |
| Excel 2003 and earlier versions | Format menu in the menu bar→Conditional formatting | The early version did not have a functional area, and the entrance was in the menu. |
| Can't see buttons in ribbon | Double-click any tab name, or press Ctrl+F1 to expand the ribbon | Buttons are hidden when the ribbon is collapsed |
Specific location in the ribbon
Style group of home tab
After opening the workbook, click the Home tab at the top and find the Styles group in the middle of the toolbar. There are usually three buttons in this group: Conditional Formatting, Apply Table Format, and Cell Styles. Click conditional formatting, and a drop-down list will pop up listing all entries by category.
The structure of the drop-down list is roughly as follows:
- Highlight cell rules: mark cells by greater than, less than, between, equal to, text containing, etc.
- Item selection rules: Mark the first few items, the last few items, and the cells above or below the average.
- Data bars, color scales, and icon sets: Use bars, color gradients, or icons to represent numerical values.
- New rules, clear rules, management rules: listed at the bottom of the list for adding, deleting and viewing rules.
Quick analysis button
After selecting a piece of data, a small quick analysis icon will usually appear in the lower right corner of the area (Excel 2013 and later versions). After clicking it, switch to the Format tab, which also contains common options for conditional formatting, such as data bars, color scales, and icon sets. This entry is good for quick application, but the options are not as complete as the conditional formatting drop-down list.
shortcut key
In the Windows version, you can open the conditional formatting drop-down list by pressing the Alt, H, and L keys in sequence. These three keys should be pressed in sequence, not at the same time. The shortcut keys of the Mac version are different from those of the Windows version. It is recommended to use the mouse directly to operate in the Start tab.
Why aren't data bars, color scales, and icon sets in separate menus?
Many people think that data bars, color scales, and icon sets each have an independent entrance. In fact, they are all subtypes of conditional formatting and are unified in the conditional formatting drop-down list. The advantage of this design is that all functions for automatically labeling data are concentrated in one place, so you don’t have to look for them everywhere.
Another area of confusion is applying table formats. It is also in the Styles group, next to conditional formatting, but it mainly changes the appearance of the range and header filtering, not dynamically marking cells by numerical values. If you want the value to change color when it exceeds a certain value, you should click on conditional formatting rather than applying table formatting.
📊 Core comparison
| Entrance | Suitable for the scene | Notes |
|---|---|---|
| Conditional format dropdown list for the ribbon | View all types, create or manage rules | You need to select a cell or area first |
| Quick analysis | After selecting the data, quickly add data bars or levels | There are fewer options, and they only appear near the selected area |
| Alt, H, L shortcuts (Windows version) | Users accustomed to keyboard operation | Press all three keys in sequence, not at the same time |
| Find and select → location conditions → conditional formats | Find cells in the worksheet that already have conditional formatting | Only selects and does not modify the rules themselves |
How to actually use it
- Open the workbook, click the cell you want to process, or drag and drop an area.
- Switch to the Home tab and click Conditional Formatting in the style group.
- To view existing rules, click Manage Rules and select the viewing range in the pop-up window. It can be the current worksheet or the currently selected area.
- To delete a rule, click Clear Rule, then choose to clear the rule for the selected cell or the rule for the entire worksheet.
- To find cells in the entire table that have conditional formatting, click the Find & Select or Locate Conditions in the Start tab one by one. After selecting the conditional format, confirm, these cells will be selected all at once.
If the table you want to process is from PDF, you can first export it as an Excel file using the PDF editor in the OmniDoc desktop client, then enter Excel and click the above steps. After conversion, merged cells, numeric format, and headers may not exactly match the original, so you need to check each item individually. The scanned PDF must first undergo OCR recognition, and the recognition results also need manual verification.
🎯 Where should you go in what kind of situation?
- If you just want to quickly add a data bar to a column of numbers: select the data and use quick analysis.
- To modify or delete existing rules: Open the conditional format dropdown list in the ribbon, select Manage Rules or Clear Rules.
- When taking over someone else's table, it's unclear where the format is: select all cells with conditional formatting using locator conditions and check them one by one.
❓ Frequently Asked Questions (FAQ)
1. If you can't find the conditional format button, besides the ribbon being folded, what other reasons could there be?
When the window is too narrow, the toolbar compresses buttons into smaller groups, requiring enlargement or monitoring whether buttons are folded. If the file is in read-only or protected mode, some editing functions will be grayed out, so you need to enable editing first.
2. How to tell if a cell has already been formatted with conditional formatting?
Select the cell, open the management rules in the conditional format dropdown list, and check the rules that apply to that cell. To check the entire table, you can use the conditional format in the positioning condition to select all formatted cells at once.
3. If you can't find the options you need in Quick Analysis, where should you go?
Quick Analysis only provides commonly used data bars, color levels, and icon sets. If more types are needed, such as marking by numeric range or text content, you should use the conditional format dropdown list in the ribbon.
4. Can tables in PDF be directly formatted with conditional formatting?
Usually, it cannot be applied directly because PDF only saves the page appearance and does not save cells. You should first convert the table to an Excel file, then set conditional formatting in Excel. After conversion, check whether the data is complete.
📝 Summary
The entry point for Excel conditional formatting is in the style group of the Start tab. The position of each version is basically the same, with only the old menu and the web interface slightly different. Once you find the entry point, the management rules, clear rules, and location conditions can help you view, delete, and check existing formats. Using them well means you don't have to keep searching.
