Yen Jin has attended:
Excel Pivot Tables course
Data Model
Hi, how do I link tables (in the same workbook) into a data model using connections. My only options are connections files from my network or computer. I could not find the instructions in the handbook given out during my training
RE: Data Model
Hi Yen Jin,
Thank you for the forum question.
We have the information that you are using Excel 2010. Is that right?
2010 can work with different connections and in newer version of Excel you have more options.
To create connections to external sources you can do it, when you create a PivotTable, from MS Query and from Connections from the Data tab.
In Excel 2010 you can only create a datamodel by using PowerPivot.
You can generate relationships between multiple tables in Excel 2010, you have got from external connections. This can generate a new table based on data from all the related tables from which you can create your PivotTables.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Data Model
Hi Jens Bonde,
Just to clarify, for Excel 2010, I can't connect tables from the same file (different tabs of the worksheet) into a data model? I can only create connections to external sources? So the only way I could do it is VLookUP I guess. I remembered I could do it with Excel 2016 during the training.
Regards,
Yen Jin
RE: Data Model
Hi Yen Jin,
You have to install PowerPivot to create a data model in Excel 2010. You do not have the relationship tool on the Data tab in 2010. You can in 2010 create relationship by using MS Query. Both tools can connect tables from the same workbook.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
