Data Analysis transforms 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 industry for a wide variety of purposes. Data analytics has become one of the most lucrative fields in business today, and the prospects for a career in the field keep growing. Obtaining a position as a data analyst requires both experience and practice. Data analysts must be adept at explaining data succinctly. The data analyst interview is likely to look at your expertise carefully.

Since Excel is the most popular tool used in data analytics, data analyst interview candidates must be proficient in it and able to answer interview questions about 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.

We’ve compiled a list of Excel interview questions 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

1. What are the available Excel data formats?

Answer: 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?

Answer: Excel supports a variety of annotation techniques, including 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 call you will be able to see the comment.

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

Answer: When dealing with formulas in Excel, 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., those from B2 to B5, together. By default relative cell references are used. Relative cell references automatically adjust and change when copied, moved, or when using AutoFill by the amount they have been moved.

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

4. Explain VLOOKUP

Answer: The term VLOOKUP refers to the vertical lookup operation. Using this function, Excel will search for a specific value in a column of cells 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 left-most column when using VLOOKUP. A VLOOKUP can only look left to right, not right to left.

5. Can you explain conditional formatting?

Answer: As the name suggests, conditional formatting refers to applying a variety of formatting styles to a range of cells depending on certain conditions. Using conditional formatting, it is very easy to highlight certain values within a table or to make specific cells easily identifiable. You can use conditional formatting if you want to highlight cells containing values that meet a certain condition. For example, negative numbers can be highlighted in red.

6. How can unique values be extracted?

We can use two methods to extract unique values for methods.

1. • 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 questions would you ask before building a chart and dashboard?

Answer: 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:

1. • The data type for which the chart is to be created.
   • The data relationships 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?

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

1. • COUNT: Counts the number of cells containing numeric data (numbers) in a range.
   • COUNTA: Counts the number of filled cells in a range (i.e., not blank cells).
   • COUNTBLANK: Counts the number of empty cells in a range.
   • COUNTIF: Counts the number of cells in a range that satisfy a particular condition.

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

Answer: 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 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?

Answer: A Pivot Table 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:

1. • 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. How do the Index and Match functions work?

Answer:  The INDEX and MATCH functions are the most popular Excel tools for performing more advanced lookups.

1. • 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?

Answer: 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:

1. • 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?

Answer: 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. The only thing that will be copied is the formatting edit, not the data.

14. When cell addresses are copied, what should you do if you do not want to change them?

Answer: 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 and before the column letter (e.g., $B$9). This converts the default relative reference to an absolute reference. 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?

Answer: A macro in Excel is a program written in the VBA language (Visual Basic for Applications) that 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 or 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 menu?

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

• • Choose the cells you want to include, 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?

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

• Select the columns you want to edit first, 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 cell’s contents.

18. What is the best way to clear formatting without removing the cell content?

Answer: 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 operations is in Excel?

Answer: 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. To get the final outcome, the result is first added and then subtracted.

20. Explain freeze panes in Excel.

Answer: If you want to keep specific 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

Suppose you are going to appear for an interview. In that case, 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 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 you are and the more familiar you are with the software, the better your chances of succeeding in your interview.

This article was written by Alisha Singh for HackerNoon and was edited and published with permission.