Limit Values in a Pivot Table

Limiting the values in a pivot table is moderately easy to do if you are familiar with Microsoft Excel. Filtering data through manipulating field settings allows you to limit the values expressed by the pivot table. Filtering data will allow you to display limited values in pivot table and in pivot table charts.Step 1 - create a pivot table

Highlight the data in the Excel Sheet by clicking on the upper left hand corner of the data sheet so that everything on the sheet is highlighted. Then Select "Insert" "Pivot Table".

Verify the range of the data in the worksheet in the pop up box and then Choose a "New worksheet" and Select "OK".

Step 2 - Set up the format

Set up your table by dragging the column and row header labels to the appropriate places in the pivot table work sheet. Drag only the columns and row headers that you want to see.

Step 3 - Adjust the totals

Adjust the total values in your pivot table by "Right clicking" on the Column label in your pivot table worksheet and choosing "Field Settings". The software defaults to summing the data (adding it to get a total) but if you choose to adjust it, you can show averages, a product, a count, a min, a max or the standard deviation of the set of numbers in that column.

Step 4 - Limit the values in a range

Limit the values of a field within the pivot table by Clicking the "Down arrow" that is next to the "Column Labels" heading. A pop up box unfolds prompting you to "Select Field", and then below that it has the following options: "Sort A to Z", "Sort Z to A", "More Sort Options", "Label Filters" and "Value Filters".

Select "Value Filters" and a new pop up box unfolds. It provides the following additional options to filter or limit your pivot table data information: "Equals", "Does not Equal", "Begins With", "Does not Begin With", "Ends With", "Does Not End With", "Contains", "Does Not Contain", "Greater than", "Greater Than Or Equal To", "Less Than", "Less Than Or Equal to", "Between" and "Not Between" and "Top 10."

Choose the option that meets your needs. Enter your filter values into the pop up box labeled "Value Filter". It will ask you to either input in a single number which is characterized by a "?", or input a series of numbers characterized by using a "*". Start by using either the question mark or the asterisk and then input your singular or series of numbers (separated by a comma) following the symbol.

Note that the pivot table retains all the information but only displays totals and data based upon your filter selections.

Tip

Once an Excel pivot table filter is used, it will stay "On" until turned "Off." To deselect the options, just click on it again. The check mark disappears indicating the filter is off.

When opening an existing Excel pivot table worksheet, always check the filter options to make sure you are aware of the current filter settings. Do not risk the chance of analyzing the data incorrectly because of exclusions caused by previous filtering activity.

Source:
Personal experience

http://office.microsoft.com/en-us/excel-help/pivottable-reports-101-HA001034632.aspx]