1. Starting with Excel
In today's 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.
2. Creating and Saving spreadsheets
Creating New Workbook/Spreadsheet
- Go To File -> New
Shortcut to save workbook, type Ctrl+N
A new blank workbook will appear.
Saving Workbook/Spreadsheet
- Go To File -> Save As
- Select folder and enter file name.
Shortcut to save workbook, type Alt+F+A. The Save As dialog will appear.
By default excel save's the workbook with ".xls" extension
Saving changes
It is always good practice to save your changes in workbook frequently. This practice will help you to over come any unexpected loss of data due to unknown reasons. This has helped me a lot from redoing work. For example just think that you have to redo the last half an hour work again.
- Go To File -> Save
Shortcut to save workbook, type Ctrl+S
3. Linking spreadsheets
Suppose you have data in two different worksheets (tabs) and you want all that data in one worksheet/tab. You can easily copy and paste data from those two worksheets into third worksheet. But that is not the smart way of working. What if the data is updated regularly in two tabs? Every time you will end up copying and pasting into third worksheet/tab.
However, excel has better way to do this task by linking the data. Lets look at the following illustration.
I have three worksheets/tabs, namely "All Data", "2010" and "2009".
Following is the company's income statement for year 2009.
Similarly there is quarterly data for year 2010.
Now I need to link all these data in "All Data" worksheet/tab. Since the line item are same in both tabs, I will link them as well.
- Click on "All Data" tab.
- In cell A1 type "="
- Click on "2009" tab then click on cell A1 of this tab
- Now press Enter
Now you can drag remaining links by pointing cursor to south east corner of active cell. The cursor will change from arrow to plus sign. Left click and drag it to bottom (row 30) and then to right (column F).
Note you will see the link (='2009'!A2) to "2009" tab's A2 cell in the formula bar.
If you are linking cells in which there is no data the it will show "0" in your destination ("All Data") tab.
Similarly, 2010 data can be linked from "2010" tab as follows.
- In "All Data" tab, click on G1 and type "=".
- Click on "2010" tab then click on cell B1 of this tab
- Now press Enter
- Remaining links can be dragged by pointing to south east of active cell.
The arrow cursor will change to plus cursor. Left click and drag till row 30 and then drag it to one column right. Entire data in "All Data" tab will look as follows:
To finalize the workbook delete all the zeros (blank cell links).
