Information Technology and Media Services
________________________________________________________
Office 2010
Excel
Getting Started
June 2014
________________________________________________________
Excel 2010 Getting Started (310712) i ©De Montfort University, IT Training 2012
CONTENTS
INTRODUCTION ................................................................................................... 1
WHAT IS A SPREADSHEET? ..................................................................................... 3 EXAMPLES OF OUTPUT .................................................................................................. 3
GETTING STARTED ............................................................................................... 4 THE RIBBON .............................................................................................................. 5
The file tab ..................................................................................................7 THE QUICK ACCESS TOOLBAR .......................................................................................... 8 GETTING HELP ........................................................................................................... 9
WORKSHEET STRUCTURE ...................................................................................... 11
ENTERING DATA ................................................................................................. 12
COLUMN WIDTH.................................................................................................. 13 SETTING A SPECIFIC VALUE ........................................................................................... 14
EDITING DATA .................................................................................................... 15
SAVING A WORKBOOK .......................................................................................... 16
OPENING A WORKBOOK ........................................................................................ 18
INSERTING AND DELETING COLUMNS AND ROWS ......................................................... 19 INSERTING A ROW OR COLUMN ....................................................................................... 19 DELETING A ROW OR COLUMN ........................................................................................ 20
FORMULAS ........................................................................................................ 21 EXAMPLES OF FORMULAS .............................................................................................. 21 ARITHMETIC OPERATORS .............................................................................................. 22
EDITING DATA .................................................................................................... 25 SELECTING CELLS ....................................................................................................... 25
COPYING DATA ................................................................................................... 26
COPYING A VALUE TO ADJACENT CELLS ............................................................................. 26 COPYING A FORMULA TO ADJACENT CELLS ......................................................................... 27
HIERARCHY OF OPERATIONS .................................................................................. 28 PARENTHESES ........................................................................................................... 29
FUNCTIONS ....................................................................................................... 30 AUTOSUM BUTTON ..................................................................................................... 31 INSERT FUNCTION ...................................................................................................... 34 TYPING A FUNCTION ................................................................................................... 35
DISPLAYING FORMULAS ........................................................................................ 37
DISPLAYING VALUES .................................................................................................... 37
FORMATTING VALUES .......................................................................................... 38 DISPLAYING NUMBERS AS DECIMALS OR INTEGERS ................................................................. 38
Displaying the thousand separator .................................................................... 40 Displaying cells in currency format .................................................................... 41
ADDING BORDERS ....................................................................................................... 41 COPYING FORMATTING ................................................................................................ 43 FILLING A CELL WITH COLOUR ........................................................................................ 43 REMOVING FORMATTING .............................................................................................. 44
ALIGNING TEXT .................................................................................................. 45
HORIZONTAL ALIGNMENT ............................................................................................. 45 VERTICAL ALIGNMENT ................................................................................................. 45 USING THE WRAP TEXT COMMAND ................................................................................... 46 USING THE MERGE AND CENTRE COMMAND ......................................................................... 47
ZOOM .............................................................................................................. 48
WORKING WITH WORKSHEETS ................................................................................ 50 RENAMING A WORKSHEET ............................................................................................. 50 ADDING WORKSHEETS .................................................................................................. 51
Excel 2010 Getting Started (310712) ii ©De Montfort University, IT Training 2012
DELETING A WORKSHEET .............................................................................................. 52 MOVING OR COPYING A WORKSHEET ................................................................................ 52
RELATIVE AND ABSOLUTE REFERENCES .................................................................... 54 RELATIVE REFERENCE.................................................................................................. 54 ABSOLUTE REFERENCE ................................................................................................ 54 USING ABSOLUTE REFERENCES ....................................................................................... 55
CONDITIONAL FORMULA ....................................................................................... 57
HEADERS AND FOOTERS ....................................................................................... 62
OPTIONS FOR PRINTING A WORKSHEET .................................................................... 65 PRINT PREVIEW ......................................................................................................... 65 PRINTING A SPECIFIED COLUMN ON EVERY PAGE .................................................................. 66 PRINTING A WORKSHEET ON A SINGLE PAGE ....................................................................... 68 CHANGING THE PAGE ORIENTATION ................................................................................. 69 CHANGING THE MARGINS ............................................................................................. 70
Centring the worksheet on the printed page ........................................................ 71 PRINTING THE ROW AND COLUMN HEADINGS ...................................................................... 72 PRINTING A RANGE OF CELLS ......................................................................................... 74
Setting a Print Area ...................................................................................... 75
SORTING DATA ................................................................................................... 77
FIND AND REPLACE ............................................................................................. 78 FINDING A WORD OR A VALUE ........................................................................................ 78 REPLACING A WORD OR A VALUE .................................................................................... 79
FREEZE PANES ................................................................................................... 81 UNFREEZING PANES .................................................................................................... 81 FREEZING MULTIPLE ROWS OR COLUMNS ........................................................................... 82
USING AUTOFILL ................................................................................................ 83
ERROR MESSAGES ............................................................................................... 86
Excel 2010 Getting Started (310712) 1 ©De Montfort University, IT Training 2012
INTRODUCTION This introductory level course is aimed at those who want to learn spreadsheet basics using Microsoft Excel. Knowledge assumed
experience of using a computer, for example keyboard and mouse familiarity
experience of using Windows, for example familiarity with icons, loading software, opening files and saving files
Areas covered
worksheet structure
formatting cells
printing from a spreadsheet
using formulae and functions
relative and absolute references
manipulating worksheets
simple charts
Excel 2010 Getting Started (310712) 2 ©De Montfort University, IT Training 2012
Document signposts Instructions for you to type
Bold text
Shortcuts
Reminders
Notes
Exercises
Excel 2010 Getting Started (310712) 3 ©De Montfort University, IT Training 2012
WHAT IS A SPREADSHEET? A spreadsheet enables you to enter and store data in a series of rows and columns. Once the data is entered, you can perform numerical and statistical calculations and analyses. A spreadsheet file contains one or more sheets called ‘worksheets’. Excel uses the term ‘workbook’ to describe a spreadsheet file. A worksheet is the primary document that you use to store and work with data. Worksheets are always stored in a workbook. You can enter and edit data on several worksheets simultaneously and perform calculations based on data from more than one worksheet. When you create a chart, you can place the chart on the same worksheet as its related data, or on a separate chart sheet. Worksheets can be used to present a wide variety of information such as budgets, petty cash records, stock control, and loan payments. As well as using the information to create charts, you can also export it into a Word report, a PowerPoint presentation, or an Access table, as well as emailing it directly using Outlook.
EXAMPLES OF OUTPUT
A Worksheet
A Pie Chart
A Column Chart
Excel 2010 Getting Started (310712) 4 ©De Montfort University, IT Training 2012
GETTING STARTED Double click the Microsoft Excel 2010 icon on the desktop
Or
Click the Start button
All Programs
Microsoft Office
Microsoft Excel 2010
A blank worksheet is automatically opened ready for use.
Quick Access Toolbar
The Ribbon
Ribbon tabs
Help Column headings
Active cell
Row headings
Sheet tabs
Windows Task bar
Status bar
Excel 2010 Getting Started (310712) 5 ©De Montfort University, IT Training 2012
THE RIBBON
Excel’s commands can be accessed through the various groups of buttons, menus and galleries on the Ribbon that is located at the top of the window. Each tab displays a different set of commands.
The Home tab is selected by default. This section of the ribbon was designed to have the commands that are likely to be used most frequently and includes commands for carrying out general formatting tasks. Apart from these standard tabs that are always displayed, other contextual tabs exist and appear when they are needed. For example, when a chart on a worksheet is selected, three additional tabs are shown. These provide access to commands that allow you to configure the chart.
When the chart is no longer selected the chart contextual tabs are removed from the ribbon.
Some of the command groups have an arrow icon in the bottom right corner. This is a Dialog Box Launcher and is used to open a dialog box or task pane that gives further options for that group. Ensure that the Home tab is selected
Tabs
The Ribbon
Group names
Dialog box launchers
Contextual tabs Providing access to Chart tools
Excel 2010 Getting Started (310712) 6 ©De Montfort University, IT Training 2012
Click the dialog box launcher for the Font group
The Format Cells dialog box is displayed:
Click Cancel to close the Format Cells dialog box
Font group Dialog box launcher
Excel 2010 Getting Started (310712) 7 ©De Montfort University, IT Training 2012
The file tab
The green File tab on the left is a special tab. It does not display ribbon commands.
When this tab is selected it provides access to the file management tools enabling you to perform general tasks with files, such as opening, saving and printing files. Select the File tab
Microsoft refers to this window as the ‘Backstage view’.
Ensure that Recent is selected from the list of options in the left column
This provides access to the most recent documents that have been used and the folders from which files were recently taken. To close the Backstage View: Select the Home tab (selecting any of the other tabs will also close the backstage)
Excel 2010 Getting Started (310712) 8 ©De Montfort University, IT Training 2012
THE QUICK ACCESS TOOLBAR
The Quick Access toolbar is at the top left of the screen. There are just three buttons on the Quick Access Toolbar by default, but it can be customised to include some of the commands that you use frequently. Any command on the Ribbon may be put onto the Quick Access Toolbar. For example, to add the Paste command to the Quick Access Toolbar: Click the right mouse button on the Paste command in the Clipboard group
on the Home tab
A shortcut menu is displayed:
From the shortcut menu: Select Add to Quick Access Toolbar
The Paste command is now added to the Quick Access Toolbar. To remove the Paste button from the Quick Access Toolbar: On the Quick Access Toolbar: Click the right mouse button on the Paste button
A shortcut menu is displayed.
From the shortcut menu: Select Remove from Quick Access Toolbar
Excel 2010 Getting Started (310712) 9 ©De Montfort University, IT Training 2012
GETTING HELP
Whether or not you are connected to the internet, help files are available that can provide you with instruction, guidance and useful tips, or remind you of a particular procedure. When connected to the internet you gain access to even more files that are part of Microsoft’s Online Help system, stored on their servers. Click the help button in the top right corner of the window
The Excel Help window is displayed
If you are connected to the internet, Excel Help connects you to Online Help by default and the Connection Status button shows Connected to Office Online. If you are connected to the internet but the Connection Status button shows Offline, to connect to the Online Help: Click the Connection Status button
The Connection Status Menu is displayed.
Connection Status button
Excel 2010 Getting Started (310712) 10 ©De Montfort University, IT Training 2012
From the Connection Status Menu choose:
Show content from Office Online
Help can now be accessed by either browsing through the Table of Contents, or using the Search facility to get help on a specific topic. To search for help on creating charts in Excel: Key in creating charts in the Search text box
Click the down arrow by the Search button
To ensure that all relevant files, including multimedia training files, are searched:
Select All Excel
Click the Search button
Select an appropriate link from the list of search results to view a help article on how to create a chart.
You can also browse through the help files to find the information that you need. Click the Home button to return to the start page
Select the Charts link in the Browse Excel 2010 Support section
Click Creating charts
This displays a list of links similar to the one obtained using the Search button. Select a link from the list for an explanation on how to create a chart
Close the Help window
Excel 2010 Getting Started (310712) 11 ©De Montfort University, IT Training 2012
WORKSHEET STRUCTURE The worksheet is divided into rows and columns, and the box formed by the intersection of a row and a column is called a cell. ROWS A row is a horizontal line of cells across the worksheet. Each row has a reference number. COLUMNS A column is a vertical line of cells down the worksheet. Each column is referenced by a letter. CELLS A cell is a single ‘box’ on the worksheet. Each cell has a reference. Amongst the types of data that may be entered in a cell are: CELL REFERENCE The cell reference consists of the column letter and the row number. For example, A1 is the reference for the cell in the top left corner of the worksheet.
A B C D E F G
1 A1
2 E2
3
4
5 F5
Column headings
Row
Cell
Row headings
Column Cell reference (column F, row 5)
Excel 2010 Getting Started (310712) 12 ©De Montfort University, IT Training 2012
ENTERING DATA You are going to create a worksheet to record wages information for the Jumble Sales Corporation. This section explains how to enter the following data into the worksheet:
A B C D
1 Name Hourly Rate Hours Worked Total
2
3 John Markshaw 7.5 30
Ensure that A1 is selected (the selected cell is called the active cell)
If A1 is not the active cell, click into A1 to select it. Type Name
To move to the next cell B1, press the Tab key
Alternatively, to move around the worksheet:
● Either use the Arrow keys () or ● Click in the relevant cell using the left mouse button
Type Hourly Rate
(Don’t worry that the column is not wide enough to display all of the data – you will learn how to correct this shortly). Continue the process and enter the two lines of data shown at the top of this page
(ensure that each bit of data is entered in the correct cell)
Your worksheet will look something like this:
Excel 2010 Getting Started (310712) 13 ©De Montfort University, IT Training 2012
COLUMN WIDTH You will probably find it necessary to increase the width of some columns in the worksheet to enable the data in cells containing long words or values to be displayed fully. You may also wish to change the widths of certain columns in order to enhance the presentation of a worksheet. To widen the Name column in your worksheet: Move the mouse pointer on to the boundary between column headings A and B until
it changes into a black cross with a double arrow (column width indicator)
Click and hold the left mouse button, and drag the mouse pointer to the right until
the column is wide enough to display all of the data
Release the mouse button
Repeat this action for the Hourly Rate and Hours Worked columns
ADJUSTING THE COLUMN WIDTH TO BEST FIT
Follow the instructions above until the mouse pointer changes to a black . cross with a double arrow over the dividing line
Double click the left mouse button
The cell will automatically widen to accommodate the longest value in the column. This action must be repeated when data containing more characters is added to the column.
Excel 2010 Getting Started (310712) 14 ©De Montfort University, IT Training 2012
SETTING A SPECIFIC VALUE
To change the width of column D to exactly 6 units: Click into any cell in column D
Click Format in the Cells group on the Home tab
Select Column Width
In the Column Width dialog box, Type 6
Click OK
All the above methods for changing column widths can also be applied to changing row heights.
Excel 2010 Getting Started (310712) 15 ©De Montfort University, IT Training 2012
EDITING DATA The contents of a cell can be edited in one of three ways: Completely replacing the existing contents: Click on cell C3
Type 25
Press Enter
The previous content is now replaced by the number 25. Amending the content within the cell: Double click on cell A3
The insertion point is now inside the cell. Change Markshaw to Grimshaw by deleting ‘Mark’ and typing ‘Grim’
Press Enter
Amending errors by typing in the Formula Bar: Click once on cell A3
Click in the Formula Bar
Change Grimshaw to Markshaw
Press Enter
Click here
Excel 2010 Getting Started (310712) 16 ©De Montfort University, IT Training 2012
SAVING A WORKBOOK It is very important that you remember to save your workbook regularly. You have entered data into a worksheet which is stored in a workbook file. Click the File tab
From the list of options on the left choose: Save
Or
Select the Save button on the Quick Access Toolbar
The Save As dialog box is displayed. Scroll down the list in the left hand (navigation) pane to locate Computer
Click the arrow next to Local Disk (C:) to display the
folders
Excel 2010 Getting Started (310712) 17 ©De Montfort University, IT Training 2012
Locate and select the ExcelIntroduction folder on drive C
In the File name box, replace the default filename (Book1) with Pay1
Click the Save button
Excel automatically adds the file extension .xlsx to the filename. To close the workbook: Click the File tab
From the list of options on the left choose: Close
Excel 2010 Getting Started (310712) 18 ©De Montfort University, IT Training 2012
OPENING A WORKBOOK You will now open the workbook JumbleSales.xlsx from the ExcelIntroduction folder on drive C. Select the File tab
From the list of options on the left choose: Open
The Open dialog box is displayed. Locate and open the ExcelIntroduction folder on drive C
Select JumbleSales
Click Open
The JumbleSales workbook is now open and ready for editing. Ensure that all the data in the worksheet is fully displayed
Change the width of Column D to 9.5
Edit A9 to display Penelope’s full name: Penelope East
Excel 2010 Getting Started (310712) 19 ©De Montfort University, IT Training 2012
INSERTING AND DELETING COLUMNS AND ROWS
INSERTING A ROW OR COLUMN
Before inserting a new row or column note carefully which cell is active, because where the row or column is inserted depends on where the active cell is located. A new row is inserted above the active cell and a new column is inserted to the left of the active cell. To insert a new row between Tony Bare (row 8) and Penelope East (row 9): Click on any cell in row 9
In the Cells group of commands on the Home tab: Click the Insert down arrow
From the displayed list of Insert commands choose: Insert Sheet Rows
A row is inserted above Penelope East (row 9), and the rows below move down.
IMPORTANT TO REMEMBER
When you insert a row, it will be positioned directly before the row containing the active cell.
For example, if you want to insert a row between rows 6 and 7, the active cell must be in row 7.
Similarly, if you want to insert a column between columns D and E, the active cell must be in column E.
Excel 2010 Getting Started (310712) 20 ©De Montfort University, IT Training 2012
DELETING A ROW OR COLUMN
To delete a row (or column), you must first select any cell in the row (or column). To delete row 5: Select any cell in row 5
In the Cells group of commands on the Home tab: Click the Delete down arrow
Select Delete Sheet Rows
Row 5 is deleted.
Sophie Doige has changed from full-time to part-time employment. Amend her hours worked to 20
Marie Kaur has been given a pay rise – change her hourly rate to 7.5
In the new row between Tony Bare and Penelope East add the following information about a new member of staff:
(Name)
(Hourly Rate)
(Hours Worked)
(Total)
Belinda Miles
8
25
Save the workbook
Excel 2010 Getting Started (310712) 21 ©De Montfort University, IT Training 2012
FORMULAS Formulas are what make a spreadsheet so useful. Using formulas lets you calculate results from the data stored in the worksheet. When you create a formula, you use cell references to identify the data to be included in the calculation. By using cell references, rather than the values themselves, you ensure that any changes to the values in the cells referenced by the formulas automatically result in an updated answer. A formula always commences with an equals sign (=) to indicate that what follows is an instruction, not a value.
EXAMPLES OF FORMULAS
In the example worksheet below, cells D2 and D3 both contain formulas to add the respective cells in columns B and C. Look at cell D2
The intention of this formula is to add together the values in cells B2 and C2 (15 plus 20). If the value in either cell changes, the formula will not take this into account and you will need to remember to alter the values in D2, otherwise the resulting figure will no longer be correct. This formula, therefore, is not very useful. Look at cell D3
The formula specifies the cell references (B3 and C3) rather than the current values of these cells. If you change the values in either or both cells, there is no need to worry about the formula in D3. Because the formula uses cell references, the resulting figure will be updated automatically.
A B C D
1 JAN FEB TOTAL
2 PETROL 15.00 20.00 =15.00+20.00 3 OIL 5.00 14.50 =B3+C3
Excel 2010 Getting Started (310712) 22 ©De Montfort University, IT Training 2012
ARITHMETIC OPERATORS
When creating formulas the following arithmetic operators are used:
Operator Example
+ (plus sign) Addition (3+3)
– (minus sign) Subtraction (3–1)
Negation (–1)
* (asterisk) Multiplication (3*3)
/ (forward slash) Division (3/3)
% (percent sign) Percent (20%)
^ (caret) Exponentiation (3^2)
ADDITION In A20 enter 12
In B20 enter 4
To add these two values together and display the result in C20: In C20 type: =A20+B20
Press Enter
This formula instructs Excel to add the contents of cell A20 to the contents of B20. C20 displays the value 16 (12+4=16). After you have created a formula, any changes to the cells in the formula, automatically result in an updated answer. In B20 type 10
Press Enter
The value in C20 is automatically updated to 22 (12+10=22).
Excel 2010 Getting Started (310712) 23 ©De Montfort University, IT Training 2012
MULTIPLICATION In C21 type: =A20*B20
Press Enter
C21 displays the value 120 (12x10=120). Notice that in Excel an asterisk is used for multiplication. You can, of course, multiply the contents of a cell by a value. In C22 type: =A20*3
Press Enter
C22 displays the value 36 (12x3=36). SUBTRACTION In C23 type: =A20-B20
Press Enter
C23 displays the value 2 (12-10=2). DIVISION In C24 type: =A20/B20
Press Enter
C24 displays the value 1.2 (12÷10=1.2).
Excel 2010 Getting Started (310712) 24 ©De Montfort University, IT Training 2012
CALCULATING THE TOTAL PAY In cell D3 you are going to enter the formula to calculate the Total for John Markshaw (Hourly Rate * Hours Worked). Click on cell D3
Type =
The formula is =B3*C3, but instead of typing the cell references you will learn a quick way to insert cell references into a formula. Point to cell B3 and click the mouse button
The cell is highlighted and its reference is added to the formula in D3.
Type *
Point to cell C3 and click
The cell is highlighted and its reference is added to the formula in D3.
Press Enter
The following result is displayed:
Excel 2010 Getting Started (310712) 25 ©De Montfort University, IT Training 2012
EDITING DATA
SELECTING CELLS
To select a single cell, point to it and click the left mouse button
To select a range of cells, point to the first cell in the range, click and hold the left mouse button, and drag the pointer to the last cell in the range
Notation:
A20:C24 is used to mean the range of cells from A20 through to C24.
Select A20:C24
To select a row, point to the row heading and click the left mouse button. Select row 20
Press Delete to delete the contents of the row
Select C21:C24 and delete the contents
INSERTION POINT SHAPES
indicates SELECT + indicates COPY / FILL
indicates EDIT indicates MOVE
Click here to select row 20
Excel 2010 Getting Started (310712) 26 ©De Montfort University, IT Training 2012
COPYING DATA You can copy the contents of one cell, or a range of cells, from one part of the worksheet to another. This works in different ways depending on the contents of the cell that you are copying.
COPYING A VALUE TO ADJACENT CELLS
In F3 type 10
Press Enter
Click on Cell F3
Notice that a small black square is displayed in the bottom right corner of the cell. This is the Fill Handle.
Move the mouse pointer over the square
The pointer changes to a cross + Click and hold down the left mouse button and drag the fill handle down, over the
cells F4 to F7
Release the mouse button
The content of F3 is copied to all the cells down to and including F7.
Select F3:F7 (this means all the cells from F3 to F7 inclusive)
Press Delete
Fill handle
Excel 2010 Getting Started (310712) 27 ©De Montfort University, IT Training 2012
COPYING A FORMULA TO ADJACENT CELLS
If you are copying a formula (remember that you usually see the result of the formula rather than the formula itself), the cell references change automatically relative to the row and column of the cell it is being copied to. You now need to calculate the Total for the remaining employees. Rather than create a formula for each one, you can copy the formula entered for John Markshaw into the remaining cells in the Total column. Click on cell D3
To copy the formula in D3 to the cells D4:D15: Move the mouse pointer over the fill handle
Click and hold down the left mouse button and drag the fill handle over the range D4
to D15
Release the mouse button
Column D now looks like this:
Save the workbook
Excel 2010 Getting Started (310712) 28 ©De Montfort University, IT Training 2012
HIERARCHY OF OPERATIONS As you already know, a formula in Excel always begins with an equals sign (=). Following the equals sign are the elements to be calculated which are separated by operators (+, -, *, / etc). However, you should be aware that Excel does not always calculate the formula from left to right. Say you entered the following formula into a worksheet: =2+2*6 You might expect the answer to be 24. After all, 2 plus 2 equals 4, and 4 multiplied by 6 equals 24. Try this for yourself now In cell G3 type =2+2*6
Press Enter
You might be surprised at the resulting answer of 14. If you combine several operators in a single formula, Excel performs the operations in the following order:
first * and / (Multiplication and division)
then + and – (Addition and subtraction)
In the above formula Excel deals with the multiplication first (2 * 6 = 12) and then the addition (2 + 12) giving a total of 14.
Excel 2010 Getting Started (310712) 29 ©De Montfort University, IT Training 2012
PARENTHESES
To change the order of evaluation (to override the built-in order of precedence between *, /, + and -), you use parentheses. You will enter the same formula again, but this time enclosing the first part in parentheses. In cell G4 type =(2+2)*6
Press Enter
The answer is 24. Excel dealt with the calculation in the parentheses first and then multiplied the result. You can appreciate, therefore, how important it is that you understand how Excel deals with the elements in a formula, before you create complex formulas. Delete the contents of G3 and G4
Excel 2010 Getting Started (310712) 30 ©De Montfort University, IT Training 2012
FUNCTIONS Functions are predefined formulas that can be used to perform calculations. The most frequently used functions are:
Name Calculates…
SUM The total value
MIN The minimum value
MAX The maximum value
AVERAGE The average value
COUNT The number of cells containing numbers
COUNTA The number of non-empty cells
COUNTBLANK The number of blank cells All of these functions operate on a range of cells. For example, a range of cells could begin at D2 and end at D16. In this case you would express the range as D2:D16 – the colon in this situation can be interpreted as the word “to”. So D2:D16 means D2 to D16.
To total the range of cells above without using a function, your formula would look like this:
=D2+D3+D4+D5+D6+D7+D8+D9+D10+D11+D12+D13+D14+D15+D16 However, by using the SUM function the formula is reduced to:
=SUM(D2:D16)
D2:D16
Excel 2010 Getting Started (310712) 31 ©De Montfort University, IT Training 2012
You can enter functions into your worksheet in three different ways:
using the AutoSum function button on the Ribbon
using the Insert Function button
typing the function
AUTOSUM BUTTON
You are required to add up all the figures in the Total column to calculate the company’s monthly salary expenditure, and display the value in D17. Select the cell in which the total is to appear – in this case D17
The AutoSum button creates a SUM formula. The AutoSum button is in the Editing group on the Home tab, and also in the Function library group on the Formulas tab. Click the AutoSum button
Excel automatically selects a range for you (indicated by a dashed box around the cells).
You should always check to ensure that the range that is selected is correct – do not assume that Excel has got it right! If this range is correct (it should cover all of the figures in column D) press Enter
The result of the calculation is displayed in the selected cell, D17.
AutoSum
Excel 2010 Getting Started (310712) 32 ©De Montfort University, IT Training 2012
If the range is not correct then you can either: Click into the Formula Bar and type the correct range before pressing Enter
Or Highlight the required range using the mouse pointer and then press Enter
If a cell in your worksheet displays #########, don’t panic.
This indicates that the cell is not wide enough to display the result of your calculation.
To solve the problem simply increase the cell width.
Many functions are used in a similar way. Delete the contents of D17
To calculate the average pay of all the employees: With D17 selected, click the down arrow next to the AutoSum button
This gives you quick access to some of the most common functions. Click Average
Excel 2010 Getting Started (310712) 33 ©De Montfort University, IT Training 2012
Check to ensure that the range selected is correct (i.e. all the numbers in column D are selected)
Press Enter
The average is calculated to be 194.79231 Click the Undo button on the Quick Access Toolbar to undo the average function
Other functions can be accessed via the AutoSum button. Click the down arrow next to the AutoSum button
Select More Functions
The Insert Function dialog box is displayed.
This dialog box can be accessed in various ways and is the general method used for inserting most of the functions that are available in Excel. Click Cancel
Undo
Excel 2010 Getting Started (310712) 34 ©De Montfort University, IT Training 2012
INSERT FUNCTION
Another way to access the Insert Function dialog box is to use the Insert Function button on the Formula Bar. For this exercise you will use a function in A17 to display the number of employees listed on the spreadsheet. The function needed is called COUNTA. Select A17 (this is where the result is to be displayed)
Click the button
The Insert Function dialog box is displayed. Click the down arrow in the Or select a category box and select Statistical
In the Select a function window use the scroll bar to locate COUNTA
Click OK
Excel 2010 Getting Started (310712) 35 ©De Montfort University, IT Training 2012
The Function Arguments dialog box is displayed. In the Value 1 box type: A3:A15 (this is the range of cells that we want the function
to count)
Click OK
This function works by counting the number of non-empty cells in the range that you specify. It therefore counts the number of names in A3:A15 and returns the value 13.
TYPING A FUNCTION
If you know the function name then, rather than using either of the previous two methods, you can type the function in the cell where you want the answer to appear. Ensure that A17 is selected
Notice what appears in the Formula Bar:
This can be typed instead of using the Insert Function dialog box. Delete the formula in A17
In A17, type =COUNTA(A3:A15) and press Enter
You should get 13, the same value as before. The syntax is the same for each of the functions listed on page 30, so if you want to sum cells C3 to C8, you would type: =SUM(C3:C8). Delete the formula in A17
Excel 2010 Getting Started (310712) 36 ©De Montfort University, IT Training 2012
In cell A17, type TOTAL
In cell C17 use the SUM function to calculate the total number of Hours Worked by all the employees (i.e. sum the range from C3 to C15)
In D17, use the SUM function to calculate the total pay for all the employees
In A18, type AVERAGE
In C18, use the AVERAGE function to calculate the average number of hours worked
In A20, type Maximum Salary
In A21, type Minimum Salary
In D20, use the MAX function to calculate the Maximum salary from the Total column, ensuring that you specify the correct range
In D21 use the MIN function to calculate the Minimum salary from the Total column, again ensuring that the range is correct
Save the workbook
Excel 2010 Getting Started (310712) 37 ©De Montfort University, IT Training 2012
DISPLAYING FORMULAS When you select a cell, you can see the formula (if there is one) displayed in the Formula Bar. It is also possible to view the formulas used in the entire worksheet. Select the Formulas tab
Click the Show Formulas button in the Formula Auditing group
The formulas used in the worksheet are displayed.
DISPLAYING VALUES
To return the display to values rather than formulas: Click the Show Formulas button to deselect it
To display/hide formulas
Press and hold the key and press the key at the top left of the keyboard.
Excel 2010 Getting Started (310712) 38 ©De Montfort University, IT Training 2012
FORMATTING VALUES You can format cells to make the values in them easier to read and also to ensure consistency, particularly with regard to the number of decimal places displayed.
IMPORTANT TO REMEMBER
Formatting a value simply affects how it is displayed in the worksheet – the formatting is not saved with the value.
There are several ways in which numeric data can be displayed in a worksheet:
10.000 10 £10.00
Decimal Integer (whole number)
Currency
Before choosing a different format you must ensure that you have highlighted the appropriate cells that you want to change.
DISPLAYING NUMBERS AS DECIMALS OR INTEGERS
Select the numerical values in the Hours Worked column (C3:C18)
Select the Home tab
The current Number Format of the selected cells is displayed within the Number group of commands.
The default format is General, meaning ‘no specific format’. Click the down arrow next to General
Current Format
Excel 2010 Getting Started (310712) 39 ©De Montfort University, IT Training 2012
A list of different number formats is displayed.
Select Number
Each value in the selected cells is now formatted as a number with 2 decimal places. The number of decimal places can be changed using the Increase Decimal and Decrease Decimal buttons in the Number group.
With the cells in column C still highlighted, click the Increase Decimal button once
to change the number of decimal places to 3
Increase Decimal
Decrease Decimal
Excel 2010 Getting Started (310712) 40 ©De Montfort University, IT Training 2012
Displaying the thousand separator
The number format also gives you the option to include commas. This makes it easier to read large numbers. Select D17
Display the list of Number Formats
Select More Number Formats
The Format Cells dialog box is displayed with the Number tab selected. Select Number from the Category list
Click the Use 1000 Separator check box
Click OK
D17 now includes the 1000 separator.
Click here to display the list of Number Formats
Excel 2010 Getting Started (310712) 41 ©De Montfort University, IT Training 2012
Displaying cells in currency format
Select the cells containing the Hourly Rate (B3 to B15)
Display the list of Number Formats
Select Currency
The selected cells are now formatted as currency with 2 decimal places. Format the range C3:C17 to 2 decimal places
Format the range D3:D17 as currency (£)
ADDING BORDERS
Excel enables you to add a variety of different types of borders. First you will add a border around the total salary value for each employee. Select the range D3:D15
Click the down arrow on the border button in the Font group of commands
Excel 2010 Getting Started (310712) 42 ©De Montfort University, IT Training 2012
A list of borders is displayed.
Select All Borders
You will now put a double bottom border on the cells containing the total values Select the cells C17 and D17
Click the down arrow on the border button
Select Bottom Double Border
Excel 2010 Getting Started (310712) 43 ©De Montfort University, IT Training 2012
COPYING FORMATTING
You will copy the formatting from D17 to D20 and D21. Click the cell D17 to select it
Click the Format Painter in the Clipboard group on the Home tab
This action takes the formatting that exists in D17 ready to copy it onto any cell that you now select. When you move the mouse pointer over the worksheet you will see that it has a paint brush attached. Select both D20 and D21 by clicking into D20 and dragging the mouse pointer over
D21 before releasing the mouse button
The formatting is copied.
In summary, there are three steps in the process for copying formatting:
● Step 1: Select the cell that contains the format you want
● Step 2: Click the Format Painter button
● Step 3: Select the cell(s) that you want to copy the format to
FILLING A CELL WITH COLOUR
You are going to colour C17 and D17 light green. Select C17 and D17
In the Font group of commands, click the down arrow on the Fill Colour tool
Choose Light Green from the list of Standard Colours
Fill Colour
Excel 2010 Getting Started (310712) 44 ©De Montfort University, IT Training 2012
REMOVING FORMATTING
The Clear command can be used to remove all formatting from a cell. To remove the formatting from C17 and D17: Ensure that C17 and D17 are selected
From the Editing group of commands on the Home tab
Click Clear
A list of options is displayed.
Clear All would remove all content and formatting.
Clear Formats would remove just the formatting.
Select Clear Formats
All formatting is removed from the two cells. Click the Undo button on the Quick Access Toolbar to bring the formatting back
Excel 2010 Getting Started (310712) 45 ©De Montfort University, IT Training 2012
ALIGNING TEXT
HORIZONTAL ALIGNMENT
Text is automatically aligned with the left edge of the cell, but you can choose to centre or right align it if you prefer. Select cell A1 (Name)
Ensure that the Home tab is selected
From the Alignment group of commands choose: The Centre alignment button
Select the other column labels and right align them
VERTICAL ALIGNMENT
First you will change the height of the row so you can see the difference that vertical alignment makes. Change the height of row 1 to 40 units (see page 14 if you need help)
Notice that by default the contents are aligned at the bottom of the cells.
To vertically centre align row 1: Select row 1 (see page 25 if you need a reminder)
Left Centre Right
Excel 2010 Getting Started (310712) 46 ©De Montfort University, IT Training 2012
Click the Middle Align button in the Alignment group
USING THE WRAP TEXT COMMAND
When text is entered in a cell, it remains on one line even if the cell is not wide enough to display it in full. Text wrapping causes the text to ‘wrap’ to the next line when the cell is not sufficiently wide to display the content on one line. Insert a blank row at the top of the worksheet
In cell A1 type ABC Sales Department
Press Enter
Select A1 and click the Wrap Text button in the Alignment group of commands on
the Home tab
The text in A1 wraps on to two lines within the cell.
Ensuring that A1 is still selected, click Wrap Text once more to remove the text
wrapping
Top Align
Middle Align
Bottom Align
Excel 2010 Getting Started (310712) 47 ©De Montfort University, IT Training 2012
USING THE MERGE AND CENTRE COMMAND
Change the size of the font in A1 to 18
Excel enables you to select a range of cells and merge them into one large cell. There is also a convenient ‘Merge and Centre’ command that will merge a range of cells and centre the text within the merged area with one click of the mouse. To centre the title in A1 across the range A1:D1 Select the range A1:D1
Click the Merge & Centre button in the Alignment group of commands on the Home tab
The range A1:D1 is merged and the text is centred.
Save the Workbook
Excel 2010 Getting Started (310712) 48 ©De Montfort University, IT Training 2012
ZOOM The Zoom option allows you to see the worksheet in a different size. This may be useful if you have a lot of data and you would like to see more on the screen. However, you will not be able to zoom out too far before the display will be too small for you to comfortably see. Select the View tab
From the Zoom group of commands Select the Zoom button
A list of Zoom options is displayed:
Select 75%
Click OK
While you can see more rows and columns on the screen, the size of the data on the sheet is now quite small. Select the Zoom button once more
To zoom to a value that is not specified on the list, for example 80% Select Custom
Type 80
Click OK
Excel 2010 Getting Started (310712) 49 ©De Montfort University, IT Training 2012
The other two commands in the Zoom group can help to simplify and speed up zooming in certain situations. Select the cells A1 to D16
Click the Zoom to Selection button from the Zoom group
The view has changed to just fit the selected area on to the screen. Click the 100% button
The display reverts to the default 100% view. Try some of the other Zoom options to see the result
Try 125%
Change the display back to 100%
Alternative method of displaying the Zoom options
Click the percentage value next to the zoom slider on the status bar
Click here
Excel 2010 Getting Started (310712) 50 ©De Montfort University, IT Training 2012
WORKING WITH WORKSHEETS When you open a new workbook it contains three worksheets. The worksheet tabs can be seen at the bottom of the worksheet.
The different sheets are selected by clicking on their name tabs.
RENAMING A WORKSHEET
To make it easier to identify the contents of the worksheets they should be renamed to reflect the information they hold. Double click the name tab of the first sheet (Sheet 1)
The name of the sheet is selected
Type Payroll
Press Enter
Change the name of Sheet 2 to Travel Expenses
Change the name of Sheet 3 to Overtime
Excel 2010 Getting Started (310712) 51 ©De Montfort University, IT Training 2012
ADDING WORKSHEETS
You need to insert a new worksheet between Payroll and Travel Expenses. When you insert a worksheet it is positioned before the selected sheet, depending on the method used to insert the sheet. Click the Travel Expenses worksheet tab to select it
From the Cells group of commands on the Home tab: Select the Insert down arrow
Select Insert Sheet
A new worksheet (Sheet1) is inserted between Payroll and Travel Expenses.
Alternative Method using the Insert Worksheet button This method always inserts the new worksheet as the last sheet. Click the Insert Worksheet button
A new worksheet (Sheet2) is inserted as the last sheet.
Insert Worksheet
Excel 2010 Getting Started (310712) 52 ©De Montfort University, IT Training 2012
DELETING A WORKSHEET
To delete the new worksheet (Sheet2) Select Sheet2
From the Cells group of commands on the Home tab: Select the delete down arrow
Select Delete Sheet
MOVING OR COPYING A WORKSHEET
You are going to move the Travel Expenses worksheet to the right of the Overtime sheet. Select the Travel Expenses worksheet
Click Format (in the Cells group of commands on the Home tab)
Select Move or Copy Sheet
Excel 2010 Getting Started (310712) 53 ©De Montfort University, IT Training 2012
The Move or Copy dialog box is displayed.
Choose (move to end)
Click OK
The sheet is moved.
There is a tick box at the bottom of the Move or Copy dialog box that will allow you to create a copy of the sheet rather than move it.
Moving or Copying a worksheet to another workbook
The Move or Copy dialog box enables you to move the selected worksheet to another opened workbook by including the following extra steps.
In the To book: section
Click the down arrow
Select the workbook to which the sheet will be moved or copied
Save and close the workbook
Excel 2010 Getting Started (310712) 54 ©De Montfort University, IT Training 2012
RELATIVE AND ABSOLUTE REFERENCES
RELATIVE REFERENCE
By default, formulas in Excel use relative cell references. This means that, as a formula is copied and pasted to other cells, the cell references in the formula change to reflect the formula’s new location. The example below shows the effect of copying the formula in cell C1 to other cells in column C.
A B C
1 6 3 18 (=A1*B1)
2 4 2 8 (=A2*B2)
3 10 4 40 (=A3*B3)
However, let’s say that in column C you want to produce a formula that will multiply cell A1 by a series of cells in column B. In other words, you want the following:
A B C
1 6 3 18 (=A1*B1)
2 2 12 (=A1*B2)
3 4 24 (=A1*B3)
To achieve this, the reference to cell A1 needs to be changed from a relative reference to an absolute one before copying the formula down the column.
ABSOLUTE REFERENCE
An absolute cell reference in a formula does not change when the formula is copied and pasted into other cells. In the formula above, to make the reference to cell A1 absolute, and therefore ensure that it remains unchanged when it is copied to other cells, you include $ signs ($A$1). The diagram below illustrates what happens when the formula in C1 is changed to =$A$1*B1 and then copied down to other cells in column C. The absolute reference ($A$1) has remained unchanged, and the relative reference (B1) has changed to, B2 and B3 respectively.
A B C
1 6 3 18 (=$A$1*B1)
2 2 12 (=$A$1*B2)
3 4 24 (=$A$1*B3)
Excel 2010 Getting Started (310712) 55 ©De Montfort University, IT Training 2012
USING ABSOLUTE REFERENCES
Open the workbook PayRoll from the ExcelIntroduction folder on drive C
Insert a new column between columns E and F
In cell F1 type the column title Overtime pay
Overtime pay is calculated using the formula:
Hourly rate * Overtime rate * Overtime hours In F3, calculate the overtime pay for John Markshaw
To calculate the overtime pay for John Markshaw:
Select F3
Type =B3*A19*E3
Press Enter
You now need to copy this formula down the column to calculate the overtime pay for the other employees. Select cell F3
Before copying the formula you must first decide if any of the references need to be absolute. The Hourly rate figure changes for each employee and therefore this needs to be a relative reference. The Overtime hours figure changes for each employee and therefore this also needs to be a relative reference. However, the Overtime rate figure is the same for every employee and this is stored in A19. Therefore A19 must be made into an absolute reference. Position the insertion point in the Formula Bar and edit the formula so that the
reference to cell A19 is made absolute by including $ signs
=B3*$A$19*E3 Remember to press Enter when you have finished editing the formula.
Copy the formula down the column to all employees
Excel 2010 Getting Started (310712) 56 ©De Montfort University, IT Training 2012
Click on the overtime pay for some of the other employees to see that while the
hourly rate and overtime hours references have copied relatively, the overtime rate reference is absolute
Save the workbook
As some staff have worked overtime, this figure must be added to their pay. Select cell H3 ready to calculate the Total Pay for John Markshaw
Edit the formula to include his Overtime pay
Copy the amended formula down to the other staff – you do not need to make any
part of this formula absolute
To finish this exercise, use the SUM function in cell H17 to total the column
Excel 2010 Getting Started (310712) 57 ©De Montfort University, IT Training 2012
CONDITIONAL FORMULA As a gesture of goodwill, the management has decided that members of staff who earn £7.50 or less an hour will receive a bonus of £20.00. All others will receive £10.00. For this purpose you will use the IF function. This is one of the most important functions as it can give your formulas decision-making capability. The IF function includes a logical test, that compares a cell against a specified value. A logical test is a test that produces an answer of either True or False. The IF function is structured as follows: =IF(Logical Test, Value 1, Value 2) Which produces these results: If the Logical Test is TRUE, Value 1 is inserted into the cell If the Logical Test is FALSE, Value 2 is inserted into the cell If the Value is text rather than a number, it must be enclosed in quotation marks (“text”). For example, you might want to include the words “solvent” or “bankrupt” as the result of a calculation in the worksheet.
You can compare two values with the following operators. When two values are compared using these operators, the result is a logical value, either TRUE or FALSE. > More than < Less than = Equal to >= More than or equal to <= Less than or equal to
Excel 2010 Getting Started (310712) 58 ©De Montfort University, IT Training 2012
The management of Jumble Sales Corporation has decided that staff who earn £7.50 or less per hour will receive a bonus of £20 and all others will get £10. Therefore, the bonus depends on each member of staff’s hourly rate. So the logical test must check to see if the hourly rate is £7.50 or less. To calculate the bonus payment for John Markshaw: Select cell G3 (this is where his bonus pay will be entered)
Click the Insert Function button
Select the IF function
If this function is not available in the Most Recently Used list
Click the down arrow
Select either All or Logical
You should now be able to choose the IF function.
Click OK
Excel 2010 Getting Started (310712) 59 ©De Montfort University, IT Training 2012
The Function Arguments dialog box for the IF function is displayed.
There are three boxes to complete, and the first one is the logical test. You must test to see if the hourly rate is £7.50 or less. The hourly rate value is in B3, so you are testing to see if B3 is £7.50 or less. In the Logical_test box, type B3<=7.5 This will check to see if B3 is less than or equal to 7.5. The pound sign ‘£’ is not included because this is just formatting.
The logical test must include
● A cell reference
● An operator
● A value
In the Value_if_true box, type 20
This gives a bonus of £20 if the hourly rate is £7.50 or less. In the Value_if_false box, type 10
This gives a bonus of £10 if the hourly rate is more than £7.50.
Click OK
Excel 2010 Getting Started (310712) 60 ©De Montfort University, IT Training 2012
G3 displays John Markshaw’s bonus as 20.
Look at the formula in the Formula Bar
=IF(B3<=7.5,20,10) Copy the formula down the column to the other members of staff
Look down the Bonus payment column to check that the figures are correct
Ensure that the range G3:G15 is formatted as Currency
Logical Test: Is the value in cell B3 less than or equal to 7.5?
If the test is true return the value, 20
If the test is not true then return the value, 10
Excel 2010 Getting Started (310712) 61 ©De Montfort University, IT Training 2012
Update the Total Pay for John Markshaw by adding the Bonus payment
Copy the new formula for Total Pay to the other members of staff
Save the workbook
Excel 2010 Getting Started (310712) 62 ©De Montfort University, IT Training 2012
HEADERS AND FOOTERS Information that you want to appear on every page, such as the date, file name or page numbers, may be included in a header and/or footer. In this exercise you will include your name in the header, and the date, the page number and the name of the worksheet (sheet name) in the footer. Select the Insert tab
Select Header & Footer from the Text group
The view changes to Page Layout view, a Design contextual tab is displayed showing Header & Footer tools on the ribbon, and the central section of the Header is selected ready for you to add text into the Header.
The Payroll worksheet is spread over two pages, so you may find that on your worksheet the header for page 2 is selected. This is not a problem, since whatever you put into the header or footer will appear on both pages.
Excel 2010 Getting Started (310712) 63 ©De Montfort University, IT Training 2012
The Header is divided into three sections: left, centre, and right. Press the tab key to select the right section in the Header
Type your name
Note that text typed in this section is right-aligned. From the Navigation group on the Design tab choose: Go to Footer
One of the three sections of the Footer is highlighted.
First you will put the date in the left section of the Footer. If the left section of the Footer is not selected, press the Tab key until it is
Click the Current Date button in the Header & Footer Elements group on the Design tab
Press the Tab key to move to the central section of the Footer
Click the Page Number button in the Header & Footer Elements group to insert a centred page number
Press the Tab key to move to the right section of the Footer
Click the Sheet Name button in the Header & Footer Elements group to insert the name of the sheet
Select any cell on the worksheet to close the Header & Footer boxes
The Header & Footer boxes close, but the worksheet remains in Page Layout view.
Excel 2010 Getting Started (310712) 64 ©De Montfort University, IT Training 2012
To change the view to the Normal view Select the View tab
From the Workbook Views group of commands choose: Normal
Alternative method From the Views Shortcut buttons in the bottom right of the window
Select the Normal shortcut button
Normal Page Break Preview
Page Layout
Excel 2010 Getting Started (310712) 65 ©De Montfort University, IT Training 2012
OPTIONS FOR PRINTING A WORKSHEET Before turning our attention to how you can change the way a worksheet is printed, we will first look at how you can get a preview of what the printout will be like.
PRINT PREVIEW
Print Preview allows you to see how your worksheet will be presented on paper. The preview window also offers you the opportunity to make some adjustments to the page. Click the File tab
Select Print from the options on the left
Here you have access to standard printer options, some page settings, and a preview of what will be printed and how it will fit and look on the page.
Print Preview Print Settings
Excel 2010 Getting Started (310712) 66 ©De Montfort University, IT Training 2012
At the bottom of the screen is an indication that the worksheet will be printed on two pages.
To view the second page: Click the Next Page arrow
(or use the scroll bar on the right of the window)
Observe that on the second page there is no indication of which employee each row refers to. When a worksheet is spread over two or more pages it is sometimes useful to repeat any column or row headings on every page. In the current worksheet it will be helpful to print the employee names (column A) on both pages.
PRINTING A SPECIFIED COLUMN ON EVERY PAGE
To repeat the data in column A on the print out of all pages: Select the Page Layout tab
Click Print Titles in the Page Setup group of commands
The Page Setup dialog box is displayed with the Sheet tab selected.
Click here to choose rows to repeat
Click here to choose columns to repeat
Click here to see page 2
Excel 2010 Getting Started (310712) 67 ©De Montfort University, IT Training 2012
The Print titles section of the dialog box contains boxes that enable you to specify rows to be repeated at the top of each printed page and/or columns to repeat at the left of each page. Click the Collapse button in the Columns to repeat at left box
The Page Setup dialog box collapses to show only the Columns to repeat text box and the mouse pointer changes to a black down arrow, ready for you to choose the column or columns that you wish to repeat on each printed page. Click anywhere in the first column to select column A
Excel adds the column name to the dialog box.
Click the Collapse button once more to restore the dialog box
Click OK
Display the Print Preview and view page 2 to see the effect
Page 2 now displays the list of employee names on the left.
Collapse button
Excel 2010 Getting Started (310712) 68 ©De Montfort University, IT Training 2012
PRINTING A WORKSHEET ON A SINGLE PAGE
Excel enables you to shrink the data so that it fits onto one page. Ensure that Print is still selected on the File tab
In the Settings section Click the Scaling button labelled No Scaling
The following options are displayed:
Select Fit Sheet on One Page
The Print Preview verifies that all the data will be printed on to one page.
Click here
Excel 2010 Getting Started (310712) 69 ©De Montfort University, IT Training 2012
You will find that this is a very useful option but, as more columns and rows are added to the worksheet, in order to accommodate the text on the page, it is displayed in a very small font. It is therefore essential that you always check that the data can still be read. From the Print option on the File tab you can also change the page orientation, margins, and pager size.
CHANGING THE PAGE ORIENTATION
Sometimes the better option for displaying your spreadsheet on one page is to change the page orientation to landscape. In the Settings section Click the Orientation button labelled Portrait Orientation
Choose Landscape Orientation from the available options
Click here
Excel 2010 Getting Started (310712) 70 ©De Montfort University, IT Training 2012
CHANGING THE MARGINS
Making the margins smaller will enable more of the worksheet to fit on the page. In the Settings section Click the Margins Setting button labelled Last Custom Margins Setting
The current margin settings are shown highlighted with the title Last Custom Setting
Select Narrow from the list of available options
Click here
Excel 2010 Getting Started (310712) 71 ©De Montfort University, IT Training 2012
Centring the worksheet on the printed page
If your worksheet is small it may look better centred on the page when it is printed. Click the Margins Setting button (now labelled Narrow Margins)
Select Custom Margins
The Page Setup dialog box is displayed with the Margins tab selected.
The main section on this page enables you to set margins individually and precisely. In the Centre on page section, select Horizontally
Excel 2010 Getting Started (310712) 72 ©De Montfort University, IT Training 2012
Click OK
The Print Preview shows the spreadsheet centre aligned. Save the workbook
The Page Layout tab
With the exception of Print Preview, all the settings that you have looked at in this section can also be accessed from the Page Layout tab.
Select the Page Layout tab
The Page Setup and Scale to Fit groups of commands provide alternative access to the print settings options
PRINTING THE ROW AND COLUMN HEADINGS
By default, Excel does not print the column headings (A, B, C etc) or row headings (1, 2, 3 etc) that you see on screen. However there may be occasions when you do want to print them. The Sheet Options group of commands on the Page Layout tab has four check boxes relating to viewing and printing gridlines and row and column headings.
To set the worksheet to include row and column headings when printing: Select the Print checkbox under Headings so that a tick appears in it
To set the worksheet to include gridlines when printing:
Excel 2010 Getting Started (310712) 73 ©De Montfort University, IT Training 2012
Select the Print checkbox under Gridlines so that a tick appears in it
View the effects of these settings in Print Preview
Excel 2010 Getting Started (310712) 74 ©De Montfort University, IT Training 2012
PRINTING A RANGE OF CELLS
To print just a section of the worksheet you first need to select the range of cells that are to be printed. Select the Home tab so that the worksheet will be displayed (any tab other than the
File tab will display the worksheet)
Select cells A1 to D15
Select the File tab
Select Print
Click the Print Active Sheets button
The following options are displayed.
Select Print Selection
Click here
Excel 2010 Getting Started (310712) 75 ©De Montfort University, IT Training 2012
The selected range of cells is shown in the print preview.
Keep the file open. Setting a Print Area
If a particular range of cells on the worksheet is the section that you wish to print each time, then a more convenient and efficient method is to set this range as the print area. This will be the only area printed when you print the active sheet. Open the JumbleSales workbook
Ensure that the Payroll worksheet is selected
Select the range A1:D18
Select the Page Layout tab
Click Print Area in the Page Setup group of commands
Select Set Print Area
This is now set as the area to be printed when you print the Payroll sheet. Click on an empty cell in the worksheet
Excel 2010 Getting Started (310712) 76 ©De Montfort University, IT Training 2012
The print area is bordered by broken lines.
Select the File tab
Select Print
Check the settings to see that Print Active Sheet is showing
Check the Print Preview to see that the range selected as the Print Area is the only area that will be printed
Close the JumbleSales workbook without saving
Excel 2010 Getting Started (310712) 77 ©De Montfort University, IT Training 2012
SORTING DATA It is sometimes necessary to sort the data in the worksheet. Let’s say that you need to produce a list of staff who earn £8.00 or less per hour. The simplest way to do this is to sort the data in order of the Hourly Rate first and then print the appropriate selection. The PayRoll workbook should still be open. To sort the data by Name in ascending alphabetical order: Click into a cell that contains a name (any cell between A3 and A15)
From the Editing group of commands on the Home tab choose: Sort & Filter
Select Sort A to Z from the drop-down list of options
The data is sorted alphabetically by name.
Sort the data by Hourly Rate in ascending order
Excel 2010 Getting Started (310712) 78 ©De Montfort University, IT Training 2012
FIND AND REPLACE
FINDING A WORD OR A VALUE
The Find command enables you to locate all occurrences of a particular word, phrase, or value in your worksheet. Open the workbook ClubMembers from C:\ExcelIntroduction
You are going to search for Vince wherever it appears on the sheet. From the Editing group of commands on the Home tab choose: Find & Select
Find
In the Find what: option box, type Vince
The Find Next button will find and select the first cell that contains the text you are searching for. The Find All button will list all the cells that satisfy your search criterion and select the first one. Click Find All
Excel 2010 Getting Started (310712) 79 ©De Montfort University, IT Training 2012
Excel lists the two cells that satisfy the criterion and selects the first one. Observe that one of the cells found is C23 (Vincenzo), because it contains ‘Vince’.
Click the Options button
Notice the check boxes that allow you to ‘Match case’ and ‘Match entire cell contents’. So if you wish to find cells containing the exact name Vince, then you will need to tick the check box to ‘Match entire cell contents’ before selecting the Find All or Find Next button. Click the Match entire cell contents check box to put a tick in it
Click Find All
This time B89 is the only cell found. Click Close
REPLACING A WORD OR A VALUE
It is possible to replace a word, phrase, or value with an alternative in one operation. You want to change the month June to July wherever ever it occurs in Column J. Select Column J (to ensure that only this column is searched)
From the Editing group of commands on the Home tab choose: Find & Select
Excel 2010 Getting Started (310712) 80 ©De Montfort University, IT Training 2012
Replace
In the Find What: option box, type June
In the Replace With: option box, type July
To make this alteration everywhere June occurs in the column: Click Replace All
Excel confirms that nine replacements were made.
Click OK
Click Close
June Gregson’s first name should not have been changed. Find June in Column B to check that June Gregson’s name is unchanged
Save the workbook
If June Gregson’s name was also changed to July,
● Click the Undo command on the Quick Access Toolbar
● Repeat the exercise above, ensuring that Column J is first selected
Excel 2010 Getting Started (310712) 81 ©De Montfort University, IT Training 2012
FREEZE PANES If you have a large worksheet, you will find that when you scroll down or across you are no longer able to see the column and/or row labels. To overcome this inconvenience, you can use the Freeze Panes command to keep the initial row(s) and/or column(s) visible on the screen. Switch to the PayRoll workbook
You are going to freeze the column labels in row 1. Select the View ribbon tab
From the Window group of commands: Select Freeze Panes
Choose Freeze Top Row from the list of options
A line appears at the bottom of row 1, indicating that the first row is frozen. Although your worksheet is small, you should be able to drag the vertical scroll bar down or click the down arrow by the scroll bar and see the entries move while the column labels remain stationery.
UNFREEZING PANES
To unfreeze the pane, from the Window group of commands: Select Freeze Panes
Choose Unfreeze Panes
Excel 2010 Getting Started (310712) 82 ©De Montfort University, IT Training 2012
FREEZING MULTIPLE ROWS OR COLUMNS
To freeze more than one row or column, you must first select the correct cell before using the Freeze Panes command. All rows above the selected cell will be frozen and all columns to the left of the selected cell will also be frozen. To freeze:
Top rows Select the left hand cell below the rows where you want the split to appear.
Left columns Select the top cell to the right of where you want the split to appear.
Both the top row(s) and left column(s)
Click the cell below and to the right of where you want the split to appear.
To freeze both the first row (column labels) and the first column (names): Select cell B2
Select the Freeze Panes command
Choose Freeze Panes from the list of options
Test it out by moving first the horizontal and then the vertical scroll bar
Unfreeze the panes
Switch to the ClubMembers workbook
Freeze the column labels in row 4
Save and close the ClubMembers workbook
Excel 2010 Getting Started (310712) 83 ©De Montfort University, IT Training 2012
USING AUTOFILL Excel includes a useful tool that will allow you to create a consecutive list. For example days or dates. Ensure that the workbook PayRoll is open
In cell A21 of your worksheet, type Monday
Click on the AutoFill handle and drag the handle down to A28
This also works for months of the year and sequences of numbers. You also have the option of creating and adding your own lists of data that you use on a regular basis. Delete the days of the week that you have just added to the worksheet
In A21, type Month 1
With A21 selected, drag the AutoFill handle down to A26
Excel recognises that A21 is the start of a sequence and fills in the other cells appropriately. Delete the contents of A21:A26
Copy handle / AutoFill handle
Excel 2010 Getting Started (310712) 84 ©De Montfort University, IT Training 2012
There are some situations where Excel cannot guess that a sequence is wanted. To illustrate this, you will add incrementing numbers to the payroll records. Insert a column at the beginning of the worksheet
In cell A1 type Staff number
Type 1 in cell A3
With A1 selected, click the AutoFill handle and drag the handle to A15
Excel does not create the sequence that you want, so you need to specify that you want a sequence. An Auto Fill Options button is displayed at the bottom right of the selected range, immediately after using the AutoFill handle.
Click the Auto Fill Options button
Select Fill Series
Auto Fill Options
Excel 2010 Getting Started (310712) 85 ©De Montfort University, IT Training 2012
The values in Column A change to the sequence 1 to 13.
Format the numbers by making them centred, bold, and italic
Save the workbook
Excel 2010 Getting Started (310712) 86 ©De Montfort University, IT Training 2012
ERROR MESSAGES Error messages come in several guises and some are more helpful than others in telling you where the error lies. If you have an error message, the best advice is to read it and try to understand before taking any action. The example below is quite useful. It tells you that there is an error in the formula (there was a semi-colon in the range instead of a colon) and suggests a solution which you can choose to accept.
If you choose not to accept it, more information is offered and it is up to you to decide what the error is and how it should be amended.
WHAT DOES IT MEAN?
######- the column is not wide enough to display the numerical value
#NAME? - a formula contains text that Excel does not recognise
#VALUE! - a cell referenced in a formula does not have a numerical value
#DIV/0! - number is divided by zero
#REF! - a formula includes an invalid cell reference
Close the workbook
Close Excel