What pivot tables can do in spreadsheets

Wikipedia describes pivot table as a data summarization tool found in spreadsheets and business intelligence software. Although, the tool is used mainly for summarization, the tool has been put into other uses in various software. Pivot tables can do graphs, calculate values, percentage changes and so on and so forth.

Making a basic pivot table

To make a basic pivot table, there are few things that a spreadsheet user should

understand. The main objective is to aggregate the values in a numerical field or column. To illustrate, I'll use this web page that shows how to make a basic pivot table. In the data, the amount column has the numerical data and will be a vital component of the pivot table.

Every pivot table summarizes a numerical data from a column in a workbook. It uses filters to select data that fit a certain criteria whilst summarizing a numerical data. There are 3 components of pivot table and they are :

Rows labels: These are row filters applied to pivot tables whilst working on numerical data.

Column labels: These are column filters applied to pivot table whilst working on numerical data

Report Filters : This filter selects rows and columns that meet a set criteria whilst working on data.

Values: This is the numerical data to be worked on. Aggregation functions like average,count,sum, product can be performed on the data. Statistical functions like standard deviation can be performed as well.

Values

These values worked on can be taken directly from the data or derived from a calculation. The values can be formatted and displayed according to user requirements. They can also be displayed in special ways by using the "Show Values As' tab.

 

The diagram above shows the shows the 'Show Value As' tab. The columns or fields in the data can determine how values are displayed. There are are 15 different calculations that can determine data is displayed based on columns or fields in the data. This website explains the process in details. Using fields or columns as base field, it is possible to calculate percentage difference between fields, rankings between fields. Thorough planning is needed to pick a base field that will be able deliver what user intended.

Charts

This is basically the process of visualizing data from pivot tables. The advantage of this graph is the use of filters to visualize data that meets a given criteria. This website explains the process in details. Pivot chart is the quickest way of graphs that are supposed to meet multiple criteria. The diagram below is an example of a pivot chart. See the filter buttons that change the graphs depending on what filter is applied.

There's limit on how data can be manipulated with pivot tables, all is needed is planning to figure out what the end result should look like.

Enjoy working with pivot table.

Why data lookups fail in spreadsheets

To be able to lookup data is quite an important skill to have for a spreadsheet user. However, most users struggle with this skill because of Cell Referencing.

The website spreadsheets.about.com describes cell reference  as identification of a cell or a group of cells in a worksheet. So data lookup will not work without cell referencing. There are 3 types of cell referencing, namely:

Relative cell reference: This is the default setting in spreadsheets. Row and Columns change with formula with  this cell reference.
Absolute cell reference: This cell reference has instructions to peg a row or column. It is done by inserting the '$' sign in front row column notation. For instance $A1, will peg reference to column A, the cell reference will change only for row numbers.
Mixed cell reference: This reference combines relative and mixed cell reference to look up data for formula. To find out more on cell referencing , check this page 

Formulas that look up data
There are so many formulas that look up data. It depends on what the user's objective is for the data. Is data the final product or work-in-progress that goes into another formula. This web page provides guidelines on how to decide what formula to use and offers tutorials on how to use these formulas. I will discuss the impact of cell referencing on a few of these formulas with an example.

Let's assume that there are two tables in a worksheet, the first table is just as shown below.

The other table will be looking up data from this table above. This table has got data validation tool that enables it to pick data from a list, this means that it can look up price data for the entire item on the list. It looks as shown below.

Any item picked from the list will have it's price displayed by looking it up from the list above. The lookups has been done with formulas VLOOKUP,INDEX MATCH and OFFSET MATCH. I'll be discussing the cell referencing used to look up the data.

VLOOKUP

The syntax for VLOOKUP can be summarized as shown below:

vlookup(what is to be looked up, where to search, what column to search, the kind of match needed) 

In the formula above, E1 in blue is the item to be looked up, $A$2: $B$5 in red is range of cells to search, 2 means that search should narrowed to the 2nd column which is price, 0 tells the program that exact match is required. For this formula, mixed cell referencing has been used, the character in blue has relative cell reference, while those in red have absolute cell reference. This way, any item picked (having a relative cell reference) can be looked up because search boundaries have been set with absolute cell references in red.

INDEX MATCH

The formula combines two formulas to look up data and the syntax is as follows:

index(the result area,match(what is to be looked up, the area to search, the kind of match needed)

In the formula, the area in pink shows the boundary for the search, the character in red is the value to be searched for in the search boundary. The areas in blue is where the result data can be found. Like in the formula above, mixed cell referencing is used, the item to be searched has relative cell reference, while the result and search boundaries do have absolute cell referencing.

OFFSET MATCH

Just like the formula above, this formula is also a fusion of two formulas, the syntax is as follows:

offset(the first row of search area,match(what is to be looked up, other rows in the search area , match needed), the number of adjacent rows to return)

The the first row is pegged with absolute cell reference in blue, the item to be searched for has relative cell reference ( this way, any item can be looked up), the search area is pegged with an absolute reference in red.

Cell referencing establishes search boundaries for formulas to use during look up. The rule of the thumb is to have items to be looked with relative cell references, search areas with absolute cell references so that the search areas are confined and the right data looked up.

Enjoy looking up your data