Top 25 Expert-Approved Excel Interview Questions and Answers 2024 for Beginners
Beginner-Level Excel Interview Questions
Table of contents:
- What is a cell address in Excel?
- What do you mean by Relative cell referencing and Absolute cell referencing in MS Excel?
- How do you freeze panes in Excel? (Must asked Excel Interview Questions)
- How can you restrict someone from copying a cell from your worksheet?
- How is a Formula different from a Function in Excel?
- Mention the order of operations used in Excel while evaluating formulas.
- How will you write the formula for the following? – Multiply the value in cell A1 by 10, add the result by 5, and divide it by 2
- What is the difference between count, counta, and countblank? (Must asked Excel Interview Questions)
- What is the shortcut to add a filter to a table?
- How do you create a hyperlink in Excel? (Must asked Excel Interview Questions)
- How can we merge multiple cells’ text strings in a cell?
- How can you split a column into 2 or more columns?
- What is the use of VLOOKUP and how do we use it? (Must asked Excel Interview Questions)
- How is VLOOKUP different from the LOOKUP function?
- How many report formats are available in Excel? (Must asked Excel Interview Questions)
- How does the IF() function in Excel work? (Must asked Excel Interview Questions)
- How do we use the SUMIF() function in Excel? (Must asked Excel Interview Questions)
- Using the COVID data, find the number of days in which the number of deaths in Italy has been greater than 200
- What is a Pivot Table
- Create a drop-down list in Excel. (Must asked Excel Interview Questions)
- How do we apply advanced filters in Excel?
- Using the below-given sales data, highlight those cells where total sales > $5000.
- Using the given table, explain how the index-match function works in Excel
- How do you find duplicate values in a column (Must asked Excel Interview Questions)
- How can you remove duplicate values in a range of cells?
1 . What is a cell address in Excel?
Answer –
When it comes to spreadsheets, a cell address is any distinct location for a cell. It’s more or less like having an address for every box in a grid. By this, I mean that each of these cells has its own perfect home which is pointed out using both letters and numerals. The letter refers to the column while the number represents the row.
For instance, if you have names in your table, different addresses are there for cells containing names such as “A1” or “B2”. Hence, in case one is interested in particular information on the sheet, Excel looks to these addresses so that it can pinpoint exactly where it is placed.
To put it simply, just as street addresses help you find places on a map, so do cell addresses allow you to move around your Excel sheet. This makes it easier for one to locate and arrange data within his/her worksheet.
2. What do you mean by Relative cell referencing and Absolute cell referencing in MS Excel?
Answer –
Relative cell referencing and absolute cell referencing are two methods used in MS Excel to refer to cells in formulas.
In relative cell referencing, the reference changes based on the location of the formula. For example, if you copy a formula from cell A1 to cell A2, the reference automatically adjusts from A1 to A2.
In absolute cell referencing, the reference remains fixed, regardless of where the formula is copied. Adding a dollar sign ($) before the column letter and row number, such as $A$1, denotes this.
3. How do you freeze panes in Excel? (Must asked Excel Interview Questions)
Answer –
Select the row or column below or to the right of where you want the freeze to occur.
Navigate to the “View” tab.
Choose “Freeze Panes” from the options.
Now, the selected rows and columns will remain visible as you scroll through the worksheet.
4. How can you restrict someone from copying a cell from your worksheet?
Answer –
To prevent someone from copying a cell, protect the worksheet and choose the ‘Locked’ option for the specific cell
5. How is a Formula different from a Function in Excel?
Answer –
Formulas in Excel are like recipes for cooking up numbers. You start with an equal sign (=) and then mix numbers, cells, and special commands to get the result you want. It’s like following a recipe to bake a cake, but instead of flour and eggs, you’re using numbers and operations.
Functions, on the other hand, are like ready-made tools that Excel gives you. They’re like having a magic wand that instantly performs specific tasks. Need to find the total of a bunch of numbers? There’s a function for that! Want to count how many items are on a list? There’s a function for that too!
So, while formulas are your custom-made solutions, functions are like the shortcuts that make your life easier in Excel. They work together to help you crunch numbers and get things done in your spreadsheets
6. Mention the order of operations used in Excel while evaluating formulas.
Answer –
In Excel, when evaluating formulas, it follows a specific order of operations, just like in math class. First, Excel tackles anything inside parentheses, then it handles exponentiation (^), followed by multiplication (*) and division (/), and finally, it deals with addition (+) and subtraction (-). This sequence ensures that calculations are carried out accurately and consistently
7. How will you write the formula for the following? – Multiply the value in cell A1 by 10, add the result by 5, and divide it by 2.
Answer –
To write the formula for this task, follow these steps:
First, multiply the value in cell A1 by 10.
Then, add the result by 5.
Finally, divide the sum by 2.
In Excel, you can write this formula as follows:
=(A1*10 + 5) / 2
8. What is the difference between count, counta, and countblank? (Must asked Excel Interview Questions)
Answer –
COUNT: This function counts the number of cells in a range that contain numbers.
COUNTA: It counts the number of cells in a range that are not empty, including those containing text, numbers, or errors.
COUNTBLANK: This function counts the number of empty cells in a range.
In summary, COUNT counts only numeric values, COUNTA counts all non-empty cells, including text and errors, while COUNTBLANK counts the number of empty cells in a range.
9. What is the shortcut to add a filter to a table?
Answer –
To add a filter to a table in Excel, use the shortcut:
Alt + Shift + L
This keyboard shortcut instantly applies a filter to your table, making data analysis a breeze.
10. How do you create a hyperlink in Excel? (Must asked Excel Interview Questions)
Answer –
- Choose the desired cell for your hyperlink.
- Employ the shortcut
Ctrl + Kor right-click the cell and opt for “Hyperlink.” - Engage the dialog box to insert your URL or preferred destination.
- Seal the deal by hitting “OK.”
11. How can we merge multiple cells’ text strings in a cell?
Answer –
To merge text from different cells into one in Excel, you use a formula called CONCATENATE.
- Click the cell where you want the merged text.
- Type =CONCATENATE( in the formula bar.
- Select the cells you want to merge.
- Close the formula with ) and press Enter.
For example, =CONCATENATE(A1, B1, C1) merges text from cells A1, B1, and C1 into one. After entering, it shows the combined text.
12. How can you split a column into 2 or more columns?
Answer –
To split a column into two or more columns in Excel, you can use the Text to Columns feature.
- Select the column you want to split.
- Go to the Data tab on the Excel ribbon.
- Click on the Text to Columns option.
- Choose the delimiter that separates the text in your column (such as comma, space, or other custom characters).
- Specify where you want to place the split data (in the same worksheet or a new one).
- Click Finish to split the column into multiple columns based on your chosen delimiter.
13. What is the use of VLOOKUP and how do we use it? (Must asked Excel Interview Questions)
Answer –
VLOOKUP is a function in Excel used to search for a value in the first column of a table and return a corresponding value in the same row from another column. Here’s how you can use it:
Start by typing “=VLOOKUP(” in the cell where you want the result.
Provide the particular value you want to search for.
Specify the range of cells where Excel should search for the value.
Indicate the column index number that contains the value you want to return.
Choose “TRUE” for an approximate match or “FALSE” for an exact match.
Close the parentheses and press Enter.
For example, if you have a table of employee names and their salaries, you can use VLOOKUP to find a specific employee’s salary by entering their name.
14. How is VLOOKUP different from the LOOKUP function?
Answer –
VLOOKUP and LOOKUP functions serve different purposes in Excel:
1. **VLOOKUP (Vertical Lookup)**:
– Vlookup searches for a value in the first column of a table array and returns a value in the same row from a specified column.
– Requires specifying the lookup value, table array, column index number, and optionally, the range lookup (approximate or exact match).
– Useful for finding specific values in large datasets, such as looking up a product price based on its code.
2. **LOOKUP Function**:
– It searches for a value in a one-row or one-column range and returns a value from the same position in a second one-row or one-column range.
– Does not require the data to be sorted and can handle unsorted data more flexibly than VLOOKUP.
– Often used for simple approximate matching scenarios or when dealing with small datasets.
In summary, VLOOKUP is specifically designed for vertical lookup tasks within tables, while the LOOKUP function is more general and can handle various lookup scenarios, but with less precision and flexibility compared to VLOOKUP.
15. How many report formats are available in Excel? (Must asked Excel Interview Questions)
Answer –
In Excel, there isn’t a fixed number of “report formats” per se. Instead, the term refers to various ways you can present data in Excel to create reports. Some common report formats include:
Tabular Reports: Tabular Reports are simple tables presenting data in rows and columns.
Pivot Tables: Summarize and analyze data interactively.
Charts and Graphs: Visual representations of data, such as bar graphs, pie charts, and line graphs.
Dashboard Reports: Combine multiple charts and tables on a single sheet to provide a comprehensive overview of data.
Conditional Formatting: Apply formatting rules based on cell values to highlight important information.
Sparklines: Miniature charts embedded in individual cells to show trends.
These are just a few examples, but the possibilities are virtually limitless depending on your data and reporting needs. Excel offers a wide range of tools and features to customize and create various report formats tailored to specific requirements.
16. How does the IF() function in Excel work? (Must asked Excel Interview Questions)
Answer –
The IF() function in Excel works like a decision-maker, allowing you to set conditions and tell Excel what to do based on those conditions.
Here’s how it operates:
– First, you define a condition, like “Is this number greater than 10?”
– Then, you specify two possible outcomes: one if the condition is true, and another if it’s false.
– Excel evaluates the condition and performs the action based on whether it’s true or false.
Think of it as giving Excel instructions: “If this happens, do that; if not, do something else.”
In essence, the IF() function helps Excel make choices and take action, making your spreadsheets smarter and more adaptable to different scenarios.
17. How do we use the SUMIF() function in Excel? (Must asked Excel Interview Questions)
Answer –
Using the SUMIF() function in Excel is like having a smart assistant to add up specific numbers based on certain conditions.
Here’s how it works:
– First, you define the range of cells to check and the condition you want to apply. For example, you might want to add up all sales above $100.
– Then, you specify the range of cells containing the numbers you want to add if they meet the condition.
– Excel checks each cell in the defined range. If it meets the condition, Excel includes it in the sum.
– Finally, Excel adds up all the numbers that meet the condition and gives you the total.
In a nutshell, the SUMIF() function helps you quickly sum up values that meet specific criteria, saving you time and effort in your Excel calculations.
18. Using the COVID data, find the number of days in which the number of deaths in Italy has been greater than 200
Answer –
To find the number of days when the number of deaths in Italy exceeded 200 using COVID data, follow these steps:
1. **Access the COVID data**: Obtain the dataset containing information on daily deaths due to COVID-19 in Italy.
2. **Organize the data**: Ensure the data is organized in a table format with dates in one column and the corresponding number of deaths in another.
3. **Filter for Italy**: If the dataset contains data from multiple countries, filter the data to include only the information related to Italy.
4. **Set the condition**: Create a condition where the number of deaths in Italy is greater than 200.
5. **Count the days**: Using Excel functions or formulas, count the number of days where the number of deaths in Italy exceeds 200.
6. **Review the result**: Once the calculation is complete, review the output to determine the total number of days that meet the condition.
By following these steps, you can efficiently identify the days when the number of deaths in Italy surpassed 200 based on the COVID data provided.
19. What is a Pivot Table?
Answer –
A Pivot Table is a powerful feature in Excel that allows you to summarize and analyze large amounts of data quickly and easily. It enables users to rearrange and reorganize data from a spreadsheet or external source into a concise, meaningful format without altering the original data.
Here’s how it works:
- Data organization: You start by selecting the data you want to analyze. This could include rows and columns of numbers, text, or other data types.
- Inserting the Pivot Table: Once your data is selected, you insert a Pivot Table from the Excel menu. This creates a new sheet where your Pivot Table will be displayed.
- Field arrangement: In the Pivot Table interface, you have rows, columns, values, and filters. You drag and drop fields from your data into these areas to arrange your data the way you want to analyze it.
- Data summarization: The Pivot Table then automatically summarizes your data based on your field arrangements. For example, it can calculate sums, averages, counts, and other statistical measures for your data.
- Dynamic analysis: One of the key features of Pivot Tables is their dynamic nature. You can easily change the arrangement of your fields to view your data from different perspectives without altering the original dataset.
- Visual representation: Pivot Tables also allow for visual representation of data through charts and graphs, making it easier to interpret and understand trends and patterns in your data.
In summary, Pivot Tables in Excel are a flexible and efficient tool for analyzing and summarizing large datasets, providing users with valuable insights into their data with minimal effort
20. Create a drop-down list in Excel. (Must asked Excel Interview Questions)
Answer –
Creating a drop-down list in Excel allows you to provide predefined options for data entry, making it easier to select from a list rather than typing manually. Here’s how you can do it:
Select your cell range: First, select the cells where you want the drop-down list to appear.
Go to the Data tab: In Excel, navigate to the “Data” tab on the ribbon at the top of the window.
Data Validation: click on the “Data Validation” button. This will open the Data Validation dialog box.
Choose the List option: In the Settings tab of the Data Validation dialog box, select “List” from the Allow dropdown menu.
Enter your list values: In the Source field, you can either type in the list of values directly (separated by commas) or select a range of cells where your list values are located.
Optional settings: You can choose to ignore blank cells, enforce strict data entry, or show an error message if an invalid entry is made.
Apply the validation: Once you’ve set up your list, click OK to apply the data validation to the selected cell range.
21. How do we apply advanced filters in Excel?
Answer –
To perform advanced filtering in Excel, you’re essentially diving deeper into your data to uncover specific insights or patterns. Here’s a unique breakdown:
Data Readiness: Make sure your data is neatly organized with clear headers. This sets the stage for efficient filtering.
Data Selection: Click on any cell within your dataset to select it. This signals to Excel that you’re about to perform some operations on this data.
Access Advanced Options: Head over to the “Data” tab on the Excel ribbon. Here, you’ll find the tools for manipulating your data. Look for the “Advanced” button in the “Sort & Filter” group, and give it a click.
Criteria Definition: Excel now presents you with a dialog box where you can define your filtering criteria. Think of this as setting the rules for what data you want to see and what you want to hide.
Specify the Range: Indicate the range of cells that contain your data and where your filtering criteria are located. This helps Excel understand which data to filter and based on what rules.
Filter Implementation: With your criteria set and ranges specified, it’s time to tell Excel what to do. You can choose to filter the data in place, replacing the current view with the filtered one, or you can copy the filtered data to another location if you want to keep the original intact.
Application and Observation: Click “OK” and witness the magic as Excel applies your criteria to your dataset, instantly filtering out the unwanted rows and leaving you with a refined, more focused view of your data.
22. Using the below-given sales data, highlight those cells where total sales are> $5000.
Answer –
To highlight cells in Excel where total sales exceed $5000, follow these steps:
Select Your Data: Begin by selecting the range of cells that contain your sales data. This typically includes columns for items sold and their corresponding sales figures.
Access Conditional Formatting: Go to the “Home” tab on the Excel ribbon. Look for the “Conditional Formatting” option in the toolbar.
Choose Highlight Cells Rules: Click on “Conditional Formatting” and then select “Highlight Cells Rules” from the dropdown menu.
Set the Condition: Choose “Greater Than” from the list of options. In the dialog box that appears, enter “5000” as the value. This tells Excel to highlight cells where the sales figure is greater than $5000.
Apply Formatting: After setting the condition, you can choose the formatting style you prefer for the highlighted cells. This could be a different font color, background color, or both.
Confirm and Apply: Once you’ve selected your formatting options, click “OK” to apply the conditional formatting to your selected range.
23. Explain how the index-match function works in Excel
Answer –
The INDEX-MATCH function in Excel is a powerful tool used to retrieve data from a table based on specific criteria. Here’s how it works uniquely and simply:
1. **Understanding INDEX**: The INDEX function returns the value of a cell in a specified range based on its row and column numbers. It’s like locating a specific item in a grid by knowing its row and column coordinates.
2. **Understanding MATCH**: The MATCH function searches for a specific value in a range of cells and returns its relative position. It’s like finding the position of a book on a shelf based on its title.
3. **Combining INDEX and MATCH**: Instead of using traditional lookup functions like VLOOKUP or HLOOKUP, INDEX and MATCH work together seamlessly to perform more flexible and dynamic lookups.
4. **How It Works**: The MATCH function searches for a value in a specific column (or row) of a table and returns its relative position. Then, the INDEX function uses this position to retrieve the corresponding value from another column (or row) in the same table.
5. **Benefits**: Unlike VLOOKUP, which requires the lookup value to be in the leftmost column of the table, INDEX-MATCH allows you to search in any column and return values from any other column. It’s more versatile and avoids the limitations of VLOOKUP.
6. **Flexibility**: INDEX-MATCH is particularly useful when dealing with large datasets or when the lookup value may change. It offers greater flexibility and accuracy in retrieving data from tables.
In summary, the INDEX-MATCH function duo provides a robust and flexible way to search for and retrieve data in Excel tables, making it a preferred choice for many Excel users.
24. explain in a unique and simple way (Must asked Excel Interview Questions)
Answer –
Finding duplicate values in a column in Excel is essential for data analysis and cleanup. Here’s a unique and simple way to do it:
- Conditional Formatting: Excel’s conditional formatting feature helps identify duplicate values in a column by highlighting them in a distinctive color.
- Select the Column: First, select the column where you want to find duplicate values.
- Conditional Formatting Option: Then, navigate to the “Home” tab on the Excel ribbon and locate the “Conditional Formatting” option in the toolbar.
- Highlight Cells Rules: Click on “Conditional Formatting” and choose “Highlight Cells Rules” from the dropdown menu.
- Duplicate Values: In the submenu, select “Duplicate Values.” Excel will open a dialog box where you can choose the formatting style for duplicate values.
- Select Formatting: Choose the formatting style you prefer for highlighting duplicate values. Excel will automatically apply this formatting to cells with duplicate values in the selected column.
- Review Results: Once applied, review the column to see which cells are highlighted. These highlighted cells contain duplicate values that you may need to investigate or remove.
25. How can you remove duplicate values in a range of cells?(Must asked Excel Interview Questions)
Answer –
- Select Your Data: Start by selecting the range of cells from which you want to remove duplicate values. You can do this by clicking and dragging your mouse over the desired cells.
- Access the Data Tab: Next, navigate to the “Data” tab on the Excel ribbon. This tab contains various tools and features for managing and analyzing data.
- Click on Remove Duplicates: In the “Data Tools” group, locate and click on the “Remove Duplicates” option. It’s represented by a small icon that looks like two intersecting circles with a red cross.
- Choose Columns: A dialog box will appear, prompting you to select the columns where you want Excel to check for duplicate values. By default, all columns in your selected range will be checked.
- Review Options: You can choose to remove duplicates based on specific columns or criteria by checking or unchecking the boxes in the dialog box.
- Confirm and Remove: Once you’ve made your selections, click the “OK” button. Excel will then analyze the selected data range and remove any duplicate values based on your specified criteria.
- Review Results: Excel will provide a summary of the number of duplicate values found and removed. You can review the cleaned-up data to ensure that duplicates have been successfully eliminated.
For additional insights and practice, you can check out this comprehensive guide on Excel interview questions provided by Indeed
Related : To strengthen your Excel skills, especially with popular functions like VLOOKUP, explore our detailed guide on using VLOOKUP in Excel like an expert here
Discover more from
Subscribe to get the latest posts sent to your email.