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
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
For Excel 2007
Then goto What-if Analysis -> Goal Seek
I have Total Revenue in cell L6. I want to change it to 8,000 by changing Domestic Revenue, which is in cell L4. Click OK.
Note: The cell you want to change (L4 in this case) cannot be formula. It should be hard coded only.
Excel will give you the option to keep the result by clicking OK or revert by clicking cancel.
For Excel 2003
The procedure for Goal Seek is same. You can find Goal Seek under Tools -> Goal Seek
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
There is a simple shorcut for dividing/multipling/adding/subtracting any cell value with a factor. Lets me illustrate. We have following data.
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).
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)
4 .Click Ok.
Thats it.
Note: The above keyboard shortcuts I mentioned works for both Excel 2003 and Excel 2007.
In the above example Domestic Revenue and International Revenue are hard-coded and Total Revenue is calculate with sum formula.
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].
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] )
3. Then goto Edit -> Paste Special and check Transpose ( keyboard shortcut [Alt + E + S + E] )
Thats it.
Note: The above keyboard shortcuts I mentioned works for both Excel 2003 and Excel 2007.
How to Seek Your Goal
Excel has very cool function to set cell value to specified number with the help of Goal Seek.
Let me illustrate with example:
My Goal is to change Total Revenue from 8,304 to 8,000 by either changing Domestic Revenue or International Revenue.
For Excel 2007
Goto Data Tab
Then goto What-if Analysis -> Goal Seek
In Goal Seek Dialog I have set the values as follow:
Note: The cell you want to change (L4 in this case) cannot be formula. It should be hard coded only.
The procedure for Goal Seek is same. You can find Goal Seek under Tools -> Goal Seek
