Spreadsheets

All Businesses need to cope with or rather handle numeric data i.e. sales, profits, financial forecasts, stock
movements and mathematical models of all kinds. The spreadsheet meets these needs.
For many years, Businesses have used manual Spreadsheet Large sheets of squared paper divided into
columns and rows. Managers have used these sheets of paper “spread out” on their desks to analyze various
types of business information.
For example, its estimated that up to a third of managers’ time is typically spent preparing budgets. This
involves such operations as manipulating, calculating and analyzing numeric information, using formulae,
inserting text and drawing graphs. When the data changes, lengthy and tedious recalculation becomes
necessary.
A computer spreadsheet is simply the equivalent of this sheet of squared paper, with in-built calculating
facilities. A simple example of an Excel Spreadsheet is shown below, calculating students’ personal finances;
the columns represent the weeks and the rows various categories of income and expenditure. The balance at
the weekend is carried forward in the next week.

You can use these data in the following ways:

 As a record of your past finances.
 To budget for future expenditure
 To compare expenditure patterns week by week
 To experiment with the data to model possible income or expenditure
 To draw graphs illustrating any of the above.

APPLICATIONS OF SPREADSHEETS
 Budget preparations
 Balance sheets
 Manufacturing accounts
 Cash inflows
 Journal reports
 Payrolls
 Statistics
 Inventory tax and profit plans
 Financial arrangements
 Sales projections.

Features of spreadsheets
 Purpose can be created.
 Graphs can be created having columns and rows
 A single unit of column and row is called a cell
 A cell is identified with cell references
 An entry is made in a cell
 Cell entries can be moved or copied to other cells
 Sorting is possible
 Windows are made for comparison and printing
 Rows run from 1 to 16536
 Columns run from A to iv

Excel Summary Notes 1

Advantages of a spreadsheet
 It is much easier to use computerized spreadsheet such as excel than to perform manual calculations
 A spreadsheet is a general-purpose tool that can be used to solve a wide variety of problems.
 Any information can be presented in columns and rows.
 Reduces the drudgery of calculations.
 Reduce chances of making errors especially computational errors.
 Frees user time to concentrate on problem solving.
 Allows users to examine alternative solutions
 Produces quicker results,

Disadvantages of spreadsheets
 Incase of mistakes in value, this affects the whole worksheet
 Wrong formula will lead to incorrect results.
 When the sheet is too long it is not possible to view it at ago.

MICROSOFT EXCEL
Ms Excel is a spreadsheet package used for preparing, manipulating and maintaining mainly data that require
mathematical computations in such as accountant sheets in rows and columns. Ms Excel program is produced
by Microsoft Corporation, a U.S. Software Company. It is specifically designed to operate using windows, a
Microsoft graphical operating environment.
Ms Excel program was first launched in 1987. The previous version Ms Excel Version ‘97 was introduced in
1995 specifically to operate with Windows 95. Ms Excel 97 was launched in January 1997 and at the time of
writing, is still operating under Windows 95 or Windows 98.

Other examples of spreadsheets programs include: lotus 1 2 3, Multiplan, Supercalc, VisiCalc, Quattro pro

COMPONENTS AND FEATURES OF MS-EXCEL

Spreadsheet package used for preparing, manipulating and maintaining mainly data that require mathematical
computations in rows and columns

Cell
Is an intersection of a row and a column. It’s the basic storage in ms excel. It can contain various types of data
such as Numeric data or character data. A cell is referenced by its address, which consists of the column letter
and a row number.

Worksheets
Are sheets that contain rows and columns forming cells in which the user can enter and store data.

Workbooks
A workbook is the main file Ms Excel program and usually contains a number of worksheets. By default an
Ms Excel work book must have 16 worksheets. You can navigate to various worksheets using the tile sheet tab

Workspace
It contains related workbooks e.g. all workbooks that contain data of all the departments of a store

Shared workbooks
This is a group of workbook which enables you to use a workbook from any location on the network. Sharing
workbooks helps in storing all data in one workbook that can be referred to easily

Linking worksheets
This Ms Excel feature enables you to work on a number of worksheets at the same time

Excel Summary Notes 2

Charts
This is an Ms Excel facility used to represent data in form of a graph especially for summary purpose. It
enables you to graphically represent data in a worksheet

Formula AutoCorrect
Automatically corrects formulas in Excel. The formula AutoCorrect feature corrects 15 most common formula
entry mistakes automatically. Excel also prompts to confirm some suggested mistakes by displaying a
dialogue box and suggesting a correction

Range finder
Uses colored coordinated frames, to indicate the cells that are referenced in a given formula if the cell in
which the result of the formula is displayed is clicked

Natural language formulas

This feature enables you to use names of columns in formulas.

Data validation
It helps in maintaining consistency in a shared workbook where a number of people use the shared• workbook.
You can also setup rules and specify a message for a cell or range of cells specifying the kind of data that has
to be entered in the cell.

Track changes
Allows you to trace changes made to the worksheet by various users in a workbook. This feature is useful
when you are working on a shared workbook and want to find out about the modifications made by other
users.

AutoCorrect and spellchecker

Automatically corrects common typing, spelling and grammatical errors.

Page break management

Automatically prints a worksheet that is larger than a page on subsequent pages. You can specify which rows
and columns are printed on the page by inserting horizontal and vertical page breaks respectively. Excel also
provides a page breaks by dragging them to different location in the worksheet.

WORKING WITH EXCEL

Loading and running ms excel
 Turn on the computer and the screen (if necessary)
 Click on start button in the windows environment
 Select Microsoft Excel from the programs menu.

Excel Summary Notes 3

 Parts of excel window
15
1 14 16

Title bar
2 Menu bar
Toolbar
3 Name box
Current cell
Row
4
Formula bar
Column
Cell
5 Grid lines
7 Tab scrolling buttons
8 Worksheet tabs
6 Horizontal scroll bar
Minimize button
Maximize button
Close button
9
10

11 12
13

Element What it is Used For

Title bar Displays the name of the program you are currently using and the name of the workbook you are working on.
Menu Bar Displays a list of menus used to give commands to Msexcel. Clicking on a menu name displays a list of
commands-for example, clicking the Format menu name would display different formatting commands.
Standard Toolbar Toolbars are shortcuts-they contain buttons for the most commonly used commands (instead of wading through
several menus).
Formatting toolbar Contains buttons for the most commonly used formatting commands, such as making text bold or in italics.
Worksheet window This is where you enter data and work on your worksheet. You can have more than one worksheets window
open at a time, allowing you to work on several worksheets.
Cell pointer Highlights the cell you are working on.
Formula bar Allow you to view, enter and edit data in a current sheet.
Name box Display the active cell address.
Worksheet tabs Allows you to move from one sheet to another by clicking the name of the sheet you want to use.
Scroll bars Used to move around the worksheet.
Status bars Displays message and feedback.

THE WORKBOOK
The file in which you work and store your data is a Workbook. Each contains a number of worksheets (also referred to as
sheets). Thus, whenever you open, close or save a file in Microsoft Excel, you are opening, closing or saving the workbook.
The sheets are named Sheet1, Sheet2, Sheet3, etc. The sheet names appear on tabs (sheet tabs) at the bottom of the
workbook window. By clicking on the tabs you can move from sheet to sheet within a workbook.

Excel Summary Notes 4

USING MENUS
To open a menu
 Click the menu name with the mouse. Or
 Press the Alt key and then the underlined letter.
Menus found in Ms-Excel
File Description
File File-related commands to open, save, close, print, and create new files.
Edit Commands to copy, cut, paste, find, and replace text
View Commands to change how the workbook is displayed on the screen.
Insert Lists items that you can insert into a workbook
Format Commands to format fonts, cell alignment and borders.
Tools Lists tools such as the spelling checker
Data Commands to analyze and work with data information
Window Commands to display and arrange multiple windows.
Help Get help on using Microsoft excel.

MOVING AROUND IN A SHEET

Key(s) Movement
Arrow Keys________________________One cell
PgUp_____________________________One screen up
PgDn_____________________________One screen down
Tab______________________________One screen right
Shift + Tab________________________One screen left
Ctrl + Home_______________________Cell A1
Clicking on a cell will move the cell pointer to that cell.
It is also possible to move around using the scroll bars. This, however, will not affect the position of the cell pointer.

HIDING DISPLAYING AND MOVING TOOLBARS.

To view or hide toolbar.
Select view, toolbars from the menu and select the toolbar you want to hide or display
Or
Right click any toolbar or menu and select the toolbar you want to hide or display from the shortcut menu.

Keystroke and right mouse button shortcuts.

Keystroke Description
<Ctrl>+<B> Toggles bold font formatting
<Ctrl>+<l> Toggles italics for formatting
<Ctrl>+<U> Toggles underline font formatting
<Ctrl>+<Spacebar> Returns the font formatting to the default setting
<Ctrl>+<O> Opens a workbook
<Ctrl>+<S> Saves the current workbook
<Ctrl>+<P> Prints the current workbook
<Ctrl>+<C> Copies the selected text or object to the Windows clipboard
<Ctrl>+<X> Cuts the selected text or object from its current location to the Windows
clipboard
<Ctrl>+<V> Pastes any copied or cut text or object in the Windows clipboard to the
current location
<Ctrl>+<Home> Moves the insertion point to the beginning of the workbook
<Ctrl>+<End> Moves the insertion point to the end of the workbook.

Right-mouse Button shortcut menus: Whenever you are unsure or curious about what you can do with an object,
click it with the right mouse button to display a list of commands related to the object.

Excel Summary Notes 5

Saving a workbook.
 Click the save button on the standard toolbar or
 Select file, save from the menu or
 Press Ctrl + S
 Specify where to save the file in, type the file name and click save button.
To save a workbook in a new location or with a different Name
 Select file, save as from the menu
 Type the new name or specify the new location change the file name if necessary and click save.

Opening a workbook
 Click the open button on the standard toolbar. Or
 Select file, open from the menu or
 Press Ctrl + O

SELECTING CELLS
Before you can perform any operation on data in a worksheet, you must select the cells or objects you want to
work with.
Single cell:-------------------------------click on it or move the cell pointer there.
Range of cells:--------------------------point on the first cell in the range to select, then click and drag to the last
cell.
Non-adjacent cells or ranges:--------select the first range, hold down the CTRL key, and then select all the other
ranges.
Entire row/ column:-------------------Click the row/ column heading.
Entire worksheet:----------------------Click the Select All button, i.e. the button at the top left hand corner of the
worksheet.
Selecting Sheets in a Workbook.
You can work with multiple worksheets in a workbook simultaneously by making them part of a group selection,
i.e. selecting them. Some of the things you can do with a group selection include:
 Entering common column titles and formulae in several sheets.
 Formatting cells and ranges on several sheets.
 Deleting several sheets at once.
To select Do
Single Sheet: Click its tab.
Adjacent Sheets: Click the tab for the first sheet you want to select, hold down the SHIFT key, and then
click the last sheet tab.
Non-adjacent Sheets: Click the tab for the first sheet you want to select, hold down the CTRL key, and then
click the tabs for each of the other sheets you wish to select.
To cancel a selection, select a sheet that is not selected.

Getting help from the office assistant

 Press F1 key
 Type your question in the office assistant speech balloon and click search or press enter key.
 Click the topic that best matches what you have been looking for. (Repeat this steps as necessary)

Closing a workbook and exiting excel.

 To close a workbook click the workbook windows close button or select file close from the menu
 To exit Microsoft excel click the excel program close button or select file, exit from the menu.

Excel Summary Notes 6

ENTERING DATA IN A WORKSHEET.
There are two basic types of information you can enter in a cell:-
Labels and values.
Labels: Is any type of text or information not used in calculations.
Values: is any type of numeric data: numbers, percentages, fractions, currencies, dates, and times, usually used in
formulas or calculations.
To enter a label
 Select the cell you want to contain your label
 Type the label – excel will recognize it as a label if it begins with a letter. Type an apostrophe (‘) if
your label begins wit a number.
 Confirm the entry.
To enter values
 Select the cell you want to contain your value
 Type the label – excel will recognize it as a value if it begins with a number.
 Confirm the entry.

To edit cell entries (labels and values)

Entries can be edited in three ways:
 Moving the cell highlight to the cell you want to edit and then pressing F2 to make the changes on the formula
bar, or
 Select the cell you want to edit then place the insertion point on the formula and make the required changes or .
 Double-clicking on the cell and making the changes while within the cell.

EDITING WORKBOOK
Editing, clearing and replacing cell contents
To clear cell contents.
 Select the cell(s)
 Press delete key or in edit menu select clear then contents.
To edit cell contents.
 Select the cell
 Click anywhere in the formula bar.
 Edit the cell contents (use the arrow, delete and backspace keys).
 Press enter key when through.

To edit cell contents in place.

 Double click the cell you want to edit. Or press F2.
 Edit the contents of a cell in place.
 Press enter key when you are finished editing the cell.
Cutting, copying and pasting cells
TO Cut and Paste:
1. Select the cell or cell range you want to cut
2. Click the Cut button on the standard, toolbar. Or... Select Edit - Cut from the menu. Or... Press <Ctrl> +
<X>.
3. Select the cell where you want to paste the cut cell(s).
4. Press <Enter>.

Excel Summary Notes 7

 To Copy and Paste:
1. Select the cell or cell range you want to copy.
2. Click the Copy button on the Standard toolbar. Or... Select Edit Copy from the menu. Or... Press <Ctrl>
+ <C>.
3. Select the call where you want to paste the cut cell(s).
1. Click the Paste button on the Standard toolbar. Or... Select Edit -+ Paste from the menu. Or... Press
<Ctrl> + <V>
Moving and copying cells with drag and drop
To Move Cells with Drop and Drag:
1. Select the cell or the cell range you want to move.
2. Move the pointer to the border of the cell or cell range, click and hold down the mouse button and drag
the cell or cell range to the upper-left cell of the area where you want to move the data.
3. Release the mouse button.

To Copy Cells with Drop and Drag:

Follow the above procedure, only hold down the <Ctrl> key while you drag and drop the cell(s).

Collecting and posting multiple items.

You can use the office clipboard to collect and paste multiple items. Excel clipboard can hold up to twelve items.
To Display the Clipboard Toolbar:
Select View-+ Toolbars -), clipboard from the menu. Or...
Right-dick any toolbar or the menu bar and select Clipboard from the shortcut menu.

To Add Items to the Office Clipboard:

Copy and/or cut the items as you normally would or consecutively.
To View the Contents of a Clipboard item:
Point to the item on the Clipboard toolbar.

To Paste from the Office Clipboard:

Display the Clipboard toolbar and then dick the item you want to paste. Click the Paste All button to paste
everything.

Using the paste special

To use the paste special Command:
Excels paste special command lets you specify what exactly you want to copy. For example you can use the paste
special command to copy the resulting value of a formula without copying the formula itself, to paste special:
1. Select the cell or cells, range you want to copy.
2. Cut or Copy the selection using standard cut or copy procedures
3. Select the upper left cell of the area or select the cell range where you want to paste the copied cells
4. Select edit, paste special from the menu.

Refer. To table on paste special options for the different Paste options
Paste options Description
All Paste all cell contents and formatting. Same as the paste function.
Formulas Pastes only the formula as entered in the formula
Values Paste only the values as displayed in the cells.
Formats Pastes only cell formatting.
Comments Pastes only comments attached to a cell
Validation Pastes data validation rules for the copied cells to the paste area.
All except borders Pastes all cell contents and formatting applied to the copied cell except borders.
Operations Specifies which mathematical function, if any, you want to apply to the copied data, e.g.
you could multiply the pasted data by 5
Skip blanks Avoid replacing values in your paste area when blank cells occur in the copy area.
Transpose Changes columns of copied data to rows and vice versa
Link Links the pasted data to the source.
Excel Summary Notes 8
Working with absolute cell referencing
Cell referencing is a way of identifying a cell or a range of cells on a worksheet and tells ms excel where to look
for values you want to use in a formula. There is two ways of referencing cells in excel: -
Relative cell referring: -relative references tell excel how to find another cell starting from the cell that contains
the formula. Using a relative reference is a lot like telling someone directions that explains where to go from
where the person is currently standing. When a formula containing a relative reference is moved, it will reference
new cells based on their location to the formula. Relative cell referencing is the default type of references used in
excel.
Absolute cell referencing: - cell references always refer to a particular cell address. They do not change if the
formula is moved to a new location. Absolute cell reference is preceded by $.
The table below shows the different types of cell references

Referenc Effect on a cell reference

e
$A$1 Both row and column references are absolute
A$1 Only the row reference is absolute
$A1 Only the column reference is absolute
A1 Relative cell reference

Renaming a Sheet.
1. Double-click the sheet tab or right click the sheet in the sheet tab and select rename from the shortcut menu,
2. Type the new name, and then press enter key

Moving sheets
You can move a worksheet from one side to another on the sheet tabs or from one workbook to another
To move:

Inserting and deleting rows and columns

You can insert or delete rows or columns to change the structure of data on a worksheet.
You can insert blank rows and columns anywhere on a worksheet.
MS-Excel adjusts references to the shifted cells to reflect the new location.
NOTE: Rows are always inserted above, and columns are inserted to the left of the cell pointer position (or the
selection).
Inserting Rows
1. Place the cell pointer where you wish to insert the empty row. If you want to insert more than one, select
highlight downwards, the number you want (i.e. if you want five rows, highlight five cells).
2. From the Insert menu, select Rows. Or right click the row heading and select insert from the shortcut menu

Inserting Columns
1. Place the cell pointer where you wish to insert the empty column. If you want to insert more than one, select
highlight to the right, the number you want ;( i.e. if you want five columns, highlight five cells).
2. From the Insert menu, select Columns. Or right click the column heading and select insert from the shortcut.
Menu
Excel Summary Notes 9
Deleting Rows or Columns
When you delete a row or column, all the data in that row or column is also deleted.
1. Select the rows or columns you want to delete.
2. On the Edit menu, select Delete. Or right click the selected row or column heading and select delete.
Deleting a range of cells.
 Select the range of cells you want to delete
 Right-click the selection and select delete from the shortcut menu. Or select edit, delete from the
menu.
 Specify how you want adjacent cells shifted.

Undoing commands
When you change your mind, for instance, about erasing the worksheet title or make a mistake, you can reverse
your last command or last action by choosing Undo command from the Edit menu, or clicking the Undo button
on the toolbar. In the Edit menu the Undo command changes to show the most recent command or action. If
Undo doesn't apply the words 'Can't Undo' appears dimmed on the menu. Remember, you can undo only the last
command you choose or the last cell entry you typed.
Caution: You can't undo the results of all the commands in MS-Excel. When you choose a command that cannot
be undone and will change your data, MS-Excel will warn you before it carry out the command.

Checking your spelling.

 Click the spelling button on the standard toolbar or
 Select tools, spelling from the menu or
 Press F7

Excel Summary Notes 10

Finding and replacing information.
You can locate or trace easily specific words, phrases and values in a workbook.
To find information in a workbook:
1. Select Edit, Find from the menu or Press ctrl + F
2. Enter the information you want to search for in find what box
3. Click the find next button
4. Repeat step 3 until you find the text you are looking for
To find and replace information
1. select Edit, Replace form the menu or Press ctrl + H
2. Enter the information you want to search for in find what box
3. Enter the information you want to replace with in the replace with box
4. click find next button
5. Click replace button to replace the text.
6. repeat steps 4 and 5 if there is more than one occurrence that you want to replace or
7. Click replaces all buttons to search and replace every occurrence of the text in the workbook.

Inserting cell comments

Sometimes you may need to add notes to your workbook to document complicated formulas, questionable
values or leave a comment to another user. Excel’s cell comments helps you document your worksheet and
make then easier to understand.
 To insert a comment :
o Right click the cell you want to attach a comment or select the cell then select insert, comment
from the menu
o Enter the comment and click anywhere outside the comment area when you’re finished.

 To Edit a comment
o Right click the cell that contains the comment you want to edit and select edit comment from
the shortcut menu.
o Edit the comment and click anywhere outside the comment area when you’re finished.

 To delete comment
o Right click the cell that contains the comment and select delete comment from the shortcut
menu or
o Select the cell that contains the comment and select edit clear comment from the menu.

Excel Summary Notes 11

FORMATTING A WORKSHEET.
Formatting fonts
You can emphasize text in a worksheet by making the text darker and heavier (bold), slanted (italics), or larger
(font size) in a different typeface (font). To do this you can use: -
 The formatting toolbar by clicking the respective buttons or
 On format menu select cells then font tab and make the required formatting

Formatting values.
Applying the number formatting changes how the values are being displayed. The number formatting you can
apply to your values is like currency, percent, comma, increase or decrease decimal, accounting, date, time,
fraction, text etc.
To apply the number formatting using the formatting toolbar:
 Select the cell or the range of cells you want to format and click the appropriate number formatting
buttons on the formatting toolbar
 The number formatting buttons on the formatting toolbar include currency, percentage, and comma,
increase decimals and decrease decimal.
To apply number formatting using the format cells dialog box: -
 Select the cell or range of cells you want to format, click format menu, select cells or right click the
selection and select format cells from the shortcut menu, click the number tab, and specify the
number formatting you want to apply.

Adjusting row height and column width.

Changing column widths
In a new worksheet, all the columns are set to a standard width. You can change the standard width setting to
adjust all columns on the sheet, or you can adjust only the columns you want to change.
To Change the Width of a Single Column
1. Move to the right column-heading border of the column until the mouse pointer changes to a double-sided
arrow.
2. Click and drag to the right to enlarge or to the left to reduce the width.
NOTE: If you double-click for step 2 instead of dragging, the column width will increase or reduce to
accommodate the longest entry in that column. Or
Right-click the column header(s), select width from the shortcut menu and enter the column width or
Select the column header(s), select format, column, width from the menu and enter the column width.

Changing the Width of More Than One Column

Select all the columns you wish to resize.
1. Move to the right column-heading border of one of the selected columns until the mouse pointer changes to a
double-sided arrow.
2. Click and drag to the right to enlarge or to the left to reduce the width.
NOTE: If you double-click for step 2 instead of dragging, the column widths will increase or reduce to
accommodate the longest entries in each column. Or
Right-click the column header(s), select width from the shortcut menu and enter the column width or
Select the column header(s), select format, column, width from the menu and enter the column width.

To change the row height.

 Drag the row header’s bottom border up or down. Or
 Right-click the row header(s), select row height from the shortcut menu and enter the row height. Or
 Select the row header(s) select format, row, and height from the menu and enter the row height.
Excel Summary Notes 12
Changing the cell alignment.
 Using the formatting toolbar: select the cell or cell range and click the appropriate alignment button
(left, centre, right or merge and centre) on the formatting toolbar.
 Using the format cells dialog box: select the cell or cell range and either right click the selection and
select format cells from the shortcut menu or select format cells from the menu. Click the alignment
tab and select the desired alignment option.

Hiding Grid lines

By hiding gridlines on a sheet, you can make formats such as borders; double underlining and shading stand out.
1. Choose Options from the Tools menu.
2. Select the View tab.
3. Clear the gridlines check box and click OK.

Adding borders
 Using the format toolbar: select the cell or range of cells you want to add a border(s) to and click
the border style list arrow in the formatting toolbar and select the border you want (outside, inside, all
borders, bottom border, left and right border etc)
 Using the format cells dialog box: select the cell or range, right-click the selection and select format
cells from the shortcut menu or select format, cells from the menu. Click the borders tab and select
the types of borders you want to add then click OK to apply the border

Applying colours and patterns.

 Using the formatting toolbar: select the cell or range of cells and click the fill color list arrow on the
formatting toolbar and select the color you want.
 Using the format cell dialog box: either right clicks the selection and select format cells from t he
shortcut menu, or select format, cells from the menu. Click the patterns tab and select the color or
pattern you want to use.

Using auto format.

 Auto format automatically formats your worksheet using one of the sixteen preset formatting
schemes.
 Select format, autoformat from the menu and select one of the sixteen autofomarts from the list.

Formatting cells with conditional formatting.

 Conditional formatting is a format, such as cell shading or font color, that excels applies to cells if a
specified condition is true.
 To conditionally format a cell or cell range: select the cell or cell range you want to format
conditionally and select format, conditional formatting from the menu. Enter the condition (e.g. cell
value is greater than 10), click the format button and specify the formatting you want to use if the
condition is true. If you want to specify additional conditions for the selected cells, click the add
button otherwise click OK.

Merging cells, rotating text and using autofit.

 To merge cells: select the cells that you want to merge, select format, cells form the menu, click the
alignment tab, select the merge cells check box and click ok or click merge and centre button on the
standard toolbar.
 To rotate text in a cell: select the cell or cell range, select format, cells from the menu and click the
alignment tab. Select one of the options in the orientation section or adjust the and select the angle
by dragging the text rotation tool.

Excel Summary Notes 13

ENTERING A SERIES OF VALUES OR LABELS (CUSTOM LIST)
Copying Using the Fill Handle
The fill handle is found at the lower right corner of cell highlight. When the pointer is positioned on the fill
handle, it changes into a thin cross. When you use the fill handle to highlight (either down or across), data in the
cell is copied into all the cells that you highlight. This is normally useful when copying formulas.

As seen, you can copy the contents of cells into other cells by dragging the fill handle.
You can also create a series by incrementing the value in the active cell into a range using the fill handle/auto fill.
E.g. you can extend a series such as 1, 2, 3 to include 4, 5, 6... Or create series such as Period 1, Profit, Loss,
Period 2, Profit, and Loss ... and so on. Auto fill is a time saving feature for data entry in excel. Auto fill
automatically enters a series of values in any cells you select. Auto fill is used to enter values or labels of a
common known series like days of the week, months and other custom series that you can create.
Other examples of such series, which MS-Excel recognises as a series, are: -
 Jan, Feb, Mar, …
 Mon., Tue, Wed.
 Period 1, Period 2...
 1st Group, 2nd Group...
 10%, 20%, 30%...
To use auto fill
 Type in at least the first two values in the series, e.g. 10%, 20% or 5, 10 or Period 1, Period
 Select the cells you used in step 1
 Point on the fill handle auto fill and drag either down or across until you see the value you wish to go
up to on the formula bar.

Entering Labels
You can use the fill handle to enter a series of labels, e.g. Days of the week or months. Any other list apart from
these two will have to be added, as explained later. This is known as a custom list.

To enter a list of labels;

1. Type in the first item in your list, e.g. Jan, Monday, etc.
2. Use the fill handle to drag until all the other items are copied.

CUSTOMIZING AUTOFILL
If you frequently use special series of data, like product lists or names, you can use Autofill to automatically enter
that data on your worksheets.
The example we will use is creating a custom list that gives different kinds of music.
1. Type the data you would like to use in your custom list.
Jazz
Country
Rock & Roll
Blues
Classic
2. Select the range containing this data.
3. On the Tools menu select Options.
4. In the Options dialog box select the Custom Lists tab.

Excel Summary Notes 14

5. Click the Import button. This inserts the range contents into the List Entries. If need be, you can edit these
entries here. Otherwise these are inserted in the Customs List box.
6. Click OK.
To enter this list in a worksheet, type in the first entry, place the cell highlight in this cell then drag using the fill
handle.

To remove a list from the custom lists;

1. Select Options from the Tools menu.
2. In the Options dialog box, click the Custom Lists tab.
3. Select the list you wish to remove.
4. Click the Delete button.
5. Click OK.

Excel Summary Notes 15

WORKING WITH FORMULAS
A Formula combines values with operators, such as a plus sign or minus sign, in a cell to produce a new value
from existing values. A formula can include names or reference to cells in place of, or in addition to, constant
values and can also use worksheet functions instead of operators.
NOTE: A formula must always be preceded by an equal (=) sign.
There are three ways in which you can enter a formula:
1. Using values:
= (180/25) +100
2. Using cell addresses:
= (B4/B7) +100
B4, B7-------------------Cell addresses
3. Using functions:
= SUM (B2:B6)
=AVERAGE (B1:E1)
SUM () and AVERAGE ()------------Functions
B2:B6, B1:E1--------------------------Cell ranges

NOTE: When working with data in a worksheet, always use either the second (cell addresses) or third method
(functions) of entering formulas. This is because this way, you will be able to copy the formulas so as to calculate
other ranges.

USING THE AUTO SUM BUTTON.

The SUM () function is the most frequently used of all worksheet functions. With it you can transform a long and
complex formula such as =A2+A3+A4+A5+A6+A7+A8+A9 into the more concise form =SUM (A2:A9). Even
the more convenient is the AutoSum button (). When you use this button, Excel types the function for you and
even suggests the range of cells you want to add.

Entering a Sum with the AutoSum button.

To enter a sum formula, select a cell adjacent to a row or column of numbers you want to add, and click the
AutoSum button. If the suggested range is incorrect, drag through the correct range and press ENTER. You could
also select the values you want to add (including the cells where the totals should appear), and then click the
Autosum button. The totals will be inserted in the empty cells that were selected.

To enter formula
 Click the cell where you want to insert the formula.
 Press (=) equals sign to begin the formula
 Enter the formula
 Press enter key
Or
 Click the cell where you want to insert the formula.
 Click fx button on standard toolbar or click insert menu then function
 On paste function dialog box select the function category you want to use then
choose click the function you want to use.
 Click ok button.
 On the formula palette select the range
 Press enter key or click ok button.
Excel Summary Notes 16
Examples of operators, references and formulas.
Operator or Purpose Example
function name
= All formulas must start with equals sign
+ Performs addition between values =4 +3
- Performs subtraction between values =A4-A3
* Performs multiplication between values =B1 * 2
/ Performs division between values =A2/C4
Sum Adds all the numbers in a range =Sum (A1:E1)
Average Calculates the average of all the numbers in a range =Average (C4:C17)
Count Counts the number of items in a range =Count (A2:C10)

Fixing errors
Formula errors can result in error values as well as cause unintended results. Below are some tools to help find
and investigate errors. If a formula cannot properly evaluate a result, Microsoft Excel will display an error
value. Each error type has different causes, and different solutions as shown in the table below:.
Excel error values

Error Value Description

##### The numeric value is too wide to display in a cell. Resize the column width.

#VALUE! You entered a mathematical formula that refers to a text entry instead of numeric entry.

#DIV/0! You tried to divide a number by zero. This formula occurs when you refer to a blank cell
when creating a formula

#NAME? You entered text in a formula that excel do not recognize. You may have misspelled the
name of the function, or typed deleted name.

#N/A This error occurs when the value is not available to the formula or as function.

#REF! This error occurs when the cell reference is not valid. You probably deleted a cell range
referred to in a formula.

#NUM! Occurs when you used an invalid argument in a worksheet.

#NULL! You specified an intersection to two ranges in a formula that do not intersect.

Displaying and printing errors

To display or hide worksheet formulas:- select tools, options from the menu and click the view tab and check
or uncheck the formula check box.

Excel Summary Notes 17

MANAGING WORKBOOKS
Switching between sheets in a workbook
 Switch to a worksheet by clicking the sheet tab at the bottom of the screen.
Inserting and deleting worksheets
 To add a new worksheet: select insert, worksheet from the menu or right click on the sheet tab ,
select insert from the shortcut menu and select worksheet from the insert dialog box.
 To delete worksheet: select edit, delete sheet from the menu or right click on a sheet tab and select
0delete from the shortcut menu.
Renaming and moving worksheets.
 By default sheets are named sheet 1, sheet2, sheet3 and so on.
 To rename a worksheet there are three methods.
o Double click the sheet tab and enter a new name for the worksheet
o Right click the sheet tab and select rename from the shortcut menu, and enter a new name for
the worksheet.
o Select Format, sheet, rename from the menu and enter the new name.
 Move a sheet
o Dragging its sheet tab to the desired location.
o Right click on the sheet tab, select move or copy from the short menu, select the
direction/location and click ok.
 Copy sheet
o Copy a sheet by holding down CTRL key while dragging the worksheet tab to a new location.
o Right click on the sheet tab, select move or copy from the short menu, check create a copy and
click ok.


CHARTS
Creating and working with charts
A chart is a graphic representation of worksheet data. Values from worksheet cells are displayed as bars, lines,
pie slices, or other shapes in the chart. Data points are grouped in data series, which are distinguished by the
different colours or patterns.
Showing your data in a chart can make it clearer, and easier to read. Charts can also help you evaluate your data
and make comparisons between different worksheet values.
The ChartWizard
The ChartWizard is a button found in both the Standard toolbar and the Chart toolbar.
It is a series of dialog boxes that simplifies creating of a chart. The ChartWizard guides you through the process
step by step: you verify your data selection, select a chart type, and decide whether to add items such as titles and
legend. A sample of the chart you are creating is displayed so that you can make changes before you finish
working in the ChartWizard.

Forms of Charts
There are two forms of charts, depending on their location.
Embedded Charts: This is a chart that is created on the same sheet on which the data is. It becomes an object
on the worksheet. Use this when you want to display a chart along with its associated data.
For example, you can use embedded charts for reports and other documents in which it's
better to display a chart within the context of the worksheet area.
Chart Sheets: This is a chart that is created on its own sheet. Use this when you want to display a
chart apart from its associated data. You might do this when you want to show overhead
projections of your charts as part of a presentation.
Excel Summary Notes 18
Whenever you have a chart, it is automatically linked to the data you created it from. When you change the data,
the chart is updated to reflect the changes.
Creating a Chart
Step 1 - Data range:
Specify the data to be used in creating the chart. Click the chart wizard button on standard toolbar or select insert,
chart from the menu.
Step 2 - Chart type:
Select the type of chart to create e.g. bar, column, pie etc.
Step 3 - Chart Sub-type:
Select the sub-type of chart.
Step 4 - Preview and data series:
A preview of the chart is displayed. This step can also be used to specify the data series (i.e. either in rows or
columns).
Step 5 - Titles
Type the titles for the chart.
Once you click Finish, the chart is created. This chart will be created as part of the worksheet.
To create a chart on its own sheet:
1. Select the data to use in creating the chart.
2. Click Insert and point on Chart Follow the five steps above
3. In the last step then select As New Sheet.
Selecting a Chart
Once the chart is created, you can still make any changes you want on it, e.g. enlarging the chart area, changing
the chart type, changing the headings, etc. To do this, you must select the chart by double-clicking in the chart
area.
Once you finish making changes, click outside the chart area to deselect it.
Moving and resizing charts.
 To resize a chart, click to select it, and then drag its size handles (located along the edges of the chart)
until it’s the size you want.
 To move a chart: click and hold down the left mouse button on the blank area around the chart, drag
the chart to a new location in the workbook, and then release the left mouse button.
Changing the chart’s source data.
 To change a chart’s data source: select chart, source data from the menu and click data range tab.
Click the data range box and select the cell range you want to base the chart on (click the collapse
dialog box if necessary).
 The collapse dialog box temporarily shrinks and moves the dialog box so that you enter the cell
range by selecting cells in the worksheet. When you finish you can click on the button again or
press enter to display the entire dialog box.
 Or
 Right click the chart area to display the shortcut menu.
 Select source data and follow the above steps in changing the source data.
Changing a chart type.
 The most common types of charts are column, bar, pie, line etc.
 To change the chart type: right click the chart, select chart type in the shortcut menu
 Select the chart type and chart sub type you want then click ok to continue
 Or o n chart menu select chart type and continue as in the dialog box.
Adding titles, gridlines, and a data table.

Excel Summary Notes 19

  To add or remove gridlines from a chart: select chart, chart options from the menu or right click the
chart area, select chart options from the shortcut menu and click gridlines tab. Check or uncheck the
appropriate grid line check box.
 To add or change titles to a chart: select chart, chart options from the menu or right click the chart
area, select chart options from the shortcut menu and click the titles tab. Enter or modify the text
boxes that correspond to the desired chart titles.
 To add or remove data tables: select chart, chart options from the menu or right click the chart area,
select chart options from the shortcut menu and click the data table tab. Check show data table and
click ok.
 To add or remove chart data labels: select chart, chart options from the menu or right click the chart
area, select chart options from the shortcut menu and click data labels tab. Check or uncheck the
appropriate check boxes to display or to hide data labels.
Formatting a data series and chart axis.
 A data is a group on a chart that comes from a row or column in a worksheet. An axis is a line that
borders one side of a chart that provides scale of measurement or comparison in a chart. For most
charts data values are plotted along the value (y) axis, which is usually vertical, and categories are
plotted along the category (x) axis, which usually horizontal.
 To add labels to a data series: double click the data series or select the data series and select format
objects from the menu. Click data labels tab and select the appropriate option.
 To change the scale of a chart: double click the axis or right click the axis and select format axis from
the shortcut menu. Click the scale tab and make the changes to the scale.
Using fill effects.
 To add fill effects: right-click the chart area or the plot area then select format chart area or format
plot area respectively. Click the patterns tab and click the fill effects button. Select one of the four
tabs, select a fill effect, and click.

Excel Summary Notes 20

Printing Charts
Embedded charts can be printed out together with the worksheet or on their own. To do this you follow the
normal printing procedure.
To print out the chart on its own,
1. Select the chart by double-clicking in the chart area.
2. From the File menu, select Print. Note that under Print What, the only selection available is Selected Chart.
3. Click OK.

PAGE SETUP
Under Page Setup you can set margins, vertical and horizontal alignment to the page, add or edit headers or
footers as well as set the page orientation.
From the File menu, select Page Setup.
In the Page Setup dialog box, you have the following tabs:
a) Page
b) Margins
c) Header/Footer
d) Sheet
1. Page
Fitting Your Work to the Page: Your work may not fit exactly on the page. There are two ways of solving this
problem;
 Changing the Page Orientation.
 Scaling the printed work.

Changing the Page Orientation: Under Orientation, select either Landscape or Portrait to change the way text is
printed in relation to the paper.
Changing the scaling: Under Scaling, adjust the %age size of text to be printed. The default is 100% (exact
size). You can use scaling to reduce or increase the size of printed data.

2. Margins
Click the Margins tab to set the margins for your page. You can also set how much space is to be occupied by the
header or footer.

Centering data on the page:

Normally, when data is printed, it appears at the top and on the left margins. However, it is possible to centre it
either horizontally or vertically along the page.
Under Centre on page, select either horizontally or vertically or both to centre data.

3. Header/ Footer
Click the Header/ footer tab to insert a header or footer. Page numbers are normally inserted as headers or
footers.
To insert any one of the pre-set headers or footers, click the drop-down list box for the header or footer and select
the one to insert. If, on the other hand, you wish to insert your own, click on either Custom Header… or Custom
Footer… and type it in.

Excel Summary Notes 21

 4. Sheet
Click the Sheet tab to specify the order of print as well as what should appear on the printed page along with the
data.
Under Print, click the check box for any of the items listed for them to be printed, e.g. gridlines, row and column
headings, etc.

Under Page Order, you are required to specify how the data is to be printed. Pages are printed and numbered in
one of the following ways: -

Down, then across: Numbering and printing proceed from the first page to the pages below, and then move to
the right and continue down the sheet.
Across, then down: Numbering and printing proceed from the first page to the pages to the right, and then
move down and continue across the sheet.

PRINTING
You can print a selected sheet(s), a portion of the sheet or even an entire workbook!
1. On the File menu, select Print. If you want to print a range, select it before you go to the menu.
2. Under print what, select, which range you, want to print (Selection, Selected sheet(s), or Entire workbook).
3. Under copies, specify the number of copies you want printed.
4. Click OK to begin printing.

Previewing Print
It is always a good idea to view the document the way it will appear on paper before you go ahead and print, so
that any necessary changes can be made.
On the File menu, select Print Preview.

Excel Summary Notes 22

USING FUNCTIONS.
THE =IF FUNCTION
=IF compares two values with a special character called a logical operator. The following logical operators can
be used with =IF.
= Equal to < Less than
> Greater than <= Less than or equal to
>= Greater than or equal to <> Not equal to

=IF requires three arguments:

a) The comparison/ logical test
b) Value if true
c) Value if false
=IF (Comparison, result-if-true, result-if-false)

Comparison: Any value or expression that can be evaluated to TRUE or FALSE.

Result-if-true: Value or text that is displayed if Comparison is TRUE. If omitted, TRUE is displayed.
Result-if-false: Value or text that is displayed if Comparison is FALSE. If omitted, FALSE is displayed.
Mathematical functions.
Function Syntax Description
ABS =ABS (number) Determines the absolute value of a number. The absolute value of a number
without its sign
COS =Cos Returns cosine of a number
DEGREES =DEGREES Converts radians into degrees
EVEN =EVEN (number) Rounds a number up to the nearest even or odd integer
ODD
FACT =FACT (number) Calculates the factorial of a number.
LOG =LOG (number, Calculates the logarithm of a positive number using specified base
base)
LOG10 =LOG10 (number) Calculates the base 10 logarithm of a number.
MOD =MOD (number, Returns the remainder after number is divided by divisor.
divisor)
PI =PI () Returns the value of a constant pi, accurate to 14 decimal places.
POWER =Power (number, Raises a number to a specified power.
power)
PRODUCT =Product Multiplies all numbers in a range of cells
(number1,
number2…)
SUM =Sum (number1, Adds all the numbers in a range
number2…)
TAN =Tan (number) Returns the tangent of a angle
SIN =Sin (number0 Returns the sine of an angle
SQRT =SQRT (number) Returns the positive square root of a number.
ROUND =ROUND Rounds a number to a specified number of digits. The round up or rundown take
ROUNDUP (number, the same form and as the same suggests always either round d up or down.
ROUNDDOWN num_digits)

Excel Summary Notes 23

Date and time functions.

Function Syntax Description

Date =Date (year, month, day) Enters date in a cell
Today =Today () Returns the value of the current date
Time =Time (hour, miniute, Enters a time in the cell. Uses a 24-hour time system
second)
Now =Now () Returns the value of current time.
Year =Year (serial number, return Returns the value of the year for a specific date.
type) E.g. YEAR (“3/15/1998”) equals 1998
Month =Month (serial number, Returns the value of a month for a specific date
return type) E.g. MONTH (“3/15/1998”) equals 3
Day =Day (serial number, return Returns the value of a day for a specific date
type) E.g. DAY (“3/15/1998”) equals 15
Hour =Hour (serial number) Returns the hour value for specific time
E.g. HOUR (“12:15:45”) equals 12
Minute = Minute (serial number) Returns the minute value for specific time
E.g. MINUTE (“12:15:45”) equals 15
Second =Second (serial number) Returns the second value for specific time
Eg HOUR (“12:15:45”) equals 45
Days360 =Days360 (start_date, Returns the number of days between two dates based on a 360-day year.
end_date) E.g. DAYS360 (“1/30/93”,”2/1/93”) equals 1

Statistical functions.

Function Syntax Description

AVERAG =Average (range) Calculates the average or arithmetic mean, of the numbers in a range or
E arguments
COUNT =Count (number11, number2…) Counts the number of cells that contains numbers, including dates and
formulas. Ignores all blank cells that contain text or errors.
COUNTA =Counta (number1, number2…) Counts the number of all non blank cells, regardless of what they contain
MAX =Max (number1, number2…) Returns the largest value in a range.
MEDIAN =Median(number1, number2…) Calculates the median of the numbers in the range or arguments.
MIN =MIN (number1, numbver2…) Returns the smallest value in a range.
MODE =MODE (number1, number2…) Determines which value occurs most frequently in a set of numbers.
STDEV =Stdev (number1, number2…) Estimates standard deviation based on a sample.
STDEVP =STDEVP Estimates standard deviation based on an entire population.
VAR =VAR (number1, number2….) Estimates variance based on a sample.
VARP =VARP (number1, number2…) Estimates variance based on an entire population.

Excel Summary Notes 24

Financial functions
Using the pmt function.
The PMT function is a very valuable function when working with real estates, investments, or is considering
taking out a loan. The PMT function calculates the payment of a loan based on the periodic payments and a
constant interest rate. For example, say you want take out a kshs 10,000 car loan at 8% interest and will pay the
loan off in four years. Using the PMT function the payments for such loan would be kshs 244.13. You can also
use the PMT function to determine payments to investments. For example, if you want to save say kshs 50000 in
20 years by saving the same amount each month; you can use PMT to determine how much you must save.

To use the PMT function

 Click the fx (paste function) button on the standard toolbar to open the formula palette or click insert
menu the function
 Select financial in the function category list, select PMT in the function list name and click ok.
 Enter the required arguments for the PMT function.

Or
 Write the formula using the syntax
=PMT (rate, nper, PV)

=PMT (rate, nper, pv)

The present value of

Function loan amount or
The The principal
interest number of
rate per payments
period

Link Sheets.
Sheets are normally independent. If a change in a value in one sheet alters other values in different sheets, we
link them so that excel will automatically update the affected values if we make any changes.
Step by step
1. When typing in a formula that refers to a cell in another sheet, include the name of the sheet before that
particular cell reference separated by colon.
Examples
1. The formula = G6 sheet! B6 instead of =G6*B6 will refer to B6 in sheet1 rather than in the current
sheet.
2. =Salesinfo! A10 in cell A10 of sheet2 will cause the contents of cell A10 in the named salesinfo to be
also the contents of A10 in sheet2.

Excel Summary Notes 25

 Working with lists
Another work of excel is keeping track of information in a list or databases. Examples of things that you
might track include employee roasters, telephone numbers, clients etc. once you create a list you can easily
find, organize and analyze its information with excel.

Creating a list.
Lists are organized by records. Each record contains information about a thing or person, just like an
individual listing in a phone book. Records are broken up into fields, which store specific pieces of
information, For example, the Last Name field would contain people’s last names, and the Phone field
would contain their phone numbers. In Excel, the columns contain the list’s fields, and the rows contain
the list’s records.

Guidelines for creating a list.

 Only have one list on a worksheet:- Some list management features, such as filtering, can be
Used on only one list at time.
 Leave at least one blank column and one blank row between the list and other data on the
worksheet: - Excel more easily detect and select the list when you sort, filter or insert automatic
subtotals.
 Avoid putting blank rows and columns in the list:-so that Microsoft Excel can more easily detect
and select the list.
 Create column labels in the first row of the list: - Excel uses the labels to create reports and to find
and organize data.
 Design the list so that all rows have similar items in the same column. This makes the list more
meaningful and organized.
 Try to break up information as much as possible:-This gives you more power to sort, filter and
manipulate the list.
 Each column should contain the same information:-This will make the list easier to read and
understand.
 Don’t use duplicate field names:-Duplicate field names can cause problems when entering and
sorting information.

To create a list in excel

 Enter the field names as column headings.
 Enter records as rows.
Using data form to enter records.
Data form can do a lot of things including:
 Adding records
 Displaying and scrolling through the records
 Editing existing records.
 Deleting records
 Finding specific records.

Finding Records
 To Find Records using the Data Form: Make sure the active cell is located inside the list and
select Data, Form from the menu. Click criteria button, enter the information you want to search
for in the appropriate fields, and click either the Find Next or Find Prev button.
 To Find Records using the Edit command: Select Edit, find from the menu. Enter the information
you want to search for click the Find Next button.
 To Find and Replace information: select Edit, Replace from the menu. Enter the text you want to
search for in find what box, and enter the text you want to replace it with in the Replace with box.
Clicks replace all to search and replace every occurrence of the text or click the Find Next button.

Excel Summary Notes 26

Deleting records.
 To Delete a record with the Data Form: Make sure the active cell is located inside the list and
select Data, form from the menu. Find the record you want to delete using the Find Next, Find Prev
or Criteria buttons, click Delete and confirm the deletion of the record.
 To Delete a Record Directly in the Worksheet: Delete the record’s rows or cells.

Sorting a List
 To Sort a List by One Field: Move the cell pointer to the column you want to use to sort the list
and click either the Sort Ascending button or Sort Descending button on the Standard toolbar.
 To Sort a List by More than One Field: Make sure the cell pointer is located within the list and
select Data, sort from the menu. Select the first field you want to sort by from the drop-down list
and specify Ascending or Descending order. Select the second and third fields you want to sort by
(if desired).

Filtering a List with the Autofilter

 AutoFilter displays only the records that meet your criteria, and hides the records that do not.
 To Filter a List with AutoFilter: Move the cell pointer anywhere within the list, select
Data ,Filter ,Autofilter from the menu, and select the filter criteria from the drop-down arrows in
the field names of the header row.
 To Remove an AutoFilter: Select Data, Filter, and AutoFilter from the menu.

Viewing workbooks and worksheets

A Microsoft Excel workbook is a file that contains one or more worksheets, which you can use to organize
various kinds of related information. You can enter and edit data on several worksheets simultaneously and
perform calculations based on data from more than one worksheet. When you create a chart, you can place the
chart on the same worksheet as its related data or on a separate chart sheet.
You can move from one worksheet or chart sheet to another by clicking the sheet tabs at the bottom of the
workbook window. You can color-code your sheet tabs to make them easier to recognize. The tab for the
active sheet is underlined in the color you choose; tabs for inactive sheets are fully colored.
Viewing different parts of a worksheet at the same time
Split a worksheet To view and scroll independently in different parts of a worksheet, you can split a
worksheet horizontally and vertically into separate panes. Splitting a worksheet into panes allows you to view
different parts of the same worksheet side by side and is useful, for example, when you want to paste data
between different areas of a large worksheet. In the following example, you can view the rows for different
cities in the upper panes while keeping the February totals visible in the lower panes.
The two right panes scroll together, as do the two lower panes.
Keep labels or data visible To keep row and column labels or other data visible as you scroll through a
sheet, you can "freeze" the top rows and/or left columns. The frozen rows and columns don't scroll but remain
visible as you move through the rest of the worksheet.
The labels in the first row remain fixed while you scroll the rest of the worksheet vertically.
You cannot both split a worksheet and freeze parts of a worksheet at the same time.
Note Split or frozen panes do not automatically show up on each page when the sheet is printed. You must
specify any labels you want to print.
Creating custom views

Excel Summary Notes 27

You can save a set of display and print settings as a custom view, then apply your custom view at any time.
For example, you have a worksheet that contains data about four different sales regions. You can set up a view
of each region's data and then save them. When you open the worksheet you can then request a certain view
and the data is displayed.
What can be stored in custom views? The stored settings include display options, window size and position
on the screen, window splits or frozen panes, the sheet that is active, and the cells that are selected at the time
the view is created. You can also optionally save hidden rows, hidden columns, filter settings, and print
settings.
The custom view includes the entire workbook. If you hide a sheet before a view is added, Excel hides the
sheet when you show the view.
Hiding all or part of a workbook
When you hide parts of a workbook, the data is removed from view but is not deleted from the workbook. If
you save and close the workbook, the hidden data remains hidden the next time the workbook is opened. If
you print the workbook, Microsoft Excel does not print the hidden parts. To prevent others from displaying
hidden sheets, rows, or columns, you can protect the workbook or sheet with a password.
Hide workbooks and sheets You can hide workbooks and sheets to reduce the number of windows and
sheets on the screen and to prevent unwanted changes. For example, you can hide sheets that contain sensitive
data, or you can hide a workbook containing macros so that the macros are available to run but no window
appears for the macro workbook. The hidden workbook or sheet data is accessible, and other documents can
use the information.
Hide rows and columns You can also hide selected rows and columns of data that you aren't using or that
you don't want others to see.
Hide window elements You can hide most window elements in order to use as much of your
screen as possible to display worksheet data. These window elements include the
Microsoft Windows taskbar and the Excel title bar, toolbars, formula bar, and status bar. These
elements remain hidden only as long as the workbook is open; they are redisplayed when you close
the workbook and open it again.

Zoom the display

In the Zoom box, click the size you want, or enter a number from 10 to 400.
To enlarge the selected area to fill the window, click Selection.
Note Zooming in or out does not affect printing. Sheets are printed at 100 percent unless you change the
scaling on the Page tab of the Page Setup dialog box (File menu).

Excel Summary Notes 28

Macros
If you perform a task repeatedly in Microsoft Excel, you can automate the task by using a macro. A macro is a
series of Excel commands and instructions that you group together as a single command to accomplish a task
automatically.
Here are some typical uses for macros:
 To speed up routine editing and formatting
 To combine multiple commands; for example, inserting a table with a specific size and borders, and
with a specific number of rows and columns
 To make an option in a dialog box more accessible
 To automate a complex series of tasks
Excel offers two ways for you to create a macro, the macro recorder and the Visual Basic Editor.
For quick access to your macro, you can assign it to a toolbar, a menu, or shortcut keys. Running the macro is
as simple as clicking the toolbar button or menu command or pressing the shortcut keys.
If you give a new macro the same name as an existing built-in command in Excel, the new macro actions will
replace the existing actions. For example, if you record a new macro and name it File Close, it becomes
attached to the Close command. When you choose the Close command, Excel performs the new actions you
recorded.
Create a macro
You can create a macro by using the macro recorder to record a sequence of actions, or you can create a macro
from scratch by entering Visual Basic for Applications code in the Visual Basic Editor.
You can also use both methods. You can record some steps, and then enhance them with additional code.
By recording steps
1. On the Tools menu, point to Macro, and then click Record New Macro.
2. In the Macro name box, type a name for the macro.
3. In the Store macro in box, click this workbook in which you want to store the macro.
4. In the Description box, type a description for the macro.
5. If you don't want to assign the macro to a toolbar, a menu, or shortcut keys, clicks OK to begin
recording the macro.
To assign the macro to a toolbar or menu, click Toolbars, and then click the Commands tab. In the
Commands box, click the macro you are recording, and drag it to the toolbar or menu you want to
assign it to. Click Close to begin recording the macro.
To assign the macro to shortcut keys, click Keyboard. In the Commands box, click the macro you are
recording. In the Press new shortcut key box, type the key sequence, and then click Assign. Click
Close to begin recording the macro.
6. Perform the actions you want to include in your macro.
You can use the mouse to click commands and options, but the macro recorder cannot record mouse
actions in a document window. To move the insertion point or select, copy, or move text, for example,
you must use shortcut keys.
7. To stop recording your macro, click Stop Recording.
From scratch using Visual Basic for Applications
Excel Summary Notes 29
 1. On the Tools menu, point to Macro, and then click Macros.
2. In the Macros in list, click the template or document in which you want to store the macro.
3. In the Macro name box, type a name for the macro.
4. Click Create to open the Visual Basic Editor.
Note If you give a new macro the same name as an existing built-in command in Microsoft Word, the new
macro actions will replace the existing actions. To view a list of built-in macros in Word, point to Macro on
the Tools menu, and then click Macros. In the Macros in list, click Word Commands

Pause and restart recording a macro

1. To suspend recording, click Pause Recording on the Stop Recording toolbar that appears when you
are recording a macro.
2. Perform any actions you don't want to record.
3. To resume recording, click Resume Recorder.
Run a macro
1. On the Tools menu, point to Macro, and then click Macros.
2. In the Macro name box, click the name of the macro you want to run.
If the macro doesn't appear in the list, select a different workbook in the Macros in box.
3. Click Run.

Edit a macro
Open a macro in the Visual Basic Editor to make corrections, remove unnecessary steps, rename or copy
individual macros, or add instructions that you cannot record in Microsoft Word.
1. On the Tools menu, point to Macro, and then click Macros.
2. In the Macro name box, click the name of the macro you want to edit.
If the macro doesn't appear in the list, select a different workbook in the Macros in box.
3. Click Edit.

Rename macros
1. On the Tools menu, click Templates and Add-Ins.
2. Click Organizer.
3. Click the Macro Project Items tab.
4. In the In box on the left, click the entry you want to rename, and then click Rename.
5. In the Rename dialog box, type a new name for the entry.
6. Click OK, and then click Close.

Excel Summary Notes 30

Delete macros
1. On the Tools menu, point to Macro, and then click Macros.
2. In the Macro name box, click the name of the macro you want to delete.
If the macro doesn't appear in the list, select a different workbook, or list in the Macros in box.
3. Click Delete.

File Management
File management includes moving, copying, deleing, renaming the files you have created I ms-excel.
Although it’s a little easier to work with and organize files using windows explorer or my computer you can
also perform a surprising number of file management chores right in excel especially with its new and
improved open or save dialog box.
Basic fie management in the open dialog box.
1. Open the open or save as dialog boxes by selecting Open, or Save As from the File menu.
2. Right click the file and refer to the table below: File shortcuts menu commands for a list of things you
can do to the selected file or select the file and select a command from the Tools menu.
Table 1: File shortcut menu commands

Command Description.
Open Opens the selected file
Open read only Opens the selected file so that it can be read but not changed
Open as copy Creates a copy of the selected file with the name “copy of” and the name of the original
file and then opens the new copied file.
Print Sends the selected file to the default printer
Quick view Displays the contents of the file without opening the file.
Send to Depending on how your computer is setup, it lets send the selected file to a printer, to a
floppy , my documents etc.
Cut Used in conjunction with paste command to move files. Cuts or removes the selected file
from its current folder
Copy Used in conjunction with paste command to copy files. Copies the selected file.
Paste Pastes a cut or copied files
Create shortcut Create a shortcut- a quick way to a file or folder without having to go to its permanent
location- to the file.
Delete Deletes the selected file or files
Rename Renames the selected file
Properties. Displays the properties of the selected file, such as when the file was created or last
modified, or how large the file is

To change how files are displayed.

 Click the view button arrow and select a view.

Excel Summary Notes 31

Password protect a worksheet or workbook
Protect worksheet elements
Protect worksheet elements from all users
1. Switch to the worksheet you want to protect.
2. Unlock any cells you want users to be able to change: select each cell or range, click Cells on the
Format menu, click the Protection tab, and then clear the Locked check box.
3. Hide any formulas that you don't want to be visible: select the cells with the formulas, click Cells on
the Format menu, click the Protection tab, and then select the Hidden check box.
4. Unlock any graphic objects you want users to be able to change.
You don't need to unlock buttons or controls for users to be able to click and use them. You can unlock
embedded charts, text boxes, and other objects created with the drawing tools that you want users to be
able to modify. To see which elements on a worksheet are graphic objects, click Go To on the Edit
menu, click Special, and then click Objects.
1. Hold down CTRL and click each object that you want to unlock.

2. On the Format menu, click the command for the object you selected: AutoShape, Object, Text Box, Picture,
Control, or WordArt.

3. Click the Protection tab.

4. Clear the Locked check box, and if present, clear the Lock text check box.

5. On the Tools menu, point to Protection, and then click Protect Sheet.
6. Type a password for the sheet.
Note The password is optional; however, if you don't supply a password, any user will be able to
unprotect the sheet and change the protected elements. Make sure you choose a password you can
remember, because if you lose the password, you cannot gain access to the protected elements on the
worksheet.
7. In the Allow all users of this worksheet to list, select the elements that you want users to be able to
change.
8. Click OK, and if prompted retype the password.
Give specific users access to protected ranges
You must have Windows 2000 to give specific users access to ranges.
1. On the Tools menu, point to Protection, and then click Allow Users to Edit Ranges. (This command
is available only when the worksheet is not protected.)
2. Click New.
3. In the Title box, type a title for the range you're granting access to.
4. In the Refers to cells box, type an equal sign (=), and then type a reference or select the range.
5. In the Range password box, type a password to access the range.

Excel Summary Notes 32

 The password is optional; if you don't supply a password, any user will be able to edit the cells.
6. Click Permissions, and then click Add.
7. Locate and select the users to whom you want to grant access. If you want to select multiple users,
hold down CTRL while you click the names.
8. Click OK twice, and if prompted retype the password.
9. Repeat the previous steps for each range for which you're granting access.
10. To retain a separate record of the ranges and users, select the Paste permissions information into a
new workbook check box in the Allow Users to Edit Ranges dialog box.
11. Protect the worksheet: Click Protect Sheet in the Allow Users to Edit Ranges dialog box.
12. In the Protect Sheet dialog box, make sure the Protect worksheet and contents of locked cells check
box is selected, type a password for the worksheet, click OK, and retype the password to confirm.
Note A sheet password is required to prevent other users from being able to edit your designated ranges.
Make sure you choose a password you can remember, because if you lose the password, you cannot gain
access to the protected elements on the worksheet.
Protect workbook elements and files
Protect workbook elements
1. On the Tools menu, point to Protection, and then click Protect Workbook.
2. Do one or more of the following:
o To protect the structure of a workbook so that worksheets in the workbook can't be moved,
deleted, hidden, unhidden, or renamed, and new worksheets can't be inserted, select the
Structure check box.
o To protect windows so that they are the same size and position each time the workbook is
opened, select the Windows check box.
o To prevent others from removing workbook protection, type a password click OK, and then
retype the password to confirm it.

Protect a shared workbook

1. If the workbook is already shared, and you want to assign a password to protect the sharing, unshare
the workbook.
1. Have all other users save and close the shared workbook (shared workbook: A workbook set up to
allow multiple users on a network to view and make changes at the same time. Each user who
saves the workbook sees the changes made by other users. You must have Excel 97 or later to
modify a shared workbook.). If other users are editing, they will lose any unsaved work.
2. Unsharing the workbook deletes the change history (change history: In a shared workbook,
information that is maintained about changes made in past editing sessions. The information
includes the name of the person who made each change, when the change was made, and what
data was changed.). If you want to keep a copy of this information, print out the History
worksheet (History worksheet: A separate worksheet that lists changes being tracked in a
shared workbook, including the name of the person who made the change, when and where it
was made, what data was deleted or replaced, and how conflicts were resolved.) or copy it to
another workbook.
Excel Summary Notes 33
 1. On the Tools menu, point to Track Changes, and then click Highlight Changes.
2. In the When box, click all.
3. Clear the Who and where check boxes.
4. Select the List changes on a new sheet check box, and then click OK.
5. Do one or more of the following:
 To print the History worksheet, click Print.
 To copy the history to another workbook, select the cells you want to copy, click
Copy, switch to another workbook, click where you want the copy to go, and
click Paste.
Note You may also want to save or print the current version of the workbook, because
this history might not apply to later versions. For example, cell locations, including row
numbers, in the copied history may no longer be current.
3. On the Tools menu, click Share Workbook, and then click the Editing tab.
4. Make sure that you are the only person listed in the Who has this workbook open now box.
5. Clear the Allow changes by more than one user at the same time check box.
If this check box is not available, you must unprotect the workbook before clearing the check
box.
1. Click OK, point to Protection on the Tools menu, and then click Unprotect Shared
Workbook.
2. Enter the password if prompted, and then click OK.
3. On the Tools menu, click Share Workbook, and then click the Editing tab.
6. When prompted about the effects on other users, click yes.
2. Set other types of protection if you want: Give specific users access to ranges, protect worksheets,
protect workbook elements, and set passwords for viewing and editing.
3. On the Tools menu, point to Protection, and then click Protect Shared Workbook or Protect and
Share Workbook.
4. Select the Sharing with track changes check box.
5. If you want to require other users to supply a password to turn off the change history or remove the
workbook from shared use, type the password in the Password box, and then retype the password
when prompted.
6. If prompted, save the workbook.

Protect a workbook file from viewing or editing

1. On the File menu, click Save As.
2. On the Tools menu, click General Options.
3. Do either or both of the following:

Excel Summary Notes 34

 o If you want users to enter a password before they can view the workbook, type a password in
the Password to open box, and then click OK.
o If you want users to enter a password before they can save changes to the workbook, type a
password in the Password to modify box, and then click OK.
4. When prompted, retype your passwords to confirm them.
5. Click Save.
6. If prompted, click yes to replace the existing workbook.

Excel Summary Notes 35