Guided Computer Tutorials Learning Microsoft® Excel® 2007 By Greg BowdenPUBLISHED BY GUIDED COMPUTER TUTORIALS PO Box 311 Belmont, Victoria, 3216, Australia www.gct.com.au © Greg Bowden This product is available in Single or Multi User versions. Single-user versions are for single person use at any particular time, just as a single text book would be used. If you intend to use the notes with multiple students the single user version should be upgraded to the multi-user version. Multi-user versions allow the school or institution to print as many copies as required, or to place the PDF files on the school network, intranet and staff laptops. A certificate of authentication is provided with multi-user versions. Bookmarks provide links to all headings and sub-headings, and individual chapters are provided. First published 2007 ISBN: 1 921217 44 8 (Module 1) 1 921217 45 6 (Module 2) PDF document on CD-ROM Every effort has been made to ensure that images used in this publication are free of copyright, but there may be instances where this has not been possible. Guided Computer Tutorials would welcome any information that would redress this situation.© Guided Computer Tutorials, 2007 C-1 Learning Microsoft Excel 2007 Module 1 Contents Chapter 1: Introduction to Microsoft Excel The Microsoft Excel Screen ....................................................................1-1 Moving the Cursor ..................................................................................1-3 Using the Mouse .............................................................................1-3 Using the Arrow Keys ....................................................................1-3 Using the Scroll Bars ......................................................................1-4 Moving to Specific Cells .........................................................................1-6 The GO TO Command .................................................................1-6 Entering the Cell Reference ..........................................................1-7 Placing Labels on the Spreadsheet ........................................................1-8 Closing the Spreadsheet .........................................................................1-9 Entering Values into the Spreadsheet .................................................1-10 Entering Formulas into the Spreadsheet ............................................1-12 Adding Labels to Formulas ..................................................................1-14 The Value of a Spreadsheet ...................................................................1-15 Other Types of Formulas ......................................................................1-16 Printing Spreadsheets ...........................................................................1-19 Gridlines and Headings ...............................................................1-19 Page Setup .....................................................................................1-19 Print Preview ................................................................................1-20 Erasing the Contents of a Cell .............................................................1-21 Spreadsheets Exercise 1-1 .....................................................................1-23 Spreadsheets Exercise 1-2 .....................................................................1-26 Spreadsheets Assignment 1 ......................................................... 1-27© Guided Computer Tutorials, 2007 C-2 Learning Microsoft Excel 2007 Chapter 2: Formatting Cells Formatting Single Cells ..........................................................................2-2 Formatting Groups of Cells ...................................................................2-2 Formatting Values to Currency .............................................................2-4 Formatting Values to Per Cent ..............................................................2-6 Changing Column Widths .....................................................................2-6 Entering the Required Width .......................................................2-7 Manually Adjusting Column Widths ..........................................2-9 Automatic Column Widths ........................................................2-10 Approaches for Entering Spreadsheets ...............................................2-12 Starting a New Workbook ...........................................................2-12 Adjusting Column Widths ..........................................................2-12 Entering the Labels and Values of the Spreadsheet .................2-13 Entering the Formulas .................................................................2-13 Wrap Text ......................................................................................2-14 Formatting Non-Adjacent Cells .................................................2-16 Underlining Using Borders .........................................................2-18 Entering the Main Headings .......................................................2-20 Centring Across Selection ...........................................................2-20 Setting the Font, Sizes and Colours ...........................................2-22 Adjusting the Row Height ...........................................................2-23 Checking the Spreadsheet ...........................................................2-24 Saving and Printing......................................................................2-24 Spreadsheets Assignment 2 ......................................................... 2-26© Guided Computer Tutorials 20067 C-3 Contents Chapter 3: Further Formatting Formatting the Values .............................................................................3-1 Formatting the Table Headings .............................................................3-2 Formatting the Main Heading ...............................................................3-5 The Format Painter Tool .........................................................................3-6 Adding Borders .......................................................................................3-7 Fill Colours ...............................................................................................3-9 Column Widths .....................................................................................3-10 Saving and Printing ...............................................................................3-10 Spreadsheets Exercise 3 ........................................................................3-11 Printing Formulas .................................................................................3-12 Spreadsheets Assignment 3 ....................................................... 3-15 Chapter 4: Copying Cell Data Fill Right ...................................................................................................4-1 Fill Down ..................................................................................................4-2 Autofill ......................................................................................................4-3 Autofill Options .......................................................................................4-4 Fill Series ..................................................................................................4-5 Filling Common Items ...........................................................................4-6 Clearing Cells With Autofill...................................................................4-8 Absolute and Relative Cell References..................................................4-9 Loading the Prepared Workbook ...............................................4-9 Entering the Formulas .................................................................4-10 The Grocery Store Template ................................................................4-13 Opening the Prepared Workbook ..............................................4-13 Autofilling the Month Headings ................................................4-13 Entering the Month Formulas ....................................................4-14 Copying the Formulas .................................................................4-15 Adding Borders ............................................................................4-16 Entering the Totals Formula .......................................................4-19 Saving and Printing......................................................................4-20 Making Some Changes ................................................................4-20 Spreadsheets Assignment 4 ......................................................... 4-21© Guided Computer Tutorials, 2007 C-4 Learning Microsoft Excel 2007 Chapter 5: Larger Spreadsheets Loading The Prepared Template ...........................................................5-1 Freezing Titles ..........................................................................................5-1 The Horizontal Split Bar ................................................................5-2 The Vertical Split Bar .....................................................................5-3 Removing Split Bars ................................................................................5-5 Inserting Rows or Columns ...................................................................5-6 Editing Cell Contents............................................................................5-10 Sorting ....................................................................................................5-12 Sorting on Labels ..........................................................................5-12 Sorting on Values .........................................................................5-13 Spreadsheets Assignment 5 ......................................................... 5-15 Chapter 6: Using Worksheets Loading a Prepared File ..........................................................................6-1 Saving the Workbook ..............................................................................6-1 Naming a Worksheet ..............................................................................6-2 Copying Data to a New Worksheet .......................................................6-3 Changing the Data in the New Worksheet ..........................................6-5 Copying a Worksheet ..............................................................................6-6 Moving a Worksheet ...............................................................................6-8 Changing the Sheet Tabs Displayed ......................................................6-9 Moving Through Worksheets ..............................................................6-10 Deleting Worksheets .............................................................................6-11 Setting Tab Colours ...............................................................................6-12 The Equation Dump .............................................................................6-13 Copying a Worksheet ..................................................................6-13 Turning on the Formulas ............................................................6-14 Turning on the Headings and Gridlines ...................................6-15 Spreadsheets Assignment 6 ......................................................... 6-16© Guided Computer Tutorials 20067 C-5 Contents Chapter 7: Displaying Data in Charts Loading the Spreadsheet ........................................................................7-1 Creating a Pie Chart ...............................................................................7-1 The Chart Layouts ...................................................................................7-3 The Chart Location .................................................................................7-6 Looking at The Chart ..............................................................................7-7 Adjusting the Size of the Chart ..............................................................7-8 Adjusting the Chart Values ....................................................................7-8 The Chart layout ......................................................................................7-9 The Chart Title ...............................................................................7-9 The Chart Area .............................................................................7-10 Other Chart Sections ...................................................................7-11 Changing the Chart Type .....................................................................7-12 Setting the Legend .................................................................................7-13 Exploding a Slice from the Pie Chart .................................................7-14 Changing Slice Patterns ........................................................................7-16 Looking at Other Chart Types .............................................................7-17 Printing Charts ......................................................................................7-18 Deleting a Chart ....................................................................................7-18 Spreadsheets Assignment 7 ......................................................... 7-19 Chapter 8: More Detailed Charts Creating a Column Chart .......................................................................8-1 The Chart Options ..................................................................................8-2 The Chart Title ...............................................................................8-2 The Axis Titles ................................................................................8-3 Data Labels ......................................................................................8-4 Data Table .......................................................................................8-5 Setting the Chart Location .....................................................................8-6 Looking at the Column Chart ...............................................................8-7 Changing the Plot ..........................................................................8-8 Changing the Axes Format ...........................................................8-9 Altering Column Effects .............................................................8-10 Altering Background Textures ...................................................8-12 Looking at Other Bar and Column Charts ...............................8-13© Guided Computer Tutorials, 2007 C-6 Learning Microsoft Excel 2007 Line Charts .............................................................................................8-15 Altering Line Formats .................................................................8-16 Changing the Axis Scales ............................................................8-18 Labelling Axes ..............................................................................8-19 Custom Charts .......................................................................................8-19 Looking at the Charts ...........................................................................8-20 Comparing Values .................................................................................8-21 Spreadsheets Assignment 8 ........................................................ 8-24 Chapter 9: The Drawing Tools Opening the Prepared Template ...........................................................9-1 Creating the Logo ....................................................................................9-2 Inserting a Text Frame ...................................................................9-2 Adjusting the Text Box Shading ...................................................9-3 Centring the Text .....................................................................................9-5 Importing Graphics.................................................................................9-6 Setting the Graphics Properties .............................................................9-9 Magnifying the Screen ..........................................................................9-11 Printing the Spreadsheet ......................................................................9-12 The Shape Tools .....................................................................................9-12 Creating Shapes ............................................................................9-12 Inserting Word Art ......................................................................9-16 Adding Text to Shapes .................................................................9-18 Fill Effects ......................................................................................9-21 Spreadsheets Assignment 9 ......................................................... 9-25 Module 1 Project EasyTune ............................................................................................... P1-1© Guided Computer Tutorials 20067 C-7 Contents Module 2 Contents Chapter 10: Making Predictions With Excel The Motel Template ..............................................................................10-1 Loading the Template ..................................................................10-1 Calculating the Income From Rooms .......................................10-1 Calculating the Income From Food...........................................10-2 Calculating the Total Daily Income ...........................................10-3 Using the Spreadsheet to Make Predictions .............................10-3 Adjusting the Motel Template .............................................................10-5 The Goal Seek Command ....................................................................10-8 Applying the Goal Seek Command ..................................................10-10 Spreadsheets Exercise 10-1 ................................................................10-11 Spreadsheets Assignment 10 ..................................................... 10-12 Chapter 11: Making Decisions With Excel The IF Command ..................................................................................11-2 Greater Than or Equal To .....................................................................11-5 Exercise 11-1 ..........................................................................................11-6 Completing the Template ............................................................11-6 Displaying the Formulas Used ...................................................11-8 AND and OR .......................................................................................11-10 AND (a,b) ...................................................................................11-10 OR (a,b) .......................................................................................11-12 Exercise 11-2 ........................................................................................11-14 Conditional Formatting......................................................................11-16 Setting a Conditional Format ...................................................11-16 Changing Values .........................................................................11-17 Adding Another Conditional Format .....................................11-18 Removing Conditional Formats ...............................................11-18 Spreadsheets Assignment 11 ..................................................... 11-19© Guided Computer Tutorials, 2007 C-8 Learning Microsoft Excel 2007 Chapter 12: Lookup Commands Interest Rate Calculations ....................................................................12-1 Loading the Template ..................................................................12-1 Calculating the Interest Due .......................................................12-2 Income Tax Calculations ......................................................................12-4 Loading the Template ..................................................................12-4 The Base Tax Calculation ............................................................12-5 The On Income Calculation .......................................................12-5 The Extra Income Calculation ....................................................12-6 The Tax Rate Calculation ............................................................12-6 The Extra Tax Calculation ...........................................................12-7 The Total Tax Calculation ...........................................................12-7 The Net Income Calculation .......................................................12-8 Using the Tax Table ......................................................................12-8 The Equation Dump ....................................................................12-9 Spreadsheets Assignment 12 ..................................................... 12-11 Chapter 13: Creating Sales Invoices Entering the Labels of the Invoice .......................................................13-1 Adding Borders .....................................................................................13-2 Shading the Table Headings .................................................................13-3 Creating the Company Title ................................................................13-3 Entering the Calculations .....................................................................13-5 Initial Settings ...............................................................................13-5 Calculating the Amount ..............................................................13-5 Turning Zero Values Off .............................................................13-6 Calculating the Sub-Total ...........................................................13-7 Calculating the GST .....................................................................13-8 Calculating the Overall Total ......................................................13-8 Inserting Today’s Date .................................................................13-9 Checking the Invoice .................................................................13-10 Using the Invoice .................................................................................13-10© Guided Computer Tutorials 20067 C-9 Contents Adding an Inventory to the Invoice ..................................................13-10 Loading the Template ................................................................13-11 Looking up the Description ......................................................13-11 Looking up the Unit Price .........................................................13-12 Setting the Blank Invoice ..........................................................13-13 Setting the Print Area ................................................................13-14 Saving as a Read-Only File .......................................................13-14 Using the Stock Inventory .........................................................13-15 Exercise 13-1 ........................................................................................13-16 Loading the Invoice ...................................................................13-16 Naming the Stock Table ............................................................13-16 Looking up the Description ......................................................13-17 Looking up the Unit Price .........................................................13-18 Turning Off Zero Values ...........................................................13-19 Calculating the Amount and Sub-Total ..................................13-19 Calculating the GST ...................................................................13-21 Calculating the Total ..................................................................13-22 Setting the Print Area ................................................................13-22 Data Validation ...........................................................................13-23 Saving as a Read-only File .........................................................13-26 Using the Invoice ........................................................................13-26 Displaying the Formulas ...........................................................13-27 Spreadsheets Assignment 13 ..................................................... 13-28 Chapter 14: Sharing Data Between Files Naming Single Cells ..............................................................................14-1 Entering Formulas Using Names ........................................................14-3 Naming Groups of Cells .......................................................................14-4 Loading the Template ..................................................................14-4 Naming the Cells ..........................................................................14-4 Entering the Formula ..................................................................14-6 Another Advantage of Named Cells ...................................................14-7 Deleting Names .....................................................................................14-7© Guided Computer Tutorials, 2007 C-10 Learning Microsoft Excel 2007 Linking Spreadsheets ............................................................................14-8 Loading the Outdoors Template ................................................14-9 Entering the Formulas .................................................................14-9 Naming the Cells ........................................................................14-11 Setting up the Home Department File .............................................14-12 Setting up the Electrical Department File ........................................14-13 The Dependent Spreadsheet ..............................................................14-14 Loading the Office Template .....................................................14-14 The Net Profit Formula ............................................................14-14 The Percentage Profit Formula .................................................14-15 The Totals Formula ....................................................................14-16 Linking the Spreadsheets ..........................................................14-16 Changing Values in a Support Spreadsheet .....................................14-18 Looking at all the Files ........................................................................14-19 Spreadsheets Assignment 14 ..................................................... 14-21 Chapter 15: Using Macros and Buttons Using a Simple Discount Table ............................................................15-1 Opening a Sample File .................................................................15-1 Setting a 5% Discount Macro .....................................................15-1 Setting a 10% Discount Macro ...................................................15-4 Setting a No Discount Macro .....................................................15-5 Using the Macros .........................................................................15-6 Saving the File as a Macro-Enabled File .............................................15-7 Assigning Buttons to Represent Macros ............................................15-8 Drawing the Shape .......................................................................15-8 Formatting the Shape ..................................................................15-8 Setting the Button Properties .....................................................15-9 Assigning a Macro to the Shape ...............................................15-10 Adding Buttons for the Other Macros ....................................15-11© Guided Computer Tutorials 20067 C-11 Contents Other Macros and Buttons .................................................................15-13 The New Items Macro and Button ...........................................15-13 The Sort Items Macro and Button ............................................15-17 Looking at the Macro Steps................................................................15-20 Deleting a Macro .................................................................................15-21 The Developer Tab ..............................................................................15-22 Spreadsheets Assignment 15 ..................................................... 15-24 Chapter 16: Payroll Systems Loading the Payroll Template ..............................................................16-1 Completing the Payroll Worksheet .....................................................16-1 The Tax Calculation ..............................................................................16-3 Calculating the Net Pay ........................................................................16-4 Printing the Payroll ...............................................................................16-4 The Pay Advice Worksheet ...................................................................16-5 Naming the Payroll Cells.............................................................16-5 Sorting The Payroll ......................................................................16-6 Completing the Name Section of the Pay Advice ....................16-7 Completing the Pay Advice Table ..............................................16-9 Completing the Superannuation Section ................................16-11 Printing the First Pay Advice ....................................................16-12 Printing the Pay Advice for Other Employees .......................16-12 Using the Payroll System ....................................................................16-13 Adding Macros and Buttons ..............................................................16-14 The first Employee Macro .........................................................16-14 Setting a Button ..........................................................................16-15 Creating Buttons for the Other 3 Employees .........................16-17 The 3 Remaining Employee Macros ........................................16-18 Saving the File as a Macro-Enabled Workbook .....................16-18 Spreadsheets Assignment 16 ..................................................... 16-19© Guided Computer Tutorials, 2007 C-12 Learning Microsoft Excel 2007 Chapter 17: Financial Applications An Electronic Cheque Book ................................................................17-1 Opening the Prepared Template ................................................17-1 Completing the Formulas ...........................................................17-1 Using the Cheque Book ...............................................................17-3 The Home Loan Simulator ...................................................................17-3 Opening the Prepared Template ................................................17-4 Entering the Initial Values ..........................................................17-4 Naming the Cells ..........................................................................17-5 Calculating the Monthly Interest ...............................................17-5 Calculating the Number of Repayments ..................................17-6 Setting the Monthly Repayment ................................................17-6 Completing the Months Column ...............................................17-8 The Start Balance Column ..........................................................17-9 The End Balance Column .........................................................17-10 Turning Zero Values Off ...........................................................17-11 The Monthly Payment Column ................................................17-11 The Principal Remaining Column ...........................................17-12 The Total Repaid Column .........................................................17-12 Completing the Start Balance Column ...................................17-13 Adding a Border After Each Year .............................................17-15 Using the Simulator ...................................................................17-16 Spreadsheets Assignment 17 ..................................................... 17-17 Chapter 18: Date Calculations Creating a Library Book Overdue System .........................................18-1 Opening the Prepared Template .........................................................18-1 Naming the Book List ...........................................................................18-2 Looking up the Book Title and Author ..............................................18-3 Calculating the Return Date ................................................................18-4 The Overdue Calculation .....................................................................18-5 Filling the Formulas ..............................................................................18-6 Using the Library System .....................................................................18-7 Creating the Equation Dump ..............................................................18-8 Spreadsheets Assignment 18 ....................................................... 18-9© Guided Computer Tutorials 20067 C-13 Contents Chapter 19: Useful Tools Help Features .........................................................................................19-1 The Save Options ...................................................................................19-2 Style and Table Formats ........................................................................19-4 Custom Styles .........................................................................................19-7 The Currency Button ..........................................................................19-10 Spell Checking .....................................................................................19-12 Setting the Enter Key ..........................................................................19-12 Module 2 Project Quantum Electronics ........................................................................... P2-1© Guided Computer Tutorials, 2007 C-14 Learning Microsoft Excel 2007© Guided Computer Tutorials 2007 1-1 Chapter 1 Introduction to Microsoft Excel A spreadsheet is a computer program that turns the computer into a very powerful calculator. Headings and comments can be entered along with detailed formulas. The spreadsheet screen is divided into ROWS and COLUMNS; the intersection between a row and a column is termed a CELL. Usually only a small section of the overall spreadsheet is displayed on the screen. The Microsoft Excel Screen 1 Load Microsoft Excel and you should receive the following screen. Read through its labelled sections. Column Headings Row Headings Cell Cursor Quick Access Toolbar Worksheet Tabs The Ribbon Cells Zoom and Page Views The Office Button© Guided Computer Tutorials 2007 1-2 Learning Microsoft Excel 2007 NOTE: At cell A1 you should see a rectangular bar. This bar is called the CELL CURSOR and it can be positioned over any storage cell. When you first start up Microsoft Excel the cell cursor will be positioned at cell A1. 2 Each Microsoft Excel file is considered to be a WORKBOOK hence the title: BOOK1 MICROSOFT EXCEL 3 Each WORKBOOK can have numerous sheets, called WORKSHEETS. 4 The letters ‘A’, ‘B’, ‘C’, etc. across the top of the sheet label the spreadsheet COLUMNS. 5 The numbers down the left edge of the sheet label the spreadsheet ROWS. 6 Storage CELLS are found at the intersection of rows and columns. The intersection of row 1 and column A is read as A1.© Guided Computer Tutorials 2007 1-3 1 Introduction to Microsoft Excel Moving the Cursor The cursor can be moved in a number of different ways. A Using the Mouse 2 Try clicking the mouse button with the cursor over cell B4, then cell D2. B Using the Arrow Keys 2 Try the other ARROW KEYS. 1 Move the mouse so that the pointer (which is a solid cross-hair) is over cell C3 and click the left mouse button to move the cursor to this new cell. 1 Press the DOWN ARROW KEY a few times. The cursor should move straight down.© Guided Computer Tutorials 2007 1-4 Learning Microsoft Excel 2007 C Using the Scroll Bars To move over larger distances, the HORIZONTAL and VERTICAL SCROLL BARS are used. The rectangular box in each bar is the SCROLL BOX. 1 Move the pointer over the VERTICAL SCROLL BOX, hold down the left mouse button and drag the mouse so that the bar moves down to the bottom of the column, then release the button. 2 Hold down the SHIFT key and drag the scroll bar down. This will allow you to scroll further down the spreadsheet. Set the scroll bar, now a small box, to the centre of the VERTICAL SCROLL BAR. 3 Click on the SCROLL DOWN ARROW at the bottom of the VERTICAL SCROLL BAR a few times and more rows will be brought into view. © Guided Computer Tutorials 2007 1-5 1 Introduction to Microsoft Excel NOTE: There are over a million rows in the spreadsheet. The SCROLL DOWN ARROW at the bottom of the VERTICAL SCROLL BAR can be used to display more rows or you can press the PAGE DOWN key on the keyboard. NOTE: i There are over 1000 columns in the spreadsheet. ii The HORIZONTAL SCROLL BAR can also be used to bring more columns into view. NOTE: i To move the cursor over small distances, use the MOUSE or the ARROW KEYS. The TAB key can be used to move the cursor one cell to the right. SHIFT+TAB moves the cursor one cell to the left. ii To move the cursor over larger distances, use the SCROLL BARS, the SCROLL ARROWS or the PAGE UP and PAGE DOWN keys. iii It might be helpful to think of the screen as a window that slides across the top of a very large sheet of cells. 4 Click on the SCROLL RIGHT ARROW at the right of the HORIZONTAL SCROLL BAR to bring more columns into view. 5 Use the SCROLL BARS to set the screen to its original view with cell A1 at the top left corner. © Guided Computer Tutorials 2007 1-6 Learning Microsoft Excel 2007 Moving to Specific Cells Sometimes you will wish to move directly to a specific cell. This is achieved by using the GO TO command which is found in the FIND & SELECT icon in the HOME tab of the RIBBON, or by entering the cell reference directly in the CELL REFERENCE BOX. A The GO TO Command 1 Position the cell cursor at cell D10. NOTE: The F5 key can also be pressed to display the GO TO dialogue box. You can also press CTRL+G. 6 Try using the GO TO command (or the F5 key) to move the cursor to the following cells: E95, AC276, AL490, A1. 2 In the HOME tab of the RIBBON click on the FIND & SELECT icon in the FIND group and select GO TO. 3 You should receive the GO TO dialogue box. 4 Enter: A1 in the REFERENCE box. 5 Press the key, or click on OK and the cursor will be positioned at cell A1.© Guided Computer Tutorials 2007 1-7 1 Introduction to Microsoft Excel B Entering the Cell Reference 3 Try moving to cells: C12, AB16, A1 using the CELL REFERENCE (or NAME) BOX. 1 Move the pointer over the CELL REFERENCE BOX (which is also called the NAME BOX) just above COLUMN A’s heading and click the mouse button. The A1 cell reference should highlight. 2 Enter G6, press the key and the cursor should move to cell G6.© Guided Computer Tutorials 2007 1-8 Learning Microsoft Excel 2007 Placing Labels on the Spreadsheet You will now learn how to enter letters and numbers onto the spreadsheet. We will start by entering a label (entries that contain letters are called labels). 1 Move the cursor to cell A1, if it isn’t there already. NOTE: i If you make a mistake while entering text, you can delete using the BACKSPACE (or DELETE) key, click on the CANCEL symbol , or press the ESC (Escape) key and re-enter the text. ii If you wish to change the contents of a cell, simply position the cursor over that cell and re-enter the required text. 2 Enter the label: Computers and click on the ENTER button next to the CELL REFERENCE BOX. 3 The word ‘Computers’ should now appear in cell A1 and in the FORMULA BAR at the top of the sheet. 4 Move the cursor to cell D3 and enter the label: used and click on the ACCEPT ENTRY button or press the key. © Guided Computer Tutorials 2007 1-9 1 Introduction to Microsoft Excel 5 Try entering the following labels in the given cells: A5: properly B1: can B3: use A3: to D1: fun C3: if C1: be Closing the Spreadsheet Once you have finished using a workbook, it can be closed. 1 Click on the OFFICE BUTTON at the top left of the screen and select CLOSE. ALT+F can also be pressed and you will receive a warning dialogue box asking whether you wish to save the file. 2 In this case we do not need to save the spreadsheet so click on the NO button and the file will be closed.© Guided Computer Tutorials 2007 1-10 Learning Microsoft Excel 2007 Entering Values into the Spreadsheet A new worksheet will be started. 3 You can also click in the CLOSE WINDOW button at the top right of the spreadsheet screen to close the file. 1 Click on the OFFICE button and select NEW. 2 Leave BLANK WORKBOOK selected and click on CREATE.© Guided Computer Tutorials 2007 1-11 1 Introduction to Microsoft Excel NOTE: When you press the key the cursor moves down to the next cell. 3 You can add buttons from the OFFICE BUTTON to the QUICK ACCESS TOOLBAR by clicking on the CUSTOMISE QUICK ACCESS TOOLBAR arrows and selecting the required button. 4 Enter the value: 20 in cell A1 and press the key. Notice that Microsoft Excel automatically recognises this as a value and places it at the right of the cell. Labels (text) are placed at the left of a cell. 5 Move the cursor to F4 and enter the value: 54 6 Use this procedure to enter the following values in the respective cells: C12: 600 A5: 8900 D10: 24000© Guided Computer Tutorials 2007 1-12 Learning Microsoft Excel 2007 NOTE: Click on the ENTER button in the FORMULA BAR if you wish to stay at the same cell, or simply enter a value or label and use the MOUSE, TAB key or ARROW keys to move to the next required cell. Entering Formulas into the Spreadsheet Formulas allow Microsoft Excel to do calculations. 1 Close the current workbook without saving and start a NEW workbook. NOTE: To enter a formula in Microsoft Excel the = sign must first be pressed. 2 Enter 50 in cell A1 and 25 in cell B1. 3 Move the cursor to cell D1. 4 A formula will be entered to add the contents of cells A1 and B1, enter the formula: =A1+B1 5 Click on the ACCEPT ENTRY button and, if you have done things correctly, 75 should appear in cell. D1.© Guided Computer Tutorials 2007 1-13 1 Introduction to Microsoft Excel NOTE: You should now have 4 numbers in column D (75, 25, 1250 and 2), but you may have forgotten what each number represents. 6 Move the cursor to cell D2 and enter the formula: =A1 -B1 This will subtract the two numbers. 7 In cell D3 enter: =A1 * B1 to multiply the two numbers. 8 In cell D4 enter: =A1 /B1 to divide A1 by B1.© Guided Computer Tutorials 2007 1-14 Learning Microsoft Excel 2007 NOTE: We can tell that the cell contents are formulas, not text, because there is an = sign before the letters. Adding Labels to Formulas At the moment the numbers in column D have little meaning. Let’s add some explanations to them. NOTE: No calculation is made with this entry because an = sign was not entered at the start of the entry. 9 Use the mouse to click in each of the 4 cells in column D. The cell coordinates and formula are listed in the FORMULA BAR just above the COLUMN HEADINGS. 1 Move the cursor to cell C1 and enter: A1 + B1 =© Guided Computer Tutorials 2007 1-15 1 Introduction to Microsoft Excel The Value of a Spreadsheet Now let’s see the value of a spreadsheet: 1 Move the cursor to cell A1. 2 Enter 40, BUT DO NOT PRESS THE KEY. 4 Try changing the contents of B1 to 70, then 200 and observe the changes in column D. 2 Enter the following labels into the other cells in column C: at cell C2: A1 -B1 = at cell C3: A1 * B1 = at cell C4: A1 /B1 = 3 Watch the values in COLUMN D as you press the key. Notice that each number changes as the formulas recalculate to account for the new entry.© Guided Computer Tutorials 2007 1-16 Learning Microsoft Excel 2007 Other Types of Formulas Microsoft Excel has built in functions that allow sums and averages to be calculated (as well as many other functions). 1 Close the workbook without saving, start a NEW BLANK workbook. 2 Enter the labels and values in the indicated cells. 3 Move the cursor to cell B11. 4 Enter the formula: =SUM(B5:B9) and click on the ACCEPT ENTRY button.© Guided Computer Tutorials 2007 1-17 1 Introduction to Microsoft Excel NOTE: i Formulas should not have any spaces in them. ii You should have the sum of the cells (341) placed in the cell B11. NOTE: The AVERAGE function tells Microsoft Excel to add the numbers between B5 and B9 then divide by the number of values added. =SUM(B5:B9) Start adding from this cell Finish adding from this cell 5 The formula tells Excel to add the contents of the cells from B5 to B9, including all the cells in between them. The ‘:’ is read as ‘to’. 6 Move the cursor to cell B13 and enter the formula to average the marks: = AVERAGE(B5:B9) 7 When you press the key you should receive the value 68.2.© Guided Computer Tutorials 2007 1-18 Learning Microsoft Excel 2007 10 Click on the SAVE button in the QUICK ACCESS TOOLBAR and save the file in your STORAGE folder under the file name: Chapter 1 8 Change the value in cell B7 to 75 and watch the TOTAL and AVERAGE cells change accordingly. 9 Make the following changes and observe the effect on the TOTAL and AVERAGE: ENGLISH 48 MATHS 80 GERMAN 92 HISTORY 55© Guided Computer Tutorials 2007 1-19 1 Introduction to Microsoft Excel Printing Spreadsheets Before printing the spreadsheet you should check that the page settings are correct. A Gridlines and Headings The gridlines and row and column headings that make up the spreadsheet should not be printed. B Page Setup The ORIENTATION of the sheet and the PAPER SIZE should be checked. 1 Open the PAGE LAYOUT tab in the RIBBON. 2 In the SHEET OPTIONS group check that the PRINT options for GRIDLINES and HEADINGS are turned off. 1 Click on the ORIENTATION icon in the PAGE LAYOUT tab of the RIBBON and select PORTRAIT. This sets the spreadsheet to print on the vertical side of the paper.© Guided Computer Tutorials 2007 1-20 Learning Microsoft Excel 2007 C Print Preview It is a good idea to look at a screen preview of what the printout will look like. You may see some problems with your workbook that you wish to fix before you print. 2 Click on the SIZE icon in the PAGE LAYOUT tab and select the PAPER SIZE that your printer uses. It is probably A4. 1 Click on the OFFICE BUTTON, highlight PRINT and select PRINT PREVIEW.© Guided Computer Tutorials 2007 1-21 1 Introduction to Microsoft Excel 4 If everything looks correct, click on the PRINT icon in the RIBBON to print the worksheet. If adjustments are required, click on the CLOSE PRINT PREVIEW icon in the RIBBON, make the adjustments, then print the worksheet. NOTE: Check over your printout and make any corrections. If any corrections were required, print another copy. Erasing the Contents of a Cell If you wish to erase the contents of a cell and not replace it with anything else, the CLEAR command can be used, or use the DELETE (or DEL) key. 2 Click on the ZOOM icon in the RIBBON to take a closer look at your worksheet. 3 This is how the workbook will print. 1 Position the cursor over cell B13 then click on the CLEAR icon in RIBBON and select CLEAR CONTENTS.© Guided Computer Tutorials 2007 1-22 Learning Microsoft Excel 2007 2 If you need to clear more than one cell, the required cells must first be highlighted before CLEAR (or DELETE) is selected. NOTE: The DELETE key can be used to clear the cells. If you use the BACKSPACE key instead of DELETE, just the first cell is cleared and you will receive the ACCEPT ENTRY or CANCEL buttons in the FORMULA BAR. 3 Use the mouse to position the cursor at cell B5. Hold down the mouse button and drag the mouse so that the cursor is moved to B9. then click on the CLEAR icon in HOME tab and select CLEAR CONTENTS.© Guided Computer Tutorials 2007 1-23 1 Introduction to Microsoft Excel Spreadsheets Exercise 1-1 In this Exercise you will enter a spreadsheet that allows you to keep track of the total number of students in a school. 1 Load Microsoft Excel, or close the current file and start a NEW BLANK workbook. 2 Enter the headings in the indicated cells. 3 Move the cursor to cell B5, press the apostrophe key ‘ (this sets the cell as a label) then press the -sign 12 times and press the key. An underline will be created. 4 Move the cursor to cell C5 and repeat step 3 to create an underline in that cell.© Guided Computer Tutorials 2007 1-24 Learning Microsoft Excel 2007 5 Enter the Year Level numbers and Student Numbers in these cells. 6 Enter the TOTAL label in cell B13 and add underlines in cells B12, C12, B14 and C14.© Guided Computer Tutorials 2007 1-25 1 Introduction to Microsoft Excel 8 Two students have left from Year 12, so move the cursor to C11, enter 81 and press the key. The total should adjust to 811. 9 Save the workbook under the file name: SS Exercise 1-1 10 Check that the PAGE SETUP is correct for your printer (see page 1.19). 11 Use PRINT PREVIEW to check the workbook and make any necessary adjustments. 12 Print the workbook and ask your teacher to check it. NOTE: Don’t worry if the underlines do not fill the columns when printed. You will learn a more effective way of underlining in the next chapter. 7 Move the cursor to cell C13 and enter a formula that will add the number of students in the school: =SUM(C6:C11)© Guided Computer Tutorials 2007 1-26 Learning Microsoft Excel 2007 Spreadsheets Exercise 1-2 Start a NEW BLANK workbook and enter the following labels and values showing the monthly rainfall for 3 years: NOTE: The double underline for the Rainfall heading can be created by using the = sign. Remember to press the ‘ key first. 1 Use formulas to complete the COLUMN E, for example: = AVERAGE(B8:D8) 2 Save the workbook under the file name: SS Exercise 1-2 3 Check that the PAGE SETUP is correct for your printer (see page 1.19). 4 Use PRINT PREVIEW to check the workbook and make any necessary adjustments. 5 Print the workbook and ask your teacher to check it.© Guided Computer Tutorials 2007 1-27 1 Introduction to Microsoft Excel Spreadsheets Assignment 1 You have been placed in charge of a fund-raising drive conducted over a long weekend. You are required to produce a summary of the funds raised from the particular activities. Start a new workbook and enter the following template, but DO NOT enter the ****** Ask your teacher to widen the first column for you so that the headings will fit, or set the font size for the whole spreadsheet to 8 point. 1 Use formulas to complete the sections marked with *****. Each formula should add either a row or column of numbers. 2 Save the workbook under the file name: SS Assignment 1 3 Check that the PAGE SETUP is correct for your printer (see page 1.19). 4 Use PRINT PREVIEW to check the workbook and make any necessary adjustments. 5 Print the workbook and make any further adjustments. 6 Reprint a final copy, if necessary, and submit it for marking.