Contextures News

Excel Conditional Formatting Mess

March 14, 2017

Fix a conditional formatting mess, and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos -- let me know if you're looking for something and can't find it there.

Happy Pi Day!

It's March 14th, and 3.14 is the rounded version of Pi, so Happy Pi Day! Did you know that Excel has a PI function?

  • In the screen shot below, I entered a circle's diameter in cell D2.
  • In cell D3, that number is divided by 2, to get the radius: =D2/2
  • Finally, in cell D4, the PI function calculates the circle's circumference: =2*PI()*D3

If you don't need to know the radius, just multiply the diameter by Pi. There are lots more function tips, videos and examples on my Contextures website.

custom tab on ribbon

Conditional Formatting Mess

You can use conditional formatting to highlight one or more cells, based on rules. Sometimes though, the rules are duplicated automatically, and that can create a huge mess in your workbook.

The duplicate rules are usually created when:

  • rows are inserted or deleted in the table
  • a rule has a formula that refers to cells in other rows

If this happens to you, here's how to fix the problem -- or watch my video:

  • Except for the first row, select all the rows with the same conditional formatting rules
  • On the Excel Ribbon's Home tab, click Conditional Formatting
  • Click Clear Rules, then click Clear Rules from Selected Cells
  • Select the first row, and on the Excel Ribbon's Home tab, click the Format Painter
  • Drag the Format Painter over all the cells where the conditional formatting rules should be applied, including the first row

duplicate conditional formatting rules

Get the sample workbook, and more details on my Conditional Formatting page.

Excel Articles

Here are a couple of recent Excel articles that you might find useful.

Data Viz - Jorge Camoes has 10 reasons why we should take Excel dashboards seriously. I liked #5: "let your imagination run wild". He's got a new book on dashboards too, but it's only available in Portuguese. (Level - All)

Pivot Tables - Allen Arthur studied Social Journalism, and learned to love Pivot Tables, while working on his final project. His final paragraph was my favourite part of the story. (Level - All)

Also see: My Excel Products || Excel Events || Weekly Excel Humour

Maple Syrup

It's maple syrup season in Canada, and you could spend the whole month going to the maple syrup festivals and events. There's a long tradition of this -- here's a picture that my Dad took, long ago, at Upper Canada Village. I'm on the right, holding my sister's scarf to keep it out of the sap bucket, while my Mom and aunt look on. Good times!

weekly photo

That's it for this week! If there are topics that you'd like to see covered in future emails, please let me know.

ddalgleish @

P.S. You can choose to get the full Contextures news package (news and occasional special announcements), or the basic news package (news only). Click either link to change your news option.

Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.




Get weekly Excel tips from Debra






Last updated: March 15, 2017 11:58 AM