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

1. Copy the cleaned column
2. 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:

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

1. Select table (Ctrl + A)
2. Home → Find & Select → Go To Special → Blanks
3. Type value in formula bar
4. 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

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

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 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.

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

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

LEFT, RIGHT, MID — Extract Characters

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

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

Syntax

FILTER(array, include, [if_empty])

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.