Excel Highlight rows and records

there are many ways of managing an Excel worksheet what we ultimately want to end up with is having Excel work for us and otherwise help us identify information on the fly visually so what if we had a worksheet that looked like this that had a date column region Department item number and so on and if I were in charge of managing this data it might be part of my job to go through and look at departments and identify say the toys fields and look for that specific information on that record well in this example we're going to look at how we can create a little drop-down list over here and here we have a list of all the available departments that coincide with column C right over here so if I come over here and click on electronics this little feature highlights those specific records for me if I come again and go to toys it'll specify those records as well now I can scroll through my worksheet and review that information let's take a look as to how we created this here we are in the raw data the worksheet that I might have otherwise received from a colleague or maybe my manager and I was asked to highlight specific records in this data set housewares or so on well if I'm asked to do this I might go so far as to select a row or in this case a record and then I might can come up and highlight that and let's see if I scroll through and find the next one here's housewares and then I can highlight that now here's one I missed here's high housewares and I can highlight that that is a incredible waste of time in managing this worksheet and if you're doing that now I'm going to show you an easier way of doing it so I just clicked undo to remove that now there are several steps involved in creating a drop-down list and then applying a conditional format to the data set that will reference that def that drop-down list let's take a look the first thing we need to do is select our column that we want to search for so in this case I'm going to go to this first cell here and I'm going to press shift control arrow key down and that selects the entire column of information I can see it's on the 300th row once I have it selected I'll copy this information I'm going to come to this next worksheet and create a new worksheet and then up here I'll just press control V to paste it I'll with it selected still what I want to do is I want to remove all the duplicates that will leave me with the appropriate number of items listed here so the way I do this I'm going to go to my data tab over in the tools group I'm going to click remove duplicates it'll ask me in the dialog box here which column do I want to remove duplicates from well there's only one selected column a I'll click OK tells me the number of duplicates found and removed I'm going to click OK and now I have my list of unique values or departments in this case so now that's done I'm going to come back to my worksheet here I'm going to press control home now I'm at the beginning of my document now right over here I'll create a little label or header called Department I'll press tab and then autofit column L and I'm going to widen column m just a little bit because I know we're going to create our drop-down list right here now with this cell selected I'm going to come up to my data tab and in the data tools group I'm going to click data validation in the Settings tab of my dialog box where it says allow I'm going to click the drop down here and choose list so that I can create a drop-down list now I'm going to click inside the source and with my insertion point blinking right there I'm simply going to come down and click on sheet 1 that's where my list is I'm going to select this list and that's it I'll click OK and I can see my drop-down arrow is right there now this is only visible when I have the cell selected if I click off the cell I don't see anything so I want to accentuate this area I'm going to click back on this cell and I'm going to give this a thick border so I'll come to my home tab coming to my font group and click my border drop-down and choose this thick box border so now when I don't have it selected it has a thick box border around it and let's come back and click the drop-down and see that indeed it did list this drop-down list that's great that'll make nice use later on if I send this to somebody else I can tell them simply click in the department you want to accentuate or highlight click the drop-down and choose what you want now that we have it all set up let's apply a conditional formatting formula so first I want to select my entire data set I'm going to come here to my first cell in my data set a 2 I'm going to press shift ctrl arrow key down it selects that column from there I'll press shift ctrl arrow key right and now I have my entire data set selected I'll then scroll all the way back to the top so that I can see this first record or first row row 2 now from there I'm going to come here to my home tab and then I click conditional formatting and then I come down here to new rule now from here I'm going to come down and choose use a formula to determine which cells to format that will convert the dialog box and now I'll click inside this area here where I type in my formula so what I want to do in this case is I want to identify the first cell in the column that I want to search in this case Department now you'll notice that this is absolute cell referenced meaning the column is locked and the cell is locked well I only want to lock the column so I'm going to press f4 two times and that converts this over to locking the column only meaning that when this formula is ran it will run the to search the entire data set that's been selected while looking for information in this column d I'll then say equals and then I'll click this cell here with my drop-down list it's really that simple now I'm going to come down and click on format' because I want to identify these particular records with a specific fill color so I'm going to come to my fill tab here and I'll use the yellow that's the go to better yet let's use light green just because that's fun click OK and then from here I'll click OK again deselect nothing's really happen yet why because nothing's been identified in this cell here so I'll click here click the drop down let's choose toys well what do you know pretty clever what if I click this drop down again and choose clothing nice so as I scroll through all of those records are identified with a light green now of course I'll notice that when I scroll down the header doesn't move down with me so I want to change that I'm going to select these two cells here because they're down below the header row I'm going to just drag them up a little bit so now they're up here so now I can lock this first row in place I'll select this cell here go to my View tab and in the window group I'm going to click freeze panes and then click freeze panes again so now when I scroll down that stays with it so if I'm down here and say I want to change this from clothing just click right here click the drop down list choose Auto Wow very nice let's Chromebook scroll back to the top and there you have it I hope this comes in handy for you and if you liked it and you've learned it real well I sure would like to see you share it with all your friends and neighbors and empower them with the knowledge as well thanks for joining me

Channel: Shiftkeycom

Views: 545,527

Rating: 4.8739738 out of 5

Keywords: shiftkeycom, Excel, Microsoft, records, highlight, dropdown, list

Id: WfHAXe_IgO8

Length: 8min 41sec (521 seconds)

Published: Tue Nov 22 2016