Přeskočit navigaci | Přeskočit na novinky
     

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ů:

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 Excel 2010 - practice

Conditional Formatting

(1) Conditional Formatting Mark Word

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 File format *.xlsx 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.

Conditional formatting: for row, in cell condition

Practical Solutions

=$A4="green" =$A4="yellow" =$A4="red"

Downloads

File Conditional formatting: for row, in cell condition File *.xlsx 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.

Conditional formatting: row cell more condition

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 File *.xlsx free downloads.

(4) Conditional formatting: un-locked cells

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

conditional formatting: un-locked cells

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 File *.xlsx free downloads.

(5) Conditional formatting: Change in value

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

Conditional formatting: Change in value

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 File *.xlsx 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.

Conditional formatting for Quiz

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 File *.xlsx free downloads.

(7) Conditional formatting find and marked cells

In the area mark searched value.

conditional formatting

Practical Solutions

=A6=$A$16

Downloads

File Conditional formatting find and marked cells File *.xlsx free downloads.

(8) Conditional formatting even/odd

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

conditional formatting even/odd

Practical Solutions

=ISODD(E4) =ISEVEN(E4)

Downloads

File Conditional formatting even/odd File *.xlsx free downloads.

(9) Conditional formatting day of the week

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

conditional formatting: day of the week

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 File *.xlsx 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).

Conditional formatting: Cursor marking

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 File *.xls 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).

conditional formatting

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 File *.xlsx 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 File *.xls free downloads.

(13) Conditional formatting column: cell condition

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

Conditional formatting column: cell condition

Practical Solutions

=A$5="green" =A$5="yellow" =A$5="red"

Downloads

File Conditional formatting column: cell condition File *.xlsx 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: 23.01.2015 19:14

Donate

For further development of this web site.

Thank you for donations.


Pavel Lasák - autor webu

Pavel Lasák

Microsoft Office (Word, Excel, PowerPoint) se věnuji od roku 2000 (od 2004 ne této doméně) - Roku 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 také na on-line videích pro SEDUO. Školím a konzultuji, učím na MUNI. Hlavně tvořím tento web. Je zde k dispozici přes 1.000 návodu, tipů a triků včetně stovek různých šablon, sešitů.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.







Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


Newsletter

Přihlaste se odběru novinek a získejte:
Ebook zdarma -
10 kroků ke zvládnutí (Word, Excel, PowerPoint)
Šipka Kniha 3D Více o ebooku ...

TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2017 | 3074

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.