Приятного просмотра

Excel Drop Down List Including Cell Colour Change: Colour Fill

Опубликовано: 11 месяцев назад
53 971 просмотров
👎 9
Скопируйте и вставте на Ваш сайт


This video explains how to create a data validation drop down list in Excel that changes the fill (background) and/or font colour dependent on what is selected from the list: in other words a colour coded drop down. The change of colour is achieved through conditional formatting.
Blue Pecan provides onsite Excel training at your business premises.


Hi in this video we're going to learn how to create drop-down lists that also change the color of the sale when a particular value is chosen within the drop-down list so I've got a little database here and a couple of the fields priority and resolved need some drop-down lists now the values for those drop-down lists are on another sheet in the list sheet priority one day five day.

Thirty day resolved yes and now so to get those drop-down lists within the sheet on the start in the priority field I'm going to click into one sale only I'm going to go up to the data tab on my ribbon and in the data tools group I'm going to click on the data validation button the data validation dialog box I'm going to make sure I'm on the settings tab and in the allow menu I'm.

Going to select list then in the source box I'm going to go to the lists tab lists worksheet tab and I'm going to select my priority values 1 5 and 30 look when I came so now I have that drop-down list in this cell later on I'll show you how to copy easily copy or get Excel to automatically copy the drop-down lists down to other rows that you use within your database so resolved.

As the other field that needs to drop down this I click into that single cell and the data tab I go to data validation and the Settings tab I select list from the allow menu I click into the source box and then go to the lists worksheet and I select the two cells that hold the values that need to clear in this list and now I have yes and no so very simply within this field if I select no I want.

The background of the cell to go red just to indicate to the user that it's an unresolved issue so what I do is I select that cell I go to the Home tab on my ribbon though in the Styles group I click on the conditional formatting button I then go to highlight cell rules and within that menu I select equal to and the kindig the condition for this format is the.

Word no so if no appears in the cell by default ewar formatting with the red fill and dark red text if you didn't want that format you can choose a different format from the drop-down list so if I click on OK and I select no within that list I get a red background if I select yes I don't now for priority we do something slightly different we'll say while is obviously.

High-priority so maybe we will have a red background then amber and then green for a thirty-day priority so what I'm going to do is I'm going to go to conditional formatting highlight cell rules and then I'll say equal to 1 and for one we'll choose green so in that case if I chose 1 now it's going to be green in fact I think I said I was that's going to be red so this is quite.

Useful I've made a mistake here so how do I amend that I can go to conditional formatting you manage rules that brings up this dialog box conditional formatting rules manager so I select the rule that I've created make it to edit rule and now I can change the format so what I'm doing is I'm clicking on the format button and I'm going to change the rare the green background to red.

When I go to the font and let's say we had the font as white click on OK to put OK click on OK so now if I choose 1 I get a red background with white text to choose five or thirty I think it's gonna happen cuz I'm set up a rule for those values so let's continue to set up some roles so still that self selected conditional formatting highlight cell rules equal to.

Well the next value is fine and we're safe for that we're gonna have a yellow fill with dark red text so if I chose 5 we get a yellow background then I just need to do the 30 so I go conditional formatting highlight cell rules equal to 30 and we'll go for green different okay so if I choose 30 and now I'll get a green background so I've got conditional formatting on two of the.

Fields how do I get those rules just data validation rules for the drop-down menus to copy down to subsequent records and how do I get that conditional formatting to automatically copy down to subsequent reference well what I do is I convert this range that makes up my database into an Excel table so I select the fields that make up my database I go to the insert tab on.

My ribbon and I click on the table button this just confirms the range of cells that I'm using for my table and also that I have headers at the top of my tables I click on OK now if I just filled in this data today's date a reference number property 1 the street if you type roof priority 30 deadline well that would be equals this for us the 30 click on OK and I need to convert.

That to a date I only have to do this the first time so resolved no and that gives me a red background now if I press tab in the last cell of that record and come down here I put in a little date xx reference to to the street and that's a is guttering priority let's say on this occasion it's 5 so I'll get a yellow background deadlines automatically be worked out for me because that form has.

Been copied down you can see that that conditional formatting has been copied down and this would also be and now you can say I also get the red fell on this new referent and I'll continue to do that just press tab when I reach the end of the record and it automatically copied all of these features that have been added to the initial rows okay hopefully.

That's helpful creating drop-down lists that also change the color of the cell


James Oyet • 24 дня назад
Thank you for the useful video
👍 0
TJ Sloan • 2 месяца назад
Can you change the color of say cell A depending on what the color is in cell E? In other words I want to select an option in a dropdown box and have other cells turn the same color as the dropdown box.
👍 0
Kasi Reddy • 2 месяца назад
👍 0
structuralbd • 3 месяца назад
Nice video and perfectly explained, thanks
👍 1
christhehaitian • 4 месяца назад
Great video Chester!
👍 0
Divergence • 4 месяца назад
Excellent my man. really helps out with making my test case, look a little more professional. :D Thanks!
👍 0
Shaukat Samad • 5 месяцев назад
I Have tried to put color on yes in the same formula but couldn't worked, why becx fixing ..... formula..... pls advise
👍 0
Abdullah Fahad • 5 месяцев назад
Thanks for this useful tutorial video. I have date that I wanted to apply the formatting conditions on it but I couldn’t. I want to change the whole row based on a dropdown list “letters”. Any tips?
👍 0
Shaukat Samad • 5 месяцев назад
perfect, I have learn many lessons from u, n hope in future u ll do more ...... that v can go ahead
👍 0
Saibaby Barba • 5 месяцев назад
how to make a cell that has an drop down arrow?? heres the function that i want 1 main cell that has a drop arrow and when you click it an sub cell will appear and not the pick type cell?
👍 0
Jhay Torres • 6 месяцев назад
Quick question here, how about counting how many yes or no automatically?
👍 0
Men Ekasith • 6 месяцев назад
thank for this it help alot
👍 0
Rucha Ande • 6 месяцев назад
Very Useful Video...and to the point
👍 0
O J • 8 месяцев назад
Thanks, it helped a lot
👍 0
Stay Classy • 8 месяцев назад
👍 0
andrew vasquez • 8 месяцев назад
👍 0
Shawn Henry • 9 месяцев назад
Thank you!
👍 0
Sim Stander • 10 месяцев назад
thanks bro
👍 0