Microsoft Excel for Beginners

1Pinkerton AcademySteve ThissellTechnology Integration CoordinatorMicrosoft ExcelforBeginnersClick here for Intermediate Level Slides2OverviewMS Office ’97 or 2000SpreadsheetSpreadsheet WindowCreating A New Document3MS Office 97Microsoft WordMicrosoft ExcelMicrosoft AccessMicrosoft Power PointMicrosoft Outlook4Office Assistant–The Office assistant is the help program that allows you to type in questions in English and get answers.5Creating a New DocumentTool Bar Button File menu NewStart button New Office Document6New DocumentsBlank DocumentTemplates7IntroductionWhat is a spreadsheetFormatScreen LayoutTitle BarMenu BarTool BarFormula Bar8What 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 year9Spreadsheet 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)10Consists of:–Title Bar Menu Bar Tool bars Formula Bar–Scroll Bars Status Line–Message lineSpreadsheet Window11Spreadsheet ToolbarTitle barMenu BarTool Bar12Spreadsheet FormatMade up of Columns and RowsColumns labeled A -B -CRows Labeled 1 -2-3Where column meets row called CELL13Rows and ColumnsCell (B7)14DATA TYPESText 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 name15Text 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 Word16Numbers or valuesNumbersFormulasFunctionsDatesOther17Formula: =b27+c34This is saying “Take what is in cell b27 and add it to what is in cell c34)Answer goes where you typed in the formulaFORMULAS18FUNCTIONSBegins with a name:sum average count etc.Includes range of cells=sum(b24:b56)=average(e34:e56)191ABCDEFG2NAME DATE PERIOD ASSIGNMENT #345678910ADDITION1112FIRST NUMBER21.5858.3178.2498.2123.9813SECOND NUMBER36.4821.8758.9187.8714.2514------------------------------------------------------------------------------------------------------------15ANSWER58.0680.18137.15186.0838.23SPREADSHEET ASSIGNMENT #1DECIMAL PROBLEMSSample Spreadsheet20WHAT DO YOU WANT TO DO?1.Grades2.Budget3.Checkbook4.Class Lists5.Rosters6.Seating Plans21Plan AheadLay it out on paper–SketchLabel parts–Labels (text)–Values (numbers)–Formulas & Functions22Layout SpreadsheetEnter column titlesEnter row titlesSet up area for dataCreate formulas23TemplatesTemplates –pre-created spreadsheets on various topics ready for you to fill in the data.–Remember; this is called Microsoft Office NOT Microsoft School24Templates25Intermediate Excel WorkshopOverviewFormulasFormatting NumbersAbsolute ReferencesRange NamesPrintingPage SetupAutoSumPaste26Formulas1.Simple formulas may consist of several parts.2.begins with = sign to tell the computer that what follows is going to be a formula and not text3.A cell reference such as C194.An operation such as + -* /(addition subtraction multiplication division)5.A second cell reference such as D196.Finished formula looks like this: =C19+DI9and reads "The sum of the values in C19 and D19“27Formulas cont’1.You can enter this information several different ways:2.You can type it right in the cell it is going in.3.Or, you can point to the cells you want the formula to compute.28Formulas 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 B5a) Select the cell that the formula goes in.b) Type =c) Click cell to be the first cell referenced B5d) Enter the minus signe) Click on the next cell referenced C11f) Hit Enter.291-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 summedamount in and click the AutoSum button on the toolbar to automatically sum the numbers.Quick and Dirty30Conditional 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.31Formulas Continued1) Referencing cells in formulas.2) Relative Reference3) 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.32Absolute Reference1) 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 themwith $. (=$A$12) or =($A12) or =(A$12)33To enter absolute references in a cell1) Hit equals sign and the cell location you want to make absolute =B142) 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.34Entering references to other sheets in your workbook.1.You can include references to data in other worksheets in your workbook2.To do so, enter the name of the sheet an exclamation point and the cell reference. -sheet 3! A33.This refers to cell A3 in sheet 3 of your workbook35Referencing other files.1)To reference from another file or workbook, for instance, to refer to cell C10 in sheet 1 of the file Western Sales 2)type ='[Western Sales.XLS]Sheet V$C$1036Using Range Names1) =I39-C9 isn't very clear what data is being used2) 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 -Expenses4) this is very clear as to what data is being referenced. 37To create a Range Name1) Select range2) Click the NAME box located at the left end of the formula bar.3) Enter the name you want assigned to the cells4) Enter38Printing Worksheet Data1)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. OK5)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 defaulta) Select Area to printb) File menu--> Print Area--> Set Print Area39Functions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)40FunctionsFunctions, in essence, are built-in shortcuts that make completion of complex formulas unnecessary.41FunctionsUnderstanding the over 200 built-in functionsStatistical, financial, mathematical, database, business, scientific and engineering functions are all done for you.42FunctionsIncludes date, time, info, logical, look up, reference, text and trigonometric functions.43Using 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)44Using arguments in functionsSome functions also have optional arguments. These mean that you can use them but they are not required.45Using the Paste Function Feature1)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.46Using the Paste Function Feature3)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.47Using the Paste Function Feature1)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.48Using the Paste Function Feature5) Errorsa) 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.