3 Ways to Swap Columns in Excel (Fast)

Reordering columns is common during data cleaning and reporting. These are the 3 fastest methods.

Method 1 — Cut & Insert Cells (Most Precise)

Best when you want exact placement.

Select the entire column
Press Ctrl + X (Cut)
Click the column you want it placed to the left of
Right click → Insert Cut Cells

The column moves directly into that position.

Method 2 — Shift Key + Drag (Fastest)

Best for quick manual reordering.

Select the column
Hold Shift
Drag the column left or right
Release when you see the vertical insertion line

Excel inserts the column at that spot.

Method 3 — SORTBY Function (Dynamic Order)

Best when you want column order to change automatically based on logic.

Use when columns should be rearranged based on a rule (e.g., sort months, metrics, priority).

Example:

=SORTBY(A1:F100, A1:F1, 1)

A1:F100 → your data
A1:F1 → header row (used to determine order)
1 → ascending order

This doesn’t move columns physically. It creates a reordered view of the data that updates automatically.

When to Use Each

Method	Use When
Cut & Insert	You need exact, permanent placement
Shift + Drag	You want speed
SORTBY	You want dynamic, formula-driven column order

Summary

Cut & Insert = precise and safe
Shift + Drag = quickest manual method
SORTBY = dynamic, automated column order

Excel Data Cleaning Essentials (Fast Checklist)

These are the small actions that prevent errors, speed up analysis, and make data usable.

Auto-Fit Columns and Rows

Fix cramped or cut-off data instantly.

Home → Format → AutoFit Column Width
Home → Format → AutoFit Row Height
Or double-click the edge between column letters / row numbers

Prevent `#REF!` Errors (Paste as Values)

If you clean data with formulas (TRIM, LEFT, etc.) and later delete the original column, formulas break.

Fix: convert results to values

Copy the cleaned column
Right click → Paste Special → Values

Now it’s permanent and no longer tied to the reference.

Find & Replace (Power Move)

Quick cleanup across the sheet.

Example: remove anything inside parentheses

Find: (*)
Replace: (leave blank)

Removes all text in parentheses.

Text to Columns

Split messy data into structured columns.

Common uses:

Split full name into first/last
Split comma-separated fields
Split IDs or codes

Data → Text to Columns

Core Text Cleanup Functions

Use these constantly when cleaning text:

Function	Purpose
`UPPER`	All caps
`LOWER`	All lowercase
`PROPER`	Capitalize words
`TRIM`	Remove extra spaces
`LEFT` / `RIGHT`	Extract characters

Identify Duplicates (Visual)

Quickly see duplicate IDs.

Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values

Choose a highlight style → duplicates are instantly visible.

Remove Duplicates (Actual Cleanup)

Data → Remove Duplicates

Select entire table first
Check My data has headers

Fill All Empty Cells at Once

Select table (Ctrl + A)
Home → Find & Select → Go To Special → Blanks
Type value in formula bar
Press Ctrl + Enter

Fills every blank cell at once.

Remove Gridlines / Hide Formula Bar (Presentation)

View → Uncheck Gridlines
View → Uncheck Formula Bar

Makes sheets cleaner for sharing.

Always Use Tables (Ctrl + T)

Convert raw data into a Table.

Ctrl + T → Create Table

Benefits:

Filter buttons on every column
Automatic range expansion
Table styles (Table Design → Table Styles)
Add a Total Row with built-in aggregations

Key Takeaways

Good data cleaning in Excel is mostly:

Auto-fitting
Removing duplicates
Fixing text
Converting formulas to values
Using Tables instead of raw ranges

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:

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

Method 1 — Cut & Insert Cells (Most Precise)

Method 2 — Shift Key + Drag (Fastest)

Method 3 — SORTBY Function (Dynamic Order)

When to Use Each

Summary

Auto-Fit Columns and Rows

Prevent #REF! Errors (Paste as Values)

Find & Replace (Power Move)

Text to Columns

Core Text Cleanup Functions

Identify Duplicates (Visual)

Remove Duplicates (Actual Cleanup)

Fill All Empty Cells at Once

Remove Gridlines / Hide Formula Bar (Presentation)

Always Use Tables (Ctrl + T)

Key Takeaways

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 &

Prevent `#REF!` Errors (Paste as Values)

`#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 `&`