A Critical Pivot Table Skill - Removing Subtotals
If there is one thing that you need to learn about creating pivot tables, this is it. Removing subtotals is a simple but critical step in getting a better view of your data. Take it from someone that uses pivot tables every day, you need to learn this skill. Once removed, subtotals can always be added back later if necessary.
David Carter’s article linked to here shows you how it is done.
Just Pivot Tables Posts Summary
Check out these recent Just Pivot Tables Posts
Building a Pivot Table in Excel 2007
What Is a Pivot Table
Format Pivot Tables With Colors
Excel 2007: Your Guide to Effective Pivot Tables
Building a Pivot Table in Excel 2007
For those of you that prefer video training, the following pivot table tutorial for excel 2007 from “The Missing Manuals” series will be of interest.
What Is a Pivot Table
For an excellent review of how and why you can use a pivot table, Matthew MacDonald’s Missing Manual series is a great resource.
What Is a Pivot Table
by Matthew MacDonald, author of Excel: The Missing Manual
08/12/2005
- Pivot Table
- The Pivot Tables tool is one of the most powerful yet intimidating features in Excel. Pivot tables allow you to quickly summarize and analyze large amounts of data in lists and tables–independent of the original data layout in your spreadsheet–by dragging and dropping columns to different rows, columns, or summary positions.
This article discusses the following:
Pivot Tables With Colors
Jazz up the presentation of your pivot tables with colors.
Format Pivot Table with Colors
Do you know that you can format pivot table with colours to faciliate viewing or presentation? I have always thought that pivot table is only a good analytical tool but not a good report presentation tool. This is because the results displayed is very plain with the fields colored grey and the data are all nested within lines that outline the pivot table with a white background. I was totally wrong. I found that you can actually format the pivot table to make it look professional and use it for reporting. The question is how? I took a fair bit of time to figure out how to format the pivot table so that the colors appears at the right place. And you don’t need to purchase tools that will help you to format the pivot tables. Here is the step by step guide to achieve a colorful professional looking report.
Excel 2007: Your Guide to Effective Pivot Tables
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. As the instructor of our Excel 2007 Training, Bill Kulterman, put it “Pivot tables allow you to find that needle in the haystack.”
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.
Introduction to Excel 2003 Pivot tables
If you are interested in a well done tutorial on how to use excel pivot tables the pdf file linked to below from Duke University is what you are looking for.
Introduction to excel 2003 Pivot Tables
How to Use Pivot Tables to show values and percentages side by side
I came across this very well done video of how to set up a pivot table in Excel 2007 to show the value and percent for the same item. This is a little used but valuable feature in Excel that will help you to easily show the relative importance of the various items.
Rikki’s Hints & Tips - May 2008
Click on the image to view the video
OpenOffice.org Calc’s answer to Excel pivot tables
Just in case you thought that Excel pivot tables were the only game in town. You can check out the link below for some information on OpenOffice’s pivot table otherwise known as the data pilot.
Using the data pilot: OpenOffice.org Calc’s answer to pivot tables
Solveig Haugland
With the right statistics, you can prove anything. Whether you want to prove that coffee shops serving your company’s brand of coffee have 12.5% more attractive customers, that your spouse does only 23.42% of the chores around the house, or that the Flying Spaghetti Monster is the one true deity, you need to be able to manipulate and slice and dice your data to produce the right statistics.
How to Create Pivot Tables
How to Create Pivot Tables in Excel
from wikiHow - The How to Manual That You Can Edit
PivotTables are an interactive table that allow the user to group and summarize large amounts of data in a concise, tabular format for easier reporting and analysis. One advantage of this feature in Excel is that it allows you to rearrange, hide, and display different category fields within the PivotTable to provide alternate views of the data.
Steps
- Start Microsoft Excel.
- Download the data used for this exercise at PivotTable source file so you can follow along.
- Click on the Data menu and choose PivotTable and PivotChart Report.
- Answer the questions presented as follows:
- Specify the location of the data you are going to summarize. If you have your data in an Excel list that is currently open, Excel will automatically select the cell range.
- Click Next.
- Ensure the New Worksheet option is selected after you click Next from the previous step, and then click Finish.
- Assemble the PivotTable. The PivotTable field list can be a lifesaver for new and advanced users alike.
- Looking at the data, let’s say you want to see how my sales representatives did. Add SalesRep field to the Row area. To do that, click on SalesRep, change the dropdown to Row Area and click Add.
- Next, add the Total field to the Data area.
- That just tells you how much of everything they sold, but what if you want to see how much in each category? Add the Category field to the Column area.
- Supposing you don’t want the Condiments category as part of the analysis, click on the dropdown arrow beside Category and deselect Condiments.
- Click OK. Notice how the Grand Totals have been recalculated to not include results from the Condiments category…
- To make a PivotTable look good, there are a multitude of AutoFormats available. Click on the Format menu and choose AutoFormat.
- You’re done, unless you want to do further analysis!
Tips
- If you use the Import Data command from the Data menu, you have more options on how to import data ranging from Office Database connections, Excel files, Access databases, Text files, ODBC DSN’s, webpages, OLAP, XML/XSL, and more. Then you can use your data as you would an Excel list.
- If you click the Browse button in the dialog box from Step 7, Excel will allow you to browse to an external Excel file but you will have to specify the cell range yourself. It’s better to use the External Data source instead.
Warnings
- If you are using data in an existing spreadsheet make sure that the range that you select has a unique column name at top of each column of data.
Things You’ll Need
- Microsoft Excel
- Data to consolidate
- Network connection for some kinds of external data.
Related wikiHows
- How to Create an Excel Financial Calculator
- How to Create a Currency Converter With Microsoft Excel
- How to Recover a Corrupt Excel File
- How to Add Two Cells Already Containing Sum of Other Formulas
- How to Calculate the Day of the Week in Excel
- How to Merge Documents in Microsoft Word
Article provided by wikiHow, a collaborative writing project to build the world’s largest, highest quality how-to manual. Please edit this article and find author credits at the original wikiHow article on How to Create Pivot Tables in Excel. All content on wikiHow can be shared under a Creative Commons license.
