clait certificate - introduction to excel
TRANSCRIPT
-
8/3/2019 CLAIT Certificate - Introduction to Excel
1/13
Introduction to Excel for CLAIT Users
Excel Desktop
The Standard and the Formatting Toolbars , found just below the ExcelsMain Menu - as shown below, are displayed by default.
To display any other toolbars, click View Toolbars select a toolbar you want.
Alternatively, you can right-click anywhere within the toolbars area andselect a desired toolbar from the shortcut menu.
To find out what each button is used for, just hover the mouse pointer over the appropriate button and you will get a Screen Tip to help you.
To get even greater help with the features, press Shift + F1 and click onany toolbar icon, any desktop feature or any commands in pull downmenus or within dialogue boxes.
Worksheet tabs
Name box
Active cell
Formula BarScrollbuttons/arrows
Formattin Toolbar
Standard ToolbarMain Menu
Title Bar
A typical Excel worksheet consists of a table of cells, arranged in 65 536rows and 256 columns.
Columns maximum width is 255 characters and maximum height of rowsis 409 points.
Column and row labels reference each cell, so that the cell selectedabove is A1 this is also shown in the Name Box on the left hand side ofthe Formula toolbar.
Number sheets in a workbook is limited by the available memory of thecomputer.
A spreadsheet is limited to 16 undo levels.
Mato Pulji January 2003 Page 1 of 13
-
8/3/2019 CLAIT Certificate - Introduction to Excel
2/13
Introduction to Excel for CLAIT Users
Workbooks & Worksheets
A workbook is the file in which you work and store your data. As eachworkbook can contain many worksheets, you can organize various kinds ofrelated information in a single file.
A worksheet is a table unit within a file called workbook. You can enter and editdata on several worksheets simultaneously and perform calculations based ondata from multiple worksheets.
Sheet tabs are the names of the worksheets that appear on tabs at the bottomof the workbook window. To move from sheet to sheet, click the sheet tabs.
A spreadsheet is a worksheet/table consisting of columns and rows onto whichyou enter values and formulas to make calculations. Below is an example of a
Cell
spreadsheet :
s a single box in the spreadsheet at which a column and a row intersect.A cell is the smallest unit of the spreadsheet into which you enter information.A cell i
A cell
Mato Pulji January 2003 Page 2 of 13
-
8/3/2019 CLAIT Certificate - Introduction to Excel
3/13
Introduction to Excel for CLAIT UsersIntroduction to Excel for CLAIT Users
Mato Pulji January 2003 Page 3 of 13
Entering and Deleting Text, Numbers or Formulae
To Enter Text, a Number or a Formula
1. Click on the cell into which you wish to make the entry.
2. Type in the text, number or formula.
3. Accept the entry by:
i. Pressing Enter / Return .
ii. Pressing any of the arrow keys or the Tab key.iii. Clicking on the green tick in the Formula toolbar.
4. Remember that a Formula always starts with an equal sign = .
5. Note also that if the column is not wide enough to display all the text andthe next column is empty (as the sample below shows) the text will spillover into the next cells.
To Clear/Delete Contents, Formats or Comments from Cells
1. To clear the contents of a cell, select the cells, rows or columns and then do one of the following:
i. Press the Delete or Backspace key. (The cellcontents are removed but not cell formats or comments, if any).
ii. Click Edit Clear and then click All, Contents,Formats or Comments , depending what do youwant to clear from the selected area.
iii. Right-click on the cell and select Clear Contentsfrom the shortcut menu.
2. To delete cells, rows, or columns
i. Select the cells, rows, or columns you want todelete, then click Edit Delete or
ii. Right-click rows, columns or selected area/cells and select Delete from the shortcut menu. Surrounding cells shift to fill the space.
Mato Pulji January 2003 Page 3 of 13
-
8/3/2019 CLAIT Certificate - Introduction to Excel
4/13
Introduction to Excel for CLAIT Users
Columns
A column is a series of cells that goes down the spreadsheet . Each column hasa reference , which is a letter of the alphabet (A, B, C etc). The lettered or numbered gray area at the top of each column is the column heading or
column title.
Cha
3.
mno
Column A
nging the Column Width
Change the width of a column manually.
i. Point to the right border of a coluheading (an oval cross will change t
a solid cross ).ii. After an oval cross changes to a soli
cross, left-press (Excel will give yoreadout of a present width!) and dra
Change the width of a column by doubl
du a
g to a desired width.
4. e-clicking
n heading
the column heading right border.
i. Point to the right border of a colum(an oval cross will change to a solid cross ).
Double-click the column heading border.ii.
5. Change the width of a column through the Excels
i. Select the cell with thewidth value you want
djust the width ofa column, e.g. in our case A6.
ose Format Column AutoFitSelection .
Main Menu .
to a
ii. Cho
Mato Pulji January 2003 Page 4 of 13
-
8/3/2019 CLAIT Certificate - Introduction to Excel
5/13
Introduction to Excel for CLAIT Users
Rows
A row is a series of cells extending across the spreadsheet . Each row isreferenced by a number (1,2,3 etc). Each row has the row heading or row title
Changi width except that
Row 3
Row Height
ng row height is done in the similar way as columng ick, hot ro
To ch lar heig
i. Select rows the height of which you want to
ii.
iii.
you o to the bottom of the row heading and cl ld and drag its border until
1. ht:
change.
he w is the height you need.
ange all or some of the rows to a particu
Choose Format Row Height
Type a new value in the Row height box.
iv. Click OK.
Mato Pulji January 2003 Page 5 of 13
-
8/3/2019 CLAIT Certificate - Introduction to Excel
6/13
Introduction to Excel for CLAIT Users
Worksheet Formatting
To Format Numbers
1. As General format, select rows, columns or range of cells, then:
i. Choose Format Cells.
ii. Under Category select General (cells have no specific number format) OK .
2. As Numbers(with decimals),select cells,rows/columns,then:
i. Choose
Format Cells.
ii. Under Category
tNumber
places
3. In
decimals),
at Cells.
ii. Under Category select Number reduce the number of decimalplaces to zero OK .
4. As Currency (with a pound sign ), select cells, rows/columns, then:
i. Choose Format Cells.
ii. Under Category select Currency make sure the pound symbol isselected select number of decimal points, if required OK.
To Format Text
selec
select thenumber ofdecimal
OK .
As tegers(without
select cells, rows/columns, then:
i. Choose Form
Select the characters you want to format, and then click a button on theFormatting toolbar, just like in a word processing package.
Ali n Left Ali n Ri ht
Align Centre
Mato Pulji January 2003 Page 6 of 13
-
8/3/2019 CLAIT Certificate - Introduction to Excel
7/13
Introduction to Excel for CLAIT Users
To insert a Row/Column
tional row between rows 9 and 10, select any cell in row10 (always the greater of the two!), and then click on I
1. To insert an addinsert Rows .
ct any cells the greater of the two!), click on I
2. To insert an additional column between columns C and D, selensert in column D (remember alway
Columns .
3. a new column/row or a cell:
ow heading or cel l where you want
ii. shortcut menu .
g cells to make room for
iv.select Entire
n/row to insert a
right/down to insertonly a cell within thecolumn/row.
v. Click OK.
Alternatively, to insert
i. Simply right-click on the column/rto insert .
Choose insert from the
iii. If you click on a cell, you will be asked how youwant to move the existinthe new ones.
From the Insert shortcutmenucolumnew column/row or select Shift cells
Mato Pulji January 2003 Page 7 of 13
-
8/3/2019 CLAIT Certificate - Introduction to Excel
8/13
Introduction to Excel for CLAIT Users
To delete a Row/Column
letter
2. the row or column itself select the entire row or column byclicking on the row number or column letter (i.e. in the grey area) andclick on E
1. To delete the contents of a row or column (and leave empty cells) selectthe entire row or column by clicking on the row number or column(i.e. in the grey area as shown in the screenshot above for Column B) and
press the delete key.To delete
An a ay to delete the
ii.
iii. From the Delete boxr
n
iv.
dit on the Menu toolbar, then select Delete from the drop-downmenu.
3. lternative wrow or column:
i. Right-click on any cell in the row or column.
Select Delete from the shortcut menu.
choose Entire row oEntire column as showbelow.
Click OK
Mato Pulji January 2003 Page 8 of 13
-
8/3/2019 CLAIT Certificate - Introduction to Excel
9/13
Introduction to Excel for CLAIT Users
Spreadsheet Formulae
1. A Formula always starts with an equal sign =.
Formulae may include:
i. Numbers
2.
xt or nothing within quotation marks, e.g. Well Done! or .
3. Function names, e.g. SUM, Product, Power
4. Formula examples here are a few examples of what you can dowith a formula:
ii. Cell references
iii. Arithmetic operators (+, , etc)
iv. Te
To do this: Type this into a cell
Add cells B2 and C2 =B2 + C2
Multiply the contents of cell B3 by 4 =B3 * 4 or =4 * B3
Divide cell C4 by 5 =C4 / 5
Take cell B7 from C7 =B7 C7
Multiply cell D5 by cell B1 =D5 * B1
Multiply cells B5 + C5 by 2 =(B5 + C5) * 2
To raise cell C6 to the power 2 =C6 ^ 2
5. The order in which Excel calculates is given below:Order Operator Example
Brackets ( ) =(B5 + C5) * 2
Over or Exponentiation(to the power of)
^ =C6 ^ 2
/ =C4 / 5Division andM * =D5 * B1ultiplication (left to right)
+ =B2 +ddition and C2A
S =B2 C2ubtraction (left to right)
6. wor co dby:
i. g a cell for the formula
he AutoSum icon
iii. Adjusting the range ifnecessary
iv. And pressing the Enter/Returnkey.
A Formula to sum the cells in a rolumn can be quickly create
Selectin
ii. Clicking on t
Mato Pulji January 2003 Page 9 of 13
-
8/3/2019 CLAIT Certificate - Introduction to Excel
10/13
Introduction to Excel for CLAIT Users
To Replicate (Copy) the Contents of a Cell
Method 1: Using Copy Paste action
7. from one cell to other adjacent or non-adjacent cells,copied (by clicking on it), then:
y OR
To copy a Formulaselect the cell to be
i. Choose Edit Cop
ii. Click on the Copy icon on th e Standard toolbar OR
iii. Right-click on the cell to be copied and select Copy from theshortcut menu.
8. Select the cell(s) into which the formula is to be copied, then:
i. Click Edit Paste OR
n the Paste iconii. Click o
iii. Right-click on th choose Pamenu.
Finally press the Esca inate the
thod 2: Using the Cells
OR
e selection and ste from the shortcut
9. pe key [ Esc ] to term copying process.
Me Fill Handle
Whe Excel, the bottom right hand corner of the cell willcon e, called fill handle that y can us the contentsof th djacent) cells.
Rem contains the formula, onl e form opied,
othe ill be copiedula to be copied.
2. handle until the mouse pointer (ovals) changes to a solid cross.
3. s ss the cells into which you want tocopy ula.
4.
5.
n you select a cell intain a tiny squar ou e to copye cell to other neighbouring (a
ember, if a cell y th ula will be c
rwise the value of the cell w .1. Click on the cell that contains the form
Hover the mouse pointer over the fillcros
Pres the mouse button and drag acrothe form
Release the mouse button.
Click anywhere to deselect the cells.
Mato Pulji January 2003 Page 10 of 13
-
8/3/2019 CLAIT Certificate - Introduction to Excel
11/13
Introduction to Excel for CLAIT UsersIntroduction to Excel for CLAIT Users
Mato Pulji January 2003 Page 11 of 13
To Preview and Print your Spreadsheet
Before printing, check with thePrint Preview facility that your spreadsheet will print as
intended.
1. Double click the Sheettab and give it ameaningful name.
2. Click on the Print Previewicon.
3. Make sure that thePreview status bar indicates that your
spreadsheet occupiesonly one page.
4. You may change PageOrientation and/or click on Fit to 1 page(s) wideby 1 page tall optionbox.
5. On the Sheet tab youcan select gridlines to beprinted for better
readability.6. Under the Header/Footer
tab, in the Footer youmay add your name,centre number, dateand print no, among other details.
7. Click Ok and OK again, when finished, and print your spreadsheet.
8. As the next printout will incorporate changes to the spreadsheet, make a
copy of the Sheet tab and work on the copy. Ask your tutor to show youhow to accomplish that.
Mato Pulji January 2003 Page 11 of 13
-
8/3/2019 CLAIT Certificate - Introduction to Excel
12/13
Introduction to Excel for CLAIT Users
To View / Print Spreadsheet Formulae
To display and print the formulae you havefollowing steps:
used in the spreadsheet, folow the
1. Click on Tools on the Menu Bar.Click on Options from the dropthen appear:
2. down menu. The following window should
3. Make sure the View tab is at the front by clicking on it.
4. Click on the box next to Formulas - a tick should appear in it.
5. Click on OK.
6. It may be necessary to increase the width of the column containinformulae in order to display them correctly.
g the
7. Use Print Preview Page Setup to fit the printout to 1 page.
Mato Pulji January 2003 Page 12 of 13
-
8/3/2019 CLAIT Certificate - Introduction to Excel
13/13
Introduction to Excel for CLAIT Users
Mato Pulji January 2003 Page 13 of 13
Some Useful Keyboard Shortcuts
Press To
Arrow keys Move one cell up, down, left, or right
HOME Move to the beginning of the row
CTRL e to the beginning of the worksheet+HOME Mov
END, Move to the end of the rowHOME
CTRL Move to the last cell on the worksheet+END
ENTER Comselec
plete a cell entry and move down in thetion
ALT+E a new line in the same cellNTER StartTAB Complete a cell entry and move to the right in
e selectionth
ESC ancel a cell entryC
BACK E Delete the character to the left of the insertionpoint, or delete the selection
SPAC
DELET elete the character to the right of the insertionor delete the selection
E Dpoint,
= (equal sign) Start a formula
CTRL er the date+; (semicolon) Ent
CTRLmark
nate between displaying cell values and+` (single left quotation)
Alter displaying cell formulas
CTRL+A Select All
CTRL+C Copy the selection
CTRL+B Bold
CTRL+I Italics
CTRL+V Paste
CTRL+X Cut
CTRL+Z Undo