5 hacks every consultant should know

Written by Peter Albert on . Posted in Hacks, Shortcuts, Starter

Executive summary

  1. Autosum shortcut: Alt=
  2. Filter tables with a custom formula column and AutoFilter
  3. Show more information about any selection in the statusbar
  4. Modify many cells’ values with advanced Paste Special
  5. Redo of last activity: CtrlY

Check out the pro tips for each item below!

Detailed instructions

1. Autosum shortcut

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:

Autosum demonstration   Autosum demonstration2

2. Custom filtering of tables

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:

Table with date, customer and region columns

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 AND, OR and NOT functions). Then, you only need to filter this column for TRUE:

Table with filter column, using filter formula

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.

3. Enhanced statusbar

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:

Right click the status bar to select additional summary metrics for any range

Pro tip: You can select and quick analyze multiple cells, that are not adjacent to each other by using Ctrl and the mouse.

4. Modify many cells’ values with advanced Paste Special

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: ×(-1)
  • Convert numbers stored as text to numbers: ×1
  • Change thousands to normal number: ×1000
  • Offset start date by a year:-365

Paste special Operation options

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 CtrlAltV! Easier and quicker to do than the old Excel 2003 AltESV shortcut…

5. Redo of last activity

If you use Undo (CtrlZ), you can undo the Undo with CtrlY. So far so good. However, if you use CtrlY 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 CtrlY 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:

Redo example

Pro tip: The redo also works in Word and PowerPoint! Really useful in PowerPoint when applying the same, custom format to multiple shapes.

  1. 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 AltShift0 

Tags: , , ,

Trackback from your site.

Peter Albert

Worked 5 years as Consultant and Project Manager at McKinsey & Company and Bain & Company. Now builds Excel, other models and web applications for consultancies and other clients - and provides Excel training to consultants at all skill levels.

Leave a comment