Excel Corner

Useful Excel features, tips and tricks, no registration required.

Tables in Excel, powerful and convenient

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

Conditional formatting of headers when a list is filtered

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.

Highlight cells with formulas that were mistakenly overwritten

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.

Count the number of cells that contain errors (e.g. #NV, #DIV/0, etc.)

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.

Alternative to Vlookup if search column is not in the leftmost column

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.

Multiple tabs printout as PDF

You can select multiple tabs which will become part of one PDF document.

Scroll bars / A few examples

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.