College Being

Pivot tables are used in Microsoft Excel for easy compilation and analysis of large volumes of data. A pivot table summarizes the list of information into a simplified format arranging the rows and columns as per the requirement of the user and also provides options for easy rearrangement. These tables are considered one of the most powerful features of Microsoft Excel. The pivot tables can be used for easy data analysis in the academic as well as the professional world. This article lists 10 things you should know about Pivot tables and their application.

1. They can be quickly created

A pivot table can be created in Microsoft Excel within a minute. The manual creation of reports has been often a lengthy process and learning the application of pivot tables can simplify this process and make it much faster. The simple steps for creation of a table are given here:

1. Select any cell in the source data

2. On the Insert tab of the ribbon, click the PivotTable button

3. In the Create PivotTable dialog box, check the data and click OK

4. Drag a “label” field into the Row Labels area (e.g. Organization)

5. Drag a numeric field into the Values area (e.g. Units sold)

2. ‘Perfect’ data

The pivot tables make use of only perfect source data so that no blank rows or columns are included in it. The repeated groups of data are not included during data analysis and tool ensures that each row and column has a unique name and every field has a unique value which minimizes the chances of error in calculation or doubling of information.

3. Count of first

A quick count of the rows or the total record count can be obtained by simply adding a value field in the form of the text field in a table.

4. Creation of dynamic range

Creating a pivot table automatically makes the range of the data dynamic, so that it expands and contracts as the data is added and removed in the tables. This ensures that the created table is always in sync with the data be added or removed from it.

5. Closer auditing

The pivot tables allow the users to get the details regarding the data that makes the value in a cell. For doing this you can double click on the cell and this opens a new sheet comprising of the data that makes that value. It extracts the source of data, including the totals and subtotals to find out what makes up the value.

6. Different layouts

There are three different layouts that you can choose from for the creation of a pivot table. The compact, outline and tabular format, provide a different functionality to the users. The compact layout increases the readability of the tables and keeps the data related to one type in one column only. The outline layout has field headers for each column and is horizontally placed to provide easy access to the item labels. The tabular layout also has field headers included in each column and display the information in the traditional table format, providing an easy method for reusing the data for any further analysis.

7. Formatting numbers

The cells can be easily formatted by right clicking a value and selecting the number format option from the drop down menu. There are various formats to choose from including number, percentage, currency, ratios, or customized formats.

8. Show percentage

If you wish to show the data as a percentage instead of a count of numbers, this can be done by following the given steps:

1. Add a Product to the pivot table as a Row Label

2. Add Sales to the pivot table as a Value

3. Right-click the Sales field, and set “Show Values As” to “% of Grand Total”

9. Adding slicers

Slicers are often defined as the point and click tools that help in refining the data which is included in a pivot table. Adding slices also allow multiple users to customize the tables as per the requirements find multiple selections for a slicer can also be done by holding onto the control key and to choose the columns. In the example included below choosing a slicer of backpacks will only show backpacks from the various item types that are included in the earlier row labels.

10. Recommended Tables

Advanced pivot tables are already available in the Microsoft Excel and these can be accessed by choosing the recommended pivot tables option from the insert tab.

The thumbnails available on the left side of the window allow the users to view and choose the automatic Excel generated tables and this is a great tool for the people who are starting with the uses of pivot tables. These tables can also be modified as per your requirements and prove to be a time saving starting point for the users.

Author Bio: Nickea Pittman is working at AssignmentWorkHelp (AWH) which provides online assignment writing services to students worldwide. She has excelled in the field of assignment writing and thesis writing that help students to accomplish better academic score.