A sheet is composed of columns and rows. The rows are indicated by numbers. The columns are indicated by letters and the point of intersection of a row and a column is called a cell, a combination of a column letter and a row number represents a cell, for ex.: Cell A1 or cell A12.
To perform some of the commands or tasks, you first need to select cells.
Click on cell E1 and it gets selected. If you wish to select an entire row, click on the left of the row. This selects the 256 columns that are available per a row in a worksheet, and if you wish to select an entire column, click on the top of the column and you will have selected 16384 rows per that are available in a worksheet.
If you want to select a group of adjacent cells, click the first cell of the group and drag the pointer to the last cell in the group. All the cells that are selected are marked in black and a group of cells selected together is called a range.
If you want to select a cell range that is larger than your screen, then click the first cell, hold the Shift key, scroll with the toolbars in the spreadsheet and click the last cell in the range.
If you want to select non-adjacent cells, rows or columns then select the first cell, column or row, hold the control key button and then click the next cell, column or row. If you wish to select all the cells in your worksheet, click on the button at the top corner of the sheet, to deselect it, click somewhere in the worksheet.
To move the cursor to a specific position in the worksheet , you can also use F5 key. When you press F5, we get a ‘go to’ (go to) dialog box. Type in the ‘reference’ (reference) to select the cell and press Enter.
You can do this by clicking the name box at the top left next to the “Formula Bar”. Click the Name box, type the cell you want to select and press Enter key.
To enter text or numbers in a cell, place the cursor in a cell, type the text or number, and press the Enter key. The cursor automatically jumps to the next line. You can change this if you find it annoying:
Click on the Tools menu (Tools) ‘Options’ (Options) and Click on the “Edit” (Edit) tab. Uncheck the option “Move selection after Enter ‘ (Move selection after Enter) and click OK.
To delete the contents of a cell, select the cell and press the Delete key.
You can enter different data
Text is automatically left aligned, numbers are automatically right-aligned, negative numbers can be entered by a ‘-‘ sign before the number, you can enter a percentage by typing percentage sign after the number, use the dot as the decimal separator.
If you want to enter a time, you can use the colon between the hours, minutes and seconds eg. 10:30:15
If you enter a date, enter it with a slash or a hyphen as in day / month / year or day-month-year. Never use points between day, month and year because Excel will not recognize it as a date. If you want to enter the current date, press on ctrl key and the ‘colon / semicolon’ key.
Any combination of numbers, spaces and non numerical data are interpreted by Excel as text.
Each cell can contain up to 255 characters, the text just runs into the blank cells on the right side.
If there is already data in the cell on the right side, then the text gets broken. To see the full text in the cell, we increase the column width by double clicking the right dividing line of the column header.
You can adjust several coloumns at the same time:
- Select the columns
- Click on the “coloumn” option on the (Format) menu.
- Choose the command ‘AutoFit Selection’ (AutoFit selection).
All selected columns are automatically made wide enough for the widest cell in each column selected
Excel ’95 and ’97 have the auto-complete function, that is when we type the word ‘computer’ in an empty cell and press Enter. If we type the letter ‘c’ in the below column, Excel will automatically fill the word ‘computer’ again. If this is not the word that you want to type, you just continue to type.
You can set or remove the auto-complete feature using this: Click the Tools menu (Tools) and choose ‘Options’ (Options), Click on the ‘Edit Tab’ (Edit) and click on the checkbox ‘Enable AutoComplete for cell values’ (Enable AutoComplete for cell values) . Click again to remove option and the checkmark disappears.
Data can be easily copied within a row or column :
In cell A8 type eg. ‘Computer’. Select cell A8, and move the mouse pointer to the bottom right corner until a black sign of the cross appers, this is the fill handle, drag the fill handle across the cells B8, C8 and E8, and the word ‘computer’ is copied to the these cells.
The ‘AutoFill’ option can also be used to make time series :
- Type in cell A1: 9hrs
- Then in cell B1: Monday
- Then in cell C1: January
- Select the three cells
- Take down and drag the fill handle down
- The values will increase with one unit
If you want the units to increase by more than 1 unit, type the second cell in the column in the unit you want to use for automatic eg transit.:
- Type the data in the row below
- Select the six cells
- Drag the fill handle down
- The values are increased with 2 units
The difference between the data from rows 1 and 2 determine the interval at which the data is automatically incremented
For the passage of time series, we have more examples:
- Eg type today’s date in cell B8 by clicking on the shortcut ‘Control’ ‘colon / semicolon’ and press Enter
- Select the cell B8 again
- Take the fill handle and drag it with the RIGHT mouse button over the cells where you want to auto fill the date and a dialog box appear. Here you can specify which value you want to increment: date, months or years.
Also, you can quickly make linear ranges:
- In cell B19 enter the value 15
- In cell B20 enter the value 16
- Select the cells B19 and B20 and hold the fill handle and drag down
The linear array is thus increased by a value in the interval, this interval value can be changed to your needs.
For growth trends, the values are multiplied by a constant factor:
- In cell F19 enter the number 3
- In cell F20 enter the number 6 (as a result of 2X3)
- Select the cells F19 & F20
- Take the fill handle and drag it once with the RIGHT mouse button down
- In the dialog choose the option ‘growth trend’ and you get the growth series 3-6-12-24-48 etc. for 3×2 = 6, 6×2 = 12, 12×2 = 24, etc.
This growth trend with an interval value of X2
- Select select ‘linear trend’ in the dialog box, then you get the growth series for 3-6-9-12-15-18 3×2 = 6 3×3 = 9, 3×4 = 12 etc.
A combination of text and numbers can also be easily implemented:
- In cell A31 type period 1
- In cell B31 type region 1
- Select both cells, drag the fill handle with the right mouse butto
If you frequently need the same lists in Excel you can create your own custom sets, so it is always useful to open an existing set again as it would automatically fill in:
- Select the range of existing regions A1 to A5
- Open the “Tools” menu
- Click on the ‘Options’
- In the dialog box click on the ‘Custom List’ tab
- Click below on the ‘Import’ option
- and press the OK button to close the dialog box
You can use this custom list for auto fill. Type in the first item in the series, select it, drag it with the fill handle and the list is automatically updated.
You can also create new lists for automatic fill for later use:
- Place the cursor in a blank cell on your worksheet
- Open the Tools menu (Tools)
- Select ‘Options’
- Choose the ‘Custom List’ (Custom list) tab
- Click in the “List entries” (List entries) and type any one of the desired items, separated by a comma and space
- When your list is complete, press the “Add” button (Add)
- click OK
Now you can use this list for auto-fill in all worksheets in all workbooks in Excel. Type the first item, and drag the fill handle.
When opening a new workbook you will find three worksheets at the bottom of the workbook: Sheet1, Sheet2, and Sheet 3
To the left of the worksheets you will find arrows, these buttons allow you to navigate through the different worksheets by clicking the arrows.
To slide through several spreadsheets, hold the Shift key while you click on one of the arrows.
If you click on the arrow with the RIGHT mouse button, you will see the list of worksheets in your workbook and you can quickly select the desired worksheet.
You can rename the worksheets:
Double-click on Sheet1, type in the name you want to give this worksheet eg January, and press ‘Enter’
You can delete worksheets
Click with the RIGHT mouse button on a worksheet and choose Delete (Delete). A dialog appears with the warning: this worksheet will be removed PERMANENTLY, click OK if this is desired.
You can also delete several worksheets at once:
Click on the first sheet that you wish to remove, hold the Shift key and move to the last worksheet, click the RIGHT mouse button and choose ‘Delete’ (Delete), Click OK.
You can also remove several non-adjacent worksheets at once.
Select the various worksheets while holding the Control key pressed, click the RIGHT mouse button and choose Delete (Delete), Click OK.
You can add a new sheet:
Click with the RIGHT mouse button on eg Sheet1, select Insert (Insert), A dialog box with different types of worksheets opens, select ‘worksheet’ (Worksheet) for the insertion of a plain sheet with rows and columns.
The new worksheet is always inserted to the left of the current sheet. You can 2drag it to the desired location.
You've completed Lesson 2 START NEXT LESSON