98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsPrevious article Next article Excel articles
Microsoft Excel Features and Functions for Accountants
Wed 4th October 2006
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.
Author is a trainer with a Microsoft Office training company, the UK industry leader in its sector. For more information on Excel training, please visit https://www.stl-training.co.uk.
Original article appears here:
https://www.stl-training.co.uk/article-48-excel-features-functions-accountants.html
London's widest choice in
dates, venues, and prices
Public Schedule:
On-site / Closed company:
TestimonialsCrown Prosecution Service
Learning And Development Manager Lucy Hutton Office 365 End User Fantastic course, was very engaging and Tom did a great job with pacing the session. Teach First
HR & Recruitment Coordinator Human Resources Kamile Bagucanskyte Excel Advanced My personal experience with STL has been great! Not only are trainers well trained and always deliver best training sessions, but their customer service has been amazing as well. Furthermore, it is always pleasant to talk to the admin team. The helpfulness is always at the highest level and customer/client care always guaranteed. Viasat World
Social Media Content Executive Gerry Koeva PowerPoint Intermediate Advanced It's nice to have a training that's very comprehensive, but when it's quite long it's hard to do - maybe it can be split up in parts/modules and also recorded, so you could watch it later. |
PUBLICATION GUIDELINES