Excel is great. Here I share a few tips on using Excel efficiently and I highlight a few features that you might not know about.

If you do any sort of work with numbers, you should be using a spreadsheet. I still meet people who say things like, “I just use R, I have no need of spreadsheets”, but they have no idea what they are missing. The flexibility, the range of features and the ease of use mean that there is some aspect of your work that you should be using a spreadsheet for.

A basic type of spreadsheet program existed on mainframe computers as far back as the 1960s, and then when micro-computers were invented, spreadsheets quickly became one of their two essential programs. First came VisiCalc and then SuperCalc, but the first one I learned (on a CP/M computer, the operating system that predated MS-DOS, let alone windows) was the relatively obscure PerfectCalc. Then the power and ease of use was stepped up a couple of notches in Lotus 1-2-3 in 1983, which quickly led the market.

Microsoft Excel took over as market leader in 1995 and has been dominant ever since. There are some freebie spreadsheets available, which can be good, but they can’t match Excel. I use it every day for a huge range of tasks. It is my go-to tool for all things quantitative. There are things you need specialised tools for, of course (e.g., specialised statistics, large optimisation models) but even in those areas, Excel does a good job of solving many practical problems.

I’m not a super-user but I’m better acquainted with it than most. Using Excel so extensively, I have accumulated some knowledge that might be worth sharing, so here is a bit of a grab bag of tips and tricks.

Efficient navigation and selection

Most people navigate around Excel using the mouse, but I find that using the keyboard is usually more efficient in some situations. The End key is particularly useful. If you are sitting in a table or a list of numbers, you can jump to the top, the bottom, the extreme left of the extreme right of the table by hitting the End key (don’t hold it down) and then hitting the arrow key for the direction you want to go. This sends you in that direction until it runs out of cells with any content. This is especially useful when navigating large tables (e.g., with 50 or 100 columns or rows of numbers). It only works fully if the table is full of numbers (no empty cells except outside the table).

Even more useful is selecting cells using the keyboard rather than the mouse. You do this by holding down the shift key and then using navigation keys (up, down, left, right, PgUp, PgDn, Home) to move around. Excel selects a rectangle of cells consisting of where you started to where you are now.

You can combine the previous two tips: hold down Shift, hit End, and hit an arrow key, and you select all cells to the edge of the table. I use this often.

Keyboard shortcuts

There is a theme developing here. Using the keyboard is more efficient than using the mouse for many tasks. I have memorised a modest number of keyboard shortcuts and they mean I can work more quickly than I could if I relied on the mouse.

Pause for a complaint about Apple. I used to use a Mac, and the system of keyboard shortcuts it uses are far less effective and consistent than they are on a Windows PC. It’s one of a number of reasons I prefer Windows.

Ctrl-S (hold down Ctrl and hit S): this saves your file. I do this so frequently (every few minutes) that it has become instinctive, and I don’t even think about it. Sure you can use the built-in file-recovery system if something goes wrong, but it is safer and less mucking around if your main copy of the file is up to date.

Ctrl-C: copy the currently selected range. Vastly better than having to move the mouse pointer to the selected range, right click, look for Copy somewhere in the pop-up menu and click on it.

Ctrl-X: cut.

Ctrl-V: paste.

Ctrl-Z: undo.

Ctrl-Y: redo.

Ctrl-F: find.

Alt-E Alt-E: find and replace.

Changing some default settings

By default, when you type in a new value for a cell and press Enter, Excel moves the selected cell down. I don’t like this. I often want to move in some other direction, or not move at all, so I switch off that default setting. File | Options | Advanced | uncheck the option “After pressing Enter, move selection”.

As an alternative to cut-and-paste, Excel has an option to “Drag and Drop” a cell. Personally, I dislike this too, as I’ve sometimes done it by accident, so I switch it off too. File | Options | Advanced | uncheck the option “Enable fill handle and cell drag-and-drop”.

Advice on spreadsheet design

This is a big topic. Just a few key tips.

As far as possible, keep your data entry cells separate from your calculation cells that contain formulas. I generally put them on separate sheets within the same workbook.

Never put a number directly into a formula. Put it in a cell and refer to that cell in the formula. This makes things transparent and means you can change the number without having to edit a formula, or perhaps a bunch of formulas.

Use colours consistently to indicate the type of cell. For example, blue for data entry, grey for a formula, and black on white for a heading or other text.

Include sufficient labels and explanations in the spreadsheet so that it is clear to others (and to yourself in the future) what you have done and why. A number of times I have had to come back to a spreadsheet after several years and have been grateful that I left lots of clues about how it worked.

One facility that helps with making things clear is to put a note or a comment behind a cell with some text about the cell. These are displayed when you hover the mouse pointer over the cell. Keyboard shortcut Alt-I Alt-M inserts a note behind the current cell.

A strategy that makes it a bit easier to decode what a formula is doing is to give key cells a meaningful, descriptive name. Formulas | Define name. You can then use the name in formulas that look at that cell, rather than a cell reference that doesn’t have any meaning until you go and look at the cell.

Testing and debugging

One guy who offers a service online checking spreadsheets for bugs claims that he finds bugs in 88% of the spreadsheets he checks. That seems quite plausible to me. Most people are not nearly careful or disciplined enough in checking for and fixing bugs. You should build your spreadsheet systematically and carefully and check it thoroughly at every stage before moving on. Excel provides some great facilities to help with this.

Check which cells use the current cell in their formula: Formulas | Trace Dependents.

Check which cells are used by the current cell in its formula: Formulas | Trace Precenents.

Evaluate a formula (check what each part of a formula does, step by step). Set on the cell in question and select Formulas | Evaluate Formula

Create a Watch Window to display the results in a cell while you experiment with making changes to input numbers on a different sheet or in a different place on the same sheet: Formulas | Watch Window | Add Watch …

My final tip is to invest some time in becoming familiar with the main functions that Excel provides for use in formulas. There is an incredible array of functions that are great to have at your fingertips.

If you can imagine something that you would like to do in a spreadsheet, it is almost certain to be possible. An enormous amount can be done just with formulas using the standard functions provided, and an enormous additional amount can be done by programming Excel using the VBA language. But that’s another level of expertise requiring a big commitment to learning.

Those are just a few of my favourite tips. If you have other favourites, please include them in the comments below.

If you want to pick up other tips, tricks and skills for Excel, there is an almost limitless supply of extremely helpful advice available online, including written text and videos on YouTube. Or you could do my online courses on Benefit: Cost Analysis, which include hands-on spreadsheet training.

Here are some videos that offer an introduction to Excel.

Microsoft Excel Tutorial – Beginners Level 1. (A very nice simple and basic introduction. 33 minutes.)

Microsoft Excel Tutorial – Beginners Level 2. (A follow-up. Worth watching as well. 22 minutes.)

Microsoft Excel Tutorial – Beginners Level 3. (The first half on copy-paste is recommended. 24 minutes.)

If you’re enjoying these, he also offers level 4 (https://youtu.be/c8qePWuYleg) and level 5 (https://youtu.be/sK4-Jk7SCPg).

The help that is built into Excel is pretty good but generally I just use Google to search for specific help when I need it. Here are some of the most useful sites.

https://www.excel-easy.com/ (designed for beginners)

https://www.exceltip.com/ (takes a practical approach)

https://exceljet.net/ (lots of great examples)