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 - 2024 |