2024 Guide: 60+ Must-Know Excel Questions for Data Analysts

The title of ‘data analyst’ can be considered a very broad occupation that includes roles that are involved with data, business process, project analysis, and management. One of the essential skills for a data analyst is the ability to be technically literate. That means that as a data analyst, you should be aware of the various functions of different applications and useful software, even ones in which you do not operate regularly. One of these programs (and perhaps the most useful and popular of them all) is Microsoft Excel, which you will almost assuredly spend time mastering over your career.

Excel is one of the most common types of data analyst interview questions, and Excel interviews typically range from basic knowledge about the software to advanced functions about how to visualize and format the data, which means preparation can be slightly confusing and overwhelming.

To prepare for an Excel interview, you should review and practice:

• Easy Excel questions
• Intermediate Excel questions
• Hard Excel questions

In order to establish why Excel is so useful, it is helpful to think of the teams you may work or be embedded with. The finance department of a company is an excellent example. Establishing plans and testing models for this team puts you in a position of deciding on crucial financial decisions for the company itself. That is why a successful data analyst should focus on learning and improving their financial reporting, forecasting and strategizing, which is where knowing important Excel functions and tools comes in handy. You may very well work with other functions of a business like sales or customer success (retention), and the following tools will also be a huge asset for their planning as well:

Pivot Tables

Pivot tables are perhaps one of the tools that will save you the most time in Excel. It is capable of summarizing and analyzing data in a matter of seconds. And regardless of the size of the data itself, it will provide you with a summary of it, with the ability to quickly update when new data is inputted. With these advantages, the pivot table can be one of the best ‘friends’ of data analysts who use Microsoft Excel regularly.

Charts

Charts are the essential way for you to present your data. Everyone can turn in pages upon pages of numbers. And sometimes that kind of presentation is required or requested as well. More often however, the detailed and customizable Charts section in Excel is going to help you create graphics that both convey data and tell a better story. Common charts include:

• Column Chart
• Line Chart
• Pie Chart (2-D and 3-D)
• Doughnut Chart
• Bar Chart

Calculated Items

Calculated items work as custom formulas that use the sum of different items. The sum of different items is calculated and put into a pivot field. It can be looked at almost as a supportive feature of Pivot Tables.

Conditional Formatting

This feature also goes well when you are presenting your final results to a group. It helps you differentiate categories of data cells from each other. You can highlight, emphasize and change the format of the cells, often by adding cell color based on the cell contents. As a business analyst, you are going to deal with numbers constantly. Therefore, presenting them in a more fashionable way will help everyone to realize and understand what they are looking at easily.

It is crucial that you know Excel in today’s times, especially when you have to work with data. A data analyst has to know a number of tools, and excel is one of the basic ones in that list. There are plenty of other tools that can work with large datasets, but it is Excel where most analysis starts, before increasing in complexity. Some questions you need know how to solve in Excel at a basic level are:

1. What are the common data formats in Microsoft Excel?

Excel can accommodate a number of basic and advanced data formats. Some of the most common ones are numbers, percentages, dates, and text.

2. How are data formats used in Excel?

There are many ways in which one can use data formats in Excel. For example, numbers can be used as decimals or rounded figures, percentages can be used to show part of a whole (the whole being 100%), dates can be changed depending on the region and location that Excel is connected to, and text can be used to analyze data and reports imported from other spreadsheets. These formats are often used in conjunction with one another when doing more advanced coding and manipulation, which we will cover further in the article (for example, multiplying a number by a percentage).

3. What is a spreadsheet and what are its fundamental components?

A spreadsheet in Microsoft Excel is made up of cells organized into rows and columns. Information is inputted into the cells in order to organize, display and manipulate data. For example, a spreadsheet can contain the names of a list of products, the price per unit, and the number of units sold. This enables the viewer to read and search through this list and make calculations for what data needs to be analyzed.

4. In how many formats can Microsoft Excel export data?

There are a total of six formats in which data can be exported. They are:

1. Excel workbook: .xlsx
2. Excel macro-enabled workbook: .xlsm
3. Excel binary workbook: .xlsb
4. Template: .xltx
5. Template (code): .xltm
6. XML data: .xml

Choosing which format to export it may be dependent on the team that needs the file or constraints on how large a file can be for its end destination.

5. How can you wrap the text within a cell?

Wrapping text is useful for data presentation and keeping your sheet clean. You must select the text you want to wrap, click wrap text from the home tab, and you will see the text wrap within a cell.

6. What is a Ribbon in Excel?

A Ribbon refers to the topmost area of the application that contains menu items and toolbars available in MS Excel. Ribbons can be shown/hidden using CTRL+F1. The ribbon runs on the top of the application and acts as a replacement for the toolbars and menu dropdowns that can be hard to navigate without lots of muscle memory. The ribbons have various tabs on the top, and upon clicking on it you can see a group of commands which you can use without having to re-navigate to the tab header. Additionally, hovering over ribbon commands will show you the hotkey combination to perform that function, so that you can memorize some of the functions you use most often.

7. What order of operations do you use when evaluating formulas in Excel? In other words, in what order will Excel formulas process the inputted code?

The same as you use in mathematics which is defined by the term “PEMDAS” or “BEDMAS”. This stands for:

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

8. What are cells In Excel?

Each worksheet on Microsoft Excel is comprised of multiple rows and columns. The points where the rows and columns intersect form a rectangle, which is also known as a cell.

9. What is a cell address and how is it denoted in Excel?

The cell address is a unique identification value given to every cell on an Excel sheet to make it easier to find data present on the document. The cell address is denoted by the respective column letter and corresponding row number for the cell. The notation is simple, A1 is the left uppermost cell in a spreadsheet, and other cells count outwards from there.

10. How do you delete unwanted sheets in your Excel workbook?

Right click on the bottom sheet tab of the sheet that you wish to delete. The right click will bring up a menu of options, one of which is delete. Selecting delete will activate a prompt that asks if you are ready to permanently delete the sheet; make sure you have a saved version or really are positive you will no longer need the information contained within.

11. Can you add annotations to a cell in your Excel workbook? If yes, how?

You can annotate a cell in excel using multiple techniques like utilizing colors, callouts, and cell comments.

12. What are cell references?

Cell references are used to refer to data located in the same Excel spreadsheet but to data in a different cell. This becomes useful when building custom formulas that rely on data from different locations within the same sheet or from a different sheet.

13. How do you insert a comment into an Excel spreadsheet?

To add a comment to a sheet, right click on the sheet you want to add the comment to and choose insert comment from the menu. Type your comment in the comment area provided. Cell comments are directly linked to specific cells and are indicated by a red triangle in the corner of a cell. To see the comment, simply hover over the cell with your mouse.

14. Can you protect your data in Excel? If so, how?

There are three ways in which you can do this, and they are: Setting passwords to open the workbook. Adding, removing or hiding sheets (make sure you have reviewed how to unhide the sheet for future reference). Protecting the sheet from the alteration of window sizes or positions.

15. How many cell formats are possible in Microsoft Excel?

Microsoft Excel 2003 can handle 4,000 different cell format combinations, and Excel 2007 and later can handle 64,000. Any unique combination of formats counts towards this number: for example, one cell formatted with Arial font and pink fill color, and another cell with Arial font and blue fill color, would count as two different combinations.

16. How do you change the formatting of a cell in Excel?

The default font of all cell content in a workbook is Calibri (in black). However, this can be changed by selecting any cell and clicking the Font drop-down menu on the Home tab. You can change the font, size and color and make your text bold, italic or underlined. Other options available to you include: - Cell Fill colors. - Cell Borders. - Cell styles. - Auto number formatting (adding currency symbols, percentage symbols, etc). - Alignment. - Auto text formatting (changing the appearance of dates and times etc).

17. What does the red triangle in the upper right corner of a cell denote?

A red triangle in the upper right corner of a cell indicates that a cell comment has been attached to this cell. If you hover over the cell with your cursor, the comment will be displayed. Check back to “How do you insert a comment” above to review that process.

18. What does an Absolute Cell Reference accomplish in Excel?

Absolute cell referencing is the exact opposite of relative cell referencing. By marking the row number and column letter with a $ symbol (e.g. A1), you can make a cell reference fixed (or “absolute”). This means that when you copy and paste it to another cell or use AutoFill, the cell references will not change.

19. What is the function of the dollar sign in Microsoft Excel?

When you insert the dollar sign, it tells a cell in Excel whether to change the location of the reference or not, if the formula for it is copied to other cells. You can also make only the row or column absolute ($A1, or A$1), in order to allow a portion of a reference to be updated when pasting or AutoFilling.

20. What is the shortcut to the Data Filter on Excel?

The shortcut to turn filters on or off is Ctrl+Shift+L. To display the filter drop-down menu, the shortcut is Alt+Down Arrow

21. What is the difference between a function and a formula in Excel?

Formulas are written instructions for a calculation in Excel. All calculations within a spreadsheet will be written as formulas (e.g. =C3+C4+C5+C7+C8). On the other hand, functions are prewritten formulas and a feature of Excel. Excel has over 500 built-in functions that allow users to achieve complicated calculations without having to create the formula themselves, or type it out in full.

22. How do you clear the cell formatting without removing text?

You need to go to the Home tab, click the Clear button and then select the option for Clear Formats. Any formatting done on the cell will be removed without affecting content.

23. How do you enter the current date and time on an Excel sheet?

=NOW() function will return the current date and time. If you just want to enter the current time, but not date, use the keyboard shortcut Ctrl+Shift+semicolon key, and for the current date without time, press Ctrl+Semicolon.

24. What is a Dashboard?

A dashboard is used to present important information through graphical representation. It is helpful in presenting huge data on a single computer screen so it can be reviewed with a glance, and useful insights can be inferred from it. Often a dashboard will utilize color coded gauges to show if metrics are within desired parameters or if a deeper dive is needed on a specific topic.

25. How do you split information in a column into two or more columns?

Text to column option in the Data tab could be used to split the original column into two or more columns. You can split by number of characters, or by delimiting (looking for a certain indicator). A common example is separating cell FirstLastName (Susan Crabapple) into a First Name (Susan) and Last Name (Crabapple). By delimiting based on the space, you could separate a whole range of names into their component parts.

Explaining data succinctly is part of the job, but explaining the software and tools that data analysts use definitely is not, and not everyone is naturally gifted in this area. This means that you need to be prepared for any kind of question that the interviewer may ask, and can show your additional capabilities by articulating what other interviewees may not be able to.

As the interview progresses, the questions can get tougher depending on how well you know the software. If you have experience in data analytics, you can expect questions on functions and other tasks that you can carry out in Microsoft Excel, such as writing SQL Queries within excel.

26. What is an Excel chart, and how many types of charts are there?

A chart in Excel is a feature that allows you to display data through a range of visually intuitive graphs. These charts and graphs can make it easier and quicker to comprehend data, in comparison to just looking at the numbers on the worksheet. Available charts on Excel include:

• Bar graphs
• Line graphs
• Pie charts
• Area graph
• Scatter graphs
• Surface graphs
• Doughnut graphs
• Radar charts

Each of these charts have their advantages and disadvantages, based on the story you wish to tell your audience. Make sure you have a reason for choosing a specific chart, and that it is not emotion or random.

27. What is a Macro in Microsoft Excel?

A macro is a sequence of performable actions in Excel which have been recorded, saved, and named for easy use in the future.

A macro can then be called on whenever necessary to complete the sequence of actions without the user having to replicate each step manually. This saves valuable time and effort when performing repetitive tasks with larger sets of data. You might, for example, need to manipulate a data set in the same way every week, but it involves 15 steps to complete. By recording a macro, you can manipulate the data in a consistent way extremely quickly, with just a click of the button.

28. How do you standardize the formatting in the sheets of a workbook?

Formatting will automatically be copied to all selected sheets, so to apply a single format to all the sheets in a workbook, simply hold down Ctrl (or Cmd on Mac) to select every sheet. Only formatting edits will be copied over with this method, not data.

29. What is a relative cell address?

A relative cell address (or relative cell reference) is a cell address within a formula that does not include a $ symbol before the row number or column letter. This means that when the formula is copied one column over, the cell address will change accordingly. The formula will calculate relative to its position, rather than displaying the calculation from the original position. This is why making formulas with absolute cell references is so important, as you often do not want your equations to roll forward automatically.

30. What is a drop-down list?

A drop-down list allows the creator of a spreadsheet to control the data inputted into certain cells. In other words, when the creator only wants one of four predetermined options to be used, instead of relying on potentially unreliable user inputs (misspellings, non-standard categories), they can ensure it is adhered to by having the users click an option from a drop-down list. Good inputs equal good outputs, and reducing the opportunities for manual entry will allow for better analysis down the line.

31. How do you use the Name Box function?

The Name Box is an input box above the Excel sheet, to the left of the formula bar. Its default mode displays the address of the currently selected cell, but it has other uses too. Firstly, the Name Box can be used to quickly select a specific cell or range of cells: typing in a cell reference like G8 will automatically navigate to the cell G8, and typing in a range of cells like G8:G30 will select all cells within that range. Secondly, the Name Box can be used to create a named range, and then can be used as a drop-down menu to navigate between your assorted named ranges.

32. What is the LOOKUP function in Microsoft Excel?

The LOOKUP function allows the user to find exact or partial matches in the spreadsheet. The VLOOKUP option lets the user search for data located in the vertical position. The HLOOKUP option functions in the same way but in the horizontal plane.

33. Can you use multiple data formats in Pivot Tables?

Data can be imported from a variety of sources by selecting the Data tab and clicking Get External Data > From Other Sources. Excel worksheet data, data feeds, text files and other such data formats can be imported, but you will need to create relationships between the imported tables and those in your worksheet before using them to create a pivot table.

34. How do you create a hyperlink in Excel?

To create a link in Excel, select the element you wish to use as the anchor (this can be a cell or an object like a picture). You can then use a variety of pathways: select Link from the Insert tab, right-click and select Link on the menu, or press Ctrl+K. This will bring up a series of options that will allow you to indicate what kind of content you would like to link to, such as a file, a web page, a specific location or an email address.

35. How do you find the day of the week for a particular date in an Excel sheet?

You can do this by using the WEEKDAY function. First, enter the formula in the required cell, WEEKDAY(A1,1). The second argument (1) determines to what day excel will assign the value. Press Enter, and the Formula will return 3, meaning that your date is the 3rd day of the week.

36. What are the benefits of using the SHEET formula?

When working in a massive workbook with many sheet tabs, it can be helpful to know that you are looking at the correct sheet and not missing any hidden tabs. The SHEET formula returns the number of the sheet you are in, such that the second sheet tab would return a value of 2. This is beneficial as an organizational tool.

37. How do you build a Pivot Table?

• Click on the Data tab in the ribbon section.
• Then, click the Data Validation button in the Data Tools group
• Navigate to Configurations>Allow>List
• Select an array for the source list

38. What is the default value of the last parameter of the VLOOKUP function?

If the last parameter is not specified via TRUE or FALSE, the return value will default to TRUE (approximate), and show an approximate match for your request. This is usually not what you are looking to accomplish, as the typical use case for VLOOKUP is to find an exact match.

39. How do you refresh a Pivot Table?

There are several methods to refresh a pivot table without making a new one from scratch:

1. Click the pivot table, select Analyze, and then select Refresh from the pivot table tools menu.
2. Right-click the pivot table and select Refresh.
3. Use the keyboard shortcut Alt+F5.

You can also refresh all pivot tables within a workbook at once by selecting the arrow underneath the Refresh button in the pivot table tools menu, and select Refresh All.

40. What is the most common error message in Excel?

The most common error message in Excel is the #### error message, which is displayed when a cell is not large enough to display all the data that has been inputted into it. To solve this error message, simply drag the cell to increase its width or depth.

Other common errors include #VALUE! , which results when a portion of the data you are trying to analyze has a different format than the rest, or #NAME! , when the formula was written incorrectly and is not processing as it should.

41. How do you find duplicate values in a column in Excel?

To find but not delete duplicates in a column, select the target range of data and navigate to the Style group on the Home tab. Here, click the arrow next to Conditional Formatting. You will then be able to choose Highlight Cell Rules, Duplicate Values, and enter the values you wish to find duplicates of. This will highlight duplicates of the values you entered, but will not manipulate the data within.

42. What are Macro languages in Excel?

Macros for Excel are written in VBA (Visual Basic for Applications). You can use VBA to manually type out actions for a macro yourself, or you can “record” actions using the Macro Recorder in Excel, and the software will convert these to VBA code for you. Once you become stronger in VBA, you can review recorded macros and perform the updates yourself to tweak your use cases.

43. How do you extract unique values in Excel?

You can find unique values by selecting the desired range of data and navigating to Data > Sort & Filter > Advanced. To permanently delete duplicate values and create a list of unique values only, click Data > Data Tools > Remove Duplicates.

44. How do you create Cross-Tabulation in Excel?

The product of the pivot table feature in Excel is often referred to in other software as a contingency table or cross-tabulation. To create a “crosstab” use the PivotTable option on the Insert tab.

45. What are Freeze Panes?

The Freeze Pane function in Excel serves to lock rows or columns of a sheet so that they are always visible. This can be found on the View Tab of the ribbon. This is often helpful when you want to keep headers of columns in view, or perhaps the identifier of a row while scrolling right.

Bonus Question: What is the difference between “Relative”, “Absolute”, and “Mixed” cell references in Excel?

In Excel, when copying formulas from one cell to another, the way cell references are treated can differ depending on their type:

• Relative reference (e.g., A1): When copying this type of reference from one cell to another, it adjusts based on the relative position of the cell. For instance, if you copy a formula from cell B2 that references A1 to cell B3, the reference will automatically change to A2.
• Absolute reference (e.g., A1): This reference type remains constant regardless of where you copy the formula. It will always refer to the specified cell.
• Mixed reference (e.g., A1 or A1): In this type, one part (either row or column) remains constant while the other part adjusts. If you copy the formula with A1 from cell B2 to B3, it will still reference A1. However, if copied from B2 to C2, the reference will change to B1.

By understanding and using these references effectively, data analysts can make their spreadsheets more dynamic and reduce errors during data manipulation. It also helps if they need to quickly scale formulas across dozens or hundreds of columns and rows.

No matter what position you work in, no matter which company you work for, if you are unable to solve the problems you face, you will not be successful. That means you have to be good at identifying the problems themselves. Then you have to consider which ones can be or should be solved. After your evaluations, you can create solutions to challenges to both the organization and to you as a data analyst. By being able to answer difficult questions on Excel, and knowing how to navigate these issues, you will find your job as a data analyst much easier.

These harder questions about Excel include data analysis, sorting and other hard functions.

46. What is conditional formatting?

Conditional formatting is a method that allows us to identify the characteristics (conditions) of a cell’s contents that are of interest to us, and superimpose highlighting of cells or ranges that meet that criteria. When a cell is updated with new information, the conditional formatting will recognize and update itself. For instance, if you wanted to apply a green highlight to any cell that contains the letter Z, and you update Randolph to Ziggy, the conditional format would automatically apply.

47. What is the limitation of the VLOOKUP function?

The VLOOKUP function can only move in one direction, from left to right. Therefore, the information you wish to seek out must be located in a column to the right of the lookup value’s location. In newer versions of Excel, a successor to VLOOKUP has been added, called XLOOKUP. This new function works in any direction and defaults to exact matches rather than approximate. At some point in the future, XLOOKUP will completely replace VLOOKUP, but this will not happen until the majority of users have moved away from using older versions of Excel.

48. Does VLOOKUP use case sensitive values?

VLOOKUP is not case-sensitive, and will always return the first value of the match irrespective of the case. In other words, the name Apgar and the acronym APGAR would be viewed as the same by VLOOKUP. It is, however, possible to manipulate VLOOKUP into returning case-sensitive values by using a helper column, or by sorting your data in an ascending or descending order so that the value you want is always the first to be encountered by VLOOKUP.

49. How do the INDEX and MATCH functions work in Excel?

You can use two MATCH functions within an INDEX formula to specify a cell reference and return the value of that cell. The dynamic formula will return the corresponding data of any two MATCH values you input. For example, if you have a table detailing the price per unit and the number of units sold for a variety of products, you can use the match index function to return a specific piece of information about a specific product.

50. What is the difference between COUNT, COUNTA, COUNTBLANK and COUNTIF functions?

COUNT: This function counts how many cells within a specified range contain numerical data. It will ignore (not count) any cells that are blank or contain text or symbols only.

COUNTA: This function counts how many cells within a specified range contain data of any type (the cell is filled). It will count all cells that are not blank.

COUNTBLANK: This function will count the number of blank cells within the designated range.

COUNTIF: This function will count only the cells whose value meets a certain condition specified by the user.

51. What is Data Validation?

Data Validation limits the type of values that can be inserted into a single cell or a set of cells by a user. It again works to limit user input errors and keep the data inputs clean.

52. How can you measure an Excel percentage?

• Select the destination cell to display the percentage
• Then, type a “=” sign.
• Type in your reference, say A1/ A2, then hit the Enter key.
• Click on the Home tab, select the % symbol from the numbers group. This converts the value into a percentage format.

53. Can you build Excel feature shortcuts?

Yes. It is possible to configure the ‘Easy Access Toolbar’ above the home button to display your most commonly used shortcuts.

54. What are the alignments for Left, Right, Fill and Distributed?

• The left/right alignment aligns the cell’s contents to the left and right.
• Fill the cell with the same text repetitively, as the name suggests.
• Distributed spreads the text over the cell width.

55. How do you use Advanced Filters?

You can extract a unique list of items using Advanced Filters, or you can also extract a particular item from various worksheets. It may be assumed that the Advanced Filter is an advanced Auto Filter variant.

56. What is an Excel Array formula?

With an array, a user can perform multiple calculations on multiple items using just one formula. For instance, if you want to multiply A1 by B1, A2 by B2, A3 by B3, and so on, instead of writing a formula for each calculation, you can write =A1:A10*B1:B10 in cell C1, and the results for each calculation will spill down the C column.

This is called a multi-cell array, as the results will be stored in multiple cells. You can also create a single-cell array if, for example, you wanted to sum the entirety of the values calculated by the above array. This would look like: =SUM(A1:A10*B1:B10) and return a single value calculated by adding the results of all ten multiplications together.

57. How do you extract the first name from a full name in Excel?

The FIND function will return the numerical location of this target (with the first character of the text being 1). The LEFT function can then extract the number of characters specified by the FIND function from the beginning of the text (i.e the left).

However, the value returned by FIND will include the space itself, so we need to subtract 1 from the value in order to find the actual ending point of the first name. The formula would look like this:=LEFT(A1,FIND(“ ”,A1)-1).

A second method would separate the first names and last names and deposit them into separate new columns, using the Text to Columns feature found in the Data tab. We covered this earlier under how to split information in a column. The Text to Columns dialogue box will allow you to select the delimiter separating each field (e.g space) and show you a preview of the result. The last step will allow you to choose where you want the result to be displayed.

58. What is the difference between a function and subroutine in VBA?

It is the duty of a function to return the value of the mission it performs. Subroutines, meanwhile, do not return the importance of the assignment they perform. Functions are used as formulas, as they are in spreadsheets. Subroutines are not used as formulas directly inside spreadsheets.

59. What’s the distinction in VBA between thisworkbook and activeworkbook?

The name of the workbook from which the code is running is provided by ThisWorkbook. ActiveWorkbook is as the name implies, the workbook that is actually active in the numerous open workbooks.

60. What is the easy way to go back to a specific area of a worksheet?

Using the name box is a fast way to return to a particular section of the worksheet. To return to a particular area of a worksheet, you can type the cell address or name of the range in the name box.

61. How do you handle circular references in Excel?

Circular references occur when a formula in a cell refers back to itself either directly or indirectly. Excel will display a warning message if it detects a circular reference, because it can cause the program to calculate endlessly while never generating a result. It is therefore critical to address the circular references immediately to prevent them from potentially interfering with other formulas. To identify and correct circular references:

• Go to the “Formulas” tab.
• In the “Formula Auditing” group, click on “Error Checking.”
• Choose “Circular References” to see where they exist.
• Check the formulas in the identified cells and adjust them to eliminate the circularity.

Excel is one of the most frequently asked data analyst interview questions, and they are not designed to catch candidates out or determine whether they “know the correct answer”. They are instead used in tandem with portfolios and past experiences to gauge a candidate’s experience and proficiency with the software. These questions are not “hard” per se, but they are often quite technical. Therefore, if you are familiar with the software and take your time to prepare for your interview, you will have a high chance of acing it!

In addition to Excel, you’ll also want to practice a variety of SQL questions for data analysts and data analyst behavioral questions prior to the interview.

For in-depth interview preparation, we recommend exploring the data analytics learning path and the product metrics learning path. In case you are seeking a guide that is tailored to a specific company, the Google data analyst interview guide would be worth your consideration.