VIEWS: 1,791 PAGES: 48 CATEGORY: Computers & Internet POSTED ON: 2/6/2008
Microsoft Excel for Beginners Pinkerton Academy Steve Thissell Technology Integration Coordinator Click here for Intermediate Level Slides 1 Overview MS Office ’97 or 2000 Spreadsheet Spreadsheet Window Creating A New Document 2 MS Office 97 Microsoft Word Microsoft Excel Microsoft Access Microsoft Power Point Microsoft Outlook 3 Office Assistant – The Office assistant is the help program that allows you to type in questions in English and get answers. 4 Creating a New Document Tool Bar Button File menu New Start button New Office Document 5 New Documents Blank Document Templates 6 Introduction What is a spreadsheet Format Screen Layout Title Bar Menu Bar Tool Bar Formula Bar 7 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 8 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) 9 Spreadsheet Window Consists of: – Title Bar Menu Bar Tool bars Formula Bar – Scroll Bars Status Line – Message line 10 Spreadsheet Toolbar Title bar Menu Bar Tool Bar 11 Spreadsheet Format Made up of Columns and Rows Columns labeled A - B - C Rows Labeled 1 -2- 3 Where column meets row called CELL 12 Rows and Columns Cell (B7) 13 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 14 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 15 Numbers or values Numbers Formulas Functions Dates Other 16 FORMULAS 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 FUNCTIONS Begins with a name: sum average count etc. Includes range of cells =sum(b24:b56) =average(e34:e56) 18 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 WHAT DO YOU WANT TO DO? 1. 2. 3. 4. 5. 6. Grades Budget Checkbook Class Lists Rosters Seating Plans 20 Plan Ahead Lay it out on paper – Sketch Labels (text) Values (numbers) Formulas & Functions Label parts – – – 21 Layout Spreadsheet Enter column titles Enter row titles Set up area for data Create formulas 22 Templates Templates – pre-created spreadsheets on various topics ready for you to fill in the data. – Remember; this is called Microsoft Office NOT Microsoft School 23 Templates 24 Intermediate Excel Workshop Overview Formulas Formatting Numbers Absolute References Range Names Printing Page Setup AutoSum Paste 25 Formulas 1. 2. 3. 4. 5. 6. 26 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’ 1. 2. 3. 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. 27 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. 28 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. 29 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. 30 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. 31 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) 32 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. 33 Entering references to other sheets in your workbook. 1. 2. 3. 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 34 Referencing other files. 1) 2) 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 35 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. 36 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 37 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 38 Functions 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) 39 Functions Functions, in essence, are built-in shortcuts that make completion of complex formulas unnecessary. 40 Functions Understanding the over 200 built-in functions Statistical, financial, mathematical, database, business, scientific and engineering functions are all done for you. 41 Functions Includes date, time, info, logical, look up, reference, text and trigonometric functions. 42 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) 43 Using arguments in functions Some functions also have optional arguments. These mean that you can use them but they are not required. 44 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. 45 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. 46 Using the Paste Function Feature 1) 47 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. 48