Below are some common formatting Excel interview questions (click on the question to view the answer 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. + Microsoft Excel Interview Questions and Answers, Question1: What is Microsoft Excel? Question2: What is the easiest solution to reduce the file size?.

Excel Interview Questions And Answers Pdf

Language:English, German, Portuguese
Published (Last):27.07.2016
ePub File Size:23.37 MB
PDF File Size:16.17 MB
Distribution:Free* [*Register to download]
Uploaded by: MINDI

going for the interview, prepare yourself for those questions that are Answer: Microsoft Excel is an electronic spreadsheet program, created by multiple highly. 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. MS Excel Interview Questions And Answers in threadergrenacmu.ml for more MS Excel Interview Questions And Answers to download in pdf format.

ETC 13 How can you increase the number of rows and columns in a worksheet? The amount of rows in a worksheet is fixed i. What you can do is insert rows into part of a spreadsheet. It will have the effect of pushing rows down, but it will not increase the amount of rows in the worksheet. And same with columns they are also fixed its not possible to increase ,you can start a new Excel workbook and transfer your data.

Excel Dependency Tree is an internal structure which is used to build calculation change and informs Excel which cells should be recalculated when something is changed in the spreadsheet. Dependency Tree is not shown by Excel, you can one use Formula Auditing commands to work with it. Show Precedents and Show Dependents commands point you to list of dependent cells in the spreadsheet.

Dependency Auditor adds its own set of formula auditing commands to work with Dependency Tree and research the calculations. It provides you set of very helpful graphical and navigation tools to go through dependency list. The Ribbon is the strip of buttons and icons located above the work area beginning with Excel The Ribbon replaces the menus and toolbars found in earlier versions of Excel. Clicking on a tab displays the options located in this section of the ribbon.

The IF statement is a simple function in Excel that is one of the building blocks you need when you are working with large spreadsheets. You may not know you need it yet, but once you know how to use it, you won't want to live without it.

It then outputs a result based on the outcome of that test. Early version of Execl used a macro language called XLM. The VBA language was introduced in Excel 5 and its vastly superior in every way. Excel provides three ways to protect a workbook: Require a password to open the workbook.

Thew red triangle at the top of right of a cell indicates that: There is an error in the cell There is a comment associated with the cell The font color of the text in cell is red The cell can't accet formula 25 How can you prevent someone from copying your worksheets? By entering password,you can secure your worksheet from getting copied by others. The SUM function provides a quick and easy way to carry out this task in an Excel worksheet.

The syntax for the SUM function is: Number 28 What is Macro in Excel? A macro can be defined as the recording of a series of tasks. When used right, macros can save you hours by automating simple, repetitive tasks. This is a version of Visual Basic a prominent Microsoft programming language developed specifically for use in Office-like applications.

To create Excel Macro: Step-1 Turn on the Developer tab. Step-2 Right click anywhere on the ribbon, and then click Customize the Ribbon. Step-3 Under Customize the Ribbon, on the right side of the dialog box, select Main tabs if necessary. Step-4 Check the Developer check box.

Click OK. Step-7 In the code window of the module, type or copy the macro code that you want to use. Step-8 To run the macro from the module window, press F5. The width of columns and height of rows can be adjusted to better fit your data in a few different ways.

Use the format button- The Format button in the cells group under the Home tab can also be used to adjust widths and heights. Simply select the cells that need to be adjusted,then click on the Format button and choose either Row Height or Column Width. Type in your desired number, and the cells will adjust. Drag the border- Hover your arrow over the border between the column or row labels.

Then click and drag the border to widen or narrow the size of each cell. The columns to theright or rows below will adjust to make room for your changes. You can make several columns or rows the same width or height. Select your desired columns or rows by clicking on the labels while holding down either the shift key or control key. Then use any of the methods described above, and all of the columns will be made the same width or rows the same height.

By typing in Name Box you can return to a specific area of spreadsheet in quick way. Formulas in spreadsheet programs such as Excel are used to perform calculations on values entered and stored in the program. Excel's main function is to interlink cells, through the use of formulas, so that the formula cell changes when data is inputted into linked cells.

The best way to store information in spreadsheets is through the use of formulas.

Formulas can range from basic mathematical operations-such as addition and subtraction-to complex engineering and statistical calculations. To format data in Excel you van: Use column labels to identify data Use cell borders to distinguish data Extend data formats and formulas Avoid leading or trailing spaces to avoid errors Use an Excel table format to work with related data 34 What is the function of sumif in Microsoft excel?

You would use it when you only want to total some values from a list. So say you had values in A1 to A15 and you only wanted to total up the ones that were over , you could use a formula like this: Then say you want to add values that are beside only one particular name that occurs a few times in the list and that name was John.

You could do that with this formula: Excel offers a wide range of chart types: You can even mix different types on a single chart by assigning different chart types to different series on the chart. These mixtures are called Combination Charts, and Excel provides a small number of these on the Custom Types tab of the Chart Type dialog box. To combine different charts: Step-2 Select the data that you want included in the chart.

Choose the column chart type that you want to use. Step-3 Next, right-click on the column representing the data that you want to convert to a line. Step-4 In the Change Chart Type window that appears, select what type of chart you want to use for this variable. Click OK to continue. To change colour of the worksheets: Step-1 Right-click on the worksheet tab you want to change. Excel displays a Context menu. Step-2 Choose Tab Color from the Context menu.

Excel displays the Format Tab Color dialog box. Step-3 Select a color to use for the tab. Step-4 Click on OK. To change the font: Step-2 Select Properties, and then select the Appearance tab. Step-3 Click Advanced skip this step if using Windows Step-4 From the Item dropdown list, choose Scrollbar. Step-5 Change the Size and click OK twice.

To protect the Macros: To lock the cells: Step-1 Select all cells. Step-2 Right click, and then click Format Cells. Step-4 Right click cell you want to lock , and then click Format Cells. Step-5 On the Protection tab, check the Locked check box and click Ok. With the Internet Assistant Wizard,you can leverage existing Microsoft Excel spreadsheet data to create and distribute documents that can be viewed online with popular browsers.

By clicking a series of buttons in the wizard, you can convert spreadsheet data to HTML format either as a separate Web document or into an existing Web document. Array formulas may be Excel's most powerful feature for summarizing data. You can use array formulas to do complex tasks, such as: Count the number of characters that are contained in a range of cells.

Sum only numbers that meet certain conditions, such as the lowest values in a range or numbers that fall between an upper and lower boundary. Sum every nth value in a range of values. Counting Values Based on Multiple Criteria. In simple words the Replace function replaces part of a text string, based on the number of characters you specify, with a different text string.

The Substitute function substitutes new text for old text in a text string. You use Substitute when you want to replace specific text in a text string and you use Replace when you want to replace any text that occurs in a specific location in a text string. The COUNT function is generally used to count a range of cells containing numbers or dates excluding blanks. COUNTA, on the other hand will count everything numbers, dates, text or a range containing a mixture of these items, but does not count blank cells.

Although you can use COUNTA to generate the number of clients or employees in a list, it counts each entry, not unique entries. Microsoft has developed an interactive referencing guide to help you find your favorite Excel or Excel commands in Excel With this guide, you can point your mouse to an Excel or Excel menu or button and see where that feature is now located. To protect formulas: Step-2 And a Format Cells dialog box will pop out.

Click Protection, and unchecked the Locked option. The whole worksheet has been unlocked.

Check Formulas from Select option, and then click OK. 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.

Microsoft Excel Interview Questions & Answers

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. Click OK. How can you make text invisible in Excel? There are multiple ways to do this: Excel Formula Questions Below 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: What is the difference between a function and a formula in Excel? What according to you are the top 5 functions in Excel? What is the difference between absolute and relative cell references? 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: This is called the division error. Erro r: The value error occurs when you use an incorrect data type in a formula. This is called the reference error and you will see this when the reference in the formula is no longer valid.

This error is likely to a result of a misspelled function. Number error can occur if you try and calculate a very large value in Excel. How can you tackle errors when working with Excel Formulas? There are various ways you can tackle the errors in Excel: You can highlight the errors using conditional formatting.

Which function would you use to get the current date and time in Excel? The following functions can be used: NOW - This function takes no argument and would return the current date and time value.

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: Click here to read how it works.

What formula would you use to find the length of a text string in a cell? It could be a value, a cell reference, or a text string. In the score sheet example, this would be your name. This could be a reference to a range of cells or a named range.

If you want the scores for Physics, you'd look in column 2. If omitted, it defaults to TRUE — approximate match. For example, if you have a text string in cell A1 and you want to remove the spaces, you can use the following formula: If you have some text in cell A1 from which you want to remove spaces, use the below formula: It can not be used when the lookup value is on the right. Now this limitation can be overcome by using it with other formulas, it tends to make formulas complex.

When used on large data sets, it can make your workbook slow. What are volatile functions? Can you name a few? Here is a list of volatile formulas: Highly volatile: Excel Keyboard Shortcuts Below 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? This opens the delete dialog box where you can choose what to delete. 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? What is the shortcut to open a new Excel workbook? How can you select all the cells in the worksheet. How would you insert a new line in the same cell?

What's the shortcut to insert a comment in Excel? Excel Pivot Table Questions Below 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? What are the various sections in a Pivot Table? A Pivot table is made up of four different sections: Values Area: This is the area where the values are reported.

Rows Area: The headings to the left of the Values area makes the Rows area. Columns Area: The headings at the top of the Values area makes the Columns area.

Filter Area: It is an optional filter that you can use to further drill down in the data set. What are slicers? What is a Pivot Chart. This chart is called the Pivot Chart. How can you refresh a Pivot Table? Can you group dates in Pivot Tables?

If you have date wise records, you can easily group these into the following segments: What is a Pivot Cache? Can you make a Pivot Table from multiple tables? What are Calculated Fields in Pivot Table? 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.

You have a couple of options: You can go back to the original data set and add this new column. This is feasible but not the best solution as it alters the original data set and increases file size. You can do this calculation outside of the Pivot Table. This is alright if your Pivot Table is static, but if you update the table or change the layout, you'll need to do the calculations again.

You add a calculated field. There are a lot of benefits of using a Pivot Table Calculated Field: It doesn't require you to handle formulas or update source data. It's scalable as it will automatically account for any new data that you may add to your Pivot Table. Once you add a Calculate Field, you can use it like any other field in your Pivot Table.

It easy to update and manage.

Microsoft Excel Related Tutorials

For example, if the metrics change or you need to change the calculation, you can easily do that from the Pivot Table itself. Excel Charting Questions Below 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? What is a Bar chart? What is a Line chart? What is a Scatter chart?You can use array formulas to do complex tasks, such as: Data validation options are available in the data tab.

The arguments of the function describe what ingredients go into it. Nevertheless, Excel still reigns supreme. Making a great Resume:

SHERLY from West Valley City
Also read my other articles. One of my hobbies is bell ringing. I do relish studying docunments surprisingly.