Useful Excel features, tips and tricks, no registration required.
Tables have the following features:
- 'speaking' formulas if items refer to other table cells
- changes to a formula are automatically updated in all other column cells
- tables grow automatically if a new value is entered below the table
- tables have a name to which e.g. a PivotTable can refer to as source
- for tables various pre-formatted layouts can be applied
It is a common problem when working with tables or lists: when a filter is set and applied the only indication is a very small triangle in the header cell. This feature helps to highlight the entire labels/headers in a background colour of your choice, if a filter is applied.
How often did you overwrite a cell with a formala by mistake, either a single cell or even an array of formulas? This feature helps to highlight cells, where a formula is expected but a value has been erroneously entered instead.
Cells with errors are often undedected for a long time which potentially may lead to severe calculation errors. This example shows how to count all cells with errors (e.g. #NV, #DIV/0, etc.) and bring it to the user's attention.
The Vlookup formula only works if the search column is the first column in the array which is specified in the formula parameters. Often this is not the case, then the following alternative to Vlookup can be applied.
You can select multiple tabs which will become part of one PDF document.
Scroll bars are useful to pick values from a definable range.By default, negative values cannot be used.
With a simple trick, you can get around this obstacle. See how this can be done without using VBA code in a couple of examples that we have prepared for you.
Excel® is a registered trademark of Microsoft Corporation.