My Excel Journey

Sunday, 26 April 2026

7 Powerful Ways to Remove Duplicate Data in Excel (Beginner to Advanced)

Duplicate data in Excel can lead to incorrect reports, wrong calculations, and poor decision-making. Whether you're working with small datasets or large reports, knowing how to remove duplicates in Excel is an essential skill.

In this guide, you'll learn 7 easy and powerful methods to remove duplicate data in Excel, from basic tools to advanced techniques.

Let’s explore all 7 methods 👇

Remove Duplicates Tool (Easiest Method)

Excel's built-in tool is the fastest path from messy to clean. It permanently removes duplicate rows in seconds — perfect for quick data cleanup.

Select your data range or any cell within it.
Go to the Data tab on the ribbon
Click Remove Duplicates

4. Select which columns to check for duplicates, you can expand it up to next column.

5. Click OK— Excel tells you how many rows were removed.

Important: This method permanently deletes rows. Always work on a copy of your data or use "Ctrl+Z" to undo.

Advanced Filter (Non-Destructive)

Want to extract unique records without touching the original? Advanced Filter copies unique rows to a new location, leaving your source data untouched.

1. Go to Data → Sort & Filter → Advanced

2. Choose "Copy to another location"

3. Set the List Range to your data

4. Set the Copy To range (destination cell)

5. Check "Unique records only" → Click OK

💡 This is ideal for audit scenarios where you need to show before & after, or when your boss wants to review what was removed first.

UNIQUE Function (Excel 365 / 2021)

The most powerful modern approach. The UNIQUE function returns a live, dynamic list that updates automatically whenever your source data changes.

=UNIQUE (A2:D100, FALSE, FALSE) ← entire row deduplication

Click an empty cell where you want unique values
Type=UNIQUE (and select your range
Press Enter— results spill automatically

✨ Combine with =SORT (UNIQUE (A2:A100)) to get a clean, alphabetically sorted unique list in one formula!

Power Query (Best for Large Data)

Power Query is Excel's ETL powerhouse. Once set up, you can refresh it with a single click — making it perfect for repeated monthly or weekly data cleaning tasks.

1. Select your data → Data→ From Table/Range

2. The Power Query Editor opens

3. Go to Home → Remove Rows → Remove Duplicates

4. Click Close & Load to send clean data back to Excel

Conditional Formatting (Visual Review First)

Not ready to delete yet? Highlight duplicates first, review them manually, then decide what to delete. Perfect for sensitive data where human review matters.

1. Select your column or range

2. Home → Conditional Formatting → Highlight Cell Rules

3. Click Duplicate Values

4. Choose a highlight color → Click OK

5. Review highlighted rows, then manually delete what you don't need

COUNTIF Formula

Use a formula to flag duplicates, giving you granular control over which occurrence to keep — the first, the last, or any based on your own logic.

1. Add a helper column next to your data

2. Paste the formula above in the first row, drag it down

3. Rows showing TRUE are duplicates (2nd occurrence onward)

4. Filter by TRUE → Select those rows → Delete them

5. Remove the helper column when done

=COUNTIF($D$2:D2, D2) > 1

💡This approach keeps the first occurrence and removes all later repeats. Change the formula logic to keep the last instead.

Pivot Table (For Reporting)

Don't need to clean the source — just need a unique list for a report? A Pivot Table extracts unique values instantly without modifying anything.

1. Click any cell in your data

2. Insert → Pivot Table

3. Drag your column to the Rows area

4. The Pivot Table shows only unique values automatically

5. Copy → Paste as Values to use the list elsewhere

Which Method Should You Use?

Sunday, 8 February 2026

How to Create a Search Box in Excel (No VBA Required)

🚀 Introduction

When working with large datasets in Excel, finding repeated or matching values can become slow and frustrating. Most users rely on the Ctrl + F shortcut to search for values. While this works, Excel shows only one result at a time, forcing you to click Find Next repeatedly to locate every occurrence.

This approach is time-consuming and inefficient when you need a quick visual overview of all matching entries in your sheet.

What if you could create a simple search box where typing a value instantly highlights all matching cells across your dataset?

In this tutorial, you will learn how to build a dynamic Excel search box using Conditional Formatting — no VBA, no macros, just a smart formula.

🖼️ Dataset with duplicate values circled

In the dataset above, several values repeat multiple times. Using the normal Find option would require many clicks to track each one.

🖼️ Find and Replace dialog with Find Next

The Find and Replace dialog helps you locate values, but it navigates to only one match at a time. Each press of Find Next jumps to the next occurrence.

You also don’t get a visual overview of all matches together.

✅ What We Are Going to Create

We will create a colored cell that works as a Search Box.

🖼️ Search box highlighting all matches

The moment you type any value into it, Excel will automatically highlight every matching cell in your dataset.

🧩 Step 1 – Create the Search Box Cell

Choose an empty cell in your worksheet (for example, H1). Apply a background color to make it clearly visible. This cell will act as your search input box.

🖼️ Search box cell with color

You can label the adjacent cell as Search Here for clarity.

🧩 Step 2 – Select the Dataset

🖼️ Dataset selected

Select the entire dataset where you want this search feature to work. Conditional Formatting will apply only to this selected range.

🧩 Step 3 – Open Conditional Formatting

Go to the Home tab
Click Conditional Formatting
Choose New Rule

🖼️ New Formatting Rule Dialog

Select:

Use a formula to determine which cells to format

🧩 Step 4 – Enter the Formula


=$H$1=A2

🖼️ Formula Entered

Explanation:

$H$1 → Search box cell (fixed reference)
A2 → First cell of the dataset (relative reference)

If Excel adds $ before A2, remove it.

🧩 Step 5 – Choose the Highlight Color

Click Format → Fill → Choose a color

🖼️ Fill color selection

Pick a bright color and press OK.

🧩 Step 6 – Apply the Rule

🖼️ Rule preview with color

Click OK again to apply the rule.

🎉 Your Excel Search Box Is Ready

🖼️ Final result with highlighted matches

Now, type any value into H1 and Excel will instantly highlight all matching cells.

💡 Practical Uses

Finding duplicate customer names
Highlighting repeated invoice numbers
Searching product IDs
Identifying repeated attendance entries
Reviewing large reports quickly

⚙️ Tips to Improve Visibility

Use a bold, bright highlight color
Place the search box at the top of the sheet
Add borders around the search cell
Use Data Validation for controlled inputs

Why Conditional Formatting Works So Well Here

Conditional Formatting continuously checks each cell against the formula condition. The moment the value in H1 changes, the entire dataset updates automatically without any manual refresh.

This makes the search dynamic and instant.

🏁 Conclusion

By using Conditional Formatting with a simple formula, you can convert a normal Excel cell into a powerful search tool. This method saves time, improves visibility, and removes the need for repetitive Find operations.

It’s a small trick that makes a big difference in daily Excel work, especially when handling large datasets.

Try it once, and you’ll never rely on Ctrl + F the same way again.

How to Remove Special Characters in Excel (Formula + VBA Method)

VLOOKUP for Beginners: Find Data from Another Sheet (Step-by-Step Guide)

Excel VBA Data Cleaning Automation – Remove Special Characters Step by Step (Beginner Guide)

Excel interface (Workbook, Worksheet, Ribbon)

Thursday, 1 January 2026

How to Remove Special Characters in Excel (Formula + VBA Method)

When working with real-world data in Microsoft Excel, you will often face a common problem — special characters in your data. These unwanted symbols can break formulas, cause errors in reports, and create issues while importing or exporting data.

In this detailed tutorial, you will learn multiple ways to remove special characters in Excel, using both Excel formulas and VBA automation. This guide is perfect for beginners, office users, and Excel learners who want clean and professional data.

What Are Special Characters in Excel?

Special characters are symbols that are not letters (A–Z), numbers (0–9), or spaces.

Common Examples:

@ # $ % & * ! ? / \ ( ) _ - + =

Example Dirty Data:

Such characters often appear due to:

Data imported from websites
Copy-paste from PDFs
CRM or ERP exports
User input mistakes

Why Should You Remove Special Characters?

Removing special characters helps you to:

Clean and standardize data
Avoid formula errors
Improve data accuracy
Prepare data for reports and dashboards
Import data into databases or software

Clean data = Better results 📊

Method 1: Remove Special Characters Using Excel Formulas

Excel formulas are best when:

You have small to medium datasets
You don’t want to use VBA
You want quick results

Method 1.1: Using SUBSTITUTE Function (Manual Way)

The SUBSTITUTE function replaces specific characters with blank values.

Syntax:

=SUBSTITUTE(text, old_text, new_text)

Example:

If cell A2 contains:

Amit@123

Formula:

=SUBSTITUTE(A2,"@","")

This removes only @.

⚠️ Limitation:

You must write one SUBSTITUTE for each character.

Removing Multiple Special Characters with Nested SUBSTITUTE

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"@",""),"#",""),"$","")

❌ Not practical for many characters
❌ Hard to maintain

Method 1.2: Using TEXTJOIN + MID + CODE (Advanced Formula)

This formula removes all non-alphanumeric characters.

=TEXTJOIN("",TRUE,IF((CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))>=48)*(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))=122),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),""))

✔ Removes almost all special characters

✔ No VBA required

⚠️ Works best in Excel 365 / Excel 2021
⚠️ Complex for beginners

Method 2: Remove Special Characters Using Excel VBA (Best Method)

If you work with large datasets or want a one-click solution, VBA is the best choice.

Benefits of VBA Method:

Removes ALL special characters automatically
Fast and efficient
Reusable
Ideal for automation projects

Step 1: Sample Dataset

Paste this data in Column A:

Step 2: Open VBA Editor

Press ALT + F11
Click Insert → Module
Paste the VBA code below

Step 3: VBA Code to Remove Special Characters

Sub CleanText_ProperCase()

Dim cell As Range

Dim cleanText As String

Dim i As Long

Dim ch As String

For Each cell In Selection

cleanText = ""

' Step 1: Replace special characters with space

For i = 1 To Len(cell.Value)

ch = Mid(cell.Value, i, 1)

If ch Like "[A-Za-z0-9 ]" Then

cleanText = cleanText & ch

Else

cleanText = cleanText & " "

End If

Next i

' Step 2: Remove extra spaces

cleanText = Application.WorksheetFunction.Trim(cleanText)

' Step 3: Convert to Proper Case

cell.Value = Application.WorksheetFunction.Proper(cleanText)

Next cell

End Sub

Step 4: How to Run the VBA Code

Select the cells containing data
Press ALT + F8
Select RemoveSpecialCharacters
Click Run

✨ Done! All special characters will be removed instantly.

How This VBA Code Works (Line-by-Line Explanation)

Loops through each selected cell
Reads each character one by one
Keeps only:
- A–Z
- a–z
- Numbers
- Spaces
Removes symbols like @#$%^&*
Trims extra spaces

This makes your data 100% clean and usable.

Bonus: Remove Only Specific Characters (Custom VBA)

If you want to remove only selected symbols, use this version:

Sub RemoveSpecificCharacters()

Dim cell As Range

For Each cell In Selection

cell.Value = Replace(cell.Value, "@", "")

cell.Value = Replace(cell.Value, "#", "")

cell.Value = Replace(cell.Value, "$", "")

cell.Value = Replace(cell.Value, "%", "")

cell.Value = Replace(cell.Value, "&", "")

Next cell

End Sub

✔ Useful when rules are fixed
✔ Easy to modify

👉 Recommendation:

Use Formulas for small tasks and VBA for professional or repeated work.

Common Mistakes to Avoid

Not keeping a backup of original data
Running VBA on wrong cells
Forgetting to select data before running macro
Using complex formulas unnecessarily

Practical Use Cases

Cleaning customer names
Preparing data for Power BI
Removing symbols from phone numbers
Cleaning Excel data before upload
Excel automation projects

Final Thoughts

Removing special characters is one of the most important Excel data-cleaning tasks. Whether you are a beginner or an advanced Excel user, knowing both formula-based and VBA-based methods gives you complete control over your data.

If you regularly work with Excel, learning VBA automation will save you hours of manual work and make you more productive.

Excel VBA Data Cleaning Automation – Remove Special Characters Step by Step (Beginner Guide)

❓ Frequently Asked Questions (FAQ)

Q1. How do I remove special characters in Excel without VBA?
You can use Excel formulas like SUBSTITUTE or advanced TEXTJOIN formulas to remove unwanted symbols.

Q2. What is the best way to remove special characters in Excel?
For large datasets, VBA is the best and fastest method as it automates the entire process.

Q3. Does removing special characters affect spaces between words?
If you replace special characters with spaces and apply TRIM, word separation remains intact.

Q4. Can VBA also capitalize the first letter automatically?
Yes, using the PROPER function in VBA converts text to Proper Case.

Monday, 29 December 2025

Rows, Columns & Cells Explained (Beginner-Friendly Guide)

Microsoft Excel is built on a simple but powerful grid structure. Everything you do in Excel—entering data, applying formulas, creating charts, or cleaning datasets—depends on understanding rows, columns, and cells. If you master these three basics, Excel becomes far easier, faster, and less confusing.

This detailed guide explains rows, columns, and cells in clear language with practical examples, perfect for beginners and refresher learners.

What Is an Excel Worksheet?

An Excel worksheet looks like a large table made up of thousands of small boxes. These boxes are arranged in horizontal and vertical lines:

Horizontal lines → Rows
Vertical lines → Columns
Each box → Cell

Together, rows and columns create the worksheet grid where data is stored and processed.

Understanding Rows in Excel

What Is a Row?

A row is a horizontal line of cells that runs from left to right across the worksheet.

How Rows Are Identified

Rows are identified by numbers
They start from 1 at the top
Example: Row 1, Row 2, Row 3, and so on

Example

If you enter names of students like this:

Each name appears in a different row, one below the other.

Uses of Rows

Rows are commonly used to:

Store individual records (one student, one product, one transaction)
Insert or delete complete records
Sort data vertically
Apply formatting or formulas to full records

Important Row Facts

A modern Excel worksheet has over 1 million rows
You can hide or unhide rows
Entire rows can be selected by clicking the row number

Understanding Columns in Excel

What Is a Column?

A column is a vertical line of cells that runs from top to bottom in the worksheet.

How Columns Are Identified

Columns are identified by letters
They start from A, B, C…
After Z, they continue as AA, AB, AC…

Example

If you enter student details:

Here:

“Name” is in Column A
“Marks” is in Column B

Uses of Columns

Columns are used to:

Store a specific type of data (names, dates, prices)
Apply formulas to a full category
Adjust width to fit data
Filter data by category

Important Column Facts

A worksheet has 16,384 columns
Entire columns can be selected by clicking the column letter
Column width can be increased or decreased

Understanding Cells in Excel

What Is a Cell?

A cell is the intersection of a row and a column.
It is the smallest unit in Excel where you enter data.

Cell Address (Cell Reference)

Each cell has a unique address made by combining:

Column letter + Row number

Examples:

A1 → Column A, Row 1
B5 → Column B, Row 5
C10 → Column C, Row 10

What Can a Cell Contain?

A cell can store:

Text (Name, City)
Numbers (Marks, Price)
Dates and time
Formulas (=A1+B1)
Functions (=SUM(A1:A10))

Active Cell

The active cell is the currently selected cell:

Highlighted with a bold border
Its address is shown in the Name Box

What Is a Range in Excel?

Definition of Range

A range is a group of selected cells.

Range Examples

A1:A5 → Vertical range
A1:E1 → Horizontal range
A1:C5 → Rectangular range

Why Ranges Are Important

Ranges are used for:

Applying formulas
Formatting data
Creating charts
Sorting and filtering

Difference Between Rows, Columns & Cells

Practical Example (Real-Life Use)

Imagine a sales report:

Each row = one sale
Each column = type of information
Each cell = one data value

This structure makes data easy to calculate, analyze, and present.

Common Beginner Mistakes (And How to Avoid Them)

1. Confusing rows with columns

👉 Remember: Rows = numbers, Columns = letters

2. Typing multiple values in one cell

👉 Keep one value per cell for clean data

3. Ignoring cell references in formulas

👉 Always double-check cell addresses

4. Not selecting full ranges

👉 Select entire required rows or columns when applying formulas

Tips to Work Faster with Rows, Columns & Cells

Double-click column border to auto-fit width
Use Ctrl + Arrow keys to jump across data
Use Shift + Arrow keys to select ranges
Click row/column headers to select entire rows or columns
Name important ranges for easy reference

Why Rows, Columns & Cells Matter in Excel Automation

If you plan to learn:

Excel formulas
Conditional formatting
Pivot tables
VBA automation
Power Query

Then understanding rows, columns, and cells is non-negotiable.
Every formula, macro, or automation task works by identifying and manipulating cells through rows and columns.

Final Words

Rows, columns, and cells form the foundation of Excel. Once you clearly understand how data is organized inside this grid, Excel becomes logical instead of confusing. Whether you are a student, office professional, or data learner, mastering these basics will boost your confidence and efficiency in Excel.

VLOOKUP for Beginners: Find Data from Another Sheet (Step-by-Step Guide)