MS Excel for Data Analysis

📊 Excel Basics #34 – Excel Tables

If you're working with a dataset that keeps growing, an Excel Table can make your work much easier.

Instead of treating your data as a simple range, you can convert it into a structured, dynamic table.

📌 What is an Excel Table?

An Excel Table is a structured range of data with built-in features such as: Automatic filters, Structured references, Automatic formatting, Automatic expansion, Total Row, Calculated columns

To create one: Select your data → Insert → Table

Keyboard shortcut: Ctrl + T

📌 Example

Suppose you have:

Employee | Department | Sales

Rahul | IT | 75000

Priya | HR | 55000

Amit | Finance | 90000

Select the data and press: Ctrl + T

Excel converts it into a Table.

📌 1. Automatic Filters

Once you create a Table, filter dropdowns automatically appear in the headers.

You can immediately filter: Department → IT or Sales → Greater Than → 50000

📌 2. Tables Automatically Expand

Suppose your Table contains 100 rows.

You enter a new record directly below it.

Excel can automatically extend the Table to include the new row.

This is extremely useful when your dataset grows regularly.

📌 3. Structured References

Tables allow you to use column names instead of traditional cell references.

Instead of: =SUM(C2:C100)

You can use: =SUM(Sales)[Sales]

Here: "Sales" → Table name, "" → Column name[Sales]

This makes formulas easier to understand.

📌 4. Calculated Columns

Suppose you add a new column: Profit

Formula: =[@Sales]-[@Cost]

Excel automatically fills the formula down the entire Table.

If you add another row later, the formula can automatically extend to the new row.

📌 5. Total Row

Excel Tables can automatically add a Total Row.

Go to: Table Design → Total Row

You can calculate: Sum, Average, Count, Maximum, Minimum

For example: Total Sales → SUM

📌 6. Table Styles

Excel provides predefined styles that can be applied to your Table.

You can customize: Header formatting, Banded rows, Total row, Borders, Colors

Use a consistent style rather than excessive formatting.

📌 7. Rename Your Table

Instead of keeping the default name: "Table1" rename it to something meaningful.

Example: "SalesData"

Then you can write: =SUM(SalesData)[Sales]

This makes complex workbooks much easier to understand.

📌 Real-World Example

Imagine you maintain a daily sales dataset.

Every day, new transactions are added.

Without a Table:

❌ You may need to update formulas manually.

❌ Charts may not automatically include new rows.

❌ Pivot Table source ranges may need adjustment.

With a Table:

✅ Data automatically expands.

✅ Formulas can automatically fill down.

✅ Structured references make formulas easier to maintain.

📌 Table vs Normal Range

Normal Range - Fixed cell range. No structured references. Less automatic expansion.

Excel Table - Dynamic structure. Built-in filtering. Structured references. Automatic expansion. Easier to use with formulas, charts, and Pivot Tables.

📌 Common Mistakes

❌ Creating a Table with blank headers.

❌ Using merged cells inside the dataset.

❌ Mixing different types of data in the same column.

❌ Giving Tables unclear names.

✅ Best Practices

Use Tables for datasets that will grow.

Keep one type of data per column. Give Tables meaningful names. Avoid blank rows and columns inside the dataset. Use structured references for readable formulas.

💡 Quick Tip:

If you regularly add rows to a dataset, Ctrl + T should become one of your favorite Excel shortcuts.

Excel Tables are one of the most important foundations for building reliable reports, dashboards, and Pivot Tables.

💡 Double Tap ❤️ For More

📊 Excel Basics #33 – Sorting & Filtering Data

When working with hundreds or thousands of rows, you don't want to manually search through the entire dataset.

Excel's Sorting and Filtering features help you quickly organize and analyze your data.

📌 1. What is Sorting?

Sorting rearranges your data based on a specific column.

You can sort:

• A → Z

• Z → A

• Smallest → Largest

• Largest → Smallest

• Oldest → Newest

• Newest → Oldest

Example:

Employee| Sales

Rahul| 75000

Priya| 45000

Amit| 90000

Neha| 60000

Sort Sales from Largest to Smallest:

Employee| Sales

Amit| 90000

Rahul| 75000

Neha| 60000

Priya| 45000

📌 2. Basic Sorting

Select your dataset and go to:

Data → Sort & Filter

You can choose:

Sort A to Z

or

Sort Z to A

For numbers:

Smallest to Largest

or

Largest to Smallest

📌 3. Multi-Level Sorting

You can sort using multiple columns.

Example:

First sort by:

Region → A to Z

Then by:

Sales → Largest to Smallest

This groups employees by region and ranks sales within each region.

Go to:

Data → Sort

Then click:

Add Level

📌 4. What is Filtering?

Filtering temporarily hides rows that don't meet your selected criteria.

Example:

Employee| Region| Sales

Rahul| North| 75000

Priya| South| 45000

Amit| North| 90000

Neha| West| 60000

Filter Region to North.

Excel displays only:

Employee| Region| Sales

Rahul| North| 75000

Amit| North| 90000

The other rows are hidden, not deleted.

📌 5. Enable Filters

Select your dataset and use:

Data → Filter

Keyboard shortcut:

Ctrl + Shift + L

Small dropdown arrows will appear in the column headers.

📌 6. Filter by Number

For numeric columns, you can filter using conditions such as:

• Equals

• Greater Than

• Less Than

• Between

• Top 10

• Above Average

• Below Average

Example:

Sales → Number Filters → Greater Than → 50000

Excel displays only sales above 50,000.

📌 7. Filter by Text

For text columns, you can use:

• Equals

• Does Not Equal

• Begins With

• Ends With

• Contains

• Does Not Contain

Example:

Department → Text Filters → Contains → "Data"

This displays rows where the department contains the word "Data".

📌 8. Filter by Date

For date columns, you can filter by:

• Today

• Yesterday

• Tomorrow

• This Week

• This Month

• Last Month

• Between two dates

This is extremely useful for analyzing transactions and business activity over specific periods.

📌 Real-World Example

Imagine you have 100,000 sales records.

You want to find:

👉 Sales from the North region

👉 With sales greater than ₹50,000

You can apply both filters:

Region = North

AND

Sales > ₹50,000

Excel immediately shows only the relevant records.

📌 Sorting vs Filtering

Sorting → Changes the order of the rows.

Filtering → Temporarily hides rows that don't match your criteria.

Think:

👉 Sort = Rearrange

👉 Filter = Show only what I need

📌 Common Mistakes

❌ Sorting only one column instead of the entire dataset.

❌ Forgetting to include column headers.

❌ Assuming filtered rows are deleted.

❌ Adding filters to inconsistent or poorly structured data.

✅ Best Practices

• Keep headers in the first row.

• Select the complete dataset before sorting.

• Convert your dataset into an Excel Table for easier filtering.

• Clear filters when you're finished analyzing.

• Be careful when sorting data with formulas or related columns.

💡 Double Tap ❤️ For More

📊 Excel Basics #32 – Data Validation

When multiple people enter data into an Excel sheet, incorrect or inconsistent entries can easily create data-quality problems.

For example:

❌ Someone enters "Pending"

❌ Someone enters "pending"

❌ Someone enters "Pendng"

Data Validation helps control what users can enter into a cell.

📌 What is Data Validation?

Data Validation allows you to set rules that restrict or control the type of data entered into a cell.

Go to:

Data → Data Validation

📌 1. Create a Drop-Down List

One of the most common uses of Data Validation is creating a dropdown.

Example:

You want users to select only:

• Pending

• In Progress

• Completed

Steps:

1️⃣ Select the cells.

2️⃣ Go to Data → Data Validation.

3️⃣ Under Allow, select List.

4️⃣ Enter:

Pending,In Progress,Completed

5️⃣ Click OK.

Now users can select a status from a dropdown instead of typing it manually.

📌 2. Restrict Numbers

You can restrict users to entering numbers within a specific range.

Example:

Allow marks only between 0 and 100.

Go to:

Data Validation → Allow → Whole Number

Then set:

between → 0 → 100

If someone enters "150", Excel can reject the entry.

📌 3. Restrict Dates

You can also control which dates users can enter.

Example:

Allow dates only between:

01-Jan-2026 and 31-Dec-2026

This is useful for project trackers, financial reports, and attendance sheets.

📌 4. Restrict Text Length

You can limit the number of characters entered.

Example:

Employee ID must contain a maximum of 10 characters.

Go to:

Data Validation → Allow → Text Length

Then specify the required limit.

📌 5. Create an Input Message

Data Validation can display instructions when a user selects the cell.

Example:

Input Message:

"Select a valid project status from the dropdown."

This helps users understand what they are expected to enter.

📌 6. Create an Error Alert

You can decide what happens when someone enters invalid data.

Excel provides options such as:

Stop → Prevent invalid entry.

Warning → Warn the user but allow them to continue.

Information → Display an informational message.

For important business data, Stop is usually the safest option.

📌 Real-World Example

Imagine a project tracker:

Employee | Status | Priority

Rahul | Completed | High

Priya | In Progress | Medium

Amit | Pending | Low

Instead of allowing users to type anything, create dropdowns for:

Status:

• Pending

• In Progress

• Completed

Priority:

• High

• Medium

• Low

This keeps the dataset consistent and easier to analyze.

📌 Common Mistakes

❌ Allowing users to type values manually when a dropdown would be better.

❌ Not setting an error alert.

❌ Applying validation to only part of the required data range.

❌ Using inconsistent values in the source list.

✅ Best Practices

• Use dropdowns for fixed categories.

• Restrict numbers and dates where appropriate.

• Add helpful input messages.

• Use meaningful error messages.

• Apply validation before distributing the workbook.

• Keep the allowed values standardized.

💡 Remember:

Data Validation doesn't just make Excel look professional.

It helps improve data quality by controlling what users can enter.

For data analysts, this is especially important because clean and consistent input data leads to more reliable analysis.

Double Tap ❤️ For More

📊 Excel Basics #31 – TEXT() Function

Sometimes the value in Excel is correct, but you want to display it in a specific format.

For example:

• "17-Aug-2026" → "August 2026"

• "0.25" → "25%"

• "125000" → "₹125,000"

That's where the TEXT() function is useful.

📌 What is the TEXT() Function?

TEXT() converts a number or date into text using a format you specify.

Syntax:

=TEXT(value, format_text)

⚠️ The result of TEXT() is text, not a numeric value.

📌 Example 1 – Format a Date

Suppose:

A2 = 17-Aug-2026

Formula:

=TEXT(A2,"dd-mm-yyyy")

Result:

17-08-2026

Another example:

=TEXT(A2,"mmmm yyyy")

Result:

August 2026

📌 Example 2 – Extract the Month Name

=TEXT(A2,"mmmm")

Result:

August

Short month name:

=TEXT(A2,"mmm")

Result:

Aug

📌 Example 3 – Format Numbers

Suppose:

A2 = 125000

Formula:

=TEXT(A2,"#,##0")

Result:

125,000

📌 Example 4 – Format Currency

=TEXT(A2,"₹#,##0")

Result:

₹125,000

You can also use:

=TEXT(A2,"₹#,##0.00")

Result:

₹125,000.00

📌 Example 5 – Format Percentage

Suppose:

A2 = 0.25

Formula:

=TEXT(A2,"0%")

Result:

25%

With decimals:

=TEXT(A2,"0.00%")

Result:

25.00%

📌 Example 6 – Combine Text with a Date

Suppose:

A2 = 17-Aug-2026

Formula:

="Report generated on "&TEXT(A2,"dd-mmm-yyyy")

Result:

Report generated on 17-Aug-2026

This is especially useful for dynamic report titles and dashboard labels.

📌 Common Date Format Codes

• dd → Day

• ddd → Short day name

• dddd → Full day name

• mm → Month number

• mmm → Short month name

• mmmm → Full month name

• yy → Two-digit year

• yyyy → Four-digit year

Example:

=TEXT(A2,"dddd, dd mmmm yyyy")

Result:

Monday, 17 August 2026

📌 Important Difference

Changing the cell's number format only changes how the value looks.

TEXT() actually converts the value into text.

For example:

=TEXT(A2,"₹#,##0")

may look like a currency value, but the result is text and shouldn't be used directly for mathematical calculations.

📌 Real-World Uses

• Format dates in reports.

• Create dynamic dashboard titles.

• Display currency values.

• Format percentages.

• Create readable messages.

• Combine numbers or dates with text.

📌 Common Mistake

❌ Using TEXT() when you still need to perform calculations on the result.

If you only need to change how a number or date looks, consider using Cell Format → Number Format instead.

✅ Quick Tip

Remember:

TEXT() → Convert a value into formatted text

Examples:

• =TEXT(A2,"mmmm yyyy") → August 2026

• =TEXT(B2,"₹#,##0") → ₹125,000

• =TEXT(C2,"0.00%") → 25.00%

Double Tap ❤️ For More

📊 Excel Basics #30 – DATEDIF() & EDATE() Functions

When working with employee records, project timelines, subscriptions, loans, or customer data, you often need to calculate the time between dates or move a date forward or backward by a specific number of months.

Two useful functions are DATEDIF() and EDATE().

📌 1. DATEDIF() Function

DATEDIF() calculates the difference between two dates.

Syntax:

=DATEDIF(start_date,end_date,unit)

The unit determines what you want to calculate.

Common units:

• Y → Complete years

• M → Complete months

• D → Total days

• YM → Remaining months after complete years

• YD → Remaining days after complete years

• MD → Remaining days after complete months

📌 Example 1 – Calculate Complete Years

Suppose:

A2 = 01-Jan-2020

B2 = 19-Aug-2026

Formula:

=DATEDIF(A2,B2,"Y")

Result:

6

The employee has completed 6 full years.

📌 Example 2 – Calculate Complete Months

=DATEDIF(A2,B2,"M")

This returns the total number of complete months between the two dates.

📌 Example 3 – Calculate Total Days

=DATEDIF(A2,B2,"D")

This returns the total number of complete days between the dates.

📌 Example 4 – Display Years and Months

You can combine multiple DATEDIF() functions:

=DATEDIF(A2,B2,"Y")&" Years "&DATEDIF(A2,B2,"YM")&" Months"

Example result:

6 Years 7 Months

This is useful for calculating employee tenure or customer relationship duration.

📌 2. EDATE() Function

EDATE() returns a date that is a specified number of months before or after a starting date.

Syntax:

=EDATE(start_date,months)

📌 Example 1 – Add Months

If:

A2 = 19-Aug-2026

Formula:

=EDATE(A2,3)

Result:

19-Nov-2026

📌 Example 2 – Subtract Months

=EDATE(A2,-3)

This returns the date 3 months before the date in A2.

Result:

19-May-2026

📌 Real-World Example

Suppose a subscription starts on:

19-Aug-2026

and lasts for 12 months.

Formula:

=EDATE(A2,12)

Result:

19-Aug-2027

You can use this to calculate renewal dates.

📌 DATEDIF() vs EDATE()

DATEDIF() → Calculates the difference between dates.

EDATE() → Calculates a new date by adding/subtracting months.

Think:

👉 DATEDIF() → How long?

👉 EDATE() → What date after/before X months?

📌 Real-World Uses

• Calculate employee experience.

• Calculate customer tenure.

• Calculate project duration.

• Find subscription renewal dates.

• Calculate loan or contract dates.

• Track service anniversaries.

📌 Common Mistakes

❌ Putting the end date before the start date in DATEDIF().

❌ Using the wrong DATEDIF() unit.

❌ Forgetting that DATEDIF() returns complete units, not rounded values.

❌ Formatting an EDATE() result as a number instead of a date.

✅ Quick Tip

Remember:

DATEDIF() → Difference between two dates

EDATE() → Move a date by months

These functions are extremely useful when working with real-world business data.

Double Tap ❤️ For More

📊 Excel Basics #29 – TODAY(), NOW() & Basic Date Functions

Dates are extremely important in Excel, especially when working with sales, invoices, employee data, projects, and deadlines.

Excel provides built-in functions to work with the current date and time.

📌 1. TODAY() Function

"TODAY()" returns the current date.

Syntax:

=TODAY()

Example:

If today's date is 17-Aug-2026, the formula returns:

17-Aug-2026

The value automatically updates when Excel recalculates on a different day.

📌 2. NOW() Function

"NOW()" returns the current date and time.

Syntax:

=NOW()

Example:

17-Aug-2026 13:08

The exact displayed format depends on your cell formatting and system settings.

📌 TODAY() vs NOW()

"TODAY()" → Current date

"NOW()" → Current date + time

📌 3. Calculate Days Since a Date

Suppose an employee's joining date is in "A2".

Formula:

=TODAY()-A2

This returns the number of days between the joining date and today.

💡 Example:

Joining Date → "01-Jan-2026"

Formula:

=TODAY()-A2

Result → Number of days since joining.

📌 4. Check Whether a Date Has Passed

Suppose a project deadline is in "A2".

Formula:

=IF(A2

📊 Excel Basics #28 – TRIM(), UPPER(), LOWER() & PROPER()

Raw data often contains extra spaces or inconsistent capitalization.

For example:

" rahul SHARMA "

This can create problems when filtering, matching, or analyzing data.

Excel provides several useful text-cleaning functions to fix these issues.

📌 1. TRIM() Function

"TRIM()" removes unnecessary spaces from text.

Syntax:

=TRIM(text)

Example:

=TRIM(" Rahul Sharma ")

Result:

Rahul Sharma

It removes leading/trailing spaces and reduces multiple spaces between words to a single space.

📌 2. UPPER() Function

"UPPER()" converts text to uppercase.

Example:

=UPPER("data analyst")

Result:

DATA ANALYST

Useful when you want consistent formatting for codes, categories, or headings.

📌 3. LOWER() Function

"LOWER()" converts text to lowercase.

Example:

=LOWER("RAHUL@GMAIL.COM")

Result:

rahul@gmail.com

This is especially useful when standardizing email addresses or other text fields.

📌 4. PROPER() Function

"PROPER()" capitalizes the first letter of each word.

Example:

=PROPER("rahul sharma")

Result:

Rahul Sharma

Useful for cleaning names, cities, departments, and other labels.

📌 Real-World Example

Suppose your raw data contains:

Raw Name
" rahul sharma"
"PRIYA PATEL"
"amit kumar"

Clean it using:

=PROPER(TRIM(A2))

Results:

Rahul Sharma

Priya Patel

Amit Kumar

Here, "TRIM()" removes unnecessary spaces and "PROPER()" standardizes capitalization.

📌 Combining Functions

You can combine these functions to clean data more effectively.

Example:

=UPPER(TRIM(A2))

This removes unnecessary spaces and converts the result to uppercase.

If:

"A2 = " power bi ""

Result:

POWER BI

📌 Real-World Uses

• Clean imported datasets.
• Standardize employee names.
• Clean customer information.
• Standardize email addresses.
• Prepare data before using lookup functions.
• Fix inconsistent categories.

📌 Important Tip

"TRIM()" removes regular spaces, but some data copied from websites or external systems may contain non-breaking spaces that "TRIM()" alone doesn't remove.

For such cases, you can use:

=TRIM(SUBSTITUTE(A2,CHAR(160)," "))

✅ Quick Tip

"TRIM()" → Remove extra spaces

"UPPER()" → Convert to UPPERCASE

"LOWER()" → Convert to lowercase

"PROPER()" → Capitalize Each Word

Double Tap ❤️ For More