Excel - Conditional Format if Delta is Beyond 15% Above or Below - Excel - Episode 1509

2,603 views · Published 20 January 2012 · 7:30 · Indexed 22 October 2026

Channel: MrExcel.com · 2012 · Education

Watch on YouTube

Microsoft Excel Tutorial: Conditionally Format Values that have Increased or Decreased by 15%

Welcome to another exciting episode of Dueling Excel, where Mike Girvin from ExcelIsFun and I, Bill Jelen from MrExcel, go head to head to find the best solution to an Excel problem. In this episode, we tackle the challenge of conditionally formatting end values that have increased or decreased by 15%. At first, I thought this couldn't be a duel because there's only one way to do it, but I think I have the solution.

I start by copying the data over and creating a new column called "CHANGE". In this column, I use the formula =the ending value / by the beginning value – 1 to calculate the percentage change. Then, I use the brand new ICON SET feature in Excel 2010 to add the up and down arrows to the data. However, I don't want to highlight all changes, only those that are + or – 15%. So, I go into the manage rules and edit the rule to only show the green icon when the value is >= to 0.15 and the red icon when the value is <= -0.15. I also make sure to select "SHOW ICON ONLY" to hide the numbers.

But, there's one problem - the yellow – signs. To get rid of them, I create a new rule using the formula =AND(D4<0.15,D4>-0.15) and select "STOP IF TRUE" to make sure it doesn't interfere with the previous rule. Then, I use the custom formatting option to hide the value when it shows through. Now, the data is properly formatted with the correct icons and no extra column visible.

But, Mike takes it a step further by using a logical formula to conditionally format the data. He creates a formula off to the side and then copies and pastes it into the conditional formatting dialog box. He also uses a parameter in a cell to easily change the formatting if needed. And, the most impressive part - he does it all without using any parentheses, showing his expert knowledge of the order of operations in Excel.

So, who do you think had the best solution? Let us know in the comments below and don't forget to subscribe to our channels for more exciting Dueling Excel episodes. Thanks for watching and we'll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Episode topic: Conditional format + or - 15%
(00:19) Bill's solution using icon sets
(01:40) Mike's solution using logical formulas
(06:48) Comparison and discussion of solutions
(07:20) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial


This video answers these common search terms:
Conditional format + or - 15%
Excel conditional formatting
Excel conditional formatting formulas
Excel fill color
Excel icon sets
Excel logical formulas
Excel manage rules
Excel order of operations
Hide cell values in Excel
Paste linked picture in Excel
Percentage change formula


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153187/



Initially, it appears that there is only one way to accomplish this task, but Mike "ExcelIsFun" Girvin and Bill "MrExcel" Jelen find another way to tackle and solve this challenge. Follow along with Episode #1509 to see how its done!

Dueling Excel Podcast #95...This episode is the video podcast companion to the book, "Slaying Excel Dragons: A Beginners Guide to Conquering Excel's Frustrations and Making Excel Fun", by Mike Girvin and Bill Jelen. [Currently available in eBook / Print Edition and as a DVD Bundle]

and

"Learn Excel 2007 through Excel 2010 from MrExcel". Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!

"The Learn Excel from MrExcel Podcast Series"

MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!

More from this channel