98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Data Model
Data Model
Resolved · High Priority · Version 2010
Wed 20 Dec 2017, 14:29Delegate Yen Jin said...
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
Thu 21 Dec 2017, 09:48Trainer Jens said...
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
Fri 22 Dec 2017, 09:31Delegate Yen Jin said...
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
Fri 22 Dec 2017, 10:00Trainer Jens said...
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
Fri 29 Dec 2017: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:View a unique listYou have a column with hundreds of entries, and you need to see what unique items are entered in it. Select any cell in that column, hold down Alt and press the down arrow: Excel produces an alphabetically-sorted list of unique entries in that column. |
