Microsoft Excel 2007 A Beginners` Guide
Contents
1. 1 Move to cell A7 andtypein 25 12 then press lt Enter gt Because you forgot the equals sign denoting a calculation Excel interprets this as a date 2 Move back to A7 and correct your mistake type 25 12 and press lt Enter gt You will find that the result is still translated into a date Excel has assigned a date format to the cell To display the information as a number you have to clear the format To do this 3 Click once on the Format Painter to pick up the format of cell A8 4 Now click on cell A7 the format clears and the number should be properly displayed Note that if you click on the Format Painter once you can copy the format to a single cell or range and then the painter is automatically turned off You can also clear a format via the Clear button in the Editing group on the right of the Home tab of the Ribbon 5 Finally press lt Delete gt to empty the cell the data isn t needed Inserting Blank Rows and Columns Next add a title to your work Unfortunately there is insufficient room at the top of the sheet for this so you will first have to insert some blank lines 1 Right click on the row number 1 and choose Insert from the menu which appears 2 Repeat step 1 for a second blank row Note If you right click inside a cell you can insert or delete either a single cell or the whole column row You can also insert or delete a column by clicking on a column letter Try this next 3 Right click on the co2. AutoSum button on the right of the Ribbon the remaining totals are filled in Sometimes Excel guesses the range of cells to be summed incorrectly The default is to total down a column rather than across a row See what happens by recalculating the total in F7 5 Move to F7 and press lt Delete gt to clear the cell 6 Click on the AutoSum button note the range is incorrect 7 Drag through the correct cells B7 to E7 to amend the range then press lt Enter gt to complete the calculation The IF Function Another slightly different function is IF which lets you test whether something is true or not and take different action as appropriate This next example shows you how it works What would happen if in 2011 there was an exceptional Costs bill of 35 000 for new equipment or premises for example Income is only 34 650 so the business would make a small loss 1 Type anew Costs figure of 35000 into cell E4 and press lt Enter gt Note that the Profit Before Tax is correctly shown as negative However the Tax figure is also negative as if the tax collector was giving you money for making a loss This doesn t usually happen To make the model behave correctly you need to alter the formula in the 2011 Tax cell E6 2 Move to E6 and press lt Delete gt to clear the cell 3 Click on the Insert Function button on the Formula Bar and choose IF reset Or select a category to Most Recently Used to get to this most easily the
3. and E2 The Copy Command Fes Copy Mili Paste Cut amp Assume that both income and costs will grow by 20 in 2009 from their 2008 figure 1 InC3 enter B3 120 press lt Ctrl Enter gt to carry out the calculation but stay in the cell Where one formula is essentially exactly the same as another except that the calculations are being made in a different row or column on different cells you can copy it and Excel will automatically adjust it to account for its new position Assuming costs also rise by 20 the formula for C4 is almost identical to that for C3 and can be copied across 2 Check you are in cell C3 then click on the Copy button or press lt Ctrl c gt or right click and choose Copy from the popup menu the cells will be surrounded by a moving border 3 Move down to C4 press lt down arrow gt then press lt Enter gt note how the formula which was B3 120 in C3 has been modified to B4 120 in C4 IMPORTANT When pasting formulae in Excel you don t have to use Paste at all Instead you move to destination cell and press lt Enter gt This completes the copy paste process turning off the moving border and removing the data from the Clipboard If you use Paste then the moving border remains indicating that you can Paste again should you need to Get used to using lt Enter gt for a single copy paste and Paste for multiple ones use lt Enter gt for the final one in the sequence You can copy a range of
4. cells in the same way Here the Profit Before Tax Tax and Profit After Tax formulae for 2009 are essentially the same as those for 2008 4 Drag through the cells required here from B5 to B7 The three cells should now be blocked with a single darker border around them 5 Click on the Copy button the cells will be surrounded by a moving border 8 6 Press lt right arrow gt to move to C5 and press lt Enter gt Appropriately modified formulae appear in the cells with the results displayed Note that you do not have to select a block of cells to paste a range just paste in the top left cell of the destination area Copying formulae or values between cells is such a common requirement that Excel provides a special facility called autofill for doing this The key is the cell handle the small black square at the bottom right corner of the active cell or range Try using this for the 2010 and 2011 formulae 7 The cells to be copied C5 to C7 should still be selected if not drag through them 8 Move the mouse cursor over the small black handle at the bottom right of the selection in cell C7 the cursor will change shape to a simple black cross 9 Hold down the left mouse button and drag the handle over the area you wish to fill across to E7 release the mouse button and the formulae are copied across Take great care when using the cell handle to copy formulae If the mouse cursor is pointing to the edge of the sel
5. 000 18 000 19 800 20 790 5 Profit Before Tax 10 000 12 000 13 200 13 860 MR E 6 Tax 3 000 3 600 3 960 4 158 7 ProfitAfterTax 7 000 8 400 9 240 9 702 g Realigning Titles iH f _ _ Align Left L Center T Align Right neal Merge and Center By default text is displayed on the left of a cell numbers including dates and times on the right Your model would look much neater if the year headings 2008 2009 etc were in bold text in the centre of the columns and the main title Profit and Loss was centred across the figures below 1 Select row 2 by clicking on the number 2 on the left hand side 2 Click on the Bold button to make the text bold 3 With the cells still highlighted click on the Center button on the Home tab of the Ribbon The justification commands are also available via the Format Cells Alignment button the little arrow on the right of the Alignment group heading on the Home tab Here you can justify cell contents vertically as well as horizontally Another option Wrap text allows text to flow onto several lines the row height increases to accommodate it Tip If you want to force text onto more than one line in a cell hold down the lt Alt gt key and press lt Enter gt where you want each new line to begin Merge and Center is used to centre text across several columns try this for your main title 4 Select cells A1 t
6. Border Next select F3 to F7 and add a Left Border in a similar way ae YS Removing the Gridlines The gridlines are the grey lines separating the rows and the columns Excel gives you the choice as to whether you want them displayed or not To turn them off 1 Move to the Page Layout tab on the Ribbon 2 Inthe Sheet Options group uncheck the View option in the Gridlines button 3 Toturn them back on repeat step 2 as above Note this only affects whether they are displayed on the screen If you want them to be turned on off when printed turn on the Print option below View The default is that they aren t printed Printing your Work General information about printing on IT Services machines is available in the Quick Guide to Printing document In Excel you should also be familiar with the options available in Page Setup 14 Print Preview Before printing your work you should preview it This is particularly important in Excel if you want it to fit neatly onto the page In a minute you will look at the Page Setup but first 1 Click on the Office button and choose Print followed by Print Preview Ideally it would look better to have the paper sideways and you wouldn t want all of the data shown the taxrate cell for example shouldn t appear 2 Click on Close Print Preview to quit Print Preview Page Setup In Page Setup you can control how your results will appear on a printed page including whether any column headings ar
7. IT Services University of 7 Reading Microsoft Excel 2007 An Essential Guide Level 1 Contents Introduction co cccccccccscscscsseseseccscscssesescscsssssssscscscscesescscssscsssscacasscscsscssasscessscseacatscsacseass 1 SI E E E E PE E E ETENEE 1 The Excel Screen wecccecccscsssssscscscesecsscscscessseccscscsssssscscssecessscscssscsssscaeacecscene 1 GOE AO D eona a E E 2 Moving Around the Worksheet u c cecsessssssssessessesseessessessesseeseesseees 2 SVINE OMI WO eaen E E EEE 2 PATA ERE Y a E tines 3 K a e ra E E REE 4 Bamining the Cell Contents sissisotaan 4 Commands cceccscscssscessscscecssessscscscscesssescssscsssscscacscsssscscassssssscscasacsssscscacacsusecseasacsessene 5 Changing Column Width sssssssssssesssseseesssssssseccrsssssssseecrsessssssseeresssssssss 5 Changing the Format sssssssssesssssssssseceesssssssseecrsssssssseeerressssssserrreesssssss 5 Cl aring a OUI LA ccatessssea cae esedescrenndeastecaanstseencee ds ance nr iraner kiise ii irentsi T Inserting Blank ROWS and Columns ssssssssesssssesessssssssseceesssssssseeersesss T Changing the Style Of Text wo ccsssssessessssssssssessessesssessesseeseeseesseees T Storing Numbers as Cane ene ene nen 8 The CODY COM AINGD sensira arni 8 Realigning Titles sstetrastercestaantaeiensnnntanieiantieneiennneaneern 10 FUNCTIONS esessesessessesessesesseseesesresesreseneseesessesesreseeesresesrestuestesesresenoeseesesrestseseeseseseses 10 Calculating Tot
8. als the SUM Function ssssssssssessssseeeesssssssseceresesss 11 The IF Function iu eecececccccceccscscccesessscscscscecsscscssscscsscscscscsseseseasecsesscacaceces 11 REVS aNd ENANS onssas E A E E EER 12 OTAS a e E A E 12 Altering the Chart Type ssssssssessssssseesssssssseceressssssseeeressssssseeerreessssseeeens 13 3 D ROON serisinin aa ea ENE rE a raiti 13 Borders and Gridlines es sessesssssssssssesssssesssssesssssesssssesnsseressssesnsssrssssceessseressseees 14 Removing the Gridlines s ssssssssssesssssssssesersssssssseeeressssssssecrrssssssseeerns 14 Printing your Work ssssssssssssssseesssssssssssssssosetsesssossssssssseettreeonnsssssssssserterorsnsssssssss 14 Print Preview sessesseseesessessessesseseeseesessesseseseeseeseesessosesesresreseesessessesresne 15 Pee CUD er a E cee escent 15 EE VINEX CC e A E A AE 16 APPENA eae E E E E E EE S 16 Introduction Spreadsheets were originally developed for book keeping however they are also used for scientific calculations data manipulation and for producing graphs Excel includes some statistical functions but for serious research work a specialised package such as SAS or SPSS should be used Excel can also sort and select data subsets For storing large amounts of data or more complex tasks Access should be used These notes cover the fundamental usage of Excel but also include many hints and tips Starting Excel To start up the program 1 Open the Windows Sta
9. areas 1 Select the required data area cells A2 to E7 ie missing out the Totals and main heading 2 Click on the Insert tab on the Ribbon then on the Column button and choose the first 2 D Column chart type for a Clustered Column A chart appears on the spreadsheet and three new Chart Tools tabs are added to the Ribbon Don t worry if the chart partly covers the data that is still all there indeed the two are intricately linked If you were to alter any of the original data the graph would immediately be updated Further you can copy additional values directly from the worksheet and paste the data straight onto the graph Moving a Chart This default chart isn t quite right and charts are better placed on separate sheets To change this 1 Onthe Chart Tools Design tab on the Ribbon click on the Switch Row Column button on the left the year is now used for the x axis 2 Click on the Move Chart button on the far right of the Ribbon or right click on the chart and choose Move Chart then choose New sheet press lt Enter gt for OK Next add a title to your chart 3 Move to the Chart Tools Layout tab on the Ribbon 4 Click on the Chart Title button and choose Above Chart 5 Type Financial Statistics 2008 11 then press lt Enter gt Note that you could also add Axis Titles here but these are not needed as the year is obvious and the vertical axis already has a currency symbol 12 Your chart should now appear as
10. ax and Profit After Tax figures are now displayed with a single decimal place This looks a bit untidy as the other figures have no decimal places It would be neater if all the numbers were shown as a currency either with two decimal places pounds and pence or as whole numbers pounds only To do this you first have to select the cells here format the whole column 2 Click once on the letter B at the top of the column the column should be highlighted except for cell B1 Five commonly used formats are provided in the Number group on the Home tab of the Ribbon 3 Click on the first formatting button for an Accounting style What was 24444 should now be shown as 24 444 00 If HHHH HHH is displayed widen the column 4 To remove the decimal point and pence click twice on the Decrease Decimal button The figures should now be displayed as whole numbers Note that you have only changed the display format formats don t affect the stored data or accuracy to which calculations are made Er ere Tae Number F A wider range of cell formats is available via the Format Cells Number button This is shown as a small arrow to the right of the word Number below Decrease Decimal Try using this next 5 Make sure column B is still selected 6 Click on the Format Cells Number button the small arrow in the corner of the group 7 Inthe Format Cells window change the Category from Custom to Currency 8 Set the number of Dec
11. below Financial Statistics 2008 11 E Profit After Tax 5 000 If you want to change any of the other settings eg one of the colours used for the columns or the background colour 6 Right click on the area of the chart to be changed eg a column or the background and choose Format Data Series or Format Chart Plot Area 7 Move to the Fill options turn on Solid Fill and choose a Color click on Close Note that you can also get to these commands using the Format Selection button on the far left of the Chart Tools Format tab on the Ribbon Another button on this tab is Shape Fill which takes you directly to fill colours Altering the Chart Type Change Chart Type Lem There are over a hundred different styles of graph which Excel can produce To change the type 1 Right click on the chart and choose Change Chart Type or click on the Change Chart Type button on the far left of the Chart Tools Design tab on the Ribbon 2 Using the mouse select some of the different chart types shown to see what is available 3 D Rotation Excel allows you to rotate a 3 D graph to whatever angle and pitch you require which can be very useful when some data values are hidden behind others The procedure is as follows 1 Right click on the chart and choose Change Chart Type 2 Inthe Column category choose 3 D Column the seventh in the top row click on OK Sadly t
12. d whether you want to save the changes to test xlsx Note This document is an introduction to Microsoft Excel 2007 More advanced notes Microsoft Excel 2007 An Intermediate Guide and Advanced Spreadsheet Topics Using Microsoft Excel 2007 are also available Appendix The formulae required to complete the test example are In A5 type Profit After Tax BI B3 B4 D3 C3 110 D4 C4 110 E3 D3 105 E4 D4 105 Trademark owned by Microsoft Corporation Screen shot s reprinted by permission from Microsoft Corporation Copyright 2008 The University of Reading Last Revised July 2010 16
13. e Note that Excel has coded the file name as amp File This is called a Field Code and will display the current name of the file if you save it with a different name Other codes are available via the icons provided read the text above to work out which is which Try adding a page number on the right 10 Click inside the Right section then on the second icon the sign amp Page is added 11 Click on OK and note the page number now shows in the preview of the footer The Sheet tab lets you define the area to be printed letting you omit cells you don t want here you don t want the taxrate displayed one way to do this would be to make the font colour white but setting the print area is better It also controls whether Row and column headings are printed 12 Move to the Sheet tab 15 13 In the Print area box type A1 F7 Tip Sheet also lets you set Print titles rows and or columns which are automatically repeated on each page for data which extends over several pages To set this to the first row for example use 1 1 for the first two columns use A B type the range or select the rows columns to fill these in Within Page Setup you have links to Print and Print Preview 14 Click on Print Preview to view the modifications 15 Click on Close Print Preview to return to the worksheet Leaving Excel To quit from Excel 1 Choose Exit Excel from the Office button 2 Press lt Enter gt for Yes when aske
14. e active cell to cell A1 To move to the left edge of a block of data hold down lt Ctrl gt and press an lt arrow gt key in the direction you wish to move lt Ctrl Enter gt keeps the current cell the active cell useful when entering data or editing a formula Saving your Work Save It is important to save your work frequently you never know when the computer might fail 1 Click on Save on the Quick Access Toolbar or use Save from the Office button Up until now your work has been known as Book7 you are now asked to give it a proper name 2 2 Type test into the File name box Note that the default drive is set to My Documents on drive N your home directory on IT Services PCs Avoid working directly from to floppy disks or a USB memory stick always copy your files to My Documents work on them there and then copy back to the floppy stick if required 3 Press lt Enter gt or click on Save to carry out the save Note that once the file has been saved the new name test x sx appears at the top of the screen Tip Press lt Ctrl s gt every so often to save your file as you are working on it thereby insuring you don t lose any changes you have made lt Ctrl s gt is a shortcut key for the Save command Data Entry v Undo Information can be entered into the active cell either in the form of raw data or calculations Excel recognises various sorts of data text numbers dates and times which can the
15. e repeated on each page as a header row 1 On the Page Layout tab on the Ribbon click on the Page Setup button the arrow to the right of the Page Setup group heading You will see various options grouped under four tab headings Several settings are exclusive to Excel The following exercise modifies some of these to show you how they work 2 Check you are viewing the Page tab 3 Under Orientation choose Landscape ie sideways 4 Under Scaling set Adjust to to 180 this will magnify your data to fill the page Note that you can always force your work to fit on a single page or more than one if you require it Note also that the Paper size should be A4 if you are printing from an IT Services machine 5 Move to the Margins tab Click on both Horizontally and Vertically under Center on page Move to the Header Footer tab Click on the list arrow attached to Footer and choose Test to add the file name Next click on Custom Footer the following dialog box appears oe ee Footer Footer To Format text select the text then choose the Format Text button To insert a page number date time File path Filename or tab name position the insertion point in the edit box then choose the appropriate button To insert picture press the Insert Picture button To Format your picture place the cursor in the edit box and press the Format Picture button ela ta Left section Center section Right section amp Fil
16. ection rather than the handle the cells are moved and not copied Your screen should now look like this Profit and Loss 1 F F F F 2 2008 2009 2010 2011 3 Income 25 000 30 000 4 Costs 15 000 18 000 5 Profit Before Tax 10 000 E 12 Hi 6 Tax 3 000 3 600 7 ProfitAfterTax 7 000 8 400 Don t worry about the 0 s for 2010 and 2011 because although the formulae are there there are no income or costs figures in D3 D4 E3 or E4 to work on Examine the formulae which underlie the figures by moving the active cell around and looking at the Formula Bar Notice how the Profit Before Tax formula which was c3 c4 in 2009 has changed to D3 D4 in 2010 and E 3 E 4 in 2011 Very occasionally you might want to copy the values themselves rather than the formulae This you can do via the Paste Special button Next provide the model with some Income and Costs figures for 2010 and 2011 10 Work out formulae for D3 D4 E3 and E4 yourself given the following assumptions e in2010 Income and Costs will increase by 10 over the 2009 values e in2011 Income and Costs will increase by 5 over the 2010 values Hint Copy the formula from C3 to D3 and 3 amend these then copy down to D4 and E4 The screen should look like this see the Appendix or use the links above to check the formulae A B r D E i Profit and Loss 008 2009 2010 2011 E 25 000 30 000 33 000 34 650 4 Costs 15
17. he Profit figures are hidden by the Income and Costs To rectify this 3 Move to the Chart Tools Layout tab on the Ribbon and click on the 3 D Rotation button 4 Change the X Rotation angle using the spin button control up down arrows provided 5 Experiment changing the Y Rotation angle and Perspective 13 Another way to display the profit figures would be to reverse the axis values 6 First click on Default Rotation then press lt Esc gt for Close to reset the rotation 7 Next click on the Axes button on the Chart Tools Layout tab on the Ribbon and choose Depth Axis followed by Show Reverse Axis You should now be able to see the hidden values Borders and Gridlines Seasees x Borders EE Another format you can apply to a range of cells or single cell is to put a border round them For example you might want your screen to look like this A B C E E F 1 Profit and Loss 2 2008 2009 2010 2011 Totals 3 Income 25 000 30 000 33 000 34 650 122 0 4 Costs 15 000 18 000 19 800 35 000 5 Profit Before Tax 10 000 12 000 13 200 350 6 Tax 3 000 3 500 3 950 0 7 Profit AfterTax 7 000 8 400 9 240 350 a Move back to Sheet1 by clicking on its tab on the left at the foot of the screen Select cells B3 to F7 include the Totals this time On the Home tab on the Ribbon click on arrow on the right of the Borders button Choose a Thick Box
18. he cell references by clicking on the cells required with the mouse Here in B3 for example you would type then click on B1 then type before clicking on B2 and pressing lt Enter gt This is particularly useful when picking up cell references from other sheets You are now presented with the result of the calculation rather than the formula itself namely 7000 Note however that though Excel displays the answer it is the formula which is stored in the cell Spreadsheets are designed to recalculate as they go along so see what happens when you change one of the original numbers 7 Move up to B1 press lt up arrow gt and type 25000 8 Watch as you press lt Enter gt and note that the Profit in B3 is recalculated as 10000 The formula B1 B2 knows that B1 has changed and the display in B3 adjusts accordingly Next extend the example to bring tax into the calculations 9 Change the text in A3 move to the celland enter Profit Before Tax instead On pressing lt Enter gt you will find the text is truncated letters on the right are missing Don t worry about this the column will be widened later to show all the letters 10 In cell A4 type Tax then move to B4 press lt right arrow gt Assume that tax is levied at 30 of Profit Before Tax enter the following formula for this cell 11 In B4 type B3 30 don t forget the leading sign Note that Excel in common with other computer software uses an asterisk for multipl
19. hich time you will be asked to supply a real name On the far right are the usual Minimize Maximize and Close buttons 1 The next section down the screen is called the Ribbon This replaces the menu and toolbar system in previous versions of Excel All the commands now appear as buttons pictures on this Ribbon which has a series of tabs under which different commands are grouped When you point to a button a tool tip appears telling you what that button does Below the Ribbon is the command line The area on the left showing the characters A1 is the Name Box To its right is the Formula Bar which shows you what information is stored in a cell The main body of the screen contains the worksheet You are currently using Sheet1 of Book1 as denoted by the sheet tab at the bottom The worksheet has numbers down the side denoting rows and letters across the top denoting columns Each intersection of a row and column is known as a cell and has a unique name The cell in the top left corner is A1 the intersection of column A and row 7 and is currently the active cell This is denoted by a darker border with its identity shown in the Name Box while the column letter and row number are shown with an orange brown background Any information you type is stored in the active cell Down the right of the screen is the scroll bar which is used for moving up and down your work A horizontal scroll bar for moving left and right appears to the right
20. ication and a slash for division Use the numeric keypad for convenient access to these characters if you want to use the numbers too turn lt Num Lock gt on 12 Press lt Enter gt and the tax figure 3000 will be worked out for you 13 Work out a label and a formula to put in cells A5 and B5 to show the Profit After Tax assuming that this is Profit Before Tax minus Tax Tip As you begin to type the word Profit in A5 Excel picks up Profit Before Tax from the list of entries above This is very useful when you are typing the same information again and again eg Travel Accommodation Food on an expenses sheet You don t need this information this time but you could edit it by double clicking on the word Before on the Formula Bar and typing Ajter instead Note how the words in A5 initially appear in full they are only truncated when B5 is filled If you have got the formula right follow the links or look at the Appendix to check your answers the screen should look like this don t be tempted to cheat by typing in the value of 7000 i E D E F 1 income 25000 2 Costs 15000 3 Profit Bet 10000 4 Tax 3000 5 Protit Afte 7000 E 7 Try altering the Income or Costs figures in B1 or B2 and notice how the Profit Before Tax Tax and Profit After Tax figures are updated automatically Use the Undo button or retype the data to reset the values to 25000 and 15000 respectively Naming a Cell Sometimes it
21. imal places to 0 and choose a currency Symbol eg a Euro 9 Choose the last format for Negative numbers 1234 inred 10 Press lt Enter gt or click on OK to apply the format This produces much the same result as before except that the currency symbol is next to the figures while any negative values will appear in red you ll see this later Note that you can also display the Format Cells window by right clicking on a cell or selected range and choosing Format Cells 11 Reduce the column width by double clicking on the dividing line between the column B and C headings J Format Painter Columns C D E and F which you will be using later also need to be formatted similarly The simplest way to do this is to copy the format from column B to the other columns 12 Make sure column B is still selected 13 Double click on the Format Painter button on the far left of the Home tab of the Ribbon You will find that the blocked cells have a moving boundary while the mouse cursor now has a little brush attached 14 Click on each of the other column headings ie C to F in turn or drag across them 15 Click on the Format Painter button again to turn it off the brush disappears Only when you enter data into these cells will the new format become apparent 6 16 Finally move to B1 and retype the original value of 25000 Clearing a Format Cell formats can sometimes cause confusion to the new Excel user As an example
22. is useful to reference a cell by name rather than by column and row For example the tax rate of 30 could be held in a cell named taxrate and then referred to as such in any formulae 1 Move down to cell A10 ie well away from the rest of your work 2 Click on the active cell name A10 in the Name Box just above the heading to column A the name will be highlighted 3 Typeinthe new name of taxrate spaces aren t valid then press lt Enter gt Now in cell A10 type in 30 and press lt Enter gt 5 Finally amend the formula in cell B4 to read B3 taxrate then press lt Enter gt Tip It s easier to edit a formula rather than retype it in the cell Click at the end of the Formula Bar then use lt Backspace gt to delete the 30 Type in taxrate then press lt Enter gt Note also that function key lt F2 gt puts you in edit mode in the cell with the typing position at the end of the contents Use the arrow keys to move the typing position along the formula and press lt Enter gt or lt Tab gt to finish editing Examining the Cell Contents Often you want to view a formula rather than its result You can examine an individual formula by making its cell the active cell Move to cell B5 and you will see B3 B4 showing in the Formula Bar This means that the content of the cell B5 is the formula B3 B4 and not 7000 as displayed Move the active cell around and see what has been stored in each cell Take this opportunity to make sure you unde
23. l become bold Tip If you want just part of the text in a cell to be bold italic etc or a different font then select the text on the Formula Bar and apply the format to that selection You can also apply formatting as you type in any text Now make the text larger and change the font 3 Click on the list arrow to the right of the Font Size box and choose 14 4 Click on the list arrow to the right of the Font box and choose Arial Note how the text changes automatically as you move up and down the list of font sizes and fonts You can also right click on a cell for a floating copy of these buttons instead of using the Ribbon Storing Numbers as Text Next extend the model over time Imagine you want to project the profit and loss figures over a four year period based on some simple assumptions about what is going to happen to income and costs Firstly you need some labels to show which year is which 1 Move to cell B2 and type 2008 then move to C2 press lt right arrow gt Don t miss out the single quote at the beginning this tells Excel that although 2008 looks like a number it should be treated as text This will prevent the year 2008 being displayed as 2 008 for example as the display format for this cell is set to a currency Another reason for entering numbers as text is that they aren t then included in calculations for example if you summed a column of figures 2 Nexttype 2009 2010 and 2011 into cells C2 D2
24. lumn letter A and choose Insert from the menu a blank column A will be added You don t actually need this column so delete it 4 Right click on the column letter A again but this time choose Delete from the popup menu Inserting rows and columns may seem trivial but Excel has to adjust any formulae to take account of the changes For example the Profit Before Tax formula in B5 now says B3 B4 instead of B1 B2 Had it not been changed it would of course be invalid as B1 and B2 are now empty cells Note also that though the taxrate has moved to A12 it is still held in a cell named taxrate This is one good reason for naming certain cells you do not have to keep a list that has to be updated each time you insert a row or column of which cells hold which fixed values 5 Finally enter the title Profit and Loss in cell A1 press lt Ctrl Enter gt this will keep the current cell as A1 ready for the next command Note that you can also insert and delete rows columns using the Insert and Delete buttons in the Cells group on the right of the Home tab on the Ribbon Changing the Style of Text Arial v 10 Font Font Size mam Bold Italic Underline As in Microsoft Word you can alter the font font size and style of your data For example the title would look better if it was bigger and bolder 1 Check that the current cell is still A1 2 Click on the Bold button or press lt Ctrl b gt the text wil
25. n be used in formulae Before you begin typing make sure you are at the top left corner in cell A1 when following this course it is vital that you use the same cells as in the document 1 InA1 type the word Income then press lt right arrow gt to move to B1 Income is recognised as text and is stored as such in A1 By default text is shown on the left of a cell Pressing lt right arrow gt completes the data entry and moves the active cell ready for the next item of data If you need to correct a typing error click on Undo on the Quick Access Toolbar or simply move back to the cell in question and retype the information 2 InB1 enter 22000 then press lt down arrow gt to move down to B2 22000 is stored as an ordinary number on which the spreadsheet can perform calculations By default numbers appear on the right of a cell 3 Move back to A2 lt left arrow gt and type Costs then move to B2 lt right arrow gt 4 In B2 type 15000 then move to A3 lt down arrow gt then lt left arrow gt 5 InA3 type Profit then move to B3 lt right arrow gt In cell B3 you are going to store a formula to subtract costs from income Note that formulas in Excel start with an equals sign As you type in a formula any cell references are colour coded to help you verify the correct cells are being used 6 In B3 type B1 B2 or b1 b2 case doesn t matter and press lt Enter gt Tip When entering a formula into a cell you can pick up t
26. n press lt Enter gt for OK 4 Inthe dialog box which appears set Logical Testto E5 gt 0 and press lt Tab gt 11 5 Set Value_if_trueto E5 taxrate press lt Tab gt 6 Finally set Value_if_falseto o the screen should appear as below Function Arguments Logical _ test E5 gt 0 Yalue_if_true eS taxrate Value_if false 0 Checks whether a condition is met and returns one value if TRUE and another value if FALSE alue_if_false is the value that is returned if Logical_test is FALSE If omitted FALSE is returned Formula result 0 7 Finally press lt Enter gt for OK This means that if the contents of E5 Profit Before Tax are more than or equal to gt 0 the tax paid is equal to 30 of Profit Before Tax otherwise the tax is zero This produces a zero tax figure when the Profit Before Tax is in fact a loss This example should teach you that spreadsheet models even if they behave correctly in most circumstances may break down under special conditions 8 Copy the new formula from E6 into D6 to B6 by dragging the cell handle backwards Graphs and Charts When plotting a graph it s easiest to first select the data to be plotted Include the column and row headings if there are any as these are used for the category x and value y names Tip To select non adjacent areas you first drag through one area and then use control select hold down lt Ctrl gt as you drag through further
27. o F1 column F will be used shortly 5 Click on the Merge and Center button on the right in the Alignment group The title will now appear in the centre of the selected range even though it is still stored in cell A1 Incidentally cells B1 to F1 no longer exist To undo a merge and centre 6 Check the merged cell is the active cell then click on the Merge and Center button again Finally the year headings would look better if they were separated from the main heading Most users would add extra rows to achieve this but a better answer is to increase the row height 7 Position the mouse cursor over the division between the Row 2 and Row 3 headings on the left just like you did when changing the width of the columns 8 Hold down the mouse button and drag the division down a row 9 Release the mouse button and the row height will be increased Functions vr Insert Function AutoSum joo Excel has hundreds of built in functions which as in mathematics are denoted by using brackets after the function name These can be seen via the Insert Function button on the Formula Bar 1 Move to an empty cell and click on the Insert Function button 2 From Or select a category choose All 3 Scroll down the Select a function list to get an idea of what s provided Note that information about any function selected is given at the foot of the Insert Function Window while additional help is available via the Help on this function link Don t
28. of the sheet tabs The mouse cursor should also be visible This can have several different shapes some of which you will be meeting later in the course Within the cells it appears as an outlined cross Getting Help Help E As with all the other Microsoft Office software a quick link to the help system is provided by the Microsoft Office Excel Help button to the right of the Ribbon tabs The lt F1 gt key also loads the help system On IT Services PCs a Get Started tab is provided on the Ribbon If you already know Excel 2003 this shows you how to get to the equivalent command in Excel 2007 Moving Around the Worksheet Various keys or key combinations can be used to move the active cell around the worksheet These include lt Enter gt lt Tab gt lt Home gt and the lt arrow gt keys take care that lt Scroll Lock gt is not turned on as this affects the arrow keys You can also move directly to a cell using the mouse 1 Using the mouse point to any cell other than A1 2 Click on the left mouse button to make this the active cell note that the name of the cell in the Name Box has changed as have the orange brown row column indicators 3 Move the active cell around the screen try pressing the lt arrow gt keys lt Tab gt and lt Shift Tab gt and lt Enter gt and lt Shift Enter gt noting the names of the cells in the Name Box 4 End by making A1 the active cell Tip Pressing lt Ctrl Home gt moves th
29. rstand what is happening Note You can display all the formulae rather than the results by pressing lt Ctrl gt the key in the top left corner of the keyboard above lt Tab gt Press it again to return to normal Commands Sometimes it is necessary to issue a command to tell the program to do something to the worksheet such as altering its layout or saving it The commands are found on the Ribbon This has several tabs covering different aspects of use Most of the commands in this course are on the Home tab Some commands are also available by clicking the right button on the mouse or through lt Ctrl gt key combinations as shown by the tool tips which appear when you select a button on the Ribbon Changing Column Width Before proceeding further change the layout so there is enough room to fit the whole of Profit After Tax into its column 1 Move the mouse pointer onto the line between the letter A at the top of column A and B at the top of column B the cursor changes becomes a double headed arrow as shown above 2 Hold down the mouse button and drag the column divider to the right note that the current width is displayed as you move the divider set the width to about 20 0 145 pixels 3 Release the mouse button and the column is resized Getting the column width right using this method can be very time consuming To fit the column exactly to the data 4 Move the mouse pointer to the dividing line between the col
30. rt menu 2 Choose All Programs then Microsoft Office followed by Microsoft Office Excel 2007 Tip To create a shortcut to this software on the Desktop right click on Microsoft Office Excel 2007 then choose Send To followed by Desktop create shortcut You ll then be able to load Excel using the icon on the Desktop The Excel Screen You will then be presented with an empty worksheet as below ready to enter your data Qn a gt Booki Microsoft Excel ox ca Home Insert Page Layout Formulas Data Review View Get Started 2 x eee T gt Wo em cle Ss 1 oF p gy imen z AP a aee e m ee E g Fua a F Paste B Z U A B BS BE a a o 549 00 Conditional Format Cell ER Sot amp Find amp v 7 MA ead 3 Formatting as Table v Styles Format lt 27 Filter Select Clipboard Font E Alignment fia Number E Styles Cells Editing Al fe TN a ow oO o m ou ry le a x E 2 O In the top left corner of the screen the Office Button appears Use this to Open or Print your files To the right of this is the Quick Access Toolbar which only has three buttons on it by default Save Undo and Redo To add extra buttons click on the down arrow on the right and choose Customize Quick Access Toolbar Moving right Excel has given your work a name Book1 which can comprise a set of related data and chart sheets This name will change when you save your work in a file at w
31. try inserting any of the functions into your empty cell just note what s available 4 Press lt Esc gt or click on Cancel to close the window 10 Calculating Totals the SUM Function Imagine you want to work out four year totals so that your model looks like this A B C D E i Profitand Loss Profit After Tax 7 000 8 400 9 240 9 702 34 342 2 2008 2009 2010 2011 Totals 3 Income 25 000 30 000 33 000 34 650 122 650 A Costs 15 000 18 000 19 800 20 790 73 590 5 Profit Before Tax 10 000 12 000 13 200 13 860 49 060 6 Tax 3 000 3 600 3 960 4 158 14 718 7 a 1 Typethe heading Totals into cell F2 and press lt Enter gt Next you need to add up the figures across each row One way of calculating this for row 3 would be to use the formula B3 C3 D3 E3 This works but imagine you had twenty items to add up or a thousand Instead you can use a function to work out the value To specify a function you type its name then in brackets the cell or range of cells to which it is to be applied To signify a cell range a colon is used to separate the starting cell from the end cell 2 IncellF3 type SUM B3 E3 press lt Enter gt to carry out the calculation The SUM function and other commonly used ones is also available from the AutoSum button Using this you can calculate the missing total figures in one go 3 Select F4 to F7 4 Click on the
32. umn headings as before 5 Double click on the mouse button Note Column width can also be set via the Format button under Cells on the Home tab of the Ribbon Here Column Width lets you type in a fixed width while AutoFit Column Width is equivalent to double clicking on the column border If cells contain numeric data and the column width becomes too narrow to display the numbers properly then Excel displays H instead The first time you see this you will probably think an error has occurred so it s useful to demonstrate it here 6 Move the mouse pointer to the dividing line separating the column B and C headings 7 Hold down the mouse button and drag the divider to the left set the width to less than 5 0 8 Release the mouse button and should appear in some or all the cells 9 Use the Undo button to restore the column to its original width Changing the Format T o lt 0 00 7 2 00 P gt 0 Accounting Percent L Comma L Increase Decimal L Decrease Decimal Loon 5 The way data ina cell is displayed can be set using a format For example a date could be shown as 25 12 09 or 25 Dec 09 or 25th December 2009 or in various other similar ways The basic information held in the cell is identical however it s up to you how you want it displayed In this next exercise you will add a currency format to your data 1 Change the Income figure in B1 to 24444 then press lt Enter gt The resulting T
Download Pdf Manuals
Related Search