Excel Merge / Append Sheets/Tables Together (on Mac)

Merge / Append Sheets or Tables in Excel (Mac)

Environment used:
Microsoft® Excel for Mac • Version 16.106 • Microsoft 365

Merging data works differently across Excel versions and OSs. On Mac (365), you have two main methods:

1. Power Query (best, modern way)
2. Classic XLOOKUP / VLOOKUP (manual method)

Method 1 — Power Query (Recommended)

Best for:

• Large datasets
• Repeatable workflows
• Clean, reliable merges/appends
• No formulas needed

Open Power Query

Data tab → Get Data (Power Query) → Launch Power Query Editor

Import Data

Use Get Data to import each sheet/table you want to combine.

Each import becomes a query.

Merge Queries (like a SQL JOIN)

Use when:

• You have a common key (e.g., Order ID, Product ID)
• You want columns from both tables combined side-by-side

Home → Merge Queries → Merge Queries as New

Steps:

1. Select Table 1
2. Select Table 2
3. Click the matching key column in both
4. Choose join type (usually Left Join)
5. Expand the new columns

Append Queries (stack tables)

Use when:

• Tables have the same columns
• You want to stack rows on top of each other

Home → Append Queries → Append Queries as New

Load Back to Excel

Click Close & Load to place the result into a sheet.

When source data changes → Refresh. No rework.

Method 2 — Classic XLOOKUP / VLOOKUP

Best for:

• Small datasets
• Quick one-off tasks
• When Power Query isn’t an option

Scenario (Merge)

You have:

• Sheet1: Product ID, Product Name
• Sheet2: Product ID, Boxes Shipped

You want Boxes Shipped added to Sheet1.

Use XLOOKUP (preferred)

=XLOOKUP(A2, Sheet2!A:A, Sheet2!B:B)

• A2 = Product ID in Sheet1
• Looks in Sheet2 Product ID column
• Returns Boxes Shipped

Copy down.

If forced to use VLOOKUP

=VLOOKUP(A2, Sheet2!A:B, 2, FALSE)

More fragile:

• Breaks if columns move
• Uses column index numbers

When to Use Each

Key Takeaway

On Excel for Mac (365):

• Power Query is the correct, scalable way to merge or append tables
• XLOOKUP/VLOOKUP is a quick, manual workaround for small jobs