Step 1: Open
Microsoft Excel
There are a number of ways to open Microsoft Excel:
1. Using
the mouse, click the Start
button.
2. Click
All Programs.
3. Click
Microsoft Office (name of office
suite)
4. Click
Microsoft Office Excel 2010.
OR
On the desktop
(main screen), click on the icon (or symbol) for Microsoft Excel.
Once you have opened Excel, the screen should look like
this:
Step 2: Knowing How to Use an Excel Worksheet/Definitions
Cell-
a box where you enter any spreadsheet data.
Cell
address- the cell location determined by the letter and number of
the intersecting column and row.
Example: A1.
Column-
a vertical line of cells with a
distinct letter identifying it.
Examples: A, B, C.
Row-
a horizontal line of cells with a distinct
number identifying it. Examples: 1, 2,
3.
Formula-
a set of numbers and values that performs a calculation. In Excel, formulas begin with an equal sign
(=) and continue with the formula text.
Example: =B1+B2 (The number in cell B1 adds the number in cell B2)
Range-
a group of 2 or more cells used in making charts.
Spreadsheet- a
computer program used for manipulating numbers and statistics in rows and
programs (Excel).
Workbook-
a spreadsheet file that consists of one or more worksheets.
Worksheet-
the area where data and formulas are entered.
Worksheet
tab- displays the worksheet name at the bottom of the screen (Sheet
1, Sheet 2, Sheet 3) which the user can navigate between the worksheets.
Excel
Window Controls- several commands to minimize,
maximize, restore, and close Excel.
These features are useful when working with several worksheets.
Step 3: Creating Charts and Graphs in Microsoft Excel
EXERCISE:
Create a graph using the following information:
1A
|
7G
|
|
Jan
|
1
|
6
|
Feb
|
0
|
5
|
Mar
|
3
|
5
|
Apr
|
0
|
0
|
May
|
2
|
7
|
Jun
|
2
|
9
|
Jul
|
3
|
12
|
Aug
|
2
|
13
|
Sep
|
5
|
15
|
Oct
|
3
|
15
|
Nov
|
1
|
14
|
Dec
|
0
|
14
|
1. Type the above information in Excel, starting with Jan in
cell A2. Type the rest of the
months (Feb, Mar) in the A column. Type 1A in cell B1 and 7G in cell C1. Type the data in the corresponding B and C columns. The table should look like
the following:
2. Select the cell range
with the mouse. You can also hold down
the Control and A keys simultaneously to select the range. (This is a shortcut for
the “Select All” command). The entire range
is highlighted.
3. Click the Insert tab
on the ribbon.
4. Select a chart type.
In this case, we are going to use the Column option. Excel lists
several configurations for the Column option.
5. Select the first configuration under 3-D Column.
6. The chart should look like this:
Feel free to experiment with other configurations, but
return to the Column option and 3-D Column.
7. The next step is to add a chart title. Click the chart with the mouse.
8. Click the Layout tab
on the ribbon.
9. Click Chart Title.
10. Click Above Chart
and type the title Work Accidents at Springfield Nuclear Plant by Sector,
2010. The chart should look like this:
11. To add axis titles, click Axis Titles.
12. Click Primary
Horizontal Axis Title.
13. Click Title Below
Axis and type the title Month.
14. Click Primary
Vertical Axis Title.
15. Click Rotated
Title and type the title Accidents.
16. To add a legend
(or key) identifying the colors of each data series (1A, 7G) click the chart
and click the Layout tab. Click Legend
and the position you want to use for the chart legend.
The final chart should look like the following:
KEY
POINT: When you type data anywhere in the range (for example,
change 14 to 15 in cell C13) the
graph data changes as well.
Formulas
You can manipulate data in Excel using formulas, or equations.
Remember that formulas begin with an equal sign (=). In Excel, most formulas have a
built-in function like SUM or AVERAGE.
The following mathematical operators are used in formulas:
+ (plus sign) for addition
- (minus sign or hyphen) for
subtraction
* (asterisk) for multiplication
/ (slash) for division
^ (caret) for raising a number to an
exponential power
EXAMPLE: Type TOTAL in cell
A14. Click on cell B14 and type the following in the Formula (fx) bar:
=(B2+B3+B4+B5+B6+B7+B8+B9+B10+B11+B12+B13)
Click on the chart when done.
·
The simplest way would be to use the
formula =SUM(B2:B13). Click on cell
B14 and type =SUM(B2:B13) in the
fx bar.
·
Go to cell C14 and repeat the same steps, using the formula =SUM(C2:C13).
KEY
POINT: Microsoft Excel has the AutoSum
feature, which allows you to add numbers without entering data. Click a cell where you want the sum to appear
and click the Sum button, or Greek
sigma symbol () on the ribbon. If you want to use a
function besides SUM, click the Sum and click on the operation you want to
use.
EXAMPLE: Click on cell C14 (total accidents for Sector 7G).
OPTIONAL: Adding data labels to a chart (makes a chart
easier to read)
1. Click
the chart “Work Accidents at Springfield Nuclear Plant by Sector, 2010”
2. Click
the Layout tab.
3. Click
Data Labels and select the position
you want to use for the data labels.
OPTIONAL: Applying a conditional
format to a range (a conditional format applies to only certain cells that
meets certain criteria)
1. Using
the mouse, select the range.
2. Click
the Home tab.
3. Click
Conditional Formatting.
4. Click
Highlight Cell Rules. Click the operator (Greater Than…, Less
Than…) you want to use and type the value you want to use.
5. Click
the formatting you want to use.
6. Click
OK.
Now we are going to work in a new worksheet. On the worksheet
tab, click Sheet 2.
Create a chart/graph based on the previous exercise.
Step 4: Filtering and Sorting Data
Click on Worksheet 2.
EXERCISE:
Type the following information, starting in Cell A1:
Customer ID
|
Customer Name
|
City
|
State
|
Amount Spent
|
15642
|
Gumble, Barney
|
Springfield
|
IL
|
$25.28
|
21123
|
Szlac, Moe
|
Springfield
|
IL
|
$67.18
|
29077
|
Eisenreich, John
|
Philadelphia
|
PA
|
$107.98
|
33211
|
Carlson, Carl
|
Springfield
|
IL
|
$30.92
|
43766
|
Richardson, Larry
|
Portsmouth
|
NH
|
$200.12
|
43766
|
Webster, David
|
Flagstaff
|
AZ
|
$66.20
|
56777
|
Hunter, Diane
|
Los Angeles
|
CA
|
$182.50
|
77992
|
Flanders, Ned
|
Springfield
|
IL
|
$12.00
|
88556
|
Henderson, Heather
|
Denver
|
CO
|
$148.00
|
99996
|
Gomez, Jennifer
|
Trenton
|
NJ
|
$244.11
|
·
To sort
data, highlight the A column (Customer ID). Click on the Data tab. Click either the Sort Ascending or Sort Descending button. If
we select Sort Descending, Excel
will start with the bottom ID (99996).
·
We
will use the Auto Sum feature
here. Click on cell E12. Click the button and the drop-down button to AVERAGE.
(We are taking the average amount of money spent).
·
To filter
data, highlight the B column (Customer Name).
Click on the Data tab and
click the Filter button on the ribbon.
A drop-down button appears in cell
B1 (Customer Name):
·
Click on the drop-down button. Unselect the Select All option. Select
“Carlson, Carl” and “Flanders, Ned.” Notice how only the data affiliated with these
names is displayed.
Other Excel Essentials
1. Resizing columns:
Often the text or number does not fit in a cell. Position the mouse on the border between the columns. The cursor becomes a black cross. Drag the cursor to the right. To change the width of all columns, hold down the Control
and A keys and click the Home tab. Click Format
and click Column Width. Type the width in the Column width text box
and click OK.
2. Changing the row height:
Select a range including at least one cell in every row you want to resize (1,
2, 3). Click the Home tab and click Format. Click Row
Height. A Row Height dialog box
appears and type the desired height.
Click OK.
3. Applying a style to a cell:
Select a cell or range to format. Click
the Home tab and click Cell Styles. Click the style you want.
4. Inserting pictures and graphics: You
can insert images into a worksheet, including clip art (artwork that comes with Excel) and photos. To insert clip art, click the Insert tab and click Clip Art. Choose an image in the Clip Art box and click
Go.
To insert photos, click the Insert
tab and click Picture. Open the folder that contains the photo and
click the photo. Click Insert.
5. Finding and replacing text: Click
the Home tab and click the Find & Select option. If you want to find text, click Find. The Find and Replace box appears and type the
text in the Find what text box. To find more recurrences, click Find Next. To replace
text, click Replace. In the Find What text box, type the text
you want to find and the text you want to replace in the Replace with text
box. Click Close when you are done with either option.
6. Printing
an Excel workbook: Click on the File
tab and click Print. (If you
want to print a single worksheet, click the tab on the Worksheet tab). Once you
have made all your settings, click Print.
7. Deleting data:
If you want to delete a range, select
the range that you are going to delete.
Click the Home tab and click Delete.
Click Delete Cells. Pick your option (often _Shift cells up_) and
click OK.
If you want to erase data from a single cell, select the cell with the
mouse and click the Home tab. Click Clear
and Click Contents.
8. Getting help in Microsoft Excel:
Click the icon at the top right screen (next to the icon). Type
a word or phrase related to your question- “change column width.” Press the ENTER key- Excel displays a list of
topics most closely related to your question.
Click on the desired topic. To
exit the Help window click X in the top right corner.