Microsoft Excel spreadsheets are an indispensable tool for accountants, being widely used to create financial statements as well as to produce many other types of financial reports including budgets, forecasts, cash flow and financial models. Many accountants consider themselves to be experts in using Excel but the reality is that the majority are self taught and are not aware of how to utilise the full potential of Excel. With some basic excel software training the wealth of functions available to the accountant within Excel can be identified and put to use.



Calculation Functions

Basic arithmetic functions such as SUM() and border formatting will enable you to produce useful models and perform some quite complex calculations. By expanding your knowledge of just some of the many other functions, Excel can become a much more versatile tool.

There are many financial functions available for carrying out interest and investment calculations which can greatly simplify the long formulae normally required for e.g. loan repayment. Care needs to be taken to check that the functions work in the same way as your own textbook formula but, once you have tested the functions in a variety of situations and confirmed the results, they can greatly simplify tasks such as determining the net present value.

If you wish to only perform calculations on items within your data that meet specific criteria, the Database and Array functions are very useful. The SUMPRODUCT() function is also a very good alternative. It is able to extract all sorts of values from a table of data and can be used as an alternative to a considerable number of functions.

Pivot Tables

Many Excel users are not familiar with Pivot Tables, which is one of the most powerful features of Excel. A pivot table is a great reporting tool that will sort and sum data independent of the original data layout in the spreadsheet. By dragging and dropping column headings around you can change the way the data is viewed. For example, take a list of rows each containing an employee name, week number, expense item description and expense amount. Using pivot tables, the data can be easily transformed into a table summing the total for each employee week by week or the total for each expense item by employee. The options are almost endless and can moved between at the touch of a button.

Auditing and Checking Features

Accountants have a professional responsibility to ensure they are presenting accurate data. To remove the chance of critical errors they should ensure that spreadsheets have been well designed and rigorously checked. Use of the IF() and TEXT() functions help by allowing error checking messages to be incorporated and demonstrate that the models have been reconciled and are performing properly.

Other features such as the validation tool, to check data falls within specified ranges and the audit toolbar, which will highlight the cells which combine to produce an error are also powerful tools in ensuring that data is presented accurately.

Protection of Data

It is always helpful to make your models user-friendly. Protecting or restricting the values that can be placed in cells can help to prevent unforeseen errors. Options are available in Excel to allow you to protect all or just selected cells within a worksheet. This allows you to restrict the cells where data can be input and prevent your formulas being accidentally overwritten or amended. Using drop down boxes is another good option available to limit the values that can be entered by users.

Whatever you are using Excel for, it is important that you attain the knowledge required to enable you to complete your everyday tasks. Excel software training can give you this knowledge and equip you with the skills you need to work more accurately and efficiently.