ecdl unit 4 manual

ECDL Module 4 (Syllabus 5)

Spreadsheets

Learning & Development

ECDL Unit 4 - Spreadsheets

About This Training ManualThis material is yours to keep and is intended as a guide to be used during the training course and as a reference once the course is completed. Each section begins with a list of topics to be explored. The courseware is designed so that each topic is fully explained and step–by-step instructions are given.

There are a number of conventions used in this training manual:

Format Description

BOLD ITALICS This is indicates a command to follow e.g. an option or button to press

[ ] Keys to press are shown in square brackets e.g. [space]

This marks the start of a method for performing a specific task

NOTE: This marks additional information or points out a common pitfall

[CTRL] + [Page Up] This means the first key is used in conjunction with the second

1


2


ECDL Unit 4 Course Contents

USING THE APPLICATION: WORKING WITH SPREADSHEETS............................................................................6

THE SPREADSHEET CONCEPT.....................................................................................................................................7OPENING/CLOSING MICROSOFT EXCEL (4.1.1.1).........................................................................................................8OPENING/CLOSING SPREADSHEETS (4.1.1.1).............................................................................................................10CREATING NEW SPREADSHEETS (4.1.1.2).................................................................................................................12SAVING SPREADSHEETS (4.1.1.3)............................................................................................................................13SAVING SPREADSHEETS AS ANOTHER FILE TYPE (4.1.1.4)............................................................................................16SWITCHING BETWEEN OPEN SPREADSHEETS (4.1.1.5).................................................................................................17

USING THE APPLICATION: ENHANCING PRODUCTIVITY.................................................................................20

SETTING BASIC OPTIONS/PREFERENCES (4.1.2.1).......................................................................................................21USING HELP FUNCTIONS (4.1.2.2)..........................................................................................................................24USING MAGNIFICATION/ZOOM TOOLS (4.1.2.3)........................................................................................................25RESTORING & MINIMIZING THE RIBBON (4.1.2.4)......................................................................................................26

CELLS: INSERT, SELECT.................................................................................................................................. 28

CELL DATA (4.2.1.1).............................................................................................................................................29GOOD PRACTICE IN CREATING LISTS (4.2.1.2)...........................................................................................................29ENTERING DATA IN A CELL (4.2.1.3)........................................................................................................................30DATES AND TIMES................................................................................................................................................31SELECTING DATA (4.2.1.4).....................................................................................................................................32

CELLS: EDIT, SORT......................................................................................................................................... 34

EDITING DATA (4.2.2.1)........................................................................................................................................35EDITING DATA WHILST TYPING.................................................................................................................................36THE UNDO/REDO FACILITY (4.2.2.2).......................................................................................................................37THE SEARCH COMMAND (4.2.2.3)..........................................................................................................................38THE REPLACE COMMAND (4.2.2.4).........................................................................................................................39SORTING A CELL RANGE (4.2.2.5)...........................................................................................................................40COPYING CONTENT (4.2.3.1).................................................................................................................................45THE FILL HANDLE/AUTOFILL TOOL (4.2.3.2).............................................................................................................47MOVING CONTENT (4.2.3.3)..................................................................................................................................48DELETING CONTENT (4.2.3.4)................................................................................................................................50

MANAGING WORKSHEETS: ROWS AND COLUMNS........................................................................................52

SELECTING ROWS & COLUMNS (4.3.1.1 & 4.3.1.2)..................................................................................................53INSERTING & DELETING ROWS & COLUMNS (4.3.1.3)................................................................................................54MODIFYING COLUMN WIDTHS (4.3.1.4)..................................................................................................................56MODIFYING ROW HEIGHTS (4.3.1.4).......................................................................................................................58FREEZING/UNFREEZING TITLES (4.3.1.5)..................................................................................................................59

MANAGING WORKSHEETS: WORKSHEETS.....................................................................................................62

SWITCHING BETWEEN WORKSHEETS (4.3.2.1)...........................................................................................................63INSERTING/DELETING WORKSHEETS (4.3.2.2)...........................................................................................................64RECOGNISING GOOD PRACTICE IN NAMING WORKSHEETS (4.3.2.3)..............................................................................65COPYING, MOVING, RENAMING WORKSHEETS (4.3.2.4).............................................................................................65

FORMULAS AND FUNCTIONS: ARITHMETIC FORMULAS................................................................................70

RECOGNISING GOOD PRACTICE IN FORMULA CREATION (4.4.1.1).................................................................................71CREATING FORMULAS (4.4.1.2)..............................................................................................................................71EDITING A FORMULA.............................................................................................................................................72STANDARD ERROR VALUES (4.4.1.3).......................................................................................................................73

3


RELATIVE CELL REFERENCING (4.4.1.4)....................................................................................................................74ABSOLUTE CELL REFERENCING (4.4.1.4)...................................................................................................................75

FORMULAS AND FUNCTIONS: FUNCTIONS....................................................................................................78

FUNCTIONS (4.4.2.1)............................................................................................................................................79CONDITIONAL LOGIC (4.4.2.2)................................................................................................................................81

FORMATTING: NUMBERS/DATES.................................................................................................................. 84

FORMATTING CELL NUMBERS & DATES (4.5.1.1)......................................................................................................85FORMAT DATE STYLES & CURRENCY SYMBOLS (4.5.1.2).............................................................................................88FORMATTING CELLS TO NUMBERS AS PERCENTAGES (4.5.1.3)......................................................................................92

FORMATTING: CONTENTS............................................................................................................................ 96

FORMATTING FONT SIZES & TYPES (4.5.2.1).............................................................................................................97APPLYING BOLD, ITALIC, UNDERLINE & DOUBLE UNDERLINE (4.5.2.2).........................................................................100APPLYING COLOURS TO CELL CONTENT (4.5.2.3).....................................................................................................102APPLYING CELL BACKGROUNDS (4.5.2.3)................................................................................................................104USING FORMAT PAINTER (4.5.2.4)........................................................................................................................106

FORMATTING: ALIGNMENT, BORDER EFFECTS............................................................................................108

TEXT WRAPPING (4.5.3.1)...................................................................................................................................109ALIGNMENT (4.5.3.2).........................................................................................................................................110CELL CONTENT ORIENTATION (4.5.3.2)..................................................................................................................112MERGING CELLS & CENTERING A TITLE (4.5.3.3).....................................................................................................114ADDING CELL BORDERS (4.5.3.4)..........................................................................................................................115

CHARTS: CREATE........................................................................................................................................ 118

CREATING CHARTS (4.6.1.1)................................................................................................................................119SELECTING A CHART (4.6.1.2)..............................................................................................................................120CHANGING THE CHART TYPE (4.6.1.3)...................................................................................................................121MOVE, RESIZE, DELETE A CHART (4.6.1.4).............................................................................................................122IDENTIFYING & SELECTING CHART ITEMS.................................................................................................................125ADD, REMOVE, EDIT A CHART TITLE (4.6.2.1).........................................................................................................126ADDING DATA LABELS TO A CHART (4.6.2.2)..........................................................................................................127CHANGING THE CHART AREA BACKGROUND COLOUR (4.6.2.3)..................................................................................128ADDING/REMOVING A LEGEND..............................................................................................................................129CHANGING THE LEGEND FILL COLOUR (4.6.2.3).......................................................................................................131CHANGING THE COLOUR OF CHART ELEMENTS (4.6.2.4)...........................................................................................132CHANGING FONT SIZE AND COLOUR (4.6.2.5).........................................................................................................133

PREPARE OUTPUTS: SETUP......................................................................................................................... 136

CHANGING MARGINS (4.7.1.1).............................................................................................................................137CHANGING WORKSHEET ORIENTATION (4.7.1.2)......................................................................................................139CHANGING PAPER SIZE (4.7.1.2)...........................................................................................................................139PAGE SETUP.......................................................................................................................................................140FITTING WORKSHEET CONTENTS (4.7.1.3)..............................................................................................................141HEADERS & FOOTERS (4.7.1.4 & 4.7.1.5).............................................................................................................142CHECKING SPREADSHEETS (4.7.2.1).......................................................................................................................148DISPLAYING/HIDING GRIDLINES (4.7.2.2)...............................................................................................................149DISPLAY/HIDING ROW & COLUMN HEADINGS FOR PRINTING (4.7.2.2)........................................................................150APPLYING AUTOMATIC TITLE ROW PRINTING (4.7.2.3).............................................................................................151PRINT PREVIEW (4.7.2.4)....................................................................................................................................152PRINTING (4.7.2.4)............................................................................................................................................153SETTING PRINT AREAS..........................................................................................................................................154

MORE INFORMATION................................................................................................................................. 156

MORE INFORMATION...........................................................................................................................................157

4


EXCEL KEYBOARD SHORTCUTS................................................................................................................................158

5


6


Chapter 1

Using the Application: Working with Spreadsheets

Topics

The following topics are covered in this chapter:

The Spreadsheet Concept Opening/Closing Microsoft Excel (4.1.1.1) Opening/Closing Spreadsheets (4.1.1.1) Creating New Spreadsheets (4.1.1.2) Saving Speadsheets (4.1.1.3) Saving Spreadsheets as Another File Type (4.1.1.4) Switching Between Open Spreadsheets (4.1.1.5)

7


The Spreadsheet ConceptA computer spreadsheet is similar to a very large piece of paper which is ruled into rows and columns. The intersection of a row and a column is called a cell and each cell has its own unique reference, similar to a map reference.

A spreadsheet can hold a variety of different data types, and is generally used when calculations need to be performed. The power of a computer spreadsheet lies in its ability to automatically recalculate formulae whenever data is changed. This saves a great deal of time and allows the user to create different results easily. Operations such as copying data, formatting numbers and creating graphs can be performed simply and quickly.

8

Columns

Rows

Cell


Opening/Closing Microsoft Excel (4.1.1.1)The location of Microsoft Excel on your PC will depend on how the application has been installed. The instructions below are an example of finding Excel, but you may find that the actual location differs from PC to PC.

To Open Microsoft Excel

Using the Mouse:

1. Click

2. Choose All Programmes

3. Select Microsoft Office

4. Click Microsoft Office Excel 2007

Microsoft Excel will now open

OR

1. Double click on the Excel shortcut icon on the Desktop

Microsoft Excel will now open

9


To Close Microsoft ExcelUsing the Mouse:

1. Click

OR

1. Click

2. Select

Microsoft Excel should now close down

10


Opening/Closing Spreadsheets (4.1.1.1)A spreadsheet that has been saved can be re-opened so that further data can be added or amendments made. Before a spreadsheet can be opened, the location it was saved in must be specified.

Once you have finished working on a spreadsheet, you can close it. It’s a good idea to get into the habit of closing files when not working on them to prevent having too many files open at any one time.

To Open a SpreadsheetUsing the Microsoft Button:

1. Click the Microsoft Button

2. Click Open

The Open dialogue box should now be displayed

3. In the Look In box, click to choose the location of the file (Drive & Folder)

4. Select the file (e.g. letter to Mr. Smith)

5. Choose Open

OR

1. Click on the Quick Access Toolbar

2. Follow steps 3-5 as shown above

11


To Close an Excel SpreadsheetUsing the Microsoft Button:


2. Click Close

A prompt may appear that asks the user to save any changes to open spreadsheets.

12


Creating New Spreadsheets (4.1.1.2)Every time a new spreadsheet is created, a specific template is used to establish specific formats, margins paper size, font type and character size. There are several different templates available in Excel, ranging from blank templates to templates that allow a user to create faxes, memos and brochures. When a template is selected, Excel automatically creates a new spreadsheet based on the selected template.

To Create a New Blank SpreadsheetUsing the Microsoft Button:

1. Click the Microsoft button

2. Click New

The New document dialogue box appears

3. Select Blank Workbook

4. Click

Using the Keyboard

1. Press [CTRL] + [N

13


Saving Spreadsheets (4.1.1.3)Spreadsheets may be saved at any time. It is important to save work at regular intervals, not just at the end of data input. A filename and location is specified when saving a workbook. There are two ways to save a workbook; Save and Save As. The Save As option will allow a new filename to be given to a workbook. The Save option will save the file keeping the same name. Apart from being reasonably descriptive, there are a few rules governing the choice of filenames:

The complete path to the file, including drive letter, server name, folder path and filename can contain up to 218 characters

filenames cannot include forward slash (/), backslash (\), greater than sign (>), less than sign (<), asterisk (*), question mark (?), quotation mark (“), pipe symbol (|), colon (:), or semicolon (;)

The location that files are saved in can vary. The most common areas in UWE are:

Drive Description

A: Floppy Disk drive (FDD). A FDD reads and writes data to a small, circular piece of metal-coated plastic similar to audio cassette tape. The capacity for a floppy disk is 1.44mb. The FDD is removable media.

C: & D: The PC’s Hard Drive. It is split into 2 areas. The C: drive should not be used as it’s used for application and system data. The D: can be used but it is not ‘Backed up’.

H: This is your Personal Drive. When a user logs into windows they gain access to a personal area on the network that allows them to save their own work without others getting access to it. The data stored on the H: drive is ‘Backed up’ so is therefore the advised place to save work.

S: This is a shared network Drive. Typically this drive is arranged into Faculty or Service groupings. Users will only be able to save and access work in their own group folders. (E.g. Personnel staff will be able to access the Personnel folders but will not be able to open any other group folders).

T: Another shared network drive but for students. Academic staff will have access to their own group folders to share documents with students.

14


To Save a Spreadsheet for the First TimeUsing the Mouse:

1. On the Quick Access Toolbar, click the Save icon

The Save As dialogue box opens

2. In the Save As drop down, choose a location for the spreadsheet

3. In the File Name box, type a name for the spreadsheet

4. Click Save

Using the Keyboard:

1. Press [CTRL] + [S]


2. In the File Name box, type a name for the spreadsheet

3. Click Save

15

Type File Name here

Use this drop down arrow to choose location


To Save an Existing Document as a New SpreadsheetUsing the Microsoft Button:

1. To prevent overwriting the original document, use the Save As command to create a new file as soon as you open the original spreadsheet.

2. Open the Spreadsheet that you want to use as the basis for the new document.

3. Click the Microsoft Office Button and then click Save As.


4. In the File Name box, type a name for the spreadsheet (something different to the existing name)

5. Click Save

16


Saving Spreadsheets as Another File Type (4.1.1.4) To Save a Spreadsheet as another file type

Using the Microsoft Button:

1. Click the Microsoft Button and then click Save As.


2. In the File Name box, type a name for the document (something different to the existing name)

3. Click the Save as type drop down box and select the required file type

4. Click Save

17


Switching Between Open Spreadsheets (4.1.1.5)It is possible to open more than one Spreadsheet at any time; a user can then simply switch between the open Spreadsheets.

Switching Between Open SpreadsheetsUsing the Mouse:

1. Open the Spreadsheets required

Multiple Spreadsheets should now be displayed in the status bar

2. Click appropriately on the Spreadsheets required to bring them up on the screen.

18


NOTES

19


NOTES

20


Chapter 2

Using the Application: Enhancing Productivity

Topics


Setting Basic Options/Preferences (4.1.2.1) Using Help Functions (4.1.2.2) Using Magnification/Zoom Tools (4.1.2.3) Restoring & Minimizing the Ribbon (4.1.2.4)

21


Setting Basic Options/Preferences (4.1.2.1)It is possible to set a user name for Excel and to change your default directory/Folder. Changing this default will allow you to access a specific directory or folder every time you try to open or save a file.

To Change the User NameUsing the Mouse:

1. Click

2. Click

The Excel Options Dialogue box will appear

3. Type in the new User name required

4. Click OK

22


To Change the Default Directory/FolderUsing the Mouse:

1. Click

2. Click

The Excel Options Dialogue box will appear

3. Click Save

The following screen will appear

4. Type in the new Default File location

23


OR

Click Browse and search for the new Default File location – Click OK once found

5. Click OK

The Default Directory/Folder should now be changed

24


Using Help Functions (4.1.2.2)Help is available using Help Task Pane. A user can type in their issue and a list of matching information pages will be displayed. Alternatively the user can click to see a full table of contents

Options Description

Table of Contents Displays as a table of contents, which allows the user to navigate through the help topics.

Microsoft Office Online Allows the user to Connect the the Microsoft Website for assistance, training, or downloads

To use HelpUsing the Keyboard:

1. Press [F1]

OR

Using the Menu

1. Click

A user can then click on any of the table of contents links or search by typing something into the search box.

25


Using Magnification/Zoom Tools (4.1.2.3)You can zoom in to get a close-up view of your spreadsheet or zoom out to see more of the page at a reduced size. When you zoom in or out, it will make no difference to the way the spreadsheet prints out.

Some users prefer to see more of a spreadsheet by zooming out, whilst some prefer to have the data bigger by zooming in. The zoom control on the status bar allows you to control the zoom level by moving the slider left or right.

To Quickly Change the Zoom LevelUsing the Mouse:

1. On the status bar, click the Zoom slider

.

2. Slide to the percentage zoom setting that you want.

Using the Keyboard/Mouse:

1. Hold down [CTRL]

2. Scroll the Mouse wheel to zoom in or out

To Choose a Zoom SettingUsing the Ribbon:

1. Click the View tab

2. Select the view settings required

26


Restoring & Minimizing the Ribbon (4.1.2.4) To Minimize the Ribbon

Using the Mouse:

1. On the Quick Access Toolbar, click

The Customise Quick Access Toolbar drop down will appear

2. Click Minimize Ribbon

The Ribbon should now be minimized

To Restore the RibbonUsing the Mouse:

1. On the Quick Access Toolbar, click

The Customise Quick Access Toolbar drop down will appear

2. Click Minimize Ribbon

The Ribbon should now be Restored

27


NOTES

28


Chapter 3

Cells: Insert, Select

Topics


Cell Data (4.2.1.1) Good Practice in Creating Lists (4.2.1.2) Entering Data in a Cell (4.2.1.3) Dates and Times Selecting Data (4.2.1.4)

29


Cell Data (4.2.1.1)A cell should only ever contain one data item (i.e. surname in one cell and student number in an adjacent cell. Sticking to this rule makes it easier to manipulate and sort data.

Good Practice in Creating Lists (4.2.1.2)To some extent, Excel workbooks can be used as a database. Any data can be manipulated and interrogated, but it helps if you data is structured in a proper list format.

Excel lists consist of columns and rows of data structured in a specific way:

Each column should contain the same type of data in every row in the column. In a database, columns are known as fields

Each row in the list contains all of the data for one entity (a person, organization, object, etc.). In a database, Rows are called Records

The first row of the list must contain a unique name at the top of each column. These will be your headings

This first row does not need to be the first row of the worksheet

The row containing the column headings must be formatted differently from the rest of the list (i.e. bold, larger font, italicized, etc.)

There can be no blank rows in the list (there can be blank cells in a column, but the entire row cannot be empty)

Data in a column must be in the same format for every row in the column (numbers shouldn’t be spelled out in one row and entered as digits in the rest of the rows in that column)

Blank rows should be inserted before any Total rows

Cells bordering a list should be blank

30


Entering Data in a Cell (4.2.1.3)Data will appear in the cell where the cell cursor is located. When the cell cursor is positioned on a cell, this makes that cell the active cell. As data is typed in a cell, an insertion point will automatically display and all data will appear to the right of the insertion point. As data is typed, it displays in both the current cell and in the Formula Bar.

Types of DataExcel distinguishes between two main types of data: text and numbers.

Types of Data Description

Text Any characters which are not to be used in calculations

Numbers Data to be used in calculations, e.g. numbers, formulae and characters such as + * - / %, etc

To Enter DataUsing the Keyboard/Mouse:

1. Position the cell cursor on the required cell

2. Type text

3. In the Formula Bar, click

Or

Click in another cell

Keyboard:

1. Type text

2. Press [Return]

Note: When data is entered into a cell, the data is stored in that single cell even though it may appear to have extended into the next cell. Up to 32,767 characters can be entered into a single cell.

31

Data typed in cell appears here Point

Insertion Point

Data being typed into current cell


Dates And Times When entering dates use a hyphen or slash to separate the day, month and year, e.g. type Oct-97 or 10/1/97.

When entering times based on the 12 hour clock, type the time required followed by a space and then type the letter a for am or p for pm, e.g. 10:00 p. If am or pm is not specified in this way by the user, Excel will enter the time as am. Dates and times entered in this way will be treated as numeric values and can therefore be included in calculations.

Excel allows the user to quickly enter the current date and the current time.

To Add Today’s DateUsing the Keyboard:

1. In the required cell, press [Ctrl]+[;]

2. Press [Return]

To Enter the Current TimeUsing the Keyboard:

1. Press [Ctrl]+[Shift]+[;]

2. Press [Return]

32


Selecting Data (4.2.1.4)To enter or edit data in several rows or columns, it is necessary to select the range of cells. Selecting data is also very important for formatting and including cells within a formula. There are various ways of selecting cells by using the mouse, keyboard or a combination of the two.

To Select CellsUsing the Mouse:

Action Description

Click in a cell Selects the cell

Click and drag over several cells Selects the range of cells

Click in one cell, press [Shift] and click in another cell Selects the range of cells

Press [Ctrl] and drag over the ranges required Selects multiple ranges

Click row number Selects the whole row

Click column letter Selects the whole column

Click to left of column A Selects the whole worksheet

Click and drag across row or column headings Selects adjacent rows or columns

Select the first row or column, press [SHIFT]and select the last row or column

Selects adjacent rows or columns

Select the first row or column, press [CTRL] and select other rows or columns

Selects non-adjacent rows or columns

Note: A selection can be cancelled by clicking in another cell.

Using the Keyboard:

Key Description

[Arrows] Selects one cell

[Shift]+[Arrows] Selects a range of cells

[Shift]+[Spacebar] Selects the whole row

[Ctrl]+[Spacebar] Selects the whole column

[Ctrl]+[A] Selects the whole worksheet

33


NOTES

34


Chapter 4

Cells: Edit, Sort

Topics


Editing Data (4.2.2.1) Editing Data Whilst Typing The Undo/Redo Facility (4.2.2.2) The Search Command (4.2.2.3) The Replace Command (4.2.2.4) Sorting a Cell Range (4.2.2.5)

35


Editing Data (4.2.2.1)Existing cell entries can be edited, enabling characters to be inserted or deleted. If required, data in a cell can be completely overtyped.

To Edit Cell EntriesUsing the Mouse:

1. Double-click in the cell to edit

2. The insertion point displays.

3. Position the insertion point

4. Make changes required

5. In the Formula Bar, click

Using the Keyboard:

1. Position the cell cursor on the cell to edit

2. Press [F2]

3. The insertion point displays.

4. Position the insertion point

5. Make changes required

6. Press [Return]

To Overtype Cell EntriesUsing the Keyboard:

1. Position the cell cursor on the required cell

2. Type new data

3. Press [Return]

36


Editing Data whilst TypingWhen typing data into a cell, if any errors are made before the [Return] key is pressed, there are several ways to remove the mistake: cancel the data entry completely or press the [Backspace] key to erase the previous character typed.

To Delete DataUsing the Keyboard:

1. Whilst typing press [Delete] to clear data to the right of the insertion point

Or

Press [Backspace] to clear data to the left of the insertion point

To Cancel Data EntryUsing the Keyboard:

1. Whilst typing data, Press [Esc]

37


The Undo/Redo Facility (4.2.2.2)Excel 2007 keeps track of all editing and formatting changes that are made to a worksheet. If a mistake is made, Excel allows that action to be undone. The last 100 actions can be reversed.

If an action has been undone by mistake, the Redo command can be used.

To UndoUsing the Mouse:

1. Click

Note: To undo several actions, click the arrow next to the Undo button and select the action(s) required from the list. Excel reverses the selected action and all actions above it.

To RedoUsing the Mouse:

1. Click

38


The Search Command (4.2.2.3)Microsoft Excel can be used to quickly search for every occurrence of specific content. Using the same dialogue box, you can also find and replace content. For example, you could find all instances of ‘Data’ and replace it with ‘information’. You can extend your search by using wildcards and codes.

To Find ContentUsing the Ribbon:

1. Click the Home tab

2. In the Editing group, click Find & Select

3. Click Find

The Find/Replace dialogue box appears

4. Type the content you want to find in your spreadsheet

5. Click Find Next

6. Click Find Next again to find the next instance of that text in your spreadsheet

39


The Replace Command (4.2.2.4) To Replace Content

Using the Ribbon:


2. In the Editing group, click Find & Select

3. Click Replace

The Find/Replace dialogue box appears

4. In the Find what section, type the content you want to find in your spreadsheet

5. In the Replace with section, type the content you want to use as a replacement

6. Click Replace to replace each instance one at a time

Or

7. Click Replace All to replace all instances

40


Sorting A Cell Range (4.2.2.5)Sorting allows you to organise your list into a specific order whether alphabetically or numerically. For example, you may have a list of stock items with prices. You can use the sort facility to organise the list so that the most expensive items are at the top of the list and the least expensive at the bottom.

If the list is to be sorted, then a user can use the sort commands on the Ribbon or they can use the Sort dialogue box.

Note: When sorting, it is important to select either the whole list or to just place the active cell in the column to sort. If only the column is selected then that column might be sorted and the rest of the data could stay in its place.

To Sort DataUsing the Ribbon:

1. Click in a cell, within the column to sort

2. Select the Home tab

3. Click the Sort and Filter Command button, located in the Editing group

4. Click to sort Ascending

Or

5. Click to sort Descending

Using the Ribbon:


2. Select the Data tab

3. Click to sort Ascending

Or

4. Click to sort Descending

41


Using the Ribbon:


Or

Select the whole list

2. Click the Data tab

3. Click the Sort command, located in the Sort and Filter group

The Sort dialogue box appears

4. In the Sort By box, choose the column to sort by

5. In the Sort on box, choose what you would like Excel to sort based on

6. In the Order box choose Ascending/Descending

7. To then sort by another column click the add level, and follow steps 4-6

8. Choose OK

42


NOTES

43


NOTES

44


Chapter 5

Cells: Copy, Move, Delete

Topics


Copying Data (4.2.3.1) The Fill Handle/Autofill Tool (4.2.3.2) Moving Content (4.2.3.3) Deleting Content (4.2.3.4)

45


Copying Content (4.2.3.1)Copying is used to duplicate data in one part of the spreadsheet to another. When using the Copy and Paste commands, Excel allows the user to paste data as many times as required. Unlike the Cut and Paste feature, the dotted outline still displays on the edges of the selected data and the Status Bar describes the next steps to take. This allows the user to paste data repeatedly. Once data has been duplicated as required, the user can press the [Esc] key to cancel any further copies.

To Copy ContentUsing the Ribbon:

1. Select the Data to move

2. On Home tab, within the Clipboard group, click the Copy icon

A dotted outline displays on the edges of the selected area and the Status Bar at the bottom left corner of the workbook window describes the next steps to take.

3. Position the insertion point where the Data is top be placed

4. In the Clipboard group, click the Paste icon

Note: The copied Data can be pasted as many times as required until a new piece of Data is copied

Using the Keyboard:

1. Select the Data to be moved

2. Press [CTRL] + [C]

3. Position the insertion point where the Data is to be inserted

4. Press [CTRL] + [V]

Using the Right Mouse Button:


2. Right click the selected Data

3. Choose Copy from the shortcut menu

4. Right click where you would like the Data to be positioned

5. Choose Paste from the shortcut menu

46


To Copy Content using Drag and DropUsing the Mouse:


2. Position the Mouse on any edge of the selected data

3. The Mouse changes to an arrow shape

4. [Ctrl] + click and drag to new location

5. Whilst dragging, a navigation tip and outline displays indicating where the data will be placed. The mouse pointer has a + sign attached to it, indicating that the data will be copied and not moved

6. Release the Mouse, then release [Ctrl]

Note: To insert cells between existing cells, hold the [Shift] key while following step 3.

47

Fill Handle


The Fill Handle/Autofill Tool (4.2.3.2)The Fill Handle is especially useful when copying formulae across a range. It allows the formulae to be created only once and then copied into the relevant adjacent cells. When a formula is copied in Excel the references used are adjusted. This is because Excel does not record the actual cell references, but records the position the cells referred to in relation to the cell containing the formula. This is known as Relative Cell Referencing.

The fill handle can be used to copy formulas, numerical values, text or custom lists. Types of data that the Fill Handle will sequence are:

Months

Days

Numbers (to sequence numbers, type in the first two number of the sequence, select them both and then use the fill handle)

Text (when ordinary text is typed, using the fill handle will just copy that text)

In the example below the Data in cell A1 is copied using the fill handle

To Copy Data to an Adjacent RangeUsing the Mouse:

1. Select data to copy

2. Position the Mouse pointer on the fill handle

3. The Mouse pointer shape changes to a crosshair

4. Click and Drag over the adjacent range

5. Whilst dragging, a tip displays a sample of the data being copied and an outline displays indicating where the data will be placed

6. Release the Mouse

48


Moving Content (4.2.3.3)Data can easily be moved from one location to another, and if required from one worksheet or workbook to another using the Cut and Paste features. There are many methods of moving data by using the mouse, keyboard or a combination of the two. Excel enables the user to store up to 24 items in the clipboard making moving and copying data more efficient.

To Move ContentUsing the Ribbon:

1. Select the text to move

2. On Home tab, within the Clipboard group, click the Cut icon

The Data will be removed from the document

3. Position the insertion point where the Data is top be placed

4. In the Clipboard group, click the Paste icon

Using the Keyboard:


2. Press [CTRL] + [X]

3. Position the insertion point where the Data is to be inserted

4. Press [CTRL] + [V]

Using the Right Mouse Button:


2. Right click the selected Data

3. Choose Cut from the shortcut menu

4. Right click where you would like the Data to be positioned

5. Choose Paste from the shortcut menu

49


To Move Content using Drag and DropUsing the Mouse:


2. Position the mouse over on any edge of the selected data

3. Click and Drag to new location

4. Whilst dragging, a navigation tip and outline displays indicating where the data will be placed.


Note: To insert cells between existing cells, hold the [Shift] key whilst dragging.

50


Deleting Content (4.2.3.4)Data can be erased in several ways:

Erasing the contents of a cell, leaving the cell blank (deleting text, numbers and formulae)

Clearing the formats of a cell, leaving the contents as entered with no formatting clearing all, which removes both contents and formatting

To Clear Contents of a cell(s)Using the Keyboard:

1. Select cell(s)

2. Press [Delete]

Using the Ribbon:

1. Select cell(s)


3. Click from the cells group

51


NOTES

52


Chapter 6

Managing Worksheets: Rows and Columns

Topics


Selecting Rows & Columns (4.3.1.1 & 4.3.1.2) Inserting & Deleting Rows & Columns (4.3.1.3) Modifying Column Widths (4.3.1.4) Modifying Row Heights (4.3.1.4) Freezing/Unfreezing Titles (4.3.1.5)

53


Selecting Rows & Columns (4.3.1.1 & 4.3.1.2)To enter or edit data in several rows or columns, it is necessary to select the range of cells. Selecting data is also very important for formatting and including cells within a formula. There are various ways of selecting cells by using the mouse, keyboard or a combination of the two.

To Select Rows & ColumnsUsing the Mouse:

Action Description

Click row number Selects the whole row

Click column letter Selects the whole column

Click and drag across row or column headings Selects adjacent rows or columns

Select the first row or column, press [SHIFT]and select the last row or column

Selects adjacent rows or columns

Select the first row or column, press [CTRL] and select other rows or columns

Selects non-adjacent rows or columns

Note: A selection can be cancelled by clicking in another cell.

Using the Keyboard:

Key Description

[Shift]+[Spacebar] Selects the whole row

[Ctrl]+[Spacebar] Selects the whole column

54


Inserting & Deleting Rows & Columns (4.3.1.3)Rows can be inserted or deleted in a spreadsheet wherever necessary. To insert a single row, select a cell in the row immediately below where the new row is to be inserted, e.g. to insert a new row above row 3, select a cell in row 3. To insert multiple rows, select rows immediately below where the new rows are to be inserted. Select the same number of rows as the number to insert.

To insert a single column, select a cell in the column immediately to the right of where the new column is to be inserted, e.g. to insert a new column to the left of column F, select a cell in column F. To insert multiple columns, select columns immediately to the right of where the new columns are to be inserted. Select the same number of columns as the number to insert.

To Insert RowsUsing the Ribbon:

1. Select the cell/row(s) as required


3. Within the Cells group click the Insert drop down arrow

4. Select Insert Sheet Rows

Or

Click the Right Mouse button on the selected area, choose Insert

To Delete a RowUsing the Ribbon:

1. Select the row(s) to delete


3. Within the Cells group click Delete

Or

Click the Right Mouse button on the selected area, choose Delete

55


To Insert a ColumnUsing the Ribbon:

1. Select the cell/row(s) as required


3. Within the Cells group click the Insert drop down arrow

Select Insert Sheet Columns

Or

Click the Right Mouse button on the selected area, choose Insert

To Delete a ColumnUsing the Ribbon:

1. Select the Column(s) to delete


3. Within the Cells group click Delete

Or

Click the Right Mouse button on the selected area, choose Delete

Surrounding cells move to fill the space and related formulae update.

Note: When choosing the Delete option, if entire columns are not selected, a dialogue box will display, offering the opportunity to delete the entire column or entire row.

56


Modifying Column Widths (4.3.1.4)If data appears to have extended into another cell and the user wishes to display it in one cell, the width of the column can be altered to accommodate the data. The standard column width in a new Excel worksheet is set to 8.43. By double-clicking on a column, Excel will automatically adjust the width of that column to fit the longest entry. Alternatively, the user can specify a particular measurement by dragging the border with the mouse.

To Change the Column WidthUsing the Mouse:

1. To change the width of a single column, position the mouse on the column border to the right of the required column

Or

To change the width of several columns, select the columns required and position the mouse on the column border to the right of any of the selected columns

The mouse pointer changes shape.

2. Click and Drag right to increase the width

Or

Click and Drag left to decrease the width

3. Whilst dragging, a column width tip displays the width.


Using the Ribbon:

1. Select the column(s) as required


3. Within the Cells group click the Format drop down arrow

57


4. Select Column Width

5. Type in the width required

6. Choose OK

To Change the Column Width to the Longest EntryUsing the Mouse:

1. Select the columns as required

2. Position the mouse on the column border required

The mouse pointer changes shape.

3. Double-click

4. The width of the column alters to the size of the longest entry in the column

Using the Ribbon:

1. Select the columns as required


3. Choose Format, AutoFit Column Width

To Change the Column Width for the Entire WorksheetUsing the Ribbon:

1. Choose Format, Default Width

2. Type the width required

3. Choose OK

All columns in the current worksheet adjust to the same width, except those columns that have previously been changed.

58


Modifying Row Heights (4.3.1.4)The standard row height in a new Excel worksheet is set to 15.00. This may be altered through the menu or with the mouse.

To Modify the Row HeightUsing the Mouse:

1. To change the height of a single row, position the mouse on the row border below the required row

2. The mouse pointer changes shape to a double-headed arrow.

3. Click and Drag down to increase the width

Or

4. Click and Drag up to decrease the width

Or

5. Double-click to accommodate the largest font size in the row

Whilst dragging, a row height tip displays the height.

Using the Ribbon:

1. Select the rows as required



4. Click Row Height

Or

Click the Right Mouse button on the selected area, choose Row Height

5. Type the height required

6. Choose OK

59


Freezing/Unfreezing Titles (4.3.1.5)Panes can be frozen so that horizontal and vertical titles will remain on the screen as the user moves around the spreadsheet. The same part of the spreadsheet is not seen twice. The top row and first column can be easily frozen, however if you want to freeze extra rows them you will need to position your active cell in the correct position. When you freeze panes manually, rows above or columns to the left of the active cell will be frozen

To Freeze PanesUsing the Ribbon:

1. Select the View tab

2. In the Window group, click Freeze Panes

3. Choose the required option

To Unfreeze PanesUsing the Ribbon:

1. Select the View tab

2. In the Window group, click Unfreeze Panes

Note: When panes are frozen data may still be entered in all panes

60


NOTES

61


NOTES

62


Chapter 7

Managing Worksheets: Worksheets

Topics


Switching Between Worksheets (4.3.2.1) Inserting/Deleting Worksheets (4.3.2.2) Recognising Good Practice in Renaming Worksheets (4.3.2.3) Copying, Moving, Renaming Worksheets (4.3.2.4)

63


Switching Between Worksheets (4.3.2.1)A workbook containing multiple worksheets has the following advantages over single sheet workbooks.

Data can be organised and formatted in a more flexible manner

Related data can be accessed more efficiently than if it is in separate files

Formulae can be created to consolidate multiple worksheets

A workbook by default will have 3 worksheets.

To Switch Between WorksheetsUsing the Mouse:

1. Click on the required worksheet tab

64


Inserting/Deleting Worksheets (4.3.2.2) To Insert Additional Worksheets

Using the Ribbon:

1. Choose the Insert drop down, located on the Home tab, within the Cells group

2. Select Insert Sheet

Or

Click the Right Mouse button on the sheet tab, select Insert, Worksheet, OK

Or

Click

Each new sheet is assigned a sheet name, e.g. Sheet4, Sheet5, Sheet6.

To Delete a WorksheetUsing the Ribbon:

1. Select the required sheet

2. Choose the Delete drop down, located on the Home tab, within the Cells group

3. Choose Delete Sheet

Using the Mouse:

1. Right Mouse click the sheet to be deleted

2. Choose Delete

Note: Once a worksheet has been deleted, there is no going back, the Undo facility will not be available

65


Recognising Good Practice in Naming Worksheets (4.3.2.3)When naming or renaming worksheets it is recommended to use meaningful names, this will then give a good indication of what data is contained within the worksheet. Names can contain up to 31 characters and can include spaces. Duplicate names are not allowed.

Copying, Moving, Renaming Worksheets (4.3.2.4) To Copy a Worksheet

Using the Ribbon:

1. Click the sheet to be copied

2. Choose the Format drop down, located on the Home tab, within the Cells group

3. Select Move or Copy Sheet

The Move or Copy dialogue box will appear

4. If required select a sheet to put the copy before

5. Tick Create a copy

6. Click OK

66


Using the Mouse:

1. Right Click on the sheet to be Copied




4. Tick Create a copy

5. Click OK

OR

1. Click and Hold [CTRL] and the Left Mouse button on the required sheet

2. Drag and drop in the reuired location

To Move a WorksheetUsing the Ribbon:

1. Click the sheet to be copied



67




5. Click OK

Using the Mouse:

1. Right Click on the sheet to be Copied




4. Click OK

OR

1. Click and Hold the Left Mouse button on the required sheet

2. Drag and drop in the reuired location

68


To Rename a WorksheetUsing the Ribbon:


2. Select Rename Sheet

3. Type name required

4. Press [Return]

Using the Mouse:

1. Double-click on the required sheet tab


3. Press [Return]

OR

1. Right Click on the sheet to be renamed

2. Select Rename


4. Press [Return]

69


NOTES

70


Chapter 8

Formulas and Functions: Arithmetic Formulas

Topics


Recognising Good Practice in Formula Creation (4.4.1.1) Creating Formulas (4.4.1.2) Editing a Formula Standard Error Values (4.4.1.3) Relative Cell Referencing (4.4.1.4) Absolute Cell Referencing (4.4.1.4)

71


Recognising Good Practice in Formula Creation (4.4.1.1)Formulas are calculations created within Excel. When creating formulas within Excel, cell references should be used wherever possible, rather than just typing in numbers. This will then mean that if the numbers in the cells which have been referenced change, the formulas will automatically be recalculated.

Creating Formulas (4.4.1.2)All formulae start with =, e.g. 10-6 would be =10-6. Normally, formulae contain cell references instead of actual numbers, so that the formulae will recalculate whenever the numbers in the cells referred to are changed. A typical formula could be =B11-B12. When creating a formula several arithmetic operators may be used.

In the Example Below, we have a Sales figure and a Costs figure. To work out what the Profit/Loss is we have to enter a formula that deducts the Cost from the Sales

To get the most out of excel, a user would always type formulas with cell references (e.g.= A1+A2), so that if any of the original figures change, the answer will update. In our example above, to obtain the Profit/Loss the user would type in the formula shown below:

Operators Description

+ Addition

- Subtraction

* Multiplication

/ Division

% Percent

^ Exponentiation (the power of)

72

Cell B1

Cell B2

Formulae that deducts the Cost figure from the Sales

Figure


To Create a FormulaUsing the Mouse:

1. Select the answer cell

2. Type [=]

3. Select the first cell in the calculation (e.g. B1)

4. A dotted outline displays around the selected cell. If the wrong cell is selected it can be changed by simply selecting another cell.

5. Type an arithmetic operator, e.g. +

6. Repeat steps 3 and 4 until the calculation is complete

7. Press [Return]

Or

In the Formula bar, click

Using the Keyboard:


2. Type [=]

3. use the [Arrow] keys to navigate to the cells to be included in the formula

4. Type the formula required, e.g. =B2-B3

5. Press [Return]

Editing a FormulaA formula can be edited in the same way as any other data. However, when editing a formula, all cells and ranges referred to will display in a different colour and matching borders are applied to the cells and ranges.

To Edit a FormulaUsing the Mouse:

1. Double-click on the formula to edit

2. The cells that the formula refers to display in a different colour and borders display around the cells.

3. Edit the formula as required

4. Press [Return]

Or

73



To Edit a Formula Using the Formula BarUsing the Mouse:

1. Select the formula to edit

2. The Formula Bar will Display the Formula

3. Click into the Formula bar

4. Edit the Formula

5. Press [Return]

Or


Standard Error Values (4.4.1.3)Sometimes when creating formulae in Excel we may get an error message and need to amend our formulae accordingly. Common error messages include -

#ValueWhen text has been typed into a formula where a number is required

#DIV/0!When a formula divides by 0 or a blank cell

#Name?When Excel doesn’t recognise text in a formula

74


Relative Cell Referencing (4.4.1.4)The Fill Handle is especially useful when copying formulae across a range. It allows the formulae to be created only once and then copied into the relevant adjacent cells. When a formula is copied in Excel the references used are adjusted. This is because Excel does not record the actual cell references, but records the position the cells referred to in relation to the cell containing the formula. This is known as Relative Cell Referencing.

The Fill Handle can be used to copy formulas, numerical values, text or custom lists. Types of data that the Fill Handle will sequence are:

Months

Days

Numbers (to sequence numbers, type in the first two number of the sequence, select them both and then use the fill handle)

Text (when ordinary text is typed, using the fill handle will just copy that text)

In the example below the Data in cell A1 is copied using the fill handle

To Copy Data to an Adjacent RangeUsing the Mouse:

1. Select data to copy

2. Position the Mouse pointer on the fill handle

3. The Mouse pointer shape changes to a crosshair

4. Click and Drag over the adjacent range

5. Whilst dragging, a tip displays a sample of the data being copied and an outline displays indicating where the data will be placed


75

Fill Handle


Absolute Cell Referencing (4.4.1.4)Sometimes when copying a formula that contains a cell reference, it is important that the reference is not relative, e.g. does not change as it is copied to other cells. This means that the cell reference in the formula must be made Absolute (fixed).

This Formula is Incorrect. When copied, This formula is correct B8 is now fixed.B8 will change to B9, then to B10 etc and wont change when copied

To Absolute a CellUsing the Mouse:

1. Create the formula in the normal way, selecting the cells to include

2. Press [F4] whilst selecting the cell that is to be absolute

1. An absolute reference appears, e.g. $A$1

2. The [F4] key offers four options if pressed repeatedly, A$1, $A1, A1, $A$1.

3. The $ symbol means fix, so that any part of the cell reference can be made absolute

4. Complete the formula as normal

Note: The user can manually type the $ symbol into the formula instead of pressing [F4] if they wish.

76


NOTES

77


NOTES

78


Chapter 9

Formulas and Functions: Functions

Topics


Functions (4.4.2.1) Conditional Logic (4.4.2.2)

79


Functions (4.4.2.1)Functions are predefined formulae that perform calculations by using specific values, called arguments, in a particular order, called the syntax.

The syntax of a function begins with the function name, followed by an opening parenthesis, the arguments for the function separated by commas and a closing parenthesis. If the function starts a formula, type an = before the function name.

Some of the most frequently used are:

Function Description

=SUM(range) Calculates the total of a range of numbers

=AVERAGE(range) Calculates the average of a range of numbers

=MAX(range) Calculates the maximum number in a range

=MIN(range) Calculates the minimum number in a range

=COUNT(range) Calculates the number of entries in a range (numbers only)

=COUNTA(range) Calculates the number of entries in a range (text)

=COUNTBLANK(range) Calculates the number of blank cells in a range

All of the above functions are created in a similar way. Once a function has been created, use the fill handle to copy it across/down to the required range. Remember formula only needs to be created once.

The user can create functions themselves or use the Function box and Formula palette to assist them.

To Manually Create a FunctionUsing the Keyboard:


2. Type the function required, e.g. =SUM(

3. Select the range of cells to sum together

Whilst dragging, a tip displays the row and column number of the selected range and a dotted outline displays around the range.

4. Type )

5. Press [Return]

The result of the formula displays in the answer cell and the calculation displays in the Formula bar.

80


To Create a Function Using the Formula PaletteUsing the Mouse


2. Type =

The Functions box now replaces the Name box.

3. In the Functions box, click

A list of the most commonly used functions displays.

4. Choose the function required, e.g. Average

The Formula Palette now displays.

5. In the Number 1 box, click

The Formula Palette collapses to display the worksheet.

6. Select the range required

7. In the Formula Palette, click

The Formula Palette expands to its original size.

8. On completion, choose OK

The result of the formula displays in the answer cell and the calculation displays in the Formula bar.

Note: If the function required is not listed in the Functions box, choose ‘More Functions’ to display the Paste Function dialogue box.

81


Conditional Logic (4.4.2.2)Having the ability to test cells, and automate decisions based on the results of those tests is one of the most valuable functions in Excel. Logical functions can be used either to see whether a single condition is true or false or to check multiple conditions. A conditional logic function known as the IF function is used to make decisions based on a logical test.

The IF function can be used to determine whether a condition is true or false. One value is returned if the condition is true and a different value is returned if the condition is false.

E.g. =IF(Condition,Action If True,Action If False)

Conditions can contain any of the following logical operators:

Symbol Meaning

= Equal to

> Greater than

< Less than

>= Greater than or equal to

<= Less than or equal to

<> Not equal to

Examples of the IF function are shown below:

Formula Description

=IF(B1<B2,"OOPS","SPEND") If B1 is less than B2, then the text OOPS is displayed, otherwise the text SPEND is displayed.

=IF(B4>0,B4*2%,0) If B4 is greater than zero then B4 is multiplied by 2%, otherwise zero is displayed.

=IF(B4>0,"CREDIT","O/D") If B4 is greater than zero, the message displayed is Credit, otherwise the message displayed is O/D.

82


NOTES

83


NOTES

84


Chapter 10

Formatting: Numbers/Dates

Topics


Formatting Cell Numbers & Dates (4.5.1.1) Formatting Cells to Display Dates Styles & Currency Symbols (4.5.1.2 Formatting Cells to Numbers as Percentages (4.5.1.3)

85


Formatting Cell Numbers & Dates (4.5.1.1)To change the way numbers display in a worksheet, the user can change the number format. Changing the number format does not affect the actual data values used in calculations. Number formats are applied using the Format menu or Formatting Toolbar buttons. Users can also create their own formats.

To Alter the Number FormatUsing the Ribbon:

1. Select the required cell(s)


3. Within the Number group click the Format drop down arrow

The Format Cells dialogue box will appear

86


4. Within the Number tab, select Number from the category listing

5. Select the required formats

To Alter the Number FormatUsing the Mouse:

1. Select the required cell

2. Click the Right Mouse button on the selected area, choose Format Cells

3. Choose the Number tab

4. Choose the Number category

5. Choose the required options

6. Choose OK

87


To Alter the Number of Decimal PlacesUsing the Ribbon:


2. Click the required decimal button within the number grouping

88

Decrease/Increase Decimal Place


Format Date Styles & Currency Symbols (4.5.1.2)To change the way dates display in a worksheet, the user can change the number format. Changing the number format does not affect the actual data values used in calculations. Number formats are applied using the Format menu or Formatting Toolbar buttons. Users can also create their own formats.

To Alter the Display DateUsing the Ribbon:





4. Within the Number tab, select Date from the category listing

89



To Alter the Date FormatUsing the Mouse:




4. Choose the Date category


6. Choose OK

90


To Alter the CurrencyUsing the Ribbon:





4. Within the Number tab, select Currency from the category listing


91


To Alter the Date FormatUsing the Mouse:




4. Choose the Currency category


6. Choose OK

To Alter the CurrencyUsing the Ribbon:


2. Select the required Currency from within the Currency drop down

92


Formatting Cells to Numbers as Percentages (4.5.1.3) To Alter the Format to a Percentage

Using the Ribbon:





4. Within the Number tab, select Percentage from the category listing


93


To Alter the Format to a PercentageUsing the Mouse:




4. Choose the Percentage category


6. Choose OK

To Alter the Format to a PercentageUsing the Ribbon:


2. Click the Percentage button within the Number Group

94

Percentage Button


NOTES

95


96


Chapter 11

Formatting: Contents

Topics


Formatting Font Sizes & Types (4.5.2.1) Applying Bold, Italic, Underline & Double Underline (4.5.2.2) Applying Colours to Cell Content (4.5.2.3) Applying Cell Backgrounds (4.5.2.3) Using the Format Painter (4.5.2.4)

97


Formatting Font Sizes & Types (4.5.2.1)Formatting the cell content in a spreadsheet can be done using 3 methods, using either the Mini Toolbar, Formatting group or Format Cells dialogue box.

To Format the Font SizeUsing the Ribbon:

1. Select the required cell content

2. Select the required font size using the font size drop down box or shrink/increase font buttons

To Format the Font SizeUsing the Mini Toolbar:


2. Move to the top right of the cell

The Mini Toolbar will appear

3. Select the required font size using the font size drop down box or shrink/increase font buttons

To Format the Font Size Using the Formatting Dialogue BoxUsing the Ribbon:


2. On the View tab, within the Font group, click the Dialogue Box Launcher

98


The Formatting Dialogue Box should now be displayed

3. Select the required font size using the font size drop down box

4. Click OK

To Format the Font TypeUsing the Ribbon:


2. Select the required font type using the font type drop down box

99


To Format the Font TypeUsing the Mini Toolbar:





To Format the Font Type Using the Formatting Dialogue BoxUsing the Ribbon:





4. Click OK

100


Applying Bold, Italic, Underline & Double Underline (4.5.2.2)Formatting the cell content in a spreadsheet can be done using 3 methods, using either the Mini Toolbar, Formatting group or Format Cells dialogue box.

To Apply Bold, Italic, Underline, Double UnderlineUsing the Ribbon:


2. Select the required formatting using the appropriate command buttons

Or

3. Use the Underline drop down to select double underline

To Apply Bold, ItalicUsing the Mini Toolbar:




3. Select the required formatting using the appropriate command buttons

To Apply Bold, Italic, UnderlineUsing the Ribbon:



101



3. Select the required formatting from the font style drop down box and Underline box

4. Click OK

102


Applying Colours to Cell Content (4.5.2.3)Formatting the cell content in a spreadsheet can be done using 3 methods, using either the Mini Toolbar, Formatting group or Format Cells dialogue box.

To Apply Colours to Cell ContentUsing the Ribbon:


2. Click the font colour drop down

The font colour palet will appear

3. Click on the required colour

To Apply Colours to Cell ContentUsing the Mini Toolbar:




3. Select Click the font colour drop down

The font colour palet will appear


103


To Apply Colours to Cell ContentUsing the Ribbon:




3. Select Click the font colour drop down

4. Click on the required colour Click OK

104


Applying Cell Backgrounds (4.5.2.3)Formatting the cell background in a spreadsheet can be done using 3 methods, using either the Mini Toolbar, Formatting group or Format Cells dialogue box.

To Apply a Cell BackgroundUsing the Ribbon:

1. Select the required cell/s

2. Click the font colour drop down

The background colour palet will appear


To Apply a Cell BackgroundUsing the Mini Toolbar:




3. Select Click the background colour drop down

The background colour palet will appear


105


To Apply a Cell BackgroundUsing the Ribbon:




3. Click the Fill Tab

4. Select Click the background colour required

5. Click OK

106


Using Format Painter (4.5.2.4)The Format Painter is a feature that allows a user to copy formats from one section of your spreadsheet and transfer it to another. The Format Painter copies the formats from the current selection and paints them onto the next selection

To Use the Format PainterUsing the Ribbon:

1. Select the required cell/s with the formats to copy

2. On the Home tab, within the Clipboard group, click

Your mouse pointer should now have changed

3. Highlight the text to be formatted

The formats should now have been copied

To Use the Format PainterUsing the Mini Toolbar:

1. Select the required cell/s with the formats to copy



3. Click

Your mouse pointer should now have changed

4. Highlight the text to be formatted

The formats should now have been copied

107


NOTES

108


Chapter 12

Formatting: Alignment, Border Effects

Topics


Text Wrapping (4.5.3.1) Alignment (4.5.3.2) Cell Content Orientation (4.5.3.2) Merging Cells & Centering a Title (4.5.3.3) Adding Cell Borders (4.5.3.4)

109


Text Wrapping (4.5.3.1)To display multiple lines of text within a cell, the Wrap Text feature can be used. The number of wrapped lines depends upon the width of the column and the length of the cell contents.

To Display Multiple Lines of Text within a CellUsing the Ribbon:


2. Click the Wrap Text Button on the ribbon, located on the home tab within the Alignment Group

OR




3. Click the Alignment Tab

4. Tick Wrap Text

5. Click OK

110


Alignment (4.5.3.2)When entering data into a cell, Excel horizontally and vertically aligns it according to the default alignment. Horizontal alignment defaults to General which right aligns numbers, formulae and dates and left aligns text. Excel also automatically aligns data vertically on the bottom of a cell.

To Change the Horizontal AlignmentUsing the Ribbon:




4. Choose Format Cells

Or

Click the Right Mouse button on the selected area, choose Format Cells

5. Choose the Alignment tab

6. In the Horizontal box, click

A list of available alignment displays.

Alignment Description

Left(Indent) Aligns contents at the left edge of the cell.

Centre Centres the contents in the cell.

Right Aligns the contents at the right edge of the cell.

Fill Repeats the characters in the left-most cell in the selection across the selected range. All cells to be filled in the selected range must be empty.

Justify Breaks the cell contents into multiple lines within the cell and adjusts the spacing between words so that all lines are as wide as the cell.

Center across selection

Centres the contents of the left-most cell in a selection in the selected range. All other cells in the selected range must be empty. The cells are not merged. The data remains in the left-most cell although it may appear otherwise.

7. Choose the alignment required

8. Choose OK

111


Using the Mouse:



3. In the Alignment Group, click the required alignment button

To Change the Vertical AlignmentUsing the Ribbon:





Or



6. In the Vertical box, click

7. A list of available alignment displays

8. Choose the alignment required, e.g. Centre

9. Choose OK

112

Align Left Align Right

Align Centre


Cell Content Orientation (4.5.3.2)Text can be rotated to any angle in a cell. Rotated text can reduce the amount of horizontal space required for long text items, e.g. headings, leaving more room for the data.

To Rotate Text to 45 or 90 degreesUsing the Ribbon:


2. Click the Rotate Text button on the ribbon, located on the home tab within the Alignment Group

The rotation drop down menu will appear

3. Select the required rotation

To Rotate Text Using the Format Dialogue BoxUsing Ribbon:




113



4. Selet the required degree of rotation

5. Click OK

To Rotate Text Using the Format Drop DownUsing Ribbon:

1. Select the cells to rotate text





6. In the Degrees box, type the degree required

OR

Click and Drag the indicator to the angle required

7. Choose OK

114


Merging Cells & Centering a Title (4.5.3.3)To align data that spans several columns or rows, such as column and row labels, the user can merge the range of cells and align the text within the resulting merged cell.

To Merge Cells & Centre a TitleUsing the Ribbon

1. Select the cells to merge


3. To merge cells and centre the contents, click , located within the alignment group.

To Merge CellsUsing the Ribbon:





Or



6. Choose Merge Cells

7. If required change the horizontal and vertical alignment

8. Choose OK

To Merge Cells using the Format dialogue boxUsing the Ribbon:



3. Within the Font group click the dialogue box launcher


5. Choose Merge Cells

6. If required change the horizontal and vertical alignment

7. Choose OK

The selected cells combine into a single cell. The cell reference for a merged cell is the upper left cell in the original selected range.

115


Adding Cell Borders (4.5.3.4)Borders can be used to highlight key information on a spreadsheet. Any combination of horizontal and vertical lines can be drawn.

To Add BordersUsing the Ribbon:





Or


5. Choose the Border Tab

6. Choose the line style, colour, border and position required

7. Choose OK

Note: To add borders to selected cells that contain rotated text, use the Outline and Inside buttons in the Presets box. The borders are added to the edges of the cells, which are rotated to the same

degree as the rotated text.

Using the Mouse:



3. Click on the within the Font group

4. Click the arrow to the right of

The Borders palette displays.

5. Choose the border required

6. Once the borders have been chosen, the Borders button retains the last choice made.

116


To Add Borders using the Format dialogue boxUsing the Ribbon:



3. Within the Font group click the dialogue box launcher

4. Choose the Border Tab

5. Choose the line style, colour, border and position required

6. Choose OK

117


NOTES

118


Chapter 13

Charts: Create

Topics


Creating Charts (4.6.1.1) Selecting a Chart (4.6.1.2) Changing the Chart Type (4.6.1.3) Move, Resize, Delete a Chart (4.6.1.4)

119


Creating Charts (4.6.1.1)Charts allow data to be made visual. This makes it easier for users to see comparisons, patterns and trends within data. Rather than having to analyse columns of numbers within a worksheet, a user can see at a glance what data means – for example whether temperatures are falling or rising over annual periods.

To Create a ChartUsing the Ribbon:

1. Select the data to be displayed on the Chart

2. Click on the Insert Tab

3. Within the Chart Group, click on the drop down for the chart type you require

OR

Click within the Chart Group

4. Click on the Chart you require

The chart you have chosen will now appear on the screen

Note: When a Chart is inserted and subsequently selected, the Ribbon will display 3 new chart tools tabs - Design, Layout and Format. These new tabs can be used to change the look and location of your chart.

120


Selecting a Chart (4.6.1.2) To Select a Chart

Using the Mouse:

1. Click on the Chart

Once selected a border will appear around the chart

121


Changing the Chart Type (4.6.1.3)When formatting a Chart the user can change the Chart style, attach features such as a legend, titles, arrows, and change the appearance of any part of the graph. The Chart Tools tabs and Shortcut menu can be used to format a chart.

To Change the Chart TypeUsing the Ribbon:

1. Select the Chart

2. Click on , located within the Design tab in the Type group

A Change chart type dialogue box displays.

3. Choose the Chart type required

4. Click OK

Using the Mouse:

1. Right Click on the Chart

2. Choose Change Chart Type

3. Choose the Chart type required

4. Click OK

122


Move, Resize, Delete a Chart (4.6.1.4)A Chart may be moved to a different location on the spreadsheet, resized or if no longer required deleted.

To Move a ChartUsing the Mouse:

1. Position the Mouse in the Chart Area

2. Click and Drag to new location

The mouse pointer changes to a four-headed arrow

3. To align the chart with the spreadsheet gridlines press [Alt] whilst dragging

To Move a Chart to a New SheetUsing the Mouse:

1. Select the Chart

2. Click the Design Tab

3. Click Move Chart, located in the location group

4. Select the location to move the chart to

5. Click OK

To Size a ChartUsing the Mouse:

1. Click in the Chart Area to select the chart

2. Position the Mouse on one of the selection handles

The Mouse pointer changes to a double arrow

3. Click and Drag to resize

4. To keep the Chart in proportion press [Shift] whilst dragging

To Delete an Embedded ChartUsing the Keyboard:

1. Select the Chart

2. Press [Delete]

123


NOTES

124


Chapter 14

Charts: Edit

Topics


Identifying & Selecting Chart Items Add, Remove, Edit a Chart Title (4.6.2.1) Adding Data Lables to a Chart (4.6.2.2) Changing the Chart Area Background Colour (4.6.2.3) Adding/Removing a Legend Changing the Legend Fill Colour (4.6.2.3) Changing the Colour of Chart Elements (4.6.2.4) Changing Font Size & Colour (4.6.2.5)

125


Identifying & Selecting Chart Items Your chart consists of many elements which can be selected for modification. These elements are very close together so selecting the right one can be difficult. To ensure the right element is select, there are ways of identifying them.

To Identify a Chart ItemUsing the Mouse:

1. Position the Mouse pointer on the required chart item

A Chart tip will display the item’s name, e.g. Chart Area

To Select a Chart ItemUsing the Mouse:

1. Click on the chart item required

Or

In the Layout Tab, click the arrow to the right of

A list of chart items displays

2. Select the item required

3. When an item is selected, selection handles display around the edges of the item

126

Chart Tip


Add, Remove, Edit a Chart Title (4.6.2.1)Additional information can be added to the chart such as titles.

To Add TitlesUsing the Ribbon:

1. Choose The Layout tab

2. Click the Chart Title command, located within the labels group

3. Choose the Position of you chart title

4. Type the title as required, then press [return]

To Edit TitlesUsing the Ribbon:

1. Select the Chart Title

2. Click the Chart Title

The chart Title will then be in edit mode and you will see the insertion point

3. Amend the Chart Title as required

4. Click away from the Chart Title

To Remove TextUsing the Keyboard:

1. Select the text box

2. Press [Delete]

Note: If the wrong text is deleted, the Undo feature can be used to restore the text.

127


Adding Data Labels to a Chart (4.6.2.2)Data Labels can be used to label the elements of the chart with their actual data values.

To Add Data LabelsUsing the Ribbon:

1. Select the Chart


3. Click the Data Labels command, located within the labels group

4. Choose the Data Label option required

The Data Labels will now appear in the appropriate place within the chart area.

128


Changing the Chart Area Background Colour (4.6.2.3)

To Change the Chart Area Background ColourUsing the Ribbon:

1. Select the Chart

2. Choose The Layout tab, within the Chart Tools

3. Click Format Selection, located within the current selection group

4. Click Solid fill

5. Select the Colour and Transparency required

6. Click Close

The Chart Area Background colour will now have changed

129


Adding/Removing a LegendA Legend is the key to the chart. The text to display as legend text must be selected in the original range during chart creation in order for the legend to be created automatically.

To Add a LegendUsing the Ribbon:

1. Select the Chart


3. Click the Legend command, located within the labels group

4. Choose the Legend option required

The legend will now appear in the appropriate place within the chart area. The position of the Legend can be moved by clicking and dragging it

130


To Remove a LegendUsing the Ribbon:

1. Select the chart


3. Click the Legend command, located within the labels group

4. Choose None

Using the Keyboard:

1. Select legend

2. Press [Delete]

131


Changing the Legend Fill Colour (4.6.2.3)

To Change the Chart Area Background ColourUsing the Ribbon:

1. Select the Legend

2. Choose The Layout tab, within the Chart Tools

3. Click Format Selection, located within the current selection group

7. Click Fill

8. Select the Colour and Transparency required

9. Click Close

The Legend Background colour will now have changed

132


Changing the Colour of Chart Elements (4.6.2.4)Once all the information has been added to the Chart, the appearance of the Chart may be customised in the same way as formatting cells on the spreadsheet.

Note: In order to modify an item it must first be selected

To Format a Chart ItemUsing the Ribbon:

1. Select the Chart item (i.e. column, pie slice etc)

2. Choose the Format tab

3. Click the Shape Fill drop down, located within the Shape Styles group

4. Select the colour required

The Chart items colour should now have changed

Using the Ribbon:



3. Click , located in the current selection group

4. Click Fill

5. Click Solid Fill

6. Select the colour and transparency required

7. Click Close

Using the Mouse:


2. Right Click on the chart item

3. Click Format Data Series

4. Click Fill

5. Click Solid Fill

133



7. Click Close

Changing Font Size and Colour (4.6.2.5)Once all the information has been added to the Chart, the appearance of the Chart may be customised in the same way as formatting cells on the spreadsheet.

Note: In order to modify an item it must first be selected

To Format Font SizeUsing the Ribbon:

1. Select the Chart text (i.e. title, axes or legend)

2. Choose the Home tab

3. Select the text size from within the Font group

The text size should now change

To Format Text ColourUsing the Ribbon:



3. Click the Text Fill drop down, located within the Wordart Styles group

4. Select the colour required

The Chart text colour should now have changed

134


Using the Ribbon:



3. Click , located in the current selection group

4. Click Solid Fill


6. Click Close

Using the Mouse:


2. Right Click on the chart item

3. Click Format Data Series

4. Click Solid Fill


6. Click Close

135


NOTES

136


Chapter 15

Prepare Outputs: Setup

Topics


Changing Margins (4.7.1.1) Changing Worksheet Orientation (4.7.1.2) Changing Paper Size (4.7.1.2) Page Setup Fitting Worksheet Contents (4.7.1.3) Headers & Footers (4.7.1.5)

137


Changing Margins (4.7.1.1) To Change Worksheet Margins to a pre-set size

Using the Ribbon:

1. Click the Page Layout tab

2. Click Margins, located in the Page setup group

3. Select the preset margin size required

To Change Worksheet Margins to a Specific SizeUsing the Ribbon:


2. Click Margins, located in the Page setup group

3. Click Custom Margins

138


The Page Setup Dialogue Box will now appear

4. Type in the required margins or adjust them using the arrows

5. Click OK

139


Changing Worksheet Orientation (4.7.1.2)Changing the worksheet orientation will allow a user to switch between portrait and landscape layouts.

To Change Worksheet OrientationUsing the Ribbon:


2. Click Orientation, located in the Page setup group

3. Select the layout required

Changing Paper Size (4.7.1.2) To Change Paper Size

Using the Ribbon:


2. Click Size, located in the Page setup group

3. Select the required size

140


Page SetupPage Setup controls the appearance of printed sheets. It allows the user to change margins and the orientation of the page.

Options Available Description

Page Tab Allows the page orientation to change from landscape to portrait and vice versa. Scaling options are available allowing the spreadsheet to be enlarged or reduced to fit the page.

Margins Tab Allows the margins to be set and the spreadsheet to be centred on a page vertically and/or horizontally.

Header/Footer Tab Allows the user to specify the content of the header and footer and its position on the page.

Sheet Tab Allows gridlines, row numbers and column letters to be printed.

To Change the Page SetupUsing the Ribbon:


2. Within the Page Setup tab Choose the required options

Or

Click to see the Page Setup dialogue box

141


Fitting Worksheet Contents (4.7.1.3)

To Fit the Spreadsheet on a PageUsing the Ribbon:


2. Click to see the Page Setup dialogue box

3. Choose the Page tab

4. Choose Fit to

5. Enter the pages wide and tall information

6. Click OK

142


Headers & Footers (4.7.1.4 & 4.7.1.5) To Add Custom Headers and Footers using the Ribbon

Using the Ribbon:

1. Click the Insert tab

2. Choose the Header & Footer button

The Design tab will automatically appear

3. Click into the relevant header or footer

4. Select options required from the Header & Footer Elements group.

OR

Type into the relevant space on the Header/Footer

The Header is split into 3 sections (left, centre, right, these can be accessed by click into them on the page.

To Add Pre-defined Headers and Footers using the RibbonUsing the Ribbon:

1. Click the Insert tab

2. Choose the Header & Footer button

The Design tab will automatically appear

3. Click into the relevant header or footer

4. Click the Header or Footer drop down.

5. Select the Header/Footer required

Note: The Header is split into 3 sections (left, centre, right, these can be accessed by click into them on the page.

143


To Add Custom Headers using Page setup Using the Ribbon:



3. Choose the Header/Footer tab

4. Choose or

5. Type header/footer required

6. Choose OK

To Add Pre-defined Headers and FootersUsing the Ribbon:



3. Choose the Header/Footer tab

4. In the Header box, click

A list of available headers displays.

5. Select the header required

6. In the Footer box, click

A list of available footers displays.

7. Select the Footer required

8. Choose OK

144

File Name

Font Formats

Page Number

Total number of pages

Current Date

Current Time

Sheet Name

File Name & Path

Insert Image

Edit Image


To Edit Headers and FootersUsing the Ribbon:

1. Click into the Header/Footer

Header & Footer Tools Design tab will appear

2. Edit the Header/footer as required

3. Click back onto the worksheet

To Delete Text from Headers and FootersUsing the Ribbon:

1. Click into the Header/Footer

Header & Footer Tools Design tab will appear

2. Select the text to be deleted

3. Press the [Delete] key

4. Click back onto the worksheet

145


NOTES

146


NOTES

147


Chapter 16

Prepare Outputs: Check & Print

Topics


Checking Spreadsheets (4.7.2.1) Displaying/Hiding Gridlines (4.7.2.2) Displaying/Hiding Row & Column Headings for Printing (4.7.2.2) Applying Automatic Title Row Printing (4.7.2.3) Print Preview (4.7.2.4) Printing (4.7.2.5)

148


Checking Spreadsheets (4.7.2.1)Spreadsheets should always be checked as an error in a formula can render the whole spreadsheet useless. Users should always check for error messages. It is also important to check for any spelling mistakes, this can be done using the Spell Check Tool.

To Use the Spell Check ToolUsing the Ribbon:

1. Click the Review tab

2. Click the Spelling command, located in the proofing group

The spell checker should now run

3. The offending word will be highlighted in red in the top window

4. Choose a suggestion from the bottom window and choose Change

5. If the word does not need correcting choose Ignore

6. Continue until you reach the end of the document

Options Description

Ignore Leaves the highlighted error unchanged and finds the next error.

Ignore All Leaves all instances of the highlighted error unchanged throughout the document.

Add Adds the highlighted word to the Custom Dictionary.Change Changes the word shown in the Not in Dictionary box to the selected

word shown in the Suggestions box. When the selected error is a duplicate word, the Change button is replaced with a Delete button.

Change All Changes all instances of the highlighted word.AutoCorrect Adds the spelling error and its correction to the AutoCorrect list.Check grammar

When selected, checks the grammar of the document. Unchecked will not check the grammar.

Options Specifies the rules Word uses to check the spelling and grammar of the document.

Undo Reverses the most recent action made during the spelling and grammar.

Close Closes the Spelling and Grammar dialogue box.

149


Displaying/Hiding Gridlines (4.7.2.2)Viewing gridlines between rows and columns in a sheet can make it easier to complete and edit an excel worksheet. These gridline can also be set to print.

To Display/Hide GridlinesUsing the Ribbon:


2. Within the Sheet options tab, add or remove the view tick as required

To Print GridlinesUsing the Ribbon:


2. Within the Sheet options tab, add or remove the print tick as required

150


Display/Hiding Row & Column Headings for Printing (4.7.2.2)Row headings are the numbers at the side of the spreadsheet. Column heading are the letters that appear above the spreadsheet. These will not automatically print, however they can be set to print where necessary.

To Display/Hide Row & Column HeadingsUsing the Ribbon:


2. Within the Sheet options tab, add or remove the view tick as required

To Print Row & Column HeadingsUsing the Ribbon:


2. Within the Sheet options tab, add or remove the print tick as required

151


Applying Automatic Title Row Printing (4.7.2.3)Automatic Title Rows can be set up so that they print on every page of a printed worksheet.

To Apply Automatic Row PrintingUsing the Ribbon:


2. Click Print Titles, located within the Page Setup group

The Page Setup dialogue box will appear

3. Click the appropriate

The rows or columns to repeat selection box will appear

4. Select the required cells to be repeated

5. Click

The selected cell references will now show within the print titles

6. Click OK

152


Print Preview (4.7.2.4) To Print Preview a Worksheet

Using the Ribbon:

1. Click the Office Button

2. Hover the mouse over the print arrow

3. Select Print Preview

153


Printing (4.7.2.4)Excel can print the entire contents of the worksheet, workbook or just a selected area.

To Print the Workbook/sheetUsing the Office Button:


2. Click Print

3. The Print Dialogue Box appears

4. Choose what to print, e.g. Active sheet(s)

5. Choose the number of copies required, e.g. 3

6. Choose the page range, e.g. Page(s) from 1 to 3

7. Choose OK

Note: When using the Print icon the workbook will be printed automatically with the default printing options

154


Setting Print AreasUsers can specify a print range, known as a Print Area. Print areas can be set using Normal view and Page Break Preview.

To Set a Print Area in Normal ViewUsing the Ribbon:

1. Select the required cells


3. Click located in the Page Setup tab

4. Select Set Print Area

A dotted outline displays, indicating the print area.

Note: If multiple areas are set (using the [Ctrl] key), Excel 2003 prints the first selected area on the first page, the second area on the second page and so on.

To Reset the Print Area in Normal ViewUsing the Ribbon:


2. Click the Print Area button located in the Page Setup tab

3. Select Clear Print Area

155


NOTES

156


Chapter 17

More Information

Topics


More Information Keyboard Shortcuts

157


More InformationWe hope that you have found the course helpful and this training documentation easy to use. If you require more information about other courses we run, please visit our IT Training Website at:

http://www.uwe.ac.uk/ldc

Visit our web pages to find:

A comprehensive list of all IT courses currently available with information about course contents, duration and course tutor.

An up-to-date timetable of when our courses are running and availability.

Online Resources including links to all our training documentation, FAQ’s and training videos.

Contact Information

If you want to ask the IT Training Team a question about any aspect of training, please contact us at [email protected] or phone us on ext: 81202

158

http://www.uwe.ac.uk/ldc

Excel Keyboard ShortcutsWorksheet Navigation Worksheet Formatting

Description Shortcut keys Description Shortcut Keys

Left one cell [] Bold [Ctrl] + [B]

Right one cell [] Italic [Ctrl] + [I]

Down one cell [] Underline [Ctrl] + [U]

Up one cell [] Currency number formats [Ctrl] + [Shift] + [$]

First cell in row [Home] Percentage formats [Ctrl] + [Shift] + [%]

Return to A1 [Ctrl] + [Home] General number format [Ctrl] + [Shift] + [~]

The last occupied cell [Ctrl] + [End] Date format [Ctrl] + [#]

Up one screen [page up] Time format [Ctrl] + [Shift] + [@]

Down one screen [page down] Comma format [Ctrl] +[Shift] + [!]

Left one screen [Alt] + [Page up] Add outline border [Ctrl] + [Shift] + [&]

[Right one screen [Alt] + [Page down] Remove all borders [Ctrl] + [Shift] + [-]

Go To specific cell [F5] Format cells dialogue box [Ctrl] + [1]

Move to the left worksheet [Ctrl] + [Page up] Spell check [F7]

Move to the right worksheet [Ctrl] + [Page down]

Entering and Editing Data Selecting Ranges

Description Shortcut keys Description Shortcut KeySelect a range [Shift] + [Arrow keys] Clears contents of cells [Delete]

Select to edge of data [Ctrl] + [Shift] + [Arrows] Clears contents of active cell [Backspace]

Select to beginning of row [Shift] + [Home] Edit mode [F2]

Select to beginning of worksheet [Ctrl] + [Shift] + [Home] Confirm data entry & move to cell move [Return]

Select to last occupied cell [Ctrl] + [Shift] + [End] Confirm data entry & move to cell above [Shift] + [Return]

Select current row [Shift] + [Spacebar] Move to next cell to the right [Tab]

Select current column [Ctrl] + [Spacebar] Move to previous cell to left [Shift] + [Tab]

Whole worksheet [Ctrl] + [A] Cancel data entry [Esc]

Collapse to active cell [Shift] + [Backspace] Insert date [Ctrl] + [;]

Edit cell comment [Shift] + [2] Insert time [Ctrl] + [Shift] + [;]

Undo last action [Ctrl] + [Z]

ecdl unit 4 manual

Documents

cell data

formatting cells

relative cell

cell backgrounds

absolute cell

cell range

cell content orientation

formatting cell numbers