For your Excel pivot tables to be useful and easy to work with, you must first ensure that the source data table is properly structured. A good data structure is rectangular. It begins in the upper left corner and contains no missing rows and no missing columns. The data should be contiguous. Also, in Excel, make sure that there are no merged cells. Unmerge them before creating your pivot table.

Data is representative of “observations”. An observation is an item in the world, such as a box of cereal, or a person. If your data is a list of employees in a company, then each row will represent one employee. Each column will describe that employee with a “variable”. For example, an employee has a first name, a last name, and a date of birth. The first row of the table should contain a title describing the variable.

Good source data is free of unnecessary formatting. The source data should not contain any subtotal rows or calculated fields. Excel Pivot Tables will handle the calculated fields and subtotalling for you. Your source data should have no headers or footers inside the data itself. Keep it clean and simple when it comes to your source data.

Types of Data

The columns will contain one of two types of data: dimensions or measures. Dimensions are qualitative. Dimensions can be categorical, such as meat, dairy, produce or even a direct description of the observation itself such as russet potatoes. A dimension can be a geographical observation such as a country or region. Also, questions that have a yes or no answer are also categorical data. Measures are quantitative. A measure is a numerical value, such as a quantity, a dollar amount, a weight, a distance, a rating from 1 to 10, a volume in liters or some other value. Measures can be aggregated. A number can be discrete or continuous. Continuous data is infinite and impossible to count. Your weight in pounds is continuous. It’s impossible to get a perfectly accurate measure of your weight because of the various conditions and tools used to measure weight. Time and Length are continuous.