Pivot tables are THE indispensable skill you need to know to master Excel. They are the workhorses of Excel. You have , rows of data by invoice, product. Editorial Reviews. About the Author. Bill Jelen, Excel MVP and the host of medical-site.info, has medical-site.info: Microsoft Excel Pivot Table Data Crunching (Business Skills) eBook: Bill Jelen, Michael Alexander: Kindle Store. Download this FREE eBook and become an analytics Super Star in one hour! Pivot Tables are an absolutely essential tool for anyone working with data in Excel.
|Language:||English, Spanish, Hindi|
|Distribution:||Free* [*Register to download]|
Pivot Tables in Excel are one of the most powerful features within Microsoft Excel. A Pivot Table allows you to analyze more than 1 million rows of data with just a. Home eBooks Excel PivotTables and PivotCharts eBook. Excel PivotTables A PivotTable can help you extract more meaningful information from your data. Learn how to use one of the most powerful tools in Excel, pivot tables, to quickly summarize data to Conditional Formatting for Pivot Tables Video Thumb
Jekyll and Mr.
If you find an error, you can report it to us through our Submit errata page. Sign in. Your cart. Book List price: This eBook includes the following formats, accessible from your Account page after download: Downloads Follow the instructions to download this book's companion files. Click the Download button below to start the download. If prompted, click Save.
Locate the. Right-click the file, click Extract All, and then follow the instructions.
Features By reading this book, students will: The following screencast animations shows how quickly a summary report can be created from a table list of data using a pivot table. Each row in the data set contains sales data for a product sold to a customer. You can download this workbook to follow along. How to Insert a Pivot Table The first step is to insert a pivot table into your workbook. Typically you will want to insert your pivot table on a new worksheet.
After you create the pivot table you will see a list of fields in the task pane on the right side of the screen.
These fields are the columns in your data set. The Pivot Table Areas The pivot table contains four areas that you can drag the fields into to create a report. Filters area Rows area Values area The following diagram shows where each area of the pivot table is located on the report.
This can be used as a guide to familiarize yourself with the different areas. We are going to create this quarterly sales report by region as an example. Some magic happens when a field is placed in an area. That magic is different for each area of the pivot table. Here is another link to download the sample file. How Pivot Tables Work. When you drag a field into the Rows area of the pivot table, all the unique values in that field will be displayed in the first column of the pivot.
The pivot table removes all the duplicates in the field column of source data and only displays the unique values. In my sample data set you can see the regions are listed more than once because we made sales in each region multiple times throughout the years.
When I place the Region field in the rows area, each region is only listed one time in the first column of the pivot table. Again, the pivot table automatically removes the duplicates and only displays the unique values. The layout of the source data is extremely important for this to work, and I will explain more about this below. The Values Area The Values area displays the data values that we want to summarize in our pivot table report.
When you drag a field into the Values area, the pivot table will automatically sum or count the data in that field.
If the data in the field contains numbers, then the sum will be calculated. If the data contains text or blanks, then the count will be calculated. The calculation type can be changed later to other functions like Average, Max, Min, etc.
In this example I will place the Revenue field in the Values area. This field contains numbers sales dollars and the pivot table will automatically sum the revenue for each region that is listed in the Rows area. The pivot table performs its magic by filtering and calculating the data for each cell in the values area. This is a really important concept to learn.
In the image above, the pivot table shows 5, in cell K4. This is the Sum of Revenue for the Northeast region.
How did the pivot table calculate this number? We can break it out in two steps: First, the pivot table filters the source data for the criteria in the Fields, Columns, and Rows areas.
Next, the pivot table calculates the Sum of the Revenue column. This process is then repeated for each cell in the values area of the pivot table. The source data table is not actually filtered on the sheet when you add a field into the Values area. However, understanding the concept of the calculation will also help you understand why the structure of the source data is so important. Tabular Data Structure — Getting the Source Data Right Since we now know that a pivot table uses filters to calculate the results in the Values area, it is critical that the data is structured in a way that can be filtered.
Pivot tables require your source data to be in a Tabular layout format. Tabular means that the source data can only have one row of headers descriptive names for each column , and rows of data below the header. However, there is more to it… Unique Field Characteristics Each header column name in the source data must only describe ONE characteristic of the data.
The following image shows data that could be considered tabular because it is a table of data with one row of headers. However, this is NOT the best layout for source data of a pivot table because the column headers describe more than ONE characteristic of the data in the column. For example, column D contains revenue for January.
This means the column contains two characteristics: the revenue amount and the month January. When the source data is structured like this, it makes it impossible for the pivot table to calculate the total revenue for a region. The revenue is divided up into columns by month and you cannot calculate the sum of one column to get total revenue. Instead, the pivot table requires that there only be one column for revenue. This will allow you to create a report that displays total revenue by month, quarter, product, region, or whatever way you want.
Therefore, each column should only contain one characteristic that describes the data field Revenue. To fix this we would need to create separate Revenue and Month columns. The data table should then look like the following.
This is called unpivoting the data. It means the number of rows will grow, and the table will get much longer. But this is ok!
Remember that the job of the pivot table is to Filter and Calculate. It can only filter columns in a vertical manner, so all the revenue numbers need to be in a single column. This gives you a lot more flexibility when you add the Revenue field to the Values area of the pivot.
With this understanding of how the data should be formatted, you will begin to see the power of the pivot table. The Columns area works just like the Rows area. It lists the unique values of a field in the pivot table. The only difference is that it lists the values across the top row of the pivot table.
The image above shows what the pivot table will look like if you put the Region field in the Columns area versus the Rows area. When the Region is in the Columns area, the values Sum of Revenue are placed horizontally on the sheet.
The Sum of Revenue calculation works the same as before.