Microsoft Excel for Beginners by user002

VIEWS: 1,791 PAGES: 48

									Microsoft Excel for Beginners
Pinkerton Academy Steve Thissell
Technology Integration Coordinator

Click here for Intermediate Level Slides



MS Office ’97 or 2000


Spreadsheet Window


Creating A New Document


MS Office 97
Microsoft Word

Microsoft Excel
Microsoft Access Microsoft Power Point Microsoft Outlook


Office Assistant
– The

Office assistant is the help program that allows you to type in questions in English and get answers.


Creating a New Document
 Tool

Bar Button  File menu  New  Start button  New Office Document


New Documents


Document Templates



What is a spreadsheet Format Screen Layout

 

 

Title Bar
Menu Bar
Tool Bar Formula Bar


What is a Spreadsheet?
    

Number cruncher. Does math for you. Always correct Tireless Copy and paste to reuse over and over.
– – –

Make templates Save blank versions Reuse year to year


Spreadsheet format.
     

Like a piece of graph paper. Made up of columns and rows. Plot points on spreadsheet How far over How far down Just like plotting points (R1C1) (row 1 column1)


Spreadsheet Window
 Consists

of: – Title Bar Menu Bar Tool bars Formula Bar – Scroll Bars Status Line – Message line


Spreadsheet Toolbar

Title bar

Menu Bar
Tool Bar

Spreadsheet Format

Made up of Columns and Rows  Columns labeled A - B - C  Rows Labeled 1 -2- 3  Where column meets row called CELL


Rows and Columns
Cell (B7)

DATA TYPES Text Values Formulas
 Text: Anything beginning with letters or an apostrophe. Value: Numbers, dates, and times Formulas: Begin with = sign Functions: begin with = sign and a name

Text or Labels (What limitations)
Begins with a letter.  Anything can be text if preceded by an apostrophe.

 John

Smith (this is a label)

 23 (this is a number and can be used in

mathematical calculations)
 ’23 (this is a label and cannot be used in a

calculation)  Formatted just like Word

Numbers or values

    

Numbers Formulas Functions Dates Other



Formula: =b27+c34
This is saying “Take what is in cell b27 and add it to what is in cell c34)

Answer goes where you typed in the formula 17

Begins with a name:

sum average count etc.
Includes range of cells



Sample Spreadsheet
1 A B C D E F G 2 NAME DATE PERIOD ASSIGNMENT # 3 4 SPREADSHEET ASSIGNMENT #1 5 6 DECIMAL PROBLEMS 7 8 9 10 ADDITION 11 12 FIRST NUMBER 21.58 58.31 78.24 98.21 23.98 13 SECOND NUMBER 36.48 21.87 58.91 87.87 14.25 14 ---------------- ---------------- ------------------------------------------------------------------------19 15 ANSWER 58.06 80.18 137.15 186.08 38.23

1. 2. 3. 4. 5. 6.

Grades Budget Checkbook Class Lists Rosters Seating Plans


Plan Ahead
 

Lay it out on paper

Labels (text) Values (numbers) Formulas & Functions

Label parts
– –



Layout Spreadsheet
   

Enter column titles Enter row titles Set up area for data Create formulas


 Templates

– pre-created spreadsheets on various topics ready for you to fill in the data.
– Remember;

this is called Microsoft Office NOT Microsoft School




Intermediate Excel Workshop

Formulas  Formatting Numbers  Absolute References  Range Names  Printing  Page Setup  AutoSum  Paste


1. 2.






Simple formulas may consist of several parts. begins with = sign to tell the computer that what follows is going to be a formula and not text A cell reference such as C19 An operation such as + - * / (addition subtraction multiplication division) A second cell reference such as D19 Finished formula looks like this: =C19+DI9 and reads "The sum of the values in C19 and D19“

Formulas cont’



You can enter this information several different ways: You can type it right in the cell it is going in. Or, you can point to the cells you want the formula to compute.


Formulas cont’
1) Build a formula by pointing to the cells rather than by typing the cell references. 2) TASK Enter a formula in cell B14 that subtracts the value in cell B11 from the value in cell B5 a) Select the cell that the formula goes in. b) Type = c) Click cell to be the first cell referenced B5 d) Enter the minus sign e) Click on the next cell referenced C11 f) Hit Enter.


Quick and Dirty
1- You can speed up the process of adding up columns of numbers by using the "AutoSum" option. 2- To automatically sum a column of numbers you can select the cell you want to place your summed amount in and click the AutoSum button  on the toolbar to automatically sum the numbers.


Conditional Formatting
Allows you to apply specific formatting to a group of cells only if certain conditions are met.  For instance, if the value in a cell is less than 60 then format it RED.  Apply and design by selecting the Conditional Formatting option from the Format menu.


Formulas Continued

1) Referencing cells in formulas. 2) Relative Reference 3) If you copy a formula down I cell the row # will automatically change. 4) If you copy a formula over a cell the column letter will automatically change. 5) The new formula will display cell references RELATIVE to the row or column it is in.


Absolute Reference
1) This is used when you want your formula to reference a specific cell all the time. 2) Such as; a group of values all multiplied by the same given value in a single specific cell. 3) To keep references from changing, prefix them with $. (=$A$12) or =($A12) or =(A$12)


To enter absolute references in a cell
1) Hit equals sign and the cell location you want to make absolute =B14 2) Hit the F4 key to activate the absolute reference key. The F4 key will enter the $ for you. 3) Keep pressing the F4 key until the correct combination of dollar signs and letters appears. 4) Enter the remainder of the formula and hit enter. 5) You can combine both absolute and relative references such as -$A1 or -A$ 1. This way either the row or the column reference does not change.


Entering references to other sheets in your workbook.



You can include references to data in other worksheets in your workbook To do so, enter the name of the sheet an exclamation point and the cell reference. -sheet 3! A3 This refers to cell A3 in sheet 3 of your workbook


Referencing other files.



To reference from another file or workbook, for instance, to refer to cell C10 in sheet 1 of the file Western Sales type ='[Western Sales.XLS]Sheet V$C$10


Using Range Names

1) =I39-C9 isn't very clear what data is being used 2) You can assign a name to a cell or group of cells to make their reference more clear. 3) A formula might then look like this: - Total - Sales - Total - Expenses 4) this is very clear as to what data is being referenced.

To create a Range Name
1) Select range 2) Click the NAME box located at the left end of the formula bar. 3) Enter the name you want assigned to the cells 4) Enter


Printing Worksheet Data
1) Printing a particular area. 2) Default settings cause you to print the entire worksheet that is currently active. 3) To print less than all of it select the range of cells you want printed. 4) File menu--> Print--> and click on Selection in the Print Whole Section. OK 5) If you plan to print the same area over and over, you can define this as a Print Area and it will print this range by default a) Select Area to print b) File menu--> Print Area--> Set Print Area


Functions are built-in calculation tools that perform complex financial, statistical, or analytical calculations. Begins with a name: =sum =average =count etc. Includes range of cells =sum(b24:b56) =average(e34:e56)


    



Functions, in essence, are built-in shortcuts that make completion of complex formulas unnecessary.


 

Understanding the over 200 built-in functions Statistical, financial, mathematical, database, business, scientific and engineering functions are all done for you.


Includes date, time, info, logical, look up, reference, text and trigonometric functions.



Using arguments in functions
Each function consists of an = equals sign, the function name, and the arguments (or cell names) The SUM function adds up the values in the cells in a listed range. =SUM(argument1:argument2)




Using arguments in functions


Some functions also have optional arguments. These mean that you can use them but they are not required.


Using the Paste Function Feature

1)If you're not sure just how a particular function works the Paste Function feature will guide you through its setup. 2)Either use the Paste Function button fX on the toolbar, or go to the Insert Menu--> Function--> the Paste Function dialog box has a category window and a function Name window. a) The name window lists the functions for each function selected.

Using the Paste Function Feature
3) If you are not sure which function to use you can activate the Office Assistant and choose the Yes provide help button. a) Enter a description of what you want to accomplish in the text box and click the search button. b) The Office Assistant will provide you with a list of topics that might satisfy your query. In this case a list of possible functions. c) For example: If you type "I want to create a total." The assistant responds with the SUM function.


Using the Paste Function Feature


After you select your function the function pallet drops down and prompts you to enter the proper arguments. a) An argument may be a single cell, or a group of cells, a number, or another function. b) Some functions require a single argument others require multiple arguments. c) Function arguments are enclosed in parentheses and arguments are separated by commas.

Using the Paste Function Feature
5) Errors a) If the function contains an error the display will be changed to #NAME, or #NUM?, or some such. b) In some cases, Excel highlights the part of the function that contains the error.


To top