MS Excel for Data Analysis

🚀 𝗠𝗮𝘀𝘁𝗲𝗿 𝗜𝗻-𝗗𝗲𝗺𝗮𝗻𝗱 𝗧𝗲𝗰𝗵 𝗦𝗸𝗶𝗹𝗹𝘀 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 𝗶𝗻 𝟮𝟬𝟮𝟲 🔥

Want to upgrade your tech skills without spending money?

Here are some excellent FREE YouTube resources to learn high-demand technologies through tutorials and hands-on practice.

🔥 Learn → Practice → Build Projects → Upgrade Your Resume

🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 👇:-

https://pdlink.in/4x3B9hb

🎯 Perfect for Students • Freshers • Job Seekers • Working Professionals

📊 Excel Basics #40 – Pivot Tables

When you have thousands of rows of data, manually calculating totals and summaries can be extremely time-consuming.

Pivot Tables allow you to quickly summarize, analyze, and explore large datasets without writing complex formulas.

📌 What is a Pivot Table?

A Pivot Table is an Excel tool that summarizes data by categories.

It can quickly calculate:

• Sum

• Count

• Average

• Minimum

• Maximum

For example, you can turn thousands of sales transactions into a simple report showing total sales by region.

📌 Example Dataset

Date| Employee| Region| Product| Sales

01-Aug| Rahul| North| Laptop| 50000

02-Aug| Priya| South| Mouse| 5000

03-Aug| Amit| North| Laptop| 60000

04-Aug| Neha| West| Keyboard| 8000

05-Aug| Rahul| North| Mouse| 7000

Instead of manually calculating sales for each region, create a Pivot Table.

Go to:

Insert → PivotTable

📌 Pivot Table Areas

After creating a Pivot Table, you'll see four main areas:

Rows

→ Determines how data is grouped.

Columns

→ Creates categories across columns.

Values

→ Performs calculations such as Sum or Count.

Filters

→ Filters the entire Pivot Table based on selected fields.

📌 Example – Sales by Region

Drag:

Region → Rows

Sales → Values

Excel produces something like:

Region| Sum of Sales

North| 117000

South| 5000

West| 8000

Grand Total| 130000

You created a summary from the original transaction-level data in just a few steps.

📌 Change the Calculation

By default, Excel may use Sum for numeric fields.

You can change it to:

• Sum

• Count

• Average

• Max

• Min

For example:

Sales → Values → Value Field Settings → Average

Now the Pivot Table shows average sales instead of total sales.

📌 Add Multiple Fields

You can create more detailed reports.

Example:

Region → Rows

Product → Columns

Sales → Values

Now you can compare product sales across different regions.

📌 Why Pivot Tables are Powerful

✅ No complex formulas required.

✅ Summarize thousands of rows quickly.

✅ Easily change the analysis by dragging fields.

✅ Group and compare categories.

✅ Excellent for reporting and data analysis.

📌 Real-World Uses

Pivot Tables are commonly used for:

• Sales analysis.

• Employee performance.

• Expense reports.

• Inventory analysis.

• Customer analysis.

• Financial reporting.

• Monthly and regional comparisons.

📌 Important: Refresh Your Pivot Table

If the source data changes, the Pivot Table may not automatically reflect the new values.

Right-click the Pivot Table and select:

Refresh

If the source is an Excel Table, new rows are easier to incorporate into the Pivot Table's source.

📌 Common Mistakes

❌ Source data has blank or inconsistent headers.

❌ Mixing different data types in the same column.

❌ Forgetting to refresh after changing the source data.

❌ Placing the wrong field in Rows, Columns, Values, or Filters.

✅ Best Practices

• Keep your source data clean and structured.

• Use an Excel Table as the source when appropriate.

• Give columns clear, unique headers.

• Refresh Pivot Tables after source data changes.

• Use meaningful names and number formats in the final report.

💡 Quick Tip:

Think of a Pivot Table as:

Raw Data → Drag & Drop → Instant Summary

Once you become comfortable with Pivot Tables, analyzing large Excel datasets becomes dramatically easier.

💡 Double Tap ❤️ For More

🚀 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲 𝗢𝗻 𝗔𝘇𝘂𝗿𝗲 𝗠𝗮𝗰𝗵𝗶𝗻𝗲 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴 ☁️

✨ Build practical skills in Cloud AI • Machine Learning • Data Preparation • ML Workflows • Azure Data Services.

🔥 Learn → Practice → Build Projects → Strengthen Your Tech Career

🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 👇:-

https://pdlink.in/3UyljxK

🎓 Perfect for Students • Freshers • Data Science Aspirants • AI/ML Learners • Working Professionals

📊 Excel Basics #39 – Freeze Panes

When working with large datasets, scrolling down can make your column headers disappear. Then you have to scroll back to the top just to remember what each column represents.

Freeze Panes solves this problem by keeping selected rows or columns visible while you scroll.

📌 1. Freeze the Top Row

If your headers are in Row 1:

Go to:

View → Freeze Panes → Freeze Top Row

Now Row 1 remains visible while you scroll down.

💡 Perfect for datasets with hundreds or thousands of rows.

📌 2. Freeze the First Column

If you want the first column to remain visible while scrolling horizontally:

View → Freeze Panes → Freeze First Column

For example, if Column A contains Employee IDs, the IDs remain visible while you move across other columns.

📌 3. Freeze Multiple Rows

Suppose you want to keep the first 2 rows visible.

1. Select cell A3.
2. Go to:

View → Freeze Panes → Freeze Panes

Rows 1 and 2 will remain visible while scrolling.

📌 4. Freeze Rows AND Columns

You can freeze both rows and columns at the same time.

Example:

You want to keep:

• Rows 1–2 visible.
• Columns A–B visible.

Select:

Cell C3

Then:

View → Freeze Panes → Freeze Panes

Now both the selected rows above and columns to the left remain visible.

📌 5. Unfreeze Panes

To remove the frozen rows or columns:

View → Freeze Panes → Unfreeze Panes

📌 Real-World Example

Imagine a sales dataset with 50,000 rows:
Employee Region Product Sales Profit
Rahul North Laptop 75000 10000
Priya South Mouse 45000 7000
After scrolling to row 10,000, you may no longer see:

Employee | Region | Product | Sales | Profit

Freeze the header row and it remains visible while you scroll.

📌 Freeze Panes vs Split

These features are different.

Freeze Panes
→ Keeps selected rows or columns visible while scrolling.

Split
→ Divides the worksheet into separate scrollable sections.

For most data-analysis work, Freeze Panes is the more commonly used option.

📌 Common Mistakes

• ❌ Selecting the wrong cell before freezing multiple rows/columns.
• ❌ Forgetting that Freeze Panes applies to the current worksheet.
• ❌ Freezing too many rows or columns, reducing the visible workspace.

✅ Best Practices

• Freeze header rows for large datasets.
• Freeze important identifier columns when working with many columns.
• Don't freeze more rows or columns than necessary.
• Unfreeze panes when they become inconvenient.

💡 Quick Tip:

Remember the rule:

Select the cell → Everything ABOVE and LEFT of that cell gets frozen.

For example:

Select C3 → Rows 1–2 and Columns A–B are frozen.

Freeze Panes is a small Excel feature that makes working with large datasets much easier.

💡 Double Tap ❤️ For More

📊 Excel Basics #38 – Find & Replace

When working with large Excel datasets, manually searching for specific values and changing them one by one can take a lot of time.

Find & Replace lets you quickly locate and modify data across a worksheet or workbook.

📌 1. Find Data

Use Find when you simply want to locate specific text, numbers, or formulas.

Keyboard shortcut: Ctrl + F

Example: Suppose your dataset contains hundreds of records and you want to find: "Mumbai"

Press: Ctrl + F, Enter: "Mumbai". Excel highlights matching cells.

📌 2. Replace Data

Use Replace when you want to find something and replace it with another value.

Keyboard shortcut: Ctrl + H

Example: You want to change: "Mumbai" to: "Pune"

Use: Ctrl + H, Find what: "Mumbai", Replace with: "Pune", Then click Replace All.

📌 3. Replace All vs Replace

Replace → Changes one matching value at a time.

Replace All → Changes every matching occurrence that meets the search criteria.

⚠️ Always review the results before using Replace All, especially in important workbooks.

📌 4. Search Within

Excel allows you to control where it searches. You can search:

• Sheet

• Workbook

If you select Workbook, Excel searches across multiple worksheets. This is useful when the same value appears in several sheets.

📌 5. Search by Rows or Columns

The Find & Replace window also provides options for controlling the search direction.

You can search: By Rows or By Columns. This can make searches more predictable in complex datasets.

📌 6. Find Specific Formatting

Find & Replace can also search based on cell formatting.

For example, you can find cells with a particular:

• Font

• Fill color

• Number format

• Border

This is useful when cleaning inconsistently formatted reports.

📌 7. Find Formulas, Values, or Comments

Using the Look in option, you can search within:

• Formulas

• Values

• Comments/Notes

Example: If a formula contains a specific reference, searching in Formulas can help locate it.

📌 8. Wildcards

Excel supports wildcards in Find & Replace.

"*" → Represents any number of characters.

"?" → Represents one character.

Example: Rah* can find text beginning with Rah.

?123 can match values such as: "A123", "B123"

📌 Real-World Example

Suppose a dataset contains inconsistent department names:

• "IT"

• "Information Technology"

• "Info Technology"

You can use Find & Replace to standardize them to: IT

This makes filtering, Pivot Tables, and analysis more reliable.

📌 Important Warning

Be careful with Replace All. If you replace a common word such as: "IT" you may unintentionally change parts of other text or formulas depending on your search settings.

Always use the Find Next or Replace option first to verify what will be changed.

📌 Common Mistakes

❌ Using Replace All without reviewing matches

❌ Searching only the current sheet when the data exists across multiple sheets

❌ Forgetting to check whether you're searching formulas or values

❌ Using wildcards incorrectly

✅ Best Practices

• Use Ctrl + F for quick searches

• Use Ctrl + H for replacements

• Preview a few matches before using Replace All

• Search the entire workbook when necessary

• Be especially careful when replacing values inside formulas

• Keep a backup before performing large-scale replacements

💡 Double Tap ❤️ For More

𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿:
You have 2 minutes to solve this Excel problem.

You have the following data:

Employee Department Salary

John IT 75,000
Sarah HR 60,000
Mike IT 82,000
David Finance 90,000
Alice HR 65,000


Find the employees whose salary is above the average salary of their department.

𝗠𝗲: Challenge accepted! 💪

=C2>AVERAGEIF(B2:B6,B2,C2:C6)


💡 Explanation:

The formula compares each employee's salary with the average salary of their own department.

• AVERAGEIF() calculates the average salary for the employee's department.
• B2 identifies the current employee's department.
• C2 is the employee's salary.

The formula returns TRUE when the employee earns more than their department average.


🎯 Expected Output Example

Employee Department Salary Above Dept. Average?

John IT 75,000 FALSE
Sarah HR 60,000 FALSE
Mike IT 82,000 TRUE
David Finance 90,000 FALSE
Alice HR 65,000 TRUE


🚀 Bonus — Return the Employee Name Only

In Excel 365:

=FILTER(
A2:A6,
C2:C6>AVERAGEIF(B2:B6,B2:B6,C2:C6)
)

This returns the employees whose salaries are above their respective department averages.


❤️ React with ❤️ for more Excel interview challenges!

📊 Excel Basics #37 – Flash Fill

Have you ever had to manually clean or transform hundreds of rows because the data follows a pattern?

Flash Fill can often recognize that pattern and complete the rest automatically.

It is one of Excel's most useful features for quick data transformation.

📌 What is Flash Fill?

Flash Fill automatically detects a pattern in your data and fills the remaining cells accordingly.

You can activate it using:

• Data → Flash Fill

• Keyboard shortcut: Ctrl + E

📌 Example 1 – Extract First Names

Suppose you have:

Full Name | First Name

Rahul Sharma | Rahul

Priya Patel |

Amit Kumar |

Neha Singh |

Type the first result manually:

"Rahul"

Then press:

Ctrl + E

Excel recognizes the pattern and fills:

• Priya

• Amit

• Neha

📌 Example 2 – Extract Last Names

Full Name | Last Name

Rahul Sharma | Sharma

Priya Patel |

Amit Kumar |

Neha Singh |

Enter:

"Sharma"

Then press:

Ctrl + E

Excel fills the remaining last names based on the pattern.

📌 Example 3 – Create Email Addresses

Suppose:

Name | Email

Rahul Sharma | rahul.sharma@company.com

Priya Patel |

Amit Kumar |

Enter the email for the first row.

Then press:

Ctrl + E

If Excel recognizes the pattern, it can generate the remaining email addresses.

📌 Example 4 – Combine Data

Suppose you have:

First Name | Last Name | Full Name

Rahul | Sharma | Rahul Sharma

Priya | Patel |

Amit | Kumar |

Enter the first full name:

"Rahul Sharma"

Then use:

Ctrl + E

Excel can fill the remaining rows based on the pattern.

📌 Example 5 – Extract Product Codes

Suppose:

Product ID | Code

LAP-2026-001 | 001

LAP-2026-002 |

LAP-2026-003 |

Enter "001" and press:

Ctrl + E

Excel can recognize the pattern and extract the corresponding codes.

📌 Important Limitation

Flash Fill is pattern-based, not formula-based.

That means the generated results are generally static values.

If the original data changes later, Flash Fill does not automatically recalculate the results like a formula would.

For dynamic transformations, formulas or Power Query may be a better choice.

📌 Flash Fill vs Formula

•

Flash Fill

→ Quick, pattern-based transformation.

•

Formula

→ Dynamic result that updates when source data changes.

•

Power Query

→ Better for repeatable and larger-scale data transformation.

📌 When Flash Fill Works Best

Flash Fill is particularly useful for:

• Splitting names

• Combining names

• Extracting codes

• Standardizing text

• Creating email addresses

• Reformatting IDs

• Extracting parts of structured text

📌 Common Mistakes

• ❌ Expecting Flash Fill to understand every complex pattern

• ❌ Not providing a clear example for Excel to recognize

• ❌ Assuming the results will update when the original data changes

• ❌ Using Flash Fill for a transformation that needs to be repeated automatically

✅ Best Practices

• Give Excel a clear example of the desired result

• Check the generated values before using them

• Use Ctrl + E for quick access

• Use formulas or Power Query when you need a repeatable, dynamic process

💡 Double Tap ❤️ For More

📊 Excel Basics #36 – Text to Columns

Sometimes multiple pieces of information are stored inside a single Excel cell.

For example:
"Rahul,IT,Pune"

You may want to separate this into:
Rahul | IT | Pune

Excel's Text to Columns feature can do this quickly.

📌 What is Text to Columns?

Text to Columns splits the contents of one column into multiple columns based on a specific separator or fixed position.

Go to:
Data → Text to Columns

There are two main options:

👉 Delimited
👉 Fixed Width

📌 1. Delimited

Use Delimited when different pieces of data are separated by a character.

Common delimiters:
• Comma ","
• Space
• Tab
• Semicolon ";"
• Other custom characters

Example

Suppose A2 contains:
"Rahul,IT,Pune"

Select the column and choose:
Data → Text to Columns → Delimited → Comma

Excel separates the data into:
Name | Department | City
Rahul | IT | Pune

📌 2. Space as a Delimiter

Suppose:
"Rahul Sharma"
is stored in one cell.

Using Space as the delimiter can split it into:
First Name | Last Name
Rahul | Sharma

⚠️ Be careful with this method if names contain multiple words.

For example:
"Rahul Kumar Sharma"
would be split into three columns.

📌 3. Fixed Width

Use Fixed Width when data is aligned based on character positions rather than a separator.

Example:
101 Rahul IT
102 Priya HR
103 Amit Finance

You can place column breaks at specific positions to separate the fields.

📌 4. Text to Columns for Dates

Text to Columns can also help when dates are stored as text and need to be converted or split.

For example:
"17-08-2026"

You can use the wizard to specify the appropriate date format.

📌 5. Important: Check the Destination

By default, Excel may place the split data into columns next to the original data.

⚠️ If those columns already contain information, the existing data can be overwritten.

You can specify a different Destination in the Text to Columns wizard.

📌 Real-World Example

Suppose you receive customer data like:
Customer Details
Rahul,IT,Pune
Priya,HR,Mumbai
Amit,Finance,Delhi

You can split the single column using:
Data → Text to Columns → Delimited → Comma

Result:
Name | Department | City
Rahul | IT | Pune
Priya | HR | Mumbai
Amit | Finance | Delhi

Now the data can be easily filtered, sorted, analyzed, or used in Pivot Tables.

📌 Text to Columns vs Formulas

Text to Columns
→ Quick one-time transformation.

Text Functions
→ Useful when you want the transformation to update dynamically as the source data changes.

For example:
"LEFT()", "RIGHT()", "MID()", "TEXTBEFORE()", and "TEXTAFTER()" can be useful alternatives depending on your Excel version and requirement.

📌 Common Mistakes

❌ Forgetting to check the preview before finishing.
❌ Using the wrong delimiter.
❌ Overwriting existing data in neighboring columns.
❌ Splitting names or addresses incorrectly because they contain the chosen delimiter.

✅ Best Practices

• Always preview the result before clicking Finish.
• Make sure the destination columns are empty.
• Keep a backup when transforming important data.
• Choose the delimiter carefully.
• For repeatable workflows, consider formulas or Power Query instead of repeatedly using Text to Columns manually.

💡 Double Tap ❤️ For More

📊 Excel Basics #35 – Remove Duplicates

Duplicate records are common when working with data collected from multiple files, systems, or sources. Excel provides a quick way to identify and remove duplicate values without manually checking thousands of rows.

📌 What are Duplicates?

A duplicate occurs when the same record appears more than once.

Example:

Employee ID | Name | Department

101 | Rahul | IT

102 | Priya | HR

101 | Rahul | IT

103 | Amit | Finance

Here, the record for Employee ID 101 appears twice.

📌 1. Remove Duplicates

Select your dataset and go to: Data → Remove Duplicates

Excel will show a window where you can choose which columns should be checked. Click OK, and Excel removes duplicate rows based on the selected columns.

📌 2. Choosing Columns Matters

Suppose you have:

ID | Name | City

101 | Rahul | Pune

101 | Rahul | Mumbai

If you select all three columns, these are not considered duplicates because the City is different. But if you select only ID and Name, Excel considers them duplicates.

So always decide what makes a record "duplicate" before removing anything.

📌 3. Remove Duplicates from an Excel Table

If your data is already an Excel Table: Table Design → Remove Duplicates

You can select the columns you want Excel to use for identifying duplicates.

📌 4. Excel Keeps the First Record

When Excel removes duplicates, it generally keeps the first occurrence and removes subsequent matching records.

Example:

ID | Name

101 | Rahul

101 | Rahul

After removing duplicates:

ID | Name

101 | Rahul

📌 5. Remove Duplicates vs Find Duplicates

These are different tasks.

Remove Duplicates → Permanently removes duplicate records from the selected dataset.

Conditional Formatting → Duplicate Values → Highlights duplicates without deleting them.

💡 If you're unsure whether duplicates should be deleted, highlight them first and review the data.

📌 Real-World Example

Imagine you have 50,000 customer records collected from different sources. Some customers appear multiple times.

You can select: Customer ID → Remove Duplicates

Excel can quickly reduce the dataset to unique customer records.

📌 Common Mistakes

❌ Removing duplicates without checking which columns define uniqueness.

❌ Selecting only one column when the entire record should be compared.

❌ Not keeping a backup before deleting data.

❌ Assuming similar-looking records are always duplicates.

✅ Best Practices

• Always keep a backup of the original dataset.

• Decide which columns define a unique record.

• Review duplicates before deleting important data.

• Use Conditional Formatting first when you're unsure.

• For large datasets, use a unique ID whenever possible.

💡 Double Tap ❤️ For More

🎓 𝐀𝐜𝐜𝐞𝐧𝐭𝐮𝐫𝐞 𝐅𝐑𝐄𝐄 𝐂𝐞𝐫𝐭𝐢𝐟𝐢𝐜𝐚𝐭𝐢𝐨𝐧 𝐂𝐨𝐮𝐫𝐬𝐞𝐬 😍

Boost your skills with 100% FREE certification courses from Accenture!

📚 FREE Courses Offered:
1️⃣ Data Processing and Visualization
2️⃣ Exploratory Data Analysis
3️⃣ SQL Fundamentals
4️⃣ Python Basics
5️⃣ Acquiring Data

𝐋𝐢𝐧𝐤 👇:- 

https://pdlink.in/4yJKnBy

✅ Learn Online | 📜 Get Certified

𝗙𝗥𝗘𝗘 𝗚𝗲𝗻𝗔𝗜 + 𝗖𝗹𝗮𝘂𝗱𝗲 𝗢𝗻𝗹𝗶𝗻𝗲 𝗠𝗮𝘀𝘁𝗲𝗿𝗰𝗹𝗮𝘀𝘀😍

Learn how to use 25+ powerful AI tools to automate your work, create professional content and save hours every week!

🎯 Perfect For:-
Freelancers • Working Professionals • Business Owners • Self-Employed Individuals

💡 No technical knowledge or prior experience required!

🔗 𝗥𝗲𝗴𝗶𝘀𝘁𝗲𝗿 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 👇:-

https://pdlinks.in/ai

⚡ Start using AI smarter—limited slots available!

📊 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

🚀 𝗙𝗥𝗘𝗘 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲! 📊

Here’s a great chance to learn valuable skills and earn a FREE Certificate 🎓

✅ Beginner-friendly
✅ Learn Data Analytics skills
✅ Free certification
✅ Boost your resume & LinkedIn profile
✅ Great for students & job seekers

𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇 :-

https://pdlink.in/4qn5q94

📌 Start learning today & upgrade your career!

📊 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

𝗣𝗮𝘆 𝗔𝗳𝘁𝗲𝗿 𝗣𝗹𝗮𝗰𝗲𝗺𝗲𝗻𝘁—𝗕𝗲𝗰𝗼𝗺𝗲 𝗮 𝗙𝘂𝗹𝗹 𝗦𝘁𝗮𝗰𝗸 𝗗𝗲𝘃𝗲𝗹𝗼𝗽𝗲𝗿 𝘄𝗶𝘁𝗵 𝗚𝗲𝗻𝗔𝗜😍

Curriculum designed and taught by alumni from IITs & leading tech companies.

🏆 Placement Highlights:-

💰 ₹41 LPA highest salary
📈 ₹7.4 LPA average salary
🎓 2,000+ students placed
🏢 500+ partner companies

🔗 𝗔𝗽𝗽𝗹𝘆 𝗡𝗼𝘄 👇:-

https://pdlink.in/3SuUeuD

⚡ Take the first step toward your dream tech career today!

📊 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

🚀 𝗪𝗶𝗽𝗿𝗼 𝗘𝗹𝗶𝘁𝗲 𝗡𝗧𝗛 & 𝗧𝘂𝗿𝗯𝗼 𝗙𝗥𝗘𝗘 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗞𝗶𝘁 💻🔥

Get access to a FREE interview preparation kit and prepare smarter for your upcoming assessment & interview rounds.

📚 Prepare For:-
✅ Technical Interview Questions
✅ Software Engineer Interview Rounds
✅ Interview Preparation Resources

🎯 Perfect for Students | Freshers | Engineering Graduates | Wipro Aspirants

🔗 𝗚𝗲𝘁 𝗙𝗥𝗘𝗘 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄 𝗞𝗶𝘁 👇:-

https://pdlink.in/4zh9E6g

🔥 Start preparing early and improve your chances of cracking the Wipro hiring process!

📊 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

🚀 𝗔𝗜 & 𝗠𝗮𝗰𝗵𝗶𝗻𝗲 𝗟𝗲𝗮𝗿𝗻𝗶𝗻𝗴 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲

🔥 Upgrade your skills and prepare for exciting career opportunities in AI!

✅ Beginner-friendly course
✅ Learn AI & Machine Learning fundamentals
✅ Gain practical, job-ready skills
✅ Earn a FREE certificate
✅ Boost your resume and LinkedIn profile
✅ Ideal for students, freshers and professionals

🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗳𝗼𝗿 𝗙𝗥𝗘𝗘 👇:-

https://pdlink.in/4zrkYNg

⚡ Limited opportunity—start learning today!

☁️ 𝟰 𝗙𝗥𝗘𝗘 𝗚𝗼𝗼𝗴𝗹𝗲 𝗖𝗹𝗼𝘂𝗱 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 | 𝗕𝘂𝗶𝗹𝗱 𝗜𝗻-𝗗𝗲𝗺𝗮𝗻𝗱 𝗖𝗹𝗼𝘂𝗱 𝗦𝗸𝗶𝗹𝗹𝘀

Explore these Google Cloud learning resources covering cloud fundamentals, infrastructure, networking, security, data and AI/ML.

🔥 4 Courses to Explore:
1️⃣ Cloud Computing Fundamentals
2️⃣ Infrastructure in Google Cloud
3️⃣ Networking & Security in Google Cloud
4️⃣ Data, ML & AI in Google Cloud

🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:- 

https://pdlink.in/4zrksPn

🎯 Perfect for Students | Freshers | Developers | Cloud & DevOps Aspirants