Top Microsoft Excel Interview Preparation Guide

Download PDF

Generally when we enter long data to a particular cell if it is in accounting format then it shows #####

In Microsoft Excel 2003 it is possible to color code the tabs of worksheets. Changing the color of worksheets tabs containing related information can make it easier to navigate through very . Or to use tab colors to differentiate between sheets containing unrelated information.

The default setting for Excel toolbars is to display them on a single row, thereby hiding many of the toolbar buttons. It is quite easy to change the default settings to show Excel toolbars on two rows, making for a easier time formatting your spreadsheet.

* Some of the Business functions (BAPI's / RFC unctions) offer data upload
* The general tool in SAP for data upload is the Batch input / call transactions

In the batch input file you specify screen by screen and field by field the input to SAP.

SAP has a recorder to create the sample file. Just run trans SHDB. Within this transaction, you run a case of the changes you want to do. On exit of the trans you will have a batch input file that you can download and from excel. Manipulate the file before you call the RFC function RFC_CALL_TRANSACTION.

Number formatting in Excel spreadsheets is used to change the look of the numbers in the cell. The most common formatting options are currency, comma, date, accounting and percent, although there are more options available.

The Excel SUM function is probably the most often used function in Excel spreadsheets. The AutoSUM button on the Standard toolbar makes it easy to quickly sum columns or rows of adjacent data without actually typing in the cell references.

One of the most useful functions in Excel is the IF function. What it does is test a value in a specific cell to see if that value meets a certain condition. If it does, one result will happen, if not, a different action can occur. This article covers how to write an IF function.

Microsoft Excel is an electronic spreadsheet program that people use for storing, organizing and manipulating data. The types of data that Excel can use includes numbers, text and formulas. Formulas are used to perform basic mathematical operations, calculating repayment plans for loans or mortgages, and to find the average, maximum, or minimum values in a specified range of data.

In olden days we have Lotus 123, it is also having spread sheets for using accounts purpose. Now excel having spread sheets not only accounts purpose but also for various types of formats with calculations.

To open a file as copy, just follow the steps

* Select your file through the file open box (CTRL+O)
* Now at the bottom you may notice an ARROW BOX next to the OPEN button (in fact, it is a drop down box)
* Click the arrow and select OPEN AS COPY.
* Now you could be having a copy of the original version of the file.

If you open a Excel file using the OPEN AS COPY option, then a new file is created and saved in the same directory of the original file. So if you have a file, say test.xls in D drive. Then if you open the file as copy, then Excel creates a file in the D drive with the name Copy(1) of test.xls.
You can access this file through the path D:/Copy(1) of test.xls. You can always rename and copy your file somewhere else but by default Excel saves the copy of the file in the same drive. Also, Excel duplicates the original file and whatever is in the original file, it is copied including formats, formulas and other functionalities.

1. Choose Tools > Options and select the General tab.
2. All files listed in the "At startup, open all files in:" box will be loaded when Excel starts.
3. Alter or remove this list.

Clear out c:windows emp directory on the machine that houses the files, then reboot.

Links come in several flavors; linked formulas, defined names (Insert > Name > Define menu), objects (buttons and stuff) assigned to remote macros.

1. Choose Tools > Options and select the General tab.
2. Remove the check mark from 'R1C1 reference style'

In Excel 2002, and later versions, you can colour the sheet tabs. Right-click on a sheet tab, and choose Tab Color.
Note: When the sheet is selected, the colour will show in a thin strip at the bottom of the sheet tab. The full tab is coloured for non-selected sheets.

To increment a formula reference by rows, when copying across columns you need a combination of the INDIRECT and ADDRESS Function. For example, place this formula in any row in Column "D".

=SUM(INDIRECT(ADDRESS(1,1)&":"&ADDRESS(COLUMN()-2,1)))

Now copy it across any amount of Columns. You will note that at each Column the row number increases by 1. In affect it will look like:

=SUM($A$1:$A$2) at Column D
=SUM($A$1:$A$3) at Column E
=SUM($A$1:$A$4) at Column F
=SUM($A$1:$A$5) at Column G
etc

The Syntax for the ADDRESS Function is

ADDRESS(row_num,column_num,abs_num,a1,sheet_text)

The last 3 arguments (abs_num,a1,sheet_text) are all optional. If your range resides on another sheet, it is the sheet_text that you need, eg

=SUM(INDIRECT(ADDRESS(1,1,,,"Sheet 2")&":"&ADDRESS(COLUMN()-2,1)))

Note that the Sheet name is supplied to the first ADDRESS function only

An add-in with a programming error is causing problems.

1. Choose Tools > Add-ins
2. Note which are checked
3. Uncheck all (but one)
4. Restart XL
5. If no error, check the next one and repeat from step 4

Got the error? Uncheck the one you last checked. If no luck, see if there's an .xla file in the startup directory (see previous tip). Scanner software typically place itself there, sometimes with code errors like the one in question.

Not an add-in? It will be a hidden workbook then.

1. Try (in Excel) Windows, Unhide and write down which files are listed.
2. Unhide them.
3. Now go to the VBE (press Alt + F11).
4. On the left side there should be the project explorer.
5. Select the first project you unhid
6. From the menu choose Debug, compile
7. No Errors? Select the next project listed, repeat 6.

This sounds like you just have two windows open that are displaying the same workbook.

Select the window that you want to close, and press Ctrl + w

OR, you can close the extra window by clicking the lower of the two X buttons in the upper right corner of the screen (don't click the top X button, as that will close Excel). Then save the workbook.

If you are sure it's not really happening (you may have Book1 open and then attempt to open a Book1 from another folder), then try re-registering Excel.
1. On the Windows taskbar, click the Start button
2. Choose Run
3. Type "C:Program FilesMicrosoft OfficeOfficeExcel.Exe" /regserver (include the quotes) -- adapt to fit your path.
Other things to check:
1. In Excel, choose Tools>Options.
2. On the General tab, make sure "Ignore Other Applications" is not checked

This could really be anything. Considering that, it's remarkably often a corrupt toolbar file. So worth a try the following:
1. Close Excel (if it managed to open).
2. Reboot if necessary.
3. Locate all files (with Start > Search etc) with extension .xlb
4. Rename the extension(s) to something like .old
5. Start XL again.

To-Do List:
Try opening Excel without any addins or hidden workbooks:

1. On the Windows taskbar, click the Start button
2. Choose Run
3. Type "C:Program FilesMicrosoft OfficeOfficeExcel.Exe" /Automation (include the quotes) -- adapt to fit your path.

If your error disappears, there is either an add-in or a (hidden) workbook that is loaded upon XL's start, which is causing problems.

* Try locating the XLSTART directory, move everything from there.
* In XL: Tools, Addins, note the ones checked and uncheck them one at the time, each time restarting XL
* In XL, Tools, options, general. Check if a path is entered after "Alternate startup file location". Clear it.

Another option is to open XL in Safe mode:
"C:Program FilesMicrosoft OfficeOfficeExcel.exe" /Safe

Also, you might try:
"C:Program FilesMicrosoft OfficeOfficeExcel.exe" /Regserver

Another possible problem is a corruption of your toolbar customisation file:

* Locate all files with extension .xlb
* Rename the extension(s) to something like .old
* Now try and start XL again.