10 Excel tricks for CAs

Excel
By Isabelle Bell, CA Today

19 October 2015

Love it or hate it, Microsoft Excel is one of the most essential tools to any CA. We’ve asked some of our resident Excel wizards for their top tips to make your working life a bit easier.

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.

  1. Select a cell in your source data.
  2. On the Insert tab, click the PivotTable.
  3. Choose the fields to add to your PivotTable report.
  4. Drag a ‘label’ field into the Row Labels area (e.g. Client).
  5. Drag a numeric field into the Values area (e.g. Assets).
  6. 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.

  1. To create a drop down list, you first need to create your list of data options in a single column or row.
  2. 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.
  3. To add your drop down list options to a cell, navigate to the Data Tab and click Data Validation.
  4. Under the Settings tab, select List as the option under the Allow drop down list, then type the name of your list under Source.
  5. Select the cells, then click the name box at the left of the formula bar.
  6. 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.

  1. To add a Watch Window, select the cells you’d like to have visible all the time.
  2. 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.

  1. To create conditional formatting, click on Conditional Formatting under the Home tab.
  2. 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.
  3. 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.

  1. To do this, go to the Formulas tab.
  2. 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 Manager button, 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.

  1. To do this, insert a new column where you want your combined data to appear.
  2. 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.
  3. 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!
  4. 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.

E.g. =COUNTIF(A1:A200,”STATIONERY”)

Share your own

We hope you found these Excel tips useful. If you have an Excel tip to share, tweet us @icasaccounting using #CAexceltips.

Topics

  • Technology
  • CA life

Previous Page