The pivot table is a summary of a large dataset that usually includes total statistics, average, minimum, maximum, etc. Suppose you have sales data for different regions, with pivot tables, you can summarize and find data by region. Average sales per region, maximum and minimum sales per region, etc. Excel Pivot tables allow us to analyze, summarize and show only relevant data in our reports.
Pivot Table is a reporting engine built in Excel. They are the best tools in Excel for analyzing data without formulas. You can create a basic pivot table in about a minute, and start searching for your data interactively. Following are more than 10 tips to get the most from this flexible and powerful tool.
Feauteres of Pivot Table
1. You can build a pivot table in one minute
Many people think that building a pivot table is complicated and time consuming, but this is simply not true. Compared to the time it would take you to manually create an equivalent report, pivot tables are incredibly fast. If you have well-structured source data, you can create a pivot table in less than a minute.
2. Perfect your source data
To minimize problems down the road, always use good quality source data, arranged in a tabular layout. The “true” source data will not contain any empty rows or columns, and no subtotals. Each column will have a unique name (on one row only), each row will have a value in each row, and the column repeatedly holds groups of data (such as month names, place names, area names, etc.). Will not do
3. Count the data first
When you first create a pivot table, use it to generate a simple count first to ensure that the pivot table is processing the data as you expect. To do this, add a text field as a value field. You will see a very small pivot table that displays the total record number, i.e. the total rows of your data. If this number makes sense to you, then you are good to go. If the number does not make sense to you, it is possible that the pivot table is not reading the data correctly or that the data is not defined correctly.
4. Plan before you build
While it’s a lot of fun pulling fields around a pivot table, and looking at Excel is yet another unusual representation of data, you can find yourself going down the unproductive rabbit hole a lot more easily Used to be. An hour later, it’s not fun anymore. Before you start building, note down what you are trying to measure or understand, and sketch some simple reports on a notepad. These simple notes will help guide you through the large number you choose at your disposal. Keep things simple, and focus on the questions you need to answer.
5. Use a table for your data to create a “dynamic range”
If you use an Excel table for the source data of your Pivot table, you get a very good benefit: your data range becomes “dynamic”. A dynamic range will automatically expand and shrink tables when you add or delete data, so you won’t have to worry that the pivot table is missing the latest data. When you use Excel Pivot table,it will always be in sync with your data
6. Use a pivot table to count things
Automatically, a pivot table will count any text field. This can be a very useful feature in many general business situations. For example, you have a list of employees and you want list which will count by department?
7. Show totals as a percentage
In many Excel pivot tables, you will want to show a percentage instead of a count. For example, maybe you want to show a categrazie of sales by product. But, instead of showing total sales for each product, you want to show sales as a percentage of total sales. Assuming your data has a field called Sales.
8. Use a pivot table to build a list of unique values
Because pivot tables summarize data, they can be used to find unique values in an area. This is a good way to quickly see all the values that appear in a field and also detect typos, and other inconsistencies.
9.Create a self-contained pivot table
When you have created a pivot table from the data in the same worksheet, you can extract the data if you want and the pivot table will continue to function normally. This is because a pivot table contains a pivot cache that contains an exact duplicate of the data used to create the pivot table.
10. Group a pivot table manually
Although Excel pivot tables automatically group data in many ways, you can also group items into their own custom groups. For example, suppose you have an axis table that shows the breakdown of employees by department. Suppose you want to group engineering, fulfillment and support departments in group 1 and group sales and marketing further.
CBitss Technologies provide an advance excel training in Chandigarh where we get in depth knowledge about Excel Pivot Table that will definatly help you to make your Excel skills better.