How to search in comments

Here are the cells with comments I want to search.
 

The data range here is small, so you can search each comment manually by pointing your mouse to cell with comments.

However, there is much better way of searching comments by

1. Typing Ctrl+F and following instructions are self explanatory

2. Now you can select all cells by clicking on first cell in Find dialog box.

and then press Shift+End to select all.

OR

2. Hold Ctrl and then click on cell you want to highlight

3. Once the cells are selected you can hightlight it with

Fill Color button.


Note: We can only find comments and not replace any text in them.

How to select visible ERRORs

Today I am going to show you how to delete/debug all unwanted errors in worksheet in just few steps:



1. Make sure that you group/hide columns/rows in which you donot want to delete errors.
2. Then press Alt+; to select all visible cells
3. Then GoTo Special by typing Ctrl+G or F5 and type Alt+S
4. Select Formulas and uncheck Numbers, Text and Logicals
5 . Click Ok

 6. For all the visible cells with errors are selected

7. Now you can simply delete it by pressing Delete button in keyboard OR

8. Highlight it

  so you don't miss any of them for checking

Keyboard Shortcuts to move and scroll within worksheet

1. To move right end of data range type Ctrl+Right Arrow Key (->)

2. To move left end of data range type Ctrl+Left Arrow Key (<-)

3. To move at top of worksheet/bottom right of freeze pane type Ctrl+Home Key

4. To move at end of data range type Ctrl+End Key

5. To move one screen right (right horizontal scroll) type Alt+Page Down

6. To move one screen left (left horizontal scroll) type Alt+Page Up

Keyboard Shortcuts for Worksheet

1. To rename the current sheet type Atl+O+H+R

2. To insert new worksheet type Shift+F11

3. To move or copy the current sheet Alt+E+M

4. To delete the current sheet type Alt+E+L

Shorcut for divid'g/multipl'g/add'g/subtract'g with a factor

There is a simple shorcut for dividing/multipling/adding/subtracting any cell value with a factor. Lets me illustrate. We have following data.

All the above data is in thousands ('000). 

You copied data from another workbook/worsheet. But figures are not comparable.

In this case, you need to adjust to the copied data. You have to decided to divide each cell by 1000 manually. But this task is so boring and tedious.
Excel is suppose to make life easier? What if there were 3000 data rows. Now what shall I do?

Don't worry there is very simple way to do it.
1. Type factor (in this case 1000) in any empty cell. (I have typed in cell E1.)
2. Copy [Ctrl+C] that cell (E1 in my case).
3. Select range of cells that you want to divide. (my range is from F3 to F8)
4. Goto Edit -> Paste Special and then click Divide. Or use keyboard shortcut [Alt+E+S+I].

5. Click Ok.


Note: Cells with formula or "=" signs will retain its formula and show divide by 1000 seperately.
But cell with hard-coded numbers will be replaced by resulting value.
In this example you can see that F3 lost its original value from 2,10,000 to 210 (as noticed in formula bar).

But cells with formula like F4 (which had formula [11 x 22000]) is intact (see in formula bar).

How to transpose data in excel

Sometimes we receive data which is running horizontally for given year. It is difficult to interpreted data and linking in another workbook/worksheet becomes tedious task. But there is cool feature in Excel to transpose that data. Here are the steps to do it.


1. Select and copy the data to transpose (keyboard shortcut to copy [Ctrl + c] )

2. Then select a destination cell. ( I selected cell A11)

3. Then goto Edit -> Paste Special and check Transpose ( keyboard shortcut [Alt + E + S + E] )

4 .Click Ok.

Thats it.


Note: The above keyboard shortcuts I mentioned works for both Excel 2003 and Excel 2007.

Exploring Excel with me

Hi
Welcome to Excel Explored blog. Here you will find some cool tips and tricks in Excel to make your life easier. I have found that many people are unaware about interesting excel features. I have seen them working hard and in time consuming manner. Indead people would love to work smartly. So, I will keep posting ideas that will help them to move in that direction. This blog is also open to post ideas from basic excel features to vba in excel.