MS-PowerPoint 2007
Spreadsheet Basics
· Screen Layout
· Title bar
· Menu bar
· Standard Toolbar
· Other Tools
· Task Pane
· Adding and Renaming Worksheets
Modifying Worksheets
· Moving Through Cells
· Adding Worksheets, Rows,
Columns, and Cells
· Resizing Rows and Columns
· Selecting Cells
· Moving and Copying Cells
· Deleting Rows, Columns, and Cells
· Freeze Panes
Formatting Cells
· Formatting Toolbar
· Format Cells Dialog Box
· Formatting Worksheet
· Dates and Times
· Format Painter
· AutoFormat
Formulas and Functions
· Formulas
· Linking Worksheets
· Relative, Absolute, and Mixed
Referencing
· Basic Functions
· AutoSum
Sorting and Filling
· Basic Sorts
· Complex Sorts
· Auto-fill
Comparing Workbooks
· Compare Side by Side
Page Properties and Printing
· Page Breaks
· Page Setup
· Margins
· Header/Footer
· Sheet
· Print Preview
· PrintAcademic Computing Center - AUB Excel 2003 - Tutorial I
http://staff.aub.edu.lb/~acc/
Wednesday, January 18, 2006 2 of 19
Spreadsheet Basics
Microsoft Excel is a spreadsheet program that you can use to organize, analyze and
attractively present data such as a budget or sales report. Each Excel file is a workbook
that can hold many worksheets. The worksheet is a grid of columns, designated by
letters, and rows, designated by numbers. The letters and numbers of the columns and
row called labels are displayed in gray buttons across the top and left side of the
worksheet. The intersection of a column and a row is called a cell. Each cell on the
spreadsheet has a cell address that is the column letter and the row number. Cells can
contain text, numbers, or mathematical formulas.
Screen Layout
Menu bar Standard toolbar Fo Formatting toolbar N rmula Bar ame Box
Row 5
Column E
Cell E5
W Task pane
orksheet tab
scroll buttons
Worksheet tabsAcademic Computing Center - AUB Excel 2003 - Tutorial I
http://staff.aub.edu.lb/~acc/
Wednesday, January 18, 2006 3 of 19
Title bar
The Title bar contains the name of the program Microsoft Excel, and the default name of
the workbook (Excel file) Book 1 that would change as soon as you save your file and
give another name.
Menu bar
The Menu bar contains menus that include all the commands you need to use to work
your way through Excel such as File, Edit, View, Insert, Format, Tools, Data,
Window, and Help
Standard Toolbar
This toolbar is located just below the Menu bar at the top of the screen and allows you to
quickly access basic Excel commands.
Note: If not all the list appears, press on the Toolbar Options button placed at the end
of any toolbar, and press on Add or Remove Buttons tag, then select Standard and you
will get the possible commands for this toolbar.
· New: Select File > New from the Menu bar. The New Workbook pane
appears on the right hand side of the screen. Under the title New select
Blank workbook. An alternative is to click the New button found on
the Standard toolbar to create a new workbook.
· Open: Click File > Open from the Menu bar, or click the Open button
found on the Standard toolbar to open an existing workbook.
· Save: To save click on the Save button found on the Standard
toolbar. Choose a directory to save the file in. Specify a filename, and then
press Save. This is done only the first time you try to save a file. It is
recommended that you save your changes every ten minutes. All you have
to do is to press on the Save button, or simply go to File > Save. This will
update your initially saved file.
Print Preview
New
Open
Save
Permission
Spelling
Research
Cut
Copy
Paste
Format Painter
Undo/Redo
Insert Hyperlink
AutoSum
Sort
Chart Wizard
Drawing
Zoom
Help
Toolbar
OptionsAcademic Computing Center - AUB Excel 2003 - Tutorial I
http://staff.aub.edu.lb/~acc/
Wednesday, January 18, 2006 4 of 19
· Save As: To save a different copy or version: Click on the Save As option
in the File menu, and save your document under a different name, or the
same name but in a different location.
· Print: Select File > Print from the Menu bar, or click the Print button
found on the Standard toolbar to print a worksheet.
· Print Preview: Select File > Print Preview from the Menu bar, or click
the Print Preview button found on the Standard toolbar to preview
the worksheet before it is printed.
· Spelling: Use the Spelling button found on the Standard toolbar to
correct spelling errors on the worksheet.
· Cut, Copy, Paste, and Format Painter : These
actions are explained in the Modifying Worksheets section.
· Undo and Redo: Click the backward Undo arrow to cancel
the last action you performed, whether it is entering data into a cell,
formatting a cell, entering a function, etc. click the forward Redo arrow to
cancel the Undo action.
· Insert Hyperlink: To insert a hyperlink to a Web site on the Internet,
type the text into a cell you want to be the link that can be clicked with the
mouse. Then, click the Insert Hyperlink button found on the
Standard toolbar and enter the Web address you want the text to link to
and click OK.
· AutoSum, Function Wizard, and Sorting: These tools are discussed in
detail in the Formulas and Functions section.
· Zoom: To change the size of the worksheet that appears on the screen,
choose a different percentage from the Zoom option found on
the Standard toolbar.
Other Tools
a. Formatting toolbar: used to format text, for example font type / size / alignment /
color / text indentation. Also used to create bulleted / numbered lists, borders…
etc.
b. Drawing toolbar: contains certain commands for drawing shapes, filling colors…
etc.
Note: To add or remove a toolbar select from the Menu bar, View > toolbars and
then select the toolbar of your choice. A toolbar that is displayed has a check
beside it.
c. Scroll bars: allow you to browse through a worksheet.Academic Computing Center - AUB Excel 2003 - Tutorial I
http://staff.aub.edu.lb/~acc/
Wednesday, January 18, 2006 5 of 19
Task Pane
The Task Pane appears each time you start Excel. To display or hide the task pane:
From the Menu bar, select View > Task pane. To close it, click on the small X button at
the top-left corner. The Task Pane is a dynamic tool found in the Office XP and 2003
suite applications. It allows you to perform certain actions/commands some of which are
shortcuts to commands provided by the Menu bar or Standard toolbar.
The task pane contains several options:
· Getting started: It allows you to connect to the internet to get more information
on Microsoft Excel. Moreover, you can open saved files from your local PC and
create a new workbook.
· Help: in case you are lost and you need some feedback. Under Search for you
can directly type your keyword and Excel will provide you with information
(on/offline).
· Search Results: Allows you to view the result of your previous search under
Help. It allows you to enter a new search at the bottom of this pane.
· Clip Art: allows you to search the Clip Art Gallery using keywords.
· Research: if you are doing a research Excel can provide you with online
information. You can choose what type of reference books you would like
Microsoft to take into consideration while searching online.
· Clipboard: a list of the items you have recently cut, pasted, or copied
· New Workbook: you can open a new blank workbook or select one from the
existing workbooks available in your local computer, or select one of the
templates saved in Excel.
· Shared Workspace: you can create a document workspace if you want to share a
copy of your document. A workspace also enables you to invite others, assign
them tasks, and link to additional resources.
Adding and Renaming Worksheets
The worksheets in a workbook are accessible by clicking the Worksheet tabs in the
lower part of the screen. By default, three worksheets are included in the default
workbook. To add a sheet, select Insert > Worksheet from the Menu bar. To rename the
Worksheet go to Format > Sheet > Rename or right-click on the tab with the mouse
and select Rename from the Shortcut menu or double click on the name of the sheet and
when it is highlighted you can type in the new name. Press the Enter key after having
typed in the new sheet name.
Modifying Worksheets
Moving Through CellsAcademic Computing Center - AUB Excel 2003 - Tutorial I
http://staff.aub.edu.lb/~acc/
Wednesday, January 18, 2006 6 of 19
Use the mouse to select a cell you want to begin adding data to and use the keyboard
strokes listed in the table below to move through the cells of a worksheet.
Movement Key stroke
One cell up up arrow key
One cell down down arrow key or ENTER
One cell left left arrow key
One cell right right arrow key or TAB
Top of the worksheet (cell A1) CTRL+HOME
End of the worksheet (last cell
containing data)
CTRL+END
End of the row CTRL+right arrow key
End of the column CTRL+down arrow key
Any cell Edit > Go To (Menu bar command)
Adding Worksheets, Rows, Columns, and Cells
· Worksheets: Add a worksheet to a workbook by selecting Insert > Worksheet
from the Menu bar.
· Row: To add a row to a worksheet, select Insert > Rows from the Menu bar, or
highlight the row by clicking on the row label, right-click with the mouse, and
choose Insert.
· Column: Add a column by selecting Insert > Columns from the Menu bar, or
highlight the column by clicking on the column label, right-click with the mouse,
and choose Insert.
· Cells: Add a cell by selecting the cells where you want to insert the new cells,
Click Insert > Cells > Click an option to shift the surrounding cells to the right or
down to make room for the new cells.
Resizing Rows and Columns
There are two ways to resize rows and columns: The first way is to resize a row by
dragging the line below the label of the row you would like to resize (up/down). Resize a
column in a similar manner by dragging the line to the right of the label corresponding to
the column you want to resize. To auto-fit text inside a cell simply double click on the
separator line (separating the two columns: the one you are typing in and the one to its
right).
Or
The second way is to click the row or column label and select Format > Row > Height
or Format > Column > Width from the Menu bar to enter a numerical value for the
height of the row or width of the column.
Selecting CellsAcademic Computing Center - AUB Excel 2003 - Tutorial I
http://staff.aub.edu.lb/~acc/
Wednesday, January 18, 2006 7 of 19
Before a cell can be modified or formatted, it must first be selected (highlighted). Refer
to the table below for selecting groups of cells.
Cells to select Mouse action
One cell click once in the cell
Entire row click the row label
Entire column click the column label
Entire worksheet
click the whole sheet button (upper left
corner of the labels “empty label”)
Cluster of cells
drag mouse over the cells or hold down the
SHIFT key while using the arrow keys
To activate the contents of a cell or to edit it, double-click on the cell.
Cutting Cells
To cut cells, highlight the cells the select Edit > Cut from the Menu bar or click the Cut
button on the Standard toolbar.
Copying Cells
To copy the cell contents first highlight the cell then select Edit > Copy from the Menu
bar or click the Copy button on the Standard toolbar.
Pasting Cut and Copied Cells
Highlight the cell into which you want to paste the content, and select Edit > Paste from
the Menu bar or click the Paste button on the Standard toolbar.
Drag and Drop
You can drag and drop content between cells. We recommend you use this method if the
cells are adjacent to each other. Highlight the cell you would like to move, simply drag
the highlighted border of the selected cell to the destination cell with the mouse. But be
aware that the Drag-and-Drop method cuts the contents the source cell and pastes it in
the destination cell.
Deleting Rows, Columns, and Cells
Rows: select the row by clicking its number, Click Edit > Delete
Columns: select the column by clicking its letter, Click Edit > Delete
Cells: select the cells you want to delete, Click Edit > DeleteAcademic Computing Center - AUB Excel 2003 - Tutorial I
http://staff.aub.edu.lb/~acc/
Wednesday, January 18, 2006 8 of 19
Freeze Panes
If you have a large worksheet with column and row headings, those headings will
disappear as the worksheet is scrolled. By using the Freeze Panes feature, the headings
can be visible at all times.
1. Click the label of the row that is below the row that you wish to keep frozen at the
top of the worksheet.
2. Select Window > Freeze Panes from the Menu bar.
Note: To remove the frozen panes, select Window > Unfreeze Panes
Freeze panes have been added to row 1 in the image above. Notice that the row
numbers skip from 1 to 6. As the worksheet is scrolled, row 1 will remain stationary
while the remaining rows will move.
Formatting Cells
Formatting Toolbar
The contents of a highlighted cell can be formatted in many ways. Font and cell attributes
can be added from shortcut buttons on the Formatting toolbar. If this toolbar is not
already visible on the screen, select View > Toolbars > Formatting from the Menu bar,
or right click on the toolbars area, and select the Formatting toolbar.
Percent style
Font size
Currency style
Comma style
Borders
Fill color
Font color
Increase/decrease
indent
Left Center Right
justification
Font menu
Merge and center
Increase/decrease
decimal
Bold Italics underlineAcademic Computing Center - AUB Excel 2003 - Tutorial I
http://staff.aub.edu.lb/~acc/
Wednesday, January 18, 2006 9 of 19
Format Cells Dialog Box
For a complete list of formatting options,
right-click on the highlighted cells and
choose Format Cells from the Shortcut
menu or select Format > Cells from the
Menu bar.
· Number tab - The data type can be selected from the categories listed on this tab.
Select General if the cell contains text and number, or another numerical
category if the cell is a number that will be included in functions or formulas.
· Alignment tab - These options allow you to change the position and alignment of
the data with the cell.
· Font tab - Font attributes are displayed in this tab including font name, size, style,
and effects.
· Border and Pattern tabs - These tabs allow you to add borders, shading, and
background colors to a cell.
· Protection tab – Allow you to protect or hide a certain cell in your worksheet.
Formatting Worksheet
1-Change horizontal alignment of data:
a. Select the cells containing the data you want to align.
b. Click one of the following:
Align Left
Center
Align Right
2- Change data color:
a. Highlight the cells containing the data you want to change to a different color
b. Click the Font Color button on the Formatting toolbar to choose the
color you want to use. To change the color, press on the arrow on the right side of the box
and then select the color you want by clicking on it.Academic Computing Center - AUB Excel 2003 - Tutorial I
http://staff.aub.edu.lb/~acc/
Wednesday, January 18, 2006 10 of 19
3- Change cell color:
There are two ways to change the cell color:
1. Select the cells, click on the Formatting toolbar.
2. Select the cells, click Format > Cells, the Format Cells dialog box appears.
Click the Pattern tab, and choose a color.
4- Indent data:
a. Select the data you want to indent in the cells.
b. Click the Increase/Decrease indent button on the Formatting toolbar
5- Change alignment of data:
Excel automatically aligns data at the bottom of the cell. To change the position of
data:
a. Select the cell
b. Click Format > Cells. Click the Alignment tab, under Vertical choose the way
to align the data, click OK to confirm.
Or
Perform the steps above a & b and find the box labeled Orientation. Double click in the
Degrees box and type the number you want your data to rotate by.
6- Add borders to cells
You can add borders to cells to enhance the appearance of your worksheet in two
ways:
a. Click on the arrow beside the Borders icon on the Formatting toolbar then you can
choose any border option from the obtained list.
b. OR from the Borders list obtained (as above) click Draw Borders (Click the line style
you want from the Border toolbar).
Dates and Times
If you enter the date "January 1, 2001" into a
cell on the worksheet, Excel will
automatically recognize the text as a date and
change the format to "1-Jan-01". To change
the date format, select the Number tab from
the Format Cells dialog box. Select Date
from the Category box and choose the
format for the date from the Type box. If the
field is a time, select Time from the
Category box and select the type in the right
box. Date and Time combinations are also
listed. Press OK when finished.Academic Computing Center - AUB Excel 2003 - Tutorial I
http://staff.aub.edu.lb/~acc/
Wednesday, January 18, 2006 11 of 19
Format Painter
A handy button on the Standard toolbar for formatting text is the Format Painter. If
you have formatted a cell with a certain font style, date format, border, and other
formatting options, and you want to format another cell or group of cells the same way,
place the cursor within the cell containing the formatting you want to copy, then click the
Format Painter button found on the Standard toolbar (notice that your mouse
pointer now has a paintbrush beside it). Highlight the cells which you want to re-format.
To copy the formatting to many groups of cells, double-click the Format Painter button.
The format painter remains active until you press the ESC key to turn it off.
AutoFormat
Excel has many preset table formatting options. You can add these styles by following
these steps:
1. Highlight the cells you want to
format.
2. Select Format > AutoFormat
from the Menu bar.
3. On the AutoFormat dialog box,
click to select the format you
want to apply to your
highlighted table. Use the scroll
bar to view all of the formats
available.
4. Click the Options... button. This
will open the Format to apply
section at the bottom of the
AutoFormat dialog box to
select the elements that the
formatting will apply to.
5. Click OK when finished.
Formulas and Functions
The unique feature of a spreadsheet program such as Excel is that it allows you to create
mathematical formulas and execute functions. Otherwise, it is not much more than a large
table for displaying text. This page will show you how to create these calculations.Academic Computing Center - AUB Excel 2003 - Tutorial I
http://staff.aub.edu.lb/~acc/
Wednesday, January 18, 2006 12 of 19
Formulas
Formulas are entered in the
worksheet cell and must begin
with an equal sign "=". The
formula then includes the
addresses of the cells whose values
will be manipulated with
appropriate operators placed in
between. After the formula is
typed into the cell, the calculation
executes immediately and the
formula itself is visible in the
Formula Bar. See the example to
the right to view the formula for
calculating the subtotal for a
number of textbooks. The formula
multiplies the quantity and price of
each textbook and adds the
subtotal for each book.
Linking Worksheets
When working with formulas, you may want to use a cell from a worksheet other than
your current worksheet. 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! cell-address".
The formula for this example would be "=A1+Sheet2! A2" where the value of cell A1 in
the current worksheet (since current worksheet means the active worksheet then there is
no need to specify the name of this sheet) is added to the value of cell A2 in the
worksheet named "Sheet2".
Relative, Absolute, and Mixed Referencing
Calling cells by just their column and row labels (such as "A1") is called relative
referencing. When a formula contains relative referencing and it is copied from one cell
to another, Excel does not create an exact copy of the formula. It will change cell
addresses relative to the row and column they are moved to. 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. To prevent this change, cells must be called by
absolute referencing and this is accomplished by placing dollar signs "$" within the cell
addresses in the formula. Continuing the previous example, the formula in cell C1 would
read "= ($A$1+$B$1)" if the value of cell C2 should be the sum of cells A1 and B1. Both
the column and row of both cells are absolute and will not change when copied. Mixed
referencing can also be used where only the row or column are fixed. For example, in
the formula "= (A$1+$B2)", the row of cell A1 is fixed and the column of cell B2 is fixed
Formula BarAcademic Computing Center - AUB Excel 2003 - Tutorial I
http://staff.aub.edu.lb/~acc/
Wednesday, January 18, 2006 13 of 19
($ appears before row number however it doesn’t appear before column name row is
fixed and column isn’t).
Basic Functions
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)". Several other
functions and examples are given in the table below.
Function Example Description
SUM =SUM(A1:A100) finds the sum of cells A1 through A100
AVERAGE =AVERAGE(B1:B10) finds the average of cells B1 through B10
MAX =MAX(C1:C100) returns the highest number from cells C1 through C100
MIN =MIN(D1:D100) returns the lowest number from cells D1 through D100
SQRT =SQRT(D10) finds the square root of the value in cell D10
TODAY =TODAY() returns the current date (leave the parentheses empty)
Function Wizard
You can view all functions available in Excel by using the Function Wizard.
1. Select the cell where the
function will be placed and
click the Insert Function
button next to the
Formula bar (or go to Insert >
Function).
2. From the Insert Function
dialog box, browse through the
functions and select a category
from the drop-down menu, and
select the function from the
Select a Function choices
below. As each function name
is highlighted a description and
example is provided below the
two boxes.
3. Click OK to select a function.Academic Computing Center - AUB Excel 2003 - Tutorial I
http://staff.aub.edu.lb/~acc/
Wednesday, January 18, 2006 14 of 19
4. The next window allows you to choose the cells that will be included in the
function. In the example below, cells A1, A2 and A3 were automatically selected
for the sum function by Excel. The cell values {1;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
B1, B2 and B3,
needed to be
added to the
function, those
cells would be
added in the
format “B1:B3”
to the Number
2 field.
5. Click Ok when all the cells for the function have been selected.
AutoSum
Use the AutoSum functions to add the contents of a cluster of adjacent cells.
1. Select the cell where you want
the sum to appear. This cell
should be outside the cluster of
cells that you will select. Cell C2
was used in this example.
2. Click the AutoSum button
(Greek letter sigma) on the
Standard toolbar.
3. By default, the group of cells
that will be summed will be
highlighted, in this example cells
A2 through B2.
Press the ENTER key on the keyboard
or click the green check mark button on
the Formula Bar.
Sorting and Filling
Basic SortsAcademic Computing Center - AUB Excel 2003 - Tutorial I
http://staff.aub.edu.lb/~acc/
Wednesday, January 18, 2006 15 of 19
In Excel you can execute a basic descending or ascending sort based on one column.
Highlight the cells that will be sorted (make sure you highlight the items with their
corresponding data so that information remains intact and no item loses its corresponding
data) and click the Sort Ascending (A-Z) button or Sort Descending (Z-A) button
found on the Standard toolbar.
Complex Sort
To sort by multiple columns, follow these
steps:
1. Highlight the cells, rows, or columns
that will be sorted.
2. Select Data > Sort from the Menu bar.
3. From the Sort dialog box, select the first
column for sorting from the Sort by dropdown menu and choose either Ascending
or Descending.
4. Select the second column and, if
necessary, the third sort column from the
drop-down menus labeled Then by.
Make sure before you sort that all the cells contain text or numbers, not formulas,
otherwise sorting might not function properly.
If the cells you highlighted include text
headings in the first row, select the
option Header row under the title My
data range has. Click the Options…
button for special non-alphabetic or
numeric sorts such as months of the year
and days of the week.
Click OK to execute the sort.
Auto-fill
The Auto-fill feature allows you to quickly fill cells with repetitive or sequential data
such as chronological dates or numbers, and repeated text.