Level 1 – Spreadsheets and Graphs Office 2010
01/02/2011
SPREADSHEETS AND GRAPHS LEVEL 1
Contents Introduction ..................................................................................................... 6 Using Microsoft Excel 2010 .............................. Error! Bookmark not defined. Opening Microsoft Excel .............................................................................. 9 The Microsoft Excel window .......................................................................... 11 Quick Access Toolbar ................................................................................ 11 Title Bar. .................................................................................................... 12 Minimise, Restore and Close. .................................................................... 12 Name Box .................................................................................................. 12 Formula Bar ............................................................................................... 12 The Ribbon. ............................................................................................... 13 Scrollbars ................................................................................................... 13 View Options and Zoom Control ................................................................ 13 Ribbon Tabs .................................................................................................. 14 The File Tab .............................................................................................. 14 The Home Tab ........................................................................................... 14 Insert.......................................................................................................... 15 Page Layout .............................................................................................. 15 Formulas.................................................................................................... 15 Data ........................................................................................................... 15 Review ....................................................................................................... 15 View ........................................................................................................... 16 Creating a New Workbook ............................................................................ 17 Closing A Workbook ...................................................................................... 18 Closing Microsoft Excel ................................................................................. 19 What Is A Spreadsheet? ............................................................................... 19 Selecting Cells .............................................................................................. 21 Selecting a single cell ................................................................................ 21 Selecting Adjacent Cells ............................................................................ 22 Selecting Non-adjacent Cells ..................................................................... 24 Selecting a Row ......................................................................................... 25 Selecting Adjacent Rows ........................................................................... 26 Selecting Non-adjacent Rows .................................................................... 26 Selecting a Column.................................................................................... 27 Selecting Adjacent Columns ...................................................................... 27 Selecting Non-adjacent Columns............................................................... 28 Selecting the whole spreadsheet ............................................................... 29 Excel 2010 Level 1
2
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Entering and Deleting Cell Contents.............................................................. 30 Saving a Workbook ....................................................................................... 33 Opening a recent Workbook .......................................................................... 35 Opening a Saved Workbook .......................................................................... 35 Save and Save As ......................................................................................... 39 Using the Sheet Tabs .................................................................................... 42 Understanding Alignment .............................................................................. 44 Moving Between Cells ................................................................................... 46 Switching Between Workbooks ..................................................................... 47 Practice Exercise One ................................................................................... 49 Answers to Practice Exercise One ................................................................ 51 Rows and Columns ....................................................................................... 53 Inserting a Row .......................................................................................... 53 Inserting a Column ..................................................................................... 55 Deleting a Row........................................................................................... 57 Deleting a Column ..................................................................................... 58 Hiding Rows and Columns ......................................................................... 58 Editing Data ................................................................................................... 64 Inserting Additional Text into a Cell............................................................ 64 Using the Undo and Redo Commands ...................................................... 67 Using the Redo Command ......................................................................... 68 Modifying Column Width and Row Heights ................................................... 70 Altering Row Heights ................................................................................. 72 Adjusting Multiple Rows and Columns ....................................................... 75 Moving and Copying Data ............................................................................. 77 Copying Text in a Cell ................................................................................ 77 Copying Text from a Range Of Cells ......................................................... 79 Copying Text to a New Sheet .................................................................... 80 Moving Text in a Cell ................................................................................. 82 Moving Text to a New Sheet ...................................................................... 85 Drag And Drop............................................................................................... 86 Practice Exercise Two ................................................................................... 94 Arithmetic Formulas....................................................................................... 96 Addition Formula ........................................................................................ 96 Using the Sum Function ........................................................................... 100 Using the AutoSum Icon .......................................................................... 101 Selecting Different Cells When Using AutoSum ....................................... 104 Excel 2010 Level 1
3
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Replicating a Formula .............................................................................. 107 Subtraction Formula ................................................................................ 109 Multiplication Formulae ............................................................................ 111 Division Formulae .................................................................................... 112 Switching Between Formula and Normal View ........................................ 114 Shortcut Way to View Your Formulas ...................................................... 115 The Average Function ............................................................................. 116 What Is BODMAS?...................................................................................... 121 Recalculating Data ...................................................................................... 126 Why should you use cell references in Excel? ......................................... 126 Formatting Data .......................................................................................... 128 Formatting Cells .......................................................................................... 129 The Thousand Separator ......................................................................... 132 Applying Currency Formatting ................................................................. 133 Applying Date Formatting ........................................................................ 137 Applying Text Formatting ......................................................................... 138 Sorting Data ................................................................................................ 138 Borders and Shading................................................................................... 142 Applying Shading ..................................................................................... 145 Practice Exercise Three .............................................................................. 147 Creating Charts and Graphs ...................................................................... 149 Creating a Column Chart ......................................................................... 149 Moving and deleting labels ...................................................................... 158 What data to select .................................................................................. 159 Moving and deleting labels ........................... Error! Bookmark not defined. Creating a Bar Chart ................................................................................ 162 Creating a Pie Chart ................................................................................ 167 Creating a Line Chart............................................................................... 172 Displaying Values in a Line Chart ............................................................ 176 Creating A 3D Chart ................................................................................ 178 Changing the Chart Type ......................................................................... 179 Changing Chart Colours .......................................................................... 181 Deleting a Chart ....................................................................................... 183 Changing the Chart Scale ........................................................................ 185 Selecting Non-adjacent Cells ................................................................... 188 The Different Parts of a Chart ..................................................................... 192 Headers and Footers................................................................................... 193 Excel 2010 Level 1
4
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Entering Headers and Footers on a Chart Worksheet. ............................ 199 Practice Exercise Four ................................................................................ 202 Printing A Spreadsheet ................................................................................ 208 Print Preview ............................................................................................ 209 Printing Options ....................................................................................... 211 Printing in Formula View .......................................................................... 212 Using the Fit Sheet on One Page Option ................................................. 213 Landscape or Portrait Orientation ............................................................ 215 Show Row and Column Headings ........................................................... 217 Showing the Gridlines When Printing ....................................................... 218 Margins .................................................................................................... 218 Printing a Chart............................................................................................ 223 Interactive Exercise ..................................................................................... 228 Consolidation Exercise One ........................................................................ 229 Consolidation Exercise Two ........................................................................ 236 Consolidation Exercise Three ...................................................................... 243
Excel 2010 Level 1
5
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
WELCOME TO SPREADSHEETS Introduction Spreadsheets are extremely useful in everyday life. They can calculate things very quickly and accurately and then produce graphs or charts to help you. You will be using Microsoft Office Excel 2010. This program is designed to help you manage data and even create charts and graphs to enable you to represent numerical data more clearly. Once you have entered data into a spreadsheet you can quickly create a wide range of formulas to calculate and analyse the information. Spreadsheets are ideal for helping you to keep control of your household budget (monthly expenditure) or for storing a list of names and addresses. Below is an example of a monthly expenditure.
Spreadsheets can also be used instead of a database to store small amounts of data. As they have a variety of uses it is a good idea to know how to use them. There are several companies that make spreadsheet programs, for example Lotus, Star Office, etc but the most popular spreadsheet program is Excel. As you work through this book when you are asked to click a mouse button, it will mean to click the left mouse button – unless you are specifically asked to click the right mouse button.
Excel 2010 Level 1 – February 2011
Page 6
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Logging onto the College system Once the computer and monitor have been switched on you will see a screen similar to the example below, with the instruction: Press Ctrl-Alt-Delete to log on.
Holding down the Control and Alt keys with your left hand, press Delete with your right hand. The next screen to appear will be similar to the example below. Enter your User Name and Password into the relevant boxes.
Excel 2010 Level 1 – February 2011
Page 7
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
When you reach the screen regarding IT Acceptable Use Policy you should read it through the first time and then click OK to accept it.
Now click onto the OK button. Your tutor will be able to provide you with your User Name and Password if you have forgotten this information. Note: You can move from the User Name field to the Password field by pressing the Tab key on your keyboard.
When you enter your password the box will display bullets: the screen.
………. on
Now press the Enter key on the keyboard. Please be patient while the computer goes through the rest of its start-up procedure. The next screen to appear will be similar to the example on the next page. Excel 2010 Level 1 – February 2011
Page 8
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
You have now successfully started the computer and logged on to your user area. This screen, which you are now looking at, is called the Desktop.
Opening Microsoft Excel Click on the Start button in the bottom-left corner of the screen.
Excel 2010 Level 1 – February 2011
Page 9
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Now click on the All Programs link, then the Microsoft Office link.
Click on the Microsoft Office link.
Access 2010 Excel 2010
Click on the Excel 2010 link.
When Excel launches it creates a blank spreadsheet, or Workbook, ready to work with. As a spreadsheet can consist of many separate sheets (like pages),it is known as a Workbook.
Excel 2010 Level 1 – February 2011
Page 10
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The Microsoft Excel window The Quick Access Toolbar, Title Bar and Ribbon are similar to other Microsoft Office 2010 applications. Microsoft Excel Icon
Quick Access Toolbar
Title Bar
Minimise Close and Restore commands
Ribbon
Formula Bar
Cells for entering data
The Cells that make up the spreadsheet are where all of the useful tasks that spreadsheets perform take place. Quick Access Toolbar This is positioned in the top left corner of the screen This area is useful as it can be customised to contain the functions that you use most often. You may have more commands on your toolbar than are seen here. To add other commands: a) click on the down arrow at the right of the Quick Access Toolbar
This window opens where you can click to enter a tick next to the commands you want to add b)Click on New, if it hasn’t already got a tick next to it
Repeat steps a) and b) to add Open and Print Preview and Print to the Quick Access Toolbar
Excel 2010 Level 1 – February 2011
Page 11
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The toolbar will now look like this:
The six commands are Save, Undo, Redo, New, Open and Print Preview and Print. These are the ones you are most likely to use. To remove a command, click on the down arrow at the right end of the Quick Access Toolbar and click to remove the tick from the command you want to remove. Title Bar. This gives the name of the document which is currently open. Minimise, Restore and Close. Make the window smaller and resizable
Minimise the window
Close Microsoft Word If you Minimise the window, the workbook is still open but is not shown on the screen. It can be seen as a button on the Windows Taskbar at the bottom of the screen. For example, the Taskbar below, shows that three workbooks are open. The icons show that all workbooks are Microsoft Excel
The title of each workbook is on the button. The lighter colour of the Books 1 and 3 buttons indicates that they have been Minimised – the other workbook, Book 2, is currently active and visible on the screen. Clicking on the Microsoft excel Book1 button would make workbook1 active. Name Box This gives the reference of the active cell, or of the top left hand cell in a range of cells. Formula Bar This box displays the contents of the active cell. It can be text, numbers or a formula. Excel 2010 Level 1 – February 2011
Page 12
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The Ribbon. This contains all the commands you will need to format your document. Scrollbars On the right and bottom of the page are Scrollbars which are used to move vertically and horizontally through a document. There are arrows at both ends with a sliding bar in between. Up Arrow – Click once on this to move one row up the spreadsheet. Scrollbar – Click and hold the left mouse button whilst moving the bar up or down. This allows you to move up or down a long spreadsheet more quickly.
Down Arrow – Click once on this to move one row down the spreadsheet. . . The horizontal scrollbar works in a similar way. Clicking on the right arrow moves one column to the right and clicking on the left arrow moves one column to the left. Click and drag the scrollbar to move more quickly across the spreadsheet. View Options and Zoom Control The icons on the left give different options for how a spreadsheet is displayed on the screen. You will use the Normal option.
Zoom Control
Click on the Minus and then on the Plus signs to see how the document is displayed smaller and larger on the screen. You can also left click and drag the slider to achieve the same result
Excel 2010 Level 1 – February 2011
Page 13
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Ribbon Tabs The File Tab
The commands here refer to the workbook as a whole. You will be covering most of these later in this course.
The Home Tab Whenever you open an application, the Home tab is selected, as in the example below.
You will be using commands from the Clipboard, Alignment, Number and Cells groups
Excel 2010 Level 1 – February 2011
Page 14
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Insert
You will be using commands from the Charts group. Page Layout
You will be using commands from the Page Setup Group Formulas
You will be using commands from the Formula Auditing group. Data
You will be using commands from the Sort and Filter group. Review
You will be using commands from the Proofing group.
Excel 2010 Level 1 – February 2011
Page 15
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
View
You will be using commands from the Window group. When you are working with Charts a contextual tab will be available with three additional tabs – Design, Layout and Format. You will be using commands on the Design and Layout tabs.
Remember
The appearance of the commands on the ribbon might vary from those in the illustrations in this book.
Depending on the size of your monitor screen the commands might be in a horizontal row or a vertical row.
The commands will always be in the same ribbon Group however they are displayed.
Excel 2010 Level 1 – February 2011
Page 16
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Creating a New Workbook When you start Microsoft Excel a new workbook will automatically open. However if you have closed this workbook you will need to create a new one. To do this, select the File tab and click on the New option.
From the various options available, click on Blank workbook then Create. You can also create a new Workbook quickly by clicking on the New command you added to the Quick Access Toolbar earlier..
Excel 2010 Level 1 – February 2011
Page 17
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Closing A Workbook
To close a workbook, click on the File tab
then the Close menu option. Do this now.
When you close a workbook the following message may be shown:
If it does, click on the Don’t Save option. You will then have the Excel screen with no workbook open.
Excel 2010 Level 1 – February 2011
Page 18
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Closing Microsoft Excel
Now close the application by clicking on the File tab
and then on Exit.
What Is A Spreadsheet? Open Microsoft Excel so a blank spreadsheet is on the screen. A spreadsheet consists of many boxes, referred to as cells, arranged in rows and columns to form a grid. Each cell has a unique reference which is made up of the Column Letter – A,B,C etc. along the top of the sheet, followed by the Row Number – 1,2,3 etc. down the left side of the sheet. It is easier to see the cells if the gridlines are visible.
If the gridlines are not displayed when Excel opens, select the File tab
and click on Options.
Excel 2010 Level 1 – February 2011
Page 19
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The following window opens. Click on Advanced
Scroll down until you see Display options for this worksheet.
Click on the down arrow next to Gridline colour
And select grey 50%. Then click OK
You will now be able to see the gridlines on your worksheet.
Excel 2010 Level 1 – February 2011
Page 20
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Selecting Cells Selecting a single cell When you click into a particular cell it becomes Active – that is, you can type into it – and will be outlined in black. In the example below, the cell B5 is selected. Column B, row 5
As a spreadsheet can extend to over 1000 columns and rows, it is important to get the cell reference right. To find the cell with a particular reference, eg C15, follow down column C until you reach row 15. If you click in that cell you will see that the column letter and row number are both highlighted. This allows you to check that you have got the right cell.
Column C and row 15 are highlighted
This cell has the reference C15
Excel 2010 Level 1 – February 2011
Page 21
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Just above columns A and B there is a Name Box which gives the reference of the cell you are in – in this case C15
Another way to find a particular cell is to click into the Name Box, type the cell reference in and press Enter on the keyboard. Excel will take you to the cell which will be outlined in black and active. Practise finding the following cells – D14, G35, K9 Selecting Adjacent Cells So far you have been clicking into one cell to select it. It is however possible to select more than one cell at a time. To select several adjacent cells, Left click in cell C1 and, keeping the left mouse button held down, drag your mouse pointer down to cell C11.
Excel 2010 Level 1 – February 2011
Page 22
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Now release your left mouse button. Your screen should now look like to the one on the previous page. Selecting cells in a row uses a similar method. Left click in cell A8 and, keeping the left mouse button held down, drag the mouse pointer horizontally across the cells until you reach cell G8.
Now release the left mouse button. You will notice that the first cell (in this example cell A8) appears to have no shading, so it appears not to be selected. All the cells, however, are surrounded by a black line to show they are selected.
When you select a range of cells the first cell is always shown unselected however it has been added to your selected cells.
Excel 2010 Level 1 – February 2011
Page 23
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Selecting Non-adjacent Cells You can select a range of cells that are not next to each other (non-adjacent). Select the cell C4 and, keeping the left mouse button held down, drag the mouse pointer down to cell C8. Now release the left mouse button. Now press and hold the Ctrl key on the keyboard.
Now select the cell E4 and, keeping the left mouse button held down, drag the mouse pointer down to cell E8. Now release the left mouse button. Now release the Ctrl key on the keyboard. Your spreadsheet should look like the example below.
You have selected cells in columns that are not next to each other (nonadjacent). Now click into any blank cell in your spreadsheet to de-select the cells. Now select the following range of non-adjacent cells: A3 to A9 and D2 to D10. Remember to hold down the Ctrl key to select non-adjacent cells.
Excel 2010 Level 1 – February 2011
Page 24
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Your spreadsheet should look like the example below.
You have selected cells in column A and cells in column D. Now click in any blank cell in your spreadsheet to de-select the cells. Selecting a Row To select a row in the spreadsheet you must click on the grey number next to the row. Click on the number 3 on the left hand side of the screen.
This has selected the third row of the spreadsheet.
Notice that the mouse pointer has changed shaped to a right-facing arrow. Now click in any blank cell in your spreadsheet to de-select the cells.
Excel 2010 Level 1 – February 2011
Page 25
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Selecting Adjacent Rows Select the fourth row by clicking on the grey number 4 next to the row, and with your left hand mouse button held down, drag the mouse over the row numbers 5, 6 and 7, and then release the left hand mouse button. Your spreadsheet should now look like the example below. Rows 4, 5, 6 and 7 have been selected.
Now click in any blank cell in your spreadsheet to de-select the cells. Selecting Non-adjacent Rows Select row 3 and press and hold the Ctrl key on the keyboard. Now select row 6 and then select row 9. Release the Ctrl key on the keyboard. Your spreadsheet should look similar to the example below. The non-adjacent rows 3, 6 and 9 have been selected.
Excel 2010 Level 1 – February 2011
Page 26
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Now click in any blank cell in your spreadsheet to de-select the cells. Selecting a Column Selecting a column is a very similar procedure to selecting a row. Click on the letter C at the top of the spreadsheet.
Column C has been selected as shown below.
Notice that the mouse pointer has changed shaped to a downwards facing arrow. Now click in any blank cell in your spreadsheet to de-select the cells. Selecting Adjacent Columns Let’s now practice selecting adjacent columns. Select column B and, with your left mouse button held down, click and drag the mouse over the columns C, D, and E. Release the left hand mouse button. Your spreadsheet should look like the example on the next page.
Excel 2010 Level 1 – February 2011
Page 27
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Now click in any blank cell in your spreadsheet to de-select the cells. Selecting Non-adjacent Columns Select column B and press and hold the Ctrl key on the keyboard. Now select columns D, F and H. Now release the Ctrl key on the keyboard. Your spreadsheet should look like the example below.
You should have the following non-adjacent columns highlighted. Now click in any blank cell in your spreadsheet to de-select the cells. Practise selecting the following cells:
Select the rows 1, 2, 3 and 4. Now deselect the rows.
Select the columns B, C, D and E. Deselect the columns.
Excel 2010 Level 1 – February 2011
Page 28
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Select the Rows 1, 3, 5 and 9. Deselect the rows.
Select the columns A, D, E and G. Deselect the columns
Remember to use the Ctrl key to select non-adjacent cells. Selecting the whole spreadsheet To select the entire spreadsheet, click on the cell at the top left corner of the spreadsheet now.
The entire spreadsheet has now been selected. Now click into any cell in your spreadsheet to de-select the cells. Close the workbook (File tab – Close) but keep Excel open.
Excel 2010 Level 1 – February 2011
Page 29
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Remember
When a cell is selected it is Active – content can be entered.
A selected cell has a black border around it.
To select a single cell, click in it.
Click on a row number to select that row.
Click on a column letter to select that letter.
Click and drag to select adjacent cells, rows or columns
Hold down the Ctrl key to select non-adjacent cells, rows or columns.
Click outside a selected area to deselect the cells.
Click in the small top left hand cell to select the entire cell.
Entering and Deleting Cell Contents Before you continue open a new workbook (File tab – New – Blank Workbook). If you click into a cell and make it active you can type directly into it. As well as typing into a cell Excel has a function called Auto-fill which is very useful if you want to enter data which is in a series eg Monday, Tuesday etc, January, February etc. To show how this works type Monday in cell B2 then click into any other cell. Your screen should look like the example below.
Notice the small black square at the bottom right hand corner. This square is called the AutoFill handle. Point your mouse onto this square – you need your mouse pointer to change shape to a thin cross.
Excel 2010 Level 1 – February 2011
Page 30
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Click and hold down your left mouse button and drag across to cell F2. Click anywhere in your spreadsheet to un-highlight the text. Your spreadsheet should look like the example below.
Now type January in cell A3. Point to the Auto-fill handle and when the cursor changes to a cross, click and drag down to cell A15. The spreadsheet will look like the example below.
It is easy to delete the contents of a cell. Select cell H2 and press the Delete Key on the keyboard. The contents of cell H2 have been deleted. To delete a range of cells you first select the range as describes in a previous exercise and then press the Delete key on the keyboard. Select cells E2 to G2 and press the Delete key on the keyboard. Now select cells A9 to A15 and press Delete on the keyboard. The contents of the cells will have been deleted and the worksheet will look like this. Excel 2010 Level 1 – February 2011
Page 31
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
You can change the contents of a cell just by selecting it and typing directly into the cell. There is no need to delete the contents first.
Remember
Click into a cell to type in contents.
Use the Auto-fill handle to let Excel complete data in a series.
Click in a cell and press Delete to delete the cell contents.
To delete contents from a range of cells, first select the cells and then press delete.
To alter the contents of a cell, select it and type directly into the cell. You do not need to delete the contents first.
Now close the workbook without saving the changes.
Excel 2010 Level 1 – February 2011
Page 32
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Saving a Workbook When you start creating workbooks, it is very important to save them at regular intervals in case the computer should freeze or crash when you could lose all your work. Computers should always be shut down properly. If you just switch if off you will lose any unsaved work you have done. If you accidentally try to close Microsoft Word without saving your work, a reminder will appear on the screen. Always read these reminders properly to make sure you give the correct instruction. When you save a workbook it is usually saved in a special area called the hard drive. The hard drive is a storage area where the data is kept safe even when the computer is switched off. At Highbury, once you have your own user name and password any work you produce must be stored in your own part of the hard drive called My Documents. Open Microsoft Excel and in cell B1 type Monday. Using the Auto-fill handle, click and drag to cell F1. In cell A2 type Week 1. Again use Auto-fill and click and drag down to cell A6. You will now have a spreadsheet which looks like this:
To save the workbook select the Home tab and click on Save As.
Excel 2010 Level 1 – February 2011
Page 33
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The Save As window is opened. Make sure the Save in box says Documents. If it doesn’t click on the Documents icon on the left side.
Type the name Weekly Sales in the File name box then click on Save. The Title bar will now show the name of the workbook.
Close the workbook. File tab – Close.
Excel 2010 Level 1 – February 2011
Page 34
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Opening a recent Workbook If you select the File tab now you may find that your workbook is listed as shown below
This is because Excel is showing you all the Recent Workbooks you have been using – the most recent at the top.
If this screen is not showing, click on Recent for it to be displayed.
Opening a Saved Workbook If you want to open a workbook which is not listed in Recent Workbooks, select the Home tab and click on Open.
Excel 2010 Level 1 – February 2011
Page 35
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The Open dialogue box will open and here you can navigate to the file you want to open. Click on the My Documents icon .
Then click on the Spreadsheets and Graphs Level 1 Workfiles folder and click Open. The window now looks like this
Click on the file named Budget and click Open. Excel 2010 Level 1 – February 2011
Page 36
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Your screen should resemble the example below.
Click into cell A16 and enter your name, in cell C16 type Highbury’s centre number (58515) and in cell E16 type today’s date. Your screen will now look similar to the one below.
Excel 2010 Level 1 – February 2011
Page 37
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Now click on the File tab, And the on the Save As command
The Save As window is displayed. In the File name box type Opened and Saved.
Now click the Save button. Your Save As window may show other files already saved in the Creating Spreadsheets and Graphs Workfiles folder. You have now successfully opened a previously saved spreadsheet, entered more text into it and then saved it with a different filename. Now close the spreadsheet. Excel 2010 Level 1 – February 2011
Page 38
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Save and Save As Open the workbook Weekly Sales which you saved earlier in My Documents. You can find it using Recent or Open on the File tab. Fill in the cell as follows
You are now going to save the changes you made to Weekly Sales. The quickest way to do so is to click on the Save icon on the Quick Access Toolbar.
You can also save the changes by selecting the File tab and clicking on Save.
Close the workbook and then open Weekly Sales again and you will see that your changes have been saved but the originally spreadsheet with that name is no longer available. Change the numbers in the following cells C3 to 184 D5 to 218 E5 to 157 F2 to 207 F5 to 205 This time you want to save this workbook but you also want to keep the one with the original data. To do this you must use the Save As command and give the workbook a new filename.
Excel 2010 Level 1 – February 2011
Page 39
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Select the File tab and click on Save As. When the dialogue box opens give the workbook the name Weekly sales update
Close the workbook. If you now select the File tab and Recent option you will find both files there as shown on the next page.
Both files – Weekly sales and Weekly sales update are available
Excel 2010 Level 1 – February 2011
Page 40
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Remember
Use the Save As command the first time you save a document
Save your work in your My Documents area
Save your work regularly using the Save command on the Quick Access Toolbar
If you need two versions of the same document, make the changes you need and save the second version using the Save As command and give it a new file
name
Using the Save command means that your original document is lost and any changes you make will be saved using the original filename
Using the Save As command allows you to save a different version of a document with a new filename and your original document will still be saved. Using Save command Original Document
Changed Document
Deleted from hard drive
Saved to hard drive
Using Save As command Original Document
Changed Document
Still kept on hard drive
Saved to hard drive with new filename
Excel 2010 Level 1 – February 2011
Page 41
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Using the Sheet Tabs Make sure Excel is open and you have a blank spreadsheet open. If you look at the bottom of the spreadsheet you will notice there are tabs labelled Sheet1, Sheet2 and Sheet3.
Each of these tabs is like a page in a book. A spreadsheet automatically opens with three sheet tabs but you can add more if you need them or you can delete sheet tabs you no longer need. Each spreadsheet is called a book or workbook (as it can contain many sheet tabs). By clicking on each of these tabs a new sheet will open up on the screen. If you click on them now you will not see any difference in the sheet as there is no content in them, but the sheet number will be in bold type to show that it is the active sheet. In the example below, Sheet2 is active
Click on each tab to see how the tab name changes to bold type. Although throughout this course you will only be using one sheet, it is useful to be able to add more sheets to a workbook. For example a workbook may be used for managing household accounts with each month’s accounts displayed on a separate sheet. i.e. a sheet for January, February, March and April etc.
Excel 2010 Level 1 – February 2011
Page 42
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
To create a new worksheet, click on the Insert Worksheet tab at the bottom.
You have now added a fourth worksheet. Like the first three, it too is blank at present as no data has been added.
Worksheets can also be deleted. Delete Sheet2 by first clicking on the Sheet2 worksheet tab. Now select the Home tab and click on the arrow beneath the Delete button in the Cells group Click on Delete Sheet.
The tabs on your worksheet will now look like the following example:
Keep Excel open for the next exercise.
Excel 2010 Level 1 – February 2011
Page 43
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Understanding Alignment Close the blank spreadsheet (File – Close) and open the one called Weekly Sales Update which you created earlier (File – Recent). When you type text into a spreadsheet it will be automatically aligned to the left hand side of the cell.
When you type numbers into your spreadsheet these will be automatically aligned to the right of the cell. It is possible, as with all other Microsoft applications, to alter the alignment (position) of text and numerical (numbers) data etc. Click into cell D4. The number is currently right aligned. With the Home tab selected click on the left align command in the Alignment group.
The number will now be left aligned as below
With cell D4 still selected click on the Centre command.
Excel 2010 Level 1 – February 2011
Page 44
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The text will now be centre aligned in the cell.
Now click on the Right align icon to align the numbers back to the right hand side of the cell. Now close the workbook and click on the Don’t Save button when you see this message. Keep excel open for the next exercise.
Remember
Alignment of cell contents can be changed using the Alignment commands on the Home tab. Alignment group..
You can change the alignment of many cells by selecting them first.
You can select adjacent and non-adjacent cells, rows and columns as explained in an earlier exercise.
Data in a cell may look as if it aligned to left, right or centre but it may only appear so particularly if the data takes up all the width of a cell
Always use the alignment icons even if data appears to be aligned how you want it.
Excel 2010 Level 1 – February 2011
Page 45
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Moving Between Cells Make sure Excel is open and open the file Weekly Sales Update There are a few different ways of moving from one cell to the next. You can use your mouse to click into cells, but if you have a lot of data to enter there are quicker and easier ways. The Tab key on the left side of the keyboard can be used but it only allows movement from one cell to the next on the right.
To move to the left you must hold down the Shift key whilst using the Tab key.
A better way of moving between cells cell is to use the Arrow keys at or near the right side of the keyboard These have the advantage of allowing you to move up, down, left and right between cells.
Up Left
Right Down Click into cell A2. Use the arrow keys to go to cell C5 and change the number to 184. Now use the arrow keys to reach cell F3 and change the number to 225. Now use the Tab and Shift keys to return to cell B3. Practise using both the arrow keys and the Tab/Tab+Shift keys to navigate round the spreadsheet altering numbers, adding or deleting data, until you are used to using the different methods. Close Weekly Sales Update but Don’t Save the changes.
Excel 2010 Level 1 – February 2011
Page 46
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Switching Between Workbooks There are times when you may need to work on several workbooks at the same time. There may be data in one workbook that you wish to copy to another workbook or several workbooks. To do this you will need to be able to switch between the workbooks. Open the spreadsheet: SUMMARY (situated in the folder called Spreadsheets and Graphs workfiles folder in your student area). The SUMMARY spreadsheet will now be displayed on the screen as shown below.
Now open the spreadsheet LOWBURY COLLEGE (situated in the folder called Spreadsheets and Graphs workfiles folder in your student area) The LOWBURY COLLEGE spreadsheet will now be displayed as shown below.
Excel 2010 Level 1 – February 2011
Page 47
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
You now have two spreadsheets open on the screen with the LOWBURY COLLEGE spreadsheet in front of the SUMMARY spreadsheet. To switch to viewing the SUMMARY spreadsheet select the View tab on the LOWBURY COLLEGE spreadsheet. Click on the Switch Windows icon.
Click on the SUMMARY option to display the SUMMARY spreadsheet. You will now be viewing the SUMMARY spreadsheet. Now follow the above instructions to switch to the LOWBURY COLLEGE spreadsheet. Keep practicing switching workbooks until you are confident performing this procedure. As explained earlier in the Minimise, Restore and Close section, you can also switch between workbooks by clicking on the buttons on the Windows Taskbar
Try that now. Remember that the darker blue button indicates the workbook which is open on the screen. Now close all of your workbooks, without saving the changes.
Excel 2010 Level 1 – February 2011
Page 48
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Practice Exercise One Write your answers to the following questions on a blank piece of paper. 1)What is the name of this bar?
2) What is the name of this area in Excel where commands and options are found?
3) What is this bar called?
4) What is the name that Microsoft gives to its spreadsheet application? 5) How many sheets are present when you open a spreadsheet for the first time? 6) When a cell is clicked in or referred to, is the cell named with a number then a letter or a letter then a number? 7) Is it possible to select more than one adjacent cell at a time? 8) How would you select more than one non-adjacent row? 9) What do the scroll bars allow you to do? 10) Open a new spreadsheet. 11) In cell A1 type Books. 12) In cell A3 type the text Oliver Twist. 13) In cell A4 type the text Bleak House. 14) In cell A5 type the text A Christmas Carol. Excel 2010 Level 1 – February 2011
Page 49
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
15) In cell C3 type the number 12. 16) In cell C4 type the number 18. 17) In cell C5 type in the number 36. 18) In cell A8 type your name. 19) In cell C8 type the centre number (58515). 20) In cell E8 type today’s date (e.g. 04/01/2008). 21) Select cell A1 and make the contents Centre aligned. 22) Select cell range C3 to C5 and make the contents left aligned. 23) Save this spreadsheet as Books in your My Documents folder. 24) In cell A6 type Pickwick Papers. 25) In cell C6 type the number 20. 26) In cell C2 type the word Copies. 27) Left align the contents of C6. 28) Select cell C8 (centre number) and right align the contents. 29) Save the spreadsheet with the new filename CD Books. Check your answers with the answers on the following pages.
Excel 2010 Level 1 – February 2011
Page 50
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Answers to Practice Exercise One 1) Title Bar 2) Ribbon. 3) Formula Bar. 4) Excel. 5) 3 sheets (tabs). 6) A letter then a number. 7) Yes, you can select more than one adjacent cell. 8) To select non-adjacent rows you would press the Ctrl key on the keyboard. 9) The scroll bars allow you to see or select cells further across or down a spreadsheet i.e. move horizontally or vertically. Questions 11 to 23 your spreadsheet should look similar to the example below.
24) Saved as Books as seen in the Title Bar.
Excel 2010 Level 1 – February 2011
Page 51
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Questions 25 to 29 your spreadsheet should look similar to the example below.
30) Saved as CD Books as shown in the Title Bar. Now close the spreadsheet but leave Excel open.
Excel 2010 Level 1 – February 2011
Page 52
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Rows and Columns Inserting a Row Create a new spreadsheet (click on the Office Button and then New and select Blank Workbook Enter the following data into the new workbook. Please make sure that the data is entered into the cell ranges shown below.
Now save the spreadsheet in your My Documents folder with the filename: Class figures. Select row 4. Select the Home tab
Click on the Insert command in the Cells group. (The down-pointing arrowhead beneath the word Insert provides more options that are not needed now.)
Excel 2010 Level 1 – February 2011
Page 53
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Click anywhere in your spreadsheet to un-highlight the new row. Now add some data to the new row. Type the following into the new row (row 4) as shown below (remember to use the Tab key to move between the cells).
Now select row 8 and insert a new row. In the new row that you have inserted, type in the following data as circled below.
Excel 2010 Level 1 – February 2011
Page 54
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Inserting a Column Select column C and click on the Insert command in the Cells group.
A new column has been inserted into the spreadsheet.
Enter the following data into the new column as shown below (remember to use the down arrow on the keyboard to move down the cells in the column).
Excel 2010 Level 1 – February 2011
Page 55
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Now select column D and insert a new column into the spreadsheet. Enter the following data into the new column as shown below.
Excel 2010 Level 1 – February 2011
Page 56
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Deleting a Row Select row 8 and select the Home tab.
Now click on the Delete command in the Cells group. The row will now be deleted. Now delete row 4 from the spreadsheet. Make sure that you have selected the number 4 row label. Your spreadsheet should now look similar to the example below.
Excel 2010 Level 1 – February 2011
Page 57
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Deleting a Column Select column C and select the Home tab
Now click on the Delete command in the cells group. The column has now been deleted from the spreadsheet. Now click anywhere on your spreadsheet to un-highlight the column. Now delete the new column D from your spreadsheet. Make sure that you have selected the column heading D. Save the spreadsheet with the new filename Class Information – remember to use the Save As command.
Remember
To delete a row or column, always select the row/column heading and use the Delete command in the Cells group.
Do not highlight the row/column and press the delete key on the keyboard. This will delete the contents of the cell but the row/column will remain.
Excel 2010 Level 1 – February 2011
Page 58
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Hiding Rows and Columns In Excel it is possible to hide rows or columns in your spreadsheet. This is a useful feature if you are working on a spreadsheet with a large amount of data, and you wish to view or work on a part of the spreadsheet. Make sure you have the workbook Class Information open. You created this in the previous exercise. With the Home tab selected first select column C. Now click on Format in the Cells group.
Run your cursor down the menu which opens until it is pointing to the Hide & Unhide option
When you point to this option a submenu will appear. Slide your cursor into the sub-menu and click on Hide Columns.
.You will now see that column C has disappeared from the spreadsheet
Excel 2010 Level 1 – February 2011
Page 59
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The column has not been deleted; it has just been hidden from view. If you had large amounts of data in a spreadsheet you could hide several columns to make it easier for you to view the data you wish to work on. Now click on a blank cell to clear the thick black line between columns B and D. To make the hidden column re-appear you will need to select the column before the hidden column and the column after the hidden column. Click on the column heading B and, keeping the left mouse button held down, drag the cursor over column D. Now release the left mouse button. Column B and column D have been highlighted as shown below.
Click on the Format command in the Cells group. Run your cursor down to the Hide & Unhide command.
Slide the cursor over to the submenu and click on Unhide Columns
Excel 2010 Level 1 – February 2011
Page 60
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Click on a blank cell to un-highlight the columns. The hidden column C is now showing. Now click on the row heading 5.to select that row. Row 5 will be highlighted
Now click on Format.
Move the cursor down to the Hide & Unhide option.
Move the cursor across to the sub-menu and click on Hide Rows.
Now click in a blank cell to clear the thick black line between rows 4 and 6. Row 5 has now been hidden from view. To unhide the row, click on the row heading 4, keep the left mouse button held down and drag the cursor over row heading 6. Now release the left mouse button. You have now highlighted rows 4 and 6.
Excel 2010 Level 1 – February 2011
Page 61
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Use the Format menu as before
Hide & Unhide
Unhide Rows
Click on a blank cell to un-highlight the rows. The hidden row 5 has now been un-hidden as shown below.
Practise hiding and un-hiding rows and columns. Do the following: Hide column B in the spreadsheet Class Information. Unhide the column B in the spreadsheet. Hide row 7 in the spreadsheet Class Information. Unhide row 7 in the spreadsheet. Excel 2010 Level 1 – February 2011
Page 62
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Remember
The commands for Hiding & Un-hiding row/columns are found under the Format command in the Cells group on the Home tab
To hide a row or column you must first select it by clicking on the row/column heading.
To unhide a row you must select the row above and the row beneath the hidden row.
To unhide a column you must select the columns to the left and the column to the right if the hidden column
Save the changes made so far to the spreadsheet and close the workbook.
Excel 2010 Level 1 – February 2011
Page 63
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Editing Data Before you continue, open the spreadsheet: General Fiction, situated in the Spreadsheets and Graphs Workfiles folder. Inserting Additional Text into a Cell When you wish to insert additional text into a cell that already has some text in it, you can’t just type in the cell, because the original text will be deleted and replaced with the new text that you type. To insert additional text in a cell you will need to type in the Formula Bar area of Excel. Now practice adding more text to a cell in the spreadsheet called General Fiction. Select cell A5 and you will see that the text is also displayed in the Formula Bar area of Excel.
Now position your cursor at the front of the text: New in the Formula Bar and click the left mouse button to insert the cursor in front of the text. Now type in the following text: The
Excel 2010 Level 1 – February 2011
Page 64
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Press the Spacebar once to leave a space between the words The and New. Your spreadsheet should look similar to the example below.
Now press the Enter key on the keyboard. The new text has been inserted in front of the original text as shown below.
Now select cell A12 and type in the following text: Sale Now On and press the Enter key on the keyboard.
Excel 2010 Level 1 – February 2011
Page 65
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
You are now going to replace the words Sale Now On in cell A12 with the words Half Price Books. Select the cell A12 and type in the words: Half Price Books and press the Enter key on the keyboard. .
You will see that when you type the words Half Price Books in the cell, the original text Sale Now On has been replaced with the new text. Keep the workbook open for the next exercise.
Remember
To insert additional text into a cell place the cursor in the relevant position in the Formula Bar and type in the additional text.
To replace the text in a cell with new text, select the cell and type in the new text.
Excel 2010 Level 1 – February 2011
Page 66
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Using the Undo and Redo Commands If when you are using Microsoft Excel you make a mistake, you can undo the error by using the Undo icon on the Quick Access Toolbar.
The Undo icon will undo the last action you have done, and if you click again it will undo the action before that, and so on. Select cell B17 and type in the following text: Books For Us. Now press the Enter key on the keyboard. Now select cell B17 and type in the following text: A few books left. Now press the Enter key on the keyboard. You have unfortunately typed the text A few books left in the wrong cell. Click on the Undo icon on the Ribbon to undo the mistake.
The original text has now appeared back in cell B17.
Excel 2010 Level 1 – February 2011
Page 67
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Using the Redo Command The Redo icon on the Quick Access Toolbar is only available when you have used the Undo command. The Redo command will reset any action that the Undo command has done. If you realise that you did, in fact, mean to add the text A few books left in cell B17, instead of retyping the text you can just click on the Redo icon to insert the text that the Undo command deleted. Click on the Redo icon on the Quick Access Toolbar now.
The text that was deleted when you used the Undo icon has reappeared now you have used the Redo icon.
To practise using these commands do the following: In cell A19 type in the following text: Free Delivery. In Cell C20 type in the following text: No VAT. In cell A19 replace the text Free Delivery with the text: Postage paid. In cell C20 add the following text: Today after the text VAT (remember to leave a space). Delete the text in A12. In B17 add the text special after the text few (remember to leave a space). Your spreadsheet should look similar to the example below.
Excel 2010 Level 1 – February 2011
Page 68
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Close the workbook and click on Don’t Save when you see the following message.
Did you remember to click into the Formula Bar to add text to a cell?
Excel 2010 Level 1 – February 2011
Page 69
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Modifying Column Width and Row Heights Open the workbook Book Sales Data, (situated in the folder Spreadsheets and Graphs Workfiles) It is important to make sure that all the data in your spreadsheet is fully displayed. It is easy for data to be hidden by another column or row. Always make sure that your columns and rows are adequately spaced so all the data can be read (by default each column starts with a width of about nine numeric characters). In cell A1 type in the following text: Book Club Sales Data and click anywhere in the spreadsheet to de-select the cell. You will see that the text is covering cell B1 as well. Any text in cell B1 would be hidden. You can alter the column width of A1 to accommodate the text. Position the cursor at the column border of column A and a double arrow will appear.
Keeping the left hand mouse button pressed down, drag the column border to the right until the column width is large enough to accommodate the text. Now release the left mouse button. The column is now wide enough to show all the text. Your column A width should look similar to the example below.
Excel 2010 Level 1 – February 2011
Page 70
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Now click on the Undo button to return the column to its original width.
Another method of altering the width of a column is to allow Excel to alter the width automatically. Position the cursor at the column border of column A again and a double arrow will appear. This time, double click the left mouse button and the column will automatically adjust to fit the text in column A. Now click on the Undo button to return the column to its original width. Another method is to select the Format Column AutoFit menu option(Home tab, Cells group) to alter the width of the column. Position your cursor over the letter A above column one and click to select this column. Click on the Format command in the Cells group and click on AutoFit Column Width.
The width of column A has now been automatically altered to fit the text. Now click on the Undo button to return the column to its original width. There is a fourth method of adjusting column width.
Excel 2010 Level 1 – February 2011
Page 71
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Make sure that column A is still selected. Now click on the Format command.
Now click on Column Width option.
This will produce the Column width measurement box where you can type a precise width amount.
In the Column width box type in the following measurement: 20 and click on the OK button. Column A has been altered to a precise measurement. Click anywhere in your spreadsheet to un-highlight column A. Altering Row Heights Altering row heights is very similar to altering column widths. The main difference is that you choose the Row menu option instead of the Column menu option. Select row 1 by clicking on the number 1 on the row label and click on the Format command.
Now click on the Row Height menu option.
Excel 2010 Level 1 – February 2011
Page 72
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
This will produce the Row height measurement box where you can type a precise height amount. In the Row height box type in the following measurement: 35.
Now click on the OK button. The height of row 1 has been altered to a precise measurement. With the row still selected, chose the AutoFit Row Height from the Format menu. The row height will be reduced to fit the text. Use the undo command to return the row back to a height of 35. This time point your cursor at the border of row 1 and row 2 and double click to adjust the height of the row automatically. Now position the cursor at the border between row 1 and row 2 to produce the double arrow and click and drag the row border to any size.
As you adjust the height manually a small box appears telling you the height of the row. A similar box will show when you adjust column widths.
Now adjust column C to a measurement of 25. Adjust row 7 to a measurement of 40. Now adjust the width of column C to fit the text. Adjust the height of row 7 to fit the text. Close the workbook Book Sales Data without saving the changes.
Book Sales Data.xlsx?
Excel 2010 Level 1 – February 2011
Page 73
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Remember
There are 4 ways to adjust row height and column width.
Auto-Fit Row Height and Auto-Fit Column Width are found under the Format command in the Cells group on the Home tab.
The Row Height and Column Width commands for entering an exact size are found under the Format command in the Cells group on the Home tab.
automatically by double clicking on the double arrow at the border between row numbers or column letters.
Row Height and Cell Width can be adjusted manually by clicking and dragging the double arrow at the border between row numbers or column letters.
Excel 2010 Level 1 – February 2011
Page 74
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Adjusting Multiple Rows and Columns All of the ways of adjusting Column width covered above can be applied to more than a single row or Column at the same time. Open the workbook Petrol situated in the folder Spreadsheets and Graphs Workfiles The width of all Columns is currently set to 8.43. You are going to change the width of Columns A, F G and H to 15. Click on the heading of Column A (letter A). While holding down the Ctrl key on the keyboard, click on the headings of Columns F, G and H.
Now release the Ctrl key. You will see that all 4 columns have been selected.
Using the Format button on the Home tab set the width of these three Columns to 15.
Excel 2010 Level 1 – February 2011
Page 75
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
You have just selected non-adjacent Columns by holding down the Ctrl key. You will now select adjacent Rows. Click on the heading for Row 3 (the number 3). Holding down the Shift key on the keyboard, click on the heading for Row 10. You have now selected all of the rows in the table, as below.
Again, using the Format Button on the Home tab, change the Row Height to 20.
Excel 2010 Level 1 – February 2011
Page 76
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Moving and Copying Data A useful feature in Excel, and all Microsoft applications, is the ability to move or copy data within a spreadsheet. It is however important to be aware of the difference between moving data and copying data. If you copy data then the original data stays in its location in the spreadsheet and a duplicate copy appears in a new location. If you move data then the original data does not stay in its original location and it only appears in a new location. Before you continue open a new spreadsheet and save it with the filename: Sales Targets 08. Copying Text in a Cell In cell A1 type in the following text: Region A Sales. Press the Enter key on the keyboard. Select cell A1 and adjust the width of the cell to accommodate the text (you have practiced this in a previous section of the book). You will now practice copying the text in cell A1 to cell A11. Make sure cell A1 is selected and click on the Copy icon on the Ribbon.
Excel 2010 Level 1 – February 2011
Page 77
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Select cell A11
Now click on the Paste icon on the Ribbon.
The contents of cell A1 have now been copied to cell A11.
Note: For standard Paste, which you will be using throughout this book, click on the Clipboard icon. Clicking on the down-pointing arrowhead below the word Paste brings up a menu with various options that you will not be using.
Excel 2010 Level 1 – February 2011
Page 78
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
You will notice there is a flashing broken line around cell A1. To turn these flashings line off, press the Esc key on the keyboard. To make the cells in column A wide enough to fit all the text, use one of the four methods outlined in an earlier exercise. Copying Text from a Range Of Cells Now type the following information into your spreadsheet in the cell references shown below.
To copy the contents of these cells, select cells B3 to F3 – click into cell B3, then press and hold the left mouse button and drag across the cell ranges C3, D3, E3 and F3. Now release the left hand mouse button. Click on the Copy icon on the Ribbon. (Home tab, Clipboard group)
Excel 2010 Level 1 – February 2011
Page 79
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Select cell B11.
Click on the Paste icon on the Ribbon.
Now press the Esc key on the keyboard.
Click anywhere in your spreadsheet to un-highlight the range of cells.
You have now copied a range of cells and pasted them into another location in your spreadsheet.
Copying Text to a New Sheet Now type the following information into your spreadsheet in the cell references shown below.
Excel 2010 Level 1 – February 2011
Page 80
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Select cells A1 to F5 in the spreadsheet by clicking on cell A1 and dragging the cursor across and down to cell F5. You should have selected the following cells in your spreadsheet.
Now click on the Copy icon on the Ribbon.
Now click on the sheet tab Sheet2 situated at the bottom of the screen.
You will now have Sheet2 on the screen.
This sheet is at present empty. Click on the Paste icon on the Ribbon.
Excel 2010 Level 1 – February 2011
Page 81
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Click anywhere in your spreadsheet to un-highlight the range of cells. The copied range of cells from Sheet1, have been pasted into this sheet.
Note If a Smart Tag is displayed (as in the above example), please ignore this – Smart Tags are mostly outside the scope of this workbook although they will mentioned briefly later. Notice also that Column A needs to be resized in order for the contents of the cells to fit. Moving Text in a Cell Select Sheet 1 and press the Escape key. Select cells A11 to F11 and press the Delete key on the keyboard to remove the contents of these cells. Select cell A1 and click on the Cut icon on the Ribbon.
Excel 2010 Level 1 – February 2011
Page 82
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Select cell A8 and click on the Paste icon on the Ribbon.
Click anywhere in your spreadsheet to un-highlight the cell. Your spreadsheet should look similar to the following example. The text in cell A1 has been moved to the new location of A8.
Now select the cell range A3 to F5 and click on the Cut icon.
Excel 2010 Level 1 – February 2011
Page 83
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Now click in cell A9 and click the Paste icon.
The selected data has been moved to a new location in the worksheet.
Click anywhere in your spreadsheet to un-highlight the range of cells.
Excel 2010 Level 1 – February 2011
Page 84
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Moving Text to a New Sheet Select the cell range A9 to F10 and click on the Cut icon. Click on Sheet 2, to select the second worksheet.
Now click on cell B8 and click on the Paste icon. The data has now been moved from Sheet1 into Sheet2.
Now click on Sheet1. As you have cut the data out of Sheet1 the data is no longer displayed on Sheet1.
Excel 2010 Level 1 – February 2011
Page 85
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Save the spreadsheet with the new filename: Sales Targets Amended. Close the spreadsheet but keep Excel open.
Remember
When you use the Cut and Paste commands, data is moved from one location to another
When you use the Copy and Paste commands the data is left in its original position and also copied into a new position.
If you cannot see all the data in a cell adjust the width using one of the methods covered in a previous exercise.
Drag and Drop So far in this book you have been shown the Cut and Paste method to move data from one location to another. However there is another method that you can use to move text from one location to another in the same worksheet. It is called Drag and Drop. Before you practice using Drag and Drop make sure you have a new worksheet open in Excel. In the new worksheet type the text Monday in cell D3. Press the Enter key on the keyboard.
Excel 2010 Level 1 – February 2011
Page 86
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Now click in cell D3 to select the cell. Position your cursor on the black line above the text in cell D3.
The cursor changes to a white arrow with four smaller black arrows behind it. Click your left mouse button and the four small black arrows will disappear. The cursor then is just a white arrow that has a black outline. Keeping the left mouse button held down, drag your cursor over to cell A3.
An A3 label appears to let you know where the contents of the dragged cell will be moved to. Now release your left mouse button.
Excel 2010 Level 1 – February 2011
Page 87
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The text in cell D3 has now been moved (dragged) to cell A3.
Now enter the text in the cells shown below.
Select the cell range A3 to A7.
Now position the cursor on the black line below the text Friday. Keeping the left mouse button held down, move your cursor to cell D6. Now release your left mouse button. Click on any blank cell to un-highlight the cells.
Excel 2010 Level 1 – February 2011
Page 88
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The highlighted data has now been moved to cell range D2 to D6.
As you can see, it is possible to drag the cell(s) using any line (top, left, right or bottom) around the selected cell. Now select the cell range D2 to D6 and, using the Drag and Drop method, move this cell range data to the cell range of B5 to B9. Your data should be in this position.
The method you choose when you are asked to move data within a worksheet will depend on which method you are comfortable using Close this worksheet without saving the changes.
Excel 2010 Level 1 – February 2011
Page 89
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Find and Replace A useful function in Excel is the Find and Replace tool. If you have a word that you wish to find and replace with an alternative word then you can use the Find and Replace function. Before you continue open the spreadsheet: PETROL, situated in the folder Creating Spreadsheets and Graphs Workfiles. Click on Find & Select on the Ribbon. Home tab – Editing group
Then select the Replace menu option. This will produce the Find and Replace window.
You wish to find the word Totol in your spreadsheet and replace it with the word Total. In the Find what box type the word: Totol. In the Replace with box type the word: Total.
Excel 2010 Level 1 – February 2011
Page 90
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Your Find and Replace box should look similar to the example below.
Click onto the Options button to reveal further options on how you would like to amend the worksheet.
Further Find and Replace options
The Match case box is used to replace the word in the Find What box exactly as it is typed only. If you do not put a tick in the Match case box then Excel will find all instances of the word which ever case they are typed in and replace them with the Replace with word exactly as it is typed. If you want all instances of a word to be replaced, regardless of the case it is typed in, do not use the Match Case option. The Replace With word will be entered into the spreadsheet exactly as you type it in the box
Excel 2010 Level 1 – February 2011
Page 91
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Click in the Match case box now to put a tick in this box.
Now select to Replace All the instances of the word Totol with the replacement word Total click on the Replace All button. A message window appears to tell you how many replacements have been made.
Click on OK
Click Close to exit from the Find and Replace window.
Excel 2010 Level 1 – February 2011
Page 92
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
This was the word Totol in cell A10, before you used the Find and Replace function.
The word Totol has now been replaced with the word Total.
Now use the Find and Replace function to replace the word Octber in the PETROL spreadsheet with the word October (remember to use Match case). The word in cell B5 should now read: October. Now close the PETROL workbook without saving any changes.
Excel 2010 Level 1 – February 2011
Page 93
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Practice Exercise Two 1. Open the Workbook Shifty Computer Sales, situated in the folder Creating Spreadsheets and Graphs Workfiles. 2. Save the Workbook in your My Documents folder with the new filename of Trusty Computer Company. 3. Insert an extra column between columns E and F. 4. In cell F3 enter the following text: MAY.
5. Now enter these numbers into the new column. 6. Alter row 3 to a new height of 20. 7. In cell A8 add the following text: INTERNET in front of the text USB MOUSE. Do not delete the original text. Keep one space between each word in cell A8. 8. Adjust column A so that all the text is visible. 9. In cell D8 delete the contents. 10. In cell D8 enter the new amount of 97.61. 11. Select the cell range B3 to F3 and copy this cell range. 12. Select cell B10 and paste the contents of copied cell range. 13. Select the cell range A1 to G8 and copy this range and paste it into Sheet2 starting at cell C3. 14. Adjust the columns in Sheet2 so that all the data is visible. 15. Select the cell range C5 to C10 and move this range to sheet 1 cell A10. 16. In Sheet1 replace the words MONITOR with the new name of Flat Screen
Excel 2010 Level 1 – February 2011
Page 94
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Sheet1 of your spreadsheet should look similar to the example below.
Sheet2 of your spreadsheet should look similar to the example below.
Save and close the spreadsheet. If your spreadsheet doesn’t look like the examples above then repeat this exercise.
Excel 2010 Level 1 – February 2011
Page 95
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Arithmetic Formulas Formulas are placed in cells whenever you want Excel to add, subtract, multiply, divide or do other mathematical calculations. The formula should be placed in the cell where the result of the calculation is to appear. You place a formula in the spreadsheet by typing it in the appropriate cell. All formulas start with an equals sign (=) - this is letting Excel know that you will be performing a calculation. If you do not enter an equals sign in front of the calculation then Excel thinks that you are entering text. The equals sign indicates that the cell selected will be equal to a result of a specified calculation. A formula can be simple or complicated but it must not contain any spaces. Addition Formula Before you continue open the spreadsheet called: Rusty Widget Sales, situated in the folder Spreadsheets and Graphs Workfiles. Remember a formula starts with an equals sign (=). This key on the keyboard is situated to the left of the Backspace key on the keyboard. In cell H5 type in the following text: Total. Click anywhere in your spreadsheet to accept the text in the cell. You will now use a formula to add up the total sales for Widget W45. When you are using formulas it is important to enter them in the correct cell. As you wish the results of the total for Widget W45 to appear in cell H6 you will write the formula in cell H6 Select cell H6 and type in an equals sign now.
When you are referring to data in a cell you always refer to the cell reference in a formula not the actual data. For example the sales for Widget W45 for January are 134 but in a formula you do not type 134, instead you type in the cell reference for 134, which is B6. Remember always use the cell reference not the data amount in a cell.
Excel 2010 Level 1 – February 2011
Page 96
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
You are going to add the sales for Widget W45 for the months of January through to June using the cell references (B6, C6, D6, E6, F6 and G6) for the data. Type in the first cell reference B6 now. To add B6 to C6 you type in the addition symbol (+), which is situated on the Numeric keypad on your keyboard.
Esc
~ `
! 1
F1
F2
F3
F4
@ 2
# 3
$ 4
% 5
Q
Tab
W
E
R
F5
^ 6 T
F6
& 7 Y
F7
* 8 U
F8
( 9 I
F9
F10
) 0 O
+ = P
Caps Lock
S Z
Shift
D X
F C
G V
H B
J N
K
}
< ,
M
: ;
L > .
| \
] " '
? /
F12
Backspace
{ [
A
F11
Print Scr een
Scr oll Lock
Insert
Home
Delete
End
Pau se
Pag e Up
Pag e Do wn
Num Lock
Num Lock
Caps Lock
Scr oll Lock
/
*
8
9
4
5
6
1
2
3
7 Home
PgUp
+
Enter Shift
End
PgDn Enter
Ctrl
Alt
Alt
.
0
Ctrl
Ins
Del
Make sure that the Number lock light is on the keyboard and press the Addition key now. If the Number Lock light is not on then press the Num Lock key on the keyboard.
Esc
~ `
! 1
Tab Caps Lock Shift
F1
F2
F3
F4
@ 2
# 3
$ 4
% 5
Q
W
E
R
F5
^ 6 T
F6
& 7 Y
F7
* 8 U
F8
( 9 I
F9
F10
) 0 O
+ = P
S Z
D X
F C
G V
H B
J N
K M
: ;
L < ,
> .
} ] " '
? /
F12
Backspace
{ [
A
F11
| \
Print Scr een
Scr oll Lock
Insert
Home
Pag e Up
Delete
End
Pag e Do wn
Pau se
Num Lock
Caps Lock
Scr oll Lock
Num Lock
/
*
7
8
9
4
5
6
1
2
3
Home
PgUp
+
Enter Shift
End
PgDn Enter
Ctrl
Alt
Alt
Ctrl
0 Ins
. Del
Now type in the next cell reference, which is C6 and then press the Addition key on the keyboard and then the next cell reference, which is D6. Now press the Addition key on the keyboard and then the next cell reference, which is E6. Now press the Addition key on the keyboard and then the next cell reference, which is F6. Now press the Addition key on the keyboard and then the last cell reference, which is G6. Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 97
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Although the whole formula might not to appear in the cell, it will be visible in the Formula Bar.
Your formula should look like the example above. You have now finished entering a formula to add the sales figures for Widget W45. All that is left to do is to get Excel to calculate the formula. This is achieved by pressing the Enter key on the keyboard. Do this now. When you have finished entering a formula do not click in your spreadsheet, as this will corrupt your formula, always press the Enter key. You should now have the following result (2432) in cell H6.
If you click on cell H6 now (as above) you will see that the formula in the cell is displayed in the Formula Bar. The result of the formula continues to be displayed in the cell. The formula that you typed in has produced the total sales for Widget W45. You will now enter the formula again but this time, use a different method. Press the Delete key on your keyboard to remove the calculation in cell H6. This time you will use the mouse to select the cell reference rather than entering it manually. Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 98
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Select cell H6 and type in an equals sign. Click into cell B6 to select it. Cell B6 is selected and is also displayed in the Formula Bar.
Now press the Addition key on the keyboard and then click into the next cell reference, which is C6. Now press the Addition key on the keyboard and then click into the next cell reference, which is D6. Now press the Addition key on the keyboard and then click into the next cell reference, which is E6. Now press the Addition key on the keyboard and then click into the next cell reference, which is F6. Now press the Addition key on the keyboard and then click into the last cell reference, which is G6. Although you may not be able to see the formula in your cell as you type, it will be visible in the Formula Bar.
You have now finished entering a formula to add the sales figures for Widget W45. All that is left to do is to get Excel to calculate the formula. This is achieved by pressing the Enter key on the keyboard. Do this now. Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 99
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
You should once again, have the following result (2432) in cell H6.
Using the Sum Function So far you have added a range of adjacent cells together by specifying each cell reference (B6+C6+D6+E6+F6+G6). Although this method may be used for a few cell references it would be impractical for a large range of cells. Just think if you had to add up a range of cells starting at A2 and ending at Z2, it would take you a while to type in each cell reference. A quicker way to add up adjacent cells is to use a built-in function called SUM which you will now use to find the total sales for Widget 46. Select cell H7 and type in an equals sign to start our formula. Now type in the function name: SUM. So far your formula should be: =SUM Now type in an open bracket (parentheses), this can be achieved by pressing the Shift Key and the number 9 key on the keyboard. The Shift key is situated below the Caps Lock and Enter keys.
Esc
~ `
! 1
Tab Caps Lock Shift
F1
F2
F3
F4
@ 2
# 3
$ 4
% 5
Q
W
E
R
F5
^ 6 T
F6
& 7 Y
F7
* 8 U
F8
( 9 I
F9
F10
) 0 O
+ = P
S Z
D X
F C
G V
H B
J N
K M
: ;
L
}
< ,
> .
] " '
? /
F12
Backspace
{ [
A
F11
| \
Print Scr een
Scr oll Lock
Insert
Home
Pag e Up
Delete
End
Pag e Do wn
Pau se
Num Lock
Caps Lock
Scr oll Lock
Num Lock
/
*
7
8
9
4
5
6
1
2
3
Home
PgUp
+
Enter Shift
End
PgDn Enter
Ctrl
Alt
Alt
Ctrl
0 Ins
. Del
Now enter the first cell reference where the data you wish to add is situated in the spreadsheet; this is B7. Type in B7. Now type in a colon (press the Shift key and the key to the right of the L key on the keyboard) and then the last cell reference where the data you wish to Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 100
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
add is situated in the spreadsheet, this is G7, type in G7. The colon between the cell references in the formula means to include all the cells in between B7 and G7. Now type in a close bracket (parentheses), this can be achieved by pressing the Shift Key and the number 0 key on the keyboard. Your formula should now read: =SUM(B7:G7) Press the Enter key on the keyboard to calculate the formula. You should now have the following result (543) in cell H7
As you can see using the SUM function is a quicker method than specifying each cell reference. Now add the sales figures for Widget W47 using the SUM function. Remember to start the formula with an equals sign. Your result in cell H8 should be 209. Your formula should read: =SUM(B8:G8) If your result is incorrect then click in cell H8 and check your formula against the formula above to see where you went wrong. Then delete the formula and type it in again. As you can now see the preferred method of adding a range of adjacent cells is to use the SUM function. Be aware that the SUM function can only add cells that are adjacent (next to) - it cannot add cells that are non-adjacent. For example the SUM function cannot add A6, B7 and C9, as these cells are not adjacent to each other. To add these cells you would have to use the addition symbol in your formula, for example (=A6+B7+C9). Using the AutoSum Icon As the SUM function is so useful in adding a range of adjacent cells together that Excel has an icon that can apply the SUM function automatically, it is called AutoSum. This is found in the Editing group on the Home tab. Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 101
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
You will now use the AutoSum icon to add the sales figures for Widget W45. Delete the contents of cells H6 to H8. Select cell H6 and click on the AutoSum icon on the Home tab, Editing group.
Note: Clicking on the small down-pointing arrowhead to the right of the word AutoSum will cause a menu of further options to be displayed. You will not be using these options so make sure that you click on the symbol (â&#x2C6;&#x2018;) or the word AutoSum, not the arrow. The AutoSum icon will now highlight the range of cells to add up.
The AutoSum icon will also produce the complete formula for us. Now to get Excel to calculate the formula, press the Enter key on the keyboard. Your total in cell H6 should be 2432. Remember when you have to add a range of adjacent cells together always use the SUM function or the AutoSum icon. Keep Rusty Widget plc open for the next exercise.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 102
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Remember
A formula always begins with the equals ( = ) sign.
Do not put spaces in a formula.
Always use cell references in a formula not the actual data in the cell
You can type in each cell reference of the range you want to add with a plus ( + ) sign between.
You can click in each cell to add with a plus ( + ) sign in between.
You can add a range by using the =Sum formula.
You do not have to type the word sum in capital letters – Excel will change it to capitals when you press Enter
You can use the AutoSum function.
The =Sum and AutoSum functions can only be used for adding adjacent cells.
Always use either the Sum function or AutoSum when adding adjacent cells.
When the formula is complete press Return on the keyboard. Do not click anywhere in the spreadsheet.
Always use the Sum or AutoSum formulas to add a range of adjacent cells
Excel 2010 Level 1 – February 2011
Page 103
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Selecting Different Cells When Using AutoSum When you use the AutoSum icon to add a range of adjacent cells together Excel tries to work out the range of cells you wish to use. However sometimes the AutoSum feature will select the wrong range of cells or only part of the range of cells you wish to use. If this happens then it is possible to select your own range for the AutoSum feature to use. This is achieved by manually selecting the range after the AutoSum has selected the wrong range. With Rusty Widget plc open delete the contents of cell H6. With cell H6 still selected click on the AutoSum icon on the Ribbon.
Excel will now select the range of cells it thinks you wish to add together.
However this time you are going to choose your own range. Click in cell B6 and, keeping the left mouse button held down, drag the cursor over the cells C6 and D6. Now release the left mouse button. You have now selected a different cell range (B6 to D6) using the AutoSum feature.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 104
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Now press the Enter key on the keyboard. The sum of B6 to D6 is 1110.
Notice there is a Smart tag displayed next to your calculation. A Smart tag is designed to help you complete tasks easily in all of the Office applications. Move your mouse over the Smart Tag (do not click). You will see a message warning that you have not included all the numbers in the row.
Now delete the contents of cell H6. Now use the AutoSum icon to add up the sales figures for Widget W45. Do not press the Enter key. With the range B6 to G6 still selected, amend the cell range manually select the range E6 to G6. Now release the left mouse button.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 105
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
You should have the following range selected.
Now with the range E6 to G6 still selected, amend the cell range manually select the range B6 to G6. Now release the left mouse button. You have now manually selected the range B6 to G6. Now press the Enter key on the keyboard. Your total should show: 2432. Keep Rusty Widget plc open.
Remember
When using AutoSum, Excel selects a range of cells automatically.
This can be amended to include the range you want.
Do not press enter until you have selected the range you want.
When using AutoSum always check that the range selected is the range you want
Excel 2010 Level 1 – February 2011
Page 106
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Replicating a Formula To add up the sales figures for all the other Widgets you could use the AutoSum icon to produce the formula to calculate the totals for each Widget. However there is a quicker way to produce the sales for the other Widgets and that is to replicate (copy or duplicate) the formula in cell H6 down to the cells for the other Widgets. Replicating a formula is the process of copying the formula and pasting (replicating) the formula to other adjacent cells. When you replicate a formula Excel will change the cell references automatically. Select cell H6 and you will see a black square in the bottom right corner of the cell.
Position your cursor over the black square and your white cursor will change to a black cross.
Press and hold down the left mouse button and drag down to cell H12 and release the left mouse button. You will notice that as you were dragging down the cells a dotted line appeared around the cells.
This is showing you the cells that you are replicating the formula to. Now click anywhere in your spreadsheet to un-highlight the cells. You will now notice that the sales figures for the other Widgets have been calculated for you.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 107
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The sales figures for the other Widgets have been calculated.
Notice another Smart Tag is displayed. Do not worry about this; it will disappear as you continue to work. Select cell H7 and look in the Formula Bar and you will see that the formula has been replicated down from cell H6.
When you have entered a formula in one cell and you need the same formula entered into the other adjacent cells, you should replicate the formula. Donâ&#x20AC;&#x2122;t type the formula in again or use the AutoSum icon. Now try the following: Select the cell B13 and add this column up by using the AutoSum icon. Replicate the formula in cell B13 to cells C13 to H13 (remember to select the cell B13 before you replicate). In cell A13 type in the following text: Total Per Month. Now click anywhere in your spreadsheet to un-highlight the cells. Your spreadsheet should look like the example on the next page.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 108
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Remember
Before you replicate a formula to other cells make sure the original formula is correct. If there is an error in the formula then the error will be replicated to the other cells.
Subtraction Formula If you study the results you have just produced, you will see that in January less Widgets were sold than in February. You wish to produce a formula to find how many more Widgets were sold in February. If you were to work this sum out on paper, you would take the total for January away from the total in February (2172 – 1650). However, with Excel you do not need to resort to pen and paper – you produce a formula that will display the result in cell B15. Select cell A15 and type in the following text: Feb minus Jan. Now select B15 and type in an equals sign and then the cell reference for the monthly total for February, which is C13. Now type the subtraction symbol by pressing on the subtraction key on the keyboard (the subtraction key is situated above the addition key in the number keypad).
Excel 2010 Level 1 – February 2011
Page 109
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The cell reference for the January monthly total for is B13, so type in B13. Your formula should look like this: =C13-B13.
Now to get Excel to calculate the formula, press the Enter key on the keyboard. Your spreadsheet should look like the following example.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 110
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The total for February (2172) subtracted from the total for January (1650) equals 522. 522 more widgets were sold in February than in January. Now in cell A16 type in the following text: Apl minus Mar. In cell B16 produce a formula to see how many more widgets were sold in April than in March. Now press the Enter key on the keyboard to get Excel to calculate your formula. Your result in cell B16 should be: 234. The formula that you should have typed in is as follows: =E13-D13 Multiplication Formulae The multiplication key is to the left of the subtraction key in the numerical keypad on the keyboard. You are going to produce a formula to multiply the estimated yearly total using just January’s figures. January’s total is in cell B13 and there are 12 months in a year so our formula would be: =B13*12. In cell A17 type in the following text: Estimated Yearly Total (Jan). Adjust the column so that all the text is visible. In cell B17 type in the formula to calculate the yearly total for January: =B13*12 Now press the Enter key on the keyboard to get Excel to calculate your formula. The result in cell B17 should be: 19800. Using January’s figures you estimate selling 19800 widgets in a year.
Excel 2010 Level 1 – February 2011
Page 111
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Your spreadsheet should look like the example below.
Now in A18 type in the following text: Estimated Yearly Total (Feb). In cell B18, produce a formula to multiply the monthly total by twelve using just February’s figures. Now press the Enter key on the keyboard to get Excel to calculate your formula. The result in B18 should be: 26064. The formula that you should have typed in is as follows: =C13*12 Division Formulae The division key is to the left of the multiplication key in the numerical keypad on the keyboard.
/
You are going to produce a formula to divide January’s total for the year by February’s total for the year. January’s total for the year is in cell B17 and February’s total for the year is in cell B18. So the formula would be: =B17/B18. In cell A19 type in the following text: Jan/Feb Total. In B19 type in the formula to divide January’s estimated total by February’s estimated total: =B17/B18. Now press the Enter key on the keyboard to get Excel to calculate your formula. The result in cell B19 should be: 0.759669.
Excel 2010 Level 1 – February 2011
Page 112
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Dividing January’s estimated total for the year by February’s estimated total for the year equals 0.759669. Your spreadsheet should look like the example below.
Remember
Always start a formula with the equals sign (=).
Always use cell references in a formula – not the cell contents
When adding adjacent cells together always use the Sum function.
Press the Enter key to get Excel to calculate your formula.
Always replicate the formula to the other relevant cells.
Make sure that your formula is correct before you replicate it.
Always make sure that all the data is displayed in full.
Excel 2010 Level 1 – February 2011
Page 113
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Switching Between Formula and Normal View When your spreadsheet has data that was produced by formulas you may need to be able to view the formulas or print them. This is useful if you are not sure the results are what you expected so you can check that you have selected the right information. Using the Rusty Widget Sales spreadsheet letâ&#x20AC;&#x2122;s practice switching between Normal view and Formula view. Click on the Formulas Tab on the Ribbon.
Now click on Show Formulas in the Formula Auditing group.
You will notice that your columns have been automatically widened to display the formulas in your spreadsheet. The formulas in your spreadsheet are now visible.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 114
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
To return to Normal view, make sure that you still have the Formulas Tab selected on the Ribbon and click again on the Show Formulas option.
Your spreadsheet has now been returned to Normal view and the column widths have been restored. Shortcut Way to View Your Formulas A quick way to view the formulas is to use the Ctrl key on the keyboard and the Back quote key together.
Try this now to view the formulas in your spreadsheet. Press the Ctrl key and the Back quote key together and you will return to Normal view. Now close the Rusty Widget Sales spreadsheet without saving the changes.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 115
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Remember
Do not adjust the columns or change any part of the spreadsheet when you are in Formula view. If you do then when you return to Normal view the columns in your spreadsheet will not be automatically adjusted back to their original width.
While formula view is displayed Show Formulas remains highlighted.
Use Ctrl + Back quote as a shortcut to showing and hiding formulae.
You may need to print out the formulae in a spreadsheet so remember how to go to Formula view.
The Average Function A useful function in Excel is the Average function. It will calculate the average of a range of numbers in your spreadsheet. This simplifies the process of adding all of the indicated cells together and dividing by the total number of cells. Open the spreadsheet Rusty Widget Sales. In cell H5 type in the following text Average. Select cell H6 and enter an equals sign. Now type in the text AVERAGE. As you type the word Average after the equals sign, Excel recognises that you are typing a formula function and tries to anticipate what it is. When the box showing alternatives appears, a single click on any option causes a brief description of the function to be displayed.
Excel 2010 Level 1 – February 2011
Page 116
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Double clicking on one of the functions displayed will cause that function to be selected for you – even if you have not yet finished typing the whole word – and the open bracket. It will also display how the formula needs to be constructed
Note: The example above tells you that inside the brackets you should enter the numbers that are to be averaged. It shows that the individual numbers (or cell references) can be added individually, separated by commas. However, you will often need to average the contents of adjacent cells and this is more easy achieved by entering a range of cells inside the brackets as described in this chapter. Always type in the cell reference, not the contents. If you do not already have the open bracket typed in, do this now by holding down the Shift key and pressing the 9 number key on the keyboard. You then release the Shift key. Your formula should now look similar to this: =AVERAGE( You now need to enter the number range you wish to average. This will be the cell range B6 to G6. Now type in B6:G6. The colon (:) means include all the cells between B6 and G6. Now hold down the Shift key on the keyboard and press the number 0 key on the keyboard once. This will produce a closed bracket. Now release the Shift key. Your formula should now look like this: =AVERAGE(B6:G6).
Press the Enter key to calculate the average of these cells. Excel 2010 Level 1 – February 2011
Page 117
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
You should now have the following result in cell H6 as shown below.
Now select cell H7 and produce the formula to calculate the average for the cell range B7 to G7. Do not replicate the above formula. You should now have the following result in cell H7 as shown below.
Your formula should be: =AVERAGE(B7:G7) Now select cell H8 and produce the formula to calculate the average for the cell range B8 to G8. You should now have the following result in cell H8 as shown below.
Your formula should be: =AVERAGE(B8:G8) Continue producing the average for the remaining Widgets.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 118
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Your spreadsheet Average column should look similar to the example below.
Now close the Rusty Widget Sales spreadsheet without saving the changes. Now open the spreadsheet Book Sales Data. In cell F3 enter the following text Average. Produce a formula to calculate the average for SPORT for JAN to APR in cell F4. Replicate this formula for all the other products (SPORT to FASHION). Your spreadsheet should look similar to example below.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 119
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Remember
When you use the Average function you must enclose the cell range you wish to average in brackets (also known as parentheses) i.e. =AVERAGE(F1:F9)
Excel will offer different suggestions for a formula as you start to type. You can double click on one to select it or type the formula in manually.
If you wish to average numbers that are not adjacent (next to each other) then you must use commas to separate the different numbers i.e. =AVERAGE(A1,B5,D7,F6)
Note that it does not matter if you type Average in upper or lower case. If you type it in lower case, Excel will change it to upper case when you press the Enter key.
Always type cell references into a formula – not cell contents.
Now close the spreadsheet without saving the changes
Excel 2010 Level 1 – February 2011
Page 120
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
What Is BODMAS? Excel calculates formulas in a particular order to make sure the answer is calculated correctly. For example look at the following calculation: 7 + (6 * 3 + 4) *12 How do you think Excel will calculate this formula? Should Excel calculate from the right to left or from left to right? Luckily there is a set rule, which should be followed for the calculation to be correct. Excel always works out whatâ&#x20AC;&#x2122;s in the brackets first, then the powers and square roots, then any division or multiplication, and then any addition or subtraction. An easy way to remember the correct calculation order is to remember the words BODMAS. BODMAS stands for B
Brackets first
O
Orders (ie Powers and Square Roots)
DM
Division and Multiplication (left-to-right)
AS
Addition and Subtraction (left-to-right)
Why Should You Care? It is important to remember the correct calculation order when you are entering your formulas into Excel, as Excel makes calculations using the rules above. If you do not put your calculations in your formula in the correct order then the results will be incorrect. In the example below you wish to subtract the Discount price from the Price and then multiply this by the Total Sold 2007.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 121
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
If you type in the cell reference for the Price (C4) and subtract it from the Discount Price (D4) and then multiply it from the Total Sold 2007 (B4), then the formula would look like this: =C4-D4*B4 The result you are looking for is £200 - (£125.00 - £25.00 = £100 * 2 = £200). However when you use the formula =C4-D4*B4 the result is £75.00. Why has Excel returned a result of £75.00?
You get a result of £75 because when Excel calculates a formula it calculates any brackets first. In the formula there are no brackets so it then looks to calculate any powers or square roots. In the formula there are no powers or square roots so it then looks to calculate any division or multiplication. In the formula =C4-D4*B4 there is a multiplication symbol so Excel starts calculating from the multiplication symbol (starting from the left).
So Excel multiplies D4 (£25) by B4 (2) and gets the result of £50. Excel then looks to calculate any addition and then any subtraction. There is no addition in the formula but there is a subtraction symbol, so Excel calculates C4 (£125) subtracts the £50 it has from the previous calculation. This results in a result of £75 (£125 - £50 = £75). So how do you get Excel to calculate the formula and produce the result you are looking for (£200)? Excel 2010 Level 1 – February 2011
Page 122
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
You need to put the part of the formula you wish Excel to calculate first in brackets, because, as you know from the BODMAS rule, Excel will calculate brackets first. You want Excel to subtract the Discount price from the Price, so you put this part of the formula into brackets. So our formula now looks like this: =(C4-D4)*B4 Excel will now calculate the bracket part of the formula first C4 (£125) subtract D4 (£25) and get a result of £100. Now it will take this result (£100) and multiply by the cell B4 (2) and return a result of £200 (£100*2 = £200). As you can see it is important to remember how Excel calculates a formula. Remember the BODMAS rule. Now let’s look at the original example 7 + (6 * 3 + 4) *12. Excel will look for Brackets and deal with what is in the brackets first. Excel will now look inside the brackets for any of the following:- Orders (Powers, Square Roots), Division, Multiplication Addition or Subtraction. Remember also that it works from left to right. Excel will look at Multiplication first
(6 * 3 + 4) 6 * 3 = 18
The calculation in the brackets is now Then Addition
(18 + 4) 18 + 4 = 22
The Brackets have now been dealt with (6 * 3 + 4) = 22 The calculation now looks like this
7 + 22 * 12
Excel will now look again for any Orders (Powers, Square Roots) outside the brackets. There are none so it will now look for Division and Multiplication. It will then see there is another Multiplication. 7 + 22 * 12. So Excel does the Multiplication
Excel 2010 Level 1 – February 2011
22 * 12 = 264
Page 123
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The calculation now looks like this
7 + 264
Excel will now do the final calculation in the formula - Addition. 7 + 264 = 271 Following the BODMAS rule the result is 7 + (6 * 3 + 4) * 12 = 271 As shown below.
Without using the BODMAS rule you would have arrived at one of several different answers. The same calculation, without brackets, gives a totally different answer. See example below.
It is very important to know when and how to use the brackets. What Excel did in the above example was Multiplication first B4 * C4 = 6 * 3 = 18
&
D4 * E4 = 4 * 12 = 48
Giving a calculation
7 +18 + 48
This returns the result
7 + 18 + 48 = 73
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 124
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Remember
The BODMAS rule
B
Brackets first
O
Orders (ie Powers and Square Roots, etc.)
D
Division (left-to-right)
M
Multiplication (left-to-right)
A
Addition (left-to-right)
S
Subtraction (left-to-right)
Excel 2010 Level 1 – February 2011
Page 125
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Recalculating Data Why should you use cell references in Excel? The power of a spreadsheet is in its ability to recalculate when you change any data that is linked to a formula. For example if you change a figure that is in a column with a total that has been created using a formula, then the total will automatically recalculate to take into account the changed figure. To see the need for using cell references, open a new worksheet and type in the following data using the cell references shown below.
You are now going to add column B without using cell references. In cell B7 type the following formula:: =25+32+62 Now press the Enter key on the keyboard. You are going to add column E using cell references. In cell E7 type in the following: =E4+E5+E6 Now press the Enter key on the keyboard. Your totals will be the same in both columns as below.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 126
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Both methods gave the correct result but to see why using the cell references in a formula is so important do the following: In Set One the amount for Sally in cell B4 was incorrect. Click in cell B4 and type in the new amount 35. Press the Enter key on the keyboard. You will see that the total in cell B7 has not changed, even though the amount in cell B4 has changed. This is because you did not use cell references in the formula, so Excel will keep on calculating =25+32+62 every time you change any data and ask it to recalculate. To see the difference cell references make in a formula, in cell E4 type in the new amount 35. Now press the Enter key on the keyboard. You will see that the total in cell E7 has automatically updated to take into account the new figure in cell E4. This is because Excel will take the figures in the cells E4, E5, E6 and recalculate them every time you change any amount in these cells. Remember the formula was =E4+E5+E6, which tells Excel to recalculate these cells each time any figures change in these cells. When you are using a formula to produce any calculations on figures in cells, always use cell references, so your spreadsheet can automatically update any changes made. Close the worksheet without saving the changes.
Remember
Always use cell references in formulas.
If you use cell references in formulas, any changes you make to the cell contents will be automatically recalculated.
If you use the cell contents in a formula, when you change the contents of a cell, the result will not take the new data into account.
Excel 2010 Level 1 – February 2011
Page 127
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Formatting Data Each cell in an Excel spreadsheet has a category or Format set to tell the cell what type of information is stored in that cell. The format can be set by you to tell it the information is a number, a date, a time, a currency, etc. All cells are initially formatted as General. You will not always need to change the format from General because Excel recognises numerical data as numbers and aligns it to the right of the cell. Open a new workbook. In cell D2 enter the number 164 In cell D3 enter the number 6.23. Click on any empty cell. Click on each of the cells into which you have entered the numbers. Notice that the box in the Number group displays that the format is General.
Click on the other cell containing data and check what format is displayed. You should notice that numbers in a General format cell are displayed exactly as you entered them. Numbers in cells formatted a General can be used in calculations. In the next section you will see how you can specify how numbers are displayed in cells. When text is entered into a cell formatted as General it is recognised as text and aligned to the left of the cell. Naturally you cannot perform calculations with text. Type your name in cell E2. Then click into any empty cell to accept the entry. Now click back E2 â&#x20AC;&#x201C; you will see that the category is still set to General even though the cell contains text.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 128
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Formatting Cells When you are using numbers in a spreadsheet it is possible to alter how the numbers will be displayed, for example whether to display the numbers as Integer (whole numbers) or display the numbers after the decimal point. Before you continue open the spreadsheet: Shifty Computer Sales situated in the folder Creating Spreadsheets and Graphs Workfiles. To show how formatting works, select all the cells from B4 to F8. In the Number Group on the Home tab, click on the small arrow in the corner.
This will produce the Format Cells window (below).
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 129
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
To alter how the selected numbers will be displayed in the spreadsheet, click on the Number option in the Category box.
This will display the Number tab from where you can decide how many decimal places your selected numbers will show. As you can see two decimal places are automatically chosen for you.
Click onto the OK button. This will format the numbers to be displayed to two decimal places in the selected cells. Click anywhere to de-select the cells. Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 130
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
You will see that all the numbers in the spreadsheet are now displayed to two decimal places after the full stop.
To display the numbers as whole numbers (Integer) select the cells from B4 to F8. Open the Format Cells window as before and select the Number tab. Click on the Number option in the Category box, if it is not already selected.
Now use the down arrow or type in the Decimal places box and alter the figure to zero. Click on the OK button and the selected numbers will now be altered to Integer. Click anywhere to de-select the cells.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 131
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
You will see that the numbers in the spreadsheet are now displayed as Integers (whole numbers). The Thousand Separator When you have numbers in your spreadsheet that are in the thousands they are better displayed using the thousand separator. The thousand separator will put a comma between the thousand numbers and the hundred numbers. To apply the thousand separator to the figures in the Shifty Computer Sales spreadsheet, select the cells from B4 to F8. Open the Format Cells window as before and select the Number tab Click on the Number option in the Category box and if it is not already selected, click in the Use 1000 separator (,) box to produce a tick.
Now click on the OK button. Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 132
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Click anywhere to de-select the cells. You will now see that all the numbers in the spreadsheet that are in the thousands have a comma inserted (thousand separator).
Applying Currency Formatting When you are using numbers that represent currency (money) then it is advisable to format them to be displayed in a currency format. To format cells as currency, select the cells F4 to F8. Open the Format Cell window and select the Number tab Click on the Currency option in the Category box.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 133
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The UK pound sign is automatically chosen for us and is shown in the Symbol box.
Now click on the Up arrow and alter the number of decimal places to 2.
Now click on the OK button.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 134
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The figures in the Total column have been changed to display a pound currency symbol and 2 decimal places. Click anywhere in your spreadsheet to de-select the cells. Your Total column should look like the example below.
Now select the cell B4 to E8 and apply currency formatting to these cells (make sure that the decimal places are set to 2). Click anywhere in your spreadsheet to de-select the cells. Your spreadsheet should look like the example below.
Close the spreadsheet Shifty Computer Sales without saving the changes.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 135
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Open the spreadsheet Jules Foodstores PLC. Apply a number formatting of 2 decimal places with thousand separator to the cell range B5 to H10. Select the Total sales figures (I5 to I10, this is the capital letter l not the number 1 or the lowercase letter l) and apply a currency formatting (UK pound) using 2 decimal places. Select the sales figures for Bournemouth only (B10 to H10) and apply an integer number formatting (set the decimal places to 0). Select the Grand Total (B12) and apply a number formatting of 3 decimal places. Your spreadsheet should look similar to the example below.
Now close the spreadsheet without saving the changes.
Remember
If you need to display a pound sign in a cell always follow the above instructions, never use the pound key on the keyboard.
When you format cells to be displayed as currency they are usually displayed to 2 decimal places. However you can display them using more or less decimal places if you wish.
Excel 2010 Level 1 – February 2011
Page 136
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Applying Date Formatting Please open the SLEEPEASY spreadsheet, situated in the Spreadsheets and Graphs Workfiles folder. You are now going to apply a different date style to the dates in the SLEEPEASY spreadsheet. Select cell B4, and in the Numbers Group, click onto the small arrow in the corner (Dialog Box Launcher) to open the Format Cells window.
Click on the Date category to display the date format options.
You are going to alter the date format to display the full month (14th March 2001) option, so click on this option and then click onto the OK button.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 137
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Now select the cells C4 to F4 and apply the same date style (full month) as above. You may have to widen the columns to see the full dates. Click anywhere to de-select the cells. The dates on your spreadsheet should now resemble the following example:
Now close the SLEEPEASY spreadsheet without saving the changes. Applying Text Formatting In most circumstances, text entered in a cell formatted as General is perfectly fine. However, sometimes it is better if numbers are formatted as text. For example, it would be meaningless to perform calculations using telephone numbers! In this situation, it would be better to format these cells as text.
Sorting Data In Excel it is possible to sort data in a spreadsheet in ascending, descending, alphabetical or numerical order. This is achieved by using the Sort Smallest to Largest (Ascending) or Sort Largest to Smallest (Descending) commands in the Sort & Filter group on the Data tab..
Sort Smallest to Largest (Ascending)
Sort Largest to Smallest (Descending)
Before you continue open the spreadsheet: SUNNYMEAD (this file is situated in the Spreadsheets and Graphs Workfiles folder in your student area). When you are sorting a range of data in a spreadsheet it is important to select all the cells that have the data you wish to sort. However do not select any headings or totals, as these must not be sorted.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 138
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Select the cell range A6 to G12 only. You should have the following cells selected.
Notice that the headings January, February, March, April, May and June are not selected. You will now sort the data in Smallest to Largest (ascending) order of class name. Ensuring that you have the Data tab selected, click on the Sort Ascending command.
The list of classes will now be sorted in ascending order (from A to Z). Click anywhere in your spreadsheet to un-highlight the selected cells. Your class list should look similar to the example below.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 139
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Notice that the data associated with the classes has also been sorted so that the relevant data stays with the relevant class. This is why it is important to select all the relevant data when you perform a sort. However, if you only select the class names and then click on a Sort command as below, Excel warns you that just one column will be sorted and offers to expand the selection â&#x20AC;&#x201C; see below.
If you leave the option Expand the selection selected and click Sort, the adjacent columns containing data will be automatically selected before the sort is performed. This avoids related data getting mixed up. Remember - always select the relevant data before sorting.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 140
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
You are now going to sort the ‘Classes’ data by descending order (from Z to A). Select the cell range A6 to G12 only. Click on the Sort Largest to Smallest command on the Data tab.
Click anywhere in your spreadsheet to un-highlight the selected cells. Your class data has been sorted in descending order (from Z to A). Close the SUNNYMEAD workbook without saving the changes.
Excel 2010 Level 1 – February 2011
Page 141
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Borders and Shading If you have a spreadsheet with a large amount of data, you may need to make certain parts of the spreadsheet stand out from the rest of the data. Applying either a border or shading around the data can achieve this. Open the spreadsheet Jules Foodstores PLC, situated in the folder Creating Spreadsheets and Graphs Workfiles. To apply a border around data in a spreadsheet, select cell A1 and open the Format Cells window by clicking on the small arrow in the Number Group on the Home tab of the Ribbon.
This will produce the Format Cells window as shown below.
Click on the Border tab at the top of the window.
This will display the border options available.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 142
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Click on the Outline button to produce an outline border around the cell. A preview of how the cell will look is displayed as well as the Line Style options.
Click on the OK button to apply the border around cell A1. Click on a blank cell to un-highlight cell A1.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 143
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
A solid line border has been drawn around cell A1.
Now select cells B4 to H4 and click on the Format menu option and the Cells menu option. Make sure that the Border tab is selected.
Now click on the Outline and Inside buttons. This will apply a border around the highlighted cells and in between them. Now click on the OK button. Click on a blank cell to un-highlight the cells. The cell range B4 to H4 now has a border style applied to it.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 144
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Now select cell A3 and apply an outline border. Applying Shading As well as applying a border to make parts of the spreadsheet stand out you can also apply shading to achieve the same result. Keep Jules Foodstores PLC workbook open. Select cell B3 and open the Format menu as before. To apply shading you will need to click on the Fill tab. Do this now.
This will show you the shading options available. To apply light grey shading to the highlighted cells in the spreadsheet, click on the light grey square.
The sample colour will be displayed here. Click on OK
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 145
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Click on a blank cell to un-highlight the cells. The cell B3 has had light grey shading applied to it.
Now select cell B12 and apply light grey shading. Your spreadsheet should resemble the example below.
Now close the spreadsheet without saving the changes.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 146
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Practice Exercise Three 1) Open the spreadsheet Rusty Widget Sales, situated in the folder Creating Spreadsheets and Graphs Workfiles. 2) Save the spreadsheet in your My Documents folder with the new filename Shining Widget Data. 3) In cell H5 enter the following heading: Total. 4) In cell H6 use the SUM function or Autosum icon to calculate the total sales for widget W45 for the months of January to June. 5) Replicate this formula to show the totals for all the other widgets. 6) Insert a new column with the name July between June and Total 7) Enter the following data into the July column. 521.30 256.30 224.68 986.31 632.22 852.36 741.20 8) In cell J5 enter the following column heading: Price. 9) Copy the cell range B6 to B12. 10) Paste the cell range into cell J6. 11) In cell K5 enter the following column heading: Sales. 12) In cell K6 enter a formula to calculate the Sales for Widget W45 by multiplying the Total by the Price. 13) Replicate this formula to show the sales for all other Widgets. 14) In cell A14 enter the following heading: Widget W50 less W45. 15) Adjust column A so that all the data is visible 16) In cell B14 enter a formula to subtract the sales of Widget W45 (cell K6) from the sales of Widget W50 (cell K11).
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 147
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
17) Select the cell range B6 to H12 and display this range as Integer numbers (zero decimal places). Remember to click on the Number tab in the Format Cells window. 18) Select the cell range I6 to I12 (please note: this is column I not the number 1 or L) and display this range to 2 decimal places. 19) Select the cell range J6 to K12 and display this range to 2 decimal places with a currency symbol. 20) Select the cell range B5 to K5 and apply a border around the outside and the inside of the cells. 21) Select the cell range K6 to K12 and apply light grey shading to this cell range. 22) Delete the text PLC from the title (Cell A1) RUSTY WIDGET PLC. Make sure you still have the text RUSTY WIDGET in cell A1. 23) Save the spreadsheet. Your spreadsheet should look similar to the example below.
Close the spreadsheet.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 148
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Creating Charts and Graphs When you have numerical data in a spreadsheet it can be useful to represent the data as a graph or chart. Before you continue, open the spreadsheet called: Rusty Widget Sales situated in the folder Spreadsheets and Graphs Workfiles. Creating a Column Chart Select cells A5 to G12. Now click on the Insert Tab on the Ribbon You will see that the Charts area of the Ribbon is devoted to creating Graphs and Charts from your data.
There are many different types and styles of chart available. As you move the cursor over each type you see that a message is displayed that describes in what situation that type of chart is typically used.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 149
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
For this exercise you will create a Column Chart. With your data still selected, click on the Column button on the Ribbon.
A menu of different styles of Column chart appears. These are grouped into different categories (2-D Column, 3-D Column, Cylinder, etc.).
You are going to use a standard 2-D Column chart with each month represented by its own column.
Click on the first 2-D Column chart option.
.
Your worksheet should look similar to the diagram on the next page. Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 150
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The chart is currently selected. You know this because there is a border around the chart and the data used in the chart has coloured borders. Click anywhere on the worksheet away from the chart to deselect it. Your worksheet should now look similar to the image below.
Y Axis
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
X Axis
Page 151
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
This area of a chart is called the Legend. It tells you what each of the bar colours represents.
You will now make some changes to the chart. You want to include a title and labels on each of the axes. For this you need to select the chart again. Click on a blank area of the chart to select it.
You will see that the chart border has returned (indicating that the chart is selected)
and the Ribbon has changed to include a new section – Chart Tools.
In the Chart Tools section of the Ribbon are three new tabs – Design, Layout and Format. Under each of these tabs are many options to modify the chart to look just the way that you want. Most of these options are outside the scope of this course but you will use two now to add axis titles to your chart. Excel 2010 Level 1 – February 2011
Page 152
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Click on the Layout tab.
Now click on Axis Titles. Two options appear. First add a title for the X-axis. Click on Primary Horizontal Axis Title.
Further options appear. The second option – Title Below Axis – is the one you need to create a title. . None would remove a title if one existed Click on Title Below Axis now.
An Axis Title box has appeared below the horizontal (X) Axis in which you can type a title.
When the Axis Title is surrounded by a box with a circle in each corner it is selected. If it is not selected, click on the words Axis Title now. If, while it is selected, you click again inside the box, the solid line becomes a broken line (see below) and you can edit the text directly using backspace and delete keys. You can also type in new words and letters.
Excel 2010 Level 1 – February 2011
Page 153
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
For now, make sure that you have a solid line surrounding the words Axis Title. If it is a broken line, click on the worksheet away from the graph. Now move the cursor back over the text (the cursor will become a 4-headed arrow) and make a single click. You should now have the words Axis Title selected and looking as below.
It is very easy to change the title when it is selected like this. Simply type the title that you want to use. Please type Widget now. You will see the words you type appear in the Formula Bar. To accept this as your new title simply press the Enter key or click anywhere on the worksheet.
Formula Bar
The chart should now be displaying the new title (see below).
New title added
The border around the chart shows that it is still selected.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 154
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
To add a title for the vertical (Y) Axis click on the Layout tab, then on Axis Titles.
Click on Primary Vertical Axis Title and select Rotated Title.
The Vertical Axis should now look like this.
Type the new title for the Vertical Axis; in this case Sales. Press the Enter key.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 155
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Finally, to put a Chart Title in place…
click on the Chart Title button
and select the Above Chart Option.
Now type in the title Widget Sales. Click on the worksheet to accept the text and deselect the chart. Your worksheet should look similar to this:
Excel 2010 Level 1 – February 2011
Page 156
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Now reposition the graph so that it is below the table of data. Move your curser over a blank portion of the graph (avoiding the central display area, text and numbers). You will see that the cursor changes to a four-headed arrow behind a white arrow.
Click the left mouse button and hold it down. Move the mouse to drag the chart to a position to the left hand side of your worksheet, just below the data table. Release the mouse button and click on the worksheet to deselect the chart. Your worksheet should now look like this.
Save the workbook in your My Documents area with the title Creating Charts, and keep it open. You will be using it for the next few exercises. Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 157
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Moving and deleting labels Using the chart you have just created, click on the vertical (Y) Axis Title to select it.
600
Place your mouse pointer exactly over one of the sides of the box around the axis label
500
600
When the mouse pointer changes to a 4 point arrow, click and drag the label to a new position while holding the mouse button down. Release the button.
500
A label can be deleted easily. Click on the label to select it and press the delete key on the keyboard. The Chart Title, Axis Labels and Legend can all be moved or deleted in this way
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 158
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
What data to select It is important to select the correct data from a spreadsheet before creating a chart. To demonstrate this using the Creating Charts workbook, first highlight just the numerical data as below.
If you create a column chart from the selected data it will appear as below
The legend and Y Axis Data Labels tell you nothing about the Widget sales.
Now highlight the numerical data and the column headings and create another column chart.
This time you have some of the information you need on the chart but not all.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 159
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Now try the same selecting the row headings and the numerical data and create a column chart.
Again you will have some of the information you need but not all.
Now select column and row headings as well as the data
This time you have produced a chart containing meaningful information
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 160
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Remember
Always highlight the data you want to use before you select your chart type.
Make sure you select the row and column labels so that the chart makes sense of the data.
The vertical axis of a Column chart is known as the YAxis and the horizontal axis is known as the X-Axis
When entering Axis Titles or a Chart Title, clicking on the worksheet away from the chart results in the new title being accepted. The Chart Tools Ribbon options will disappear as the chart is no longer selected. However, clicking on the chart at any time will cause these options to reappear.
Rotated Title rotates text through 90 degrees.
Vertical Title creates a title in which the letters are the right way up but positioned one below the other
When you select to insert a chart into your spreadsheet with the data that was used to create it, it is a good idea to move it to below the data in the spreadsheet. This will allow the chart and the data to be clearly viewed in your spreadsheet.
Keep the workbook open for the next exercise.
Excel 2010 Level 1 – February 2011
Page 161
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Creating a Bar Chart A Bar Chart is very similar to a Column Chart except a bar chart is displayed horizontally in relation to a column chart. Use the data in the Creating Charts spreadsheet to create a Bar Chart. Select the cells A5 to G12 and then click on the Bar option in the Charts group on the Insert Tab. Creating Charts
Click on the first option under the 2-D Bar heading.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 162
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The Bar Chart will be created as below.
Click on a blank area of the chart and drag it to a position next to the Column Chart.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 163
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
A chart can be resized when it is selected by clicking and dragging on any of the areas of the border that are marked with feint dots (for example where indicated by the arrows).
Practise resizing the chart now.
You now need to add the Titles. With the chart selected, click on Layout on the Chart Tools tab and then the Axis Titles button. Select Primary Horizontal Axis Title and then Title below Axis.
With Axis Title selected, type the new Axis Title, Sales.
Use the Enter key to accept the new title.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 164
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Now repeat the process for the Vertical Axis (remember to select the Rotated Title option). Give it the title Widgets. Finally, give the chart a title. Remember that you click on the Chart Title button and select the Above Chart option. Creating Charts
Give the chart the title Widget Sales. Click outside chart to deselect it. Your worksheet should now look similar to this.
Charts do not have to be displayed on the worksheet with the data table. Instead a chart can be positioned on its own worksheet.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 165
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Click on the Widget Sales Bar chart to select it and click on the Design tab. Charts
Now click on the Move Chart button in the Location group. Clicking on the Move Chart button brings up the Move Chart window. Currently the Widget Bar chart is located in Sheet 1.
Click in the circle next to New sheet in the Move Chart window. You can see the default name that will be given to this worksheet is Chart 1. The name can be changed but for this exercise you will leave it as Chart 1. Click the OK button.
The Widget Sales chart has now been moved to a new worksheet called Chart 1 which is now the first worksheet tab.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 166
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Creating a Pie Chart You will now use the Creating Charts spreadsheet to create a Pie Chart for the sales of Widget W45. If you do not already have the Sheet1 worksheet displayed, click on the tab now.
Select the cells A5 to G6 On the Insert Tab of the Ribbon, click on the Pie option. Creating Charts
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 167
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Several Pie Chart options are displayed in a drop-down menu. Positioning the cursor over one of the options causes a box to be displayed briefly describing the typical use for that style of Pie chart. Creating Charts
Select the first style of Pie chart. Sheet 1 should now look similar to the image below. You will notice that the Ribbon has now changed to display options for the Pie chart. Options that allow percentage for each segment to be displayed and legend position to be varied.
Options for different display colours
Creating Charts
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 168
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The Pie chart has been given the title of Widget W45 because only the data for this Widget was selected.
Notice also that a Legend has been created to show what each coloured segment represents. However, a Pie chart can be more easily understood if instead of using a Legend, the segments are labelled directly. This option is available on the Ribbon. If you have the Pie chart selected, the Chart Tools options will still be displayed on the Ribbon. Creating Charts
If you do not currently have the Pie chart selected, please click on it now to select it. Seven different Layouts are available for Pie charts. To view these, click on the arrowhead symbol with a line above it. Creating Charts
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 169
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
To see how each of these affects the appearance of the Pie chart move your cursor over the first option. You will notice that a box appears labelling this option as Layout 1. Creating Charts
Now click on each of these seven options in turn to see how the look of the Pie chart changes. You will see that you can display either the number of Widgets sold per month (exactly as in the table) or each month as a percentage of the total sold in the months in the table (January to June). In addition, the categories can be labelled either directly on the Pie chart segments or in a Legend. Although the Layout graphics on the seven chart options are small, a percentage symbol, a legend symbol, a wide line representing a chart title etc. help you chose the layout that you want. The table below summarises the display provided by each of the seven options. You do not need to memorise this; the display you want can easily be found by clicking on the different layouts provided. Layout
1 2 3 4 5 6 7
Number of Widgets sold
Percentage of total Widgets sold
Category name on segments
YES YES
YES
YES
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Legend displayed
YES YES YES YES
YES
YES YES
Page 170
Chart Title displayed
YES YES
YES YES
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Display the seven Pie chart style options by clicking on the down-pointing arrowhead.
Select the layout that will display the Chart Title and label the segments with the category name on the segments only Your Pie chart should now look like this:
In the same way that you moved the Bar Chart, you can move the Pie Chart to its own worksheet. With the chart still selected click on the Move Chart button on the Ribbon. Creating Charts
Note that the Move Chart window has automatically offered Chart2 as the name for the new sheet. This is because the Workbook already has a Chart1 worksheet.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 171
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Click on New Sheet and then OK.
The Widget Pie chart has been moved from Sheet1 to the newly created Chart2 worksheet.
Creating a Line Chart A Line Chart uses a line to represent the selected data in a spreadsheet. Use the Creating Charts spreadsheet again to create a line chart for the sales of the Widget W45 for the months January to June. Click on the Sheet1 tab at the bottom of the spreadsheet. Select the cells A5 to G6 (if they are not already selected) and with the Insert Tab of the Ribbon selected, click on Line in the Charts group Creating Charts
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 172
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Select the Line with Markers option. Creating Charts
Your Line graph should now look similar to this:
Once again you have various options for changing exactly what is displayed on your chart and the overall look. With the graph selected the Chart Tools are displayed on the Ribbon. Click on the Design tab then click here to display layout options.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 173
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Creating Charts
To add a Chart Title and a Title for each Axis, click on the graphic that includes all of these titles (Layout 10).
Your graph should look similar to this:
Excel has made a guess at the Chart Title that you might want to use based on the data selected. However, you need to insert suitable titles for each of the axes. Click on the words Axis Title to select it. A border with a circle in each corner will surround the words.
Type the new heading Month then press Enter. Repeat the process to rename the Vertical Axis: Sales. Finally, click on Move Chart on the Ribbon Move the chart to its own worksheet named Chart3 Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 174
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Note: When you created your first chart, the Column chart, you added titles using the Layout tab on the Ribbon. This method allows chart options such as title positions to be selected individually. reating Charts
For the Pie and Line charts you used the options available on the Design tab in Chart Tools. The Design tab provides pre-set styles and is quicker to use. Creating Charts
Both methods are available for all chart types and they are equally acceptable. You can use whichever you are most comfortable with. Keep the Creating Charts workbook open for the next exercise.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 175
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Displaying Values in a Line Chart When you create a line chart it is sometimes beneficial to display the values for each data point. With the Creating Charts workbook open you are going to add values for each data point in a Line Chart. Click on Sheet1 tab at the bottom of the spreadsheet and select the cell range A5 to G7. Click on the Line option in the Charts group on the Insert tab. Charts
Select this option from the menu that appears (Line with Markers)
Add the following tiles to the chart: Chart title: Widget Sales. X Axis label: Month. Y Axis label: Sales
The chart should now look similar to this.
Make sure the chart is selected to add the data labels.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 176
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Click on the Chart Tools Layout tab and then on Data Labels.
The menu provides options for where the data labels are positioned in relation to the data points. This allows you to select the best position to display the figures clearly.
For this exercise, select the menu option Above.
The data labels will be positioned above the corresponding data point on the chart.
Finally, with the chart still selected, click on the Chart Tools Design tab. Use the Move Chart button to relocate the chart in a new sheet named Chart4. Keep Creating Charts open for the next exercise. Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 177
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Creating A 3D Chart Now use the Creating Charts workbook to create a 3D Column chart for the sales of Widgets. Click on the Sheet1 tab at the bottom of the spreadsheet. Select the cells A5 to G12 and click on the Column button on the Insert tab of the Ribbon. Choose the 3-D Clustered Column option. Creating Charts
Now, with the chart selected, add titles to the chart as you have done before (Chart Tools Layout tab). Add a Chart Title using the Above Chart option. Give the chart the title Widget Sales. Using the Rotated Title for the Vertical Axis Title and give it the title Sales Use the Title below Axis for the Horizontal Axis Title and give it the title Widget Type. Move the chart to a new sheet using the Move Chart button on the Chart Tools Design tab.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 178
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Your chart should look similar to the following example.
Changing the Chart Type When a chart has been created, it can easily be changed to a different type of chart without going through the process of creating a new chart. To change the Bar chart that was created earlier in this chapter to a Column chart click on the Chart 1 tab to display the Widget Sales Bar chart.
Click anywhere on the chart to select it. This will cause the Chart tools options to be displayed.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 179
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Make sure that the Design tab is selected. Creating Charts
Now click on the Change Chart Type button in the Type group on the Ribbon.
In the Change Chart Type window, click on the Column option.
Now click on the Clustered Cylinder option.
Click on OK to change your chart to the new type selected.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 180
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Your new chart should look similar to this:
Keep this chart open for the next exercise. Changing Chart Colours To change the colours in the Widget Sales chart, click the in the outer region of the chart, for example where indicated below.
This has selected the entire chart area and the Chart Tools options are available on the Ribbon. Select the Format tab. Creating Charts
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 181
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Click on Shape Fill in the Shape Styles group.
From the colour options click on a medium blue colour.
The entire chart area should now be coloured blue. Now select the Plot area by clicking somewhere within the area defined by the x and y axes, for example here.
Note that the Clustered Cylinder chart is an example of a 3-D chart. The plot area for 3-D charts is termed the Back Wall.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 182
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Click on shape fill and this time select a pale green colour fill. The Back Wall area should now be green.
The colours of each of the cylinders representing the different monthâ&#x20AC;&#x2122;s sales can also be changed. To change the colour of the Orange cylinders representing the data for June, click on any of the Orange cylinders. Again click on Shape Fill. This time select the colour Tan.
All of the orange cylinders and the legend for June should now have changed to the new colour. Deleting a Chart It is very easy to delete a chart that is no longer wanted. This applies to whether the chart is on its own worksheet or is an object in a worksheet alongside data. You are now going to delete the Widget Sales Clustered Cylinder chart that you have just created.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 183
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
First you need to select the whole chart by clicking on it. Clicking on the centre portion of the graph (columns, axes, labels etc.) will not select the whole chart. You need to click anywhere in the outer chart area.
Click on a suitable area to select the chart and then press the Delete key on the keyboard. The chart should disappear. Now save the changes to the Creating Charts workbook and close it.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 184
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Changing the Chart Scale When you create a chart the scale for the vertical axis is set automatically. The scale is how the numbers that run down the side of your chart are spaced. However it is possible to alter the scale manually Open the spreadsheet Jules Foodstores PLC, situated in the folder Spreadsheets and Graphs Workfiles. Select the cell range A4 to H5. Create a line chart from the selected cell range. These styles of Line chart do not include a symbol (normally a diamond shape or a square) for each data point. They only display the line that connects them. These styles of line chart do display a symbol for each data point.
Please choose this style.
Name the X-axis: Days Of The Week. Name the Y-axis: Sales. Move the line chart to a new sheet. Your line chart should look similar to the example below.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 185
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
To change the scale of the Vertical Axis, make sure that the chart is selected.
Now click on the Axes button of the Chart Tools Layout tab. Choose Primary Vertical Axis
and then More Primary Vertical Axis options.
You should now have the Format Axis window displayed – as below. The Vertical Axis will also have a border around it. Notice that under Axis Options, the minimum and maximum both have Auto selected. You can also see the scale range that is currently set by the Auto process. You are going to change the Vertical Axis (Sales) scale from its current range of 0-500 to a new range of 50 – 800.
Excel 2010 Level 1 – February 2011
Page 186
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Click in the minimum Fixed circle to select this option.
The value box should now be available to edit (not greyed out). Delete the figure of 0.0 that is in the value box and type 50. Repeat the process to set a new maximum value for the vertical axis of 800. Your chart should now look similar to the chart below.
The Y-axis has now been altered to start (minimum) at 50 and end (maximum) at 800 (although the way the chart is displayed results in the scale stopping at 750).
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 187
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Using the same process, alter the Y-axis to start (Minimum) at 10 and to end (Maximum) at 820 Your Y-axis should look like this example.
The scale starts at 10 and ends at 820.
Now close the Jules Foodstores PLC spreadsheet without saving the changes.
Selecting Non-adjacent Cells So far in this book you have created charts using data that are adjacent (in the same columns or rows) to each other. It is possible to create a chart from data that is contained in non-adjacent cells. Before you continue open the spreadsheet: BUDGET, situated in the folder Spreadsheets and Graphs Workfiles. Letâ&#x20AC;&#x2122;s now practice creating a chart using data in the BUDGET spreadsheet that are in non-adjacent cells. Select cells A3 to A7 and then press and hold down the Ctrl key on the keyboard. Now continuing to hold the Ctrl key down, select cells C3 to C7, cells E3 to E7 and cells G3 to G7. Now release the Ctrl key.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 188
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The following cells have been selected in your spreadsheet.
Now create a Bar chart choosing the first option, Clustered bar, from the selected cells. Remember that the button for this is on the Insert tab. Give the chart the title: Monthly Bill Amounts. Give the Category (X) axis the title: Utilities. Give the Category (Y) axis the title: Amount. Insert the chart in a new sheet. You have now created a chart using data from non-adjacent cells. Your chart should look similar to the example below.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 189
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Now click on Sheet1.
Click in a clear part of your spreadsheet to de-select the cells. Now select the following non-adjacent cells: A3 to B5, A7 to B7, A10 to B10 and A12 to B12. You should now have following cells selected.
From the selected cells create a Pie chart using the first Pie chart style. Accept the default title name for the chart. Insert the values next to each segment and keep the legend. Insert the pie chart in a new sheet in the spreadsheet. Your pie chart should look similar to the example on the next page.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 190
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Save the workbook with the new name of: Budget Chart Sales. Close the workbook.
Remember
To select non-adjacent cells in a spreadsheet use the Ctrl key on the keyboard.
Excel 2010 Level 1 – February 2011
Page 191
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The Different Parts of a Chart Chart Title
Widget sales 1000 900 Axis Label 800 700 600 500 400 300 200 100 0 Y Axis
Data Series
Scale Legend
Sales
January February
March April May June Widget W45
Widget W46
Data Label
Widget W47
Widget W48
Widget W49
Widget
Widget W50
Widget W51
X Axis
Axis Label
This is the chart you created and saved as Creating Charts. Open this workbook now and spend some time altering and deleting the various components. Remember you can use the Undo command on the Quick Action Toolbar to undo any changes you make.
Remember
If a part of the chart is selected it can be changed or deleted
You can change the Axis Titles and the Chart Title by selecting it, typing the new name and then pressing Enter
By clicking in the area surrounding a chart you can change the colour using the Chart Tools Format tab.
By clicking on the chart area itself the colour of that area can be changed using the Chart Tools Format tab.
By clicking on one of the Data Series you can change the colour using the Chart Tools Format tab. The legend will automatically update to show the colour change.
Chart Titles and Axis Labels can be moved to different positions or deleted when they are selected.
Excel 2010 Level 1 – February 2011
Page 192
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Headers and Footers The Header and Footer area is situated at the top and bottom of the spreadsheet. Anything typed or inserted into this area will appear on every page printed. In Excel you can only see the Header and Footer in Page View and Print Preview. Open the spreadsheet Rusty Widget Sales, situated in the folder Spreadsheets and Graphs Workfiles. Select the Insert Tab on the Ribbon.
Now click on the Header and Footer button in the Text group The spreadsheet is now shown in Print View (below). This view is helpful when checking how a worksheet will look when printed. Adjusting how worksheets will be printed will be dealt with in a later chapter.
Three areas where your own Header text can be typed also appear when you click on the Header and Footer button. Notice also that the screen splits – do not be concerned as this will close when you have finished this task.
Excel 2010 Level 1 – February 2011
Page 193
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Notice also that the Ribbon now displays Header and Footer Tools and a Design Tab.
The tools available allow the automatic entry of a range of useful information, for example the page number, where the file is saved on your computer (the file path), the date etc. You can even insert pictures into a Header. You will now add your own text to the Header in the left and right Header sections. Click in the left Header section. This section will become outlined. Now type your name.
Click in the right Header section and type the words Highbury College.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 194
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Now click on the Go to Footer button.
You will see the Footer area of the page, again divided into three sections.
As well as typing text directly into the Header or Footer boxes you can insert automatic entries (often called fields) such as page numbers, date and time, filename, etc. To insert automatic fields into the Footer section of the spreadsheet, first click in the left section and then click on the Page Number button on the Ribbon.
You will see that &[Page] has been added to this section
This is how the automatic page number option is displayed when you have this section of the Header and Footer selected. When you select another area of the Footer it will change to show the actual page number.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 195
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Now click in the middle section of the Footer to select it and then on the Current Time button.
This will insert the actual time in the middle Footer section when you print a spreadsheet. Finally, click in the right section of the Footer and then on the Current Date button.
Your Footer should look like this.
As the right section of the Footer is selected, the Automatic Date symbol is still showing – click into another cell to see the date displayed.
Excel 2010 Level 1 – February 2011
Page 196
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
To close the Header and Footer, first click away from the Footer area on any cell on the worksheet. For example here
The Header and Footer tools are now turned off but if you select the View tab you can see that the display remains in Page Layout view.
To return the display to Normal, click on the Normal button.
Note that the dotted lines shown on the page indicate the page boundaries when you print.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 197
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Everything within the dotted lines will be printed on a single page. You can get an accurate view of how your page will look when printed by selecting the File tab and clicking on Print. Try this now.
The right side of the Print Preview and Print window shows exactly how your Header and Footer will appear on the printed page, as below:
Select the Home tab to return to the Normal View. Now close the spreadsheet without saving the changes.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 198
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Entering Headers and Footers on a Chart Worksheet. Open the spreadsheet BUDGET, situated in the folder Spreadsheets and Graphs Workfiles. Create a Column Chart to show Gas and Electricity costs for the months January to June. Move this chart to a new sheet called Chart 1. As before, click on the Insert Tab and then on Header and Footer. The Page Setup window appears. Click on Custom Header.
You now see the Header window with the various automatic entry options, as before. If you move the cursor onto each one a description of what it does appears. Page Number
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Date
Page 199
Time
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Click in the left section and type your name. Click on OK. Click on Custom Footer.
In the left section add an automatic page number.
In the right section add an automatic date field.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 200
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
To Print Preview the chart click on Print Preview in the Page Setup window.
Your page should look similar to the one below (note the Header and Footer).
Close the spreadsheet without saving the changes.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 201
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Practice Exercise Four Open the spreadsheet Jules Foodstores PLC, situated in the folder Spreadsheets and Graphs Workfiles. 1. Save the spreadsheet in your Documents folder with the new filename Budget Food Centres. 2. Delete the contents in cell A1. 3. In cell A1 enter the following text: Budget Food Centres. 4. Create a column chart showing the sales for the Portsmouth store for the days Monday to Sunday. 5. Enter the following as the chart title: Portsmouth Store Sales Week One. 6. Enter the following as the title for the X-axis: Days of the Week. 7. Enter the following as the title for the Y-axis: Sales. 8. Produce the chart on a new sheet separate from the data source (Chart 1). 9. Using the data in Sheet1, produce a bar chart for all the stores for the sales Monday to Wednesday only. 10. Enter the following as the heading for the chart: Sales All Stores Mon â&#x20AC;&#x201C; Wed. 11. Enter the following as the title for the X-axis: Stores. 12. Enter the following as the title for the Y-axis: Sales. 13. Produce the chart on a new sheet separate from the data source (Chart 2). 14. Using the data in Sheet1, produce a pie chart for the store Portsmouth for the sales for Monday to Sunday only. 15. Enter the following as the heading for the chart: Portsmouth Sales. 16. Produce the chart on a new sheet separate from the data source (Chart 3). 17. Using the data in Sheet1, produce a line chart for the stores Portsmouth and Fareham for the sales for Monday to Sunday only. Use the Lines with Markers option 18. Enter the following as the title for the chart: Sales for Portsmouth and Fareham. 19. Enter the following as the title for the X-axis: Days of the Week. 20. Enter the following as the heading for the Y-axis: Sales.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 202
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
22. Produce the chart with the data used to create it (see below).
23. Move the chart below the text Grand Total. 24. Set the Y-axis range from 20 to 1020. 25. Using the data in Sheet1, produce a column chart for only the stores Portsmouth, Southampton and Bournemouth for the sales for Monday to Sunday. Make sure that the days of the week are visible in the chart. 26. Enter the following as the heading for the chart: Sales for Main Stores. 27. Enter the following title for the X-axis: Days of the Week. 28. Enter the following as the title for the Y-axis: Sales. 29. Produce the chart on a new sheet separate from the data source (Chart 4). 30. In the header area of the chart, enter your name in the Left section, your centre number in the Centre section and an automatic date field in the Right section. 31. Using the data in Sheet1, produce a pie chart for the store Gosport for the sales for Monday to Sunday only. 32. Delete the legend and insert Category Name data labels and Percentages for each segment of the pie chart. 33. Enter the following as the heading for the chart: Gosport Sales. 34. Produce the chart on a new sheet separate from the data source (Chart 5). Make sure that the legend showing days of the week is not present. 35. Save the spreadsheet with the name Budget Food Centres. Check your answers with the answers on the next few pages.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 203
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Sheet1 on the spreadsheet should look similar to the example below.
The Line Chart, showing the data used to create it, should look similar to the example below.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 204
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Chart1 should look similar to the example below.
Chart2 should look similar to the example below.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 205
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Chart3 should look similar to the example below.
Chart 4 should look similar to the example below in Print Preview.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 206
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Chart5 should look similar to the example below.
Close the spreadsheet.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 207
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Printing A Spreadsheet Open the file Shifty Computer Sales, (situated in the folder Spreadsheets and Graphs Workfiles), which will look like the example below.
A spreadsheet has two possible views, Normal View or Formula View. If you can see the values but not the formulas in your spreadsheet, like the example above, then you are viewing the spreadsheet in the Normal View. If you change the view to show the formulas in your spreadsheet then you are viewing the spreadsheet in the Formula View. When you print your spreadsheet it is possible to print in either Normal View or Formula View. It is sometimes useful to print in both views as the Normal View will show you the values in your spreadsheet and the Formula View will show you the formulas used to produce the values. Before you print your spreadsheet it is important to check your data is fully displayed.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 208
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Print Preview Before you print any spreadsheet it is always advisable to Print Preview the document. There are two ways to print preview a spreadsheet.
You can click on the Print Preview and Print icon on the Quick Access Toolbar (you added this icon earlier – if it is not there, follow the instructions on page 8),
or you can select the File Tab and then click on Print.
Either way you will then see the Print Preview and Print page.
Excel 2010 Level 1 – February 2011
Page 209
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
This screen will show you exactly how your spreadsheet will print.
At the bottom right of the screen are two icons. The one on the left turns the margin and column markings on and off.
The one on the right zooms in and then out to Page View. Try clicking on these icons to see how the preview changes, If your spreadsheet contains more data than the Shifty Computer Sales spreadsheet, it might not fit on a single page. You can tell how many pages will be printed by looking at the middle of the bottom of the Print Preview screen.
In this case you are viewing Page 1 of a document that will consist of just 1 page. In other words, you are looking at the whole document. If the document has more than one page you can click on the arrow to see the following pages
. To return to the Normal View select the Home tab.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 210
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Printing Options The middle section of the Print Preview and Print window gives several options for printing out your spreadsheet
The up and down arrows allow you to print more than one copy
This is the printer your computer is connected to – you will have a different printer name here The down arrow gives the choice of printing only the active sheets or the entire workbook You can print a specified range of pages
To print the spreadsheet you would click on the Print button. Do not do this as you will not be printing at the moment
Remember
Always print preview your spreadsheet to make sure the data is displayed as you want it.
Make sure that all your data is fully displayed before you print.
Make sure you have specified what page range you want to print and how many copies you need.
Excel 2010 Level 1 – February 2011
Page 211
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Printing in Formula View Printing in the Formula View is the same as printing in the Normal View that you practiced on the previous pages. You must however change the view of your spreadsheet to the Formula View before you Print Preview or print the spreadsheet. First select the Formulas Tab and then click on Show Formulas.
Your screen will now look similar to the one below.
This dotted line that appears down the middle of the spreadsheet is showing you where Page One ends and Page Two begins. Any data that is to the right of the dotted line will appear on Page Two.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 212
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Print Preview the spreadsheet which will look like this:
The left side – page 1 – of the spreadsheet is shown here
This is showing that you are looking at page 1 of 2 pages Using the Fit Sheet on One Page Option If you wish to print the spreadsheet using just one page but the data covers more than one page, then you can use the Fit to One Page option to make the data fit onto one page.
Excel 2010 Level 1 – February 2011
Page 213
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
..
The centre area of the Print Preview and Print page has a command which allows to you adjust the scaling of the spreadsheet
Click on the down arrow next to the No Scaling option and then click on Fit Sheet on One Page.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 214
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The Print Preview display will now show that all of the data has been fitted onto one page, as below.
Remember that you can click on Zoom (bottom right of the screen) to see the page more clearly. Now select the Home tab to return to Formula View. Landscape or Portrait Orientation Depending on the arrangement of data in your spreadsheet, it can be a good idea to change the orientation of the spreadsheet to landscape before you print. In landscape orientation you will be able to show more data on one page than you can in portrait orientation. This is portrait orientation.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
This is landscape orientation.
Page 215
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
On the Print Preview and Print page you can change the page orientation to see if the data fits better.
Click on the down arrow next to Portrait Orientation and click on Landscape Orientation
The print layout has changed from Portrait to Landscape.
As you previously selected Fit Sheet on One Page, the data has been adjusted to fit on the page. However, in this case, the data will be larger and easier to read. Select the Home tab to return to Formula View. Keep the Workbook open for the next exercise.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 216
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Show Row and Column Headings In a spreadsheet it is quite useful to print the Row and Column headings. They can be printed in either the Normal View or the Formula View. With the Shifty Computer Sales workbook open and in Formula View select the Page Layout tab and click to put a tick in the Print box under Headings in the Sheet Options group.
If you now click on the Print Preview and Print command the column and row headings are shown on the spreadsheet.
Print Preview now shows the Row and Column headings. These will be printed on the page as well as the data. Select the Home tab to return to the Formula View
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 217
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Showing the Gridlines When Printing When you print a spreadsheet the gridlines normally donâ&#x20AC;&#x2122;t show. If you wish to show the gridlines on your printout you will have to switch this feature on. With the Shifty Computer Sales workbook still open and in Formula View select the Page Layout tab and click to put a tick in the Print box under Gridlines in the Sheet Options group.
If you now click on the Print Preview and Print command the The gridlines are now visible and will show when you print.
Now select the Home tab to return to Formula View. Margins The margins are the area around the outside of your printout. If you wish to fit more data onto the page before you print, you can alter the amount of space the margin area takes up on your page. Select the Page Layout Tab.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 218
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Now click on Margins to display various options.
Click on the Narrow option to have the maximum area for printing. If you now click onto Print Preview you will see that the margin on the left is now smaller. The margin above the data table appears to be wide. This is because in addition to the margin, a space has been left for the header.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 219
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
For precise control of margin sizes select Margins on the Page Layout tab
and then Custom Margins.
This opens the Page Setup window on the Margins Tab.
The margin sizes are displayed in centimetres. You can either use the up or down arrows to adjust each of the four margins, or delete the current margin size and type in the new size.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 220
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Change the values for each margin and the Header and Footer by deleting the existing value and typing in the values displayed below.
Click on Print Preview in order to see the effect the margin change has made. Now change the Top, Bottom, Left and Right margins to the new measurement of 1cm. Use Print Preview to view the new margins. As well as using the Page Layout tab to show the column and row labels, the grid lines and to change the margins, these options can also be altered using commands on the File tab and Print option as shown on the next page.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 221
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Click on the down arrow to alter the margins and access the Custom Margin option
Click on this to open the Page Setup window
The Page Setup window has 4 tabs: 1. Page – where you can change the Orientation 2. Margins – to alter page Margins 3. Header and Footer – to add Header and Footer 4. Sheet – to choose to print Row And Column Headings and Gridlines (see next page).
Excel 2010 Level 1 – February 2011
Page 222
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Now close the spreadsheet without saving the changes.
Printing a Chart Open the Budget spreadsheet situated in the folder Spreadsheets and Graphs Workfiles. You are going to produce a column chart using two non-adjacent rows.
Remember
Use the Fit Sheet on One Page option to display data on one page,
If you have a lot of data covering more than two pages using the Fit Sheet on One Page option, might make the data so small you will be unable to read it.
You can change the Page Orientation, print Row and Column Headings and print Gridlines using commands on the Page Layout tab..
These commands can also be accessed using the File tab and Print option
Excel 2010 Level 1 – February 2011
Page 223
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Select the cells range A3 to G3 and A6 to G6. Remember to press and hold the Ctrl key on your keyboard to select the second range.
Create a 2D Column chart. (select the Insert tab and click on the Column command in the Charts group.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 224
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Select the Layout tab to make the following changes. Leave the Chart Title as ELECTRICITY. Give the X-Axis the title MONTHS. Give the Y-Axis the title COST. Delete the Legend from the chart. Move the chart to a new position below the data in the spreadsheet (click into the chart, hold down your left mouse button and drag it below the data). Your chart should look similar to the example below. Notice the small dots around the edge of the chart. These are called re-sizing handles.
If you click on a chart which is in a spreadsheet with the data, re-sizing handles are displayed indicating that the chart is selected. If you click in a blank area in your spreadsheet the re-sizing handles will disappear and the chart is de-selected. If you print a spreadsheet with the chart selected, only the chart will print. If you print the spreadsheet with the chart de-selected the chart and the data will print. To demonstrate this, click on the chart to select it and then click on the Print Preview and Print icon on the Quick Access Toolbar.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 225
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The chart will print filling the whole page.
Select the Home tab, de-select the chart by clicking outside it and click on Print Preview and Print again.
The chart will appear on the page with the data
The Print button will print one copy of the spreadsheet or selected chart. Do not click this button at the moment.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 226
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Now save the spreadsheet in your My Documents folder with the filename Budget Chart.. Now close the spreadsheet.
Remember
If you have placed a chart as an object in a spreadsheet and you want to print the chart along with the data, make sure the chart is de-selected.
If you have placed a chart as an object in a spreadsheet and you want to print the chart alone, make sure the chart is selected ie. You can see the sizing handles on the sides and corners.
Always check the Print Preview carefully to make sure you are going to print exactly what you want.
Excel 2010 Level 1 – February 2011
Page 227
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Interactive Exercise It is now time to consolidate what you have just learned by completing the following exercise. Open the following file, which is located in the Spreadsheets and Graphs workfiles folder. ď&#x201A;ˇ
TEST 02V2Q
This file is a PowerPoint Quiz, which will test your knowledge of spreadsheets and graphs. Double click the left mouse button to open the file and work through the exercise. When you have completed this exercise successfully, then close the application down and return to this module book. If you are unsure how to close the application down then please ask a tutor for assistance.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 228
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Consolidation Exercise One There are instructions in this exercise to produce prints of your work. As this is a consolidation exercise you may find it helpful to produce prints. However, if you are able to check your work using the Print Preview option it would be greatly appreciated in order that paper is not wasted. 1. Create a new spreadsheet. 1. Set the page orientation to landscape. 2. Enter the following data ensuring that the data will fit into the column widths.
3. In the header enter: a. Your name b. Your centre number c. An automatic date field. 4. Save the spreadsheet in your Documents folder using the filename Car Sales. 5. In cell H4 enter the text Total. 6. In cell I4 (this is the letter I not the number 1) enter the following text Sales. 7. Make sure that all the data is fully displayed in your spreadsheet. You are requested to make some calculations. 8. In the Total column, use the SUM function to calculate the Total for the VW Golf 1.6 data (Jan to June inclusive). 9. Replicate this formula to show the Total for all other models. 10. Insert a new column with the label Price between Total and Sales.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 229
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
11. Enter data into the Price column as follows: Model Price VW Golf 1.6 199.98 Ford Fiesta 1.1 89.99 Renault Clio 1.3 180 Vauxhall Astra 1.6 150 VW Polo 1.1 129.1 You are requested to calculate the sales figures. 12. Insert a formula to calculate the Sales for the VW Golf 1.6 by multiplying the Total by the Price then multiplying this figure by 1.05. 13. Replicate this formula to show the Sales for all models. 14. Save the spreadsheet keeping the same filename. 15. Make sure all data is displayed in full. 16. Print one copy of the spreadsheet on one page in landscape orientation, showing the figures, not the formulae. You are requested to make some changes to the spreadsheet file called Car Sales. 17. The company no longer rents out the model Renault Clio 1.3. Delete this row. 18. Make sure blank cells do not remain. 19. Apply the following alignment: 20. Centre the label Model. 21. All other text in the first column should be displayed as left aligned. 22. Display all numeric data as right aligned. 23. Format the numbers as follows: 24. Display the figures in the Price column with a currency symbol and to 2 decimal places. 25. Display the figures in the Sales column with a currency symbol and in integer format (zero decimal places). 26. Display the figures in the other columns in integer format (zero decimal places). Do not alter the Sales or Price columns. 27. Add a single outside border around all the column labels starting with Model and ending with Sales.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 230
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
28. Save the spreadsheet in your Documents folder with the new filename Car Rentals. Make the following changes to the spreadsheet file called Car Rentals. 29. The VW Polo 1.1 must be renamed VW Polo 1.3. 30. The Apr figure for Vauxhall Astra 1.6 must be changed to 56. 31. The price of the Ford Fiesta 1.1 must be changed to 91.99 (the currency symbol must remain displayed). 32. Make sure that the sales figures have been updated as a result of these changes. 33. In the Model column, in the row below the VW Polo 1.3, enter the label Overall Sales. 34. In the Overall Sales row, at the bottom of the Sales column, use a SUM function to calculate the total of the Sales. 35. Make sure this Overall Sales figure is displayed with a currency symbol and zero decimal places. 36. Save the spreadsheet keeping the filename Car Rentals. 37. Make sure gridlines will be displayed on the printout. 38. Print one copy of the spreadsheet on one page in landscape orientation showing the figures, not the formulae. 39. Display the formulae. Make sure the formulae are displayed in full. 40. Make sure the page orientation is landscape and the spreadsheet fits on one page. 41. Make sure that gridlines and row and column headings (1,2,3 and A, B, C â&#x20AC;Ś) will be displayed when printed. 42. Save the spreadsheet using the filename Rcars. 43. Print the entire spreadsheet on one page in landscape orientation showing the formulae. 44. Make sure all formulae are displayed in full and are readable on your printout. 45. Close the file Rcars. You have been asked to produce graphs to show certain data in a spreadsheet. 46. Open the spreadsheet Jumpstart Car Sales found in Spreadsheets and Graphs Workfiles. 47. Create a comparative line graph showing the model sales forecast of VW Golf 1.6 and Ford Fiesta 1.1 from Jul to Dec inclusive. Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 231
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
48. Display the months along the X-axis. 49. Give the graph the heading Model Sales Forecast. 50. Give the X-Axis the title Month 51. Give the Y-Axis the title Forecast Sales. 52. Use a legend to identify each line. Make sure that the lines and/or data points are distinctive and can be clearly identified when printed. 53. Display the values (numbers) for each data point on both lines. 54. Make sure that the chart is created on a full page on a sheet that is separate from the source data. 55. Set the Y-Axis range from 80 to 240. 56. In the header enter: d. Your name. e. Your centre number. f. An automatic date field. 57. Create a comparative column chart showing the model sales forecast of all the models from Jul to Dec inclusive, on sheet one. 58. Give the graph the heading Model Sales Forecast. 59. Give the X-Axis the title Month 60. Give the Y-Axis the title Forecast Sales. 61. Make sure that the chart is shown with the data that was used to create it. 62. Move the chart to be positioned to the left hand side below the data in the spreadsheet. 63. Print just the chart not the chart and the data. 64. Save the file using the filename Mdgraph. 65. Print one copy of the line graph. Check your answers with the answers with the following then close the file and exit the software.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 232
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The spreadsheet Car Sales.
The spreadsheet Car Rentals.
The spreadsheet Rcars in Formula View.
The spreadsheet Rcars in Formula View (close up).
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 233
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The following line chart uses the data from the Jumpstart Car Sales spreadsheet (saved as Mdgraph).
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 234
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The following column chart uses the data from the Jumpstart Car Sales spreadsheet.
The chart has been positioned with the data that was used to create it. It will be printed alone if it is selected first.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 235
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Consolidation Exercise Two There are instructions in this exercise to produce prints of your work. As this is a consolidation exercise you may find it helpful to produce prints. However, if you are able to check your work using the Print Preview option it would be greatly appreciated in order that paper is not wasted. 1. Create a new spreadsheet. 2. Set the page orientation to landscape. 3. Enter the following data, leaving the Total and Takings rows blank as shown.
4. In the footer enter: a. Your name b. Your centre number c. An automatic date field. 5. Save the spreadsheet in your Documents folder using the filename Digital Camera Data. 6. Make sure that all the data is fully displayed in your spreadsheet. You are requested to make some calculations. 7. In the Total row, use the SUM function to calculate the Total for the EOS 350D. (Sunday to Saturday inclusive). 8. Replicate this formula to show the Total for all other camera models.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 236
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
9. Insert a new column with the label EOS 1D between EOS 20D and EOS 5D 10. Enter data into the EOS 1D column as follows: Sunday Monday Tuesday Wednesday Thursday Friday Saturday Price
11 8 15 6 9 9 5 650.11
11. Replicate the formula for the Total for the EOS 1D column. 12. Delete the Sunday row making sure blank cells do not remain where the data was deleted. 13. In the Takings row, use a formula to calculate the takings for the EOS 350D by multiplying the Price by the Total. 14. Replicate this formula to show the takings for all camera models. 15. Save the spreadsheet keeping the same filename. 16. Make sure all data is displayed in full. 17. Print one copy of the spreadsheet on one page in landscape orientation, showing the figures, not the formulae. You are requested to make some changes to the spreadsheet file called Digital Camera Data. 18. Apply the following alignment: 19. Right-align the column labels EOS 350D, EOS 5D, EOS 1D and EOS 20D. 20. Add a single line outside border around all the row labels starting with Monday and ending with Saturday. 21. Display the figures in the Price and Takings rows with a currency symbol and to 2 decimal places. Make sure all the data is fully displayed. 22. Display all the other figures in the spreadsheet as a number and integer format. 23. The row label Takings must be changed to Takings + VAT. 24. Make sure all the data is fully displayed. 25. The Thursday figure for EOS 20D must be changed to 21. 26. Duplicate all the data on Sheet1 and paste into Sheet2 starting at cell B2.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 237
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
27. Make sure all the data is fully displayed. 28. On Sheet1 alter row 3 to a new height measurement of 28.5. 29. Display the formulae on Sheet1. Make sure the formulae are displayed in full. 30. Make sure that gridlines and row and column headings (1,2,3 and A, B, C â&#x20AC;Ś) will be displayed when printed. 31. Print the entire spreadsheet on one page in landscape orientation showing the formulae. 32. Make sure all formulae are displayed in full and are readable on your printout. 33. Save the spreadsheet formulae in your My Documents folder using the filename EOS Sales Info. 34. Close the file EOS Sales Info. You have been asked to produce graphs to show certain data in a spreadsheet. 35. Open the spreadsheet Canon EOS Range. 36. Create a comparative bar chart to show the sales forecast for Sunday and Monday from EOS 300D to EOS 10D inclusive. 37. Display the types of camera on the X-Axis. 38. Title the chart Camera Data Sun-Mon. 39. Give the X-Axis the title Camera Range. 40. Give the Y-Axis the title Amount Sold. 41. Display the values for each bar. 42. Make sure that the chart is created on a full page on a sheet that is separate from the source data. 43. Set the Y-Axis range from 5 to 25. 44. In the footer enter: a. Your name b. Your centre number c. An automatic filename. 45. Change the Y-Axis label (Amount Sold) to read EOS Amount Sold. 46. Print one copy of the chart. 47. Create a pie chart showing the sales forecast for Sunday, Tuesday, Thursday and Friday for the EOS 300D.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 238
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
48. Delete the legend and show the category name labels and percentages on each data segment. 49. Make sure that the chart is displayed with the data that was used to create it. 50. Move the chart to be positioned to the left hand side below the data in the spreadsheet. 51. Print the spreadsheet showing the data and the chart. 52. Save the file in your My Documents folder using the filename Canon EOS Range Charts.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 239
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Check your answers with the answers on the following pages then close the file and exit the software. The EOS Sales Info, Sheet1, spreadsheet in Normal View.
The EOS Sales Info, Sheet1, spreadsheet in Formula View.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 240
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The EOS Sales Info, Sheet2, spreadsheet in Normal View.
Chart1 in the Canon EOS Range spreadsheet.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 241
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The second chart shown on Sheet1 with the data used to create it.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 242
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
Consolidation Exercise Three There are instructions in this exercise to produce prints of your work. As this is a consolidation exercise you may find it helpful to produce prints. However, if you are able to check your work using the Print Preview option it would be greatly appreciated in order that paper is not wasted. 1. Create a new spreadsheet. 2. Set the page orientation to landscape. 3. Enter the following data, leaving the Total, Takings and Average Sold rows/columns blank as shown.
4. In the header enter: a. Your name b. Your centre number 5. In the footer enter: a. An automatic date field. b. An automatic filename field. 6. Save the spreadsheet in your Documents folder using the filename English Apples. 7. Make sure that all the data is fully displayed in your spreadsheet. You are requested to make some calculations. 8. In the Total column use the SUM function to calculate the Total for the Winston apple (Monday to Saturday inclusive). 9. Replicate this function to show the Total for all the other types of English apples. 10. In the Average Sold row use the AVERAGE function to calculate the average apples sold for Monday. Replicate this function for the other days of the week (Tuesday to Saturday inclusive). Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 243
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
11. Insert a new row between row 5 and 6. 12. Enter the following data into the new row: Mon Charles 6 Ross
Tue 8
Wed 9
Thu 9
Fri 10
Sat 11
Price 1.59
Discount 0.56
13. Update the Total column to include this new row data. 14. Delete the Saturday column making sure blank cells do not remain where the data was deleted. 15. Save the spreadsheet keeping the same filename. 16. Insert a formula to calculate the Takings for the Winston apple by subtracting the Discount figure from the figure for Price and then multiplying by Total. Remember the BODMAS rule! 17. Replicate this formula to show the takings for all the English apples. 18. Apply grey shading to the cell range J4 to J9. 19. The figures in the Price, Discount and Takings columns should be displayed with a currency symbol and to 2 decimal places. 20. Display the figures in the other columns as left aligned. 21. Replace the word English with the words English Apples wherever it occurs in the spreadsheet (match the case). 22. Save the spreadsheet keeping the same filename. 23. Make sure all data is displayed in full. 24. Print one copy of the spreadsheet on one page in landscape orientation, showing the figures, not the formulae. 25. Make sure that gridlines and row and column headings (1,2,3 and A, B, C â&#x20AC;Ś) will be displayed. 26. Print the entire spreadsheet on one page in landscape orientation showing the formulae. 27. Make sure all formulae are displayed in full and are readable on your printout. 28. Save the spreadsheet keeping the same filename. 29. Close the file English Apples. You have been asked to produce graphs to show certain data in a spreadsheet. 30. Open the spreadsheet Pears Sold.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 244
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
31. Create a column chart showing the sales for the Conference, Williams and Onward pears from Jan to June inclusive. 32. Display the months along the X-Axis. 33. Title the chart Pears Sold 1st Half. 34. Give the X-Axis the title Months. 35. Give the Y-Axis the title Sales. 36. Use a legend to clearly identify each bar. 37. Display the values for each bar. 38. Make sure that the chart is created on a full page on a sheet that is separate from the source data. 39. In the header enter: a. Your name. b. Your centre number. c. An automatic date field. 40. Save the file using the filename Pear First Half Results. 41. Print one copy of the chart. 42. Create a line chart showing the sales for the Conference and Williamsâ&#x20AC;&#x2122;s pears from Jan to Apr inclusive. 43. Title the chart Comparison Chart. 44. Give the X-Axis the title Months. 45. Give the Y-Axis the title Amount. 46. Display the values for each data point. 47. Make sure that the chart is created with the data that was used to create it. 48. Move the chart to be positioned to the left hand side below the data in the spreadsheet. 49. Print only the chart. 50. Save the spreadsheet keeping the same filename. Check your answers with the answers on the following pages then close the file and exit the software.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 245
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The English Apples spreadsheet in Formula View.
The English Apples spreadsheet columns G – J (close up).
The English Apples spreadsheet in Normal View.
Excel 2010 Level 1 – February 2011
Page 246
Highbury College
SPREADSHEETS AND GRAPHS LEVEL 1
The Pears First Half Results, Chart1, spreadsheet.
The Pears First Half Results, second chart, shown with the data used to create it.
Excel 2010 Level 1 â&#x20AC;&#x201C; February 2011
Page 247
Highbury College