SPREADSHEET NOTE(EXCEL) University of kisubi

ELECTRONIC SPREADSHEETS

A Spreadsheet is a grid of rows and columns that may contain numbers, text, formulas, graphics, etc.
Electronic spreadsheets replace the paper version of worksheets with in-built calculation facilities. The
purpose of a spreadsheet is to solve problems that involve numbers. They have enormous advantages,
including the ability to carry out calculations automatically. This ability can save the user almost a lifetime
of tedious arithmetic.

In modern business, various people like Managers, Accountants, Secretaries, Auditors amongst others, use
electronic spreadsheets as MIS (Management Information Systems) tools for policy analysis because they
offer speed, efficiency, flexibility and functionality that meet the needs of these policy managers. Computer
spreadsheets can handle a variety of applications such as financial planning, break-even analysis, budgeting
and project cost projections, what-if analysis, sensitivity analysis, cost planning and regressions.

These programs are particularly useful in planning, where their capacity to recalculate a set of related
figures, allows the user to explore the implications of alternative sets of assumptions relatively easily.

Operational: Spreadsheets used to facilitate tracking and monitoring of workflow to support operational
processes, such as a listing of open claims, unpaid invoices and other information that previously would
have been retained in manual, paper file folders. These may be used to monitor and control that financial
transactions are captured accurately and completely.

Analytical/Management Information: Spreadsheets used to support analytical review and management

decision-making. These may be used to evaluate the reasonableness of financial amounts.

Financial: Spreadsheets used to directly determine financial statement transaction amounts or balances that
are populated into the general ledger and/or financial statements.

Apart from the spreadsheet facility, they also provide the user with facilities for handling databases in order
to organise and process structured information and graphics to produce diagrams such as pie-charts, bar-
graphs, line graphs, radar charts, price index charts e.t.c. Unlike data, which is presented in a series of
numbers, charts present data in a way, which gives a much clear immediate visual interpretation.

Electronic spreadsheets include software applications like:

 Lotus 1-2-3
 Microsoft Excel
 Quattro-Pro
 Visual Calc
 Framework

1
SPREADSHEET NOTE(EXCEL) University of kisubi

MICROSOFT EXCEL
Among the various spreadsheets, we will look at Microsoft Excel (MS-Excel), which is the most commonly
used spreadsheet application. Just like other software applications Excel has undergone various updates. The
common versions used today include MS-Excel 97 (8.0), MS-Excel 2000 (9.0), MS-Excel 2002 (XP)(10),
MS-Excel 2003 (11), MS-Excel 2007, MS-Excel 2010,2013 and today MS-Excel 2016.
234560
GENERAL INTRODUCTION TO MS-EXCEL
MS-Excel is a general-purpose electronic spreadsheet application that can be used to organise data and do
practically anything that requires manipulation of figures. MS-Excel combines powerful spreadsheet and
presentation features in an easy to use graphical environment of windows.
STARTING MS-EXCEL
1. Click on the Start button on the Taskbar.
2. On the Start Menu that appears, move the mouse pointer to the Programs menu
3. When the programs submenu appears, cross and click on Microsoft Excel. The Excel screen is
loaded onto your monitor/ screen.
OR
1. Click on the Start button on the Taskbar
2. On the Start Menu that appears, click on the RUN menu
3. On the Run dialog box that appears, type EXCEL and click on the OK button.
OR
Double click on the Microsoft Excel Icon, if it appears on the Desktop screen.

NOTE: To start MS-Excel 2003, after steps 1 and 2 above, cross and point at Microsoft Office, then cross
again and click on Microsoft Office Excel 2003.

PARTS OF THE MICROSOFT EXCEL SCREEN/WINDOW.

When Excel is loaded or started, the Excel user interface displays a new empty workbook in the application
workspace. An Excel Window consists of two windows i.e the Application Window which holds the Menu
bar and Toolbars and the Document Window which holds the worksheets. The features in Ms-Excel window
include;

TITLE BAR:
The coloured bar at the top of the screen is called the Title Bar. It displays the program that is currently
running, that is Microsoft Excel in our case, and the name of the workbook/file currently open on the screen.
It also displays window control buttons (Minimise, Maximise, Close) at the right hand extreme.

MENU BAR:
This is located immediately below the Title Bar and gives you access to the complete range of commands
available in Ms-Excel application. When you first open Excel you will see a row of names running across
the Menu Bar. These are called Menus e.g File, Edit, Help e.tc. To Open a Menu with the Mouse, move the
mouse cursor to the name of the menu you want to open, then click the left mouse button once. To Open a
Menu with the Keyboard, hold down the Alt button on the keyboard, then press the underlined letter
specified in the Menu name you want to open.
Ellipsis:
Next to some Menu Options you will see three dots. This is an Ellipsis. An option with no ellipsis is a
simple one step action - such as Exit under the File Menu. An option with an Ellipsis will open a dialogue
box, as there is more than one step to carrying out this task or there are multiple outcomes possible from this
option e.g. the Open option in the File menu.

2
SPREADSHEET NOTE(EXCEL) University of kisubi

TOOLBARS:
The term Toolbar refers to the rows of buttons/icons running across the top section of the screen. Each row
is an independent Toolbar. The two toolbars visible when you open Excel are the Standard Toolbar which
holds file handling icons and the Formatting Toolbar beneath it which holds icons for changing text
appearance. To know what a button/icon on a toolbar does, move the mouse pointer on it and a short text
(screen tip) displaying its use will appear.
The Toolbars enable you to carry out the most common tasks very quickly. They are the same tasks
achievable in the menus.

Displaying and Hiding Toolbars:

To display or hide a toolbar, click on the View menu, select the Toolbar option, then cross click on the
toolbar you want to hide or display.

SCROLL BARS:
In each new worksheet you have 256 columns labelled from A to IV and 65,536 rows - which do not fit on
one screen! For this reason you have two Scroll Bars, the Vertical and Horizontal Scroll Bars, at the right
hand side and bottom of the screen respectively. The Scroll Bars enable you to view other areas of the
worksheet.
THE STATUS BAR:
This is the bar at the bottom of the screen that displays information about a selected command or an
operation in progress. The right side of the status bar shows whether keys such as CAPS LOCK, SCROLL
LOCK, NUM LOCK are turned on or not. Click on Status bar on the View menu to display or hide the
Status bar.

THE FORMULA BAR:

This appears below the Toolbars and has an equal sign (=) or (f *) before it. It displays the formula or the
text entered in a particular cell in Excel. The Formula bar can be used to edit the contents of a cell by
clicking on the cell and click on its contents displayed on the Formula bar then make the required
adjustments.
THE NAME BOX:
This is located on the far left side of the formula bar, this box identifies the cell your cursor is in (the active
cell) and will also show you the cell reference. The Address bar or Name Box enables you to know which
cell is ready to take a command from you at any time, as it shows the location of the cell pointer (the
rectangular shaped cursor in Excel). If the Address bar reads A1, it means that the active cell is A1.

WORKSHEET TABS:
These indicate whether a file/workbook contains more than one worksheet. They are normally labelled by
defaults as Sheet1, Sheet2, Sheet3 at the bottom of the Excel workbook.

TERMS ASSOCIATED WITH SPREADSHEETS

Spreadsheets are made up of a grid structure of columns and rows. Columns appear vertically while Rows
appear horizontally. The rectangular highlighted cell in the worksheet is the cell pointer or currently active
cell that will receive whatever is typed on the keyboard. There are several terms associated with
spreadsheets, these include the following;

A CELL

3
SPREADSHEET NOTE(EXCEL) University of kisubi

This is the intersection of a column and row. Cells appear as small boxes running downwards and across.
Each cell on the worksheet has its own name, or Cell Reference, or Cell Address. We use the cell
references/addresses when entering calculations onto the spreadsheet. There are two ways of finding out a
cell reference/address:

Column header

Row header

Active cell

Cells

(i) Above each column and to the left of each row, you will see a button with a single letter and number
respectively. Read the column letter first, then the corresponding row number to make the cell
address/reference - the active cell in the diagram above is A1, containing the number 1.
(ii) Click on the required cell then simply read its address on the Name Box.
NOTE:
- To enter data into a cell, click on a particular cell, then type in the required data and press the enter
key to mark the end of entry to that cell. To enter fractions type e.g 4 followed by space, then 1/2.
same as 4.5.
- To edit the contents of a cell, double click on the cell or click on the cell and press the enter key, or
click on the cell and click on its contents in the Formula bar then make the required adjustments.
- To delete cell contents, click on the cell or highlight them if more than one and press the delete key
on the keyboard. OR click on the edit menu, select clear, then cross to the submenu and click on All,
however you could also clear only Formats, Contents or Comments.
- To undo or redo changes made to a cell, click on the Edit menu and click on Undo or Redo as
necessary or Press the key combinations Ctrl + Z or Ctrl + Y respectively.

A RANGE:
This is a group of two or more adjacent cells. We identify ranges using a range address. A range address is a
reference to a group of two or more adjacent cells and has the format of top or left cell : bottom or right.

COLUMNS:
These appear vertically from top to bottom in a worksheet and are identified by column letters at the top
column header section of the worksheet. A column can have a maximum width of 255.

ROWS:
These appear horizontally from left to right in a worksheet and are identified by row numbers along the left
row header section of the worksheet. A row can have a maximum height of 409.

WORKBOOK:

4
SPREADSHEET NOTE(EXCEL) University of kisubi

An Excel file is called a WorkBook, which is composed of atleast one or more WorkSheets. The first new
WorkBook is called Book1.xls not until you save it and give it a new name. The workbook by default starts
with three WorkSheets called Sheet1, Sheet2, and Sheet3. You can move between the WorkSheets by
clicking on the WorkSheet tabs at the bottom of the Excel screen or pressing the key combinations Ctrl +
PgUp and Ctrl + PgDn.

WORKSHEET:
This is the biggest range because it consists of all the cells in one sheet. An Excel workbook can contain up
to 255 worksheets. By default, Excel displays three worksheets in a workbook labelled Sheet1, e.t.c.
However, one can rename, delete, copy, move, hide, unhide and even insert more worksheets as follows;
1. Renaming a worksheet.
To rename a worksheet follow the procedure below;
(i) Click on the worksheet tab of the sheet to rename e.g Sheet1
(ii) Click on the Format menu
(iii) Select sheet
(iv) Click on rename and then type the new name for the currently displayed worksheet.
OR
Double click the worksheet tab of the sheet you want to rename and type a new name for it.
OR
Renaming worksheets can also be done by clicking on the tab to rename with the right mouse button, select
rename on the menu that appears and type the new name.

2. Inserting more worksheets in a workbook.

By default, Excel displays three worksheets, but one can insert more worksheets a follows;
(i) Click on the Insert menu
(ii) Select worksheet. (select worksheet and click on OK if prompted).
OR
Right click on any worksheet tab besides which you want to insert a new sheet and select Insert on the menu
that appears, (select worksheet and click on OK if prompted).

3. Deleting worksheets
To delete a worksheet, follow the procedure below;
(i) Click on the worksheet tab of the sheet to be deleted
(ii) Click on the Edit menu
(iii) Select Delete Sheet and click on OK if prompted.
OR
Right click on the worksheet tab of the sheet to be deleted and select Delete on the menu that appears and
click on OK if prompted.

4. Moving Worksheets.
To rearrange the order of your worksheets in a workbook, follow the procedure below;
(i) Click on the Worksheet tab of the sheet to be moved
(ii) Click on the Edit menu
(iii) Select Move or Copy sheet
(iv) Select the Workbook to which you want to move the worksheet or leave the currently displayed
Workbook, then select the position between different worksheets to move the worksheet to and click
on OK.

5
SPREADSHEET NOTE(EXCEL) University of kisubi

OR
Right click on the Worksheet tab of the sheet to be moved, select Move or Copy sheet on the menu that
appears, then follow step (iv) above.

5. Copying worksheets.
(i) Click on the Worksheet tab of the sheet to be copied
(ii) Click on the Edit menu
(iii) Select Move or Copy sheet
(iv) Select the Workbook to which you want to copy the worksheet or leave the currently displayed
Workbook, then select the position between different worksheets to place the new copy
(v) Click in the Create a copy Checkbox and then click on OK.
OR
Right click on the Worksheet tab of the sheet to be copied, select Move or Copy sheet on the menu that
appears, then follow step (iv) and (v) above

6. Hiding and Unhiding Worksheets

For security purposes one can hide and later unhide worksheets. To do this follow the procedure below;
(i) Click on the Worksheet tab of the sheet to be hidden
(ii) Click on the Format menu
(iii) Select sheet and click on Hide.
To unhide worksheets, click on the Format menu, select sheet and click on unhide, then select the worksheet
to unhide and click on OK.

7. Linking Worksheets
You may want to use the value from a cell in another worksheet within the same workbook in a formula.
For example, the value of cell A1 in the current worksheet and cell A2 in the second worksheet can be
added using the format "sheetname!celladdress". The formula for this example would be "=A1+Sheet2!A2"
where the value of cell A1 in the current worksheet is added to the value of cell A2 in the worksheet named
"Sheet2".

FUNCTIONS OF SPREADSHEETS
Spreadsheets are basically used for entering data, saving, retrieving, formatting and printing spreadsheet
documents. These functions are discussed in detail below.
ENTERING DATA IN WORKSHEETS AND WORKING WITH FILES
To enter data move to the required cell address by clicking or using arrow keys and type the required data
item. Press enter to complete.
(a) Creating a new file
1. Click on the File menu
2. Click on New…(a new dialog box appears)
3. Select Blank workbook
4. Click on the OK button (if prompted)

Type in the data below beginning from cell A1

MOLLY CORNER
Item Description Cost Qty Amount
1. Yoghurt 400 6
2. Cakes 300 7
3. Ice cream 1000 3

6
SPREADSHEET NOTE(EXCEL) University of kisubi

4. Chocolates 1200 5
5. Crisps 500 4

This is a new file already created.

(b) Saving a file for the first time
To save a new file like the above,
1. Click on the File menu
2. Click on Save As…(A save as dialog box appears)
3. Type in a suitable name of the file to save, in the File name text box and,
4. Select a location to save the file in the Save in dropdown list box, or leave the default location
My documents.
5. Click on the Save button.

Unless a different extension is specified, Ms-Excel automatically assigns a document the file extension
.xls
Saving changes to an existing file
If the file was originally saved, then,
1. Click on the File menu
2. Click on Save
OR
Press the key combination CTRL + S on the keyboard.
(c) Retrieving/Opening a saved file
To open a saved file
1. Click on the File menu
2. Click on Open on the menu that appears (an open dialog box appears)
3. Select a location that the file was saved in the Look in dropdown list box
4. Click on the required file name or type the file name to be opened
5. Click on the Open button
(d). To copy/ Move a range
Data can easily be copied or moved from one range to another e.g. using the above file.
1. Select the required range (e.g. A1:D7)
2. Click on Edit
3. Select Copy (for copying) or Cut (for moving)
4. Move to a new cell where you want to retrieve the data and,
5. Click on the Edit menu
6. Select Paste

SEARCHING FOR DATA

Finding Data - used to locate data in a worksheet. Click on the EDIT menu, then click on FIND.
Type the word to be found, then click on the Find Next button.

Replacing Data - this is an extension of the Find option. Once information is found, you can
substitute other data in place of that.

Click on the EDIT menu, then click on REPLACE. Type the word to be found and that to Replace it
with, then click on the Replace All button.

7
SPREADSHEET NOTE(EXCEL) University of kisubi

ZOOM VIEW - You can simply magnify or reduce the view of the worksheet you are working on. This
allows you to get a closer view of parts of the worksheet or look at a smaller view of the entire sheet. This
DOES NOT affect the way it will print.

NAVIGATING THE WORKSHEET.

The following table describes the various keys that can be used to move around the worksheet.

Key Action
Arrow keys Move cursor, one cell either to the left, right, down or up
Tab Moves one screen full to the right
Shift + Tab Moves one screen full to the left
Pg. Up Moves one screen full upwards
Pg. Down Moves one screen full downwards
Home Moves to the left most cell, normally in column A
End + Arrow key Moves to the end of a range in the direction of the arrow
F5 (Goto) Moves to a specified cell
Ctrl + Right Arrow key Moves to the last column, IV
Ctrl + Left Arrow key Moves to the first column, A
Ctrl + Down Arrow key Moves to the last row e.g row 65,536
Ctrl + Up Arrow key Moves to the first row, i.e row 1
Equals Sign Start a formula
ESC Cancel Entry
Enter Complete entry
F2 Activate a cell for editing
CTRL+SEMICOLON Enter the date
CTRL+SHIFT+COLON Enter the time

TYPES OF DATA ENTRIES

MS-Excel classifies every entry either as a value entry or a label entry.
Values
Any entry that begins with a number (0-9) or the symbols +, -, #, @, $, is a value entry. All value entries are
right aligned in the cell.

Labels
A label is a text entry, which can include any combination of characters and numerals e.g. P.O.Box 7070
Kampala, or Plot 3/4/5 Kiira Road, can all be entered as labels so long as the number entries are not
intended to be used for calculations. All label entries are left aligned by default.

Label prefixes
Labels can be preceded by any of the characters below. (Some of these may not be applicable to higher
versions of MS-Excel)
‘ -Left Aligns Labels
“ -Right Aligns Labels
^ -Center Aligns Labels
/ -Creates a non-printing label, which will be displayed on screen but not printed

8
SPREADSHEET NOTE(EXCEL) University of kisubi

Some of the above prefixes can also be used to enter values as labels e.g. to enable spreadsheets recognise
1999 as a label and not a value, precede it with a ‘1999.

MANAGING THE WORKSHEET

Inserting columns, rows and cells;
(a) Rows:
To insert a row in any part of the worksheet;
1. Position the cursor in the row that you want a new row inserted
2. Click on the Insert menu
3. Click on Rows (a new row appears)
(b) Columns:
To insert a column in any part of the worksheet;
1. Position the cursor in the column that you want a new column inserted
2. Click on the Insert menu
3. Click on Columns (a new column appears)
(c) Cells:
To insert a cell in any part of the worksheet;
1. Position the cursor in the cell that you want a new cell inserted
2. Click on the Insert menu
3. Click on Cells
4. On the insert dialog box that appears, select either to shift cells to the right, shift cells to the left,
move the entire row or move the entire column.
5. Click the OK button for changes to take effect.

(d) Deleting Columns

To delete a Column follow the steps below;
1. Position the cursor in any column to delete,
2. Click on the Edit menu
3. Select Delete…
4. Select Entire column on the Insert dialog box that appears
5. Click on the OK button(The selected column disappears)

(e) Deleting Rows

To delete a Row follow the steps below;
1. Position the cursor in any row to delete,
2. Click on the Edit menu
3. Select Delete…
4. Select Entire row on the Insert dialog box that appears
5. Click on the OK button(The selected row disappears)
If deleting a sheet make sure the sheet to delete is the current one then carry out the above procedure, but
instead of step 3 and step 4, select Delete Sheet.

(f) Hiding an d displaying Columns/ Rows;

Columns can be hidden or redisplayed in a worksheet as follows;
1. Select the columns to hide
2. Choose Format
3. Select Columns/ Rows
4. Hide

9
SPREADSHEET NOTE(EXCEL) University of kisubi

The data disappears from the worksheet but remains intact and can be redisplayed when needed as follows;
1. Select both adjacent columns before and after the hidden columns e.g. if column D&E are hidden
2. Select C and F then;
3. Choose Format
4. Select Column/ Rows
5. Unhide

(g) Changing column width and row height

Column Width
When a value is too wide to fit in a cell it appears as a scientific notation or as a line of #######. Labels are
always truncated to only the fitting characters, or they overlap if the next cells are empty. To change the
column width, follow the procedure below;
1. Select the column to change its width
2. Click on the Format menu
3. Select Column
4. Click Auto fit selection or select width… and specify a value between 1 – 240 in the set width box.
5. Click on the OK button.
OR
1. Place the mouse pointer at the right hand edge of the column to be adjusted at the top column header
section.
2. When the cursor changes to a double headed arrow, press and hold the left mouse button and then
drug the mouse to adjust the column width.
OR
Place cursor on line between column headings until it turns into a double-headed arrow and DOUBLE
CLICK.

Row Height:
MS-Excel automatically adjusts row height to match the height of the biggest font size. To change it
manually, select the row(s) to change, and
1. Click on the Format menu
2. Select Row
3. Select Autofit or click on Height… and specify a value
4. Choose OK
OR
Place mouse pointer between rows on the line separating the column headings. Pointer will become a
double-headed arrow. Click and drag to the desired column width

CELL AND FONT FORMATTING:

To format is to change the appearance or presentation of your work. This module explores the formats that
can be applied to text and the appearance of cells.

Changing font styles and attributes

The font style and attributes of a selected range of data can be changed as follows;
1. Select the range of data to format
2. Click on the Format menu
3. Select Cells…
4. Click on the Font tab

10
SPREADSHEET NOTE(EXCEL) University of kisubi

(a) To change the Font Face or the type of lettering;

5. Select the appropriate font from the Font list box e.g Arial, Tahoma e.t.c
(b) To change the Font Style;
5. Select the appropriate font style from the Font Style list box e.g bold, italic e.t.c
(c) To change the Font Size;
5. Select the desired font size from the Size list box e.g 12, 14, 18, e.t.c
(d) To change the Font Colour;
5. Select any of the existing colours from the colour combo box
(e) To change the Font Underline;
5. Select any of the existing underlines from the underline combo box
(f) To change the Font effect;
5. Select a particular effect from the effects options group e.g superscript, subscript e.t.c
6. Click on the Ok button for the changes to be effected.

Shading cells
To shade cells means to apply colour and or pattern to a cell or a range of selected cells. To shade a cell(s),
follow the steps below;
1. Select the range of cells to which you want to apply shading
2. Click on the Format menu
3. Select Cells…
4. Click on the Patterns tab
5. Under colour, select a colour of your choice. One can also select a particular pattern in the pattern
combo box if necessary.
6. Click one the OK button for the changes to take effect.

Changing alignment
You can change the alignment for both values and labels either horizontally or vertically, one can also
change the orientation of a cell content as required.
To change the alignment, follow the procedure below;
1. Select the range for which you want to change the alignment
2. Click on the Format menu
3. Select Cells…
4. Click on the Alignment tab
5. Select an option under the Horizontal drop down list box e.g. center
6. Select an option under the Vertical drop down list box e.g. bottom
7. In the Degrees box under Orientation, select an angle of direction for the text.
8. In the Text control options group, you can choose Wrap text to avoid text overlapping to other cells,
Shrink to fit to reduce the Font size to fit on a particular cell or Merge to combine two or more cells
into one.
9. Click one the OK button for the changes to take effect.

Setting Cell Borders.

Borders ca be used to mark off various sections of the worksheet and make them easier to read. To insert
borders, follow the steps below;
1. Select the cells that you want to apply borders to
2. Click on the Format Menu
3. Select cells.
4. On the Format cells dialog box that appears, click on the Borders tab.

11
SPREADSHEET NOTE(EXCEL) University of kisubi

5. In the line style section, select the style of line you want to apply as your borders.
6. In the presets section, click on Outline option to apply the borders around the selected cells or click
on the Inside option to apply the border to all lines within the selected cells. Select the None option
if you don’t want any borders.
7. In the Border section, you can customize the Borders by putting on and off a particular section of the
Borders.
8. Click on the OK button to effect the changes.
Number Formatting
In MS-Excel the numeric data format can always be changed as required, either with commas, currency
symbols($), percentages(%), e.t.c
1. Select the range of figures to format
2. Click on the format menu
3. Click on the Number format tab
4. Select the category of number format you want to apply in the category list box
5. Make the appropriate changes and click on the OK button
Hiding values
If you want to hide the data in a cell, select the cells containing the values to be hidden, then click on the
Format menu, click on cells, click on the number tab, in the category list, select custom and in the type box,
enter three semi-colons (;;;)

Merge and Center

The “Merge and Center” button on the formatting toolbar can be a timesaver, especially when working with
text titles. If you have content that spills beyond one cell, select the content and click this button to have
Excel automatically merge the affected cells together and center the content inside the merged cells.
Although this button works with numeric content as well as text content, it’s most often used for text.

Freezing Panes
Rows and or columns can be frozen, so they remain visible as you scroll through the data in case the data is
too big to fit on one screen.
To freeze panes, move the cell pointer to the cell immediately below the rows and to the right of the
columns you want to freeze and then
i) Click on the Window menu
ii) Select Freeze Panes
iii) To clear frozen panes,
iv) Click on the Window menu
v) Select Unfreeze panes.

OPERATORS IN MS-EXCEL

(i) Arithmetical operators

Operator Description
+ (Plus) Addition
- (Minus) Subtraction
* (Asterisk) Multiplication
/ (Forward slash) Division
^ (Curate) Exponential/ Square
% (Percent) Percentage

12
SPREADSHEET NOTE(EXCEL) University of kisubi

: (Colon) Reference operator (A1:A5)

, (Comma) Union operator =sum(A1:A5, C1:C5)

(ii) Comparison operators

Operator Description
< Less than =5<4
> Greater than
= Equal to
< > or Not equal to
>= or => Greater than or equal to
<= or =< Less than or equal to

13
SPREADSHEET NOTE(EXCEL) University of kisubi

(iii) Logical operator

Operator Description
AND Logical and, returns true if both expressions are true
OR Logical or, returns true if either of the expressions is true
NOT Logical not, returns false if expression is true and true if expression is
false
() Parenthesis for grouping
TRUE Returns logical value True
FALSE Returns logical value False

Examples
=AND(2+2=4, 2+3=5) result is True
=AND(5<2,2<5) result is False
=OR(5<2,2<5) result is True
=NOT(1+1=2) result is False

Combining Text
Text can also be combined from different cells into a single cell.
e.g =A1&” “&A2

NAMING CELLS AND RANGES

You can assign names to cells and cell ranges and then use those names in your formulas. Using names
instead of cell references is convenient because it eliminates the need to type complex cell references. Using
names instead of the column and row labels used in natural language formulas allows you to refer to named
cells from anywhere in the workbook, or even from another workbook.

Defining Names with the Name Box

The easiest way to define a name is to use the Name box in the formula bar. To do this, follow the steps
below;
i) Click on the cell or select the range of cells to be named
ii) Click in the Name Box
iii) Delete the cell reference that appears, then type the new name and press the enter key.

OR
i) Click on the cell or select the range of cells to be named
ii) Click on the Insert menu, point at Name, then cross and click on Define
iii) On the dialog box that appears, type the new name in the “Names in the workbook” box and click on
the OK button.

To clear Names, follow the procedure below;

i) Click on the Insert menu, point at Name, then cross and click on Define
ii) On the dialog box that appears, click on the Name to be cleared and then click on the Delete button.

If a selected cell or range is named, the name takes precedence over the cell reference and is displayed in the
Name box. For example, the Name box displayed C5 at the beginning of the exercise, but after renaming,
the Name box displays TestName when C5 is selected.

14
SPREADSHEET NOTE(EXCEL) University of kisubi

Using Names in Formulas

You can use the name of a cell or range in a formula. The result is the same as it would be if you entered the
reference of the cell or the range. For example, suppose you enter the formula
=A1+A2
in cell A3. If you define the name Mark as cell A1 and the name Vicki as cell A2, the formula
=Mark+Vicki. This returns the same value.
Rules for Naming Cells and Ranges
These rules will come in handy when you name cells and ranges in Excel.
 All names must begin with a letter, a backslash (\), or an underscore ( _ ).
 Numbers can be used.
 Symbols other than backslash and underscore cannot be used.
 Blank spaces are indicated by an underscore.
 Names that resemble cell references cannot be used.
 Single letters, with the exception of the letters R and C, can be used as names.

CELL COMMENTING
In MS-Excel, comments can be added to cells such that whenever the mouse pointer is moved over that
cell, the comment pops up and displays a specified message. This can be suitable especially if documenting
cell contents like special formulas or just adding a cell tip.
To do this follow the procedure below;
(i) Click on the desired cell, that you want to comment.
(ii) Click on the Insert menu
(iii) Click on Comment and type the required comment.

VALIDATING DATA ENTRY

You can use Microsoft Excel's Data Validation command to ensure that new entries to a list or database
meet certain criteria. You can specify the type of data you'll allow (whole numbers, dates, times, or text, for
example), as well as the range of acceptable values (for example, whole numbers between 1 and 100). You
can even set up a list of acceptable values (the names of your operating divisions, for example) and have
Excel create a drop-down list of those values.

Setting up validations.
To set up data validation criteria,
(i) Select the range of cells where you want the criteria to apply
(ii) Click on the Data menu and then click on Validation. Excel presents the Data Validation
dialog box.
(iii) In the Settings tab of this dialog box, indicate what kind of data will be allowed in the
Allowed drop down list box and what range of values you want to allow in the Data box.
(iv) The Input Message tab lets you create a prompt or message that tells the user (or you, if
you're setting up the criteria for yourself) what kind of data is allowed.
(v) The Error Alert tab lets you specify the message that appears if an entry is invalid.
(vi) Click on the OK button.

15
SPREADSHEET NOTE(EXCEL) University of kisubi

THE AUTOFILL FEATURE.

It is possible in MS-Excel to automatically fill or complete formulas and a set of known standard values or
text

Fill formulas into adjacent cells

1. Select the cell that contains the formula that you want to fill into adjacent cells.
2. Drag the fill handle (fill handle: The small black square in the lower-right corner of the selection. When
you point to the fill handle, the pointer changes to a black cross.+) across the cells that you want
to fill.

You can use the Auto Fill Options button , which appears after you drag the fill handle, to choose how to
fill the selection. For example, you can choose Fill Formatting Only or Fill Without Formatting.

Note You can also fill the active cell with the contents of the cell above it. Point to Fill on the Edit menu,
and then click Down (or press CTRL+D). To fill the active cell with the contents of the cell to the left, point
to Fill on the Edit menu, and then click Right (or press CTRL+R).
Tip You can automatically fill a formula downward, for all adjacent cells that it applies to, by double-
clicking the fill handle of the first cell that contains the formula. For example, you have numbers in cells
A1:A15 and B1:B15, and you type the formula =A1+B1 into cell C1. To copy that formula into cells
C2:C15, select cell C1 and double-click the fill handle.

Fill in a series of numbers, dates, or other built-in series items

Using the fill handle, you can quickly fill cells in a range with a series of numbers or dates or with a built-in
series for days, weekdays, months, or years.
1. Select the first cell in the range that you want to fill.
2. Type the starting value for the series.
3. Type a value in the next cell to establish a pattern.
4. Select the cell or cells that contain the starting values.
5. Drag the fill handle across the range that you want to fill.

To fill in increasing order, drag down or to the right. To fill in decreasing order, drag up or to the left.
For example, if you want the series 1, 2, 3, 4, 5..., type 1 and 2 in the first two cells. If you want the series 2,
4, 6, 8..., type 2 and 4. If you want the series 2, 2, 2..., you can leave the second cell blank.
When you fill a series, the selections are extended as shown in the following table. Note that in the
examples below, items separated by commas are placed in individual adjacent cells.

Initial selection/Highlight Extended series

1, 2, 3 4, 5, 6,...
9:00 10:00, 11:00, 12:00,...
Mon Tue, Wed, Thu,...
Monday Tuesday, Wednesday, Thursday,...
Jan Feb, Mar, Apr,...
January January, February,…
Jan, Apr Jul, Oct, Jan,...
Jan-99, Apr-99 Jul-99, Oct-99, Jan-00,...

16
SPREADSHEET NOTE(EXCEL) University of kisubi

15-Jan, 15-Apr 15-Jul, 15-Oct,...

1999, 2000 2001, 2002, 2003,...
1-Jan, 1-Mar 1-May, 1-Jul, 1-Sep,...
Qtr3 (or Q3 or Quarter3) Qtr4, Qtr1, Qtr2,...
text1, textA text2, textA, text3, textA,...
1st 2nd, 3rd,...
Product 1 Product 2, Product 3,...

Additional Information
You can also specify the type of series by using the right mouse button to drag the fill handle over the range
and then clicking the appropriate command on the shortcut menu (shortcut menu: A menu that shows a list
of commands relevant to a particular item. To display a shortcut menu, right-click an item or press
SHIFT+F10.). For example, if the starting value is the date JAN-2002, click Fill Months for the series FEB-
2002, MAR-2002, and so on; or click Fill Years for the series JAN-2003, JAN-2004, and so on.
If the selection contains numbers, you can control the type of series that you want to create. On the Edit
menu, point to Fill, and then click Series. Under Type, specify the options that you want to use. A Linear
series is calculated by adding the value in the Step Value box to each cell value in turn. A Growth series is
calculated by multiplying the value in the Step Value box by each cell value in turn. A Date series fills date
values incrementally by the value in the Step value box and dependent on the unit specified under Date unit.
An Auto Fill series produces the same results as dragging the fill handle.
You can suppress Auto Fill by holding down CTRL while you drag the fill handle. The selected values are
then copied to the adjacent cells, and Excel does not extend a series.

Fill data by using a custom fill series

To make entering a particular sequence of data (such as a list of names, sales regions, letters of the alphabet,
e.t.c) easier, you can create a custom fill series. A custom fill series can be based on a list of existing items
on a worksheet, or you can type the list from scratch.

Use a custom fill series based on a new list of items

1. On the Tools menu, click Options, and then click the Custom Lists tab.
2. In the Custom lists box, click New list, and then type the entries in the List entries box, beginning with
the first entry.

Press ENTER after each entry.

3. When the list is complete, click Add then OK buttons respectively.

4. On the worksheet, click a cell, and then type the item in the custom fill series that you want to use to
start the list.
5. Drag the fill handle across the cells that you want to fill.

17
SPREADSHEET NOTE(EXCEL) University of kisubi

Notes:
A custom list can contain text or text mixed with numbers. To create a custom list that contains only
numbers, such as 0 through 100, first select enough empty cells to contain the list. On the Format menu,
click Cells, and then click the Number tab. Apply the Text format to empty cells, and then type the list of
numbers in the formatted cells. Select the list and then import the list.

You cannot edit or delete a built-in fill series (such as a fill series for months and days), but you can edit or
delete a custom fill series. On the Tools menu, click Options, and then click the Custom Lists tab. In the
Custom lists box, select the list that you want to edit or delete. To edit the fill series, make the changes that
you want in the List entries box, and then click Add. To delete the fill series, click Delete

Alt + Down arrow key can also be used to provide a list of items in the above cells.

TYPES OF CELLS AND RANGE REFERENCE

There are three types of cell and range references in MS-Excel i.e.

(a) Relative cell reference

Relative references are the default type of cell reference. Calling cells by just their column and row
labels (such as "A1") is called relative referencing When a formula containing relative cell
references is copied to other cells, the cell references are updated or increment accordingly. . For
example, if a simple addition formula in cell C1 "=(A1+B1)" is copied to cell C2, the formula would
change to "=(A2+B2)" to reflect the new row.

(b) Absolute (fixed) cell reference

An absolute cell reference is used when you want the reference always to point to a particular
worksheet cell no matter where the formula containing the reference is copied to. Absolute cell
references are distinguished by having $ sign placed in front of the column and row identifiers, for
example $C$5. Absolute cell references do not change or increment when they are copied to other
cells. For example in creating the multiplication table below, only one formula can be inserted and
copied to other cell to complete the multiplication table.

Example:
A group of individuals went to CENTE Forex to exchange their local currency to foreign currency as
follows;
Currency Rate
Dollars 1700
Pounds 3000
Euro 1800
DM 1100

Name Shillings Dollars Pounds Euro DM

Mukasa 3,500,000 =b8/$b$2
Namiiro 2,000,000
Aketcho 1,500,000
Alum 7,000,000
Mugisha 6,500,000

18
SPREADSHEET NOTE(EXCEL) University of kisubi

Required:
Using the fixed exchange rates at the top of the table, enter appropriate formulae to compute the
different foreign currency equivalent of each individual’s Shillings value.
Save your work as Currency Exchange.

(c) Mixed cell reference

It is possible to have a mixed cell reference that is partly relative and partly absolute, for example,
$C5 or C$5. When formulas containing such references are copied, they will adjust in one direction
but not in the other direction. The example below shows the use of mixed references in creating a 10
by 10 multiplication table.

A B C D E F G H I J A
1 1 2 3 4 5 6 7 8 9 10
2 1 =$A2*B$1
3 2
4 3
5 4
6 5
7 6
8 7
9 8
10 9
11 10

WORKING WITH EXCEL FORMULAS

Formulae are the basis of all calculations in MS-Excel. When you calculate with formulae, your data is
dynamic. When changing the data, the formula automatically recalculates.
Definition:
A formula is a worksheet entry that calculates data. The result of a formula could be a number or texts. A
formula can contain numbers, text, operators, cell addresses, range names, functions and other formulas.
This can be as simple as subtracting a number from another or as complicated as finding the Net Present
Values of a series of future cash flows.

Building a Formula.
Formula can easily be built using operands, operators and separators like brackets.

To build a formula.
i) Select the cell where you want to enter the formula.
ii) Type = to begin the formula i.e tell the application to carry out a computation.
iii) Type in the first operand e.g. A5 or a number 6
iv) Type in the first operator e.g. +
v) Type in the next operand e.g. B5 or a number 8
vi) Press Enter to display the results.

For example, to find the Amount for yoghurt, assuming the unit cost of 400/= is in cell C3 and the Qty of 6
is in cell D3. Follow the procedure below.
Position the cursor in cell E3 that corresponds to Amount by clicking in it. Type = C3*D3 and press enter as
follows.

19
SPREADSHEET NOTE(EXCEL) University of kisubi

i) Type = to begin the formula

ii) Type in the first operand which is C3
iii) Type in the first operator which is *
Contemporary Issues in Early Childhood Development
iv)
v) Type in the next operand which is D3
The result of 2400 will be displayed.

Type of formula
There are three types of formulas in Spreadsheets.

(a) Numeric formulae;

These performs calculations in values, and use the +,-,/,* and ^ numeric operators for calculation. Most of
the calculations in worksheet are based on these. Typical examples include:-
=25+5
=15+A1
=A15*B1/ B5

(b) Text formulae;

These are used to manipulate text. The text is normally enclosed in “ “ (quotation marks) and to combine
strings of text, use &(ampersand). These can be used to combine text from different cells to one cell e.g. if
cell B3 contains Term one
cell C3 contains Term two
cell A3 contains Students

The following could be created as follows

Enter, =B3&” AND “&C3 in B10 to have, Term One and Term Two
Or =A3&” Marks” in A10 to have Students Marks.

(c) Logical Formulae

These are statements that evaluate a condition. They result into 1 if true and 0 if false. Logical formulas use
=, <, >, <=, >=, and <> and also AND, OR and NOT to calculate various conditions e.g.

Entry Results
=5>4 Result is True or 1
=5<A1 Result is 1 or True if A1 has a value less than 5
=AND(A1=1,A2=2) Result is 1 or True (if A1 contains 1 and A2 contains 2)
=OR(A1=1,A2=2) Result is 1 or True (if A1 contains 1 and A2 contains 2)
=NOT(A1=0) Result is 1 (if A1 contains any value but zero)

Within the Operators there is an order that Excel will always adhere to. Some people remember it by the
pneumonic BODMAS - although BEDMAS is more accurate!
Our original list of operators is in precedential order:

Brackets
Exponent
Divide
Multiply

20
SPREADSHEET NOTE(EXCEL) University of kisubi

Add
Subtract

Lack of awareness of Algebraic Precedence can lead to confusion:

eg We would calculate =10 +10*10 to give the answer 200 because we read from left to right.
Excel calculates this to give the answer 110 because the Multiplication is of higher algebraic precedence
than the Addition.
To correct the problem, insert brackets to give:
= (10+10)*10
Points to remember while using excel formula:
- A formula must always begin with an equal sign (=). It may also begin with a ‘+’ or ‘-‘ sign. In such
a case, Excel automatically adds an ‘=’ sign in the front.
- The length of the formula can be up to 255 characters long
- It cannot contain spaces, except alphabets, numbers or any symbols enclosed in quotation marks (i.e
string).

EXCEL FUNCTIONS

Excel has a library of built-in or pre-written formulas called functions that can make it
easier and faster to build models and analyze data. Functions can be a more efficient
way of performing mathematical operations than formulas. For example, if you
wanted to add the values of cells D1 through D10, you would type the formula
"=D1+D2+D3+D4+D5+D6+D7+D8+D9+D10". A shorter way would be to use the
SUM function and simply type "=SUM(D1:D10)". There are two easier ways to sum
values:

 Highlight the row or column, plus one blank cell for the result, and press [Alt]++.
 Highlight the row or column, plus one blank cell for the result and click AutoSum on the Standard
toolbar.

Entering Functions
There are two ways in which you can enter a formula using functions, the typing method and the Function
Wizard.
The advantage to using the Function Wizard in that the formula is typed for you in the formula bar. You do
not have to worry about the syntax, or composition, of the function.
The Type Method
 In order to enter the function manually:
 Type the equals sign =
 Type the name of the function
 Open parentheses
 Type the range (or another kind of argument)
 Press Enter.

21
SPREADSHEET NOTE(EXCEL) University of kisubi

The Function Wizard Method

When using the Function Wizard, there is no need to type the equals sign to begin the formula. Functions
are grouped into categories. To use the Function Wizard;
 Select the cell that will contain the function
 Choose the Function… command from the Insert menu or click on on the toolbar
The Function Wizard – Step 1 of 2 dialog box appears:
Fig. 1

 Scroll through the Function Category list and click on the appropriate category.
Note: Click on All in the category list, if you do not know the category of the function you wish to use.
 Scroll through the Function Name list and click on the function you wish to use.
The function name and its arguments appear below
 Click Ok
 The Function Wizard – Step 2 of 2 dialog box appears:
Fig. 2

 Drag through the cells to be included in the argument

 Click on OK
Note: you may move the Function Wizard dialog box to another location on your screen so that you can
select the cells you wish. You may also use the scroll bars to scroll through your sheet.

22
SPREADSHEET NOTE(EXCEL) University of kisubi

Function Example Description

returns the current
TODAY =TODAY() system date (leave the
parentheses empty)
Returns the current
system date and
NOW =NOW()
time(leave the
parentheses empty)
Finds the square root of
SQRT =SQRT(D10) the values in the
specified cell.
Returns the highest
MAX =MAX(C1:C100) values in the specified
range of cells.
Returns the lowest
MIN =MIN(D1:D100) values in the specified
range of cells.
Returns the number of
cells containing only
COUNT =COUNT(A1:A10) numeric values not text
and not blank, in the
specified range of cells.
Returns the number of
cells containing both
COUNTA =COUNTA(A1:A10) numeric and text data
but not blank in the
specified range of cells.
Returns the number of
cells satisfying a
=COUNTIF(A1:A10,”>10”)
COUNTIF particular condition in
=COUNTIF(A1:A10,”Male”)
the specified range of
cells.
Returns the number of
cells that are blank in
COUNTBLANK =COUNTBLANK(B1:B20)
the specified range of
cells.
=SUM(A1:A10) returns the sum of
SUM =SUM(A1:A10,B1:B5) values in the specified
range of cells.
returns the sum of
values satisfying a
SUMIF =SUMIF(A1:A10,”>=5”) particular condition in
the specified range of
cells.

23
SPREADSHEET NOTE(EXCEL) University of kisubi

finds the average of

AVERAGE =AVERAGE(B1:B10) values in the specified
range of cells.
Multiplies all the values
PRODUCT =PRODUCT(B1:B4) in the given range of
cells.
Checks whether a
condition is met and
IF =IF(A1>=10,”Good”,”Bad”)
returns one value if true
and the other if false.
Returns the number of
LEN =LEN(A5) characters in a string of
text.
Returns the specified
=LEFT(B3,4) number of characters
LEFT
=LEFT(“JANUARY”,3) from the start or left of
a string or text.
Returns the specified
=RIGHT(B3,4) number of characters
RIGHT
=RIGHT(“JANUARY”,3) from the end or right of
a string or text.
Returns the characters
from the middle of a
text string, given the
MID =MID(“ENTEBBE”,2,3)
starting position and
number of characters
from that position.
Returns the results of a
=POWER(A1,B1)
POWER number raised to a
=POWER(3,4)
power.
Repeats characters or
=REPT(“Entebbe”,5)
REPT text a given number of
=REPT(B2,4)
times.
Converts a text string to
=UPPER(A3)
UPPER all upper case or capital
=UPPER(“small letters”)
letters.
Converts a text string to
=LOWER(B2)
LOWER all lower case or small
=LOWER(“CAPITAL LETTERS”)
letters.

24
SPREADSHEET NOTE(EXCEL) University of kisubi

Function Wizard

View all functions available in Excel by using the Function Wizard.

 Activate the cell where the function will be placed and click the Function
Wizard button on the standard toolbar.
 From the Paste Function dialog box, browse through the functions by clicking
in the Function category menu on the left and select the function from the
Function name choices on the right. As each function name is highlighted a
description and example of use is provided below the two boxes.

 Click OK to select a function.

 The next window allows you to choose the cells that will be included in the
function. In the example below, cells B4 and C4 were automatically selected for
the sum function by Excel. The cell values {2, 3} are located to the right of the
Number 1 field where the cell addresses are listed. If another set of cells, such
as B5 and C5, needed to be added to the function, those cells would be added
in the format "B5:C5" to the Number 2 field.

 Click OK when all the cells for the function have been selected.

Autosum

25
SPREADSHEET NOTE(EXCEL) University of kisubi

Use the Autosum function to add the contents of a cluster of adjacent cells.

 Select the cell that the sum will appear in that is outside the cluster of cells
whose values will be added. Cell C2 was used in this example.
 Click the Autosum button (Greek letter sigma) on the standard toolbar.
 Highlight the group of cells that will be summed (cells A2 through B2 in this
example).
 Press the ENTER key on the keyboard or click the green check mark button on
the formula bar .

SPREADSHEET CHARTS
Spreadsheets like MS-Excel produce presentation quality charts. One can be able to create 2D or 3D type
charts. A chart is a visual representation of worksheet data.

EXCEL CHARTS
A chart is a graphical representation of Spreadsheet data. A chart converts data from rows and columns on
your worksheet into a visual format that can be read at a glance. It can be difficult to make sense of tables of
figures presented in a spreadsheet. Graphs or charts based on the figures can assist greatly in understanding
whatever is being presented. Creating charts in Excel is very straightforward; you simply highlight a range
of data in a worksheet, and prompt Excel to create the required chart. Charts can be created either as
separate sheets (As New Sheet) or embedded (As object in) in a worksheet. When the data used for creating
a chart in the worksheet is updated or changed, the charts also automatically update to reflect the new data.
In this section we will look at different ways to generate a chart and some of the formatting and
customisation options available.

There are 14 standard chart types and numerous sub types that can be created by Excel. The most popular
are the line, column, bar and pie charts. Excel also provides several built-in custom chart types.

26
SPREADSHEET NOTE(EXCEL) University of kisubi

Standard Chart Types (Commonly used types)

CHAR
NAME DESCRIPTION
T
Column A column chart allows a comparison of two or more items over time.
Values are represented as vertical columns. Each column represents a
single value/data point in the worksheet.
Bar Bar charts are very similar to column chart except the bars are
represented horizontally rather than vertically. They are often used to
compare the sizes of items at one point in time.
Line A line chart is good for showing trends over time. Where the time (or
other units of change) are plotted on the X axis. One can analyse actual
values as well as compare the slope of lines to measure the rate of
change.
Pie A pie chart is best used for comparing the percentages of a sum that
several numbers represent. The entire pie is the sum, and each number is
represented by a slice. A pie chart can be exploded by clicking on one
the slices and dragging it away from the rest.
Pie charts can only represent one data series, if more than one is
selected, Excel plots the first one.
Doughnut This chart is very similar to the pie chart. It is a variation on the pie
chart. Where as the pie chart is restricted to only one data series, the
Doughnut chart is not.
Area This chart shows the magnitude of change over time. It is particularly
useful when several components are changing and the user is interested
in the sum of the components.
Area charts let the user see the change on the individual components as
well as the change in the total. An area chart is a stacked line chart, with
the area between the lines filled with colour or shading. An area chart
plots data series one on top of the other.
XY or XY charts or Scatter charts are used to analyse the relationship between
Scatter two sets of data. It shows the relationship between pairs of numbers and
the trends they present. For each pair, one of the numbers is plotted on
the X-axis and the other number is plotted on the Y-axis. Where the two
meet, a symbol is placed on the chart. When a number is plotted, a
pattern may emerge. XY chart is also used for numerical analysis.
Radar A radar chart is used to show the relationship between individual and
group results. It shows data changes in relation to a center point and to
each other. The values axis radiates from the center point. Data from the
same series is connected by lines. This chart can be used to plot several
interrelated series and easily make visual comparison. It has specialist
use.

3-D Charts

27
SPREADSHEET NOTE(EXCEL) University of kisubi

There are 3-D versions of many of the basic chart types.

They are often used for enhanced presentation purposes.

CHART TERMS/ ELEMENTS

Excel uses a number of different terms to identify the elements of a chart as detailed below:

ELEMENT DESCRIPTION
Data Points/Marker Data points refer to each symbol in the chart that provides a visual
representation of a single data point or value from the worksheet.
They are represented by bars, lines, columns, slices, dots and other
figures (data markers). The values from the worksheet determine
the size/position of the data markers.
Data Series Data Points which come from the same row or column in a
worksheet are grouped into data series. Data series are simply a
group of similar data markers. The similar data markers may be
represented by the same colour, pattern, e.t.c.
Value Axis (y-axis) The value axis is the numerical scale which shows the value of the
data point. It is plotted on the vertical (y) axis.
Category Axis (x-axis) The category axis is the line where the various data series are
organised, or x-values in x-y charts.
Chart Area This is the entire chart and all it’s elements.
Gridlines These are lines we can add to a chart that make it easier to view
and evaluate data. Gridlines extend from the tick marks on an axis
across the plot area.
Tick Marks & Tick Mark Tick marks are small lines of measurement that intersect an axis,
Labels similar to divisions on a ruler. Tick mark labels identify the
categories, values or series in the chart.
Legend This is a key to the chart defining what the colours, lines or bars in
a chart represent.
Data Range This is the range of cells on your worksheet that you want to plot
data for.
Embedded charts An embedded chart is a chart displayed in the worksheet alongside
the data. When using the ChartWizard this is the default
Chart Sheets A Chart Sheet is added to the left of the worksheet that it is based
on when you choose to create a chart on a new sheet. (Insert menu,
Chart, As New Sheet).

The main parts of a chart are identified in the figure below.

28
SPREADSHEET NOTE(EXCEL) University of kisubi

Figure 0 - The Parts of a Chart

Creating Charts in Excel

You can create a chart in Excel using the data from an existing Excel worksheet. However, before you
create your chart, you will need to do a little planning.
• Consider what type of chart you want to create. Excel has 14 different chart types, but not all data is
appropriate for every chart.
• As you create your worksheet, consider what data you want highlighted in your chart and try to build a
worksheet that allows you to easily copy that data into the chart.

Charts in Excel are created using the Chart Wizard – a four step wizard which makes the chart creation
procedure fairly straightforward. The steps of the wizard are as follows:

a Chart Type
b Chart Source Data
c Chart Options
d Chart Location

Creating the Chart

1. Select the range of cells containing the data you want plotted in the chart.
If the data is not next to the text you want to use as chart labels, use the Ctrl key to select the
separate ranges.
2. Click on Insert menu and select Chart, or click on the Chart Wizard button on the Toolbar.
3. Select a Chart type in the Chart Wizard step1.
4. Select a Chart sub-type.
5. Click on the Next button.
6. The Chart Source Data dialog box will appear.
This dialog box shows a preview of the chart and demonstrates what data is being used to create the
chart.
7. Click on the Next button.

29
SPREADSHEET NOTE(EXCEL) University of kisubi

8. Select the Titles tab.

9. Type in an appropriate title for your chart.
10. Click on the Legend tab.
This section lets you decide whether or not you want to display your chart’s legend. If you chose to
display the legend, it also allows you to chose where you want the legend displayed.
12. Click on the Data Labels tab.
This section allows you to choose how you want to have your data labeled.

Summary of chart options

Titles Use to label the chart axes, and to add a title to the chart itself

Axes Use to customise or remove axis labels

Legend Choose whether to display a legend, and where to place it

Gridlines Choose whether to show horizontal and vertical gridlines, and at what intervals

Data Labels Choose to add labels containing data values to the data points

Data Table Choose to show the actual data values alongside the chart.

13. Click on the Next button.

14. Select where you want to place your sheet i.e either as a new sheet or as an object in the same sheet
as the data.
15. Click on the Finish button.
16. Excel will complete the chart and store it as a separate sheet in your workbook.
17. As a precaution, save your work.

Changing the Appearance of the Chart

Once a chart has been created it is possible to change the settings selected at any of the four steps of its
creation (chart type, source data, chart options, location).

Changing the Chart Properties

1. Select the chart. A Chart menu appears on the menu bar.

2. From the chart menu select Chart Type, Source, Options, Location as required and make the necessary
changes.

Formatting the Chart

Using The Format Menu

1. Select the part of the chart which you wish to modify.

2. Click on the Format menu.
Note that this menu is dynamic and the formatting options depend on the selected object.

By Double-clicking the Chart

30
SPREADSHEET NOTE(EXCEL) University of kisubi

1. Double-click on any part of the chart which you wish to modify

An appropriate dialogue box will appear offering options for altering the format/appearance of the
selected part.

Chart Wizard Step 1 - Chart Type

1. Before activating the Chart Wizard, you should select the data to be plotted, remembering to include the
data labels as shown in Figure 0.1.

Figure 0.1 - Selected Data to be Plotted

From the Insert menu select Chart.

Step 1 of the Chart Wizard appears as shown in .
In this step you are offered the chart types shown in the Figure.

1. Choose a suitable chart type from the left hand panel, and, if you wish, a sub-type from the right hand
panel, and press Next to continue to Step 2.

Figure 0.2 - Chart Wizard Step 1 – Selecting Chart Type

Chart Wizard Step 2 - Chart Source Data

The next Wizard Step, Step 2 Chart Source Data shown in Figure 0.6, has two important tabs; one for
controlling the data ranges to be plotted, and the other for selecting what to plot on the x-axis, and which
series to show on the y-axis.

31
SPREADSHEET NOTE(EXCEL) University of kisubi

Data Range Tab

Data Range Tab

Series Tab

Figure 0.3 - Chart Wizard Step 2 – Chart Source Data – Data Range Tab Dialogue

The range of cells selected in Step 1 will be displayed in the Data Range tab. This tab also allows you to
specify whether the data are organised in rows or columns. By default, the chart is automatically plotted
according to the structure of the data. If the data to be plotted has more columns than rows then the default
is set to Data Series in Rows (Figure 0.4).

Data points
grouped
from
columns
Data
series
grouped
from
rows

Figure 0.4 - Data Series in Rows

If there are more rows than columns in the data set then the Chart Wizard default is set to Data Series in
Columns (Figure 0.5).

32
SPREADSHEET NOTE(EXCEL) University of kisubi

Data
points
grouped
Data series
from rows
grouped
from
columns

Figure 0.5 - Data Series in Columns

1. Make sure the correct data range is selected in the dialogue box.

2. Select to plot Series in Rows or Columns as appropriate for the data.

Series Tab

Figure 0.6 - Chart Wizard Step 2 –Chart Source Data – Series Tab Dialogue

The Series tab dialogue allows you to select one series as the data to plot on the x- axis, and to choose which
series to plot on the y axis (you may have several different series plotting on the y axis). By default, Excel
will place the first series, and any series containing labels, on the x-axis.

3. Make sure that Excel has correctly identified which data to place on the x and y axes. Click on Next to
continue to Step 3.
Chart Wizard Step 3 - Chart Options

33
SPREADSHEET NOTE(EXCEL) University of kisubi

In Step 3 (Figure 0.7) you are presented with many more options for formatting and labelling your chart.
Take some time to explore the alternatives offered by the different tabs (Titles, Axes, Gridlines, Legend,
Data Labels, Data Table).

Figure 0.7 - Chart Wizard Step 3 – Chart Options

Titles Use to label the chart axes, and to add a title to the chart itself

Axes Use to customise or remove axis labels

Legend Choose whether to display a legend, and where to place it

Gridlines Choose whether to show horizontal and vertical gridlines, and

at what intervals

Data Labels Choose to add labels containing data values to the data points

Data Table Choose to show the actual data values alongside the chart.

1. When you have fine-tuned the chart to your liking, click on Next to continue to the final step (Step 4).

Chart Wizard Step 4 - Chart Location

In this step you are asked whether you want to embed the chart in the existing worksheet, or to place it in a
separate sheet.

As New Sheet

On this Sheet

34
SPREADSHEET NOTE(EXCEL) University of kisubi

Figure 0.8 - Chart Wizard Step 4 – Chart Location

1. If you select As Object in (the default), the chart appears embedded in the data sheet as shown in
Figure 0.9 – you can move it by clicking in the chart and dragging it to the required location. The
chart can also be re-sized as described in section .

If you choose As New Sheet the chart will appear in a separate window.

Figure 0.9 - Embedded Chart

Changing the Appearance of the Chart

Once a chart has been created it is possible to change the settings selected at any of the four steps of its
creation (chart type, source data, chart options, location). Formatting options may be accessed by several
routes.

Changing the Chart Properties

The Chart Menu

3. Select the chart.

A Chart menu appears on the menu bar.

4. From the chart menu select Chart Type, Source, Options, Location as required and make the necessary
changes.

The Chart Shortcut menu

1. Right click on the selected chart

The chart short-cut menu (Figure 0.10) appears which includes all four Chart
Wizard steps.

35
SPREADSHEET NOTE(EXCEL) University of kisubi

Figure 0.10 - Chart Short Cut Menu

Formatting the Chart
The Format Menu

3. Select the part of the chart which you wish to modify.

4. Click on the Format menu.

Note that this menu is dynamic, and the formatting options depend on the selected object.

Double-click the Chart

2. Double-click on any part of the chart which you wish to modify

An appropriate dialogue box will appear offering options for altering the format/appearance of the
selected part.

36
SPREADSHEET NOTE(EXCEL) University of kisubi

The Chart Toolbar

1. Select the chart.

The Chart Toolbar (Figure 0.11) appears. (You can also use the View menu and choose Toolbars to
reveal it).

Chart objects Chart Data By By Angle

box Format type Legend Table Row Column Text

List of chart
objects from
chart objects
box

Figure 0.11 - The Chart Toolbar.

2. From the Chart Objects box select the part of the chart which you wish to amend.

3. Click on the Format button to access a Format dialogue which will allow you to modify the selected part
of the chart.

4. Once you have amended the format settings in the Format Dialogue, click OK to implement the changes.

Changing the Chart Type

1. Use the Chart Type button from the Chart Toolbar to select an alternative chart type.

2. Use the By Row and By Column buttons on the Chart Toolbar to modify the data structure from
Rows to Columns and vice versa (refer to Section ).

Legends and Data Tables

1. Use the Legend button from the Chart Toolbar to toggle the legend on and off.

2. Use the Data Table button from the Chart Toolbar to toggle the Data Table display on and off.

Re-sizing Charts & Chart Objects

37
SPREADSHEET NOTE(EXCEL) University of kisubi

1. Select the chart, or the part of the chart which you wish to re-size.

2. A set of handles appear around the selected objects. Click and drag to re-size the selected object.

Adding & Removing Data Sets

Once you have worked through the chart wizard step-by-step, you may well decide that you want to add a
further series to the chart. Rather than repeat the whole chart creation process, you can simply select the data
to be added, and drag and drop or copy and paste the data into the chart. Similarly, you can also remove
unwanted data from charts without having to remove the entire chart.

This will work with both embedded charts and on charts which are stored on separate sheets.

To Add a Data Set to an Embedded Chart

Figure 0.12 - Dragging a Data Set to a

Chart

1. From the worksheet select the data you want to add to the chart, including any labels.

2. Point to the border area of the selected data and when the pointer is arrow shaped click and drag the
data (as if moving the data in a normal drag and drop operation) over the chart and release.

3. The new data set will automatically be added to the chart.

To Add a Data Set to a Chart Sheet

1. From the worksheet select the data you want to add to the chart, including any labels. Click on the
Copy button.

2. Select the Chart Sheet.

38
SPREADSHEET NOTE(EXCEL) University of kisubi

3. Click on the Paste button.

To gain more control over the way that your data is plotted choose Paste Special. The Paste Special
dialogue box is displayed (Figure 0.13).

4. In the Add Cells As group specify whether you are adding a series of data, New Series or data point(s),
New Point(s).

5. In the Values(Y) In group specify whether the data is

organised in Rows or Columns.

6. Choose to include the appropriate label by specifying

Series Names in First Row or Categories (X Labels) in
First Column.

7. Click on OK to finish.

Figure 0.13 - Paste Special Dialogue Box

To Delete a Data Series from a Chart

1. Select the chart.

2. Select the data series in the chart that you wish to delete (either by clicking on it, or using the Chart
Objects box from the Chart Toolbar).

3. Select the Edit menu, Clear and Series.

Or press the [Delete] key.

4. The data series is removed from the chart.

Plotting Non-adjacent Cells

Sometimes you may wish to plot data from different parts of a worksheet. Using the Control [Ctrl] key it is
possible to select cell ranges that are not adjacent and to base charts on these ranges e.g. to plot the
hospitality data in our example worksheet whilst using the Course Labels.

1. Select the first range in the worksheet.

2. Hold down the [Ctrl] key and select the second range.

3. Click on the Data Range tab on Step 2 of the Chart Wizard button to check that the range is correct and
proceed as before, completing the remaining steps of the ChartWizard.

39
SPREADSHEET NOTE(EXCEL) University of kisubi

Cells selected
using [CTRL] key

Data range
corresponding to
selected cells

Figure 0.14 - Plotting Non-Adjacent Cells

Printing a Chart

The procedure depends on whether the chart is Embedded within a worksheet, or is held as a separate Chart
Sheet.

Chart Sheet

1. If a chart is held in a separate sheet, make sure that the sheet containing the chart is selected, and from
the File menu choose Print.

Embedded Chart

1. For an embedded chart, to obtain a print-out of the entire work sheet including the chart, make sure
that the chart is not selected, and from the File menu choose Print.

2. To obtain a print-out of an embedded chart on its own, select the chart, and from the File menu
choose Print. The Print Dialogue appears (Figure 0.15).

3. In the Print Dialogue that the Selected Chart option should be chosen.

40
SPREADSHEET NOTE(EXCEL) University of kisubi

Selected
sheet option

Figure 0.15 - Print Dialogue

Print Quality & Size

1. From the File menu choose Page Setup and the Chart tab. (If the Chart tab is not visible make sure
the chart is properly selected). The Page Setup dialogue box will appear as shown in Figure 0.16.
The size and quality of the printed charts can be specified from here.

2. Select the required Chart Size and Print Quality options and OK.

Select required Chart Size.

Choose Custom to print the
chart as it is displayed in the

Select to print in black and

white. When cleared,
colours print as greyscale on
a black-and-white printer.

Figure 0.16 - Page Setup for Printing Charts

Use these options to specify how a chart will be scaled when printed.

Use Full Page Expands the chart to fit the full page. The proportions of the chart are
changed to fill the page.
Scale To Fit Page Expands the chart to fit the full page. The proportions of the chart are
preserved.

41
SPREADSHEET NOTE(EXCEL) University of kisubi

Custom Scales the chart sheet as it appears on your screen so you can adjust
the chart to any size on the page.

TYPES OF CHARTS:
(1) Bar charts
(a) Standard bar charts; emphasize individual values. The X-axis can show time progression from left to
right. MS-Excel creates a clustered bar chart if there are more than one data range, but each in a different
colour. Clustered bars let you compare several points in time.

(b) Started bar chart; These compare totals in addition to individual values of each period. The bars in
stacked bar charts represent totals and the sequence in each bar represents the parts that comprise the totals.

(c) Comparison bar charts; These emphasise the difference between corresponding sequences in each bar.

(2) Line Charts

These show the changes in a set of data over-time, they show time progressing from left to right on the X-
axis. One can analyse actual values as well as compare the slope of lives to measure the rate of change.

(3) Area Charts

These show trends in data overtime by emphasizing the area under the curve created by each data curves.
They also downplay individual values like line charts.

(4) Mixed Charts

These can combine parts from a line chart, bar chart or an area chart. This lets you plot data in two forms on
the same chart.

(5) XY Charts
These have their points plotted against a numeric X-axis, and these can be used to determine whether a
correlation exists or not.

(6) Pie Charts

These compare parts of a whole. The part of a slice corresponds to the percentage of the range it represents.
Slices can further be exploded such that they are apart from the rest.

(7) High-Low-Close-Open(HLCO) or Stock

These can be used to track data fluctuations overtime such as stocks, commodities, currency rates, e.t.c.

(8) Radar Charts

These are lines or area charts wrapped around a central point, each axis represents a set of data-points.
Radar Charts are useful in showing symmetry or uniformity of data since they plot data as a function of
distance from a central point.

n. Creating a Chart
To create a chart, follow the following procedure
1. Select the range or collection of data to chart
2. Click on the Insert menu
3. Choose Chart… At this stage a chart Wizard starts, displaying the selected range
4. Select a Chart Type and Chart Sub-Type

42
SPREADSHEET NOTE(EXCEL) University of kisubi

5. Click Next and specify whether the data series are in columns or rows.
6. Click Next to add Chart Title, Chart Axis, Gridlines, Legends, Data table,
7. Click Next and click on As New Sheet (to put the chart on a different sheet.)
8. Click Finish. The chart will be displayed on a new chart sheet.

PRINTING IN MS-EXCEL
In MS-Excel one can print ranges, charts, and drawn objects and query tables. To enhance your printed
pages, you can add headers, footers, print titles and page breaks, shrink and enlarge printed data, change the
page orientation, save named page settings for printing. You can also preview printing and print directly
from the Print Preview window.

o. To print a selection or range or chart

1. Select the range or collection or chart
2. Click on File
3. Select Print
4. Choose Selected Range or All
5. Choose OK.

WORKING WITH MS-EXCEL DATABASES

One of the key attractions of computers lie in its ability to store information and allow us to retrieve it
easily. Most managers today create databases using application packages like Excel and Access. A database
is simply a common collection or a pool of data or information pertaining to a particular entity. These
databases can be queried to obtain information required by a manager to make decisions or prepare reports.

Businesses or organisations use databases to store and retrieve records of all types. In Excel, such records
are stored in the worksheet with rows representing records and columns representing fields.

To Create A spreadsheet Database;

(vii) Enter the field names in adjacent cells in an empty row.
(viii) Enter the input or list records immediately after the field name row leaving no blank
rows in between

FILTERING DATA FROM A DATABASE USING ADVANCED FILTER.

An Excel Database contains three important sections namely;
The List/Input range:
This is a series of worksheet rows that contain related data, such as an employee database, a set of students
records, e.t.c. A list contains the initial data/information that is input into the database from which we filter
out a set of records satisfying a set a set criteria.
The Criteria range:
This is a range of cells that contains a set of search conditions to filter out data from a list of records that
satisfy this conditions. The criteria consists of a row of criteria labels or column titles and atleast one or
more rows that define the search condition.
The Copy to/Output range:
If information is to be filtered out to another range, the “Copy to” range will specify the range that will
contain the filtered information. The range specified in this section should be able to accommodate all the
data in the list range.

43
SPREADSHEET NOTE(EXCEL) University of kisubi

Below is a structure of a database containing ten records, with a criteria condition occupying only one row.

Row Record Description

No. No.
A B
1 Titles
2 1
3 2
4 3
5 4
6 5 List/Input range containing the titles and 10 records
7 6
8 7
9 8
10 9
11 10
12 Skip one row
13 Titles
14 Condition Criteria range containing titles and conditions
15 Skip one row
16
17
18
19
20 Copy to/Output range which is number of rows occupied by the
21 list range plus the first output row number minus one.
22 i.e 11 + 16 -1 =26
23 i.e copy to range is from row 16 to row 26.
24
25
26

SORTING RECORDS
1. Select the records to sort
2. Click on the Data menu
3. Select Sort (A sort dialog box comes up)
4. Select a field name in the sort by drop down list box
5. Specify a sort order either Ascending or Descending by clicking on either.
6. Select OK.

If they are more sort criteria’s repeat step 4 and 5 in the then by list boxes.
Select OK to finish.

FILTERING DATA
Before filtering data you need to create a query table first from the database table created or from an
external database.

44
SPREADSHEET NOTE(EXCEL) University of kisubi

To Filter Data using auto filter

1. Select the range of database to filter
2. Click on Data
3. Select Filter
4. Select Auto-Filter (Drop down list boxes appear on all selected field names)
5. From these select the criteria under each field to filter (Only the data that meets the specified
criteria is displayed)

To remove the drop down list boxes, Select Data, Filter, then Auto-Filter.
To Sort Records
7. Select the records to sort
8. Click on the Data menu
9. Select Sort (A sort dialog box comes up)
10. Select a field name in the sort by drop down list box
11. Specify a sort order either Ascending or Descending by clicking on either.
12. Select OK.

If they are more sort criteria’s repeat step 4 and 5 in the then by list boxes.
Select OK to finish.

Like a lot of business software users, I’m a generalist. On any given day, my job may
require a little number crunching and word processing, and maybe some page layout,
slide show creation, or report building. For the most part, the various Office apps
make it easy to meet these needs. But certain tasks just don’t come up often enough
for me to keep them in my head. (Yes, I’m blaming the tasks here.)

Excel is a good example. I use it every day, but I seldom need to do more than enter
data or a simple formula. So when a job requires something a little more
sophisticated, I waste a certain amount of time trying to remember how I got it to
work before. Then I waste even more time trying to extract a useful answer from
online help.

Frustrating, unproductive, and a little embarrassing.

So I made a list of a few Excel techniques I occasionally need but inevitably forget.
This is bunny stuff, and not for you power users out there. But if you’re a casual Excel
user (or you’re on the help desk but don’t spend much time in Excel yourself), maybe
this list will help you cut to the chase.

Note: This cheat sheet is also available as a PDF download.

1: Toggle the display of formulas

When you need to see what’s going on under the hood of a worksheet, you may want
to turn on Excel’s formula display. There’s a convoluted way to do this via Excel
options (and Excel 2007 offers the Show Formulas button in the Formula Auditing
group of the Formulas tab - if you want to remember that). But you can toggle the
display on the fly just by pressing [Ctrl] ~. If you select a cell whose formula you want

45
SPREADSHEET NOTE(EXCEL) University of kisubi

to troubleshoot before turning on the display, Excel will also show you the dependent
cells for the formula.

2: Convert a formula to its results

Sometimes, you may need to replace a formula with its results - either to preserve a
static value or to optimize your sheet by reducing calculations. There’s a pretty
simple trick for this, but a word of warning: Be sure you really want to wipe out a
formula before you do it. (There could be undesirable consequences.) In fact, a good
practice is to create a backup copy of the workbook as a safety net in case things go
awry.

To convert a formula, click in its cell and press [F2] to enable in-cell editing. Next,
press [F9] to calculate the formula and display its results. Then, hit [Enter], and your
formula will be replaced by the value it produced.

You can also copy the formula and use Paste Special | Values to paste the results
someplace else, leaving the formula intact in its original location.

3: Create a copy of an existing worksheet

Excel offers an efficient way to copy a worksheet, either within the current book or
into a different one - handy when you need to start a new sheet that includes some or
all of the data and/or formatting of an existing sheet. It works like this:

1. Right-click on the sheet tab of the sheet you want to copy.

2. Choose Move Or Copy.
3. Select the Create A Copy check box in the bottom-left corner of the Move Or Copy dialog box.
4. Choose a different workbook, if desired, from the To Book drop-down list. (That other workbook
must be open to show up in the list.) You can also select New Workbook.
5. In the Before Sheet list box, specify where you want the copied sheet to go within the specified
workbook.
6. Click OK.

4: Start a new line within a cell

This may seem beyond simplistic - until the day you can’t remember how to do it. If
you need to create a multiple-line entry in a cell, you can’t just press [Enter] to insert
a line break, since that will propel you into the next cell. Instead, you have to press
[Alt][Enter].

5: Unhide hidden rows or columns

From time to time, someone will send me a worksheet with hidden rows or columns. I
usually don’t need to see the data, so of course I forget how to unhide it on the rare
occasions when I do need to see it. It’s easy, though: Highlight the row above and the
row below the hidden row(s) - or the column to the left and to the right of the hidden
column(s). Then, you can reveal the data in various ways:

46
SPREADSHEET NOTE(EXCEL) University of kisubi

 Press [Shift][Ctrl]0 (that’s a zero).

 Right-click the selection and choose Unhide.
 Choose Column (or Row) from the Format menu and then select Unhide. In Excel 2007, go to the
Cells group on the Home tab, click Format, choose Hide & Unhide, and select Unhide Rows or
Unhide Columns.

6: Enter a fraction in a cell

Say you type 1/4 in a cell, wanting to enter the fraction one-fourth. Ordinarily, Excel
will turn the value into a date - 4-Jan. To prevent that, just preface your entry with a
zero and a space: 0 1/4. Excel will leave your fraction alone. Without the zero, you’ll
see 1/4/2009 (or whatever year you happen to be in) in the Formula bar. With the
zero, you’ll see 0.25.

7: Simultaneously copy data into noncontiguous cells

To copy data from one cell into adjacent cells, you just drag the cell’s fill handle
across the cells where you want the copied data to appear. But sometimes, you’ll
need to copy data into cells that are scattered around the worksheet. The most
efficient way to handle that task is to copy the desired data, hold down [Ctrl], and
select all the other cells where you want to paste the data. Then, press [Ctrl]V and
Excel will insert the copied data into each of the selected cells.

8: Simultaneously enter data into noncontiguous cells

Similar to the previous trick, you can save time when you need to enter the same
data into cells that aren’t next to each other. Start by holding down the Ctrl key and
selecting all the cells into which you want to enter data. Then, type your data and
press [Ctrl][Enter]. Excel will insert the data into all of the cells in the noncontiguous
selection.

9: Enter text in the same location in multiple worksheets

This may not come up all that often, but it’s a cool trick when you need it. Let’s say
that you’re entering month names as column headers at the top of a sheet - and you
want them to appear on your other sheets as well. Click in the cell where you’ll be
entering January. Then hold down [Ctrl] and click on the sheet tabs of the other
sheets where you want the month names to appear. This will group the sheets so that
what you do now affects all of them.

Go ahead and type January. Then (another cool trick coming…), drag the cell’s fill
handle to the right across the next 11 cells. Excel recognizes that January is the first
item in a built-in series, so it will insert the rest of the month names for you.

To complete the process, right-click on one of the selected sheet tabs and choose
Ungroup Sheets from the shortcut menu. If you check those sheets, you’ll see your
month names have been entered in all of them.

47
SPREADSHEET NOTE(EXCEL) University of kisubi

10: Transpose data from a row to a column, or vice-versa

Once in a while, I’ll set up a worksheet using one structure that seems to make sense,
only to realize it would make a whole lot more sense if the rows were columns and the
columns were rows. And apparently I’m not alone in this befuddlement, because Excel
provides a Transpose option to facilitate the necessary flip-flopping of data:

1. Select the range of cells you want to transpose and click Copy or press [Ctrl]C.
2. Click in a new location (not overlapping your selection).
3. Go to Edit | Paste Special and select the Transpose check box. In Excel 2007, click Paste in the
Clipboard group of the Home tab and select Transpose.
4. You can then delete your original, wrong-structured data.

48