This article explains source data formats for a Pivot Table, each section of the Pivot Table’s work screen – the Field List – and demonstrates the elemental steps needed to make a Pivot Table on the Excel screen. You’ll be able to start making your own Pivot Table after reading this article.
＜＜ Related Posts ＞＞
- Excel: How to Sum Data by Week, Month and Year【Data Analysis Excel Skill】
- How to learn Excel functions, and a look at Excel functions: SUM, SUMIF and SUMIFS【Excel Skills】
You can make a Pivot Table without using any Excel functions.
Hi, this is Mike Negami, Lean Sigma, Black Belt.
I made a video about date aggregation using Excel’s SUMIFS Function earlier. After that I got this comment.
“Why don’t you recommend Pivot Table?”
Thanks, Kai’s Mobile for your comment. As he suggested, I’ll recommend that you try out Excel’s Pivot Table. The Pivot Table is one of the most powerful Excel features, especially when you need to make an analysis in a short time.
The image below is the result of the previous date aggregation. You can make the same thing with a Pivot Table without using any Excel functions. We’ll learn how to use a Pivot Table through making this same aggregation with it.
Preparation before making a Pivot Table
Let’s start with the same source data (See the image below). Each column is called a field and has a field name in the top cell. As with a Pivot Table, you should always use this simply formatted data for any data analysis.
After preparing the source data, select a range of the data with your mouse. Make sure the field names are at the top of your selected range. Then click ‘Insert’ and ‘Pivot Table’. You’ll see this small screen as shown below. You can make different settings here, but you can also just click ‘OK’ so that a Pivot Table will be made in a new worksheet.
Since I would like to show my source data and Pivot Table side by side, I’ll select ‘Existing Worksheet’ (See the image above), click a cell next to the Pivot Table and click ‘OK’. Then the Pivot Table work screen will appear, which is called a ‘Field List’ (See the image below).
By the way, I’m using Excel 2016. Please be aware that layouts and functions may be different depending on your Excel version.
Explanation of each area of the Field List
First, let’s understand the meaning of each section. The upper half represents the source data, and the field names from it are listed. Therefore, if even one field name in your source data was blank, an error message would appear and not let you proceed forward.
The lower half shows the structure of the target result you would like to have. Let’s compare the two. The leftmost column in the target result are rows, the upper part are columns. Rows and columns are crossed, so this formatted analysis is called a cross aggregation.
These rows are represented by the lower left box in the Field List, which is ‘Rows’ area. Since you want to show ‘Sales Channel’ in the rows, drag and drop the field name from above. (See the image below.)
The section that contained the SUMIFS Function last time is represented by the lower right box in the Field List, which is the ‘Values’ area. Since this is ‘Sales Amount’, drag it from above and drop it in the box. (See the image below.)
In most cases however, the count of sales data appears first. We would like to have the ‘Sum’ here. Right-click on one of the value cells and in the ‘Summarize Values By’, choose ‘Sum’. Then, the values all change to sums.
Our target result is weekly sales, and each column is ‘Sales Date’ by week. In the Field List, it’s represented in the ‘Columns’ area on the upper right. Drag ‘Sales Date’ from the top and drop it there.
Since there are many dates, Excel has aggregated dates to annual sales automatically, but what we want are the past six weeks’ weekly sales. Here is an icon that looks like an auto filter. When clicking on it you’ll see ‘Date Filters’. From there, select ‘After…’ and click the calendar icon, then narrow it to the last 6 weeks.
Since it’s still an annual aggregation, I want to make it weekly. Right-click on any date and select ‘Group…’. I’ll select ‘Days’ and put ‘7’ since I need weekly, then click ‘OK’. (See the image below.) Although its formats are different, the values are the exact same.
Pivot Table’s Weak Point: Limitation of changing layout or formats, ant solution for that.
The Pivot Table is a very powerful tool, but one issue I have is that it has some limitations with changing a layout or formats. So, my recommendation is to get calculations from the Pivot Table, then do layout and formatting in a separate table.
After checking the Pivot Table results, make a table manually with formats you like. Then copy the Pivot Table results, and right-click and select ‘Paste Options’ ‘1 2 3’ (Values) onto the table you just made. (See the image below.)
Although I didn’t show you today, you can put other fields in the remaining upper left Filter section and easily have a filter function.
After you get used to the Pivot Table, you can make quite complicated aggregations in a short time. Please try to master this powerful tool.