Format data instantly with conditional formatting

It used to take a bit of work to dress up your data with conditional formatting like bars and color scales. But now there’s a shortcut that lets you do things like highlight your biggest numbers in two clicks. The key is the new Quick Analysis Lens button that shows up when you select your data.

  1. Select the cells that contain the data you want to format.
  2. Click the Quick Analysis Lens button image button that appears to the bottom right of your selected data (or press CRTL + Q).

Selected data with Quick Analysis Lens button visible

  1. Under Formatting, select the options you want. If you aren’t sure which option to pick, just point to each one to see a preview.

 Tip    Don’t like the conditional formatting that you choose, just click Clear.

Clear option

You might notice that the formatting options you can choose from aren’t always the same. That’s because the options available in the lens change based on the type of data you have selected in your workbook. For example, if you select numbers you will have one set of formatting options. If you select text you will have a different set of options.

Want more formatting options?

If you want to apply different formation options from the ones you see on the Quick Analysis Lens, you can access all the available conditional formatting choices from the Ribbon.

  1. Select the cells that contain the data you want to format.
  2. Click Home > Conditional Formatting.
  3. Pick the conditional formatting options that you want.

Conditional Formatting options

Learn more about conditional formatting

Apply conditional formatting based on text in a cell

Copy conditional formatting

Find duplicate values fast by applying conditional formatting

Remove conditional formatting

Use a formula to apply conditional formatting

Applies to:
Excel 2013