1. Define the following:-
1: relative reference
2: absolute reference
3: mixed reference?
All these words are used in describing copy paste function of formulas or functions
Relative reference: This is the normal way we write a formula or function. In excel when we copy a formula by default it will change reference automatically. For ex we have data from A1 to A10 in cell A11 when we use a function sum (Sum(A1:A10)), it will return total of values given in A1 to A10. When we copy the function given in A11 and paste it in B11 the function will be (sum(B1:B10)) that will return total of values given in B1 to B10. This is called relative reference.
Absolute reference: In this scenario even if we copy a formula the reference will not change. We make this happen by using $ symbol in function syntax. Going y previous example instead of the above syntax if I use sum($A$1:$A$10), even if I paste the formula in B11 it will continue to give me total of values present in A1 to A10.
Mixed reference: In mixed reference we will only lock in either row or column that means we use $ symbol to fix either row or column and leave other one as relative reference. For ex sum(A$1:A$10) this formula when I copy and paste in B11 it will be changed to sum(B$1:B$10) but when I paste same formula in A33 it will not change.
2. How to Change the Sheet Tab Color of Excel Spreadsheets?
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.
3. Show Excel Toolbars on Two Rows?
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.
4. Upload data from Excel into SAP?
* 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.
5. What are Excel Spreadsheet Number Formats?
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.
6. How Do You Use the SUM and AutoSUM Functions in Excel?
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.
7. How Do You Use IF Functions in Excel?
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.
8. What is Excel and When Would I use it?
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.
9. How to open a new copy of a Excel file?
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.
10. How to access my Copy 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.
11. When I start Excel, why do a million files open up automatically?
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.
12. All of a sudden a number of my Excel 2000 files have become read-only?
Clear out c:windows emp directory on the machine that houses the files, then reboot.
13. Why does Excel say my file has links, when I know it does not?
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'
15. How can I change the color of the sheet tabs?
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.
16. Excel Uses for the INDIRECT and ADDRESS Functions or Formulas?
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
17. When I start XL, I get the error message Compile error in Hidden Module?
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
20. Excel crashes on opening / closing / prompts not enough resources?
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.
Select the row below the section that you want to freeze. Then, on the Ribbon's View tab, click Freeze Panes, and click the Freeze Panes option.
Lock the title row in Excel 2003, and earlier versions:
Assuming title row is 1, select cell A2, then choose Window > Freeze Panes
23. Why do my scrollbars go to row 500 -- my data ends in cell E50?
Excel may be remembering some data that was in row 500, but has been deleted. To go to the cell which is currently the "Last Cell" in the worksheet, hold the Ctrl key, and press the End key. If the Last Cell (cell J500 in this example) is outside the range of cells that is actually being used, you can reset the used range, so the scroll bar works correctly.
Note: If any cells contain references to the deleted cells, those references will be replaced with a #REF! error. If you have cells that are formatted, but outside the range that contains data, the formatting will be lost.
To manually reset the used range:
1. Select the last cell that contains data in the worksheet
2. To delete any unused rows:
o Move down one row from the last cell with data.
o Hold the Ctrl and Shift keys, and press the Down Arrow key
o Right-click in the selected cells, and, from the shortcut menu, choose Delete
o Select Entire Row, click OK.
3. To delete any unused columns:
o Move right one column from the last cell with data.
o Hold the Ctrl and Shift keys, and press the Right Arrow key
o Right-click in the selected cells, and, from the shortcut menu, choose Delete
o Select Entire Column, click OK.
4. Save the file. Note: In older versions of Excel, you may have to Save, then close and re-open the file before the used range is reset.
If the high contrast setting is turned on you won't see the fill colour. There's information in the following MSKB article:
The fill color, the fill pattern, or the line color of a WordArt or AutoShape object in an Office document does not change
Fill patterns were designed for older printers with low resolution. Today's printers may print the patterns in such fine detail, and so small, that the pattern sometimes disappears. If you really, need to print patterns, you could roll your own, using white rectangles, with transparent border, and lines, etc.
Create a square that represents one repeat of the pattern. For example, if you want closely spaced vertical lines, draw a 1" white square with 6 vertical 1" black lines. Use the alignment and spacing commands to finish the pattern. Group the objects in each pattern, copy, and paste into the data series. Then format the series to have a black border. If necessary, set the Picture format (in Fill Effects) to Stack. It doesn't look too good on the screen, but prints beautifully
26. Excel Limits and Specifications:
I need more than 256 columns and/or 65536 Rows.
In Excel 2003, and earlier versions, that's the limit. Excel 2007 has 1,048,576 rows and 16,384 columns.
How many worksheets I can put in a workbook?
It is not a limit per se, but of course there is a practical one which depends on the computer resources.
How many characters can be placed in a cell?
In Excel 97 and above, you can have up to 32K characters in a worksheet cell, but only the first 1000 or so are displayed (the exact number depends on your font and display characteristics). However, you can increase the number of characters displayed if you add line breaks in the cell (press Alt+Enter).
27. How can I protect formulas from being deleted or changed?
Before protecting the worksheet:
1. Select all cells that users ARE allowed to change.
2. On the keyboard, press Ctrl + 1 to open the Format Cells dialog box
3. On the Protection tab, remove the check mark from Locked.
4. Click OK, to close the dialog box
Protect the worksheet in Excel 2007
1. On the Excel Ribbon, click the Review tab
2. Click Protect Sheet.
Protect the worksheet in Excel 2003
1. Click the Tools menu, then click Protection
28. How many worksheets I can put in a workbook?
It is not a limit per se, but of course there is a practical one which depends on the computer resources.
29. I have lost my password in MS Excel?
Well, there are passwords and there are passwords.
* One is to open the file
* one other is to unprotect the worksheet
* still another is to unprotect the workbook
* yet another one is to unprotect the Excel VBA macro cod
There is no foolproof method for this, and most solutions limit themselves to require macros enabled on opening. This said, creating your own system is a fun challenge allowing lots of "evil creativity".
31. Is there a way to allow the use of AutoFilter on a protected worksheet?
This needs a macro to run first:
Sub Protect_keep_filter()
With ActiveSheet
.EnableAutoFilter = True
.Protect DrawingObjects:=True, _
contents:=True, Scenarios:=True, _
UserInterfaceOnly:=True
End With
End Sub
32. When I type a number, example 44, it becomes 0.44 even if I format the cell without decimals?
Goto Tools>Options/Edit and uncheck the Fixed decimals checkbox.
33. When I clear a large range, it takes forever. How can I make it faster?
If you have Google Desktop Search installed, turn it off in Excel.
34. How can I prevent hyperlinks from appearing when I type an email address?
To turn that option off in Excel 2002 and Excel 2003:
* Choose Tools | AutoCorrect Options
* Select the AutoFormat as you type tab
* Remove the check mark from Internet and network paths with hyperlinks
* Click OK
To undo the hyperlink creation in all versions:
* Type the email address and press Enter
* Immediately, press Ctrl+Z. This is a shortcut for Undo, and will convert the hyperlink back to text.
Excel only maintains 15 significant digits for numbers and cannot be used for credit cards which require 16 digits. If you don't need to do math on them, input long numbers into a range that you've formatted as text (Format>Cells, Number tab).
To prevent both of the above issues (hyperlinks and 16-digit numbers) -- as well as entries that are automatically converted to dates (e.g. 3/4) -- type a single quote ( ' ) before the entry. Excel will treat the cell as pure text and change nothing.
