Show
Microsoft applications such as Word, Excel, PowerPoint are used in almost every large organization as well as by millions of small businesses, freelancers, and entrepreneurs. For something that is so popular, it makes sense to have a decent knowledge on how to use these applications efficiently. In some job roles, a major part of the work involves working with these applications on a daily basis. I have been part of many interview panels where I have seen the candidates mention Excel as one of the skills they are expert in, but when asked a few questions on it, the expertise fizzled out. In case you’re prepping up for your next interview, and have listed Excel as one of your skills, take some time to go through these common Excel interview questions. I have tried to cover all the popular Excel interview questions I have seen being asked, as well as some that I thought could be good to know. This guide can also be used by interviewers who want to gauge the Excel skill of the candidates in the interview. Note: I will keep on adding new questions to this guide. You can bookmark it so that you can come back and check it later. Related:Best Books to Learn Excel and become an Expert How to Use this Excel Interview Questions Guide
Excel Interview Questions TopicsExcel Formatting QuestionsBelow are some common formatting Excel interview questions (click on the question to view the answer to it). What are the different data formats in Excel?The following formats are available in Excel:
How can you wrap text in Excel?Wrapping text in Excel allows you to avoid any text overflowing out of the cell. By applying the the wrap text option, you can make sure all the text fits nicely in one single cell (which may change it’s height though). To wrap text, select the cell, go to the Home tab and click on the Wrap text option in the Alignment group. Note that this is a toggle button, which means that if you click on it again, it will unwrap the text. How can you merge cells in Excel?To merge cells, you need to first select the cells that you want to merge, then go to the Home tab, and click on the ‘Merge and Center’ option in the Alignment group. Note that while using ‘Merge and Center’ gets the job done, it is not the most efficient way to do it. The problem of using ‘Merge and Center’ is that the resulting cells wouldn’t sort properly. The right way merge cells is by using the ‘Center Across Selection’ option. You can read more about merging cells the right way here. What is ‘Format Painter’ used for?‘Format Painter’ allows you to copy the format from a cell and apply it on another cell (or range of cells). You can read more about Format Painter here. How would you clear all the formatting without removing the cell contents?Sometimes, you may want to remove all the formatting (colors, borders, font styling, etc.) and just have plain simple data. You can do that by clearing all the formatting in Excel. To do this, you need to use the ‘Clear Formats’ option, which can be found in the Home tab in the editing group. It becomes visible when you click on the ‘Clear’ drop down. Note there are other options as well – such as clear contents, clear comments, and clear Hyperlinks. In case you want to clear everything – use the ‘Clear All’ option. What is conditional formatting?Conditional Formatting allows you to format a cell based on the value in it. For example, if you want to highlight all the cells where the value in less than 30 with a red color, you can do that with Conditional Formatting. You can read more about Conditional Formatting here. How would you highlight cells with negative values in it?You can do this using conditional formatting. Here are the steps:
How would you highlight cells with duplicate values in it?You can do this easily using conditional formatting. Here are the steps:
How would you highlight cells with errors in it?In Excel there can be different types of errors – such as #N/A, #DIV/0!, #VALUE!, #REF!, #NAME, and #NUM. You can highlight all the cells that contain any of these errors using conditional formatting. Here are the steps to highlight cells with errors:
How can you make text invisible in Excel?There are multiple ways to do this:
Excel Formula QuestionsBelow are some common Excel formula questions for interviews (click on the question to view the answer to it). What is the order of operations used when evaluating formulas in Excel?Following is the order of precedence in Excel formulas:
An easy way to remember this is by the acronym PEMDAS – which is the first alphabet of each operator. What is the difference between a function and a formula in Excel?A formula is a user-defined expression that calculates a value. A function is pre-defined built-in operation that can take the specified number of arguments. A user can create formulas that can be complex and can have multiple functions in it. For example, =A1+A2 is a formula and =SUM(A1:A10) is a function. What according to you are the top 5 functions in Excel?This question is often asked to understand comfort of a candidate with Excel functions. While there are 450+ functions in Excel and there is no set criteria to select the top five, here are the ones I believe are worthy:
I have chosen the above functions as these are not very basic and are quite useful for someone who does analysis in Excel. You can also consider the following functions – SUMPRODUCT, TEXT, SUM, AVERAGE, LEN/LEFT/RIGHT/MID. Again, there is no right or wrong answer to this. Just make sure you know the functions that you mention. You can find detailed explanations on most popular Excel functions here. What is the difference between absolute and relative cell references?In Excel reference refers to a cell reference – such as A1 or range reference – such as A1:A10. Relative References: These are cell references that change when you copy and paste the formula that has the references. To give you a simple example, if you put =A10 in cell A1, and then you copy cell A1 and paste it in cell A2, the reference would change to A11. This happens as this is a relative cell reference and it changes relative to the cell it’s copied from. Absolute References: These are the references that remain the same and don’t change copy and paste the formula that has the references. For example, if you put =$A$10 in cell A1 and then copy cell A1 and paste it in cell A2, the reference would still remain $A$10. The $ sign before the column alphabet and the row number makes it absolute. You can read more about absolute and relative references here. What are the different types of errors you can encounter in Excel?When working with Excel, you can encounter the following six types of errors:
How can you tackle errors when working with Excel Formulas?There are various ways you can tackle the errors in Excel:
Which function would you use to get the current date and time in Excel?The following functions can be used:
Remember that that dates and time are stored as numbers in Excel. So you can perform operations such as addition/subtraction with these dates. How can you combine the text from multiple cells using a formula?To combine text from different cells, you can use any one of the following three methods:
You can read more about joining strings in Excel here. What formula would you use to find the length of a text string in a cell?You can find the length of a string in a cell using the LEN function. For example, if you want to know the length of the string in cell A1, you can use the formula =LEN(A1) What is the syntax of the VLOOKUP function?VLOOKUP is definitely one of the most popular Excel functions. And this is also one of the most asked Excel question that I have seen in interviews. Here is the VLOOKUP syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
If you’ve time, I recommend going this VLOOKUP function guide I created with 10 practical examples. How would you get rid of leading / trailing / double spaces in a text in Excel?To get rid of leading, trailing, and double spaces, you need to use the TRIM function. For example, if you have a text string in cell A1 and you want to remove the spaces, you can use the following formula: =TRIM(A1) Note that it doesn’t remove single spaces between words. Excel TRIM function does a good job in removing spaces in Excel, however, it fails when you have non-printing characters (such as line breaks) in your data set. To remove non-printing characters, you can use a combination of TRIM and CLEAN functions. If you have some text in cell A1 from which you want to remove spaces, use the below formula: =TRIM(CLEAN(A1)) You can read more about it here. What are the known limitations of the VLOOKUP function?The VLOOKUP function is mighty useful, but it also has a few limitations:
You can read my comparison of VLOOKUP Vs. INDEX/MATCH here. Here are some examples of using the INDEX MATCH combo in Excel. When would you use the SUBTOTAL function?When you’re working with tabular data, you can use the SUBTOTAL function to get variety of subtotals – such as AVERAGE, COUNT, MAX, MIN, STDEV. One of the highlights of SUBTOTAL function is that it allows you to ignore hidden/filtered cells. So if you have a huge data set and you filter it based on a criteria or hide some rows, SUBTOTAL function will automatically update to give you the result from the visible cells only. Of course, if you don’t want the data of filtered/hidden cells to be ignored, you can do that too. What are volatile functions? Can you name a few?A volatile function recalculates the formula again and again (whenever there is any change in the worksheet). This can slow down the workbook considerably. A very simple example of a volatile function is the NOW() function (to get the current date and time in a cell). Whenever you edit any cell in a worksheet, it gets recalculate. This is fine if you have a small data set and less number of formulas, but when you have large spreadsheets, this could significantly slow down the processing. Here is a list of volatile formulas:
BONUS TIP: Conditional formatting is also volatile. You should use it only the cells where it’s needed. Excel Keyboard ShortcutsBelow are some common Excel interview questions about keyboard shortcuts that you might get asked in an interview (click on the question to view the answer). What are the most useful keyboard shortcuts you use?There are hundreds of Excel keyboard shortcuts. I am listing my top five shortcuts, but in case you have your own, feel free to use that.
You can read the following tutorials in case you want to more keyboard shortcuts:
What is the shortcut for opening the find and replace dialog box
What is the shortcut for spell check?F7 – This opens the spell-check dialog box. What is the shortcut to quickly auto-sum rows/columns?ALT = If you have numbers in a column/row, you can quickly get the sum by using this Excel keyboard shortcut. What is the shortcut to open a new Excel workbook?CONTROL N This works only when you have the Excel application open. How can you select all the cells in the worksheetYou can use CONTROL A A – hold the control key and hit the A key twice. In case you don’t have any data around the active cell, hitting the A key once would select the entire worksheet. But in case there is data, hitting the A key once select the entire data and hitting it again then selects all the cells in the worksheet. How would you insert a new line in the same cell?To insert a new line in the same cell, use the shortcut ALT Enter – hold the ALT key and press enter. What’s the shortcut to insert a comment in Excel?SHIFT F2 Select the cell in which you want to add the comment, hold the ALT key press the F2 key. In case you have selected a range of cells, it will insert the comment in the active cell only. Excel Pivot Table QuestionsBelow are some common Excel interview questions about Pivot Table that you might get asked in an interview (click on the question to view the answer). What is a Pivot Table, and when would you use one?A Pivot Table is a feature in Microsoft Excel that allows you to quickly summarize huge data sets (with a few clicks). Even if you’re absolutely new to the world of Excel, you can easily use a Pivot Table. It’s as easy as dragging and dropping rows/columns headers to create reports. To give you an example, if you have the 10,000 rows sales data from four different regions, you can use a Pivot Table to instantly find what are the total sales in each region. If you want to further drill down and see what are the sales of each product in each region, you can easily do that (it’s literally as easy as dragging a data point in a box). You can read more about Pivot Tables here. What are the various sections in a Pivot Table?A Pivot table is made up of four different sections:
What are slicers?Slicers were introduced in Pivot Table in the 2010 version of Excel. A Pivot Table Slicer enables you to filter the data when you select one or more than one options in the Slicer box (as shown below). What is a Pivot ChartWhen you create a Pivot Table, you get the summary of your data. You can also plot this summary in a chart that is connected to the data. This chart is called the Pivot Chart. One big benefit of using a Pivot Chart is that it updates when you change the Pivot Table layout. For example, if you have the total sales by region, and you update the Pivot Table to show sales data for each product in the regions, the Pivot Chart would accordingly update. What are the difference between Pivot Charts Vs Regular Charts?While Pivot Charts are amazing and come with the ability to update when the Pivot Table updates, these are not as flexible as the regular charts. In general, you can do a lot of customization in a regular Excel chart, but not in a Pivot chart. Also, if you customize a Pivot Chart, and then update the Pivot Table, you are likely to lose the customization. Despite the limitations, Pivot Charts are useful and can help create quick views from a Pivot Table. How can you refresh a Pivot Table?To refresh a Pivot table, click on any cell in the Pivot Table, right-click and select Refresh. Another way of refreshing a Pivot Table is to select any cell in the Pivot Table. It will enable the Pivot Table Tools tab. In the Analyze tab, click on ‘Refresh’. You can read more about refreshing the Pivot Table here. Can you group dates in Pivot Tables?If you have date wise records, you can easily group these into the following segments:
The option to group data in Pivot Table is in the Analyze tab, which becomes visible when you select a cell in the Pivot Table in the Rows area. You can read more about grouping dates in Pivot Table here. What is a Pivot Cache?Pivot Cache is something that automatically gets generated when you create a Pivot Table.It is an object that holds a replica of the data source. While you can’t see it, it is a part of the workbook and is connected to the Pivot Table. When you make any changes in the Pivot Table, it does not use the data source, rather it uses the Pivot Cache.The reason a pivot cache gets generated is to optimize the pivot table functioning. Even when you have thousands of rows of data, a pivot table is super fast in summarizing it. You can drag and drop items in the rows/columns/values/filters boxes and it will instantly update the results.Pivot Cache enables this fast functioning of a pivot table. Can you make a Pivot Table from multiple tables?Yes, you can create one Pivot Table from multiple different tables. However, there needs to be a connection in these tables. For example, if you have two tables, one that has date, Product ID and sale value, and another which has Product ID and Product Name, then you can combine these as the common column in Product ID. Once you have connected these tables, you can create a Pivot table from these. A crucial part of this is to set-up table relationships (where you specify the relationship between 2 tables). You can read more about this here. What are Calculated Fields in Pivot Table?A calculated field allows you to add a column to the Pivot Table data where you can use the existing columns to do some calculations. Let me give you a simple example. Suppose you have a data set of retailers and you’ve created a Pivot Table as shown below: The above Pivot Table summarizes the sales and profit values for the retailers. Now, what if you also want to know what was the profit margin of these retailers (where the profit margin is ‘Profit’ divided by ‘Sales’). You have a couple of options:
Consider Calculated Field as a virtual column that you have added using the existing columns from the Pivot Table. There are a lot of benefits of using a Pivot Table Calculated Field:
You can read more about the Pivot Table Calculated Field here. Excel Charting QuestionsBelow are some common Excel interview questions about charting that you might get asked in an interview (click on the question to view the answer). What is a Column chart?A column chart is made up of vertical bars that is used to compare values over time or two compare values in different categories. For example, you can use it to see how the sales have done over the years. Or you can use it to compare which product category has done better sales. Since you can see all the vertical bars at one go, it is easier to visually see and compare. You can also create clustered column charts, where you can have multiple columns for the same category or year (something as shown below). What is a Bar chart?A bar chart is made up of horizontal bars that is used to compare values in different categories. For example, you can use it to compare which product category has done better sales. Or what has been the response of a survey. You can also create clustered bar charts, where you can have multiple bars for the same category (something as shown below). What is a Line chart?Line chart are useful when you want to show a trend over the years (or other time periods such as weeks, months, or quarters). You can have multiple lines in a line chart. This would allow you to compare different categories over the same period of time (something as shown below). What is a Scatter chart?A scatter chart is used to compare two sets of values. For example, you can have data of different products on two KPIs, and you can plot the data on a scatter chart (as shown below). This allows you to see what products are doing well on both the KPIs (the top right quadrant) or doing bad on both the KPIs (bottom-left quadrant). You can see an example of scatter chart in action in the KPI Dashboard here. Are Pie charts good? Should it used in reports/dashboard?There are two school of thoughts. There are some who completely hate Pie chart and recommend never to use these (such as this article). And then there are some (including myself), who sometime use Pie charts in dashboards in reports. There are many managers who are comfortable with pie charts and find these easy to read. So if you want to show a breakup of revenue by division (where you have only a few divisions), then you can use a pie-chart. Let me be clear. Pie chart can be completely replaced by a bar chart. There is no additional benefit of using it. But in some cases, Pie charts make a good story (for example showing that one division is bringing in ~75% of the revenue as shown below). You should avoid using Pie charts:
What is a Waterfall chart? When would you use it?A waterfall chart shows different values (positive and negative) that lead to the final result value. For example, if you’re analyzing companies net income, you can have all the cost components shown in the waterfall chart. This will help you visually see how the value from revenue to net income is obtained when all the costs are deducted. What are Combination charts?Combination charts are those where you combine more than one chart type. A popular example of this is showing bar chart with a line chart. Combination charts let you present and compare two different data-sets that are related to each other. For example, you may be interested in plotting the revenue figures of a company, and at the same time, also be able to show how the profit margin has changed. A combination chart (as shown below) is an apt way of doing this. What is a secondary axes in a chart?In a chart, there is a Y axis where you show the scale on which you can measure the chart (be a bar chart or line chart or others). In cases where you have two show two different types of data set with a significant difference in value, you can use the secondary axes. To give you an example, if you want to show the revenue and net income margin in the same chart, you need to show two different axes. This is because revenue numbers can be in thousands or millions, but net income margin would be in percentage and always less than 100%. In this case, you need to have two axes, one that shows scale for revenue and one that scale for net income margin. So when you add another axes, it is called the secondary axes. In the below figure, the axes on the right is the secondary axes. What is a Bullet chart? When should we use it?Bullet charts were designed by the dashboard expert Stephen Few, and since then it has been widely accepted as one of the best charting representations where you need to show performance against a target. One of the best things about bullet charts is that it is power-packed with information and takes little space in your report or dashboards. Below is an example of a bullet chart: Note that bullet charts are not a default chart type in Excel, and you need to use a number of steps to create these. You can read more about bullet charts here. Excel Data Analysis QuestionsBelow are some common Excel interview questions about Data Analysis that you might get asked in an interview (click on the question to view the answer). How to replace one value with another in Excel?You can replace one value with another using the FIND & REPLACE feature in Excel. To do this, select the data set and use the keyboard shortcut – CONTROL H (hold the control key and then press H). This will open the Find & Replace dialog box. In this dialog box, you can specify the value you want to change and the replacement value. If you’re interested in learning more about Find and Replace, click here. What kind of data filters are available in Excel?In Excel, you can filter a data set based on the kind of data. The following types of data filters are available in Excel:
You can apply filter to a data set, by selecting the data, then clicking the Home tab and clicking on the Filter icon. When you have tabular data and you apply filters, based on the data in the column, Excel shows you the relevant filter. For example, if you have text data, it will show you filters related to text (such as text contains, begins with, ends with, etc.). You c How can you sort data in Excel?There is a sorting feature in Excel that can sort data based on text, numbers, or colors. Here are some ways to sort data in Excel:
You can read more about data sorting here. What is data validation?Data Validation allows you to enter only that data in a cell that satisfies a criteria. For example, if you want to only have numbers entered in a cell, then you can use Data validation to do this. In case any one enters something other than numbers, Excel will show an error and not allow that. Data validation options are available in the data tab. You can read more about data validation here. Data validation can be really helpful when you’re creating forms in Excel. For example, if you want a user to enter only their age, you can use Data Validation to make sure the cell accepts a numeric value only. How can you transpose a data set in Excel?There are two popular ways to transposing data in Excel:
With Paste Special dialog box, you need to first copy the data that you want to transpose, select the cell where you want to paste it, right-click and go to Paste special, and select the Transpose option (as shown below). You can read more about transposing data in Excel here. How can you select all blank cells in Excel?If you work with a data set that has blank cells in it, you can easily select these cells in Excel. Once selected, you can choose to highlight these, delete these, or add some value to it (such as 0 or NA). To do this, you need to use the Go To Special dialog box in Excel. Here are the steps to select all blank cells in Excel:
You can read more about selecting blank cells in Excel here. How can you remove duplicates from a data set?Excel has an in-built functionality that allows you to remove duplicate cells/rows in Excel. You can find the option to remove duplicates in the Data tab. Here are the steps to remove duplicates in Excel:
You can read more about removing duplicates in Excel here. What is an advanced Filter in Excel?Excel Advanced Filter – as the name suggests – is the advanced version of the regular filter. You can use this when you need to use more complex criteria to filter your data set. Here are some differences between the regular filter and Advanced filter:
You can read more about Excel Advanced Filter here. Can you sort multiple columns at one time?Yes, you can sort multiple columns in Excel. With multiple sorting, the idea is to sort a column and then sort the other column while keeping the first column intact. Below is an example of multiple level sorting in Excel. Note that Column A is sorted first in this case and then Column B is sorted. The final result has Column A values sorted, and Column B sorted for each item in Column A. To do multiple level sorting, you need to use the Sort dialog box. To get that, select the data that you want to sort, click the Data tab and then click on the Sort icon. In the Sort dialog box, you can specify the sorting details for one column, and then to sort another column, click on ‘Add Level’ button. This will allow you to sort based on multiple columns. You can read more about multiple-column data sorting here. What is a one-variable data table?One variable Data Table in Excel is most suited in situations when you want to see how the final result changes when you change one of the input variables. For example, if you want to know how much on monthly installment change if you increase/decrease the number of months, you can set up a one-variable data table for it. This can be useful when you want to keep the monthly payment less than $500 and know what all options you have (6 months, 9 months, 12 months, etc.) The option to set One-Variable data table is in the Data tab, in the What-if Analysis drop down. You can read more about One-variable data table here. What is a two-variable data table?Two variable Data Table in Excel is most suited in situations when you want to see how the final result changes when you change two of the input variables. For example, if you want to know how much on monthly installment change if you increase/decrease the number of months and the interest rate. You can set up a two-variable data table for it that will show you a the final monthly installment based on different combinations of interest rate and number of months. This can be useful when you want to keep the monthly payment less than $500 and know what all options you have. The option to set Two-Variable data table is in the Data tab, in the What-if Analysis drop down. You can read more about Two-variable data table here. What is Scenario Manager?Scenario Manager in Excel can be the tool of choice when you have multiple variables, and you want to see the effect on the final result when these variables change.If you only have one or two variables changing, you can create a one variable or two-variable data table. But if you have 3 or more than 3 variable that can change, then scenario manager is the way to go. For example, if you’re a regional sales manager and have four areas under you, you can use scenario manager to create different scenarios (such as):
You get the idea.. right? With scenario manager in Excel, you can easily create the scenarios and analyze these one by one or as a summary of all the scenarios. You can read more about scenario manager here. What is Goal Seek?Goal Seek in Excel, as the name suggests, helps you in achieving a value (the goal) by altering a dependent value. For example, if you’re buying a car and you want to know how many month’s installment you should opt-for so that your monthly payment is not more than $500, you can do this using Goal seek. You can read more about Goal Seek here. What is a Solver?Solver in Excel is an add-in that allows you to get an optimum solution when there are many variables and constraints. You can consider it to be an advanced version of Goal Seek. With Solver, you can specify what the constraints are and the objective that you need to achieve. It does the calculation in the back-end to give you a possible solution. You can read more about Solver here. Excel VBA QuestionsBelow are some common Excel interview questions about VBA that you might get asked in an interview (click on the question to view the answer). What is VBA?VBA stands for Visual Basic for Applications. It’s the programming language that you can use to automate tasks in Excel. What are the benefits of using VBA in Excel?While Excel has a lot of amazing features and functionalities, it may not have everything you need. VBA allows you to enhance Excel’s ability by creating codes that can automate tasks in Excel. Below are some of the things you can do this VBA:
What is a macro in Excel?A macro is a set of instructions written in the VBA language that Excel can understand and execute. A macro can be as simple as a single line or can be thousands of line long. In many cases, people tend to use VBA code and macro interchangeably. How can you record a macro in Excel?Even if you know nothing about VBA, you can still create some macros and automate your work. You can do this by recording a macro. When you record a macro, Excel closely watches the steps you’re taking and notes it down in a language that it understands – which is VBA. Now, when you stop the recording, save the macro, and run it, Excel simply goes back to the VBA code it generated and follows the exact same steps. This means that even if you know nothing about VBA, you can automate some tasks just by letting Excel record your steps once and then reuse these later. You can record a macro by using the Record Macro option which is available in the Developer tab in the ribbon. Once you record the macro, Excel stores it with the name you specify and then you can easily reuse it as many times as you want. You can read more about recording a macro here. What are limitations of recording a macro in Excel?While recording a macro is a great way to quickly generate code, it has the following limitations:
What is a UDF in Excel VBA?A UDF is a User Defined Function in VBA. These are also called custom functions. With VBA, you can create a custom Function (UDF) that can be used in the worksheets just like regular functions. These are helpful when the existing Excel functions are not enough. In such cases, you can create your own custom UDFs to cater to your specific needs. You can read more about User Defined Functions here. What are Events in VBA?In Excel VBA, an event is an action that can trigger execution of the specified macro. For example, when you open a new workbook, it’s an event. When you insert a new worksheet, it’s an event. When you double-click on a cell, it’s an event. There are many such events in VBA, and you can create codes for these events. This means that as soon as an event occurs, and if you have specified a code for that event, that code would instantly be executed. Excel automatically does this as soon as it notices that an event has taken place. So you only need to write the code and place it in the correct event subroutine. You can read more about the Excel VBA Events here. What are some of the useful loops in VBA?There are the following loops in Excel VBA:
You can read more about Excel VBA Loops here. What are the different ways to run a macro in Excel?You can use the following ways to run a macro in Excel:
You can read more about running a macro here. What are UserForms?A UserForm is a dialog box that you can design and build in Excel VBA. Once created, these can be used in many ways in the Excel worksheet area:
What are add-ins?An add-in is a file that you can load with Excel when it start. When you create an add-in and install it in Excel, it opens whenever the Excel application opens. You can have many different macros in an add-in and whenever you open Excel, these codes are available for use. This is useful as you can create an add-in and becomes available to all the workbooks. So if there are some tasks that you need to do often, you can automate these by writing a macro and then saving these as add-ins. Now no matter what Excel you open, you can use the macro. Another benefit of add-in is that you can share the add-in file with others as well. All they need to do is install it once and they will also have the same macros available to them You can read more about creating an add-in here. Excel Dashboard QuestionsBelow are some common Excel interview questions about dashboards that you might get asked in an interview (click on the question to view the answer). What is difference between a dashboard and a report?A report is meant to provide relevant data. It could be a report that has all the data or can also have a few charts/visualizations. Examples of reports can be sales transaction data or employee survey data. A dashboard is meant to answer questions using the data. It could be to show which regions are performing better in sales or which areas are lagging in terms of employee feedback. These dashboards could be static or interactive (where the user can make selections and change views and the data would dynamically update). You can read more about Excel Dashboards here. What are some of the questions you should ask a client/stakeholder before creating a dashboard?While the questions would depend on a case to case basis, there are few high-level questions that you should ask when creating a dashboard in Excel.
There can be many such questions. The intent here is to be clear on what the dashboard needs to be and what purpose it serves. What are some interactive controls you can use in dashboards?Excel has a number of interactive tools that can be used in a dashboard:
Apart from these regular interactive tools, you can also use VBA to add more functionality to your dashboard. What are some useful chart types that you can use in a dashboard?One of the need when creating a dashboard is to show relevant data with visuals. Charts that can tell a good story and show relevant information are more suited for dashboard. Since a dashboard is usually expected to fit in a single screen, there is limited space for data and visuals. In such cases, combinations charts come handy. The following charts can be useful when creating a dashboard:
What are some best practices when creating a dashboard in Excel?Here are some best practices when creating dashboard in Excel:
Other Excel QuestionsThese are the questions that I could not fit in any of the above categories. So I am putting all these together here (click on the question to view the answer). What is an Excel Table?An Excel Table is a feature in Excel. This is not the same as tabular data. When you convert tabular data into an Excel Table, there are a few additional features that get added to it that can be really useful. According to Microsoft Help Site – “A table typically contains related data in a series of worksheet rows and columns that have been formatted as a table. By using the table features, you can then manage the data in the table rows and columns independently from the data in other rows and columns on the worksheet.” You can read more about Excel Tables here. What are the benefits of using Excel Table?When you convert tabular data into Excel Table, the following features are automatically added to the data:
What are structured references?When you use an Excel Table, you don’t need to use the cell references. Instead, you can use the Table name or the column names. These references are called structured references. What are the commonly used file formats in which an Excel file can be saved?There are a lot of file formats in which you can save your Excel workbook. Some commonly used ones are:
How to reduce the size of an Excel file?There are many ways you can reduce the file size of an Excel Workbook:
Read more: 8 ways to reduce file size in Excel What steps can you take to handle slow Excel workbooks?You can use the following techniques to handle slow Excel workbooks:
You can read more how to handle slow Excel workbook here. How many rows and columns are there in an Excel worksheet?An Excel sheet (2007 and later versions) has:
How to add/delete rows in Excel?To add new rows, right-click on any cell where you want insert the row and click on the Insert option. This will open a dialog box which you can use to insert new rows. Similarly, to delete cells/rows/columns, select these, right-click and select Delete. How can you zoom in or zoom out in Excel?There are Zoom In and Zoom Out buttons in the status bar in Excel. You can click the plus sign to Zoom In and minus sign to Zoom Out. You can also hold the Control Key and then use the scroll wheel in the mouse to Zoom in and Zoom out. Also, in the View tab, there are option to Zoom in and out in Excel. How to protect a sheet In Excel?You can protect a sheet by clicking the Review tab and then clicking the Protect Sheet option. This opens a dialog box where you can set a password. If you don’t want to set a password, you can leave it blank. What are named ranges? What are its benefits?A named range is a feature in Excel that allows you to give a cell or a range of cells a name. Now you can use this name instead of using the cell references. Using a named range makes it easier when you’re working with formulas. This becomes specially useful when you have to create formulas that use data from multiple sheets. In such cases, you can create named ranges and then use these instead of the cell references. You can give descriptive names to these named ranges – which also makes it easier to read and understand the formula. For example, you can use =SUM(SALES) instead of =SUM(C2:C11), which will instantly tell you what the formula is about. You can read more about Named Ranges here. Can you make the header rows/columns stick and be visible when you scroll?When you’re working with large data sets, when you scroll to the bottom or to the right, the header rows and columns disappear. This sometimes makes it difficult to understand what a data point is about. By using Freeze Panes option in Excel, you can make the header rows/columns be visible when you scroll away to the far off data points. The Freeze Panes options are available in the View tab in the Excel ribbon. You can read more about Excel Freeze Panes here. How would you identify cells that have comments in it?The cells that have a comments added to it are flagged by a small red triangle at the top-right of the cell. When you hover the cursor over the cell, the comments becomes visible. How would you save an Excel worksheet as a PDF?To save a worksheet as a PDF document, you can specify the file type as PDF when saving the file. To do this, click on the File tab and then click on Save As. In the Save As dialog box, select the location where you want to save the file and use the Save As type drop-down to select PDF. This will save the entire worksheet as a PDF document. How to create a hyperlink in Excel?To create a hyperlink, select the cell in which you want the hyperlink and use the keyboard shortcut Control K (hold the control key and press the K key). This will open the Insert Hyperlink dialog box where you could specify the URL. You can also get the option to add the hyperlink when you right-click on the cell. When would you consider switching from automatic to manual calculation in Excel?While in most cases automatic calculation mode is the way to go, in case you have a formula heavy file where recalculation takes a lot of time every time you change anything in the sheet, then you can switch to manual calculation. Once you have switched to manual calculation, you need to refresh every time you want the sheet to recalculate. What is Flash Fill?Flash Fill is an amazing tool that was added in Excel 2013 and is available in all version after that. You can use Flash Fill in Excel to make data entry easy. It’s a smart tool that tries to identify patterns based on your data entry and does that for you. Some simple examples of using Flash Fill could be to get the first name from the full name, get name initials, format phone numbers, etc. You can read more about Flash Fill here. What is a Fill Handle?Fill handle is a tool that you can use to autocomplete lists in Excel. For example, if you have to enter numbers 1 to 20 in cell A1:A20, instead of manually entering each number, you can simply enter the first two numbers and use the fill handle to do the rest. Fill Handle is the small square you would see when you select two or more than two cells in Excel. When you hover your cursor on Fill handle, the cursor changes to a plus icon. Now you can hold the left-mouse key and drag it to add more numbers in the series. What are wildcard characters in Excel?There are three wildcard characters in Excel:
Wildcard characters are useful when you want to use it in formulas or while filtering data. You can read more about Wildcard Characters here. What is a Print Area and how can you set it in Excel?A print area is a range of cells (contiguous or non-contiguous) that you designate to print whenever you print that worksheet. For example, instead of printing the entire worksheet, if I only want to print the first 10 rows, I can set the first 10 rows as the print area. To set the Print Area in Excel:
You can read more about Print Area here. How can you insert Page Numbers in Excel?You can insert page numbers using the Page Setup dilaog box. Here are the steps to do this:
You can read more about Page Numbers in Excel here. While I have tried to keep this ‘Excel Interview Questions & Answer’ guide error-free, in case you find any errors in any of the questions, please let me know in the comments area. Related: Free Online Excel Training (7-part video course) You May Also Like the Following Excel Tutorials:
What is the power of using flash fill to set up the data the way you prefer?What is the power of using Flash Fill to set up the data the way you prefer? You can create a new column, enter the first name on the list the way you prefer, and then select Flash Fill so the column will populate. The Flash Fill feature allows content to be easily populated.
Why should you avoid using this chart type if you are printing it as part of a report?Why should you avoid using this chart type if you are printing this as part of a report? It will use a lot of ink from your printer.
What happens if you type P in cell A6?As can be seen in the above image, typing “P” into cell A6 shows Excel does not attempt to autocomplete the text because of the blank in cell A5. Another limitation to the autocomplete feature is that it only works on single column based data-entry so effectively it only looks up to find similar values, never sideways.
What does ## mean in Excel?Microsoft Excel might show ##### in cells when a column isn't wide enough to show all of the cell contents. Formulas that return dates and times as negative values can also show as #####.
|