Discover the Best Bilty Format in Excel for Entertainment

Bilty format in excel for Entertainment

hello and welcome to format items in a list my name's jefff I'm glad you're here let's just jump right in I was recently asked the following question how do I format values that appear in a list and I'm going to answer that question in this video exercise one all right the function that we're going to use is match let's just get warmed up with the match function equals match basically we're going to ask the match function to try to find this comma in here comma and zero for exact match close the function and enter so what the match function is saying is that January is found in the first row within this list Feb is found in the second row June is found in the sixth row December is found in the 12th row what if it's not found so what if we type in like q1 well it's not found so it gives us an error so the match function tries to find a value in a list if it's found it Returns the position otherwise it returns an error and with that intro complete let's move to the next exercise exercise two so going back to the original question we'd like to highlight these transactions when the status is found in a list so what we need is a Formula that returns a true or false value if the status is found in this list and as you can imagine the match function is going to be the basis for this formula equals match we want to find this comma in this list and I'll press F4 to create the absolute reference comma 0 for exact match close the function and enter and now we can fill this formula down and now we can see when this status is not in the list we get an error if it is in the list we get a number so we can easily convert this to a true or false result simply by wrapping the is number function around the match function let's fill this updated version down so now we can apply conditional formatting in this exercise we're just going to format at this highlight column but in the next exercise we'll highlight the status column instead so we select the cells go to conditional formatting highlight cell rules equal to and we say if this is equal to true then we can pick whatever format we want and click okay and this is how we can highlight the transactions when the status value is found in a list and those are the basics but we can definitely improve it so let's go to the next exercise exercise three the first Improvement we'll make is we're going to convert this status range into a table and that way it's easy to add new statuses that we want to be highlighted later to do that we simply select the range and click insert table now we could conditionally format the ID column the status column the amount column or all of the columns depending on your personal preference regardless the steps are the same we select the range that we want to conditionally format then we go to conditional formatting new rule and here we want to use a formula to determine which cells to format and our our formula is basically going to be the same logic that we did before equals is number match and what we want to do is we want to match the status column value which is currently in c14 so we type in c14 but this is where we need to be careful because this formula is going to be applied to all of the columns that are selected so we need to be careful about absolute versus relative cell references so as this formatting formula is filled through this range we need to lock the column reference onto column C to do that we're going to put a dollar sign in front of c and as this formatting formula is filled down throughout the range we want that to be a relative row reference so we're going to leave the row reference 14 without a dollar sign so we want to match that in this range zero for exact match we close the match function and close the is number function and then we can pick a format here we can pick any format we like click okay and click okay and now we can see that conditional formatting is applied to any rows where the status is found in our status list and since we stored the status values in a table this is totally Dynamic for example we could add processing and hit enter and now processing is highlighted as well and circling back to the original question that's how we can format items that are found in a list hopefully this helped thanks for joining me have a great day hey Excel user if you ever need to create summary reports check out my pivot table for beginners video it starts at the beginning and shows how to store the data transactions in a table and then how to summarize those transactions with a pivot table report I hope it helps unlock the incredible power of pivot tables this video is a production of excel University [Music]