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:

Power Query (best, modern way)
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:

Select Table 1
Select Table 2
Click the matching key column in both
Choose join type (usually Left Join)
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

Situation	Use Power Query	Use 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 Name	Last Name	Address	Phone	Date
John	Doe	123 Main St	555-123-4567	01/15/2024
Jane	Smith	456 Oak Ave	555-987-6543	12/05/2023

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

LEFT, RIGHT, MID — Extract Characters

Function	Purpose	Example	Result
`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])

Argument	What it means
array	The range or table you want to filter from
include	The 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

Function	Best For	Key Limitation	Returns Multiple Matches?	Works in Old Excel?
XLOOKUP	Modern default lookup	Excel 365 / 2021+ only	❌ No	❌
VLOOKUP	Legacy spreadsheets	Lookup column must be first	❌ No	✅
INDEX+MATCH	Flexible, backward compatible	More complex to write	❌ No	✅
FILTER	Returning all matches	Excel 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

Product	Price
Apples	2.50
Oranges	3.00
Carrots	1.80

Goal: Find the Price of “Carrots”.

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

What happens:

MATCH finds the row where “Carrots” exists
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 Name	Category	Price	Boxes Shipped
Apples	Fruit	2.50	120
Oranges	Fruit	3.00	95
Carrots	Veggie	1.80	150
Bananas	Fruit	2.20	130

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

Type	Example	Problem
Relative	`A2:D6`	Breaks when dragged
Absolute	`$A$2:$D$6`	Breaks when rows change
Table	`Table1[Product Name]`	Auto-updates (best)

Press F4 to make a reference absolute.

Common Lookup Errors

Error	Meaning
`#N/A`	Value 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

Function	Description
`COUNT`	Counts the number of cells within the range that have numeric values
`COUNTA`	Counts the number of non-empty cells in a range (values can be numbers, text, or dates) (i.e. NOT blank)
`COUNTBLANK`	Counts the number of blank/empty cells in a range
`COUNTIF`	Counts the number of cells meeting a single condition
`COUNTIFS`	Counts number of rows meeting multiple conditions
`SUMIF`	Adds values in a range meeting one condition
`SUMIFS`	Adds values meeting multiple conditions
`AVERAGEIF`	Calculates the average of values meeting a condition

Example Table

Name	Department	Sales	Status
John	Marketing	100	Active
Jane	Marketing	150	Inactive
Mike	Sales	200	Active
Sara	Sales	250	Active
Tom	Marketing		Active

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

Function	Use When
`IF`	You have one condition
`IFS`	You have multiple conditions
`IFERROR`	You want to hide all errors
`IFNA`	You 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

Merge / Append Sheets or Tables in Excel (Mac)

Method 1 — Power Query (Recommended)

Open Power Query

Import Data

Merge Queries (like a SQL JOIN)

Append Queries (stack tables)

Load Back to Excel

Method 2 — Classic XLOOKUP / VLOOKUP

Scenario (Merge)

Use XLOOKUP (preferred)

If forced to use VLOOKUP

When to Use Each

Key Takeaway

What You’ll Do

Prepare Data for a Pivot Table

Insert a Pivot Table

Understand the PivotTable Fields Pane

Analyze Data with Rows, Columns, and Values

Change How Values Are Calculated

Sort Data

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

Use Filters

Show Values As (Percentages, etc.)

Adjust Look and Feel

Pivot Charts and Slicers

Refresh When Data Changes

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

#VALUE! — Wrong Data Type

#REF! — Invalid Cell Reference

Quick Summary

Example Table

LEFT, RIGHT, MID — Extract Characters

TRIM — Remove Extra Spaces

LEN — Count Characters

CONCAT and & — Combine Text

Using &

Using CONCAT

Key Takeaways

Syntax

Step 1 — Select the Data to Filter (array)

Step 2 — Define the Condition (include)

Example 1 — Basic Filter

Example 2 — Dynamic Filter (Better)

Why FILTER is Powerful

Optional: Handle Empty Results

When to Use FILTER

Quick Comparison

Important Behavior (All Lookups)

Use XLOOKUP (Default Choice)

Use VLOOKUP (Legacy Only)

Use INDEX + MATCH (Power + Compatibility)

Use FILTER (Return All Matches)

Summary — When to Use Each

When to Use INDEX + MATCH

MATCH — Finds the Position (Row Number)

Syntax

Important Rules

INDEX — Returns the Value at a Position

Syntax

Basic INDEX + MATCH Example

Example Table

Critical Rule: Ranges Must Align

Advanced: Lookup with Two Criteria (Row + Column)

Advanced MATCH: Multiple Criteria with &

INDEX + MATCH vs XLOOKUP

Key Takeaways

Simple Example Table

The VLOOKUP Way (Old Method)

The Problem with VLOOKUP

The XLOOKUP Way (Modern Method)

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

Best Formula You Can Write

If You Are Forced to Use VLOOKUP

Strong Alternative: INDEX + MATCH

References: Relative vs Absolute vs Table

Common Lookup Errors

Key Takeaways

Example Table

Basic Counting

Conditional Counting

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

`#VALUE!` — Wrong Data Type

`#REF!` — Invalid Cell Reference

CONCAT and `&` — Combine Text

Using `&`

Step 1 — Select the Data to Filter (`array`)

Step 2 — Define the Condition (`include`)

Advanced MATCH: Multiple Criteria with `&`