Warning: "continue" targeting switch is equivalent to "break". Did you mean to use "continue 2"? in /homepages/16/d462231950/htdocs/EFC/wp-content/plugins/wp-markdown/markdownify/markdownify.php on line 299

Warning: Declaration of WarpMenuWalker::start_lvl(&$output, $depth) should be compatible with Walker_Nav_Menu::start_lvl(&$output, $depth = 0, $args = Array) in /homepages/16/d462231950/htdocs/EFC/wp-content/themes/yoo_sync_wp/warp/systems/wordpress/helpers/system.php on line 678

Warning: Declaration of WarpMenuWalker::end_lvl(&$output, $depth) should be compatible with Walker_Nav_Menu::end_lvl(&$output, $depth = 0, $args = Array) in /homepages/16/d462231950/htdocs/EFC/wp-content/themes/yoo_sync_wp/warp/systems/wordpress/helpers/system.php on line 678

Warning: Declaration of WarpMenuWalker::start_el(&$output, $item, $depth, $args) should be compatible with Walker_Nav_Menu::start_el(&$output, $item, $depth = 0, $args = Array, $id = 0) in /homepages/16/d462231950/htdocs/EFC/wp-content/themes/yoo_sync_wp/warp/systems/wordpress/helpers/system.php on line 678

Warning: Declaration of WarpMenuWalker::end_el(&$output, $item, $depth) should be compatible with Walker_Nav_Menu::end_el(&$output, $item, $depth = 0, $args = Array) in /homepages/16/d462231950/htdocs/EFC/wp-content/themes/yoo_sync_wp/warp/systems/wordpress/helpers/system.php on line 678

Warning: count(): Parameter must be an array or an object that implements Countable in /homepages/16/d462231950/htdocs/EFC/wp-content/themes/yoo_sync_wp/warp/systems/wordpress/helpers/system.php on line 61

Warning: session_start(): Cannot start session when headers already sent in /homepages/16/d462231950/htdocs/EFC/wp-content/themes/yoo_sync_wp/warp/systems/wordpress/helpers/config.php on line 48
How to Excel in Consulting

Veodin KeyRocket: Great way to learn keyboard shortcuts

Written by Peter Albert on . Posted in Advanced user, Expert, Shortcuts, Starter

I recently got the opportunity to install the trial version of Veodin KeyRocket. It’s a small program to observes how you use Excel, other office programs and some other programs (e.g. Windows Explorer, Gmail in Chrome (using a separate extension), Visual Studio). If you’re using the mouse to access any functionality more than a few times, a small unobtrusive information box shows up at the bottom of your screen:

Ctrl-F4: Closes the selected workbook window.

Being a shortcut nazi myself, I really thought I knew most of them, but boy, was I wrong! It took a mere 5 days until I got the full version for 99€. But you can savely test it for free: only after you learnt/applied 5 recommended shortcuts, you’ll be asked to register.

Check with your IT department. I know of some consultancies that already bought licences for their consultants but only install it upon request! Also, for members of [Affenium](http://www.affenium.com), licenses are available.

Neat bonus feature: with the latest update, you can now define your own shortcuts for functionality you use often but cannot access with a shortcut. Especially useful in PowerPoint, where custom shortcuts so far were almost impossible/annoyingly difficult to set.

Disclaimer: I’m not affiliated with Veodin, but reached out to Veodin’s director Jan Mechtel after trying out KeyRocket. They have pretty impressive skills when it comes to deeply integrating software into Windows – so check them out if you need some hardcore development expertise…

Always link to the cell above with a simple trick

Written by Peter Albert on . Posted in Advanced user, Expert, Hacks, Shortcuts

Here’s a neat little trick that is really useful: create a named range CellAbove that dynamically links to the cell above the formula – and then use it in formulas where you need to refer to the above cell. E.g. if you want to have a list of years, starting with a (dynamic) start year stored in cell A1, instead of writing =A1+1 in the cell below, use =CellAbove+1. Now copy this formula down. The advantage? If you insert another row somewhere in this range of years, the new formula will still refer to the row above – while the normal formula will refer now two rows above.

So, in order to set up this name, do the following:

  1. Select cell B1
  2. In the “Formula” tab of the ribbon, click on the “Name manager”, select “New”
  3. Enter the name CellAboveas name, select the current worksheet as scope
  4. In the “Refers to field”, select the cell above, i.e. cell A1. Important: Make sure to remove the $ (use F4 to toggle them)
  5. Press “OK” and close the name manager

Use formula "=Sheet1!A1" without $ for the name CellAbove

Small limitation: This name will always refer to the cell above on Sheet1! If you want to use this approach on multiple sheets, repeat the steps above, but replace Sheet1 with the target sheet.

Pro tip: As I use this really often, I create a small macro in my personal macro workbook that does the job – and assigned the macro to CtrlShiftA:

  1. Open the Visual Basic editor (AltF11)
  2. In the tree panel on the left, expand “VBAPRoject (personal.xlsm)”.
  3. Right click “Modules”->”Insert->”Module”
  4. Double click on the new module
  5. Paste the code below in the main code window
  6. Save (CtrlS) the macro and close the editor
  7. In Excel, open the Macro dialog (kbd>Alt–F8, select the macro Personal.xlsm!subAddCellAboveName, click on “Options” and assign a shortcut of your choice.
    Public Sub subAddCellAboveName()
        On Error Resume Next
        ActiveSheet.Names.Add Name:="CellAbove", RefersToR1C1:="=R[-1]C"
    End Sub

Speeding up VLOOKUP (and MATCH) formulas by orders of magnitude!

Written by Peter Albert on . Posted in Data handling, Expert, Modeling

Executive summary

By default, you’re using FALSE as the last parameter of a VLOOKUP or MATCH formula to make sure that the proper result is returned. This approach is really slow on larger data sets. Alternatively, sorting the data and using TRUE will increase the overall search speed by a very large factor!!!

This post will explain how and when to use this technique – and what to watch out for.

Quick reference

  1. Sort your data ascending according to your key column (the first column of your VLOOKUP range)
  2. Replace your =VLOOKUP(A1,Sheet2!$A:$B,2,0) with =VLOOKUP(A1,Sheet2!$A:$B,2) (you can omit the TRUEas last parameter as this is the default)
  3. Watchout: In case you are not sure if A1 is contained in your list, use this formula: =IF(VLOOKUP(A1,Sheet2!$A:$B,1)=A1,VLOOKUP(A1,Sheet2!$A:$B,2),NA())

Detailed explanation

Reduce file size and load/save time with .xlsb format

Written by Peter Albert on . Posted in Starter

Executive Summary

Store any Excel 2007+ files as Excel Binary Workbook (.xlsb), instead of the default Excel Workbook or Excel Macro-Enabled Workbook! This will reduce the size of the file significantly (usually by 30-60%!) and with it the load/save time.

To make this the default format, use OptionsSaveSave file in this format and change the dropdown to “Excel Binary Format“.

Detailed description

Powerful shortcuts to change the structure of your worksheet

Written by Peter Albert on . Posted in Shortcuts, Starter

Executive Summary

6 shortcuts will allow you to insert, delete, move, and copy rows or columns with easy. Applying them will make you look like an Excel-pro to your team and client, even if you just started!

Quick reference

Shortcut Functionality
CtrlSpace Select column
ShiftSpace Select row
Ctrlarrow keys Extend selection (to multiple rows/columns)
Ctrl+ Insert
Ctrl- Delete
CtrlX Cut (use together with Ctrl+ to move to new location)

Detailed explanation

Use INDEX and MATCH instead of VLOOKUP

Written by Peter Albert on . Posted in Advanced user, Data handling

Executive summary

Replace your VLOOKUP formula with a combination of INDEX/MATCH. There are only upsides to it – apart from the minor learning effort ;-)!

Quick reference





E.g. replace =VLOOKUP(G1,$B$2:$D$5,2,0) with. =INDEX($D$2:$D$5,MATCH(G1,$B2$2:$B$5,0)).

If you need to retrieve multiple columns for one key, extract the MATCH part of the above function to another cell and refer all INDEX functions to this cell.

Detailed description