The market may be saturated with accountancy software providing increasingly intuitive processes which take the pain out of time-consuming and repetitive tasks, but Excel remains the go-to tool for most accountants.

This is not surprising considering that its versatile functionality around data analysis and financial reporting allows accountants to build, analyse, model and report financial data using tried and tested methods. But how up-to-date and efficient are these methods, really? Are there quicker and easier ways of carrying out the basics?

Ben Ducker, chartered accountant, Principal Consultant at Modelling Expert Ltd and member of ICAEW’s Excel Community Advisory Group shares five easy-to-follow hacks on improving the basics when building and working with Excel spreadsheets.

Multiple cell filling

Instead of the copy/paste function (CTRL+C > CTRL+V) for every single cell, use the following hack to fill multiple cells at a time.

To fill a cell/s to the right:

• place the cursor on the cell you want copied;
• select all the cells to the right that you want to fill;
• press CTRL+R to fill cells using the value or formula from the leftmost cell.

To fill multiple cells downwards:

• place the cursor on the cell you want copied;
• select all the cells below that you want to fill;
• press CTRL+D to fill cells using the value or formula from the top row.

View several tabs simultaneously 

Having to constantly flick between tabs in the same workbook to compare data can be annoying, so use this hack to view two tabs at the same time: 

• click on the ‘View’ in the ribbon;
• click on ‘New Window’;
• click ‘Arrange All’ to choose whether to view tabs side by side, above/below, tiled etc.

“However, if you save and close a workbook with multiple windows open, it will then open with multiple windows,” says Ducker. “This may confuse other users who are not familiar with this feature, so if you’re sending the workbook to other users, remember to close down to one single window view again before saving.”

Selecting multiple cells using CTRL+F

Selecting multiple cells to change the formatting or making other tweaks can be time-consuming, especially if they are distributed across a large spreadsheet and not next to each other. A slightly more advanced technique which can save time is quickly select all the cells you need to edit, try this method:

• CTRL+F to find cells whose contents matches your criteria eg, all cells containing the word ‘Feb’ for example, or where the formula contains “=SUM(“ (to find total rows);
• click ‘Find All’;
• click in the found list;
• press CTRL+A to select all found entries in the list. This will also select the found cells in the current worksheet;
• click on the window title bar (Green bar at the top of Excel) to focus on the workbook but without changing the selection;
• you now have all the found cells selected so you can make formatting changes or other tweaks eg, font size or fill colour change.

Selecting multiple cells using Filters

This is another method to select cells you want to copy or edit. Sometimes the CTRL+F method won’t be suitable, because you need to select cells based on criteria in a different column. (eg, to find all the rows labelled as “Total” but where the formula isn’t necessarily a =SUM( so using CTRL+F wouldn’t work).

This, says Ducker, is where the filter functionality can come into its own. Try this the following.

• Apply a filter to the headings column by selecting the header row and going to Data > Filter (or pressing Ctrl + Shift + L).
• Enter filter criteria to select required rows (eg, where text contains “Total”).
• Select all the cells in the columns to edit, press F5 to open the ‘go to’ dialog box, click  ‘Special’ then select ‘Visible cells only’.
• Your selection is now set to formula cells in the filtered rows, based on the value from the label column.
• You can now edit the cells, change the colour, or even copy/paste the values or a link etc. 

Utilising the ‘Go To Special’ function

This function allows users to find cells within a selection which meet certain criteria. “Go To Special can be used for many different things and it’s really underrated,” says Ducker. Examples include identifying typed values (or where users have manually overwritten formulas), selecting cells containing formulas or using as part of Copy and Paste function. Here’s how to use it:

• select required range of cells;
• F5 for the ‘Go To’ dialog box then click ‘special’;
• select required cells in the menu (eg, constants, formulas, Data validation etc); then
• click OK.

Example application

Using Go To Special can be used to clear out assumptions from a model without affecting the formulas. Imagine a worksheet which contains a mixture of input cells containing hardcoded numbers, interspersed with subtotal formulae etc.

You need to delete all the hardcoded numbers without deleting the formulae. One option could be to use the Autofilter method above to filter the sheet to show only cells which are formatted as inputs, then select the visible cells, clear the contents and then remove the filter.

Another alternative would be to select the columns with the mix of hardcodes and formulae, then use Go To Special, and select only constants. Now your selection will be only the hardcoded values and you can hit Delete to clear those values, while leaving the formula rows intact.