- Autosum shortcut: Alt–=
- Filter tables with a custom formula column and AutoFilter
- Show more information about any selection in the statusbar
- Modify many cells’ values with advanced Paste Special
- Redo of last activity: Ctrl–Y
Check out the pro tips for each item below!
Whenever you quickly want to build a plain sum formula, use the shortcut Alt–=1. It will produce the formula
=SUM(NextRangeFound) – where
NextRangeFound is the range of consecutive cells with numbers above the cell. If no numbers are present above, all consecutive numbers to the left are taken.
Pro tip: The shortcut also works on multiple selected cells:
If you want to filter a table, the AutoFilter feature of Excel is a rather powerful tool. However, there are some situations, where the filter options are not suitable to your needs. Consider the following table:
Imagine you need to filter this table to show all sales in a certain period that were either sold to customer X or in region B. Applying the standard filters, you could not filter for the or. Instead, introduce a new filter column – and in this column apply boolean logic (using
NOT functions). Then, you only need to filter this column for
Pro tip: Using this technique, you can easily create filters that can be parameterized with outside variables. In the above example, all parameters (start date, end date, product and region) were stored in input cells. After changing any of those, the filter dialog only needs to be opened – and the updated input will be applied. This can be much more efficient than changing the filter in the dialog.
If you select any range, the total of this range is usually shown in the status bar. If you right click the status bar, you can actually choose to display further statistics for the selection, namely min, max, average, count and numerical count. This makes it a great tool to do quick sanity checks, e.g. on full columns:
Pro tip: You can select and quick analyze multiple cells, that are not adjacent to each other by using Ctrl and the mouse.
If you copy a cell with a value and then select another range, you can easily apply basic math operations (
/) with the copied value and selected range, e.g.:
- Negate values:
- Convert numbers stored as text to numbers:
- Change thousands to normal number:
- Offset start date by a year:
Pro tip: This approach can also be used to quickly modify a bunch of formulas: If you copy a cell with a formula and then select
Formula and an operation in the Paste Special dialog, this formula (adjusted for the cell references!) will be added/subtracted/etc. to the existing formula.
Shortcut: To quickly access the Paste Special dialog, use Ctrl–Alt–V! Easier and quicker to do than the old Excel 2003 Alt–E–S–V shortcut…
If you use Undo (Ctrl–Z), you can undo the Undo with Ctrl–Y. So far so good. However, if you use Ctrl–Y after other actions than Undo, it becomes a real Redo, i.e. Excel will apply the last action you did to the current selection. This is really useful especially when applying custom formatting. Let’s say you format a cell’s number format, font size and color in the “Format cells” dialog. Select any other cells afterwards (even on other worksheets!), press Ctrl–Y and the same format will be applied.
This way you can kind of define keyboard shortcuts on the fly! Here’s the shortcut to change a date format from English to German format and apply grey font color:
Pro tip: The redo also works in Word and PowerPoint! Really useful in PowerPoint when applying the same, custom format to multiple shapes.
On many international keyboard layouts you’ll actually need to press Shift to get the =. E.g. on the German keyboard the full shortcuts is Alt–Shift–0 ↩
Trackback from your site.