Information dissemination

The little Green Triangle - Number Stored as Text

       May be you already know but here is a cool way to get rid of all those green triangled cells that come up for cells with numbers saying 'Number Stored as Text'. Clicking the Convert to Number is a good option but when you have a whole spreadsheet like that it takes good amount of time. You can select 'Error Checking Options' and suppress these but if you want to add or do any other mathematical calculations on these cells, you would HAVE to convert these. And if you are thinking, it is simple, you will go to format cells and say Number in the option there...it will NOT WORK. 
     Here is a snapshot of what we are talking about.

So, Here is a simple way to do that without using any formula.
1. Enter 0 in a blank cell. It gets formatted as a number. Copy it.
2. Select your whole column/list of 'Numbers stored as text'
3. Choose 'Paste Special' from the Edit Menu
4. Paste:Values,Operation: Add [This - You didn't know or didn't try ;-) ]
5. All those numbers in text format are now converted to numbers and there are no green triangles anymore.

Now don't say that you will copy 1 from a cell and paste saying Multiply. It is basically the same thing, using the option of Operation while Pasting.
 

Delete Empty/Blank rows in Excel - without a macro or VBA
 
I was fumbling around yesterday to delete the blank rows in between in an Excel spreadsheet containing more than 20,000 rows. Later, found this which was very quick. The issue was that I didn't have an option to Sort as I needed the order in which the file has rows and I could not filter by blanks or spaces because when I do so,the first blank it encounters, it thinks the end of the file is reached and shows me only the next blank row.

Here is the trick that came in very handy. Make a copy of the spreadsheet first, to be safe.

• Select the 'entire' first column.Press Ctrl G or F5(Go To)
• Click the 'Special' button, select/check 'Blanks' and click OK. This selects all the blank rows though you don't exactly see the same on the screen.
• Now click Edit->Delete,select 'Entire Row' option and click Delete.
• Turn the AutoFilter on(Date->Filter->AutoFilter) and select All from the dropdown.
• All the Blank/Empty rows are gone.

Hope this helps when you get into this situation, some day.

Difference between the functions FIND and SEARCH in Excel
 
Have you ever wondered about the difference between the functions FIND() and SEARCH() in Microsoft Excel? I had this thought saying I want to know/find the place or the number of a given letter/alphabet say 'N' in the alphabet sequence. So, was checking the functions to see which one works to find the position of a character in a given string. You see what, there are two functions and I was wondering what the hell is the difference. Both seem to work exactly the same way.

Well, you see every thing has a reason for its existence. So, when I dig deeper I have a revelation :-). Microsoft seems to know that too :-p.
 
FIND is case sensitive but SEARCH is not.
 
SEARCH() will find it even if you give lower case or upper case irrespective of your target string.So all of you guys out there, make use of the difference too. Don't keep changing the target string for each case just to make use of FIND()

Scroll Lock: Move through worksheets without losing your place 
 

             Have you ever wondered how to use the Scroll Lock button at the top of your keyboard? In Microsoft Excel, the scroll lock provides a very useful option for navigation.Typically, with scroll lock off, you use the arrow keys to move the active cell pointer around a fixed worksheet. The active cell location changes with each arrow key pressed.
When you use the scroll lock option, the active cell pointer remains in the current cell and the sheet moves rows or columns depending on the direction of the arrow key pressed.

            This option is very useful when you want to view other areas of the spreadsheet without losing your current active cell position.