🚀 Excel Interview Questions with Answers — Part 6

51. How do you sort data by one or multiple columns?
Sorting arranges data in ascending or descending order.

Single Column Sort:
1. Select data
2. Go to: Data → Sort A to Z
OR
Sort Z to A

📌 Example: Sort employee names alphabetically.

Multiple Column Sort:
1. Select dataset
2. Go to: Data → Sort
3. Add levels

Example:
First sort by Department
Then by Salary Highest to Lowest

📌 Useful for hierarchical analysis.

52. How do you use AutoFilter and custom filters?
AutoFilter helps display only relevant records.

Enable Filter:
Data → Filter
Shortcut: Ctrl + Shift + L

Filter Types:
• Text filters
• Number filters
• Date filters
• Color filters

Custom Filter Example:
Show sales greater than 50,000.

📌 Helps analyze specific subsets quickly.

53. How do you filter by blanks, non-blanks, or specific values?
Filter Blank Cells:
1. Open filter dropdown
2. Select: Blanks

Filter Non-Blanks:
Uncheck Blanks.

Filter Specific Values:
Select required values from checklist.

📌 Example: Show only “Completed” orders.

54. How do you remove duplicates from a list?
Steps:
1. Select data
2. Go to: Data → Remove Duplicates
3. Choose columns to check

📌 Example: Remove duplicate customer IDs.

Important:
⚠️ Always keep backup before removing duplicates because deletion is permanent.

55. How do you use conditional formatting with formulas?
Conditional formatting can use formulas for advanced highlighting.

Steps:
1. Select range
2. Go to: Conditional Formatting → New Rule → Use a Formula

Example:
Highlight values greater than average.
=A1>AVERAGE(A1:A10)

Example:
Highlight duplicate values.
=COUNTIF(A1:A10,A1)>1

📌 Useful for anomaly detection and dashboards.

56. How do you highlight top/bottom N values?
Steps:
1. Select data
2. Go to: Conditional Formatting → Top/Bottom Rules

Options:
• Top 10 Items
• Bottom 10 Items
• Top 10%
• Bottom 10%

📌 Example: Highlight top 5 sales performers.

57. How do you standardize and clean messy data e.g. “Yes / yes / Y” → “Yes”?
Data cleaning improves consistency and analysis quality.

Common Techniques:
Function : Purpose
TRIM : Remove extra spaces
UPPER/LOWER/PROPER : Standardize case
SUBSTITUTE : Replace text
CLEAN : Remove hidden characters

Example:
=PROPER(TRIM(A1))

Standardizing Values:
=IF(OR(A1="Y",A1="yes",A1="Yes"),"Yes","No")

📌 Very important in real-world reporting.

58. How do you use Text-to-Columns and Flash Fill for quick cleaning?
Text-to-Columns:
Splits data into separate columns.

Steps:
1. Select column
2. Go to: Data → Text to Columns

Delimiters:
• Comma
• Space
• Tab
• Custom symbol

📌 Example: Split "John,Doe" into two columns.

Flash Fill:
Automatically detects patterns.
Shortcut: Ctrl + E

📌 Example: Extract first names from full names automatically.

59. How do you create subtotals and grouped views?
Subtotals:
1. Sort data first
2. Go to: Data → Subtotal

Example:
Show total sales department-wise.

Excel can calculate:
• SUM
• COUNT
• AVERAGE
• MAX/MIN

Grouped Views:
Use: Data → Group

📌 Allows expanding/collapsing sections for cleaner reports.

60. How do you build a simple KPI or summary table?
A KPI table summarizes important business metrics.

Common KPIs:
• Total Sales
• Profit
• Growth %
• Customer Count
• Conversion Rate

Example Formula:
=SUM(B2:B100)

Percentage Growth:
Growth % = (Current - Previous) / Previous × 100

Excel Formula:
=(B2-A2)/A2

Best Practices for KPI Tables:
✅ Use conditional formatting
✅ Add charts/sparklines
✅ Keep layout clean
✅ Highlight important metrics

Double Tap ❤️ For Part-7