Intro to Spreadsheets Workbook v2010

Views:
 
Category: Entertainment
     
 

Presentation Description

No description available.

Comments

Presentation Transcript

PowerPoint Presentation:

Introduction to Spreadsheets Using Microsoft Excel 2010 John Wheatley College School of Creative Technologies August 2012 Introduction to Spreadsheets Page 1 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

When you have completed this workbook, you will be able to  Create a Spreadsheet  Save a spreadsheet  Save a spreadsheet under a different name  Open an existing spreadsheet  Insert data  Delete data  Copy or cut and paste data  Perform arithmetical calculations  Use basic functions (sum and average) on ranges of data  Print spreadsheet Introduction to Spreadsheets Page 2 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

Please ask your lecturer for a copy of the Microsoft Excel Students’ Reference Book Microsoft Excel is an electronic spreadsheet. You can use it to organize your data into rows and columns. You can also use it to perform calculations quickly. This tutorial teaches Microsoft Excel basics and was created for the computer beginner. Introduction to Spreadsheets Page 3 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

HOW TO OPEN MICROSOFT EXCEL 2010 Click on:  Start  All Programs  Microsoft Office  Microsoft Excel 2010 The Microsoft Excel Window The Microsoft Excel 2010 window appears and your screen looks similar to the one shown here. Note: Your screen will probably not look exactly like the screen shown. Introduction to Spreadsheets Page 4 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

When you click the File button, a menu  appears. You can use the menu to create a new file, open an existing file, save a file, and perform many other tasks. The Title Bar Next to the Quick Access toolbar is the Title bar. On the Title bar, Microsoft Excel displays the name of the workbook you are currently using. At the top of the Excel window, you should see “Book1 - Microsoft Excel” or a similar name. Introduction to Spreadsheets Page 5 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

The Ribbon In Microsoft Excel 2010, you use the Ribbon to issue commands to tell the computer what to do. At the top of the Ribbon are several tabs (see Ribbon above). Clicking a tab displays several groups and within each group are buttons (see below). You will click buttons to issue commands or to access menus and dialog boxes. Throughout this book you will be told which tab and button to use. Introduction to Spreadsheets Page 6 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

Worksheets Microsoft Excel consists of worksheets and each worksheet contains columns and rows. The columns are lettered A to Z and then continuing with AA, AB, AC and so on; the rows are numbered 1 to 1,048,576. The combination of a column and a row make up a cell address. For example, the cell located in the upper-left corner of the worksheet is cell A1, meaning column A, row 1. Cell E10 is located under column E on row 10. You enter your data into the cells on the worksheet. Note: If you’re not sure about any of the above, ask your lecturer to help you Introduction to Spreadsheets Page 7 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

1 PROJECTED BUDGET A B C D E F 2 QTR1 QTR2 QTR3 3 Income (£) (£) (£) (£) 4 High St Store 5400 5650 5300 5 Duke St Store 4180 4920 4575 6 Total Income Create a Spreadsheet The first spreadsheet you will create represents the projected quarterly sales figures for a small company.  Create the spreadsheet below entering all data exactly as shown If you enter any data incorrectly click in the appropriate cell, press the delete key on the keyboard and re-type the data. The main heading PROJECTED BUDGET overlaps cells A1 and B1 Introduction to Spreadsheets Page 8 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

Save the spreadsheet:  Click on the File button  Click on Save As  Choose your memory stick under the Save In box  Name the file Probudg You can improve the appearance of the spreadsheet by making headings bold or realigning them as follows: 1 Embolden the main heading:    Activate cell A1 by clicking in it  Select the Bold icon from the Font group of the Home tab  Change the point size to 16. Introduction to Spreadsheets Page 9 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

2 Embolden and Right Align column headings:   Highlight the range C2:F3 by clicking in cell C2 and dragging the mouse pointer to cell F3  Embolden the cells by clicking on the Bold icon in the Font group of the Home tab  Select the Align Right icon from the Alignment group of the Home tab  The column headings should now be Bold and Right Aligned 3 Embolden the Total row:  Highlight all of Row 6 by clicking on the row number  Click on the Bold icon in the Font group of the Home tab Introduction to Spreadsheets Page 10 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

A B C D E F 1 PROJECTED BUDGET 2 QTR 1 QTR2 QTR3 TOTAL 3 Income 4 High St Store (£) 5400 (£) 5650 (£) 5300 (£) 5 Duke St Store 4180 4920 4575 6 Total Income Your spreadsheet should now look like this: 4 Re-save the spreadsheet  Click the Save icon  Introduction to Spreadsheets Page 11 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

Calculations Calculations in Excel are carried out by typing a formula into a cell. Formulae always start with an equals sign (=) and may use the following, depending on the calculation: + plus example =b3+b4 - minus example =b3-b4 * multiply example =b3*b4 / divide example =b3/b4 Example: If you want to add cells B3 and B4 together, type the formula =B3+B4 in the cell where you want the answer to go. Exercises on the following pages will give you practice in adding, subtracting, multiplying and dividing cells. Introduction to Spreadsheets Page 12 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

CALCULATION EXERCISES Note: Click the Enter key (circled below) after typing each formula. 1. Addition: Remember: Click the Enter key after each formula Calculate the Qtr1 Total for both stores as follows:  Activate cell C6 by clicking in it and then type =c4+c5 Calculate the Qtr2 Total for both stores as follows:  Activate cell D6 by clicking in it and then type =d4+d5 Calculate the Qtr3 Total for both stores as follows:  Activate cell E6 by clicking in it and then type =e4+e5 Introduction to Spreadsheets Page 13 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

A PROJECTED BUDGET B C D E F QTR1 QTR2 QTR3 Income (£) (£) (£) (£) High St Store Duke St Store 5400 5650 4180 4920 5300 4575 Total Income 9580 10570 9875 Expenditure 4500 4350 4600 3200 3750 3400  Check your spreadsheet: A B C D E F 1 PROJECTED BUDGET 2 QTR QTR2 QTR3 TOTAL 1 3 Income (£) (£) (£) (£) 4 High St Store 5400 5650 5300 5 Duke St Store 4180 4920 4575 6 Total Income 9580 10570 9875  Key in the new Expenditure figures for High Street and Duke Street stores shown below 1 2 3 4 5 6 7 8 9 High St Store 10 Duke St Store  Key in the words Total Expenditure in cell A11 and make the text bold  Key in the words Total Savings in cell A13 and make the text bold Introduction to Spreadsheets Page 14 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

1 A PROJECTED BUDGET B C D E F 2 QTR1 QTR2 QTR3 3 Income (£) (£) (£) (£) 4 5 High St Store Duke St Store 5400 4180 5650 4920 5300 4575 6 Total Income 9580 10570 9875 7 8 Expenditure 9 High St Store 4500 4350 4600 10 Duke St Store 3200 3750 3400 11 Total Expenditure 7700 8100 8000 12 13 Total Savings  Calculate the Total Expenditure for the 2 stores using the addition formula  Check your spreadsheet: Introduction to Spreadsheets Page 15 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

1 A PROJECTED BUDGET B C D E F 2 QTR1 QTR2 QTR3 3 Income (£) (£) (£) (£) 4 High St Store 5400 5650 5300 5 Duke St Store 4180 4920 4575 6 Total Income 9580 10570 9875 7 8 Expenditure 9 High St Store 4500 4350 4600 10 Duke St Store 3200 3750 3400 11 Total Expenditure 7700 8100 8000 12 13 Total Savings 1880 2470 1875 2. Subtraction The Total Savings for the 3 quarters can now be calculated as well. This time instead of calculating each quarter individually use the Autofill command as below:  Activate cell C13 and type =c6-c11  Move the mouse pointer to the small black box at the bottom right-hand corner of the selected cell until the mouse pointer changes to a black cross  called the AutoFill command  Click and drag the mouse pointer across to cell E13  Check and save your spreadsheet Introduction to Spreadsheets Page 16 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

3. Preview the Spreadsheet Before printing a spreadsheet it is good practice to preview it first to see what is going to be printed. You can then make changes to the spreadsheet layout if necessary.  Select the File Button  Click the Print button the print preview can be seen in the left hand pane. SELF TEST Explain why the print preview option should always be used prior to printing any spreadsheet. _______________________________________________ _______________________________________________ _______________________________________________ 4. Print the Spreadsheet  Select the File Button  Choose Print. Then Print again Introduction to Spreadsheets Page 17 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

5. Amending a spreadsheet 5.1. Delete existing data  Activate cell C5  Press the Delete key on the keyboard  The contents of cell C5 should now be clear 5.2. Enter new data  Activate cell C5  Enter the value 4380  Check that all formulae have produced the correct results  Compare the spreadsheet against your original printout 5.3. Print  Preview and print the amended spreadsheet  Save the spreadsheet SELF TEST By changing the value of C5 you automatically changed the result of C6. Explain why _________________________________________ _________________________________________ _________________________________________ Introduction to Spreadsheets Page 18 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

The figures for Quarter 4 are now available. Amend the spreadsheet to include this data as follows: Use instructions below to insert a new column between Column E and Column F (to allow for this new data).  Click anywhere in Column F  Under the Home tab, click Insert from the Cells group (see below)  Select Insert Sheet Columns   A new column to the left of Column F will be inserted Introduction to Spreadsheets Page 19 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

Copy data using the AutoFill Command  Insert a new column heading QTR4 as follows:  Highlight cells E2 to E3  Move the mouse pointer to the small black box at the bottom right-hand corner of the selected cell until it changes to a black cross  and drag to cell F3 Cell F2 will now read QTR4 and Cell F3 will read (£)  Enter the following figures for QTR4: 5800 in F4 4700 in F5 4760 in F9 3700 in F10  Using the Autofill(also known as fill right) command, replicate the formulae as follows: From cell E6 into cell F6 From cell E11 into cell F11 From cell E13 into F13 Check your figures over the page and save the spreadsheet. Introduction to Spreadsheets Page 20 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

A B C D E F G 1 PROJECTED BUDGET 2 QTR1 QTR2 QTR3 QTR4 TOTAL 3 Income (£) (£) (£) (£) (£) 4 5 High St Store Duke St Store 5400 4380 5650 4920 5300 4575 5800 4700 6 Total Income 9780 10570 9875 10500 7 8 Expenditure 9 High St Store 4500 4350 4600 4760 10 Duke St Store 3200 3750 3400 3700 11 Total Expenditure 7700 8100 8000 8460 12 13 Total Savings 2080 2470 1875 2040 Explain below the function of the 2 different mouse options you have used so far. A White Cross: ______________________________________________ ______________________________________________ A Black Cross: _________________________________________ _________________________________________ Introduction to Spreadsheets Page 21 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

THE SUM FUNCTION To calculate the High St Store Income for all 4 quarters, you could enter the formula: =C4+D4+E4+F4 in cell G4. This would produce the correct result but takes time and you could make lots of mistakes. Imagine if you were adding the contents of 100 cells. To overcome this you can use a much shorter formula called the Sum function as follows:  Activate cell F4 by clicking in it  Type in =sum(B4:E4) and press the enter key circled below:  Replicate/copy this formula (use the Autofill command) to F5 and F6 Introduction to Spreadsheets Page 22 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

NOTE:  Every formula starts with an = sign(e.g. =a1+b1+c1)  Every function also starts with an = sign followed by the name of the function, in this case SUM  Type an Open Bracket (  Enter the first cell in the range, in this case C4  Enter the “Through-to” command :  Enter the last cell in the range to be added, in this case F4  Type a Close Bracket ) So when you type =sum(c4:f4) you mean: Add up all the numbers in the range C4 to F4 Question: What would you type in cell G9 to add up the Expenditure for the High St Store? Answer: ____________________________________ Question: What would you type in cell G13 to add up the Total Savings for both stores? Answer: ___________________________________ Introduction to Spreadsheets Page 23 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

A B C D E F G 1 PROJECTED BUDGET 2 QTR1 QTR2 QTR3 QTR4 TOTAL 3 Income High St Store (£) 5400 (£) 5650 (£) 5300 (£) 5800 22150 (£) 4 5 6 Duke St Store Total Income 4380 9780 10570 4920 4575 9875 10500 40725 4700 18575 7 8 Expenditure 9 High St Store 4500 4350 4600 4760 18210 10 Duke St Store 3200 3750 3400 3700 14050 11 Total Expenditure 7700 8100 8000 8460 32260 12 13 Total Savings 2080 2470 1875 2040 8465 Enter those sum functions into cells G9 and G13, replicate/copy where necessary and check your answers below. Ask your lecturer to show you how to use the Autosum icon on the Editing group of the Home ribbon instead of always having to type the SUM function.   Preview, print and save the amended spreadsheet Introduction to Spreadsheets Page 24 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

FORMAT THE SPREADSHEET 1. Centre the heading across columns  Activate cell A1 by clicking in it  Check that the formula bar reads PROJECTED BUDGET  Highlight cells A1 to G1  Click on the Merge and Centre icon in the Alignment group of the Home tab  The text will now be centred over the chosen cells. Introduction to Spreadsheets Page 25 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

2. Change values to currency  Highlight cells from C4 to G13  Click the down arrow in the Number group under the Home tab   The Format Cells window should appear, as shown  Click on the Number tab  From the Category: list, select Currency  From the Negative numbers: select the top option, - £1,234.10  Click on the downward arrow to the right of Decimal Places and reduce this to 0 and click on the OK button    Introduction to Spreadsheets Page 26 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

A B C D E F G 1 PROJECTED BUDGET 2 QTR1 QTR2 QTR3 QTR4 TOTAL 3 Income (£) (£) (£) (£) (£) 4 High St Store £5,400 £5,650 £5,300 £5,800 £22,150 5 Duke St Store £4,380 £4,920 £4,575 £4,700 £18,575 6 Total Income £9,780 £10,570 £9,875 £10,500 £40,725 7 8 Expenditure 9 High St Store £4,500 £4,350 £4,600 £4,760 £18,210 10 Duke St Store £3,200 £3,750 £3,400 £3,700 £14,050 11 Total Expenditure £7,700 £8,100 £8,000 £8,460 £32,260 12 13 Total Savings £2,080 £2,470 £1,875 £2,040 £8,465  Click on any cell in the spreadsheet to remove the highlighting. The spreadsheet should now show currency with no decimal places. See below. 3. Delete a row  Highlight all of row 12 by clicking on the row number  Introduction to Spreadsheets Page 27 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

 Click on the Delete icon in the Cells group  Row 12 will now be deleted and all other rows moved up 1 place to fill the gap.  Click on any cell in the spreadsheet to remove the highlighting 4. Delete a column  Highlight column B by clicking on the column heading  Click on the Delete icon in the Cells group as above  The original column B should now be deleted and all other columns moved left 1 place to fill the gap  Click on any cell in the spreadsheet to remove the highlighting 5. Adjust column widths (Best Fit)  Move the mouse pointer to the right hand edge of column heading A until it changes to a black arrowhead cross.  Double click the mouse  Column A should now be set to the widest entry in the column  Repeat for columns B to F Introduction to Spreadsheets Page 28 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

A B C D E F 1 PROJECTED BUDGET 2 QTR1 QTR2 QTR3 QTR4 TOTAL 3 Income (£) (£) (£) (£) (£) 4 High St Store £5,400 £5,650 £5,300 £5,800 £22,150 5 Duke St Store £4,380 £4,920 £4,575 £4,700 £18,575 6 Total Income £9,780 £10,570 £9,875 £10,500 £40,725 7 8 Expenditure 9 High St Store £4,500 £4,350 £4,600 £4,760 £18,210 10 Duke St Store £3,200 £3,750 £3,400 £3,700 £14,050 11 Total Expenditure £7,700 £8,100 £8,000 £8,460 £32,260 12 Total Savings £2,080 £2,470 £1,875 £2,040 £8,465 6. Adjust column widths (Manually)  Move the mouse pointer to the right hand edge of any column heading until it changes to a black arrowhead cross  Click and drag the mouse pointer to reduce or increase the column to a suitable width Check your spreadsheet with the one below: Introduction to Spreadsheets Page 29 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

7. MAXIMUM, MINIMUM AND AVERAGE CALCULATIONS Find the best month’s income for the 2 stores as follows:  Click in cell A14 and type Highest Income  Enter the following formula in cell B14 =max(b4:e5) £5,800 should appear in B14 Find the lowest month’s income for the 2 stores as follows:  Click in cell A15 and type Lowest Income  Enter the following formula in cell B15 =min(b4:e5) £4,380 should appear in B15 Find the average month’s income for the 2 stores as follows:  Click in cell A16 and type Average Income  Enter the following formula in cell B16 =average(b4:e5) £5,091 should appear in B16 Introduction to Spreadsheets Page 30 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

A B C D E F 1 PROJECTED BUDGET 2 QTR1 QTR2 QTR3 QTR4 TOTAL 3 Income (£) (£) (£) (£) (£) 4 High St Store £5,400 £5,650 £5,300 £5,800 £22,150 5 Duke St Store £4,380 £4,920 £4,575 £4,700 £18,575 6 Total Income £9,780 £10,570 £9,875 £10,500 £40,725 7 8 Expenditure 9 High St Store £4,500 £4,350 £4,600 £4,760 £18,210 10 Duke St Store £3,200 £3,750 £3,400 £3,700 £14,050 11 Total Expenditure £7,700 £8,100 £8,000 £8,460 £32,260 12 Total Savings £2,080 £2,470 £1,875 £2,040 £8,465 COPY INFORMATION AND RENAME WORKSHEETS  Copy and paste the data on the Sheet1 worksheet to Sheet2  Double click the Sheet2 name tab and rename it High Street Store  Highlight first 3 rows on High Street Store worksheet  Copy and paste at A1 on sheet 3  Highlight row 5 on High Street Store worksheet  Cut and paste at A5 on sheet 3 Introduction to Spreadsheets Page 31 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

 Highlight rows 6 to 8 on High Street Store worksheet  Copy and paste to A6 on sheet 3  Highlight row 10 on High Street Store worksheet  Cut and paste at A9 on sheet 3  Highlight rows 11 and 12 on High Street Store worksheet  Copy and paste at A11 on sheet 3  Double click the Sheet3 name tab and rename it Duke Street Store  Print a copy of all 3 worksheets and leave them for your Lecturer Introduction to Spreadsheets Page 32 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

Congratulations! You have completed an Introduction to Spreadsheets and should be able to:  Create a Spreadsheet  Save a spreadsheet  Save a spreadsheet under a different name  Open an existing spreadsheet  Insert data  Delete data  Copy or cut and paste data  Perform arithmetical calculations  Use basic functions (sum and average) on ranges of data  Print spreadsheet Please complete your Introduction to Spreadsheets Record of Achievement Introduction to Spreadsheets Page 33 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

PowerPoint Presentation:

Flexible Learning Unit Record of Achievement Student’s name: ___________________ I Can Please Tick Start Microsoft Excel 2007 Understand the layout of the Excel Screen Find the Microsoft Office Button, Quick Access Toolbar, the Ribbon and the Title Bar Confidently move onto the next section Create new spreadsheet Save spreadsheet Re-save with different names Open existing spreadsheet Insert data Delete data Copy or Cut and Paste data Perform arithmetical calculations Use basic functions (sum and average) on ranges of data Print spreadsheet Confidently move onto the next section College Certificate Achieved: Lecturer’s signature: ______________ Date __________ Introduction to Spreadsheets Please record your progress as you work through the Introduction to Spreadsheets Workbook and tick the relevant boxes: Please give to your lecturer for signature and discuss your options! Introduction to Spreadsheets Page 34 of 34 Using Windows 7 and Office 2010 N Smith/J Barr

authorStream Live Help