getting started - de montfort universityexcel 2010 getting started (310712) 1 ©de montfort...

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


Document signposts Instructions for you to type

Bold text

Shortcuts

Reminders

Notes

Exercises


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


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


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


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


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)


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


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


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


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)


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:


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.


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.


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


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


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


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


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.


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


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


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).


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).


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:


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


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


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


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


Column D now looks like this:

Save the workbook


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.


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


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


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


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


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


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


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


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


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.


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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


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)


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


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


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


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


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


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


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


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.


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.


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


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


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


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


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


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


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


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


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:


Select the Print checkbox under Gridlines so that a tick appears in it

View the effects of these settings in Print Preview


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


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


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


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


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 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


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


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


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


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


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


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


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

getting started - de montfort universityexcel 2010 getting started (310712) 1 ©de montfort...

Documents