SIMPLE EXCEL SOLUTIONS: Understanding Pivot Tables

I always get questions about handling pivot tables. What are they? How should we use them? Or some ask: “Why aren’t they working?”

Below, you will find a brief description of my method of handling pivot tables, that may help you get a better understating as to how it works, if you already use it, or encourage you to start pivoting.

I would define a pivot table as an interactive, “life-like”, tool to transform data file into summary tables and graphs. Using pivot tables, you can transform huge data files into meaningful reports and summaries at no time. A pivot table is a query Database tool created in Excel using Database approach.

To start a pivot table you need a data file, most probably from your accounting system or data collection system. Reliable and consistent data are the key for any pivot table. You will get your pivot results much faster and easier, if you have understood a data file and know how to update, adjust or enhance the data.

Data table requirements:

1.      Choose a simple tabular layout with information presented in rows and columns. Hint: always review your data table; check if there are blank rows or blank columns, or if data are consistent (text format in “Text” column, numerical format in “numerical data” columns).

2.      Title: Each column must have a title (mandatory). Pivot table won’t work if you have unnamed columns. Pivot table reads entire data table and stops working if there are empty title columns. Few examples:

o  Wrong data table: 3rd column has no title name data. Solution: delete the empty column or add the title in case there are data

o  Correct data table, all columns has title:

3.  Pivot table always refers to data range, so If you are using pivot tables on a monthly basis, and your data are expected to expand, create a “table” from your data source in such a manner in order to organize and make your data range dynamic (Ctr+T).

4      Review data and plan what report, grouping and summary you are going to create out of your data. For example, your product sales report contains 3 columns: “Product ID”, “Month” and “Sales amount”. In the sample table below, product ID starts with the letter, which is region indicator; use IF of VLOOKUP functions to create a new column “Region” to create pivot and summarize “Sales by region”. You can do any updates and additions to your data table. Next time when you get your data source it will be easy to update it.

Happy Holidays! More to come …