Laziness as a Virtue
I’ve always been a bit lazy. Not lazy like I don’t like to work. Almost the opposite, actually. Lazy like I spend too much time on things that should allow me to work less. Like that time I decided to start learning the dvorak keyboard so I could spend “less time typing.”
While I’m not sure my approach to “doing less work” has actually achieved its
stated goal, it has made me more efficient at certain tasks. Like using Excel.
It’s fun to dance around a worksheet using only my keyboard and have people
chuckle a bit and ask “what just happened”? And you can easily get that
reaction even if you don’t know as many shortcuts as me. Sometimes just using
Ctrl+Down to go to the last row in the sheet is enough to spark a little
wonder. And if it’s not, maybe
Alt-e-s-p to paste special might do the trick.
Regardless, it’s definitely worth learning the shortcuts. I do it in almost
every program I use. It isn’t always easy, but it’s almost always worth it.
When I start mentoring someone on how to get better with Excel/data, one of the first things I try to tell her is “lose the mouse.” I had a friend from grad school tell me his boss ripped the mouse cord out of his computer when he wouldn’t stop using it, so he had no choice after that. I thought that was a great strategy! Changing your environment is one of the easiest ways to achieve new goals. I’ve threatened to rip the mouse cord out of a staff’s computer a few times, but I’m not one to destroy company property. But you should consider doing it when you are using Excel! It is possible to get 95% (?) of your work done without a mouse. I’m living proof.
To help you wrap your head around what’s possible, I put together a simple spreadsheet that maps out all Excel shortcuts in a simple spreadsheet itself (I know, very meta)1. Here’s a sample of what you’ll find in the spreadsheet:
This is heavily based on similar material from Chip Pearson’s
website. I think I
started with his webpage and then modified it based on what I knew or could
find out. As you can see, it’s organized by key down the left (e.g.,
F1, etc.), and then by “modifier” along the top (e.g.,
Alone modifier means that there is no modifier (e.g., just
F1 key by itself will bring up Excel help).
But enough about Excel …
What is “Vim”?
When I started learning Excel shortcuts, I thought I was hot sh**. I knew
Ctrl-Space highlighted the entire column! No one knows that!
(remember, I was still young and smarter than everyone back then). Anyway, I
heard about a program called Vim that I decided to try
out. Vim was identified as (and actually is) one of the most difficult programs
to learn that has ever been created. I found it to be even more difficult to
learn than figuring out how to control Windows automatic updates. Millions of
people can’t even exit the progam after they open
But that didn’t bother me. If I was willing to spend time learning the dvorak
keyboard to become a faster typer, I could sure as hell learn a text editor to
help me compound that efficiency gain.
That experience taught me a little humility. I learned what true efficiency meant, and I had no idea the program even existed. You should go read about the program, download it, and try it out if you’ve never heard of it. Use the link above if you can’t figure out how to exit.
Vim + Excel = Crazy Idea?
So I now know Excel and Vim pretty well. And a little while back, I was trying to figure out what shortcuts I could use for some custom macros I had created. In particular, I created an open-source add-in based on the ideas in a financial modeling course I had taken. But I wanted to expand on the program, so I went searching for a comprehensive list of shortcuts, and found a pretty complete list at Chip Pearson’s website. As I mentioned above, I expanded his work and build the hotkeys spreadsheet. In doing this, I realized that there was nothing stopping me from creating a “Vim-like environment” in Excel.
See, normally when you start typing a letter in Excel without a modifier (or
with just the
Shift key), you start editing the cell (overwriting whatever
was in there with the letter you just typed). If you’ve used Vim before, you
know that this doesn’t have to be the way it works. What if instead we
repurposed those keys to do something else? Vim has several “modes” that you
are operating in whenever you’re using the program. The default mode is called
normal mode. In normal mode, you can go up one line by pressing the
or down one line with the
j key. If you want to start editing text, you press
i key, which puts you into
insert mode. What if we could create a
normal mode versus an
insert mode in Excel? It turns out you can, and it’s
not even that hard.
The trick is to create a few VBA functions that simulate what you want to do,
to call those functions instead of editing a cell. The hotkeys
spreadsheet also gives you the ability to call custom macros (or create the
code that does so) pretty easily. If you want to call your “call_my_mom”
macro by pressing
Ctrl-m, just type
call_my_mom into cell
intersection of the
m key and the
Ctrl key), then press the
button. You can then paste the code on your clipboard into the module where you
call\_my\_mom macro and reload the spreadsheet to make it happen.
I’ve created a few “vim-like” macros and assigned some shortcuts that you can download here. Here is what’s implemented:
Shift-a: edit starting at the end of the current cell (Excel can already do this with
b: go back one “word” (like
w: go forward one “word” (like
Shift-b: select previous “word(s)” to the right (not quite the same as Vim, but interesting).
Shift-w: select next “word(s)” to the right (not quite the same as Vim, but interesting).
i: edit cell (same as
Shift-aabove and standard
Shift-i: edit starting at the beginning of the current cell (no equivalent in Excel that I’m aware of).
h: go left one cell
j: go down one cell.
k: go up one cell.
l: go right one cell.
Shift-h: go to top row of current “viewport” (no equivalent)
Shift-l: go to bottom row of current “viewport” (no equivalent)
Shift+m: go to middle row of current “viewport” (no equivalent)
o: insert row below the current row
Shift+o: insert row above the current row
$): go to “end” of current row (no equivalent in Excel that I’m aware of).
0: go to beginning of current row (i.e., column a - no equivalent in Excel that I’m aware of).
Space: go right one cell (because I use dvorak, I don’t use the
lkey to go right - I usually use the space key!)
This isn’t supposed to be true Vim emulation (that would be virtually impossible and probably not something I’d be interested in). But it was a fun exercise and will hopefully spark some ideas about what’s possible! If you like it, please consider signing up for my newsletter below to hear if I make any updates or post other things you may find interesting!
It’s certainly possible that I missed some. E.g., I only learned you could use
Alt-F5to refresh a pivot table within the last few years (I used
Alt-A-R-Rto refresh instead). Which brings me to another point: there are even more shortcuts than I could list in the spreadsheet because you can use
Alt-+ the ribbon shortcut (e.g.,
Alt-Ato go to the
Datatab), then type the shortcut to the button on that ribbon (e.g.,
Rto bring up the “Refresh” menu), and then possibly even type a subcommand (e.g.,
Alt-A-Rto refresh a pivot table). That’s not easy to write yet alone document in a table, unfortunately. ↩︎