Common MS Excel Questions to Help you Excel in a Data Analyst Job Interview

Data Analysis is intended to transform raw data into meaningful insights that can be used to make a decision or come to a conclusion. The use of this method is widespread in the industry for a wide variety of purposes. Among the many job roles in this field, a data analyst is extremely popular worldwide.

Data analytics has become one of the most lucrative fields in business today, and the prospect of a career in the field keeps growing every day. Obtaining a position as a data analyst requires both experience and practice. Data analysts must be adept at explaining data succinctly, but describing the software and tools they use has never been as easy as it seems. Consequently, answering Excel interview questions can be quite challenging.

Since Excel is the most popular tool used in data analytics, candidates must be proficient in it.  Among many other things, Excel is used to create quick summaries of data and incorporate them into an interactive dashboard that can be used as a tool to evaluate and visualize data.

In order to simplify the interview process, we've compiled a list of Excel interview questions and answers for data analysts. With these questions in hand, you are sure to feel confident and ready to take on the job of your dreams.

Top Excel Interview Questions for Data Analysts

1. What are the available Excel data formats?

   Sol: In Excel, there are several different types of data formats available that you can choose from:

   • XML data: .xml

   • Excel workbook: .xlsx

   • Excel binary workbook: .xlsb

   • Excel macro-enabled workbook: .xlsm

   • Template (code): .xltm

   • Template: .xltx

     
     

2. Is it possible to add annotations to a cell?

   Sol: The Excel spreadsheet supports a variety of annotation techniques, such as color, cell comments, and callouts. A red triangle will appear in the corner of a cell when a comment has been added, indicating that it is directly linked to the cell. If you hover your mouse over the cell, you will be able to see the comment.

   
   

3. In Excel, what does relative cell reference mean?

   Sol: When dealing with formulas in Excel, relative cell references are used. For example, you can write the formula =SUM(B2:B5) that will sum up the values of a set of cells i.e., B2 to B5 together. Relative cell reference automatically adjusts and changes when using AutoFill or copied.

   In the event that you use relative cell references in your formulas, then every time you copy and paste that formula into another section of the spreadsheet, the cells in that section will change as compared to where the formula has been pasted. In the example above, if you were to copy the formula (B2:B5) over one column, the formula would become =SUM(C2:C5).

   
   

4. Explain VLOOKUP.

   Sol: The term VLOOKUP refers to the vertical lookup operation. By using this function, Excel will search for a specific value in a column in order to retrieve values from a different column in the same row. If you want to find items in a table or range by row, you can use VLOOKUP.

   Syntax:

   VLOOKUP([value], [range], [column number], [false or true])

   The VLOOKUP function is not case-sensitive. For VLOOKUP, 'scaler' and 'Scaler' are identical. However, VLOOKUP has one limit i.e., the lookup value in the table array must be in the most left-hand column when using VLOOKUP.  A VLOOKUP can only look left to right, not right to left.

   
   

5. Can you explain conditional formatting?

   Sol: As the name suggests, conditional formatting refers to the process of applying a variety of formatting styles to a range of cells depending on certain conditions. It is very easy to highlight certain values within a table or to make specific cells easily identifiable by using conditional formatting. If you want to highlight cells containing values that meet a certain condition, you can use conditional formatting.

   
   

6. In Excel, how can unique values be extracted?

   Sol: To extract unique values from Excel, we can use two methods.

   • To filter out duplicates temporarily: Select the desired data range and click Data > Sort & Filter > Advanced.

   • Delete duplicate values permanently: To eliminate duplicate values permanently, select Data > Data Tools > Remove Duplicates.

     
     

7. What are the questions you would ask before building a chart and dashboard?

   Sol: Before building a chart, a dashboard, or any other type of visualization, it is important to consider several things. A couple of things that should be considered before building a chart or dashboard are as follows:

   • The data type for which the chart is to be created.

   • The relationship you wish to portray.

   • The number of variables you want to include in the chart.

   • Whether you want dynamic or interactive charts and dashboards.

     
     

8. State the difference between COUNT, COUNTBLANK, COUNTA, and COUNTIF in Excel?

   Sol: In Excel, you can use a variety of functions in order to perform certain tasks. Here are a few of them:

   • COUNT: It counts the number of cells containing numeric data (numbers) in a range.

   • COUNTA: It counts the number of filled cells in a range (i.e., not blank cells).

   • COUNTBLANK: It counts the number of empty cells in a range.

   • COUNTIF: It counts the number of cells in a range that satisfy a particular condition.

     
     

9. What can you do to secure your Excel workbook?

   Sol: The data contained within Excel files can be secured by encrypting them with a password. You can do this by clicking on the "Protect Workbook" box and selecting "Encrypt with Password" from the menu that appears. Your workbook can then be protected by entering a password of any length or style that you like. As soon as a file has been password-protected, it can be further secured by using the "Locked" and "Hidden" features which disable and hide formulas and editing functionality.

   
   

10. Explain the Pivot table. What are the different sections of the Pivot table?

    Sol: A PivotTable is a powerful tool for calculating, summarizing, sorting, grouping, counting, totaling, and analyzing data to identify trends, patterns, and comparisons. The tool can be used to convert columns to rows and vice versa. You can group by any field (column) and perform advanced calculations. There is a slight difference in how PivotTables function based on what platform you are running Excel on. Pivot tables consist of four sections:

    • Values Area: The values are reported here.

    • Rows Area: The headings appear to the left of the values.

    • Column Area: Those headings at the top of the values area make up the columns area.

    • Filter Area: It is an optional filter that allows you to drill down into the data set.

      
      

11. In Excel, how do the Index and Match functions work?

    Sol:  The most popular Excel tool for performing more advanced lookups is the INDEX and MATCH functions.

    • MATCH(): This function returns a cell's position in a row or column based on its value as the input. It searches for a specified item within a range of cells and returns its relative position.

    • INDEX(): The function returns the value of a cell based on the row and column references. This function searches for a specified item within a range of cells and returns its value or reference.

      
      

12. What is the best way to find duplicates in a column in Excel?

    Sol: There are times when duplicate data is useful, and there are times when it makes your data harder to understand. If you want to find and highlight duplicate data, use conditional formatting. Duplicate data can be found by following the steps below:

    • Select the cells that you would like to check for duplicates.

    • Click Home, select Conditional Formatting, click Highlight Cells Rules, then click Duplicate Values.

    • If you want to apply different formatting to duplicate values, select it in the box next to values with, and then select OK.

      
      

13. What are the steps to applying a single format to all the sheets in a Workbook?

    Sol: Yes, a single format can be automatically copied to all selected sheets. Hold down Ctrl (or Command on a Mac) on your keyboard to select all the sheets in a workbook so that the single format can be applied to them all. The only thing that will be copied over is the formatting edits, not the data.

    
    

14. When the Cell Addresses are copied, what should you do if you do not want to change them?

    Sol: As a precautionary measure, if you want to make sure that cell addresses are not changed regardless of where they are copied, you should add a "$" before the row number as well as before the column letter (e.g. $B$9). The absolute reference cannot change under any circumstance, so it can be copied without changing. For instance, if you want to display the results of a calculation in several places, like showing the monthly profits separately, you can use this feature.

    
    

15. Can you explain macro languages in Excel?

    Sol: A macro in Excel refers to a program that is written in the VBA language (Visual Basic for Applications) and which contains specialized programming codes. A macro code automates an action that would otherwise have to be performed manually in Excel. With VBA, you can manually type out macro actions yourself, or you can record actions in Excel with the Macro Recorder and the software will convert them to VBA code.

    
    

16. How do you insert a Drop-Down?

    Sol: The following steps will guide you through creating a drop-down menu in Excel:

    • Choose the cells you would like to include in the lists, then click Data Validation under the Data tab.

    • A dialogue box will appear allowing you to select the type of input these cells may receive.

    • Select the List option and enter your drop-down menu options (separated by commas, without spaces) in the Source field.

      
      

17. Is there a way to auto-fit column width in Excel?

    Sol: You can auto-fit column widths in Excel by following these steps:

    • Select the columns you would like to edit first and then click on the home tab.
    • Then, within the Cells section of the Home tab, click on the Format button.
    • Lastly, in the Cell Size section, select AutoFit Column Width.

    It will automatically fit the width of the cells to the contents of the cell.

    
    

18. In Excel, what is the best way to clear formatting without removing the cell content?

    Sol: Following are the steps for removing all formatting from your cells:

    • First, select the cells from which you wish to remove the formatting, and then click the Home tab.
    • In the tab's Editing section, click the Clear button to access the drop menu.
    • Select Clear Formats from the menu.

    Using this method will remove all formatting from the cells. Alt+E+A+F is the keyboard shortcut.

    
    

19. Can you tell me what the Order of Operation is in Excel?

    Sol: A simple way to remember Excel's order of operations is to think of it as PEMDAS (parentheses, exponents, multiplication, division, addition, subtraction).

    • Parentheses | ()
    • Exponentiation | ^
    • Multiplication | *
    • Division | /
    • Addition | +
    • Subtraction | -

    In the first step, the parentheses are operated on, followed by the exponentiation. Following that, it can either be a division or multiplication operation. In order to get the final outcome, the result is first added and then subtracted.

    
    

20. Explain Freeze Panes in Excel.

Sol: If you want to keep certain rows or columns visible while scrolling in Excel, you can use freeze panes. This function allows you to "lock" a row or column so that it remains displayed whenever you navigate around a worksheet. You can, for instance, freeze the first row of your spreadsheet so that the column labels remain visible as you scroll down. The freeze panes feature can be found in most spreadsheet applications, such as LibreOffice Calc, Microsoft Excel, Apple's Numbers, and Google Sheets, among many others.

Conclusion

If you are going to appear for any interview, it is always a good idea to prepare as long in advance as possible so that you have time to fill in any gaps in your knowledge, CV, or portfolio while also giving yourself ample time to prepare for the interview. As with any data analyst role in general, Excel interview Questions are not used in an attempt to catch candidates off guard or to find out if they can "guess the correct answer" or not, but rather are used by interviewers in tandem with resumes and past experiences to assess a candidate's proficiency with Excel and their experience with it. It should be noted, however, that Excel interview questions are often quite technical and not necessarily hard. The better prepared you are and the more familiar you are with the software, the better your chances of succeeding in your interview.

In addition, Excel is not the only tool used to analyze data, so you will not be asked exclusively about it. Typical data analyst interview questions will also cover a wide range of topics, so prepare for those as well. In preparation for interviews for roles as a senior/lead data analyst, the above questions would prove useful. Take the time to understand the concepts covered in the questions, rather than just mumbling up answers. Regularly practicing Excel and going over these Excel interview questions will help you prepare for any question you might be asked.