10 Practical Excel Tips for Finance Personnel

Working as an accountant or a finance analyst requires decent Excel spreadsheet and word processing skills. In fact, even when an accountant moves further up the corporate ladder as a finance manager or CFO for example, this skill becomes even more indispensable. A CFO for example would expend considerable time and energy every month generating reports, linking each sheet to its mother sheet, and linking all of that into a power point presentation that should be ready for board presentation. However, majority of the time, this Excel spreadsheets skill comes with experience – after one has been helplessly thrown into a lion´s den. Even then, there are still techniques out there that many are unaware of. So if you are new or if you are a little rusty on some Excel tips and tricks, here are 10 practical tips on how to become a Finance Excel Ninja.

Saving Excel workbooks in binary format compresses the data and reduces its size. This will consequently lead to prevention of possible crashes.  

If you want to add sum formula for multiple columns/rows, Auto sum can help. You can insert a formula into multiple cells with a single click. Alternatively, you can also use for the same function

.

Hard coding excel formulas can help with short-term problems however, the danger also lies in making it very hard to identify and trace. Example: If you are calculating on the basis of the going interest rates which let´s say is 1.8% which you then hardcoded into A1*1.8%, later on the interest rates will change and it can be very tricky how the number came about if you work on it weeks or months later. Therefore, write formula that has cell references.

This simple excel tip saves time because with a little help from a tiny paint brush, copying the formatting of one cell to another cell is easy. When you click once on the icon you can apply formatting to a single cell. Double click apply for multiple cells.

Excel paste special option supports simple arithmetic operations like addition, multiplication, division & subtraction.

 

It’s common to write lengthy excel formulas to execute complex logic’s but the issue with this is that it becomes very hard to debug in case if of errors. A good tip then is to break down formulas by each logic by using Alt+Enter in the formula bar.

Control + Enter allows you to quickly fill formulas in a selected range. For example, if you want to add current date in E1: E10 range Select range E1: E10 → enter Today () formula → Click on Control + Enter

This is a convenient function to use when your data is not in proper format. Below are the functions you can use.

 

The problem with Excel is that things can go wrong very fast and you might end up losing all you have worked hard for. Therefore, always make sure you save versions of your work and make back-ups. Just click on Save as and use version1, version2, etc.