Excel has become the tool of choice for accountants and business professionals. Many have been using Excel for years, but most are self-taught and depend on others in their offices for tried and true solutions to common problems. In this article, I have compiled a series of useful excel tools or skills that would make life easier and shave valuable minutes off the work of chattered accountants.
1. Use PivotTables to analyse and summarise data
Many spreadsheet pros believe that pivot tables are the most powerful tool in Excel. PivotTables are a great way to summarise a list of data at the click of a button. They are also very easy to do.
Before you begin to create a PivotTable, make sure your source data is well organised and that you have an idea of what data should feature in it.
- Select a cell in your source data.
- On the Insert tab, click the PivotTable.
- Choose the fields to add to your PivotTable report.
- Drag a ‘label’ field into the Row Labels area (e.g. Client).
- Drag a numeric field into the Values area (e.g. Assets).
- Adjust what value is shown by right-clicking in your values field, and selecting ‘Show value as’. You can choose from options such as % of grand total, running total, and difference from.
2. Keep your cell references fixed with absolute references ($)
Excel uses ‘relative references’ as its default, which means that the cell reference (and any formulas) automatically change when copied elsewhere. So, if you have a formula in cell A1 and you copy the formula to cell B1, the result will automatically update for you in reference with the cell’s position.
However, there are times when you might want the cell references to remain fixed when you copy it elsewhere, and this is where absolute references come in. Absolute references mean that no matter which cell the formula is in, the cell reference will not change. If you decided to copy the formula to another cell or sheet, the formula will still refer to the original cell reference.
To create an absolute reference, you just need to add a dollar sign ($) to your cell reference, to fix your column and/or row references. Use the different absolute references below as required to anchor your references:
- A$1: The row will not change when copied.
- $A1: The column will not change when copied.
- $A$1: The column and the row will not change when copied.
To avoid manually adding the absolute reference, try pressing the shortcut key F4 multiple times until the correct option is displayed automatically.
3. Reduce errors with drop down lists
Data validation lists reduce errors in a spreadsheet and can make data entry easier, particularly if you have a spreadsheet that multiple people use. They allow a user to select from a drop down list of defined data options within a cell, such as a list of accounting codes. You can even add in an error message that will display if someone tries to enter data which isn’t in the drop down list.
- To create a drop down list, you first need to create your list of data options in a single column or row.
- Next, give your list a name list by selecting your cells, and clicking the name box at the left of the formula bar, then pressing Enter. The name of your cells must not have any spaces, eg:AccountingCodes.
- To add your drop down list options to a cell, navigate to the Data Tab and click Data Validation.
- Under the Settings tab, select List as the option under the Allow drop down list, then type the name of your list under Source.
- Select the cells, then click the name box at the left of the formula bar.
- Fill out the Error Alert tab to set your error message to stop someone entering different information.
4. Improve your visibility with The Watch Window
The Watch Window is a handy tool to use when you are working with a large spreadsheet, possibly with multiple sheets.
It enables you to keep some of your data in view at all times, so you can quickly refer to it without having to navigate back and forth between columns or sheets.
- To add a Watch Window, select the cells you’d like to have visible all the time.
- Go to the Formulas tab and click Watch Window to add them.
5. Highlight interesting data with conditional formatting
Conditional formatting is an extremely useful tool in Excel for accountants. You can use it to apply automatic formatting to cells that contain a certain value, duplicate values and even specific text.
For example, you could use it to flag up overdue payments by creating a rule to format all cells red that fall within a certain date.
- To create conditional formatting, click on Conditional Formatting under the Home tab.
- Select New Rule and choose from the different options and formatting selections. For example, you could choose to format cells that contain a certain value, if they are greater/less than a certain value, or if they are duplicated values to help you flag up errors.
- Choose the cells the formatting should apply to and click ok.
6. Name your formulas with named ranges
If you are using complicated formulas, you can give them a name to help you and your team understand and remember what they are. For example, you can name your first quarter sales formula ‘FirstQuarterSales’, or your income tax formula ‘IncomeTax’. This is very useful for spreadsheets that multiple people are working on.
- To do this, go to the Formulas tab.
- Click Define Name and select which cells it refers to.
By naming all of your different formulas in this way, Excel keeps a list of them under the Name Managerbutton, allowing you to edit them and apply them to different cells quickly and easily.
7. Combine data from multiple cells into one cell
If you have data in multiple cells that you want to combine into once cell, you can use the CONCATENATE function to do it automatically.
This can be really handy when you are working with spreadsheets that have names split into different columns for first name and last name, or your supplier names in one column and their payment terms in the other.
- To do this, insert a new column where you want your combined data to appear.
- Select a cell in the column, then type =CONCATENATE(A1,B1) into the formula bar with your relevant cell references for the data that is to be combined.
- To keep a space between your different data, use this formula: =CONCATENATE(A1," ", B1). The ," ",keeps the space between data, so don’t forget it!
- Your new data will now appear in the cell.
8. Speed up your navigation and cell selection
If you have a big spreadsheet and need to navigate through it quickly, just hold down the CTRL key and use your arrows.
If you want to select lots of data at one time, just hold down Shift and CTRL and use the arrows in the same way.
9. Copy formatting quickly with the F4 key
The F4 key is a useful shortcut in Excel that enables you to copy any formatting you have applied to a cell to another cell.
To copy formatting from one cell to another cell (or column), just apply the formatting, click where you want the formatting to appear and press F4.
10. Count how many times data appears
COUNTIF is a very useful and easy function to use in Excel. Quite simply, it will count the number of cells that meet your specified criteria. For example, you could count how many times you paid one of your suppliers, or how many times the word ‘January’ appears in your spreadsheet, or how many times ‘office supplies’ has been claimed on expenses.
The COUNTIF function will automatically search how many times your data appears.
In the cell you want your total to appear, enter the formula =COUNTIF(range,“criteria”).
Under range, specify the cells/columns it should search, and under criteria, specify the data it should search for. In your criteria, you can search for text, a number, cell reference, or even a comparison, such as >50.
11. Short cut for making total of the required number of rows:
Assuming that you have values ranging from the cells A1 to A20 and you want to get the total in Cell A21. This activity can be performed in two ways: The first method is very common and used mostly by many accountants. The first method requires you to insert this formula in Cell A21 “=Sum(A1:A20)”.
Another effective and short way is that you need to press simply the Alt key + = by placing the cursor in the same cell as mentioned above. You will notice that the said formula appears in Cell A21 automatically. Now you need to press enter key. In this way, you will have got same results with two different methods for making total of the required number of rows.
12. Making Grand total and Subtotals:
Very often, accountants are required to make grand total of subtotals in rows or columns. Whether it may be the tasks relating to preparing schedules or segregating the expenses to be charged to various cost centers, there is a great need for making subtotals as well as grand totals. This activity can also be performed in two ways. Let’s take a simple example for the first method. Supposing that you have values in a particular worksheet ranging from cells A1 to A20; every fifth row has a subtotal, such as A1 to A4 with subtotal in A5 and so on. And you want to make grand total in Cell A21. In order to make a grand total of subtotals, you simply need to type this formula in cell A21, “=Sum(A1:A20)/2”- without quotation marks. . This will give you the grand total of subtotals in cell A21. If you have the value 1 in each cell, with all subtotals 4, then, the grand total should be 16.
As to the other method for making grand total of subtotals, it may be said that it’s a bit tricky. It’s not as simple as the above mentioned method. In order to make grand total, you need to assign this formula in the cells in which you want subtotals. The formula is: “=Subtotal(9,A1:A4)” , “=Subtotal(9,A6:A9)” and so on. Finally, for your grand total, this formula will be required for inserting, “=Subtotal(9,A1:A20)”. This will provide the same result, which is 16. Yet again, by applying two methods you will have made subtotals and their grand totals.
13. Right/Left Function:
If there is a need for using some limited number of letters from a text existing in particular cells, you can make use of these functions. For instance, there is a text in Cell A1 “Commonsense” and you want to display only 6 letters of the word in Cell B1 starting from left. The function goes in this way: “=Left(A1,6)”. The word ‘Common’ will appear in B1. Similarly, by using this function in C1 this way, “=Right(A1,5), you will get the word ‘sense’. Simply speaking, the right and left functions are useful when you need to pick limited letters from a text starting either from left to right or right to left.
14. Use number formatting shortcuts
For circumstances when you need to format a large amount of data, Excel offers time-saving shortcuts for many common formatting functions. Experiment with these handy ones:
Format numbers to include two decimal places: Ctrl+Shift+1
Format as time: Ctrl+Shift+2
Format as date: Ctrl+Shift+3
Format as currency: Ctrl+Shift+4
Format as percentage: Ctrl+Shift+5
Format in scientific/exponential form: Ctrl+Shift+6
15. Use Sparklines to display data
Sparklines are a built-in feature of Excel that allow you to display small charts inside individual cells. These can be line charts, bar charts or simple win/loss charts. To create a Sparkline chart, select the range of numbers you’d like to include, click the “Insert” menu, then choose one of the chart options. Select a location range, which must be located along a single row or column in the same worksheet as your data range. Sparklines can help you easily display trends in your data in a compact format.
16. Move between formulas and results
To efficiently switch between the cell data and formula, use the Ctrl+tilde (~) keystroke. This allows you to rapidly check formulas when working in a large spreadsheet.
17 Hide zero values
Hiding zero values can be helpful within large data sets by allowing you to see data more clearly. To hide zero values, you simply need to change the options in your Excel setup. Navigate to this function by clicking the “File” drop-down menu, and choose “Options.” Then choose “Advanced” from the left-hand menu and uncheck the box for “Show a zero in cells that have zero value.” (Mac users: Go to the “Excel” drop-down menu and choose “Preferences,” then uncheck “Show zero values.”)
These are just a few of the helpful Excel features that can decrease the time you spend on spreadsheets and increase the amount of work you get done.
For a 150 page fully illustrated with screenshot e-book with lots of very eseential and useful tips and tools in Excel that can ramp up your productivity as an accountant, you can contact us. it costs just N1,500 and is available as a downloadable ebook. We can send it to you as an attachment too.