SPREADSHEETS.

Spreadsheet programs are used to organize, calculate, analyze, manipulate and

store sets of numbers or data in an electronic file.

Uses of spreadsheets.
Spreadsheets are typically used by accountants and others to organize and analyze
data for professional or personal business activities, such as:
 Financial projections
 Income tax estimates
 Budget to actual comparisons
 Cash flow analysis
 Bid preparations
 Depreciation schedules
 Stock portfolio analysis
 Inventory analysis
 Sports statistics
 Expense account analysis

Example of spreadsheets
LOTUS 1, 2, 3
VISICALC
MS EXCEL

Ms Excel
Excel 2007 software (Excel) is used to organize, analyze, and store data in an
electronic file

To start Excel using the Windows Start menu and view a new workbook:
 Click the Start button on the taskbar.
 Point to All Programs.
 Click the Microsoft Office folder.
 Click Microsoft Excel.

1
Identifying Excel Window Elements and Describing a Worksheet

The Excel window on your screen includes the following.

 Office Button – opens Backstage view with commands to open, close, save,
create, print, and share workbooks
 Quick Access Toolbar – a customizable toolbar
 title bar – contains the name of the workbook, the name of the software, and
the Minimize, Restore Down or Maximize, and Close buttons
 Minimize button – minimizes the window to a button on the Windows
Taskbar
 Restore Down or Maximize button – restores a window to a smaller size
on the screen or maximizes the window to fill the screen
 Close button – closes Excel
 Ribbon – contains tabbed groups of commands organized around
completing a specific type of task
 Ribbon tabs – the default Home, Insert, Page Layout, Formulas,
 Data, Review, and View tabs used to group command buttons around
common tasks; other Ribbon tabs might appear.
 contextual tabs, which appear as needed as you work in a worksheet

2
  Group – a group of related command buttons on a Ribbon tab
 Minimize the Ribbon button -- minimizes the Ribbon to just Ribbon tabs;
when the Ribbon is minimized, the Expand the Ribbon button appears
 Microsoft Excel Help button – used to access Excel Help
 Minimize Window button – minimizes the current workbook to a title bar
icon inside the Excel window
 Restore Window button – restores the active workbook to a smaller
window inside the Excel window
 Close Window button – closes the active workbook
 Status bar – a customizable bar below the worksheet area that is used to
display various messages, the View Shortcuts buttons, and the zooming
tools
 Scroll bars – vertical and horizontal scroll bars used to change the vertical
or horizontal view of worksheet areas
 Status bar – a customizable bar below the worksheet area that is used to
display various messages, the View Shortcuts buttons, and the zooming
tools

Elements that are specific to the Excel software window are worksheets, sheet tabs,
Tab scroll buttons, the Name Box, and the Formula Bar.

Workbook
Spreadsheets comprises of a grid of numbered rows and lettered columns
In spreadsheets you work in a document called worksheets. You can collect
related worksheets in a workbook. Worksheets can be renamed and workbook
containing many worksheets. The file you save in spreadsheet program is the
workbook.

Worksheets
A worksheet is a work area or grid consisting of vertical columns and horizontal
rows—much like a sheet of accounting paper.
A single Excel worksheet has 1,048,576 horizontal rows (numbered 1 through
1048576) and 16,384 vertical columns from column A to column XFD.

Cell
The intersection of each column and row is called a cell. You enter text, numbers,
and formulas (calculations) in cells.

3
If a workbook contains many worksheets, all the sheet tabs may not be visible.
You can click the worksheet Tab scrolling buttons to activate the first sheet tab,
the previous sheet tab, the next sheet tab, and the last sheet tab, as necessary.

Cell pointer
The active cell is emphasized with a thick, dark border.
Cell pointer is the thick rectangular frame that surrounds the active cell.

Column heading and row heading number

Column heading is the system of identifying columns labeled A, B, C…………
Row heading is the system of identifying columns rows labeled 1, 2, 3 ………

Cell references.
Cells are referenced using their column heading and row heading number. For
example the cell at the intersection of column A and row 1 is referenced as A1; the
cell at the intersection of column F and row 15 is referenced as F15. You will use
cell references to navigate in a worksheet, select cells in a worksheet, and to create
formulas.

Name Box and Formula Bar

The Name Box shows the cell reference of the active cell in the worksheet
Formula Bar shows the contents of the active cell. Formula bar also displays the
contents of the formulas entered in a cell.

Column heading and row heading number

Cells are referenced using their column heading and row heading number. For
example the cell at the intersection of column A and row 1 is referenced as A1; the
cell at the intersection of column F and row 15 is referenced as F15. You will use
cell references to navigate in a worksheet, select cells in a worksheet, and to create
formulas.

Range
Often, you will need to select multiple adjacent cells. A group of adjacent cells is
called a range. When working with a range of cells, you reference the range by

4
noting the individual cell references for the cells in the upper-left and lower-right
corners separated by a colon.

Building a Worksheet
Saving a Workbook
The default Book1 workbook that automatically opens when you start Excel the
save the worksheet.

 One way to save a new workbook for the first time is to

 click the Microsoft Office Button to view the menu,
 point to the Save As command, and then click the desired file type in the
submenu to launch the Save As dialog box with this file type selected
 Switch to the folder in which you want to save the workbook, give the
workbook a unique name.
 click the dialog box Save button

Inserting, Deleting and Renaming Worksheets

A new workbook contains three worksheets; but you can add or delete worksheets
as necessary. You can use the Insert Worksheet tab, a command button on the
Ribbon, or a shortcut menu to insert and delete worksheets.

Using the Ribbon

To insert a worksheet in front of an existing worksheet, first activate the existing
worksheet by clicking its sheet tab. Then display the Home tab on the Ribbon and
locate the Cells tab group.

To insert a new worksheet in front of the Sheet1 using the Ribbon:

 Activate the Sheet1 sheet tab
 Click the Home tab on the Ribbon, if necessary
 Locate the Cells group on the right side of the Home tab
 Click the Insert command button arrow in the Cells group to display a menu
 Click Insert Sheet

To delete a worksheet using the Ribbon:

 Activate the Sheet5 sheet tab, if necessary
 Click the Home tab on the Ribbon, if necessary
 Click the Delete command button arrow in the Cells group to display a menu

5
Using a Shortcut Menu
A shortcut menu is a brief list of frequently used commands available when you
right-click a screen element.

To insert a worksheet using a shortcut menu:

 Right-click the Sheet2 sheet tab to activate the sheet tab and view the
shortcut menu
 Click Insert on the shortcut menu to launch the Insert dialog box
 Click the General tab in the dialog box, if necessary
 Double-click the Worksheet icon to insert.

To delete the worksheets using a shortcut menu:

 Right-click the Sheet6 sheet tab to activate the sheet tab and view the
shortcut menu
 Click Delete on the shortcut menu

Insert/ delete multiple worksheets

To add or delete multiple worksheets at one time, first select multiple sheet tabs
using the SHIFT + click method or CTRL + click methods. Then right-click a
selected sheet tab and click Insert or Delete on the shortcut menu.

Renaming a Worksheet
You can rename the active worksheet by clicking Rename on the sheet tab shortcut
menu or by double-clicking a sheet tab. Then simply key a new name in 31
characters or less and press the ENTER key or click the sheet tab to accept the new
sheet tab name.

Entering, Editing and Formatting Cell Contents

Entering text or numbers in a cell is a three step process. You must:

6
  Activate the cell
 Key the text or numbers in the cell or in the Formula Bar
 Accept the cell contents by activating another cell or by clicking the Enter
button on the Formula Bar leaving the current cell the active cell

Adjusting Row Height and Column Width/ Resizing row and column sizes
When you start working on a worksheet, all columns are 8.43 characters wide (in
default font) and row heights are set to fit the content of the cell with a maximum
of 15 points.
Excel may widen the column or increase the row height to fit the cell content.
Adjusting the width or height is easy to do and can be done using the Ribbon
option or the Mouse option.

Adjusting Row Height: Ribbon Option

 To adjust a single row, select any cell from the row to be adjusted
To adjust multiple non-contiguous rows, press [Ctrl] + select cells from
each row to be adjusted
 From the Ribbon, select the Home command tab
 In the Cells group, click FORMAT
 In the Cell Size section, select Height...
The Row Height dialog box appears.
 In the Row height text box, type the desired height
 Click OK. The row height is adjusted.

Adjusting Row Height: Mouse Option

If you choose to adjust multiple rows at once, all selected rows will be adjusted the
same amount no matter which row border you move.
 To adjust multiple non-contiguous rows, press and hold [Ctrl] while
clicking the ROW ID for each desired row
To adjust multiple contiguous rows, press and hold [Shift] while clicking the
first and last ROW ID for the desired range
 Along the row ID (e.g., 1, 2, 3, ...), point to the border below the row to be
adjusted
 When the pointer turns into a double-arrow, click and drag
HINT: For a shorter row, drag up; for a taller row, drag down.
A box appears next to the pointer, indicating the current row height as you
drag it.

7
  When the row reaches the desired height, release the mouse button. The row
height is adjusted.
Adjusting Column Width: Ribbon Option

1. To adjust a single column, select any cell from the column to be adjusted
To adjust multiple non-contiguous columns, press [Ctrl] + select cells from
each column to be adjusted
2. From the Ribbon, select the Home command tab
3. In the Cells group, click FORMAT
4. In the Cell Size section, select Width...
The Column Width dialog box appears.
5. In the Column width text box, type the desired width
6. Click OK. The column width is adjusted.

Adjusting Column Width: Mouse Option

If you choose to adjust multiple columns at once, all selected columns will be
adjusted the same amount no matter which column border you move.

1. To adjust multiple non-contiguous columns, press and hold [Ctrl] while

clicking the COLUMN ID for each desired row
To adjust multiple contiguous columns, press and hold [Shift] while clicking
the first and last COLUMN ID for the desired range
2. Along the column ID (e.g., A, B, C, ...), point to the border right of the
column to be adjusted
3. When the pointer turns into a double-arrow, click and drag
HINT: For a narrower column, drag left; for a wider column, drag right.
A box appears next to the pointer, indicating the current column width as
you drag it.
4. When the column reaches the desired width, release the mouse button. The
column width is adjusted.

Using AutoFit to Adjust Rows and Columns

To adjust the column width or the row height, Excel can determine the best width
and height based on the information in the column or row.

8
Using AutoFit to Adjust Row Height: Mouse Option

1. Along the row ID (e.g., 1, 2, 3, ...), point to the border below the row to be
adjusted
2. When the pointer turns into a double-arrow, double click
The row height adjusts so the tallest item in the row is displayed in full.

Using AutoFit to Adjust Row Height: Ribbon Option

1. To select a row to be adjusted, click the ROW ID (e.g., 1, 2, 3, ...)

The entire row is selected.
2. From the Ribbon, select the Home command tab
3. In the Cells group, click FORMAT
4. In the Cell Size section, select AutoFit

Using AutoFit to Adjust Column Width: Mouse Option

1. Along the column ID (e.g., A, B, C, ...), point to the border right of the
column to be adjusted
2. When the pointer turns into to a double-arrow, double click the column
width adjusts so the widest item in the column is displayed in full.

Using AutoFit to Adjust Column Width: Ribbon Option

1. To select a column to be adjusted, click the COLUMN ID (A, B, C, ...)

The entire column is selected.
2. From the Ribbon, select the Home command tab
3. In the Cells group, click FORMAT
4. In the Cell Size section, select AutoFit Selection

Inserting and Deleting Rows or Columns

By default, Excel inserts new rows above the selected rows and new columns to
the left of the selected columns

You can also select nonadjacent rows or columns and then insert additional rows
above each selected row(s) or additional columns to the left of each selected
column(s).

9
After you select the number of rows or columns to insert, you can click the Insert
command on a shortcut menu.

You can insert and delete rows and columns by clicking the Insert and Delete
command buttons in the Cells group and then clicking the appropriate command
on the menu.

Format of the cells in your worksheet

Learn how to use the following dialogue box to format your cell contents

Editing Cell Contents

Rekey the text or numbers. You can also edit the contents of a cell directly on the
Formula Bar or in the cell itself.

To edit cell contents directly in the Formula Bar, first activate the cell. Then move
the mouse pointer to the Formula

Opening an Existing Workbook

 Click the Open button on the Quick Access Toolbar
 Switch to the folder that contains the workbook files
 Double-click the workbook file name.

Navigating in a Worksheet

10
Worksheet cell A1 is often called the home cell because it is the first cell in the
upper-left area of the worksheet. You can use the mouse pointer to navigate from
one cell to another. When you move the mouse pointer over a cell, the mouse
pointer becomes a large white plus pointer.

You can also navigate from cell to cell by pressing the ARROW keys or by
pressing a combination of keys, called keyboard shortcuts,

Navigating in a Worksheet Using the Name box

However, because you know where the variables are located, you can quickly
navigate to the area by using the Name box to the left of the formula bar.

The Name box generally contains the cell reference (row number and column
letter) of the active cell.
 Move the mouse pointer into the Name box; the mouse pointer becomes the
I-beam pointer
 Click the Name box to select its contents
 Key L1:P4
 Press the ENTER key

11
  Observe that the worksheet has scrolled to the right and you can now see the
selected range, L1:P4.

Navigating in a Worksheet Using a Defined Name

Defining a name for a worksheet ranges is useful when the worksheet contains
multiple ranges in which you frequently work.

Then you can simply key the defined name in the Name box and press the
ENTER key to select the range.

Creating a Defined Name

You can define a unique name for a selected cell or range of cells in the New
Name dialog box by clicking the Define Name command button in the Defined
Names group on the Formulas tab.

In the New Name dialog box you key the name, select the scope of the name
(entire workbook or specific worksheet), and specify the range to which the name
applies.

You can also select a range of cells and then key the defined name in the Name
box.
To select the range L18:P21 and define a name for the range:
 Key L18:P21 in the Name box
 Press the ENTER key to select the range
 Key var in the Name box
 Press the ENTER key

Selecting Worksheet Cells, Rows, and Columns

To select a range of cells using the mouse pointer, you can drag the mouse pointer
across the range.
To select the range B7:E7:
 Point to cell B7
 Press and hold the left mouse button
 Drag the mouse pointer to the right to cell E7
 Release the left mouse button

12
Select range (Adjacent)
Press and hold down the SHIFT key and click the cell in the lower-right corner of
the range. This is sometimes called the SHIFT + click selection method.

To use the SHIFT + click method to select a larger range, A1:F15:

 Activate cell A1, if necessary
 Press and hold the SHIFT key
 Click cell F15
 Release the SHIFT key

Select range (Non Adjacent)

You can use the CTRL key together with the mouse pointer to select such ranges.
This method is sometimes called the CTRL + click selection method.

To use the CTRL + click method to select the nonadjacent ranges A5:A15,
C5:C15, and F5:F15:
Drag to select the range A5:A15
 Press and hold the CTRL key
 Drag to select the range C5:C15
 Drag to select the range F5:F15
 Release the CTRL key
Selecting entire row and columns

You can also use the mouse pointer to select an entire row or column by clicking
the column heading or row heading.

When you place the mouse pointer on a column heading it becomes a small, black
downward-pointing selection arrow. When you place the mouse pointer on a row
heading it becomes a small, black right-pointing selection arrow.

To select multiple columns or multiple rows, you can drag across the column or
row headings. You can also use the SHIFT + click or the CTRL + click methods to
select multiple adjacent or nonadjacent columns or rows.

Creating, Copying, and Pasting Basic Formulas

Data directly entered in a cell.
 Constant e.g. dates, numeric values, currency etc.
 Formulae

13
  Functions
Arguments- can be numbers, text, logical values, error values.

Functions and Formulas.

Formulas and functions

A formula is a mathematical expression. You create formulas in a worksheet to

calculate values; for example, the total revenues or total expenses in a budget
worksheet.

The power of an Excel worksheet lies in its ability to recalculate its formulas when
the values for the variables used by the formulas are changed. To exploit this
automatic recalculation feature, you should use cell references instead of actual
values in a formula whenever possible.

Formula Syntax

Excel formulas begin with an equal sign (=); this tells Excel that what follows the
equal sign is a calculation.

After the equal sign are the operands (numbers, text, or cell references) and
mathematical operators (+, -, *, /) necessary for the calculation. Although most
formulas contain both operands and mathematical operators, a very simple
formula, such as that found in cell B7, can contain just an operand.

The result of the formula’s calculation is visible in the cell and the formula itself is
visible in the formula bar above the worksheet area

You could calculate the total by entering the formula =B7+C7+D7+E7+F7 in a

cell

Function Syntax

A function is a predefined Excel formula you can use to perform common

calculations such as calculating the sum, average, minimum, or maximum value in
a range of cells. You must follow a strict syntax when using a function.

 If the function is used alone or if it is the first calculation in a complex

formula, it must be preceded by an equal sign (=) just as any other formula.
 A function name is required.
14
  An open parenthesis must follow the function name.
 Text, numbers, or cell references are entered as the function’s argument—
what the function acts upon; multiple arguments are separated by a comma.
 A closing parenthesis must follow the function’s arguments.
 Some functions do not have arguments; however, the opening and closing
parentheses must be included.

CATEGORY OF FUNCTIONS IN EXCEL.

Using the Insert Function Icon and the Function Library

Excel’s functions and how to use some of the most commonly used functions, such
as SUM, to create basic formulas. Excel has hundreds of functions you can use to
perform a wide variety of calculations.

15
Copying formulas

A faster way to copy and paste formulas to adjacent cells, however, is to use the
fill handle.

The dark border around the active cell or selected range has a fill handle, a small
square in the border’s lower-right corner. When the mouse pointer is positioned on
the fill handle, it becomes a black cross-hair fill pointer.

You can use the fill handle to copy or fill a range of adjacent cells with text or
numbers or formulas by dragging the fill handle with the fill pointer.

When you fill a cell or range using the fill handle the results depend on the content
of the active cell or cells.
If the active cell contains text or a number, the text or number is copied into the
adjacent cells. If the active cell contains a combination of text and numbers the
text is copied but the number increments by 1.

Relative cell addresses and absolute cell addresses.

Relative cell addresses.

When you copy a formula from one cell to another, spreadsheets program
automatically adjusts each cell reference in the formula.

e.g c1+c1=E1

c2+c2=E2

16
By default, Excel pastes copied formulas with relative references. This means that
Excel automatically adjusts the column or row references based on the location of
the pasted formula’s destination cell.

Absolute cell addresses.

You can instruct ms excel not to change the cell address by cell reference an
absolute cell address.

Inserting a dollar sign ($) in front of the row or column reference in a formula tells
Excel that the cell reference should not change when the formula is pasted into
another cell.

 You can insert a $ by keying it or by pressing the F4 function key. The F4

function key has four settings which you can toggle through by pressing it
multiple times.
 Press the F4 key once to insert a $ in front of both the row and column
reference ($A$5) to create an absolute reference. Both the column and the
row reference will be unchanged when the formula is copied.

Mixed cell references.

You can create mixed references by making a part of a cell address absolute and
part relate by ‘locking in’ either column or the row.

Pasting Formulas with Relative References

By default, Excel pastes copied formulas with relative references. This means that
Excel automatically adjusts the column or row references based on the location of
the pasted formula’s destination cell.
 Copying and Pasting Formulas
 You can use the fill handle to copy formulas by dragging the fill handle with
the fill pointer

17
The Sum (AutoSum) command button, located in the Editing group on the
Home tab, is used to quickly enter a formula containing the commonly used SUM,
AVERAGE, MAX, and MIN functions

Pasting Formulas with Mixed or Absolute References

Inserting a dollar sign ($) in front of the row or column reference in a formula tells
Excel that the cell reference should not change when the formula is pasted into
another cell.

 You can insert a $ by keying it or by pressing the F4 function key. The F4

function key has four settings which you can toggle through by pressing it
multiple times.
 Press the F4 key once to insert a $ in front of both the row and column
reference ($A$5) to create an absolute reference. Both the column and the
row reference will be unchanged when the formula is copied.
 Press the F4 key again to insert a $ in front of the row reference (A$5) to
create a mixed reference. The column reference will change but the row
reference will be unchanged when the formula is copied.
 Press the F4 key again to insert a $ in front of the column reference ($A5) to
create a mixed reference. The row reference will change but the column
reference will be unchanged when the formula is copied.
 Press the F4 key again to remove any $ (A5) to create a relative reference.
Both the column and row references will change when the formula is copied.

Pound symbols (#######)

If some of the cells are not wide enough to contain the formatted results of the
formulas’ calculations; you will see pound symbols (#######) in the cells when
this occurs.
You can fix this by widening columns B:G to fit their cells’ contents.

ROUND function

You use the ROUND function to first specify a value or formula and then to
specify how many decimal places to round the value or the formula’s result.

18
The ROUND
Function has two arguments: number and num_digits. The number argument is the
specific value or formula to be rounded. The num_digits argument is the number of
digits you want to the right of the decimal point. For example, setting the
num_digits argument to zero will round the value or formula’s calculation to whole
numbers.

e.g =ROUND(B12*(1+C21),0)

The IF function
You must create a formula that performs logical tests. The IF function is used to
perform logical tests and has three arguments separated by commas: a logical test,
the action to take if the test is true, and the action to take if the test is false

When it is necessary to perform multiple logical tests, you can use an IF function
as part of the original IF function’s argument. This process is called nesting IF
functions.

19
When developing a complex formula using one or more IF functions, it can be
helpful to write down the calculations before you begin to create the formula in the
cell.

Example

IF the value in cell B6 is less than or equal to 200,000, then return the value
located in cell I26, otherwise test again.
IF the value in cell B6 is greater than 200,000 and less than or equal to 500,000,
then return the value located in cell I27, otherwise (500,001 or more) return the
value located in cell I28.

Trace Error icon

The error value #DIV/0! appears in each cell in the range as a small, dark green
triangular-shaped error indicator symbol.

20
The Trace Error icon () should appear to the left of the active cell indicating an
error. You can point to the Trace Error icon to view a ScreenTip describing the
error or click the button to view a menu of error correction options.

Pasting Formulas’ Results as Values

A fast way to paste formulas as values is to click a Paste Values option icon in the
paste options gallery.

Applying Style Formatting

You can add dollar signs, percent signs, thousands separators (commas), and
underlining to the values. You apply these formats by choosing options on the
Number tab in the Format Cells dialog box.
You can change the currency symbol and increase or decrease the number of
decimal places by changing options in the Number tab.

You can add single or double underlines with options in the Font tab in the
Format Cells dialog box.

21
Creating Complex Formulas

Arranging Multiple Workbook Windows

You will probably need to have more than one workbook open at a time.

When you have multiple workbooks open simultaneously, you can switch between
them by clicking the user interface’s View tab and then, in the Window group,
clicking the Switch Windows button and clicking the name of the workbook
you want to view.

You can quickly arrange both open workbooks’ windows on your screen by
clicking the View tab on the Ribbon and clicking the Arrange All command
button in the Window group to launch the Arrange Windows dialog box. Then
click the arrangement style option button you want and click OK.

22
Entering Formulas on Grouped Worksheets

Grouping worksheets allows you to perform the same task on multiple

worksheets at one time.
For example, you can quickly apply formatting to multiple worksheets at one time
or enter the same formula on multiple worksheets at one time by first grouping two
or more worksheets.

You can use the SHIFT + click selection method to group adjacent worksheets.
Just press and hold down the SHIFT key and click a sheet tab to group the
currently active worksheet, the worksheet you click, and all the worksheets in
between.

To group nonadjacent worksheets, use the CTRL + click selection method. Press
and hold down the CTRL key as you click the sheet tabs. To group all the
worksheets in a workbook, right-click a sheet tab and click Select all Sheets on the
shortcut menu

23
When worksheets are grouped, the notation [Group] appears in the title bar
and all the grouped sheet tabs are white—indicating that the worksheets are
active

Creating Formulas with 3-D References

You will create formulas with 3-D references, formulas with range references that
span two or more worksheets or workbooks.

3-D formulas that span worksheets in the same workbook contain worksheet
references in addition to cell references. 3-D formulas that span workbooks
contain workbook, worksheet, and cell references.

You begin by creating 3-D formulas that link worksheets in the same workbook.

Observe the marquee around cell B13 and the formula e.g ='Call Center 1'!B13
in the formula bar

The formula ='Call Center 1'!B13 ensures that cell B7 in the Summary worksheet
will always contain the result of the formula’s calculation in cell B13 on the Call
Center 1 worksheet.(cell references and sheet references are samples)

24
Linking Workbooks with 3-D Formulas

You can also create formulas that span workbooks. For example, suppose you want
to use the total call volume data in the Summary worksheet in the
lesson_2_call_volume_forecast workbook to estimate the number of employees
each call center will need each of the five forecast years.

The employee estimates are maintained in a different workbook; you can create
formulas in that workbook that reference cells on the Summary worksheet in the
lesson_2_call_volume_forecast workbook.

You begin by opening a second workbook and saving it with a new name. Then
you tile both workbooks on your screen in order to work more easily in both of the
workbooks.

Cell B6 and the formula in the formula bar in your Employee Estimates worksheet
should look similar to.

25
The formula ='[lesson_2_call_volume_forecast.xls]Summary'!$B$7 indicates that
cell B6 in the Employee Estimates worksheet will always contain the contents of
cell B7 in the Summary worksheet in the lesson_2_call_volume_forecast
workbook. Note that the $B$7 cell reference is an absolute reference

Note that the $B$7 cell reference is an absolute reference.

Showing and Hiding Worksheet Elements

Temporarily hiding numeric data and formulas by hiding cell values, rows,
columns, or entire worksheets allows you to share a workbook with others or print
a worksheet without disclosing sensitive data.

The hidden data is still available for use in formulas and the hidden formulas
continue to work correctly. When you want to view the data or formulas again, you
can unhide the hidden cell values, rows, columns, or worksheets.

To temporarily hide the growth assumptions data in the range C19:E22:

 Select the range C19:E22 using the SHIFT + click method
 Open the Format Cells dialog box
 Click the dialog box Number tab, if necessary
 Click Custom in the Category list
 Key ;;; (three semicolons) in the Type text box

Hiding Rows or Columns

You quickly can hide or unhide rows or columns by first selecting the row or
column headings, right-clicking the selected headings, and clicking Hide or
Unhide on the shortcut menu.

Hiding Worksheets
26
Hide
Step 1 Right-click a selected sheet tab
Step 2 Click Hide

Unhide
Step 3 Click Unhide to launch the Unhide dialog box
Step4 Click the worksheeet in the Unhide sheet list box

Working in Normal View, Page Layout View and Page Break Preview
Worksheet views include the default Normal view, the view you are currently
using to enter and format text, numbers, and formulas, plus Page Layout view and
Page Break Preview.

Page Layout View

Page Layout view shows you how the worksheet data looks on a page and
provides tools you can use to enhance the worksheet before you print it.

Page Break Preview

When working with large worksheets, Excel inserts automatic page breaks as
necessary based on the paper size, margin settings and so forth allowing worksheet
contents to flow to additional pages.
You can control this flow by inserting horizontal or vertical manual page breaks
to force worksheet contents to the desired page(s). To insert a manual page break,
you must first select a row, column, or cell below and to the right of where you
want the manual page break inserted. The best worksheet view in which to work
with page breaks is Page Break Preview

Page Break Preview allows you to see existing automatic page breaks and insert
or reposition manual page breaks. You can also enter and format text and numbers
and insert formulas in Page Break Preview. You can switch to Page Break Preview
by clicking the Page Break Preview view button in the View Shortcuts on the
status bar.

To do this, you can switch to Page Break Preview and then insert a manual page
break at row 16 using a shortcut menu.

To switch to Page Break Preview and insert a manual page break at row 16:

27
  Click the Page Break Preview button in the View Shortcuts on the status
bar
 Click OK to close the Welcome to Page Break Preview dialog box, if
necessary
 Observe that only the active area of the worksheet appears in Page Break
Preview; a dark blue border appears around the area to be printed
 Observe that there are no automatic page breaks (dashed lines) as the active
area of the worksheet can print on Page 1
 Zoom the view to 100%, if necessary, using the Zoom Slider

 Right-click the row 16 row heading to view the shortcut menu

 Click Insert Page Break
 Activate cell A1
 Observe the dark blue manual page break inserted above row 16; the
worksheet is now separated into two pages

A quick way to modify print boundaries or page breaks is to drag a boundary or

manual page break to a new location. When you place the mouse pointer on a
print boundary or manual page break, the mouse pointer becomes a two-headed
black move pointer.

Page Setup Options, Preview, and Print a Worksheet

Previewing a worksheet allows you to better see how the worksheet data will
appear on the printed page(s).

To change page orientation and margins:

 Click the Portrait Orientation button in the Print options

 Click the Landscape Orientation
 Click the Normal Margins button in the Print options
 Click Custom Margins to open the Margins tab in the Page Setup dialog
box
 Click the Horizontally and Vertically checkboxes to insert check marks
 Click OK

28
  Observe the worksheet preview; the worksheet is presented in landscape
orientation and the worksheet appears centered vertically and horizontally on
the page

 Click the Next Page icon to view page 2; the Assumptions data on page 2
appears centered vertically and horizontally on the page
 Click the Previous Page icon to view page 1

Now you are ready to launch the Print dialog box, review print options, and print
the worksheet.
Printing a Worksheet

The Quick Print command button allows you to print the worksheet using the
existing print options.
Creating Charts

An Excel chart is a picture of worksheet data, called a chart object, stored in an

Excel workbook. Excel charts are useful because charts make it easier to see trends
or patterns in data and to compare data.
You can insert an embedded chart object on the same worksheet that contains the
charted data or you can insert a chart object on a separate sheet in the workbook,
called a chart sheet.

Excel provides a variety of 2-dimensional and 3-dimensional chart types including

column, pie, bar, area, and line charts. In this lesson, you will create both a 2-D pie
chart and a 2-D column chart. A typical 2-D column chart contains several chart
elements.

Identifying Chart Elements

29
Creating an Embedded Pie Chart

A pie chart, a chart that compares data points in a single data series

Sometimes you may need to add a small chart to a worksheet in order to present a
visual representation of the worksheet’s data. You can do this by embedding the
chart as an object on the worksheet.

You begin by selecting the cells that contain the data to plot in your char.

 Select the ranges

 Click the Insert tab on the Ribbon
 Locate the Charts group
 Click the Pie button to view a gallery of pie chart subtypes
 Point to the first 2-D Pie chart subtype icon to view the ScreenTip
description for the icon.
 Click the first 2-D Pie chart subtype icon to create the embedded pie chart
object
 Observe the embedded pie chart object on the worksheet
 Observe that the Ribbon now displays the contextual Chart Tools Design
tab
 Observe the colored borders around the data series ranges in the worksheet
 Click in the worksheet outside the chart object to deselect it

To make the chart object slightly smaller by resizing it proportionally:

30
  Move the mouse pointer to the sizing handle in the upper-right corner of the
selected chart
 Observe that the mouse pointer becomes a sizing pointer
 Drag the sizing handle downward and to the left until the chart object is
approximately 1/2 inch smaller on each boundary
 Drag the chart to reposition it attractively below the data
 Click in the worksheet to deselect the chart object

Creating a Column Chart on Its Own Chart Sheet Using a Keyboard Shortcut

To create a column chart you must first select the cells containing the data to be
charted plus the cells containing the data that identifies the category names for the
horizontal (category) axis and the individual data series.

Excel looks at column or row labels on the longest edge of the selection rectangle
to determine the category names for the horizontal (category) axis and the column
or row labels on the shortest edge of the selection rectangle to determine the data
series

You can quickly create a default 2-D Clustered Column chart on its own chart
sheet by pressing the F11 shortcut key.

To create the default 2-D Clustered Column chart on its own chart sheet:

 Press the F11 shortcut key

 Observe that a chart sheet named Chart1 is inserted in the workbook
 Double-click the Chart1 sheet tab
 Rename the chart sheet

Changing the Chart Type and Subtype

 You may decide that the ‘story’ you want the chart to tell or the ‘picture’ of
the data that you want the chart to present can be improved by changing the
chart type and/or subtype. You can quickly change a chart’s type or subtype
using buttons on the Ribbon or a shortcut menu.

 A chart’s type and subtype can be changed by selecting a different type and
subtype in the Change Chart Type dialog box.

31
 You can launch the Change Chart Type dialog box by clicking the Change
Chart Type button in the Type group on the Chart Tools Design tab.
You can also launch the dialog box using a shortcut menu
 Clicking a chart type in the dialog box’s left pane displays the available
subtypes in the right pane. Moving the mouse pointer to a subtype icon
displays a tip with the name of the subtype.

32