Spreadsheet Notes January 2013 Page 1 of 32

Table of Contents
Contents Page
SPREADSHEETS ................................................................................................................................................ 2

WHAT IS A SPREADSHEET? ........................................................................................................................................ 2

DIFFERENT PARTS OF EXCEL WINDOW ............................................................................................................. 3

SAVING A WORKBOOK .................................................................................................................................... 3

SELECTING ENTIRE ROW .................................................................................................................................. 4

SELECTING ENTIRE ROW COLUMN ................................................................................................................... 4

CUTTING AND COPYING TEXT FOR PASTING .................................................................................................... 4

USING THE FIND TOOL ............................................................................................................................................. 6

STEPS TO USE FIND TOOL .......................................................................................................................................... 6
USING THE SPELLING CHECKER ................................................................................................................................... 7
USING THE HELP FEATURE ........................................................................................................................................ 9

CUSTOMISING WORKSHEETS ........................................................................................................................... 9

MODIFYING ROWS AND COLUMNS ............................................................................................................................. 9

FORMATTING TEXT ................................................................................................................................................ 12
FORMATTING TEXT LAYOUT..................................................................................................................................... 13
MANAGING WORKSHEETS ...................................................................................................................................... 15

CUSTOMISING PRINTING ............................................................................................................................... 16

MODIFYING A PAGE LAYOUT ................................................................................................................................... 16

PORTRAIT ORIENTATION ......................................................................................................................................... 16

ADDING HEADERS AND FOOTERS .................................................................................................................. 17

PRINTING A WORKSHEET ........................................................................................................................................ 17

WORKING WITH FORMULAS .......................................................................................................................... 18

INSERTING A FORMULA........................................................................................................................................... 18
SPECIFYING A FUNCTION ......................................................................................................................................... 21

INSERTING ROWS .......................................................................................................................................... 27

INSERTING COLUMNS .................................................................................................................................... 27

FIND MAXIMUM (MAX) ................................................................................................................................. 27

FIND MINIMUM (MIN) ................................................................................................................................... 27

PUTTING BORDERS TO SELECTED CELLS ...................................................................................................................... 27

WORKING WITH CHARTS ............................................................................................................................... 28

CREATING A CHART ............................................................................................................................................... 28

CHANGING SERIES FOR THE BAR CHART ........................................................................................................ 28

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 2 of 32

CREATING A PIE CHART WITHIN A WORKSHEET ............................................................................................. 29

CREATING A PIE CHART ON A NEW WORKSHEET ........................................................................................... 29

MODIFYING A CHART ............................................................................................................................................. 30

CHANGING CHART TYPE ......................................................................................................................................... 30

Spreadsheets
What is a Spreadsheet?
A spreadsheet is a document that stores data in a grid of horizontal rows and
vertical columns. Rows are typically labelled using numbers (1, 2, 3, etc.), while
columns are labelled with letters (A, B, C, etc). Individual row and column locations,
such as C3 or B12, are referred to as cells.

Spreadsheets allow you to use columns and rows to organize data, make
mathematical calculations, present charts and perform complex functions. Microsoft
Excel is an example of a spreadsheet.
Excel spreadsheets simplify what would take hours, or even days, to complete
manually on paper.

Cell – is the box where a row and column meet. You identify a cell using a combination of
the column letter and row number, e.g., B3

Charts
- are the graphics used for pictorial display of the relationships between sets of data.

How to open Excel in Windows XP

1. Click Start button on the task bar

2. Click on All Programs
3. Click on Microsoft Office
4. Click on Microsoft Office Excel 2007 on the Microsoft Office drop-down
list
Once you open Excel, a blank file appears in the Excel window. A file in Excel is called a workbook
and the worksheet consists of worksheets.

Workbook
- is a file that contains a number of related worksheets.

Worksheet
- is a collection of rows and columns.

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 3 of 32

- The rows are numbered and the columns are identified using alphabetic letters. The data is
entered in cells.

You can organise data in different worksheets of the same workbook for easy reference.

Different parts of Excel Window

Quick Access Toolbar – used to perform common tasks such as printing and saving a
workbook.

Title Bar – it displays the name of the workbook with which you are working.

Ribbon – is designed to help you quickly find the command that you need to perform
tasks.

Sheet Tabs can move from one worksheet to another by clicking the sheet tab.

Entering text, numbers and dates in cells

- You can enter text in numbers in cells and move the active cell from one cell to another using a
mouse and arrow keys.

Saving a Workbook
1. Click the Save button on the Quick Access toolbar or click on Office Button then move to
save and click
2. Here you can specify the folder in which you want to save the file, e.g. My Document or
Documents
3. Type the file name related to the typed text
4. Click Save button

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 4 of 32

Closing a Workbook

1. Click the Close Window button in the upper-right corner on the Ribbon

Opening an existing workbook

1. Open Microsoft Excel application

2. Click Microsoft Office button at the upper- left corner of your screen
3. Select Open from the Office Button menu that appears
4. From the list of workbooks that appears in the Open dialog box, select the workbook to be
opened
5. Click Open button to open the selected workbook

Multiple selection of non-adjacent cells or groups of cells

- Can be done by depressing Ctrl then click on cells to be selected

Selecting entire row

- Select the entire row by clicking the row header e.g. 2

Selecting entire row column

- Select the entire column by clicking the column header e.g. B

Cutting and copying text for pasting

 When you cut text it means you are removing text from the original position to the new
position

 When you copy text it means you are duplicating text to new position and leaving the
original text where is was before

Copying a cell’s contents within a worksheet

1. Select the cell to be copied

2. Click Copy button
3. Select the cell where you want to paste the copied contents
4. Click Paste button in the Clipboard group on the Home tab

Copying cell contents from Sheet1 to Sheet2 of the same Workbook

1. Open the Worksheet

2. Make sure Sheet1 is the current worksheet
3. Select the cell with contents to be copied
4. Click Copy button in the Clipboard group on the Home tab
5. Click on Sheet2
6. Click the cell where you want to paste the copied contents to select it

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 5 of 32

7. Click Paste button in the Clipboard group on the Home page

Copying Data Across Workbooks

1. Select text to be copied in a workbook

2. Click Copy button in the Clipboard group on the Home tab
3. Open the workbook in which you want to paste the copied text. Click Microsoft Office
Button
4. Click Open on the Office menu
5. Open the folder where your workbook is
6. Click the workbook to select it
7. Click Open button to open the selected workbook
8. Click the row header in this worksheet where you want to paste the copied text
9. Click Paste button in the Clipboard group on the Home tab

Cutting a cell’s contents within a worksheet

5. Select the cell to be copied

6. Click Cut button
7. Select the cell where you want to paste the copied contents
8. Click Paste button in the Clipboard group on the Home tab

Cutting cell contents from Sheet1 to Sheet2 of the same Workbook

8. Open the Worksheet

9. Make sure Sheet1 is the current worksheet
10. Select the cell with contents to be copied
11. Click Cut button in the Clipboard group on the Home tab
12. Click on Sheet2
13. Click the cell where you want to paste the copied contents to select it
14. Click Paste button in the Clipboard group on the Home page

Cutting data across Workbooks

10. Select text to be copied in a workbook

11. Click Cut button in the Clipboard group on the Home tab
12. Open the workbook in which you want to paste the copied text. Click Microsoft Office
Button
13. Click Open on the Office menu
14. Open the folder where your workbook is
15. Click the workbook to select it
16. Click Open button to open the selected workbook
17. Click the row header in this worksheet where you want to paste the copied text
18. Click Paste button in the Clipboard group on the Home tab

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 6 of 32

Sorting is the process of organising data in ascending or descending organising.

Sorting Data

1. Select the cells with the data you to be sorted

2. Click Sort & Filter command in the Editing group on the Home tab
3. Click Custom Sort on the drop-down list
4. Clock Sort by arrow to choose what to sort by e.g. Surname, Average etc.
5. Click on the field to sort by e.g. Surname
6. Click the Order arrow to choose the sorting order e.g. A to Z (ascending order) or Z to A
(descending order)
7. Click on A to Z to arrange the data in ascending order
8. Click OK button on the Sort dialog box

You can sort the letters or numbers in ascending order (A to Z) or descending order (Z to A) by
clicking Sort & Filter command then click on A to Z or Z to A from the Sort & Filter drop-down list.

Fill handle
A fill handle is a black cross at the lower-right corner of the selected cells. It is used to fill series in
cells.
You can fill a serries by selecting cells and dragging the fill handle. Once you point to the fill handle,
the mouse pointer changes to a black cross. Using this black cross, you can fill a series of different
data types such as numbers and dates.
It is good practice to avoid blank rows and columns within a table

Save As option is used to save an existing workbook in different format, name or location.

Using “Save As” option

1. Click Microsoft Office Button
2. Click Save As on the Office menu
3. Click down arrow of the Save as type field, to display a list of available file formats
4. Click the Excel 97-2003 option from the displayed list
5. Click the Save button to save the workbook in the selected Excel 97-2003 workbook file
format

Using the Find Tool

Finding a Specific Number or text

The Find tool allows you to locate numbers and text in a worksheet quickly and easily. You can
search for characters, numbers, words and phrases in a workbook.

Steps to use Find Tool

1. Click the Find command in the Editing group on the Home tab
2. Click the Find option from the drop-down list displayed
3. Specify the value that you want to search e.g. 100, in the Find and Replace dialog box
4. Click Find Next button to search for the value

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 7 of 32

 The first occurrence of the number is highlighted in the workbook. To find each instance of
the number you cold keep clicking the Find Next button.
 You can use the Find tool to find words and numbers quickly in a worksheet. You can also
use this tool to replace a word or number with another word or number.

Replacing all occurrences of a certain number by another number, e.g. 100 by 125

1. The Price List workbook must be open

2. Click the Find command in the Editing group on the Home tab
3. Click the Find option from the drop-down list displayed
4. Specify the word or number that you want to replace e.g. 100, in the Find what text box
5. Click the Replace tab
6. Type 125 in the Replace with text box
7. Click the Replace All button
8. Click OK, to close the alert window that displays the number of replacements made in the
document
9. Click Close button, to close the Find and Replace dialog box

All the occurrences of the price 100 are replaced with 125.

Using the Spelling Checker

Checking the Spelling and Grammar

1. Type the workbook and save it

2. Click the Spelling & Grammar command in the Proofing group on the Review tab
3. Click Change button to replace the incorrect word with the suggested correct word
displayed. You can click the Change All button if the same word is misspelled throughout the
worksheet and you want to fix all of these the same time.
4. Click OK button to close the alert window, when the spelling checker tool has checked the
entire workbook

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 8 of 32

Checking formulas in the displayed worksheet

1. Click Error checking command in the Formula Auditing group on the Formulas tab

2. Click the Edit in Formula Bar button to correct the formula

3. Delete the value 0(zero) in the formula bar

4. Enter the value 100 in the Formula to replace it
5. Press Enter key

6. Click Resume button on the Error Checking dialog box

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 9 of 32

7. Click OK button to close the alert window

You have fixed the formula error in the worksheet.

Using the Help Feature

Steps for using the Help Feature

1. Click Microsoft Office Excel Help button at the right top corner of the window
2. Type how to copy data in the search box
3. Click Search button
4. Click move or copy cells and cell contents option from the list of available topics

Customising Worksheets
Modifying Rows and Columns
Inserting a column to the left of D

1. Type the following in the workbook and save as Expenses

2. Select the column D by clicking the column heading of column D

3. Click the down arrow next to the Insert command on the Home tab, in the Cells group
4. Click the Insert Sheet Columns option from the drop down list

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 10 of 32

NB Just as you have inserted a column, you can also insert a row, by selecting the row and using the
Insert Sheet Rows option in the Cells group.

Deleting a row

1. Continue using the Expenses workbook bellow:

2. Click the row heading of row 5 to select it

3. Click the down arrow next to the Delete command, on the Home tab in the Cells group
4. Click the Delete Sheet Rows option from the drop-down list displayed

Deleting a column

1. Continue using the Expenses workbook

2. Click the column heading of column C to select it
3. Click the down arrow next to the Delete command, on the Home tab in the Cells group
4. Click the Delete Sheet Columns option from the drop-down list displayed

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 11 of 32

Increasing Column width

1. Move the mouse pointer to the right margin of the selected column heading
2. Drag the mouse pointer to increase the column width

Adjusting row height

1. Place the mouse pointer on the lower edge of the row heading where the row number is
written
2. When the mouse pointer changes to a double headed arrow, drag the arrow to the desired
position

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 12 of 32

Formatting Text

Formatting selected cell content

1. Select the cells to be formatted

2. Click the down arrow next to Font command in the Font group on the Home tab
3. Scroll down the list of font, to locate Century Gothic in the list and click on it
4. Increase the font size to 12 by clicking the down arrow next to the Font Size command in
the Font group on the Home tab
5. Click 12 from the Font Size drop down list
6. To change the colour of the text. Click the down arrow next to the Font group on the Home
tab
7. Click Red square from the list of available font colours displayed
8. To change the selected text to Italics, Click the Italics command (I) in the Font group on the
Home tab

Applying the Bold style to selected cell or group of cells

1. Select a cell or group of cells

2. Click Bold (B) button to change text to Bold

Using “Fill Colour” tool to apply fill colour to the background of a cell

1. Select a range of cells to be filled with colour

2. Click the down arrow next to the Fill Colour command in the Font group on the Home tab to
choose a colour
3. Choose the desired colour e.g. light green option by clicking on it
4. De-select the cells by clicking outside the highlighted cells

The colour of text is not changed but only the background colour of the cells changed to light green.

Using “Format Cells” dialog box to add a wider range of effects

An example is applying a Double Underline

Applying a Double Underline

1. Click the cell with contents to be double underlined to select it
2. Click the down arrow next to the Format command in the Cells group on the Home tab
3. Click the Format Cells option from the drop-down list
4. Click the down-arrow in the Underline section
5. Click on Underline Accounting on the “underline styles” options available
6. Click OK button
7. De-select the highlighted cell by clicking outside it

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 13 of 32

You have applied a double underline style to a cell.

Changing the number format of the numeric data

1. Select the Sales, Total Expenditure and Profit amounts in the Expenses workbook
2. Click the down-arrow next to the Format command in the Cells group on the Home tab
3. Click the Format Cells… option
4. Click Number in the Category box
5. Click the down-arrow of the Decimal places: box and set decimal place to one(1)
6. Click the OK button to apply the format

Applying currency formats to numeric data

1. Select the Sales, Total Expenditure and Profit amounts in the Expenses workbook
2. Click the down-arrow next to the Format command in the Cells group on the Home tab
3. Click the Format Cells… option
4. Click Currency in the Category box
5. Click Symbol down-arrow
6. Scroll down the list to click on the desired currency e.g. £ English (United Kingdom)
7. Click OK to apply the pound (£) symbol

Applying Percentage formats to numeric data

1. Select the cells with numbers to have a percentage (%) sign in the worksheet
2. Click the down-arrow next to the Format command in the Cells group on the Home tab
3. Click the Format Cells… option
4. Click Percentage in the Category box
5. Click down-arrow for Decimal places: to decrease the decimal place to zero (0)
6. Click OK to apply the Percentage (%) symbol

Applying Date formats to numeric data

1. Select the cell with the date to be changed from 04/09/2008 to 14-Mar-01 format in the
worksheet
2. Click the down-arrow next to the Format command in the Cells group on the Home tab
3. Click the Format Cells… option
4. Click Date in the Category box
5. Scroll the Type: arrow to click on the desired date format e.g. 14-Mar-01
6. Click OK to apply the desired date format

Formatting Text Layout

The types of alignments are:

 Left Alignment

 Right Alignment

 Centre Alignment

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 14 of 32

 Justify Alignment

Horizontally align the data to the right within the selected cell

1. Select text to be aligned right

2. Click the down arrow next to the Format command in the Cells group on the Home tab
3. Click Format Cells option in the displayed list
4. Click the Alignment tab in the Format Cells dialog box
5. Click the down arrow of the Horizontal box
6. Click the Right option in the list of Horizontal alignment options displayed
7. Click the OK button to apply the alignment option to the text in the selected cells

Freezing rows and column

You can freeze rows and columns to fix certain topics so that you can always see them all the time.

Freezing rows and column

1. Select the cells you want to scroll, leaving the headings to remain fixed while scrolling
2. Click View tab
3. Click Freeze Panes on the Windows group on the View tab
4. Click Freeze Panes from the Freeze Panes displayed list

You can also unfreeze rows and columns in excel to stop the action of freezing rows and columns

Unfreezing rows and column

1. Select the cells you had selected in order to freeze the panes
2. Click View tab
3. Click Freeze Panes on the Windows group on the View tab
4. Click Unfreeze Panes from the Freeze Panes displayed list

Applying the “Wrap text” Feature

1. Type “Zvakanaka zvakadaro” in Cell A2

2. Click Cell A2 to select it
3. Click the down-arrow next to the Format command in the Cells group on the Home tab
4. Click the Format Cells… option from the displayed list
5. Click Alignment tab
6. Click the Wrap text checkbox in the Text Control group
7. Click OK button

After applying the Wrap text the cell contains the additional text that was not visible when the Wrap
text option was not applied. You can even apply the Wrap text option to a range of selected cells.

Orientation
- Is the direction in which a worksheet is printed on a page. You can print a worksheet either
horizontally or vertically.

Adding Borders to selected cells

1. Select the cells to have the border

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 15 of 32

2. Click the down arrow next to the Borders command in the Font group on the Home tab
3. Click the Outside Border option to apply a border outline to the selected cells

Applying a Border Style

1. Select Cell(s) that you wish to enclose within the border

2. Click the down arrow next to the Borders command in the Font group on the Home tab
3. Select the More Borders… option from the displayed list
4. Choose the appropriate line style and colour from the Style and Colour sections
5. Select the appropriate options from the Presets and Border sections to indicate the border
placement

Applying Formatting on other text

1. Click on the cell in which you want to copy the format

2. Click the Format Painter command in the Clipboard group on the Home tab
3. Click on the cell in which you want to apply the copied format

Using the Zoom Tool

1. Click Zoom command in the Zoom group on the View tab

2. Click 200% to increase the magnification of the worksheet on the screen
3. Click OK button

Alternatively, you can use Zoom controls in the status bar at the bottom of the window.

Managing Worksheets
Deleting a Worksheet

1. Click the sheet to be deleted, to select it

2. Click the down arrow next to Delete command in the Cells group on the Home tab
3. Select the Delete Sheet option from the displayed list

Moving current worksheet (Sheet1) to a new position

1. Click Sheet1 to select it

2. Click the Down arrow next to the Format command in the Cells group on the Home tab
3. Click the Move or Copy Sheet… option
4. Select Sheet3
5. Click OK button

Creating a copy of the Worksheet

1. Click the down arrow next to the Format command in the Cells group on the Home tab
2. Click the Move or Copy Sheet… option from the displayed list
3. Click the Sheet3 option
4. Click the Create a Copy checkbox
5. Click the OK button to create the duplicate Worksheet and position it in front of Sheet3

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 16 of 32

Renaming a Worksheet

1. Double-click a Worksheet name to be renamed

2. Type the new name e.g. Marks

Customising Printing
Modifying a Page Layout
Preview a Worksheet
- Preview feature allows you to view a worksheet as It will appear in print.
Preview a Worksheet

1. Click the Microsoft Office button on the upper-left corner of the window
2. Click Print option in the Office menu
3. Click Print Preview
4. Close Print Preview to go back to the original document

How to set column and row titles to appear on every printed page of the worksheet
1. Click Page Layout tab
2. Click the arrow at the bottom of the Page Setup group
3. Click on the Sheet tab
4. Click on the Rows to Repeat icon
5. Click anywhere on Rows 1 to select it
6. Press the Enter key on the keyboard
7. Click on the Columns to Repeat icon
8. Click anywhere on Column A to select it
9. Press Enter
10. Click the Gridlines checkbox to print gridlines
11. Click the OK button

Orientation of a Worksheet

Portrait orientation
Landscape orientation
1. Click the Page Layout tab
2. Click the arrow at the bottom of the Page Setup group
3. Click Landscape option
4. Click OK button to apply the changes

Changing the margins of the displayed worksheet

1. Click Margins tab using the Page Setup dialog box
2. Click up arrow of the Left box
3. Click the up arrow of the Right box to increase the margin by 0.5 centimetres
4. Click OK button to apply the settings

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 17 of 32

Alternatively, you can also change the margins by clicking the Margins command in the Page Setup
under the Page Layout tab.

Adding Headers and Footers

1. Click Page Layout tab
2. Click Expander arrow of the Page Setup group
3. Click on the Header/Footer tab in the Page Setup dialog box
4. Click on the Custom Header… button
5. Select the Centre section by clicking on it to add the name of the Workbook in the centre
section of the header
6. Click on the Insert File Name button to automatically place the name of the current
Workbook into the centre section
7. Click the Insert Sheet Name button to automatically place the name of the current
Worksheet in the Centre section
8. Click OK button to finish the process for addition of the header
9. Click the Custom Footer…button
10. Click on the Left section to select it
11. Click on the Insert Time button to automatically place the current time in the left section
12. Click on the Centre section to select it
13. Click on the Insert Date button to automatically place the current date in the centre section
14. Click the Right section to select it
15. Click the Insert Page Number button to automatically place the current page number in the
right section
16. Click the OK button to complete the addition of the Footer to the worksheet
17. Click the Print Preview button to see how the entries of the Header and Footer will appear
when printed

Alternatively, you can click the Insert tab and select the Header & Footer command in the Text
group to add Header & Footer to your worksheet.

How to modify the Header and Footer of the Worksheet

1. Click the Page Layout tab
2. Click the arrow at the bottom of the Page Setup group
3. Click on the Header/Footer tab in the Page Setup dialog box
4. Click the Custom Header… button
5. Type the word Confidential in the Left section
6. Click OK button in the bottom right corner
7. Click the Custom Footer… button
8. Press the Delete key on the keyboard to remove the Time entry from the Left section of the
Footer
9. Click OK button
10. Click the Print Preview button to see how the worksheet will look like when we print

Printing a Worksheet
Printing multiple copies of a Worksheet

1. Click Microsoft Office button

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 18 of 32

2. Click Print option in the Office menu

3. Type the number 4 in the box to specify the number of copies you want to print in the
Number of copies box
4. Click OK button

Printing a range of cells on your Worksheet

1. Type and save the above Worksheet

2. Select cells to be printed by clicking and dragging to select the desired cells
3. Click Microsoft Office button
4. Click Print option in the Office menu
5. Choose the Selection option in the Print what dialog box
6. Click OK button

Working with Formulas

Inserting a Formula
Formulas in Excel

In Excel the formula is started by typing an = sign.

An equal “=” sign inform excel that you are entering a formula.
Following are examples of formulas:
i. Addition: =(A2+A3+A4+A5) or =(A2:A5) or =sum(A2:A5)
ii. Subtraction: =(A2+A3+A4+A5)-25 or =(A2:A5) -25 or =sum(A2:A5) -25
iii. Multiplication: = A2*A3
iv. Division: = A2/A3

To type the formula, click the cell in which you want the result to be then type the formula and press
Enter key on the keyboard.

Copying formulas to other cells

1. Click the cell with the formula to select it

2. Click Copy command in the Clipboard group on the Home tab to copy the formula
3. Click the cell where you want the formula to be pasted
4. Click Paste button to paste the formula
OR
1. Click the cell with the formula
2. Point on the Fill handle where the mouse pointer changes from outline cross to dark cross
3. Click and drag the mouse up to the cell where the formulas are to be copied

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 19 of 32

Absolute cell referencing

Absolute cell referencing

1. Click the cell where you have to type the formula e.g. cell F4

2. Type =C4*D$10
3. Press Enter key

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 20 of 32

4. Click cell F4 where the formula to be copied is

5. Click the Copy button in the Clipboard group on the Home tab
6. Select the cells where the copied formula is to be pasted
7. Click the Paste button to paste the copied formulas

Fully Absolute Referencing

Fully Absolute Referencing

#Value! Error: Addition of Text Value and a Number

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 21 of 32

Specifying a Function

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 22 of 32

Functions

Calculating student results

1. Click the cell you want the Total Marks for the student to appear e.g. cell G2

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 23 of 32

2. Click the AutoSum command in the Editing group on the Home tab

3. Press the Enter key to see the total marks for the first student

Alternative path

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 24 of 32

Range

Calculating the average

1. Click cell where you want the average result to appear, to select it
2. Type =AVERAGE(G5:G11) to calculate the average for numerical values from G5 through G11
3. Press the Enter key to see the average

Alternative path

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 25 of 32

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 26 of 32

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 27 of 32

Functions in Excel

Inserting rows
1. Position the active cell in the row in which you want the row to be inserted above it
2. Click Insert on the Cells group
3. Click on Insert Sheet Rows on the drop-down list

Inserting columns
1. Position the active cell in the column in which you want the column to be inserted on your
left
2. Click Insert on the Cells group
3. Click on Insert Sheet Columns on the drop-down list

Find Maximum (MAX)

1. Select the Column or Row including a blank cell where the Maximum Number is to appear
2. Click AutoSum arrow
3. Click Max for the Maximum number to appear in the extra selected cell for the Row or
Column

Find Minimum (MIN)

1. Select the Column or Row including a blank cell where the Minimum Number is to appear
2. Click AutoSum arrow
3. Click Min for the Minimum number to appear in the extra selected cell for the Row or
Column

Putting Borders to selected cells

1. Select the cells to have borders
2. Click Format on the Cells group on the Home tab
3. Click on Format Cells… on the popped up menu
4. Click Border tab on Format Cells dialog box
5. Click Outline and Inside on Presets to apply borders to selected cells
6. Click OK on the Format Cells dialog box

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 28 of 32

Working with Charts

Creating a Chart
Charts

Creating a Column Chart

1. Select the data that you want to present graphically
2. Click Insert tab on the Home tab
3. Click the Column command in the Charts group
4. Click the desired Column in the displayed list. The chart is inserted and you can label it
5. Click the Layout tab to reveal the corresponding Ribbon
6. Click Data Labels in the Labels group
7. Click on More Label Options….
8. Tick/check the desired label options e.g. Value
9. Click Close button on the Format Data Labels dialog box
10. Click Chart Title command in the Labels group on the Layout tab, to add the title for the
chart
11. Select the Above Chart option in the displayed list
12. Click in the text box labelled Chart Title and delete the text the type the desired Chart Title
13. Click Axis Titles command in the Labels group on the Layout tab, to add the label for the X-
axis
14. Select the Primary Horizontal Axis Title from the displayed list
15. Click Title Below Axis option
16. Click on the Axis Title box that appears below the X-axis
17. Click the Axis Titles command in the Labels group on the Layout tab, to specify the title for
the Y-axis
18. Select Primary Vertical Axis Title from the displayed list
19. Click Rotated Title option in the displayed list
20. Click on the Axis Title box that appears next to the Y-axis

Changing Series for the Bar Chart

If the Legend (Key) has series1, series2 etc, change the series to meaningful Legend e.g. Test1, Test2,
etc. following the steps that follow:
1. Right-click the Legend for the created bar chart
2. Move to Select Data…. From the drop-down list and click
3. Select Series1 on the Select Data Source
4. Click on Edit tab on the Legend Entries (Series)
5. Type the name to replace series e.g. Test1 in the Series name: text box
6. Click OK on the Edit Series dialog box
7. Select Series “Series2” on Select Data Source
8. Click on Edit tab on the Legend Entries (Series)
9. Type the name to replace series e.g. Test2 in the Series name: text box
10. Click OK on the Edit Series dialog box
11. Click OK on the Select Data Source dialog box

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 29 of 32

Changing the “axis” scale of a chart

1. Click Y-axis of the chart to select it
2. Click the Layout tab on the Ribbon
3. Click the Axes command in the Axes group
4. Select the Primary Vertical Axis option to reveal the menu
5. Click More Primary Vertical Axis Options… to open the Format Axis dialog box
6. Click the radio button labelled Fixed in the Major Unit section
7. Click on the Major Unit text box and type 400, to replace 1000 with 400
8. Click Close button to close the Format Axis dialog box

Creating a pie chart within a worksheet

1. Select the text to be presented graphically
2. Click Insert tab on the Ribbon
3. Click the desired chart on Charts group e.g. pie
4. Select the desired pie from the drop-down list. The pie chart is inserted and you can label it
by proceeding with number 5 bellow
5. Click Layout tab on the Ribbon
6. Click Data Labels in the Labels group
7. Click on More Data Labels Option…
8. Tick/Check the desired Label options e.g. Percentage
9. Choose Label position e.g. Centre, Inside End etc.
10. Close the Format Data Labels dialog box by clicking Close on your far bottom right. You will
see the Percentages shown on the Pie Chart
11. Type the Chart Title by clicking on Layout tab
12. Click on Chart Title
13. Choose the position of the title e.g. Above Chart by clicking on it
14. Click on the text Chart Title in the text box then delete the text and type the desired title for
the Pie Chart

Creating a pie chart on a new worksheet

1. Select the text to be presented graphically
2. Click Insert tab on the Ribbon
3. Click the desired chart on Charts group e.g. pie
4. Select the desired pie from the drop-down list. The pie chart is inserted and you can label it
by proceeding with number 5 bellow
5. Click Layout tab on the Ribbon
6. Click Data Labels in the Labels group
7. Click on More Data Labels Option…
8. Tick/Check the desired Label options e.g. Percentage
9. Choose Label position e.g. Centre, Inside End etc.
10. Close the Format Data Labels dialog box by clicking Close on your far bottom right. You will
see the Percentages shown on the Pie Chart
11. Type the Chart Title by clicking on Layout tab
12. Click on Chart Title
13. Choose the position of the title e.g. Above Chart by clicking on it
14. Click on the text Chart Title in the text box then delete the text and type the desired title for
the Pie Chart

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 30 of 32

15. To move the chart to the new sheet, select the chart on the sheet with data
16. Click on Move Chart on the Location group on Design tab
17. Click on New Sheet: on the Move Chart dialog box
18. Click on OK on the Move Chart dialog box
The chart is automatically inserted on anew sheet

Modifying a Chart
Adding Labels to a pie chart
1. Click the displayed Chart to select it
2. Click Layout tab on the Ribbon
3. Click the Data Labels command in the Labels group
4. Click More Data Label options… to open the Format Data Labels dialog box
5. Click the check box to the Percentage option in the Label Contains section
6. Uncheck the Value option by clicking the checkbox next to it. You can leave the box checked
if you want the values to appear on the chart.
7. Click the Close button to close the Format Data dialog box

Alternatively, you can Right-click the selected chart and click on the Format Data Labels
option in the right-click menu to open the Format Data Labels dialog box.

How to add a Title to a Chart

1. Click the displayed Chart to select it
2. Click Layout tab on the Ribbon
3. Click Chart Title command in the Labels group
4. Click Above Chart option from the displayed list to place the Chart Title box above the chart
5. Click Chart Title box

Legends

A legend is an explanation of what is represented by the colours or symbols in

a chart.

Changing Colours in a Chart

Changing the colour of a sector of the Pie Chart

1. Click Pie Chart to select it

2. Double-click the Discman sector to select it
3. Click Format tab on the Ribbon
4. Click down arrow next to the Shape Fill command in the Shape Styles group
5. Click the yellow coloured box in the Standard Colours section

Changing Chart Type

Changing a Column Chart to Line Chart

1. Double-click the Chart to select it

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 31 of 32

2. Click the Change Chart Type command in the Type ground on the Design tab to open the
Change Chart Type dialog box
3. Click the Line option in the Change Chart Type dialog box, to change the existing chart to
line
4. Select the Line chart, which is the first option in the Line submenu
5. Click OK button to apply the selection

Alternative path

Moving the displayed Chart within the Worksheet

1. Click the Revenue chart, keeping the left mouse button pressed, drag the chart to the
desired position

Copying a chart and placing it in the same worksheet

1. Click the Revenue chart to select it
2. Click the Copy command in the Clipboard group on the Home tab
3. Click the cell where you want to paste the copied chart
4. Click Paste button to paste the copied chart

Cutting & Pasting a chart from one worksheet to a different worksheet

1. Click anywhere on the Chart to select it
2. Click Cut command in the Clipboard group on the Home tab
3. Switch to the worksheet called Sheet 2 by clicking on its tab
4. Click the Paste command in the Clipboard group on the Home tab to place the chart onto
the worksheet

Alternative path

Copying & Pasting a chart from one worksheet to a different worksheet

1. Click anywhere on the Chart to select it
2. Click Copy command in the Clipboard group on the Home tab
3. Switch to the worksheet called Sheet 2 by clicking on its tab
4. Click the Paste command in the Clipboard group on the Home tab to place the chart onto
the worksheet

Mutare Polytechnic Compiled by M. Matimba

Spreadsheet Notes January 2013 Page 32 of 32

Alternative path

Cutting and Pasting a chart from one workbook to another

1. Open the 2 workbooks first
2. Click the Revenue Chart in Chart Workbook to select it
3. Click Cut command in the Clipboard group on the Home tab
4. Click View tab
5. Click Switch Windows command in the Window group
6. Select the Workbook called Reports
7. Click on cell C5 in Reports Workbook to select it
8. Click Home tab to reveal the corresponding Ribbon
Click Paste command in the Clipboard group on the Home tab to place chart in the selected cell

Copying and Pasting a chart from one workbook to another

1. Open the 2 workbooks first
2. Click the Revenue Chart in Chart Workbook to select it
3. Click Copy command in the Clipboard group on the Home tab
4. Click View tab
5. Click Switch Windows command in the Window group
6. Select the Workbook called Reports
7. Click on cell C5 in Reports Workbook to select it
8. Click Home tab to reveal the corresponding Ribbon
9. Click Paste command in the Clipboard group on the Home tab to place chart in the selected
cell

Mutare Polytechnic Compiled by M. Matimba