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

[@LeilaGharani]

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

[@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.

[@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

[@eslamfahmy87]

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

[@subashguleria]

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

[@LeilaGharani]

Happy to hear that!

[@go2mac]

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

[@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.

[@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.

[@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

[@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 !

[@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.

[@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.

[@arvindsv5116]

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

[@LeilaGharani]

Thank you Arvind for your support :)

[@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

[@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...

[@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...

[@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.

[@osirkeci]

easiest way to learn advanced excel ...big 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!

[@wayneedmondson1065]

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

[@gholamhosseingorgin4799]

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

[@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 :)

[@mga5075]

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

[@mrpage221]

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

[@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 ?

[@stephengaskill4206]

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

[@DINESHMaharjan-pe8mt]

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

[@LeilaGharani]

Glad it was helpful!

[@nsanch0181]

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

[@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.

[@yulinliu850]

Excellent as always. Thanks for sharing!

[@LeilaGharani]

You're very welcome Yulin.

[@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 :)

[@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.

[@serigamel]

Thanks for sharing this Leila

[@ganeshmadhyastha]

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

[@LeilaGharani]

Thank you Ganesh! Glad you find the tip useful.

[@ismailismaili0071]

Thank u so much Ms. Leila the video really useful

[@LeilaGharani]

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

[@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?

[@sumitthakur2241]

Now my vocabulary is finished for your teaching mam

[@chingmei9528]

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

[@ub9731]

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

[@LeilaGharani]

You're very welcome :)

[@مقدادحديد]

As usual always creative, greetings

[@LeilaGharani]

Thank you! I'm glad you like 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.

[@khalidalisubhanallah2947]

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

[@LeilaGharani]

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

[@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 😀😀😀

[@manuelmabuki3107]

The best Excel Chart Instructor on Udemy. Leila is skillionaire

[@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!

[@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?

[@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.....

[@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!

[@arunark274]

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

[@LeilaGharani]

Sure :) thank you for watching.

[@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

[@florinbasangiu1746]

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

[@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?

[@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.

[@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

[@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 :)

[@sonupandey977]

🤯🤯🙌🏻🙌🏻🙌🏻 Thankyou

[@sushantjoshi5865]

Thanks a ton.. fantastic video

[@LeilaGharani]

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

[@grbrum]

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

[@yavuzatak2498]

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

[@DougHExcel]

Thanks for the custom video!

[@LeilaGharani]

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

[@creative_gujarati]

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

[@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.

[@ΑΠΟΣΤΟΛΟΣΤΣΑΛΤΑΣ]

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."

[@MalinaC]

Brilliant, thank you Leila :)

[@LeilaGharani]

Thank you Malina :)

[@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

[@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

[@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.

[@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, “”)

[@samuelkodjoe1645]

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

[@LeilaGharani]

Glad you like it. Thanks for your support Samuel.

[@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 %

[@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.

[@martijnfransen9933]

nice video Leila

[@LeilaGharani]

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

[@WhatThe_Stock]

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

[@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.

[@pinkeishz9106]

Great trick ❤️

[@manasa5639]

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

[@nazarster]

Could you or anyone please help to solve such case : I want to display calculated difference in absolute number (110-100=10), but to apply standard icons (either custom or conditional formatting) in same cell based on difference percentage (>=10% - green,

[@eminabdullayev6569]

Thanks Leila

[@johnborg6005]

Thanks leila :)

[@LeilaGharani]

You're very welcome John :)

[@francoispluto9513]

Thank you for all

[@LeilaGharani]

You're welcome Silvan :)

[@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

[@thesimpleplantbali7559]

AWESOME!!!

[@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")))"

[@nitinshokeen6090]

Really nice

[@privatepg]

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

[@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

[@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.

[@5953kim]

Thanks a lot.

[@LeilaGharani]

You're very welcome.

[@hariiee_]

Loved it

[@TechnoNetworkGuy]

Some Keyboard shortcuts which improves productivity :- kzbin.info/www/bejne/qWa9pmmLpLqims0

[@KKB-tt4lj]

I have a question if possible: have you make already a video about this problem? I have 3 range and for each range there will be selected a value: 2 20,000-60,000 --> 1 >60,000 -->0.5 In another cell if I write 25,000 it should ref to the 3 range and give me 1..... Hope you can help! Thanks!

[@LeilaGharani]

Hi - how about using an IF with AND function here, like in this video? kzbin.info/www/bejne/gZy3koRrhM90fcU

[@KKB-tt4lj]

Thanks for the feedback. I found a solution with Vlookup. Thanks!

[@LeilaGharani]

Great! Yes you're right. If your data is built up in order, then you can also use the lookup or vlookup as well.

[@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.

[@pinkeishz9106]

Leila, I regularly have to work on data which contents around 80000 rows and 36 columns... My pain area: have to find duplicate data with combination of 2 or 3 column then do countif or do condition formatting to find duplicate in a column... In both methods when I filter or do anything else file (Excel) gets hung sometimes even other Microsoft products also gets hung. Any advice here would be highly appreciated

[@Betterifitsfree]

Pinkeish Zaveri Wow!! 😨

[@LeilaGharani]

Hi Auris - Yes - conditional formatting can make this very slow. Power query might be an option here - or using Pivot tables on the data set, where you can sort based on your count formula.... you could also restrict the pivot table to show the fields that have a count greater than 1....

[@vinodpandey4927]

Hello., I'm little bit confused about your trick .you have entered(color10)∆;red another symbol .in that case why you have not enter color code for red color ? Pls explain

[@LeilaGharani]

Hi - yes the color codes can be confusing. I explain them a bit more in the part 1 and 2 videos.... red is a predefined color so you don‘t necessarily need to use the color code if you are ok with the red you get. Instead of color10 you can use green but I don‘t like the standard green you get so I use a different shade of green. That‘s the color10. You can test it out by replacing color10 with green....

[@vinodpandey4927]

Leila Gharani thanks for your quick reply👍

[@MdNurNabiRobel]

Thanks

[@LeilaGharani]

You're very welcome.

[@hariomgupta4677]

Any trick I want send many whatsup massage by using excel sheet to different people and different massage and different attachments