“Pivot Tables don’t work for me!” Lets consider the raw data!
Many people complain that they can’t use Pivot Tables and struggle to make them work. One of the big issues is the raw data they are using. Have a look at some of the common problems, and see if you can spot them in your data!
Blank Rows or columns – a blank row or column will split your data. As far as Excel is concerned the information stops there!
Inconsistent Data input – check that you haven’t any spelling mistakes. In the example above the Customer has been input as Col Cakes and Cool Cakes. Excel treats these as two separate companies.
Missing or poor column headings – Use your column headings well. Describe what goes in the column. Don’t leave the heading blank! Otherwise it will be blank in the Pivot table, and you still struggle to know what it refers to.
Ensure data formats are recognisable to Excel – It does not like 01.01.16 (although can be forced to accept that), or 1st Jan 2016. Unrecognisable formats appear on the left of the column, the recognised formats are on the right hand side.
Don’t enter text in a numerical column – If you have a column in your database displaying figures, ensure you don’t enter text in that column. It will affect any functions (such as a sum) as well as causing errors messages in a formula.
Spend a few minutes tidying up your data and you could save yourself hours!
