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

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

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

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.

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

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

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

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

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

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.

Thanks for sharing this Leila

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

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

Thanks Leila

Thank u so much Ms. Leila the video really useful

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

Thank you for all

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?

As usual always creative, greetings

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!

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

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

Brilliant, thank you Leila :)

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.

🤯🤯🙌🏻🙌🏻🙌🏻 Thankyou

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

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

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.

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?

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!

AWESOME!!!

Really nice

Thanks a lot.

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

If you reply to this, I'll quit porn forever.

Some Keyboard shortcuts which improves productivity :- ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-s5Zu6UoUodk.html

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

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

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,

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

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

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

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

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

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.

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 %

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

Oh, beautiful women.

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

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.

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

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

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

loveuuuuu mam

thanks.

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

Excellent as always. Thanks for sharing!

Hi Ma'am, I am facing such type of issues in excel 2007. In cell date is showing in value format but in formula bar showing correct date. Please suggest how I do adjust date format, Which I see correct date in cell. Also , I write any number he is changing automatic text format, and text automatic changing in number format. Please suggest, Also I would request you to please share email ID, then I will share excel attachments

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

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?

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

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.

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

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

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

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

Instead of nesting IF functions, wouldn’t it be better and simpler to use IFS function?

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

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

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.

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.

How does one add icons that don't exist in symbols. Example would be thumbs up or down

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 !

Now my vocabulary is finished for your teaching mam

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

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!

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

The best Excel Chart Instructor on Udemy. Leila is skillionaire

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

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

Thanks

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?

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

Thanks for the custom video!

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

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

Hi Leila, Can u help me out with zimbra mailbox Marcos automation I.e.., client webmail box I have searched the same on google but not use I hope on pls help out

When I open open the Format Cells dialogue box, the copy function on my chosen symbols drops out and I cannot paste into the format line. How do I get round that? Thank you.

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!

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

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.

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

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

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

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

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

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!

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

Hello What excel formula or function can I use in the following scenario: I have a worksheet with the following columns Date, Payee, Amount, Country (selected from drop down list), Analysis columns (with Country names same as those in drop down list) I need help in coming up with formula or function so that once I enter amount and pick a country its automatically posted in column with same country name selected. All details entered will be in same row.

Hi ma'am, can you also suggest how to sum 50 columns and which contains text and special character and every cell has different length and also can we seperate number, text and special characters in different cells irrespective of there position ? pleas suggest and thanks.

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