Excel Hack

Conditional formatting shortcut

Excel’s conditional formatting is a tool that allows you to apply any formatting to cells that meet certain conditions. For example, you can apply a format that is useful for managing data, such as highlighting cells with a number greater than 50. Below is a shortcut that will help you use conditional formatting more quickly. This method is useful for managing large amounts of data in an easy to read format.

Conditional formatting shortcut

Display the New Formatting Rule dialog box

Windows
Access Key: Alt + H + L + N

Mac
None

Display the top/bottom rules

Windows
Access Key: Alt + H + L + T

Mac
None

Display the highlight cells rules

Windows
Access Key: Alt + H + L + H

Mac
None

Display the data bars

Windows
Access Key: Alt + H + L + D

Mac
None

Display the color scales

Windows
Access Key: Alt + H + L + S

Mac
None

Display the Conditional Formatting Rules Manager dialog box

Windows
Access Key: Alt + O + D or Alt + H + L + R

Mac
None

Display the New Formatting Rule dialog box

Learn how to display the New Formatting Rule dialog box with shortcut keys.

Total Time: 1 minute

Select a cell

Select a cell

Select a cell. Press Alt + H + L + N.

Display the New Formatting Rule dialog box

Display the New Formatting Rule dialog box

The New Formatting Rule dialog box will appear. From this screen, you can create a new condition.

Apply the top/bottom rule to cells

Select a cell

Select a cell.

Press Shift + ↓

Press Shift + (Down Arrow key) to select multiple cells to which you want to apply the rule.

Press Alt + H + L + T

Press Alt + H + L + T to display the rules that can be applied to a cell. This time, we will select the top 10 items, so press the T key again.

Enter a value

The Top 10 Items dialog box will appear.

This time, we will highlight the top three cells with the highest values. Press (Down Arrow key) to change the value and press Enter.

Rules applied

The rule has been applied to the cells, and the three cells with the highest values have been highlighted.

Highlight cells rules

Select a cell

Select a cell.

Press Shift + ↓

Press Shift + (Down Arrow key) to select multiple cells that you want to highlight.

Press Alt + H + L + H

Press Alt + H + L + H to display the highlight cells rules. Select any rule. In this case, we will select greater than, so we will continue to press G.

Enter a value

The Greater Than dialog box will appear.

Enter the value of the cell to be highlighted. In our case, we want to highlight values above 1000, so we will enter 1000 and press Enter.

Highlighted

The cell is now highlighted.

Apply data bars

Select a cell

Select a cell.

Press Shift + ↓

Press Shift + (Down Arrow key) to select multiple cells to which you want to apply the data bar.

Press Alt + H + L + D

Press Alt + H + L + D to bring up the menu for selecting the data bar. Use the Arrow keys, etc. to select the data bar of your choice.

The data bar has been applied

The data bar has now been applied to the cell.

Apply color scales

Select a cell

Select a cell.

Press Shift + ↓

Press Shift + (Down Arrow key) to select multiple cells to which you want to apply the color scales.

Press Alt + H + L + S

Press Alt + H + L + S to display a menu where you can select the color scales. Use the Arrow keys, etc. to select the color of your choice.

Color scale applied

The color scale has been applied to the cells.

Display the Conditional Formatting Rules Manager dialog box

Select a cell

Select a cell. Press Alt + O + D or Alt + H + L + R.

Display the Conditional Formatting Rules Manager dialog box

The Conditional Formatting Rules Manager dialog box will appear.

In this screen, you can manage the conditions you have created.

Articles related to Conditional & Format & Shortcut

Comments

Please send us your comments on this article.

0 comments
Inline Feedbacks
View all comments