|
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.
English Excel
=SEARCH($D$5,B4)
Excel in Czech
=HLEDAT($D$5;B4)
File Conditional Formatting Mark Word
free downloads, for Excel 2007 and later.
Mark row (part of row) depending on the condition in the designated cell (the same line). One condition.
=$A4="green"
=$A4="yellow"
=$A4="red"
File Conditional formatting: for row, in cell condition
free downloads.
Mark row (part of row) depending on the condition in the designated cell (the same line). Conditions may be more on the line.
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")
File Conditional formatting: row cell more condition
free downloads.
Mark locked/unlocked cells. Cells which is/is not applied the lock.
English Excel
=CELL("protect";A1)=0
Excel in Czech language
=POLÍČKO("zámek";A1)=0
File Conditional formatting: un-locked cells
free downloads.
Mark numbers (values) at every change, if the same value - do not change color cells.
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
File Conditional formatting: Change in value
free downloads.
Conditional formatting based on values in a hidden column. Example for tests, quizzes, testing of vocabulary in a foreign language.
English Excel
=AND($C14<>$E14,LEN($E14)>0)
=$E14=$C14
Excel in Czech language
=A($C14<>$E14;DÉLKA($E14)>0)
=$E14=$C14
File Conditional formatting for Quiz
free downloads.
In the area mark searched value.
=A6=$A$16
File Conditional formatting find and marked cells
free downloads.
Mark even (odd) number. Again, can be applied to the all cells in row.
=ISODD(E4)
=ISEVEN(E4)
File Conditional formatting even/odd
free downloads.
Mark the days that we need (Monday, Wednesday, ..). Practical use calendars to mark Saturday, Sunday.
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)
File Conditional formatting day of the week
free downloads.
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).
=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")
File Conditional formatting: Cursor marking
free downloads.
Mark the line if the given cell in a row has/does not have the required number of char (letter).
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)
File Conditional formatting: Count of letter
free downloads.
Marking line based on date, time (for example, check paid invoices).
=$B$4>$B7
in B4 is function =TODAY()
File Conditional formatting: Control date/time
free downloads.
Marking columns based on conditions in the cell (in the marking column)
=A$5="green"
=A$5="yellow"
=A$5="red"
File Conditional formatting column: cell condition
free downloads.
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
For further development of this web site.
Thank you for donations.
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ů.
Můžete být prvními co zanechají smysluplný komentář.
Pomohl Vám návod? Sdílejte na Facebooku, G+ |
||
LinkedIn... |
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 - 2021 |