This blog is translated from German with DeepL.
Conditional formatting and not quite everyday functions
1. Conditional formattings
Should a field in Excel be colored as long as it is empty? Or should a frame be drawn around a field as soon as something is written in it? Then we can resort to conditional formatting.
A short tutorial on how to do this:
1.1 Click on the icon “Conditional Formatting” in the start menu of Excel.
1.2 The menu opens
1.3 The following new window opens at:
- New rule: directly create a new rule (conditional formatting).
- Delete rules: Delete rules in selected cells or in the whole worksheet.
- Manage rules: A submenu opens here:
1.3. I.
- Define selection
- Selected cells / current selection
- This worksheet
- List of all worksheets
1.3. II.
- Creating a new rule
1.3. III.
- Listing of existing rules with details
1.4 Clicking on “New rule” opens another window:
With a click on the second position (I.) the lower menu of the window (II.) becomes visible.
The cell value can now be set here:
Scenarios:
I want to format only cells whose content is greater than 2 and less than 6 (here can also be referenced to a cell).
-> Cell value / between / 2 / 6
I only want to format cells that are empty (e.g. in a form for a better overview, where I still have to enter something).
-> cell value / equal / =”” where “” symbolizes an empty cell
I want to format only cells whose content is greater than the number 5 (here can also be referenced to a cell).
-> cell value / greater than / 5
I want to format only cells with content xyz.
-> Specific text / with content / xyz
I want to format only cells where zyx does not occur.
-> Specified text / without content / zyx
I want to format only cells whose content starts with abc.
-> Specified text / starting with / abc
I want to format only cells whose date is next week.
-> date / next week
For the more experienced Excel users, conditional formatting is also available with its own formula:
Scenarios:
If the cell in column A (per row) is not empty, I want to select the whole row from column A to column E.
-> =$A1<>””
-> In the rule administration window the rule is listed, then enter the desired range there, in this case: =$A:$E
I want to format only cells whose value is less than 5 or greater than 10.
-> =OR(A1<5;A1>10)
-> Then define range in the management window (if the formatting (III.) should be not only in the already selected cells).
III: Formatting:
The formatting of the cell is then done using this button. Do I want to draw a border around each cell with content, should empty cells be colored yellow, red, black, etc.?
2. Not quite everyday functions
Formulas like SUM, IF or of course simple operations like +, -, *, / are familiar even to the less experienced Excel users among us. What else does Excel actually offer? This question was probably one of the reasons why I was affectionately called an Excel nerd internally. In the following I will show you a few other formulas that Excel nerds sometimes need.
2.1 Concatenate
2.2. Total
2.3 Matrix formula calculations
2.4. Rounds
2.5 Rounding up / rounding down
2.6 Counting non-empty cells in a range
2.7. Count number of cells containing a number
2.8 Add text to a formula / result
2.9 Include multiple dependencies in formula
2.10. Output line number
Conclusion:
I hope that these tips can help you in your daily work with Excel or make a few things easier. Of course there are countless other functions and tricks and partly even much more complex formulas up to the “macros”.
But this should be only a few useful user tips, because Excel is very complex and has many functions and uses. If you have any questions or even want an individual training, please feel free to contact us.
Remark:
We have created these examples with Excel 2010, but they should work up to the current Excel version 2016 and also in the older versions like 2007.
