Introduction To Microsoft Office

Lutz F. Krebs

Introduction to Microsoft Office

© 2001 – 2002 Universiteit Maastricht. All rights reserved. No part of this publication may be reproduced, in any form or by any means, without prior, written permission from the publisher. PICTURE CREDITS Section cover background: size-isnt-everything.co.uk TYPEFACES Headlines are set in TheSansCorrespondence © TheTypes b.v., Lucas de Groot. Function calls are set in Arial Narrow © The Monotype Corporation plc. Image components are labeled in Arial © The Monotype Corporation plc. Body text and side notes are set in Times New Roman © The Monotype Corporation plc. Keys are set in RatCaps 3D PC and RatKeys 3D PC © Quadrat Communications, David Vereschagin. TRADEMARKS Product names, logos, designs, titles, words or phrases mentioned in this publication may be trademarks, service marks, or trade names of companies or other entities and may be registered in certain jurisdictions or internationally. DISCLAIMER WE HAVE USED EXCEPTIONAL CARE IN PREPARING THIS BOOK. NONETHELESS, PUBLISHER AND AUTHOR MAKE NO CLAIMS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE BOOK AND SPECIFICALLY DISCLAIM ANY WARRANTIES REGARDING THE USEFULNESS FOR A PARTICULAR PURPOSE. THERE ARE NO WARRANTIES EXCEPT THOSE GRANTED HERE. THE ADVICE GIVEN IN THIS PUBLICATION IS NOT GUARANTEED TO PRODUCE ANY PARTICULAR RESULTS AND MAY NOT BE SUITABLE FOR EVERY INDIVIDUAL OR UNDER PARTICULAR CIRCUMSTANCES. NEITHER THE PUBLISHER NOR THE AUTHOR SHALL BE LIABLE FOR ANY LOSS OF PROFIT OR ANY OTHER COMMERCIAL DAMAGES, INCLUDING BUT NOT LIMITED TO SPECIAL, INCIDENTAL, CONSEQUENTIAL OR OTHER DAMAGES. ACKNOWLEDGEMENTS I would like to thank the following for their continued support: Dirk Tempelaar. The computer whiz kids at ACO and ICTS. Andreas van Rienen and Carsten Sturmann. Christiane Arndt. FEEDBACK We are happy to hear from you. You can contact the author directly at [email protected].

Version 1.2 – 28/07/02 Maastricht University P.O. Box 616 6200 MD Maastricht Netherlands

CONTENTS A. GENERAL TOPICS ●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

1. Introduction 3 2. Common Elements of Microsoft Office Programs 7

B. MICROSOFT WORD ●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

1. Introduction 19 2. Entering & Editing Text 23 3. Formatting Your Document 33

C. MICROSOFT EXCEL ●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

1. Introduction 41 2. Entering Data & Making Calculations 45 3. Formatting Your Spreadsheet 57 4. Analyzing & Displaying Data 69

D. MICROSOFT POWERPOINT ●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

1. Introduction 81 2. Building Your Visual Aids 85 3. Refining & Rehearsing 97

APPENDIX ●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

A. A Selection of Useful Excel Functions 107

GENERAL TOPICS

Image: size-isnt-everything.co.uk

●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

1. INTRODUCTION Office packages are bundles of programs that help you with daily ‘office’ chores, such as writing documents, making extensive calculations, preparing presentations, maintaining databases etc. While there are a number of competing Office suites, Microsoft Office is the unchallenged market leader and is used by the majority of companies around the world. An Overview This manual is meant as an introduction to the three most important programs in Microsoft Office 2000: Word, Excel and PowerPoint. In the first section, we will discuss all the elements that these three have in common. We then devote a section to each of the programs. In the section on Word, we discuss how you can use this word processor to type, edit and format documents such as writing assignments. In the third section, we will learn the basics about Excel. Excel is Office’s spreadsheet program, and it is used heavily in your Quantitative Methods subjects. The fourth section then provides a step-by-step guide to creating presentations in PowerPoint. What you need to get started SMR (Studenten Micro Ruimte) Synonym for computer lab at our university.

Strictly speaking, you do not need anything. All of the above programs are installed in our faculty’s computer labs (or SMRs as they are called at our university). There are two SMRs at the Faculty of Economics and Business Studies, and both are located in the main building at Tongersestraat 53. To use a PC there, you need your student ID number and a password that has been mailed to you together with your student ID card. THE COMPUTER ROOMS LOCATION NUMBER OF PCS

SMR 1 Rooms 3.047 – 3.061 120

SMR 2 Rooms 0.012 – 0.014 60

9 a.m. – 10 p.m. 9 a.m. – 5 p.m. closed

9 a.m. – 5 p.m. 9 a.m. – 5 p.m. closed

OPENING HOURS

Mon – Thu Fridays Weekends MORE INFO

www.fdewb.unimaas.nl/smr/

In general, the SMRs should only be used for study related activities, e.g. downloading and reviewing lecture notes, writing papers, registering

4 Introduction to Microsoft Office ●●●●●●●●●●●●

for courses etc. If you want to write personal emails or surf the web for fun, you are asked to restrict yourself to the early morning (9 – 10 a.m.) or the evening (after 5 p.m.). Even with these rules in place, the SMRs tend to get crowded often. When the SMRs are closed, you can walk over to the library, which offers a small computer room of its own. Be aware that other faculties also use this computer room – you may have to wait (possibly a long time) for a PC to become available. If you know in advance that you need a PC for a study related reason, you can make a reservation for up to two hours per day. THE LIBRARY ADDRESS PHONE WEBSITE

Bonnefantenstraat 2 (043) 388 34 27 www.ub.unimaas.nl

OPENING HOURS

Mon – Thu Fridays Saturdays Sundays

8.30 a.m. – 10 p.m. 8.30 a.m. – 9 p.m. 10 a.m. – 5 p.m. 12 a.m. – 5 p.m.

Our computer labs can get very crowded during peak times, and it may be a good idea to buy a used or new PC to work at home. In that case, you will also need to buy a copy of Microsoft Office, as Office is a commercial program. The cheapest way to get Microsoft Office 2000 is to buy it from the ICT Service Center. Office 2000 Professional costs 25 €1 for students and includes Word, Excel and PowerPoint, plus other useful programs such as Microsoft Outlook. THE ICT SERVICE CENTER ADDRESS PHONE EMAIL WEBSITE

Looiersgracht 14, Room 0.011 (043) 388 35 64 [email protected] www.icts.unimaas.nl

OFFICE HOURS

Phone/Email

Walk in

Weekdays Saturdays

8 a.m. – 10 p.m. 10 a.m. – 4 p.m.

8.30 a.m. – 5 p.m. closed

On the Use of this Book In this book, we assume that you have access to a PC with the appropriate software. We also presume that you know in general how to 1

These prices are sharply reduced in comparison to normal retail prices, because Maastricht University (in cooperation with other Dutch universities) buys large quantities of educational licenses.

General Topics: Introduction 5 ●●●●●●●●●●●●

operate a Windows-based PC. We will take it from there. This manual is not meant to be all-encompassing. It is meant as an introduction and as a guide. We will focus on getting you started, and in doing so will ignore a number of useful advanced features. You are encouraged to go beyond the contents of this book and explore the tools that you are working with. Some of your future courses will also expand upon the contents in this manual, particularly the QM courses make extensive use of Microsoft Excel. If you feel that you need further guidance, there are a number of excellent printed resources. Personally, we find the jargon-free language and useful hints of the ‘For Dummies’ series to be particularly appealing, but there are certainly many other useful and well-written computer books out there. There is also a lot of helpful information on the web. This book is focused on showing you how to accomplish certain tasks in Microsoft Office. Be aware that there are usually several ways to get things done. We will endeavor to show you as many of them as possible. In doing so, we will use the following conventions: ·

Toolbar buttons that initiate a certain function are usually displayed in the margin together with a brief description of what the button does.

·

If you need to press a key on the keyboard, we will indicate this by printing the key, e.g. / implies that you need to hit ‘enter’. Frequently, key combinations are used to accomplish tasks. An example of such a key combination would be c + P, which means that you should press and hold the ‘ctrl’ key while pressing ‘p’.

·

When you need to enter more than a few characters, we will not print the individual keys, because that would be space consuming and hard to read. Instead, we will indicate the text using a special font, as demonstrated here: ‘enter this’.

·

Some of the options cannot be initiated by using a keyboard shortcut or an on-screen button. Such options are can usually be found in the menu bar. If we want you to select an option from the menu bar, we will list the names of the options in chronological order. E.g. ‘File’ „ ‘Print’ would imply that you click on ‘File’ in the menu bar, and then select ‘Print’ from the menu that appears. Instead of clicking on the items, you can also use your keyboard: press a and the underlined letter, in our example a + F. Once the menu is open, simply pressing the underlined letter is sufficient. E.g. after pressing a + F, pressing P will be enough to take you to the print menu.

A toolbar.

A menu bar.

6 Introduction to Microsoft Office ●●●●●●●●●●●●

·

The Start menu.

Finally, we will use a similar notation when you need to select options from the Start menu, e.g. ‘Start’ „ ‘Programs’ „ ‘Microsoft Word’ will launch Word. Once again, you can use keyboard shortcuts: press the Windows key (ÿ), followed by the underlined letters.

If you are stuck… If you cannot find the answer here, there are still a number of ways to get help. If you are stuck using a certain program, the first thing you can try is pressing the 1 key. Pressing 1 is like asking the computer for help. In most cases, the appropriate program will then give you additional information on whatever you are currently doing. The intricacies of the help system are discussed in more detail in the second chapter of this section. There are, however, cases when your computer does not understand your problem (because the persons that built the software did not foresee it) or when your PC is not able to help you (because it is malfunctioning). In such cases, you may want to check out a helpful website, Tech24 SelfHelp at www.tech24inc.com/help.html. Tech 24 offers self-help links that are sorted by category (e.g. ‘Internet’, ‘Email’, ‘Outlook’). They also offer live support from certified technicians. You can also try contacting the producer directly. If you have trouble with any part of Office, you might want to visit www.microsoft.com to see if they know the answer to your question. However, some producers do not offer a lot of support or charge money for it. Another option is to call or email the computer whiz kids at the ICT service center of our university. They offer support for the software used throughout the university, which includes among others Windows, Microsoft Office, Internet Explorer, and the McAfee virus scanner. (See the info box on p. 4 for their contact information.) Finally, befriending a geek may be the best solution. Geeks are by nature friendly, helpful and extremely well versed in all things computer. If you can count one of them as your friend, he (or in rare cases she) probably won’t mind dropping by and helping you out when you are having trouble with your PC. An invitation to dinner or to the movies in return won’t hurt either. We wish you success in working with Microsoft Office! ●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

COMING UP… This chapter discusses common elements of all Office programs. We will first discuss their common makeup and will then turn towards common functions.

2. COMMON ELEMENTS OF MICROSOFT OFFICE PROGRAMS All Microsoft Office programs, and indeed most Windows programs, share some common elements. We will start out by discussing features that are more or less similar across all Office Programs, namely the menu bar, toolbars, and status bar. We will then dive into functions that are shared by all Office products, such as the methods to create, open, save and print files. Common features All Office programs have a comparable assortment of control features: a menu bar, several toolbars and a status bar. We will now discuss these elements one by one. The menu bar

The menu bar offers you access to all services of the application you are using. You can call up any item on the menu bar either by clicking on it directly or by pressing and holding the a key while pressing the underlined letter of the entry. (E.g. to open the file menu, press a + F.) Clicking on any of the headings will bring up the appropriate menu, which contains a broad range of functions. Be aware that no Office application displays all of the available functions right away. Many computer users experienced the vast range of available options as confusing. This is why Microsoft Office applications initially display only the most frequently used items. Only if you do not do anything for a moment after opening a menu will they display the remaining options. Click the ‘expand menu’ button To speed up the process, you can also press the ‘expand menu’ button at to see the entire range of options. the bottom of the menu.

8 Introduction to Microsoft Office ●●●●●●●●●●●●

If the option you are looking for is not in the list, you can wait a few moments for the menu to expand to its full length…

… or you can click on the ‘expand menu’ button to expand it to full size immediately.

All options available in Office applications are grouped into one of the menus. While the individual options differ between the applications, the general menus are rather similar. · The ‘file’ menu offers options to create and open documents, save, preview and print your work and other options related to the actual document. ·

The ‘edit’ menu allows you easy access to the most fundamental editing options, such as cutting, copying and pasting, undoing and redoing changes, as well as finding and replacing text.

·

The ‘view’ menu allows you to determine how you would like your document to be displayed. Furthermore, it gives you access to parts of your document and of the application that are not always visible on the screen, such as specialty toolbars.

·

The ‘insert’ menu lives up to its name: it enables you to insert all sorts of objects into your document, whether it is a mere symbol, an equation, a footnote, a comment or a picture.

·

Under the ‘format’ heading, you find all of the formatting options. What you can format depends on the application: e.g. fonts and paragraphs in Word, cells, rows and columns in Excel, and slides in PowerPoint.

·

The ‘tools’ menu contains an assortment of helpful functions, such as the spell checker.

·

The ‘window’ menu comes in handy when you are working on several documents simultaneously, as it allows you to arrange the documents in any way that you like or that is useful to you.

General Topics: Getting Hooked Up 9 ●●●●●●●●●●●●

·

Finally, the ‘help’ menu is your first stop whenever you are stuck.

The toolbars While the menu bar is highly useful, it would be frustrating if you had to use it all the time. Imagine you want to cut a line of text and place it somewhere else – it would take quite long if you had to select ‘edit’, ‘cut’ and then ‘edit’, ‘paste’ for every simple adjustment. For that reason, there are a number of toolbars that contain shortcuts to the most frequently used functions. Generally, Word, Excel and PowerPoint will display only the two most important toolbars at startup: the standard toolbar and the formatting toolbar.

The standard toolbar gives access to the most common functions: creating, opening, saving, printing and emailing documents, cutting, copying and pasting text, undoing and redoing changes, etc.

The formatting toolbar, as its name suggests, gives access to the most common formatting options. Using it, you can e.g. change the typeface, size, alignment, and indentation of your text. The function of the most important buttons will be discussed in the appropriate sections of the next chapters. If you are looking for a particular shortcut or are not certain what a specific button does, there is a quick way to get help: let your mouse pointer hover above the button for a few moments, and a yellow sign will appear with a brief explanation.

Use this button to get access to infrequently used shortcut buttons.

‘View’ „ ‘Toolbars’

Toolbars are similar to menus in one aspect: unless there is an abundance of space, they only show the most commonly used functions. If you do not use a particular shortcut button for a while, it will drop off the toolbar. To get it back, click on the ‘more buttons’ button at the right end of the appropriate toolbar. All applications also offer toolbars for specific problems. Beside the standard and formatting toolbars, Word offers 14 additional toolbars, Excel offers a further 12 and PowerPoint another 11 additional bars. You can make additional toolbars visible by clicking on ‘View’, ‘Toolbars’ on the menu bar, and then selecting the appropriate toolbar.

10 Introduction to Microsoft Office ●●●●●●●●●●●●

The status bar

The status bar gives ready access to a wealth of information about your document, but the type of information is very much dependent on the application. Most of the information displayed there is aimed at the advanced user, and we will only discuss it in a small number of cases. Common functions We will now turn towards the common functions of Microsoft Office. There are usually several ways to activate a function, such as a button on a toolbar, a keyboard shortcut or an entry in the menu bar. All possible ways to launch a feature are detailed in the margin. Please note that buttons on the toolbar work slightly differently than the other two options: they immediately execute the feature, without giving you an opportunity to change any settings. If you press e.g. the ‘print’ button on the toolbar, your document will be printed immediately using the settings from the last print job. This could be dangerous if you are working in the SMR and your last print process used the expensive color printer! If you want to check or change any of the settings before activating a feature, you should use the keyboard shortcut or the entry in the menu bar. Creating new documents c + N

‘File’ „ ‘New…’ Create a new document.

Pressing the ‘new document’ button on the standard toolbar will get you a completely blank document in Word, Excel and PowerPoint. Using the keyboard shortcut or ‘File’ menu will allow you to choose between a blank document and some prefabricated templates, such as templates for letters in Word or PowerPoint presentations with elaborate designs. Opening existing documents

c + o

‘File’ „ ‘Open…’ Open a document.

If you want to access an existing document, you can press the ‘open’ button on the standard toolbar, select ‘File’, ‘Open’ in the menu bar or press c + O. All of them work in the same way; they lead to a window in which you can select the file to be opened:

General Topics: Getting Hooked Up 11 ●●●●●●●●●●●●

File type Files can be classified into different types, indicating that they contain different information. E.g. Word documents can contain elaborate texts, while PowerPoint files contain slides for a presentation. Files cannot simply be opened with any program. If you do not have the software associated with the file type, e.g. PowerPoint for PowerPoint documents, you may not be able to use the file, or you may be restricted in its use.

To open a file, you first need to go to the location of the file. Two of the most frequently used locations, the ‘my documents’ folder and your ‘desktop’, can be found using the shortcuts on the left side of the window:. You can also use the ‘history’ button to locate files that you have recently worked on. If all this does not help, e.g. when your file is on a floppy disk, use the ‘look in’ box at the top of the window to navigate to the location of your file. Once you have moved to the appropriate location, you should see a list of documents. If your file is among them, you can open it by doubleclicking on its name or by selecting it and pressing the ‘open’ button. If a large number of files is listed, you may need to scroll through the list to find the file you are looking for. If you are in the right location, but your file is not on the list, check if the right file type is selected. However, the correct type will usually be selected by default, e.g. Microsoft Excel will always try to open Excel documents initially. Saving documents

c + S

‘File’ „ ‘Save…’ Save the current document.

If you have worked on a document and wish to save your progress so that you can continue editing or using it later, use the ‘save’ button, select ‘File’, ‘Save’ from the menu or press c + S. If you created a new document, you will be asked to enter a name for the document. If you already saved the document earlier on, you will not have to enter its name anymore – the old version will simply be overwritten. – Different file types Under very specific circumstances, you may want to save a document in a different file type than its standard file type. E.g. if you intend to email an essay to a person who does not own Microsoft Word, sending it as a Word document may not be a good idea. In this case, you can select

12 Introduction to Microsoft Office ●●●●●●●●●●●●

different file types at the time when you enter the file name. Usually, you will select the file type by consensus: the recipient will tell you what file types he can use and you will select one of them. In the vast majority of cases, it will not be necessary to change the file type and we will only return to this subject in the section on PowerPoint. – Saving under a new name In some cases, you may want to save a file without overwriting the old version, e.g. because you have made significant changes and you want to ‘File’ „ ‘Save as…’ Save the current document under preserve the original together with the revised version. In that case, you can select ‘File’, ‘Save as’ from the menu bar and enter a new filename. a new name. Printing documents c + P

‘File’ „ ‘Print…’ Print the current document.

Pressing the ‘print’ button will immediately print the entire document on the standard printer, or on the printer that was used for the last printout. Pressing c + P or choosing ‘File’, ‘Print’ from the menu bar will allow to make a number of choices before printing, such as: · which parts of the document you want to print · which printer you would like to use · how many copies you would like to print – Print preview

‘File’ „ ‘Print preview…’ Print preview.

Preview a single page.

View several pages.

Before printing a document, you can check whether everything looks the way you intended it to look. Pressing the ‘print preview’ button or selecting the appropriate item from the menu bar will get you a miniature preview of the printed document. You can choose to preview each page individually, resulting in a more detailed preview, or you can opt to preview several pages simultaneously. You will be able to choose how many pages to preview (up to a maximum of 24 pages) and how they should be arranged. If you are satisfied, you can print directly from the print preview by clicking on the ‘print’ button (see above), or you can return to the document for further changes by clicking on ‘close’. – SMR printer guide If you are working in the SMR, selecting the correct printer is important. At the time of writing (July 2002), Hewlett-Packard printers were used for simple black and white paper printouts. To make a color printout or to print sheets for overhead projectors, you can use the Tektronix Phaser printer. Be sure not to confuse the two: printing on the Tektronix printer is far more expensive than making a normal printout. Once you have selected a printer for your printouts, it will be used until you select a different printer. For more information, ask the SMR duty manager or visit the SMR homepage. „ www.fdewb.unimaas.nl/smr/

General Topics: Getting Hooked Up 13 ●●●●●●●●●●●●

Closing documents

c + 4

‘File’ „ ‘Close’ Close the current document.

If you want to continue working e.g. in Word, but are done with one particular document, you can close it. Keeping documents open when you are not working on them will tie down computer memory and may slow down your computer’s other operations. There are lots of ways to close a document. You can press c + 4 or select ‘File’, ‘Close’ from the menu bar. Alternatively, you could click on the ever-present ‘close’ button that can be found in the top right corner of every window. Be aware that there may be two such buttons if you have only one document open: the upper ‘close’ button closes the application, e.g. Word, while the lower ‘close’ button closes only the current document. If you have more than one document open, there will only be one close button, which will always close the current document. Cutting, copying, pasting The most comfortable way to move text and other items around in a document or to transfer them between documents is to cut or copy the desired item and to paste it wherever it is needed. Before you can do so, you need to select what you want to cut or copy. There are small, but significant differences between the procedures used to select text in Word, cells in Excel and slide elements in PowerPoint. We will therefore discuss these techniques individually in the appropriate chapters. – Cutting or copying?

s + x or c + x ‘Edit’ „ ‘Cut’ Cut

c + i or c + C ‘Edit’ „ ‘Copy’ Copy

s + i or c + V ‘Edit’ „ ‘Paste’ Paste

Once you have highlighted the text, cells or objects that you want to cut or copy, you will have to choose between cutting and copying them. Both methods will allow you to insert the object(s) in some other spot, but there is a crucial difference between them: cut text disappears from its original location while copied text remains where it is. E.g. if you want to move a passage from the middle of your essay to the end, you would usually cut it. If, on the other hand, you want to re-use a passage in some other essay, you would copy it. Before you can insert the text, you need to move the cursor to its new location, e.g. to the end of your essay. You can then push the ‘paste’ button, press s + i or select ‘Edit’, ‘Paste’ from the menu bar. You can paste the same element as many times as you like. Undoing changes

c + Z

‘Edit’ „ ‘Undo’ Undo

When you have done something really disastrous, e.g. deleted all the text in your document, you can undo the changes one at a time by clicking on the ‘undo’ button. If you click on the right part of the ‘undo’ button , you will see a list of your last changes and you can undo as many of them as you like.

14 Introduction to Microsoft Office ●●●●●●●●●●●●

c + Y

‘Edit’ „ ‘Redo’ Redo

c + Y

‘Edit’ „ ‘Repeat’ Repeat

If it turns out that the changes were indeed justified, you can re-do them by clicking the ‘redo’ button. If you use the ‘redo’ button immediately after pressing the ‘undo’ button, then your changes will be restored. If your last action however was not to ‘undo’ something, then the ‘redo’ button will be deactivated. Instead, you can use the keyboard shortcut or the appropriate ‘Edit’ menu item to repeat your last action. This will not work in all circumstances. The help system

1

e.g. ‘Help’ „ ‘Microsoft Word Help’ Launch the help system.

If you get stuck working in a Microsoft Office application, simply hit 1 or push the ‘help’ button. What happens next depends on the settings of your computer. If the Office Assistant is installed, it will spring to action and offer help. If the Office Assistant is not installed, pressing 1 will bring up the help system, which is a combination of a user manual and a list of anticipated questions. – The regular help system The help system will appear in the form of another window that will push your current window to the side so that both windows are visible at the same time. Its window is split into two main areas: a navigation area, which helps you find the topics you want, and a text area, where the actual help will be displayed.

There are three different ways to get information: The contents and index work just like you would expect them to: the ‘contents’ area offers a list of all the chapters and sections contained in the user manual, while the index is an alphabetical list of all the words used in the manual. The answer wizard is probably the most useful tool:

General Topics: Getting Hooked Up 15 ●●●●●●●●●●●●

it lets you search a list of topics for your particular problem. Let’s assume you are working in Word and want to italicize text, but you do not know how. Simply call up the help system, click on ‘answer wizard’ and type in a couple of keywords:

When you push the ‘search’ button, the help system will return with a list of topics that may be of relevance for you:

In our case, the answer to our problem is right at the top of the list. If you have trickier problems, you may have to go through a number of topics. To read any of the listed topics, click on its title. The topic will then be displayed in the adjacent area:

– The office assistant

Clippy, the best-known (and most hated) office assistant.

The office assistant is a cartoon character that is meant to help you do your daily chores using Microsoft Office programs. It will take the form of a paperclip unless you actively ‘hire’ another character as your assistant. When you press 1 and the office assistant is installed on your computer, you can ask questions in the same way as when you are dealing with the answer wizard (see above).

‘Help’ „ ‘Hide the Office Assistant’

Furthermore, as long as the office assistant is visible on the screen, it will monitor what you are doing and will offer suggestions when it thinks that you could accomplish a task more efficiently. This behavior may be quite useful while you are still learning the ropes, but after a while, many users get irritated with the office assistant. If you want to get rid of it, right-click on it and select ‘hide’ from the menu that pops up. Alternatively, you can select ‘Help’, ‘Hide the Office Assistant’ from the menu bar.

16 Introduction to Microsoft Office ●●●●●●●●●●●●

Dialogue box A window that requires your decision on some topic. E.g. when you save a document, your PC needs to know under what name it should save the document and will open a window where you can enter the filename. This window would be a dialogue box.

– Help in working with dialogue boxes During some particular activities, there is a quicker way to get help. Let’s assume that you want to print something. You have clicked on ‘File’, ‘Print’ and a window has popped up where you can adjust some of the print settings before printing. However, you are unfamiliar with some of the advanced settings. In almost all of these dialogue boxes, you will find a button with a question mark in the upper right corner. Pushing this button will put you into ‘quick help’ mode. To indicate you are in this mode, a question mark will be added to your mouse pointer . When you now click on any element of the dialogue box, a brief description of its function will be displayed. For example, clicking on the ‘ok’ button in quick help mode… will yield the following explanation:

●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

MICROSOFT WORD

Image: size-isnt-everything.co.uk

●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

1. INTRODUCTION Microsoft Word is a word processor, a program designed to handle a wide variety of pieces of writing. You can use it to write anything from a short letter to reports of several hundred pages. It offers a broad range of formatting options and also allows you to integrate images, graphs, tables, equations and all sorts of other objects into your documents.

‘Start’ „ ‘Programs’ „ ‘Microsoft Word’ Create new Office docs.

Open existing Office docs.

You can start Microsoft Word by clicking on its icon on the desktop, or alternatively by selecting it from the ‘Programs’ category of the Start menu. Word will start as well if you double-click on any Word document. Finally, you can also use the ‘new office document’ and ‘open office document’ tools, which may be located in your quick launch bar or at the top of your Start menu. A few moments after launching Word, its window will appear on your screen: The Word screen

As all Microsoft Office programs (and most other applications), Word features the usual assortment of a menu bar, several toolbars and a status bar. Beyond that, the screen is dominated by white space. This is where

20 Introduction to Microsoft Office ●●●●●●●●●●●●

you will be entering and editing your documents. We will discuss most of the elements found on the toolbars and some of the options on the menu bar in the latter chapters, so we will only examine the status bar here. Word’s status bar plays a more prominent role than the status bars of other Office applications. It provides a wealth of information about your document.

Let’s investigate the individual elements on the bar. · · · · · · · · ·

Page 1: we are on the page labeled ‘1’.2 Sec 1: we are in section 1 of our document. 1/1: this is the first page of our document, and there is one page in total. At 2.5 cm: on the current page, we are 2.5 cm below the top margin. Ln 1: this is line 1 on the current page. Column 1: in the current line, we are in the first column. 3 4 REC, TRK, EXT, OVR: a number of options are not active. English (U.S.): we are using American English for our document. The two empty boxes represent activities. The first one will inform you about the status of the automatic spell check, the second one will report on saving and printing activities that occur in the background.

Writing a document in Word In producing a document in Word, you will usually follow these steps: 1. Create a new document. (Analogous to taking a fresh sheet of paper.) 2. Typing a first version of the document. 3. Revising and editing it. 4. Applying formatting to your text. 5. Preserving the document for later use by saving or printing it. The distinction between these stages is in a way artificial. Once you have acquainted yourself with Word, you will certainly apply formatting while typing, and you will probably save your document early on. Nonetheless, we will investigate these processes separately from each other for the sake of order. The following chapter is dedicated to the second and third step of the 2

This is not necessarily the first page of the document, e.g. in books the first pages are usually labeled ‘i’, ‘ii’, ‘iii’ and so on, as they contain the cover page, contents, etc. The first page of actual text is then labeled ‘1’. 3 These are not ‘columns’ as such. Instead, it represents the number of the next character. Here, we are at the beginning of the line, so the next character will be the first. 4 Note that the options are gray; if they are active, they will be black as the rest of the information on the status bar. We will discuss some of these options in later chapters.

Microsoft Word: Introduction 21 ●●●●●●●●●●●●

process: typing your document and editing it. As it turns out, there is quite a bit to say about these deceivingly simple activities. The third chapter is then concerned with the formatting of your document. Finally, saving and printing in Word works just like in any other Office application. Therefore, these activities will not be discussed separately – we refer you to the second chapter of the general section, ‘Common Elements of Microsoft Office Programs’. ●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

COMING UP… The present chapter discusses the peculiarities of typing a document in Word, how you can edit it and how the spell checking works.

2. ENTERING & EDITING TEXT In this chapter, we will discuss the basics of creating a document in Word. While we assume that almost everyone has already seen a keyboard, there are a few peculiarities to working with a Word processor such as Word, and these are discussed in the section on typing text. We then discuss how you can edit your document. Finally, we discover what Word does while you are typing. Typing text There are few peculiarities about typing and even people that have never used a keyboard before will quickly get used to the process. In the following, we discuss a series of loosely related issues. Want to learn touch-typing? If you have not typed a long text yet, it may be a good idea to learn professional touch-typing instead of developing your own ‘hunt and peck’ method of typing. Among the advantages are increased speed, fewer mistakes and reduced strain on your hands and wrists. Also, learning it early on is easier than unlearning your own style later. Fortunately, learning touch-typing is not overly difficult and it can be done at zero cost. TYPING TUTORS ON THE WEB We have dug out four automated typing tutors that you can download for free from the web. All of them have received good grades from their users. Feel free to download them all and see which one works best for you. Analytical Eye Typing Tutor This software can be downloaded and used for free.

„ www.aspex.force9.co.uk/ae/ttutor

KP Typing Tutor This software can be downloaded and used for free.

„ www.fonlow.com/zijianhuang/kp/

TypingMaster Touch Typing Tutor 2002 This is a professional application, which you would have to buy. The producer does however offer a very good (and free) demo on their website.

„ www.typingmaster.com/tutor.htm

Clasys Elite Typing Tutor 2001 This software is shareware: you can use and evaluate it for free for 30 days. If you want to continue using it after 30 days, you will need to pay a small registration fee.

„ www.clasys.com/elitetyping.html

24 Introduction to Microsoft Office ●●●●●●●●●●●●

Cursor An indicator on your computer screen that shows where the next character will appear.

The cursor Before you start typing, we would like to introduce the cursor to you. The cursor is the blinking, vertical line on your computer screen. It tells you where your next character will appear. It also plays a role in editing your document, as you will need to move it around to correct mistakes, delete obsolete passages etc. We will discuss these functions in the section on editing. Special function keys Now that you know where your text will appear, we can start typing. While almost everyone is familiar with the use of a keyboard by now, there are a few keys that have a special significance in word processing programs. Below, we list the five most important ones and tell you what they are used for and what they should not be used for. · If you are still familiar with typewriters, you may expect to use the / key very often. But since Word takes care of the line breaks and the distance between paragraphs, it is no longer necessary to press / except once at the end of every paragraph. ·

It is no longer necessary to use the n bar for anything other than separating words. If you want to indent the first line of a paragraph, use the T key instead.

·

The T key (pronounced ‘tab’ key) can be used to conveniently indent the first line of a paragraph or indeed the entire paragraph.

·

The B key, called ‘backspace’, is used to delete characters that you have just written. Each time you press B, the first character to the left of the cursor will be deleted.

·

The x key has a similar function: it also deletes characters, but only those to the right of the cursor.

Entering umlauts & accents While all PCs at the University of Maastricht use English keyboards, it is still possible to write letters with umlaut marks and accents. Certain keycombinations are reserved for this purpose:

Microsoft Word: Entering & Editing Text 25 ●●●●●●●●●●●●

Sign acute accent cedilla

Example á, é ç

Key combination ’ + the appropriate letter ’ + C

circumflex accent

ê, î

^ + the appropriate letter

grave accent

ò, ù

` + the appropriate letter

dieresis

ä, ï

“ + the appropriate letter

ñ

~ + the appropriate letter

tilde

This also has consequences for typing ordinary signs. When you press one of the combination keys ’, `, ^, “, or ~, the sign will not appear until you enter the next character. If the next character does not form an umlaut, then both signs will appear simultaneously. E.g. typing “ and N will result in ‘"n’. If you want to type a combination sign without any following letter, you need to press the combination key, followed by n . No space will be printed on the screen – the space is simply used to release the combination sign from limbo. The same holds if you want to type a combination sign and a letter that would usually combine. E.g. to obtain ‘"e’, press “ followed by a n , then press E. ‘Insert’ „ ‘Symbol…’

If you need to use any characters or symbols that cannot be created using the above-mentioned method, select ‘Insert’, ‘Symbol’ from the menu bar for a broader range of characters. Insert and overtype mode You can type in two different modes: insert and overtype. If you are writing in overtype mode, every new character that you type will replace the character directly to the right of the cursor. You should only use this mode when you deliberately want to replace a few letters. Instead, it is best to use the insert mode most of the time. In insert mode, your new characters will be inserted, pushing any characters to the right of the cursor further in that direction.

Overtype mode

Insert mode

The status bar tells you in which mode you currently are. If the overtype sign is black, then you are in overtype mode; if it is gray, you are in insert mode. You can switch between the two modes by pressing the i key. Creating ordered lists Entering a list of items is easy in Word. If you enter a line that starts with a number as ‘1)’ or ‘1.’, Word will assume that you are writing a list. Once you press /, Word will indent the line (and all following lines) and automatically add the appropriate number to the next line.

26 Introduction to Microsoft Office ●●●●●●●●●●●●

Press

/ You can then enter the rest of the list without having to worry about the numbering. The advantage is that you can add and delete items and Word will automatically adjust the numbering of all following items. Once you are done with the list, press / twice to continue writing normally.

Ordered list. ‘Format’ „ ‘Bullets and Numbering…’

Alternatively, you can begin and end an ordered list by clicking on the ‘ordered list’ button. To fine-tune your list, select ‘Format’, ‘Bullets and Numbering’: you can then adjust the style of numbering and you will also be able to specify whether the numbering should start at ‘1’ or at any other position. Creating unordered lists

Unordered list.

Unordered lists work just like ordered lists, but instead of numbering, they use bulleting. If you start a line with a dash ‘–’, Word will automatically set up an unordered list for you. You can also start a bulleted list by clicking on the ‘unordered list’ button. In all other respects, unordered lists work just like ordered lists. Adding footnotes and endnotes

‘Insert’ „ ‘Footnote…’

To insert a footnote or endnote at any position in your document, click on ‘Insert’, ‘Footnote’ on the menu bar. Before you can enter the actual note, you will need to specify whether it is a footnote or endnote, and whether you want to use automated numbering or not. Automated numbering is a useful feature: if you use it, Word will keep track of all footnotes and endnotes in your document and will adjust their numbering accordingly. Once you confirm your choice, you can start typing your note. When you are done, click on ‘close’. Inserting objects into your document Objects are items that can be included in text documents, such as pictures and charts. Microsoft Word can handle a broad variety of objects and there are three different ways to insert them into an existing document: · pasting them from other applications · inserting them from a file or library · creating them inside Word

s + i or c + V ‘Edit’ „ ‘Paste’ Paste

– Pasting objects The easiest way to insert documents in Microsoft Word is to paste them: copy the object in the other application and paste it into Word by

Microsoft Word: Entering & Editing Text 27 ●●●●●●●●●●●●

clicking on the ‘paste’ button, pressing s + i or selecting ‘Edit’, ‘Paste’ from the menu bar. But while this method may be fast, it may not be the best option.

‘Edit’ „ ‘Paste special…’

In fact, Word is able to insert the same object in different ways. E.g. an Excel graph can be insert as a ‘Microsoft Excel Chart Object’ or as a picture. The former will allow you to edit the graph even when you no longer have the Excel file, but this advantage comes at the price of an increased file size. The latter option will simply include a static picture of the graph, which has the same quality, but can no longer be edited as an Excel chart. Similar choices exist for most objects that you can paste. When you select ‘Edit’, ‘Paste special’ from the menu bar, you will be presented with a window where all possible options are listed.

Word also provides a brief explanation for each option that is meant to guide you in the selection process. – Using files or libraries Inserting objects from a file is a very reliable method, presuming that Word knows how to deal with the information. You can e.g. insert a great variety of images as well as documents from other Microsoft Office applications. But Microsoft Word does not know how to deal with every file. If you do not get the desired results, try opening the object in the program in which it was originally created, then pasting it into your Word document. ‘Insert’ „ ‘Picture’ „ ‘From file…’ ‘Insert’ „ ‘Object…’

‘Insert’ „ ‘Picture’ „ ‘Clip Art…’

To insert an image from a file, select ‘Insert’, ‘Picture’, ‘From file’. To insert any other sort of object, selecting ‘Insert’, ‘Object’ and then switch to the ‘Create from file’ category. You can also insert pictures from Microsoft’s ClipArt library, which comes with Microsoft Office. To do so, select ‘Insert’, ‘Picture’, ‘Clip Art’. You will then be able to select illustrations from a broad range of categories. You can also insert sounds and movies from the ClipArt gallery, but these are obviously useless if you plan to print the document.

28 Introduction to Microsoft Office ●●●●●●●●●●●●

– Creating new objects ‘Insert’ „ ‘Object…’

Finally, you can also create a broad array of new objects inside Word. To do so, select ‘Insert’, ‘Object’ from the menu bar, and then choose the type of object that you would like to create.5 Because of the technical implications of this process, creating new objects in Word is not an entirely stable process. It may be wiser to create graphs and other objects the traditional way, by using e.g. a drawing program to create the graph and then pasting the result into your Word document. Editing your text In the following, we discuss how you can move around in your document to add additional text, and how you can select parts of your work so that you can move, format or delete it. Moving around in a document You can move your cursor to a new location in the text simply by clicking on that location with your mouse pointer. But once you start serious revisions, you may find that it can be quite tiring if you have to grab the mouse every time you want to jump to another spot. Instead, you could use the arrow keys u, d, l and r, but this is rather slow. Fortunately, there are some keyboard shortcuts that allow you to move around quickly in a document: · c + l and c + r allow you to move wordwise in each direction. · c + u and c + d take you to the beginning and end of the current paragraph. · h and e take you to the beginning and end of the current line respectively. · c + h or c + e enable you to jump directly to the beginning and end of the document. · M and W scroll your document by one screen-length in each direction. · c + M or c + M take you to the previous or next page in the document. Once you have arrived at the desired location, you can add additional text or alter the original text. Selecting text If, instead, you want to move, format or delete parts of your work, you need to select the section of your document to which the changes should be applied. To select text using the mouse, follow these three steps:

5

Which objects you can generate depends on the software configuration of you computer.

Microsoft Word: Entering & Editing Text 29 ●●●●●●●●●●●●

1) Move your mouse pointer to the beginning of the text that you want to highlight. 2) Press the left mouse button and keep it pressed while… 3) you move the mouse pointer to the end of the text.

Instead of using the mouse, you can also use the keyboard: 1) Move the cursor to the beginning of the text. 2) Press and hold the s key while… 3) moving the cursor around with the arrow keys u, d, l and r. This allows you to highlight one character at a time. You can speed up the process by holding s and c while using the arrow keys. This will highlight one word at a time. Some additional timesavers: · Pressing s + h or s + e highlights everything from your current position to the beginning or end of the current line. · Pressing s + M or s + W selects larger portions of text. · Pressing c + s + h or c + s + e highlights everything from your current position to the top or bottom of the document. · Pressing c + s + u or c + s + d selects everything from your current position to the top or bottom of the paragraph. Once you selected all desired elements, you can continue to cut and paste them normally. (See ch. 2 of the general section, ‘Common Elements of Microsoft Office programs’, for more details.) Furthermore, you can delete entire blocks of text by selecting them and pressing x. Finally, you will also need to select text if you plan to format it (see next chapter). While you are typing… … Word is busy, too: it checks what you are typing. Word will automatically correct some of the most common typing demons. Words with two capital letters, e.g. ‘EXample’, will quietly be fixed, ‘acheive’ becomes ‘achieve’ again, and so forth. Furthermore, it will alert you to more complex issues by underlining them in red or green.

30 Introduction to Microsoft Office ●●●●●●●●●●●●

Spelling mistakes… Red underlining indicates a possible spelling mistake. We say ‘possible’ simply because Word’s dictionary is not all-encompassing. Certain scientific terms still elude it, as do certain place names, names of persons and companies, etc. Still, it is worth checking out each term with red underlining. When you right-click on the underlined word, Word will offer some suggestions regarding the correct spelling. You can then select one of the suggestions or you can tell Word to: · add the unknown word to its dictionary (so it does not show up as a mistake any more) · ignore the word (in this particular document) · automatically correct this mistake in the future · or to switch to a different language … and grammar trouble Similarly, green underlining indicates a possible grammatical mistake. Invariably, the grammar check is less reliable then the spell-checker: computers still have trouble to identify all the different meanings that we can express in a language. Therefore, they do not always know what is right and what is wrong. E.g. how about a bit of Yoda-talk6: “Jumbled this sentence is.” Word is not the slightest bit distressed by the above sentence. Not even when you write “jumbled is sentence this” instead. It does however a pretty good job in other areas, such as: · capitalization errors · confusion of homophones, e.g. ‘there’, ‘their’ and ‘they’re’ · hyphenation · multiple negation · passive sentences · punctuation · subject-verb agreement · long or wordy sentences As with spelling mistakes, you only need to right-click on the underlined word to see some suggestions. Word also tells you why it thinks that your sentence is wrong. Spelling and grammar status Word provides you with a way to check the spelling and grammar status of a document. The second icon on the right on the status bar tells you whether you still need to check your document: 6

Yoda is not only a mighty Jedi-master and skillful manipulator of the English language, he is also a registered trademark of Lucasfilm Ltd.

Microsoft Word: Entering & Editing Text 31 ●●●●●●●●●●●●

· · ·

If it displays a moving pen, then Word is still busy evaluating your document. You will have to wait a few moments for the results. If you find a check mark, then there is reason to rejoice: Word was not able to find spelling or grammar mistakes. Good job! More frequently, you will see this icon: Word has found at least one mistake somewhere. While it is entirely possible that Word is misguided in its analysis, it still pays off to manually check your document before you print it or hand it in.

Doing a complete spelling and grammar check 7

‘Tools’ „ ‘Spelling and Grammar…’ Initiate a complete spelling and grammar check.

Once you are done with a document, you may want to do a complete checkup. You can launch such a check by pressing the ‘spelling and grammar’ button on the standard toolbar, by selecting ‘Tools’, ‘Spelling and grammar’ from the menu bar or by pressing 7. Word will then take you past all the mistakes it found, giving you the opportunity to correct or ignore every single one. Writing documents in a different language If you are using an English version of Word, then it assumes by default that you are writing an English document.7 Similarly, a Dutch version Word will assume you are using Dutch, and so forth. Writing documents in another language will understandably confuse Word. There are two possibilities: if you are starting a new document in a language that is known to Word, it will usually recognize this after a few words and adjust the language setting for the document accordingly.8 In that case, Word will proceed to check the spelling in the appropriate language.

‘Tools’ „ ‘Language’ „ ‘Set Language…’

In three cases however, Word will not be able to make the adjustment: 1) You may be using a different dialect of a particular language. E.g. you may be writing American English while Word expects British English. You can check which language Word expects by having a look at the status bar. If that is not the language that you want to use, you can change it. To do so, select everything that you have written so far, then click on ‘Tools’, ‘Language’, ‘Set Language’ from the menu bar and choose the appropriate language. Blue checkmarks appear next to the languages for which Word has a dictionary. 2) If you are just using a few terms from another language in an otherwise English document, Word will not recognize them as foreign-language terms. In that case, the best option is to adjust the language setting for these words. Simply highlight those particular terms, select ‘Tools’, ‘Language’, ‘Set Language’ from

7

Whether that is British or American English depends on where the version was released. PCs in the SMR are usually set to British English by default. 8 It is possible that Word is slightly off, e.g. mistaking Canadian French for French French.

32 Introduction to Microsoft Office ●●●●●●●●●●●●

the menu bar and choose the appropriate language. 3) Finally, it may be the case that Word simply does not have the appropriate dictionary installed. The English version of Word comes with dictionaries for all dialects of English, French and Spanish. If you are using Dutch or German terms, Word will be lost. In that case, you can either ignore the spellchecker or turn it off. To turn it off, highlight the offending words, select ‘Tools’, ‘Language’, ‘Set Language’ from the menu bar and check the option ‘do not check spelling or grammar’. ●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

COMING UP… This chapter discusses ways to change the appearance of your document.

3. FORMATTING YOUR DOCUMENT Good formatting can support your message and give your document a personal touch. In this chapter, we will discuss several ways to format documents. We will begin with formatting options that affect individual words, and then move on to talk about formatting at the paragraph and document level. Adjusting the appearance of text

c + s + F

Font This changes the typeface of your text. There is usually a broad assortment of typefaces available, but the general choice is between three different types of fonts: serifs, sans serifs and decorative fonts. Serif typefaces are fonts that feature small decorative lines at their stem and shaft. These lines help guide the eye along the line of text, making serif fonts better suited for long pieces of writing. The following fonts, among others, belong to this category:

Sans serif fonts lack the decoration and are therefore very clear. They are a good choice for text that should draw the attention of the reader, such as titles and labels. The following fonts are sans serif typefaces:

Fonts in the decorative category do not contain letters and are therefore not suited for the setting of text. Instead, they offer a variety of symbols for illustrative purposes. WingDings, WingDings 2, WingDings 3 and WebDings fall into this category. The default font is ‘Times New Roman’. As a serif font, it is very easy to read in print. Font size c + s + P

9

This changes the size of your text. The default size for text is 12 pt.9 and is widely used because it guarantees readability.

This size is also usually used for writing assignments, essays, etc. Note that some block coordinators may require other sizes – please check the requirements for each assignment individually.

34 Introduction to Microsoft Office ●●●●●●●●●●●●

Font style Bold Italics Underline

c + B c + I c + U

These options influence the appearance of your text. You can set your text in boldface and italics, and you can underline your text. By convention, underlining is no longer used to highlight words; instead, text is set in boldface or italics. Underlining is now used to indicate Internet links instead. Font color

Clicking on the left part of the button will change the color of your text to the color indicated on the button. If you wish to use another color, click on the right part of the button.

To change the color of the text, click on the ‘font color’ button. This will change the color of your text to whatever color is indicated on the button. (It is indicated in the bar below the ‘A’; in our case, it is black.) If you however do not want your text to appear in that particular color, you can click on the right part of the button to expand the color menu.

Highlighting text Highlights your text.

Clicking on the highlight button will allow you to mark important sentences or words by highlighting them. Clicking on the left side of the button will add the corresponding background color to your text. If you do not like the default color, click on the right side of the button to choose between bright and dark shades of red, yellow, green, blue and gray, as well as turquoise, pink, violet, teal and black. Further options…

‘Format’ „ ‘Font…’

The options described above are clearly the most important options. But also Word offers a number of other choices for your text that can be accessed by clicking on ‘Format’, ‘Font’ on the menu bar. Under the ‘font’ category, you find most of the previously discussed options, as well as a range of underline styles and typesetting effects. In the ‘character spacing’ section, you can magnify the font, increase the space between characters and raise or lower the text. Finally, the ‘text effects’ section allows you to animate your text, which is obviously only useful when you distribute the document electronically. Formatting entire paragraphs Paragraph formatting is formatting that cannot be applied to single words but only to entire paragraphs. The two most important paragraph formats, its alignment and indentation, are available as shortcuts, the

General Topics: Getting Hooked Up 35 ●●●●●●●●●●●●

remainder of the options is available through menu settings.

Left Center Right Justify

c c c c

+ + + +

L E R J

Paragraph alignment Here, you can choose the alignment for the current paragraph. You can align it to the left or right margin, you can center each row so that the distance to both margins is equally big, or you can justify your text, aligning it with both margins simultaneously. Indentation

Increase indent

Decrease indent

The ‘decrease indent’ and ‘increase indent’ buttons allow you to adjust the indentation of text. Paragraph spacing

‘Format’ „ ‘Paragraph…’

This feature is located in the paragraph options – you can access it by clicking on ‘Format’, ‘Paragraph’ on the menu bar. It determines how large the gap between paragraphs should be. E.g. if you want to have a full line’s distance between all paragraphs, you could send the spacing after the paragraph to 12 pt. It is preferable to use this setting instead of pressing / several times between paragraphs, as it is easier to adjust the paragraph spacing once for the entire document than to add additional line feeds throughout your text. Line distance

‘Format’ „ ‘Paragraph’

This feature is also located in the paragraph options – select ‘Format’, ‘Paragraph’ on the menu bar to access it. With this setting, you can influence the distance between lines. E.g. writing assignments usually require a 1.5 line distance for improved readability. You can use the preset levels ‘single’, ‘1.5 lines’ and ‘double’ or create a custom setting. Recycling your work Once you have created a set of formats that you like, Word allows you to easily recycle them using two methods: the format painter and formatting styles. The format painter The format painter allows you to transfer formatting from one paragraph to another. Its advantage is speed: if you need to transfer formatting only once, then this is the faster solution: 1. Position the cursor in the paragraph whose formatting you would like to copy.

36 Introduction to Microsoft Office ●●●●●●●●●●●●

Press this button to transfer formatting to another part of your document.

2. Press the ‘format painter’ button on the standard toolbar. A paintbrush will be added to your mouse pointer . 3. You can now select the text to which the formatting should be applied. Before: After: Formatting styles Styles allow you to save a particular set of formats and recall it whenever you it is needed. This is the better solution of you wish to apply a formatting again and again throughout your document. To save a style: 1. Position your cursor inside the paragraph whose formatting should be preserved, … 2. click on the style box on the formatting toolbar and … 3. enter a descriptive name for your style.

Once you have saved a style, you can easily apply it again: 1. Select the text to which the style should be applied, and … 2. select the appropriate style’s name from the style box.

Changing the page layout Finally, there are options that affect the entire document, or at least significant parts of it. There are two groups of settings in this category: general page settings, such as the paper size, as well as the header and footer settings. General page settings Among the general page settings are: · the paper size, e.g. ‘A4’ · the paper orientation, ‘portrait’ vs. ‘landscape’ · the width of the four margins ‘File’ „ ‘Page setup…’

All of these settings can be adjusted by clicking on ‘File’, ‘Page setup’. The first two options can be found in the ‘paper size’ category, while the latter option belongs to the ‘margins’ category.

General Topics: Getting Hooked Up 37 ●●●●●●●●●●●●

Header and footer ‘View’ „ ‘Header and Footer’

To edit your document’s header and footer, select ‘View’, ‘Header and Footer’ from the menu bar. Your view will shift to a section in the upper margin of the page and an additional toolbar will appear. Everything that you enter here will appear on each and every page.

Insert the page number.

The toolbar enables you to insert predefined variables, such as the page number or the date. You can also insert complete headers, such as ‘page x of y’ using the ‘Insert AutoText’ button.

Insert complete headers.

‘File’ „ ‘Page setup…’

Finally, it is also possible to obtain different headers for the first page, as well as for odd and even pages. You can activate differentiated headers by pressing the ‘page setup’ button and then selecting ‘different first page’ and/or ‘different odd and even’. Once you are done manipulating the header and footer, you can return to the normal view by pressing ‘close’. ●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

MICROSOFT EXCEL

Image: size-isnt-everything.co.uk

●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

1. INTRODUCTION Microsoft Excel is a spreadsheet program, a program that allows you to enter all sorts of information, relate the individual bits of information to another, and to use them for calculations and analyses. Most Excel users only find out about its full capabilities after they have been using it for a while, so we will get started right away… Starting Excel To start Excel, double-click on the Microsoft Excel icon on the desktop. Alternatively, you will find Microsoft Excel in the Start menu under ‘Programs’. Excel will start as well if you double-click on any Excel document. Finally, you also can use the ‘new office document’ and Open existing ‘open office document’ tools. Within a few seconds, you will see the Office docs. Excel window on your screen:

‘Start’ „ ‘Programs’ „ ‘Microsoft Excel’ Create new Office docs.

Excel’s window is dominated by cells.

Besides the ever-present menu bar and toolbars, you will notice that the screen is dominated by heaps of small boxes arranged in rows and Cells columns. These cells are the cornerstones of spreadsheet programs. Cells Containers of information, which are containers for information. There are few restrictions on the kind of can be filled with text, numbers information a cell can contain: or formulas.

42 Introduction to Microsoft Office ●●●●●●●●●●●●

· · · ·

numbers of all kinds, e.g. 2.638, ½, 75%, 1.653 ´ 1015 date or time information, e.g. 01/01/2002, or 12:41:03 currency values, e.g. € 1,500.00 text, e.g. “Maastricht University rules!”

So far, this is not particularly exciting: any table could do that. But the advantage of spreadsheet programs is that you can also fill cells with formulas, and Excel will automatically do the calculations for you. Before we turn towards these advanced functions of cells, we investigate another aspect: their order. All cells are arranged in rows and columns, as you can see on the screen. These rows and columns, in turn, are labeled – columns are labeled with letters, rows are labeled with numbers. Each cell can be identified by its column letter and row number:

Worksheet A page in your Excel document; contains 65,536 rows and 256 columns of cells.

In identifying cells, we first list the column, then the row. So the top left cell is number ‘A1’, not ‘1A’. In total, there are 65,536 rows and 256 columns10. Together, these cells make up one ‘worksheet’ (or simply ‘sheet’). Beyond cells, there are three other important features: · The name box, which is located directly above the worksheet area. It tells you where you currently are on the worksheet, e.g. in cell ‘A1’, and it allows you to give your cells names (see ch. 2):

10

·

The formula bar, which helps you enter information and formulas into cells. It is located to the right of the name box and looks quite unspectacular:

·

Finally, the sheet selector. Believe it or not, some people think that the 16,777,216 cells contained in one sheet are not sufficient for their needs. Luckily, you can have as many sheets as you like – by default, Excel will create three empty sheets when you open a new document. The sheet selector, which is located right beneath the actual worksheet, helps you to select the sheet on which you want to work.

By the way, the column after ‘Z’ is labeled ‘AA’, followed by ‘AB’ and so forth till you get to column ‘IV’

Microsoft Excel: Introduction 43 ●●●●●●●●●●●●

In the following three chapters, we will investigate most of the basic functions of Excel. The next chapter discusses how you can enter information in Excel and how you can use this information in calculations. The third chapter covers the different formatting options that are available in Excel. Finally, the fourth chapter discusses two advanced topics: a) it builds the foundation for various data analysis tasks in Quantitative Methods, and b) it discusses how you can display information graphically using Excel’s chart wizard. ●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

COMING UP… Here, we learn how you can enter information in Excel and how you can use this information in calculations.

2. ENTERING DATA & MAKING CALCULATIONS This chapter covers the basic function of Excel: to receive data and to manipulate it using calculations. We start out by discovering different ways to enter data, and then develop the concepts of formulas and functions. Entering information Excel stores information in cells. Each cell is meant to contain only one piece of information, e.g. a number (however large that number may be) or a sentence. To enter data in a worksheet, we first need to determine which cell should receive the information. Once we have selected a cell, we can start typing. Selecting cells You can select cells using your mouse or keyboard. If you are using the mouse, simply place your mouse pointer on top of the desired cell and click once. (Note that the mouse pointer takes the form of a cross when you are working with cells.) 1) 2) 3) The cell that is currently being used, or the ‘active’ cell, is surrounded by a thick black border. Alternatively, you can use the keyboard to move from your current position to another cell on the worksheet: simply use the arrow keys u, d, l and r to get to your destination. Entering information Once you are ‘in’ the cell of your choice, you can start entering information simply by typing.

Be aware that the information you are entering is not placed in the cell until you confirm that you are done. You can do so by · pressing /. This will place the information in the cell and it will also take you to the cell directly below your current position. · pressing T. This will place the information in the cell and it will also take you to the cell directly to the right of your current position. The only difference is where you end up next. If you are entering a

46 Introduction to Microsoft Office ●●●●●●●●●●●●

column of information, you should use / because it will move you to the next cell in your current column. Conversely, if you are entering a row of information, use T.

Because Excel does not place any information in the cell until you confirm it, you can always step back if you are making a mistake. Let’s assume that you are entering information in a cell that already contains information, and that you do not want to overwrite the old information. In this case, you can simply press ~ to annul what you were doing. Be aware that Excel is programmed to recognize certain types of information, such as dates, times, currency values, percentages and fractions. If you e.g. enter ‘€ 1625’, Excel will recognize that you are entering a currency value and will format it as such. Entering series Entering information can be quite tedious, especially when you are entering information that repeats or follows a pattern. Fortunately, Excel can fill in such series for you. Let’s assume that you want to create a table of quarterly profits for the years 1990 – 2000. Usually, we would have to enter the entire series: 1990, 1991, 1992, and so forth. Similarly, we would have to label cells ‘1st quarter’, ‘2nd quarter’, etc. Fortunately, there is a shortcut. Instead of typing the entire series, we only enter the first item, e.g. ‘1st quarter’. We then select this cell, so that it is surrounded by the thick black border again:

Note that there is a black dot in the lower right corner. When you place your mouse pointer above this dot, it will change from the familiar selection cross to a black plus sign . When it takes this shape, you can continue a series by pressing and holding the left mouse button while dragging the mouse in the desired direction:

Microsoft Excel: Entering Data & Making Calculations 47 ●●●●●●●●●●●●

While you are doing so, Excel tells you what it will place in the last cell, so that you know when to stop. Once your series has reached its desired length, release the left mouse button and Excel will place the appropriate information in the cells.

In our first example, Excel recognized what it was supposed to do. However, this is not always the case: when we enter ‘1990’ and try to complete the series as described above, Excel is clueless:

To be precise, it does not know how the series is supposed to work: is the next cell supposed to be 1991, 1995, 2000 or something else? This can be remedied by providing Excel with a hint: entering the second cell, too. If you select both entries, Excel can continue the series for you:

Using Excel for calculations: formulas What distinguishes a spreadsheet program such as Excel from a simple table is its ability to perform calculations with the information that you entered. To make a calculation, you need to place a formula in a cell. To distinguish a formula from a normal bit of information, every formula starts with an equal sign ‘=’. The simplest formulas contain basic arithmetic operations, such as additions, subtractions, multiplications and divisions. An example would be =2+2 If you enter this formula in a cell and press /, you will see the following:

Note that the actual cell contains the result, while the formula can be

48 Introduction to Microsoft Office ●●●●●●●●●●●●

found in the formula bar. This keeps the worksheet from becoming cluttered. If you want to edit a formula at a later stage, select the appropriate cell, then press 2 or click on the formula bar. ARITHMETIC OPERATORS The arithmetic operators in Excel are slightly different than the ones we are used to from mathematical formulas. Operation Addition Subtraction Multiplication Division Raising to power

Operator +

Example =2+2

plus sign

Result: 4

–

=3–5

minus sign

Result: –2

*

=2*3

asterisk

Result: 6

/

=5/2

slash

Result: 2.5

^

=3^2

hat

Result: 9

Excel follows the arithmetic rules of precedence when calculating the result of a formula. This implies e.g. that powers have precedence before multiplication and division, and that the latter have precedence before addition and subtraction: =2+4/2 will yield 4, not 3. You can prioritize certain parts of equations by enclosing them in brackets: =(2+4)/2 will yield 3. You can have more than one pair of brackets in the same term, e.g. =((2+4)/2+6)/18. As you include more and more brackets, it becomes harder and harder to keep track of them. That’s why Excel assigns different colors to pairs of brackets, depending on their level. The first pair of brackets will be black. If you open another pair inside the first pair, it will be green. If you open a pair of brackets inside the first two, then the third pair will be purple, and so forth. Also, whenever you close a bracket, Excel will briefly indicate the corresponding opening bracket, so that you know which pair of brackets you just closed.

Referring to cells (Part 1) Formulas are not limited to simple numbers, you can include cells in your calculations, too. To do so, use their cell address, the combination of column letter and row number. An example would be the addition of cells A1 and A2 depicted here:

Microsoft Excel: Entering Data & Making Calculations 49 ●●●●●●●●●●●●

This has one huge advantage: when you change the contents of, say, cell A2, cell A3 adjusts automatically. Every time you enter new information, all formulas will be recalculated – no further action is required on your part. CIRCULAR REFERENCES When you enter a formula that includes a cell reference, Excel uses the content of that cell for the calculation. Say, if you were to enter the formula =A1*2 in cell A2, Excel would obviously need to read the content of A1 to calculate the value of A2. Let’s assume that A1 in turn contains the formula =A2-3. So to calculate A2, Excel needs to know the value of A1. But to calculate A1, Excel needs to know A2. It’s like the hen and the egg: a circle with no clear beginning. The essence is that no formula can refer to its own result – doing so creates a circular reference. If you create such a circular reference, Excel will immediately warn you: besides opening the automatic help system to explain what is going on, Excel will indicate where the circular reference is to be found.

You will then need to break the circle by changing the formula in one of the involved cells.

Functions Obviously, the scope of primary arithmetic operations is limited. Some calculations would become very tedious, while others would be completely impossible to realize if we only had these five operations at our disposal. Imagine you would like to sum all cells between A1 and A30. If you had to write =A1+A2+A3+A4+ … +A30 you would get tired soon. That’s why Excel offers a range of functions that extend the scope of formulas while simplifying the actual work. Functions always look the same: NAME (parameter 1, parameter 2, …) Parameters Bits of information that functions use in calculations

11

A function starts with its own name. Any information that the function requires is then included in brackets. These so-called parameters have a predetermined order11 and they are separated by commas. Depending on the information that a function needs, you may have to enter up to 30 parameters (although most functions make do with just 1 to 3).

The appendix contains a list of functions, which specifies each function’s parameters and their order.

50 Introduction to Microsoft Office ●●●●●●●●●●●●

Let’s illustrate this with some examples: One of the simplest functions is the function pi(), whose sole purpose is to return the number p, 3.141… Because p does not depend on any outside factors, the function does not require any parameters: PI() We can include functions in our formulas like normal terms, e.g. =2.5^2*PI() will calculate the area of a circle with a radius of 2.5 units (19.635 square-units). If you simply want the result of a function, you will still need to enter an equal sign: =PI() will return 3.141592654. One of the most useful functions is sum(), which sums all terms contained in the brackets. It requires at least one parameter: SUM (number 1, number 2, …) You can use sum() with ordinary numbers, but you can also enter cell references, e.g. =SUM (251, A1, C3) What’s more: you can place functions inside functions: =SUM (10, PI()) Referring to cells (Part 2) So far, we have only dealt with single cells at a time. But what do you do when you want to work with a whole range of cells? Let’s come back to the example where we wanted to sum all cells between A1 and A30. So far, we could write =A1+A2+A3+A4+ … +A30 or we could write =SUM(A1,A2,A3,A4, … ,A30) but neither of them seems very appealing. Fortunately, there is a shorthand for including several cells simultaneously: quote the address of the top left cell and the address of the bottom right cell, separated by a colon. In our example, we would write A1:A30. This shorthand can be used with many different functions, such as sum(): =SUM(A1:A30) You can include as many cells as you like, e.g. =SUM(A22:Y7453) would sum all 185,800 cells between A22 and Y7453. You do not need to enter cell references manually. While you are

Microsoft Excel: Entering Data & Making Calculations 51 ●●●●●●●●●●●●

entering your formula, you can simply use the mouse to select the range of cells that you want to refer to. Excel will then enter the appropriate cell reference for you: 1)

2)

3)

INTERNATIONAL DIFFERENCES There are obvious differences between the English version of Excel and versions in other languages. Not only have all menus and dialogues been translated, the function names have been translated, too. Some of the function names are easy to translate, e.g. sum() becomes som() in the Dutch version. Others, however, differ significantly from the direct translation. It is therefore best to use an English version of Excel. Additional problems can result from the use of different decimal indicators: if you are using a non-English version of Windows, then your system most likely uses a decimal comma instead of a decimal point. Two things change when your computer uses a decimal comma: 1. Even when you are using an English version of Excel, you will need to use a decimal comma. E.g. =2,5^2*PI() 2. As a result, parameters in functions are not divided by a comma, but by a semicolon, e.g. =SUM (251; A1; C3)

The ‘function wizard’ You can enter functions just like any other formula: simply select the desired cell and start typing. For simple functions like pi() or sum(), this is certainly the easiest and quickest way. But many functions require several parameters, accept only certain values or are simply hard to remember. The ‘function wizard’ is there to help you enter such functions.

‘Insert’ „ ‘Function…’ Start the function wizard.

Whenever you want to enter a function, be it in the middle of a formula or in an empty cell, press the ‘paste function’ button to call up the function wizard. This will bring up the selection screen, where you can choose the appropriate function:

52 Introduction to Microsoft Office ●●●●●●●●●●●●

All functions are grouped together in categories such as ‘statistical functions’, ‘math and trigonometry functions’, and so forth. This usually allows you to find the function you are looking for pretty quickly. If you do not know which category a function belongs to, you can use the allencompassing ‘all functions’ category. A list of the most recently used functions is also available. Once you have selected the category in the left list, you will be able to select the desired function in the right list. For each function you select, a brief explanation will be given below the two lists. For now, let’s assume that we want to round the number contained in cell A1 to three digits after the decimal point. To do so, we select the round() function from the ‘math and trigonometry’ list and click ‘ok’. This takes us to a second window where you have the opportunity to enter all the details. Excel lists the required parameters, as well as any optional parameters (there are none in our example), tells you what each of them means and reminds you what type of information is required. In our example, the first parameter is the number that we want to round, and it obviously needs to take the form of a number.

Microsoft Excel: Entering Data & Making Calculations 53 ●●●●●●●●●●●●

To select cells when working in the function wizard, push this button. Once you are done, you can transfer the selected cell reference to the Wizard by pressing this button.

Since we want to round the number in cell A1 to 3 digits, we enter ‘A1’ in the first field and 3 in the second field. Instead of writing ‘A1’, we could also have selected the cell A1 with the mouse. If you want to select a cell that is not visible on the screen, press the ‘cell selection’ button in the right corner of any field and the window will shrink, allowing you to freely select any cells. Once you are done, press the ‘transfer references’ button to return to the function wizard. While you enter the information, Excel will check your input and, if necessary, give you feedback. If, e.g. you were to enter text instead of a number, you would get the following error message12:

Once Excel has sufficient information, you will see a preliminary result on the screen. You can then confirm your choice by click ‘ok’.

You can also use the function wizard to edit existing functions. Simply select a cell that contains a function and press the ‘paste function’ button. Parameter types Different functions require different sorts of information. Broadly speaking, there are several different types of parameters, such as numbers, text and logical statements. Parameters of the type ‘number’ and ‘text’ are self-explanatory. But if you lack experience with programming languages or spreadsheets, you will not have encountered logical statements so far. Logical statement A comparison of two pieces of information that is either correct or incorrect, e.g. 0 = 1 is such a statement, and it is incorrect.

A logical statement is a statement that compares one bit of information with another to check whether they fulfill a certain condition. For instance, A2 = 2 is such a statement. Here, the two bits of information are the cell ‘A2’ and the number ‘2’, and the condition is that both bits of information must be equal to each other. Another statement would be A2 > 2. In this case, the condition is that the cell ‘A2’ must contain a value that is larger than 2. Excel will try to determine whether a condition has been met or not. In our second example, A2 > 2, the condition would be met if the cell ‘A2’ contained the value ‘3’, because 3 > 2. In that case, we say that a

12

See below for an interpretation of the different error messages.

54 Introduction to Microsoft Office ●●●●●●●●●●●●

statement is true or correct. If, however, the cell ‘A2’ contained the value ‘1’, then the condition would not be met. We would say that the statement is false or incorrect. The decision whether a statement is true or false is the result of such a logical statement. Just as =4+3 will return 7, =1>0 will return true. This ‘response’ can then be used in logical functions e.g. for making decisions. Logical statements and functions will be discussed in more detail during your Quantitative Methods classes. A selection of functions Finding the right function for the right task is not always easy. Therefore, we have compiled a selection of functions that are useful during the first blocks of your study. The functions are grouped into three categories (mathematical, statistical and logical functions) and can be found in the appendix. Referring to cells (Part 3) When you are editing cells, you will notice that formulas quickly become hard to read if they contain several terms. While it may not be possible to simplify all formulas, you can make them easier to understand by giving your cells names. It is possible to christen cells in three steps: 1. Select the cell or cells that you want to label, …

2. click on the name box and … 3. enter a name, followed by /. Once you have named your cells, you can refer to them in formulas by their name. In our example, we would be able to write =SUM(profit), which is much easier to understand then e.g. =SUM(B4:B21). Errors Sometimes, Excel encounters an error when it is calculating the formulas that we enter. There can be two causes for this: either we did not enter the formula correctly, or a cell that we are referring to contains invalid information. An example for the first type of error would be a simple spelling mistake: =SIM(A1:A200) instead of =SUM(A1:A200). An example for the second type of error would result if we entered the

Microsoft Excel: Entering Data & Making Calculations 55 ●●●●●●●●●●●●

formula =A1/A2 and A2 contained the value ‘0’ – the formula is theoretically correct, but in practice it results in a division by zero. Excel indicates errors by printing an error message in the appropriate cell, allowing us to go back and investigate what went wrong. All in all, Excel knows eight different types of errors: · #DIV/0! Your formula may be fine, but one denominator turns out to be zero, leading to a ‘division by zero’ error. You may want to check whether your formula and all cell references are correct, and whether any cells that you refer to are empty. ·

#N/A. This error occurs only in connection with the incorrect use of lookup-functions. Lookup-functions are not discussed here since they are an advanced concept.

·

#NAME? Excel does not recognize the name of a formula or cell that you used. Entering =SIM(A1:A200) instead of =SUM(A1:A200) will get you this error. You will need to return to the cell and check all function and cell names that you used.

·

#NULL! This error is rather uncommon and occurs only in response to advanced types of cell references.

·

#NUM! You have entered an invalid number, e.g. a number that is too large.

·

#REF! This error indicates an invalid cell reference. It occurs e.g. when you replace one cell with another cell, when the original cell was used in a formula. The invalid reference has been removed from your formula; you will need to edit it.

·

#VALUE! Generally happens when the information that you entered does not match the requirements, e.g. when you enter text where a number is expected. =53+"Hello?" would result in such an error.

·

######## This is not actually an error; the formula you entered is fine. Instead, the cell is too small to display its output. For example: if the result of your formula is 187,355,202,937,928.98, but the cell only has room for eight digits, Excel will return this error. Increasing the width of the cell will take care of the problem. ●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

COMING UP… This chapter discusses different formatting options for cells and worksheets.

3. FORMATTING YOUR SPREADSHEET Over time, worksheets can become very complex, but that does not necessarily imply that they need to become cluttered or hard to understand. The present chapter discusses how you can format your worksheet. You can use these functions to make your worksheets easier to grasp and navigate, e.g. by formatting numbers, highlighting important cells and using borders and white space to delineate different data series. First, we will discuss how you can select the cells to which changes should be applied. We will then discover how it is possible to format the content of cells, the appearance of cells and the appearance of entire worksheets. Selecting cells To select a single cell, click on it so that it is surrounded by a thick black border:

Selecting a coherent block of cells works like selecting text in PowerPoint or Word: 1) Move your mouse pointer to the top left cell of the block that you want to highlight.

2) Press the left mouse button and keep it pressed while…

3) you move the mouse pointer to the lower right cell of the block.

Alternatively, you can click on the top left cell, then press and hold the s button while clicking on the lower right cell. Finally, to select several unrelated cells: 1) Click on the first cell so that it is surrounded by a thick black

58 Introduction to Microsoft Office ●●●●●●●●●●●●

border.

2) Press and hold c while you click on the next cell that you want to select. This cell will now be surrounded by a light black border, while all previously selected cells will be lightly shaded.

3) Repeat the second step until you have selected all desired cells.

There are shortcuts that allow you to select entire rows, columns or even the entire worksheet: · To select an entire row or column, click on the row or column heading.

·

To select an entire worksheet, click on the upper left corner of the worksheet area (the area to the left of the first column header and above the first row header):

Formatting cells Changing the content type Excel allows you to format cells to fit any sort of content, such as dates, currency values, fractions, etc. It will try to do so automatically when you enter the information, but in some cases, you may want to adjust the content type manually. You can do so by selecting one of the shortcuts on the formatting bar:

Microsoft Excel: Formatting Your Spreadsheet 59 ●●●●●●●●●●●●

These shortcuts allow you to have your number formatted as a currency value13 or as a percentage. You can also switch digit grouping on. Commas will then separate groups of three digits: 1475346 will become 1,475,346. Finally, you can increase or decrease the amount of visible decimal places.14 c+1 ‘Format’ „ ‘Cells…’

More options are available in the ‘Format’, ‘Cells’ dialogue under the ‘Number’ heading:

There are nine different categories that you can choose from: · General numbers. Allows you to specify the amount of visible decimal places, how negative numbers should be formatted and whether digit grouping should be applied.

13 14

·

Currency values. Allows you to specify the amount of visible decimal places, how negative numbers should be formatted and which currency symbol should be used. Numbers will be aligned at the decimal point.

·

Currency values (accounting style). Similar to the ‘currency’ type, except that the currency symbols will also be aligned. It is not possible to change the formatting of negative numbers.

·

Dates. Allows you to specify how the date should be formatted.

·

Time values. Allows specification of the time format.

·

Percentages. Allows you to choose the amount of decimal places.

The currency depends on your PC’s regional settings. If necessary, Excel will round the number automatically.

60 Introduction to Microsoft Office ●●●●●●●●●●●●

·

Fractions. Allows you to specify constraints for the fractions, e.g. ‘only single-digit fractions’, or ‘only quarters’.

·

Numbers in scientific notation. Shorthand for writing extremely large or small numbers as multiples of 10 to a power, e.g. 1.563 ´ 10 27 would equal 1,563,000,000,000,000,000,000,000,000 Because Excel lacks the ability to write powers properly, it will write 1.563E+27 instead.

·

Text. Usually, Excel will automatically recognize when you are writing text. This setting is useful if there are ambiguities, e.g. when you want to write an equal sign without it being interpreted as a formula by Excel.

Besides these nine categories, there are a number of special-purpose categories available. If all else fails, you can design your own category. Finally, you can format cells as ‘general’, i.e. leaving them without any formatting whatsoever. Formatting the cell content The formatting toolbar offers a number of choices regarding the lettering of your cells.

– Font c + s + F

This changes the typeface of your text. There is usually a broad assortment of typefaces available, but the most common ones are

Arial is selected by default as it provides good readability for numerical data. Excel will automatically adjust the cell height for you when you choose another font whose letters are higher than Arial’s. However, the cell width will not be adjusted, so if you select a font with wider characters, less information will be visible inside each cell. See the section on increasing the cell size (p. 63) for tips on correcting this problem. – Font size c + s + P

This changes the size of your text. The default size is 10 pt. Once again,

Microsoft Excel: Formatting Your Spreadsheet 61 ●●●●●●●●●●●●

Excel will automatically adjust the height of all affected cells, but it will leave their width unchanged. If you increase the font size, less information will be visible in each cell, see the section on increasing the cell size (p. 63) for tips on correcting this problem. – Font style c + B c + I c + U

Bold Italics Underline

This changes the appearance of your text. You can set your text in boldface and italics, or you can underline your words. By convention, underlining is no longer used to highlight text; instead, text is set in boldface or italics. Underlining is now used to indicate Internet links instead. – Paragraph alignment

Left

Center

Right

Allows you to choose whether your text is aligned to the left or right margin of the placeholder, or whether it is centered.

– Indentation Increase indent

Decrease indent

The ‘decrease indent’ and ‘increase indent’ buttons allow you to adjust the indentation of text. – Font color

Clicking on the left part of the button will change the color of your text to the color indicated on the button. If you wish to use another color, click on the right part of the button.

Finally, you can also change the color of the text, e.g. to indicate an important word. To do so, click on the ‘font color’ button. This will change the color of your text to whatever color is indicated on the button. (It is indicated in the bar below the ‘A’; in our case, it is black.) If you do not want your text to appear in that particular color, you can click on the right part of the button to expand the color menu.

Recycling your work You can apply any changes you are making to as many cells as you like. But you can also recycle your formatting at a later stage, e.g. when you have added new columns or rows and want to transplant the formatting from existing cells. To do so, 1. select a cell that is formatted to your liking and …

62 Introduction to Microsoft Office ●●●●●●●●●●●●

Press this button to copy the formatting of one cell to other cells.

2. press the ‘format painter’ button on the standard toolbar. At this, your mouse pointer will change shape . 3. You can now select any cells to which you want to apply the same formatting. Before:

After:

Formatting the cell – Borders The borders menu.

The borders menu allows you to quickly adjust the borders of cells. First, select the cells that you want to affect. Then expand the borders menu by clicking on the right part of the button to see the menu below.

Each of these buttons will make a number of borders visible or invisible. E.g. if you highlight a cluster of cells and click on the top left button, you will get a border around the cluster, but no internal borders between cells. c+1 ‘Format’ „ ‘Cells…’

Advanced border formatting options can be found in the ‘Format’, ‘Cells’ dialogue, under the ‘Borders’ heading.

When adjusting borders, first select the line style (thick, thin, dashed, double, etc.) and the line color. You can then opt for one of the three quick selection buttons, which affects a number of borders simultaneously. Alternatively, you can apply individual borders by clicking on any of the eight border toggles. These will switch the corresponding borders on and off.

Microsoft Excel: Formatting Your Spreadsheet 63 ●●●●●●●●●●●●

Top

Left Bottom

Between rows

Right

Between columns

Diagonal strikethrough

If you are formatting a block of cells, then the outside border toggles will affect only the cells on the perimeter of the block. E.g. the ‘bottom border’ button will toggle the bottom border for all cells in the bottom row. If you are working on a single cell instead, or on several noncontiguous cells, then the buttons will simply toggle the top, bottom, left and right borders. The inside border buttons are only available when you are working on a block of cells. They will switch borders between rows and columns on and off. Finally, the diagonal strikethrough buttons will provide you with the appropriate strikethroughs inside cells. – Background color You can set the background color of any cell by using the ‘background color’ button. – Increasing the cell size Frequently, you will enter information that does not ‘fit’ inside a single cell, i.e. the information cannot be displayed completely. If there is nothing in the next cell, then Excel will simply write across the cell boundary:

But if there is something in the next cell, then you will only see part of the information:

This problem can be solved in two ways: you can make cells wider (or higher), but this will affect all cells in that particular column (or row). Alternatively, you can merge the cell with its neighboring cells. – Adjusting column width and row height There are several ways to adjust the width of a column or the height of a row. You can do it manually by: 1. Moving your mouse pointer to the border between two column or row headers, so that it changes into a two-pronged arrow , and …

64 Introduction to Microsoft Office ●●●●●●●●●●●●

2. pressing and holding the left mouse button while you resize the column or row to your liking.

Once you release the mouse button, your changes will be put into action.

‘Format’ „ ‘Row’ „ ‘Height…’ ‘Format’ „ ‘Column’ „ ‘Width…’

Alternatively, you can select ‘Format’, ‘Column’, ‘Width’ or ‘Format’, ‘Row’, ‘Height’ from the menu bar. You will then be asked to enter a number between 0 and 255, which represents the average amount of characters visible in each cell.15 However, there is a quicker way: move your mouse pointer to the gap between two column or row headers and perform a double-click. This will automatically resize the column or row to fit the cell with the largest content.

‘Format’ „ ‘Row’ „ ‘AutoFit’ ‘Format’ „ ‘Column’ „ ‘AutoFit’

Alternatively, you can select ‘Format’, ‘Column’, ‘AutoFit’ or ‘Format’, ‘Row’, ‘AutoFit’ from the menu bar. – Merging cells The second option would be to merge two or more cells. This is advantageous whenever you want to make a cell wider, higher, or both without affecting the other cells in its row or column. To merge cells: 1. select all the cells you want to merge and …

2. press the ‘merge and center’ button. Pressing this button will merge all selected cells. It will also center their combined content.

You have just created a family-sized cell! Two things should be noted: firstly, the content of the new cell will be centered. If you prefer it to be aligned to the left or right margin, you will need to adjust the alignment yourself. Secondly, and more importantly: the new cell will only keep the value of the top left cell. The content of all other cells will be lost! 15

Be aware that this value is an average. After all, a ‘w’ is wider than an ‘i’. It is also presumed that you are working with the standard font and size, Arial 10 pt.

Microsoft Excel: Formatting Your Spreadsheet 65 ●●●●●●●●●●●●

c+1 ‘Format’ „ ‘Cells…’

If you want to ‘unmerge’ this cell at any point in the future, 1. select the merged cell 2. select ‘Format’, ‘Cells’ from the menu bar 3. switch to the ‘Alignment’ category and 4. unselect the ‘Merge cells’ option.

Working with rows and columns Inserting and deleting rows and columns ‘Insert’ „ ‘Rows’ ‘Insert’ „ ‘Columns’

To insert a new row or column, select ‘Insert’, ‘Rows’ or ‘Insert’, ‘Columns’ from the menu bar. New rows will be added above the currently active cell, while new columns will be inserted to the left of it. If you want to insert more than one row or column, highlight as many rows or columns as you need, and select the ‘Insert’, ‘Rows’ or ‘Columns’ option. Excel will insert as many new rows or columns as you have currently selected. Hiding parts of your worksheet

‘Format’ „ ‘Row’ „ ‘Hide’ ‘Format’ „ ‘Column’ „ ‘Hide’

‘Format’ „ ‘Column’ „ ‘Unhide’ ‘Format’ „ ‘Row’ „ ‘Unhide’

In some cases, you may want to hide parts of your worksheet, e.g. to make it easier to grasp. To do so, highlight the row(s) or column(s) you want to hide and select ‘Format’, ‘Row’, ‘Hide’ or ‘Format’, ‘Column’, ‘Hide’ respectively. Your rows or columns will still be there, they will just not be plainly visible. Hiding parts of your worksheet will not affect any of the formulas. To make hidden columns visible again, highlight the last column before and the first column after the hidden part, then select ‘Format’, ‘Column’, ‘Unhide’ from the menu bar. A similar procedure works for rows. Two things should be noted: firstly, it is not possible to hide individual cells; only entire rows and columns can be hidden. Secondly, hiding parts of your worksheet will not protect your data from prying eyes. There are better ways to secure your work, but an experienced user can easily circumvent all of Excel’s security measures. If you have confidential data, you should rely on professional encryption tools. Working with sheets Labeling sheets Especially when you are working with more than three sheets, it can be hard to keep track of them all. Fortunately, you can easily give them more meaningful names than ‘Sheet7’. To label a sheet,

66 Introduction to Microsoft Office ●●●●●●●●●●●●

1. double-click on its sheet tab, … 2. enter the new name and … 3. press /.

Copying and moving worksheets To copy or move a sheet, right-click on its tab and select ‘move or copy’ from the menu that pops up.

By default, Excel assumes that you want to move or copy the sheet within the same workbook. If you want to transfer a sheet to another workbook, you need to open this workbook before moving or copying the sheet. You can then specify to which workbook the sheet should be sent and where it should be inserted. Finally, you can choose whether you want to copy the sheet or move it.

Note that there can be problems when you move or copy a sheet to another workbook. If formulas on the transferred sheet referred to any of the other sheets in the original workbook, then this information will not be included in the process. If you open the new workbook, these formulas will not work unless you open the original document too. Inserting and deleting worksheets ‘Insert’ „ ‘Worksheet’

To insert a new worksheet, select ‘Insert’, ‘Worksheet’ from the menu

Microsoft Excel: Formatting Your Spreadsheet 67 ●●●●●●●●●●●●

bar. To delete a worksheet, right-click on its tab and select ‘delete’ from the menu that appears. Any formulas that refer to cells on the deleted worksheet will cease to function. Hiding a complete worksheet ‘Format’ „ ‘Sheet’ „ ‘Hide’ ‘Format’ „ ‘Sheet’ „ ‘Unhide…’

By selecting ‘Format’, ‘Sheet’, ‘Hide’ from the menu bar, you can hide an entire worksheet from view. To let it reappear again, click on ‘Format’, ‘Sheet’, ‘Unhide’ from the menu bar and then select which sheet you would like to make visible again. We should warn you that hiding worksheets will not protect sensitive data. Although Excel contains a number of security features that you could use, an experienced user can easily circumvent all of them. Rely on professional encryption tools if you need to protect confidential data. ●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

COMING UP… This chapter introduces Excel’s data analysis tool and explains how you can produce graphs with Excel.

4. ANALYZING & DISPLAYING DATA This chapter concludes our coverage of Microsoft Excel by discussing two advanced topics. In the first part, we briefly discuss Excel’s data analysis tools. In the second part we learn how to present data graphically by creating charts. The data analysis tools Excel includes a set of tools that enables you to analyze data in a variety of ways. For instance, it enables you to perform ANOVAs, z- and t-tests, and creates histograms and correlation tables for you. Unfortunately, this useful tool is not automatically available in Excel; you need to install it first. Installing the tools The data analysis tools are not automatically installed in Excel. Instead, they are an add-on, a component that needs to be activated separately. To find out whether you can use the data analysis tools, click on ‘Tools’ in the menu bar. If you can find an option called ‘Data analysis’, then the appropriate component has already been activated and you can immediately start with your analysis. If no ‘Data analysis’ option is listed, then you need to activate the component manually.

‘Tools’ „ ‘Add-ins…’

1. Select ‘Tools’, ‘Add-ins’ from the menu bar.

2. Select ‘Analysis Toolpak’ (not ‘Analysis Toolpak VBA’) in the window that appears. While you are there, you may want to activate the ‘Solver’ add-in, too. It will be used later in your Quantitative Methods course. After a few moments, the data analysis tools will be available.

70 Introduction to Microsoft Office ●●●●●●●●●●●●

Using the data analysis tools The description of each individual data analysis tool and its function goes beyond the scope of this introduction. A significant number of these tools will be covered in detail during your first year-QM courses. Therefore, we limit ourselves to a brief discussion of their common features. The data analysis tools, as most other features of Excel, usually expect your data to be arranged in columns, i.e. each column represents a different variable, while each row represents an individual observation. E.g. in the table below, several houses have been categorized according to their sales price, size, age, and other variables.

‘Tools’ „ ‘Data analysis…’

When you want to analyze data using the data analysis tools, you do not need to select the data beforehand. Instead, you can immediately launch the toolset by selecting ‘Tools’, ‘Data analysis’ from the menu bar.

You can then choose from an assortment of analysis tools. In the next step, you will need to enter information that is specific to the tool of your choice. Common to all tools are the following options: – Input options Here, you select which part of your data needs to be analyzed. You can either enter the cell reference directly, or you can select the cells manually by 1. clicking on the ‘selection’ button, … 2. selecting the cells with your mouse and … 3. confirming your choice by clicking on the ‘transfer reference’

General Topics: Getting Hooked Up 71 ●●●●●●●●●●●●

button. 1.

2.

3.

It is recommended that you select the column heading (‘size’ in our example), too. Excel will use this label in its output: if you are doing several analyses, you won’t have to guess which is which. If you select the column label, be sure to also select the ‘labels’ option – otherwise, Excel will not know how to deal with the text. – Output options Also common to all analysis tools is the output options dialogue. Here, you can specify where the results should be stored. There are three options: · Output range. This option saves the results to a range of cells on any existing worksheet. You only need to select the top left cell. Be aware that the output will usually consume several rows below and several columns to the right of that cell. Any data in these cells will be overwritten, so it is crucial that you select a cell that is not next to any important data. ·

New worksheet ply. This will create a new worksheet and save the results there. If you want to, you can provide a label for the new worksheet.

·

New workbook. This will create a new Excel document and save the data there.

The specific options of the individual tools will be among the subjects of your Quantitative Methods classes. Creating charts Let’s assume that you have prepared a ‘revenues and costs’ calculation and you would like to communicate your results. A chart of the figures would be the optimal tool for this purpose, as charts are quickly and easily understood by everyone.

72 Introduction to Microsoft Office ●●●●●●●●●●●●

The quick and dirty method You can create a chart very quickly using these two steps: 1. Select the two data series (including their labels or headings)

2. Press -. 14,000 € 12,000 € 10,000 € 8,000 €

Revenues Costs

6,000 € 4,000 € 2,000 € 0€ 1

2

3

4

5

6

7

8

9

10

11

12

The result is not too bad for such a small effort. There are, however, a number of drawbacks to this procedure. Most notably: Excel will always create a bar graph, which is not appropriate in a number of situations. In our example, there is a better alternative available… When to use which graph Different types of information call for different types of graphs. The question which graph to use in which situation will be discussed during your Quantitative Methods classes. Therefore, we only provide a short overview of the most important types of charts: · Bar charts are mainly used to allow a comparison of amounts across a range of categories. An example would be a comparison of the scores of two students in several subjects.

General Topics: Getting Hooked Up 73 ●●●●●●●●●●●●

·

Line charts are a good way to display developments over time. Such a graph would have been more appropriate for our example above, since we want to investigate the development of revenues and costs over the last year.

·

Pie charts show how many percent each category contributes to a total, e.g. which part of the monthly costs stems from wages, advertising expenditure, etc.

·

Scatter plots allow us to plot observations for two separate variables. E.g. we could plot countries according to their GDP per capita and their social security expenditures.

In the following, we will create a more appropriate line graph for our revenue and cost data. Using the chart wizard Once again, we start out by selecting the data series that we want to plot. Remember to include the labels for both data series in your selection.

This button launches the chart wizard.

We then call up the chart wizard by clicking on the appropriate icon. The chart wizard allows us to create and configure a chart in four steps. In the first step, we select which type of graph we wish to use.

For each type of chart, there are several subtypes. Excel provides a brief explanation of the purpose of the currently selected type below the selection window. You can also call up a preview to see how your data would be displayed using the selected chart type. Once you are done,

74 Introduction to Microsoft Office ●●●●●●●●●●●●

clicking ‘next’ will take you to the second step. From this step onward, Excel will automatically provide you with a thumbnail preview so that you have an idea what the final chart will look like. In the second step, you have the opportunity to make minor adjustments to your chart. First of all, Excel assumes that your data is arranged by columns, as is indeed the case in our example. If your data is arranged by rows, then you can make the appropriate adjustment now:

Secondly, and more importantly, you can also adjust the labeling of your graph. To do so, switch from the ‘data range’ to the ‘series’ category.

Here, you can specify the label for each individual series, as well as category headings that will be used to label the x-axis.

Before we started the wizard, we selected not only the actual data series, but also its label. Excel has recognized the top row as the heading and has already labeled the series for us: in the series list you can see that both data series are recorded with the appropriate names. If necessary, you can change these labels be entering a different name in the ‘name’ box. Before we continue with step 3, we also add category labels. To do so, 1. click on the ‘selection’ button in the ‘Category (X) axis labels’ box… 2. select the row headings from our original table, and …

3. confirm your choice by clicking on the ‘transfer reference’ button.

Excel will then add the appropriate labels to the x-axis. Our graph now looks roughly like this:

General Topics: Getting Hooked Up 75 ●●●●●●●●●●●●

Next on our agenda: adding titles to our graph and to the axes.

Meticulous labeling generally improves the quality of a graph. In our case, we might want to label the graph ‘Revenues & Costs 2001’, with the currency unit (‘Euro’) added to the y-axis. A brief overview of the other options: · In most cases, you will need both axes. Still, you have the option of removing one or both of them by unselecting them in the ‘axes’ menu. Furthermore, you can also specify how the labels for the x-axis should be displayed. It is usually best to go with the ‘automatic’ setting. ·

In the ‘gridlines’ category, you can switch gridlines for both axes on and off. ‘Major’ gridlines are gridlines spaced at broad intervals. ‘Minor’ gridlines are only useful when your audience needs to be able to track down the value for each observation rather precisely.

·

The next category allows you to switch the legend on and off, and it also allows you to specify where exactly it should be located in the graph.

·

The ‘data labels’ category allows you to add data labels to all lines. These labels are used when it is important for your audience to know the exact values for each observation. Using these labels tends to clutter your graph, so use them sparingly.

·

Finally, adding a ‘data table’ enables you to include the exact

76 Introduction to Microsoft Office ●●●●●●●●●●●●

values for each series and each observation in a table below the graph. This concludes the third step. The only remaining task is to specify the location of our graph.

If you wish to print the graph on one page together with your calculations, you should include it as an object in the appropriate sheet. However, if you plan to transfer the graph to another document, e.g. to a Word file, it will be of better quality if you place it on its own sheet for now. Once you click ‘finish’, the chart will be created to your specifications: 14,000 €

12,000 €

10,000 €

8,000 € Revenues Costs 6,000 €

4,000 €

2,000 €

be r

be r

ec em D

ov em N

O ct ob er

t Se pt em be r

Au gu s

Ju ly

Ju ne

M ay

Ap ril

ar ch M

Fe br ua ry

Ja nu ar y

0€

If you find at a later point that you need to adjust any of the choices made so far, simply right-click on any open space in the graph (i.e. not on the labels, lines, or axes). The menu that appears allows you to go back to any of the four steps…

Furthermore, you can edit the graph by double-clicking on any of the

General Topics: Getting Hooked Up 77 ●●●●●●●●●●●●

elements. E.g. double-clicking on one of the data series will allow you to specify its style and color. Similarly, clicking on the chart background allows you to adjust its color and border. Finally, you can also alter the appearance of the gridlines and axes in a variety of ways. The range of options is vast and goes beyond the scope of this introduction. Transferring a graph to another document To copy a graph to another document, e.g. for inclusion in a writing assignment written in Word, simply click on the white area that surrounds the actual chart. (Be careful not to click on the axes, labels or legend.) If you push the ‘copy’ button, you will note that the entire chart will be surrounded by a dashed border. You will be able to insert the graph into other documents as long as this border is there. ‘Edit’ „ ‘Paste special…’

When you insert the graph into Word or PowerPoint, you may want to do so by selecting ‘Edit’, ‘Paste special’ from the menu bar, instead of simply clicking on the ‘paste’ button. This will allow you to specify how the chart should be inserted. Usually, the choice is between the options ‘Microsoft Excel Chart Object’, ‘Picture’ and a couple of others. Choosing ‘Microsoft Excel Chart Object’ allows you to edit the graph later on, even when you do not have the original Excel document anymore. At the same time, opting for ‘Microsoft Excel Chart Object’ will greatly increase the size of your Word or PowerPoint file because all data and all options need to be saved together with the chart. If you want to keep the file size as small as possible, go for the ‘picture’ option. There will be no qualitative difference between the two ways of pasting the chart. ●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

MICROSOFT POWERPOINT

Image: size-isnt-everything.co.uk

●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

1. INTRODUCTION Microsoft PowerPoint is a presentation graphics program. You can use it to prepare visual aids and handouts for your presentations. If you are using a beamer, you will even be able to add movies, sounds and animations to your presentation. In the following, we will acquaint you with the program and offer you a step-by-step guide to preparing presentations. Starting PowerPoint

‘Start’ „ ‘Programs’ „ ‘Microsoft PowerPoint’ Create new Office docs.

Open existing Office docs.

You can start PowerPoint by double-clicking on the PowerPoint icon on your desktop. If there is no such icon, you can start the program by clicking on ‘Programs’ and then ‘Microsoft PowerPoint’ in the Start menu. PowerPoint will start as well if you double-click on any PowerPoint document. Finally, you can also use the ‘new office document’ and ‘open office document tools’. Unless you are opening an existing PowerPoint document, you will be presented with a range of choices that are meant to get you started quickly. For now, we presume that you want to create a new presentation. In that case, you have to choose between three options:

The AutoContent Wizard will provide you with a structured outline for your presentation. Say you want to present a marketing strategy for your marketing course, then PowerPoint would suggest a structure like this: · Describe the market · Introduce the product · Provide an overview of the competition · Position the product · Discuss the communication strategy · Comment on logistical aspects · Provide a way to measure success · Outline a schedule The AutoContent Wizard is an extremely useful feature if you are not entirely certain how to structure your presentation. But there is a major caveat: there are almost no AutoContent Wizards for students. In most cases, you will have to do it on your own. Template wizards do not help you with the actual content of your presentation, but they provide pre-designed slides for your presentation.

82 Introduction to Microsoft Office ●●●●●●●●●●●●

These templates include all the settings for background patters, color schemes and fonts, so that you do not have to worry about the look of your presentation anymore. The advantage is that you can immediately start working on the content. However, some people are easily distracted by the design elements on the slides. For these people, it may be best to start with a blank presentation. You can always go back and add a design template later. In the following, we will start off with a blank presentation. The main reason is expressed by the mantra ‘form follows function’. A presentation is always about the content, so you should focus first and foremost on what you want to say. After you have selected ‘blank presentation’ from the opening screen, you will need to choose how your first slide should be structured.

Choose your slide structure…

You will usually start your presentation with a title slide. To do so, double-click on the top left option. Finding your way in PowerPoint At this point, you will be confronted with the main screen of PowerPoint.

Microsoft PowerPoint: Introduction 83 ●●●●●●●●●●●●

Besides the obligatory menu bar, toolbars and status bar, you will note that the screen has been divided into three distinct areas. Outline pane Used to design the broad structure of a presentation.

To the left, we have the outline pane. When you are creating a new presentation, you will usually turn to the outline pane first. It is used to prepare the general structure of your presentation.

Slide preview Area where you edit your slides.

The large area to the left is the slide preview. This is where you will refine your presentation by adding text, images and graphs.

Notes pane Area where you jot down notes for individual slides.

Finally, below the slide preview is the notes pane. Here you can leave remarks regarding each individual slide. These notes will not show up on the screen during your presentation, but they will be included in the handouts that PowerPoint can generate for you. Before you start… Be aware that your visual aids are just that, a support for your presentation. You should first and foremost focus on what you want to say, not what is on the screen or on the overhead projector. A good way to obtain this focus by completing a short exercise before you start working on your slides: find a place where you are alone and try to hold your speech without preparing or using any notes. (If you are presenting a paper or essay you have written, put it away.) Even though the exercise may be awkward and your impromptu speech may be rather short, it is a

84 Introduction to Microsoft Office ●●●●●●●●●●●●

helpful experience. Once you are done, collect all the points that you mentioned during your test run. Now, try to structure your points logically, from the problem to the solution, from the broad to the detailed, etc. At this point (or even during your speech), you may notice that you forgot to mention one thing or another. Be sure to include them in your structure, but highlight them with a colored marker – these are topics that you may tend to forget again and you will have to pay special attention to them when rehearsing your presentation later. Once you have a broad structure, you are ready to prepare your slides. We will do so in the following order: 1. Transfer your structure to PowerPoint 2. Flesh out each individual slide 3. Add notes when necessary 4. Check the order of your slides 5. Add outlines and summaries 6. Add a layout and additional effects The first two steps are the biggest chunk of work, and we will focus on the in the following chapter. The remaining four tasks are concerned with checking and refining your presentation, and are discussed in the third chapter of this section. ●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

COMING UP… This chapter discusses how you can create transparencies in PowerPoint, moving from a broad outline to a complete series of slides.

2. BUILDING YOUR VISUAL AIDS In the process of creating slides for a presentation, creating a structure and populating your slides with text and illustrations is the largest part of the work. Below, we will discuss how you can quickly create a large number of slides by transferring your outline to PowerPoint. We will then focus on giving these slides substance by adding text, images, tables and charts. Transferring your structure to PowerPoint Entering your outline in PowerPoint does not take too much time. Simply click once on the outline pane and you can start typing. Initially, PowerPoint will interpret your input as titles for each slide. Every time you press /, PowerPoint will generate a new slide for you. This way, you can easily create and label a large number of slides without needing to enter the actual content.

Your outline might look like this…

However, if you have a large number of slides, entering at least a few keywords regarding their content may be beneficial. To do so, press / and T after entering the title of a slide. Pressing T moves you to a lower level: everything that you enter now will show up as keywords on the slide. Once you are done entering the last keyword, press / and then s + T. You will then be able to enter a slide title again. Fleshing out the individual slides

The slide selector is directly below the scroll bar.

Once you have prepared a broad outline, you will want to enter more detailed text on each slide. To do so, we move to the slide preview. You can use the scroll bars to select the slide that you want to work on, or you can use the slide selector to browse through the slides one by one. On slides, everything needs to be in placeholders. The advantage of placeholders is that they can be moved around or resized easily to fit your needs. Almost all slides have pre-positioned placeholders, which you can recognize by their dashed border and the hint ‘click to add text’ or ‘click to add title’.

Entering text To enter text, click once inside the border of the placeholder of your choice. Once a placeholder contains text (or anything else), its border becomes invisible. If you want to edit the text inside a placeholder, simply click once on the text itself.

86 Introduction to Microsoft Office ●●●●●●●●●●●●

When entering text, remember that your slides are only meant as a support for your actual presentation. That implies that you should keep your text short and to the point. The best way to do this is to restrict yourself to keywords only. You do not want your audience to spend significant time reading elaborate sentences on the screen while they should be listening to you. Also, for reasons of visibility, you should restrict yourself to 30 words or (preferably) less per slide, and make sure that the text is big enough. Font sizes around 30 pt. are best. Only if you are really desperate can you go down to 24 pt. Everything below 24 pt. is a big no-no. Formatting text To format your text, you can use the formatting toolbar.

You have the following options: – Font c + s + F

This changes the typeface of your text. There is usually a broad assortment of typefaces available, but the most common ones are

– Font size c + s + F

This changes the size of your text. For reasons of visibility, choose sizes around 30 pt. You should go no lower than 24 pt. Unlike in Word, it is probably best if not to use this option. To maintain a uniform look throughout your slides, PowerPoint contains two options that apply standardized changes to your text. Both can be found on the formatting toolbar. – Adjusting the font size

c + s + > Increase the font size.

The first set of buttons allows you to change the size of your text. Each time you click the ‘increase font size’ or ‘decrease font size’ buttons, PowerPoint adjusts the size of your text by a degree that will be visible on the screen.

Microsoft PowerPoint: Building Your Visual Aids 87 ●●●●●●●●●●●●

c + s + < Decrease the font size.

– Promoting and demoting text The second set of buttons adjusts the level of the text. In your lists, some entries may be broad categories and some may be individual items that belong to a category, as in our example below: · Economics Propedeuse o Block 1 § Organization & Marketing § Quantitative Methods 1 o Block 2 § Competition & Government Policy § Quantitative Methods 2 Here, ‘Economics Propedeuse’ is the broadest category, and it contains subcategories such as ‘block 1’. These subcategories in turn include individual entries, which are even narrower in scope. We say that ‘Economics Propedeuse’ is on the highest level, and the other categories are on lower levels. When you enter your list, you start by typing · Economics Propedeuse · Block 1 At this point, you will have to demote ‘block 1’ to a lower level by clicking the ‘demote’ button, or pressing T. · Economics Propedeuse o Block 1

Demote

In PowerPoint, this will indent the line you are currently in. It will also decrease the font size and change the style of the bullet to indicate a conceptual difference. To reverse the process, you can ‘promote’ entries to higher levels by using the ‘promote’ button or pressing s + T.

Promote

– Font style c + B c + I c + U

Bold Italics Underline Shadow

This changes the appearance of your text. You can set your text in boldface or italics, and you can underline your words or add a shadow. By convention, underlining is no longer used to highlight text; instead, text is set in boldface or italics. Underlining is now used to indicate Internet links instead. The shadow will be too weak to create a distinction between more and less important text, but you can use it as an added effect for words that stand alone. – Paragraph alignment

Left

Center

c + L

Right

c + R

Allows you to choose whether your text is aligned to the left or right margin of the placeholder, or whether it is centered.

88 Introduction to Microsoft Office ●●●●●●●●●●●●

– List type Ordered list.

Unordered list.

Finally, most of the text on slides is in the form of a list. Here you can choose whether it is an ordered or unordered list. To illustrate: An ordered list: An unordered list: 1. First item · One item 2. Second item · Another item – Adjusting the color

Clicking on the left part of the button will change the color of your text to the color indicated on the button. If you wish to use another color, click on the right part of the button.

Finally, you can also change the color of the text, e.g. to indicate an important word. To do so, click on the ‘font color’ button which is (for no reason at all) not located on the formatting but on the drawing toolbar. This will change the color of your text to whatever color is indicated on the button. (It is indicated in the bar below the ‘A’; in our case, it is black.) If you do not want your text to be that particular color, you can click on the right part of the button to expand the color menu.

The color menu always contains eight colors, all of which serve a particular function. 1. Background 2. Text and lines 3. Shadows Title text 4. 5. Fills 6. Accent 7. Accent and hyperlink 8. Accent and followed hyperlink Depending on the design template’s color scheme, the colors may change, but their function will not. E.g. the second color will always be the standard color for text. To highlight text, use the ‘accent’ color (no. six). We realize that (depending on your taste preferences), some of these colors may cause nausea. In that case, feel free to click on ‘more font colors’ and take your pick from the palette that pops up. However, sticking with the eight colors above has one advantage: if you change to another design template later on, your colors will be adjusted automatically to the new color scheme. Color that you choose from the ‘more font colors’ palette will not be adjusted – you will have to go through each slide manually to see if your colors conflict with the new design template.

Microsoft PowerPoint: Building Your Visual Aids 89 ●●●●●●●●●●●●

The effect your changes will have… There are two possible scenarios. You may want to change text that you have already typed, or you may alter the attributes of text you are about to type. – Editing text When you want to change the attributes of text that you already typed, you need to indicate which part of the text you want to affect. You can do so by marking the text. 1. Move your mouse pointer to the beginning of the text that you want to highlight. 2. Press the left mouse button and keep it pressed while… 3. you move the mouse pointer to the end of the text. 4. Make your changes.

Using the mouse may not always be the fastest way to select text. The section on selecting text on p. 90 details several keyboard shortcuts that can greatly speed up the process. – Altering the attributes of text you are about to type If you want to adjust the properties of text that you have not typed yet, simply make the changes without highlighting anything and start typing. E.g. if you know that the next word should be printed in italics, simply press c + I or click on the italics-button and type the word. To continue typing normally, press c + I again. To remove formatting

c +n Removes all formatting.

If you mistakenly italicized some text, you can simply undo the damage by pressing the italics-button again. The same goes for all the formatting styles. There also is a shortcut to remove all formatting simultaneously: simply hold c while pressing n . Cutting, copying, pasting Just like in most Office programs, you can cut, copy and paste text and other objects. Before you can cut or copy, you need to select the elements that you want to affect. The procedure differs for selecting text and for selecting placeholders. Once you selected all desired elements,

90 Introduction to Microsoft Office ●●●●●●●●●●●●

you can continue to cut and paste them normally. (See ch. 2 of the general section, ‘Common elements of Microsoft Office programs’ for more details.) – Selecting text To select text using the mouse, follow these three easy steps: 1) Move your mouse pointer to the beginning of the text that you want to highlight. 2) Press the left mouse button and keep it pressed while… 3) you move the mouse pointer to the end of the text.

Instead of using the mouse, you can also use the keyboard: 1) Simply move the cursor to the beginning (or end) of the text. 2) Press and hold the s key while… 3) … moving the cursor around with the arrow keys u, d, l and r. This allows you to highlight one character at a time. You can speed up the process by holding s and c while using the arrow keys. This will highlight one word at a time. Some additional timesavers: · Pressing s + h or s + e highlights everything from your current position to the beginning or end of the current line. · Pressing c + s + h or c + s + e highlight everything from your current position to the top or bottom of the document. · Pressing s + M or s + W highlights larger portions of text. · Pressing c + s + u or c + s + d highlight everything from your current position to the top or bottom of the paragraph. Selecting placeholders 1) Click on the first placeholder. A border may appear around the placeholder. (This depends on its contents.)

Microsoft PowerPoint: Building Your Visual Aids 91 ●●●●●●●●●●●●

2) Press and hold s while you click on the next placeholder. It, too, may be surrounded by a border.

3) Repeat the second step until you have selected all desired placeholders. Undoing changes c + Z

‘Edit’ „ ‘Undo’ Undo changes.

c + Y

‘Edit’ „ ‘Redo’ Redo

c + Y

‘Edit’ „ ‘Repeat’ Repeat

When you have done something really disastrous, e.g. deleted all the text on one slide, you can always undo the changes one at a time by clicking the undo button. If you click on the right part of the undo button , you will see a list of your last changes and you can undo as many of them as you like.

If it turns out that the changes were indeed justified, you can re-do them by clicking the ‘redo’ button. If you use the ‘redo’ button immediately after pressing the ‘undo’ button, your changes will be restored. If your last action however was not to ‘undo’ something, then the ‘redo’ button will be deactivated. Instead, you can use the keyboard shortcut or the appropriate ‘Edit’ menu item to repeat your last action. This will not work in all circumstances. Adding additional slides

c + M

‘Insert’ „ ‘New Slide…’ Insert additional slides.

If you need to insert additional slides at any point, click on the ‘new slide’ button. You can then choose from an assortment of preformatted slides for simple text, two-sided arguments, tables, graphs etc.

Choose your slide structure…

92 Introduction to Microsoft Office ●●●●●●●●●●●●

Add a table to your slide Adds a table of the dimensions that you specify.

To add a table to your slide, simply click on the ‘add table’ button on the standard toolbar and select the dimensions of your table from the grid that pops up:

The table will then be placed on your slide. Tables are always placed inside their own placeholder – it is not possible to add a table to a preexisting text placeholder. You can now add text to the individual cells. If the cells are too big or too small, you can resize them by placing your mouse on top of any of When you are above a table the borders, so that it turns into a double line that is pulled by two border, your mouse pointer looks like this to indicate that you can opposing arrows . Press and hold the left mouse button and resize the table to your liking. resize the table.

To see the ‘tables and borders’ toolbar, press this button.

Whenever you are working on a table, the ‘tables and borders’ toolbar will automatically pop up. You can also force it to appear by clicking the ‘tables and borders’ button on the standard toolbar.

Below, we provide a quick overview of its features. – Drawing tools Press this button to draw a table.

When drawing tables, your mouse pointer looks like a pen.

Instead of creating a table as described above, you can also use the drawing tools. Click on the ‘draw table’ button and your mouse point will turn into a pen . Now you can conveniently draw cells and entire tables. When you are done, you need to turn the drawing feature off by clicking on the ‘draw table’ button a second time.

Microsoft PowerPoint: Building Your Visual Aids 93 ●●●●●●●●●●●●

If you made mistakes when drawing or if your table has a few Erase cells and borders using this superfluous cells, you can take care of them by using the eraser. Click on the ‘eraser’ button to switch the feature on or off. When the tool is tool. activated, your mouse pointer will look like an eraser and will perform the same function: it will remove unnecessary lines and cells from your When erasing, your mouse tables. pointer will look like this.

– Border attributes Sets the line style.

Sets the border thickness.

Sets the border color.

You can adjust three different types of border attributes: their style, thickness and color. Among the border styles you can select to have no border, a straight line, or a dashed line. The thickness determines how pronounced the border will appear on the screen. Common values are 1 pt., 2 ½ pt. and 3 ¼ pt. Finally, the border color button presents you with the obvious choice. Once again, you can choose between colors from the standardized color scheme or from the ‘more colors’ palette. – Borders menu

The borders menu.

The borders menu allows you to quickly adjust the borders of a whole array of cells or even of the entire table. To apply these settings, you need to select the cells that you want to affect. You can then expand the borders menu by click on the right part of the button :

Each of these buttons will make a number of borders visible or invisible. E.g. if you highlight your entire table and click on the top left button, you will get a border around your table, but no internal borders between your cells. – Background color This button allows you to pick a background color for your cells.

You can set the background color of any cell by using the ‘background color’ button. As always, you can take your pick from the standardized color scheme or you can opt for any other color. – Table menu

Quick access to the table menu.

The table menu gives you access to a number of maintenance functions for your table: you can add columns and rows and quickly select parts of your table or even the entire table.

94 Introduction to Microsoft Office ●●●●●●●●●●●●

– Merge & split cells Merge as many cells as you like using this button.

This button lets you split a cell into a number of rows and/or columns.

In some cases, you may want to combine a number of cells into one larger cell. You can do so by highlighting these cells and then clicking on the ‘merge cells’ button. Obviously, this button will only work if you have selected more than one cell. The opposite is also possible: you can split a cell into as many rows and columns as you want, simply by clicking on the ‘split cell’ button. This button is not available when you have selected more than one cell. – Vertical alignment

Top

Center

Bottom

The vertical alignment determines whether your text is located at the top, the center or the bottom of each cell. Adding other elements to your slides You can also freely insert content from other applications. You may e.g. want to add a graph from Excel. To do so, simply cut or copy the graph in Excel and paste it into your slide. The object will automatically be placed inside its own placeholder, so that you can move it around and resize it.

‘Insert’ „ ‘Picture’ ‘Insert’ „ ‘Movies and Sounds’ ‘Insert’ „ ‘Object…’ ClipArt A picture or drawing inserted into text documents for illustration purposes.

Alternatively, you can add pictures, movies, sounds and other objects by clicking on ‘Insert’ on the menu bar, and then selecting among the ‘Picture’, ‘Movies and sounds’ and ‘Object’ options. You can insert pictures from files, from a scanner or from a digital camera, and you may want to check out PowerPoint’s ClipArt library. Finally, you can insert just about any object that you can create using other software. Moving and resizing individual placeholders Once you placed your content on the slide, you may want to refine the layout by moving the individual placeholders around or by expanding or shrinking some of the items.

The border of a text placeholder.

Your mouse pointer will look like this.

To move tables and text placeholders, click inside them once to make their border visible. If you place your mouse pointer on top of the border, it will sport a four-way arrow: you can now move the object around by pressing and holding the left mouse button while moving the mouse. Moving objects other than text boxes or tables is even easier: your mouse pointer only needs to be on top of the object, you do not have to worry about its border.

Microsoft PowerPoint: Building Your Visual Aids 95 ●●●●●●●●●●●●

To resize objects, use the white squares at the corners and in the middle of their border.

When resizing objects, your mouse pointer will look like a two-pronged arrow.

Resizing objects works in a similar way. If you are working with a text placeholder or a table, you need to make its border visible by clicking on the text or table once. Notice that there are white squares at each of the four corners and in the middle of each border. When you place your mouse pointer on top of any of these squares, it will turn into a twopronged arrow, indicating that you can now make the object larger or smaller. Simply press the left mouse button and keep it pressed while you move the object’s border. Example:

Move your mouse pointer to one of the squares.

Press the left mouse button and keep it pressed while you relocate the border.

Release the mouse button when the object has the desired dimensions.

Note that each square only allows you to move the border(s) to which it is attached. E.g. the square in the middle of the top border will allow you to move the top border up and down (see above), while the square in the lower right corner allows you to simultaneously move the borders to the right and below the object. The mouse pointer will indicate in which directions you can move. To resize objects other than text boxes and tables, you will need to click on them once. At this point, the appropriate white squares will appear next to the object. Adding arrows, geometric shapes, etc. Finally, you may want to add arrows, boxes or other shapes to convey certain ideas. You may e.g. want to draw a mind-map. You can easily do so using the drawing toolbar.

Simply select a shape by clicking on the appropriate button and start drawing. To draw, move your mouse to the point where you want the upper left corner of the object to be. Press and hold the left mouse button and move to the point where the lower right corner should be located. If the object did not come out quite right, you can move and resize it like

96 Introduction to Microsoft Office ●●●●●●●●●●●●

any other object on a slide. Furthermore, you can easily adjust its properties by using one of the attribute buttons on the drawing toolbar.

The ‘draw’ menu offers a great number of further options.

Finally, the ‘draw’ menu features some additional options, such as rotating objects or placing them in above or below other objects. The ‘draw’ menu can also be found on the drawing toolbar. ●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

COMING UP… In this chapter, we learn how to perfect the order of slides, how to add outlines and summaries, and how to prepare a slide show or printouts for your presentation.

3. REFINING & REHEARSING Once you have prepared all individual slides, it is time to polish your presentation by checking the order of the slides, adding outlines and summaries, and by rehearsing it. Finally, we will turn to the question of printing your slides and materials or transferring them to a disk. Before you go on… At this point, you may want to have a first rehearsal of your presentation. You should try to do the test run without notes: by now, you have spent considerable time on your presentation, so you should be fairly well acquainted with its content and order. This rehearsal serves several purposes: · to give you a better idea of what you will be saying when each individual slide is on · to time yourself · to check whether the content of all slides fits your speech, so that any mistakes can be corrected before you invest further work · to see if you have trouble remembering certain facts, figures or discussion points, which can then be put on cue cards for use during the presentation There are several ways you can rehearse. Pressing 5 will start the presentation. You can use n, /, d, r and N or the left mouse button to move forward in your presentation, i.e. to put the next slide on. If you went too far, you can use u, l and P to return to the previous slide.

‘Slide show’ „ ‘Rehearse timings’

Alternatively, you can do a timed rehearsal. To do so, click on ‘Slide show’, ‘Rehearse timings’ on the menu bar. The computer will now keep track of the time you spend on each slide. When you are done with your presentation, PowerPoint will tell you the overall total and will give you the option of saving the timing for each slide. At this point, you should select ‘no’, to keep PowerPoint from using these timings to automatically switch from slide to slide16. Adding notes One of the reasons for the rehearsal was to find out whether you have trouble remembering certain items. If you do, you may want to document them in the notes for the appropriate slides. Alternatively, you can direct your notes at your audience and have PowerPoint print them on your handouts. The choice is up to you.

16

You should use the automatic slide timing only when your presentation is complete and you have rehearsed it several times so that you know your rhythm. Until then, it is best to manually switch from slide to slide

98 Introduction to Microsoft Office ●●●●●●●●●●●●

Check the order of your slides The fourth icon on the view selector takes you to the ‘slide sorter’ view. ‘View’ „ ‘Slide sorter’

Another reason for the rehearsal was to check whether your slides are in a sensible order so that your presentation flows naturally and does not jump from topic to topic. If you want to rearrange your slides, you can switch to the ‘slide sorter view’ using the view selection bar or by clicking on ‘View’, ‘Slide sorter’ on the menu bar. The slide sorter view provides you with miniature previews of a large number of slides, allowing you to simply pick them up and drag them around.

PowerPoint in the ‘slide sorter’ view.

To move a slide, simply click on it and keep the left mouse button pressed while you drag it to its new position. Add outlines and summaries In the last stage of the content phase, you may want to give your audience some additional guidance. It is beneficial to start your presentation with an outline of the subjects that you will discuss, and to summarize your arguments before reaching a conclusion.

Automatically create a summary slide… 17

An outline slide is meant to give an overview of the topics that you are about to discuss. In most of the cases, it will be sufficient if it simply contains a list of these topics. Such a slide, which is called a ‘summary slide’ in PowerPoint, can easily be created automatically: simply highlight all your slides (or at least the important ones) in the slide sorter view17 and click on the ‘summary slide’ button. This will automatically

Click on the first slide, press and hold s, then click on the last slide. To select or unselect individual slides, press and hold c while clicking on the slides.

General Topics: Getting Hooked Up 99 ●●●●●●●●●●●●

insert an outline slide (or possibly several outline slides) containing the titles of all the highlighted slides. While PowerPoint calls your outline a summary page, a real summary page is meant to recapture your main arguments before your conclusion. It is meant to provide the big picture for the audience that may still be stuck in the detailed arguments that you presented. Such a page should be written manually. When you are giving a rather long presentation, it may be a good idea to have an outline at the beginning and a summary at the end of each part of the presentation – but this usually applies only to presentations that last 10 minutes or longer. Add a layout and additional effects Design templates

‘Format’ „ ‘Apply design template…’

‘View’ „ ‘Normal’

With the content prepared, we turn towards the looks of your presentation. If you started out with a blank presentation, you can now add a design template to your slides by selecting ‘Format’ from the menu bar, and then clicking on ‘Apply design template’. You can then choose out of an array of different designs. Your choice will affect all the slides in your presentation. Be aware that design templates also change the fonts used in your slides, so that the text may be arranged slightly differently. Check each individual slide to see if you need to correct anything. You can switch back from the ‘slide sorter’ view to the ‘normal’ view by clicking on the first icon on the view selector bar, by double-clicking on any individual slide in the ‘slide sorter’ view or by selecting ‘View’, ‘Normal’ from the menu bar. Transitions and animations Also, if you are delivering your presentation using a beamer or computer screen, you may want to add some transitions and animations to your slides. Transitions determine how a new slide replaces its predecessor on the screen. Your new slides could e.g. fly in from the right side of the screen. Or the screen could fade to black and then fade in on the new slide. Animations, on the other hand determine how individual discussion points enter the screen. By default, all your discussion points are immediately visible on your slide. If you select an animation, your slide will initially be empty (except for the title) and your bullet points will enter the screen one by one at the push of a button.

100 Introduction to Microsoft Office ●●●●●●●●●●●●

Choose your transition here…

…and your animation here.

A number of different options available in the ‘slide sorter’ view. First, mark all the slides to which you want to apply a particular effect. Then click on the ‘transition’ field and select a transition effect. The same holds for animations. You will see a miniature preview once you have selected any effect. Rehearsing your presentation Finally, you may want to rehearse your presentation for a couple more times. (You will find more detailed information regarding timed and non-timed rehearsal on p. 97.) This is also the time to prepare your cue cards. Transferring your presentation There are many different ways to take your slides to the presentation. If you know that you can use a beamer or computer screen when delivering your presentation, you will want to take them in electronic form. Otherwise, you will need to create sheets for an overhead projector. Finally, you may want to distribute handouts so that your audience can take notes during the presentation. Transferring your slides in electronic form

This saves your presentation…

‘File’ „ ‘Pack and go’

If you know that PowerPoint is installed on the PC you will be using during your presentation, you only need to save your presentation. To do so, click on the ‘save’ button. By default, your slides will be saved as a normal ‘presentation’. Instead, you may opt to save it as a ‘PowerPoint show’. The difference is marginal: when you open a ‘PowerPoint show’, the presentation starts immediately. This saves some time because it will not open directly in PowerPoint and you do not need to press 5 to start the presentation. To save your slides as a show, select ‘PowerPoint show’ in the ‘save as type’ selection box. Once you have saved the file, you can transfer it to a floppy disk or email it to the place were you will use it. If, however, there are doubts whether you have PowerPoint available during your presentation, it is best to go with the ‘Pack and go’ wizard. Simply click on ‘File’ from the menu bar, and then select ‘Pack and go’. This wizard packages your presentation so you can use it on any Windows-PC. The first step does not require any action, so you can immediately click on ‘next’. In the second step, you need to indicate which presentation you want to package. For now, we assume that you want to use the presentation that you are currently working on.

General Topics: Getting Hooked Up 101 ●●●●●●●●●●●●

In the third step, you can choose where you want to save the final result.

Next, PowerPoint needs to know what it should include. If you included any graphics or other objects, be sure to select ‘include linked files’. If you used any fancy fonts (instead of the normal fonts that are set by default), you should also select ‘embed TrueType fonts’ to be on the safe side.

Finally, if you know that the PC to be used during your presentation does not have PowerPoint installed, you always need to include the viewer.

This was the last step, clicking on ‘finish’ now will create several files. A setup file (‘pngsetup’) and one or more archive files (in our case: ‘pres0.ppz’). If you selected a floppy disk drive as a destination, then the files may not fit on a single floppy. They will then be distributed over several disks. To install your presentation, simply run ‘pngsetup’ by double-clicking on the icon. This will then allow you to extract your entire presentation to a destination of your choice. After extraction of the files, you will be given the option of immediately running the presentation. As a final remark: be aware that many things can go wrong. The computer or beamer you were planning to use may be out of order, your floppy disk may be damaged or you may be forced to shift to another room that does not have the appropriate equipment installed. If it is not too expensive, it may be advisable to print your presentation on a set of

102 Introduction to Microsoft Office ●●●●●●●●●●●●

ordinary overhead sheets (see below) just to be safe. If you are using an overhead projector In this case, you will have to print a set of overhead sheets. If you have a printer at home, it may be able to print sheets for you. Please check its user manual to see if it is capable of doing so and if it needs any particular type of sheets. You should be able to get blank sheets at any decent stationary store. If you cannot use your own printer to produce sheets, you can print your sheets in the computer lab. To do so, save your presentation on a floppy (see above), open it in the SMR and press c + P. (Do not press the print button, this will instantaneously create a printout before you can make any of the required adjustments!) To avoid making any costly misprints, please follow these instructions precisely: 1. Select ‘Tektronix 850’ as printer. 2. Click on ‘Properties’ (right next to the printer name). 3. Set ‘transparency’ as the paper source. 4. Close the ‘properties’ window by clicking on ‘ok’. 5. Make sure that the number of copies is set to ‘1’, 6. that PowerPoint is about to print ‘slides’,

7. and (if you want color sheets) that ‘grayscale’ and ‘pure black and white’ are not selected.

8. Start the print process by clicking on ‘ok’.

Please be aware that printing slides (and particularly color slides) is significantly more expensive than printing normal documents. Also, you will need to return the settings to their initial values after your printout if you intend to print other documents too.

General Topics: Getting Hooked Up 103 ●●●●●●●●●●●●

Preparing handouts and notes PowerPoint can also generate handouts for your audience. There are two different types: ‘handouts’, which only include miniature views of your slides, and ‘notes pages’ which include a copy of each slide together with the notes that you entered for that slide. For print options, press c + P. (Please make sure that a HewlettPackard laser printer is selected at this stage.) To print handouts, simply select them from the ‘print what?’ list. You can then choose how many slides should be printed per page: 2, 3, 4, 6 and 9 are possible values. Three slides are a common value, because this setting leaves your audience enough room to take notes for each individual slide. Alternatively, you can also print notes for your presentation. To print them, all you need to do is to select ‘notes pages’ from the ‘print what?’ list. ●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

APPENDIX

Image: size-isnt-everything.co.uk

●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●

A. A SELECTION OF USEFUL EXCEL FUNCTIONS This appendix lists a number of useful Excel functions in the areas of mathematics and statistics. All logical functions are listed as well, as they play a crucial part in any subject area. Syntax Set of grammatical rules. The syntax determines in which order you convey information. Just as there is a certain order in the English language, e.g. ‘Subject – Verb – Object’ for a normal sentence, there is a certain order to information that you pass on to your computer. Without this order, the computer would be unable to understand the significance of each bit of information.

For each function, the following information is given: · its use · its syntax · an explanation of the variables (if applicable) Also, one or more examples are provided for most functions. Whenever we discuss the syntax of a function, we will indicate it with a special font: SUM (number 1, number 2, …) All required parts of a function will be printed in bold. In our example, you will need to include at least one number in the brackets. You can include more numbers, separated by commas18, but it is not necessary to do so. Note:

Mathematical functions abs() – Absolute value This function returns the absolute value of a number, i.e. the same number but without its sign. ABS (number) where: · number is a number of your choice or a reference to a single cell Examples: =ABS(-5) will return 5 =ABS(5.867) will return 5.867 exp() – (Natural) exponential function This function raises Euler’s number e to a power. In mathematical terms: e x EXP (exponent) where: 18

Note that punctuation marks depend on the regional settings of your PC. For further information, see p. 3.

108 Introduction to Microsoft Office ●●●●●●●●●●●●

·

exponent is any number or reference to a single cell

Examples: =EXP(0) returns 1 =EXP(1) returns 2.7183 (e, Euler’s number) =EXP(2) returns e 2 = 7.389 Remark: exp() is the inverse of ln(), the natural logarithm. ln() – Natural logarithm This function returns the natural logarithm of a number. LN (number) where: · number is any positive number or reference to a single cell which contains a positive number Examples: =LN(-1) will return a #NUM! error because the number is not positive =LN(0) will return a #NUM! error because the number is not positive =LN(1) will return 0 =LN(2.7183) will return 1 Remark: ln() is the inverse of exp(), the natural exponential function. log() – Logarithm This function returns the logarithm of a number to a specified base. LOG (number, base) where: · number is any positive number or reference to a single cell which contains a positive number o base is any positive number or reference to a single cell which contains a positive number (optional, if left empty, ‘10’ will be used) Examples: =LOG(-1) will return a #NUM! error because the number is not positive =LOG(10) will return 1 =LOG(2, 5) will return 0.431

Appendix: A Selection of Useful Excel Functions 109 ●●●●●●●●●●●●

Matrix functions

Array functions Functions that are valid for an array of cells. The result of such a function not only depends on the function itself, but also on the position of each cell in the array. E.g. the same function could return a different result for cell 2 in row 1 than for cell 4 in row 3. Array functions are entered in a different way than normal functions.

All matrix functions except mdeterm() are slightly different from normal functions because they need to be entered for several cells simultaneously. Such functions are called array functions. To enter such functions, follow these three steps: 1. Select as many cells as you need (in the correct proportion). E.g. if you are calculating the inverse of 2 × 2 matrix, you will need to select 2 × 2 cells.

2. Enter the formula, e.g. =MINVERSE (A1:B2)… 3. and press c + s + / to apply it to all selected cells simultaneously.

Note that the formula for each of the cells has subtly changed. It now reads ={ MINVERSE (A1:B2) }, to indicate that it is valid for an entire array of cells. mdeterm() – Determinant of a matrix Returns the determinant of a square matrix. (This function should not be entered as an array function.) MDETERM (array of cells) where: · array of cells is reference to a coherent block of cells with an equal amount of rows and columns (there are no determinants for non-square matrices) Examples: =MDETERM(A1:B2) will return the inverse of this 2 × 2 matrix =MDETERM(A1:B3) will return a #VALUE! error because this is not a square matrix Remark: if any cells in the matrix are empty or contain text, mdeterm() will return a #VALUE! error. – minverse() – Inverse of a matrix Returns the inverse of a square matrix. This function needs to be entered as an array function! The resulting matrix will be of the same size as the original matrix.

110 Introduction to Microsoft Office ●●●●●●●●●●●●

MINVERSE (array of cells) where: · array of cells is reference to a coherent block of cells with an equal amount of rows and columns (non-square matrices cannot be inverted) Examples: =MINVERSE(A1:B2) will return the inverse of this 2 × 2 matrix =MINVERSE(A1:B3) will return a #VALUE! error because this is not a square matrix Remarks: · If any cells in the matrix are empty or contain text, minverse() will return a #VALUE! error. · Not all square matrices can be inverted. minverse() returns a #NUM! error if a matrix does not have an inverse. – mmult() – Matrix multiplication Returns the product of a matrix multiplication of a k × m and an m × n matrix. This function needs to be entered as an array function! The resulting matrix will be of the order k × n. MINVERSE (first array, second array) where: · first array is reference to a coherent block of cells of any size, i.e. a matrix of the order k × m, where k and m can be freely chosen · second array is reference to a coherent block of cells that has as many rows as there are columns in the first array, i.e. a matrix of the order m × n, where n can be freely chosen but m is determined by the first array Examples: =MMULT(A1:B3,G17:I18) will return a 3 × 3 matrix =MMULT(A1:C3,G17:I18) will return a #VALUE! error, because the number of columns in array one (3) is unequal to the number of rows in array two (2) Remark: if any cells in the matrix are empty or contain text, mmult() will return a #VALUE! error. – transpose() – Transposes a matrix Returns the transpose of a matrix, i.e. a matrix whose rows have been converted into columns. This function needs to be entered as an array function! If the original matrix is of the order m × n, then the resulting matrix will be of the order n × m.

Appendix: A Selection of Useful Excel Functions 111 ●●●●●●●●●●●●

TRANSPOSE (array of cells) where: · array of cells is reference to a coherent block of cells Example: =TRANSPOSE(A1:C2) will return a 3 × 2 matrix Remark: there are no restrictions regarding the content of the cells. Transpose also works with text or empty cells. pi() – p Returns p (pi), 3.141…, accurate to 15 digits. PI () No parameters are required. round() – Rounding Rounds a number to a specified amount of digits after the decimal point. ROUND (number, amount of digits) where: · number is the number that you want to round · amount of digits is the amount of digits after the decimal point to which you want to round (entering negative numbers will cause Excel to round before the decimal point) Examples: =ROUND(1.234, 0) will return 1 =ROUND(1.234, 1) will return 1.2 =ROUND(42.234, -1) will return 40 sqrt() – Square root Returns the square root of a number. SQRT (number) where: · number is any positive number Examples: =SQRT(4) will return 2 =SQRT(-4) will return a #NUM! error, because the square root of a negative number is not defined

112 Introduction to Microsoft Office ●●●●●●●●●●●●

sum() – Sums Adds the contents of cells. SUM (number 1, number 2, …) where: · number 1 is a number or a reference to a (group of) cells o you can include additional cell references or numbers up to a total of 30 (optional) Examples: =SUM(1,2,3) will return 6 =SUM(A1:A200) will add the contents of all 200 cells and return the total =SUM(A1:B3,6,C7) will add the contents of the number 6 to the contents of the cells A1:B3 and C7 Remark: empty cells or cells that contain text, logical values or errors will be ignored. trunc() – Truncation Truncates a number with a specified precision. TRUNC (number, amount of digits) where: · number is the number that you want to truncate o amount of digits is the number of digits after the decimal point that you want to preserve (optional, if left empty, ‘0’ will be used; entering a negative value for ‘amount of digits’ will cut of digits in front of the decimal point) Examples: =TRUNC(18.956) returns 18 =TRUNC(18.956,1) returns 18.9 =TRUNC(18.956,-1) returns 10

Statistical functions average() – Mean Returns the arithmetic mean of the specified cells or numbers. AVERAGE (number 1, number 2, …) where: · number 1 is a number or a reference to a (group of) cells o you can include additional cell references or numbers up to a total of 30 (optional)

Appendix: A Selection of Useful Excel Functions 113 ●●●●●●●●●●●●

Examples: =AVERAGE(1,2,3) will return 2 =AVERAGE(A1:A200) will return the average of all 200 cells Remark: empty cells or cells that contain text, logical values or errors will be ignored. A cell that contains a ‘0’ will therefore affect the mean, an empty cell will not. binomdist() – Binomial distribution Returns the binomial probably of k successes in n trials, when the probability of a success is p. BINOMDIST (k, n, p, cumulative) where: · k is the amount of success, a natural number between 0 and n · n is the total amount of trials, a positive natural number · p is the probability for success as a percentage or decimal value · cumulative is a logical value that determines whether the probability mass function or cumulative probability is returned Examples: =BINOMDIST(1,2,50%,false) returns 0.5 =BINOMDIST(1,2,0.5,false) returns 0.5 =BINOMDIST(1,2,0.5,true) returns 0.75 =BINOMDIST(2,1,0.5,true) returns a #NUM! error because there cannot be more successes than there are trials =BINOMDIST(1,2,1.1,true) returns a #NUM! error because probabilities cannot be smaller than 0 (0%) or larger than 1 (100%) critbinom() – Binomial distribution Determines the amount of successes for which the cumulative probability equals or supersedes a specified critical value. CRITBINOM (n, p, critical value) where: · n is the total amount of trials, a positive natural number · p is the probability for success as a percentage or decimal value · critical value is the percentile that you want to determine Example: =CRITBINOM(10,0.5,0.75) will return 6, because the cumulative probability for 6 successes is past the 75% threshold (82.8%), while the cum. probability for 5 successes remained below the critical value (62.3%)

114 Introduction to Microsoft Office ●●●●●●●●●●●●

0.3

> 75%

0.25

0.2

0.15

0.1

0.05

0 0

1

2

3

4

5

6

7

8

9

10

k

count() – Number of elements Counts the amount of numbers in a list. COUNT (number 1, number 2, …) where: · number 1 is a number or a reference to a (group of) cells · you can include additional cell references or numbers up to a total of 30 (optional) Examples: =COUNT(1,2,3) will return 3 =COUNT(1,"A",3) will return 2 =COUNT(A1:A200) will return the amount of cells between A1 and A200 that contain a numerical value Remark: empty cells or cells that contain text, logical values or errors will be ignored. max() – Largest element Finds and returns the largest element in a list of numbers. MAX (number 1, number 2, …) where: · number 1 is a number or a reference to a (group of) cells o you can include additional cell references or numbers up to a total of 30 (optional) Examples: =MAX(1,2,3) will return 3 =MAX(A1:A200) will search the cells between A1 and A200 and return the largest value =MAX(A1:A200, 10) will return 10 or the largest value found between A1 and A200, whichever is larger

Appendix: A Selection of Useful Excel Functions 115 ●●●●●●●●●●●●

Remark: empty cells or cells that contain text, logical values or errors will be ignored. median() – Median Returns the median of a list of numbers, i.e. it returns the middle value. MEDIAN (number 1, number 2, …) where: · number 1 is a number or a reference to a (group of) cells; the numbers do not need to be orders according to value o you can include additional cell references or numbers up to a total of 30 (optional) Examples: =MEDIAN(1,2,3) will return 2 =MEDIAN(1,2,3,4) will return 2.5 (the average of 2 and 3) =MEDIAN(1,2,3,5) will return 2.5 (the average of 2 and 3) =MEDIAN(2,5,1,3) will return 2.5 (the average of 2 and 3) Remark: empty cells or cells that contain text, logical values or errors will be ignored. A cell that contains a ‘0’ will therefore affect the median, an empty cell will not. min() – Smallest element Finds and returns the smallest element from a list of numbers. MIN (number 1, number 2, …) where: · number 1 is a number or a reference to a (group of) cells o you can include additional cell references or numbers up to a total of 30 (optional) Examples: =MIN(1,2,3) will return 1 =MIN(A1:A200) will search the cells between A1 and A200 and return the smallest value =MIN(A1:A200, 5) will return 5 or the smallest value found between A1 and A200, whichever is smaller Remark: empty cells or cells that contain text, logical values or errors will be ignored.

116 Introduction to Microsoft Office ●●●●●●●●●●●●

normdist() – Normal distribution Returns the (cumulative) probability for a point under a normal distribution with a given mean and standard deviation. NORMDIST (x, mean, standard deviation, cumulative) where: · x is the value for which you want the probability · mean is the mean of the normal distribution · standard deviation is the standard deviation of the normal distribution · cumulative is a logical value that determines whether the cumulative probability is returned (for most intents and purposes, this value will be set to true) Examples: =NORMDIST(75,80,5,TRUE) will return 0.159 =NORMDIST(A1,100,25,TRUE) will return the cumulative probability under the normal distribution with m = 100 and s = 25 on the interval [–∞, A1] (see graph)

normsdist() –Standard normal distribution Works like normdist(), but uses the standard normal distribution. normsdist() returns only the cumulative probability. NORMDIST (z) where: · z is the value for which you want the probability Example: =NORMSDIST(0) will return 0.50 norminv() – Normal distribution Returns the point x under a normal distribution with a given mean and standard deviation for which the interval [–∞, x] yields a given probability. NORMINV (probability, mean, standard deviation) where:

Appendix: A Selection of Useful Excel Functions 117 ●●●●●●●●●●●●

· · ·

probability is the cumulative probability under the normal distribution on the interval [–∞, x] mean is the mean of the normal distribution standard deviation is the standard deviation of the normal distribution

Examples: =NORMINV(0.5,100,10) will return 100 =NORMINV(40%,100,25) will return the point x indicated in the graph below

normsinv() –Standard normal distribution Works like norminv(), but uses the standard normal distribution. NORMSINV (probability) where: · probability is the cumulative probability under the standard normal distribution on the interval [–∞, z] Example: =NORMSINV(50%) will return 0 percentile() – Percentiles Returns the specified percentile. PERCENTILE (array of cells, percentile) where: · array of cells is reference to a coherent block of cells · percentile is the percentile as a decimal value between 0 and 1 Examples: =PERCENTILE(A1:A200,0.1) will return the 10th percentile =PERCENTILE(A1:A200,1.1) will return a #NUM! error because there is no 110th percentile Remarks: · if the array contains less than 1 or more than 8,191 elements, percentile() will return a #NUM! error · empty cells or cells that contain text, logical values or errors will

118 Introduction to Microsoft Office ●●●●●●●●●●●●

· · ·

be ignored percentile(A1:A200,0) is equivalent to min(A1:A200) percentile(A1:A200,0.5) is equivalent to median(A1:A200) percentile(A1:A200,1) is equivalent to max(A1:A200)

quartile() – Quartiles Returns a specified quartile. QUARTILE (array of cells, QUARTILE) where: · array of cells is reference to a coherent block of cells · quartile determines, which quartile will be returned: § 0 – the minimum value § 1 – the lower quartile § 2 – the median § 3 – the upper quartile § 4 – the maximum value Examples: =PERCENTILE(A1:A200,1) will return the lower quartile =PERCENTILE(A1:A200,6) will return a #NUM! error because the second value needs to be a natural number between 0 and 4 Remarks: · if the array contains less than 1 or more than 8,191 elements, quartile() will return a #NUM! error · empty cells or cells that contain text, logical values or errors will be ignored · quartile(A1:A200,0) is equivalent to min(A1:A200) · quartile(A1:A200,2) is equivalent to median(A1:A200) · quartile(A1:A200,4) is equivalent to max(A1:A200) stdev() – Standard deviation Calculates the standard deviation of a sample. STDEV (number 1, number 2, …) where: · number 1 is a number or a reference to a (group of) cells o you can include additional cell references or numbers up to a total of 30 (optional) Example: =STDEV(A1:A10) will return the standard deviation of the values contained in the cells A1 through A10.

Appendix: A Selection of Useful Excel Functions 119 ●●●●●●●●●●●●

tdist() – Student t-distribution Returns the one- or two-tailed cumulative probability for a point under a T-distribution with given degrees of freedom. TDIST (x, d.f., tails) where: · x is the value for which you want the probability · d.f. indicates the degrees of freedom of the distribution · tails indicates whether you want the one- or two-tailed probability Examples: =TDIST(1,200,1) will return 0.159 (see graph)

=TDIST(1,200,2) is equivalent to =2*TDIST(1,200,1)

Remark: when d.f. < 1 or when tails does not equal 1 or 2, tdist() will return a #NUM! error. tinv() – Student t-distribution Returns the point x which cuts off a given probability in both tails of a student T-distribution with given degrees of freedom. TINV (probability, d.f.) where: · probability is the total cumulative probability that should be left in both tails (e.g. 5% in total implies 2.5% in each tail, see graph) · d.f. indicates the degrees of freedom of the distribution

120 Introduction to Microsoft Office ●●●●●●●●●●●●

Example: =TINV(5%,200) will return 1.972 (see graph)

var() – Variance Calculates the variance of a sample. VAR (number 1, number 2, …) where: · number 1 is a number or a reference to a (group of) cells o you can include additional cell references or numbers up to a total of 30 (optional) Example: =VAR(A1:A10) will return the standard deviation of the values contained in the cells A1 through A10.

Logical functions Logical statement A comparison of two pieces of information that is either correct or incorrect, e.g. 0 = 1 is such a statement, and it is incorrect.

Logical functions allow a computer to deal with logical statements. A logical statement is a statement that compares one bit of information with another to check whether they fulfill a certain condition. For instance, A2 = 2 is such a statement. Here, the two bits of information are the cell ‘A2’ and the number ‘2’, and the condition is that both bits of information must be equal to each other. Another statement would be A2 > 2. In this case, the condition is that the cell ‘A2’ must contain a value that is larger than 2. Excel will try to determine whether a condition has been met or not. In our latter example, A2 > 2, the condition would be met e.g. if the cell ‘A2’ contains the value ‘3’, because 3 > 2. In that case, we say that a statement is true or correct. If, however, the cell ‘A2’ e.g. contained the value ‘1’, then the condition would not be met. We would say that the statement is false or incorrect. The decision whether a statement is true or false is the result of such a logical statement. Just as =4+3 will return 7, =1>0 will return true. This ‘response’ can then be used in logical functions e.g. for making decisions. The following logical functions are all based on logical statements.

Appendix: A Selection of Useful Excel Functions 121 ●●●●●●●●●●●●

and() This function will return true if all of the given conditions are true. AND (condition 1, condition 2, …) where: · condition 1 is a logical statement o you can include additional logical statements up to a total of 30 (optional) Examples: =AND(1=1, 2<3) will return true because both statements are true =AND(1=1, 2>3) will return false because at least one statement is false (it does not matter how many statements are false) false() Returns false. FALSE () or FALSE No parameters are required. Remark: the brackets can be omitted. if() Evaluates a statement and commits an action in response. IF (condition, response if true, response if false) where: · condition is any logical condition · response if true is the action committed when the logical condition is true o response if false is the action committed when the logical condition is false (optional) Examples: =IF(A1>A2,A1-A2,A2-A1) will deduct A2 from A1 if A1 is bigger than A2 (and vice versa) =IF(0=1,"Your PC is drunk!","Everything is fine.") will always return “Everything is fine”, because 0 is never equal to 1 (unless your PC is drunk) Remarks: · you can refine the condition using the and(), or() and not() functions · you can include up to 7 further if() functions in the responses

122 Introduction to Microsoft Office ●●●●●●●●●●●●

not() Reverses the condition, i.e. returns true for a condition that is false and false for a condition that is true. NOT (condition) where: · condition is a logical statement Examples: =NOT(2<3) will return false, because the original statement is true =NOT(1=0) will return true, because the original statement is false or() This function will return true if at least one of the given conditions is true. OR (condition 1, condition 2, …) where: · condition 1 is a logical statement o you can include additional logical statements up to a total of 30 (optional) Examples: =OR(1=1, 2>3) will return true because at least one statement is true (it does not matter how many statements are true) =OR(1=0, 2>3) will return false because all statements are false true() Returns true. TRUE () or TRUE No parameters are required. Remarks: the brackets can be omitted. ●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●●