Microsoft Excel has proven its usefulness across a wide range of industries over the years, but for individuals working in accountancy and finance jobs the programme proves vital on an everyday basis. Being able to use the software accurately and quickly, can greatly improve your productivity levels and help you demonstrate your usefulness to your employer. Excel has a huge number of features, some of which will be useful to you and others that will not, which is why we’ve put together a list of some of the most advantageous hacks for the accountancy industry.
top excel hacks
Whether you are managing finances for a small, independent business or a huge multinational, Excel spreadsheets can get cluttered pretty quickly. However, it’s possible to group rows and columns together, which can then be collapsed in order to present information with greater clarity. Simply highlight the desired rows or columns, click the data tab and then “group.” By having multiple groups, you can ensure that huge data sets remain easily digestible.
2. enter data into multiple cells.
Entering data into Excel can be a tedious process, particularly when it’s the same data over and over again. In order to enter data quickly, simply select the relevant cells, either using “Shift” or “Ctrl” for non-adjacent cells, type your information and then press “Ctrl and Enter.” Your information should appear in all of your highlighted cells instantly, saving you and your company plenty of time.
3. pivot tables
Pivot tables are a special Excel tool that enable you to interact with data in order to analyse it more effectively. In order to create one, select “Pivot Table” from the Insert tab and then select the data that you wish to be included. Once created, you can select which fields are displayed and which are hidden, making it much easier to draw statistical conclusions.
4. making the most of autocomplete.
Sometimes it’s the little things that make a big difference. Excel has an autocomplete function which suggests what you are going to type based on previously entered data in that column and making the most of this can save considerable time. To accept Excel’s suggestions press “Tab” in order to move one cell to the right and “Enter” to move one cell down. Pressing “Backspace” rejects the autocomplete suggestion.
5. embed a spreadsheet in word.
In an accountancy job you may be tasked with creating presentations and reports that make extensive use of your Excel spreadsheets. However, displaying these spreadsheets in, say, a Word document sometimes creates formatting issues. To get around this, select the cells that you wish to embed and copy them. Then go to your Word doc and select “Paste Special” from the toolbar and choose “Microsoft Excel Worksheet Object.”
6. removing error messages.
Formulas work great if they have all the data that they need, but if you’ve set up a template in advance, sometimes the data is not yet available. This can lead to error messages appearing, which is not the best look for financial reports. In order to clean up your spreadsheet, amend your formula to include an IFError function, such as: =IFERROR((D12/C12),””). This replaces the error message with a blank cell, improving the appearance of your Excel reports.
7. highlight invalid data.
When there’s lots of data involved, manually checking every item isn’t possible. Fortunately, Excel can highlight invalid data for you as long as you set up some parameters. Select the relevant cells and click “Data Validation” from the Data tab. You can then set criteria, for example all data less than £10,000 is invalid, and have Excel either circle or remove any information that falls outside of it.
8. link cells.
You can not only link cells in the same spreadsheet, but also across different spreadsheets. If, for example, you want any changes in value for “spreadsheet X” to automatically be represented in “spreadsheet Y,” a simple formula can achieve this. In the cell where you wish the value to appear, enter: =’Spreadsheet X’! followed by the cell number in spreadsheet Y where the information is contained. In finance, individual figures can be vital across multiple accounts so being able to update them all automatically is hugely important.