Excel for Outstanding Job-Seekers

Prepared for The Ferndale Career Center

By Kevin McLogankmclogan@wowway.com

bigkkm@twitter.comfacebook.com/kmclogan

linkedin.com/in/kevinmcloganhttp://tinyurl.com/y9zmw6h

Course Objectives:After you complete this course to will be able to:After you complete this course to will be able to:

• Create spreadsheets that amaze your friends and strike fear into the hearts of your enemies

• Analyze data to solve problems• Manage and make sense of large amounts of data• Perform and understand calculations and determine

which functions are appropriate for the task at hand.• Format the contents of a worksheet• Create a basic Chart• Customize your individual Excel experience to make

you even more productive and awesome than you are now!

For this class, I am making the following assumptions, and it is

critical that you are honest • That you can copy and paste data • That you can open Excel by using the Start Menu or

by opening files • That you know how to identify the active cell• That you know how to move from sheet to sheet • That you can fill rows and columns of cells• That you have a basic familiarity with Excel 2003 and

that this is the version you are using• That you understand and use common Windows

elements

In order for us to make this an outstanding experience

• Ask questions if you are not clear on a concept

• If you have a problem with a file, let us know so that you can reload it.

• I will talk really fast if you let me-slow me down if I get ahead of you.

• If you need help, please let us know-if you can help, please do.

• This is a hands-on lesson, so you will learn by doing-you might want to take some notes.

Customize your Toolbar

This is the Standard toolbar-but you’re not standard, so why should your toolbar be?

This is what my toolbar looks like-and so can yours!

This is a way to customize your

toolbar with every command

on the menu plus a few

others.

By putting the commands you use most often on your toolbar you will be more

productiveRight click the toolbar at any place, and you will get this dialogue box.

How many sheets do you need?

Relative references   

• Relative references    A relative cell reference in a formula, such as A1, is based on the relative position of the cell that contains the formula and the cell the reference refers to. If the position of the cell that contains the formula changes, the reference is changed. If you copy the formula across rows or down columns, the reference automatically adjusts. By default, new formulas use relative references. For example, if you copy a relative reference in cell B2 to cell B3, it automatically adjusts from =A1 to =A2.

Absolute references

• Absolute references    An absolute cell reference in a formula, such as $A$1, always refers to a cell in a specific location. If the position of the cell that contains the formula changes, the absolute reference remains the same. If you copy the formula across rows or down columns, the absolute reference does not adjust. By default, new formulas use relative references, and you need to switch them to absolute references. For example, if you copy a absolute reference in cell B2 to cell B3, it stays the same in both cells =$A$1.

Insert a function

How to make functions work for you

• Use function box or create your own using operators (+, -, /, *)

• Start with “=” sign

• Edit formula in cell or in the formula bar

• Copy a formula-if it refers to another cell, then it will move its reference point (we noticed that in our last example)

Let's create some of our own formulas!

Popular functions• SUM• AVERAGE• IF• COUNT• COUNTNUMS• MAX &MIN• CONCATENATE AND MID• SUMIF• VLOOKUP

• RANK

• COUNTIF &COUNTA

Open Open SumSum file file

=SUM FUNCTIONTHE FOLLOWING FUNCTIONS WORK EXACTLY LIKE THE SUM FUNCTION:

•AVERAGE •COUNT

•MAX•MIN

•COUNTA

=SUMIF

Text Functions

• =MID: takes part of a text string apart• =CONCATENATE: puts several strings of

text into one text string

PROPER

• This is pretty cool! (But it doesn’t work on me)

CONCATENATE

• Combines text strings from multiple cells.

TEXT to COLUMNSWhen you

have multiple items in the

cells of a column and you need to

separate them out.

TEXT to COLUMNSDELIMITED allows you to choose which

mark will cause the separation

TEXT to COLUMNSFIXED WIDTH allows you to choose a point

at which the data will be separated.

=IF• This can be the most complicated of all

functions!

• It is definitely the most abused!

• Think about it as “IF condition A occurs, this is the result, IF not, then this is the result.”

• There can be multiple conditions, but that’s where we can run into trouble.

MORE IF

Formula Lesson

Open the worksheet titled “formula lesson”• What is the total sales number for January?• How much did each sales Associate sell in January?• How do I know that I added up all of the sales

figures?• What day of the month had the most sales?• The least sales?• In order to make it worthwhile for me to keep a sales

person working, they need to generate $6500 in sales per quarter-these sales are evaluated at the end of the quarter. Keep this in mind as we move forward.

• Work through each month of the first quarter and then we will total it up in the last sheet.

LOOKUP FUNCTIONS=VLOOKUP and =HLOOKUP are very useful They look up the value in the first column of the table and returns the corresponding value in the table column (VLOOKUP) or row (HLOOKUP)

SUBTOTAL FUNCTIONReturns a subtotal in a list or database. It is

generally easier to create a list with subtotals using the Subtotals command (Data menu). Once the subtotal list is created, you can modify it by editing the SUBTOTAL function.

1 AVERAGE 6 PRODUCT2 COUNT 7 STDEV3 COUNTA 8 STDEVP4 MAX 9 SUM5 MIN 10 VAR

11 VARP

Sum it up, and more• Quick: What's the sum

of the selected numbers in the picture? Even if you're very fast at doing math in your head, Excel can probably get the answer before you do.

What's the total of the selected numbers?

And the total is

• All you have to do is...wait, the total is already in the status bar at the bottom of the window: Sum=$235.35.

• As you select the numbers, Excel automatically adds them up and displays the total in the status bar.

Sorting

• The standard configuration on the toolbar is unsuitable!

• Change it or use the menu item:• Data=>Sort

Open Big Sort file for the exercise.Open Big Sort file for the exercise.

Sort Dialogue box

Filters

• Another Concept is that of filters

• There are two kinds of filters we will use today• Auto Filter: creates a drop-down menu with

choices• Advanced Filter: Helps to identify unique

items in a long list.

Open AutoFilter Names file and we will work on some examples

Naming ranges

Why is this important:• Names make your formulas more

understandable and easier to use, especially for others.

• A descriptive name is easier to remember than a range of cells

• Makes it easier to move around in the spreadsheet.

• Easy to verify-the names appear in the name box.

Define Name Box

Checking your work

• Tracing errors: when you get a message like:• ####, #DIV/0!, #REF!, #NULL!1, #VALUE!,

#NAME?, #NUM!, #N/A

Something went wrong!

Open the Fix and trace errors file

•##### column is too narrow to display the value, or you might have used a negative number for a date or time.

•#DIV/0! You attempted to divide by zero. What were you thinking?

•#NAME? Excel does not recognize the text in the cell. A name or function have been misspelled or used a name that does not exist. Text in not marked properly.

•#NUM! The formula contains an invalid numeric value or a number that is too large or too small for Excel to handle.

•#REF! The formula uses an invalid cell reference.

•#VALUE! The wrong type of argument is used in the formula.

Tracing Precedents and dependants

Drop-down menus in cells•You can limit the options for data entry with a drop-down menu in each cell.

•This keeps the entries consistent and limits errors due to spelling, or other inconsistencies.

•Let’s try it!

Formatting

If your spreadsheet looks good, so do If your spreadsheet looks good, so do you!you!

Number Formatting

Open the Format Numbers File

Alignment of the cells

Font adjustments

Borders

Patterns

Auto Format

Conditional Formatting

Open Conditional Formatting file.

If a cell’s value results in a certain condition, the format can be changed according to your

preference.

Charting for better effect

Open the Chart Examples file

• Charts and graphs are important because they bring visualization to data.

• This is an important key to gaining understanding from your message.

OTHER STUFF THAT ISN’T AS OTHER STUFF THAT ISN’T AS EASY TO CATEGORIZEEASY TO CATEGORIZE

• Tabs at the bottom. • Name one “History”-I dare ya!Name one “History”-I dare ya!

• Freezing panes

• Looking at more than one workbook at a time

• Links to other workbooks

Inserting fancy stuff into your spreadsheet

• Hyperlinks

Open the Open the more resourcesmore resources file to see how this is inserted. file to see how this is inserted.

More stuff to add

• Pictures

• Clip Art

• Diagrams

• Organizational charts

Stuff that is a little quirky in Excel• Printing: it takes some

adjustments:•page breaks, page break views•Always start from scratch in Page Setup

Copy the entire sheet by clicking the corner piece on the top left. This way you can get all the formats and row heights and column widths.

Be careful of linking inadvertently to other file that may not be present.

Other miscellany

Newer version of Excel may generate files with .xlsx file extension

I leave you with this:

SAVE YOUR WORK!

Thank you for participating, happy spread sheeting!