Simplify Your Operations with Bilty Format in Excel for Operations

Bilty format in excel for Operations

today i want to show you how you could use conditional formatting in microsoft excel now conditional formatting is a really valuable tool that you could use to visualize your data and to see trends and patterns in your data it might sound a little terrifying but it's really not let's jump into it and i'll show you how to do it so here i am in excel and just as full disclosure before we get going here i work at microsoft as a full-time employee okay well so how can you work with conditional formatting well let's say that i'm a teacher in school and let's say that i just recently had everyone take a test on i don't know maybe conditional formatting and so these are all the grades that came back we have some people who performed well and others who didn't perform so well of course if i were the teacher i would expect all the grades to be a little higher but hey we're just doing this as an example so let's say i have all these grades and i want to see who performed below in 80. so what i could do is you have this button up here under the home tab called conditional formatting if you hover over it you'll basically see what it does so once again you can see trends and patterns in your data and so let's click on that and let's try it out so the first item we see here is highlight cells rule and what i could do is you see options like greater than so if the value is greater than something you can highlight it if it's less than something you can highlight it that's exactly what we want to do we want to see people who scored below in 80. so i'm going to click on this option and what i want to do is let's say if they scored below in 80. so i'll type in 80 there and what i could do then is i could format the text in different ways so i could do a light red fill yellow green uh we're not going to do a green because it's not a good thing when you get a low score on a test so we're going to go with the red but i can also do you know red text red border i could also do custom formatting so let's say i wanted to bold it i wanted to highlight you know pretty much anything i want to format it with i could do within this view but i'm going to cancel that and come back to here and i like that the red fill so i'll just click on okay and so here very quickly now you'll see all these people so sophia she got a 76 so she's working hard but didn't quite make it uh anise i don't know what to do with her she just got a d equivalent um deborah she doesn't really seem to care that much but she still got you know half the questions right at a 51. and so you can see how the data gets highlighted here so very quickly i could see who's struggling in my class so i'm going to undo that and now we're going to go on to this next data set let's say that i run a restaurant and i had customers recently submit reviews on the food you know some people love the food it's great yes awesome other people someone told me the chicken doesn't taste like chicken all right we might need to dig into that review that sounds bad so what i'm going to do is let's highlight all the dates and i'm going to go back to conditional formatting highlight cells rules and here you can see a date occurring so let's say that i want to read all reviews from customers within the last week so i just want to see recent data i can click here and here you see all these different views and i'll say in the last seven days and once again i could format how i want to format it um so this isn't necessarily a bad thing that's been in the last week so i'm just going to go with a let's just go with a yellow fill so i'll click ok and then here you can see that wow i have some different conflicting points of view one person said the food's great the other person said the chicken didn't taste like chicken i don't know what's going on in my kitchen but i'll probably need to talk to the staff there but here very quickly i could just see what all my most recent feedback was thanks to conditional formatting the next thing i want to do is let's say i have all these students in my class i typed this list late at night and i might have typed in some duplicate values how can i get rid of the duplicates without going through the list one by one well what i can do is once again conditional formatting i'll click on highlight cells rules and there's something called duplicate values so i'm going to click on duplicate values and very quickly you'll see once again i could you know if i could i could either say duplicate values so if it occurs multiple times or identify all the unique values so basically the inverse but i'll do all the duplicate values and here i could see that i typed in kevin shaw and kerry west more than once and so i could delete those and get rid of those duplicates so i'm going to go back here and we're going to go see what else we can do with conditional formatting so let's go back to the conditional formatting view they also have something called top bottom rules and so what you can see is i want to see the top 10 items are the top 10 so let's see the top 10 percent or maybe let's say the top 20 of people so here that i could see john megan michael jacob and denise were in the top 10 percent of the class and what i could also do is if i go back to the top bottom i could do the bottom say 10 items the bottom 10 percent above average or below average so let's see all these people who are above the average and then all the people who are below the average so are you in the top 50 percent of the class or the bottom 50 percent of the class so conditional formatting lets me very quickly visualize the data in a way that i can make sense of it once again i'm going to click in here what i can also do is they have something called data bars so you'll see how it just places bars that corresponds with the value of the test score that they had i could also do color scales so here i can see who got the highest which is in bright green and then who got the lowest which is in red and then all the colors in between so you can very quickly visually see who did well versus who didn't and then they also have icon sets so i could do it with arrows or i could do it with circles so if you did well you get a green circle if you did okay you get yellow if you really didn't do well you get a red circle and then what i can also do is there's this thing called new rule and so this is now getting even giving you even more power and control over what you could do with conditional formatting so here i can format cells based on value and i could say you know the highest value a number percent a formula percentile so you can really define what you're looking for and then format it in a different way you could say cells that contain and you could say between these values equal to the top or bottom ranked values above or below the average unique or duplicate values or you can even use a formula to apply conditional formatting and if the formula resolves to true then it'll apply that formatting so there's really lots of powerful things that you can do with conditional formatting to be able to visualize and see patterns in your data this is just a very quick overview and a few kind of fun scenarios that you could use to be able to spot different trends in your data if if this was helpful and now you're using conditional formatting that comes with excel feel free to leave a like if you want to see more content like this hit that subscribe button and lastly if there are any other topics that you want to learn about feel free to leave a comment down below and i'll add it to my list of videos to make and once again uh this version of excel that i'm using this is this is office 365 so it's the latest version it'll work with 2019 i can't guarantee that it will work with older versions of excel but conditional formatting has been around for a while so chances are it'll work for you all right well thanks a lot everyone and i'll see you next time bye