Excel-FAQ2- SUBSTRING,GRIDLINES,HEADERS, COMMENTING..

SUBSTRING, GRIDLINES, HEADERS, CONDITIONAL FORMATTING..etc


1.    1) How do I Substring a text in Excel?
This is a very common question and has an easy solution to it. Although there is no specific function called “Substring” or “Substr” in excel, but there are three functions which can be used for the “Substring” operation.

  a)   MID Function: This is probably what you are looking for if you are searching for a Substring function, First let us discuss the syntax of “Mid” function

MID(text,start_num,num_chars)

Example - A1 has “Analysis-Reporting” written in it. Now A2 has the following formula in it “=MID(A1,10,9)”, which means nine characters from the string in cell A1, starting at the tenth character.
This gives “Reporting”.
The other two functions Left and Right are nothing but Special cases of the Mid function.

b)    LEFT Function: This as I said is nothing but a special case of Mid function. The syntax is
LEFT(text,num_chars) [Special case of Mid, MID(text,1,num_chars)]
Example as above A1 has “Analysis-Reporting” written in it. Now in A3 we have “=LEFT(A1,8)”. Which means 8 characters from the string in cell A1, starting at the first character (leftmost character)
So A3 we’ll have “Analysis”.


c)     RIGHT Function: Syntax of this function is
RIGHT(text,num_chars) [Special case of Mid, MID(text, LEN(text)- num_chars + 1,num_chars)]
Example as above A1 has “Analysis-Reporting” written in it. Now in A4 we have “=RIGHT(A1,9)”  Which means 9 characters from the string in cell A1, from the last character (rightmost character)
So A3 we’ll have “Reporting”.

2.      2)  Whar are gridlines? or How do I remove the grid lines from excel sheet?
     Gridlines are nothing but the lines which divide the Excel sheet into cells. So if we remove all the gridlines we would just have a plane white sheet.(although the cells will still retain their identity).

To remove gridlines:


In Excel 2007- Go to VIEW and ‘uncheck’  the gridlines check box.
Excel 2003 -Go to forms toolbar, and click  on the grid button

3.      3) How do I get the row headers and column headers back?
IN 2007 Go to VIEW> and check Heading
In 2003 Go To tools>options>row & Columns Headings

4.       4)  How do I shade a cell conditionally in excel such that the shade color depends on the another cells value?
I am trying to get a cell to return a shade of grey if the text in another cell is typed in. Example: If John requests off on Monday the Monday cells on the schedule would be greyed out. Any help out there?

Suppose A1 is where you enter 1 (if absent) and B1 is to become grey when A1 is 1 then

Excel 2003:
Go to Format>Conditional Formatting
Choose condition1 as "Formula is " then in the text box next to it put "=A1=1" (without quotes).Then choose a color by clicking on format.

Excel 2007:
Home>Conditional Formatting>New Rule
then click on "Use formula to determine which cells to format"

then in the text box put "=A1=1" (without quotes).Then choose a color by clicking on format.


5.     5)  How do I delete Combo box from my Spreadsheet?
There are two types of Combo box a)Control and b)Form .To delete the control Combo box go to DESIGN Mode by clicking on the VISUAL BASIC Tool Bar and  clicking on the DESIGN BUTTON, then right click on the combo box and delete or cut.


6.       6)How do I write “-A4” in a cell with the cell not actually taking the negative value of A4.
The easiest way would be to write it in a text format or simply putting a single quote in front of the text “ ’-A4 “.


7.       7)Is it possible to hide information in a cell unless I move my mouse over it?
I am creating a list of words and their definitions in Excel, and I want to hide the information in the cells that contain the word's definition until I move my mouse pointer over the cell. Is it possible?

You could put the definition for the word as a Comment. Then when you mouse over the word, the comment will show.
Right-click on the word
Select Insert Comment
Put your word definition in the comment



Blog Widget by LinkWithin

Search this blog..

Loading