Excel Magic Trick 1151: Get Two Tables Into Pivot Table Field List using Excel 2013

preview_player
Показать описание


See how to Get Two Tables Into Pivot Table Field List using Excel 2013 to create a Report based on Revenue from one table and Category from a second table.
1. (00:08 minute mark) Why you would need two tables in Pivot Table Field List
2. (01:10 minute mark) Convert data to Excel Table
3. (01:30 minute mark) Insert PivotTable and More Tables Button to add second table to Field List. This step adds first table to Data Model.
4. (02:25 minute mark) Drag and Drop Fields From Two Tables
5. (02:33 minute mark) Create Relationship between two tables (to simulate VLOOKUP). This step adds the second table to the data model.
6. (03:59 minute mark) Add Number Formatting without PowerPivot
7. (04:25 minute mark) Discussion about how the PivotTable from Data Model is different from PivotTable create natively in Excel
8. ( minute mark)
9. ( minute mark)

PivotTable Trick. Pivot Table Trick. Pivot Table Excel 2013
  1. excelisfun
  2. Excel 2013
  3. Excel 2003
  4. Excel 2007
  5. Excel 2010
  6. excelisfun
Рекомендации по теме
Excel Magic Trick 0:05:34

Excel Magic Trick 1151: Get Two Tables Into Pivot Table Field List using Excel 2013

Excel Magic Trick 0:07:30

Excel Magic Trick 1152: Formula or Power Query: Cost Report When Product IDs Don’t Match

Excel Magic Trick 0:06:57

Excel Magic Trick 1181: Formula to Count Cells with 4 or More Characters: COUNTIFS & SUMPRODUCT

Excel Magic Trick 0:06:09

Excel Magic Trick 1154: Unique Count:Name & Date & Criteria From 3rd Column (3 Methods)

Excel Magic Trick 0:05:20

Excel Magic Trick 1148: Calculate Retirement Year Based On 80 Rule

Excel Magic Trick 0:10:25

Excel Magic Trick 1153: Unique Count For Name & Date: Array Formula, 2013 PivotTable, More…

Excel Magic Trick 0:06:11

Excel Magic Trick 1156: Count with Two Conditions / Criteria From Non-Proper Data Set (2 Methods)

Excel Magic Trick 0:05:23

Excel Magic Trick 1186: Extract Color From Description Field & Create Revenue by Color PivotTabl...

Excel Magic Trick 0:04:47

Excel Magic Trick 1165: Substitute Final Score If Greater Than Low Score: IF, MIN & SUM Function...

Excel Magic Trick 0:05:08

Excel Magic Trick 1189: Extract Color From Description Create Revenue by Color PivotTable, 2nd Take

Excel Magic Trick 0:07:51

Excel Magic Trick 1164: Average Score After Subtracting Two Lowest: SMALL & Array or AVEAGEIF?

Excel Magic Trick 0:04:06

Excel Magic Trick 1202: Conditional Formatting For a Cross Tabulated PivotTable

Excel Magic Trick 0:11:21

Excel Magic Trick 1263: Power Query M Code: 10 Cross Tabulated Transformed into 1 Proper Data Set

Excel Magic Trick 0:11:09

Excel Magic Trick 1208: Combine 4 Columns into 1 Table For PivotTable Report (without Power Query)

Excel Magic Trick 0:07:54

Excel Magic Trick 1409: Build PivotTable From 2 Tables without Power Pivot or VLOOKUP

Excel Magic Trick 0:01:43

Excel Magic Trick 1160: Jump To Next Change In Column: Go To Special, Column Difference

Excel Magic Trick 0:08:32

Excel Magic Trick 1198: Median for Each Quarter Using AGGREGATE Function

Excel Magic Trick 0:06:27

Excel Magic Trick 1268: Excel 2016 Auto Detect Relationships: 2 Tables In PivotTable Field List

Excel Magic Tick 0:13:05

Excel Magic Tick 1458: Lookup Price in Multiple Tables: VLOOKUP, INDIRECT & Defined Names

Excel 2013 Tips 0:01:18

Excel 2013 Tips and Tricks - 8

Mr Excel & 0:07:05

Mr Excel & excelisfun Trick 165: Lookup To Find Three Values In A Row

Excel 2010 Pivot 0:02:37

Excel 2010 Pivot Tables Trick By Rohullah

Excel Tips and 0:08:17

Excel Tips and Tricks | Excel Tricks | Excel Magic Tricks | 5 Most useful Excel Tips

Consolidate Data from 0:06:03

Consolidate Data from Multiple Sheets using PivotTables

Комментарии
Автор

Excel Magic Trick 1151: Get Two Tables Into Pivot Table Field List using Excel 2013
See how to Get Two Tables Into Pivot Table Field List using Excel 2013 to create a Report based on Revenue from one table and Category from a second table.
1. (00:08 minute mark) Why you would need two tables in Pivot Table Field List
2. (01:10 minute mark) Convert data to Excel Table
3. (01:30 minute mark) Insert PivotTable and More Tables Button to add second table to Field List. This step adds first table to Data Model.
4. (02:25 minute mark) Drag and Drop Fields From Two Tables
5. (02:33 minute mark) Create Relationship between two tables (to simulate VLOOKUP). This step adds the second table to the data model.
6. (03:59 minute mark) Add Number Formatting without PowerPivot
7. (04:25 minute mark) Discussion about how the PivotTable from Data Model is different from PivotTable create natively in Excel
8. ( minute mark) 
9. ( minute mark) 

excelisfun
Автор

I am so happy, I found your video...that I am in TEARS right now!! You have solved my major project flaw! I think, I will be able to sleep at night again #nomoreovertime

nicolebritton
Автор

can you make like inventory by pcs compose of actual count items vs sales, delivery, transfer in&out, pull out and damage items

princesoler
Автор

Thanks for this great video. I have 2 table in the same sheet exactly similar to what you did. the first table has name, store location, item, amount, and month. The second table has name, age. I created pivot table and then clicked on More tables. Now I can see 2 tables but I did not get an option to create a link between them? How can I fix that?

userme
Автор

Using two tables, does it require a relationship there? For example I am trying to use two different reports. One at the quote stage, One after the quote has been turned into an offer. Do I need some unique identifier in both reports to be able to mash them into one pivot table?

mclovin
Автор

So we can't do this in excel 2010 then?

ThaRubenidas