Microsoft Excel is an immensely complex piece of software with so many features that sometimes even basic actions can confuse people. Despite working in Excel for almost two decades, I still manage to uncover new tricks that I never imagined were possible before. In this case it is a keyboard shortcut I picked up from a colleague, but I find it very useful and easy to use.
When entering data in cells, Excel can suggest values from the same column in the form of AutoComplete: start typing and after the first few letters Excel will show a matching suggestion which you can fill in the cell by typing Enter. Sounds great in theory, but in practice you will find many situations where suggestions fail to show up, especially in large sheets with multiple similar entries or blank rows. What I discovered from my colleague was that the keyboard shortcut Alt + ⬇ (down arrow) opens a drop-down list below the cell containing unique values from the table, and you can simply pick a value either with the mouse or the keyboard.
This drop-down list too has some limitations, but I found it more reliable than regular AutoComplete. As with AutoComplete, the drop-down list will be blank if you are not typing in an adjacent cell (above or below) to the area where data is already entered – it works only with contiguous regions. This limitation does not apply to Excel tables though, where both AutoComplete and the drop-down will access values from the entire table column – yet another reason to prefer modern data tables for many operations in Excel. Also, neither of these works with numbers or dates, only with strings, but arguably that is where they are most useful – you would not want AutoComplete to suggest numbers and accidentally enter wrong ones.
Once I got accustomed to this shortcut, I discovered other places where it can be used: it activates the filter drop-down if you are positioned in the filter header and data validation lists if any are defined for the active cell. In these cases, it helps you work faster by leveraging keyboard shortcuts instead of reaching for the mouse.
Post a Comment