20- Pivot tables

(Open Exercise file) Back To Excel Index Page Back To Edmond Home Page

201- What are pivottable reports and pivotchart reports? Play Video

A Pivot Table is way to present information in a report format. The idea is that you can click drop down lists and change the data that is being displayed. For example, choose just one student from a drop down list and view only his or her scores. Pivot tables are a lot easier to grasp when you see them in action. Here’s the one we’re going to create in this section:

Look at Row 4. This shows that the student is Elisa. If we click Elisa’s drop down arrow, we’ll see this:

Now we have another student to select (we’ll only use two students, for this tutorial). We could untick Lisa, and tick Mary instead. Then her scores would display.

The Subject and Month cells also have drop down lists. So we could view only January’s scores, and just for Art and English, for example.
So this is a Pivot Table – a report that we can manipulate by selecting items from drop down lists. Let’s make a start.

The first thing you need for a Pivot Table is some data to go in it. Instead of typing all the data out, you can simply grab ours. Go to this web page on our website and save the spreadsheet to your own hard drive.

202- Creating a pivottable Play Video

Pivot tables are not new to Excel 2007; in fact they’re included in all versions of the program. But surprisingly not a lot of Excel users are familiar with them. If you’re an avid Excel user don’t miss out on this very useful Excel feature since pivot tables have the ability to transform the way you look at your data.

Pivot tables allow you to easily analyze, summarize, and organize large amounts of data so that you’re able to make better sense of what you’re looking at.

Pivot tables in Excel 2007 also remain very flexible and easy to manipulate so that at any time you can adjust the data and reorganize it through the use of filters.

Creating a Pivot Table:

When you’re working with Pivot Tables the first thing you need to ask yourself is: What do I want to get out of my data? Or basically, what do you want to see on your Pivot Table.

Start by taking a closer look at your data and make sure that you don’t have any blank rows or columns.

1- select the Insert tab on the Ribbon:

2- Then click on Pivot Table at the far left, and select Pivot table:

3- Create Pivot Table dialogue box will come up that will allow you to select all the settings:

4- First you will get to select a table or range. If you have no blank columns or rows in your spreadsheet, you should already have all the data selected, but you can always double check this before you click okay.

5- You will also get to choose where you want the Pivot Table report to be placed. If you choose existing worksheet you will need to specify where you want the table to begin. For our example, I’ll put the Pivot Table on a New Worksheet:

6- Click okay, and there it is! Your Pivot Table is ready to come alive. At the right you’ll notice the Pivot Table Field List which lists your column headers under Choose fields to add to report. Underneath that you will see the different areas that the data can be applied to.


7- On the left you will see the structure of the Pivot table and this is where your pivot table will appear:

8- By selecting the different fields you get to choose which information goes into your pivot table. You do this by clicking on the boxes next to the different fields or dragging them to the different areas below:

9- The Values area is the data field and this is where you decide what you want to analyze. So in our example, if we want to show the total number of sales in the pivot table, this is where we want the sales field to be.

10- Column labels and row labels determine how many columns and rows you have in your pivot table. So if we want to see particular data in a column instead of in rows all we have to do is drag the field to the appropriate area:

11- Next we will drag the fields that we want to be able to filter in the report and the data that we want to see displayed in rows:

12- The pivot table is now ready! We have our Quarters field displayed in columns, we have our Saltwater fish displayed in rows and the data that we’re looking at show us the total number of sales and a grand total:

13- We can also analyze this data by a particular store since this is the field that we included in our Report Filter area. So at the top of the page our filter allows us to select the store (or multiple stores) and the data that will be displayed in the pivot table will include only what we have selected through our filter:

14- The pivot table can be manipulated at any time to display the data and analyze it in a different way. So if we don’t want to look at our sales numbers by store, but instead want to be able to filter it out by quarters all we have to do is drag the fields to the appropriate areas:

15- Now we can filter out our data by Quarters and see how each store performed within each quarter:

16- We can also filter out the first column so that if we want to narrow down our data and see how a specific product or a group of products performed, all we have to do is click select it:

17- Your pivot table gives you numerous options and allows you to manipulate your information in every possible way so that every question you might need to ask will receive an accurate answer displayed in an organized, easy to read and make sense of pivot table.

203- Laying your pivottable Play Video

1-

2-

3-

Modify a PivotTable Report:

1) Click any field in the PivotTable report. 

2) Click the Options tab under PivotTable Tools.

3) To change PivotTable options, click the Options button

1) Make the changes and then click OK.

· To change field settings, select the field, 

· click the Field Settings button

· Then Make the changes and then click OK.

4-

204- Designing your pivottable Play Video

Format a PivotTable Report:

1) Click any field in the PivotTable report. 

2) Click the Design tab under PivotTable Tools. 

3) Click the More list arrow under PivotTable Styles and then click the PivotTable style.

4) Select or clear the PivotTable format options: 

5) Select or clear the PivotTable format options: 

6) Row Headers, Column Headers, Banded Rows, Banded Columns

7) Select Subtotals to show or hide subtotals

8) Select Grand Totals to turn on or off grand totals for columns or rows.

9) Select Report Layout to set to compact, outline, or tabular form.

10) Select Blank Row to insert or remove a blank line after each item.

5-

205- Creating a pivotchart Play Video

Create a PivotChart Report from a PivotTable Report

1) Click any data field in the PivotTable. 

2) Click the Options tab under PivotTable Tools. 

3) Click the PivotChart button.

4) Click the chart type. Click OK.

Modify a PivotChart Report:

1) Click the PivotChart. 

2) To filter fields, click the Axis Fields (Categories) or Legends Fields (Series), 
and then select the filter options.

3) Click the Analyze tab. 

4) Select a field, and then click the Expand Entire Field or Collapse Entire Field button.

5) Use Design to change chart styles, layouts, and type.

6) Use Layout to change chart labels, axes, and background.

7) Use Format to format chart elements using Shape and WordArt styles.

Back To Top Back To Excel Index Page (Open Exercise file)

Back To Edmond Home Page

Copyright © Edmond Hakmeh 2009. All Rights Reserved