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

SituationUse Power QueryUse XLOOKUP/VLOOKUP
Large data
Repeatable process
One-time quick task
Need reliability
No formulas

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

Pivot Tables in Excel: Fast, Practical Guide

What You’ll Do

  • Prepare data
  • Insert a Pivot Table
  • Drag fields into Rows, Columns, Values, Filters
  • Change how numbers are summarized
  • Sort, filter, and format
  • Add charts and slicers
  • Refresh when data changes

Prepare Data for a Pivot Table

Your data should:

  • Be in a clean table format (no blank rows/columns)
  • Have headers in the first row
  • Contain consistent data types per column

Optional but recommended: Convert to a Table
Insert → Table

This prevents range issues when new data is added.


Insert a Pivot Table

Insert → PivotTable

In the dialog:

  • Select the table/range
  • Choose New Worksheet or Existing Worksheet

Click OK.


Understand the PivotTable Fields Pane

  • Appears on the right when you click inside the pivot area
  • Disappears when you click outside
  • Comes back when you click inside again

You’ll drag fields into:

  • Rows
  • Columns
  • Values
  • Filters

Analyze Data with Rows, Columns, and Values

Drag columns from your table into the areas.

Rule of thumb:
Anything you want calculated → goes into Values

By default, numeric fields become Sum of …


Change How Values Are Calculated

Right-click any value cell → Summarize Values By

Options:

  • Sum
  • Count
  • Average
  • Max / Min
  • Product

Sort Data

Right-click a number (e.g., Sum of Sales) → Sort

  • Smallest to Largest
  • Largest to Smallest

Use Rows vs Columns for Dimensions (e.g., Region)

Drag a field like Region into:

  • Columns → creates separate columns per region
  • Rows → creates sub-rows under the main row

Reorder fields in Rows to change hierarchy.

To remove a field → drag it out.


Use Filters

Drag a field into Filters.

A dropdown appears above the pivot table.

  • Enable the checkbox at the bottom to select multiple items.

Show Values As (Percentages, etc.)

Drag the same field into Values again (so it appears twice).

Right-click the second one → Show Values As

Example:

  • % of Column Total

Now you have both Sum and %.


Adjust Look and Feel

  • Change style/colors: Design tab
  • Remove grand totals:
    Design → Grand Totals → Off for Rows and Columns
  • Rename column headers directly

Pivot Charts and Slicers

Create chart: Insert → Chart
Charts stay linked to the pivot table.

Add slicer: PivotChart Analyze → Insert Slicer


Refresh When Data Changes

Pivot tables do not auto-update.

After adding data:
PivotChart Analyze → Refresh

If you used an Excel Table, the new rows are included after refresh.

Excel Error Types You Should Recognize Instantly

Excel errors are signals. Knowing what they mean lets you fix formulas fast.


#N/A — No Result Found (Not Always an Error)

Meaning: The formula ran correctly, but no match or no result was found.

Common cause:

  • Lookup value doesn’t exist (VLOOKUP, XLOOKUP, MATCH)
  • FILTER returns nothing

Example

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

If A2 isn’t found → #N/A

Fix (if expected sometimes):

=IFNA(XLOOKUP(...), "Not Found")

#VALUE! — Wrong Data Type

Meaning: Excel can’t perform the operation with the data provided.

Common causes:

  • Math on text
  • Incorrect function arguments
  • Hidden spaces in numbers

Examples

="10" + 5 → #VALUE!
=LEFT(A2, "two") → #VALUE!

Fix

  • Convert text to numbers
  • Check argument types
  • Use TRIM / VALUE

#REF! — Invalid Cell Reference

Meaning: The formula refers to a cell that no longer exists.

Common cause:

  • Deleting a column/row used in a formula

Example

=VLOOKUP(A2, A:C, 3, FALSE)

If column C is deleted → #REF!

Fix

  • Undo deletion
  • Rebuild the reference
  • Use Tables or structured references to prevent this

Quick Summary

  • #N/A → Nothing found (often expected in lookups)
  • #VALUE! → Wrong data type used in formula
  • #REF! → Reference to deleted/missing cells

Recognize these fast → fix formulas faster.

Excel Text Functions: LEFT, RIGHT, MID, TRIM, LEN, CONCAT

Working with text is a core Excel skill for data cleaning, analytics, and reporting. These functions help you extract, clean, measure, and combine text quickly.


Example Table

First NameLast NameAddressPhoneDate
JohnDoe123 Main St555-123-456701/15/2024
JaneSmith456 Oak Ave555-987-654312/05/2023

Assume this data starts in row 2 (A2:E3).


LEFT, RIGHT, MID — Extract Characters

FunctionPurposeExampleResult
LEFT(text, num_chars)First characters=LEFT(D2,3)555 (area code)
RIGHT(text, num_chars)Last characters=RIGHT(D2,4)4567
MID(text, start, num_chars)Middle characters=MID(TEXT(E2,"MM/DD/YYYY"),1,2)01 (month)

Use these when parts of a cell contain meaningful pieces of data.


TRIM — Remove Extra Spaces

Extra spaces break formulas and comparisons.

If a cell contains:

" John "

Use:

=TRIM(A2)

Removes leading and trailing spaces.

Important: If you delete the original column, the TRIM result breaks.
Use Paste Special → Values to make it permanent.


LEN — Count Characters

Counts all characters, including spaces and symbols.

=LEN(A2) → 4
=LEN(C2) → counts full address length

Useful for:

  • Validating data length
  • Preparing fixed-width formats
  • Finding hidden spaces

CONCAT and & — Combine Text

Using &

=A2 & " " & B2

→ John Doe

Using CONCAT

=CONCAT(A2," ",B2)

→ John Doe

Both work the same. CONCAT is newer and handles ranges well.


Key Takeaways

  • LEFT / RIGHT / MID → extract parts of text
  • TRIM → remove extra spaces that cause errors
  • LEN → count characters
  • CONCAT / & → combine text
  • Use Paste Special → Values when you want results to stay permanent

Excel FILTER Function — Crash Course

Purpose: Return all rows that match a condition.
Unlike VLOOKUP/XLOOKUP, FILTER returns multiple results by design.


Syntax

FILTER(array, include, [if_empty])
ArgumentWhat it means
arrayThe range or table you want to filter from
includeThe condition (what to keep)
[if_empty]Optional value if no results are found

Step 1 — Select the Data to Filter (array)

This is the table or range you want results pulled from.

You can:

  • Highlight a range like A2:F200
  • Or use a table like Table1 (recommended)

Step 2 — Define the Condition (include)

This is the rule Excel uses to decide which rows to return.

Think: Which rows do I want to keep?


Example 1 — Basic Filter

Return all rows where the product is 85% Dark Bars:

=FILTER(Table1, Table1[Product]="85% Dark Bars")

Excel returns every matching row.


Example 2 — Dynamic Filter (Better)

Make the filter value changeable from a cell.

If cell R2 contains a product name:

=FILTER(Table1, Table1[Product]=R2)

Now you can change R2 to instantly update the results.


Why FILTER is Powerful

  • Returns all matches, not just the first
  • Replaces complex combinations of VLOOKUP/XLOOKUP workarounds
  • Works great with Excel Tables
  • Fully dynamic when tied to a cell reference
  • Cleaner and easier than advanced filters or pivot tables for simple needs

Optional: Handle Empty Results

Avoid errors if no matches exist:

=FILTER(Table1, Table1[Product]=R2, "No results found")

When to Use FILTER

Use FILTER when:

  • You need every instance of a value
  • You want results to auto-update based on a cell
  • You want a fast, clean way to subset a table

If you only need one value → use XLOOKUP.
If you need all values → use FILTER.

VLOOKUP vs XLOOKUP vs INDEX+MATCH vs FILTER — When to Use Each

Excel has multiple ways to perform lookups. They solve similar problems, but each is better in different situations.


Quick Comparison

FunctionBest ForKey LimitationReturns Multiple Matches?Works in Old Excel?
XLOOKUPModern default lookupExcel 365 / 2021+ only❌ No
VLOOKUPLegacy spreadsheetsLookup column must be first❌ No
INDEX+MATCHFlexible, backward compatibleMore complex to write❌ No
FILTERReturning all matchesExcel 365 / 2021+ only✅ Yes

Important Behavior (All Lookups)

By default:

  • XLOOKUP
  • VLOOKUP
  • INDEX+MATCH

…return the first occurrence of a match.

You can make XLOOKUP return the last occurrence using:

search_mode = -1

But if you need every match, none of these are correct → use FILTER.


Use XLOOKUP (Default Choice)

=XLOOKUP(A2, A:A, D:D)

Use when:

  • You’re in Excel 365 / 2021+
  • You want the cleanest, simplest formula
  • You don’t want to count column indexes
  • You want better built-in error handling

XLOOKUP is designed to replace VLOOKUP and HLOOKUP.


Use VLOOKUP (Legacy Only)

=VLOOKUP(A2, A:D, 4, FALSE)

Use when:

  • You’re maintaining old spreadsheets
  • XLOOKUP is not available

Limitations:

  • Lookup column must be first
  • Breaks when columns move
  • Requires column index numbers

Use INDEX + MATCH (Power + Compatibility)

=INDEX(D:D, MATCH(A2, A:A, 0))

Use when:

  • You’re in Excel 2019 or earlier
  • You need flexibility VLOOKUP can’t provide
  • You want a dynamic lookup that doesn’t break when columns move
  • You’re working with large datasets (50,000+ rows)

INDEX+MATCH works in every version of Excel and does not require Microsoft 365.

Functionally, INDEX+MATCH and XLOOKUP achieve the same result in different ways.


Use FILTER (Return All Matches)

=FILTER(A:D, A:A=A2)

Use when:

  • A value appears multiple times
  • You need every matching row returned
  • You’re in Excel 365 / 2021+

This is something XLOOKUP, VLOOKUP, and INDEX+MATCH cannot do.


Summary — When to Use Each

  • XLOOKUP → Modern, easiest, default choice
  • VLOOKUP → Only for legacy files
  • INDEX+MATCH → Best for compatibility and flexibility
  • FILTER → When you need multiple matching results

For modern Excel: XLOOKUP first, FILTER when needed.
For older Excel: INDEX+MATCH over VLOOKUP.

INDEX + MATCH in Excel — Crash Course

INDEX+MATCH is a powerful lookup pattern used for flexible, dynamic lookups in Excel.
You’ll often hear it said as “INDEX+MATCH”, but in practice it’s really MATCH inside INDEX.

You can use XLOOKUP instead in modern Excel. They achieve similar results with different tradeoffs.


When to Use INDEX + MATCH

Use INDEX+MATCH when:

  • The lookup column is not the first column
  • You want a dynamic, non-breaking lookup
  • You want more control than VLOOKUP allows
  • You’re working in older Excel versions without XLOOKUP

MATCH — Finds the Position (Row Number)

MATCH returns the position of a value inside a single column (or row).

Syntax

MATCH(lookup_value, lookup_array, match_type)
  • lookup_value → what you’re searching for
  • lookup_array → must be one column or one row
  • match_type → usually 0 for exact match

Important Rules

  • Output is 1-indexed
  • The row number depends on the size of the range

Example

If value is in C10:

  • MATCH("Apples", C1:C20, 0) → 10
  • MATCH("Apples", C10:C20, 0) → 1

Same cell, different output because the range changed.


INDEX — Returns the Value at a Position

INDEX returns a value from a table based on row (and optionally column).

Syntax

INDEX(array, row_num, [column_num])

Instead of hardcoding row_num, you place the MATCH function here.


Basic INDEX + MATCH Example

Example Table

ProductPrice
Apples2.50
Oranges3.00
Carrots1.80

Goal: Find the Price of “Carrots”.

=INDEX(B2:B4, MATCH("Carrots", A2:A4, 0))

What happens:

  1. MATCH finds the row where “Carrots” exists
  2. INDEX returns the price from that row

The lookup and return columns are different — this is the power.


Critical Rule: Ranges Must Align

The MATCH range and INDEX range must cover the same rows.

If MATCH returns row 3, INDEX must also have a 3rd row to pull from.


Advanced: Lookup with Two Criteria (Row + Column)

If you need to find a value based on two parameters (e.g., Cookie Type and Country):

  • Use MATCH twice
  • Use INDEX with both results
=INDEX(Table1,
MATCH(E7, Table1[Cookie Type], 0),
MATCH(E8, Table1[#Headers], 0))

One MATCH finds the row. The other finds the column.


Advanced MATCH: Multiple Criteria with &

You can combine fields to match on multiple conditions.

=MATCH(E7&E8,
Table1[Cookie Type]&Table1[Country],
0)
  • Lookup value: E7&E8
  • Lookup array: combined columns with &

INDEX + MATCH vs XLOOKUP

Both solve the same problem.

  • INDEX+MATCH → more manual control, works everywhere
  • XLOOKUP → simpler syntax, modern Excel

Key Takeaways

  • MATCH finds the position
  • INDEX returns the value at that position
  • MATCH must use a single row/column
  • INDEX and MATCH ranges must align
  • You can use two MATCH functions for two criteria
  • You can combine fields with & for multi-condition matching

XLOOKUP vs VLOOKUP in Excel (The Right Way to Do Lookups)

If XLOOKUP exists in your version of Excel, you should almost always use it instead of VLOOKUP. It’s simpler, safer, and doesn’t break when your table changes.


Simple Example Table

Product NameCategoryPriceBoxes Shipped
ApplesFruit2.50120
OrangesFruit3.0095
CarrotsVeggie1.80150
BananasFruit2.20130

Goal: Look up a Product Name and return Boxes Shipped.

Assume the product you’re searching for is in cell F2.


The VLOOKUP Way (Old Method)

=VLOOKUP(F2, A:D, 4, FALSE)
  • F2 → product name to find
  • A:D → table range
  • 4 → Boxes Shipped column
  • FALSE → exact match

The Problem with VLOOKUP

  • Lookup column must be first
  • You must count column positions
  • Insert a column → formula breaks
  • Not flexible

The XLOOKUP Way (Modern Method)

=XLOOKUP(F2, A:A, D:D)
  • Lookup column does not need to be first
  • No column index numbers
  • Easier to read and maintain

Best Practice: Convert to an Excel Table (Ctrl + T)

Convert the data into a Table. Assume the table is named Table1.

Best Formula You Can Write

=XLOOKUP(F2, Table1[Product Name], Table1[Boxes Shipped])

Why this is best:

  • Expands automatically when rows are added
  • Doesn’t break if columns move
  • Uses names instead of positions
  • Fully dynamic

This is what advanced Excel users do.


If You Are Forced to Use VLOOKUP

You can still improve it with structured references:

=VLOOKUP(F2, Table1, 4, FALSE)

But you still depend on column positions.


Strong Alternative: INDEX + MATCH

=INDEX(Table1[Boxes Shipped], MATCH(F2, Table1[Product Name], 0))

Dynamic and very reliable.


References: Relative vs Absolute vs Table

TypeExampleProblem
RelativeA2:D6Breaks when dragged
Absolute$A$2:$D$6Breaks when rows change
TableTable1[Product Name]Auto-updates (best)

Press F4 to make a reference absolute.


Common Lookup Errors

ErrorMeaning
#N/AValue not found
#REF!Column moved/deleted
#VALUE!Wrong input type
#NAME?Misspelled function

Key Takeaways

  • Use XLOOKUP, not VLOOKUP
  • Convert data to a Table
  • Use structured references
  • Avoid column index guessing
  • INDEX/MATCH is still excellent

Excel Counting & Aggregation Functions: Quick Reference

Summary of Functions

FunctionDescription
COUNTCounts the number of cells within the range that have numeric values
COUNTACounts the number of non-empty cells in a range (values can be numbers, text, or dates) (i.e. NOT blank)
COUNTBLANKCounts the number of blank/empty cells in a range
COUNTIFCounts the number of cells meeting a single condition
COUNTIFSCounts number of rows meeting multiple conditions
SUMIFAdds values in a range meeting one condition
SUMIFSAdds values meeting multiple conditions
AVERAGEIFCalculates the average of values meeting a condition

Example Table

NameDepartmentSalesStatus
JohnMarketing100Active
JaneMarketing150Inactive
MikeSales200Active
SaraSales250Active
TomMarketingActive

Basic Counting

COUNT → counts numbers only

=COUNT(C2:C6)  → 4

COUNTA → counts all non-empty cells

=COUNTA(C2:C6) → 4 (counts numeric and text if present)

COUNTBLANK → counts empty cells

=COUNTBLANK(C2:C6) → 1

Conditional Counting

COUNTIF → counts cells that meet one condition

=COUNTIF(B2:B6,"Marketing") → 3
=COUNTIF(D2:D6,"Active") → 4

COUNTIFS → counts cells that meet multiple conditions

=COUNTIFS(B2:B6,"Marketing",D2:D6,"Active") → 2

Conditional Summing

SUMIF → sum values based on one condition

=SUMIF(B2:B6,"Sales",C2:C6) → 450

SUMIFS → sum values based on multiple conditions

=SUMIFS(C2:C6,B2:B6,"Marketing",D2:D6,"Active") → 100

Conditional Average

AVERAGEIF → average values based on a condition

=AVERAGEIF(B2:B6,"Marketing",C2:C6) → 125

Key Takeaways

  • COUNT / COUNTA / COUNTBLANK → basic counting of numbers, non-empty, or blank cells
  • COUNTIF / COUNTIFS → count based on one or more conditions
  • SUMIF / SUMIFS → sum values conditionally
  • AVERAGEIF → average values based on a condition
  • Use these functions for quick data aggregation, reporting, and analytics

Excel IF, IFERROR, IFNA, and IFS — When and How to Use Each

Logical functions are essential in Excel for data cleaning, reporting, and analytics. These four functions help you apply logic and handle errors cleanly:

  • IF → basic logic
  • IFS → multiple conditions
  • IFERROR → catch any error
  • IFNA → catch only #N/A

IF — Basic Logical Test

Use IF when you want Excel to return different results based on a condition.

=IF(A2>50,"Pass","Fail")

Structure

IF(logical_test, value_if_true, value_if_false)

Common uses: thresholds, flags, labels, categories.


IFS — Multiple Conditions (Cleaner than Nested IFs)

Use IFS when you have many conditions to check.

Instead of nested IFs:

=IF(A2>90,"A",IF(A2>80,"B",IF(A2>70,"C","D")))

Use:

=IFS(
A2>90,"A",
A2>80,"B",
A2>70,"C",
TRUE,"D"
)

Cleaner and easier to read.


IFERROR — Handle Any Excel Error

Use IFERROR to replace any error (#DIV/0!#N/A#VALUE!, etc.).

=IFERROR(A2/B2,"Error")

Structure

IFERROR(value, value_if_error)

Common in calculations, lookups, and formulas that might break.


IFNA — Handle Only #N/A Errors

Use IFNA when working with lookups and you only want to catch missing matches.

=IFNA(VLOOKUP(A2,Sheet2!A:B,2,FALSE),"Not Found")

This keeps other errors visible while handling only #N/A.

Structure

IFNA(value, value_if_na)

When to Use Each

FunctionUse When
IFYou have one condition
IFSYou have multiple conditions
IFERRORYou want to hide all errors
IFNAYou only want to handle missing lookup results

Common Analytics Examples

Categorize values

=IF(A2>=100,"High","Low")

Grade ranges

=IFS(A2>=90,"A", A2>=80,"B", TRUE,"C")

Safe division

=IFERROR(A2/B2,0)

Lookup with fallback

=IFNA(XLOOKUP(A2,A:A,B:B),"Missing")

Key Takeaways

These are core functions for data cleaning and reporting in Excel

  • IF handles simple logic
  • IFS replaces nested IF statements
  • IFERROR hides all errors
  • IFNA hides only #N/A from lookups