Excel can be a little bit daunting at first. When you open up a spreadsheet and you have a lattice of white cells, it can be difficult to start. And once you do start, you can get very bogged down in trying to get it to work the way you want it to. Having used Excel quite a lot during my studies, I thought I would drop my top 10 tips for Excel.
- Ditch the mouse. You can do everything faster if you just use the keyboard, you will be far more efficient and will get things done a lot more quickly. You will also be less tempted to make everything super complicated.
- Use macros. If there is no keyboard shortcut for something that you use all the time, make a macro for it. For example, if you are constantly formatting a cell to have a left and a bottom border, have right bottom aligned text that is underlined, coloured red, and in Calibri size 9 font, and your cell is coloured yellow, you can achieve all of that simply by setting up a macro. The time you will save by doing this is astounding.
- Collapse the ribbon. The ribbon is that top part of the Excel window where all your controls are. If you double click on the Home tab, the ribbon will collapse. This will do two things – stop you using the mouse, and give you more screen real estate to use.
- Stop using hundred of sheets. Rather use a few long, vertically scrolling sheets. It is much easier to navigate a sheet like this than it is to navigate among different sheets and scroll sideways.
- Learn how to split the screen. When you are using long worksheets, it can get tiresome to scroll up and down. The shortcut to do this is by placing your ‘selector’ at the spot on the screen that you want to split, and pressing Alt-W-S. You use the same shortcut to undue this. This will allow you to reference, check and copy data around your spreadsheets much faster.
- Learn how to use the Alt key. As part of getting rid of the mouse, you will have to learn how to use the Alt key. This is essentially your gateway into keyboard shortcuts. If you press Alt, you will notice that all of the buttons on the ribbon get a little label. If you press the key that corresponds to a button, you will activate the function. No more needing to use the mouse! This might seem slower at first, but after some practice you will be flying through your shortcuts.
- Anchor your cells. This is extremely important when creating formulas that you want to copy around. A cell reference in a formula will look something like G29. If you want to anchor the column, then all you have to do is write it as $G29. If you want to anchor the row, you write G$29. If you want to anchor both, it is $G$29. To make it easy, you can cycle through these settings by pressing F4.
- Always copy down and to the right. When you are building a formula that you want to copy into other cells, build it in the top left corner and copy from there. The reason for this is that you can use a combination of Shift-Arrow Keys to highlight the cells and then either Ctrl-R and Ctrl-D to copy and paste the formula into the highlighted cells.
- Learn how to audit cells. When you want to change what is written in a certain cell, it can seem impossible to do without using the mouse, or rewriting the entire cell. This may be okay if the cell’s contents is just a hard coded number, but if the cell’s contents is a complicated formula, it may not be feasible to rewrite the entire thing. The solution it to move your selector over the cell and to press F2. This puts you into edit mode, without every having to use the mouse.
- Format! Having a sheet of numbers with no formatting it incredibly ugly and difficult to understand. Learn how to format your work. Use underlining, bolding, indenting and colour coding to make your work easier to read. For example, when using a formula, do in black, and do you hard coded numbers in blue. That makes it very easy to see where your assumption drivers are, and makes it easy for somebody else to read your model, understand how it works, and actually use it.
These are the top 10 tips I have for Excel. My next blog post will get a little bit more nitty gritty with how to actually do things in Excel.