98.8% 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:
TestimonialsExpleo
Consultant Cherise Byrnes Feedback - Giving and Receiving I really enjoyed the course and how interactive it was. I could take away real-world solutions to situations. Global Blue
Sales Manager Athena Fisher Excel Intermediate Our Company used Microsoft Training and everyone on the course has said that its excellent! The course was relevant and the training was great! Croudace Homes
Adoptions Manager Graham Keeble Problem Solving / Decision Making The course was well presented, comprehensive and structured. |
PUBLICATION GUIDELINES