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
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920