Microsoft Excel Interview Questions And Answers

In the date field properties of the table, form, or properties manually input the format you would like. It is not required to choose one of the date formats in the drop down box. Example would be mm/dd/yyyy.

1. Click Tools
2. Scroll down to Protection, then Protect Sheet
3. Enter a password, Click OK
4. Re-enter password,
Click OK

1. Row - Select the row below where you want the split to appear
2. Column - Select the column to the right of where you want the split to appear
3. Go to the Menu Bar
4. Click Windows
and then click Freeze Panes

1. Must Always highlight the data before formatting
2. Click Format
3. Then go to Cells

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

To combine chart types, follow these steps:
1. If 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).

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.

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.

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.

Option A:
1. Choose Edit + Go To (or press Ctrl + F5).
2. Select Special.
3. Select Formulas.
4. Click OK.

Option B:
1. Choose Tools + Options.
2. Select the View Tab
3. In Window Options choose the check box 'Formulas'.
4. Click OK

The trick is to change the way Excel displays the worksheet before you choose to print. Check the box Tools, Options, View, Formulas and you'll see the formulas appear in each cell (with the columns changed to fit). When you print the sheet the formulas will be printed instead of the values

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.

If ActiveSheet.UsedRange.Count < 2 Then
MsgBox 1
Else
MsgBox Columns("A:A").Find(What:="", LookAt:=xlWhole).Row
End If

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

Worksheets("MySheet").Unprotect password:="drowssap"
'your code here
Worksheets("MySheet").Protect password:="drowssap"

Be sure to protect your macro code to hide the sheet password.

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

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

Combine the two solutions above:

Private Sub Workbook_Open()
UserForm1.Show
End Sub

or

Sub Auto_open()
UserForm1.Show
End Sub

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

Sub ShowForm ()
Userform1.Show
End Sub

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

There are buttons from the Forms toolbar and there are buttons from the Control Toolbox. If "Assign Macro" is not an option then it's from the Control Toolbox.

Include all the cells that your UDF depends on in the argument list. Or enter this as the first statement in your Function:

Application.Volatile

This will cause the function to be executed whenever a calculation occurs in the workbook

To reactivate the Stop Recording toolbar:

1. Choose Tools | Macro | Record New Macro
2. Click OK
3. Choose View | Toolbars | Stop Recording
4. Click the Stop Recording button (the blue square)
The next time you record a macro, the toolbar should automatically appear.

Sub AskAndDo()
If MsgBox("Are you sure ?", vbYesNo + vbQuestion) = vbNo _
Then Exit Sub
'Code goes here instead of
MsgBox "Actions here"
End Sub

Application.DisplayAlerts = False
'code to save, overwrite, delete, whatever goes here
Application.DisplayAlerts = True