Download Report

Excel Basics Handout
Regional Library Computer Center
Pg. 1
Introduction to Excel XP
What is the Excel Spreadsheet?
The Excel spreadsheet is a table of values arranged in rows and columns. These values can take
many forms such as text, dates and times, and numbers (including currency and percentages).
Each value is stored in a cell. You can define what type of data is in each cell and how different
cells depend on each other. If you change the value in a cell, the contents of any cells that depend
on that value will change automatically.
Excel can create and manipulate spreadsheets (which are called worksheets). It can also produce
graphs (known as charts) from your data and can link one worksheet to another.
At the top of the Excel workspace is the title bar displaying Microsoft Excel followed by the
name of the current workbook (Book plus a numeral if the document has not been saved). Below
that is the menu bar and toolbars. Then, just above the row of column headings, is the Name box
containing the address of the active cell (A1 at the moment) and the Formula Bar displaying the
contents of the active cell (blank at the moment).
Components of the Excel Window
Besides the usual window components (close box, title bar, scroll bars, etc.), an Excel window
has several unique elements identified in the figure below.
Excel Basics Handout
Regional Library Computer Center
Pg. 2
Standard Toolbar
The Standard toolbar, located beneath the menu bar, has buttons for commonly performed tasks
like adding a column of numbers, printing, sorting, and other operations. Excel let's you
customize the toolbar or even display multiple toolbars at the same time. The Standard Excel XP
toolbar appears in the figure below.
Formatting toolbar
The Formatting toolbar, located beneath the Standard toolbar bar, has buttons for various
formatting operations like changing text size or style, formatting numbers and placing borders
around cells.
Formula bar
The formula bar is located beneath the toolbar at the top of the Excel worksheet. Use the formula
bar to enter and edit worksheet data. The contents of the active cell always appear in the formula
bar. When you click the mouse in the formula bar, an X and a check mark appear. You can click
the check icon to confirm and completes editing, or the X to abandon editing.
Name box
The Name box displays the reference of the selected cells.
Excel Basics Handout
Regional Library Computer Center
Pg. 3
Worksheet tabs
An Excel workbook consists of multiple worksheets. Use the worksheet tabs at the bottom of the
screen to navigate between worksheets within a workbook.
Task panes
The task pane (also referred to as the Office Pane), is a new feature in this version of Excel. On
the View menu, there is now a Task Pane option. When this is clicked, it toggles between
showing and hiding the task pane.
Task Pane
Let's get started digging into what makes a spreadsheet work. Spreadsheets are made up of:
! columns
! rows
! cells
What is a Column?
In a spreadsheet the COLUMN is defined as the vertical space that is going up and down the
window. Alphabets (letters) are used to designate each COLUMN'S location.
Excel Basics Handout
Regional Library Computer Center
Pg. 4
In the above diagram COLUMN “C” is highlighted.
What is a Row?
In a spreadsheet the ROW is defined as the horizontal space that is going across the window.
Numbers are used to designate each ROW'S location.
In the above diagram the ROW labeled 4 is highlighted.
What is a cell?
In a spreadsheet the CELL is defined as the space where a specified row and column intersect.
Each CELL is assigned a name according to its COLUMN letter and ROW number.
When referencing a cell, you should put the column first and the row second.
Active cell
The active cell has a dark border around it to indicate your position in the worksheet. All text and
numbers that you type are inserted into the active cell. One way to make a cell active is by
clicking on the cell.
Excel Basics Handout
Regional Library Computer Center
Pg. 5
In the above diagram the CELL labeled B6 is highlighted.
Navigating through Excel
CTRL + END
move to the intersection of the last row and column that contains
data.
CTRL + HOME
move to first cell in the worksheet
CTRL + →
moves to the last column (IV)
CTRL + ←
moves back to the first column (A)
CTRL + ↓
moves to the last row (A 65,536)
CTRL + ↑
moves back to the first row
*TAB
moves from cell to cell horizontally
*ENTER
moves from cell to cell vertically
PAGE DN
moves down one screen
PAGE UP
moves up one screen
ALT + PAGE DN
moves one screen to the right
ALT + PAGE UP
moves one screen
* - Most often used commands for moving from cell to cell
You can also enter the cell address in the name box.
Excel Data Types
In a spreadsheet there are three basic types of data that can be entered.
! labels - (text or numbers with no numerical value)
! constants - (just a number -- constant value)
! formulas* - (a mathematical equation used to calculate)
!
Excel Basics Handout
Regional Library Computer Center
data types
LABEL
CONSTANT
FORMULA
Pg. 6
examples
Name or Wage or Days
5 or 3.75 or -7.4
=5+3 or = 8*5+3
descriptions
anything that is just text
any number
math equation
Labels in Excel
Labels are text entries. They do not have a value associated with them. We typically use labels to
identify what we are talking about.
In this example, the labels are:
• computer ledger
• car loan
• interest
• # of payments
• Monthly Pmt.
The labels are NOT for the computer but rather for US so we can clarify what we are doing.
Constants in Excel
Constants are entries that have a specific fixed value. If someone asks you how old you are, you
would answer with a specific answer. Sure, other people will have different answers, but it is a
fixed value for each person.
In our this example the constants are:
•$12,000
•9.6%
•60
As you can see from these examples there may be different types of numbers. Sometimes
constants are referring to dollars, sometimes referring to percentages, and other times referring to
a number of items (in this case 60 months).These are typed into the computer with just the
numbers and are changed to display their type of number by formatting. Again, we use constants
to enter FIXED number data.
But, how do you input numbers such as account or invoice numbers and have Excel treat these
numbers as text or labels? You must enter the single quote symbol (‘) to the left of all the
numerical characters. Otherwise, Excel will handle this number as a constant.
Excel Basics Handout
Regional Library Computer Center
Pg. 7
Column Width
A question that everyone (who has ever worked on a spreadsheet) has asked at one time or
another is, "Where did all my numbers go?" or same question, "Where did all of those #######
come from and why are they in my spreadsheet?" The problem is the number trying to be
displayed in a particular cell does not have enough width to display properly. To clear up the
problem make the column wider. You can do this many ways.
Here are three ways to change the column width
1.
Select the column (or columns) with the problem by clicking on their labels (letters).
Then you choose the FORMAT from the menu. Go down to COLUMN and over to
AUTOFIT SELECTION. This will widen the column so that all of the information within
the cells in this column are visible.
2.
Move the arrow to the right side of the column label and click and drag the mouse to the
right (to make wider) or left (to make smaller). Let up on the mouse button when the
column is wide enough.
Notice the cursor changes to a vertical line with arrows pointing left and right.
3.
Move the arrow to the right side of the column label and double-click the mouse to
extend the column width.
In many spreadsheets you can also change the vertical height of a row by moving the lower edge
of the row title (number).
Inserting Columns and Rows
Sometimes we (all) make mistakes or things change. If you have a spreadsheet designed and you
forgot to include some important information, you can insert a column into an existing
spreadsheet. What you must do is click on the column label (letter) and choose Columns from
the Insert menu. This will insert a column immediately left of the selected column.
Excel Basics Handout
Regional Library Computer Center
Pg. 8
As you can see from this example there was a blank column inserted into the spreadsheet.
(Note: And we can also insert rows. With the row label (number) selected you must choose the
Row from the Insert menu.)
AutoSum button
In Excel, the standard toolbar has a button that simplifies adding a column or row of numbers. The
AutoSum button, which resembles the Greek letter Sigma (shown at right), automatically creates a
SUM( ) function. When you click the AutoSum button Excel creates a sum function for the column
of numbers directly above or the row of numbers to the left. Excel pastes the SUM( ) function and
the range to sum into the formula bar. If the range is not correct, simply select the proper range with
your mouse on the worksheet. When you have the correct range entered, press the <Enter> key to
complete the function.
Formulas and Functions
Spreadsheets have many Math functions built into them. Of the most basic operations are the
standard multiply, divide, add and subtract. The operation symbols are:
Operation
Add
Subtract
Multiplication
Divide
Symbol
+
*
/
Basic Formulas in Excel
Formulas are entries that have an equation that calculates the value to display. Formulas and
functions that perform calculations are the true power of spreadsheets.
We DO NOT type in the numbers we are looking for; we type in the equation. This equation will
be updated upon the change or entry of any data that is referenced in the equation.
When we are entering formulas into a spreadsheet we want to make as many references as
possible to existing data. If we can reference that information we don't have to type it in again.
Excel Basics Handout
Regional Library Computer Center
Pg. 9
AND more importantly if that OTHER information changes, we DO-NOT have to change the
equations.
Formulas
To build a formula, first select the cell in which you want the results to appear. In Excel, all
formulas start with the = sign. After the = sign, type the cells you want to add or subtract along
with the mathematical operation you wish to perform (Do NOT enter spaces). For example, to
add the January sales in the worksheet below, the formula would be =B3+B4+B5. If you want to
subtract February Widget Sales from January Widget sales, the formula would be =B3-C3.
Please note: always reference the cell(s) and not the information within the cell(s).
Copying Formulas
The easiest way to copy a formula is with the Fill Handle in the lower right corner of the cell.
Create your initial formula and then position the mouse on the Fill Handle. When the mouse
changes shape to a cross-hair, click and drag over the adjacent cells to which you want to copy
the formula.
Fill handle
The lower right corner of the active cell has a small box called a Fill Handle. Your mouse
changes to a cross-hair when you are on the Fill Handle. The Fill Handle helps you copy data
and create series of information. For example, if you type January in the active cell and then drag
the Fill Handle over four cells, Excel automatically inserts February, March, April and May.
Remember: It is BEST if we can Reference as much data as possible as opposed to typing data
into equations.
Charts and Graphs
Numbers can usually be represented quicker and to a larger audience in a picture format. Excel
has a chart program built into its main program. The Chart Wizard will step you through
questions that will (basically) draw the chart from the data that you have selected. There are
many types of charts. The two most widely used are the bar chart and the pie chart.
Excel Basics Handout
Regional Library Computer Center
Pg. 10
Let’s say that you wanted to chart (show on a chart) the items sold in January (in percentages)
from the spreadsheet below:
Sample Pie Chart
Select the range of cells containing the data (B3 to B5). Click the ChartWizard button on the
standard toolbar.
Step 1 (Type): Select chart type (click Pie) and then select chart sub-type (click 3-D Pie). Click
Next>.
Step 2 (Source Data): The cell range of the data should be showing in the Data range box and
the arrangement of the data series is in Columns. Click on the Series tab. On the far right of
“Category Labels”, click on the Collapse Dialog Button
. This collapses the dialog box so
that the source spreadsheet can be seen. From the spreadsheet, select the “Widgets”, “Figets”
Click Next>.
and “Digets”. Click the Expand Dialog Button
Step 3 (Options):
1. Select the Titles tab. Enter the chart title (January Sales).
2. Select the Legend tab. Place the Legend in the desired position
3. Select the Data Labels tab. Click Show percent. Note: In Excel XP, you can show the label,
value, and percent for each piece of the pie. Click Next>.
Step 4 (Location): If you want the graph to appear in a separate chart sheet, click As new sheet.
If you want the graph to appear in the worksheet with your summary table, click As an object in
and select the worksheet containing your summary table. Click Finish.
Here is how your pie chart should appear:
January Sales
20%
44%
36%
Widgets
Figets
Digets