On the right side of the Manage Relationships dialog, click New… to create the first relationship. If you’re using Excel 2016 or newer, you’ll see a Relationships icon in the Data Tools section of the Data tab of the ribbon.Ĭlick the Relationships icon to open the Manage Relationships dialog. For this example, call the three data sets “Data,” “Products,” and “Customers.” A Table Design tab appears in the ribbon, and the Table Name can be edited in a box on the left side of the ribbon. You can easily change the name of each table before you build the relationships: Select a cell in the table. By default, these three tables will be called Table1, Table2, and Table3. Excel will ask you to verify that your data has a header row. On each of the three worksheets, select the individual data set and press Ctrl+T. Behind the scenes, it will make a data set eligible for use in the Relationships dialog. But those words, “Format as Table,” undersell how much happens when you make a worksheet into a table. The Format as Table icon on the Home tab (or Ctrl+T) sounds like it’s made for quickly formatting a worksheet. Say that you have a large invoice register on Sheet1 with fields like “Product ID” and “Customer Number.” If the data on Sheet2 is a product database and the data on Sheet3 is a customer list, then you can easily build a pivot table from data from all three worksheets without doing a bunch of VLOOKUP formulas to get the data back onto Sheet1.
It was built into Excel 2013, but the relationship-building tools that help make it easy to do first arrived in Excel 2016. The ability to link data from two worksheets debuted as an add-in in Excel 2010.
0 kommentar(er)
