Preskoči na glavno vsebino

Kako spremeniti barvo ozadja ali pisave glede na vrednost celice v Excelu?

Ko se v Excelu ukvarjate z ogromnimi podatki, boste morda želeli izbrati nekaj vrednosti in jih označiti z določeno barvo ozadja ali pisave. Ta članek govori o tem, kako hitro spremeniti barvo ozadja ali pisave glede na vrednosti celic v Excelu.


1. način: Dinamično spremenite barvo ozadja ali pisave glede na vrednost celice s pogojnim oblikovanjem

 

O Pogojno oblikovanje funkcija vam lahko pomaga, da označite vrednosti, večje od x, manjše od y ali med x in y.

Recimo, da imate nabor podatkov in zdaj morate barve pobarvati med 80 in 100, naredite naslednje:

1. Izberite obseg celic, ki jih želite označiti, in kliknite HOME > Pogojno oblikovanje > Novo pravilo, glej posnetek zaslona:

doc poudari po vrednosti 1

2. v Novo pravilo oblikovanja v pogovornem oknu izberite Oblikujte samo celice, ki vsebujejo element v Izberite vrsto pravila in v polju Formatiraj samo celice z v razdelku navedite pogoje, ki jih potrebujete:

  • V prvem spustnem polju izberite Vrednost celice;
  • V drugem spustnem polju izberite merila:med;
  • V tretje in četrto polje vnesite pogoje filtra, na primer 80, 100.

doc poudari po vrednosti 2

3. Nato kliknite oblikovana v gumbu Oblikuj celice v pogovornem oknu nastavite barvo ozadja ali pisave:

Spremenite barvo ozadja glede na vrednost celice: Spremenite barvo pisave glede na vrednost celice
klik Izpolnite in izberite eno barvo ozadja, ki vam je všeč klik Pisava in izberite želeno barvo pisave.
doc poudari po vrednosti 3 doc poudari po vrednosti 4

4. Ko izberete barvo ozadja ali pisave, kliknite OK > OK Če želite zapreti pogovorna okna, se zdaj določene celice z vrednostjo med 80 in 100 spremenijo v določeno barvo ozadja ali pisave v izboru. Oglejte si posnetek zaslona:

Označite določene celice z barvo ozadja: Označite določene celice z barvo pisave:
doc poudari po vrednosti 5 doc poudari po vrednosti 6

Opombe: Pogojno oblikovanje je dinamična lastnost, se bo barva celice spreminjala s spreminjanjem podatkov.


2. način: Statično spremenite barvo ozadja ali pisave glede na vrednost celice s funkcijo Najdi

 

Včasih morate uporabiti določeno barvo polnila ali pisave glede na vrednost celice in poskrbeti, da se barva polnila ali pisave ne spremeni, ko se vrednost celice spremeni. V tem primeru lahko uporabite Najdi funkcija, da poišče vse določene vrednosti celic in nato spremeni barvo ozadja ali pisave po svoji potrebi.

Če želite na primer spremeniti barvo ozadja ali pisave, če vrednost celice vsebuje besedilo »Excel«, storite to:

1. Izberite obseg podatkov, ki ga želite uporabiti, in kliknite HOME > Poiščite in izberite > Najdi, glej posnetek zaslona:

doc poudari po vrednosti 7

2. v Poišči in zamenjaj v pogovornem oknu pod Najdi zavihek, vnesite vrednost, ki jo želite najti, v Našli kaj besedilno polje, glej posnetek zaslona:

doc poudari po vrednosti 8

nasveti: Če želite poiskati vrednosti, ki razlikujejo med velikimi in malimi črkami ali se ujemajo s celotno vsebino celice, kliknite možnosti , da dobite napredne možnosti iskanja, na primer "Match Case"In"Ujema se s celotno vsebino celice"kot potrebujete.

3. In nato kliknite Poišči vse v polju z rezultatom iskanja kliknite kateri koli element in pritisnite Ctrl + če želite izbrati vse najdene predmete, si oglejte posnetek zaslona:

doc poudari po vrednosti 9

4. Končno kliknite Zapri , da zaprete to pogovorno okno. Zdaj lahko za te izbrane vrednosti napolnite barvo ozadja ali pisave, glejte posnetek zaslona:

Uporabi barvo ozadja za izbrane celice: Uporabi barvo pisave za izbrane celice:
doc poudari po vrednosti 10 doc poudari po vrednosti 11

3. način: Statično spremenite barvo ozadja ali pisave glede na vrednost celice s programom Kutools za Excel

 

Kutools za ExcelJe Super najdi Funkcija podpira veliko pogojev za iskanje vrednosti, besedilnih nizov, datumov, formul, formatiranih celic itd. Po iskanju in izbiri ujemajočih se celic lahko spremenite barvo ozadja ali pisave na želeno.

Po namestitvi Kutools za Excel, naredite tako:

1. Izberite obseg podatkov, ki ga želite najti, in kliknite Kutools > Super najdi, glej posnetek zaslona:

doc poudari po vrednosti 12

2. v Super najdi podokno, naredite naslednje:

  • (1.) Najprej kliknite Vrednote ikona možnosti;
  • (2.) Izberite obseg iskanja med V okviru spustite, v tem primeru bom izbral izbor;
  • (3.) Iz tip s spustnega seznama izberite merila, ki jih želite uporabiti;
  • (4.) Nato kliknite Najdi gumb za seznam vseh ustreznih rezultatov v seznamsko polje;
  • (5.) Končno kliknite Izberite , da izberete celice.

doc poudari po vrednosti 13

3. Nato so bile naenkrat izbrane vse celice, ki ustrezajo merilom, oglejte si posnetek zaslona:

doc poudari po vrednosti 14

4. Zdaj lahko spremenite barvo ozadja ali barvo pisave za izbrane celice, kot potrebujete.


Zasedeno delo ob koncu tedna, Uporaba Kutools za Excel,
vam daje sproščujoč in vesel vikend!

Ob koncu tedna otroci prosijo, naj se igrajo, toda preveč dela vas obdaja, da bi lahko spremljali družino. Sonce, plaža in morje tako daleč? Kutools za Excel vam pomaga rešite Excelove uganke, prihranite delovni čas.

pic plaža
  •  Pridobite napredovanje in povišajte plačo;
  •  Vsebuje napredne funkcije, rešuje aplikacijske scenarije, nekatere funkcije celo prihranijo 99 % delovnega časa;
  •  V 3 minutah postanite strokovnjak za Excel in pridobite priznanje svojih kolegov ali prijateljev;
  •  Ni vam več treba iskati Googlovih rešitev, poslovite se od bolečih formul in kod VBA;
  •  Vse ponovljene operacije lahko zaključite z le nekaj kliki, sprostite utrujene roke;
  •  Le 39 dolarjev, vendar vreden kot vaja za Excel za 4000 dolarjev;
  •  Izbere naj vas 110,000 elit in 300+ znanih podjetij;
  •  30-dnevno brezplačno preskusno obdobje in vračilo celotnega denarja v 60-dnevnem roku brez kakršnega koli razloga;
  •  Spremenite svoj način dela in nato spremenite svoj življenjski slog!
 

Comments (79)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Good Day,

I have a schedule with different course modules that can be placed in different order ( 1 is metal, 2 is machining, 3 is refinishing, etc.) depending on course director preferences and staff availability.

What I am trying to do to wrap up the spreadsheet is color code the modules across the months based on their order in the overall course. So if Metal is first and is 35 days long, the first 35 days are background colored blue, but if machining is first and is 20 days long, then the first 20 days are purple.

How can you do and if statement that refers to a separate cell for the initial number of days, AND color the cells based on the range between 1 and 20 or 35 or whatever?

Can I do a conditional formatting If statement with a range between numbers?

This would have to be applied to all the modules (therefor multiple conditional formatting formulas) to account for any module being in any place in the order.
That is the issue I have. Please offer any suggestions. Note that I have built the spreadsheet without VBA. (I've never had the opportunity to learn it)
This comment was minimized by the moderator on the site
Can I give colour for a row of values with respect to previous value for a whole row.
Eg

23

45

32

56

can I give red for 23 and green for 45 and again red for 32 as it is less than the previous value and green for 56 as it is more than 32.
This comment was minimized by the moderator on the site
I do get to change a cell colour according to the text or letter in fill in the cell e.g.-H=YELLOW,E=GREEN
This comment was minimized by the moderator on the site
Hello, VANESSA,
For solving your problem, please apply the Conditional Formatting feature, in the New Formatting Rule dialog box, choose the conditions as following screenshot shown:
This comment was minimized by the moderator on the site
how do i change the colour of cell depending on what is in that cell IE. Y=green N=red.

thats how i need it if it has Y riten in the cell it turns green?
This comment was minimized by the moderator on the site
Hello, Leon,
To highlight the cells based on the cell value, you should apply the Conditional Formatting feature, go to the New Formatting Rule dialog box, and then do as the following screenshot shown to highlight the cells which are Y located as green:

Do with the same way to format the cells N as red color.

Please try it, hope it can help you!
This comment was minimized by the moderator on the site
Trying to get a cell to highlight if its above a certain number, the problem is that the cell includes the number and a date. Is there a way i can get it to just look at the number? The cell will include a number+date.....1400(7/2/2018). I need the formulate just to look at the 1400 and not the date.
any ideas?
thanks
Z
This comment was minimized by the moderator on the site
Sir, I have value in cell A10, in A12. Now I entered a simple formula in A15 subtracting these (A10-A12). I want background color of result cell (A15) in green if A10 is large (means result in positive number) and background color in red if A12 is large ( result in negative number). Please provide solution
This comment was minimized by the moderator on the site
Hello,
You can select the formula cells, and then apply the Conditional Formatting > Greater than (0)-format it green, and then Less Than (0)-format it red, see screenshot:

Please try it, hope it can help you, thank you!
This comment was minimized by the moderator on the site
If I have a date in a cell, how do I make it change to orange when it is within 30 days of coming due, yellow when it is within 15 days of coming due and red when it exceeds the date (past due)
This comment was minimized by the moderator on the site
I thought this color of cells by value was going to be a real pain. It could not have been explained more easily and clearly. Thanks. It took no less than 1-2 minutes to understand what to do.
This comment was minimized by the moderator on the site
HI, I want if i change any value or number in any cell then that cell text color should get change for e.g. if number in any cell is 100 and I have to change it as 98 then that cell text 98 should change into red color. Is it possible in Excel?? Do clarify please
This comment was minimized by the moderator on the site
So I am creating a excel spreadsheet for fire department reports. What I am looking for is it to calculate the days a report is overdue, unless it has been completed: Column A Column B Column C Column D Column E Column F Incident # Incident Date Todays Date Completed Date Days Overdue Todays Date I want column B subtracting Column C to calculate in column E unless Column D is complete. I hope this makes sense. I want to use the conditional formatting to show me in colors how long a report is overdue (with the 3 color scale). I have the formula for the number of days it is overdue to have a correct value and color scale, but I want it to remain green when the report has been completed.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations