Top 50 Microsoft Excel Interview Questions You Must Prepare 19.Mar.2024

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.

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.

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

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.

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

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.

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 wers.

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

  • Powerpivot
  • Tables
  • Slicers
  • Sparklines
  • IFERROR

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

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

Here you need to mention those things which helped you for exploration and learning new things.You may some points in your wer 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.

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

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

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

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.

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.

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.

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.

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.

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

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

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.

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

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 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

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.

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

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.

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.

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 wers to many standard questions of business operations.

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

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.

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.

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.

  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.

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

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.

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

The area where data is stored is known as cell.

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.

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

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.

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

Basically, the wer 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.)

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.

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).

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

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 trfer your data.

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.

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.

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.

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