Excel Interview Questions

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.

1) What is Microsoft Excel?

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.

2) How can we add cells, rows, or columns in Excel?

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.

3) What are the format cell options?

Ans: The following are various options that MS Office provides to format the cells.

  • Number - format number to currency, accounting, percentage, etc.
  • Alignment - align the direction of the data.
  • Font - change the font, style, size, color, etc. of the data in the cell.
  • Border - apply borders, remove borders, add color borders, etc.
  • Fill - apply colors to fill the background of the cell.
  • Protection - lock or hide the cell data.

4) How can we add comments?

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. 

5) What does the red triangle indicate at the top right-hand corner of the cell?

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.

6) What is the use of freeze pane in MS Excel?

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.  

7) What are the types of workbook protection in Excel?

Ans: There are three ways to protect a workbook.

  • Passwords protect the entire workbook, so no one without access can open it.
  • Restrict users from adding, deleting, hiding, and unhiding sheets.
  • Protect the workbook from changing the size or position of windows.

8) Explain the difference between 'count', 'counta', 'countif', and 'countblank'.

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.

9) What is Ribbon and can we hide it?

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.

10) Can we resize a column?

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.

Excel Training

  • Master Your Craft
  • Lifetime LMS & Faculty Access
  • 24/7 online expert support
  • Real-world & Project Based Learning

 

11) What are the types of report formats in Excel?

Ans: Below are the three types of report formats that MS Excel supports.

  • Compact
  • Outline
  • Tabular

12) What are pivot tables?

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. 

13) Can we make a pivot table with different sources of data?

Ans: Yes, we can make a pivot table from different sources that belong to different worksheets from the same workbook. 

14) What is the process for disabling automatic sorting in pivot tables?

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. 

15) Tell the order of operating mathematical operations in Excel?

Ans: The order of operating mathematical operations in Excel is the same as the general mathematical operations.

  • Parentheses or Brackets
  • Exponent
  • Multiplication
  • Division
  • Addition
  • Subtraction

16) xplain about macros in Excel.

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.

17) Can we reduce the file size in Excel?

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.

18) Give the syntax for Vlookup.

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. 

19) Name some types of data formats in Excel?

Ans: MS Excel supports a total of 11 data formats. The following are some of the data formats.

  • Number Format
  • Currency Format
  • Date Format
  • Percentage Format
  • Text Format

20) How can we wrap the text within a cell?

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.

Subscribe to our youtube channel to get new updates..!

 

21) Explain charts in MS Excel and list the type of charts available.

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.

  • Column
  • Bar
  • Pie
  • Line
  • Area
  • X Y Scatter
  • Stock
  • Surface
  • Doughnut
  • Bubble
  • Radar

22) Explain about some functions in Excel.

Ans: Excel provides a lot of built-in functions. Some of the available functions are,

  • Count
  • Sum
  • Find
  • Date
  • Concatenate
  • Sqrt
  • Trim

23) What is the difference between substitute and replace function in Excel?

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.

24) How does the 'if' function work in Excel?

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. 

25) Can we create shortcuts for Excel functions?

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.

26) Can we apply the same formatting to every sheet in a workbook in MS Excel?

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.

27) Explain the differences between left, right, fill, and distributed alignments.

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.

28) What is the process of adding drop-downs for a column?

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'. 

29) What is the use of cell reference in the calculation?

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.  

30) Can we protect cells of a sheet from being copied?

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.

Excel Training

Weekday / Weekend Batches

 

Submit an interview question

Categories

Request for more information

Mudassir
Mudassir
DevOps ERP and IAM tools
Mudaasir is a programming developer for hkr trainings. He has a well knowledge of today’s technology and I’ve loved technology my entire life. And also been lucky enough to work for the programmer including science and technology. Big thanks to everyone who has followed me on LinkedIn and twitter.

WhatsApp
To Top