Highlight Deviations with Icons in Excel Chart based on Threshold Values | Custom Formatting

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
You'll learn an Excel custom formatting hack which enables you to to show icons for deviations. For example the up & down arrows or icons with different colors based on a threshold.
⬇️ Download the Workbook here: pages.xelplus.com/formatting-...
Key points from the video include:
Understanding Custom Formatting: Custom formatting is about the visual representation of data in Excel cells. It doesn’t change the actual cell value but alters how it's displayed. I'll show you how to access and apply custom formatting, using a combination of colors, symbols, and formatting codes to visually enhance data.
Formatting Positive and Negative Numbers: The video demonstrates how to use custom formatting to display positive numbers in green with an up arrow and negative numbers in red with a down arrow. This is achieved through specific formatting codes in the custom formatting dialog box.
Order of Formatting: An important concept highlighted is the order of formatting in custom formatting: first for positive numbers, then negative, followed by zero, and lastly text.
Practical Examples: The tutorial walks through practical examples, showing how to use custom formatting to represent data variations. This includes formatting based on thresholds, dynamic values, and multiple conditions.
Using Wildcards and Thresholds: You'll learn how to incorporate wildcards and thresholds in custom formatting for more complex data representation. This includes setting dynamic thresholds based on cell references and using wildcards for partial text matches.
Comparison with Conditional Formatting: The video emphasizes the differences between custom and conditional formatting, advocating for custom formatting due to its simplicity and efficiency.
Interactive Questions: The tutorial addresses viewer questions, providing solutions to specific custom formatting scenarios. This includes dynamic formatting based on average values and setting up multiple conditions within a single formatting rule.
Great thing is, you can have up to 4 different thresholds.
You can even define them in excel cells so they're dynamic. If you change the value in the cell, the custom formatting applied to the cells will be updated accordingly. We'll be using the IF function to assist us wit this.
★ Links to related videos:
Part 1: • Four SMART Ways to use...
Part 2: • Unusual use of Excel's...
IF Formula: • Excel IF Formula: Simp...
Microsoft link for color codes: bit.ly/2OEmUvd
★ My Online Excel Courses ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/formatting-thresholds-file

[@dasrotrad]

Another awesome tutorial Leila. Thank you!! I am currently about half-way through your Excel VBA & Macros course. I can't tell you how useful that course has been. It is so well organized and presented. The reference material you provide in that course has been invaluable for reviewing the information you so adeptly presented. What great value! I am most grateful.

[@mohammdzafer]

I really cant have the words that enough to thank you as they way it should be. Your videos are very helpful and you are able to deliver the superior information within easy way. As simple way to thank you with comment ( what can I say except AMAZING EFFORTS). Thanks as much as you deserved it !

[@ashutoshjha8551]

Have seen all the custom formatting videos posted by you. Very smart use of the feature plus thoroughly useful and impressive video. Thanks for coaching through.

[@varisco311]

Thank you Leila. I appreciate how much thought goes into each of your videos. Very professional and easy to follow. I didn't know about the color choices, and I will start using them. One small comment about this video is that the 5% value overlaps the first two conditions, so one small tweak I did on the second part of the condition was N16

[@nsanch0181]

I really enjoyed the custom format series. Thank you Leila.

[@PhilippeDeLange]

Amongst all the courses that I have followed on-line, yours are my favorites. Your presentations are very well made. Your voice and the pace of explanations are, for me, the most appropriate ones.

[@Excelbrains]

Hi Leila - great video again. Shows why Excel is the best program ever made: there are often multiple solutions to a problem. In this case and more in general I still tend to use conditional formatting instead of custom formatting. Ran into the limitation of the use of colors with custom formatting. Therefore applied the same logic via conditional formatting, but applying a custom color on the ‘•’ or up and down markers.

[@wayneedmondson1065]

Hi Leila.. thanks for another excellent video and tutorial. I use your tips and techniques in my reports every day. Thumbs up!

[@mrpage221]

It's a great video. Thank you for taking the time to answer our questions with a video.

[@mohammedMbwana]

I have all of the Leila's You Tube Videos. Now will be following up each technique slowly offline. Your excel file with video list was great to begin with. Shukran ukhty

[@mga5075]

I learn a lot with your videos... Thank you!!

[@jsoloughlin]

Fantastic explanation, I finally understand, your presentation on all 3 of this series made it possible to understand a tricky subject, thank you so much. John in Ireland.

[@mehmettevfikguler7271]

Leila , really you're süper and useful person. Thanks. I noticed power of cell formatting thanks to you. May Allah pleased from you.

[@gholamhosseingorgin4799]

Brilliant Leila, thank you so much for all your explanations! 💫

[@sandeepkothari5000]

Reading comments made by your followers is as interesting as your videos. Great! keep it that way.

[@LeilaGharani]

Thank you Sandeep. I love the comments, the suggestions & questions too. I really appreciate the community here! Thank you for your support.

[@wpiccoli]

Wise and creative solution, Leila. Thanks by share this with your audience. Good job and looking forward to your next lesson.

[@LeilaGharani]

Thank you Waldir for watching. I'm glad you find it useful.

[@subashguleria]

Your every video has a new opportunity to learn. Thanks a lot Leila

[@LeilaGharani]

Happy to hear that!

[@eslamfahmy87]

Actually, thanks a lot as you take ur comments as an important, you are one of the best instructors at YT😊

[@osirkeci]

easiest way to learn advanced excel ...big thank you

[@chingmei9528]

Very useful😍, i try to apply this concept in my report. Tqvm for sharing

[@go2mac]

great tutorial. I think most of my excel tricks came from your channel. thank you

[@petec6690]

Great series of videos. I've made notes and will be using some of these. Thanks. And I've created links so I can return to your videos and review them when I get confused.

[@LeilaGharani]

Glad you find the videos useful Pete!

[@Hurmali1]

I enjoy your class very much Leila, I never thought such things exist!

[@LeilaGharani]

Glad you liked it!

[@yulinliu850]

Excellent as always. Thanks for sharing!

[@LeilaGharani]

You're very welcome Yulin.

[@Softwaretrain]

Hi Leila, it was really creative. Actully i knew custom formatting but i didn't have your creative mind to do this with using if. Thanks alot.

[@LeilaGharani]

You're very welcome. I actually only got creative because of the questions I got :)

[@dashrathpanchal8393]

Hey Leila, Nice trick and advance way of using custom formatting, very faster way we can do this than the conditional formatting..Thanks ..You rocks as always ...Keep sharing

[@LeilaGharani]

Thank you Dashrath. Agree - I also prefer to custom formatting than conditional formatting...

[@arvindsv5116]

Very useful.. This channel is for those who think they are already good in Excel.

[@LeilaGharani]

Thank you Arvind for your support :)

[@vida1719]

very nice logic of assigning positive/negative number/ zero/ text to get formatting working

[@LeilaGharani]

Thanks Vida:) yes, those 4 conditions can come in handy...

[@stephengaskill4206]

Very nice Leila. The way you explain, its fantastic.....Could you please also make a video on bullet chart?

[@bakthapuri]

Awesome leila the way you teach... Tq very much..

[@ckokse]

So creative and to the point. Many thanks for that!

[@LeilaGharani]

You're very welcome :)

[@DINESHMaharjan-pe8mt]

Thanks a lot posting this video. It is really useful.

[@LeilaGharani]

Glad it was helpful!

[@michaeldiamond2726]

HI Leila, Marvelous as always. When I was studying custom formatting I remember reading that it only take 3 thresholds. But, I see you found the workaround by using converting the number values to text - great workaround. I'll have to add this to the CF article in some way. Slap my forehead I didn't think! Suberb!

[@LeilaGharani]

Hi Michael - yes it hadn't occurred to me either, until I saw the questions :)

[@michaeldiamond2726]

Interesting. That makes sense

[@grbrum]

I love this channel. I wish there was a similar channel for MS Access as well. Does anyone know?

[@ub9731]

Hi Leila, again a great video. Thanks a lot!

[@LeilaGharani]

You're very welcome :)

[@Dev_Bartwal]

I have no words for you LG💕 many many thanks to sharing amazing excel videos Lots of love from bottom of heart ❤️

[@LeilaGharani]

Thank you Dev for your continuous support :)

[@Dev_Bartwal]

Leila Gharani Is there any way to count unique with criteria (distinct count by formulas) We can use pivot for distinct count, If there is way to use formula it would be pleasure for me plz try to make distinct count with criteria video

[@raghuv7114]

@@Dev_Bartwal Hello Dev, Use countifs function to obtain total number of counts based on your single or multiple criteria.

[@Dev_Bartwal]

@@raghuv7114 i want unique count

[@raghuv7114]

@@Dev_Bartwal Hi, Can you please elaborate or put picture ?

[@manuelmabuki3107]

The best Excel Chart Instructor on Udemy. Leila is skillionaire

[@DougHExcel]

Thanks for the custom video!

[@LeilaGharani]

You're welcome Doug :) Thank you for watching.

[@serigamel]

Thanks for sharing this Leila

[@khalidalisubhanallah2947]

Bundle of Thanks Mam Very Very Informative Video U R Great.

[@LeilaGharani]

You're very welcome Khalid. Thank you for watching.

[@regisaguas8579]

Thank you for another great video

[@LeilaGharani]

You're welcome Regis. Thank you for your support.

[@joefromdc]

Awesome thanks for answering my question and I'm looking forward to you winning the iron chief excel 🤗🤗🤗🤗

[@LeilaGharani]

Sure. Actually thank you for your question! It inspired me to experiment. Thanks for your support for the iron chef :) I'm looking forward to the ingredients.....

[@MalinaC]

Brilliant, thank you Leila :)

[@LeilaGharani]

Thank you Malina :)

[@ismailismaili0071]

Thank u so much Ms. Leila the video really useful

[@LeilaGharani]

Thank you Ismail for watching. I'm glad you find it useful :)

[@alirezamogharabi8733]

Hi Leila, you are the best in data visualization, please make some videos about Dashboards.

[@LeilaGharani]

I appreciate that. I'm glad you like the videos. I can make bits and pieces of Excel dashboards for KZbin, but a complete dashboard is too long for here....

[@alirezamogharabi8733]

Leila Gharani Thank you so much.

[@ganeshmadhyastha]

Awesome tip.. I will definitely use it in my reports

[@LeilaGharani]

Thank you Ganesh! Glad you find the tip useful.

[@pinkeishz9106]

Great trick ❤️

[@arunark274]

Veryyyyy nice. Thank you so much for sharing. 💐👍

[@LeilaGharani]

Sure :) thank you for watching.

[@VijayKumar-zd7ou]

Good Leila nice video I m try this condition for my report. And one thing u r looking beautiful 😊

[@LeilaGharani]

Thank you Vijay :)

[@Luciano_mp]

As planilhas ficarão mais atrativas e com um aspecto profissional. Obrigado Leila.

[@LeilaGharani]

Thank you Luciano :) Agree - they will look more attractive & organized.

[@arunark274]

Hi Leila, The way you explain things is amazing. Thank you so much for sharing such a nice video. Could you please make a video on bullet chart? Like actual profit against budget/target profit... Kind Regards

[@LeilaGharani]

Hi Arun - You're very welcome. Thank you for your suggestion. I have a full section on bullet charts in my data visualization course. The course is filmed on Excel 2010 though. I'll be creating a new visualization course on the latest version of Excel soon. I'll also make a note to add a lecture on bullet charts to KZbin.

[@arunark274]

Thank you Leila. I have enrolled 4 of your online courses. Excited 😀😀😀

[@user-eq7dk2lk2d]

As usual always creative, greetings

[@LeilaGharani]

Thank you! I'm glad you like it.

[@hariiee_]

Loved it

[@clickclick8761]

Thank you very much. Useful. Clear explanation. Please share a link to your examples about Gantt charts in Excel

[@LeilaGharani]

You're welcome. I have a few videos on gantt charts here: kzbin.info/www/bejne/h4vHZJyOmNqYl6s and here: kzbin.info/www/bejne/gaW1XnaMrahop6M - you'll find the link to the workbooks in the description of the videos.

[@sumitthakur2241]

Now my vocabulary is finished for your teaching mam

[@nitinshokeen6090]

Really nice

[@thesimpleplantbali7559]

AWESOME!!!

[@sushantjoshi5865]

Thanks a ton.. fantastic video

[@LeilaGharani]

You're very welcome Sushant. Thanks for your support.

[@excelmylife1565]

Very useful

[@samuelkodjoe1645]

i really love this......got u!!!

[@LeilaGharani]

Glad you like it. Thanks for your support Samuel.

[@francoispluto9513]

Thank you for all

[@LeilaGharani]

You're welcome Silvan :)

[@sonupandey977]

🤯🤯🙌🏻🙌🏻🙌🏻 Thankyou

[@josedelbarrio2668]

Great video, it is amazing. The only thing that I don´t understand is why for the first exercise, for the positive values you pick up D6 which is a negative value?

[@LuisCarlosChavez717]

I just want to say, thank you Leila, you are amazing. I have a question, How can I align the arrows and the numbers, so it doesn't show up messy?

[@srivardhankandike4514]

loveuuuuu mam

[@5953kim]

Thanks a lot.

[@LeilaGharani]

You're very welcome.

[@johnborg6005]

Thanks leila :)

[@LeilaGharani]

You're very welcome John :)

[@eminabdullayev6569]

Thanks Leila

[@yavuzatak2498]

That's great ! İf we have more than four conditions, can we also create custom formatting ?

[@rajlakhani2958]

Hi Leila, Thank you for your very informative videos. Please can I ask you, is there a function equivalent to the Goal Seek feature in Excel to calculate the goal seek value automatically when you change the variables involved?

[@Olivier.Blanquart]

Excellent videos! Is there a way to use custom format to put a date before today in red for example? I am trying not to use conditional formatting for this. Thanks!

[@satishgopal2406]

Thank you

[@LeilaGharani]

You're very welcome. Thank you for watching.

[@hosseinhosseinpoor4845]

thanks.

[@darrenc.8663]

Thank you for the insight on Custom Formatting. I'm close to using it, but I'm still not sure it'll work for me. There are several commonalities I see in your usage of Cust. Form. One is that you are making a separate (3rd) column from Plan v Actual columns. I'm using the Actual column to format comparison to Plan - to avoid making a separate column each month. Also, Cust. Form. seems confined to formatting +ve. -ve, and 0 whereas I think i need more options. Here's my problem. Conditional Formatting is plain not working for me and I can't explain why. I've taken 3 excel courses so i'm not exactly a beginner but i feel like it now. I'm using simple color (R,Y,G) conditional formatting, no arrows or special characters. And I have used it this way many times before. So i think the real problem is going to be "user error" - which is great - then it's fixable (hopefully ;-) I have a month by month column of actuals that are conditionally formatted against a single column of plan or budget column. I've been waiting to conditionally format each month after the actuals are posted. But i decided to "get smart" and copy and paste my (correct!) conditionally formatted columns to the blank future columns so when the numbers are posted the formatting would already be there - well that was a big mistake. (The numbers & formulas did copy correctly [go F4!]). All the conditional formatting is wrong now - well, let me say, the formatting looks right, but it just isn't working anymore. I am looking at my cond. formatting formula for Yellow and the cell meets the condition (like it does in all the other cells . . !), but it is showing Green or Red, ugggh. So, i have cleared all the Conditional Formatting from all future column cells and started over - and still no good. it's like the old formatting is still there or something. Very frustrating. that's why I'm considering Custom Formatting. Anyway, i may just have to go back to Conditionally formatting month by month because the copy & paste of conditional formatting only didn't work. I tried several different ways too. Anyway, if there is a way to copy Conditional Formatting (only) to other cells, please let me know. btw, i'd like to copy & paste across rows, down columns and across both at same time (the array). and I tried the Paste Special option of Conditional Formatting only - and still didn't work. But that was after several other tries . . .so i think i had conflicting & overlapping conditional formatting. wow, what a long comment/question, whew!

[@markrowell4480]

I'm excited to experiment with custom formatting . Thanks for this series Leila. What I'm wondering is if you can also control the cell fill color similar to how you changed the text color for thresholds.

[@LeilaGharani]

Hi Mark - Yes you can but you need to use conditional formatting for that.

[@markrowell4480]

@@LeilaGharani that's what I'm doing now. Was just hoping custom formatting might work too 😆. Thanks for sharing your awesome skills

[@martijnfransen9933]

nice video Leila

[@LeilaGharani]

Thank you Martijn. I'm glad you like it!

[@MdNurNabiRobel]

Thanks

[@LeilaGharani]

You're very welcome.

[@alheeley]

So very useful and well presented, as ever! I've applied some custom number formatting to a calculated field on a pivot table, to show % difference in sales vs previous year, but some cells show errors (#DIV/0!) and some show -100% whenever a product has zero sales the year before or zero sales so far this year. I'd like to hide these from displaying if possible, is that possible with custom number formatting? Or do I somehow have to build an iferror check into the pivot table calculated field?

[@LeilaGharani]

You can add the IFerror to it so =iferror(put your formula here, “”)

[@gullenator1]

Great video! Is it possible to use custom formatting to apply a color fill to the cells based on their values? Or to set threshold dependent fill colors like with conditional formatting? Thanks!

[@hongqirong9927]

Hi, you will to do it manually. With a max 4 conditional colors given the constrain of positive, negative, zeros & text: +;-;0;@ Courtesy of Ms Leila in her workbook to the type of colors you would like to display for the conditions: Reference as per Microsoft Color codes for custom formatting: msdn.microsoft.com/en-us/library/cc296089(v=office.12).aspx For Unicode symbols, you can reference alanwood's Unichar formula with the symbol numbers found at this website. www.alanwood.net/demos/wingdings.html - for wingdings

[@user-vu8gm9vk8p]

Very helpful video... I have 1 question. In my sheet I am using code 0.?? to align values in a column. Is it possible to hide "." when I type a whole number? I want to show "5" instead of "5."

[@damoniery]

Hi Leila, thks for the knowledge... I wonder whether this custom fornatting applicable for chart label? I would like to combine this trick with your udemy class material : add dynamic series labels inside excel charts

[@NaftuliSinger]

Very nice! 1 small question, in part 2, you made 2 conditions to apply if it's =5%, doesn't this clash?

[@ElDibujante]

Lets do it :P

[@vandalo7494]

I used instead =IF(D2>10,1,IF(D2>5,-1,IF(D2>3,0,"T"))) and it works fine.

[@NaftuliSinger]

@@vandalo7494 Great, thanks.

[@josephcoon5809]

Just set the threshold column equal to the Dev column minus the threshold value. Now all you have to do is apply a standard ve+;ve-;0;@ format.

[@kimberleysmith5876]

These videos are so useful. I have subscribed to the channel. Thank you. The workbook download bitly link doesn't seem to be working at the xelplus page.

[@kimberleysmith5876]

Actually, I meant the workbook for the 1st two videos in this series.

[@LeilaGharani]

Glad to have you onboard, Kimberley. I just tested the download for the workbook from the website and they worked fine for me. Maybe try with a different internet browser?

[@kimberleysmith5876]

@@LeilaGharani I did and that worked. Thanks.

[@PetzNonofub]

Hello dear Leila, would you consider to level up the volume of this video please? Because I have the vol. on max on my PC now and it's very difficult to understand the things you are explaining ;) Thank you

[@ze0000]

Interesting. But first case could by simpler. =IF(ABS(D6) > E4; D6; 0)

[@zozozozo43]

♥♥

[@josephcoon5809]

6:45 It may be easier to just create a simple equation that subtracts your threshold number from the processed value. This will result in positive numbers for values greater than the threshold, negative values for values less than the threshold, and zero of values equal to the threshold. Now all you need is a standard value format for ve+; ve-; 0; @

[@josephcoon5809]

12:50 IF(N6

[@pradeepall4u]

Awesome tips... Does this work for pivot table as well?

[@pradeepall4u]

Also, can we do this to fill the cell color instead of text?

[@LeilaGharani]

Glad you like it. Yes - it works for pivot table as well, but you can't use this to color the cell - you can use it to change the font color or use symbols but not the cell color.

[@creative_gujarati]

Is it possible to alignment numbers based on positive, negative and zero to right, left and middle alignment in the same column?

[@privatepg]

Looking for the solution Each day have different colour. Like: red for sunday, blue for Saturday, green for Tuesday so on.

[@florinbasangiu1746]

Hi Leila! Could you please explain this formatting formula? [>10]General;[

[@ltmillerII]

Question: Can you take this formating and integrate it with a dashboard? Thanks!

[@LeilaGharani]

Yes - I do that instead of using the conditional formatting icons.

[@warlitojaque8781]

Hi Leila, i tried to link a formatted cell to my chart title. It works but the color was fixed to black and not following the eg red color for negative no. Is there a way for this to work? Thanks and appreciate your help.

[@krishnakanth7953]

How to create stock market ticker. If price increases cell colour should blink green once and blink red when price decreases

[@eslamfahmy87]

Can the custom format work without any function as my data as Cubes file and all functions are automatically and only i can see the result, but i need to make custom format for each %

[@manasa5639]

Hi,How to have Orange /Amber colour (while doing 3 color custom formating - Green,Amber,Red)

[@navaneethakrishnan1148]

Hi Leila, but if I open a another excel the custom formula is getting disappear from the list. How to get it appear in all the excel sheet.

[@PhilCoutts]

Hi I am trying this out and I am getting a strange result on the 3rd condition. My formatting looks like this [Color10][>=0]▲;[Red][

[@greggonzales233]

Did you ever figure this out? Im getting the same thing for one table, but my other table doesn’t have it. The difference between the two is one didn’t have a condition on the positive end. Only on the negative end

[@flyinglikeabird]

A minor error in the foluma, when the % is 5%, what its color will be? So the correct one should be "=IF(AND(N6>=5%,N6=3%,N610%,0,"t")))"