This part focuses on Power Query, PivotTables, charts, data cleaning, and practical interview scenarios.
1๏ธโฃ What is Power Query in Excel?
Sample Answer:
Power Query is a data transformation and preparation tool in Excel.
I can use it to:
โ Import data from different sources
โ Clean and transform data
โ Remove duplicates
โ Handle missing values
โ Split and merge columns
โ Change data types
โ Combine multiple files
โ Load the final dataset into Excel
The major advantage is that the transformation steps can be saved and refreshed when new data arrives.
2๏ธโฃ What is the difference between Power Query and formulas?
Sample Answer:
Formulas are useful for calculations and analysis directly within the worksheet.
Power Query is more suitable for repeatable data cleaning and transformation.
For example, if I receive the same monthly sales file every month, I can create a Power Query process once and refresh it instead of manually cleaning every file.
3๏ธโฃ What is the difference between a PivotTable and a normal table?
Sample Answer:
A normal table stores and organizes the underlying data.
A PivotTable summarizes and analyzes that data.
For example, from a sales table containing thousands of transactions, I can create a PivotTable showing:
โ Total sales by region
โ Sales by product
โ Monthly sales
โ Number of transactions
4๏ธโฃ What are Slicers in Excel?
Sample Answer:
Slicers are visual filters that allow users to filter Tables or PivotTables interactively.
For example, a sales dashboard could have slicers for:
โ Region
โ Product
โ Year
โ Salesperson
Instead of opening filter dropdowns, users can simply click the required option.
5๏ธโฃ What is a PivotChart?
Sample Answer:
A PivotChart is a chart connected to a PivotTable.
It automatically responds when the PivotTable is filtered or changed.
For example, I can create a PivotChart showing monthly revenue and then use a Region slicer to see the trend for a specific region.
6๏ธโฃ How would you compare actual sales with target sales?
Sample Answer:
First, I would calculate the variance.
For example:
=Actual Sales - Target Sales
Then I could calculate the achievement percentage:
=Actual Sales / Target Sales
I could use Conditional Formatting to highlight:
๐ข Above target
๐ด Below target
I could then summarize the results using a PivotTable or dashboard.
7๏ธโฃ How would you find the top-performing salesperson?
Sample Answer:
If I only need the highest sales value, I can use:
=MAX(B2:B100)
If I need the salesperson associated with that value, I could use XLOOKUP:
=XLOOKUP(MAX(B2:B100),B2:B100,A2:A100)
In a larger analysis, I could also use a PivotTable and sort sales in descending order.
8๏ธโฃ How would you calculate employee tenure?
Sample Answer:
If the joining date is in A2, I can use:
=DATEDIF(A2,TODAY(),"Y")
This returns completed years of service.
If I need years and months:
=DATEDIF(A2,TODAY(),"Y")&" Years "&DATEDIF(A2,TODAY(),"YM")&" Months"
9๏ธโฃ How would you identify overdue transactions?
Sample Answer:
I would compare the due date with today's date.
For example:
=IF(B2<TODAY(),"Overdue","Not Overdue")
If I also need to consider whether the payment has already been completed:
=IF(AND(B2<TODAY(),C2<>"Paid"),"Overdue","Not Overdue")