Power Pivot is an add-in of Excel that helps to create data models and perform powerful analysis of big data in super-fast speed and accuracy. It enables users to import and manipulate hundreds of thousands of rows of data from different data sources. It allows to create relationships among data, PivotCharts, and PivotTables, and perform thorough data analysis to make timely business decisions. In this blog, we are presenting a thorough guide on how to use Power Pivot in Microsoft Excel.
Step-by-Step Guide to use Power Pivot in Excel
Power Pivot is a power add-in of Excel, which is designed to be user-friendly and address present data analytics needs effectively. Following are the steps you have to follow to use Power Pivot:
Step 1. Enable Power Pivot Add-in
By default, Excel does not make Power Pivot enabled, so you have to manually enable the add-in. When you are in Excel sheet, click on “File” > “Options” > “Add-ins”. In the Add-ins tab, from the drop-down options of the “Manage” section, click on “COM Add-in” and then click “Go”. Now tick the checkbox narrating “Microsoft Power Pivot for Excel” and then click “OK”. Power Pivot is turned on and you can see it in the top menu ribbon.
Step 2. Create Sample Excel Sheets
To learn how to use Power Pivot on Excel, you need some data for testing. So, create some sample Excel sheets. For this guide, let’s assume you have created 3 Excel sheets. The first one is about your “Customers” that includes all of your customers’ information, such as Customer ID, Name, Phone, Address, City, Country, and Notes. The second one is your “Orders” sheet that includes the customers’ info and products they purchased. So, the columns in this section are Customer ID, Order ID, Product Name, Units Sold, and Date. The third one is your “Product” sheet that includes your Product Name, Cost, and Revenue.
Step 3. Create Data Model
For using Power Pivot, you have to create a data model. A data model is the collection of all the tables and data from the Excel sheets. So, you will make a data model using the sample Excel sheets you created above. To do that, go to the “Power Pivot” tab from the top menu bar and then click “Manage”. It will show you a whole white page, which is the main Power Pivot sheet. Now you have to import your Excel sheet data to this page. From the “Home” tab, click “From Other Sources”, scroll down, select “Excel File”, and then click “Next”. Now browser your first sample Excel file from your computer, tick the checkbox below it, and then click “Next”. Afterward, it will ask you to select the tables and views you want to import. You can also preview and filter the data. As, it is a test practice, so you can keep data as it is and click “Finish” and then “Close”. So, your first Excel sheet of “Customers” is added to Power Pivot. Follow the same process for the remaining two sample files and add them to Power Pivot. You can also use the “Add to Data Model” option from the Power Pivot tab in the Excel sheet to directly transfer that Excel sheet to Power Pivot.
Step 4. Define Relationships
Power Pivot allows you to define relationships between different tables. For example, your “Customers” sheet contains the Customer ID column and your “Orders” sheet also contains the Customer ID column, but Excel doesn’t know that they are linked. Similarly, both your “Orders” and “Product” sheets contain the Product Name column, but Excel doesn’t know that they are linked. To define such relationships, click on “Diagram View” from the “Home” tab. It will show you all the three table headers from the three sheets you just imported into Power Pivot. Now click on “Customer ID” in your “Orders” table section and hold and drag to the “Customer ID” in the “Customers” table section. You will see that Power Pivot has set up the relation between “Customer ID” for both sheets. You will see an asterisk (*) symbol at the “Orders” end and “1” at the “Customers” end. This means that it is 1 to many relationships. In simple words, the “Customer ID” in the “Customers” sheet has many relations with the “Orders” sheet. Now do the same process for establishing the relationship of “Product Name” for “Orders” and “Product” sheets. Once you are done defining relationships, click “Data View” from the “Home” tab to go back to the main Power Pivot interface.
Step 5. Calculation
Let’s assume you want to know how much revenue you made per order, like the cost, profit, etc. If you look at your “Orders” sheet, you will notice that you don’t have any column showing per product revenue or cost. While you have that information in your “Product” sheet. As you have defined relationships, so you can use them to set up the calculation for knowing revenue per order. Following are the steps you have to follow:
- In your imported “Orders” sheet in Power Pivot, add three new columns “Revenue”, “Cost”, and “Profit”.
- To calculate revenue per order, you have to extract the revenue rates from the “Product” sheet and multiple it with the “Units Sold” column of the “Orders” sheet. So, go to the cell below the newly created “Revenue” column and type “=RELATED(‘Product'[Revenue])*Orders[Units Sold]”. You don’t have to manually write all this formula, as the relevant options will show up in the drop-down suggestions because you have linked the sheets. This way, your “Revenue” column will be completed.
- To calculate cost per order, the formula will be “=RELATED(‘Product'[Cost])*Orders[Units Sold]”.
- To calculate profit per order, the formula will be “=Orders[Revenue]-Orders[Cost].
This way, you can calculate any value using the table values of all the sheets for whom you have defined relationships.
Step 6. Measurements
Let’s assume you want to know your total profit, the number of customers you have, and the average profit per customer. This can be done using the Power Pivot “Measures” feature. To access it, close the current Power Pivot and it will take you back to one of your Excel sheets. Now from the “Power Pivot” tab, click on “Measures”. A pop-up window will appear, which is where you will write your measure. Let’s first work on finding the total number of customers. As you have created specific customer IDs, so you can utilize them to find the total count. In the pop-up window, choose table name “Customers”, write the measure name as “Total number of customers”, and write the formula as “=DISTINCTCOUNT([Customer ID])”. From the “Category” section, select “Number”, and then click “OK”. Now go back to your main Power Pivot window by clicking “Manage”. From there, go to the “Customers” imported sheet, and at the bottom, you will be seeing the total number of customers count.
Other than the above method of adding measure, you can also directly add measure in the Power Pivot. Click on the cell beneath the cell narrating “Total number of customers” and write the formula “=SUM(Orders[Profit])” in the formula bar. This formula will calculate the total profit across all your product sales. You can also easily update the name of this measure from the formula bar, such as “Total profit”.
You can also use the above two measures to create a third measure. Let’s assume you want to calculate the average profit per customer. So, you will go to the cell beneath the total profit cell and write the formula “=[Total profit]/[Total number of customers]”. You can also rename it to make it clearer.
Step 7. Use Data Model to Insert PivotTable
Close the Power Pivot window and go back to the Excel sheet. From the “Insert” tab, click on “PivotTable” and then click “From Data Model”. A pop-up window will appear, which asks where you want to place the PivotTable. You can choose a new worksheet and then click “OK”. Now you have to select some fields to make PivotTable work. On the right-hand side, you will see that three sample Excel sheets sections showing. Let’s assume you want to see your customers’ names table and the orders each customer placed. So, expand the “Customers” sheet and drag the “Name” checkbox to the “Rows” section. Afterward, expand the “Orders” sheet and drag “Order ID” to the “Values” section. So, in the PivotTable sheet, you will now see customers’ names, but you will notice that the Order ID is showing as a sum value. To change that, click on the drop-down icon near “Sum of Order ID” from the “Values” section and then click “Value Field Settings”. From there, you can choose the option “Count” and click “OK”. This way, you can now see how many orders each customer made. The point to note is that “Customers” data was separate from “Orders” data, but due to the data model, you are able to create a linked PivotTable. You can also drag the measures you created in step 6 to the “Value” section to also add them in the PivotTable.
Step 8. Key Performance Indicators
You can also add Key Performance Indicators (KPIs) through Power Pivot. While in the same PivotTable sheet, go to the “Power Pivot” tab, click “KPIs” and then click “New KPIs”. In the pop-up window, you have to set your KPIs settings. From the top, you have to choose the KPI base field from the 3 measures you created in step 6. Let’s assume you want to look at total profit as KPIs, so select “Total Profit”. Now you have to define the target value. Currently, you can go for some absolute value like “60,000”. This will mean that you are expecting $60,000 in profit per customer. Afterward, you have to select the range which is considered red (means low sale), yellow, and green. Once you have set your scale, click “OK”. Now in your PivotTable, you will see another column of “Total Profit Status”. But you will be seeing numeric values instead of colors. To bring colors, expand the drop-down menu of “Total Profit” from the right-hand side and un-tick and re-tick the checkboxes. This way, you will now see red, yellow, and green colors in your PivotTable presenting KPIs.
Step 9. Utilize PivotChart
Instead of seeing a table from PivotTable, you can also see those statistics in the form of PivotChart. Go to the “PivotTable Analyze” and click PivotChart. Choose the graphical shape you like and click “OK”.
Step 10. Insert Slicers
The PivotChart you are currently seeing from step 10 is of total profit per customer. But what if you want to see the profit specific to one product. This is where Slicers help. Go to “PivotTable Analyze” and click “Insert Slicer”. From the pop-up window, click “All”, tick the checkbox narrating “Product Name”, and click “OK”. Now you will also see all the products name in your PivotTable Sheet. If you click on any product, you will see that your PivotTable and PivotChart will be narrating the profit values specific to that product.
Wrapping Up
In the present time, data holds significant importance for businesses. Data analytics are becoming crucial for making business decisions. Power Pivot makes it much simple and easy for businesses to visualize and analyze their data in different ways to extract useful insights from their data.
