Microsoft Excel Interview Questions & Answers

Question 1. What Is Microsoft Excel?

Answer :

Microsoft Excel is an electronic spreadsheet program, created by multiple highly skilled engineers from Microsoft. It enables users to organize, format, and calculate data with formulas using a spreadsheet system broken up by rows and column.

We also use this tool for storing, organizing and manipulating the data. In addition, it also offers programming that supports VBA, and we can use external database to make dynamic reports, analysis etc. Smart use of this program saves a lot of time and helps in creating our own applications too.

Question 2. What Is The Easiest Solution To Reduce The File Size?

Answer :

Below are the steps to reduce the file size:

• Find the last cell that contains data in the sheet. Delete all rows and columns after this cell
• To delete the rows, press the key Shift+Space then press Ctrl+Shift+Down on your keyboard
• Rows will get selected till the last row. Press Ctrl+- on the keyboard to delete the blank rows
• To delete the column, Press the key Ctrl+Space then press Ctrl+Shift+Right Arrow key on your keyboard
• Columns will get selected till the last row
• Press Ctrl+- on the keyboard to delete the blank columns

Question 3. What Is A Dashboard And What Are The Important Things We Should Keep In Mind While Creating A Dashboard?

Answer :

Dashboard is a technique used to present important information through graphical representation. It is helpful in presenting huge data in a single computer screen so it can be monitored with a glance.

There are few things which should be taken care of, while preparing the dashboards:

• Minimum distraction
• Simple, easy to communicate
• Important data
• Few Colors
• Relevant graphs
• Dashboard should be on single computer screen

Question 4. How Can You Format A Cell? What Are The Options?

Answer :

We can format a cell by using the “Format Cells” option and there are 6 options:-

1. Number
2. Alignment
3. Font
4. Border
5. Fill
6. Protection

Question 5. Is It Possible To Make Pivot Table Using Multiple Sources Of Data?

Answer :

If the multiple sources are different worksheets, from same workbook, then it is possible to make Pivot table using multiple sources of data.

Question 6. How Would You Provide A Dynamic Range In "data Source" Of Pivot Tables In Ms Excel?

Answer :

To provide a dynamic range in “Data Source” of Pivot tables, first create a named range using offset function and base the pivot table using a named range created in the first step.

Question 7. How Cell Reference Is Useful In The Calculation?

Answer :

In order to avoid writing the data again and again for calculating purpose, cell reference is used. When you write any formula, for specific function, you need to direct excel the specific location of that data. This location is referred as, cell reference. So, every time a new value added to the cell, the cell will calculate according to the reference cell formula.

Question 8. How You Can Resize The Column?

Answer :

To resize the column you have to change the width of one column and then drag the boundary on the right side of the column heading till the width you want. The other way of doing it is to select the Format from the home tab, and in Format you have to select AUTOFIT COLUMN WIDTH under cell section. On clicking on this the cell size will get formatted.

Question 9. How You Can Add A New Excel Worksheet?

Answer :

To add a new Excel worksheet you have to insert worksheet tab at the bottom of the screen.

Question 10. What Does The If Function In Excel?

Answer :

IF function is used in Excel to check whether certain conditions are true or false. If the condition is true then it will give the result accordingly and if the condition is false the result or output will be different.

Question 11. How Do I Run A Macro Every Time A Certain Cell Changes Its Value?

Answer :

There is an event called Worksheet Change which is triggered when a value is entered (it will not fire when a formula result changes). One of the arguments to this event is 'Target' which is a reference to what changed.

Since this event will occur whenever a value changes -

you can use the target to see if it is the cell you are interested in:

Private Sub Worksheet Change (ByVal Target as Range)

If Intersect (Target, Range ("C5")) Is Nothing Then

Exit Sub

Else

'The cell you are monitoring has changed!

'Do whatever you need to do...

End If

End Sub

Question 12. I Want To Add A Toolbar To My Spreadsheet That When Clicked, Brings Up Userform1?

Answer :

Assign the toolbar button to this macro, which should be in a standard VBA module:

• Sub Show Form ()
• Userform1.Show
• End Sub

Question 13. Is There A Way To Protect The Macros I Create So People Cannot See Or Alter Them?

Answer :

Go to Tools > VBAProject properties, lock the project for viewing, and enter a password.

Question 14. Is It Possible To Call A Macro From The Condition True Or False Side Of A Worksheet Formula? I.e. If(a2="ok",run Macro1,run Macro2)?

Answer :

Basically, the answer is No. You can write functions in VBA that you can call from worksheet cells, but these functions can only return a value. They can't modify other cells or alter any part of the Excel environment. (You may be able to use a worksheet change event to call the macro.)

Question 15. How Can I Unprotect A Vba Project Using Code?

Answer :

You cannot. A workaround is to simulate keystrokes with the Send Keys method.

Question 16. Is It Possible To Change The Color And Font Of The Sheet Tabs?

Answer :

Yes we can change the color of sheet tabs. By right clicking on sheet tabs and you will get option change color but i didn't find any option to change the font of sheet tabs.

Question 17. How To Prevent Someone From Copying The Cell From Your Worksheet?

Answer :

If you want to protect your worksheet from being copied, go into Menu bar > Review > Protect sheet > Password.

By entering password you can prevent your worksheet from getting copied.

Question 18. How You Can Sum Up The Rows And Column Number Quickly In The Excel Sheet?

Answer :

By using SUM function you can get the total sum of the rows and columns, in an excel worksheet.

Question 19. Is There A Way To Apply The Same Formatting To Every Sheet In A Workbook In Excel?

Answer :

Yes. To do this, you will need to right click on one of the worksheet tabs and then choose Select All Sheets. After you do this any formatting that you apply or text you enter will show up on all the sheets in your workbook. In order to eliminate certain sheets from the changes, hold down the Ctrl key and click on the tab of the worksheet you want excluded from the others. You can also group sheets by holding the shift key and selecting the worksheet tab.

Question 20. I Have Converted My Ms Access Database To A Current Conversion. I Am Getting Conversion Errors?

Answer :

Design specs have changed in new releases of MS Access. Visit the on-line help option for "conversion and compatibility" that explains different portions of Access databases that will have difficulty converting. Some reprogramming may be required.

Question 21. What Filter Should We Use, If You Want More Than Two Conditions Or If You Want To Analyze The List Using Database Function?

Answer :

You should use "Advanced Criteria Filter" to analyze the list or test more than two conditions.

Question 22. What Does A Red Triangle At The Top Right Of A Cell Indicates?

Answer :

A red triangle in the cell indicates the comment associated with the cell. If you place mouse on it, it will show the comment.

Question 23. Using Excel Is There A Way To Close All Open Excel Files At Once Instead Of Closing Them One At A Time?

Answer :

Yes, you can close down all your Excel files at once by using the following instructions: 

1. Hold down the Shift key. 
2. Choose File + Close All from the menu. Holding down the Shift key changes Excel's File + Close command to a File + Close all command.

Question 24. How Do I Combine Different Chart Types Into My Excel Spreadsheet?

Answer :

To combine chart types, follow these steps: 

1. Of the Chart toolbar isn't already displayed, right-click any Toolbar and select Chart. 
2. On the chart, click the series you want to change. 
3. On the Chart toolbar, click the arrow next to the Chart Type button and then select the new chart type for the series (in our example, a line chart).

Question 25. How Do I Resize Columns And Rows To Better Fit The Data In Ms Excel?

Answer :

1. Move the mouse in between any two labels (Rows/numbers or Columns/letters).
2. The pointer will turn into a vertical (letters) or horizontal (numbers) line with arrows on both ends. 
3. Simply drag the column or row to the desired size.

Question 26. Does Each Cell Have Unique Address?

Answer :

Yes, each cell has a unique address depends on the row and column value of the cell.

Question 27. How Can I Hide Or Show The Ribbon?

Answer :

By pressing the CTRL key and pressing the F1 key to toggle you can see and hide the ribbon.

Question 28. What Are The Advantages Of Using Formula In Excel Sheet?

Answer :

Formula makes it easy to calculate the numbers in Excel sheet. It also calculates automatically the number replaced by another number or digit. It is used to make complex calculations easy.

Question 29. What Is Pivot Table And Why We Use It?

Answer :

Pivot table allows quick summarizing of large data. We can calculate the field and arrange the data in presentable way in just few minutes. Most of the Excel experts believe that Pivot table is the most powerful tool.

Question 30. What Is The Use Of Vlookup And How Do We Use It?

Answer :

Vlookup is used to find the data in the large spreadsheet by lookup value in another worksheet. To use the Vlookup function, we should have common values in both data. For example, we want to search the phone number of a person. So, in order to find out the phone number, we will need the concerned persons name.

Question 31. What Filter Will You Use, If You Want More Than Two Conditions Or If You Want To Analyse The List Using Database Function?

Answer :

You will use Advanced Criteria Filter, to analyse the list or if more than two conditions should be tested.

Question 32. What Are Cells?

Answer :

The area where data is stored is known as cell.

Question 33. What Are Dashboard And Why Do Organisations Use It?

Answer :

Dashboards often provide at-a-glance views of KPIs (key performance indicators) relevant to a particular objective or business process (e.g. sales, marketing, human resources, or production).The term dashboard originates from the automobile dashboard where drivers monitor the major functions at a glance via the instrument cluster.

Dashboards give signs about a business letting the user know something is wrong or something is right.Dashboards typically are limited to show summaries, key trends,comparisons, and exceptions.

There are four Key elements to a good dashboard which help organisations to work effectively:

*Simple, communicates easily
*Minimum distractions.
*Supports organized business with meaning and useful data
*Applies human visual perception to visual presentation of information

Smart organizations are doing everything they can to find every opportunity to drive their business and helpthem stay ahead of their competition. To uncover these opportunities ,decision-makers need easy access to a very wide range of information and they need advanced visualizations to help them analyze and understand the data.One way decision-maker get easy access to data is through dashboards. Modern dashboard technology enables dozens of reports to be combined into a single, easily-consumable, visual representation.

This ability to consolidate many reports has been the biggest breakthrough in the last five years and has redefined what we think of as a “dashboard".Dashboards are highly interactive, self-contained environments that hold the answers to many standard questions of business operations.

Question 34. Do You Use Vba To Automate Your Work In Excel.if Yes, How Often Do You Use It?

Answer :

You may say that yes, you use Visual Basic for Applications(VBA) to automate your tasks in Excel by writing so called macros.And then share the process of using VBA with your interviewer smartly that you use VBA by step by step i.e

• STEP-1 Enabling VBA(To enable VBA you have to use the File, Options and select Customise the Ribbon.You then have tick the Developer box to allow the Developer icon to appear).
• STEP-2 Insert a module(To insert a module you use the command Insert,Macro,Module).
• STEP-3 Entering text.
• STEP-4 Each module you create is given a default name(Module1, Module2 and so on - but you can rename it quite easily by typing a new name into the Properties box at the bottom left)
• STEP-5 The beginning and the end(All programs in VBA have to start and end in a particular way. The first line has to read Sub name() where name is the name you want to give your program.Sub is short for Subroutine but more of this later).
• STEP-6 Getting the message.
• STEP-7 Running the program(To run the program you can simply click on the green “play” arrow in the Visual Basic toolbar).
• STEP-8 Input(to find out how to get information back from the user - i.e. let them input something.This makes use of another VBA command, InputBox, but pay attention because it is a little more tricky than MsgBox).

Question 35. What Is The Most Unusual Thing You Have Used Excel For?

Answer :

Here you need to mention those things which helped you for exploration and learning new things.You may some points in your answer like :

• I used excel to create stunning screen mockups and wowed my clients.
• I have used excel to generate and clean a list of tags for the blog i write(if).
• I wrote a small macro to send updates to twitter.

Question 36. Can You Tell Me About Some Of The Newer Enhancements To Excel Recently?

Answer :

Some of the new enhancements you can mentions in your answer are as follows are:

• Powerpivot
• Tables
• Slicers
• Sparklines
• IFERROR

With the names, you also need to add some details of particular enhancements.

Question 37. Is It Possible To Display The Text In The Data Area Of Pivot Table?

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.

Question 38. How Do You Make Two Data Fields Appear Side-by-side, Instead Of In Separate Rows?

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.

• Change pivot table data to horizontal layout.And to keep related data from spreading horizontally off of the screen and to help minimize scrolling,click Show in Compact Form.
• In compact form, fields are contained in one column and indented to show the nested column relationship.
• To outline the data in the classic PivotTable style, click Show in Outline Form.
• To see all data in a traditional table format and to easily copy cells to another worksheet, click Show in Tabular Form.

Question 39. How To Debug A Vba Code?

Answer :

To start to debug a Visual Studio project, attach to a process, break into code, step through code, run to the cursor, run to a function on the call stack, set the next statement,step through Just My Code, stop debugging, restart debugging, or detach from a debugged process.

• Specify the configurations for the debug and release versions of a program.
• Set start options (command-line arguments, working directory, remote machine).
• Debug at design time.
• Enable just-in-time debugging, which launches the Visual Studio debugger when a program running outside Visual Studio encounters a fatal error.
• Set breakpoints for source lines, assembly instructions, and call stack function. Specify conditions, hit counts, and execution location.

Question 40. How Can You Increase The Number Of Rows And Columns In A Worksheet?

Answer :

The amount of rows in a worksheet is fixed i.e 1,048,576 , so you cannot increase the amount of rows. 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(16384) it's not possible to increase,you can start a new Excel workbook and transfer your data.

Question 41. What Is The Ribbon And When Would You Use It?

Answer :

The Ribbon is the strip of buttons and icons located above the work area beginning with Excel 2007.The Ribbon replaces the menus and toolbars found in earlier versions of Excel.Above the Ribbon are a number of tabs, such as Home, Insert, and Page Layout. Clicking on a tab displays the options located in this section of the ribbon.

Question 42. How Can You Hide Or Show The Ribbon?

Answer :

To Hide and show the ribbon you can press the CTRL+F1 key or Double click on one of the ribbon tabs-such as Home, Insert, or Page Layout.

Question 43. Why Does Excel Have Two Macro Languages?

Answer :

Early version of Excel used a macro language called XLM.The VBA language was introduced in Excel 5 and its vastly superior in every way.XLM has been phased out , so you should use VBA for new macro development.

Question 44. What Types Of Workbook Protection Are Available?

Answer :

Excel provides three ways to protect a workbook:

• Require a password to open the workbook.
• Prevent users from adding sheets, deleting sheets, hiding sheets, and unhidingsheets.
• Prevent users from changing the size or position of windows.

To protect protect a workbook you can activate the workbook to be protected, then choose Tools - Protection - Protect Sheet. You will be asked to provide a password (optional).If you do provide a password, that password will be required to unprotect the workbook.

Question 45. What Are Worksheets?

Answer :

A worksheet is a collection of cells where you keep and manipulate the data. A worksheet begins with row number one and column A.Each cell can contain a number, text or formula. A cell can also reference another cell in the same worksheet, the same workbook or a different workbook. By default, each Excel workbook contains three Worksheets.When you open Excel, Excel automatically selects Sheet1 for you.The name of the worksheet appears on its sheet tab at the bottom of the document window.To select one of the other two worksheets,simply click on the sheet tab of Sheet2 or Sheet3.

Question 46. How Can You Add, Rename Or Remove Worksheets?

Answer :

To add a worksheet: Touch + next to the last worksheet tab
To remove a worksheet: Touch the sheet tab, then touch x to delete the worksheet
To rename a worksheet: Touch twice on the name of a worksheet

Question 47. How Can You Resize The Column?

Answer :

There are two ways to resize a column:

1. To change the width of one column, drag the boundary on the right side of the column until you find your desirable width.
2. Select the Format from the home tab, and in Format, select the AUTOFIT COLUMN WIDTH under cell section. Click on this to change the cell size.

Question 48. What Is A Macro In Excel?

Answer :

Excel Macro is the set of instructions that is recorded by users for repetition purposes. It is created by the users for repetitive instructions and functions they perform on a regular basis.

Question 49. How Can You Resize The Column And Rows?

Answer :

The width of columns and height of rows can be adjusted to better fit your data in a few different ways:

1. 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.
2. 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.
3. 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.

Question 50. What Is The Quick Way To Return To A Specific Area Of Spreadsheet?

Answer :

By typing in Name Box you can return to a specific area of spreadsheet in quick way.

Question 51. Tell Me About The Benefit Of Using Formula In Excel?

Answer :

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.

Question 52. How To Protect The Macros You Create So People Cannot See Or Alter Them?

Answer :

To protect the Macros: In the Visual Basic Editor, go to Tools > VBAProject properties, lock the project for viewing, and enter a password.