Jste zde: Úvodní stránka » excel » articles-in-english » Conditional-formatting-Practical-examples-Excel

Conditional formatting - 13. Practical examples Excel

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Jak na Excel - naučte se efektivně využívat Excel - nejoblíbenější (Hodnocení 97%, přes 13.000 studentů, + 750 pozitivních zpětných vazeb) - Získat kurz
MaxiKurz Excel - získejte lepší práci, více peněz, staňte se nepostradatelní (přes 6 hodin videí, 160 lekcí) - Získat kurz
G tabulky... - pokud nemáte rádi Excel využijte kurz na Google-tabulky

How to conditional formatting in Microsoft Excel. - Coloring rows, columns, cells. Marking based on specified terms (date, number, text). Presented on practical examples.

List examples of conditional formatting

Conditional Formatting

(1) Mark Word
(2) For row, in cell condition
(3) Row cell more condition
(4) Mark un-locked cells
(5) Change in value
(6) Formatting for Quiz
(7) Find and marked cells in arrea
(8) Even / Odd marking
(9) Day of the week (Monday, Wednesday, ..)
(10) Cursor marking - active cell
(11) Count of letter
(12) Marking date/time
(13) Marking column: cell condition
... in work

(1) Conditional Formatting Mark Word

Practical Solutions

English Excel


=SEARCH($D$5,B4)

Excel in Czech


=HLEDAT($D$5;B4)

Downloads

File Conditional Formatting Mark Word free downloads, for Excel 2007 and later.

(2) Conditional formatting: for row, in cell condition

Mark row (part of row) depending on the condition in the designated cell (the same line). One condition.

Practical Solutions


=$A4="green"

=$A4="yellow"

=$A4="red"

Downloads

File Conditional formatting: for row, in cell condition free downloads.

(3) Conditional formatting: row cell more condition

Mark row (part of row) depending on the condition in the designated cell (the same line). Conditions may be more on the line.

Practical Solutions

English Excel


=AND($A5="green",$B5="green")

=AND($A5="yellow1",$B5="yellow2")

=AND($A5="red",$B5="red")

Excel in Czech language


=A($A5="green";$B5="green")
=A($A5="yellow1";$B5="yellow2")
=A($A5="red";$B5="red")

Downloads

File Conditional formatting: row cell more condition free downloads.

(4) Conditional formatting: un-locked cells

Mark locked/unlocked cells. Cells which is/is not applied the lock.

Practical Solutions

English Excel


=CELL("protect";A1)=0

Excel in Czech language


=POLÍČKO("zámek";A1)=0

Downloads

File Conditional formatting: un-locked cells free downloads.

(5) Conditional formatting: Change in value

Mark numbers (values) at every change, if the same value - do not change color cells.

Practical Solutions

English Excel


=MOD(SUM(($C3:$C$3<>$C4:$C$4)/1),2)=1

Excel in Czech language


=MOD(SUMA(($C3:$C$3<>$C4:$C$4)/1);2)=1

Downloads

File Conditional formatting: Change in value free downloads.

(6) Conditional formatting for Quiz

Conditional formatting based on values in a hidden column. Example for tests, quizzes, testing of vocabulary in a foreign language.

Practical Solutions

English Excel


=AND($C14<>$E14,LEN($E14)>0)
=$E14=$C14

Excel in Czech language


=A($C14<>$E14;DÉLKA($E14)>0)
=$E14=$C14

Downloads

File Conditional formatting for Quiz free downloads.

(7) Conditional formatting find and marked cells

In the area mark searched value.

Practical Solutions


=A6=$A$16

Downloads

File Conditional formatting find and marked cells free downloads.

(8) Conditional formatting even/odd

Mark even (odd) number. Again, can be applied to the all cells in row.

Practical Solutions


=ISODD(E4)

=ISEVEN(E4)

Downloads

File Conditional formatting even/odd free downloads.

(9) Conditional formatting day of the week

Mark the days that we need (Monday, Wednesday, ..). Practical use calendars to mark Saturday, Sunday.

Practical Solutions

Excel in EN


'Saturday
=WEEKDAY(B5,2)=6

'Tuesday
=WEEKDAY(D5,2)=2

'Saturday and Sunday
=OR(WEEKDAY(F5,2)=6,WEEKDAY(F5,2)=7)

Excel in Czech language


'Saturday
=DENTÝDNE(B5;2)=6

'Tuesday
=DENTÝDNE(D5;2)=2

'Saturday and Sunday
=NEBO(DENTÝDNE(F5;2)=6;DENTÝDNE(F5;2)=7)

Downloads

File Conditional formatting day of the week free downloads.

(10) Conditional formatting: Cursor marking

Using cross out where there is an active cell. Note for the correct function enabled VBA macros. Note for the correct function enabled VBA macros. Otherwise, every time if you move, you muss recalculate the sheet (F9).

Practical Solutions


=ROW()=CELL("row")

=COLUMN()=CELL("Col")

Code for VBA


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ActiveSheet.Calculate
End Sub

Excel in Czech language


=ŘÁDEK()=POLÍČKO("Řádek")
=SLOUPEC()=POLÍČKO("Sloupec")

Downloads

File Conditional formatting: Cursor marking free downloads.

(11) Conditional formatting: Count of letter

Mark the line if the given cell in a row has/does not have the required number of char (letter).

Practical Solutions

or more then 6 Char (Letter)


=(LEN($B5)>6)

or <>5 Char (Letter)


=(LEN($B15)<>5)

Excel in Czech language


=(DÉLKA($B5)>6)
=(DÉLKA($B15)<>5)

Downloads

File Conditional formatting: Count of letter free downloads.

(12) Conditional formatting: Control/Marking date/time

Marking line based on date, time (for example, check paid invoices).

Conditional formatting: Control date/time

Practical Solutions


=$B$4>$B7

in B4 is function =TODAY()

Downloads

File Conditional formatting: Control date/time free downloads.

(13) Conditional formatting column: cell condition

Marking columns based on conditions in the cell (in the marking column)

Practical Solutions


=A$5="green"

=A$5="yellow"

=A$5="red"

Downloads

File Conditional formatting column: cell condition free downloads.

Conditional formatting column: cell condition

More information in Czech language: Podmíněné formátování - Praktické příklady (CZ).

Článek byl aktualizován: 19.09.2020 10:56

Donate

For further development of this web site.

Thank you for donations.

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.