In this tutorial today we will talk about top 30 Excel beginner level interview questions that are most frequently asked and we will also learn the solution or answer to these questions.
This Content Covers:
- 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?
- 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 25, add the result by 10, and divide it by 2.
- What is the difference between COUNT, COUNTA, and COUNTBLANK?
- What is the shortcut to add a filter to a table?
- How do you create a hyperlink in Excel?
- 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?
- How is VLOOKUP different from the LOOKUP function?
- How many report formats are available in Excel?
- How does the IF() function in Excel work?
- How do we use the SUMIF() function in Excel?
- How does the COUNTIFS Function work in Excel?
- What is a Pivot Table?
- Create a drop-down list in Excel.
- How do we apply advanced filters in Excel?
- What according to you are the top 5 functions in Excel?
- What is the use of Conditional Formatting in Excel?
- How do you find duplicate values in a column?
- How can you remove duplicate values in a range of cells?
- How many data formats are available in Excel? Name some of them.
- How can you wrap the text within a cell?
- Explain Macro in MS-Excel
- Which are the two macro languages in MS-Excel?
- What is Format Painter used for?
- What is a cell address in Excel?
A cell address is something that we use to locate a specific cell in a worksheet. It is identified by the appropriate column letter and row number in the combination. In the picture below the highlighted cell’s address is B3, which indicates that it is in row 3 and column ‘B’.
- What do you mean by Relative Cell Referencing and Absolute Cell Referencing in MS Excel?
Relative Cell Referencing: Every cell reference is a relative reference by default. Based on the relative positioning of rows and columns, they change when copied across several cells. A dollar symbol in the formula is not necessary for relative cell referencing.
Absolute Cell Referencing: Absolute references are unchanged by copying or filling. To keep the consistency of a row and/or column absolute reference is used. An absolute reference is indicated in a formula by adding a dollar symbol ($) before the column and row.
- How do you freeze panes in Excel?
Select the rows or columns you want to freeze and go to View>>Freeze Panes and select any of the options from the drop-down list according to your preferences.
- How can you restrict someone from copying a cell from your worksheet?
Step 1: Select the data you want to protect and press CTRL+SHIFT+F key. This will activate Format Cells dialogue box, from here go to the Protection tab and check Locked box, click OK.
Step 2: Select Protect Sheet under the Review tab. To secure the sheet, set a password.
- How is a Formula different from a Function in Excel?
A formula is basically like an equation in Excel which the user chooses and has to type it himself and it can be of any type. Manually typing the formula every time is time consuming.
But a function in Excel is a predetermined calculation which is an in-built feature in Excel. It is easy, quick and more preferable.
- Mention the order of operations used in Excel while evaluating formulas.
PEDMAS is the term used to describe the Excel operation order. The precedence for an Excel operation is displayed below:
- Parentheses
- Exponentiation
- Division/Multiplication
- Addition
- Subtraction
- How will you write the formula for the following? – Multiply the value in cell A1 by 25, add the result by 10, and divide it by 2.
We must stick to the PEDMAS Precedence when generating a formula for the question mentioned above. The formula will be written like this: ((A1*25)+10)/2.
- What is the difference between COUNT, COUNTA, and COUNTBLANK?
COUNT: The number of cells with only numerical values are counted. Blank cells, special characters, and cells with string values will not be included in the calculation.
COUNTA: The number of cells with any kind of content is counted. Numeric numbers, special characters, and string values included in cells will all be counted. Only a blank cell won’t be counted.
COUNTBLANK: It only counts the number of blank cells. Content-containing cells won’t be taken into account.
- What is the shortcut to add a filter to a table?
To add a filter to a table just select any cell of that table and press CTRL+SHIFT+L command keys.
So, the shortcut is: CTRL+SHIFT+L
- How do you create a hyperlink in Excel?
Press CTRL+K to activate Insert Hyperlink dialogue box. Enter the address in Text to display and click OK.
- How can we merge multiple cells text strings in a cell?
CONCATENATE() function can be used to combine text strings from multiple cells into one cell.
“&” operator can also be used to merge multiples cells text strings into a cell.
- How can you split a column into 2 or more columns?
Step 1: Select the column you want to split then go to Data>>Text to Columns.
Step 2: Select Delimited and click on Next.
Step 3: Select the delimiter or delimiters that are separating the data of the column and click Next.
Step 4: Choose the column data format and the location where you wish the split to be shown. Click on Finish.
Step 5: The text has been divided into several columns in the final output.
- What is the use of VLOOKUP and how do we use it?
The use of Excel’s VLOOKUP function is to find information in a table and extract the relevant data.
An example of the VLOOKUP function is provided below. Here we will use to VLOOKUP function to extract the sell value of product named Apple for Store 2.
VLOOKUP formula: =VLOOKUP(value, table, col_index, [range_lookup])
Here the value indicates the data we are searching in the first column of a table. The value is typed in F1. Now, A2:C5 here is the table range where the VLOOKUP function will search for the data. Col_Index here is 3 as we are searching for sell value of Apple from Store 2 which is located in column 3. Range_lookup – FALSE = exact match [optional] TRUE = approximate match (default).
- How is VLOOKUP different from the LOOKUP function?
VLOOKUP can only be used to search for a value in a table’s left-most column. The value is then returned from left to right. On the other hand, the LOOKUP function is used to search for data in a row or column. It brings up the value in a different row or column. Comparatively speaking to the LOOKUP function, the VLOOKUP is not as simple to utilize.
- How many report formats are available in Excel?
There are three report formats available in Excel.
- Compact Format
- Outline Format
- Tabular Format
- How does the IF() function in Excel work?
A logical test is run using the IF() function. If the test evaluates to true, a value is returned; if the test evaluates to false, a different value is returned according to the attached text string inside the formula which is Profit and Loss in the picture below for the condition or logical test set as, if Total Sell is greater than Total Cost. If true the function returns Profit and if false the function returns Loss.
- How do we use the SUMIF() function in Excel?
Using a specified condition or set of conditions, the SUMIF() function sums the cell values indicated. According to the specified criteria, the expenses related to the product Mango are added in the below picture.
- How does the COUNTIFS Function work in Excel?
Excel’s “COUNTIFS” statistical function is used to count cells that match multiple criteria. The criteria can take the form of a date, text, number, formula, expression, or cell reference. This function applies the selected criteria to cells in various ranges and returns the total number of times the mentioned criteria is matched.
In the picture below, the formula returns the total number of times the mentioned criteria “Mango” is available in the selected range.
- What is a Pivot Table?
A pivot table in Excel is a tool for summarizing data that is derived from larger tables. A pivot table’s data summary can also include sums, averages, or other information that the pivot table meaningfully groups together. A database, an Excel spreadsheet, or any other data that is or could be turned into a table-like shape could be these larger tables.
- Create a drop-down list in Excel.
Drop-Down list can be created using the Data Validation option from Data tab.
Step 1: Go to Data>>Data Validation.
Step 2: Select List from Allow box and type the source inside Source box. Click OK.
Step 3: A drop-down list is created.
- How do we apply advanced filters in Excel?
Step 1: Go to Data>>Advanced.
Step 2: Choose where you want to filter the table. Select the “list range” and the “criteria range” that include the criteria on which you want to filter the table.
- What according to you are the top 5 functions in Excel?
Here is a list of top 5 functions in Excel according to me:
- INDEX / MATCH
- COUNTIF
- SUMIF
- IFERROR
- VLOOKUP
- What is the use of Conditional Formatting in Excel?
By using conditional formatting, we can highlight certain values or make specific cells noticeable. This modifies a cell range’s look according to a condition or criteria. To highlight cells that contain values that match a certain condition, we can utilize conditional formatting. For example, with the help of conditional formatting, we can apply a red color to all the cells whose value is less than 35.
- How do you find duplicate values in a column?
You can find duplicate values using Conditional Formatting. Just select the range of cells that you want to check for duplicate value, then under Home tab go to Conditional Formatting>>Highlight Cells Rules>>Duplicate Values. Duplicate values will be marked.
- How can you remove duplicate values in a range of cells?
To delete duplicate values in a range of cells, just select the duplicate values and click on the Delete button in your keyboard.
Or select the range and go to Data>>Remove Duplicates to remove duplicate values in a range of cells.
- How many data formats are available in Excel? Name some of them.
There are eleven types of data formats available in Excel. Some of them are:
- Number Format
- Date Format
- Text Format
- Currency Format
- Percentage Format
- How can you wrap the text within a cell?
To wrap the text within a cell, click on the text and then go to Home>>Wrap Text. Or simply use this shortcut: (ALT+H+W) after selecting the text.
Another way of wrapping the text withing a cell is using the line break method. Simply Double-click the cell in which you want to enter a line break. In the cell, click the location where you want to break the line, and press Alt + Enter.
- Explain Macro in MS-Excel
A macro is a series of actions that can be repeated as many as necessary to complete a certain job. The whole mouse clicks and all keystrokes are recorded when a macro is produced. In other words, an action or group of actions that can be recorded, named, stored, and executed again at any time is known as an Excel macro. We can automate repetitive data processing and data reporting operations that need to be completed repeatedly by utilizing macros.
- Which are the two macro languages in MS-Excel?
XLM and VBA are the two macro languages in MS-Excel. Spreadsheet files called Excel Macro (XLM) are used to store macros. And the programming language VBA (Visual Basic for Apps) is only used to create Visual Basic applications.
- What is Format Painter used for?
Format Painter is used for applying the format from one cell to another cell or one cell to a range of cells by copying it.
For ready-to-use Dashboard Templates:
