HomeHome SearchSearch MenuMenu Our productsOur products

Three rules to help accountants use Excel pivot tables effectively

by , 21 February 2014
Do you use Excel pivot tables when working on your company's accounts? If so, continue reading to find out how playing by these three rules can make your accounting tasks easier.

In this article, we told you all about Excel pivot tables and why they're an accountant's best friend.

Now we're going to give you three rules to follow when using these tables. They'll make your job a whole lot easier.

Follow these three golden when using Excel pivot tables for your accountants

#1: Don't have any empty rows or columns in your data

The Practical Accountancy Loose Leaf says pivot tables work off one continuous block of data.

An empty row or column will split that block into two parts. Excel will select the block to the left of the empty column, or the block above the empty row as its data source and will ignore the rest of the data. This means your pivot table results will be incorrect.

#2: Avoid blank cells in your data

One blank cell, while not fatal, compromises your pivot table. If there's one blank cell in a column that contains values, Excel will assume that this column contains text. It will by default count, rather than sum, the items in this column in the report.

You then need to manually change the pivot table settings to be able to sum the values. To avoid this happening, fill all blank value cells with zeros before you build your pivot table.

#3: Each column must contain similar items

Include text in one column and numeric values in a separate column. As each column contains one element of the record, such as the Vat amount, ensure all invoiced Vat amounts are recorded in the field set aside for it.

Bear in mind that Excel can't identify if an item is recorded in the correct field, or not, and accepts anything in the Vat column as being Vat. It's your responsibility to ensure the data is correct.

There you have it. Excel pivot tables will make your accounting life easier if you stick to these rules.

Enjoyed this article? Subscribe to receive these free articles in your inbox daily.

Related articles

Related articles

Watch And Learn

Related Products


Recommended for You 

  Quick Tax Solutions for Busy Taxpayers – 35 tax answers at a glance

Here are all the most interesting, thought-provoking and common tax questions
asked by our subscribers over the last tax year – everything from A to Z!

To download Quick Tax Solutions for Busy Taxpayers – 35 tax answers at a glance click here now >>>
  Employees always sick? How to stop it today

Make sure you develop a leave policy to regulate sick leave in your company.

BONUS! You'll find an example of the leave policy and procedure in this report.

To download Employees always sick? How to stop it today click here now >>>
  Absenteeism: Little known ways to reduce absenteeism

This FREE e-report will tell you how you can reduce absenteeism in your workplace while avoiding the CCMA and without infringing your employees' labour rights.

To download Absenteeism: Little known ways to reduce absenteeism click here now >>>
  7 Health & safety strategies to save you thousands

Don't let a health and safety incident cost you one more cent. Implement these seven
strategies in your company today.

To download 7 Health & safety strategies to save you thousands click here now >>>