🚀 Excel Interview Questions with Answers — Part 9

81. What is Microsoft Power Query and how do you use it for data cleaning?
Microsoft Power Query is a data transformation tool in Microsoft Excel used to import, clean, and reshape data automatically.

Common Data Cleaning Tasks:
✅ Remove duplicates
✅ Change data types
✅ Split/merge columns
✅ Remove blanks
✅ Pivot/unpivot data
✅ Standardize formats

Access Power Query:
Data → Get Data

Why Analysts Use It:
✅ Saves time
✅ Reduces manual work
✅ Refreshes automatically
✅ Handles large datasets efficiently

📌 Example: Clean raw sales data from CSV files daily.

82. How do you import and transform data filter, group, pivot, unpivot using Microsoft Power Query?
Import Data:
Data → Get Data → From File

Common Transformations:
Transformation : Purpose
Filter Rows : Remove unnecessary data
Group By : Aggregate data
Pivot Columns : Convert rows into columns
Unpivot Columns : Convert columns into rows

Example:
Convert monthly sales columns into normalized rows using “Unpivot”.

📌 Useful for:
- Dashboard preparation
- Database-style formatting
- Reporting automation

83. How do you refresh a query that pulls from a source file?
Queries can be refreshed whenever source data changes.

Methods:
- Right-click query → Refresh
- Use:
Data → Refresh All
- Shortcut:
Ctrl + Alt + F5

Automatic Refresh:
Can refresh:
- On file open
- At intervals

📌 Example: Update daily sales report automatically from latest CSV.

84. How do you load cleaned data into a PivotTable or worksheet?
After transformation:

Steps:
1. Click:
Close & Load
2. Choose destination:
Worksheet
PivotTable
Data Model

Benefits:
✅ Clean structured data
✅ Faster analysis
✅ Dynamic reporting

📌 Common workflow: Raw Data → Power Query → PivotTable → Dashboard

85. What is Microsoft Power Pivot and DAX high-level overview?
Microsoft Power Pivot is an Excel feature used for advanced data modeling and relationships.

Features:
✅ Handle millions of rows
✅ Create relationships between tables
✅ Build calculated measures

DAX Data Analysis Expressions:
Formula language used in Power Pivot and Microsoft Power BI.

Example DAX Formula:
Total Sales = SUM(Sales[Amount])

📌 Used for:
- KPIs
- Business metrics
- Advanced dashboards

86. How do you use tables and structured references in formulas?
Excel Tables create dynamic structured data ranges.

Create Table:
Shortcut:
Ctrl + T

Structured Reference Example:
Instead of:
=SUM(A2:A100)

Use:
=SUM(Sales[Revenue])

Benefits:
✅ Easier to read
✅ Dynamic ranges
✅ Auto-expand with new data
✅ Better for dashboards

87. How do you protect and share an Excel file for multiple users?
Protection Options:
Feature : Purpose
Protect Sheet : Prevent editing
Protect Workbook : Prevent structural changes
Password Encryption : Restrict file access

Share Options:
✅ OneDrive
✅ Microsoft SharePoint
✅ Email attachment
✅ Shared network drive

Collaboration Features:
✅ Real-time editing
✅ Comments
✅ Version history

📌 Useful for team reporting environments.

88. How do you create dynamic dashboards with slicers and linked PivotTables?
Steps:
1. Create PivotTables
2. Create PivotCharts
3. Insert slicers:
PivotTable Analyze → Insert Slicer
4. Connect slicers to multiple PivotTables

Benefits:
✅ Interactive filtering
✅ User-friendly dashboards
✅ Real-time insights

📌 Example: Filter dashboard by:
- Region
- Product
- Year

89. How do you export Excel reports to PDF or CSV?
Export as PDF:
File → Save As → PDF

Export as CSV:
File → Save As → CSV

Difference:
Format : Purpose
PDF : Sharing reports
CSV : Data transfer/import

📌 CSV removes formatting and formulas.