top of page
Search
  • Writer's pictureImran Din

Something Doesn't Add up! Keeping it One Hunnit with Conditional Formatting

Lot of things need to be split. Bananas, The Bill, Atoms ... Percentages.


Let's say we're looking to divide a 100% Sales Quota amongst a team:



We want to input %'s in the Quota column, but we probably want to avoid alotting more or less than 100%:












Using Conditional formatting, we can highlight these errors.


First, we'll select the Quota % column:



Next, from the Home tab in the Ribbon, we'll click on Conditional Formatting and make a New Rule:



We'll use a Formula. In the Formula bar, we'll simply type Sum( and select whatever is in the column. Then, we'll make sure that is doesn't equal 1 (100%).




Our function will look something like this:


=sum($B$2:$B$6)<>1

Then, we'll select the formatting we want to use.


I like to use the Darkest Red as the font.



And the Lightest Red as the Background:



And there you have it! Another thing that yells at you when you make a mistake.



If the conditional formatting rule isn't true (i.e. everything does equal 100%), then the formatting goes away:






14 views0 comments

Comments


bottom of page