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.