Excel for Beginners

Jessamyn West
jessamyn@gmail.com
508-415-9074

A Few Things to Know

† There is drop-in time here on most Wednesdays from 3-5:30 for people who would
like some extra time to work on class materials or would like to ask more questions
than we have time for. See the handout for more information.
† The classroom computers are not very customizable. There may be ways to
configure Excel that are easier to use than the way they are set up here. If you have
a computer at home, your version of Excel may behave slightly differently. You can
bring your own laptop, but you will not be able to connect to the internet and your
version may be somewhat different.
† We will be saving class work to the hard drive so that we will have it for the next
class. I also have disks. If you want a disk to take home, let me know and I can give
you one. You can also bring a USB drive to class to take work home.

Class Outline [topics may change slightly based on student skill levels]

¾ Wednesday October 8th: introductions, basic vocabulary, "what is a spreadsheet?",

working with files, working with a spreadsheet
¾ Wednesday October 15th: formatting a spreadsheet, menus and toolbars, making
your own spreadsheet
¾ Wednesday October 22nd: NO CLASS
¾ Wednesday October 29th: functions, how they work, how to tame them.
¾ Wednesday November 5th: graphs! printing! wizards! images! (substitute teacher)
¾ Wednesday November 12th: TBD special topics "quiz"

Keep a few things in mind

1. I will probably touch on more topics than anyone is expected to remember at one
time. I'll repeat ideas often and if I don't, please remind me.
2. Excel does many many things. We will be covering a small number of those things so
much of what we are learning is where to find the things we do want to do, among all
the other things we don't (yet!) want to do.

Some Computer Vocabulary You Should Know

click, double-click, font, menu, scrollbar, toolbar, save as, select

Some Excel Vocabulary

columns (indicated by letters)

rows (indicated by numbers)
cells (indicated by coordinates)
data: labels, numbers, functions
 Excel for Beginners II

Some Review

CTRL-1 (format cells) CTRL-Z (undo)

CTRL-A (select all) F1 (help)
CTRL-S (save) NUM LOCK (turn on keypad)

"Format Cells" lets us change: color, font size, alignment, border, and number type

You can move the cells to fit your data, or tell Excel to automatically make the cells the
right width and height.

VTC in Randolph Center is a GREAT place to get books on technical/computer topics.

Working with Functions

Functions ALWAYS start with an equals sign. Excel knows basic math symbols.

+ – * / % ^ = < > <>

Four function parts

1. name - how do we find the function?

2. purpose - what is the function supposed to do?
3. requirements - what does the function need in order to work?
4. outcome - what is the result of the function?

Some basic functions

= AVERAGE
= SUM(B6:B8,B10:B11) [similar to =B6+B7+B8+B10+B11 but simpler]
= COUNT
= COUNTA

Functions in Action

(checkbook example)
 Excel for Beginners III

Some Review

Naming cells for functions OR charts

B1:B12 cell b1 through cell b12

B1:B12, B15 cell b1 through cell b12 plus cell b15
B1:C2 indicates cells within this range, this would be four cells total

- You can select a range of cells by using click and shift-click

- You can select a range of cells that are not adjacent by using click and CTRL-click

A few more things

SUM vs COUNT sum adds numbers in cells

count how many cells in a given range have numbers in them
counta how many cells in a given range have numbers OR words
in them
[note: a zero in a cell is not the same as an empty cell]

- conditional formatting - Excel can format cells based on what is in them

- split vs. freeze keep some data still while you work with other data

Charts & Graphs - the quick and easy way

1. Select data to be charted/graphed

2. Go to the Insert Menu and select Chart OR click this button OR press F11
3. choose the type of chart that best fits your data. Excel WILL let you make a bad
chart, so be careful.
4. You can format using the wizard OR select parts of the chart and use CTRL-1

Printing

- You have only a few printing options.

- Options: print selection, print entire workbook, print active sheets
- Print Preview is your friend.
- Remember to pick Portrait or Landscape in the File menu under Page Setup
 Excel for Beginners IV
SOME REVIEW

Charts

- remember to select the headings and data but not the totals when you make a chart. If you
just get series1, series2, series3 in your legend you may have forgotten to select the
headings.
- you can use the chart button or just go to the Insert menu and select Chart
- a chart can go on its own page or on the same page as your worksheet
- You can right click on a chart and format Chart Type, Source Data, Chart Options and
Location. Make sure you know what is behind them.

Worksheets

- remember how to select adjacent cells: click & drag or click & shift-click.
- shift and CTRL-click for selecting non-adjacent cells
- remember conditional formatting? remember how to print?

FUNCTIONS

Sort of a meta-function, Autofilter. Click in your heading row and go to Data>Filter>AutoFilter

Three ways to get to functions

1) the function wizard button
2) the Insert menu & select Function
3) SHIFT + F3

FOR MORE INFORMATION

http://www.meadinkent.co.uk/excel.htm
good simple list of Excel functions and descriptions

http://www.contextures.com/functions.html
big downloadable list of Excel functions and features

http://office.microsoft.com/en-us/templates/
this is where you get some templates [search Google for "excel templates" for more]

http://lacher.com/toc.htm
some advanced Excel examples [search Google for "excel tutorial" for more]

http://www.ozgrid.com/Excel/AutoFilters.htm
more on autofilters

don't forget drop-in time Tuesdays 2:30-5 [except Oct 25 & 27]
 Excel for Beginners V
SOME REVIEW OF SOME NEAT THINGS WE KNOW

Filtering/Sorting
Autofilter. Click in your heading row and go to Data > Filter > AutoFilter
Conditional Filtering. Format > Conditional Formatting
Sorting. Make sure you select your data properly and go to Data > Sort
Hiding. You can use the mouse to click and drag or select using *****

Putting things in
Insert Graphics. Insert > Picture > Clip Art (or From File)
Insert Row/Column. Select the space above/before where you want to put the space in.
Insert Chart. Select the data including header rows. You can include or leave out totals.

Functions
SUM/COUNT/COUNTA
AVERAGE
+ – * / % ^ = < > <>
Remember you need to know the NAME of a function, its PURPOSE, its REQUIREMENTS
and the OUTCOME you are looking for.

Printing
You can select an area or print the whole sheet
Keep in mind: portrait vs landscape? hide some data?

Features
Undo! Formatting! Charts! Blank space! Center across selection! Multiple worksheets!
Colored tabs! Hyperlinks! Copy and paste!

Keys to Remember
F1 for help, CTRL-Z for undo, CTRL-1 for format cells, CTRL-S for save

don't forget drop-in time Tuesdays 2:30-5 [except Oct 25 & 27]
 In-Class Assignment
1. open sevendwarfinc.xls and do a "save as" to save it as your own
worksheet
2. color and name the worksheet tabs

first worksheet

1. make a function that gives the average age of the dwarves and put the
average somewhere that is NOT the age column
2. sort the first worksheet by age, then hide the age column
3. make a chart in the first worksheet that shows dwarves and their heights
4. set a conditional format to make a block around all dwarves
5. make a copy of the worksheet and create a new worksheet out of it.
change the dwarf names to regular people names.

second worksheet

1. make totals and subtotals

2. change the font face, font size and font color
3. center the header rows and make the text a light color on a dark
background
4. make a heading across the top of the worksheet saying "productivity
report for dwarf llc" or some title of your own choosing
5. make a chart of the data and put it on worksheet three
6. rename this new worksheet: "chart"

both worksheets

1. add a clip art picture or two

2. format and style
3. print your final version of worksheet one

don't forget drop-in time Tuesdays 2:30-5 [except Oct 25 & 27]