Microsoft Excel Pivot Tables are used in the user’s spreadsheet to present quantitative data in an easy and user-friendly manner to allow the user to quickly analyze large amounts of data. To manipulate data in an Excel PivotTable it must have data that is capable of being able to be categorized or grouped. To create a Pivot Table you must have a data or values in Excel, or other form of data from which it could be imported, say from Microsoft Access. Before one can manipulate the Pivot Table it must first contain the data and its name for record or labeling purposes, so setup your data first, before you create the PivotTable. When the Pivot Table is already created, the next step would be displaying the data in line with the values presented in each field. To do so, you must hold down the left button of the mouse then release the mouse button on the end-column.

 

Before manipulating the Pivot Table you should first learn the different methods in presenting a group of different values which is to be presented in the Pivot Table. You can read free Excel help articles such as this, search Google, or hire an Excel consultant to help you. If the data to be grouped is in the Row or Column select on or more data by clicking on the data while pressing Ctrl keys. After highlighting, click on the right button of the mouse and select on Group and Outline. Another method is grouping through formulas. Do the same selection process then click on the right button and select Formulas. The last method is the Hide items method. The method starts on clicking on the Page field then select Hide items. All the shaded or selected boxes with the values you want to hide will be hidden.

To manipulate data in the MS Excel PivotTable such as in adding or removing click on the field and add the label of the values. In your Microsoft Pivot Table you can also turn the table such as moving the rows and columns to see the variety of data summary. To filter data is another manipulation in the Pivot Table; this is to manage large data. To achieve this filter, select on page field to separate the table in different pages. The page will eventually contain sub-data of a summary of the main Pivot Table. To create a consolidated report for easy understanding, one can make the page fields to limit the quantity of data. This could give you an easy time on the waiting and memory matter. You can also manipulate the data by showing only the data you want to see or be visible.

 

The data or variables that is seen in the center of the Pivot Table can be changed or stated in sum, average, mean, count, total, and others. In order for you to change or alter the data which is being presented just click once the right button of the mouse and select and click on Field or Field Setting. After, select and click on Summarize by: operation. Though the Pivot Table shows one field of data, there are still other available choices in the Pivot Table Field box such as to alter the name of the field, change number formatting, and you can even delete a variable.

Another manipulation of data in the Pivot Table is converting or displaying rows or columns in percentage. To have a total of 100% when you add a column, a row, the whole table, you just have to follow the previous data manipulation above mentioned. After which, click on Options to be able to extend the Pivot Table, and select and click Show Data As, then Normal Selection, and choose your format.

 

There are still other options available for field variables. Select and click-on Field variable, then click-on Options, after select Advanced. This selection gives the user the permission to show the values for the selected field and sort it on the table. The selection can be in ascending or descending order, you can also limit the values of the data with the highest possible value such as 10.

To manipulate data from a list or from a data base and transfer it to your Pivot Table you must first create a list or your data base where the source of Pivot Table. Double-click the Grand Total of the Pivot Table. Choose the cell that fits to the criteria and then double-click; this is for you to visibly see the source of your records which fits the certain variable.

These are just the main and basic manipulation of data in a Pivot Table. There is still an array of manipulative process which you can use for easy and accessible usage of the table.

 

We hope this Pivot Tables article covered what you were looking for, if not, we have additional articles on Microsoft Excel PivotTables for you to read. We also have several free Excel PivotTable videos for you to watch, along with a free Excel Pivot Table workbook you can download. If these do not take you to the level of experience you are looking for, please consider our paid Excel consultant and Access programmer services. The rates are low, the call is free, the consultation is free, and the results, well, they will exceed your expectations, guaranteed. For more information, and to have the free consultation, please call Christopher directly i at our corporate office which is in the Irvine area of Orange County California, at 714.262.6893. If you are out of state, we have offices in New York, dallas, and Florida to name a few. For those locations please call 877-392-3539, which covers Canada as well. If you would like additional articles on this topic, please send us an e-mail indicating so, and what you would want to have covered in the free Excel help article. Thank you.