Microsoft Excel 2010 – Level 1
TrainingGuideMicrosoft Excel 2010– Level 15 – Formulas and functions
Microsoft Excel 2010 - Level 1FORMULAS AND FUNCTIONSINFOCUSThe real magic of Excel lies in the use of formulas.If all Excel could do was allow you to input numbers andwords it would be virtually no different to a wordprocessing package – without the fancy features forprocessing words!Just as Microsoft Word allows you to work with words,Excel allows you to process numbers. This is done withformulas that are used to perform calculations.Formulas can perform simple tasks such as adding up afew cells or more complex operations. Excel actuallycontains several hundred pre-programmed formulas forperforming complex operations – these are known asfunctions.In this booklet you will:9gain an understanding of how formulas work and arewritten in Excel9learn how to create formulas that add using thepointing method9learn how to create formulas that subtract9learn how to create formulas that multiply and divide9gain an understanding of what functions are and howthey work9learn how to use the SUM function to add values9learn how to sum non-contiguous ranges9learn how to calculate an average9learn how to find a maximum value using the MAXfunction9learn how to find a minimum value using the MINfunction9learn how to create more complex formulas9learn how to perform What If testing using theformulas in a worksheet Learning and Development ServicePage 2Formulas and functions
Microsoft Excel 2010 - Level 1UNDERSTANDING FORMULASFormulas can be very simple, verycomplex, or somewhere in between. Excel,however, must know when you areentering a formula in a cell, after all, howcan it discern the difference betweennumbers, words, and formulas unless youspecifically tell it? And one other thing, you’llalso need to understand some basic mathstheories before proceeding to more complexformulas.1How Formulas WorkIn Excel every formula that you create MUSTstart with an equal sign ( ). This is atrigger to Excel. When Excel sees you start acell entry with an equal sign it immediatelyknows that you are about to enter a formulathat will perform a calculation.For instance, if you type 5 6 in a cell, asshown in example 1, Excel will display 5 6in that cell. It doesn’t know what else to dowith it.However, if you type 5 6 in a cell, asshown in example 2, Excel will display 11 inthat cell in the worksheet. In the formula barat the top of the worksheet it will actuallydisplay 5 6 whenever that cell is theactive cell.23So far, so good. But really, what use is therein typing 5 6 in a cell? If you next need toknow what 6 6 will equal you will need to dosome tricky cell editing to change theformula, or retype the formula completely.Formulas in Excel actually work like an illusion – a magician’s trick performed withmirrors! Instead of typing numbers into a formula, you type the numbers in their owncells, then type the cell addresses that refer to those numbers in the formula.In example 3, the value 5 has been typed into B2, the value 6 has been typed intoB3, and the formula B2 B3 has been typed into B4.This might seem like a lot more typing than you might otherwise do, but the real gainlies in the functionality of what is done here. For example, if you need to know what 6plus 6 equals, you simply type 6 in B2, and the formula in B4 will instantly update toshow you the answer.This occurs because Excel interprets the formula shown in B4 as this cell should equalwhatever is in B2 plus whatever is in B3. Whenever something new is typed into oneof the two referenced cells this formula is immediately recalculated and provides thelatest result.Rules For Using FormulasExcel follows the BODMAS rules of arithmetic to determine the order in whichcalculations in any given formula are performed. The order is – Brackets Of, thenDivision, then Multiplication, then Addition, then Subtraction.So the equation 3 2 x 10 could equal either 50 or 23. Using BODMAS the correctanswer is 23: 2 x 10 20 3 23.Computers do not have the standard arithmetic symbols that we are accustomed to.The keys on the keyboard that you will use to perform the following basic arithmeticoperations are shown below.AdditionSubtractionMultiplicationDivision Learning and Development ServicePage 3Formulas and functions
Microsoft Excel 2010 - Level 1CREATING FORMULAS THAT ADDIn Excel you can create formulas bytyping them directly into the cells, or bypointing to the cells. When pointing to acell, Excel types the cell address into theformula for you. This helps to avoid typingOpenFileTry This Yourself:2Before starting thisexercise you MUST openthe file E810Formulas 1.xlsx.1Click on cell E15 where weneed to add up all of thegross pays2Type to start theformula3Click on cell E8 and type (the plus sign)The E8 cell reference willbe added to the formulaand the active cell pointerwill jump back down toE15 ready for the next cellreference – the formula isactually being typed asyou point to the cells 4errors in your formulas. In this exercise youwill use the pointing method to create aformula that adds the gross pays forAlpheius Global Enterprises.34Repeat step 3 for each cellfrom E9 to E12Remember to press afteryou click in each cell 5Click on cell E13 (but don’ttype because there areno more cells to add)6Pressformulato complete the6For Your Reference Handy to Know To create a formula using the pointingmethod:1. Click on the cell to hold the addition2. Type then click on each of thedesired cells (typing after all exceptfor the last)3. Press The pointing technique is great to use,as you can actually see the formulabeing built on the screen for you. Learning and Development ServicePage 4Formulas and functions
Microsoft Excel 2010 - Level 1CREATING FORMULAS THAT SUBTRACTThere are many different types of formulasthat can be written in Excel. Virtually anytype of mathematical operation can beperformed. As well as addition, you canSameFileTry This Yourself:3Continue using the previousfile with this exercise, or openthe file E810Formulas 2.xlsx.1Click on the Subtractionworksheet tab at the bottom ofyour screen to make it theactive worksheet2Click in cell G8 where we needto calculate Angelo Marcuzzo’sNet Pay3Type to start the formula,then click on the gross payvalue in cell E84Type - (the minus sign) toindicate that you wish tosubtract from this value, thenclick on the tax value in cell F85create formulas that subtract one valuefrom another. Because it is usual to includecell references in the formula, when anyvalues change so to do the formula results.Pressformula46to complete theWe can now fill this formuladown for the other staff 6Click on cell G8, then hoverover the small black square atthe bottom right of the celluntil the mouse pointerchanges to a thin cross7Drag down to cell G15 thenrelease the mouse button8Click in cell G14 and pressto delete the unwanted formula8For Your Reference Handy to Know To create a subtraction formula:1. Click on the cell to hold thesubtraction2. Type then click in the first cell3. Type - (minus sign) then click on thecell to subtract4. Press You can mix various arithmetic signs ina formula to create more complexformulas. For example you can have acomplex formula that adds specificvalues and subtracts others. Learning and Development ServicePage 5Formulas and functions
Microsoft Excel 2010 - Level 1FORMULAS THAT MULTIPLY AND DIVIDEBasic formulas involve the same types ofarithmetical operations within the onecalculation – that is, addition, subtraction,multiplication, or division. You can mixthese operations within the one formula asSameFileTry This Yourself:much and as often as you need. However,you should always keep in mind the basicrules of BODMAS, especially where divisionis concerned.2Continue using the previousfile with this exercise, or openthe file E810Formulas 3.xlsx.In this exercise we’ll calculatethe superannuation payable foremployees, which is 9% oftheir gross pay. The logic is:3gross x super rategross x 9 divided by 100gross x (9 / 100)Note that the brackets are forreadability only and won’taffect the calculation 1Click on the More Complexworksheet tab to ensure that itis the active worksheet, thenclick in H8 which is where wewill calculate Angelo’s super2Type to start the formula,click in E8 then type*(9/100)3Pressformula5to complete theWe’ll fill down now 4Click in H8 then hover over thefill handle at the bottom rightof the cell until the cell pointerchanges to a thin cross5Click and drag down to H136Repeat steps 4 and 5 to fillH15 from G156For Your Reference Handy to Know To create a formula that multiplies ordivides:1. For multiplication, separate thevariables with an asterisk (*)2. For division, separate the variableswith a forward slash (/) More complex formulas can bemanaged using brackets. For example,if you want to multiply two numbersthen divide them by the product ofanother two numbers, enclose bothmultiplication parts of the equation inbrackets separated by a division sign.For example, (A*B)/(C*D). Learning and Development ServicePage 6Formulas and functions
Microsoft Excel 2010 - Level 1UNDERSTANDING FUNCTIONSImagine having to create a formula thatcalculated the monthly payments on aloan, or the average of over 100 cells –these would require complex or longformulas that would be time consuming todevelop. This is the role of hundreds ofarithmetic functions that have been preprogrammed into Excel for you.1 Functions OverviewFunctions are simply pre-programmed formulas already provided for you in Excel whichcan perform calculations covering a wide range of categories including statistics, dateand time arithmetic, financial calculations, lists, engineering and much more.Just like normal formulas that you create, functions must start with an equal sign. Theequal sign is then followed by the specific name of the function (usually a descriptivename which indicates the purpose of the function). Most functions also requireadditional information known as arguments which are supplied to the function inbrackets after the function name. Functions are therefore written as follows: name(arguments)The arguments are quite often cell or range references that contain values that can beused in the function. For example, the most common function is the SUM functionwhich, as its name suggests, is used to sum or add values together. If you wanted toadd all of the values in the cells from B10 to D15 you would write this function as: SUM(B10:D15)As you can see this is much simpler than writing your own referential formula whichwould look like: B10 B11 B12 B13 B14 B15 D10 D11 D12 D13 D14 D15Imagine writing and proofing a formula where you had to add 200 cells!2 Typing FunctionsIf you are familiar with the function that you need you can type it into a cell exactly thesame way you type any other formula. If you are not sure if Excel has a function or youon thecan’t quite remember how it is written you can use the Insert Function toolformula bar to assist you. When you click on this tool the Insert Function dialog boxwill be presented to you which lists the most recently used or common functions andalso allows you to search for other functions that you might need.The Insert Function dialog box will also type the function out for you and then provideyou with a further dialog box to guide you through the process of specifying thearguments that the function needs to perform its calculation. Learning and Development ServicePage 7Formulas and functions
Microsoft Excel 2010 - Level 1USING THE SUM FUNCTION TO ADDOne of the most used functions is the SUMfunction. This function allows you to addthe values in a range of cells. The functionis written as SUM(range or ranges to add).OpenFileTry This Yourself:Before starting this exerciseyou MUST open the file E810Formulas 4.xlsx.1Click on B9 then type sum(to start the formula2Click on B6 to point to this cellas the start, hold down thekey and click on B8Notice the relative addressingdetails, 3R x 1C, that appearsin the tool tip 3Type ) and presstocomplete the function4Click on B9, then move themouse pointer to the fill handleon the lower right corner of thecell and drag across to E9 tofill function across the range5Click on the Copy commandon the Clipboard group onthe Home tab6Click on B14, hold downand then click on cells B19 andB247Releaseand presstopaste equivalent functions intothe worksheetYou can type the function and then use thepointing technique to fill in the arguments.Excel then paints marquees around the cellsinvolved helping you to track your progress.127For Your Reference Handy to Know To type a sum function for a contiguousrange:1. Type sum(2. Select the range of cells3. Type )4. Press You can also use the Sum command inthe Editing group on the Home tab ofthe Ribbon to have Excel automaticallyenter a sum function based on a rangeof cells. Learning and Development Service You can type the name of a function inupper or lowercase – it is not casesensitive.Page 8Formulas and functions
Microsoft Excel 2010 - Level 1SUMMING NON-CONTIGUOUS RANGESMany users simply use the SUM function toadd a continuous block of data – known asa range but you can do more if you knowhow. With Excel you can write a SUMfunction that adds up data from one orSameFileTry This Yourself:Continue using the previousfile with this exercise, or openthe file E810Formulas 5.xlsx.1Click on B26 and type sum(to start the formula2Click on B9, type , (comma),and click in cells B14, B19 andB24 – typing , (comma) aftereach cell except the last one3Pressto complete thefunction, then click on B26againOops! We didn't add a rightbracket. Excel adds the bracketfor you with functions that useonly one set of brackets. Youcan also use multiple ranges ina function.4Click on C26 and type sum(5Hold downand use themouse to select the n click on C267Move the mouse pointer to thefill handle and drag to E26 tocopy the function acrossmore ranges within a worksheet. The abilityto sum non-contiguous ranges of data helpsyou to increase the level of functionality ofyour worksheet.235For Your Reference Handy to Know To type a sum function for a noncontiguous range:1. Type sum(2. Click on the first cell to sum3. Type , and click in the next cell tosum4. Type ) then press The big problem with typing a functionis that there is more chance of makinga typing mistake. Excel actually hassome in-built error checking, calledFormula AutoCorrect, that can correctup to 15 of the most common mistakesusers make (e.g. the right bracket tofinish a function. Learning and Development ServicePage 9Formulas and functions
Microsoft Excel 2010 - Level 1CALCULATING AN AVERAGEThe AVERAGE function allows you toaverage the values in a range of cells. It iswritten in much the same way as the SUMfunction, for example, AVERAGE(rangeof cells to average). The averagefunction can be applied using the FunctionsWizard; a part of Excel that steps youthrough the process of creating a function,or you can type it in yourself if you arecomfortable with it.1SameFileTry This Yourself:Continue using the previous filewith this exercise, or open thefile E810 Formulas 6.xlsx.1Click on B29 then click on theInsert Function tooltodisplay the Insert Functiondialog box2Click on AVERAGE in Select afunction then click on [OK] todisplay the FunctionArguments dialog box3Click on the Range Selectorfor Number1 to roll uptoolthe wizard, then hold downand select the following rangesB6:B8B11:B134B16:B18B21:B23Pressto complete therange specifications, then clickon [OK] to complete theprocessLet’s use the AutoSum function 5Click on B34, then click on thedrop arrow for Sumin theEditing group, on the Hometab and select Average6Click on B9, hold downandclick on B14, B19 and B24,to complete thethen pressformula3For Your Reference Handy to Know To insert an average function:1. Click in the cell then click on theInsert Function tool2. Click on AVERAGE in Select afunction3. Insert the required ranges then clickon [OK] You can type queries like “How do Iwork out the monthly payment for acar loan?” into the Search box in theInsert Function dialog box. Once youhave selected a function from theSelect a function list, the FunctionArguments dialog box will help you toenter the values into the function. Learning and Development ServicePage 10Formulas and functions
Microsoft Excel 2010 - Level 1FINDING A MAXIMUM VALUEWhen reviewing a long list of numbers it issometimes difficult to see which is thelargest value in the list. The MAX functionallows you to extract the highest valuefrom a range of cells. It is written in muchthe same way as the SUM function: MAX(range of cells). The function can eitherbe typed into the worksheet or entered usingthe Function Wizard.3SameFileTry This Yourself:Continue using the previous filewith this exercise, or open thefile E810 Formulas 7.xlsx.1Click on B30, then click on theInsert Function tooltodisplay the Insert Functiondialog box2Click on the drop arrowforOr select a category and clickon All3Scroll down and click on MAX inSelect a function, then click on[OK] to display the FunctionArguments dialog box4Click on the Range Selectorfor Number1, then holdtooldownand select the ranges:B6:B8B11:B134B16:B18B21:B235Pressto complete therange specifications, then clickon [OK] to complete theprocess6Click on B35, then click on thedrop arrow for the Sumin the Editingcommandgroup, then select Max7Click on B9, hold downandclick on B14, B19 and B24,to complete thethen pressformula7For Your Reference Handy to Know To insert a maximum function:1. Click in the cell then click on theInsert Function tool2. Click on MAX in Select a function3. Insert the required ranges then clickon [OK] The MAX function is ideal for chartinghigh points over a seasonal period. Forexample, you may have monthly salesfigures and use a MAX function todisplay the maximum each month. Thisseries can then be charted to show thehigh points in the sales. Learning and Development ServicePage 11Formulas and functions
Microsoft Excel 2010 - Level 1FINDING A MINIMUM VALUE MIN(range of cells). The function can beapplied using the Function Wizard, or bytyping the function in detail directly into thecell.The Minimum or MIN function allows youto extract the lowest value from a range ofvalues. It is written in much the same wayas the SUM function or MAX function:SameFileTry This Yourself:Continue using the previousfile with this exercise, oropen the file E810Formulas 8.xlsx.1Click on B31 then click onthe Insert Function toolto display the InsertFunction dialog box2Click on the drop arrowfor Or select a categoryand click on Statistical3Scroll down and click onMIN in Select a functionthen click on [OK] todisplay the FunctionArguments dialog box4Click on the Rangeto roll upSelector toolthe wizard, then hold downand select the to complete therange specifications, thenclick on [OK] to completethe process7Let’s simply type thefunction this time 6Click on B36 and type MIN(B9,B14,B19,B24)7Pressformulato complete theFor Your Reference Handy to Know To insert a minimum function:1. Click in the cell then click on theInsert Function tool2. Click on MIN in Select a function3. Insert the required ranges then clickon [OK] You might use a MIN function in reallife to find the lowest value in a largerange of numbers. For example, in alarge inventory it can be used to workout which product is the slowest seller. Learning and Development ServicePage 12Formulas and functions
Microsoft Excel 2010 - Level 1MORE COMPLEX FORMULASOut in the real world, you will find that youare often faced with creating formulas thatneed to add, subtract, multiply, divide, andso on, all in the same formula. Don’t bedaunted. The one rule that reminds you ofthe order in which Excel performs itscalculations is BODMAS: Brackets Of, thenDivision, then Multiplication, then Addition,then Subtraction.SameFileTry This Yourself:1Continue using the previous file withthis exercise, or open the file E810Formulas 9.xlsx.Click on the Multiplication &Addition worksheet tabLet’s create a formula that determinesthe average number of hours workedby each employee and then calculatehow much the weekly payroll wouldbe if all employees were earning 22.50 per hour.2So we need to add the total hours,then divide by the number ofemployees (6), then multiply by thenew hourly rate, then multiply by thenumber of employees again.Considering BODMAS, the order is fineexcept for the initial addition, whichwill have to be enclosed in brackets 2Click on C15 then click on the Sumcommandin the Editing group3Click on C14 in the Formula Bar andchange it to C13, then press4Click on C15 again, click immediatelyto the right of C13) then type/6*22.50*65Pressto complete the formula –hey it’s pretty close to the currentgross pay45For Your Reference Handy to Know To create complex formulas:1. Plan your formula2. Type your formula keeping in mindthat Excel solves all bracketedoperations first, then completes theremaining operations in the order ofdivision, multiplication, addition andfinally subtraction. The Evaluate Formula command inthe Formula Auditing group in theFormula ribbon tab is handy forchecking complex formulas. It willdisplay the formula in an EvaluateFormula dialog box which allows youto step through each part of theformula to see how it works. Learning and Development ServicePage 13Formulas and functions
Microsoft Excel 2010 - Level 1WHAT IF FORMULASWhen you’ve added formulas to yourworksheet you have a calculation model.Every time you change one of thedependent values that are used in aformula, that formula, and every one thatSameFileTry This Yourself:is dependent on it will update instantly. Thisallows you to perform what-if testing. Forexample, you can enter what if formulasthat answer questions like ‘what if inflationgoes up by 2%?’.1Continue using the previousfile with this exercise, oropen the file E810Formulas 10.xlsx.1Click on the Summaryworksheet tab and make amental note of the valueshere2Click on the More Complextab to display theworksheet, then click in cellC8 which contains the hoursfor Angelo Marcuzzo3Type 37 and pressNotice how the formulasupdate the values in row 15as you change thedependent data 4Click on the hours for theother employees and typethe new values as shown45Click on the Summaryworksheet tab to return tothe Summary worksheet5The values will have beenautomatically recalculatedto reflect the changesFor Your Reference Handy to Know To use a formula for what-if testing:1. Change the value in the cell that isreferenced by a formula2. Evaluate the changed results in theformula results cellExcel has three different functions thatcan be applied for what-if testing: Learning and Development Service SUMIF calculates a total amount basedon a single condition. COUNTIF counts the number of timesa value appears in a range of cells. IF is used for either/or scenarios.Page 14Formulas and functions
Microsoft Excel 2010 - Level 1CONCLUDING REMARKSCongratulations!You have now completed the Formulas and functions booklet. This booklet was designedto get you to the point where you can competently perform a variety of operations as listedin the objectives on page 2.We have tried to build up your skills and knowledge by having you work through specifictasks. The step by step approach will serve as a reference for you when you need to repeata task.Where To From Here The following is a little advice about what to do next: Spend some time playing with what you have learnt. You should reinforce the skillsthat you have acquired and use some of the application's commands. This will testjust how much of the concepts and features have stuck! Don't try a big task just yet ifyou can avoid it - small is a good way to start. Some aspects of the course may now be a little vague. Go over some of the pointsthat you may be unclear about. Use the examples and exercises in these notes andhave another go - these step-by-step notes were designed to help you in theclassroom and in the work place!Here are a few techniques and strategies that we've found handy for learning more abouttechnology: visit CLD’s e-learning zone on the Intranet read computer magazines - there are often useful articles about specific techniques if you have the skills and facilities, browse the Internet, specifically the technicalpages of the application that you have just learnt take an interest in what your work colleagues have done and how they did it - wedon't suggest that you plagiarise but you can certainly learn from the techniques ofothers if your software came with a manual (which is rare nowadays) spend a bit of timeeach day reading a few pages. Then try the techniques out straight away - over aperiod of time you'll learn a lot this way and of course, there are also more courses and booklets for you to work through finally, don’t forget to contact CLD’s IT Training Helpdesk on 01243-752100 Learning and Development ServicePage 15Formulas and functions
programmed into Excel for you. Functions Overview . Functions are simply pre-programmed formulas already provided for you in Excel which can perform calculations covering a wide range of categories including statistics, date and time arithmetic, financial calculations, lists, engineering and much more.
Excel 5.0 Excel 5.0 1993 Excel 5.0 1993 Excel 7.0 Excel 95 1995 Excel 8.0 Excel 97 1997 Excel 98 1998 Excel 9.0 Excel 2000 1999 Excel 2001 2000 Excel 10.0 Excel XP 2001 Excel v.X 2001 Excel 11.0 Excel 2003 2003 Excel 2004 2004 2.1.2 Worksheet Document Definition: Worksheet Document A worksheet document consists of a single sheet only.
While Excel 2010 documents share a file extension with Excel 2007 (*.pptx), the Excel 2010 file is a unique file type. Excel 2007 documents will open in “Compatibility mode” and will not have certain Excel 2010 tools available unless re-saved as an Excel 2010 document. Saving a Excel
What is Microsoft Excel 2010? Microsoft Excel is a spreadsheet program. The version covered in this tutorial is Excel 2010. Excel 2010 is slightly different than Excel 2007 and quite different than Excel 2003. The function of a spreadsheet is to store and manipulate data, in
Power Map Power Map provides a new perspective for your data by plotting geocoded data onto a three-dimensional view of the earth and optionally showing changes to that data over time. To use Power Map, you import raw data into a Microsoft Excel 2013 workbook, add the data to an Excel data model, and enhance the data in the data model if necessary.File Size: 1MBPage Count: 17Explore furtherGetting an excel list of all Azure Virtual machinesdbaharrison.blogspot.comDownload Azure Devops Board To Excelwww.how-use-excel.comGetting an excel list of all Azure Virtual machines .www.firstcloud.ioGetting an excel list of all Azure Virtual machines .laptrinhx.comRunning Excel On Azurewww.how-use-excel.comRecommended to you based on what's popular Feedback
1. Memulai Excel 2003 Untuk membuka aplikasi Excel 2003 kita dapat mengklik icon Excel yang ada pada layer desktop atau menggunakan menu Start All Program Microsoft Office Microsoft Office Excel 2003 hingga muncul tampilan lembar kerja Excel. Menu Microsoft Excel
Excel 2010 Beginner Level 1 Page 4 of 42 Lesson 1: Excel Interface – Part 1 Let’s get started today by opening Microsoft Excel. I’m using Windows Vista, so go to the Start button, All Programs, find Microsoft Office and select Microsoft Excel: When you open the spreadsheet, at first glance it can be intimidating .
Microsoft Excel can also be used to balance a checkbook, create an expense report, build formulas, and edit them. CREATING A NEW DOCUMENT OPENING MICROSOFT EXCEL ON A PC To begin Microsoft Excel, Go to Start All Programs Applications Microsoft Office Microsoft Excel
click Start - (All) Programs - Microsoft Office - Microsoft Office Excel 2007. If you have a Microsoft Excel document in Windows Explorer, in My Documents, or in an email, etc, you can double-click it. This would also start Microsoft Excel and would open the document. The classic way users launch Microsoft Excel is from the Start menu on the .
