+ Microsoft Excel Interview Questions and Answers, Question1: What is Microsoft Excel? Question2: What is the easiest solution to reduce the file size?. Answered Aug 26, #Tips, tricks for the question: What are the advanced Excel interview questions. Below are top 10 hack tips for your job interview. So, we've provided our list of the 10 most common Excel interview questions so that you can get some practice in before you exam. With a little bit of advance.
|Language:||English, Spanish, Dutch|
|ePub File Size:||16.77 MB|
|PDF File Size:||19.42 MB|
|Distribution:||Free* [*Sign up for free]|
Excel questions in the blue box are of an advanced level. Questions. Below are some common formatting Excel interview questions (click on the question to view the answer to it). How would you save an Excel worksheet as a PDF?. Top 25 Microsoft Excel Interview Questions & Answers. 1) What is Microsoft Excel ? Microsoft excel is an electronic worksheet developed by Microsoft, to be used. going for the interview, prepare yourself for those questions that are Answer: Microsoft Excel is an electronic spreadsheet program, created by multiple highly.
Question Answer : No its not possible to dislplay the text in data area of pivot table. You could display the text fields in the Row and Column areas, and show a count of the records in the data area or we can combine Custom Number Formats with the Pivot Table to produce Text based answers. Answer : After adding the two fields to the pivot table's data area, drag the Data field button to the right, onto the cell that contains the word 'Total'. There's a picture here that shows the 'Total' cell.
First Name. In your opinion, what are a few of the most useful functions in Excel? How do you use them? Here are a couple of our top recommendations for features, formulas, and functions to discuss: As veteran Excel users know, they're used to look up values from an external table, and are important parts of automating your work with dynamic spreadsheets.
It's a slightly more useful function, and also lacks many of the disadvantages of VLOOKUP , like the inability to insert new rows and columns into your sheets. If you go with this function, outline how you've used it in the past to dynamically lookup values and populate columns of data that would otherwise need to be manually copied and pasted.
IF statements. IF statements are another staple of any Excel veteran's arsenal. Bring them up to let your interviewer know that you've created advanced spreadsheets that make decisions based on criteria calculated in real-time. PivotTables are an extraordinarily useful tool, and if you're applying for a job that requires intermediate or advanced Excel knowledge, they're sure to be an important criteria used by your interviewer.
You may have used PivotTables for any number of things in the past, but be sure to emphasize how useful they are when quick, accurate calculations are necessary based on large sets of data with hundreds or thousands of rows. A1 Nothing locked Both row and column will change as cell is copied and pasted. Each PivotTable is composed of a number of key sections, into which the columns of a target data set can be bucketed: Report filter.
This section allows us to filter our table by one or more criteria. For example, we can only show data in our Pivot Table for the month of January. Column labels. This section allows us to summarize data across columns, placing data labels along the top of the screen. Row labels. This section allows us to summarize data across rows, placing data labels along the side of the screen.
This section allows us to specify what we're summarizing — for example, total sales or number of items ordered. Do PivotTables have any drawbacks? How can they be solved? If asked about the drawbacks of PivotTables, consider the following: Input data needs to be formatted properly. PivotTables can only be used in specific situations in which the input data set appears in flat file format — meaning that it's broken down to it's most granular level.
If data is already summarized on a table, PivotTables may not be the best way to analyze it. PivotTables need to be refreshed if input data changes.
This can lead to confusing and errors when using PivotTables as part of larger dashboards. PivotTables are easily modified, so it can be difficult to recreate your calculations.
There are many times during which you'll arrive at an answer using PivotTables, then have a difficult time recreating that answer if a supervisor asks to see your work in more detail. The flexibility of PivotTables can be a double-edged sword! What are some best practices when creating complex models in Excel? When talking through your answer, be sure to mention the following modeling best practice, which help keep your spreadsheets clean, organized, and flexible: Create multiple tabs.
Keeping different pieces of your model for example, inputs, outputs, and calculations on separate tabs can help with model organization, particularly if you're planning to hand your spreadsheet off to someone who has never seen it before.
Use dynamic inputs. When constructing a model in Excel, values should never be hard-coded into cells — especially if they are flexible assumptions that may change down the line.
Always keep assumptions and inputs on their own tab, and use cell references rather than hard-coded values to pull them into your formulas. Add a table of contents. Large models can be extraordinarily complex, and adding a table of contents to the beginning can help keep things organized and easy to use for yourself and your supervisor.
PivotTables are easily modified, so it can be difficult to recreate your calculations. There are many times during which you'll arrive at an answer using PivotTables, then have a difficult time recreating that answer if a supervisor asks to see your work in more detail. The flexibility of PivotTables can be a double-edged sword!
They can produce similar results, but are less 'fluid' — making your results more predictable and easier to track. What are some best practices when creating complex models in Excel? Excel can be used for simple calculations, but it's most effective when constructing complex mathematical models that help predict outcomes, project financial results, or track data over time.
If you're interviewing for a highly analytical role, there's a good chance your recruiter will ask about how you can use spreadsheets to accomplish these more difficult tasks. When talking through your answer, be sure to mention the following modeling best practice, which help keep your spreadsheets clean, organized, and flexible: Create multiple tabs.
Keeping different pieces of your model for example, inputs, outputs, and calculations on separate tabs can help with model organization, particularly if you're planning to hand your spreadsheet off to someone who has never seen it before. Use dynamic inputs. When constructing a model in Excel, values should never be hard-coded into cells — especially if they are flexible assumptions that may change down the line. Always keep assumptions and inputs on their own tab, and use cell references rather than hard-coded values to pull them into your formulas.
Add a table of contents. Large models can be extraordinarily complex, and adding a table of contents to the beginning can help keep things organized and easy to use for yourself and your supervisor. Comment aggressively. You can see an example of scatter chart in action in the KPI Dashboard here. 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. 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. 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. 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.
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. 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. 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. Below 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.
If you're interested in learning more about Find and Replace, click here. 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 can read more about data sorting here. 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. 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. 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. 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. You can read more about selecting blank cells in Excel here. You can read more about removing duplicates in Excel here. 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. You can read more about Excel Advanced Filter here. With multiple sorting, the idea is to sort a column and then sort the other column while keeping the first column intact. Note that Column A is sorted first in this case and then Column B is sorted.
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.
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.
You can read more about One-variable data table here. 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. 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. You can read more about Two-variable data table here. 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:. 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. Goal Seek in Excel, as the name suggests, helps you in achieving a value the goal by altering a dependent value.
You can read more about Goal Seek here. 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. Below are some common Excel interview questions about VBA that you might get asked in an interview click on the question to view the answer. It's the programming language that you can use to automate tasks 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:. 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. 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. While recording a macro is a great way to quickly generate code, it has the following limitations:. 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.
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 running a macro here. 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.
Below are some common Excel interview questions about dashboards that you might get asked in an interview click on the question to view the answer. A report is meant to provide relevant data. 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. 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.
Apart from these regular interactive tools, you can also use VBA to add more functionality to your 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. These 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.
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. When you convert tabular data into Excel Table, the following features are automatically added to the data:. 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. There are a lot of file formats in which you can save your Excel workbook. Some commonly used ones are:. Read more: You can read more how to handle slow Excel workbook here.
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. 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. 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. 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.
C11 , which will instantly tell you what the formula is about. You can read more about Named Ranges here. 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. You can read more about Excel Freeze Panes here. 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. This will save the entire worksheet as a PDF document.
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. 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.
Flash Fill is an amazing tool that was added in Excel 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. 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. You can read more about Wildcard Characters here. 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.
You can read more about Print Area here. You can read more about Page Numbers in Excel here. Free Online Excel Training 7-part video course.
I have multiple pivot tables in one big workbook. But whenever i save as file with different name and try to update the pivot table, it is still keeps referring to old file name. It is painful for me to change data source everytime. How to fix this? Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.
Excel Interview Questions Topics. Quick Navigation Excel Formatting Questions. Excel Data Analysis Questions. Excel Formatting Questions Below 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: A text string can also include punctuation and symbols. Number Format - There are different formats even within numbers.
For example you can have decimals, fractions, have thousand separators, etc. No matter what format has been applied, you can use numbers in calculations such as addition, subtraction, division, and multiplication. Date Format - The most important thing to know about dates is that these are stored as numbers in the Excel.
However, you can format it to be shown as dates. For example, would be saved as in Excel, which is the number for the given date. In Excel, you can show dates in different formats such as long date 01 January , short date , etc. For example, you can make 0. How can you wrap text in Excel? How can you merge cells in Excel? What is 'Format Painter' used for?
How would you clear all the formatting without removing the cell contents? What is conditional formatting? How would you highlight cells with negative values in it? You can do this using conditional formatting. Here are the steps: Select the cells in which you want to highlight the cells with negative value.
Go to the Home tab and click on Conditional Formatting option. In the 'Less Than' dialogue box, specify the value as 0 and the formatting. How would you highlight cells with duplicate values in it?
Select the data in which you want to highlight duplicate cells. How would you highlight cells with errors in it? Here are the steps to highlight cells with errors: Select the data in which you want to highlight the cells with errors. Click on 'New Rule'. In the New Formatting Rule dialog box, select 'Use a formula to determine which cells to format' option. Click on the Format button and specify the color in which you want to highlight the cells.