Microsoft Excel is useful software when you are working with data. It helps you to calculate two numbers and do other calculations. you can use Microsoft Excel for advanced and complicated calculations. you can also use Excel for making reports and data management. Nowadays, it's important to know how to use Microsoft Excel because many organizations use it for data storage and analysing their data. In this article, we'll go over the top 50 questions you might be asked in an Excel interview, starting with some basic-level questions that are easy to understand.
Here are a few easy Excel interview questions for beginners:
What is a cell address in Excel?
A cell address in Excel is an alphanumeric value to used identify a specific cell in an Excel sheet. It is a combination of the column letter and row number. For example, if a cell is in column D and row 5, its cell address is D5.
.png "cell address in Excel")
What is the difference between relative and absolute cell referencing in MS Excel?
Relative references change when a formula is copied to another cell. Absolute cell references is a cell reference in which the column and row remain constant no matter where they are copied. Using dollar signs in a cell reference makes it absolute.
How do you freeze panes in Excel?
Freezing panes keeps specific rows and columns visible while scrolling through the rest of a worksheet. It is a useful tool for checking out data in another part of data in the worksheet, without losing worksheet headers or labels. Let's See How we can Freeze Panes in Excel?
- First, you have to Click on the "View tab".
- Then Click on The "Freeze Panes"
If you want to freeze the first two columns of a dataset, select the third column and click 'Freeze Panes'.
How can you prevent someone from copying a cell from your worksheet?
To protect your data, first, select the data that you want to protect. Then, Press Ctrl + Shift + F to open the Format Cells tab. Then click on the Protection tab and check Locked, and then click OK. Next, Click on the Review tab, then click on Protect Sheet, and enter a password to protect the sheet.
What is the difference between a formula and a function in Excel?
The formula calculates values in a specific order. A function is a predefined formula that performs calculations by using specific values, called arguments, in a particular order. Functions can help you do things like add up a range of cells or find the highest or lowest value in a range.
What is the order of operations used in Excel?
Excel follows a certain order of operations to solve formulas. It's called PEDMAS, which stands for Parentheses, Exponents, Division/Multiplication, Addition, and Subtraction. This means that Excel first solves any calculations inside parentheses, then exponents, followed by division or multiplication (whichever comes first), addition, and finally, subtraction.
How do you write a formula to multiply the value in cell A1 by 10, add 5 to the result, and divide it by 2?
To write this formula in Excel, we need to use the PEDMAS order of operations. The correct formula would be ((A1*10)+5)/2.
What is the difference between count, count, and count blank?
These are all functions in Excel that help you count cells with certain types of data. Count only counts cells with numeric values, Counta counts all cells with any type of data (except blanks), and Countblank counts only blank cells.
How do you add a filter to a table quickly?
To add a filter to a table in Excel, you can use the shortcut Ctrl+Shift+L. This allows you to display only specific data from your entire dataset without changing any of the original data.
How do you create a hyperlink in Excel?
To create a hyperlink in Excel, you can use the shortcut Ctrl+K. This will bring up the "Insert Hyperlink" box, where you can enter the address and text you want to display. This allows you to navigate between worksheets, files, or websites with just a click of a button.
How to combine text from multiple cells into one cell?
You can combine text from multiple cells into one cell using the CONCATENATE function or the "&" operator. These allow you to join different cells' contents together into a single cell.
How to split a column into multiple columns?
You can split a column into multiple columns by using the Text to Columns feature. This allows you to separate the content of a single column into two or more columns based on a chosen delimiter, such as a comma or space.
What is VLOOKUP and how do you use it?
VLOOKUP is a function in Excel that lets you find and retrieve data from a table based on a specified search value. To use it, you need to provide the search value, the table range, the column index number, and optionally, a range lookup value.
How is VLOOKUP different from the LOOKUP function?
VLOOKUP is used to search for a value in the leftmost column of a table and return a value in the same row. LOOKUP is used to search for a value in a row or column and return a value in another row or column.
How many report formats are available in Excel?
There are three report formats available in Excel: Compact Form, Outline Form, and Tabular Form. These formats help to organize and present data in different ways, depending on the user's needs.
What does the IF() function in Excel do?
The IF() function in Excel tests whether a condition is true or false. If the condition is true, it returns one value, and if the condition is false, it returns a different value. For example, you can use IF() to check if a person's age is greater than 20 and their salary is greater than $40,000, and return a message like "Record is Valid" if both conditions are true.
How do you use the SUMIF() function in Excel?
The SUMIF() function in Excel adds up values based on a specific condition or criteria. For example, you can use SUMIF() to add up all the costs for the year 2010.
How can you use the COVID data to find the number of days where the number of deaths in Italy is greater than 200?
To find this information, you can use the COUNTIFS() function. First, you'll need to use a dataset that contains information about COVID cases, like the one shown in the example. Then, you can use the COUNTIFS() function to count the number of days when the country is Italy and the number of deaths is greater than 200.
What is a Pivot Table in Excel?
A Pivot Table is a summary table that can help you analyze large amounts of data. It lets you create reports and analyze trends in your data. To create a Pivot Table in Excel, you need to select the data you want to summarize and use the PivotTable option in the Insert tab. Then, you can drag and drop fields to create the summary table.
How do you create a drop-down list in Excel?
To create a drop-down list in Excel, you can use the Data Validation feature. This lets you restrict the values that can be entered in a cell. To create a drop-down list, first, select the cells you want to apply the drop-down list too, then go to the Data tab, and select Data Validation. In the settings, you can choose to create a list based on a range of cells or enter the values manually.
How do we apply advanced filters in Excel?
To filter data in Excel, you can use advanced filters. To do this, select the table you want to filter, go to the Data tab, choose "Advanced Filter," and then specify the conditions based on which you want to filter the data.
How do you use conditional formatting to highlight cells in Excel?
To highlight cells in Excel, you can use conditional formatting. This means you set up rules that determine which cells get highlighted with a certain colour. To do this, select the cells you want to format, go to the Home tab, choose "Conditional Formatting," and then pick a rule that fits your needs. You can choose the colour and the conditions that trigger the formatting.
How does the index-match function work in Excel?
The index-match function is a formula in Excel that helps you find data in a table based on certain criteria. It works by searching a range of cells for a specific value, and then returning data from a corresponding row or column. This formula is very useful for looking up data in large spreadsheets.
How do you find duplicate values in a column in Excel?
If you want to find duplicate values in a column in Excel, you can either use conditional formatting or the COUNTIF() function. With conditional formatting, you set up rules that highlight any cells in the column that are duplicated. With the COUNTIF() function, you count the number of times a value appears in a range and then filter the results to show only the duplicate values.
How do you remove duplicate values in a range of cells in Excel?
To remove duplicate values from a range of cells in Excel, you can use the "Remove Duplicates" feature. This feature is located in the Data tab and allows you to select the columns that you want to remove duplicates from. Alternatively, you can manually delete duplicate cells and then remove any formatting rules associated with them.
What are the wildcards available in Excel?
Wildcards are special characters used in text data. Excel has three wildcards - asterisk (*), question mark (?), and tilde (~). The asterisk means any number of characters, the question mark represents a single character, and the tilde is used to identify wildcard characters.
What is Data Validation? Illustrate with an example.
Data Validation is a feature that restricts the type of values a user can enter into a cell or a range of cells. You can apply it from the Data tab by selecting the Data Validation option. For instance, if you want to allow only text values in the "Name" column, you can apply data validation, and if someone enters a non-text value, it will show an error.
Write a function to add pass/fail to the results column based on the following criteria.
In the given student table, you can use the IF() function and an AND condition to fill the results column based on the criteria - if student marks are greater than 60 and attendance is over 75%, the student passes, or else they fail.
How do you calculate your age in years from the current date?
To calculate your age in years from the current date, you can use the YEARFRAC() or DATEDIF() function, which returns the number of days between start_date and end_date. You can subtract the birthdate from the current date to get the age.
How are nested IF statements used in Excel?
The IF() function is nested when there are multiple conditions to meet. You can replace the FALSE value in the first IF function with another IF function to make further tests. For instance, you can categorize results based on marks using nested IF statements.
How to find descriptive statistics of columns using Excel's Data Analysis ToolPak?
To find descriptive statistics of columns in Excel, first, you need to add the Data Analysis ToolPak from Options > Add-ins > Analysis ToolPak. Then click on the Data Analysis option in the Data tab and choose Descriptive Statistics. This will generate a summary table of the columns and their respective statistical measures.
How to create a pivot table to find the total cases in each country belonging to its respective continent?
To create a pivot table for this, drag the continent and country columns into rows and the cases column into the values section. This will show the total cases for each country and its respective continent.
How to Provide Dynamic Range in Data Source of Pivot Tables?
To provide Dynamic Range in the data source of pivot tables, create a Named table by going to the Insert tab and selecting Table. Then, under Table Design, give a name to the table, and this will create a dynamic range. This will adjust the pivot table automatically to new data when refreshed.
Is it possible to create a Pivot Table using multiple sources of data?
Yes, you can create a pivot table using multiple worksheets in Excel. To do this, there must be a common row in both tables. This will act as the Primary key for the first table and the Foreign key for the second table. Create a relationship between the tables and then build the pivot table.
How to create a pivot table to find the top three countries from each continent based on the total cases using COVID data?
To create a pivot table to find the top three countries from each continent based on the total cases, first, create a pivot table using the coronavirus dataset. Drag the cases into values, place the continent and country columns into rows, and then filter the table by selecting ‘Top 3’. This will show the top three countries from each continent based on the total cases.
How to add a new column in a pivot table?
Click on the PivotTable Analyze tab and select the ‘Fields, Items & Sets’ option. Then, click on ‘Calculate Field’ to create a new column. Give it a name and insert the formula by selecting the existing columns from the pivot table. Finally, click ‘Add’ an ‘OK’ to create the column.
How does a Slicer work in Excel?
Slicers are used to filter data in a Pivot table. To create a slicer, go to the Insert tab and select Slicer under Filter. Then, choose the list of fields for which you want to create slicers. In the example below, two slicers (months, countries, and territory) are created to filter the pivot table.
How to find the percentage contribution of each country and continent to the total cases using the coronavirus dataset?
Create a pivot table to show the total cases by country and continent. Right-click on the sum of cases column and select “% of Grand Total” under Show Value As.
How to create a pivot chart in Excel?
First, create a pivot table. Then, go to the Insert tab and select the ‘Pivot Chart’ option. Choose a suitable chart to represent your pivot table data.
What are macros in Excel and how to create them to automate a task?
Macros are programs that automate repetitive tasks in Excel. To record a macro, go to the Developer tab and click on Record Macro or access it from the View tab. Then, perform the task you want to automate and stop the macro recording. Finally, assign a shortcut key to the macro and click on ‘OK’.
What is the What-If Analysis in Excel?
The What-If Analysis is a tool in Excel that allows you to make complex mathematical calculations, experiment with data, and try out different scenarios. For example, you can use it to figure out how much profit you can expect if you make $10,000 worth of sales over the next few months.
What is the difference between a function and a subroutine in VBA?
In VBA, a function is a task that returns a value, while a subroutine is a task that doesn't return a value. Functions are used as formulas in spreadsheets, while subroutines are not. Functions are useful for carrying out repetitive tasks and returning a value, while subroutines can be called from anywhere in a program and can be recalled multiple times.
What is the difference between ThisWorkbook and ActiveWorkbook in VBA?
In VBA, ThisWorkbook refers to the workbook where the code is running from, while ActiveWorkbook refers to the workbook that is currently active out of all the open workbooks.
How will you pass arguments to VBA Function?
You can pass arguments to a VBA function either as a reference or as a value. When you pass arguments by reference, you are referencing the original value, and changes made in the function will affect the original value. When you pass arguments by value, you are passing a copy of the value, and the original value will not be affected.
How do you find the last row and column in VBA?
To find the last row in VBA, you can use the following code:
- Dim lastRow As Long
- lastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
- To find the last column, you can use this code:
- Dim last column As Long
- lastColumn = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
- How do we check whether a file exists or not in a specified location?
- Sub CheckFileExists()
- Dim strFileName As String
- Dim strFileExists As a String
makefilestrFileName = “File location\file_name.xlsx” strFileExists = Dir(strFileName)
If strFileExists = “” Then
vbnetMsgBox “The selected file doesn't exist”
Else
MsgBox “The selected file exists”
End IfHow can we check if a file exists in a certain place?
To do this in VBA, we can use the Dir function which takes the file location and name as input and returns a string with the file name if it exists. Here's an example code:
Sub CheckFileExists()
Dim strFileName As String
Dim strFileExists As a String
makefilestrFileName = “File location\file_name.xlsx” strFileExists = Dir(strFileName)
If strFileExists = “” Then
vbnetMsgBox “The selected file doesn't exist”
Else
MsgBox “The selected file exists”
End IfEnd Sub
How can we fix errors in the VBA code?
To debug a VBA code step-by-step, we can use the F8 key. We can also set a breakpoint to stop the code execution at a specific line. When we run the code in debug mode, it will start from the beginning and execute one line at a time until it's finished. The yellow arrow and highlighted line show us the current point of execution.
How can we write a VBA function to calculate the area of a rectangle?
To do this, we can create a function that takes the length and width of the rectangle as input parameters. We can then check if the width is missing using the IsMissing function. If the width is missing, we assume it's square and calculate the area using the length only. Otherwise, we calculate the area using the length and width. Here's an example code:
Function Area(Length As Double, Optional Width As Variant)
mathematicaIf IsMissing(Width) Then
Area = Length * Length
Else
Area = Length * Width
End IfEnd Function
How can we check if a number is a prime number in VBA?
To do this, we can check if a number is a prime number in VBA, we can write a function that takes in the number as input and checks if it has exactly two divisors. To check the divisors, we can use a loop to iterate through all the numbers from 1 to the given number and see if they divide the number without leaving any remainder. If a number has only two divisors (1 and itself), it is a prime number. If it has more than two divisors, it is not a prime number.
Here's an example of a VBA function that checks if a number is prime or not:
vbnetFunction IsPrime(ByVal n As Long) As Boolean
Dim i As Long
Dim count As Long
count = 0
For i = 1 To n
If n Mod i = 0 Then
count = count + 1
End If
Next i
If count = 2 Then
IsPrime = True
Else
IsPrime = False
End If
End FunctionTo use this function, you can pass a number to it as an argument, and it will return True if the number is prime, and False if it is not. For example:
mathematicaIf IsPrime(7) Then
MsgBox "7 is a prime number."
Else
MsgBox "7 is not a prime number."
End IfThis will display a message box saying "7 is a prime number."
