Get start with Excel 2003/2007

 In todays high speed and competitive world one need to take quick and accurate decisions. Gathering, filtering and analyzing raw data has been made easier with new technology and tools available in the maket. MS Excel is one such powerful tool to analyze data and to take quick decision. People familiar with Excel will have more advantage then others, who are not. They will be able to take faster and smarter decisions. Hence, why stay behind? Let begin with Excel!

 Click on Start->Programs->Microsoft Office->Excel 2003/2007

If you have Excel 2003, its screen will appear as follow:

If you have Excel 2007, its screen will appear as follow:

Between 2003 and 2007 they function similarly. However, 2007 is more advance and it has ribbon instead of toolbars.

Excel has three entities:
1. Workbook (Excel file with .xls extension)
2. Worksheet (by default it opens with 3 worksheets/tabs - Sheet1, Sheet2 and Sheet3)
3. Cells (Each worksheet has cells in it. All are capable of performing intricated calculations)

These three are the core strengths of Excel. Without it Excel cannot be deemed as powerful tool for data analysis.

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.