Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- "Save As" Shortcut in Excel
- Accounting Number Format in Excel
- Add-Ins in excel
- Add Filter in Excel
- Advanced Filter in Excel
- Auto Filter In Excel
- Auto Format Excel
- AutoFill in Excel
- Analysis ToolPak in Excel
- ANOVA in Excel
- Border in Excel
- Checkbox in Excel
- Check Mark in Excel (? Tick Symbol)
- Combo Box in Excel and VBA
- Conditional Formatting in Excel
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- Conditional Formatting Based on Another Cell Value
- Conditional Formatting in Pivot Table
- Consolidate Data in Excel
- Comma Style in Excel
- CSV vs Excel
- Data Bars in Excel
- Data Table in Excel
- Data Validation Excel
- Data Model in Excel
- Developer Tab in Excel
- Descriptive Statistics in Excel
- Dynamic Named Range in Excel
- Drawing in Excel
- Excel Fill Handle
- Excel Fill Down
- Error Bars in Excel
- Excel Forms for Data Entry
- Excel Tables
- Excel Power View
- Exponential Smoothing in Excel
- Filters in Excel
- Flash Fill in Excel
- Freeze Panes in Excel
- Freeze Columns in Excel
- Freeze Cells in Excel
- Format Painter in Excel
- Shortcut for Format Painter in Excel
- F-Test in Excel
- Goal Seek in Excel
- Gridlines in Excel
- Heat Map in Excel
- 3D Maps in Excel
- Header and Footer in Excel
- Insert Button in Excel
- Insert / Draw Line in Excel
- Insert Function in Excel
- List Box in Excel VBA
- Lock Cells in Excel
- Macros in Excel
- Enable Macros in Excel
- Merge and Center in Excel
- Merge Cells in Excel
- Merge Tables in Excel
- Name Box in Excel
- Name Range in Excel
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Protect Workbook in Excel
- Pivot Table in Excel
- Pivot Table Examples
- Pivot Table Filter
- Pivot Table Slicer
- Paste Special in Excel (With Top 10 Shortcuts)
- Quick Analysis Tools in Excel
- Radio Button in Excel
- Recording Macros in Excel
- Regression Analysis in Excel
- Scenario Manager in Excel
- Scroll Bars in Excel
- Scroll Lock in Excel
- Slicers in Excel
- Solver in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Sort Data in Excel
- Sparklines in Excel
- Spell Check in Excel
- Split Panes in Excel (Horizontally, Vertically, Cross Split)
- Status Bar in Excel
- Text to Columns in Excel
- Timeline in Excel
- Toolbar on Excel
- Track Changes in Excel
- Trend Line in Excel
- Two-Variable Data Table in Excel
- Watch Window in Excel
- Wrap Text in Excel
- XML in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tips (178+)
- VBA (162+)
When we work in excel it is not definite that data will be in a single worksheet, it can be in multiple worksheets in multiple tables, if we want to merge tables there are various methods to do that so that we can have data in a single table and this is known as merging tables in excel, this can be done by using VLOOKUP or INDEX and MATCH functions.
Excel Merge Table (Table of Contents)
Merge Tables in Excel
Sometimes while analyzing the data, we might gather all the necessary information in a single worksheet. It’s a very common problem or situation when data is divided across many worksheets or workbooks. There are many ways to merge the data from multiple tables into one table in excel.
How to Merge 2 Tables in Excel?
We have given Customer data city wise in two tables. We have taken 20 records for this.
Sheet 1: Table 1: CustomerInfo
Sheet 2: Table 2: ProductDetails
In both the tables, Order No. is the common information on which basis we will create a relationship between them.
Below are the steps for merging these two tables:
- Click on any cell in the Customer Info table. Go to INSERT tab and click on Table option under Tables section. Refer below screenshot.
- Then Create table dialog box will appear. Our table “CustomerInfo” has column headers, hence the checkbox “My table has headers” should be checked. Refer below screenshot.
- It will convert our data into a table format. Now, click on the Table Name field under the Properties section and give the name of this table as “Customer_info”.
- Follow the same steps for another table “ProductDetails”. We have given the name “Products” to another table. Refer below screenshot.
- Click on somewhere on the Customer_Info table then, Go to Insert tab and click on Pivot Table option under Tables section.
- A dialog box for Create Pivot Table will appear. Tick on Checkbox “Add this data to the Data Model” as shown in below screenshot.
- Click on OK then, It will open a new sheet with a new Pivot Table Fields section in the right side as shown in below screenshot.
- Click on ALL tab in the Pivot Table Field section and it will display all the tables created by us. Refer below screenshot.
- Now click on Relationships option under the Calculations section as shown in below screenshot.
- It will open a dialog box for creating a relationship between these tables. Click on the New button. Refer below screenshot.
- It will again open a dialog box as shown below and created tables are listed here.
- As there is one field “Order No.” is common in both the tables, hence we will create a relationship between these tables by using this common field/column.
- Select Customer_Info under Tables section and Order No. field under Column Section. Refer below screenshot.
- Select another table Products under Related table section and select Order No. field under Related column section. Refer below screenshot.
- Primary Key is the unique values that appear once in the table then, Click on OK. It will display the relationship as shown in below screenshot.
- Now we can drag & drop field accordingly to see the result. Click on Customer_Info table as shown in below screenshot.
- Drag fields Order No. , Customer Name and city under Rows box.
- Drag Age field under Filter box.
- Drag Product field under Column Box and Values box for the count of products.
The final result is below:
Accordingly, as per your requirement, you can drag & drop the fields.
Things to Remember About Merge 2 Tables in Excel
- You can merge more than two tables using this process.
- There should be one column common in each table.
- That one common column will work as a primary key in this process, hence this field should have unique values.
This has been a guide to Merge Tables in Excel. Here we discuss how to Merge 2 Tables in Excel by matching a column along with practical examples and downloadable excel template. You may learn more about excel from the following articles –