Enjoy… Microsoft Excel 2007 by Christine Kent

Enjoy… Microsoft Excel 2007 for Novice Users © Copyright Christine Kent, September 2008

Author: Christine Kent Web address www.christinekent.net

Blog address http://christinekent.blogspot.com/

Email address christine@christinekent.com.au

ISBN 978-0-9804893-2-3 Books in the Enjoy… series Enjoy… Microsoft Word 2007 (ISBN 978-0-9804893-0-9) Enjoy… Upgrading to Microsoft Word 2007 (ISBN 978-0-9804893-1-6) Enjoy… Microsoft Excel 2007 (ISBN 978-0-9804893-2-3) Enjoy… Internet Marketing (ISBN 978-0-9804893-3-0)

Disclaimer All rights reserved. No part of this publication may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, scanning, recording, or any information storage and retrieval system, without permission in writing from Christine Kent. No patent liability is assumed with respect to the use of the information contained herein. While every precaution has been taken in the preparation of this book, the publisher and authors assume no responsibility for errors or omissions. Neither is any liability assumed for damages resulting from the use of the information contained herein. The information contained herein was correct at the time of preparation.

Table of Contents

Table of Contents 1:

How to use this book ........................................................................................................................................5 Enjoy Excel 2007 ...................................................................................................................................................... 5 What you need to know before you start ............................................................................................................... 5

Background to Excel 2007 .................................................................................................................................7 Open Excel ............................................................................................................................................................... 7 Exercise: Open Excel 2007 from Windows XP........................................................................................................................... 7 Exercise: Open Excel 2007 from Windows Vista ....................................................................................................................... 9

Guided tour of the Excel screen ............................................................................................................................ 11 The Window..................................................................................................................................................................... 11 The Ribbon ....................................................................................................................................................................... 12 The taskbar ...................................................................................................................................................................... 12 Customise the taskbar ..................................................................................................................................................... 13 The down arrows ............................................................................................................................................................. 14 The Worksheet grid, rows, columns and cells ................................................................................................................. 15

Create a budget .............................................................................................................................................. 16 Create a new spreadsheet ..................................................................................................................................... 16 Exercise 1 – Type your cell labels ........................................................................................................................................... 16 Exercise 2– Make the text wrap in the cells ........................................................................................................................... 17 Exercise 3– Change column width ......................................................................................................................................... 18 Exercise 4 – Type your data ................................................................................................................................................... 18

Save, close and open your spreadsheet ................................................................................................................ 22 File names ........................................................................................................................................................................ 22 File locations .................................................................................................................................................................... 22 Exercise 5– Save your spreadsheet using the Save As function in Windows XP ....................................................................... 23 Exercise 6 – Save your spreadsheet using the Save As function in Windows Vista .................................................................. 25

Close your spreadsheet ................................................................................................................................................... 27 Exercise 7 – Close your spreadsheet ...................................................................................................................................... 27 Exercise 8 – Open your spreadsheet ...................................................................................................................................... 27

Enter a range of dates using AutoFill ..................................................................................................................... 29 Exercise 9 – Enter a range of dates using AutoFill .................................................................................................................. 29

Insert and delete rows ........................................................................................................................................... 29 Exercise 10 – Insert rows ....................................................................................................................................................... 29 Exercise 11 – Delete rows...................................................................................................................................................... 31

Insert and delete columns ..................................................................................................................................... 32 Exercise 12 – Insert and delete columns ................................................................................................................................ 32

Save your spreadsheet again ........................................................................................................................................... 33 Exercise 13 – Save spreadsheet with Save function................................................................................................................ 33

Navigation options ................................................................................................................................................ 34 Exercise 14 – Move around in a spreadsheet ......................................................................................................................... 34

Use and test formulas ........................................................................................................................................... 36 Use formulas to calculate values ..................................................................................................................................... 36 Exercise 15 – Multiply and divide .......................................................................................................................................... 36

The rules of precedence .................................................................................................................................................. 37 Use formulas to calculate totals ...................................................................................................................................... 38 Exercise 16 – Total a range of cells ........................................................................................................................................ 38 Exercise 17 – Set sheet to re-calculate automatically ............................................................................................................. 39

Understanding relative and absolute cell references ...................................................................................................... 40 Exercise 18 – Identify and copy a relative cell references ....................................................................................................... 40 Exercise 19 – Enter and copy an absolute cell reference......................................................................................................... 41

Enjoy… Microsoft Excel 2007

Toc i

Table of Contents

Exercise 20 – Copy a formula to another cell and modify ....................................................................................................... 42 Exercise 21 – Enter a Range of formulas using AutoFill........................................................................................................... 43 Exercise 22 – Calculate monthly debit or credit...................................................................................................................... 45

Set regional options in Windows ..................................................................................................................................... 46 Manage Excel 1 – Set regional options in Windows XP........................................................................................................... 46 Set regional options in Windows Vista .................................................................................................................................. 47 Exercise 23 – Enter time and date.......................................................................................................................................... 49 Exercise 24 – Enter fractions and other numbers ................................................................................................................... 51

The Home tab, Number group ......................................................................................................................................... 51 Exercise 25 – Format numbers............................................................................................................................................... 51

Manage the way things look in Excel .............................................................................................................. 54 Pin a spreadsheet to the Office Menu ................................................................................................................... 54 Manage Excel 2: Pin a spreadsheet to the Office Menu .......................................................................................................... 54

Customise the Quick Access Toolbar ..................................................................................................................... 55 The default items on the Quick Access Toolbar ............................................................................................................... 56 Move and customise the Quick Access Toolbar............................................................................................................... 56 Manage Excel 3: Customise the Quick Access Toolbar ............................................................................................................ 56 Manage Excel 4: Add and remove commands on the Quick Access Toolbar – the quick way ................................................... 58 Manage Excel 5: Work with Quick Access Toolbar icons (2) .................................................................................................... 59

Minimise the Ribbon ............................................................................................................................................. 59 Manage Excel 6: Minimise the Ribbon ................................................................................................................................... 59

The View tab (Alt W) ............................................................................................................................................. 59 Workbook Views .............................................................................................................................................................. 60 Manage Excel 7: Explore Workbook Views group................................................................................................................... 60 Manage Excel 8: Explore Show/Hide group............................................................................................................................ 60 Manage Excel 9: Explore Zoom group .................................................................................................................................... 61 Manage Excel 10: Explore Window group .............................................................................................................................. 62

Minimise and maximise the Excel window ............................................................................................................ 63 Manage Excel 11: Minimise and maximise ............................................................................................................................. 63

Display ScreenTips ................................................................................................................................................. 63 Manage Excel 12: Set Excel to display detailed screen tips ..................................................................................................... 63

Set your default font and other options ................................................................................................................ 65 Manage Excel 13 – Change spreadsheet defaults ................................................................................................................... 65

Navigate in Excel ............................................................................................................................................. 67 Use the keyboard instead of the mouse ............................................................................................................... 67 Access Keys ...................................................................................................................................................................... 67 Key Combination Shortcuts ............................................................................................................................................. 67 Microsoft On-line Training – Keyboard shortcuts in the 2007 Office system ........................................................................... 67 Manage Excel 14: Find Key Combination Shortcuts ................................................................................................................ 68

Display Key Combination Shortcuts in ScreenTips ................................................................................................ 68 Manage Excel 15: Set Excel to display shortcuts..................................................................................................................... 68

Explore Access Keys and the Ribbon ..................................................................................................................... 69 Manage Excel 16: Explore Access Keys and the Ribbon .......................................................................................................... 69

Format spreadsheet cells ................................................................................................................................ 73 Format your spreadsheet from the Home tab ...................................................................................................... 73 The Home tab, Clipboard group ...................................................................................................................................... 73 The Home tab, Number group ......................................................................................................................................... 73 The Home tab, Cells group............................................................................................................................................... 73 The Home tab, Font group ............................................................................................................................................... 73 Exercise 26 – Change default font& font size for spreadsheet ................................................................................................ 73 Exercise 27 – Change font, font size and font colour in headings ............................................................................................ 76

Toc ii

Enjoy… Microsoft Excel 2007

Table of Contents

Exercise 28 – Experiment with bold italics, underline and more ............................................................................................. 80

The Home tab, Alignment group ..................................................................................................................................... 81 Exercise 29 – Format text alignment in cell ............................................................................................................................ 81

The Home tab, Styles group ............................................................................................................................................. 84 Exercise 30 – Apply Conditional Formatting ........................................................................................................................... 84 Exercise 31 – Apply Cell Styles ............................................................................................................................................... 85

Format spreadsheet ........................................................................................................................................ 87 Microsoft Office Themes ....................................................................................................................................... 87 Looking at Themes ........................................................................................................................................................... 87 Exercise 32 – Cruise around themes ...................................................................................................................................... 87

Changing theme colours .................................................................................................................................................. 88 Exercise 33 – Change theme colours, keep theme fonts ......................................................................................................... 88

Changing theme fonts...................................................................................................................................................... 89 Exercise 34 – Change theme fonts, keep theme colours ......................................................................................................... 89

Format your spreadsheet ................................................................................................................................................ 89 Exercise 35 – Format your spreadsheet ................................................................................................................................. 89

Insert header and footer ....................................................................................................................................... 90 Exercise 36 – Insert a header in your spreadsheet ................................................................................................................. 90 Exercise 37 – Insert a footer in your spreadsheet ................................................................................................................... 91 Exercise 38 – Edit header and footer entries .......................................................................................................................... 93

Layout spreadsheet and prepare for publication ............................................................................................ 94 Review the Zoom function .................................................................................................................................... 94 Set your print parameters ..................................................................................................................................... 94 Exercise 39 – Cruise around Page Setup options .................................................................................................................... 94 Exercise 40 – Print Preview spreadsheet................................................................................................................................ 97 Exercise 41 – Cruise around Scale to Fit options ..................................................................................................................... 98 Exercise 42 – Control page breaks ......................................................................................................................................... 98 Exercise 43 – Cruise around Sheet Options .......................................................................................................................... 100 Exercise 44 – Set print sequence.......................................................................................................................................... 100 Exercise 45 – View and print Formulas or Values ................................................................................................................. 102 Exercise 46 – Finalise the page layout of your spreadsheet .................................................................................................. 102

Review spreadsheet ............................................................................................................................................ 103 Exercise 47 – Run spell checker ........................................................................................................................................... 103

Print and check your spreadsheet ....................................................................................................................... 105 Exercise 48 – Print spreadsheet ........................................................................................................................................... 105 Exercise 49 – Check printed spreadsheet for errors .............................................................................................................. 105

Use documentation to overcome problems .................................................................................................. 106 Microsoft Get Started .......................................................................................................................................... 106 Manage Excel 17 – Install Microsoft Get Started .................................................................................................................. 106

Microsoft Help ..................................................................................................................................................... 107 Manage Excel 18 – Guided tour of Microsoft Help ............................................................................................................... 107

Microsoft Excel on-line training........................................................................................................................... 109 Excel 2007 Solution Center .................................................................................................................................. 110 Other Online Experts ........................................................................................................................................... 110 10:

Create charts ................................................................................................................................................ 111 Create new worksheet and chart ........................................................................................................................ 111 Exercise 50 – Create and save a new worksheet with chart .................................................................................................. 111

Modify chart type and layout .............................................................................................................................. 113 Exercise 51 – Change the chart view .................................................................................................................................... 113 Exercise 52 – Print charts .................................................................................................................................................... 115 Exercise 53 – Add chart titles............................................................................................................................................... 116

Enjoy… Microsoft Excel 2007

Toc iii

Table of Contents

Exercise 54 – Format chart elements with Chart Styles ........................................................................................................ 117 Exercise 55 – Format chart elements manually .................................................................................................................... 117 Exercise 56 – Customise Labels ............................................................................................................................................ 121 Exercise 57 – Add or remove Trendlines .............................................................................................................................. 123 Exercise 58 – Format Axes and Gridlines .............................................................................................................................. 124 Exercise 59 – Change Column Type ...................................................................................................................................... 125 Exercise 60 – Change Chart Type from Column to Bar .......................................................................................................... 126 Exercise 61 – Check printed charts for errors ....................................................................................................................... 127

Select chart type and design ............................................................................................................................... 128 Exercise 62 – Create a Line Chart ......................................................................................................................................... 128 Exercise 63 – Create a Pie Chart from non-adjacent data ..................................................................................................... 129 Exercise 64 – Create a chart for average monthly sales ........................................................................................................ 132 Exercise 65 – Show maximum & minimum monthly sales .................................................................................................... 133 Exercise 66 – Explore chart formatting ................................................................................................................................ 135

11:

Name and store spreadsheet ........................................................................................................................ 136 Why document names and document storage locations matter ........................................................................ 136 Storage location conventions and protocols ................................................................................................................. 136 File and folder management in Windows Explorer ........................................................................................................ 136 Microsoft On-line Training – Organise Files and Folders (Windows XP) ................................................................................ 136 Microsoft On-line Training – Working with Files and Folders (Windows Vista) ..................................................................... 137

Folder naming conventions and path size limits ............................................................................................................ 137 Some folder naming conventions .................................................................................................................................. 137

A word on file extensions .................................................................................................................................... 138 12:

File security................................................................................................................................................... 139 How to prevent others opening a file .................................................................................................................. 139 Manage Excel 19 – Apply, modify and remove a password on a file ..................................................................................... 139 Manage Excel 20 – Protect your spreadsheet – the quick way.............................................................................................. 140

System security .............................................................................................................................................................. 142

Protocols for backing up and storing files ........................................................................................................... 142 The three levels of backup ............................................................................................................................................. 142 Manage Excel 21 – Save AutoRecover files .......................................................................................................................... 143

Perform backups manually ............................................................................................................................................ 144 Manage Excel 22 – Backup your files manually to a removable memory device ................................................................... 144

Performing backups using a backup utility – Windows XP ............................................................................................ 144 Manage Excel 23 – Perform backups using a backup utility – Windows XP ........................................................................... 144 Manage a home computer .................................................................................................................................................. 149

13:

Adjust and print spreadsheets and charts ..................................................................................................... 150 Review and edit documents ................................................................................................................................ 150 Design review ................................................................................................................................................................ 150 Business or technical review .......................................................................................................................................... 150 Grammar and style review............................................................................................................................................. 150 Presentation review ....................................................................................................................................................... 151 Proofread ....................................................................................................................................................................... 151

Toc iv

Enjoy… Microsoft Excel 2007

How to use this book Enjoy Excel 2007

1: How to use this book Enjoy Excel 2007 This book is not a manual. It does not attempt to cover the full range of functions available in Microsoft Excel 2007. It is a workbook containing projects. As you complete those projects, you will be led through a wide range of Microsoft Excel 2007 functions. You will learn how to create professional looking spreadsheets of any kind you like, quickly and easily. You will also learn how to use the Microsoft support materials to lead you through those functions not taught in this workbook.

What you need to know before you start Before you start working through the exercises in this workbook, you need to establish if you have sufficient knowledge of how a computer works to be able to follow the instructions. You need to know… Start up 



how to start a computer and log on

Navigation 

how to move a pointer or insertion point using the mouse



how to highlight and select something using the:



right mouse button



left mouse button

the meaning of: 

click



right click



left click



double click

how to scroll through a menu or file using: 

the up and down keys on your keyboard



the page up and page down keys on your keyboard



the scroll wheel on your mouse



the scroll bar in the program you are using

Enjoy… Microsoft Excel 2007

           Page 5

How to use this book What you need to know before you start

Windows 

what a window is



what a dialog box is



how to use the mouse to drag a window, a dialog box or other element around on your screen

  

Browsing the Web with Internet Explorer 

how to open Internet Explorer Browser



how to type a web address into Internet Explorer Browser

 

Windows Explorer File Management 



how to save and store documents using Windows File Manager 

Folders



Folder structures

If you are not sure of this, there is free training on the Web, placed there by private companies. You can go to the following two tutorials, but if they are no longer available, you will find more by running an internet search. Do both sets of training at: 

http://bellcrest.net/Tutorials/filestructure.html and then



http://www.lvsonline.com/tut-dirs/index2.shtml



how to write down a file path



how to navigate through Windows File Manager and move files from one location to another



how to use memory devices such as: 

floppy disks



CDs



USB memory devices such as flash drives, memory keys

  

If you do not know how to do any of these, you should do the on-line Microsoft training before you attempt this course. There is lots of on-line training to help you, and most of it is very good. For Windows XP go to: 

http://www.microsoft.com/windowsxp/using/setup/maintain/filemgmt.mspx

For Windows Vista go to:

Page 6



http://windowshelp.microsoft.com/Windows/en-US/default.mspx



Click Files and folders.



Click Working with files and folders.

Enjoy… Microsoft Excel 2007

Background to Excel 2007 Open Excel

2: Background to Excel 2007 Open Excel Exercise: Open Excel 2007 from Windows XP The following instructions are suitable for Windows XP operating systems. Go to the next exercise if you have Windows Vista. 1



You will see a window similar to this one. 

Click the Start button in the bottom left corner of your screen.

Look at the items in the left hand column.

If you see Microsoft Office Excel 2007 in that box, you can open Excel from there. 

Click

and go to Step 5. If it is not there go to Step 3.



Click

A large window lists all the programs on your computer. You will see Microsoft Office on the list. 

Slide your cursor over to Microsoft Office and click

Enjoy… Microsoft Excel 2007

Page 7

Background to Excel 2007 Open Excel

Another window displays a list of Microsoft Programs. 

Click Microsoft Office Excel 2007.

Microsoft Excel will open and you will see a blank page with rows of commands above, lots of rows and columns in the middle, and a few rows of information below.

Page 8

Enjoy… Microsoft Excel 2007

Background to Excel 2007 Open Excel

Exercise: Open Excel 2007 from Windows Vista The following instructions are suitable for Windows Vista operating system. If you have Windows XP and have already opened Excel, go to the next exercise. 1



The Start Menu displays.

Click the Start button in the bottom left corner of your screen.

Look at the items in the left hand panel. If you see Microsoft Office Excel 2007 in that panel, you can open Excel from there. 

Click and go to Step 5.

If it is not there go to Step 3.

3 

Click

A large submenu lists all the programs on your computer. You will see Microsoft Office on the list. 

Slide your cursor over to Microsoft Office and click .

Another submenu displays a list of Microsoft Programs. 

Click Microsoft Office Excel 2007.

Enjoy… Microsoft Excel 2007

Page 9

Background to Excel 2007 Open Excel

Microsoft Excel opens and you will see a blank page with rows and columns.

Page 10

Enjoy… Microsoft Excel 2007

Background to Excel 2007 Guided tour of the Excel screen

Guided tour of the Excel screen The Window So let’s take a quick look at just what has opened on your screen.

Quick Access Toolbar

Tabs

Office Menu Button

Ribbon Cell Name Box

Formula Bar

Sheet 1 Worksheet area

Status Bar

Additional worksheets

Try to memorise these terms, but don’t worry too much. Test yourself at the end of Project 1 and if you haven’t remembered these terms by then, that is the time to make a serious effort to learn them. For most people, you will just pick them up as you go.

Enjoy… Microsoft Excel 2007

Page 11

Background to Excel 2007 Guided tour of the Excel screen

The Ribbon Across the top of your screen is the Ribbon. The Ribbon is designed to help you quickly find the commands that you need to complete a task. It has three components: 

Tabs that relate to a type of activity such as writing or laying out a page. Each tab is broken into a number of groups.



Groups are related commands displayed together.



Commands are the functions you will actually perform on your document.

When you first open Excel, the ribbon displays the Home tab options, which you will use to find commands related to formatting pages and paragraphs of text in your spreadsheet.

Navigation Tabs

Commands

Groups

The taskbar The taskbar is the long horizontal bar at the bottom of your screen. Unlike the desktop, which can get obscured by the windows on top of it, the taskbar is visible almost all the time. It will look slightly different depending on whether you are using XP or Vista, but it works the same in both operating systems. These captures were taken from two different computers so they have different programs, but essentially they work the same way. It has four main sections:

The Windows XP taskbar

Page 12

Enjoy… Microsoft Excel 2007

Background to Excel 2007 Guided tour of the Excel screen

The Windows Vista taskbar



The Start button in Vista or menu. See The Start menu (overview).



The Quick Launch toolbar,



The middle section, which shows you which programs and documents you have open, and allows you to quickly switch between them.



The notification area, which includes a clock and icons (small pictures) that communicate the status of certain programs and computer settings.

in XP, which opens the Start

which lets you start programs with one click.

Customise the taskbar It’s probably not a good idea to change taskbar settings at the moment, but when you are more familiar with Excel, or when you start to get annoyed at the way the taskbar or start menu is working, come back to them and experiment with changing the settings.

Enjoy… Microsoft Excel 2007

Page 13

Background to Excel 2007 Guided tour of the Excel screen

You can tell the taskbar to stay on top, or hide until you place your cursor at the bottom of your screen. 

Place your cursor in a blank area in the taskbar and right click.



Click Properties.

The Taskbar and Start Menu Properties dialog box displays. 

On the Taskbar tab, you can select how the entire bar behaves.



On the Start Menu tab you can select how the start menu behaves.



On the Notification Area tab, you can choose which icons to show.



On the Toolbars tab, you can select toolbars to display on the taskbar.

The down arrows Very often, when you are looking at a command on the Ribbon, you will see down arrows. These indicate that there are more options. There are three kinds of down arrow. A single down arrow to the right of a command opens a menu containing more options and sometimes more commands. In this book, we will call this the More down arrow.

A double down arrow to the right of a menu opens a menu containing more options and sometimes more Commands. In this book, we will call this the More down arrow. An angled down arrow called the dialogue box launcher opens either:

Page 14



a panel to the right or left of you work area (such as the Clipboard dialogue box) OR



a dialog box with the full menu (such as the Font dialog box shown here).

Enjoy… Microsoft Excel 2007

Background to Excel 2007 Guided tour of the Excel screen

The Worksheet grid, rows, columns and cells So, you have launched Excel, and you have a new, blank spreadsheet in front of you. You have looked at the components of the Excel window and at the most important part of the Excel window, the main worksheet area in the middle. The grid divides your worksheet into rows and columns that create cells. 

Rows go from left to right across the page and are labelled with numbers 1, 2, 3 etc.



Columns go from top to bottom of the screen and are labelled with letters A, B, C etc.



The boxes they create are called Cells and are the identified with the Column Heading, (for example B), plus the Row Heading (for example, 2). So the Cell Reference for the highlighted cell shown below is B2.

Do this now. Place your cursor in cell B2. You will see a black box around the cell indicating that this is now the Active Cell, and the label B2 appears in the Name Box above. This is called the Cell Address or the Cell Reference.

Columns Column A

Cell Address in the Name Box

Row Heading

Column Heading

Rows Row 5

Cell B2

Active Cell

A worksheet can be 16,384 columns wide and 1,048,576 rows deep, so it is unlikely that you will ever reach its limits. This adds up to 17,179,869,184 cells and every cell can hold up to 32,000 characters. On top of this, every spreadsheet file can hold a virtually unlimited number of worksheets – more about this later. So let’s get started.

Enjoy… Microsoft Excel 2007

Page 15

Create a budget Create a new spreadsheet

3: Create a budget Create a new spreadsheet For project 1, you are going to create a budget. This can be a personal or a business related budget.

Exercise 1 – Type your cell labels You can type either text or numbers into a cell. If you type text into a cell it is called a cell label and does not calculate. 1



Place your cursor in cell number B2 so that it has a black outline around it. This is the active cell.



Type the words Monthly income.

You will see the letters appear in two places, in the cell into which you have been typing and in the Formula Bar. 

Click your cursor into another cell.

Your words Monthly income show in the cell but they are wider than the cell. There are two ways you can fix this: 

You can set your worksheet up so that text wraps in cells.

OR 

You can widen the column so the text fits.

We will look at both of these options now.

Page 16

Enjoy… Microsoft Excel 2007

Create a budget Create a new spreadsheet

Exercise 2– Make the text wrap in the cells The word “wrap” is used when an automatic setting in a computer decides to end a line and start a new line. You can set Excel so that when you have typed more into a cell than it can take, the text is “wrapped” onto the next line. 1

Select the entire worksheet 

Click on the corner square.

Your entire worksheet should change from white to a pale blue, and the row and column labels should change to a darker blue. This indicates that you have selected the entire sheet.



Make sure the Home tab, shown above, is selected.



Look along the Home tab to find the Alignment group.

If you have a wide screen, it will look like the first picture. If you have a narrower screen, it will look like the second picture.

Click on

Look at the words Monthly income. They are now “wrapped” so that they fit into the cell. Also the row has been made deeper to fit the two rows of text.

Enjoy… Microsoft Excel 2007

Page 17

Create a budget Create a new spreadsheet

Exercise 3– Change column width However, you could also have decided to change your column width so that the column is wide enough for the two words. 1



Place your insertion point on the edge of the Column B, so that it looks like the cursor in the picture.



Hold down your left mouse button and drag to the right.

The column should widen as you drag. If you don’t know how to drag using your mouse you should do more of the Microsoft on-line training. Go to What you need to know before you start, page 5. 2



Drag it back and forth until the column is just wide enough to fit the words Monthly Income on a single line.

You can do this any time you want to change a column width to either fit text or wrap text.

The row has remained at the greater height. To return it to the default row height: 

Click on Home tab, Cells group, Format .



Click

The row height changes back to the default row height.

Exercise 4 – Type your data Now let’s type in some income and budget items. 1

Page 18



Place your cursor in cell number B4 so that it has a black outline around it.

Enjoy… Microsoft Excel 2007

Create a budget Create a new spreadsheet

Type in your income 

Type the name of a source of income in cell B4.



If you have two or more sources of income, type the second name in cell B5, the third in B6 etc. In this example, there are three sources of income.



Then in column C, type in your Net MONTHLY income from each source.

Net income is the cash you have left in your pay packet after tax, superannuation and other automatic deductions have been taken out.

Type in your expenses 

Place your cursor in a cell B8 (unless you have more than three sources of income).



Type the word Expenses.



In the next step you will type all your expenses under your heading Expenses.

For now, use the following list even if you have one of your own.

Note that you can delete text using the delete key or the backspace key, the same way you do Don’t worry if you have expenses that for word processing. are not on the list or do not have some that are on the list. You will fix that in a later exercise.

Enjoy… Microsoft Excel 2007

Page 19

Create a budget Create a new spreadsheet

Page 20

Type each of the following under Expenses, starting with cell B10.

You will finish with a sheet that looks something like this.



Rent/mortgage



Other lease / loan repayments



Interest payments



Home maintenance



Rates & levies (council, water, etc)



Phone(s) (rental, service & calls)



Utilities (electricity, gas, etc)



Food / groceries / household



Medical & pharmaceutical



Clothes



Child care, school fees



Household purchases (eg. appliances)



Other household



Car registration



Car maintenance / repairs



Fuel



Fares



Other transport



Life / income continuance / trauma insurance



Health insurance



Superannuation (if paid by self)



Home & contents



Car



Business



Other (Boat / Caravan / Trailer) insurance



Holidays



Restaurants / theatre etc



Sports / hobbies / memberships



Newspapers / magazines / books / CDs



Other recreation



Once you have entered all the budget items in Column B, type values in Column C. For the moment, we don’t necessarily want correct values, just any values.



Drag your column width around or make it wrap so you can read your sheet comfortably.

Enjoy… Microsoft Excel 2007

Create a budget Create a new spreadsheet



Also, add some labels in Column A for each of the groups of items in Column B as shown below.

Cell Labels and Cell Values At this stage you have two kinds of entry: 

Cell Labels – these are the words you have typed in.



Cell Values – these are the numbers you have typed in. Eventually you will enter formulas so that you can start to calculate totals.

Enjoy… Microsoft Excel 2007

Page 21

Create a budget Save, close and open your spreadsheet

Save, close and open your spreadsheet We are going to do a lot more work on this spreadsheet, but it is a good idea to save it so that you don’t lose any work you have already done. If it is saved and you make a mess of the copy you are working on, you can find your saved copy and open it again. You need to know two things before you can save a document: 

What you are going to call it?



Where you are going to put it?

If you have not learned about how files are stored on computers please take the online training supplied by Microsoft. Go to What you need to know before you start, page 5.

File names When you name a file there are two things you need to consider. 

You should give it a name you will be able to find again later once you have lots of files on your computer.



You should follow the rules governing how files are named in a Microsoft environment. You will learn more about this in a later exercise.

File locations There are numerous locations to which you can save a file. 

If you are working on your own computer, you may want to save it to an appropriate place in your own folder structure OR



Select Drive (D:) if you are saving to a floppy disk (your floppy disk drive may be mapped to a different drive letter) OR



Select Removable Disk (E:) if you are saving to a flash drive (your removable memory device may map to a different drive letter).

Page 22

Enjoy… Microsoft Excel 2007

Create a budget Save, close and open your spreadsheet

Exercise 5– Save your spreadsheet using the Save As function in Windows XP 1 

Click the Office button



Click

in the top left hand corner.

2 .

You are offered a list of optional formats. 

Click Excel Workbook.

The Save As dialog box displays.

Enjoy… Microsoft Excel 2007

Page 23

Create a budget Save, close and open your spreadsheet



Click the folder where you want to save the file.

A list of possible locations displays.

Save to an appropriate location.



If you are working on your own computer, you may want to save it to an appropriate place in your own folder structure OR



Select Drive (D:) if you are saving to a floppy disk (your floppy disk drive may be mapped to a different drive letter) OR



Select Removable Disk (E:) if you are saving to a flash drive (your removable memory device may map to a different drive letter).



In the File name field, type a name for your file, something like “YourNameBudgetV1”. Type your own name, the word “Budget”, and “V1” indicating version 1 – all without any spaces between the words, and using capitals to separate the words.

The Save as type should default to (*.xlsx) or to (*.xlsm). This indicates that you are saving an Excel 2007 document. 5



Click Save.

Your document is saved, but remains open on your screen. Do not close it at this stage. 6



Write your document name here – and also the location where you saved the document – you will need to remember both your document name and location. Your path will look something like this if you are saving to a removable memory device. E:\YourNameBudgetV1

_____________________________________________________________________ _____________________________________________________________________ _____________________________________________________________________

Page 24

Enjoy… Microsoft Excel 2007

Create a budget Save, close and open your spreadsheet

Exercise 6 – Save your spreadsheet using the Save As function in Windows Vista 1 

Click the Office button



Click

in the top left hand corner.

2 .

You are offered a list of optional formats. 

Click Excel Workbook.

The Save As dialog box displays. 

If no folder list is showing, click Browse Folders.

Enjoy… Microsoft Excel 2007

Page 25

Create a budget Save, close and open your spreadsheet



Select Documents from the User drop down list.



The Save as type should default to (*.xlsx) or to (*.xlsm). This indicates that you are saving an Excel 2007 document. 5



Click Save.

Your document is saved, but remains open on your screen. Do not close it at this stage. 6



Page 26

Enjoy… Microsoft Excel 2007

Create a budget Save, close and open your spreadsheet

Close your spreadsheet If you do not want to finish your work in a single session, you can save your document and close it.

Exercise 7 – Close your spreadsheet 1 

Click the Office button



Click Close

2 .

If you have made any changes since you last saved your document you will be offered the option to Save. 3



Click Yes.

Your document is removed from your screen. You will find it again in the location you originally saved it to.

Exercise 8 – Open your spreadsheet 1



Open Excel if it is not already open on your screen.



Click the Office button



Click Open

Enjoy… Microsoft Excel 2007

Page 27

Create a budget Save, close and open your spreadsheet

The Open dialog box displays.



Click the down arrow

to the right of the Look In field.

A list of possible locations displays.



Find the location to which you saved your file – if you have forgotten where, look back to Exercise 5– Save your spreadsheet, page 23.



A list of documents in that location displays.



Highlight your saved spreadsheet.



Click Open.

Your spreadsheet re-displays on your screen.

Page 28

Enjoy… Microsoft Excel 2007

Create a budget Enter a range of dates using AutoFill

Enter a range of dates using AutoFill So far, you have entered your budgeted values. Now you need to set up columns to keep track of your actual expenditures.

Exercise 9 – Enter a range of dates using AutoFill 1



Type the word January in cell E2.



Place your cursor on the bottom right of the cell so that it turns into a plus sign. This is called the fill handle.



Press down your left mouse button and drag to the right. If you are doing it correctly, you will see the months display as you drag along to the right.



Continue until you see December then release.

All the cells will be filled in for you.

Insert and delete rows Do you have an expense that is not on this list, or did you find one or more expenses on the list that you don’t have? We will now fix the list so that it is right for you.

Exercise 10 – Insert rows First, let’s look at how to insert additional rows. In this case, we want an additional row for home renovations, as this is distinct from normal home maintenance. 1



Identify the best place to insert your row. I have chosen to place the row below Home maintenance.



Place your cursor on the row BELOW the position at which you want to insert your new row.

Enjoy… Microsoft Excel 2007

Page 29

Create a budget Insert and delete rows



Make sure the Home tab is selected.



Look along the Home tab to find the Cells group.

If you have a wide screen, it will look like the first picture. If you have a narrower screen, it will look like the second picture. 3



Click on the down arrows for



Click

. The menu below displays.

A new row is added ABOVE your highlighted row.

Page 30



Type in the budget item you want to place in that position.



Do this wherever you want to add your own item to the list.

Enjoy… Microsoft Excel 2007

Create a budget Insert and delete rows

Exercise 11 – Delete rows Are there expenses on the list that you don’t have? We will now delete those from the list.



Place your cursor on a row for an expense that you do not have.

For example, in some States, if you are renting you will not have rates. If you don’t have a car, you won’t have Registration and Fuel, but you will probably have other types of transport expenses. 2



Make sure the Home tab, shown above, is selected.



Look along the Home tab to find the Cells group.

If you have a wide screen, it will look like the first picture. If you have a narrower screen, it will look like the second picture. 3



Click the down arrow next to or under the Delete command. The following menu displays.



Click

Enjoy… Microsoft Excel 2007

. The entire row, including the text, is removed.

Page 31

Create a budget Insert and delete columns

Ooops, you made a mistake and didn’t really want to delete that row at all! Well, that is easy to fix. 

Press

on your keyboard to undo your last action.

No, its OK, you did want to do that after all. 

Press

on your keyboard to redo your last action.

These are called Key Combination Shortcuts and you will learn more about them in Key Combination Shortcuts, page 67.

Insert and delete columns You can insert and delete columns in the same way you inserted and deleted rows. You only have to remember to place your cursor in the column AFTER the position in which you want to insert the new column.

Exercise 12 – Insert and delete columns 1

Page 32



Place your cursor in any cell in column C.



Alternatively, you can highlight the Column Heading C itself to select the entire column.



Make sure the Home tab, shown above, is selected.



Look along the Home tab to find the Cells group.



Click the down arrow next to or under the Insert command. The following menu displays.

Enjoy… Microsoft Excel 2007

Create a budget Insert and delete columns



You don’t really want this column so you will delete it.

Click

. A new column is added.



From the Home tab, Cells group, click the Delete down arrow.



With your cursor anywhere in the new column C, click

The entire column is removed. 

Using the skills learned so far, complete your budget by deleting all items you don’t want, and adding items you do want. You still don’t need to worry about accurate figures at this stage.

Save your spreadsheet again Whenever you are doing something to a document that you might regret, you should save it before you make the risky changes. That way you can always re-open the original if you mess things up.

Exercise 13 – Save spreadsheet with Save function 1 

Click the Office button



Click

2 .

Your document re-saves behind the scenes but remains open on your screen. 

If you want to end your session, follow the instructions in:  Exercise 7 – Close your spreadsheet, page 27 to close your spreadsheet, and  Exercise 8 – Open your spreadsheet, page 27 when you want to find and open it again.

Enjoy… Microsoft Excel 2007

Page 33

Create a budget Navigation options

Navigation options We will take a short break from working on our spreadsheet to look at how we move around. So far, the only instruction you have been given is to click on a cell using your mouse, but there are other ways of moving around using your keyboard or your mouse.

Exercise 14 – Move around in a spreadsheet There is no right or wrong way to navigate around Excel. Eventually you will find the way that suits you best. 1

The Name Box You can use the Name Box to find a particular cell. 

Type B34 in the Name Box and see where you are taken.

You should have been taken to cell B34.

The Scroll Bars 

Practice moving up and down your worksheet using the scroll bar.

The Scroll wheel on your mouse 

Practice moving up and down your worksheet by rolling the scroll wheel on your mouse.



You can also move up or down by clicking down on the scroll wheel.  Click the wheel hard. You will see a single or double headed arrow and your page will start scrolling.  When your screen starts scrolling, rotate your mouse slowly to change the direction of the scroll.  You can stop the scrolling by rotating the mouse until the double headed arrow appears.

Keyboard keys 

Page 34

With your spreadsheet open, practice using the following keyboard keys to move around in your worksheet.

Enjoy… Microsoft Excel 2007

Create a budget Navigation options

Press these keyboard keys

OR these

Move one cell to the right Move one cell to the left Move one row down Move one row up Go to the last entry at the right of your worksheet Go to the first entry at the left of your worksheet Scroll down the page, one window at a time Scroll up the page, one window at a time Return to cell A1 Move to bottom right corner Move to next worksheet Move to previous worksheet Move to the next text currently entered in a row, or move to the end of the row Move back to the previous text currently entered on a row, or move to the start of the row Move to the next row of text currently entered in the column, or move to the end of the column Move back to the previous text entered in the column, or move to the start of the column Move across one window to the right Move across one window to the left

If you would like to reinforce what you have just learned, now is a good time to complete the Microsoft On-line Training – Get to know Excel 2007: Create your first workbook. This revises the basics of what a spreadsheet and worksheet are.

Enjoy… Microsoft Excel 2007

Page 35

Create a budget Use and test formulas

Microsoft On-line Training – Get to know Excel 2007: Create your first workbook Go to http://office.microsoft.com/training/training.aspx?AssetID=RC10076651 1033

Use and test formulas Use formulas to calculate values Now we get to the true power of the spreadsheet – using it as a sophisticated calculator. So far, you have typed values into cells, and if your values were weekly or fortnightly you had to calculate the monthly value yourself. Now we will get Excel to do all that for you, and more. For this exercise, go through your bills at home and find copies of bills that are: 

weekly



monthly



quarterly



annual

Rather than having to work out all the monthly totals manually, we will get the spreadsheet to do that for you.

Exercise 15 – Multiply and divide For simple calculations, you use the = sign. 1



Enter weekly value

Page 36

If your spreadsheet is not open, follow the instructions on Exercise 8 – Open your spreadsheet, page 27.



Find any value in your spreadsheet for which you know the accurate weekly value. In this example, I am picking the 120 per week rent.



Click on that cell.



Type = to tell Excel it has to calculate.



Type 120 for your weekly rent.



Type * to tell Excel to multiply.



Type 52 for the number of weeks in a year.



Type / to tell Excel to divide.



Type 12 for the number of months in a year.

* means multiply / means divide

Enjoy… Microsoft Excel 2007

Create a budget Use and test formulas

4 

Press

The value of 520 displays in the cell, which is your monthly rent, and the formula displays in the Formula Bar. 5

Enter quarterly value 

Now find any value in your spreadsheet for which you know the accurate quarterly value. Quite often electricity bills, and sometimes phone bills, are quarterly.



Click on that cell.



Type = to tell Excel it has to calculate.



Type the quarterly value, in this case 350.



Type a forward slash / to tell the formula to divide.



Type 3 to get a monthly value.



Press

7 .

The value of 116.667 displays in the cell, which is your monthly value, and the formula displays in the Formula Bar. 8



Now follow the same instructions for an annual bill. This might be rates, or car or household insurance.



This time, enter the value and divide by 12.



Now you can complete all the values in your worksheet.  Try to put sensible values in each cell. For some you will calculate to get a monthly value, and for some you will simply type in the monthly value.  Put a value in every cell, even if the value is zero.

The rules of precedence Excel doesn’t necessarily process your formulas from left to right. mathematical rules to determine which function it will perform first.

It uses standard



Brackets (Parentheses). Calculations within parentheses are performed first.



Percent



Exponents



Division and Multiplication



Addition and Subtraction

When a formula contains operators of equal precedence, for example add and subtract, it calculates them from left to right.

Enjoy… Microsoft Excel 2007

Page 37

Create a budget Use and test formulas

Use formulas to calculate totals Exercise 16 – Total a range of cells You can add a range of cells to get a total, so we will add some of your expenses to get total expenses – it doesn’t matter if they are not all correct at this stage. 1



Place your cursor in column D next to Other (household expenses).



On the Home tab, Editing group, find and click the down arrow next to AutoSum.

This menu displays. 

Click

The formula above displays in the cell you selected, and there is a flashing square around the neighbouring cell in column C. 

Place your cursor on the value in column C next to Other.



Hold down your left mouse button.



Drag up to Rent/Mortgage.

The flashing line is now around all the cells in the range. The formula now says =SUM(C10:C22) or something similar. You can break this formula down to work out what it is doing.

Page 38



= means the formula is asking for a value that is “equal to”



SUM is the mathematical function meaning calculate, so this formula will calculate values.



(C10:C22) is the range of cells that the formula will add.

Enjoy… Microsoft Excel 2007

Create a budget Use and test formulas

You can also see this formula in the Formula Bar above.



When you have the range selected correctly, press

A value displays where the formula was in the spreadsheet. 

Check this value to make sure it is the correct total for your monthly household expenses.



Click on the value. Even though the calculated value shows in the cell, the formula still shows in the Formula Bar.

TIP You can edit a formula in the Formula Bar manually any time you like.

Exercise 17 – Set sheet to re-calculate automatically Each time you change one of your values, your calculation should recalculate automatically. If it does not, it is because your spreadsheet has been set not to.



Make sure the Formulas tab, shown above, is selected.



Look along the Formulas tab to find the Calculation group.

If you have a wide screen, it will look like the first picture. 2

If you have a narrower screen, it will look like the second picture. 

Click Calculation Options.



Click Automatic so that it is ticked as shown.

Enjoy… Microsoft Excel 2007

Page 39

Create a budget Use and test formulas

TIP If you find that your spreadsheet becomes very slow and recalculates very slowly, this will be because one or more of your formulas is faulty. You should check all your formulas to see if you can find the problem.

Understanding relative and absolute cell references Exercise 18 – Identify and copy a relative cell references When you copy a formula from one cell to another, cell references in the formula will change. Excel attempts to help you by automatically updating cell references as they are moved or copied from one location to another.



Look in your spreadsheet at your calculation for Household Expenses, Other.



Select the cell in column D in which you calculated the total of your household expenses. This contains relative cell references, in this case, C10 and C22.



Make sure the Home tab, shown above, is selected.



Look along the Home tab to find the Clipboard group.

If you have a wide screen, it will look like the first picture. If you have a narrower screen, it will look like the second picture. 3



Click

A flashing line displays around the cell. This tells you that it has been copied into memory and Excel is waiting to paste it somewhere for you.

Page 40

Enjoy… Microsoft Excel 2007

Create a budget Use and test formulas



Click the cell beside it in column E.



Click

The formula is copied into that cell, but now it is guessing that you want to add the range D10:D22. Sometimes the guess is right for what you want to do and sometimes it is wrong – you will see an example of a wrong guess in the next exercise. 

You do not need this formula so click on the cell and press the

key.

Exercise 19 – Enter and copy an absolute cell reference Most of the time these automatic changes to a copied formula are exactly what you want, but sometimes you do not want to have the cell references changed. To stop Excel changing them, you use absolute references. An absolute cell reference is labelled by $column and $row. 1



Look again in your spreadsheet at your calculation for Household Expenses, Other.



Click the cell in column D in which you calculated the total of your household expenses.



Click in the Formula Bar, and highlight your formula.



Press F4 as many times as you need to see the range of legal options for cell references and select the one you require.



Press

Now we will copy and paste this formula but by using a different technique.

Enjoy… Microsoft Excel 2007

Page 41

Create a budget Use and test formulas



Click the cell in column D in which you calculated the total of your household expenses.



Click your right mouse button once.

A menu displays. 

Left click Copy.



Click the cell beside it in column E.



Click your right mouse button once.



When the menu displays, left click Paste.

You have copied your formula, but this time, when you look at it in the Formula Bar you will see that the cell references have remained the same. 

You do not need this formula so click on the cell and press the

key.

Exercise 20 – Copy a formula to another cell and modify Now you will do the sub totals for transport expenses, insurances and recreation. You can do this by copying your original formula for household expenses and then editing it. 1



Select the cell in column D in which you calculated the total of your household expenses.



Identify the Home tab, Clipboard group.



Click

A flashing line displays around the cell. This tells you that it has been copied and Excel is waiting to paste it somewhere for you.

Page 42

Enjoy… Microsoft Excel 2007

Create a budget Use and test formulas



Select the cell in column D next to the “Other” entry of Car Expenses.



Click

The formula is copied into that cell, but it is not quite right yet. 5



Look in the Formula Bar, and you will see that the cell range is not correct.



Click in the formula.



Delete the first incorrect number and replace with the first row number for Car Expenses, in this case, 23.



Remember you can use the Delete the second incorrect number and replace delete key or the backspace key with the last row number for Car Expenses, in this to delete. case, 26.



Look down to your worksheet and it will look something like this.



Press



Check your calculation to see that it is correct.



Complete the same step for the Insurances and Recreation groups, and any additional groups you may have created, so that you have subtotals for every group.

Exercise 21 – Enter a Range of formulas using AutoFill You can also copy a range of formulas in the same way that you entered the range of dates. 1



Select a cell in column C two rows below your last row.



On the Home tab, Editing group, find and click the down arrow next to AutoSum.

Enjoy… Microsoft Excel 2007

Page 43

Create a budget Use and test formulas

This menu displays. 

Click

The formula above displays in the cell you selected, and there is a flashing rectangle around the cell range in column C above the cell you selected. This should be around all your expenses in column C. The flashing square will stop when it reaches a gap – this is why you were told to enter values in every cell. 

If there is an empty cell, hold down your left mouse button and drag the range to the top of your values – probably to rent or mortgage.



Press



Click back onto the total so that the black line displays around it.



Place your cursor on the bottom left of the cell so that it turns into a plus sign.



Press down your left mouse key and drag to the right until you get to column P for December.

Now there will be a formula on every column so that when you start entering your actual monthly values, they will calculate automatically. 8

Page 44



Check the value in column D. if you did your subtotals correctly, it should be identical to the value in column C.

Enjoy… Microsoft Excel 2007

Create a budget Use and test formulas

Exercise 22 – Calculate monthly debit or credit Now you will enter one last formula to add up your income, subtract your expenses to get a monthly savings or a monthly debit. 1

Select a cell in column C two rows below your total.



On the Home tab, Editing group, find and click the down arrow next to AutoSum.



Click



In the Formula Bar, edit the formula so that you have the cell or the range of cells for you income. In this case, we have the range C4:C6.



Type a minus outside the brackets.



Type an opening bracket.



Click on your expenses total in column C. The cell reference will be placed automatically in the formula and the second bracket will appear automatically.



Press

If you are earning more than you are spending, you will have a plus value in that cell. If you are spending more than you are earning, you will have a minus value in that cell. 5

To save your spreadsheet:



Click the Office button



If you want to end your session, follow the instructions in:

, click

 Exercise 7 – Close your spreadsheet, page 27 to close your spreadsheet, and  Exercise 8 – Open your spreadsheet, page 27 when you want to find and open it again.

Enjoy… Microsoft Excel 2007

Page 45

Create a budget Use and test formulas

Set regional options in Windows The next thing we are going to do is enter time and date, but to do this correctly we have to make sure your version of Windows is set up correctly.

Manage Excel 1 – Set regional options in Windows XP You probably know that different countries abbreviate their dates in different ways. Microsoft products default to the American format, mm/dd/yy where you may have a different one, for example, Australia uses dd/mm/yy. 1



In Windows XP, click the Start button in the bottom left corner of your screen.



Search the various panels in your Start Menu to find Control Panel.



Click on



It can display in two different ways depending on the selection made last time someone used it.



Click and the two different views. Finish with the view shown below.



Scroll down the page until you find

Page 46

to see

Enjoy… Microsoft Excel 2007

Create a budget Use and test formulas



Double click



Click the down arrow on this field.

A full list of languages displays. There are many differences in how the English language is used in different countries, so you will see lots of different “English” options. 

Make sure to select the correct version of English for your country.

Your selection will control how each of the items on the Samples list displays.



Also make sure your country is selected for Location.

Set regional options in Windows Vista 1



In Windows Vista, click the Start button



Search the various panels in your Start Menu to find and click on Control Panel.

Enjoy… Microsoft Excel 2007

Page 47

Create a budget Use and test formulas

By default the Control Panel displays the Category View.



Click Classic View to see the list of all Control Panel items not clustered into categories.



Click Control Panel Home to return to Category View.



Open Control Panel, Classic View. (This is View, Medium Icons.)



Scroll down the page until you find

Page 48

and double click to select.

Enjoy… Microsoft Excel 2007

Create a budget Use and test formulas



Click the More down arrow on the Current format: field.

A full list of languages displays. There are many differences in how the English language is used in different countries, so you will see lots of different “English” options. 

Select the correct English for your country which wil also define how numbers, time and dates are displayed.



If you want to change any of the displays shown for your language option, click .

You are offered a full range of Numbers, Currency, Time and Date options to select from. 

Click



Also make sure your country is selected for Location.

to save any changes you have made or

to exit.

Exercise 23 – Enter time and date This particular spreadsheet does not really call for time and date, but we will put them in so that you can see how they work. 1

Enter Date 

In any cell, type a date with either forward slashes or dashes.  31/10/2009 or 31-Oct-2009



Delete when you have seen how it works.

2 In cell A1, enter today's date by pressing CTRL and semicolon (;) together.

Enjoy… Microsoft Excel 2007

Page 49

Create a budget Use and test formulas

Enter Time 

In any cell, type the numbers using a colon between the hours and minutes, a space, and then "a" or "p":  8:00 – Excel will recognise this as 8 AM.  8:00 p – Excel will recognise this as 8PM.



Delete when you have seen how it works.



In cell B1, enter the current time, by pressing CTRL, SHIFT, semicolon together.

Format Date and Time



Look along the Home tab to find the Number group.



Click on the down arrow

to the right of General.

This menu displays. 

Select cell A1.



Click through the following options to see what happens to your date:  Short Date  Long Date (If you get this

, drag your column to widen it. 

Select cell A2.  Click Time to see what happens to your date and time.

Page 50

Enjoy… Microsoft Excel 2007

Create a budget Use and test formulas

Exercise 24 – Enter fractions and other numbers This particular spreadsheet does not call for fractions or negative numbers, so just experiment with these in any empty cell. 1

Enter fractions 

In any cell, type a zero followed by a space, followed by the fraction, for example:  0 1/2.  2 3/4

If you enter 1/2 without a number before it, Excel will interpret the number as a date, January 2. You must always have a number even if it is a zero. Leave this formula where it is for the moment. 2

Enter negative numbers 

In any cell, for minus 100, type (100).



Press

Excel displays this as -100. Leave this formula where it is for the moment.

The Home tab, Number group It is very easy to format numbers in Excel.

Exercise 25 – Format numbers 1

Format numbers 

On the Home tab, Number group, click the down arrow to the right of General.

Enjoy… Microsoft Excel 2007

Page 51

Create a budget Use and test formulas

This menu displays. 

Select one of your fractions.



Click through the following options to see what happens to your fraction:  General  Currency  Accounting  Percentage  Fraction  Scientific



Highlight column C and apply the Accounting format.



Highlight column D and apply the Accounting format.



If you get this in any cell column to widen it.

, drag the

With any number highlighted, click your way through the following buttons to see what they do. 

Click the down arrow to the right of for the currency menu and see what they each do.



Click



Click to place a comma after the thousand and change the format to Accounting without the $ symbol.



Click

to add successively more decimal points.



Click

to remove decimal points one at a time.

to display percentages (watch your fractions when you do this).

When you have finished, delete your fractions and negative numbers. 5

To save your spreadsheet:



Page 52

Click the Office button

, click

Enjoy… Microsoft Excel 2007

Create a budget Use and test formulas



There are lots more complexities you could choose to add and you may want to change the layout to suit yourself, but essentially, you now have a working budget. In the next section, you will learn how to format your spreadsheet so that it is easier to read. For now, however, you may want to complete the Microsoft On-line Training – Get to know Excel 2007: Enter formulas, to reinforce the basics of working with formulas.

Microsoft On-line Training – Get to know Excel 2007: Enter formulas http://office.microsoft.com/training/training.aspx?AssetID=RC10074568 1033

Enjoy… Microsoft Excel 2007

Page 53

Manage the way things look in Excel Pin a spreadsheet to the Office Menu

4: Manage the way things look in Excel Excel has a great many settings. Experienced users learn to set these settings in order save lots of time fixing things later. Periodically we will walk you through Manage Excel exercises. These exercises can be done at any time, but must be done before the projects that follow. In other words, you could do them all up front if you wanted, but you can’t leave them all till last. If you tried to do this, some of your settings would be wrong for the exercises.

Pin a spreadsheet to the Office Menu Are you tired of searching through folders to find your document? Well, there is a quick way. When you select the Office menu, you may have noticed a list of Recent Documents in the right panel.

Manage Excel 2: Pin a spreadsheet to the Office Menu 1 

Click the Office button

The Office menu displays. Excel displays the most recent documents you have been editing in the Recent Documents list. From here, you can click the name of the document to open it, which can save you time looking for your document.

Page 54

Enjoy… Microsoft Excel 2007

Manage the way things look in Excel Customise the Quick Access Toolbar

You can also “pin” a document so that it stays in the list. 

Find the document you are currently working on and click the faded image of a pin.

It becomes green like the circled pin in the previous image. That document will stay on the list until you untick it. 

If there are any documents already showing a green pin, click them to unpin them.

Customise the Quick Access Toolbar The Quick Access Toolbar is a customizable toolbar containing commands that you want to see all the time, no matter what navigation tab is currently displayed. When you first start up Excel 2007, the Quick Access Toolbar is located in the upper-left corner next to the Microsoft Office button.

Enjoy… Microsoft Excel 2007

Page 55

Manage the way things look in Excel Customise the Quick Access Toolbar

The default items on the Quick Access Toolbar The Quick Access Toolbar has four common commands on it by default. File Save Undo the last action

Redo the last action

Move and customise the Quick Access Toolbar You can move the Quick Access Toolbar to a position below the Ribbon. You can also collect all the commands you commonly use and place them on this toolbar. That way you can reduce the number of keystrokes it takes to perform the command.

Manage Excel 3: Customise the Quick Access Toolbar 1 

With a spreadsheet open, click the Office button

, click Excel Options

Page 56



The Excel Options dialog box displays.



Click Customize.

Enjoy… Microsoft Excel 2007

Manage the way things look in Excel Customise the Quick Access Toolbar



On the left of the right hand panel, ensure Popular Commands is selected.



Scroll down the list and highlight Delete Sheet Columns.



Click the Add button.

Now Delete Sheet Columns is added to the right column. 6



Scroll down the list and highlight Delete Sheet Rows.



Click the Add button.

Now Delete Sheet Rows is added to the right column. 8



Tick the Show Quick Access Toolbar below the ribbon check box.

This will move the toolbar to the location below the ribbon. 9



You are returned to your spreadsheet, but now you will see a new bar underneath your ribbon that looks something like this.

Click

Remember this process – over time you will find commands that you want to use regularly and that will be quicker to use if you add them to this bar. 11



Run your cursor over each of these icons to display the screen tips and shortcuts.

Enjoy… Microsoft Excel 2007

Page 57

Manage the way things look in Excel Customise the Quick Access Toolbar

Manage Excel 4: Add and remove commands on the Quick Access Toolbar – the quick way Microsoft always seems to have more than one way of doing something. 1



With Excel open, cruise around the commands you have used so far and identify another one you think you might like on your Quick Access Toolbar.



Right click on that command.

This menu displays. 

Click Add to Quick Access Toolbar.

Your command will have appeared as an icon at the right hand end of your Quick Access Toolbar. 3

You can remove a command in the same way 

Right click on that command you want to remove.

This menu displays. 

Click Remove from Quick Access Toolbar.

You can also access the Excel Options, Customize menu from here. 

Click anywhere in the blank area of the Quick Access Toolbar



Click Customize Quick Access Toolbar.

You are taken to the Excel Options, Customize menu where you can add, remove and re-organise your Quick Access Toolbar icons.

Page 58



To arrange them in the order you want:

Take the time to get the commands you think you might need onto the Quick Access Toolbar.



Highlight the item you want to move.



Click

to move the highlighted item up the list.



Click

to move the highlighted item down the list.

Enjoy… Microsoft Excel 2007

Manage the way things look in Excel Minimise the Ribbon

Manage Excel 5: Work with Quick Access Toolbar icons (2) 1

Run your cursor over a range of commands to display the screen tips. 

Read the explanations.



Note the keyboard shortcut.

Minimise the Ribbon We have done a lot of work navigating around using the Ribbon, but sometimes it takes up too much of the screen, particularly when you are working with graphics, so it can be useful to be able to minimise it.

Manage Excel 6: Minimise the Ribbon 1



With a document open, right click in the Menu bar that shows the tabs.



Click on Minimize the Ribbon from the drop-down menu.

The Ribbon has disappeared, just leaving the Menu bar visible. 3



Right click on the Menu bar again.

You will see a tick next to Minimize the Ribbon.



Left click on it again. The Ribbon redisplays.



Alternatively, you can double click on the active tab to toggle between minimise and maximise.

The View tab (Alt W) Below is the View tab. You will use this tab to look at your document in a variety of ways.

Enjoy… Microsoft Excel 2007

Page 59

Manage the way things look in Excel The View tab (Alt W)

It is made up of the following groups of commands: 

Workbook Views group



Show/Hide group



Zoom group



Window group



Macros group

Workbook Views Workbook Views change the way the entire document looks on-screen, but they do not change the document itself. You may think “a document’s a document”, but things can look quite different depending on the view that you select. When you change your view of a page, you can often see things better, and work better. They key is to select the right view for the job you are doing. Once you get used to switching views, you’ll find yourself doing it often.

Manage Excel 7: Explore Workbook Views group 1

With a spreadsheet open: 

Click the View tab, and find the Document Views group.

(To exit this Full Screen view, press the Esc key.) 2



Click on each of these icons in turn and see what they do to your document.

You will find that you mostly work in Print Layout view when you are building documents, but will find the others useful for specific purposes.

Manage Excel 8: Explore Show/Hide group You can also display tools that will help you with formatting tasks. The Show/Hide commands show or hide formatting tools like rulers, gridlines, document map or document thumbnails. You may only want to use these features when you are performing a particular formatting action on your document. 1

Page 60



With a spreadsheet open, click the View tab, and find the Show/Hide group.

Enjoy… Microsoft Excel 2007

Manage the way things look in Excel The View tab (Alt W)



With Workbook Views, Normal view selected, tick each of these checkboxes in turn to see what they do to your spreadsheet.



With Workbook Views, Page Layout view selected, tick the Ruler checkbox to see what happens to your spreadsheet.

Manage Excel 9: Explore Zoom group Zoom lets you choose between a close-up of your document and a distance view. Again, you are likely to use these features when you are performing a particular formatting action on your document. When you are fiddling with some small detail, you will zoom in so that you can see the detail more easily. When you want to scroll quickly through your document to find something in particular, you may want to zoom out. 1

With a spreadsheet open: 

Click the View tab, and find the Zoom group.



Click on each of these icons in turn and see what they do to your document.

Set Zoom using the Status Bar slide In addition to the Zoom tools on the Ribbon, Document View, you can also find the Zoom tools on the Status Bar. The Status Bar is the second bar from the bottom of your screen. You will see the zoom tools at the right hand end.



Check out the



Use the slider in between the plus and minus symbols to select an exact zoom.

Enjoy… Microsoft Excel 2007

(Zoom In) and

(Zoom Out) buttons, OR

Page 61

Manage the way things look in Excel The View tab (Alt W)

If there are no zoom tools visible: 

Right click on an empty area of the Status Bar.



Tick Zoom.



Tick Zoom Slider.



Click back into your spreadsheet.



Zoom out by clicking the your screen – about 50%.

symbol until you can see your entire spreadsheet on

Manage Excel 10: Explore Window group On the View tab, the Window group lets you arrange your document into multiple views, or to arrange multiple documents. 1

With a document open: 

Click the View tab, and find the Window group.



Click on New Window.

You may want to use two windows if you are working in a long document and needing to refer to two different areas in the document. If you have two monitors, you can place one window on each monitor.

Page 62

Enjoy… Microsoft Excel 2007

Manage the way things look in Excel Minimise and maximise the Excel window

Minimise and maximise the Excel window In addition to all this, you can also change the size and shape of the Window displaying Excel itself. You can minimise the window or maximise the window.

Manage Excel 11: Minimise and maximise These icons will look slightly different depending on whether you are working with Windows XP or Vista, but they work in the same way. 1



With a spreadsheet open, click to minimise Excel so that it only displays in the Taskbar at the bottom of the page.



To redisplay your spreadsheet, find it on the Taskbar and click.



Click

to reduce your window to a smaller size – “restore down”.

Once it is reduced, you will see a different icon in that position.



Click

to maximise the Window back to full size.



Click

if you want to close Excel and your spreadsheet with it.

Display ScreenTips Manage Excel 12: Set Excel to display detailed screen tips Excel can display “screen tips” if you tell it to. 1



Run your cursor over any screen command.

Does it display an information box under it that tells you what this command does? Does it display the shortcut?

2 

Click the Office

button, click Excel Options

The Excel Options dialog box displays.

Enjoy… Microsoft Excel 2007

Page 63

Manage the way things look in Excel Display ScreenTips



On the Excel Options dialog box, make sure



Look at the Top options for working with Excel area.



Click the More down arrow for the ScreenTip style field.



Select Show feature description in ScreenTips.



Click

is highlighted.

to save changes.

After you close and re-open Excel, you will have detailed screen tips when you run your cursor over a command.

Page 64

Enjoy… Microsoft Excel 2007

Manage the way things look in Excel Set your default font and other options

Set your default font and other options You have probably realised by now that the font you are using in your spreadsheet is really too large for convenient display. You can either change that on the spreadsheet or change the default that is used by all your spreadsheets.

Manage Excel 13 – Change spreadsheet defaults Note that this setting will not change the fonts in your current spreadsheet. 1 

Click the Office button

, click Excel Options

This window displays.



The default font is Calibri, which suits us fine.

Look at the When creating new workbooks area.

The default size is probably 12 unless someone else has changed it. You want 9. 

Click the down arrow to the right of the Font size field, and select 9 from the drop down list.

The default view for new sheets is Normal View. This is also OK. The default number of worksheets on a spreadsheet is 3. You may want to change this at some stage but it is fine for now.

Enjoy… Microsoft Excel 2007

Page 65

Manage the way things look in Excel Set your default font and other options

Click



Page 66

Click again. Next time you create a spreadsheet you will start off with a different size font.

Enjoy… Microsoft Excel 2007

Navigate in Excel Use the keyboard instead of the mouse

5: Navigate in Excel Use the keyboard instead of the mouse There are two types of shortcuts: 

Key Combinations



Access Keys

Most of the shortcuts you have used in the training have been Key Combinations.

Access Keys You can use keyboard shortcuts that take you through menus and the Ribbon to find a command. These are called Access Keys and they all start with the Alt key. They give you access to every single command on: 

the Ribbon



the Microsoft Office button menu, and



the Quick Access Toolbar

Key Combination Shortcuts Key Combination Shortcuts are the same as those in Excel 2003. They are a fast easy way to apply commands, but you do have to remember them. Most of these shortcuts use the These perform specific commands, like

key. to bold text.

You can do this Microsoft training at some stage, but skip the material relating to Excel 2003 unless you are already a shortcut user in 2003. Also, skip the Practice in PowerPoint section.

Microsoft On-line Training – Keyboard shortcuts in the 2007 Office system Go to http://office.microsoft.com/training/training.aspx?AssetID=RC101562731033

Enjoy… Microsoft Excel 2007

Page 67

Navigate in Excel Display Key Combination Shortcuts in ScreenTips

Manage Excel 14: Find Key Combination Shortcuts 1



Go to http://office.microsoft.com/en-us/word/HP101476261033.aspx.



Cruise through all the lists of keyboard shortcuts. You might find it useful to identify some shortcuts you are likely to use and write them down somewhere.

Display Key Combination Shortcuts in ScreenTips You can find what a key combination shortcut is for a command, by finding the command you want and resting your mouse over it for a moment. The screen tip appears with the key combination shortcut – as long as you system is set to do that.

Manage Excel 15: Set Excel to display shortcuts 1 

Click the Office

button, click Excel Options

The Excel Options dialog box displays.

Page 68



On the Excel Options dialog box, click Advanced.

Enjoy… Microsoft Excel 2007

Navigate in Excel Explore Access Keys and the Ribbon



Scroll down to Display.



Make sure Show function ScreenTips is ticked.



Copy all the other settings shown on the illustration.



Click save changes.

After you close and re-open Excel, you will see both the ScreenTips and the Shortcut Keys when your run your cursor over a command.

Explore Access Keys and the Ribbon Check out these Access Keys. The key gets you out of text entry mode and into command mode. These keys can be tricky to use but you will find them very useful if you have an issue with using a mouse.

Manage Excel 16: Explore Access Keys and the Ribbon



With your cursor anywhere in your document, press the happens along the Ribbon.

Enjoy… Microsoft Excel 2007

key and see what

Page 69

Navigate in Excel Explore Access Keys and the Ribbon

A lot of letters and numbers appear. These are called Badges. For example there is an over the Home tab. 

Page 70

over the Office button and an

With the letters still visible, press the keyboard and see what happens.

key on your

You are taken to the Office menu where there are more Badges available.



Press



Press

and see what happens. The New Document dialog box opens. to close this dialog box.

Enjoy… Microsoft Excel 2007

Navigate in Excel Explore Access Keys and the Ribbon

4 

To move to the Home tab press

Once you are there you will have a new selection of Badges giving you options for that tab. This second layer of Badges is only visible once you have selected the tab. 

Press



To move to the Page Layout tab press

to remove these.

5 .

Once you are there you will have a new selection of Badges giving you options for that tab. 

Press



To move to the Review tab press

to remove these.

6 .

Once you are there you will have a new selection of Badges giving you options for that tab.  7

to remove these.

The Print Preview tab is only visible once you have selected Print Preview from the Office menu. However, you can find it this way. 

Press

To find Print Preview press

You will notice that the same letter appears on several tabs, for example: 

takes you to the Home tab, Fonts group, Bold command.



takes you to the View tab, Zoom group, One Page command.

Enjoy… Microsoft Excel 2007

Page 71

Navigate in Excel Explore Access Keys and the Ribbon

Page 72

You can also use arrow keys to move around once you have selected Alt. 

Press



Press

. You have moved from the Home tab to the Insert tab.



Press

. You have now moved to the groups available on the Insert tab.



Press

. You have moved along each item in a group in the Insert tab.



Press . You have moved to the next group in the Insert tab, and when you reach the end, you move on to the Quick Access Toolbar.



Practice moving around using each of the arrow keys. Note that once you start moving around the Ribbon using the arrow keys, the Badges disappear.



You can get them back by pressing

twice.

Enjoy… Microsoft Excel 2007

Format spreadsheet cells Format your spreadsheet from the Home tab

6: Format spreadsheet cells Now let’s experiment with formatting this spreadsheet a bit more.

Format your spreadsheet from the Home tab The Home tab, Clipboard group You looked at this group when you were cutting and pasting formulas from one cell to another in Exercise 20 – Copy a formula to another cell and modify, page 42.

The Home tab, Number group You looked at this group when you were formatting your budget data, in The Home tab, Number group, page 51.

The Home tab, Cells group You looked at this group when you were inserting and deleting columns and row, in Insert and delete rows, page 29 and Insert and delete columns, page 32.

The Home tab, Font group A font is a type face. You will use the functions in the Font group to change the type face used in your spreadsheet.

Exercise 26 – Change default font& font size for spreadsheet At this stage your spreadsheet is pretty boring, so let’s see what we can do with the type face to smarten it up.

Enjoy… Microsoft Excel 2007

Page 73

Format spreadsheet cells Format your spreadsheet from the Home tab

You should have your budget on your screen. 

If not, find it and load it.



To save your spreadsheet to a new name, follow the instructions on Exercise 5– Save your spreadsheet using the Save As function, page 23 . This means you will have two spreadsheets with different names.

You can be bold with the following exercises, because you have your original spreadsheet saved to a different name and can revert to it any time you like. Anytime you do something you like to this version, save it using the Save function – see Exercise 13 – Save spreadsheet with Save function, page 33 for instructions. If you really mess things up, you can either undo your changes, or close the document and open one of the saved versions. 2



Click the View tab and click Page Layout. This gives you a better way of seeing how your formatting is working.



Click to the Home tab.



Click on the corner square of your worksheet area.

Your entire spreadsheet should change from white to a pale blue, and the row and column labels should change to a darker blue. This indicates that you have selected the entire sheet. The following changes will be applied to all the text in your spreadsheet. 5



Look along the Home tab to find the Font group.



Find the Font name.

You will see the name Calibri and the number next to it, which is the font size. 6

Page 74



Click the down arrow to the right of the font name.

Enjoy… Microsoft Excel 2007

Format spreadsheet cells Format your spreadsheet from the Home tab

A list of fonts displays showing: 

Theme fonts



Recently used fonts



All fonts

Yours will look something like this, but will not be the same.



Run your cursor up and down the fonts on the list. You will see the text in your cells changing as it takes on the attributes of the font you have currently highlighted.



Click on whichever font you like – but make sure it is something neat, as it will be applied to your entire spreadsheet. You may want to stay with Calibri or go to Arial. Avoid fancy fonts on spreadsheets.

That text is now changed to the new font. 9

Is the font size too large or too small? For a spreadsheet, your current font size is probably too large, so change it to something smaller. 

Highlight your entire worksheet again.



Click the down arrow to the right of the font size.

A list of font sizes displays. 

Run your cursor up and down the font sizes on the list.

You will see the text changing size. 

Click on whichever size you like – possibly 8 or 9.

That text is now changed to the new font size. 10

There are quick ways to save your document. 

Press

Enjoy… Microsoft Excel 2007

to save your document.

Page 75

Format spreadsheet cells Format your spreadsheet from the Home tab

Exercise 27 – Change font, font size and font colour in headings 1



Make sure the Home tab on your ribbon is displayed.



Click on the row number of your row with the months in it, probably row 2. This will select the entire row.

Change text font and size 

Change font and font size as you did in the previous exercise so that this heading row is a different font and larger size than the rest of your worksheet.

Make text bold 

Highlight your row again.



Click on the bold button

 Click it again to toggle it on and off and watch your selected text.  Try off.

. It also toggles on and

TOGGLE Some Excel commands are “toggles”. Click them once to turn something on and again to turn something off. Bold, Italics and Underline are three examples of toggles but there are many more.

Change text colour 

Highlight your row again.



Click the down arrow to the right of the .

Page 76

Enjoy… Microsoft Excel 2007

Format spreadsheet cells Format your spreadsheet from the Home tab

The Theme Colors menu displays: 

a range of Theme Colors



a range of Standard Colors



and an option to select More Colors



Run your cursor around the Theme Colors and the Standard Colors and watch the text in your headings change colour.

It becomes obvious that you need fairly dark colours for text on a white background, so:  8

Select one of the dark colours and click. Your text is now changed to the new colour.

But you do not need to stop here – you can select from a much wider range of colours. 

Highlight your block of text again.



Click the down arrow to the right of the



Click on More Colors.

The Colors dialog box displays. 

Click on Standard tab if it is not already displaying.

You are presented with a greater range of colours. You can click on any one of the hexagons to select that colour. 

Click on the colour you want.



Click

Your text is now the new colour.

Enjoy… Microsoft Excel 2007

Page 77

Format spreadsheet cells Format your spreadsheet from the Home tab

But, what if you have a very clear idea about exactly what colour you want to use for heading text? 

Highlight a block of heading text.



Click the Font Colour down arrow .



Click on More Colors.



Click on Custom tab.

You can now create any colour you want.



Experiment with moving the cross hairs around the box. See what happens in the New and Current box on the bottom right of the window.



Then experiment with moving the left pointing arrow up and down the side bar. This will give you lighter and darker shades of the colour you have selected with the cross hairs. Again, see what happens in the New and Current box on the bottom right of the dialog box.

Now look at the Red, Green and Blue boxes. You will see that the Color Model is RGB meaning Red, Green and Blue. Move the cross hairs or left arrow and watch what happens to the Red, Green and Blue values. 

Page 78

Click moment.

to exit for the

Enjoy… Microsoft Excel 2007

Format spreadsheet cells Format your spreadsheet from the Home tab



Back in your document, highlight a block of heading text that is still the original colour.



Click the Font Colour down arrow .



Click on More Colors.



Click on Custom tab.

The New and Current box show the same original colour.



Click in the Red box and change the value to 150. Watch the New and Current boxes. The New area at the top will have changed to mauve.



Click in the Green box and change the value to 0. Now the New area at the top has changed to bright purple.



Click in the Green box and change the value to 0. Now the New area at the top has changed to strong red.



Click

Your text is now changed to the new colour. 14

If there is a colour you particularly want to work with, experiment with it in this spreadsheet. Take the time to get comfortable changing font colour using the: 

Themes Colors menu



More Colors, Standard tab



More Colors, Custom tab

Try putting some random numbers in the RGB fields of the Custom tab and see what amazing colours you can create. 15 

Press



Press

Enjoy… Microsoft Excel 2007

to save your spreadsheet. OR on your Quick Access Toolbar – yet another way to save a file.

Page 79

Format spreadsheet cells Format your spreadsheet from the Home tab

You now know five ways to save a file.

Exercise 28 – Experiment with bold italics, underline and more 1

Make sure the Home tab on your ribbon is displayed.



Highlight a range of cells.



Click on the italics button

 Toggle it on and off and see what happens to your selected text.  Try 

TOGGLE

Click on the underline button

Some keys in Excel are “toggles”.

 Toggle it on and off and see what happens to your selected text.  Click the down arrow with your line choices.  Try

Page 80

Click them once to turn something on and again to turn something off.

and experiment

Bold, Italics and Underline are three examples of toggles but there are many more.



Highlight a range of cells.



Change the size of the text using

and

Enjoy… Microsoft Excel 2007

Format spreadsheet cells Format your spreadsheet from the Home tab

Would you like to place a background colour in a range of cells? Try this. 

Click a cell or range of cells.



From the Home tab, Font group, click the right arrow next to the Fill button.

The Theme Colors menu displays. 

Click on a colour.

The entire table cell is now coloured with that colour.



Work on all the text in your spreadsheet until you have the font, font size, colour and bolding that you want.



Press



Press

6 to save your spreadsheet. OR on your Quick Access Toolbar.

Now you have explored a lot of what you can do with fonts. Don’t worry if you don’t remember it all. You will get to work with it all again.

The Home tab, Alignment group So far, you have applied formatting to the fonts you are using; font type, colours, size etc. Now you will apply formatting to your cells. You will determine your indents and alignments using the Alignment group.

Exercise 29 – Format text alignment in cell 1



If your spreadsheet is a complete mess, close it without saving, and re-open the last version number.



If you like what you have, save it to a new version number.

If you can’t remember how to do this, go back and follow the instructions in Exercise 5– Save your spreadsheet using the Save As function, page 23. By now, you should have remembered how to save, close and open files. If you have not, do it a few times so that you do remember it. You will now use the Home tab, Alignment group to align your text. It will look like one of these depending upon your screen size.

Enjoy… Microsoft Excel 2007

Page 81

Format spreadsheet cells Format your spreadsheet from the Home tab

Align text to cell left or right boundaries Look at the Text Alignment commands. 

Place your cursor in any cell that has text in it. One of these three will be highlighted depending upon whether:  your cell has text aligned to the left margin  your cell has centred text

. This is called Left Justified.

. This is called Centred.

 your cell has text aligned to the right margin 

. This is called Right Justified.

Click through each of these to see what happens to your text. Finish on something sensible that you are happy with.

Change row height To look at the next group we need to change a row height. 

Highlight your heading row again so that the cursor appears like this on the bottom edge for the row.



Drag down so that the row deepens.

You will now be able to see that your text is aligned, most likely, to the bottom of the cell.

Align text to cell top or bottom boundaries 

Place your cursor in any cell on your deepened row. One of these three will be highlighted depending upon whether:  your cell has text aligned to the top margin  your cell has centred text

 your cell has text aligned to the bottom margin 

Page 82

Click through each of these to see what happens to your text. Finish on something sensible.

Enjoy… Microsoft Excel 2007

Format spreadsheet cells Format your spreadsheet from the Home tab

Change left margin You can change the left margin of your text.



Select a cell with text in it.



Click

to change the margin towards the centre of the page.



Click

to change the margin back to the left edge of the page.

Change text direction Sometimes you want your text to display in a different direction – maybe to control cell width if you have a very wide spreadsheet. 

Highlight January.



Click the down arrow

to the right of the

. This menu displays. 

Click your way through the options to see what happens to your text.



This is another “toggle” so click to switch on and off.



Finish with something sensible.



If you like what you have, press



If you want to save it to a new name, press dialogue box.

7 to save your spreadsheet. to display the Save As

You now know 6 ways to save a file.

Enjoy… Microsoft Excel 2007

Page 83

Format spreadsheet cells Format your spreadsheet from the Home tab

The Home tab, Styles group So far, you have applied formatting to the fonts you are using and you your text alignment in cells. Now you will apply “conditional” formatting, formatting that only occurs if certain conditions are met.

Exercise 30 – Apply Conditional Formatting 1



Highlight column C.



On the Home tab, Styles group, click Conditional Formatting.

Conditional Formatting allows you to you specify rules by which Excel will automatically format values for you. 2

The Conditional Formatting menu displays. 

Run your cursor over Highlight Cells Rules.

The next menu displays. 

Click Greater Than.

The Greater Than dialog box displays. 

Type in a value that only has a few entries higher than it in your budget.



Click the down arrow of the with field to select a colour scheme.

The expenses in your list over that value will now be coloured in the colour scheme you selected.

Page 84

Note that you could use this function to alert you to expenses that are going over a certain upper limit.

Enjoy… Microsoft Excel 2007

Format spreadsheet cells Format your spreadsheet from the Home tab



Highlight column C.



On the Home tab, Styles group, click Conditional Formatting.

The conditional formatting menu displays. 

Run your cursor over Top/Bottom Rules

. The next menu displays. 

Click Top 10 Items.

The top 10 expenses in your list will now be coloured in the colour scheme you selected.



Experiment with the functions you can understand.



See if you can find useful ways of using any of them in your budget.

Exercise 31 – Apply Cell Styles 1



Highlight one of your totals.



On the Home tab, Styles group, click Cell Styles.,

The menu below displays.

Enjoy… Microsoft Excel 2007

Page 85

Format spreadsheet cells Format your spreadsheet from the Home tab



Run your cursor over each of the options and watch your selected cell.



Once you have checked them all out, select one of your totals, and apply the Total style.



Experiment with the various other formatting options.



See if you can find useful ways of using any of them in your budget.



You will see that you can also apply number formatting from this menu.



You may want to replace your manually formatted headings with one of the heading styles in this menu.



Press



Press

Page 86

to save your spreadsheet. OR on your Quick Access Toolbar.

Enjoy… Microsoft Excel 2007

Format spreadsheet Microsoft Office Themes

7: Format spreadsheet Microsoft Office Themes Office 2007 has an extremely useful new feature that means that anyone can produce nicely formatted spreadsheets in as little time as it takes to enter the data. “Themes” is a brand new feature in Office 2007 and once you know how to use them – and they are very easy to use – you will impress everyone with your document layout and presentation skills. Themes let you apply a complete, coordinated package of fonts, colours, and styles with a single click. They are collections of colours and fonts that work well together. In addition, for those of you who are going to be using a range of Microsoft 2007 products, the same Themes are used across all of them – Word, Excel, and PowerPoint – so you can produce lots of different documents and lots of different types of documents, all with the same look and feel – if you want. In previous exercises, you have applied colours and fonts to your text manually, but there is an easier way, with Themes.

Looking at Themes Exercise 32 – Cruise around themes



Look at the picture above. 

Open your first budget spreadsheet – the one that is not formatted, and use Save As to save it to another new name.

Click the Page Layout tab on the Ribbon.

A range of formatting options displays on that tab. 3



Click on Themes in the Themes group.

Enjoy… Microsoft Excel 2007

Page 87

Format spreadsheet Microsoft Office Themes

The Built-In box displays. 

Now run your cursor over each box in turn and watch your spreadsheet.

For example, if you place your cursor over the Office box, and look at your spreadsheet, you will see that some colours, fonts and font sizes change. 

Do this with all the boxes and watch the changes.



When you have checked them all out, click your cursor on one you like the best.

Your spreadsheet now has different colours and fonts from those of the original template you selected.

Changing theme colours Exercise 33 – Change theme colours, keep theme fonts 1

But what do you do if you like the general look of a theme – perhaps the fonts, but not the colour? 

Click the little coloured box

A Built-In box displays with lots of sets of colours. 

Run your cursor over each row of colours in turn and watch your spreadsheet.



When you have checked them all out, click your cursor on one you like.

Your spreadsheet now has different colours but keeps the same fonts.

Page 88

Enjoy… Microsoft Excel 2007

Format spreadsheet Microsoft Office Themes

Changing theme fonts A font is a type face. You may be familiar with the standard fonts Times New Roman and Arial. Cambria and Calibri are two new fonts released with Office 2007. Microsoft has defined them as the default fonts, and they will appear everywhere in your Excel templates. However, there are many other fonts to choose from when you are creating a document.

Exercise 34 – Change theme fonts, keep theme colours 1

So, what do you do if you like the general look – this time, you like the colour, but not the fonts? 

On the Page Layout tab, Themes group, click the little A. .

A different Built-In box displays with sets of fonts. 

Now run your cursor over each row of fonts in turn and watch your spreadsheet.



When you have checked them all out, click your cursor on one you particularly like.

Your spreadsheet now has different fonts but keeps the same colours.

Format your spreadsheet Exercise 35 – Format your spreadsheet 1



Take some time now to play with Themes, Colours and Fonts, and cell formatting until you have a spreadsheet you like the look of.



You can go back over the instructions in Exercise 27 – Change font, font size and font colour in headings, page 76, and, if you have selected a different theme, you will have a different range of Theme Colours to select from.

Enjoy… Microsoft Excel 2007

Page 89

Format spreadsheet Insert header and footer



When you have your spreadsheet looking the way you want, you can save it again, but be sure you do not overwrite your unformatted spreadsheet. You should have saved it to a new version number in Exercise 32 – Cruise around themes, page 87.

Your spreadsheet is saved, but remains open on your screen.

Insert header and footer Have you ever been passed a printed document for which you want to find the original softcopy, and not known where to start? The best way to make sure you can always identify a printed document is to give it a header and a footer. A header is a set of text or graphical elements that appears at the top of every page of a document, like the heading at the top of this document. A footer is a set of text or graphical elements that appears at the bottom of every page of a document, like the footer that includes the page number at the bottom of this document. Often, your organisation logo and other factors that identify your organisation, go into your header and footer. Once you start formatting a header or footer, you will see an example of a Contextual tab, the Header and Footer tools, Design tab. This is a tab that only appears on the ribbon when you are working specifically in that area.

Exercise 36 – Insert a header in your spreadsheet For these exercises, make sure you are in Page Layout view (see Manage Excel 7: Explore Workbook Views group, page 60).



Click the Insert tab and identify the Text group.



Click Header & Footer in the Text group.

The Header & Footer Tools, Design tab displays.

Page 90

Enjoy… Microsoft Excel 2007

Format spreadsheet Insert header and footer

You are taken to the header area for your spreadsheet where you will see a box with your insertion point in it. 2



Click to the left of the box. A new box displays on the left third of the header.



Click on the centre of your header again. The box displays on the centre third of your header.



Click on the right of your header. The box displays on the right third of your header.

You have these three areas to insert information that you want to appear on every page of your spreadsheet. 3



Click on the centre of your header and type My Budget.



Click on the right of your header and type the date or date range for your budget, eg June – Dec 2009.



Click back into your spreadsheet anywhere to get out of your header and see how it is looking.

Exercise 37 – Insert a footer in your spreadsheet 1



From the Insert tab, click Header & Footer in the Text group.



From the Header & Footer Tools, Design tab, Navigation group, click Go to Footer.

Your insertion point is now located in the bottom area of your page. This is also arranged in three areas.



Click through the left, centre and right areas of your footer.

This time we are going to insert some “fields” that will insert information automatically for you using the Header & Footer Elements group.

Enjoy… Microsoft Excel 2007

Page 91

Format spreadsheet Insert header and footer



Click the left area or your footer.



Click File Path in the Header & Footer Elements group.

You will see the code &[Path]&[File] inserted in your footer. This is telling Excel to find the location and name of your spreadsheet and insert it in this position. 5



Click the centre area or your footer so that your cursor moves out of the left area.

You will see &[Path]&[File] in the left area change to your location and file name. 6

In the centre area: 

Click Current Date in the Header & Footer Elements group.



Click Current Time.

You will see the code &[Date]&[Time] inserted in your footer. This is telling Excel to find the current date and time and insert it in this position.



Click the right area or your footer.

When you have exited the centre area in your footer, you will see that &[Date]&[Time] has changed to your date and time. 8



Type Page followed by a space.



Click Page Number in the Header & Footer Elements group.



Press the space bar, type of, press the space bar again.



Click Number of Pages.

You will see the code &[Page] of &[Pages] inserted in your footer. This is telling Excel to find the current page number and the total number of pages and insert in this position.

When you exit your footer, it will look like this.

Page 92



Click back into your spreadsheet anywhere to get back out of your footer and see how it is looking.

Enjoy… Microsoft Excel 2007

Format spreadsheet Insert header and footer

Exercise 38 – Edit header and footer entries There is an easy way to move between your worksheet and your header or footer. 1



To open the header, run your cursor over the header area so that one of the three boxes is highlighted in blue.



Click in that box. You can now edit the header text in that box or complete any other header/footer commands.



Place your cursor back in your text.



Click anywhere.

Your header is now closed and you are back in your text. 3



To open the footer, run your cursor over the footer area so that one of the three boxes is highlighted in blue.



Click in that box. You can now edit the footer text in that box or complete any other header/footer commands.



Place your cursor back in your text.



Click anywhere.

Your footer is now closed and you are back in your text. 5



Now experiment for yourself and put anything that you like in the three header and three footer areas.



Save your spreadsheet.

Enjoy… Microsoft Excel 2007

Page 93

Layout spreadsheet and prepare for publication Review the Zoom function

8: Layout spreadsheet and prepare for publication Review the Zoom function It can be quite tricky to work out the best way to print a spreadsheet, so the first thing you need to be able to do is zoom in and out so that you can see what needs printing, and how the various page setup options will print. As you have already seen, zoom lets you choose between a close-up and a distant view of your spreadsheet. When you want to look at some small detail, you will zoom in so that you can see the detail more easily. When you want to scroll quickly through your spreadsheet to find something in particular, or to lay it out for printing, you may want to zoom out. If you cannot recall how to do this, go back to Manage Excel 9: Explore Zoom group, page 61 to refresh your memory.

Set your print parameters So far you have accepted the margins and other aspects of page setup that were given by default when you created your spreadsheet. However, you can change these.

Exercise 39 – Cruise around Page Setup options You will find it easier to do these exercises if you: 

are in normal view (View tab, Normal)



have set your zoom above so that your whole spreadsheet shows on a single screen 1



Orientation

Click the Page Layout tab on the Ribbon.



Click on Orientation in the Page Setup group..



Click on Portrait and you will see your spreadsheet displayed as probably two or three portrait (higher than they are wide) pages.



Click on Landscape and you will see your spreadsheet displayed as probably three or four landscape (wider than they are high) pages.

At some stage you must decide if you want to print your spreadsheet landscape or portrait. 

Page 94

For the moment, click Landscape.

Enjoy… Microsoft Excel 2007

Layout spreadsheet and prepare for publication Set your print parameters

Paper Size 

Click on Size in the Page Setup group.



Make sure A4 is selected.

Standard home printers print on A4 size paper, although you may have the option with your college or workplace computer to print on larger paper. Excel spreadsheets are often printed A3 which is twice the size of A4. You will not see this size on the list of paper sizes if your printer cannot print on that size paper.

Print Area 

Highlight each of the cells in your spreadsheet you would want to print. To do this:  Click on cell A1.  Hold the left mouse button down and drag across to December and down to a row or two below your totals.



Click on Print Area in the Page Setup group.



Click Set Print Area.

You have now told your printer to print only the selected area. If you did not do this, your printer might try to print the entire spreadsheet and you would end up with a lot of blank pages. 5

Margins 

Click on Page Layout tab, Page Setup group, Margins.

You have a few options here: 

to accept the last customised margins you set



to accept the default or Normal margins



to concentrate your spreadsheet towards the centre of the page with Wide margins



to get as much onto a single page as you can with Narrow margins. You may do this if your work is only just going over two pages

Enjoy… Microsoft Excel 2007

Page 95

Layout spreadsheet and prepare for publication Set your print parameters

Print Titles You can also decide to make certain rows or columns print on every page. 

Click on Print Titles in the Page Setup group.

The Sheet tab of the Page Setup dialog box displays. 

If it does not display by default, click the Sheet tab.

The Print area field shows the range of cells you selected in Step 4.



Place your cursor in Print titles, Rows to repeat at top.



Move your cursor back onto your spreadsheet and you will see a right arrow



Click on rows 1 and 2 (or whatever heading rows you have).

The code $1:$2 will be returned to the field. 9

Page 96



Place your cursor in Print titles, Columns to repeat at the left.



Move your cursor back onto your spreadsheet and you will see a down arrow.



Click on columns 1 and 2 (or whatever heading columns you have).



The code $A:$B will be returned to the field.



Click

to exit the dialog box and save your changes.

Enjoy… Microsoft Excel 2007

Layout spreadsheet and prepare for publication Set your print parameters

Exercise 40 – Print Preview spreadsheet When you are working out the best way to layout your spreadsheet to print it, it is a good idea to look at it in print preview. 1



Click the Office button



Run your cursor over Print.

The Print menu displays.



Click Print Preview.

Your spreadsheet redisplays on your screen so you can see how it will look when printed. The Print Preview ribbon displays. This tab is a Contextual tab that only displays when you have selected Print Preview. 4



Click Zoom to toggle between a close up and full page view of your spreadsheet.



Press

and

to see what each of your pages looks like.

Make sure your spreadsheet is looking exactly as it should. 

If there is something wrong, click Close Print Preview, fix the error and repeat the print preview until you are happy.



Save your spreadsheet when you like your changes.

Enjoy… Microsoft Excel 2007

Page 97

Layout spreadsheet and prepare for publication Set your print parameters

Exercise 41 – Cruise around Scale to Fit options With Scale to Fit, you can tell Excel to make your spreadsheet fit onto a specified number of pages and Excel will work out how to do that for you. You will find it easier to do these exercises if you have set your zoom so that your whole spreadsheet shows on a single screen. 1



Scale

Click the Page Layout tab on the Ribbon.



Click on Scale in the Page Setup group.



Manually change the size to 50%.

You will see your dark dashed line go to the outside edges of your spreadsheet, indicating that it will be printed only one page wide. (If yours is still on more than one page, reduce the percentage until it is on one page.)



Check it out in Print Preview (see page 97).



Change it back to 100%.

Width 

Click the down arrow to the right of Width in the Page Setup group.



Click on 1 Page.



Check it out in Print Preview (see page 97).

Height 

Click the down arrow to the right of Height in the Page Setup group.



Click on 1 Page.



Check it out in Print Preview (see page 97).



Change both Width and Height back to Automatic.



Make sure Scale is back at 100%.

Exercise 42 – Control page breaks When you did the previous exercises, page breaks were inserted automatically by Excel if your spreadsheet covered more than a page. However, you can control exactly where these page breaks are located. You will find it easier to do these exercises if you set your zoom so that your whole spreadsheet shows on a single screen.

Page 98

Enjoy… Microsoft Excel 2007

Layout spreadsheet and prepare for publication Set your print parameters



Click Page Layout tab.



Ensure the Scale in the Scale to Fit group is 100%.



Ensure that Width and Height are Automatic.



Click View tab, Workbook Views, Normal view or Page Layout view.



Click Page Break Preview.

The Welcome to Page Break Preview dialog shows with instructions on how to use this feature.



Read the instructions.



Click

Very obvious blue broken lines appear to indicate how your printer will print the pages, and each of the pages is numbered. 

Move your insertion point over one of the vertical lines until you get a double headed arrow.



Hold down your left mouse button and drag to the right or the left.



Move your insertion point over one of the horizontal lines until you get a double headed arrow.



Hold down your left mouse button and drag up or down.

Arrange your page breaks so that: 

you have January to June on Page 1 and July to December on Page 3.



you have a page break above one of your expense categories (sub-headings).

Print Sequence The numbering that you see on these pages determines the sequence in which the pages are printed. You will see how to change this sequence in Exercise 44 – Set print sequence, page 100.

Enjoy… Microsoft Excel 2007

Page 99

Layout spreadsheet and prepare for publication Set your print parameters

Now go back to Page Layout view and see what your Scale is. 

Click Page Layout tab.



Look at the Scale in the Scale to Fit group. It will be a different number although not necessarily 72% as shown here.

If you want to print your spreadsheet laid out with page breaks in your selected positions, Excel will scale your fonts, rows and columns down to a smaller size so that it can fit them onto a page.

Exercise 43 – Cruise around Sheet Options With Sheet Options, you can specify formatting options for your entire sheet. You will find it easier to do these exercises if you have set your zoom in the previous exercise so that your whole spreadsheet shows on a single screen. 1



Gridlines

Click the Page Layout tab on the Ribbon.



Tick and untick Gridlines View to see what happens.



Tick Gridlines Print and check out your spreadsheet in Print Preview.



Untick Gridlines Print and check out your spreadsheet again in Print Preview.

Headings 

Tick and untick Headings View to see what happens.



Tick Headings Print and check out your spreadsheet in Print Preview.



Untick Headings Print and check out your spreadsheet again in Print Preview.

Exercise 44 – Set print sequence Do you recall in Exercise 42 – Control page breaks, page 98, that your pages were numbered in the sequence in which they would be printed. You can change this sequence. 1

Page 100



Click on the Page Layout tab, Page Setup group, Print Titles.

Enjoy… Microsoft Excel 2007

Layout spreadsheet and prepare for publication Set your print parameters



If it does not display by default, click the Sheet tab.

The Print area field shows the values you selected as Print Area in Exercise 39 – Cruise around Page Setup options, page 94. The Rows to repeat at top and Columns to repeat at left show the values you selected as Print Titles in Exercise 39 – Cruise around Page Setup options, page 94.

Look at Page order. This shows that the printer will print all pages down then the next page wide and down.



Click Over, then down.

This shows that the printer will print all pages wide then the next page down and wide. 

Click to exit the dialog box and save your changes.

Look at how your spreadsheet is affected in Page Break Preview. 

Click View tab, Workbook Views, Normal view or Page Layout view.



Click Page Break Preview.

The Welcome to Page Break Preview dialog shows with instructions on how to use this feature. 

Tick the Do not show this dialog again box.



Click

Enjoy… Microsoft Excel 2007

Page 101

Layout spreadsheet and prepare for publication Set your print parameters



You now have January to June on Page 1 and July to December on Page 2.

Exercise 45 – View and print Formulas or Values You can tell Excel to display and print either formulas or values in the worksheet area.



Click on the Formulas tab.



Find the Formula Auditing group.



Click Show Formulas.

Your cells now display formulas instead of results. 

Use Print Preview to see what would print.



Click Show Formulas again to switch back to results. This is another toggle.

Exercise 46 – Finalise the page layout of your spreadsheet In the previous exercises, you have walked through a large range of formatting and page layout options. These can be very confusing, so don’t worry if you are not clear about them at this stage. Now you have seen them once, you have some idea what is possible, even if you did not get everything done the way you wanted the first time. It is now a good idea to go back through the entire sequence, and make informed decisions on what way you want to set all the options.

Page 102

Enjoy… Microsoft Excel 2007

Layout spreadsheet and prepare for publication Review spreadsheet

Look at each of the following: 

Formatting options - you may want to change your font type or size, or change some aspect of your arrangement or formatting.  Exercise 32 – Cruise around themes, page 87  Exercise 33 – Change theme colours, keep theme fonts, page 88  Exercise 34 – Change theme fonts, keep theme colours, page 89



Header and Footers  Exercise 36 – Insert a header in your spreadsheet  Exercise 37 – Insert a footer in your spreadsheet  Exercise 38 – Edit header and footer entries



Margins, Orientation, Size, Print Area, Print Titles  Exercise 39 – Cruise around Page Setup options



Scale to fit options  Exercise 41 – Cruise around Scale to Fit options  Exercise 42 – Control page breaks



Sheet options  Exercise 43 – Cruise around Sheet Options



Prepare to print  Exercise 44 – Set print sequence

Take the time to check regularly in Print Preview to see if you like what you are achieving. You can also work with different zooms to see what works best for you.

Review spreadsheet You should always check a spreadsheet in a range of ways when you have finished it.

Exercise 47 – Run spell checker First you can use the Excel automatic spell checker, which will pick up many (but not all) of the errors we make.



Click the Review tab.



Click Spelling in the Proofing group.

Enjoy… Microsoft Excel 2007

Page 103

Layout spreadsheet and prepare for publication Review spreadsheet

The Spelling window displays. 

The first spelling mistake Excel finds is displayed in the Not in dictionary box.



Look in the Suggestions box. Excel will try to find a likely correct spelling and display it here.



If there is a suggested word in the list that is the correct spelling,  Highlight the correct spelling  click Change and go to Step 5.



If the word is wrong, go to Step 4.



Type the correct spelling in the Not in dictionary box.



Click Change .

The spell checker will continue to pick up wrong words until they are all fixed. Once it finds no more wrong spelling, you will see one of these Dialog boxes. 

Now save your spreadsheet again with the spelling mistakes removed.



Page 104

Click OK

Click Office button

, click Save

Enjoy… Microsoft Excel 2007

Layout spreadsheet and prepare for publication Print and check your spreadsheet

Print and check your spreadsheet Now you can print your spreadsheet to check it visually.

Exercise 48 – Print spreadsheet There are several different ways of telling Excel to print. The Quick Print process is the fastest but only prints one copy of the entire document, single sided, every time. If you want to print just a few pages, print multiple or collated copies, or print both sides of the paper, you must take a few extra steps. We will look at how to do this during Project 2. If you experience any problems, you may need help with setting your computer up correctly. 1 

Click the Office button



Run your cursor over Print



Click Quick Print.

to display the Print menu.

Hopefully your spreadsheet will print.



Return to your spreadsheet and change display to formulas. If necessary return to Exercise 45 – View and print Formulas or Values, page 102 for instructions.



Click Quick Print to print a second version with formulas.

Exercise 49 – Check printed spreadsheet for errors Once you have printed a copy of your spreadsheet, you should proofread it for errors. 1



Check the text for spelling errors that have slipped past the spell checker.





Check the data for input errors. Do all your numbers make sense?





Check the output of the calculations. Select a random range of calculations and ensure that they are correct using a calculator.



Check your formatting to be sure it makes the spreadsheet easier to read.

Enjoy… Microsoft Excel 2007

 

Page 105

Use documentation to overcome problems Microsoft Get Started

9: Use documentation to overcome problems Microsoft Get Started It is possible to download a connection to Get Started on-line training so that it displays in Excel 2007. If you have Excel 2007 on your own computer with a legal copy of Microsoft Excel 2007, you should consider getting this connection. It gives you an easy way of accessing all the various help options covered in this section.

Manage Excel 17 – Install Microsoft Get Started

Go to http://office.microsoft.com/en-us/training/HA102146851033.aspx.

Under Get it Now, click Excel 2007.

Press Continue in the Validation Required section. Microsoft will validate that you are running a legal copy of the software.

Follow the instructions to complete validation and install the connection. Select Run when that option is offered. This will install the bar into your copy of Excel. If you have trouble following the on-screen instructions, you can print them from the first page of the installation process.

Page 106

A new Get Started tab will appear in Excel that links you directly to the on-line training. This is only possible with legal copies of the software.

Enjoy… Microsoft Excel 2007

Use documentation to overcome problems Microsoft Help

Microsoft Help If you need help while you are working on a spreadsheet, you can use the built-in Help system. This comes as standard with Microsoft Excel 2007.

Manage Excel 18 – Guided tour of Microsoft Help 1 

Press the

key.

OR 

Click the little round button with the question mark

in the upper-right corner.

The Excel Help window displays.



Click on the dialog box to drag it around your screen.

You can use three methods to find the help you need.

Enjoy… Microsoft Excel 2007

Page 107

Use documentation to overcome problems Microsoft Help

The Browse facility This is the default when the Help opens for the first time. You might find something useful here. 

Click a few of the options to see what happens.

The Table of Contents 

If the table of contents is not displaying, click

on this toolbar.

A second panel opens to the left of the Browse panel.



Page 108

Click on a few of these options to see what happens.

Enjoy… Microsoft Excel 2007

Use documentation to overcome problems Microsoft Excel on-line training

The Search field This is at the upper-left of the help window. Using the Search tool is usually the quickest and easiest way to find something, so we will look at how to use it now.

Let’s say you want to find a list of keyboard shortcuts. 

Type keyboard shortcuts into the Search field.



Click

A list of possible matches displays. Scroll down this list and double click any item you think might be the response to your question. Information about that function displays. You can keep this window “on top” while you are working in your spreadsheet, so you can follow the instructions easily.

Microsoft Excel on-line training Microsoft has provided some great on-line training for Office 2007, as we have already seen a few times now. You may find them a great help to reinforce current learning, or for revision later.

Microsoft On-line Training – Menu for all available Excel training http://office.microsoft.com/en-us/training/CR100479681033.aspx

Each of these has a Quick Reference Card in the bottom left frame. You can print and keep these stored somewhere handy. They will provide you with an invaluable quick reference collection.

Enjoy… Microsoft Excel 2007

Page 109

Use documentation to overcome problems Excel 2007 Solution Center

Excel 2007 Solution Center We will not be asking you to use the Excel 2007 Solution Center at this stage. However, if you are planning on becoming an Excel guru, this is where you will start. You can go to this site if you have something odd happen to your spreadsheet, or want to do something you can’t find in the help or training.

Excel 2007 Solution Center http://support.microsoft.com/ph/11346

Other Online Experts There are also Microsoft experts who offer free assistance online. Some send out newsletters. There are also mailing groups covering Microsoft issues. You can check out each of the following. This sort of information goes out of date quickly, so please let the publisher know if these are no longer available.

Allen Wyatt’s Excel Tips

http://exceltips.vitalnews.com/ You can get a daily or weekly tips newsletter from here.

O’Reilly

http://www.oreilly.com/store/series/mm.csp O’Reilly publishers have a full range of Microsoft Office manuals, downloadable as PDF files, including Excel 2007: The Missing Manual, by Matthew Macdonald, and Word 2007 for Starters: The Missing Manual by Chris Grover

Page 110

Enjoy… Microsoft Excel 2007

Create charts Create new worksheet and chart

10: Create charts For the previous project, you looked at how to create, layout and print a spreadsheet that you will use yourself. In this section you will create a spreadsheet, and develop charts that you can use to communicate the information in the spreadsheet to others. If you like numbers yourself, you may be able to look at a spreadsheet and “see” what it all means, but many people cannot. You have heard the saying “a picture speaks a thousand words.” Well it does the same for numbers, and Excel has made it very easy for you to create pictures from numbers.

Create new worksheet and chart Exercise 50 – Create and save a new worksheet with chart For this project you are going to use information supplied in the Microsoft training. 

Close Excel if it is still open.



Open Excel. A new blank spreadsheet displays.



Copy the spreadsheet data shown on this illustration to your spreadsheet. This is taken from the Microsoft online training that you will do later.



Practice using some of the features you learned in Project 1 as you are typing this data into the cells.

 Use  Use the

key to move down the cells. to move across the cells.

 Enter the date range Jan – Mar using AutoFill. (see Exercise 21 – Enter a Range of formulas using AutoFill, page 43.)

Enjoy… Microsoft Excel 2007

Page 111

Create charts Create new worksheet and chart



When you have finished entering the data, select the range B2 to D5 that includes the:  the column titles (January, February, March)  row labels (the salesperson names).



Click the Insert tab.



In the Charts group, click the Column button.



Click Clustered Column, the first column chart in the 2-D Column list.

A chart displays on your spreadsheet. You have January to March on the horizontal axis. This is a good way to see sales by month. Your chart looks something like the chart below.



Change some values for Guiussani and watch the chart.

Your chart adjusts automatically.

Page 112

Enjoy… Microsoft Excel 2007

Create charts Modify chart type and layout



Take the time to look over your chart.  The vertical axis shows the number of cases.  The horizontal axis shows the month.  The columns show the sales made by each of the salespeople in each month.

Save your worksheet 

Follow the instructions in Exercise 5– Save your spreadsheet using the Save As function, page 23, to save this spreadsheet.



In the File name field, type a name for your file, something like “YourNameSalesReportVersion1”. Type your own name, the words “SalesReport”, and “Version1” – all without any spaces between the words, and using capitals to separate the words.



Write your spreadsheet name here – and also the location where you saved the spreadsheet – you will need to remember both your spreadsheet name and location. Your path will look something like this if you are saving to an external memory device. E:\YourNameSalesReportVersion1

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

Modify chart type and layout Exercise 51 – Change the chart view There is a huge array of formatting options you can apply to your chart once you have it.

Enjoy… Microsoft Excel 2007

Page 113

Create charts Modify chart type and layout

Create two versions 

Left click on a blank area in your chart.



Right click.

This menu displays. 

Left click Copy Here.

There are now two charts in your spreadsheet. 2



Run your cursor over the edge of the top chart until the cross hairs display.



Hold the left mouse button down and drag the chart to another position so that you can see both charts.

Switch Row/Column Excel selects a default way of displaying your data, but you may want to do it differently. 

Click on your second chart.



Click Chart Tools tab, Design group.



From the Data group, click Switch Row/Column.

You now have a different way of looking at the same data. In the first view, you have January to March on the horizontal axis. In the second view, you have the salesperson names on the horizontal axis and you are better able to see the improvements made by each salesperson.

Page 114

Enjoy… Microsoft Excel 2007

Create charts Modify chart type and layout

Exercise 52 – Print charts Now you can print your two charts and check them visually. 1



Click on the chart you want to print.



Click the Office button



Run your cursor over Print

to display the Print menu.

Click Print.

The Print dialog box displays.

The Print what frame should look like the first illustration, with Selected Chart selected. If it looks like the second illustration, go back and select your chart and try again. 

Click

Your chart will print.



Repeat with your second chart.



Save your spreadsheet using the Save command to its current name – Version 1



Then save your spreadsheet again using the Save As command and name it Version 2.

Now you have two copies or your spreadsheet. Continue working on Version 2.

Enjoy… Microsoft Excel 2007

Page 115

Create charts Modify chart type and layout

Exercise 53 – Add chart titles Now let’s add some titles to the first chart, the one with January to March on the horizontal axis. 1

Make sure you are working in Version 2. 

Click on your first chart.



Click Chart Tools, Design tab. If you can’t see it, you haven’t clicked on one of your charts.



On the Chart Layouts group, click the down arrow to the right.

A menu displays with a range of options. 2



Click Layout 9.

You will now see some text boxes placed around you chart for you to type titles into. Click your way around the other layouts to see what is there, but finish on Layout 9.



Click on Chart Title.



Type Northwind Traders Tea.



Click on Axis Title on the vertical axis on the left.



Type Cases Sold.



Click on Axis Title on the horizontal axis at the bottom.



Page 116

Northwind Traders Tea Cases Sold



Type First Quarter Sales.

500 400 300 200 100 0

Guiussani Cencini Jan

Feb

Mar

Kotas

First Quarter Sales

Enjoy… Microsoft Excel 2007

Create charts Modify chart type and layout

Exercise 54 – Format chart elements with Chart Styles

Chart Styles Now look at Chart Tools, Design, Chart Styles. 

Click the More button to the right, to display the full range of Chart Styles.



Click your way through the options and watch your chart.



Select one you like.

You could end up with something like this.

Exercise 55 – Format chart elements manually 1



Click on your second chart.



Click Chart Tools, Design tab. If you can’t see it, you haven’t clicked on one of your charts.



On the Chart Layouts group, click the down arrow to the right.

A menu displays with a range of options. 2



Select Layout 9.

You will now see some text boxes placed around you chart for you to type titles into.

Enjoy… Microsoft Excel 2007

Page 117

Create charts Modify chart type and layout



Click Chart Title.



Type Northwind Traders Tea.



Click Axis Title on the vertical axis on the left.



Type Cases Sold.



Click Axis Title on the horizontal axis at the bottom.



Type Sales by Salesperson.

Format titles with WordArt Styles Now look at Chart Tools, Format tab, WordArt Styles group. 

Click the down arrow to the right of the styles to display the full range of styles.



Run your cursor over each option and watch your heading text.

Some will look good and some will not work at all. 

Click one you like.

Format title text using Mini toolbar You may have seen the Mini toolbar display when you have been working with text. This toolbar has the same commands as the Home tab, Font group. 5



Highlight some heading text.

A faded toolbar displays.

Page 118



Run your cursor to the toolbar and click on a button.



Experiment with a few of these commands and watch your text change.

Enjoy… Microsoft Excel 2007

Create charts Modify chart type and layout

If you don’t like what you see, you can click undo a few times to reverse your actions. 

Press



Click

on your keyboard to undo your last action. OR on the Quick Access Toolbar.

Format columns You can also format the columns on your chart separately.



Click on a column.



On the Chart Tools, Format tab, in the Shape Styles group, click on the down arrow to display the full menu.



Click your way through the options and watch your selected bar.



Also, experiment with Shape Outline and Shape Effects.



Again, you can click undo to reverse your actions.

Format background with Shape Fill 

Click on the chart background.



Click Chart Tools, Format, Shape Styles, Shape Fill.

Enjoy… Microsoft Excel 2007

Page 119

Create charts Modify chart type and layout



Experiment with the colours as background colours.



Click



Experiment with the Texture options.



Click



Experiment with the Gradient options.



Work on your chart now till you get it looking the way you want.

You should end up with formatted: 

Heading text



Columns



Background



Print your two charts if you want to see what they look like. If necessary, follow the instructions in Exercise 52 – Print charts, page 115.



Save your spreadsheet to its current name – Version 2



Then save your spreadsheet again using the Save As command and name it Version 3.

Now you have three copies. Continue working on Version 3.

Page 120

Enjoy… Microsoft Excel 2007

Create charts Modify chart type and layout

Exercise 56 – Customise Labels You can also work with your headings and other aspects of the way your data displays using the Chart Tools, Layout tab.

Make sure you are working on Version 3. 

Click on chart 1.



Go to Chart Tools, Layout tab, Labels group.



Click Chart Titles.



Click through the options to see what they do to the Title of your chart.



Don’t forget that you can Undo you make too much of a mess.



Click Axis Title.

This gives you the option to change either your horizontal or vertical axis label. 

Click through the options to see what they do to the Title of your Axis labels.

Enjoy… Microsoft Excel 2007

Page 121

Create charts Modify chart type and layout



Click Legend.

This gives you the option to change the way the legend (the key at the side that tells you what the bars in your chart are referring to). 

Click through the options to see what they do to your legend.



Finish with your legend on the left of the chart.



Click Data Labels.

Data Labels will place values in your columns. 

Click through the options to see how they each work.



Finish with Outside End.



Click Data Table.

A Data Table displays in your chart showing the data in your spreadsheet with which you created your chart. Sounds confusing?

Page 122



Click through the options to see how they each work.



Finish with Show Data Table.

Enjoy… Microsoft Excel 2007

Create charts Modify chart type and layout

Exercise 57 – Add or remove Trendlines You can add lines to your chart to help show trends.



Make sure you are working on Version 3.



Click on chart 2.



Go to Chart Tools, Layout tab, Analysis group.

Add Trendline



Click Trendline.



Click Linear Trendline and add a Trendline for each of January, February and March.

These are meaningless Trendlines, so remove them again. If you need a challenge, create a chart for which you can generate meaningful Trendlines.

Enjoy… Microsoft Excel 2007

Page 123

Create charts Modify chart type and layout

Remove Trendline You could easily remove them, as follows – but don’t: 

Undo



Click Trendline, None.



Print if you want to see what your chart looks like.



Save your spreadsheet to its current name – Version 3.



Then save your spreadsheet again using the Save As command and name it Version 4.

Now you have four copies. Continue working on Version 4.

Exercise 58 – Format Axes and Gridlines

Page 124



Make sure you are working on Version 4.



Click on chart 2.



Find the Axes group on the Chart Tools, Layout tab.

Enjoy… Microsoft Excel 2007

Create charts Modify chart type and layout

Axes You can choose to display or hide your Axes labels, and label the Axes in different ways. 

Experiment with a range of the options to see what they do.

Gridlines You can choose to display or hide your gridlines and change the range of gridlines you display. 

Experiment with a range of the options to see what they do.



Finish with Major Gridlines on the horizontal Axis only.

Exercise 59 – Change Column Type 1



Click on Chart 2.



Click Chart Tools, Design tab, Type group, Change Chart Type.



Select Column and click through the options to watch your chart. Look particularly at:  Clustered Column  Stacked Column  100% Stacked Column



Also, check through the cylinders, cones and pyramids. They may not be useful for your current purposes, but think about when they might be useful.

Enjoy… Microsoft Excel 2007

Page 125

Create charts Modify chart type and layout



Click Chart Tools, Design tab, Type group, Change Chart Type, select Column and click through the 3D options to watch your chart. Look particularly at:  3D Clustered Column  Stacked Column in 3D  100% Stacked Column in 3D  3D Column



Also, check through the cylinders, cones and pyramids. They may not be useful for your current purposes, but think about when they might be useful.



Finish up with 3D Clustered Column.

Exercise 60 – Change Chart Type from Column to Bar Even at this late stage you can change the type of chart if you want. 1

Page 126



Right click your second chart and click Copy Here to create a third chart.



Drag it to somewhere convenient.



Click on the third chart.



Click Chart Tools, Design tab, Type group, Change Chart Type.



Select Bar and click through the options to see what happens to your chart.



Finish with Clustered Bar.

Enjoy… Microsoft Excel 2007

Create charts Modify chart type and layout



Print charts 2 and 3 if you want to see what they look like.



Save your spreadsheet to its current name – Version 4.

Exercise 61 – Check printed charts for errors Once you have printed copies of your charts, you should check them for errors. 1



Check the text for spelling errors.



Check the data for input errors. Do all your numbers make sense?



Ensure the chart is communicating what it is supposed to be communicating.



Check your formatting to be sure it does make the chart easier to read.



Fix any errors you find.



Make any improvements you want to the layout.



Reprint and check until you know everything is correct.

   

To reinforce this learning, you can complete some Microsoft On-line Training.

Microsoft On-line Training – Charts I: How to create a chart in Excel 2007 http://office.microsoft.com/training/training.aspx?AssetID=RC10175736 1033

Enjoy… Microsoft Excel 2007

Page 127

Create charts Select chart type and design

Select chart type and design We have looked at two types of chart, column charts and bar charts. However, there are many other ways that you can display your data. The trick with charts is to select the one that best communicates your information to someone else – a person who is less familiar with your information or uncomfortable around numbers.

Exercise 62 – Create a Line Chart 1



Save your file to a new version number – probably Version 5.



Click on each chart and press



Highlight cells A2 to D5.



Click Insert tab, Charts, Line.



Select Line from the menu.

to remove them.

The chart below displays. This kind of chart makes the improvement of each salesperson relative to the rest of the team obvious, and shows where problems in performance may be occurring.



From Chart Tools, Design tab, Chart Layout, select a layout that includes a Chart title, the Vertical Axis Title and a Legend.



Type Comparative sales into the Chart Title.



Type Cases Sold into the Vertical Axis Title.

Your chart will look something like this.

Page 128

Enjoy… Microsoft Excel 2007

Create charts Select chart type and design

Exercise 63 – Create a Pie Chart from non-adjacent data This time you want to create a chart from data in columns or rows that are not adjacent to one another. 1



Type Total quarterly sales in cell E2.



Place your cursor in cell E3.



On the Home tab, Editing group, find and click the

next to

This menu displays. 

Click

Enjoy… Microsoft Excel 2007

Page 129

Create charts Select chart type and design

This formula displays in cell E3, and there is a flashing square around cells B3:D3. The formula on the Formula Bar says =SUM(B3:D3). 

Press

to select.

Copy formula 

Place your cursor on the bottom right of the cell so that it turns into a plus sign.



Press down your left mouse button and drag down over E4 and E5.

Your formula has now been copied to E4 and E5. 

Click on E4 and you will see that the cell reference has changed to B4:D4).



Click on E5 and you will see that the cell reference has changed to B5:D5).

It was changed automatically by Excel because the entry in E3 was a relative cell reference. If you have forgotten what this means look back to Exercise 18 – Identify and copy a relative cell , page 40. 6

Page 130

Create Pie chart 

Click cell A3 and then drag to cell A5 to select just the names.



Hold down your



Click cell E3 and drag to cell E5 to select the totals.



Click Insert tab, Charts, Pie.

key.

Enjoy… Microsoft Excel 2007

Create charts Select chart type and design



Select each of the Pie options to see what they each do including:  Pie  Exploded Pie  3-D Pie



Finish with 3D Pie.



From Chart Tools, Design tab, Chart Layouts, click your way through the various options to see what they each do.



Finish with Layout 6.



From Chart Tools, Design tab, Chart Styles, click your way through the various options.



Pick one you like.



Type Proportion of Quarterly Sales into the Chart Title.

Your chart will look something like this.

Enjoy… Microsoft Excel 2007

Page 131

Create charts Select chart type and design

Exercise 64 – Create a chart for average monthly sales 1



Type Average monthly sales in cell F2.



Place your cursor in cell F3.



On the Home tab, Editing group, find and click the

next to AutoSum .

This menu displays. 

Click

This formula displays in cell F3, and there is a flashing square around cells B3:E3. 

You don’t want E3 in the formula so click B3 and drag to D3.



Press

to select.

Guiussani’s average monthly sales figure is 250 cases. 

Place your cursor on the bottom right of the cell so that it turns into a plus sign.



Press down your left mouse key and drag down over F4 and F5.

Your formula has now been copied to F4 and F5. You now have totals for each salesperson.

Page 132

Enjoy… Microsoft Excel 2007

Create charts Select chart type and design

Create a chart This time you get to select which kind of chart is best for this information. 

Click cell A3 and then drag to cell A5 to select just the names.



Hold down your



Now click your way through the full range of chart options and find the type of chart and layout that you think is best for this chart. Experiment with:

key, click cell E3 and drag to cell E5, to select the totals.

 Chart Type  Chart Layouts  Chart Styles Below is a very simple chart. Can you do one that looks better?

Exercise 65 – Show maximum & minimum monthly sales While we are looking at formulas, let’s look at maximum and minimum. 1



Type Maximum monthly sales in cell G2.



Place your cursor in cell G5.



On the Home tab, Editing group, find and click the

Enjoy… Microsoft Excel 2007

next to

Page 133

Create charts Select chart type and design

This menu displays. 

Click



Highlight cells B3 to D5.



Press

to select.

Cell G5 is now displaying 420, which is the most sales made in any month. 5



Type Minimum monthly sales in cell H2.



Place your cursor in cell H5.



On the Home tab, Editing group, find and click the

next to

The menu displays. 7



Click



Highlight cells B3 to D5.



Press

to select.

Cell H5 is now displaying 50, which is the least sales made in any month.

Page 134

Enjoy… Microsoft Excel 2007

Create charts Select chart type and design



See if you can find any useful way of showing this information in a chart. Particularly check out:  Area chart  Scatter chart  Bubble chart  Doughnut chart



Try to choose a chart type you have not used before. The one below is doughnut.

Exercise 66 – Explore chart formatting If you enjoy experimenting with graphical look and feel this is a MUST. 1 2

Create another pie chart.

Click on the Help icon

In the Search field type Present your data in a pie chart, and select that option.

Follow through the instructions to explore a range of formatting options.

Can you work out how they achieved the formatting in the second pie chart?

Enjoy… Microsoft Excel 2007

Page 135

Name and store spreadsheet Why document names and document storage locations matter

11: Name and store spreadsheet So far you have been saving your files to a reasonably sensible file name, and to either your flash drive or a location on your hard drive. However, once you are doing a lot of work, file storage is not as simple as this.

Why document names and document storage locations matter There are all sorts of reasons why you should pay attention to: 

how you name your files, and



where you store them

Storage location conventions and protocols If you are in any way unsure of your ability to manage File management in Windows Explorer, complete the following Microsoft training.

File and folder management in Windows Explorer If you are not proficient with file management, complete the following on-line training.

Microsoft On-line Training – Organise Files and Folders (Windows XP) If you have Windows XP, complete the training at this site. http://www.microsoft.com/windowsxp/using/setup/maintain/filemgmt. mspx This covers:

Page 136



Create a folder structure



Browsing files



Searching for files

Enjoy… Microsoft Excel 2007

Name and store spreadsheet Why document names and document storage locations matter

Microsoft On-line Training – Working with Files and Folders (Windows Vista) If you have Windows Vista, complete the training at this site.

For Windows Vista go to http://windowshelp.microsoft.com/Windows/en-US/default.mspx, and select Files and folders, Working with files and folders. This covers: 

What are files and folders?



How Windows organizes your files and folders



Understanding the parts of a folder



Viewing your files in a folder



Finding your files



Copying and moving files and folders



Creating and deleting files



Opening an existing file

Folder naming conventions and path size limits Many of the same rules apply to both naming files and naming folders. Names should be as short as they reasonably can whilst still being self explanatory. There are some size limitations on the total length of file paths. Once you have exceeded your limit, you will no longer be able to open your files. So it is important to name folders and files effectively and keep them as short as possible. These limits are: 

for Windows XP, up to 255 characters



for Windows Vista, up to 260 characters

A file path includes all the characters of folders, subfolders and filenames, for example: 

C:\Documents and Settings\Francesca Violetta\My Documents\By Land and By Sea\Advertising Materials\Magazine Advertisements\advertising file.pdf

This is quite a long path, but it is still only 144 characters including spaces. If you remove the spaces, it is 132 characters. If you remove wasted words and abbreviate other obvious words, you could reduce it a lot further.

Tip If you find that you are not able to open a file from Windows Explorer when you can see it there, explore the possibility that there are too many characters in the path and file name combined.

Some folder naming conventions Even for your home computer, consider setting up and applying some simple rules, for example:

Enjoy… Microsoft Excel 2007

Page 137

Name and store spreadsheet A word on file extensions



Roll words together rather than using spaces, eg ChristineKent.



Use lower case and delimit words with an uppercase letter.



Use a dash instead of an underscore if the file name is long (an underscore is hard to detect once the name becomes a URL).



Use short words if possible, as they are easier to see, remember and type.



Use abbreviations and acronyms where they are well understood, but ONLY if they are well understood.

A word on file extensions File extensions are the three of four letters that come after the dot in a file name, for example: 

Sample Budget.xlsm

These few letters tell your computer what program the document has been created in and what software program it can be modified in. If you click on your document in Windows Explorer, your computer reads that it has an .xlsm extension and so opens Excel 2007 for you. Here are some examples of file extensions, and the program the file belongs to.

Page 138

.docx

Microsoft Word 2007

.docm

Microsoft Word 2007 (macro enabled)

.doc

Microsoft Word 2003 or earlier

.xlsx

Microsoft Excel 2007

xlsm

Microsoft Excel 2007 (macro enabled)

.pptx

Microsoft PowerPoint 2007

.html

A web page

.pdf

A PDF file – the format used for a lot of files you can download from the Web. They are “read only”.

Enjoy… Microsoft Excel 2007

File security How to prevent others opening a file

12: File security There are several levels at which access to files can be controlled. For a home computer it is unlikely that you will want to do more than applying passwords to your own file.

How to prevent others opening a file You can password control a file so that it can only be opened by someone who knows the password, or so that it can only be modified by someone who knows the password.

Manage Excel 19 – Apply, modify and remove a password on a file In this exercise, you are going to use the Microsoft Office Online Help to work out how to perform the following actions. 1



Open a file that you have saved – preferably one that you will not need again.



Press the



Click the little round button with the question mark at the top right.

2 key. OR

The Excel Help window displays. 

In the Excel Help window, type the word password into the Search field.



Click



Click Demo: Set a password to open or modify a workbook, document, or presentation.



When the help window displays the instructions, drag it to a place on your screen so that you can still see your file in Excel.



Follow the instructions. You can either play the demo first, or go straight to the text instructions:

 Click Set a password to open or modify a document, workbook, or presentation.

Enjoy… Microsoft Excel 2007

Page 139

File security How to prevent others opening a file



Work your way through the following section, using the file you selected in Step 1. Do not encrypt. Go straight to:  Set a password to open a document …and set the same password to open and modify.



Once you have completed the instructions, save and close your document, then try to open it again.

You will be presented with this dialog box.



Type your password.



Click



Now, using the same file complete:

 Change a password and  Remove a password Note that you have set passwords that block both opening a file and modifying a file, but you could choose to do just one or the other. Tip It can be dangerous to lock files with a password particularly if you are likely to forget that password.

Manage Excel 20 – Protect your spreadsheet – the quick way Note that there is an easier way of controlling access to a spreadsheet.

Page 140



Click Review tab, Changes group, Protect Sheet

Enjoy… Microsoft Excel 2007

File security How to prevent others opening a file

The Protect Sheet dialog displays. This option allows you to control the current worksheet. 

Tick Protect worksheet and contents of locked cells.



Look through each of the options to see what you can allow users to modify even when the overall sheet is locked.



Select a few.



Type in your password.



Click



Click Review tab, Changes group, Protect Workbook.

The Protect Structure and Windows dialog displays. This option allows you to control the entire spreadsheet. 

Select an option and type a password.



Click

Once you have protected your spreadsheet, the Changes group will now look like this.



Click Unprotect Sheet or Unprotect Workbook.



Type your password.



Click

Enjoy… Microsoft Excel 2007

Page 141

File security Protocols for backing up and storing files

System security System security is a broad subject that is beyond the scope of this book. However, it is something you really need to understand if you are to protect your home computer from attack through the internet. You may want to do this Microsoft training to find out more about the possible threats to your computer.

Microsoft On-line Training – Find out about security risks http://office.microsoft.com/training/training.aspx?AssetID=RC101941421 033

Protocols for backing up and storing files File backup is where you create a duplicate copy of the files that are on your hard drive. You would normally backup your files regularly to another storage device such as another computer drive, zip drive, tape etc. This is done because files can get lost. Your hard disk can break down; your computer can be stolen; fire, heat or water can destroy or damage files.

The three levels of backup There are three levels of backup you may want to consider for your home computer: 

the backups that your working program, in this case, Excel, can be set to do for you automatically



the backups that you do for yourself for files that you are storing on your own computer. You should backup your own files using a floppy disk, flash drive or other local memory device. You can back your own files up in two ways:



Page 142

by copying them onto a backup disk. The files on the backup disk are identical to the files on the hard drive and can be used in the same way as the files on the hard drive. You can easily do this by saving or copying your files to a floppy disk, CD, flash drive or backup memory drive. by backing them up using a backup program. This kind of program compresses the files so that they need less space, and they have to be decompressed before they can be used again.

Enjoy… Microsoft Excel 2007

File security Protocols for backing up and storing files

Manage Excel 21 – Save AutoRecover files Excel will always try to save a file if your system locks up. It will then present you with the saved file when you re-boot – as long as you tell it to. 1



Click



Click



Look in the Save workbooks frame.

Save files in this format You have been telling Excel to save your files with an .xlsx extension. This tells Excel and any other program that this is an Excel file that can be opened in Excel 2007. 4

Save AutoRecover information every … 

Type 5 into this text box to tell Excel to save your file every five minutes no matter what you do. This way you will never lose more than 5 minutes worth of work.

AutoRecover file location … This is where you will tell Excel where to store the AutoRecover file. There is not much point in having a backup if you have no idea where to find it, so send it to somewhere you will remember, perhaps the same location as your working files.



Click



Find and select your preferred location – make sure it is somewhere that you will remember.

Default file location … Every time you open a file, you have to browse to find the location. However, you can tell Excel to offer you a specified location. If you are working in the same place all the time, you can set Excel to offer you this location automatically, for example, if you have been saving to your flash drive, you can set word to offer you the E:/ drive. 

Click



Find and select your preferred location.

Enjoy… Microsoft Excel 2007

Page 143

File security Protocols for backing up and storing files

Perform backups manually The simplest way to back up files is to “drag” them onto a floppy disk or flash drive or other memory device.

Manage Excel 22 – Backup your files manually to a removable memory device You can save your files to a floppy disk or a flash drive this way, but not to a CD or an external disk. 1



Open Windows Explorer. (If you need instructions on how to do this, go to File and folder management in Windows Explorer, page 136.)



Find your file.



Find the destination location, for instance a floppy disk drive or a flash drive.



Click the file with the left mouse button.



Drag it to the destination location.

Note that if you drag a file to a location on a different drive it will copy the file, but if you drag it to another location on the same drive, it will move the file.

Performing backups using a backup utility – Windows XP If the Windows XP Home Edition Backup software is not on your computer, you can install it by following the link below and following the instructions. 

http://www.microsoft.com/windowsxp/using/setup/maintain/backupsw.mspx

Manage Excel 23 – Perform backups using a backup utility – Windows XP If you have this software on your own computer you can follow these instructions to backup your files. In this exercise we will do a backup of files from the C:/ drive to the removable disk on Drive E:/. 1

Page 144



Click



Click

Enjoy… Microsoft Excel 2007

File security Protocols for backing up and storing files



Click System Tools.



Click Backup.

The Backup or Restore Wizard dialog box displays. 

Click

The Backup or Restore Wizard dialog box gives you a choice of backup or restore. 

Click the Back up files and settings option button.



Click

Enjoy… Microsoft Excel 2007

Page 145

File security Protocols for backing up and storing files

The Backup or Restore Wizard, What to Back Up dialog box gives you a choice of data you want to backup. 

Click Let me choose what to back up.



Click

The Backup or Restore Wizard, Items to Back Up dialog box offers you a list of your files. 

Page 146

Find the area of your drive that you are backing up.

You are asked to place a tick in the check box next to the area, folder or document you want to back up. 

Click the relevant folder or files to add the tick.



Click

Enjoy… Microsoft Excel 2007

File security Protocols for backing up and storing files

You are asked to select the backup drive. 

Click to find the place you are backing the files up to.



Click the correct location. It will be returned to the Choose a place to save your backup files field.



Type a name for this backup in the field below.



Click



Click

This message box displays…

Enjoy… Microsoft Excel 2007

Page 147

File security Protocols for backing up and storing files

…followed briefly by this message box telling you a summary of the files you have selected…

…followed by this message box that will tell you how long the backup will take and track its progress.

When the backup is finished, this dialog box displays. 

Page 148

Click

Enjoy… Microsoft Excel 2007

File security Protocols for backing up and storing files

Manage a home computer If you are managing your own home computer, check out the instructions below and use them to schedule automatic backups of your data.

Microsoft On-line Training – Back up your files http://www.microsoft.com/windowsxp/using/setup/maintain/backupfiles .mspx

There are other devices that can be used for backups such as CDs and external disks, but as these come with their own software and user interfaces, we cannot cover them here.

Enjoy… Microsoft Excel 2007

Page 149

Adjust and print spreadsheets and charts Review and edit documents

13: Adjust and print spreadsheets and charts By now, you will have completed two projects. You have spell checked them; print previewed them and printed them, as many times as it takes to get a good finished product. A time will come when your documents are finished and “ready to go”. They are ready to “publish”.

Review and edit documents Design review The first serious review you do on a document is to assess it to ensure it meets its goals. Look over your printed spreadsheets and charts now and ask: 

Does each spreadsheet or chart talk to the audience?



Are they easy to read?



Will they create their intended impact on the audience?

This is the highest level of edit. Is the design right?

Business or technical review If you have included business or technical data supplied by someone else, and you are not the expert, your finished work should go back to that expert to ensure you have interpreted them correctly. This is a critical step. No matter how well you think you have interpreted another person, you WILL miss something important, so never assume that you can ignore this step and that it is just a “rubber stamp”. For this check you must ensure that: 

Your input data is correct



Your formulas are correct



Your results are correct.

Grammar and style review This review is often missed for a spreadsheet, but you should check the following. 

Are there any words that you should change to something more suitable?



Is your information in a logical sequence?



Is your level of language suitable for your audience?



If you have headings, are they meaningful?



Do you have too many or too few headings?

Page 150

Enjoy… Microsoft Excel 2007

Adjust and print spreadsheets and charts Review and edit documents



Is there redundant material that could be cut?



Have you used any sexist language?

Presentation review This can be particularly important for charts. 

Does it look good overall or is it cluttered?



Is it centred on the page?



Are the colours good if you are printing in colour?



Have you used appropriate graphics?



If you have company standards for display, have you used them?

Proofread Then you look over your document for silly mistakes. 

Have you made any spelling mistakes?



Have you used capital letters where you should?



Is your punctuation OK?

Enjoy… Microsoft Excel 2007

Page 151

Index

Index 100% Stacked Column, 125 100% Stacked Column in 3D, 126 3D Clustered Column, 126 3D Column, 126 Access Keys, 67, 69 Active Cell, 15 add Trendline, 123 Alignment group, 17, 81 Analysis group, 123 Area chart, 135 AutoFill, 29, 43, 111 automatic recalculation, 39 AutoRecover, 143 AutoSum, 132 average, 132 Axes group, 124 Axis Title, 121 backup manual, 144 utility, 144 badges, 70 Bubble chart, 135 calculate totals, 38 Calculation group, 39 Calibri, 65 Cell Address, 15 Cell Labels, 16, 21 Cell Reference, 15 Cell Values, 21 cells, 15 Cells group, 33, 73 Centred text, 82 Change Chart Type, 126 change default font, 73 Changes group, 140 Chart Layouts group, 116 Chart Styles, 117 Chart Title, 121 Chart Tools Design tab, 125 Chart Tools Format tab, 118 Chart Tools Layout tab, 121, 123 Chart Tools tab, 114 Charts group, 112 click, 5 Clipboard group, 42, 73 Clustered Column, 112, 125 Color Model, 78 Column Heading, 15 columns, 15 Commands, 12 Contextual tab, 97 Contextual tab, see tabs, 90 Control Panel, 46 copy formula, 42, 130 currency, 52 Current Date, 92

Page 152

Current Time, 92 Data Labels, 122 Data Table, 122 date range, 29 decimal points, 52 delete rows, 31 Design group, 114 dialog box Print, 115 Dialog Box Launcher, 14 dialog boxes Colors, 77 Excel Options, 64, 68 Open, 28 Page Setup, 96 Save As, 23, 25 Document names, 136 Document storage, 136 double click, 5 Doughnut chart, 135 down arrows, 14 drag, 18 editing business or technical review, 150 design edit, 150 grammar and style edit, 150 presentation edit, 151 proofread, 151 enter fractions, 51 enter negative numbers, 51 Excel 2007 Solution Center, 110 Excel Help window, 107, 139 file extensions, 138 file locations, 22 file names, 22, 136, 137, 138 file path, 6, 137 file security, 139 folder structures, 6 folders, 6, 137 folders naming conventions, 137 Font group, 73 footer, 90 format chart elements, 117 format columns, 119 format date, 50 format font, 76 format numbers, 52 format time, 50 Formula Bar, 16, 39 formulas, 36 Formulas tab, 39 fractions, 51 Gradient, 120 grid, 15 groups Alignment, 81

ÂŠ Christine Kent

Enjoyâ&#x20AC;Ś Microsoft Excel 2007

Index

Analysis, 123 Axes, 124 calculation, 39 Cells, 73 Changes, 140 Chart Layouts, 116 Charts, 112 Clipboard, 42 Clipboard, 73 Design, 114 Document Views group, 60 Font, 73 Header & Footer Elements, 91 Labels, 121 Macros group, 60 Navigation, 91 Number, 50, 73 Page Setup, 94, 95 Paragraph, 81, 84 Scale to Fit, 100 Shape Styles, 119 Show/Hide, 60 Show/Hide group, 60 Styles, 84, 85 Type, 125 Window group, 60, 62 WordArt Styles, 118 Zoom, 61 Zoom group, 60, 62 Groups, 12 header, 90 Header & Footer Elements group, 91 Header & Footer Tools, Design, 91 Help, 107 Search, 109 Home tab, 17, 73 horizontal axis, 113 insert a footer, 91 insert a header, 90 Insert rows, 29 insertion point, 5 Internet Explorer Browser, 6 Key Combinations, 67 keyboard keys, 34 Labels group, 121 Landscape, 94 left click, 5 Left Justified text, 82 left mouse button, 5 Legend, 122 logo, 90 Long Date, 50 Margins, 95 maximise window, 63 maximize the Ribbon, 59 maximum, 133 memory device, 24, 26, 113 menu bar, 59 Microsoft Office, 9 Microsoft Office Excel 2007, 9 Microsoft Office Themes, 87 Mini toolbar, 118

Enjoyâ&#x20AC;Ś Microsoft Excel 2007

minimise window, 63 minimize the Ribbon, 59 minimum, 133 multiply, 36 Name Box, 34 Navigation group, 91 notification area, 13 Number group, 50, 73 Open spreadsheet, 27 Orientation, 94 Page Break Preview, 99, 101 Page Layout tab, 95 Page Setup dialog box, 96 Page Setup group, 94, 95, 98 Page Setup options, 94 Paper Size, 95 password, 139 change, 140 remove, 139, 140 set, 139 path size limits, 137 percentages, 52 Pie chart, 129 pointer, 5 Popular Commands, 57 Portrait, 94 Print Formulas, 102 Values, 102 Print Area, 95 Print dialog box, 115 Print Preview, 97 Print Sequence, 99 Print Titles, 96 Protect Sheet, 141 Protect Workbook, 141 Quick Access Toolbar, 55, 56, 58, 59 add icons, 56, 58, 59 Quick Launch toolbar, 13 range of cells, 38 range of dates, 29 read only, 138 recalculate, 39 relative cell reference, 130 remove Trendline, 124 restore, 145 Review tab, 140 RGB, 78 Ribbon, 12 right click, 5 Right Justified text, 82 right mouse button, 5 Row Heading, 15 rows, 15 rules of precedence, 37 Save As, 23, 25 Save As type, 24, 26 save spreadsheet, 33 Scale to Fit, 98 Scale to Fit group, 100 Scatter chart, 135 screen tips display, 63

ÂŠ Christine Kent

Page 153

Index

ScreenTips, 69 scroll bar, 5, 34 scroll wheel, 5, 34 security, 139 set print sequence, 100 Shape Effects, 119 Shape Fill, 119 Shape Outline, 119 Shape Styles group, 119 Sheet Options Gridlines, 100 Headings, 100 Short Date, 50 shortcuts, 67 spell checker, 103 Stacked Column, 125 Stacked Column in 3D, 126 standard colors, 77 Start button, 7, 9, 46, 47 Styles group, 84, 85 SUM, 38 Switch Row/Column, 114 tabs Chart Tools, 114 Chart Tools Format, 118 Chart Tools Layout, 121, 123 Chart ToolsDesign, 125 Contextual, 90 Custom, 78 Formulas, 39

Page 154

Header & Footer Tools, Design, 91 Home, 73 Page Layout, 95 Review, 140 Standard, 77 View tab, 59, 60, 62 Tabs, 12 task bar, 12 text alignment, 81 text direction, 83 text wrap, 17 Texture, 120 theme colors, 77 theme colours, 88 theme fonts, 89 toggle, 76 Trendlines, 123 type face, 73 Type group, 125 Unprotect Sheet, 141 Unprotect Workbook, 141 vertical axis, 113 Windows Explorer, 136 Windows File Manager, 6 Windows Vista, 9 WordArt Styles group, 118 Worksheet, 15 zoom in, 61 zoom out, 61

Enjoy… Microsoft Excel 2007