Download MICROSOFT EXCEL BASIC NOTES and more Exercises Computer science in PDF only on Docsity!
MS EXCEL
A spreadsheet is essentially a matrix of rows and columns. Consider a sheet of paper on which horizontal and vertical lines are drawn to yield a rectangular grid. The grid namely a cell, is the result of the intersection of a row with a column. Such a structure is called a Spreadsheet.
A spreadsheet package contains electronic equivalent of a pen, an eraser and large sheet of paper with vertical and horizontal lines to give rows and columns. The cursor position uniquely shown in dark mode indicates where the pen is currently pointing. We can enter text or numbers at any position on the worksheet. We can enter a formula in a cell where we want to perform a calculation and results are to be displayed. A powerful recalculation facility jumps into action each time we update the cell contents with new data. MS-Excel is the most powerful spreadsheet package brought by Microsoft. The three main components of this package are
Electronic spreadsheet Database management Generation of Charts.
Each workbook provides 3 worksheets with facility to increase the number of sheets. Each sheet provides 256 columns and 65536 rows to work with. Though the spreadsheet packages were originally designed for accountants, they have become popular with almost everyone working with figures. Sales executives, book-keepers, officers, students, research scholars, investors bankers etc, almost any one find some form of application for it.
You will learn the following features at the end of this section.
Starting Excel 2003 Using Help Workbook Management Cursor Management Manipulating Data Using Formulae and Functions Formatting Spreadsheet Printing and Layout Creating Charts and Graphs
Starting Excel 2003
Switch on your computer and click on the Start button at the bottom left of the screen.
Move the mouse pointer to Programs, then across to Microsoft Excel, then click on Excel as shown in this screen.
When you open Excel a screen similar to this will appear
The formula bar is the place in which you enter the formula(=A3*B5)
The alphabets A,B… are known as columns
Type the first few letters to see the help entries for those letters.
You can get the printout of any help topic by selecting it, right clicking and then clicking Print Topic.
Workbook Management
Task 1: Creating a new workbook
Click on File menu and then click on New.
Click Workbook and then click OK button. You will get the screen as shown below.
Enter data as shown in the figure below :
Click on some file (Example: sample.xls), then click on Open.
Task 4: Closing your workbook
Click on File menu, then click Close to close your workbook
Cursor Management
Task 1: Moving around the worksheet
Open sample.xls workbook. Move the cursor in your worksheet by using the arrow keys on the right-hand side of the keyboard. When you have got lots of rows of data you can move the cursor more quickly by using the PgUp and PgDn keys to move up and down a screen at a time. To move one screen to the right, press the Alt key and PgDn keys together. To move one screen to the left, press the Alt and PgUp keys together. To move further to the right, just keep pressing the right arrow key To move back to cell A1, press the Ctrl and Home keys together. Pressing the Home key on its own takes you back to column A To move to the last column(IV) press the Ctrl and right arrow keys together.
To move to last cell containing data, press Ctrl and End keys together. To move to the last row(65,536), press Ctrl and the down arrow keys together. You can also move the cursor with the mouse. Move the mouse pointer to the location you want. Press and release the left mouse button once when the cursor is where you want it.
Task 2: Moving to a Specified cell
Click on the Edit menu, choose Go To. You will get the below screen
Enter the destination cell reference in the Reference text box.
Click OK to move directly to the specified cell.
Data Manipulation
Task 1: Entering data
Start Excel. Click File and then New. An empty worksheet appears as shown below
Save your work by clicking File and then Save As. This dialog box appears.
Type cash in the File Name text box and then click Save button. Excel automatically adds the extension .xls to your file name.
Task 2: Editing data
Click File and then click Open. Click cash.xls and then click Open. Move the mouse pointer to cell D4, click and release. The cell is highlighted and 18 appears in the formula bar. Move the mouse pointer to the formula bar and click once to the right of 18.
Use the Backspace key to delete 8,then type 4 and press Enter. Cell D4 now contains the value 14.
Task 3: Replacing cell data
Make the cell B5 active by clicking on it. Type 200 and press Enter. The cell B5 will now contain the value 200 replacing old value (150).
Task 4: Deleting cell contents
Move to cell C5 and click to select. Press the Delete key. The cell becomes blank.
♦ Type date in the cell ♦ Select the cell and drag the Fill Handle
We can customize the lists with different text data to minimize the redundancy of work.
Some of the lists are listed below:
- Jan, Feb, Mar, Apr, May, June, July…. like months
- Sunday, Monday, Tuesday, Wednesday, Thursday…Like week days
- Adilabad, Anatapur, Chittor, Cuddapah… like District names
- Ravi, Kiran, Praveen, Rama…. like employees list
To create a customized list follow the steps given below:
♦ Click Tools Menu ,Click Options then click Custom Lists tab, Then you will find the figure given below:
♦ Click NEW LIST and enter the list in the List entries window ♦ Click Add button then click OK button then your list will be added to the Custom Lists. That list you can use as and when required to type. ♦ Now you can Drag the fill handle ( + ) to get the list automatically.
Using Formulae and Functions
Task 1: Entering a formulae
Click File and then click New.
Enter the data in the new worksheet as shown below
Click OK button. To print the worksheet with formulae displayed, click File menu and click on Print Preview. If the layout is satisfactory, click on the Print button.
Task 4: Using the SUM function
Open cash3.xls spreadsheet.
Suppose if you want the summation of the cells B3 to B5 should appear in the cell B6, then first select the cells from B3 to B6.
Click the Auto Sum icon on the toolbar. The result of (B3+B4+B5) will appear in the cell B6.
Task 4: Copying Formulae
Open cash3.xls spreadsheet. If you want to copy the formula in the cell B6 to C6,D6,E6 then first select the cell B6. Move the cursor to the lower right corner of the cell B6. The cursor will change to + icon. Drag the cursor from B6 to E6 and release left mouse button. You will notice that the cells C6, D6 and E6 are updated immediately as shown below.
Task 5: Copying formulae using absolute addressing
Create the worksheet shown below and save ABS If you copy the formula in the cell c2 to c3, c4, c5 you will get the incorrect
