User:Shantanuo/excelFAQ

How to delete all the comments in a sheet?

Choose Edit - Goto Click on Special.... Select Comments and choose OK. (All the cells containing comments will get selected.) Right click on any one of the cells containing a comment and choose delete comment option from shortcut menu.

How to password protect a file? From file menu choose "Save As...". Choose "Tools" - "General Options" Supply a "Password to open:" and/or "Password to modify:". Save the file by overwriting on the existing one.

How to change the text color of the specific cells?

Select all the cells in the Sheet (Ctrl + A) Choose "Conditional Formatting..." Option under "Format" menu. Choose "Cell Value is" "less than" "0". Click on "Format" option and choose the "Red" Color. Click on OK.

How to change Date Format? When you type 31 Dec. 1999 excel automatically changes the date to 12/31/99 (The American Format) If you want the format to be Indian like 31/12/99 then simply go to Start - Settings - Control Panel - Regional Settings - Choose UK instead of USA.

What is Iteration? For Eg. Gross profit - Tax = Net Profit and Tax = 10% of Net Profit Excel will show a "Circular Reference" Error. Choose Options from Tools Menu. In the Calculation Tab check the Iteration Check Box. (Excel 2000 version has iteration selected by default)

What is Precision As Displayed? Choose Options from Tools Menu. In the Calculation Tab check the Precision As Displayed Check Box.

How to apply Indian Comma Style? Choose Cells option from Format Menu. In the Code Box type [>9999999]"Rs."##\,##\,##\,##0;[>99999]"Rs."##\,##\,##0;"Rs."##,##0

What are the different Levels of Settings? There are Three Types of Settings 1. Sheet Level 2. File Level 3. Application Level (Also called Default) Sheet Level Settings: File - Page Setup (Paper Size, Margins, Header and Footer) File Level Settings: File - Summary Info, View-Zoom, Format - Cells (fonts, Border), Window Arrange Application Level Settings: Tools-Options-Iteration, Precision As Displayed, General - Standard Font, Font Size

Can you tell us more about Formulas? If you want the text and Numbers as a formulae just type (="The Total is" & b2) as your formulae. It will display "The total is 20" If you wanted a sum of all the amounts that are greater than $500 in the cell range a2 to a11, you would use the formula: =SUMIF(A2:A11,">500")

What is Relative and Absolute Reference? You can use the $ sign to change the references from Relative to Absolute and vice versa.

How to enter an arrays formulae? To enter an array formulae Ctrl + Shift + Enter. If you want a sum of all of the numbers greater than 500 and less than 300. Type this formula and when you are finished typing, create an array by hitting ctrl+shift+enter: =SUM((A2:A11>500)*(A2:A11))+SUM((A2:A11<300)*(A2:A11))

What does all those different Error Messages mean? Div/0! Is Trying to Divide by Zero N/A Refers to a value that is not available Name? Users a name that doesn't recognize Null! Specifies an invalid intersection of two areas Num! Uses a number incorrectly Ref! Refers to a cell that is not valid Value! Uses an incorrect argument or operand

How to select visible cells only? Select the cells including the hidden cells. From Edit Menu select Goto. Click on Special... button and select the "Visible cells only" option.

How to backup a file automatically?

1. From file menu choose "Save As...".
2. Choose "Tools" - "General Options"
3. Check the box "Always create backup"
4. Save the file by overwriting on the existing one.

How can I fill all blank cells with 0?

1. Select the cells including Non-Blank cells.

(Keyboard Shortcut Ctrl + Shift + *)

1. Choose Edit - Go To...
2. Choose Special and then click on Blanks Radio Button.
3. Type 0 and then hit Ctrl + Enter to fill all the blank cells with 0.

How do I Print all the formulas in my excel spreadsheet? Do you want to print all the Formulas in one of your Excel spreadsheets so you can have the hard copy right beside you while you work? Well...here's the steps to do this easily.

1. Hit ctrl+` (tile) to show all your formulas.
2. Go to the Edit menu and select "Go To".
3. In the Go To box, click on the button that says Special.
4. In the Special box, put a check in Formulas.
5. Now all your formulas are selected, so go to the File menu and choose Print and in the section where it says "Print What", choose Selection.

How do I align currency symbols to the left? To have the currency symbol appear on the left of the cell rather than just to the left of the number, include for example, $* followed by a space at the beginning of the number format. For e.g. use the number format: "$* #,##0;$* -#,##0" (without the quotes).

How do I know how many cells in a given range contain text or number? If you want to know how many cells contain text, type the following formula and hit Ctrl+Shift+Enter to creat an array. =SUM(IF(ISTEXT(D10:L10),1,0))

How do I compare two different sheets? Copy and paste the code available on the following page into your Visual Basic Editor (Tools - Macro - Visual Basic Editor). The sheet names should be sheet1 and sheet2 in the current file. You can change these names. http://www.erlandsendata.no/english/vba/ws/comparews.php You will need to save this macro to the personal workbook so that it will be available to all files!

Where will I find more functions? http://www.rhdatasolutions.com/morefunc/ Download and install this add-in for excel. You can read more about it's use here http://tinyurl.com/9wli

How do I get the name of the worksheet using a formula? =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

Using ctrl+F ("find"), it's impossible to find cells that contain a plain asterisk. Any Ideas on how to do this? Use the tilde(~) before the wildcard character to search for. For example, if you want to find "*" using CTRL+F, put ~* in the "Find What" box.

How do I calculate Age of a Person? Enter a birthdate is cell A1. In cell B1 type the following: =DATEDIF(A1,TODAY(),"y") and press the Enter key.

How can I convert a number that is formatted as text to a number that is formatted as numeric? Use the "value" function. If cell B2 contains the data you want to convert to a numeric format, the formula is =value(B2)

How do I disable starup macros? To prevent macros from executing when a workbook is opened, such as the Workbook_Open event or an Auto_Open macro, hold down the Shift key while you open the workbook.

How do I find the quarter of the year? This formula will convert a date entry into the quarter in which it falls. In this example, enter a date into cell A2. =INT((MONTH(A2)+2)/3)

How do I change the cell format?

1. Choose Format - Cells and select Custom in the Category list. 2. In the Type box, you'll see General, which is the default value. Immediately after it, type " cases" (including the quotes and the space after the first quote). The entry should read General cases. 3. Click on OK.

The format you just created will put the word cases behind any value you enter into the cell, but Excel will still treat the value you entered as a number, not text. Of course, you can substitute any word you want, such as bags, cans, or pounds.

You also can create a format that changes depending on the value typed into the cell. To create such a format, you add conditions enclosed in square brackets in front of each partial format, and separate the segments with a semicolon. For example, the format [<>1]General" units";[=1]General" unit" will cause Excel to follow the value 1 with the word unit and values other than 1 with the word units.

Adding leading zeros to a range of 3 and 4 digit numbers. Type 0000 in the box under the word Type on the right hand side of the format dialog box. Now 397 will display as 0397.

Hiding data in a cell. Type ;;; in the Type box. No matter what colour font or cell background you cannot see the data in this cell. The data or formula can still be edited in the formula bar.

Special Numbers. Red bracketed negative numbers, two decimal places, leading spaces before the numbers and the $ at the far left (with a space), two spaces at the right edge and commas at thousands. $* #,##0.00 ;[Red]($* #,##0.00) ;

Note: you have to add an extra space to the non negative numbers so that the digits will line up with the negative ones. Also make sure you get the space at the beginning before the first $.

To have the currency symbol appear on the left of the cell rather than just to the left of the number, include for example, $* followed by a space at the beginning of the number format. For e.g. use the number format: "$* #,##0;$* -#,##0" (without the quotes).

To calculate total hours, apply this - [h]:mm

To display Partial Hours as a Decimal Number: Consultants often bill their clients by the tenth of an hour, so it doesn't do much good to bill someone for 9:30 when you worked for nine and a half hours. To display a time as a decimal value, such as 9.5 hours, follow these steps: 1. Format - Cells - click on the Number tab, and select Number from the Category pull-down list. 2. Type this formula in the cell: =HOUR(value)+(MINUTE(value)/60), replacing value with the address of the cell that contains the time or the formula that generates the time you want to convert into a decimal value. If the time were in cell E4, you would use the formula =HOUR(E4)+(MINUTE(E4)/60).

How do I limit the Movement in an Unprotected Sheet? To set the Scroll Area range, click the Properties icon from the Control Toolbox toolbar. In the Scroll Area text box, type the scroll area range. (To cancel the Scroll Area restricted range, clear the Scroll Area text box.) Note: You cannot add two Scroll Areas to the Scroll Area text box.