By Reen Rose
Do you work with data in Excel? Are you using Pivot Tables? If you aren't, it may be because you don't know how. You may even be a little in awe of them and think your Excel skills aren't up to tackling these mysterious creations. Nothing could be further from the truth. I can't think of anything that is surrounded with as much undeserved mystique as Pivot Tables. Let me show you how to create one from raw data. In fact, describing the steps is much more challenging than it is to insert one into your workbook.
The concept of Pivot Tables has changed very little since they were originally introduced, but with every new version of Excel, Microsoft has introduced changes to the method of creating them. I am basing my article on Office 2010 so things may look a little different on your screen. Start by finding some data that you would like to summarize. It needs to be set up correctly in a table or database.
1. Click on any cell in your data.
2. On the Insert ribbon, in the Tables group, click on the top half of the Pivot Table button.
3. A dialog box will appear. Your data should have a flashing line around it, to indicate the cells that Excel thinks you want to summarize. You can alter this if needed. Leave New Sheet chosen for the last option and click OK.
Your screen should have a Field List pane on the right side and a box that says Pivot Table # and some text and graphics on the left. This is the point where many first time creators get a terrified look in their eyes, close the workbook without saving changes and deny they were ever there.
One way to get a basic understanding of the parts of a Pivot Table is to imagine an attendance record like the ones used in school. Generally, the dates are put in the first row (the Column Labels) and the names of the students down the rows in the first column (the Row Labels). A mark is made in the intersecting cells (Values) to show whether the student attended or not. This example doesn't accurately represent the way you collect data in Excel, but it serves our purpose of identifying the areas within a Pivot Table.
Now that we know the areas, we can return to creating one:
4. On the right of your screen you should see the Field List. The field headers from your data should be in the box at the top. Any header that you drag into the Column Labels box will go across the top of your table. Any header that you drag into the Row Labels box will go down the left side of the table. Any header that you drag into the Values box will be summarized in the cells in the center. If the header you drag represents numbers, you will have totals, but if the header represents a field of text the count function will be used for the value.
5. If you want to remove headers from any of the boxes, just drag and drop them anywhere in the Choose Fields box.
6. You can add more than one field to the Row or Column labels. Try dragging these headers to change their order and you will get a very different look. To create a linear table, only put headers in the Row Labels, leave the Column labels box empty.
The best way to begin your journey with Pivot Tables is to drag field headings in and out of the different areas and see what happens. The type of table you want to create depends on the type of information you want to find. It can be a process of trial and error. This article only scratches the surface of what you can do with Pivot Tables, but first steps are important and I encourage you to get acquainted with this powerful Excel tool. You never know what you may be able to accomplish.
Helpful Tips
1. As long as your Pivot Table is active you will see contextual ribbons (Pivot Table Tools) with two tabs (Options, Design). This is where all the Pivot Table tools are. If you are not clicked onto the Pivot Table, or the Pivot Table box that is on the left of your screen when you first insert a Pivot Table, these tools will disappear.
2. A valid database may have empty cells but there shouldn't be any completely empty rows or columns.
3. If you accidentally close the Pivot Table Field List that is on the right of your screen, click on the Field List button in the Show group of the Options tab of the Pivot Table Tools to get it to reappear.
4. Your data is on another sheet of your workbook. Look at the tabs at the bottom to find it.
5. You can make more than one Pivot Table for the same data.
6. If you make any changes to the data and you want it to be reflected in your Pivot Table, you have to update/refresh the table manually. This gives you the option to leave the table unaltered while more raw data is being added.
a. Choose Refresh from the Right-click menu (right-click on the Pivot Table).
b. Click on Refresh from the Data group on the Options ribbon of the Pivot Table Tools.
The concept of Pivot Tables has changed very little since they were originally introduced, but with every new version of Excel, Microsoft has introduced changes to the method of creating them. I am basing my article on Office 2010 so things may look a little different on your screen. Start by finding some data that you would like to summarize. It needs to be set up correctly in a table or database.
1. Click on any cell in your data.
2. On the Insert ribbon, in the Tables group, click on the top half of the Pivot Table button.
3. A dialog box will appear. Your data should have a flashing line around it, to indicate the cells that Excel thinks you want to summarize. You can alter this if needed. Leave New Sheet chosen for the last option and click OK.
Your screen should have a Field List pane on the right side and a box that says Pivot Table # and some text and graphics on the left. This is the point where many first time creators get a terrified look in their eyes, close the workbook without saving changes and deny they were ever there.
One way to get a basic understanding of the parts of a Pivot Table is to imagine an attendance record like the ones used in school. Generally, the dates are put in the first row (the Column Labels) and the names of the students down the rows in the first column (the Row Labels). A mark is made in the intersecting cells (Values) to show whether the student attended or not. This example doesn't accurately represent the way you collect data in Excel, but it serves our purpose of identifying the areas within a Pivot Table.
Now that we know the areas, we can return to creating one:
4. On the right of your screen you should see the Field List. The field headers from your data should be in the box at the top. Any header that you drag into the Column Labels box will go across the top of your table. Any header that you drag into the Row Labels box will go down the left side of the table. Any header that you drag into the Values box will be summarized in the cells in the center. If the header you drag represents numbers, you will have totals, but if the header represents a field of text the count function will be used for the value.
5. If you want to remove headers from any of the boxes, just drag and drop them anywhere in the Choose Fields box.
6. You can add more than one field to the Row or Column labels. Try dragging these headers to change their order and you will get a very different look. To create a linear table, only put headers in the Row Labels, leave the Column labels box empty.
The best way to begin your journey with Pivot Tables is to drag field headings in and out of the different areas and see what happens. The type of table you want to create depends on the type of information you want to find. It can be a process of trial and error. This article only scratches the surface of what you can do with Pivot Tables, but first steps are important and I encourage you to get acquainted with this powerful Excel tool. You never know what you may be able to accomplish.
Helpful Tips
1. As long as your Pivot Table is active you will see contextual ribbons (Pivot Table Tools) with two tabs (Options, Design). This is where all the Pivot Table tools are. If you are not clicked onto the Pivot Table, or the Pivot Table box that is on the left of your screen when you first insert a Pivot Table, these tools will disappear.
2. A valid database may have empty cells but there shouldn't be any completely empty rows or columns.
3. If you accidentally close the Pivot Table Field List that is on the right of your screen, click on the Field List button in the Show group of the Options tab of the Pivot Table Tools to get it to reappear.
4. Your data is on another sheet of your workbook. Look at the tabs at the bottom to find it.
5. You can make more than one Pivot Table for the same data.
6. If you make any changes to the data and you want it to be reflected in your Pivot Table, you have to update/refresh the table manually. This gives you the option to leave the table unaltered while more raw data is being added.
a. Choose Refresh from the Right-click menu (right-click on the Pivot Table).
b. Click on Refresh from the Data group on the Options ribbon of the Pivot Table Tools.
Reen Rose is an experienced Microsoft Certified Trainer and Microsoft Office Specialist Master.
She has been described as 'focused, fun and very effective'. Reen believes in guiding people to become empowered Microsoft Office users by giving them the skills they need to be successful, in an engaging and enjoyable environment.
To find out more about Reen and the training and corporate services she offers, please visit her website at http://www.ReenRose.com.
Article Source: http://EzineArticles.com/?expert=Reen_RoseShe has been described as 'focused, fun and very effective'. Reen believes in guiding people to become empowered Microsoft Office users by giving them the skills they need to be successful, in an engaging and enjoyable environment.
To find out more about Reen and the training and corporate services she offers, please visit her website at http://www.ReenRose.com.
Article Source: http://EzineArticles.com/8389974
0 comments:
Post a Comment