1-excel 2007 ess

8/2/2019 1-excel 2007 ess

1/102

Happy Computers 20074 - 20/07/07 V1

Happy Computers Manual

for...

Excel 2007EssentialsThis course guide is produced for the Happy Computers Excel 2007 Essentials courseFor all your computer training needs contact:

Happy Computers, Cityside House, 40 Adler Street, London, E1 1EEHelp-line: 020 7375 7373 [email protected]

Bookings: 020 7375 7300

Copies of this guide can be obtained from Happy Computers, fully bound, at a cost of 15each, or 10 for extra copies for organisations who have booked courses.Happy Computers allows this guide to be copied, provided that permission is sought andthe name and phone number of Happy Computers remains on the copies.

8/2/2019 1-excel 2007 ess

2/102

Excel 2007 Essentials-Contents

Page 2 Happy Computers 020 7375 7300

Contents

Contents............................................................................. 2Happy Computers: For All Your IT Training Needs......... 4The Happy Computers Web Site....................................... 5Learn more online with Happy eLearning........................ 6Whats New in Office 2007? .............................................. 8Smart Tags How can they help?.................................. 11Help .................................................................................. 12Getting started in Excel................................................... 13

Starting and closing Excel ................................................................14Excel 2007 screen............................................................ 16The Mouse Keeps Changing Shape ............................... 18Moving around Excel ...................................................... 19Text & Numbers............................................................... 20

Correcting your mistakes .................................................................20Undo and Redo - A licence to make mistakes.......................................21Changing the zoom control...............................................................22

Selecting parts of a spreadsheet.................................... 24Drag and Drop ...............................................................................25

Cut Copy and Paste......................................................... 27The Office Clipboard ........................................................................28Inserting and deleting columns and rows ............................................29

Changing the column widths.......................................... 31Autofill.............................................................................. 32Saving your workbook .................................................... 35

Saving a book for the first time.........................................................35Closing and Opening a Workbook ......................................................36

AutoSum .......................................................................... 38Using AutoSum...............................................................................38Formulae.......................................................................................39Creating a Formula .........................................................................40

Spreadsheet Design Principles ...................................... 42Building On The Basics .................................................. 43

Copying Formulae...........................................................................43

What Are Absolute Cell References?............................. 44Creating and Using Absolute Cell References .......................................45

Printing ............................................................................ 47

Functions ......................................................................... 50Inserting basic functions ..................................................................50

8/2/2019 1-excel 2007 ess

3/102

Excel 2007 Essentials-Contents

Happy Computers 020 7375 7300 Page 3

Using percentages .......................................................................... 51Freeze Panes ................................................................................. 53

Making a Spreadsheet Look Good ................................. 55Borders and Shading ...................................................................... 55Basic Formatting ............................................................................ 56

Using Cell Styles ............................................................................ 57Alignment ..................................................................................... 58Changing the number formats .......................................................... 60Changing the date format................................................................ 63Changing the column widths ............................................................ 63Adding Borders .............................................................................. 65Adding shading .............................................................................. 66Format painter............................................................................... 67Print Preview ................................................................................. 68Changing the page set-up................................................................ 70Headers and Footers....................................................................... 74

Charts............................................................................... 78Charts .......................................................................................... 78Moving, re-sizing and deleting charts................................................. 81Selecting parts of a chart................................................................. 81Formatting the chart using the Design tab.......................................... 84Printing the chart ........................................................................... 87Which chart shall I use? .................................................................. 88

Range Names................................................................... 91Creating & using Range Names......................................................... 91

Protection ........................................................................ 96Protecting Your Sheet ..................................................................... 96

Appendix........................................................................ 100What do the error messages mean? ................................................ 100

Index............................................................................... 101

8/2/2019 1-excel 2007 ess

4/102

Excel 2007 Essentials-Happy Computers: For All Your IT Training Needs


Happy Computers: For All Your

IT Training Needs

Manuals for You to Use

We hope you enjoy this manual and find it useful. If it is for personal use, orfor use in state funded schools, please feel free to copy it. Our full range ofmanuals, in MS Office and more, are available from:

www.happymanuals.com

For other organisations (including companies, charities, universities, colleges

and training providers) there is a licence fee. Full details of the costs are onthe site.

Award Winning Classroom Training in the UK

These manuals are produced by Happy Computers, recognised as one of theleading providers of quality training in the UK. Our awards include:

IT Training Company of the Year (Institute of IT Training Awards)2000: Finalist 2001: Gold 2002: Bronze 2003: Bronze2004: Bronze 2005: Finalist 2006: Silver 2007: Silver

IT Trainer of the Year (Institute of IT Training Awards)1999: Gold 2000: Silver 2004: Gold 2005: Silver2007: Bronze

Best Customer Service in the UK

(Management Today/Unisys Service Excellence Awards)2002 Section Winner (Business to Business)2003 Overall UK Winner

All our training is designed to fully involve you, based on the age-oldprinciple:

Tell Me & I Will Forget

Show Me & I May RememberInvolve Me & I Will Understand

Find out how we can make learning IT fun and effective:

020 7375 7300, [email protected], www.happy.co.uk

E-Learning: Anywhere in the World

See the section below on Happy eLearning or simply try our free trial:

http://www.happyelearning.co.uk/freestuff/

8/2/2019 1-excel 2007 ess

5/102

Excel 2007 Essentials-The Happy Computers Web Site


The Happy Computers Web Site

Where is it?http://www.happy.co.uk

Why should I visit it?

The Happy Computers web site is dedicated to providing you withinformation about both the software you use, and the courses we run. Youllfind copies of manuals to download and tips on the programs you use,designed to make your work quicker and easier. Youll find up-to-date newsabout Happy Computers and the team, and you can of course find

information on all our courses and book your place on one.

Feedback

If you have any comments, ideas or just fulsome praise, you can e-mail ourweb editor at: [email protected]

8/2/2019 1-excel 2007 ess

6/102

Excel 2007 Essentials-Learn more online with Happy eLearning


Learn more online with Happy

eLearning

We hope you enjoy the classroom course with Happy. But your learning doesntneed to end thereWhy dont you try Happy eLearning all of Happy Computers expertise intraining, online!

We offer courses in ECDL, ITQ and all Microsoft packages, and we caneven create custom made courses for your company

All courses let you work on the live software while you learn Courses are divided into small lessons, that take 5, 10 or 15 minutes to

do

Learn at your own pace learn what you like, when you like Easy to understand lessons, with hints and answers, and full manualprovided

Complete a pre-course assessment to gauge your level of knowledgebefore you start. Then learn only what you need to!

Quizzes and assignments test your progress Full support package available your own etutor, forums, chat room, email

support, and one year free helpline (the same as our classroom helpline) Monitor your own students progress through our courses, with our

bespoke learning management system

http://www.happyelearning.co.uk:

Try our courses for Free!

You can now try out our online courses for free just register with us athttp://www.happyelearning.co.uk/freestuff/ (it only takes a few minutes). Orpass the link onto your training manager At the moment, we have availablesample versions of ECDL and of the Office Sense health and safety course.If you are interested in any the courses we have available, give us a ring now on0207 375 7300.

8/2/2019 1-excel 2007 ess

7/102

Excel 2007 Essentials-Learn more online with Happy eLearning


Changing the way it works

To customize the Office Button menu to have more functions, click onthe Options button at the bottom of menu.

8/2/2019 1-excel 2007 ess

8/102

Excel 2007 Essentials-Whats New in Office 2007?


Whats New in Office 2007?

The RibbonMicrosoft has now done away with its familiar menus and toolbars from theprevious versions. Office 2007 works with something known as the

Ribbon, which appears at the top of the screen and is divided into a seriesof Tabs, which group related features together.

Contextual Tabs

Some Tabs are hidden by default and appear only when required e.g.commands for inserting, selecting and editing a chart, table or picture onlyappear when applicable to the work you are doing. As soon as youre donethey disappear!

Microsoft Office Button

Now instead of the File menu we have the Office Button which providesaccess to the common tasks carried out in all of the Office applications.

Is it the same in all Office packages?

No - each application will have different tabs with different functionsspecific to that application e.g. Excel has a tab for working with charts,where Word does not.

Hiding the Ribbon

To temporarily hide the Ribbon, double-click the selected tab or press

Ctrl + F1

Contextual Tabs for Objects

To quickly bring forward the Contextual Tabs for an object, double-clickthe object.

8/2/2019 1-excel 2007 ess

9/102



1. Click on the Office Button2. Click on the menu option you need

Dialog Box Launchers

Some groups in the Ribbon (such as Clipboard, Font, and Paragraph) containa Dialog Box Launcher icon. Click this icon to launch the dialog box relatedto the features contained in the group.

Quick Access Toolbar

The Quick Access Toolbar, which sits above the Ribbon, holds commonlyused options, such as save, undo/redo and print. To add a feature to theQuick Access Toolbar, right-click on it and choose Add to Quick AccessToolbar.

Changing the way it worksTo customize the Office Button menu to have more functions, click on

the Options button at the bottom of menu.

Adding extras to the QATAdd an entire group of features to the QAT by right-clicking the title at

the bottom of the group

8/2/2019 1-excel 2007 ess

10/102



Super-Tooltips

Super-Tooltips replace screen tips and provide a more detailed descriptionof what most buttons do. They can display formatted text as well as images.

Office Assistants have beencompletely removed!

Zoom Slider

Youll find the Zoom Slider in the bottom-right corner of the screen. It

allows a user to rapidly change the magnification of a document,spreadsheet, presentation etc within an Office application.

Mini Toolbar

Whenever you select some text, the Mini Toolbar pops-up on screen. Itprovides easy access to the most commonly used formatting options. Youcan also find it when you right-click on a selection of words.

Live Preview

If you want to preview a change before you actually do it, Live Preview willhelp. All you do is rest your cursor over an item in the Ribbon. As soon asyou move your mouse pointer away, the preview closes.

8/2/2019 1-excel 2007 ess

11/102

Excel 2007 Essentials-Smart Tags How can they help?


Smart Tags How can they

help?

Smart Tags were a new feature in Office 2002 (XP)/2003 and they are stillpresent in all of the Office 2007 products (Word, Excel, Access andPowerPoint).

Smart Tags are buttons that pop up and offer you help according to whatyou are doing.

Smart Tag What is it for?

This Smart Tag appears to give youinformation. It will appear when you type in aname and will prompt you to add it to yourOutlook Contact List.

When you paste in something you havecopied this Smart Tag will appear. Hover yourmouse over it and it will give you options forthe formatting you have selected.

When clicking on the cell with the green

triangle in the corner the Smart Tag on theleft appears. This is an error checking SmartTag and will give you options to check yourformulae.

An AutoFill Smart Tag appears when you useAutoFill and will give you various options (seepage 32).

An AutoCorrect Smart Tag appears as a smallblue box and turns into the Smart Tag when

you hover the mouse over it. It allows you toundo an Autocorrection.

The Insert Options button appears when rowscolumns or cells are inserted. You are thenoffered various formatting options.

8/2/2019 1-excel 2007 ess

12/102

Excel 2007 Essentials-Help


Help

Excel 2007 comes with a comprehensive Help feature if you get stuck.

You can either type what you want to find out about or browse the ExcelHelp and How-to.

Click on the Help icon

8/2/2019 1-excel 2007 ess

13/102

Excel 2007 Essentials-Getting started in Excel


Getting started in Excel

Getting familiar with the Excel 2007 screen

Excel is a spreadsheet program. Spreadsheets are basically large tables thathold number and text. Calculations can them be performed on thesenumbers.

Uses of Excel

Formulas or calculations

Storing information (database) Creating tables

For Formulas (calculations)

Here is a very simple domestic budget using Excel:-

For more information on formulas see page Error! Bookmark not defined.

This cell contains aformula to work outthe total outgoing.

This cell contains a formula whichtakes the total outgoing from the

income to give the surplus (or leftover).

8/2/2019 1-excel 2007 ess

14/102

Excel 2007 Essentials - Getting started in Excel


For storing information (as a database)

Excel has a simple database facility that allows you to Sort information into any order (e.g. by surname) Extract the information you wish to see (e.g. only the females)

For creating tables

Excel can also be used very much like Word processing tables, i.e. for layinginformation out neatly in rows and columns and making it look impressive.

Starting and closing Excel

Starting Excel

1. Click on the Start button2. Select All Programs3. Click on Microsoft Office

4. Double-click on ExcelOr

If you have a shortcut icon onthe desktop, double-click it

8/2/2019 1-excel 2007 ess

15/102

Excel 2007 Essentials - Getting started in Excel


Exiting Excel

Click on the cross in the topright corner in Excel

8/2/2019 1-excel 2007 ess

16/102

Excel 2007 Essentials - Excel 2007 screen


Excel 2007 screen

If you are familiar with previous versions of Microsoft Excel, you will noticethe screen looks very different...there are now no menus or toolbars. Theyhave been replaced with Tabs and Groups. For example, the Home tabcontains the Clipboard group and Font group. These groups containcommands, such as Paste, Bold and Font size. A command can be a buttonor icon or even a drop down menu.

Tabs - There are 7 tabs, each one represents a core tasks in Excel.

Groups Each tab contains a group of related items.

Commands These are buttons and can represent a box to enterinformation or even a menu.

A Tab

Groups

Commands

Ribbon

Office buttonQuick Access

Scroll bar

Zoom sliderSheet tabs

Rowheadings

Columnheadings Cells

Formulabar

8/2/2019 1-excel 2007 ess

17/102

Excel 2007 Essentials - Excel 2007 screen


Title bar Which program you are in and the name of the currentworkbook

Quick Accesstoolbar

This holds some of the common features, such as Undo andRedo

Office button This has replaced the File menu, so it contains options like

Save, open, Close and Print, as well as some more featuresRibbon Replaces Menus and Toolbars, contain tabs and commands

Formula bar Shows you which cell you are in, and what its contents are

Cells The boxes that make up the spreadsheet. Each cell has a cellreference, made up of its column letter and row number.E.g. A1

Sheet tabs When you first create a new workbook, it has three sheetsinside it. The sheet tabs show you which sheet you arecurrently on.

Sheetnavigation

buttons

If you add more sheets to your workbook, these buttonsallow you to move through them (See Happy Computers

Guide to Excel 2000/97 Intermediate)

8/2/2019 1-excel 2007 ess

18/102

Excel 2007 Essentials - The Mouse Keeps Changing Shape


The Mouse Keeps Changing

Shape

Mouse shapes & Positions Icon Used

Big Plus sign:

Position your mouse over themiddle of a cell.

When you are selectingcells.

Four Cross Arrow:

Position your mouse on theborder of a cell.

When you wish to movethe contents of a cell(see page 25).

Small Plus sign:Position your mouse over thebottom right hand corner of theactive cell.

When you are usingAutoFill (see page 32).

I-bar:Click into the Formula bar, or

double-click inside a cell.

When you are adding ordeleting text from a cell.

Cross-Arrow:

Position your mouse betweentwo column letters, or betweentwo row numbers.

When you are re-sizing arow or column (see PageError! Bookmark notdefined.).

Magnifying glass:

Position your mouse over thespreadsheet in print preview.

When you want to zoomin or out of the printpreview (see Page Error!Bookmark notdefined.).

Double-arrow:

Select a picture or drawn shapeand position the mouse aroundthe boxes.

When you are re-sizing apicture, chart or drawnshape (see Page Error!Bookmark notdefined.).

Egg-timer The mouse will change toan egg timer when Excelis busy. If you wait for amoment, it will

disappear.

8/2/2019 1-excel 2007 ess

19/102

Excel 2007 Essentials - Moving around Excel


Moving around Excel

As you have seen, there are no menus and toolbars now, but Tabs andGroups. You can access these using the keyboard.

Accessing the Tabs and Groups using the keyboard

1. Press ALT Key Tips appear at the top of theTabs

2. Press a letter to navigate tothe Tab

3. Press a letter to navigate to a

Group

Using the Keyboard

You may be familiar with the following shortcuts as you could use inprevious versions of Excel.

- Up one cell Down one cell Left a cell Right a cell

Ctrl Goes to the furthest right of the current spreadsheetCtrl Goes to the furthest left of the current spreadsheetHome Go to column ACtrl+Home Goes to cell A1Ctrl+End Moves to the bottom right cell of the area you have typedPage up Moves active cell up one screenPage down Moves active cell down one screen

What about the old keyboard shortcuts?

All the keyboard shortcuts that begin with Ctrl still all work, for

example Ctrl+X still moves something to the clipboard.

Using the Mouse

1. Click the mouse into themiddle of the cell you wish tomove to

You cursor will look like a plus sign

8/2/2019 1-excel 2007 ess

20/102

Excel 2007 Essentials - Text & Numbers


Text & Numbers

Entering Text1. Click on the cell2. Type the text3. Press enter

Entering Numbers

1. Click on the cell2. Type the number

3. Press enter

Entering Dates

1. Click on the cell2. Type the date For example, 26/01/20073. Press enter

Entering Percentages1. Click on the cell2. Type the number followed by

the % button on the keyboard3. Press enter

Correcting your mistakes

Deleting the contents of a cell

1. Click on the cell required2. Press Delete key

Replacing the contents of a cell

1. Click on the cell required2. Type the new contents The original contents will disappear

8/2/2019 1-excel 2007 ess

21/102



Editing the contents of a cell

Double-click on the cell requiredA cursor will appear inside the cellOr

1. Click on the cell required

2. Press F2 on the keyboard A cursor will appear inside thecell

Or1. Click on the cell required The formula bar will show the contents

of the cell2. Click on the entry line of

the formula bar

Undo and Redo - A licence to make mistakesUndo allows you to cancel up to the 100 of your previous actions if you havemade a mistake. If you then decide that you didnt mean to cancel thoseactions, you can redo up to 100 things that you have undone!

Undoing your last action

Click on the Undo button on the Quick Access ToolbarOr

Press CTRL & Z

Re-doing your last undo

Click on the Redo button on the Quick Access ToolbarOr

Press CTRL & Y

Undoing up to 100 actions

You cant select one action to undo

When you undo up to 100 actions, you cannot pick out just onefrom the list and undo that alone. For example if you the action

you want to undo was 5 actions ago, you must undo ALL of

your last 5 actions.

Click here to undo

your last action

Click here to undo

up to 100 actions

Click here to redo

your last undo

Click here toredo up to 100undos

8/2/2019 1-excel 2007 ess

22/102



1. Click on the down arrow next to undo2. Find the action(s) you want to undo, scrolling down if necessary3. Click the on the action you wish to undo from

Redoing up to 100 actions1. Click on the down arrow next to redo2. Find the action(s) you want to redo, scrolling down if necessary3. Click on the last action you wish to redo

Changing the zoom control

What is the zoom control?

This allows you to stand back from your spreadsheet, so that you can seemore of it, or zoom in closer. It does not change the size of the spreadsheetwhen it is printed.

Changing the zoom control using the Zoom Slider

The quickest way of changing the zoom of an Excel spreadsheet is to use theZoom Slider which is located in the bottom right corner of the screen. Youwill see an arrow and plus (+) and minus (-) signs to the left and right of it.

Click on the + or

buttons

The zoom will increase/decrease inincrements of 10%

Changing the zoom control using the Zoom dialog

box

Alternatively, you can still use the Zoom dialog box to change the Zoom.

1. Click on the % in the bottomright corner of the screen In this example the zoom has beenset to 100%

Zoom dialog box opens

8/2/2019 1-excel 2007 ess

23/102



2. Click on the Magnification yourequire

3. Click OK

Alternatively, you can use the View tab on the Ribbon to changethe zoom

8/2/2019 1-excel 2007 ess

24/102

Excel 2007 Essentials - Selecting parts of a spreadsheet


Selecting parts of a

spreadsheet

To select, your mouse must look like the big plus sign

Selecting a range of cells

1. Start from the cell at the topleft hand corner of the areayou wish to select

2. Make sure that your mouselooks like the big plus sign

3. Click and drag over the cellsyou require

They will go blue

Selecting columns

Click on the Column letter yourequire

OrClick and drag over thecolumn letters to selectseveral columns

Selecting rows

Click on the Row number yourequireOrClick and drag over the row

number to select several rows

Selecting the entire spreadsheet

Click on the pale blue square at the top left corner of the spreadsheet

Selecting the entire spreadsheet using the keyboardCtrl+A

Click here to select thewhole spreadsheet

8/2/2019 1-excel 2007 ess

25/102



Selecting areas which are not next to each other

1. Select the first area yourequire

2. Hold down the Ctrl key on thekeyboard

3. Select the second area yourequire

Using the keyboard to select cells

Shift Select cells to the rightShift Select cells to the leftShift Select cells aboveShift Select cells belowShift, Control Select from the current cell down to the last entry in the

columnShift, Control Select from the current cell up to the first entry in the

columnShift, Control Select from the current cell to the last entry in the rowShift, Control Select from the current cell to the first entry in the rowShift, Control, End Select from the current cell across and down to the last

typed entry on the sheetShift, Control,

Home

Select from the current cell up and across to cell A1

Drag and Drop

When you hover the mouse over the border of the cellwhose contents you wish to move your Mouse should

look like this cross arrow:

Moving a Selection

1. Select the cells you wish to move2. Position your mouse at the

border of the selection so thatit changes to a cross arrow(see the diagrams above)

3. Click and drag the selection to

its new location

You will see a fuzzy grey border

showing you where you are going

8/2/2019 1-excel 2007 ess

26/102



Copying a Selection

1. Select the cells you wish tocopy

2. Hold down Ctrl on the

keyboard3. Position the mouse at the

border of the selection sothat it changes to a crossarrow (see the diagramsabove)

4. Click and drag theselection to its newlocation

You will see a fuzzy grey line and a +sign showing you where you aregoing

5. Let go of the Ctrl key andthe mouse

8/2/2019 1-excel 2007 ess

27/102

Excel 2007 Essentials - Cut Copy and Paste


Cut Copy and Paste

Copying a selection using the keyboard and themouse

6. Select the cells you wish to copy7. Hold down Controlon the

keyboard8. Position the mouse at the border

of the selection so that it changeto a white arrow

9. Click and drag the selection its

new location

You will see a fuzzy grey line

showing you where you aregoing

10.Release control and the mouse tocopy

Moving a selection with cut

1. Select the cells you wish tomove

2. Click on the Cut icon

OrPress Ctrl-X

The selection will have flashing lightsaround it, and will be moved to thewindows clipboard

3. Place your cursor in anotherlocation

This cell will become the top left handcorner of the selection

4. Click on the Paste icon

OrPress CTRL-V

Copying a selection with copy

1. Select the cells you wish to copy2. Click on the Copy icon

OrPress CTRL-C

The selection will have flashing lightsaround it and will be copied to thewindows clipboard

3. Place your cursor in another

location

This cell will become the top left hand

corner of the copied selection4. Click on the Paste icon

8/2/2019 1-excel 2007 ess

28/102



OrPress CTRL-V

You can paste many times

Whenever you click Paste, Excel will reproduce whatever waslast copied or cut onto the clipboard, which means that you can

paste information in as often as you require.

The Office Clipboard

In Microsoft Office 2007 you can use the Office Clipboard. This allows you tocut, copy and paste up to 24 selections of text or pictures. This clipboard

allows you to select which item you wish to paste into your spreadsheet orwhich items you wish to clear from the clipboard.

Displaying the Office Clipboard Task Pane

Click on the dialog box launcher forthe Clipboard group

Task Pane opens

Inserting an item from the Office Clipboard

1. Display the Office Clipboard2. Click on the item you wish to

paste

To Delete an Item from the Office Clipboard

1. Display the Office Clipboard

2. Click on the dropdown arrownext to the item you want to

8/2/2019 1-excel 2007 ess

29/102



delete3. Click on Delete

You can paste many times

Whenever you click Paste, Excel will reproduce whatever waslast copied or cut onto the clipboard, which means that you can

paste information in as often as you require

Inserting and deleting columns and rows

Inserting a row

1. Right-click on the row belowyou require the new one

2. Click on Insert A new row will be inserted above therow you selected

Or1. Select the row below where

you require the new one2. Ctrl + A new row will be inserted above the

row you selected

If you select row 5 A new row is inserted above it

Inserting a column

1. Right-click on the column tothe right of where you requirethe new one

A new column will be inserted to theleft of the selection

2. Click on InsertOr

1. Select the column to the rightof where you require the newone

2. Ctrl + A new column will be inserted abovethe row you selected

8/2/2019 1-excel 2007 ess

30/102



If you select column B A new column is inserted to the left

Inserting several new rows or columns

e.g. Inserting six rows

1. Select six rows below whereyou require the new rows

2. Right-click within the selected

cells3. Click on Insert Six new rows will be inserted above

the rows you selectedOr

1. Select six new rows belowwhere you require the newrows

2. Ctrl +

Deleting rows and columns

1. Select the rows/columns youwish to delete

2. Right-click3. Select Delete

Or1. Select the rows/columns you

wish to delete2. Ctrl -

8/2/2019 1-excel 2007 ess

31/102

Excel 2007 Essentials - Changing the column widths


Changing the column widths

To re-size column widths, place your mouse between thecolumn letters and make sure it looks like the cross

arrow

Making a column or row just big enough

1. Place your mouse to the right of the column letter you wish to re-sizeorPlace your mouse below the row number you wish to re-size

2. Double-click

Adjusting a row or column manually

1. Place your mouse to the right of the column you wish to re-sizeor

Place your mouse below the row number you wish to re-size2. Click and drag to the size you require

8/2/2019 1-excel 2007 ess

32/102

Excel 2007 Essentials - Autofill


Autofill

What Is AutoFill?AutoFill is a great timesaving feature that allows you to copy text, numbersor Formulae in a spreadsheet.

Using AutoFill

Make sure your mouse looks like the small plus sign

Before you click and drag, make sure that your mouse lookscorrect, or you might get some unexpected results!

1. Create your first formula2. AutoFill this formula across or down to copy to other columns or

rows

3. Select the option you require from the smart tag options

Special Lists

Certain text such as months, days or dates work well with AutoFill. Have alook at the examples below, which were all created using AutoFill.

Position your mouse here toAutoFill, make sure it looks like athin black cross.

8/2/2019 1-excel 2007 ess

33/102



In the above examples, all you need to do is type the first in the sequence,then autofill down or across.

1. Type the first item in thesequence

2. Autofill down or across

Starting Off the Sequence for AutoFill

When using AutoFill for dates, for example, you might want the sequence togo from week to week rather than day to day. To achieve this, you muststart the sequence off for AutoFill.

1. Type the first item in thesequence

2. Type the second date into an

adjoining cell

3. Select both cells4. Autofill down or across

Creating a custom series

1. Click on the Office Button2. Click on Excel Options3. Click Popular4. Under the Top options for

working with Excel, click EditCustom Lists

Dialog box appears

5. Click in List entries6. Type each item in the series,

pressing enter after each one7. Click Add8. Click OK9. Click OK

8/2/2019 1-excel 2007 ess

34/102



Using a sequence from a custom series

1. Click into a blank cell2. Type the first item in the

series

3. Autofill down or across

8/2/2019 1-excel 2007 ess

35/102

Excel 2007 Essentials - Saving your workbook


Saving your workbook

The first time you save a workbook in Excel you have to specify a name forthe file, once you have done this you can click on the familiar Save icon toupdate the changes you have made.

Saving a book for the first time

1. Click on the Office button

2. Rest the mouse on the Save asoption

This gives you a chance to seethe other options

Excel WorkbookExcel Macro-enabledWorkbookExcel binary WorkbookExcel 97-2003 WorkbookFind add-ins for other fileformats

Other formats3. If you know you may have to shareyour Excel workbook with someonewho doesnt have Office 2007, thenit is best to go for the 4th optionExcel 97-2003 Workbook

Save as dialog box appears

8/2/2019 1-excel 2007 ess

36/102



4. On the left, navigate to where youwant to save the workbook

5. Type a name6. Click on Save

Changing the Default folder

Before you save do the followingfirst1. Click o the Office Button2. Click on the Excel Options

button3. Click on Save on the left side4. Click on the Default file

location box

5. Type in the drive and folder

you wish to save to6. Click OK

Saving your workbook after changes

Press Ctrl+S

Closing and Opening a Workbook

Opening Your Workbook

1. Click on the Office Button2. Click on Open3. If needed, change the folder

Excel is looking at4. Navigate to the Excel

spreadsheet you want to open5. Double-click the folder to open

it

I want to save my Excel workbook in a new folder, but I haventcreated it yet.

Thats ok, instead of doing point 4 from above

1. Click on the New Folder icon2. Type a name for the folder3. Press Enter4. Continue with steps above from point 5

8/2/2019 1-excel 2007 ess

37/102



Closing Your Workbook

Click on the bottom X at the top right of the screen

Creating a New Workbook1. Click on the Office Button2. Click on the New button Blank Workbook will already be

selected3. Click on the Create button

What are the other options?

New Installed TemplatesShows a range of preparedspreadsheets, such as Billing

Statement and ExpenseReport

New My TemplateShows any spreadsheets youhave saved as templates. Seenext page for saving aspreadsheet as a template.

New New from existingThis option allows you tocreate a new spreadsheetbased on an existingspreadsheet.

8/2/2019 1-excel 2007 ess

38/102

Excel 2007 Essentials - AutoSum


AutoSum

Using AutoSumAutoSum is a quick and easy way of adding up lists of figures in Excel.

Using the AutoSum Icon

1. Click on the cell where yourequire the answer

2. Click on the AutoSum icon

3. Press Enter or click on the tick

You dont have to leave a blank cell between the figuresand the answer

In previous versions of Excel, you had to leave a blank cellbetween the last item you were adding up and the total as if

you needed to insert any more figures to the list, then theanswer wouldnt have updated to include the new information.

However, in Excel 2007 you dont have to leave a blank cell,though it makes it easier to read if you do.

AutoSum has put flashing lights around the wrongfigures

Sometimes AutoSum guesses wrongly. If this happens justclick and drag over the correct cells before pressing Enter.

Making Sure AutoSum Adds the Correct Figures

1. Select the figures you wish toadd up, the blank cell, and the

cell where you require theanswer

2. Click on the AutoSum icon Answer appears in the last cell

Typing the Sum Formula

If you prefer, you do not have to use the AutoSum icon. You can type theformula into the cell instead.

1. Click on the cell where yourequire the

2. Type =sum( As soon as you start to type sum, allthe functions starting with s start to

8/2/2019 1-excel 2007 ess

39/102



appear with a description of what itdoes

3. Continue typing sum4. Click on the first cell you wish

to add upThe cell reference will appear

5. Type a colon6. Click on the blank cell at the

end of the listThe cell reference will appear

7. Click Enter

Formulae

What is a Formula?

Formula is the term used for a calculation in your spreadsheet. The diagramsbelow show an example formula that has been created.

To work out the surplus (in Cell B15) we need to do a calculation. This isachieved by taking away the expenditure from the income (salary). You cansee this in the diagram on the left. On the right-hand side you can see whathappens once the formula has been completed.

Formula being entered Formula completed

8/2/2019 1-excel 2007 ess

40/102



How Is the Formula Made Up?

Formulae always start with the equals sign thats how Excel knows itsa formula

Cell references are used instead of numbers

A mathematical symbol is used to denote the type of calculation

E.g. Here is the formula from the example above, which found us the surplus(or money left over).

Creating a Formula


2. Type the = sign3. Click on the first cell involved

in your calculationFlashing lines will appear around thecellThe cell reference will be insertedinto the formula

4. Type the maths symbol youare using (see below)

5. Click on the next cell involvedin your calculation

Flashing lines will appear around thecellThe cell reference will be insertedinto the formula

6. Repeat steps 4 & 5 (if youneed to add more to yourformula)

7. Press Enter

Once the formula is confirmed the answer will appear in the cell and theformula will appear on the Formula bar.

Always use cell references in Formulae and nevernumbers

Although Formulae will still work if you use numbers instead ofcell references, it is never advisable to use numbers. Using cell

references means that if the number contained in the cellshould change, the formula will update to show the correct

answer, so your spreadsheet is always correct.

=B3-B12Starts with the

equals sign

The cell thatcontains theincome

The subtraction sign

The cell thatcontains totalexpenditure

8/2/2019 1-excel 2007 ess

41/102



The formula isnt working

If your formula isnt working, go to the cell which contains theformula and look at the formula on the Formula bar. Check that

what is written there is correct.

Mathematical Symbols

Press+ To perform an addition

Press- To perform a subtraction

Press* To perform a multiplicationPress/ To perform a division

Use the number keypad

The easiest way of typing the mathematical symbols is to usethe keys around the number keypad on the right hand side of

the keyboard.

Precedence of Calculation

Calculations are not simply done from left to right. Below is the order inwhich all calculations are performed.

Priority Symbol Explanation

1 ( )Anything in brackets is done before anythingoutside the brackets is ever considered.

2 ^Raises a number in order of magnitude: raises it tothe power of something else, e.g. 32

3 * /Multiply and divide are on the same level.Whichever is the furthest left in the formula isdone first.

4 + -Plus and minus are on the same level. Whicheveris furthest left in the formula is therefore donefirst.

The acronym for this is BODMAS

BracketsOrderDivideMultiplyAddSubtract

8/2/2019 1-excel 2007 ess

42/102

Excel 2007 Essentials - Spreadsheet Design Principles


Spreadsheet Design Principles

Never put a number in a formulaAlways use cell references in Formulae and never numbers. If you usenumbers:-

If the number should change, your formula will not update to give thecorrect answer

You will have to change every formula that uses the number, rather thanjust changing the contents of one cell

It will be difficult to find all the Formulae that relate to this number Other people using your spreadsheet may not know what the number

refers to

If you come back to the spreadsheet a long time after you created it, youmay not know what the number refers to

Calculate from the left and down

Clean and well-designed spreadsheets calculate downwards and to the right.This makes them easy to follow and avoids circular references.

Avoid circular references

Circular references occur when a formula loops back on itself. At its mostsimple, a circular reference can occur when a cell containing a formula isusing itself somewhere in a calculation.

Check your spreadsheet by hand

Mistakes can easily arise through:-

Figures being entered incorrectly Formulae being typed in incorrectly New information being typed in that does not get included in existing

formulas

8/2/2019 1-excel 2007 ess

43/102

Excel 2007 Essentials - Building On The Basics


Building On The Basics

Copying FormulaeYou can copy formula using AutoFill and they will automatically adjust tomake sense.

1. Create your first formula2. AutoFill this formula across or

down (to copy it to cells)

The formula will not stay the same, but will adjust to make sense. Seethe diagram below:

When the original formula inB10 was AutoFilled across, thecolumn letters adjusted tomake sense.

When the original formula in I4 wasAutoFilled downwards, the row numbersadjusted to make sense.

Position your mouse here toAutoFill, Make sure it looks

like a thin black cross.

8/2/2019 1-excel 2007 ess

44/102

Excel 2007 Essentials - What Are Absolute Cell References?


What Are Absolute Cell

References?

Using AutoFill to copy formulas is a great way to save time, but the cellreferences in the original formula do not always need to be adjusted. Thereare some situations where a cell reference needs to remain constant.

For example:

Look at the spreadsheet shown below - everyones salary is due to increaseby 10%. The first formula, to find Shing Chens new salary has beencreated. His current salary (in Cell B4) has been multiplied by the value in

Cell B1, 10%.

The quickest way to work out everyone elses increase is to copy ShingChens formula. However, if the formula is copied using AutoFill as it is, therow numbers will be incremented, including the cell reference for the 10%increase, and well end up with some funny answers.....

Excel 2007 is notifying you there is an errorin the formula. A smart tag has appeared to

help you solve it.

8/2/2019 1-excel 2007 ess

45/102



Here, AutoFill has caused the row numbers to be adjusted. But the formulawe need requires Cell B1 to remain constant even when AutoFill isused.

Excel must absolutely always look at this cell. In other words, we need to

make it an absolute cell reference.

Creating and Using Absolute Cell References

Creating Absolute Cell References from Scratch

1. Select the cell where yourequire the first formula

2. Enter the formula as normal

(see page 40)3. Press F4 after the cell

reference you wish to beabsolute

E.g. F4

(Dollar signs will appeararound the cell reference)

4. Press EnterOrClick on the green tick on the

Formula bar5. AutoFill the formula The absolute cell reference will

remain constant

Changing an Existing Formula that Needs AbsoluteCell References

1. Click on the cell containing theformula you wish to change

2. Double-click on the cell

OrClick next to the formula on

8/2/2019 1-excel 2007 ess

46/102



the Formula bar3. Move the cursor so that it sits

next to the cell reference youwish to make absolute

4. Press F4 Dollar signs will appear around

the cell reference5. Press Enter

OrClick on the green tick on theFormula bar

Not sure if it needs to be absolute?

Create the formula without the dollar signs. If it doesnt workwhen you AutoFill, think about why. Go back and edit your

original formula then try AutoFill again.

8/2/2019 1-excel 2007 ess

47/102

Excel 2007 Essentials - Printing


Printing

Printing the entire spreadsheet using the keyboard

Setting Print Options

1. Click on the Office Button2. Select Print3. Choose Print Print dialog box appears

4. Once you have picked youroptions, click OK

Setting the Print area

If you often print the same section of your worksheet, you can set it as thePrint Area. This means that when you click on print Excel will only print outthis area.

1. Select the area you want toprint

2. Click on the Page Layout tab

3. Click on the Print Area button

1. Click Ctrl + P2. Press Enter

Decide whichpage(s) youwant to print or

specify the range

Type how manycopies you want

Select what youwant to print

If yourspreadsheetconsists of many

sheets, it is agood idea tochoose Collate

8/2/2019 1-excel 2007 ess

48/102



4. Choose Set Print Area A dashed line appears around the are

Clearing the Print area

1. Click on the Page Layout tab2. Click on the Print Area button

3. Choose Clear Print Area

Seeing and changing the Page Breaks

This works exactly the same as in previous versions of Excel.

1. Click on the View tab2. Click on Page Break

Preview

The page breaks are shown in blue

A dialog box may appear

3. Click OK4. To move a page break,click and drag the blueline

Print Preview

1. Click on the Office Button2. Select Print3. Choose Print Preview4. Use the Print Preview tab to

move to different pages andview the margins

I dont want to print the Print Area.Thats fine, in the Printing dialog box, tick the Ignore print areas box,

.

8/2/2019 1-excel 2007 ess

49/102



5. When you have finished, clickClose Print Preview

8/2/2019 1-excel 2007 ess

50/102

Excel 2007 Essentials - Functions


Functions

Inserting basic functions

Inserting basic functions

There are certain useful calculations that become long winded if you try andcreate them with straightforward formulae e.g. calculations such as findingthe Average of a group of numbers.

The Autosum icon and Insert Function icon in Excel are there tomake such calculations quick and easy.

Using AutoSum to find the Sum, Count Numbers,

Average, Minimum or Maximum number in a range


2. Click on the Sum dropdownbutton

3. Select the function you require

4. Press enter

Using the Insert Function icon to insert functions


2. Click on the Insert Function iconon the Formula Bar

8/2/2019 1-excel 2007 ess

51/102



3. Search for a function and pressthe Go buttonOr

4. Click on the category dropdownarrow and choose a category

5. Select a function from the list6. Click OK

7. Click on this icon to makethe box smaller (so that you canhighlight the appropriate cells)

8. Click on this icon again tomake the box bigger

9. Check that Excel has the correctcell references

10.If the figures are incorrect,highlight the correct figures onthe spreadsheets

11.Click OK

Typing in formulae to find the Average, Minimumand Maximum


2. Type the = sign3. Type Average, Min or Max

4. Type in an open bracket5. Type in the first cell reference

you require (or click on the cell)6. Type a colon7. Type in the blank cell reference

at the end of the list (or you canclick on the cell)

8. Press enterE.g. =Average(A1:A6) =Max(A1:A6) =Min(A1:A6)

Using percentages

Entering percentages

See page 20

Finding the percentage of a number

e.g. What is the VAT on a 100


8/2/2019 1-excel 2007 ess

52/102



2. Type the = sign3. Click on the cell containing the

percentage, e.g. 17.5% forVAT

4. Type the asterisk to signify

multiplication5. Click on the cell containing the

number you wish to find apercentage of, e.g. 100

6. Press Enter

Finding one number as a percentage of the other

e.g. Finding out what percentage of your salary your rent takes up

Entering the formula Completed formula

1. Click on the cell where you require theanswer

2. Click on the rent figure (B4 in theexample above)

This cell should be thefigure you are trying todisplay as a percentage

3. Press the forward slash to indicatedivision

4. Click on the salary figure (B1 in theexample above)

This cell should be thefigure you are trying tofind the percentage of

5. Press Enter6. Select the cell where you have the The answer will be

8/2/2019 1-excel 2007 ess

53/102



answer (C4 in the above example) displayed as adecimal

7. Click on the Percentage icon

Test your formulas with simple numbers

If you are not sure that you formulas are working, test themout with simple numbers first of all. You can replace these

numbers later.

Converting a number to a percentage

1. Click on the cell you wish tochange

2. Click on the Percentageicon

Freeze Panes

If your spreadsheet has headings at the top and down the side, it becomes abit frustrating when you scroll across or down and can no longer see whatyou are talking about. Freeze panes solve this problem by sticking columns

and rows down on the screen where you can always see them.

Freezing the top row

1. Click anywhere in thespreadsheet

2. Click on the View tab3. Click on Freeze Panes There are 3 options

Freeze PanesFreeze Top Row

Freeze First Column4. Click on Freeze Top Row If you scroll down, you will still

see the top row

Unfreezing the top row

1. On the View tab, click on Freeze Panes2. Choose Unfreeze Panes

8/2/2019 1-excel 2007 ess

54/102



Freezing a row and column

This is useful if you need to freeze the top row and the first column in onego.

1. Click on cell B2 The row above and the columnto the left will now be frozen

2. Click on the View tab3. Click on Freeze Panes4. Click on Freeze Panes If you scroll down and across

you will still see the top rowand the first column

8/2/2019 1-excel 2007 ess

55/102

Excel 2007 Essentials - Making a Spreadsheet Look Good


Making a Spreadsheet Look

Good

Borders and Shading

Often you may want to make a spreadsheet look less plain by drawing a lineunder or around cells or by adding colour/shading to text or a cell.

Adding borders

1. Select the cells you wish to

add a border to2. Click on the border button onthe on the Home tab withinthe Font group

3. Select the border you want

Adding shading

1. Select the cells you wish toformat

2. Click on the Format button onthe Home tab within the Cellsgroup

3. Click on Format cells4. Click on Fill5. Click OK

Clearing shading1. Select the cells you wish to

remove the shading2. Click on the Format button on

the Home tab within the Cellsgroup

3. Click on Format cells4. Click on Fill5. Click on No Colour6. Click OK

8/2/2019 1-excel 2007 ess

56/102



Changing the colour of text

1. Click into the cell2. Click on the down arrow next

to the Font colour icon

Removing the colour from text

1. Click into the cell you wish toclear the colour from

2. Click on the down arrow nextto Font colour icon

3. Select Automatic

Basic Formatting

Bold, italic and underline


2. Click on the icon you require

Changing the font


2. Click on the down arrow nextto the font box

Excel 2007 has a feature thatdisplays the font list in their actualfonts

3. Select the font you require

Changing the font size


2. Click on the down arrow nextto the font size box

3. Select the font size you

require

8/2/2019 1-excel 2007 ess

57/102



More Detailed Font Changes


2. Click on the Dialog box

launcher arrow on the fontgroup

These can be found on some groups

on the ribbon

3. Make the changes you require4. Click OK

Using Cell Styles

For speed, you can format your cells by using a cell style. A cell style is a setof formatting characteristics, such as, font type, font size, cell borders andcell shading.

Applying a Cell Style

1. Select the cells2. Within the Styles group, click

on Cell Styles

3. Move the cursor over thedifferent styles

You will see what the cells look like

4. Click on the style you want tobe applied to the cells

Clearing all formatting from cells

1. Select the cells you wish toclear all formatting from

2. Within the Styles group, clickon Cell Styles

3. Click on Normal All formatting will be removed

8/2/2019 1-excel 2007 ess

58/102



Alignment

Alignment signifies whereabouts your text/numbers will line up in a cell.

Examples of alignment in cells

Using the alignment icons

1. Click on the cell2. Click on the icon you need(see

above)

Orientation of text/numbers in a cell

1. Click on the cell

Top align

Left

align Right align

Bottomalign

Left alignedor the default

alignmentCentrealigned

Rightaligned

Top

alignedMiddlealigned

Bottomaligned orthe defaultalignment

Middlealign

Centrealign

8/2/2019 1-excel 2007 ess

59/102



2. Click on the Orientation button By choosing one of these options, thecell automatically becomes larger, forexample,

Using Wrap text

For this to work, there must be enough text in the cell so you cant see all ofit.

1. Click on the cell For example,2. Click on the Wrap text icon

The text will by default align to thebottom of the cell

Using Merge & Centre

Merge and Centre is a useful feature if you want to add a title to a table inExcel. If you type the title in cell A1, the title will appear to the left of thetable, however, if you use the Merge & Centre feature, the title will appear inthe centre of the table.

1. Click on the cells you want tomerge

Select cells spanning the width of thetable

2. Click on the Merge & Centreicon

Using Conditional Formatting to highlight duplicatecells

1. Select the cells2. Click on Conditional

Formatting

8/2/2019 1-excel 2007 ess

60/102



3. Click on Highlight Cell Rules4. Select Duplicate Values5. Click on the down arrow6. Select a different colour if you

wish

7. Click OK8. Click away from the cells to

see the duplicate cells in adifferent colour

Changing the number formats

What are the number formats?

Number formats allow you to change the appearance of your figures. Forexample, if your figures represent money, you can format them to appearwith the pound sign and two decimal places.

More currencies to choose from...

In this version of Excel, there is a drop down arrow by the Currencyicon, where you can pick US dollars ($) or Euro ().

Even more number formats to choose from...

1. Click on the cells you wish to format2. Click on the Number Format drop down arrow within the Number

group

Make numberslook likecurrency

Make numbers

appear aspercentages

Decrease thenumber ofdecimal places

Increase thenumber ofdecimal places

Insertcommasaround the

thousands

8/2/2019 1-excel 2007 ess

61/102



Applying number formats

1. Click on the cells you wish toformat

2. Click on the Number format

icon you require

Getting rid of number formats

1. Click on the cells you wish toclear the number formats from

2. Click on the Number Formatdrop down

3. Select the first option, General

No specific format

Displaying negative numbers in red

1. Select the cells to change2. Click on the Dialog box

launcher arrow for Number

3. Click on the Number tab4. Click on Number from the

category list5. Decide how you want the

negative numbers to be

8/2/2019 1-excel 2007 ess

62/102



displayed

6. Click OK

Displaying negative numbers in brackets

1. Select the cells to change2. Click on the Dialog box

launcher arrow for Number

3. Click on the Number tab4. Click on Custom from the

category list5. Type in a code underneath the

Type box (see below)

8/2/2019 1-excel 2007 ess

63/102



What format do you want The code you need to type

Negative numbers with brackets #,##0(-#,##0)

Negative numbers in red and withbrackets

#,##0[Red](-#,##0)

Negative numbers in currency and

with brackets #,##0.00(-#,##0.00)Negative numbers in currency, inred, and with brackets

#,##0.00[Red](-#,##0.00)

6. Click OK

Changing the date format

You must always type in the date with forward slashes around, but it doesnot have to look like this forever!

1. Select the cells you wish tochange

2. Click on the Dialog box launcherarrow for Number

3. Click on the Number tab4. Click on Date from the category

list5. Choose the date type you require

from the list on the rightYou will see a preview at thetop

6. Click OK

Changing the column widths

To re-size column widths, place your mouse between thecolumn letters and make sure it looks like the cross

arrow

Type your codeinto this box

8/2/2019 1-excel 2007 ess

64/102



Making a column or row just big enough

1. Place your mouse to the right of

the column letter you wish to re-size

Or1. Place your mouse below the row

number you wish to re-size2. Double-click The cell will be as big as it

needs to be

Adjusting a row or column manually

1. Place your mouse to the right ofthe column letter you wish to re-size

Or1. Place your mouse below the row

number you wish to re-size2. Click and drag to the size you

require

Re-sizing several columns at once

1. Select the columns or rows youwish to re-size

2. Place your mouse at the right-hand edge of the selectedcolumns

Or1. Place your mouse underneath the

selected rows2. Click and drag The columns or rows will all

become the same size3. De-select the rows

Re-sizing all the columns and rows

1. Select the whole of thespreadsheet (see page24)

2. Re-size column A to the desiredsize

And/Or1. Re-size row 1 to the desired size

2. Click in the middle of thespreadsheet to deselect

8/2/2019 1-excel 2007 ess

65/102



Adding Borders

Adding borders using Font group

1. Select the cells you wish toapply a border to

2. Click on the down arrow nextto the Borders icon

3. Select a border style

Adding borders using the Format Cells dialog box

1. Select the cells you wish toapply a border to2. Click on the Dialog box

launcher for the Font groupFormat Cells dialog box opens

3. Click on the Border tab Make your changes

4. Click OK

Clearing borders

1. Select the cells you wish toclear borders from

Click on the

border iconyou require

Click on theline style

you require

Click here to

change thecolour

8/2/2019 1-excel 2007 ess

66/102



2. Click on the down arrownext to the Borders icon

3. Choose No Border

Adding shading

Adding shading using the Font group

1. Select the cells you wish toapply shading to

2. Click on the down arrow nextto the Fill icon

3. Select a Fill colour

Its not the right colour!

Whilst your cells are still selected, they will look a completelydifferent colour to the one you chose. If you click somewhere

else on the spreadsheet to de-select them they will be fine

Adding shading using the Format Cells dialog box1. Select the cells you wish to

apply a fill to2. Click on the Dialog box

launcher for the Font groupFormat Cells dialog box opens

3. Click on the Fill tab Make your changes

8/2/2019 1-excel 2007 ess

67/102



4. Click OK

Clearing shading

1. Select the cells you wish to

clear the fill colour from2. Click on the down arrownext to the Fill icon

3. Choose No Fill

Changing the colour of text

1. Select the cells you require2. Click on the down arrow next

to the Font colour icon

3. Select a Font colour

Format painter

Format painter allows you to quickly copy formatting from one cell toanother.

8/2/2019 1-excel 2007 ess

68/102



Using format painter once

1. Select the cell(s) that are alreadyformatted

2. Click on the Format Painter icon Flashing lines will appear around

the cellA paintbrush appears next toyour mouse

3. Select the cell(s) you wish to copyformatting to

Using Format Painter more than once

1. Select the cells(s) that are alreadyformatted

2. Double-click on the Format Painter

icon

Flashing lines will appear around

the cellA paintbrush appears next toyour mouse

3. Select the cells you wish to copyformatting to

4. Click on the Format Painter iconagain when you have finished

Print Preview

Getting to Print Preview

1. Click on the Office button2. Click on Print3. Select Print Preview

Zooming In or Out on Your Print Preview

1. Hover the mouse over the

print preview

It will look like a magnifying glass

2. Click the mouse You will be zoomed in or out

Moving between the pages

1. In the bottom left corner ofthe screen you can see which

page you are previewing

2. Click on Next Page or PreviousPage

8/2/2019 1-excel 2007 ess

69/102



OrUse the scroll-bar on the righthand side of the screen

Changing the margins

1. Click on the Show marginstickbox

The margins will appear as dottedlines

1. Position your mouse over adotted line so that it changesto a cross arrow

2. Click and drag to increase ordecrease the margin

Closing Print Preview

Click on the Close PrintPreview button

Changing the Page Setup

Within the Print previewscreen, click on the PageSetup icon

Header margin

Top margin of your spreadsheet

Bottom margin of your

spreadsheet

Footer margin

8/2/2019 1-excel 2007 ess

70/102



Page Break preview

Changing Page Breaks

1. Ensure you are in Page Breakview

2. Click and drag over the bluelines to adjust the position ofthe page breaks if needed

Closing Page Break preview

Click on the Normal view icon

Changing the page set-up

Bringing up the Page setup dialog boxThere are two ways of getting into page set-up through Print Preview orfrom the Page Layout tab.

From Print Preview

1. Click on the Office button2. Click Print3. Select Print Preview4. Click on the Page Setup button Page Setup dialog box opens

Page Break preview

Once you have viewed your spreadsheet in Print preview and returnedto Normal view, you will be able to see where the page breaks are.

Alternatively, you can simply click on the Page Break Preview icon, ,found next to the Zoom slider in the bottom right corner.

8/2/2019 1-excel 2007 ess

71/102



From the Page Layout tab

1. Click on the Page Layout tab2. Click on the Dialog box

Launcher for Page SetupPage Setup dialog box opens

Changing the margins

1. Bring up the Page setup dialogbox

2. Click on the Margins tab3. Type in the margins you

requireOr

Use the up and down arrowsnext to the margin sizes

8/2/2019 1-excel 2007 ess

72/102



Centring the spreadsheet


2. Click on the Margins tab

3. Click on the two tickboxes forCentre on page

Changing the orientation


2. Click on the Page tab

3. Decide if you want thespreadsheet to be in landscapeor Portrait

Scaling the size of the spreadsheet

If your spreadsheet is too big to fit on the page, or if is too small to read,you can scale it up or down in size.

1. Bring up the Page setup dialog

8/2/2019 1-excel 2007 ess

73/102



box2. Click on the Page tab3. Increase or decrease the

percentage next to adjustto....

4. Click OK

Printing the gridlines


2. Click on the Sheet tab

3. Click next to Gridlines so thatit is ticked

4. Click OK

Changing the page order


2. Click on the Sheet tab3. Choose the option you require

below Page order

8/2/2019 1-excel 2007 ess

74/102



Headers and Footers

You can add extra text to the headers and footers in spreadsheets. The typeof information you can add can include page numbers, current date, file pathand sheet name. You can type on the left, right and centre of a header orfooter.

Inserting text in Headers and footers using theInsert tab

1. Click on the Insert tab2. Click on the header and Footer

iconHeader area appears at the top ofthe spreadsheet

3. Type the text you want toappear in the Header

4. Scroll down to the Footer

5. Type the text you want toappear in the Footer

Inserting other elements into a Header or Footer

What else can you add to a header or footer?

1. Ensure you are in headers orfooters

2. Decide which part of the

header or footer you want tobe in

You can click on the left or right

Adding text to Headers and FootersIf you need to type an ampersand (&) in the Header or Footer, use two&&. For example to see Happy Computers & Elearning in a header, type

Happy Computers && Elearning.

8/2/2019 1-excel 2007 ess

75/102



3. Click on one of the icons from

the Header & Footer Elements

For example, we will add the Page

number to the left header

We will also add the File Path to theright header

4. Click into the main part of thespreadsheet (away from theHeaders and Footers) to seethe text

Text in headers and footers

My text is running into each other!Treat the text in headers and footers just as you would treat

text in a spreadsheet.Click into the header or footer and change the size of the text

from the Home tab.

Using the standard headers and footers

Excel contains standard headers and footers that are easy to apply.

1. Ensure you are in headers orfooters

2. Click on either the Header orFooter icon

If an option contains commas, forexample, Page 1, Book 2, it means

Page 1 will appear in the left headerand Book 2 will appear in the centre

PageNumber Typed text File Path

8/2/2019 1-excel 2007 ess

76/102



header

If your name is not listed in the headers and footers

1. Click on the Office button2. Click on Excel options3. In Personalise your copy of Microsoft Office, type your

name (or whatever you want to appear instead of yourname)

Your name will only appear in spreadsheets which have beencreated after you have done this.

8/2/2019 1-excel 2007 ess

77/102



8/2/2019 1-excel 2007 ess

78/102

Excel 2007 Essentials - Charts


Charts

Charts

Creating charts with the keyboard

1. Select the cells you wish tochart

Include labels and figures (seebelow), but do not select whole rowsor columns

2. Press F11 in the keyboard

Adding a chart using the Insert tab

1. Select the cells you wish tochart

Include labels and figures (seebelow), but do not select whole rowsor columns

2. Click on the Insert tab3. Click on the Chart type you

want

4. Chart appears

Setting a chart type as a default

You may not want the default chart to be the standard Column barchart. If you prefer to use a different chart type, then you can set that

chart type as your default.

1. Select the cells you wish to chart (see below)2. Click on the dialog box launcher for Charts

3. Click on the button,

Now when you create a chart using F11, the default chart type willappear.

Labels

Figures

8/2/2019 1-excel 2007 ess

79/102



Adding a chart title

1. Double-click the chart Chart tools appear at the top of theribbon

2. Click on the Layout tab3. Click on the Chart title icon

4. Click on which option you want Chart title text box appears

5. Start typing As you type you will notice the textappears in the Formula bar

6. Press Enter Text appears as the title

Moving the Chart title text box

1. Click on the text box A thin line appears around the edge2. With the cursor as a 4-headed

arrow, click and drag the textbox to a new position

Adding axis titles

1. Click on the Axis title icon

8/2/2019 1-excel 2007 ess

80/102



2. Decide if you want the

horizontal or vertical axis3. Select Title below axis4. Start typing As you type you will notice the text

appears in the Formula bar5. Press Enter Text appears as the title

Showing the legend (key)

Click on the Legend icon Decide which option you want

Adding data labels

This feature adds a text box with the actual figure for each of the bars inyour chart.

For example, the chart could look like this if you wanted to view the figures.

1. Click on the Data labels icon

2. Click on which option you want For option in the example above isOutside end

8/2/2019 1-excel 2007 ess

81/102



Moving, re-sizing and deleting charts

Moving the chart

1. Click on the chart to select it2. Position the cursor in the

centre of the chartCursor should look like a 4-headedarrow

3. Click and drag to a newposition

Re-sizing the chart

Your mouse must look like a double-headed arrow to re-

size

1. Click on the chart to select it2. Hover the mouse over any corner Your mouse will change to a

double-headed arrow3. Click and drag to make the chart

larger or smaller

Deleting the chart1. Click on the chart to select it2. Press Delete on the keyboard

Selecting parts of a chart

What are the parts of a chart?

Horizontal(Category) Axis

The x axis

Vertical (Value)Axis

The y axis

Legend The key next to the chart that shows you what thecolours represent. This may not be present

Plot area The area taken up by the chart aloneGridlines The lines in the background of the chart that mark the

divisionsThere may be several gridlines on your chart, or you

may have none!Series Series will represent the bars in a bar chart, or the

8/2/2019 1-excel 2007 ess

82/102



pieces of pie in a pie chart. There will usually be morethan one series.

Data Labels Any labels next to the bars or pieces of pie of a chartthat signify the actual value or percentage represented.These may not be present.

Data Table Shows the figures being charted underneath the chart.This may not be present.

Titles Titles for the chart or axes. May not be presentChart Area Absolutely the whole thing!

Selecting different parts of the chart with the mouse

Click on the part of the chart yourequire to select it

Selecting different parts of the chart using theLayout tab

You may prefer to select parts of the chart using the Layout tab.

1. Click on the chart to select it

2. Click on the Layout tab3. Within the Current Selection group,

Data label

Legend

Horizontal(Category)

Axis

Vertical(Value)

Axis

Plot area

Gridlines

Series

TitleChart area

8/2/2019 1-excel 2007 ess

83/102



click on the down arrow next toChart title

4. Select what you want to change That part of the chart will beselected

Changing parts of the chart using the Layout tab

This is a contextual tab it will only appear if you select the chart.

Adding a trendline

1. Click on the chart to select it2. Click on the Trendline icon

3. Select a trendline4. Decide which series the trendline is

going to be based on5. Click OK

8/2/2019 1-excel 2007 ess

84/102



Formatting the chart using the Design tab

Changing the chart type

1. Click on the chart to select it2. Click on the Design tab3. With the Type group, click on the

Change Chart Type icon

4. Select a chart5. Click OK

Changing the colour of the bars1. Click on the chart to select it2. Click on the Design tab3. Click on the down arrow for Chart

Style

4. Select a style

Switching row/column data

1. Click on the chart to select it2. Click on the Switch row/column icon

Changing the chart layout

1. Click on the chart to select it2. Click on the down arrow for Chart

Layouts

3. Select a layout style

8/2/2019 1-excel 2007 ess

85/102



Changing the font of text

1. Click on a title, data label, axis orthe legend (see p82) to select it

2. Change the font using the Font boxon the Home tab

Changing the text using WordArt styles

1. Click on a title, data label, axis orthe legend (see p82) to select it

2. Click on the down arrow for WordArtstyles

3. Select your style

Adding more information to the chart

You may find after creating a chart, you need to add more data to it. You

dont need to recreate another chart all you have to do is add the new dataseries to the existing chart.

In this example, our new data we need to add to the chart is the Happy ITQrow.

1. Select the chart2. Click on the Design tab3. Within the Data group, click on the

Select Data iconSelect Data Source dialog boxappears

8/2/2019 1-excel 2007 ess

86/102



4. Click on the Add button Edit Series dialog box appears

5. Select the cell with the Series name In this example, select the cellwith the text happy ITQ

6. Click into the box for Series values7. Delete the text

8. Select the cells with the values In this example, select the cellswith the Quarter 1- Quarter 4figures

9. Click OK10.Click OK The chart will be updated with

the new data

Changing the scale of the axes

You can change the scale on the axes so they start or finish at different

numbers so that you can see your data more clearly.

1. Right-click the axis you wish tochange

2. Choose Format Axis Format Axis dialog box appears

8/2/2019 1-excel 2007 ess

87/102



3. Change the options next to minimumand maximum

4. Click Close

Printing the chart

If your chart is on a separate sheet

1. Click on the sheet to select it2. Ctrl+P3. Click OK

If your chart is an object on a sheet

1. Select the chart area2. Click on the Office button3. Select Print4. Select Print Selected chart is highlighted5. Click OK

Printing your chart and the figures

Your chart must be inserted as an object on the sheet

8/2/2019 1-excel 2007 ess

88/102



1. Click and drag over the data tableand the chart

2. Ctrl+P3. Click OK

Which chart shall I use?

What types of chart are there?

Area chart

Use this when you want to emphasise changeover time. In the example on the right, thecontribution of several states to sales is shownfrom 1994 to 1996. Washington has increasedthe most over time.

Surface

A surface chart is a bit like a topograhic map.Colours and patterns indicate areas that are inthe same range of values. In this charttemperature and time are plotted together toshow the tensile strength they produce.

Bar chart

A bar chart compares values with each other.The example chart shows expenditure figuresover six months.

8/2/2019 1-excel 2007 ess

89/102



Radar Chart

In this chart, each category of information has

its own line radiating out from the centre. Inthe example shown there are three brands ofvitamin, each represented by a line. Thefurther the line reaches out from the centrethe more vitamins it has in it.

Column chart

A column chart is very similar to a bar chart,

except the bars reach vertically instead ofhorizontally. The example chart comparesexpenditure figures over six months.

Bubble chart

A bubble chart shows three sets of variables,represented by the two axes and the size ofthe bubble. In the example chart marketshare is plotted. The axes shows the number

of products and the sales, and the size of thebubble shows the market share.

Line chart

A line chart is useful for comparing trends. Inthe example chart, expenditure figures areshown over six months.

Scatter graph

A scatter graph is useful for comparing a setof values with the average or predictedvalues. In the example chart, one set of dotsshow predicted temperatures, and another setof dots show the actual temperatures aroundit. This type of chart is commonly used forscientific data.

8/2/2019 1-excel 2007 ess

90/102


Page

1-excel 2007 ess

Documents

Transcript of 1-excel 2007 ess