|
No matter which domain you are in, Microsoft Excel is one thing that most of the companies expect you to know. Companies small and large make use of Microsoft Excel for various reasons. Many companies might include MS Excel questions in their interview. So going prepared for it is always a good move.
In this post, we have gathered 30 Excel interview questions that cover the most basic and advanced concepts in Excel. Go through the frequently asked MS Excel interview questions and answers and train your brain to take on any question.
Ans: Microsoft Excel is a spreadsheet where we can store data in the form of tables. Excel is a product from Microsoft released in 1987, and it supports Windows, macOS, iOS, and Android operating systems. We can organize, manipulate, and analyze the data stored in worksheets.
Ans: To add a new cell, right-click on the cell where you want to add. Select the insert option from the cell menu. We can add one or multiple cells, rows, or columns at a time. The cells that come after or below the newly added cell will shift to the right-side or down.
Ans: The following are various options that MS Office provides to format the cells.
Ans: We can use comments to give notes for a particular cell. Everyone who has access to the sheet can view the comment on the cell. Right-click on the cell for which you want to add a comment. Select the 'Insert comment' option from the cell menu, and add the text in the comment area.
Ans: When we add a comment to a cell, a red triangle appears at the top right-hand corner of the cell. If we hover over the triangle, we can see the comment. To delete a comment from the cell, right-click on the cell, and select 'Delete comment' from the cell menu.
Ans: We can lock single or multiple rows and columns with the help of a freeze pane. If we freeze a row, it will stay visible on the screen even when we scroll down. If we freeze a column, it will stay visible on the screen even when we scroll to the right.
Ans: There are three ways to protect a workbook.
Ans:
Count - count the numbers, dates, etc. present in the cells, excluding blank cells.
COUNTA or Count All - count the numbers, text, logical values, etc. present in the cells, excluding blank cells.
COUNTIF and COUNTIFS - count the cells that match certain criteria.
COUNTBLANK - count all the blank cells.
Ans: Ribbon refers to the topmost menu in the workbook. We can show or hide the ribbon with CTRL+F1 shortcut. It contains several tabs like home, insert, page layout, etc. Each tab further contains groups of related commands.
Ans: Yes, we can resize a column by changing the width of it. Click on the column header which you want to resize. Drag the right-side boundary of the column till the width you want. If you want to resize the column that exactly fits the length of the stored data, double-click on the right-side border of the column header. It will automatically resize the column according to the data.
Ans: Below are the three types of report formats that MS Excel supports.
Ans: We can get a quick summary of large data with pivot tables. We can select the entire data or a subset of data and generate a pivot table for it. It performs sort, count, total or average of the selected data and displays the result in the worksheet.
Ans: Yes, we can make a pivot table from different sources that belong to different worksheets from the same workbook.
Ans: Excel sorts the data present in pivot tables automatically. Open the menu from the row labels or the column labels and select 'More sort options'. Select the 'Sort menu' and select 'More options'. Unselect the 'Sort automatically' option.
Ans: The order of operating mathematical operations in Excel is the same as the general mathematical operations.
Ans: We can execute a set of tasks automatically with the help of macros. We can perform a macro 'n' number of times. We can use macros for customized, repetitive functions, and instructions.
Ans: An Excel worksheet comes with a lot of rows and columns. To reduce the size of the file, we can delete the empty rows and columns. Find the last cells of rows and columns in your worksheet, and delete the rest.
Ans: Vlookup refers to a vertical lookup, which is a function that looks up and fetches data from a given range. Vlookup requires data to be vertically aligned. It is very useful to search for some data in a larger worksheet.
Ans: MS Excel supports a total of 11 data formats. The following are some of the data formats.
Ans: To avoid data overflow out of the cell, we can wrap the text. Select the cell you want to wrap and select the wrap text option from the top home menu. The text will be wrapped, and the height of the cell increases.
Ans: Charts are the graphical representation of the data in Excel. We can select the set of data that you want to prepare a chart for in the worksheet. MS Excel supports the following charts.
Ans: Excel provides a lot of built-in functions. Some of the available functions are,
Ans: The substitute function substitutes one or more occurrences of old text with the new one. The replace function swaps a part of the text with the new one.
Ans: The 'if' function in Excel is used to perform the logic test for data. 'If' function checks whether a certain condition is true or false.
Ans: Yes, we can create shortcuts for Excel functions. The 'Quick Access Toolbar' contains the most frequently used commands. We can customize it and add the functions as shortcuts in the quick access toolbar.
Ans: Yes. Right-click on the worksheet tab and choose the 'Select All Sheets' option. Any format operations performed on the worksheet will be applied to all the sheets in the workbook.
Ans:
Left - aligns the text to the leftmost side of the cell.
Right - aligns the text to the rightmost side of the cell.
Fill - fills the same text repetitively in the cell.
Distributed - distributes the text across the width of the cell.
Ans: Select the header of the column to which you want to add drop-downs. Right-click on it and select the 'Data validation' option. Select the 'List of items' option for the 'Criteria' field. Add the options that you want in the drop-down in the text box next to it separated by a comma, and click on 'Save'.
Ans: A cell reference is used while writing formulas. When we apply a formula with a specific function, we direct it to the location of the cells, which is called cell reference. So when the new data in the cell reference is added, it will automatically get calculated according to the formula.
Ans: Go to the menu bar and select the 'Review' option. Then select 'Protect Sheet' and choose the 'Password' option. Enter a password and click on 'Ok'. Now, others can't copy the data from your sheet.
Batch starts on 5th Apr 2023, Weekday batch
Batch starts on 9th Apr 2023, Weekend batch
Batch starts on 13th Apr 2023, Weekday batch
©2023 HKR Trainings. All rights Reserved.