Analyzing Data Using Pivot Tables
Analyzing Data Using Pivot Tables – An Example
Remember assignment 5 where you were asked to compare Invoice amounts to Sales Order amounts? You had to create a query to join together 2 tables from an Access database. If the results of that query had been downloaded into an Excel file (a simple thing to do), you could have used the Excel file and a Pivot table to help in the analysis. Before you try to follow this example, you should learn as much as you can about Pivot Tables from Microsoft’s user training site or from a training site provided by Dummies.com. Addresses of these sites are given below.
HYPERLINK "http://office.microsoft.com/en-us/training/" http://office.microsoft.com/en-us/training/ ... and/or
http://www.dummies.com/how-to/content/the-essentials-of-excel-2010-pivot-tables-and-pivo.htmlOne way to analyze the data would have been to add a column called “difference” to the Excel worksheet (because you need to analyze the difference between Invoice amounts to Sales Order amounts) and then convert the resulting range to a table using the “Format as Table” option in Excel (see below).
After the worksheet range in converted to a Table, you can analyze the contents by using (inserting) a Pivot Table in your worksheet.
To use a Pivot Table, you have to define the range (or Table) you are analyzing and identify an area to place the results.
Next, you must identify the variable you are analyzing (in this example, it would be “difference”), and the factors you would be analyzing (in this example, it would be “CustomerID” and “Employee_ID”).
After analyzing the resulting Pivot Table, you might want to add a Pivot Chart to your analysis (as explained in the tutorials recommended
Remember assignment 5 where you were asked to compare Invoice amounts to Sales Order amounts? You had to create a query to join together 2 tables from an Access database. If the results of that query had been downloaded into an Excel file (a simple thing to do), you could have used the Excel file and a Pivot table to help in the analysis. Before you try to follow this example, you should learn as much as you can about Pivot Tables from Microsoft’s user training site or from a training site provided by Dummies.com. Addresses of these sites are given below.
HYPERLINK "http://office.microsoft.com/en-us/training/" http://office.microsoft.com/en-us/training/ ... and/or
http://www.dummies.com/how-to/content/the-essentials-of-excel-2010-pivot-tables-and-pivo.htmlOne way to analyze the data would have been to add a column called “difference” to the Excel worksheet (because you need to analyze the difference between Invoice amounts to Sales Order amounts) and then convert the resulting range to a table using the “Format as Table” option in Excel (see below).
After the worksheet range in converted to a Table, you can analyze the contents by using (inserting) a Pivot Table in your worksheet.
To use a Pivot Table, you have to define the range (or Table) you are analyzing and identify an area to place the results.
Next, you must identify the variable you are analyzing (in this example, it would be “difference”), and the factors you would be analyzing (in this example, it would be “CustomerID” and “Employee_ID”).
After analyzing the resulting Pivot Table, you might want to add a Pivot Chart to your analysis (as explained in the tutorials recommended