Mockpreps
🧑💼
🪴
Login now and master your exams with Mockpreps’ mock tests!
Top Interview Questions
.
Microsoft Excel is one of the most widely used spreadsheet applications globally. Developed by Microsoft, it is part of the Microsoft Office Suite and is available for Windows, macOS, Android, and iOS. Excel enables users to organize, format, and calculate data efficiently, making it an indispensable tool for businesses, academics, and individuals alike.
Excel was first launched in 1985 for the Macintosh and later for Windows in 1987. Initially, Excel was developed to compete with Lotus 1-2-3, which was the dominant spreadsheet software at the time. Over the years, Excel has evolved significantly, adding advanced functionalities like pivot tables, charting, and macro programming. Each version has introduced new features that enhance usability, efficiency, and the scope of data analysis. Today, Excel is considered the industry standard for spreadsheet management and data analysis.
Excel is more than just a tool for storing data. Its wide array of features enables sophisticated calculations, data visualization, and automation. Some of its key features include:
Excel files are called workbooks, and each workbook can contain multiple worksheets. Worksheets consist of rows and columns that intersect to form cells, where data can be entered. This structure allows users to organize data logically, separating information across multiple sheets within a single file.
Excel allows users to input various types of data, including text, numbers, dates, and formulas. Formatting options, such as font styles, colors, borders, and conditional formatting, make it easier to highlight important information and improve readability. Conditional formatting, in particular, is widely used to automatically change the appearance of cells based on their values, which helps in data analysis and decision-making.
One of Excel's most powerful features is its ability to perform calculations using formulas and functions. Formulas can include arithmetic operations, logical tests, and references to other cells. Functions are predefined formulas, such as:
SUM() – Adds a range of numbers.
AVERAGE() – Calculates the mean of selected numbers.
VLOOKUP() and HLOOKUP() – Used to search for values in a table.
IF() – Performs conditional tests.
TEXT() and DATE() – Format and manipulate textual and date data.
Excel contains hundreds of built-in functions, covering mathematical, statistical, financial, and logical operations.
Visual representation of data is crucial for analysis, and Excel excels in this area. Users can create various types of charts, including column, bar, line, pie, scatter, and area charts. Charts are interactive, allowing users to modify labels, axes, and colors, which facilitates effective data communication. Advanced chart types like combo charts and sparklines provide concise visual summaries of trends and patterns.
Pivot tables are one of Excel’s most powerful tools for summarizing and analyzing large datasets. They allow users to group, filter, and aggregate data dynamically. Pivot charts, derived from pivot tables, offer visual insights into the summarized data. These features are widely used in business intelligence, financial analysis, and reporting.
Excel provides numerous tools for advanced data analysis:
What-If Analysis – Includes Goal Seek, Scenario Manager, and Data Tables to explore different outcomes based on changing variables.
Solver Add-In – Optimizes complex problems, commonly used in operations research and resource allocation.
Data Validation – Restricts input in cells to specific types or ranges.
Forecasting – Built-in forecasting tools use historical data to predict future trends.
Excel supports automation through Visual Basic for Applications (VBA). Macros are sequences of commands and actions that can be recorded or programmed to automate repetitive tasks. This capability is widely used in finance, accounting, and administrative tasks, significantly improving efficiency and reducing human error.
With Office 365 and Excel Online, multiple users can collaborate in real-time on the same workbook. Changes are tracked, and cloud storage allows access from anywhere. This is particularly useful for teams working remotely or across different locations.
Excel’s versatility makes it suitable for numerous applications across various industries. Some common uses include:
Excel is heavily used in accounting, budgeting, financial reporting, and forecasting. Professionals create balance sheets, profit and loss statements, cash flow models, and investment analyses. Advanced formulas and pivot tables allow financial analysts to interpret large datasets efficiently.
Excel is a powerful tool for statistical analysis and data management. It can handle large datasets, calculate statistical measures like mean, median, standard deviation, and perform regression analysis. Researchers and analysts frequently use Excel to clean, sort, and interpret data.
Project managers use Excel to create project timelines, Gantt charts, and resource allocation sheets. Conditional formatting and formulas help track project progress, deadlines, and budget adherence.
Students and educators use Excel to record grades, calculate averages, and visualize results. Excel also helps in teaching mathematical and statistical concepts by providing interactive examples.
Businesses use Excel to track inventory levels, sales, and orders. Advanced formulas and pivot tables allow quick analysis of stock movement, reorder points, and sales trends.
Individuals use Excel for personal budgeting, expense tracking, and planning tasks. Its simplicity and flexibility make it a valuable tool for day-to-day life management.
User-Friendly Interface: Excel’s grid layout and intuitive menus make it accessible to beginners.
Flexibility: Can handle small-scale tasks like personal budgeting and large-scale data analysis.
Integration: Compatible with other Microsoft Office tools like Word and PowerPoint.
Visualization: Charts and graphs provide clear insights.
Automation: Macros and VBA reduce manual work.
Despite its extensive capabilities, Excel has limitations:
Handling Very Large Datasets: While Excel can manage thousands of rows, extremely large datasets are better handled by databases like SQL or specialized software like Python or R.
Complexity of Advanced Features: Learning advanced formulas, pivot tables, and VBA requires time and practice.
Error Prone: Manual entry and formula errors can lead to incorrect results.
Limited Collaboration in Offline Mode: While cloud features help, traditional Excel files may create version control issues when shared offline.
Answer:
Microsoft Excel is a spreadsheet application developed by Microsoft used for data entry, data analysis, calculations, visualization, and reporting. It allows users to organize data in rows and columns and perform calculations using formulas and functions.
Answer:
A workbook is an Excel file that contains one or more worksheets.
File extension: .xlsx
Each worksheet contains rows and columns where data is stored.
Answer:
A worksheet is a single spreadsheet within a workbook.
It consists of rows (1,048,576) and columns (16,384 – A to XFD).
Each worksheet is used to store and analyze data.
Answer:
A cell is the intersection of a row and a column.
Example: A1, B5
Each cell can contain text, numbers, formulas, or functions.
Answer:
| Row | Column |
|---|---|
| Runs horizontally | Runs vertically |
| Identified by numbers (1,2,3…) | Identified by letters (A,B,C…) |
Answer:
A range is a group of selected cells.
Example: A1:A10, A1:C5
Used in formulas and functions.
Answer:
A formula is an expression that performs calculations on cell values.
Always starts with =
Example:
=A1+B1
Answer:
A function is a predefined formula in Excel that performs a specific calculation.
Example:
=SUM(A1:A10)
Answer:
| Formula | Function |
|---|---|
| User-defined calculation | Predefined in Excel |
| Example: =A1+B1 | Example: =SUM(A1:A10) |
Answer:
SUM() – Adds values
AVERAGE() – Calculates average
COUNT() – Counts numbers
COUNTA() – Counts non-empty cells
MAX() / MIN() – Finds highest/lowest value
Answer:
AutoFill automatically fills data in adjacent cells based on a pattern.
Example: Dragging numbers (1,2,3…)
Used for dates, formulas, and series.
Answer:
Conditional Formatting changes the appearance of cells based on conditions.
Example: Highlight marks greater than 80
Uses color scales, data bars, and icons.
Answer:
A chart visually represents data to make it easier to understand.
Common chart types:
Column
Bar
Line
Pie
Answer:
Sorting arranges data in a specific order.
Ascending (A–Z, smallest to largest)
Descending (Z–A, largest to smallest)
Answer:
Filtering displays only the data that meets specific criteria.
Example: Show only sales above 10,000
Uses dropdown filters.
Answer:
Freeze Panes keeps selected rows or columns visible while scrolling.
Useful for headers in large datasets.
Answer:
Wrap Text displays cell content on multiple lines within the same cell, improving readability.
Answer:
VLOOKUP searches for a value in the first column and returns a corresponding value from another column.
Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Answer:
| VLOOKUP | HLOOKUP |
|---|---|
| Searches vertically | Searches horizontally |
| Works column-wise | Works row-wise |
Answer:
The IF() function performs logical tests.
Syntax:
=IF(condition, value_if_true, value_if_false)
Example:
=IF(A1>=40,"Pass","Fail")
Answer:
A Pivot Table summarizes large amounts of data quickly.
Used for reporting and analysis
Allows grouping, filtering, and aggregating data.
Answer:
Data Validation restricts the type of data entered into a cell.
Example: Drop-down lists
Prevents invalid entries.
Answer:
Cell referencing refers to how a cell is referred in formulas.
Types:
Relative: A1
Absolute: $A$1
Mixed: $A1 or A$1
Answer:
An absolute reference does not change when copied.
Example: $A$1
Answer:
| Function | Description |
|---|---|
| COUNT | Counts numeric values |
| COUNTA | Counts non-empty cells |
| COUNTBLANK | Counts empty cells |
Answer:
Combines text from multiple cells.
Example:
=CONCAT(A1," ",B1)
Answer:
Text to Columns splits text into multiple columns.
Example: Splitting names and surnames.
Answer:
Used to search for specific data and replace it with new data.
Answer:
A macro automates repetitive tasks using VBA (Visual Basic for Applications).
Answer:
Ctrl + C – Copy
Ctrl + V – Paste
Ctrl + Z – Undo
Ctrl + S – Save
Ctrl + Shift + L – Filter
Answer:
XLOOKUP is a modern replacement for VLOOKUP and HLOOKUP. It searches for a value in a range and returns a corresponding result.
Syntax:
=XLOOKUP(lookup_value, lookup_array, return_array)
Advantages:
Works left to right or right to left
No column index needed
Handles errors easily
Answer:
| VLOOKUP | XLOOKUP |
|---|---|
| Searches only left to right | Searches both directions |
| Needs column index number | No index needed |
| Slower on large data | Faster and flexible |
Answer:
Adds values based on a single condition.
Syntax:
=SUMIF(range, criteria, sum_range)
Answer:
Adds values based on multiple conditions.
Example:
=SUMIFS(C2:C10, A2:A10,"East", B2:B10,"Laptop")
Answer:
Counts cells that meet a condition.
=COUNTIF(A1:A10,">50")
Answer:
Counts cells based on multiple criteria.
Answer:
Calculates average based on a condition.
Answer:
Power Query is a data transformation tool used to:
Import data from multiple sources
Clean and reshape data
Automate data refresh
Answer:
Power Pivot is used for advanced data modeling and handling large datasets using DAX formulas.
Answer:
Flash Fill automatically fills values based on patterns.
Example:
Separating first and last names.
Answer:
Removes extra spaces from text.
=TRIM(A1)
Answer:
Used to extract text.
=LEFT(A1,4)
=RIGHT(A1,2)
=MID(A1,2,3)
Answer:
Counts characters in a text string.
Answer:
Changes text case.
Answer:
Handles errors in formulas.
=IFERROR(A1/B1,"Error")
Answer:
Occurs when a value is not found during lookup.
Answer:
Occurs when dividing by zero.
Answer:
| Sorting | Filtering |
|---|---|
| Rearranges data | Shows selected data |
| Permanent order | Temporary view |
Answer:
An Excel Table organizes data with:
Automatic filtering
Structured references
Easy expansion
Answer:
Applies formatting based on formula conditions.
Answer:
Mini charts inside a cell.
Answer:
Used to analyze different scenarios.
Goal Seek
Scenario Manager
Data Tables
Answer:
Finds the input value required to achieve a desired result.
Answer:
Prevents unauthorized changes.
Answer:
Used to hide sensitive or unnecessary data.
Answer:
Allows pasting values, formats, formulas, etc.
Answer:
Links to files, websites, or cells.
Answer:
Deletes duplicate rows.
Answer:
Keeps header row visible.
Answer:
Data analysis
Reporting
Budgeting
Inventory management
MIS reports
Answer:
In real-time projects, the most commonly used Excel features include:
Advanced formulas: VLOOKUP/XLOOKUP, INDEX–MATCH, SUMIFS, COUNTIFS
Pivot Tables & Pivot Charts for summarization
Power Query for data cleaning and transformation
Conditional Formatting for visual analysis
Data Validation for controlling user inputs
Macros (VBA) for automation
What-If Analysis (Goal Seek, Scenario Manager)
Charts and Dashboards for reporting
| Feature | VLOOKUP | HLOOKUP | XLOOKUP |
|---|---|---|---|
| Search Direction | Vertical | Horizontal | Any direction |
| Left Lookup | ❌ No | ❌ No | ✅ Yes |
| Column Number Required | ✅ Yes | ✅ Yes | ❌ No |
| Error Handling | Weak | Weak | Strong |
| Performance | Moderate | Moderate | Best |
XLOOKUP is preferred because it is more flexible, readable, and future-proof.
Answer:
INDEX returns a value from a specified position, while MATCH returns the position of a value.
=INDEX(B2:B10, MATCH(E2, A2:A10, 0))
This combination:
Works left-to-right or right-to-left
Is faster than VLOOKUP
Is less prone to column insertion issues
Answer:
Power Query is a data transformation tool used to:
Import data from multiple sources (CSV, SQL, Web, Excel)
Clean data (remove duplicates, trim spaces)
Merge and append tables
Automate repetitive data preparation tasks
Once created, queries can be refreshed automatically.
| Pivot Table | Power Pivot |
|---|---|
| Handles small data | Handles large datasets |
| Simple calculations | Advanced DAX calculations |
| Limited relationships | Multiple table relationships |
Calculated Field: Used in Pivot Tables, calculated at runtime.
Calculated Column: Created in Power Pivot using DAX, stored per row.
IF Example:
=IF(A1>=50,"Pass","Fail")
Nested IF becomes complex and hard to maintain.
IFS simplifies multiple conditions:
=IFS(A1>=90,"A",A1>=75,"B",A1>=60,"C",TRUE,"Fail")
Answer:
Conditional Formatting highlights data based on rules.
Example Use Case:
Highlight overdue invoices in red
Show top 10 sales values
Data bars for performance tracking
Answer:
Copy data to a new sheet
Use Remove Duplicates
Or use:
=UNIQUE(A2:A100)
(Excel 365)
| Function | Counts |
|---|---|
| COUNT | Numbers only |
| COUNTA | Non-empty cells |
| COUNTBLANK | Empty cells |
Answer:
Using IFERROR:
=IFERROR(A1/B1, "Not Available")
This avoids #DIV/0!, #N/A, etc.
Answer:
What-If Analysis predicts outcomes by changing inputs:
Goal Seek – finds input for a desired result
Scenario Manager – compares multiple scenarios
Data Tables – sensitivity analysis
Answer:
Macros automate repetitive tasks using VBA.
Example:
Sub ClearData()
Range("A2:D100").ClearContents
End Sub
Used for:
Report generation
Formatting automation
Data cleaning
Workbook: Entire Excel file (.xlsx)
Worksheet: Individual sheet inside a workbook
Answer:
Protect Sheet (lock cells)
Protect Workbook (structure)
Password protection
Hide formulas
Answer:
Excel Tables:
Auto-expand formulas
Structured references
Easy filtering and sorting
Better performance with formulas
Answer:
Use Excel Tables
Avoid volatile functions (OFFSET, INDIRECT)
Use Power Query instead of formulas
Turn off automatic calculation temporarily
Use Pivot Tables instead of heavy formulas
TEXT converts number to text
VALUE converts text to number
FORMAT applies display format only
Answer:
DAX (Data Analysis Expressions) is used in Power Pivot and Power BI for:
Calculated columns
Measures
Advanced analytics
Q: Sales data has duplicates, blanks, and inconsistent dates. How do you handle it?
Answer:
Load data into Power Query
Remove duplicates
Standardize date formats
Handle null values
Load cleaned data to Excel
Build Pivot Table for reporting
Freeze Panes locks rows/columns permanently
Split divides screen temporarily
Answer:
Use Pivot Tables
Add slicers and timelines
Use charts and KPIs
Use conditional formatting
Hide raw data
| Type | Example |
|---|---|
| Relative | A1 |
| Absolute | $A$1 |
| Mixed | A$1 or $A1 |
CSV: Plain text, no formulas or formatting
XLSX: Supports formulas, charts, formatting
Answer:
Yes, Excel can connect to databases using:
Power Query
ODBC connections
SQL queries
Used for reporting and analysis.
Answer:
Volatile functions recalculate every time any cell in the workbook changes, which can slow down performance.
Examples:
NOW()
TODAY()
RAND()
OFFSET()
INDIRECT()
Why avoid:
In large datasets, volatile functions cause unnecessary recalculations and performance issues.
| OFFSET | INDEX |
|---|---|
| Volatile | Non-volatile |
| Slower | Faster |
| Dynamic range | Dynamic but stable |
Best Practice:
Use INDEX instead of OFFSET for better performance.
Answer:
Use Power Query to load and clean data
Use Power Pivot data model
Avoid array formulas on raw data
Use Pivot Tables instead of formulas
Disable auto calculation during processing
| Excel Table | Normal Range |
|---|---|
| Structured references | Cell references |
| Auto-expand | Manual |
| Better performance | Lower efficiency |
Answer:
Filter: Dropdown-based, less visual
Slicer: Visual, interactive filtering for Pivot Tables and Tables
Used in dashboards for better user experience.
Answer:
Array formulas perform calculations on multiple values.
Dynamic Array functions:
FILTER()
SORT()
UNIQUE()
SEQUENCE()
Example:
=FILTER(A2:C100, C2:C100="Completed")
| SUMIFS | SUMPRODUCT |
|---|---|
| Faster | Slower |
| Simple conditions | Complex logic |
| No array logic | Supports array logic |
Use SUMIFS where possible.
Methods:
COUNTIF
=COUNTIF(B:B, A2)
Conditional Formatting
Power Query (anti-join)
XLOOKUP
Answer:
Power Pivot handles:
Millions of rows
Data relationships
DAX calculations
Used when data is too large or complex for standard Pivot Tables.
| Calculated Column | Measure |
|---|---|
| Row-by-row | Aggregated |
| Stored in model | Calculated at runtime |
| Increases size | Optimized |
XLOOKUP is the most flexible and readable.
INDEX-MATCH offers backward compatibility and speed.
LOOKUP is outdated and error-prone.
Answer:
Named ranges improve:
Formula readability
Reusability
Dashboard management
Answer:
Trace precedents/dependents
Evaluate Formula tool
Formula Auditing toolbar
Break formula into helper columns
Answer:
Used to split data based on:
Delimiter (comma, space)
Fixed width
Commonly used for CSV cleanup.
Answer:
Column/Bar: Comparisons
Line: Trends
Pie: Contribution
Combo: Dual metrics
Waterfall: Financial analysis
Answer:
TRIM() for spaces
CLEAN() for non-printable characters
PROPER(), UPPER(), LOWER()
Power Query transformations
| IFERROR | IFNA |
|---|---|
| Handles all errors | Handles #N/A only |
| Hides real issues | Safer for lookups |
Answer:
Find required sales to reach profit target:
Set cell: Profit
To value: Target profit
By changing: Sales
Answer:
Power Query for data refresh
Pivot Tables for summarization
VBA macros for formatting
Scheduled refresh (if linked to DB)
Answer:
Row limit: 1,048,576
Performance with heavy formulas
Not ideal for real-time multi-user editing
Answer:
Password protect workbook
Lock formulas
Hide sheets
Restrict editing using Protect Sheet
Workbook: Structure protection
Sheet: Cell-level protection
Answer:
Automatically detects patterns and fills data.
Answer:
Track Changes (older versions)
Version history (OneDrive)
Compare Workbooks
Q: Data comes daily with different column orders. How do you handle it?
Answer:
Use Power Query
Reference column names instead of positions
Refresh query automatically
| FILTER | Advanced Filter |
|---|---|
| Dynamic | Static |
| Formula-based | UI-based |
Answer:
=IF(A1="", "", A1*10)
Answer:
Solver finds optimal solutions under constraints (optimization problems).
Answer:
One-page design
Consistent color themes
Interactive slicers
Minimal clutter
KPIs at top
Q: Workbook is slow. What steps do you take?
Answer:
Identify volatile formulas
Reduce array formulas
Use Power Query
Optimize Pivot Tables
Switch to manual calculation
Answer:
SQL Server (ODBC)
Power BI
APIs (Power Query)
CSV/XML imports
| Automatic | Manual |
|---|---|
| Real-time updates | Faster processing |
| Slower on big files | Needs F9 |
Answer:
Allows multiple tables, relationships, and advanced analytics using Power Pivot.
Answer:
Key Performance Indicators show performance against targets using icons or measures.
Q: How do you create a self-updating Excel dashboard?
Answer:
Power Query for data ingestion
Data Model with relationships
Measures using DAX
Pivot Charts + slicers
One-click refresh
