Cập nhật nội dung chi tiết về Power Pivot For Excel Tutorial: Top Use Cases And Examples mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.
Read the Spanish version of this article translated by Marisela Ordaz
Executive Summary
What Is Power Pivot?
Introduced to Excel 2010 and 2013 as an add-on, but now native to the application, Power Pivot is part of Microsoft’s business intelligence stack capable of (but not limited to) big data analytics work without specialty infrastructure or software.
According to Microsoft, “Power Pivot enables you to import millions of rows of data from multiple data sources into a single Excel workbook, create relationships between heterogeneous data, create calculated columns and measures using formulas, build PivotTables and PivotCharts, and further analyze the data so that you can make timely business decisions without requiring IT assistance.”
Power Pivot was created in direct response to the big data demands of contemporary business intelligence needs, which prior generations of Excel-given their 1,048,576 row limit or processing speed shortcomings-struggled to cope with.
Power Pivot is expressed by Microsoft using DAX (Data Analysis Expressions), which is a collection of functions, operators, and constants usable in a formula or expression to calculate/return one or more values.
What Are the Benefits to Power Pivot vs. Basic Excel?
Power Pivot lets you import and manipulate hundreds of millions of rows of data whereas Excel has a hard constraint of just over a million rows.
Power Pivot allows you to import data from multiple sources into one single source workbook without having to create multiple source sheets and deal with potential version control and transferability issues.
Power Pivot lets you manipulate the imported data, analyze it, and draw conclusions without slowing down your computer system, as is typical of Basic Excel.
Power Pivot lets you visualize and manipulate your big datasets with PivotCharts and Power BI, where basic Excel lacks these capabilities.
How Can a Finance Expert or Excel Consultant Help Your Business?
By working alongside you as a thought partner to design, structure, build, and deliver a range of financial models, budgets, and big-data analyses/projects, all en route to decisions around captive projects, mergers and acquisitions, or strategic investments.
By creating customized models unique to your business, using Power Pivot and other specialty excel functions.
By also creating prefabricated, go-to templates that can be adapted uniquely by almost anyone across your organization, for almost any purpose, using Power Pivot and other specialty Excel functions.
By actualizing each of these and more, alongside the design, creation, and delivery of a polished and professional PowerPoint presentation, ahead of strategic decisions.
Download the data set here to follow along with the tutorial.
The Emperor’s New Clothes: Power Pivot Tutorial
What Is Power Pivot and Why Is It Useful?
Power Pivot is a feature of Microsoft Excel that was introduced as an add-in to Excel 2010 and 2013, and is now a native feature for Excel 2016 and 365. As Microsoft explains, Power Pivot for Excel “enables you to import millions of rows of data from multiple data sources into a single Excel workbook, create relationships between heterogeneous data, create calculated columns and measures using formulas, build PivotTables and PivotCharts, and then further analyze the data so that you can make timely business decisions without requiring IT assistance.”
The primary expression language that Microsoft uses in Power Pivot is DAX (Data Analysis Expressions), although others can be used in specific situations. Again, as Microsoft explains, “DAX is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values. Stated more simply, DAX helps you create new information from data already in your model.” Fortunately for those already familiar with Excel, DAX formulas will look familiar, since many of the formulas have a similar syntax (e.g., SUM, AVERAGE, TRUNC).
For clarity, the key benefits of using Power Pivot vs. basic Excel can be summarized as the following:
It lets you import and manipulate hundreds of millions of rows of data where Excel has a hard constraint of just over a million rows.
It allows you to import data from multiple sources into one single source workbook without having to create multiple source sheets that suffer from version control and transferability issues.
It lets you manipulate the imported data, analyze it, and draw conclusions without slowing down your computer to a snail’s pace.
It lets you visualize the data with PivotCharts and Power BI.
In the following sections, I’ll run through each of the above and show you how Power Pivot for Excel can be helpful.
How to Use Power Pivot
1) Importing Large Datasets
As previously alluded to, one of the major limitations of Excel pertains to working with extremely large datasets. Fortunately for us, Excel can now load well over the one-million row limit directly into Power Pivot.
To demonstrate this, I generated a sample dataset of two years’ worth of sales for a sporting goods retailer with nine different product categories and four regions. The resulting dataset is two million rows.
Using the Data tab on the ribbon, I created a New Query from the CSV file (see Creating a New Query below). This functionality used to be called PowerQuery, but as of Excel 2016 and 365, was more tightly integrated into the Data tab of Excel.
From a blank workbook in Excel to loading all two million rows into Power Pivot, it took about one minute! Notice that I was able to perform some light data formatting by promoting the first row to become the column names. Over the past few years, the Power Query functionality has vastly improved from an Excel add-in to a tightly integrated part of the Data tab on the toolbar. Power Query can pivot, flatten, cleanse, and shape your data through its suite of options and its own language, M.
2) Importing Data from Multiple Sources
One of the other key benefits of Power Pivot for Excel is the ability to easily import data from multiple sources. Previously, many of us created multiple worksheets for our various data sources. Often, this process involved writing VBA code and copy/pasting from these disparate sources. Fortunately for us, though, Power Pivot allows you to import data from different data sources directly into Excel without having to run into the issues mentioned above.
Using the Query function in Exhibit 1, we can pull from any of the following sources:
Further, multiple data sources can be combined either in the Query function or in the Power Pivot window to integrate data. For example, you can pull production-cost data from an Excel workbook and actual sales results from SQL server through the Query into Power Pivot. From there, you can combine the two datasets by matching production-batch numbers to produce per-unit gross margins.
3) Working with Large Datasets
Measures
Excel junkies will no doubt agree that PivotTables are both one of the most useful, and at the same time, one of the most frustrating tasks we perform. Frustrating particularly when it comes to working with larger data sets. Fortunately, Power Pivot for Excel allows us to easily and quickly create PivotTables when working with larger sets of data.
In the image below, entitled Creating Measures, notice how the Power Pivot window is separated into two panes. The top pane has the data, and the bottom pane houses the measures. A measure is a calculation that is performed across the entire dataset. I have entered a measure by typing in the highlighted cell.
Total Sales:=SUM('Accounting Data'[Amount])This creates a new measure that sums across the Amount column. Similarly, I can type another measure in the cell below
Average Sales:=AVERAGE('Accounting Data'[Amount])From there, watch how quickly it is to create a familiar PivotTable on a large dataset.
Dimension Tables
As financial analysts using Excel, we become adept at using convoluted formulas to bend the technology to our will. We master VLOOKUP, SUMIF, and even the dreaded INDEX(MATCH()). However, by using Power Pivot, we can throw much of that out the window.
To demonstrate this functionality, I created a small reference table in which I assigned each Category to a Type. By choosing “Add to Data Model,” this table is loaded into Power Pivot (see Adding a User-created Table to a Power Pivot Model above).
I also created a date table to use with our dataset (see Creating a Date Table below). Power Pivot for Excel makes it easy to create a date table quickly in order to consolidate by months, quarters, and days of the week. The user can also create a more custom date table to analyze by weeks, fiscal years, or any organization-specific groupings.
Calculated Columns
Besides measures, there is another type of calculation: calculated columns. Excel users will be comfortable writing these formulas, as they are very similar to writing formulas in data tables. I have created a new calculated column below (see Creating a Calculated Column below) which sorts the Accounting Data table by Amount. Sales below $50 are labeled “Small,” and all others are labeled “Large.” Doesn’t the formula feel intuitive?
Relationships
We can then create a relationship between the Accounting Data table’s Category field and the Category table’s Category field using the Diagram View. Additionally, we can define a relationship between the Accounting Data table’s Sales Date field and the Calendar table’s Date field.
Now, without any SUMIF or VLOOKUP functions needed, we can create a PivotTable that calculated Total Sales by year, and type, with a slicer for Transaction Size.
Or, we can create a chart of Average Sales for each day of the week using the new Calendar table.
While this chart looks simple, it is impressive that it took less than ten seconds to create a consolidation over two million rows of data, without adding a new column to the sales data.
While being able to perform all these consolidated reporting scenarios, we can always still drill down into the individual line items. We retain our highly granular data.
Advanced Functions
Time Intelligence
Often, when we examine financial results, we want to compare it to a comparable timeframe from the previous year. Power Pivot has some built-in time intelligence functions.
Same Period Last Year Sales:=CALCULATE([Total Sales],SAMEPERIODLASTYEAR('Calendar'[Date])) YOY Sales Growth:=if(not(ISBLANK([Same Period Last Year Sales])),([Total Sales]/[Same Period Last Year Sales])-1,BLANK())Mismatched Granularities
As a financial analyst, one problem I often have to solve is that of mismatched granularities. In our example, the actual sales data is shown to the category level, but let’s prepare a budget that is only on a seasonal level. To further this mismatch, we will prepare a quarterly budget, even through the sales data is daily.
With Power Pivot for Excel, this inconsistency is easily solved. By creating two additional reference tables, or dimension tables in database nomenclature, we can now create the appropriate relationships to analyze our actual sales against the budgeted amounts.
In Excel, the following PivotTable comes together quickly.
Further, we can define new measures that calculate the variance between actual sales and budgeted sales as below:
Actual-to-Budget Variance:=DIVIDE([Total Sales],[Total Budgeted Sales])-1Using this measure, we can show the variance on a PivotTable.
Percent of Total
Finally, let’s examine sales in a particular category as a percent of all sales (e.g., category contribution to overall sales), and sales in a particular category as a percent of all sales of the same type (e.g., category contribution to seasonal-type sales). I created the two measures below:
Total Sales as Percent of All Sales:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data')) Total Sales as Percent of Type:=[Total Sales]/CALCULATE([Total Sales],ALL('Accounting Data'[Category]))Those measures can now be deployed in a new PivotTable:
Notice how the calculations are performed at both the category and seasonal type level. I love how quickly and effortlessly these calculations are performed on such a large dataset. These are just a few examples of the elegance and sheer computational power of Power Pivot.
Compression
Another benefit is that file sizes shrink. The original file size was 91MB, and now it is under 4MB. That is a compression of 96% of the original file.
How does this happen? Power Pivot uses the xVelocity engine to compress the data. In simple terms, the data is stored in columns rather than rows. This storage method allows the computer to compress duplicate values. In our example dataset, there are only four regions that are repeated over all two million rows. Power Pivot for Excel can more efficiently store this data. The result is that for data that have many repeating values, it costs much less to store this data.
One thing to note is that I used whole-dollar amounts in this sample dataset. If I had included two decimal points to reflect cents, the compression effect would lessen to a still-impressive 80% of the original file size.
SSAS Tabular
Power Pivot models can also be scalable to the entire enterprise. Let’s say you build a Power Pivot model that starts gaining many users in the organization, or the data grows to ten million rows, or both. At this point, you may not want thirty different users refreshing the model or making changes. The model can be seamlessly converted into SSAS Tabular. All the tables and relationships are retained, but now you can control the refresh frequency, assign roles (e.g., read-only, read and process) to various users, and deploy only a small Excel front-end which links into the Tabular model. The result is that your users could then access the deployed Tabular model with a small workbook, but not have access to the formulas and measures.
4) Data Visualization and Analysis
CUBE Formulas
One of the constant requests of my clients is that I create reporting that conforms to a strictly defined layout. I have clients that request specific column widths, RGB color codes, and pre-defined font names and sizes. Consider the following dashboard:
How do we populate the sales numbers without generating PivotTables if all of our sales are housed with Power Pivot for Excel? Using CUBE formulas! We can write CUBE formulas within any Excel cell and it will perform the calculation using the Power Pivot model we have already constructed.
For example, the following formula is typed in the cell under “2016 Total Sales:”
=CUBEVALUE("ThisWorkbookDataModel","[Measures].[Total Sales]","[Calendar].[Year].[2016]")The first part of the formula, highlighted in yellow, refers to the name of the Power Pivot model. In general, it is usually ThisWorkbookDataModel for newer versions of Power Pivot for Excel. The portion in green defines that we want to use the measure Total Sales. The part in blue instructs Excel to filter for only rows that have a Sales Date with a year equal to 2016.
Behind the scenes, Power Pivot has constructed an Online Analytical Processing (OLAP) cube with the data, calculated columns, and measures. This design allows the Excel user to then access the data by fetching directly with the CUBE functions. Using CUBE formulas, I have been able to construct full financial statements that conform to predefined layouts. This capability is one of the highlights of using Power Pivot for Excel for financial analysis.
Power BI
One great thing about Power BI is the Natural Language Q&A. To demonstrate, I uploaded the Power BI model onto my online Power BI account. From the website, I can ask questions and Power BI constructs the appropriate analysis as I type:
This type of query ability enables the user to ask questions of the data model and interact with the data in an easier way than in Excel.
Another benefit of Power BI is that the developers at Microsoft are constantly releasing updates to it. New features, many user-requested, are pushed out monthly. Best of all, it is a seamless transition from Power Pivot for Excel. So, the time you invested learning the DAX formulas can be deployed in Power BI! For the analyst who needs to share his analysis to many users on varying devices, Power BI may be worth exploring.
Best Practices
Once you get started, there are a few best practices that you should follow.
The first is to thoughtfully decide what to import in the first place. Will you ever use the salesperson’s home address? Do I need to know my customer’s email address in the context of this workbook? If the goal is to aggregate the data into a dashboard, then some of the data that is available will not be necessary for those calculations. Spending time curating the data coming in will greatly alleviate issues and memory-usage later when your dataset expands.
Another best practice is to remember that Power Pivot is not Excel. In Excel, we are accustomed to creating calculations by constantly expanding our worksheets to the right. Power Pivot for Excel most efficiently processes the data if we limit this desire for manifest destiny. Instead of continuously creating calculated columns to the right of your data, learn to write measures in the bottom pane. This habit will ensure smaller file sizes and quicker computations.
Finally, I would suggest using plain-English names for measures. This one took me a long time to adopt. I spent the first few years making up names like SumExpPctTotal, but once other people began to use the same workbooks, I had a lot of explaining to do. Now, when I start a new workbook, I use measure names like Expense Line Item as Percent of Total Expenses. While the name is longer, it is much easier for someone else to use.
Real-World Use Cases
In this article I have presented only a handful of the ways in which Power Pivot for Excel allows you to take an important step beyond plain-vanilla Excel. I thought it would be useful to highlight some real-world use cases in which I’ve found Power Pivot for Excel is extremely useful.
Here are some:
Analyze performance of a large portfolio of assets over varying time ranges: Since Power Pivot for Excel allows us to define measures that compare a time-period with a previous one, we can quickly have quarter-over-quarter, year-over-year, and month-over-month performance all on a rolling basis by writing only a few measures.
Summarize accounting data using customized aggregation levels: By identifying each general-ledger line item by name, category, and financial statement, reports can quickly be created that include the appropriate line items.
Chains can identify same-store sales: Using a table that maps when stores come online, financial results can be compared on a same-store basis.
Pinpoint over- and under-performers in sales: PivotTables can be created that highlight the top five SKUs and bottom five SKUs by sales, gross margins, production timeframes, etc.
Retailers can define calendar tables that use a 4-4-5 configuration: Using a custom date table, a retailer can assign each day to a specific 4-4-5 month easily, then daily sales results can be rolled into the corresponding month.
As financial analysts, we are required to perform complex calculations on ever-expanding datasets. Since Excel is already the default analytical tool, the Power Pivot learning curve is easy, and many of the functions mirror Excel’s native functions.
With the use of CUBE functions, Power Pivot for Excel seamlessly blends into your existing Excel workbooks. The computational efficiency gain cannot be overlooked. Assuming a 20% faster processing speed, which is conservative, the financial analyst that spends six hours a day within Excel can save 300 hours a year!
Additionally, we can now analyze datasets that are much larger than we could previously with our traditional Excel. With models designed efficiently, we can easily have 10x the amount of data that we previously were allowed in traditional Excel, while maintaining quick analytical agility. With the ability to convert the models from Power Pivot to SSAS Tabular, the amount of data that can be processed is 100-1,000 times what we can achieve in Excel.
If you’re interested in trying Power Pivot for Excel out, below are some useful materials to get you started.
Useful References and Guides
Collie, R., & Singh, A. (2016). Power Pivot and Power BI: The Excel user’s guide to DAX, Power Query, Power BI & Power Pivot in Excel 2010-2016. United States: Holy Macro! Books.
Ferrari, A., & Russo, M. (2015). The definitive guide to DAX: Business intelligence with Microsoft Excel, SQL Server Analysis Services, and Power BI. United States: Microsoft Press, USA.
How To Use Powerpivot In Excel: The Ultimate Guide
Power Pivot is an add-in first introduced in Excel 2010 and now a staple part of the modern Excel. It has changed the way that we can work with and manipulate large volumes of data in Excel.
In this article, we will not only answer the question of what is Power Pivot? But also why and how to use PowerPivot with real business use cases.
Download your data files
Follow along with the steps in the article by downloading these data files
What is Power Pivot & why is it useful?
Although an Excel worksheet can handle 1,048,576 rows of data. In reality, it can struggle as you get to 100,000 or even before that depending on what you have in your workbook.
Power Pivot enables us to work with big data beyond the 1,048,576 limitation and still produce smaller, leaner and faster workbooks than a standard PivotTable.
It does this by loading the data into the internal data model of Excel and not onto a worksheet. Relationships can then be created between the different tables of data. No more VLOOKUPs to pull data together into one big list.
We can then create PivotTables based on this model to analyze multiple tables of data.
You can also use a powerful formula language in Power Pivot called DAX. This stands for Data Analysis Expressions.
The DAX language is vast and enables us to perform more complex calculations than we can do with a standard PivotTable.
So what is Power Pivot? It is really a combination of using PivotTables and DAX calculations with the internal data model of Excel for analysis of big data.
Check out this short video that explains why we need Power Pivot:
A Power Pivot use case
Let’s look at an example business use case to see where Power Pivot will help us and I’ll explain how to use PowerPivot in this case.
Let’s imagine a scenario where we export sales data from our database. This includes a CSV file of all sales transactions for a specified time period.
It also includes a CSV file with all our customers and their details, and one with all our product details.
We would like to import these 3 files into an Excel workbook to analyze them and find the top 5 selling products, as well as which countries we received over £10 million.
Previously we would have imported the files into three different sheets and then used VLOOKUPs to pull the data into one big list for use in a PivotTable.
But with Power Pivot, we will import them directly into the data model for efficient storage. Then create relationships between the tables (instead of thousands of VLOOKUPs). And perform analysis with a PivotTable and DAX.
How to get and install the Power Pivot add-in
In Excel 2013, 2016 and 365 Power Pivot is included as part of the native Excel experience. It will just take a few seconds to install it from the COM add-ins the first time you want to use it.
The Power Pivot tab will then be visible on the Ribbon.
If you are using Excel 2010 you will need to download the Power Pivot Add-In from the Microsoft Site.
How to import CSV files to the Data Model
We will now walk through our use case scenario.
You can download the files and follow along for some hands-on practice.
Download your data files
Follow along with the steps in the article by downloading these data files
Firstly we need some data. This data could already be in Excel. But often if you are working with large data sets you are getting data from a database, a folder or multiple text/CSV files.
The best way to bring this data into Excel is by using Power Query. Power Query is a tool built into Excel to make importing and transforming external data simple.
Power Query is outside the scope of this article, but here is a quick example of getting our sales data from CSV files. I will start with the chúng tôi file.
The Power Query Editor window will load. There are a lot of tools we can use here to transform the data.
This is just a quick example to get the data into the model for Power Pivot. So we will just close and load the data.
The Import Data window appears. Select Only Create Connection and check the box to Add this data to the Data Model.
The data is then loaded into the model. So you will not see it on the worksheet, but you will see a Queries and Connections pane appear showing the number of rows loaded.
The image below shows the chúng tôi file loaded. It contains 106,693 rows. That is a lot of rows, but you will see that it does not impact the performance of calculations in Power Pivot.
Repeat the process for the other 2 CSV files. When finished the loaded queries will look like below.
Viewing the Data Model in Power Pivot
The Power Pivot for Excel window is displayed.
The initial view you are taken to is called the Data View. The tables of data are shown on different tabs, similar to worksheets. This is, however, just a display and not how they are stored.
Underneath the tabs is the Calculation Area. We will talk more about this shortly when we cover measures.
This provides a better view of the model and is great for viewing the relationships between the tables which we will create.
Create relationships between the tables
With the tables loaded into the model, we will now create relationships between them. This will enable us to create PivotTables using the data from all three tables.
The Diagram View is the easiest way to set this up. Let’s start by arranging the window more efficiently.
Drag the Sales table under the Products and the Customers tables.
The Sales table contains the transactional information and is referred to as “the data”, or “the fact table”.
The Products table and the Customers table contain information on groups of objects that interact with the data and are referred to as “lookup”, or “dimension tables”.
We will create two ‘one to many’ relationships. One between the Customers table and the Sales table, and another between the Products table and the Sales table.
This is because a customer could make one or many sales with us. And the same for the products. A product could be sold once or many times.
Repeat the step to create a relationship between the Product ID field in the Sales table and the ID field in the Products table.
The image below shows the completed relationships. The filter direction of the data is displayed by an arrow, and a 1 and asterisk (*) symbols are also displayed to show the relationship type.
Create a PivotTable from the Data Model
With the Data Model set up, we can create a PivotTable.
The PivotTable appears and in the field list you can see the three tables. We can now access the fields from each table and drag them to the areas of a PivotTable as normal.
So what is Power Pivot? It is a PivotTable that uses data from the internal model.
Now let’s create one of our use case examples. We will find the top 5 selling products.
Drag the Product Name field from the Products table into the Rows area. Then drag the Total Sales field from the Sales table into the Values area.
This will sum the total for each product in our data.
We have the top 5 products in a PivotTable.
Now when we dragged the Total Sales field into the Values area of our PivotTable, it created what is called an Implicit Measure.
We can use a PivotTable from the Data Model in the same way as we may be used to doing. This is great at the beginning or if you’re just performing simple analysis. But it is better and more efficient to create measures and to use them in PivotTables.
Using DAX to create Measures
Let’s begin to have a look at the DAX language to perform calculations in Power Pivot.
There are two types of DAX calculations – Calculated Columns and Measures.
A Calculated Column is used to create additional columns in your data model. And these columns can then be used as labels in the rows, columns and Slicer areas of a PivotTable.
It is encouraged to create these columns in the original data, or in Power Query instead of the model if possible. These columns can be really useful for a further breakdown of our data such as grouping dates into weekday and weekend labels.
In this article, we are going to create the other type of DAX calculation – called a Measure. Measures are calculations that are dragged into the values area of a PivotTable such as Sum and Average.
The DAX language is huge, going far beyond the standard Sum and Average. So creating these in the model provides far more power than within a standard PivotTable and Implicit Measures.
Measures created with DAX can also be used multiple times and in multiple PivotTables (but calculated just once). This improves the processing speed. You can also assign a format to a Measure so you won’t need to format them every time they are used.
We will create a Measure to sum the Total Sales field from the Sales table.
The Measure window appears.
Select the table from the list that you would like the new measure stored within. This measure will be stored in the Sales table.
Enter a name for the measure. This measure is named Sum of Sales.
You can enter a description for a measure. Especially if complex. Here it is omitted since the name serves that role as well.
Enter the following formula into the box provided: =SUM(Sales[Total Sales])
Using a Measure in a PivotTable
With the measure created we can use it in our PivotTables for analysis.
Using the PivotTable we created earlier in the tutorial, we can remove the Sum of Total sales implicit measure.
Our new measure is shown in the list of table fields and can be dragged into the Values area as a replacement.
We will now create our other use case of showing in which countries we received over £10 million and re-use the same measure.
Insert a new PivotTable as before and drag the Country field from the Customers table into Rows, and the Sum of Sales measure from the Sales table into Values.
The countries that meet the criteria are shown as below.
Our measure has been used in both PivotTables to help us achieve both use cases. The DAX language is capable of far more and I encourage you to read further in that area.
In this article, we have answered the question of what is Power Pivot and demonstrated two business use cases on how to use PowerPivot through the entire process.
We imported the data into the model, created relationships and a measure, and then used them in PivotTables.
Power Pivot is one of the best improvements to how we use Excel. It is an extremely powerful tool and this article is an introduction to what it is capable of. I encourage you to learn and develop your Power Pivot skills even further.
Alan Murray
Alan is a Microsoft Excel MVP, Excel trainer and consultant. Most days he can be found in a classroom spreading his love and knowledge of Excel. When not in a classroom he is writing and teaching online through blogs, YouTube and podcasts. Alan lives in the UK, is a father of two and a keen runner.
Using Pivot Tables In Excel 2022
The only thing in the bottom section that you need to make a pivot table work is Values. You will find that Rows, Columns, and Filters help to organize the data and information in the pivot table.
To see what we mean, let’s choose a column from the top half.
We are going to choose Employee. We want each employee to appear in a row, so we drag it to the Rows section in the bottom half.
We chose Sum of Sales.
We are going to drag Week to the Columns section.
Now, if we look at our pivot table, we see that Excel has summarized the number of sales in our worksheet.
With pivot tables, there is something you need to keep in mind. If you drop a text field into values, Excel will assume you want to count the values. We did this, and it counted the number of occurrences of the item in our data. If you drop a numerical field into Values, Excel will assume you want a sum of the items.
You can also drag and drop to and from Values, Columns, Rows, and Filters.
You can do these things to create the pivot table that you want.
Changing the Formatting and Formulas of PivotTables
It is easy to create a pivot table in Excel 2016, but that is just where the fun begins. Now that you created a pivot table, it is time to learn how to format it.
Below is our pivot table.
If you wanted to format the data in the pivot table, you could do so by selecting a column or row, then going to the Home tab and applying formatting, such as changing the font type, font size, or font color. Those are very basic Excel skills and easy enough for you to do.
However, if you applied formatting in this manner, if you ever refreshed the data in your pivot table or added rows or columns, the formatting might not be applied.
We then see the Value Field Settings dialogue box.
In this dialogue box, we can change the name of the field in the pivot table by going to the Custom Name field.
This will return you to the Value Field Settings dialogue box.
Also in the Value Field Settings dialogue box, we can change the function. Right now, we have it at the default, which is Sum. It can be changed to Count, Average, Max, Min, etc. All of these functions relate to the total sales. For example, average of total sales, and so on.
You can also add the same field more than once.
This means that, if you wanted, you could change the function for the Sales field to Count. Then, you could drag the Sales field to the Values section again, which would display the Sum of Sales.
Just make sure you change the number format that matches the function. We would not want Currency for Sales function, for example.
Creating Different PivotTables Using the Same Data
You can create as many pivot tables as you need to using the same data from the same worksheet. You can choose to place the pivot tables together, or you can place them in different worksheets.
You can now see our new pivot table below our existing pivot table.
We can now add columns, rows, and values to our new pivot table by following the steps we learned earlier in this article.
Moving PivotTables
You can move a pivot table to a new location within a worksheet or to a new worksheet entirely.
You will then see the Move PivotTable dialogue box.
The pivot table is moved for you.
Deleting PivotTables
This selects the PivotTable.
You can then press Delete on your keyboard.
The Report Filter Option
In the snapshot below, we have a simple pivot table.
You can see that we have dragged and dropped the Employee field into the Rows section, and the Sales field into the Values section in order to create the pivot table.
Now we are going to learn what happens when you drag a field to the Filters section.
For this example, we are going to drag the Territory field to the Filters section.
When we do this, we can see the filter is added above our pivot table.
We can now filter the data in our pivot table by the territory of the employee.
Of course, we can also add another filter to our pivot table to further refine the data that is summarized
Sorting Data in a PivotTable
Data in a pivot table can be sorted by row or column labels, as well as values.
Whenever you create a pivot table, Excel does the sorting for you. Excel puts row and column labels in the order that they appeared in the original data worksheet.
You can see Row Labels circled in red below.
As you can see in the next snapshot, we are now given the ability to sort the labels alphabetically from A to Z, or Z to A.
You will then see the following dialogue box.
Choose if you want to sort the values from A to Z or Z to A by putting a check beside your choice.
In the snapshot above, you see that we can also sort by Sum of Sales – or our values.
Our data is then sorted by values.
Refreshing the Data in a PivotTable
A pivot table is based on data that is contained in a worksheet. If you change the data in the worksheet after you have created a pivot table, you will need to refresh the data in the pivot table so that it reflects the current data in the original worksheet.
Let’s show you what we mean so that it makes sense.
Below is our pivot table.
Now, we are going to go back to our original worksheet.
However, when we go back to our pivot table, our Sum of Sales column still reflects the 21 sales we originally said we made.
We need to refresh the data so the changes made in the original data are reflected in our pivot table.
To do this, go to the Analyze tab under PivotTable Tools. Go to the Refresh dropdown menu, and select Refresh All.
As you can see, the data in our pivot table is now refreshed.
Another thing you can do to make sure that your data stays refreshed is to set your options in Excel so that the data in your pivot table is refreshed each time you open the workbook.
To do this, go to the Analyze tab again.
You will then see this dialogue box:
Verifying the Data in a PivotTable
It is easy to take for granted that the data presented in a pivot table is correct.
However, if you ever wanted to double check to make sure that the data is correct, there is an easy way to do that.
Let’s say we want to verify that the employee named Smith really made 31 sales.
The data shown above is displayed in a new worksheet. You can choose whether you want to keep the worksheet or delete it.
An Ultimate Guide To Use Excel Pivot Charts
A PIVOT CHART is one of the best ways to present your data in Excel.
Why I’m saying this? Well, data in a visual way not only helps the user to understand it but it also helps you to present a clearer picture of it and you can make your point clear with led efforts.
And when we talk about Excel, there are numbers of charts which you use but there’s one of all those that STANDS OUT and that’s a PIVOT CHART.
If you are serious about taking your data visualization skills to a whole next level you need to learn to create a pivot chart.
And in the guide, I’ll be explaining to you all the details you need to know to understand how the pivot chart works. But before that, here are some words from Wikipedia.
Pivot Chart is the best type of graphs for the analysis of data. The most useful feature is the possibility of quickly changing the portion of data displayed, like a PivotTable report. It makes Pivot Chart ideal for presentation of data in the sales reports.
Difference a Pivot Chart and a Normal Chart
A standard chart use range of cells, on the other hand, a pivot chart is based on data summarized in a pivot table.
A pivot chart is already a dynamic chart, but you have to make changes in data to convert a standard chart into a dynamic chart.
Steps to Create a Pivot Chart in Excel
You can create a pivot chart by using two ways. One is to add a pivot chart in your existing pivot table, and other is to create a pivot chart from scratch.
1. Create a Pivot Chart from Scratch
Creating a pivot chart from scratch is as simple as creating a pivot table. All you need, a data sheet.
Here I am using Excel 2013 but you use steps in all versions from 2007 to 2016.
Select any of the cells in your data sheet and go to Insert Tab → Charts → Pivot Chart.
The pop-up window will automatically select the entire data range and you have the option to choose the place where you want to insert your pivot chart.
Now, you have a blank pivot table and pivot chart in a new worksheet.
In pivot chart fields, we have four components like we have in a pivot table.
Axis: Axis in pivot chart is as same as we have rows in our pivot table.
Legend: Legend in pivot chart is as same as we have columns in our pivot table.
Values: We are using quantity as values.
Report Filter: You can use report filter to filter your pivot chart.
So, here is your fully dynamic pivot chart.
2. Create a Pivot Chart from Existing Pivot Table
If you already have a pivot table in your worksheet then you can insert a pivot chart by using these simple steps.
Select any of the cells from your pivot table.
Go to Insert Tab → Charts → Pivot Chart and select the chart which you want to use.
It will insert a new pivot chart in the same worksheet where you have your pivot table. And, it will use pivot table rows as axis and columns as the legend in pivot chart.
More Information about Pivot Charts
Managing a pivot chart is simple and here is some information which will help you do it smoothly.
1. Change Chart Type
When you enter a new pivot chart, you have to select the type of the chart which you want to use. And, if you want to change the chart type you can use following steps for that.
Select your pivot chart and go to Design Tab → Type → Change Chart Type.
Select your favorite chart type.
2. Refresh a Pivot Chart
Refreshing a pivot chart is just like refreshing a pivot table. If your pivot table is refreshing automatically, then your pivot chart will also update along with that.
Go to data tab and tick mark “Refresh data when open a file”.
Use below VBA code to refresh all kind of pivot tables and pivot chart in you workbook.
Sub auto_open()Dim PC As PivotCacheFor Each PC In ActiveWorkbook.PivotCachesPC.RefreshNext PCEnd SubApart from above code you can use following VBA code if you want to refresh a particular pivot table.
Sub auto_open()ActiveSheet.ChartObjects("Chart 5").ActivateActiveChart.PivotLayout.PivotTable.PivotCache.RefreshEnd SubJust like a pivot table, you can filter your pivot chart to show some specific values. One thing is clear that a pivot table and pivot chart are connected with each other.
So, when you filter a pivot table, your chart will automatically filter.
And, when you add any filter in your pivot table it will automatically add into your pivot chart and vice versa.
Please follow these steps for this.
In your pivot chart field list, drag fields in the filter area.
4. Show Running Total in a Pivot Chart
In below pivot chart, I have used a running total to show the growth throughout the period.
To enter a running total in a pivot chart is just like entering a running total in a pivot table. But we need to make some simple changes in chart formatting.
From your pivot chart field list, drag your value field twice in value area.
Now, in second field value open “Value Field Settings”.
Go to “show value as” tab and select running total from the drop down.
So here is your pivot chart with running total but one more thing which we have to do to make it perfect.
5. Move a Pivot Chart to New Sheet
Like a standard chart, you can move your Excel pivot chart to a chart sheet or any other worksheet.
To move your pivot chart.
Now, you have two different options to move your chart.
New Chart Sheet.
Another Worksheet.
You can also move your chart back to the original sheet using same steps.
Extra Tips on Pivot Charts
Some of extra tips to make a better control over it.
1. Using a Slicer with a Pivot Chart to Filter
As I have already mentioned, you can use a slicer with your pivot chart.
Select your pivot chart and go to Analyze Tab → Filter → Insert Slicer.
Select the field which you want to use as a filter.
And, the best part is that you can filter multiple pivot tables and pivot charts with a single slicer. Follow these steps.
Using a slicer is always a better option is than a standard filter.
2. Insert a Timeline to Filter Dates in a Pivot Charts
If you want to filter your pivot chart using a date field then you can use a timeline instead of a slicer.
Filtering dates with a timeline is super easy.
Select your pivot chart and go to Analyze Tab → Filter → Insert Timeline.
Select your date field from the pop-up window and it will show you fields with dates.
3. Present Months in a Pivot Chart by Grouping Dates
Now, let’s say you have dates in your data, and you want to create a pivot chart on month basis.
One simple way is to add a month column in your data and use it in your pivot chart.
But, here is the twist.
Go to your pivot table and select any of the cells from your date field column.
You can group dates in your pivot table which will further help you to create a pivot chart with months even when you don’t have months in source data.
Sample File
Download this sample file from here to learn more.
Bạn đang đọc nội dung bài viết Power Pivot For Excel Tutorial: Top Use Cases And Examples trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!