![videos on pivot tables in excel 2013 videos on pivot tables in excel 2013](https://www.excelcampus.com/wp-content/uploads/2015/02/Turn-Data-into-a-Dashboard-with-Pivot-Tables.png)
The VLOOKUP has become redundant here.I have taken Customer (name) from the Customers table and Sales values from the Sales table and made a pivot out of it.You have two tables Sales and Customers and their columns to choose from.Notice a couple of interesting things going around here.!! Lets make a pivot table report for Sales – Customer wise.
#Videos on pivot tables in excel 2013 code
In the Related Table Field choose Customers Table -> Primary Key here is Customer Code.In the Create Relationship box -> Table Field -> choose Sales Table -> and Foreign Key here is Customer Code.Click New -> to establish a new relationship.In the Analyse Tab -> Click ‘Relationships’.but for the time being the relationship is primary or foreign and is between the Customer and Sales tables ? Take the Customers from ALL Tab to the Active Tab A separate tab for Active tables and All tables.A little table symbol in front of Sales – displaying column headers in it.Note a few visual and conceptual differences than the usual Pivot Tables Take a look at the pivot table field list, Sales table has been added. Make a pivot table from the Sales table and be sure to check the data model in the Pivot Table dialogue box Customer Code in Sales Table is a Foreign Key, WHY ? because the records are repetitive and if I want to summarize all transaction of a particular customer code, I can refer to Customer table with unique customer code that matches with the customer codes (multiple entries) in the Sales table!! I hope you got it this time. Eh? Ok Ok sorry for the weird definition take a look at the example. It would be repeated records in a table which can be uniquely identified by unique records in another table. Could you guess what is the primary key in our sales data ? Ok, what is the Primary Key in Customer Table? 2. Foreign Key Any item (column header) which is unique (non repetitive) in a set of data is the Primary Key. Sound like you have heard it ? It is the genesis of forming data relationships. To use DATA MODEL effectively you will need a good hold on the following concepts 1. To name a Table -> go to Design Tab (appears when the active cell is anywhere inside the table) ->Table Name on the extreme left -> Enter name there.Name the Sales Transaction Data as ‘Sales’ and the Customer Table as ‘Customer’.Place the active cell anywhere inside the data and use the shortcut CTRL T to convert both the data sets into a Table.Note that the customer code used in the sales transaction data are mentioned in this table with the customer name and their association date Sales transaction data ( get Raw Data here)Īnd customer data. let’s not just talk about it but experience it!! Ready for the steroid ?Ĭonsider these 2 data sets. It additionally has a few new formulas for advanced analysis. But these steroids are available only in Excel 2013Ī data model can link multiple data sets (converted into tables) and make a single pivot table. Today let’s inject a steroid named DATA MODEL to our standard pivot tables.
![videos on pivot tables in excel 2013 videos on pivot tables in excel 2013](https://i.ytimg.com/vi/ULJ7EzlBDMw/maxresdefault.jpg)
You can see it has defects, so which is why the Product result when you multiply them together is 1.Pivot Tables have been an all time favorite for most analysts and managers, especially for their simplicity and powerhouse utility. Now try double-clicking on Ice Cubes during the month of March 2013: You can see that in the data breakdown for September 2014, there are no defects! This is why the Product result is 0. We love the ones that show zero because that means there are no defects! For example, would be for Bottles during the month of September 2014. Double click on it to see more details! So if any row for that specific duration has a 1, then the result in this Pivot Table is a 1 as well. STEP 3: Now what Excel has done is for that specific duration, it has multiplied all the Defect values. Click on the arrow and select Value Field Settings STEP 2: It will default as Sum of DEFECTS.
![videos on pivot tables in excel 2013 videos on pivot tables in excel 2013](https://www.exceltip.com/wp-content/uploads/2019/08/4.gif)
This will be crucial once we use the Product function later. If there is a defect that day, we simply mark it as 1. Notice that I have added a Defects column.