In the jobs I have worked so far, I have been considered something of an Excel guru. This is not because I had the most Excel knowledge (far from it in some cases), but because I had the most applicable Excel knowledge that I also shared with others. I’ve worked with other people who really know Excel, way more than I do even now, but they didn’t apply their knowledge as much outside of their own role and still in very limited ways.
One of the things I’ve learned over the years is that some of the most useful things I do in Excel don’t actually require a whole lot of Excel knowledge, some of them could be used without even basic Excel knowledge. It was through learning and developing these non-Excel skills in Excel that I got to be the guru without knowing all the secrets.
Use a template
If you have any repetitive reporting where you have to take the outputs of one Excel-compatible file and put them into your report, do NOT use a lookup formula every time (unless you’re learning formulas, then practice as much as possible).
Make a copy of the report format that you need your data in, dump your data into another sheet and write lookups into your copy just this one time, this is your template. Each time you get new data, dump it into that template and copy the output that is now in your report format to the actual report. This saves me 10 minutes or more every day and also has the added benefit that when the data changes and total rows move, it is much more evident than it was before.
I’m also now adding additional features with the data that I couldn’t have before, such as putting some of the figures into a standard set of commentary that I can send out to other people, calculating totals that aren’t in the source data and building in checks to look for new row items automatically. These aren’t formulas you would want to write over and over, so using a template not only simplifies getting your data in the right format and layout, but also lets you add insight with little effort.
Every time you make a big change, save it as a copy and keep the original somewhere safe
Version control is a big thing for developers but not so much for Excel users, which is a bit of a problem. Sometimes we make changes to our files that cause an error, but we don’t know at what stage that happened. If you know you are going to make a big change that could produce an error, save a good copy straight away and then make your mess. It may seem a bit like paranoia, but this has saved my butt enough times that I don’t even think about it any more. As a good example, any time I am moving a row or column in a reasonably mature workbook, I save a new version. You do not want to lose all the new fresh data you dumped in there half an hour ago while adding in some extra features or moving a data column to its new home.
Do not use merge and center!
Just don’t use it. It screws up copy/pasting operations and complicates worksheet navigation. It’s much better to simply widen a column, or use center across selection, or even just indent until your text/number/whatever is in the right place for presentational purposes.
Merge and center is the worst formatting option often used in Excel workbooks, just please avoid it whenever you can, you’ll thank me later.