Starting your own business can be an extremely exciting and empowering experience but comes with its own set of responsibilities. One of the most crucial of these is the need to stay on top of your incomings and outgoings.

For many, even the most basic bookkeeping and budgeting will be an alien experience. A recent Sunlife poll, found that only a third of UK adults formally budget, with just 19% using a spreadsheet and a further 7% using an online tool or app.

Whether you’re a small business owner or just trying to get on top of your family financing, tracking your incomings, outgoings and tax liabilities has a host of benefits, such as allowing you to project your profit, loss and turnover into the future and budget accordingly. The accounting tool that most turn to for this task is Microsoft’s spreadsheet stalwart, Excel*.

Excel’s strength is its incredible versatility. When it comes to bookkeeping and budgeting, it is by far the best option on the table. To some though getting to grips with Excel’s interface can feel a little daunting. It’s true that the program comes with a lot of features, but it’s unlikely you will ever need to use more than a small fraction of them.

So without further ado, let’s take a look at five essential Excel tips and tricks that any budding SME owner really needs to get under their belts. (For the purposes of this guide, I’m going to presume a very basic working knowledge of Excel, such as summing columns, navigating around a spreadsheet, etc).

Paste Special

To explain why the paste special functionality is so crucial to Excel, take a look at the two images below.

Paste formulas

...

paste values

In the first example, I’ve pasted cells E2 to E5 below the table normally using Ctrl + V or the “paste” button on the ribbon. These cells contain a simple arithmetic formula that multiplies the value in column C with the quantity in column D. As you can see, the cells referenced in the new formulas have moved relative to where they have been pasted on the worksheet. In this case they now reference the empty cells E9 to E12, thus all returning zero values.

Now look at what happens if I paste cells E2 to E5 as values. The cell values are effectively ‘hardcoded’ and the formula isn’t pasted at all. All we get is the value that the formula originally produced. This is an extremely important function if you want to paste the values of rows, columns or any other data range, without bringing across the formulas.


Absolute Cell References

Of course there are times when you might want to paste formulas into other areas of your spreadsheet, such as a formula that references a variable in single cell. This is where absolute cell references come in.

In the example below, I’ve added a tax field in column F and entered a formula in cell F2 that references the percentage value in cell D10 to return the tax on the items sold. I’ve then pasted this formula down the column.

In the left hand image the formula in cell F2 has relative references, which leaves us with a bit of a problem when we copy and paste this formula down.

We can easily fix this problem by making D10 an absolute cell reference in our formula. To do this put your cursor on the cell reference and press F4. This will put “$” marks around the cell reference, indicating that it will not change no matter where you copy and paste the formula. Now pasting the formula down column F gives us a tax value for each row by effectively locking that part of the formula to cell D10.

Relative cell references

...

Absolute cell reference


Filters

Filters are probably one of the simplest and most effective ways of sorting or pulling out specific information from within a large dataset and are an absolute must, however simple or complex your accounting or bookkeeping workbook is. To turn them on simply highlight every cell in your header row and select filter from the Sort and Filter dropdown menu (which should be on the right hand side of ribbon in the Home tab).

In Excel 2010 onwards, filters come with a dizzying array of options for both sorting and filtering data. The four examples I’ve given below show how they can be used to filter on date, number value, cell colour and on specific search terms. Clearly these are not particularly useful on a dataset as small as that below, but with when you have hundreds or thousands of rows, they really come into their own.

Filter date

Filter number

Filter cell colour

Filter search

You can use multiple filters across multiple headers to sort and filter your data into ever more specific sets but it is important to remember to turn off all filters when you want to view your data in its entirety. The quickest way to do this is to select the “clear” option from the “sort and filter” dropdown menu in the top right of the home tab on the ribbon.


Conditional Formatting

Conditional formatting may seem on the surface a way of automating the aesthetic look and feel of your spreadsheet but at its heart it involves some of the most useful functionality that Excel has to offer, and in versions 2010 onwards, you really can do a lot with it.

There are a host of features available with conditional formatting but I’ve shown you three easy to use ones on our dataset below. From left to right they are: highlight duplicate columns, colour bars and colour scales. These can all easily be accessed from conditional formatting dropdown menu on the home tab in the ribbon. This is only the tip of the iceberg in terms of what you can do with functionality. This article from the excellent Ablebits blog will tell you a lot more.

Conditional formatting


Freezing Panes

We’ll leave you with a simple but often hugely underused function in Excel and that’s the ability to freeze panes. This is basically a way of fixing the view so that one or more rows or columns (or a combination of both) are always in view no matter how far you scroll down or across.

To freeze panes click the “view” tab from the Ribbon and select the “freeze panes” dropdown to reveal three options. Most of the time you’ll be using the “freeze top row” option which is pretty self explanatory and keeps your header row in view at all times.

If your headers aren’t on the top row of your worksheet then you’ll need to highlight the row below it before freezing panes. If you’d like to keep a couple of columns in view as well, simply select the cell to the bottom right of all the columns and rows you’d like to keep in view, click freeze panes and voila!

So there you have it; five essentials for anyone using Excel for accounting or bookkeeping. Of course this is just the tip of the iceberg and as you get more confident with Excel, you will undoubtedly discover a whole world of powerful functionality that will allow you to create powerful and insightful spreadsheets. Good luck.


*All the information in this guide is based on Excel 2010 but, for the most part, will be relevant to previous versions of Excel (although pre Excel 2007 there is no ribbon and so menus and functions will be located elsewhere).


About the Author: Nick Brown has been a chartered accountant since 1983 and a partner at Plummer Parsons since 1990, where he is also Head of Charity Audits and Payroll. As well as being a member of the Information Technology Faculty of the Institute of Chartered Accountants, Nick is also an expert on the charity and not for profit sector, holding a diploma in Charity Accounting. You can connect with Plummer Parsons on Twitter, Facebook or LinkedIn.

comments powered by Disqus

website by DAJ