How to build a pivot table in Excel. How to work with data
MS Excel Pivot Table is a powerful data analysis tool. This tool allows you to extract information from a large array of data, in any context, with just a few clicks of the mouse. In this article I will try to explain in simple and understandable language, step by step, what and how to do in order to get this extremely convenient MS Excel tool at your disposal.
Requirements for source data.
So, the basis of any pivot table is a correctly constructed array of data - the “correct table”. In the figure below you can see an example of a correctly formed data array:
Let's look at what, in fact, is the “correctness” of this table? The correctness is that:
- Each column contains only the same type of data, the column A only Dates, in column IN only Documents, in the column WITH only Customers, Money in Money, Suppliers in Suppliers, Product Categories in Categories and so on... In such a table we can very easily use Filter;
- Columns with the same type of data are not repeated;
- There are no total rows in the table, only “clean data”;
- There are no empty cells in the text data in the table; each line contains the name of the Client, the Product, the Supplier, the Manager, and so on...
By the way, in an array, it does not matter at all in what sequence the columns are located and by what field the data is sorted. This does not affect the construction of the Pivot Table in any way.
And here is an example of a “wrong table”, from which, even if you crack, the Pivot Table will not be built, and even if it is built, it will be completely impossible to work with the data... Our “favorite” accounting systems usually “please” us with such tables, which give us them in the form of reports that are completely unsuited for further analysis:
Here is another example of "Invalid Array":
There's a whole bunch of "irregularities" here:
- Firstly, the “Supplier” and “Category” columns have empty cells, so we cannot use the filter;
- Secondly, in almost every column there are lines “Total...”, they are completely unnecessary for building the Pivot Table, moreover, they will only get in the way;
- Thirdly, the "Grand Total" column is also not needed;
- Fourthly, the same type of data, namely “Money”, is in as many as three columns: “Jan”, “Feb” and “Mar”, which will significantly complicate the construction of the Pivot Table, which means we will have to do something with it ...
But this is all for a separate conversation, if you want to learn how to quickly convert such “crooked tables” into “correct arrays”, read the articles: “How to quickly build a pivot table from a 1C or SAP report?” and “How to quickly convert a table into an array for a pivot tables? "
Actually, building the Pivot Table:
Take the “correct array”, place the cursor in any cell of the array, select the “Insert” tab in the main menu, in the left corner, in the “Tables” section, click the “Pivot Table” button:
In the "Create Pivot Table" dialog box that opens, click "OK":
MS Excel will create a new sheet on which it will indicate the place where the Pivot Table will be inserted, and on the right it will display a window for setting up Pivot Table fields, in which you will see all the names of the columns of your array:
Let's start setting up the Pivot Table. What kind of cut do we want? Product categories by Manager - please. Move the mouse over the “Category” field, click on it with the left mouse button and drag it to the “ROWS” field. The “Manager” field is dragged into “COLUMNS”, and the “Amount” is dragged into “VALUES”:
Pivot tables are one of the most effective tools in MS Excel. With their help, you can transform a million rows of data into a concise report in seconds. In addition to quickly summarizing results, pivot tables allow you to literally change the way you analyze on the fly by dragging fields from one area of the report to another.
Pivot tables are also one of the most underrated Excel tools. Most users are unaware of the possibilities that are in their hands. Let's imagine that pivot tables haven't been invented yet. You work for a company that sells its products to various customers. For simplicity, there are only 4 items in the assortment. The products are regularly purchased by a couple of dozen clients located in different regions. Each transaction is entered into the database and represents a separate line.
Your director instructs you to make a brief report on the sales of all goods by region (region). The problem can be solved as follows.
First, let's create a table layout, that is, a header consisting of unique values for products and regions. Let's make a copy of the product column and remove duplicates. Then, using insert special, we transpose the column into a row. We do the same with regions, only without transposing. We will receive the header of the report.
This plate must be filled out, i.e. summarize the revenue for the relevant products and regions. This is easy to do using the SUMIFS function. Let's also add the results. You will get a summary report on sales by region and product.
You have completed the task and show the report to the director. Looking at the table, he generates several great ideas at once.
— Is it possible to make a report not on revenue, but on profit?
— Is it possible to show products by rows and regions by columns?
— Is it possible to create such tables for each manager separately?
Even if you are an experienced Excel user, creating new reports will take a lot of time. This is not to mention possible errors. However, if you know pivot tables, then the answer is: yes, I need 5 minutes, maybe less.
Here's how it's done. Open the source data. A pivot table can be built using a regular range, but it would be more correct to transform it. This will immediately solve the issue of automatically capturing new data. Select any cell and go to the tab Insert. On the left side of the ribbon there are two buttons: and Recommended PivotTables.
If you don’t know how to organize the existing data, you can use the command Recommended PivotTables. Excel will show thumbnails of possible layouts based on your data.
Click on the appropriate option and the pivot table is ready. All that remains is to bring it to mind, because... It is unlikely that a standard preparation will completely coincide with your desires. If you need to build a pivot table from scratch, or you have an old version of the program, then click the button. A window will appear where you need to specify the source range (if you activate any Excel table cell, it will be determined by itself) and the location of the future pivot table (a new sheet will be selected by default).
Very often there is no need to change anything here. After clicking OK, a new Excel sheet will be created with a blank PivotTable layout.
Before moving on to the settings, let's get acquainted with the interface and basic concepts. Table layout is customizable in the panel Pivot table fields, which is located on the right side of the sheet.
At the top of the panel there is a list of all available fields, that is, columns in the source data. If you need to add a new field to the layout, you can check the box next to it - Excel itself will determine where this field should be placed. However, it is not always correct, so it is better to drag the mouse to the desired place on the layout. Fields can also be deleted by unchecking or dragging them back.
The pivot table consists of 4 areas, which are located at the bottom of the panel: values, rows, columns, filters. Let's take a closer look at their purpose.
Range of values– this is the central part of the pivot table with values that are obtained by aggregating the source data using the selected method.
In most cases, aggregation occurs by Summation. If all the data in the selected field is in numeric format, Excel will assign a default sum. If the source data contains at least one text or empty cell, then instead of the sum it will be calculated Quantity cells. In our example, each cell is the sum of all corresponding products in the corresponding region.
You can use other calculation methods in PivotTable cells. There are about 20 types (average, minimum value, proportion, etc.). There are several ways to change the calculation method. The simplest is to right-click on any cell of the desired field in the pivot table itself and select a different aggregation method.
Line area– the names of the lines that are located in the leftmost column. These are all the unique values of the selected field (column). There can be several fields in the row area, then the table becomes multi-level. Qualitative variables such as product names, months, regions, etc. are usually placed here.
Columns area– similar to rows, shows the unique values of the selected field, only in columns. Column titles are also usually a qualitative indicator. For example, years and months, product groups.
Filter area– used, as the name suggests, for filtering. For example, the report itself shows products by region. You need to limit your pivot table to a specific industry, period, or manager. Then a filter field is placed in the filter area and the desired value is selected from the drop-down list.
By adding and removing fields to specified areas, you can customize any slice of your data you want in seconds.
Let's see how this works in action. For now, let's create the same table as was already created using the SUMIFS function. To do this, drag into the area Values field "Revenue", in the area Strings drag the “Region” field (sales region) to Columns- "Product".
As a result, we get a real pivot table.
It literally took 5-10 seconds to build it. Modifying an existing pivot table is also easy. Let's see how the director's wishes are easily translated into reality.
Let's replace revenue with profit.
Products and areas can also be swapped by dragging the mouse.
There are several tools available for filtering pivot tables. In this case, we’ll simply place the “Manager” field in the filter area.
Everything took a few seconds. Here's how easy it really is to work with Pivot Tables. Of course, not all tasks are so trivial. There are also cases where it is necessary to use a more sophisticated aggregation method, adding calculated fields, conditional formatting, etc. But this is a more advanced use of pivot tables.
Initial data
To successfully work with pivot tables, the source data must meet a number of requirements. A prerequisite is the presence of names above each field (column) by which these fields will be identified. Now some useful tips.
1. The best format for data is an Excel Spreadsheet. It is good because each field has a name and when new rows are added, they are automatically included in the pivot table.
2. Avoid repeating groups in columns. For example, all dates should be in one field, and not divided, for example, by month in separate columns.
3. Remove gaps and empty cells, otherwise this row may fall out of the analysis.
4. Apply proper formatting to fields. Numbers must be in numeric format, dates must be dates. Otherwise, problems will arise during grouping and mathematical processing. But Excel will help you here, because... It determines the data format quite well.
In general, there are few requirements, but you should know them.
If you make changes to the source (for example, adding new rows), the pivot table will not change until you refresh it using the right mouse button
or
via the command in the tab Data – Refresh All.
This was done on purpose because the pivot table takes up a lot of space in RAM. Therefore, in order to use computer resources more economically, work is done not directly with the source, but with the cache, where a snapshot of the source data is located.
Using pivot tables even at this basic level, you can significantly increase the speed and quality of processing large amounts of data. If anything remains unclear, write in the comments.
Pivot tables is one of the most powerful Excel tools. They allow you to analyze and summarize various results for large volumes of data with just a few clicks. In this article we will get acquainted with pivot tables, understand what they are, learn how to create and configure them.
When writing this article, I used Excel 2010. The concept of pivot tables has remained largely unchanged for many years, but the way they are created is slightly different in each new version of Excel. If you have a version of Excel other than 2010, then be prepared that the screenshots in this article will differ from what you see on your screen.
A little history
At the dawn of the development of programs for creating spreadsheets, the rules of Lotus were 1-2-3. Its dominance was so complete that Microsoft's efforts to develop its own software (Excel) as an alternative to Lotus seemed a waste of time. Now fast forward to 2010! Excel dominates the spreadsheet industry more than Lotus has ever done in its history, and the number of people still using Lotus is approaching zero. How could this happen? What was the reason for such a dramatic turn of events?
Analysts identify two main factors:
- First, Lotus decided that this newfangled GUI platform called Windows was just a passing fad that would not last long. They refused to create a Windows version of Lotus 1-2-3 (but only for a few years), predicting that the DOS version of their software was all that consumers would ever need. Microsoft, of course, developed Excel specifically for Windows.
- Secondly, Microsoft developed a tool in Excel called pivot tables, which was not available in Lotus 1-2-3. Pivot tables, an Excel exclusive, were so incredibly useful that people were inclined to learn the new Excel software rather than stick with Lotus 1-2-3, which didn't have them.
Pivot tables, coupled with the underestimation of the success of Windows in general, played the death march for Lotus 1-2-3 and marked the beginning of the success of Microsoft Excel.
What are Pivot Tables?
So what's the best way to describe what pivot tables are?
In simple terms, pivot tables are summaries of some data, created to facilitate the analysis of this data. Unlike manually created totals, Excel PivotTables are interactive. Once created, you can easily change them if they don't give the picture you were hoping to get. With just a couple of clicks, totals can be flipped so that column headings become row headings and vice versa. There are many different things you can do with pivot tables. Instead of trying to describe in words all the capabilities of pivot tables, it is easier to demonstrate it in practice...
The data you analyze with PivotTables can't be any kind of data. This should be raw data, like a list of some kind. For example, this could be a list of completed sales in the company over the past six months.
Look at the data shown in the figure below:
Please note that these are not raw data as they have already been summarized. In cell B3 we see $30,000, which is likely the total that James Cook made in January. Where then is the source data? Where did the $30,000 figure come from? Where is the original sales list from which this monthly total was derived? It's clear that someone has gone to great lengths to organize and sort all the sales data for the last six months and turn it into the summary table we see. How long do you think it took? Hour? Ten o'clock?
The fact is that the table above is not a summary table. It was created manually from raw data stored elsewhere and took at least a couple of hours to process. This is exactly the kind of summary table that can be created using pivot tables, spending just a few seconds on it. Let's figure out how...
If we go back to the original sales list, it would look something like this:
You may be surprised that from this list of trading transactions, using pivot tables and in just a few seconds, we can create a monthly sales report in Excel, which we discussed above. Yes, we can do this and much more!
How to create a pivot table?
First, make sure that you have some source data on an Excel sheet. The list of financial transactions is the most typical thing that is found. In fact, it could be a list of anything: employee contact information, a collection of CDs, or your company's fuel consumption data.
So, let's launch Excel... and load this list...
Once we have opened this list in Excel, we can start creating a pivot table.
Select any cell from this list:
Then on the tab Insert(Insert) select command PivotTable(Summary table):
A dialog box will appear Create PivotTable(Creating a PivotTable) with two questions for you:
- What data should I use to create a new pivot table?
- Where should I place the pivot table?
Since in the previous step we have already selected one of the list cells, the entire list will be selected automatically to create a pivot table. Note that we can select a different range, a different table, or even some external data source, such as an Access or MS-SQL database table. In addition, we need to choose where to place the new pivot table: on a new sheet or on one of the existing ones. In this example, we will choose the option - New Worksheet(On a new sheet):
Excel will create a new worksheet and place an empty pivot table on it:
As soon as we click on any cell in the pivot table, another dialog box will appear: PivotTable Field List(Pivot table fields).
The list of fields at the top of the dialog box is a list of all the titles in the original list. The four blank areas at the bottom of the screen let you tell the PivotTable how you want to summarize the data. While these areas are empty, there is nothing in the table either. All we have to do is drag the headings from the top area to the empty areas below. In this case, a pivot table is automatically generated in accordance with our instructions. If we made a mistake, we can remove the headings from the bottom area or drag others to replace them.
Region Values(Meanings) is probably the most important of the four. What heading is placed in this area determines what data will be summarized (sum, average, maximum, minimum, etc.) These are almost always numerical values. An excellent candidate for a position in this area - data under the heading Amount(Cost) of our original table. Let's drag this title to the area Values(Values):
Please note that the title Amount is now marked with a check mark, and in the area Values(Values) entry appeared Sum of Amount(Sum by Amount field), indicating that the column Amount summed up
If we look at the pivot table itself, we will see the sum of all the values from the column Amount original table.
So, our first pivot table is created! Convenient, but not particularly impressive. We probably want more information about our data than we currently have.
Let's look at the source data and try to identify one or more columns that can be used to break down this sum. For example, we can create our pivot table in such a way that the total sales amount is calculated for each seller separately. Those. Rows will be added to our pivot table with the name of each company salesperson and his total sales amount. To achieve this result, just drag the title Salesperson Row Labels(Lines):
It's getting more interesting! Our pivot table is starting to take shape...
Do you see the benefits? In a couple of clicks, we created a table that would have taken a very long time to create manually.
What else can we do? Well, in a sense, our pivot table is already ready. We've created a helpful summary of the raw data. Important information has already been received! In the rest of this article, we'll look at some ways to create more complex pivot tables and learn how to customize them.
Setting up a pivot table
First, we can create a two-dimensional pivot table. Let's do this using the column header Payment Method(Payment method). Just drag the title Payment Method to the region Column Labels(Columns):
We get the result:
Looks very cool!
Now let's make a three-dimensional table. What might such a table look like? Let's get a look…
Drag title Package(Complex) to the area Report Filter(Filters):
Notice where he ended up...
This gives us the opportunity to filter the report by the attribute “Which holiday complex was paid for”. For example, we can see a breakdown by sellers and payment methods for all complexes, or in a couple of clicks we can change the view of the summary table and show the same breakdown only for those who ordered the complex Sunseekers.
So, if you understand this correctly, then our pivot table can be called three-dimensional. Let's continue to configure...
If it suddenly turns out that only payments by check and credit card (that is, bank transfer) should be displayed in the pivot table, then we can disable the header display Cash(Cash). To do this next to Column Labels click the down arrow and in the drop-down menu uncheck the item Cash:
Let's see what our pivot table looks like now. As you can see, the column Cash disappeared from her.
Formatting Pivot Tables in Excel
Obviously, PivotTables are a very powerful tool, but so far the results look a bit plain and boring. For example, the numbers we add up don't look like dollar amounts—they're just numbers. Let's fix this.
It is tempting to do the usual actions in such a situation and simply select the entire table (or entire sheet) and use the standard number formatting buttons on the toolbar to set the desired format. The problem with this approach is that if you ever change the structure of the pivot table in the future (and there is a 99% chance of this happening), the formatting will be lost. We need a way to make it (almost) permanent.
First, let's find the entry Sum of Amount in area Values(Values) and click on it. In the menu that appears, select the item Value Field Settings(Value Field Options):
A dialog box will appear Value Field Settings(Value field parameters).
Click the button Number Format(Number Format), a dialog box appears Format Cells(Cell Format):
From the list Category(Number Formats) select Accounting(Financial) and set the number of decimal places to zero. Now press several times OK to go back to our pivot table.
As you can see, the numbers ended up being formatted as dollar amounts.
Now that we're done with formatting, let's set up the format for the entire pivot table. There are several ways to do this. Let's use the simpler one...
Open the tab PivotTable Tools: Design(Working with pivot tables: Designer):
Select any appropriate style and look at the result in your pivot table:
Other settings for pivot tables in Excel
Sometimes you have to filter data by dates. For example, our list of trade transactions contains many, many dates. Excel provides a tool to group data by day, month, year, etc. Let's see how it's done.
First, remove the entry. Payment Method from the region Column Labels(Columns). To do this, drag it back to the list of headings, and in its place, move the heading Date Booked(Booking date):
As you can see, this has temporarily rendered our pivot table useless. Excel created a separate column for each date on which a trade was made. As a result, we got a very wide table!
To fix this, right-click on any date and select from the context menu Group(Group):
The grouping dialog box appears. We choose Months(Months) and press OK:
Voila! This table is much more useful:
By the way, this table is almost identical to the one shown at the beginning of the article, where the sales results were compiled manually.
There is one more very important point that you need to know! You can create not one, but several levels of row (or column) headings:
...and it will look like this...
The same thing can be done with column headers (or even filters).
Let's return to the original table view and see how to display average values instead of sums.
To get started, click on Sum of Amount and from the menu that appears, select Value Field Settings(Value Field Options):
On the list Summarize value field by(Operation) in the dialog box Value Field Settings(Value Field Options) select Average(Average):
At the same time, while we're here, let's change Custom Name(Custom name) with Average of Amount(Quantity by Amount field) to something shorter. Enter something like this in this field Avg:
Click OK and look what happened. Notice that all values have changed from totals to averages, and the table title (in the top left cell) has changed to Avg:
If you want, you can immediately get the sum, average and quantity (sales) placed in one pivot table.
Here's a step-by-step guide on how to do this, starting with an empty pivot table:
- Drag title Salesperson(Sales Representative) to the region Column Labels(Columns).
- Drag the title three times Amount(Cost) to area Values(Values).
- For the first field Amount change the name to Total(Amount), and the number format in this field is Accounting(Financial). The number of decimal places is zero.
- Second field Amount name Averag e, set the operation for it Average(Average) and change the number format in this field to Accounting(Financial) with the number of decimal places equal to zero.
- For the third field Amount set title Count and an operation for him - Count(Quantity)
- In area Column Labels(Columns) field automatically created ΣValues(Σ Values) – drag it to the area Row Labels(Strings)
This is what we get in the end:
Total, average and number of sales - all in one pivot table!
Conclusion
Pivot tables in Microsoft Excel contain many, many functions and settings. Such a short article cannot even come close to covering them all. To fully describe all the capabilities of pivot tables would require a small book or a large website. Brave and curious readers can continue to explore pivot tables. To do this, just right-click on almost any element of the pivot table and see what functions and settings open. On the Ribbon you will find two tabs: PivotTable Tools: Options(Analysis) and Design(Constructor). Don't be afraid to make a mistake; you can always delete the pivot table and start over. You have an opportunity that longtime DOS and Lotus 1-2-3 users never had.
Pivot tables in Excel allow you to analyze data that is in one large range. Moreover, the source table that will be used for analysis can be created either in Excel or in another database or other document.
All you need to do is select the necessary headers for rows and columns from the existing ones. You can also apply a filter to the entire pivot table as a whole, or only to the values that are indicated in the names of the rows and columns.
Now about the requirements that must be followed when creating it in Excel. The source data must be presented in the form of a table, which must have a title for the columns, that is, a header. For these purposes, creating a smart table in Excel is perfect. It should also not contain empty rows, columns or cells. There should be no hidden rows/columns or merged cells.
How to create
Now let's look at an example of creating a pivot table. Suppose we have data on sales of a clothing store: which seller, what number, what items he sold and for what amount.
To make a pivot table, select any cell from the original one, then go to the “Insert” tab and click on the button "Pivot table".
The following dialog box appears. In it you need to indicate the range or name of the table with the source data, and here you can select it from another source. Then mark with a marker where to place the report to be created. Let's do it on a new sheet. Click OK.
A new sheet will be created in the open Excel workbook, on which the still empty pivot table will be placed.
A list of fields and areas will appear on the right side. The fields are all the column headers that were in the original range. Using the mouse, we will drag them into one of the four areas below. Thus, forming a pivot table.
The added fields will be marked with a check mark. In the table areas, you can change their places to achieve the look that is most suitable for analyzing your source data.
How to arrange fields
Now let's decide on what principle we will analyze the data. For example, you need to find out which seller sold which product in each month and for what amount.
We will filter the data according to the selected range by sellers. That is, we select a seller, and the table will display the goods sold by him. Left-click the “Vendor” field and drag it to the “Report Filter” area. The table has changed and the added field is now marked with a check mark.
For the lines, select “Products”. Similarly, drag the desired field into the area "Line titles".
Please note that you can insert multiple fields in an area. For example, in the lines we will select products and indicate the price. Products have become drop-down lists that display the price. If you first specify the price and then the products, the drop-down list will become price. Here the order of the fields matters.
If we had a column “Units” in the original table. Then this summary table would show which seller, in which month, how many units of goods he sold at a certain price.
To the region "Column titles" Drag the “Date” field. To display sales not for every day, but, for example, by month, right-click on any date and select “Group” from the menu.
The table will take the following form.
Now drag the “Amount” field into the “Values” area.
As you can see, just numbers were displayed, although in the original range the numeric format of the cells for this column was set; it can also be monetary or financial. In the “Date” column, the cell format was also appropriate – date.
To fix this, select the desired range of cells in the summary and press the right mouse button. then select from the menu "Number Format".
In the next window, select “Numerical”, you can check the box "Bit group separator" and click "OK".
How to work with data
After we have taken into account all the necessary fields, we can start working with the pivot table in Excel. Selecting a seller: you can select one, several or all at once by checking the box "Select multiple items".
You can also use a filter for rows and columns. In the example, these are products and months. For example, by checking the boxes for Suit and Pants, you can find out how much they were sold for by all sellers or by a specific seller.
In the Values area, you can configure settings for the field. The example displays the sum of the values: Roma sold shirts in the amount of 1,800.00 in February. let's see how many pieces it is. Left-click on the line "Sum by field..." and select from the menu "Value Field Options".
In the next window, select “Quantity” from the list, select what is suitable specifically for your case, and click “OK”.
Now, looking at the values, we can understand that Roma sold two shirts in February.
Now let's make the general filter for the table by month. We change the areas: in the “Report Filter” we drag the “Date” field, into "Column titles"- "Salesman" .
Such a summary table will show how much and what kind of goods each seller sold for the entire period or for a specific month.
Also pay attention to the tape. When you select any cells from a pivot table, a tab appears on it "Working with Pivot Tables" with two subtabs “Parameters” and “Designer”.
The problem we all face is not a lack of data; on the contrary - these are huge amounts of data! This is why I advocate using a great Excel function - Pivot tables to summarize and analyze your data.
Seeing the power of PivotTables is as simple as comparing the original data to a version of the PivotTable; With just a few clicks, I was able to see the average price of an item by state.
As a finance professional, I am genetically inclined to love spreadsheets. But I've also found that I use spreadsheets to organize my creative and freelance work. No matter what you use spreadsheets for, a pivot table can help you make more sense from your data.
We will build this lesson according to ours, for better work with data. I'll show you five of my favorite pivot table best practices.
Throughout this tutorial I will be using the sample data provided by Microsoft on this page. Use this data to recreate the examples or test the functions I demonstrate.
How to Use Advanced PivotTable Techniques in Excel (Short Video)
I like to teach using screencasts, which give you the opportunity to watch me use features step-by-step. Check out the short video below that covers five of my favorite advanced Excel PivotTable features:
5 Advanced Pivot Table Tricks in Excel
Continue reading to learn how to use each of these five features in the lesson below, including: Slicers, Timeline, Table View, Calculated Fields, and Featured Pivot Tables. Let's get started.
1. Sections
Slices- A point-and-click tool to refine the data included in an Excel pivot table. Insert a slicer and you can easily change the data included in the pivot table.
In this example I have inserted a slice for type Item. After I click on Backpack, the pivot table shows only this parameter in the table.Many times I develop dashboard reports that will be used by others. Adding slicers can help the end user customize the report to their liking.
To add a slicer, click in the pivot table and find the tab Analysis on the Excel ribbon.
Check multiple boxes to include these columns, each of which will be a slice.Hold Ctrl on the keyboard to select multiple slicer items that will include multiple selections from a column as PivotTable data.
2. Timeline
Timelines This is a special type of slicer used to adjust the dates included as part of the PivotTable data. If your data contains dates, you really need to try Timeline as a way to select data from specific time periods.
Go to section Analyze > Insert Timeline to add a timeline, a special type of slicer that manages the data included in an advanced Excel PivotTable based on date.Tip: If this feature doesn't work for you, make sure your source data has a date formatted in the spreadsheet as a date.
To add a timeline, make sure you select the PivotTable (click inside it) and then click the button Analyze > Insert Timeline on the Excel ribbon. In the pop-up window, select a date column (or multiple columns) and click OK to create a timeline.
Click and drag in the Timeline window to select a specific time range for the PivotTable.Once the timeline is inserted, you can click and move the handles on the timeline to change what is included in the pivot table.
You can change the way the timeline works by clicking the drop-down list in the bottom right corner. You can change the timeline to show quarterly or yearly data instead of specific dates, for example.
3. Tabular view
The standard view of PivotTables in Excel looks like a waterfall; As you drag more fields onto rows, Excel creates more “layers” in the data.
The problem is that it is difficult to fit formulas into PivotTables in Standard View. If you have data in a PivotTable, but want to view it as a regular spreadsheet, you must use a table view for the PivotTable.
Select Designer > Report Layout > Show in Tabular Form to work with your pivot table as a regular table.Why should you use a table view? Bringing your PivotTable data into a classic tabular style will allow you to more easily enter data formulas or insert them into a separate report.
Use Table View to make your PivotTable look more like standard rows and columns.Most of the time, I prefer to use Excel's table view. It looks more like a standard spreadsheet and is easier to write formulas and work with data in it. I might as well take this view and paste it into a new tab.
4. Calculated fields
Calculated fields are a way to add a column to a PivotTable that is not in the source data. You can use standard math operations to create entirely new fields to work with. Take two existing columns and use math to create completely new ones.
Let's say we have sales data in a spreadsheet. We have the number of items sold and the selling price for each item. This is the ideal time to use a calculated field to calculate the order total.
To get started working with calculated fields, start by clicking inside the PivotTable and then find on the Ribbon Analysis. Click menu Fields, elements and sets and then select Calculated field.
Use Analysis > Fields, Members, and Sets > Calculated Field to insert the calculated field into your PivotTable.In the new pop-up window, start by giving the calculated field a name. in my case I'll call it Total Order. The total order value is the quantity multiplied by the price of each unit. Then I double-clicked on the first title field (quantity) in the list of fields in this window.
To calculate the total order cost, I multiplied the existing unit price field ( Unit Price) on the quantity field ( Quantity).After adding the name of this field, I will add a multiplication sign * , and then double-click on the total quantity (quantity). Let's go ahead and click OK.
Now Excel has updated my extended PivotTable and added a new calculated field. You'll also see a list of PivotTables in the Field List so you can drag them anywhere in the report whenever you need it.
If you don't want to use two-column arithmetic, you can also enter your own arithmetic values in the calculated field. For example, if I wanted to simply add 5% sales tax for each order, I could write the following in the calculated field:
I multiplied the total order cost by 1.05 to calculate the cost including sales tax; you can use numeric values along with existing fields.In principle, calculated fields can contain any standard mathematical operators such as addition, subtraction, multiplication, and division. Use these calculated fields if you don't want to update the original data.
Go to Insert > to try this feature.This feature is so easy to use that it goes without saying. You can use it to quickly create advanced pivot tables in Excel. Just highlight your data, go to the tab Insert on the Excel ribbon and select Recommended PivotTables.
The pop-up window contains many options for creating a pivot table from the source data. Click the thumbnails on the left side of this window to view the recommended PivotTable options provided by Excel.
The PivotTable Recommendation feature offers many options to analyze your data in one click.Even though this is an advanced feature that few users are aware of, it is also a great tool for running pivot tables. There's nothing stopping you from modifying the PivotTable by replacing the fields yourself, but it's a good starting point.
Also, I like this feature as a data exploration tool. If I don't know what I'm looking for when I start exploring the data, the recommended Excel Pivot Tables are often more insightful than I am!
Repeat and keep learning (with even more Excel lessons)
This advanced Excel lesson helped you dive deeper into PivotTables, one of my favorite features for analyzing and viewing an Excel spreadsheet. I use pivot tables to find meaning in large data sets so I can make better decisions and take action.
These lessons will take your skills with Excel and Pivot Tables to the next level. Check them out:
- ExcelZoo has a great overview of Pivot Table techniques in their article, 10 Lessons for Mastering Pivot Tables (in English).
- We at Envato Tuts+ covered pivot tables with a beginner's tutorial.
- For an easier introduction to Microsoft Excel, check out our tutorial series.
What do you still want to know about pivot tables? Let me know your ideas or questions in the comments below this tutorial.
