Spread Sheets

By Richard John
 Spreadsheets:
A spreadsheet is an application that
displays small boxes called cells.
Spreadsheet consisting of rows and
columns, and each cell containing either
alphanumeric text or numeric values.
Spreadsheets are frequently used for
financial information because of their
ability to calculate the entire sheet
automatically.
The most popular spreadsheet
application used in Microsoft Windows is
 MICROSOFT EXCEL
Excel is a spreadsheet program in the
Microsoft Office system.
Excel is used to analyze data and make
more informed business decisions.
Specifically, you can use Excel to track
data, build models for analyzing data,
write formulas to perform calculations on
that data, pivot the data in numerous
ways, and present data in a variety of
professional looking charts.
 Getting Started
You may have a shortcut to Excel on
your desktop, if so double click the
icon and excel will open.

If not so, follow the steps below:

1. Click on the Start button
2. Highlight Programs
3. Highlight Microsoft Office
4. Click on Microsoft Excel
 Excel Terminology
Spreadsheet
Is a grid of data divided into numbered rows and
lettered columns
Is composed of rectangular grids of cells in which
text, numbers, formulas, and graphs are stored
Worksheet
Is a page of data in your spreadsheet (or Workbook)
that is organized by the labeled tabs displayed at the
bottom of the excel window.
Single page or tab within the Excel workbook.
Workbook
A workbook is a collection of spreadsheets. The
entire spreadsheet file.
By default, Excel creates a new workbook named
Book (1) with first cell (A1) active.
 Terminology…
Columns – are vertical lines denoted by letters.
Vertical array of data.
– Signified by letters – A, B, C etc.
Rows – are horizontal lined denoted by numbers.
Horizontal array of data.
– Signified by numbers – 1, 2,3 etc.
A cell
is the intersections of columns and rows.
– Signified by a combination of the column letter and
the row number. Examples: A1, C300, BA42
Active cell
A cell is active when a black rectangle encompasses
the cell.
By default, Excel creates a new workbook named
 Adding and Renaming Sheets
To add a worksheet
Right click on the tab of the sheet to be renamed
(located on the bottom of the worksheet). choose Insert.
To rename a works sheet
Right click on the tab of the sheet to be renamed
(located on the bottom of the worksheet). Choose
Rename.
To move a worksheet in a workbook
click and hold the left mouse button down on the
worksheet's tab.
Next, drag the worksheet to the desired position and
then release the mouse button.
Color Coding Tabs
Right click on the work sheet’s tab and then choose Tab
color from the menu.
Click on a color to apply that color to the current tab.
 Moving Around the Worksheet
To move to the next cell you can:
Click on the next cell with the mouse.
To move to the right press the
keyboard’s Right Arrow or the Tab key.
To move to the left press the Left
Arrow key or press Shift+Tab.
To move to the cell below press Enter
or Down Arrow key.
To move to the cell above press the Up
Arrow key or Shift+Enter.
 Moving Around the Worksheet
To move to the top row in the sheet use
the shortcut keys [Ctrl]+[Home], or
click once in the gray area directly below
the up arrow in the vertical scroll bar.
To move to the bottom row use the
shortcut key [Ctrl]+[End], or
click once in the gray area directly above
the down arrow in the vertical scroll bar.
 Moving or Copying Worksheets
Sometimes you may need to copy an entire
worksheet instead of copying and pasting
the data which may or may not paste
properly,
Move or Copy Worksheets
1. Select the worksheets that you want to move
or copy.
2. On the Home tab, in the Cells group, click
Format, and then under Organize Sheets,
click Move or Copy Sheet.
You can also right-click a selected sheet
tab, and then click Move or Copy.
Move or Copy to a Different Workbook
In the workbook that contains the sheets
that you want to move or copy, select the
sheets.
On the Home tab, in the Cells group,
click Format, and then under Organize
Sheets, click Move or Copy Sheet.
In the Move or Copy dialog box, click the
drop down list in the To book box, then:
Click the workbook to which you want
to move or copy the selected sheets.
Click new book to move or copy the
selected sheets to a new workbook.
 Entering Text and Numbers in Cells
When entering numbers or text in a cell, the
entry is visible in both the cell and formula
bar.
The cell number also displays in the Name
Box.
Enter Data in a Worksheet
1. Click the cell where you want to enter
data.
2. Type the data in the cell.
3. Press enter or tab to move to the next
cell.
 Save a Spreadsheet
To save a document in the format used
by Excel 2010 and Excel 2007, do the
following:
1. Click the File tab.
2. Click Save As.
3. In the File name box, enter a name
for your document.
4. Click Save.
 Selecting a Cell
You can select a cell by click on the cell
A cell is selected by moving a mouse
pointer, which resembles a thick white plus
sign to cell (A1) and Click.
Notice that a black border surrounds the
cell. This is the active cell.
You can also type a cells name in the
Name box ( e.g B2). Press Enter and that
cell will become the active cell.
In order to complete more advanced
processes in Excel you need to be able to
highlight or select cells, rows and columns.
 Select Cells or Ranges
There are a variety of way to do this,
Bold, Underline and Italics Text
Select the cell, range of cells, or
text.
Go to the Home tab, in the Font
group
Click on the Bold (B) Italics (I) or
Underline (U) commands.
The selected command will be
applied.
 Format Numbers
In Excel, the format of a cell is separate from
the data that is stored in the cell.
After you type numbers in a cell, you can
change the format in which they are displayed
to ensure the numbers in your spreadsheet are
displayed as you intended.
1. Click the cell(s) that contains the
numbers that you want to format.
2. On the Home tab, in the Number group,
click the arrow next to the Number Format
box, and then click the format that you want.
For example, numbers in cells will default as
rounded numbers, date and time may not
appear as anticipated.
If you are unable to format numbers in the
detail you would like that you can click on the
More Number Formats at the bottom of the
Number Format drop down list.
In the Category list, click the format that you
want to use, and then adjust settings to the
right of the Format Cells dialog box.
For example, if you’re using the Currency
format, you can
select a different currency symbol,
show more or fewer decimal places, or
change the way negative numbers are
displayed.
 Cut, Copy, and Paste Data
You can use the Cut, Copy, and Paste
commands in Microsoft Office Excel to
move or copy entire cells or their contents.

NOTE: Excel displays an animated moving

border around cells that have been cut or
copied.
To cancel a moving border, press ESC.
NOTES: Excel replaces existing data in the
paste area when you cut and paste cells to
move them.
When you copy cells, cell references are
 Move/Copy Cells
When you move or copy a cell, Excel moves or
copies the entire cell, including formulas and
their resulting values, cell formats, and
comments.
Select the cells that you want to move or
copy.
On the Home tab, in the Clipboard group, do
one of the following:
To move cells, click Cut

To copy cells, click Copy

Click in the center of the cell you would like

to Paste the information too.
On the Home tab, in the Clipboard group,
 Move/Copy Cells with Mouse
1. Select the cells or a range of
cells that you want to move or copy.
2. To move a cell or range of cells,
point to the border of the selection.
When the pointer becomes a move
pointer, drag the cell or range of
cells to another location.
 Changing Column Widths
To adjust the width between two
columns place the mouse pointer on the
line between the column headers (E, F).
When the pointer changes to a two-
headed arrow double-click and Excel
will automatically fit the column to the
contents of the cell.
An alternative method is to click and
drag the two-headed arrow either left or
right.
Notice a small window displays the width
as you move the column.
Automatically Fit Column/Row Contents
1. Click the Select All button
2. Double-click any boundary between two
column/row headings.
3. All Columns/Rows in the entire worksheet
will be changed to the new size
NOTE: At times, a cell might display #####.
This can occur when the cell contains a number
or a date that exceeds the width of the cell so it
cannot display all the characters that its format
requires.
To see the entire contents of the cell with its
current format, you must increase the width of
the column.
 Inserting Rows or Columns
To insert a row or column:
Right click in a cell to activate the context
menu and choose Insert.
A context menu opens asking if you would
like to insert an entire row or column.
OR
Click the row or column header, then click
the Insert list arrow in the group Cells on the
Home tab.
Upon choosing the desire command, Excel
inserts new columns to the left of the
selection and new rows above.
To insert more than one column or row:
First click on a column or row heading,
while holding the mouse button down
drag the white plus sign over the desired
number of column labels (A, B, C, etc.) or
row labels (1, 2, 3, etc.).
Then chose from one of the two methods
listed:
(1) Right click and choose Insert from the
context menu, or
(2) Click the Insert list arrow in the group

Cell on the Home tab and select Insert

Sheet Columns/Rows.
 Wrap Text
You can display multiple lines of text inside a cell
by wrapping the text.
Wrapping text in a cell does not affect other cells.
1. Click the cell in which you want to wrap the
text.
2. On the Home tab, in the Alignment group,
click Wrap Text.
3. The text in your cell will be wrapped.
NOTE: If the text is a long word, the characters
won't wrap (the word won't be split); instead, you
can widen the column or decrease the font size to
see all the text.
If all the text is not visible after you wrap the text,
you might have to adjust the height of the row.
Merging Cells to Center Text across Columns
Type some text in cell (A1) and the press the
Enter key.
Reselect cell (A1), then center and hold the
mouse button down inside the cell.
Drag the mouse pointer (a thick white plus
sign) across to column (H1).
Click on the Merge & Center command located
on the Formatting bar.
Note: The cells remain active until you
click outside the active area (merged cells).
Merge Cells
To merge cells only, click the arrow next
to Merge and Center, and then click
Merge Across or Merge Cells.

Split Cells
1. Select the merged cell you want to
split
2. To split the merged cell, click Merge
and Center .
The cells will split and the contents of
the merged cell will appear in the upper-
 Using Auto Fill
This feature is used to automatically
fill-in dates, numbers, or formulas
by dragging the Fill handle across
adjacent cells.
Note: The small black rectangle on
the outside corner of a selected cell
is known as the Fill handle.
 Auto Fill Date
1. Format an open cell to accept the date format.
Right click on the cell and choose Format
Cells from the shortcut menu.
2. On the tab Number, choose the category Date
and then the first type listed. Click OK.
3. Type the current date in [mm/dd/yy] format
(3/7/2012) and then press Enter.
4. Right click hold the mouse button down,
while dragging the fill handle across the next
five cells.
5. Release the mouse button and then select Fill
Months from the "Auto Fill Options" menu.
The date that was entered will now display in
 Auto fill Days, Months, and Time
Examples are Days, Months, and Time.
Type the weekday Monday in cell (D3),
and then press the Enter key.
Reselect the cell and then click and
hold the mouse button down while
dragging the AutoFill handle across a
range of cells (multiple cells).
Excel automatically adds the related
column headings (Tuesday, Wednesday,
Thursday, etc.) in the preceding
columns or rows.
 Auto Fill Time
Excel can also AutoFill time.
Type (12:00 AM) in the first cell (make
sure there is a space between the last
zero and AM) press the Enter key.
Next type the desired increment for
time (12:30 AM.)
To continue the time entry select the
two entries and then click and hold the
mouse button down while dragging the
AutoFill handle to the last cell that will
display the end time.
 Increment a cell’s value by one
1. Type the number (1) in the first open
cell, and then press the Enter key.
2. Click back in the cell and then place the
cursor on the fill handle.
3. Hold the Ctrl key down as you drag the
fill handle over the adjacent cells.
4. Notice the small plus sign beside the
mouse pointer. Release the mouse
button and then the Ctrl key, the cells
are now populated by one.
 Sorting Cells
You may use either method to sort a range
of cells.
1. Select the range of cells that requires
sorting.
2. Click on the Sort & Filter command in
the group Editing on the Home tab.
Select from the options listed, Ascending
order (A to Z), Descending order (Z to A),
or Custom Sort. OR
Click the tab Data then choose a sort
option provided in the group Sort &
Sort Data in Multiple Columns or Rows
You may want to sort by more than one column
or row when you have data that you want to
group by the same value in one column or row,
and then sort another column or row within
that group of equal values.
1. Select a range of cells with two or more
columns of data.
2. On the Data tab, in the Sort & Filter group,
click Sort.
3. The Sort dialog box will appear.
4. Under Column, in the Sort by box, select
the first column that you want to sort.
5. Under Sort On, select the type of sort.
To sort by text, number, or date and time,
select Values.
To sort by format, select Cell Color, Font
Color, or Cell Icon.
Under Order, select how you want to sort.
For text values, select A to Z or Z to A.
For number values, select Smallest to Largest
or Largest to Smallest.
For date or time values, select Oldest to
Newest or Newest to Oldest.
To sort based on a custom list, select Custom
List.
 Formatting a Cell
Formatting a cell displays the
contents in various formats for text,
numbers, fonts, colors, and borders.
Examples would include
the number of decimal places in
currency,
text position,
 various fonts, and
borders.
 Excel Calculations
In Excel, you can assign a
calculation to a cell by,
click the Sum button arrow list in
the Editing group on the Home tab,
Type an ‘=‘ sign in the desired
cell and then enter a formula using
values, cell references, operations,
or Functions, and
click the Insert Function button on
the Formula Bar.
 Addition
First, click the cell where you want
to insert the total.
Click the Sum button (the Greek
symbol Σ) located in the Editing
group on the Home tab.
The range of cells automatically
above the cell is selected by default.
Use the mouse to select the correct
range, then press the Enter key for
the Sum.
 Use Auto Sum
To summarize values quickly, you
can also use AutoSum.
1. Select the cell where you would like
your formulas solution to appear.
2. Go to the Home tab, in the Editing
group,
3. Click AutoSum, to sum your
numbers or click the arrow next to
AutoSum to select a function that
you want to apply.
Addition- alternative method
An alternative method is to click inside
cell and then click on Function Box icon
to open the Insert Function dialog box.
When the dialog box opened, you would
choose the function sum, which opens the
Function Palette.
Collapse the dialog box by clicking on
the small icon that resembles a
spreadsheet.
Next, drag the mouse arrow over the cell
range. Press the OK button.
 Formulas in Excel
Formulas are equations that perform
calculations on values in your worksheet.
A formula always starts with an equal
sign (=).
An example of a simple is =5+2*3 that
multiplies two numbers and then adds a
number to the result.
For example, the formulas =SUM(A1:A2)
and SUM(A1,A2) both use the SUM
function to add the values in cells A1 and
A2.
Microsoft Office Excel follows the
standard order of mathematical
operations.
In the preceding example, the
multiplication operation (2*3) is
performed first, and then 5 is added
to its result.
You can also create a formula by
using a function which is a prewritten
formula that takes a value, performs
an operation and returns a value.
Depending on the type of formula that you
create, a formula can contain any or all of the
following parts.
Functions - A function, such as PI() or
SUM(), but starts with an equal sign (=).
Cell references -You can refer to data in
worksheet cells by including cell references
in the formula.
For example, the cell reference A2 returns
the value of that cell or uses that value in the
calculation.
Constants - You can also enter constants,
such as numbers (such as 2) or text values,
directly into a formula.
 Create a Simple Formulas
1. Click the cell in which you want to
enter the formula.
2. Type = (equal sign).
3. Enter the formula by typing the
constants and operators that you want to
use in the calculation.
4. Press ENTER
Create a Formula with Cell References
Click the cell in which you want to enter
the formula.
In the formula bar, at the top of the Excel
window that you use, , type = (equal sign).
Click on the 1st cell you want in the
formula.
Enter an Operator such as +, or *.
Click on the next cell you want in the
formula.
Continue steps 3 – 5 until the formula is
complete
 Create a Formula with Function
Click the cell in which you want to enter the
formula.
Click Insert Function on the formula bar .
Excel inserts the equal sign (=) for you.
Select the function that you want to use.
If you're not sure which function to use,
type a question that describes what you
want to do in the Search for a function box
(for example, "add numbers" returns the
SUM function), or browse from the
categories in the Or Select a category box.
Enter the arguments.
 Delete a Formula
When you delete a formula, the resulting
values of the formula is also deleted.
To delete formulas along with their
resulting values, do the following:
Select the cell or range of cells that contains
the formula. Press DELETE.
To delete formulas without removing their
resulting values, do the following:
Select the cell or range of cells that contains
the formula.
On the Home tab, in the Clipboard group,
click Copy
On the Home tab, in the Clipboard group,
 Most commonly used Functions
Multiplication
The asterisk is used for multiplication when
referencing two or more cells.
To display the product of cells (C6) and (D6)
in cell (E7) enter this following formula in cell
(E7) = C6*D6.
Subtraction
Using the above method substitute the
asterisk with a hyphen between the cell
references.
Division
Using the above methods substitute the
 Charts in Excel
Charts are used to display series of numeric
data in a graphical format to make it easier to
understand large quantities of data and the
relationship between different series of data.
You can create a basic chart by clicking the
chart type that you want on the Insert tab in
the Charts group.
To create a chart in Excel, you start by
entering the numeric data for the chart on a
worksheet.
Then you can plot that data into a chart by
selecting the chart type that you want to use
on the Insert tab, in the Charts group.
 Create a Chart
1. On the worksheet, arrange the data that
you want to plot in a chart. The data can be
arranged in rows or columns — Excel
automatically determines the best way to
plot the data in the chart.
2. Select the cells that contain the data that
you want to use for the hart.
NOTE: If the cells that you want to plot in
a chart are not in a continuous range, you
can select nonadjacent cells or ranges as
long as the selection forms a rectangle.
You can also hide the rows or columns that
you do not want to plot in the chart.
4. Click the chart type, and then click a
chart subtype from the drop menu that
will appear.
5. Click anywhere in the embedded chart
to activate it. When you click on the
chart, Chart Tools will be displayed
which includes the Design, Layout, and
Format tabs.
6. The chart will automatically be
embedded in the worksheet. A chart
name will automatically be assigned
 Getting to know the elements of a
chart
A chart has many elements. Some of
these elements are displayed by
default, others can be added as
needed.
You can change the display of the
chart elements by moving them to
other locations in the chart, resizing
them, or by changing the format.
You can also remove chart elements
 Chart Elements
The chart area is the entire chart and all its elements
The plot area is the area of the chart bounded by the
axes
The data points are individual values plotted in a
chart represented by bars, columns, lines, or pies.
The horizontal (category) and vertical (value) axis
along which the data is plotted in the chart.
The legend identifies the patterns or colors that are
assigned to the data series or categories in the chart.
A chart and axis title are descriptive text that for the
axis or chart.
A data label provides additional information about a
data marker that you can use to identify the details of
a data point in a data series.
Move Chart to New Sheet
On the Design tab, in the Location group,
click Move Chart.
Under Choose where you want the chart to
be placed, click on the New sheet bubble
Type a chart name in the New sheet box.

Change Chart Name

Click the chart.
On the Layout tab, in the Properties group,
click the Chart Name text box.
Type a new chart name.
Press ENTER.
Change Chart Layout
Click anywhere in the chart.
Go to the Chart Tools, the Design
group
In the Chart Layouts, click the chart
layout that you want to use.
To see all available layouts, click More
.
Change Chart Style
1. Click anywhere in the chart.
2. On the Design tab, in the Chart
Styles group, click the chart style that
 Change Chart or Axis Titles
To make a chart easier to understand, you can add
titles, such as chart and axis titles.
To add a chart title:
Click anywhere in the chart.
On the Layout tab, in the Labels group, click Chart
Title.
Click Centered Overlay Title or Above Chart.
In the Chart Title text box that appears in the chart,
type the text that you want.
To remove a chart title, click Chart Title, and then click
None.
You can also use the formatting buttons on the
ribbon (Home tab, Font group).
To format the whole title, you can right-click it, click
Format Chart Title, and then select the formatting
 To add axis titles
1. Click anywhere in the chart.
2. On the Layout tab, in the Labels group, click
Axis Titles.
3. Do one or more of the following:
To add a title to a primary horizontal (category)
axis, click Primary Horizontal Axis Title, and
then click the option that you want.
To add a title to primary vertical (value) axis,
click Primary Vertical Axis Title, and then click
the option that you want.
4. In the Axis Title text box that appears in the
chart, type the text that you want.
5. To remove an axis title, click Axis Title, click the
type of axis title to remove, and then click None.
 Chart Data Labels
1. On a chart, do one of the following:
Click on the chart area to add a data label to all data
points of all data series
Click in the data series to add a data label to all data
points of a data series
Click on a specific data point to add a data label to a
single data point in a data series
2. On the Layout tab, in the Labels group, click Data
Labels, and then click the display option that you want.
3. Text boxes will appear in the area of your chart
based on your selection.
4. Click on the text box to modify the text.
5. To remove data labels, click Data Labels, and then
click None.
NOTE: Depending on the chart type that you used,
different data label options will be available.
 Legend
When you create a chart, the legend appears, but you
can hide the legend or change its location after you
create the chart.
1. Click the chart in which you want to show or hide a
legend.
2. On the Layout tab, in the Labels group, click Legend.
3. Do one of the following:
a. To hide the legend, click None.
b. To display a legend, click the display option that you
want.
c. For additional options, click More Legend Options, and
then select the display option that you want.
NOTE: To quickly remove a legend or a legend entry
from a chart, you can select it, and then press DELETE.
You can also right-click the legend or a legend entry,
and then click Delete.
 Move or Resize Chart
You can move a chart to any location on
a worksheet or to a new or existing
worksheet. You can also change the size
of the chart for a better fit.
To move a chart, drag it to the location
that you want.
To resize a chart, click on one of the
edges and drag towards the center.
 Cell Borders
By using predefined border styles, you
can quickly add a border around cells or
ranges of cells.
If predefined cell borders do not meet
your needs, you can create a custom
border.
NOTE: Cell borders that you apply
appear on printed pages. If you do not
use cell borders but want worksheet
gridline borders for all cells to be
visible on printed pages, you can
 Apply Cell Borders
On a worksheet, select the cell or range of
cells that you want to add a border to, change
the border style on, or remove a border from.
Go to the Home tab, in the Font group
Click the arrow next to Borders
Click on the border style you would like
The border will be applied to the cell or cell
range
In the Format Cells dialog box, on the Border tab,
under Line and Color, click the line style and color
that you want.
NOTE: To apply a custom border style, click
More Borders.
 Remove Cell Borders
1. Go to the Home tab, in the Font
group
2. Click the arrow next to Borders
3. Click No Border .
NOTES: The Borders button displays the
most recently used border style.
You can click the Borders button (not the
arrow) to apply that style.
 Clearing an Entire Worksheet
To clear the worksheet, select the
worksheet by clicking on the rectangle in
the upper corner of the worksheet between
Column Title (A) and Row Title (1).
Click the Clear list arrow on the Home tab
to Clear All, Clear Formats, Clear Contents,
and Clear Comments.
Note: If the worksheet has been formatted –
merged cells, numbers, or applied borders
press the Delete key to clear all contents.
Pressing the Delete key does not remove
any cell formatting
 Finalizing a Spreadsheet
To complete your spreadsheet there are a
few steps to take to ensure your document
is finalized.
Using the "Spell Check" Feature Excel
does not have the same spell check feature
as Word and PowerPoint.
To complete a Spelling and Grammar
check, you need to use the Spelling and
Grammar feature.
1. Click on the Review tab
2. Click on the Spelling & Grammar
command (a blue check mark with ABC
above it).
 Cell Styles
You can create a cell style that includes a
custom border, colors and accounting
formatting.
1. On the Home tab, in the Styles group,
click Cell Styles.
2. Select the different cell style option
you would like applied to your
spreadsheet.

NOTE: If you would like to apply a cell fill

and a cell border, select the cell fill color
 Cell and Text Coloring
You can also modify a variety of cell and text
colors manually.
Cell Fill
1. Select the cells that you want to apply or
remove a fill color from.
2. Go to the Home tab, in the Font group and
select one of the following options:
 a. To fill cells with a solid color, click the arrow
next to Fill Color , and then under Theme Colors
or Standard Colors, click the color that you want.
 b. To fill cells with a custom color, click the arrow

next to Fill Color , click More Colors, and then in

the Colors dialog box select the color that you
want.
 Remove Cell Fill
1. Select the cells that contain a fill color or fill
pattern.
2. On the Home tab, in the Font group, click
the arrow next to Fill Color, and then click No
Fill.
Text Color
Select the cell, range of cells, text, or
characters that you want to format with a
different text color.
On the Home tab, in the Font group and
select one of the following options:
 To
apply the most recently selected text color, click
Font Color.
 Print Preview
Print Preview automatically displays
when you click on the Print tab.
Whenever you make a change to a print-
related setting, the preview is
automatically updated.
1. Click the File tab, and then click
Print. To go back to your document,
click the File tab.
2. A preview of your document
automatically appears. To view each
page, click the arrows below the
preview.
 Print a Worksheet
Click the worksheet or select the worksheets
that you want to print.
Click File, then click Print.
Once you are on the Print screen you can select
printing options:
 To change the printer, click the drop-down box under
Printer, and select the printer that you want.
 To make page setup changes, including changing page
orientation, paper size, and page margins, select the
options that you want under Settings.
 To scale the entire worksheet to fit on a single printed
page, under Settings, click the option that you want in the
scale options drop-down box.
 To print the specific information, select Print Active Sheets
or Print Entire Workbook
 Print Options for Excel Workbooks
Print options include printing the entire
workbook or the current worksheet.
The user has the ability to print specific areas
(print area), charts, change layouts (portrait or
landscape), and print to a specific paper width.
To set a Print Area
Select a cell range to define the area or hold
the Ctrl key down to select additional data to
include within the area. With the area still
selected, click the Set Print list arrow, and
choose Set Print
Area in the group Page Setup on the Page
Layout tab. This is the only area that will
print until you clear the Print Area.
 To Print Row & Column Headings on Each Worksheet
Click on Print Titles in the group Page
Setup on the tab Page Layout.
Under the label Print Titles, “Rows to
Repeat” type the cell range in the space
provided or collapse the dialog box, and
then select the range with the mouse
pointer.
Click on OK to retain the settings.
 To Print to a Specific Paper Width
Click the Margins arrow and the Custom
Settings located in the group Page Setup
on the tab Page Layout.
Choose from the options provided to
select paper orientation, page scaling,
and paper size.
On the same tab, click on the commands
Margins | Custom margins to open the
Page Setup dialog box.
 Page Setup
Click the tab Page to change print
orientation, set the print scale, or paper
size.
Click the tab Margins to customize page
margins or to center the page both
horizontally or vertically.
Click the tab Header/Footer and then the
appropriate button to enter and
customize your selection.
Click the tab Sheet to define or include
Grid lines, Row and column headings,
Advanced Spreadsheet Modification
Once you have created a basic
spreadsheet there are numerous things
you can do to make working with your
data easier.
Some of these elements are hiding,
freezing and splitting rows.
You can also sort and filter data, these
features are quite helpful when working
with a large amount of data.
 Hide or Display Rows and Columns
You can hide a row or column by using
the Hide command or when you change
its row height or column width to 0
(zero).
You can display either again by using the
Unhide command.
You can either unhide specific rows and
columns, or you can unhide all hidden
rows and columns at the same time.
The first row or column of the worksheet
is tricky to unhide, but it can be done.
 Hide Rows or Columns
1. Select the rows or columns that you
want to hide.
2. On the Home tab, in the Cells group,
click Format.
3. Under Visibility, point to Hide &
Unhide, and then click Hide Rows or
Hide Columns.

NOTE: You can also right-click a row or

column (or a selection of multiple rows or
columns), and then click Hide.
 Unhide Rows or Columns
1. Select the rows, columns or entire
sheet to unhide.
2. On the Home tab, in the Cells group,
click Format.
3. Under Visibility, point to Hide &
Unhide, and then click Unhide Rows or
Unhide Columns.

TIP: You can also right-click the

selection of visible rows and columns
surrounding the hidden rows and
Freezing/Splitting Rows and Columns
To keep an area of a worksheet visible while
you scroll to another area of the worksheet, you
can either lock specific rows or columns in one
area by freezing panes.
Freezing
When you freeze panes, Microsoft Excel keeps
specific rows or columns visible when you scroll
in the worksheet.
For example, if the first row in your
spreadsheet contains labels, you might freeze
that row to make sure that the column labels
remain visible as you scroll down in your
spreadsheet.
 Freezing Panes
Excel 2010 offers two easy solutions to freezing
panes in a spreadsheet.
To keep columns and rows visible click below
the column label and to the right of the first
row label. For this example, cell (B2).
Click the Freeze Panes list arrow in the group
Window on the View tab and select Freeze
Panes. The column and row headings remain
visible when you scroll up or down and to the
left or right.
To unfreeze the pane,
click Unfreeze Panes.
 Freeze Panes
1. On the worksheet, select the row or column that
you want to keep visible when you scroll.
2. On the View tab, in the Window group, click the
arrow below Freeze Panes.
3. Then do one of the following:
To lock one row only, click Freeze Top Row.
To lock one column only, click Freeze First
Column.
To lock more than one row or column, or to lock
both rows and columns at the same time, click
Freeze Panes.
NOTE: You can freeze rows at the top and columns
on the left side of the worksheet only. You cannot
freeze rows and columns in the middle of the
 Unfreeze panes
1. On the View tab, in the Window
group, click the arrow below Freeze
Panes.
2. Click Unfreeze Panes.
 Split panes
When you split panes, Excel creates either
two or four separate worksheet areas that
you can scroll within, while rows or columns
in the non-scrolled area remain visible.
This worksheet has been split into four
areas. Notice that each area contains a
separate view of the same data.
Splitting panes is useful when you want to
see different parts of a large spreadsheet at
the same time.
NOTE: You cannot split panes and freeze
panes at the same time.
 Split Panes
1. To split panes, point to the split box at
the top of the vertical scroll bar or at the
right end of the horizontal scroll bar.
2. When the pointer changes to a split
pointer or, drag the split box down or to
the left to the position that you want.
3. To remove the split, double-click any
part of the split bar that divides the
panes.
 Protect worksheet
Review menu
Changes group of icons
Protect worksheet
Enter password
To unprotect, click the same
Enter password
 Brief Exercise
This exercise was designed to help apply the
information learned from the preceding slides.
Select a new worksheet before beginning the
exercise.
1. In cell (A1) type Loose Change Cafe and then
press the Enter key.
2. In cell (A2) type Week of (mm/dd/yy) to
(mm/dd/yy) [add 6 days to the end date]. Press
the Enter key.
3. In cell (A3) type Daily Sales Revenue and then
press the Enter key twice.
4. In cell (A5) type Week Day and then press the
Enter key.
5. In cell (A6) type Cash Receipts and then press
7. In cell (A11) type Average Revenue and then
press the Enter key.
8. In cell (C5) type Monday and then press the
Tab key. Select cell (C5) and then drag the
AutoFill handle across to cell (H5). Release
the mouse button.
9. In cell (C6) type the number (1200) and then
press the Tab key or use the right keyboard
arrow. In cell (C7) type the number (1500) and
then press the Tab or Enter key.
Notice that the text (Monday) is aligned left
and that the (numbers) are aligned right.
10. Select cell (C6), hold the mouse button down while
dragging the thick white plus sign to cell (C7).
When both cells are selected, place and hold the
mouse over the autofill handle (changes to a thin
black + sign) drag the fill handle to cell (H6) and
then release the mouse button.
The cells are now incremented by the amount of
(300) and remain highlighted.
11. Right-click anywhere within the highlighted cells
and the Format Cells dialog box will open. Choose
the tab Number and the category Currency.
Note: Inspect that the text boxes Decimal places
and Symbol include the number (2) for decimal
places and the ($) symbol. Click OK.
Formatting Instructions for Cell A1
11. Select cell (A1), hold the mouse button down while
dragging the white plus sign to cell (H1), and then
release the mouse button. Click on the Merge &
Center button in Alignment group on the Home tab.
12. Click the Fill button in the Font group and apply the
background color yellow. Within the same group,
click the Border list arrow and select Outside Borders
(7thfrom the top).
13. Click the Font size list arrow and change the font size
to (24). Repeat the same steps to the Font box and
change the font to Garamond and the style to Bold.
Press Enter.
14. Repeat the prior steps and format cell (A2), except
this time change the font to size (14) Bold. Do not add
a border, background color, or change the font.
15. Repeat the same steps for cell (A3).
16. Select the cell range (A5:H5) and change the font size to
(11) Bold. Reselect the same range and add a border. Click
on the Top & Bottom Border button (2nd row, 3rd from the
left).
17. Select the cell range (A6:H6) and change the font size to
(11) Bold.
18. Repeat step (7) for cell range (A10:A11) and add a Thick
Black Border (last row & box).
19. Adjust the headings for columns (A) and (B). Place the
mouse pointer on the fine line between both column
headings and then double-click the mouse button.
20. Adjust the column width manually for column (B) to (9.43).
21. AutoFit columns (C, D, E, F, G, and H) by holding down the
Ctrl key while clicking on each column heading. Click the
Format list arrow in the Cell group and select AutoFit
Column Width. The selected columns are adjusted to their
contents.
Extra notes
 Learning Basic Functions of Excel
A. Knowing the Worksheet
Cell address –row and column positions, Inserting
and deleting worksheet under the same file,
renaming and moving worksheets; Freezing and
unfreezing pane by worksheet row, column or both
for data entry, Hiding and un -hiding row or column,
Selecting entire row, column, or a portion of the
worksheet.
B. Data Entry
Using keyboard cursor key and Number lock for data
entry; text and numerical data entry; text labels for
row or columns; Format cell for various types of data
–changing decimal places of numeric data, wrap text
the cell alignment for label texts in fixed column
width.
C. Worksheet Calculations
Auto sum of row total and column total; Sum of selected
cells in a row or column; copy paste of calculation from one
cell to following cells in a row or a column, copy paste
calculation from more than one cell into an array of cells in
rows and columns; Formula bar –writing formula in formula
bar for simple arithmetic calculations; formula paste
function for other calculations.
D. Inserting and Formatting Worksheet
Inserting and deleting row, column, or cell, merge cell,
adjusting height of row and width of column; selecting font
and font size in cells, rows, columns, or in entire work sheet;
borders and background colours, Inserting and removing
page break – horizontal and vertical, Inserting data from
Word Tables and pasting Excel tables to Word; Creating and
Inserting Charts; Copy paste Excel Charts in Word document
and resize.
E. Editing
Cut, Copy and Paste operations; Special Paste
functions –paste value and paste formula, transpose
row and column; Fill series in columns e.g. serial
numbers; Data sorting and subtotals.
F. Printing
Page set up; Print Preview; setting print area, setting
page title –rows to repeat at top or columns to repeat
at left;
G. Data Analysis
Calculation using formula paste function ( f x) –
statistical formula for calculating mean, median,
mode, Correlation, etc; Data Analysis –simple
regression and t -values
 The Ribbon
Understanding the Ribbon is a great way to help
understand the Ms excel.
The ribbon holds all of the information in previous
versions of Microsoft Office in a more visual
stream line manner through a series of tabs that
include an immense variety of program features.
 Ribbon
Home Tab
This is the most used tab; it incorporates all text and cell
formatting features such as font and paragraph changes.
The Home Tab also includes basic spreadsheet formatting
elements such as text wrap, merging cells and cell style.
Insert Tab
This tab allows you to insert a variety of items into a
document from pictures, clip art, and headers and footers.
Page Layout Tab
This tab has commands to adjust page such as margins,
orientation and themes.
Formulas Tab
This tab has commands to use when creating Formulas.
This tab holds an immense function library which can
assist when creating any formula or function in your
spreadsheet.
 Ribbon
Data Tab
This tab allows you to modifying worksheets with
large amounts of data by sorting and filtering as well
as analyzing and grouping data.
Review Tab
This tab allows you to correct spelling and grammar
issues as well as set up security protections. It also
provides the track changes and notes feature
providing the ability to make notes and changes
someone’s document.
View Tab
This tab allows you to change the view of your
document including freezing or splitting panes,
viewing gridlines and hide cells.