Microsoft Office/Printable version

Microsoft Office

The current, editable version of this book is available in Wikibooks, the open-content textbooks collection, at
https://en.wikibooks.org/wiki/Microsoft_Office

Permission is granted to copy, distribute, and/or modify this document under the terms of the Creative Commons Attribution-ShareAlike 3.0 License.

Windows Version Differences

You might be using Microsoft Office on either Windows 7 or Windows 8 (or possibly Windows Vista or even Windows XP). This book also covers using Microsoft Office on the Mac (specifically, on OS X version 10.9, often referred to as “Mavericks”). Unfortunately, many techniques differ greatly between those operating systems. OS X looks and works nothing like Windows. However, Windows 7 and Windows 8 (or Windows 8.1, which looks and works very much like Windows 8) are very, very different, so much so that this book will sometimes discuss them if they are different operating systems.

In this book, information and instructions that are specific to one operating system will be placed in a subpage named with the appropriate operating system. Pages without an operating system in the name contain only information that applies to all platforms. For example, the page Logging In & Out contains a conceptual discussion of logging in and out, while the pages Logging In & Out (Windows) and Logging In & Out (OS X) contain step-by-step directions specific to Windows and OS X, respectively.

Finally, sometimes step-by-step instructions for Windows 7, Windows 8, and Windows 8.1 are so different that they have separate subpages. Other times, the differences are so minor that the different versions of Windows will be discussed together. In the latter case, look for the boxes at the beginning of each section that say “This section applies only to version 7 of Windows” (or whatever version is being discussed).

But I Don’t Know Which Version I’m Using!

If you don’t know which version of Windows you’re using, there is a simple way to determine. Open the Run dialog (to do so, press the Windows key and R at the same time) and type winver.exe, then press Enter. This command opens a dialog box that tells you the version of Windows you are using.

Logging In & Out

Modern computer operating systems support a “multiple account” system, wherein each person using the computer may (but does not necessarily) have their own user account. Having multiple user accounts helps to separate the files and settings of different people who use a computer, while helping to shield it from malicious or inadvertent settings changes.

Every Mac and Windows PC has at least one computer account; some have quite a few more. You are required to create a user account during the initial setup tasks that run the first time you turn on a brand-new computer. Any additional accounts must be manually created following the completion of the initial setup. While discussion of how to create and manage user accounts is outside the scope of this book, knowing how to effectively utilize a computer set up to use multiple accounts is an important part of being a productive employee in today’s modern workplace.

For platform-specific information on how to use user accounts, see the Windows subpage and the Mac subpage. This page discusses conceptual, cross-platform user account topics only. Any Mac- or Windows-specific instructions should go on one of those two pages.

What Is An Account?

Normally, user accounts can only make changes that affect their account only. However, some accounts (called administrator accounts) can modify settings that affect every user on this computer, or even an entire network of computers. If you are the owner of a particular computer, or if you are the one who initially set it up, you most likely have an administrator account. Unless you know otherwise, always assume that your account is not an administrator account. On school or work networks, administrator accounts are usually only given to members of the organization’s IT department.

Accounts are identified by a name. Usually this is a friendly name, like “Janice Smith”, but sometimes it is something more cryptic. For example, while your name might be Janice Smith, you might have to use the name “SmithJ” to log in. So that someone else can’t use your account without your permission, accounts are protected with a password. A password is a secret string of letters, numbers, spaces, and symbols that only you know. If someone else can guess your password, that person can break into your account. User accounts on modern versions of Windows and OS X also have a user picture, but in large networked environments (schools and workplaces), you generally aren’t allowed to choose your own user picture — if you even get one at all.

Logging In

Before you can use a computer set up to use multiple accounts, you must tell the computer who you are (so that you see your files and settings and not someone else’s). This is called logging in. If you walk up to a computer and see someone else’s desktop, the last person to use that computer forgot to log out when they were done. Never use somebody else’s account. This could land both you and whoever owns the account you’re using in deep trouble. Instead, log out first so that their files and settings are secure. Before you can log in, you must know your username and password. If you don’t know either of these facts, consult whoever set up the computer or network you’re trying to use (the computer or network administrator).

Logging Out

When you’re finished using a computer, you should log out before you leave. This ensures that passersby cannot use your computer account without your consent. (As noted above, unauthorized account use can have disastrous consequences in a school or work setting.) When you log out, the computer will close all programs and files that are open at the time. If you have any unsaved files open, you will be prompted to save or discard your changes before the process is complete.

Locking the Screen

If you are leaving your computer unattended for a few minutes (if you need to use the restroom, say), you should not just leave your computer logged in. If you do so, you run the risk of having your user account hijacked, your private files being read, or worse. But what if you don’t want to close everything out just because you’ll be away from your computer for a moment, and then reopen everything when you return? Screen locking was invented for just this very circumstance. When you lock your screen, all your programs remain open, but are hidden. To reshow the desktop, you must enter the password of the user who locked their screen. (The password prompt will state who is currently logged in.) If you can’t provide the correct password, you won’t be given access to the desktop. Screen locking is supported on modern versions of Windows and Mac OS X.

Please be courteous while locking your screen. If you are going to be away from the computer for more than a few minutes, and it is possible that someone else would want to use the computer while you’re gone, please log out fully instead of just locking your screen. (This is especially important in a school setting, where a large number of users share a small number of computers.) If the screen is locked, but the person who locked it is nowhere around, and you need to use the computer, you must force the computer to shut down (usually by holding down the computer’s Power button), and then restart it. If you do so, whatever the previous user currently had open would be gone forever; changes will not have been saved.

Due to a quirk of the operating system, locking a Mac’s screen and putting the Mac to sleep (a special mode which conserves electricity) are one and the same process. Unfortunately, simply putting a Mac to sleep does not guarantee that you will be asked for your password upon waking it. If you wake the computer and your desktop appears immediately, consult your system or network administrator on the proper steps to take. If are working with sensitive information, please test to make sure that you will be prompted for your password before relying on this feature to protect your files while your computer is unattended. (However, you will always be prompted for your password after locking a Windows-based PC.)

Things to Know When Saving

There are a menagerie of ways you can save a file in Microsoft Office, each with their own features and limitations. However, on a default windows installation, you might not be able to necessarily see the difference. This necessitates the enabling of file extensions, which represent the different types of file formats a Word, Excel, or PowerPoint document can be saved as. Think of these as a form of language your computer's word processor reads to spit out English back to you.

What's a file extension?

Every computer has a menagerie of programs, like Word, PowerPoint, and Excel, that read documents, which are also known as files. To prevent accidental re-writing of files intended for different programs, file extensions are used to distinguish between files that can be read, and files that should be avoided whenever a program needs to read a file.

A quick summary

Text

• .doc - The old Microsoft Word file format.
• .docx - The new Microsoft Word file format. Saves by default.
• .docm - The new Microsoft Word file format with macros enabled. Dangerous.
• .odt - The OpenDocument format, used by OpenOffice, and its successor, LibreOffice.

Slideshows

• .ppt - The old Microsoft PowerPoint file format.
• .pptx - The new Microsoft PowerPoint file format. Saves by default.
• .pptm - The new Microsoft PowerPoint file format with macros enabled. Dangerous.
• .pps - The old Microsoft PowerPoint file format with automatic slideshows.
• .ppsx - The new Microsoft PowerPoint file format with automatic slideshows.

Spreadsheets

• .xls - The old Microsoft Excel file format.
• .xlsx - The new Microsoft Excel file format. Saves by default.
• .xlsm - The new Microsoft Excel file format with macros enabled. Dangerous.
• .xlsb - The Excel binary format. More information to come...

Macros

In the past, it used to be a roll of the die whether a document received had viruses or not. Starting with Office 2000, Microsoft restricted running macros by default, but they were still easily enabled. With the release of Office 2007, Microsoft further locked down macro access; the new .docx, .pptx, and .xlsx files do not support macros. Instead, the corresponding macro-enabled OOXML-transitional Office document files now end in .docm, .pptm, and .xlsm.

But wait: What's OOXML? For that, we have to walk through the history of the standardization of word processor documents.

A brief history

For the sake of simplicity, we won't be covering obsolete, or uncommon file formats like those from WordPerfect or Lotus. However, Microsoft Word and Excel should be able to read and convert most of these files. No promises that there won't be errors though; these old, proprietary formats are not standardized whatsoever.

XML extensions for Office 2003

Prior to Office 2003, the only way to save a document with readable markup would have been to save the document as an HTML file. However, since HTML was mainly designed for the web, this was a huge annoyance to book writers, who wanted an easy way to change their book's markup, via Microsoft Word, without having to write the whole book in raw XML markup.

Enter Office 2003. Microsoft introduced the readable, albeit proprietary, WordML and SpreadsheetML to the .doc and .xls file formats of the day. This replaced the unreadable, proprietary .doc and .xls file format from Office 2000 and prior.

OASIS OpenDocument

Sun Microsystems had purchased a relatively obscure alternative Office suite called StarOffice. However, in an effort to stay competitive with Microsoft Office, Sun Microsystems opened up the StarOffice formats for use by everyone. Hence the rise of .odt and the standardization of OpenDoucment as an ISO format.

Currently, the most up-to-date Office suite using OpenDocument as its primary file format is LibreOffice.

OOXML Office 2007 "transitional"

Back to Office 2003. So Microsoft had this XML format that they shoehorned in to the .doc and .xls format. Now that OpenDocument was competing with Microsoft to become the world's standard document format, Microsoft responded with "Office Open XML", also known as "OOXML".

However, since Microsoft had just introduced XML support to Office 2003, they had to find some way to allow WordML and SpreadsheetML users to use the new OOXML format. Instead of leaving out Office 2003 users, Microsoft integrated WordML and SpreadsheetML in Office 2007's new Word and Excel file formats (.docx, .xlsx), along with parts of OOXML, creating a new file format, technically incompatible with the official ISO spec. This spec is called OOXML "Transitional" (ISO/IEC 29500 Transitional).

However, due to the fact that Microsoft was unable to release a version of Office compatible with the official ISO spec, to the disapproval of governments involved in the ISO, Microsoft added OpenDocument support with the release of Service Pack 2 for Office 2007.

OOXML Office 2013 "strict"

Office 2013 was the first version of Office to support the official ISO OOXML specification, here called "OOXML-strict". However, due to the proliferation of "OOXML-transitional" documents, as well as the need to maintain comparability with Office 2007 and Office 2010, it isn't chosen as the default file format whenever an Office document is saved.

Too complicated?

Just follow this checklist for what to ask before sending a document. If something doesn't apply to you, cross it out. Stop when a point applies to you.

• If your recipients use OpenOffice: Use OpenDocument. (.odt). OpenOffice does not support Office Open very well at all. If you can't afford Microsoft Office, consider moving to LibreOffice.
• If your recipients use LibreOffice: Use OpenDocument (.odt) or Office Open (.docx). Both will work, but OpenDocument is definitely preferred.
• If your recipients use an old version of Office (from 2003 and prior): You must use classic Office (.doc)
• If your recipients use a Mac with Office 2013 (or later) installed: Use Office Open "Strict". You may have to hunt for this in the drop-down menu, as it has the same format (.docx). However, choosing "strict" may prevent minor formatting errors when communicating between PCs and Macs.
• If your recipients use a Mac: Use Office Open (.docx). You may experience some formatting errors, so beware.
• If your recipients use Office 2007-2010: You cannot use Office Open "Strict". Cross out all other "strict" points left in this checklist. Then continue.

For most other cases

• Using Office Open (.docx) or
• Office Open "strict" (also .docx) should be fine.

More information

• Microsoft article on XML support in Office 2003

Wikipedia articles

• Office Open XML
• OpenDocument

Create and Edit a Document

A header is the top margin of each page, and a footer is the bottom margin of each page. Headers and footers are useful for including material that you want to appear on every page of a document such as your name, the title of the document, or page numbers.

Starting Microsoft Word

To open Microsoft Word: First click on your "Start" button. Next click on All Programs. You will probably need to scroll to find the Microsoft Office program menu. When you do click on Microsoft Word this will start the program. Note: Microsoft Word 2016 is the most recent perpetual version released.

(Since Office 2016, the apps do not lie in the Microsoft Office folder; to open Word, you'll have to look at 'W'. In Windows 7, this is before the listing of folders.)

Alternatively, look on your Desktop for an icon shortcut to Word, click this (or if this doesn't work, double-click).

Basic Rules for formatting text

To change the formatting of text, highlight the text you wish to change and then choose your formatting options. You may want to increase the font size, change the font color, change the font design. To change the formatting, including the font size, font, color, and many other properties.

• right click on the selection and choose what you'd like to change
• use the toolbar (1997 - 2003 versions) or
• Select the options on the Format Menu to change

Word 2007 and later uses tabs and ribbons to interface (interact) with users. To change text formats, be sure you have clicked the Home tab and are using the options found in the Font grouping.

If you change the formatting options without any text being selected, then these options will apply to any further text entered after that point in the document.

Parts of Word Screen

Parts of the Word Screen

Parts	Function
Title Bar	Displays the program name, and the name of current document displayed or being edited.
Menu Bar	Contains the names of the command menus currently available.
Standard Toolbar	Contain buttons that provide shortcuts for the most commonly used Word Features, such as opening and printing document.
Formatting Toolbar	Contains buttons and drop-down lists that you use to modify the appearance of selected text.
Ruler	It shows the width of your text, as well as any indents or tabs.
Document Window	Displays the documents you create and edit.
Insertion Point	It is blinking vertical line at the upper left corner of a new document.
Endmark	It indicates the end of the document.
Scroll Bars	It is one of the main ways to navigate through a document. Appear along the right side and bottom edges of the document window. Each scroll bar contains two scroll arrows and scroll box, which you can use to move vertically and horizontally through a document.
Status Bar	Displays the necessary information about the active document such as page.
Minimize Button	It is used to temporary hide word (to shrink it to an icon on the taskbar).
Maximize Button	It is used to enlarge MS-Word to full screen.
Close Button	It is used to exit or quit MS-Word.

Entering Text

When you start MS-Word for Windows, you see a blank area. (This blank area is called a Normal Template). To work effectively with Word, you must understand that every Word Document is based on template.

1. The Blinking Vertical Line that marks the insertion point, the location where text you type will be inserted into the document and where certain editing actions will occur.
2. The horizontal line is the end-of-document marker.

There are a number of different views available for working on the text, available on the View menu. The simplest is the 'Normal' view, while the most complex view, but the best for working on heavily formatted pages is the 'Print layout' view.

Menu Structure

When you work with MS-Word for Windows, you give it commands to instruct the program to carry out the desired tasks. Commands are usually entered by means of menus. MS-Word for Windows has three types of menus:

1. The Main Menu is displayed in the menu bar, on the second line of the screen.
2. A Pull-down Menu is a list of commands associated with each choice on the main menu. When you choose a command on the main menu, its pull-down menu is displayed.
3. A Context Sensitive Menu is pop-up screen when you right-click the mouse. Wherein it shows a list of commands to work with.

Dialog Boxes

When MS-Word for Windows needs additional information to carry out a command, it displays a dialog box. Dialog boxes contain a number of components, but mainly contain only some of these components.

Components of Dialog Box

1. A dotted outline or highlight bar indicate the current dialog box item.
2. The title bar gives the name of the dialog box, which is the command used to display the dialog box.
3. A text box is used to enter and edit text information.
4. A list box displays a list of items from which you can choose. If the list is too big to display at one time, a vertical scroll bar lets you scroll up or down the list.
5. The command buttons to either confirm or cancel the dialog box.

Working With A Document

MS-Word is used by people with widely varying levels of skill and experience, and there are often several different ways to do some tasks. Most commands are available through the Menu and sub menus. For frequently used commands, there are often iconic shortcut buttons on the Tool bars that can be selected with the mouse. For people with better keyboard skills or experience, there are keyboard shortcuts, often using the Control (CTRL), Shift (SHFT) or Alternate (ALT) keys on the PC and Command or Option keys of the Macintosh.

Create a New Document

• Choose File ➪ New from the menu bar.
  • This may ask you to select which document template to use for the new document.
• Click the New Blank Document button of the tool bar.
• Press CTRL + N on the keyboard. (Depress and hold CTRL, Press and release 'N')

Open an Existing Document

• Choose File ➪ Open from the menu bar.
• Click the Open button on the toolbar.
• Press CTRL + O on the keyboard.

NOTE: Each method will show the Open dialog box. Select the drive the file was saved on, choose the file, and click the Open button.

Save a Document

• Select File ➪ Save from the menu bar.
• Click the Save button on the toolbar.
• Press CTRL + S on the keyboard.

Navigate to the location where you would like to save the document. Make a note of the drive where the document is saved for future reference. To save an existing open document under a different name, select 'File ➪ Save As'.

Renaming a Document

To rename an existing, but not open, Word document while using the program,

• Select File ➪ Open (or press CTRL + O on the keyboard) and find the file you want to rename.
• Right-click on the document name with the mouse and select Rename from the shortcut menu.
• Type the new name for the file and press the ENTER key.

Working with Multiple Documents

Several documents can be opened simultaneously if you are typing or editing multiple documents at once. All open documents are listed under the Windows menu. The current document has checkmark beside the filename. Select another name to view another open document or click the button on the Windows taskbar at the bottom of the screen.

Closing a Document

• Selecting File ➪ Close from the menu bar
• Click the close window icon if it’s visible on the menu bar
• Press CTRL + W or CTRL + F4 on the keyboard.

Exiting Word

• Choose the File ➪ Exit on the menu bar.
• Click the Close button align with the title bar.
• Press ALT + F4 on the Keyboard.

Spacing and Special Characters

Spacing

MS Word has many options for formatting spacing, the most commonly used are for selecting spacing between lines, and the spacing between paragraphs. These options are both on the 'Format → Paragraph' dialog. As you might expect, the 'Line spacing' option sets that spacing between adjacent lines, while the 'Before / After' Spacing sets the space between different paragraphs.

If you have text that belongs on a new line, but is still part of the same paragraph, then you can use 'SHIFT-ENTER' to insert a newline while remaining in the same paragraph.

Special Symbols

Special symbols (Greek letters, typographic marks, small icons or pictures), can be inserted by using the Menus 'Insert → Symbol...'. Most commonly used symbols are included in the main fonts, while commonly used icons are in the 'Windings' or 'Webdings' fonts.

Locate the symbol you wish to insert and click 'Insert' to place it into your document at the current cursor position (also known as the 'insertion point').

Create and Edit a Flier

Vocabulary

• Mouse Pointer
• Scroll Bar
• Status Bar
• Ribbon
• Home Tab
• Active Tab
• Groups
• Gallery
• Live Preview
• Screen tip
• Dialog Box Launcher
• Task pane
• Mini Toolbar
• Shortcut menu
• Quick Access Toolbar
• Office Button
• Menu
• Submenu
• Key Tip Badge
• Formatting marks
• Wordwrap
• File
• Folder
• File name
• Font
• Style
• Theme
• Underline
• Italicize
• Quick Style
• Color Scheme
• Font Set
• Picture Style
• Resizing
• Page Boarder
• Document Properties
• Keywords
• Hard Copy

Word Document Window

SCROLL BAR - Bar along the side or bottom of your window that allows you to display different portions of a document.

STATUS BAR - Bar at the bottom of the Word window that displays number of pages, words, and other document information.

RIBBON - At the top of the Word Window and proves easy central access to the tasks you perform.

HOME TAB - The primary tab that contains the most frequently used commands.

ACTIVE TAB - The tab that you are currently on.

GROUPS - A set of commands that are all related.

GALLERY - A set of graphic choices that display in a grid, like the picture styles.

LIVE PREVIEW - When you hover over a command it it shows what the change would look like.

SCREEN TIP - Note that appears and gives the name of the command.

DIALOG BOX LAUNCHER - A small arrow in the bottom right corner of a group that displays more options in a separate window.

TASK PANE - A window containing controls that can remain open while you work on your document. (like the "find and replace" box)

MINI TOOLBAR - Automatically appears based on the task you are performing.

SHORTCUT MENU - Menu displayed when you right click an object.

QUICK ACCESS TOOLBAR - Above the ribbon and contains save, undo, redo commands

OFFICE BUTTON - In the upper-left of the Word window, it displays the document management commands.

MENU - Anything you can click on that will drop down a list of commands.

SUBMENU - Menu commands with a triangle next to them that list additional commands.

KEY TIP BADGE - Appears by pressing the ALT key, gives the code you press with ALT to access it.

FORMATTING MARKS - Character that does not print, but shows what keys have been used (ENTER, TAB).

WORDWRAP - Type characters in a paragraph continually and the automatically flow to the next line.

Saving a Document

FILE -A saved document

FOLDER - A place in storage to save an organize your documents.

FILE NAME - What you called a document when you save it.

File names can not contain:

• more than 260 characters
• /
• \
• :
• *
• "
• <
• >
• |

Formatting a Document

FONT - Appearance and shape of letters and numbers.

STYLE -Grouping of fonts, sizes, and spacing.

THEME - A set of unified formats for fonts, colors and graphics.

UNDERLINE - An emphasis placing an underscore below each character.

ITALICIZE - An emphasis causing a slanted appearance.

BOLD - An emphasis displaying characters thicker and darker.

QUICK STYLE -Predefined formatting or style set, such as: Normal, No Spacing, and Heading 1.

COLOR SCHEME - A set of 12 complementary colors used for text, background, accents and links.

FONT SET - Defines formats for two sets of text: headings, and body.

Creating a Bulleted List

A bullet is a dot or other character (or picture) that is at the beginning of a paragraph used to highlight something of importance.

Working With Graphics

PICTURE STYLE - Formatting to make more visually appealing.

RESIZING - To make bigger or smaller.

Creating Page Borders

PAGE BORDER - Framing the contents of a document to give it a finished look.

Setting Document Properties

DOCUMENT PROPERTIES - metadata - details about a file: author, title, subject, when created, when modified, ...

KEYWORDS - Words or phrases that further describe the document.

HARD COPY - The printed out document.

MLA format

Vocabulary

• research paper
• MLA
• APA
• Parenthetical citations
• Works cited
• Line spacing
• Paragraph spacing
• Double-space
• Header
• Footer
• Alignment
• Shortcut keys
• First-line indent
• Ruler
• First line indent marker
• Left indent marker
• AutoCorrect
• Note reference mark
• Footnote
• Endnote
• Note text
• Automatic Page Breaks
• Soft page breaks
• Background repagination
• Manual page break
• Hard page break
• Bibliographical list
• Hanging indent
• Hanging indent marker
• Proofreading
• Clipboard

Describe the MLA formatting for a research paper

MLA stands for MODERN LANGUAGE ASSOCIATION

MLA contains:

• Header containing the authors last name followed by the page number
• Double spaced
• One inch boarders on all sides
• Indent the first word of each paragraph 1/2 inch
• Page one has a block for the paper information at the left margin one ince from the top of the page. It includes:
  • Your Name
  • Your Instructor
  • The course name
  • The date
• Title centered one double space below the paper information
• Author references in parentheses with the page number in the text were the information is given is called PARENTHETICAL CITATION
• Footnotes are for explanatory notes with superscript numbers for references
• Double space footnote text
• Bibliography page is called WORKS CITED and is listed alphabetically
  • On a separate page
  • Lists sources referenced directly in the paper
  • "Works Cited" is centered, one inch from top of page
  • Double space all lines
  • Sources listed with a hanging indent
  • Start each source with the authors last name, or the title if the author is not available
  • Underline or italicize the title of each source

For anther source for help writing MLA formatting see: http://www.dianahacker.com/pdfs/hacker-Daly-MLA.pdf

Describe the APA formatting for a research paper

Check out this web site for some information: http://www.vanguard.edu/faculty/ddegelman/index.aspx?doc_id=796

Setting Line and Paragraph Spacing

Display the formatting marks:

• LINE SPACING - is the vertical space between lines of text within a paragraph.
• PARAGRAPH SPACING - is the vertical space between paragraphs. (by default 1.15)

For MLA we need to DOUBLE SPACE the paper. From the HOME tab click the Line Spacing Button and change it to 2.0. (this is in the paragraph section of the ribbon)

For MLA we need to double space between paragraphs as well. We will need to remove the extra spaces by going to the Line Spacing Button and clicking on REMOVE SPACE AFTER PARAGRAPH.

Formatting the Header of the paper

HEADER - text that is printed 1/2 inch from the top of every page of a document

FOOTER - text that is printed 1/2 inch from the bottom of every page of a document

The header of an MLA formatted document contains:

• the authors last name
• the page number

To create the header click on the INSERT tab. Click on HEADER, scroll down the list of the gallery. Click EDIT HEADER from the gallery, Click the HOME tab, Click the ALIGN RIGHT or press CTRL+R.

Type in your last name and then press the space bar. Click the INSERT tab, Click the PAGE NUMBER button - CURRENT POSITION - PLAIN NUMBER.

Close the header by double clicking back in the text of your document

Paper Layout for MLA

Set Paper to Double Spacing

• Go to the HOME tab
• Go to the Paragraph group
• Click on the Line Spacing Button
• Change the paper to 2.0

Set Paper to have NO Spaces After Each Paragraph

• Go to the HOME tab
• Go to the Paragraph Group
• Click on the Line Spacing Button
• Click on REMOVE SPACE AFTER PARAGRAPH

Create the heading for the paper

The heading of an MLA formatted paper is on page one of the paper. It should contain the following information:

• First and Last Name
• The Instructors Name
• The course you are writing the paper for
• The date you wrote the paper

The heading should be place flush with the left side of the paper and be one inch from the top of the paper and left side. (if you set your margins to be one inch just start typing in the first line.

What are the parts of a research paper?

Steps to writing a research paper

• Select a topic
• Research and take notes on your topic. Make sure to keep the reference information. I usually write the bibliographies as I go. Noodlebib is an excellent source to help you write your references.
• Organize your ideas with an outline, map, or some other device you are comfortable with.
• Write a first draft
• Proof read you paper and make notes of changes
• Write a final draft

Parts of a Research Paper

• Introduction
• Body
• Conclusion
• Works Cited - References - make sure your sources are of quality. They should be reputable (No Wikipedia!) and have a current date.

Place your Title

Press enter down one double-spaced line. Then click your mouse button twice in the center of the paper to jump to the center. You could also use CTRL+E to center.

Type in your title of the paper:

Set First Line Indent

• Open the Ruler by clicking on the View Ruler Button above the scroll bar on the right of the window
• Slide the top triangle (First Line Indent Marker) over to 0.5 on the ruler

Create a quick style

In MLA formatting you need to indent the first line of each paragraph by 1/2 inch. To do this move the top triangle in the ruler, called the First Line Indent Marker. To use the sticks:

• Type in the first paragraph of your research paper. This is the introductory paragraph.
• Triple click the paragraph to choose the whole paragraph.
• Right click on the chosen paragraph
• Hover your mouse of the Styles option
• Click SAVE SELECTION AS A NEW QUICK STYLE
• Type in the name of the quick style you are creating
• Click on OK
• The new quick styles appears in the Styles section of the Home Tab ribbon

Auto Correcting

When you misspell a it will show up underlined in red or will auto correct when you press the spacebar or the enter key after it. After Word auto corrects it will mark the word. If you place your mouse back over the word you will see a blue box that gives you auto correcting options. You will be able to:

• Change the word back to its previous spelling
• Stop auto correcting the word
• Set other auto correcting options (you can also get here by going to the office button and clicking on the button at the bottom of the window (WORD OPTIONS).

From here you can go to the Proofing option, then on the Auto Correct tab you can set up any word and its replacement.

Creating Citations

Create a reference for all sources used. If you are using an exact quote then use quotation marks. If you are paraphrasing you should still give the author credit, as the ideas are not yours.

To cite a reference in MLA and APA you will use PARENTHETICAL CITATIONS. This means you put the authors last name, and the page number in a set of parenthesis at the end of the sentence you are citing. Word will do much of the work for you.

Change the bibliography style from the REFERENCES tab. under Style in the Citations and Bibliography section change it to MLA.

Then click the INSERT CITATION button, and ADD NEW SOURCE.

Choose the type of source and then fill in the rest of the window. Click OK when you are done.

WORD does not include the page number in the parenthetical citation, so you will need to edit the citation. Right click the Citation that was created and click on EDIT CITATION, put the page numbers in the ADD PAGES section and click OK. The parenthetical citation goes on the inside of the period.

Creating Footnotes

In MLA formatting footnotes are used for explanations. When you use a footnote you will need to add a NOTE REFERENCE MARK or a superscribed number (a number that is raised above the rest of the text)to signify a note exists. A FOOTNOTE is a note that is located at the bottom of the page that has been referenced. A ENDNOTE is a note that is added to the end of the document. The actual text of the footnote or endnote is called the NOTE TEXT. To insert a footnote reference mark:

• Go to the References Tab
• Click the Insert Footnote button
• Type your note text at the bottom of the page
• Highlight the text
• Click on the Style you created for MLA to change the footnote to the correct style

To insert a citation in the footnote

• Go to the References Tab
• Click the Insert Citation button
• Click on the ADD NEW PLACEHOLDER command
• Type a name for your tag
• Click OK

Edit a Citation

• Click on the drop down on the parenthetical citation
• Click on Edit Source
• Type in the information for this source
• Click OK

NOTE: If you use the name of the author in the sentence, you do not need to put the authors name in the parenthetical citation.

To Remove the Author from a parenthetical citation

• Click the drop down on the parenthetical citation
• Click SUPPRESS AUTHOR check box
• Click OK

Page Breaking

Soft page break

This is an automatic page break that occurs when you run out of room on one page, Word will automatically create and place your next text on the following page.

Hard page break

This is when you need to ensure that a page break occurs, like when you are going to a Works Cited or References page. To create a Hard Page Break press CTRL+ENTER. This is also a manual page break.

Background repagination

Automatic page break task, meaning when you are typing Word will continually recalculate the pages to ensure page breaks are created and inserted correctly.

Creating a Works Cited Page

• Start a new page with a hard page break
• Click in the center of the page and type: Works Cited
• Press Enter
• Insert the Bibliographies from the sources you have created
• Go to the References Tab
• In the Citations and Bibliographies group click the BIBLIOGRAPHY drop down
• Click Insert Bibliography

Format them in MLA formatting

• Highlight all of the bibliographies
• Double space them
• Make sure there is no space after the paragraphs
• Create a hanging indent
  • Go to the ruler at the top of the page
  • Move the bottom triangle(hanging indent marker) over to 0.5

Finding a Replacing Text

From the HOME tab, in the Editing group at the far right, click REPLACE. A dialog box will pop up and ask for the word you want to replace and what you want to replace it with. Fill both in and then click replace all.

Counting Words

Click on the number of words in the status bar at the bottom of the window. This will display the information about the document like the number of words, number of paragraphs, and number of pages.

Spelling and Grammar Checking

From the Review tab you can click on the Spelling and Grammar checking button to have the document check and report its findings. If you want to turn on or off some of the options you need to go to the Office button and click on WORD OPTIONS. Go to the proofing section, then click on SETTINGS in the "When correction spelling and grammar in Word". From here you can turn on and off many grammar checking features depending on what your instructor is requiring.

Research Options in Word

In Word you can easily get to the research options by ALT+clicking on a word. It will automatically find the thesaurus with a list of synonyms for the word you are looking up.

Shortcut keys

Short Cut Key Strokes

Function	Short cut
Align to the left side of the page	CTRL+L
Align to the right side of the page	CTRL+R
Align to the center of the page	CTRL+E
Align to justified, flush with both the right and left	CTRL+J
Hard Page Break	CTRL+ENTER
Underline	CTRL+U
Bold	CTRL+B
Italics	CTRL+LI
Undo	CTRL+Z
Redo	CTRL+Y
Selet all	CTRL+A
Copy	CTRL+C
Cut	CTRL+X
Paste	CTRL+V
Print	CTRL+P
Save	CTRL+S
New Document	CTRL+N
Find	CTRL+F
Move the Cursor	Click with the mouse one time
Choose a word	Double click
Choose a paragraph	Triple click

Project

Create a Research Paper that Compares MLA to APA document styles.

• Using what you have learned in this module to write a research paper on the differences and similarities of the MLA and APA document styles.
• The paper should be 400 - 500 words
• Use the internet and this text to help with your paper. (Wikipedia is not a source)
• Include at least two references
• Includes one explanatory note as a footnote.
• You should spell and grammar check the document.
• Write the paper in APA format.

Rubric

Resume Rubric

Standards	1 point	2 points	3 points	4 points	5 points
Covers all differences between APA and MLA	Is missing four or more items	Is missing three items	Is missing two items	Is missing one items	Includes the title page versus the heading, the header, the references versus the works cited
Formatted according to APA style	Poor	Weak	Fair	Good	Exemplary
Is 400 - 500 words in length	Poor	Weak	Fair	Good	Exemplary
Has two quality cited references	Poor	Weak	Fair	Good	Exemplary
Has one explanatory note	Poor	Weak	Fair	Good	Exemplary
Has no spelling or grammatical errors	Poor	Weak	Fair	Good	Exemplary

Cover Letter

Vocabulary

• Cover Letter • Letter head • Tab stop • Tab character • Clear Formatting • Quick Style • Date line • Inside address • Non-breaking space • Building Block • Table • Cell • Dimension of table

What is a Cover Letter

A COVER LETTER is an introduction to your prospective employer on who you are. Is allows you to point out the positive points from your resume that make you the best candidate for the job. It is also a sample of your writing skills that show an employer how well you communicate.

In this lesson we will construct a Cover Letter. The goal of a cover letter is to get the prospective employer to read your resume. In today's world, getting your name to the top of the list so that you can get an interview is of vital importance. You need a cover letter that fits what is acceptable business practice, but also shows a bit of your personality. Throughout this lesson you will learn how to better use Word and create a document you will be able to edit and re-use for the remander of your life.

Turn on the format markings by clicking on the backwards P - ¶

Quick Styles

A QUICK STYLE allows you to click on one button and change multiple font settings and formattings. Quick styles are located in the HOME tab in the Styles Gallery. The most used quick styles are NORMAL and NO SPACES. The ones for HEADING 1, HEADING 2, and so forth are great for being able to make a table of contents later. The factory default in Word is to be set to NORMAL. This will have a 1.15 space after each paragraph (when you press enter).

There are a number of quick style buttons located in the ribbon of the HOME tab. For a cover letter you need to have the style set to No Space so that when you press enter it goes to the next line and does not create a 1.15 space.

Theme Colors

Using theme colors helps to automatically use colors that are complementary. From the HOME tab click on the change styles button. Click on COLORS to choose a set of colors that matches your personality.

RED – danger, power, energy

ORANGE – success, victory, creativity

YELLOW – happiness, hope, intelligence

GREEN – growth, health, healing, money, tranquility

BLUE – integrity, trust, confidence

PURPLE – wealth, royalty, magic, spiritual

BROWN – honest, simple, down-to-earth

GRAY – neutrality

WHITE – pure, clean, perfection

BLACK – authority, strength, elegance

Setting a tab stop

A TAB STOP is a position across your page that your cursor stops at when you press the TAB key.

With your cursor (a blinking I beam) at the beginning of the document click on the paragraph dialog box launcher. Click on the TAB button at the bottom left of the dialog window. Type in the place you want the cursor to jump to when you press TAB. Click the radio button for the correct alignment that the text should do at that tab stop. Set if there should be any leaders (for this document we will not have leaders, and they will be discussed in a later lesson). Click SET to accept the tab stop and then click OK.

You can also set a tab stop by clicking in the ruler at the top of the page. A left tab stop will be created. You can then double click on the tab stop and format it as needed.

When you press tab and you have the formatting marks turned on, a tab will show up with the TAB CHARACTER of → When you set a tab stop a character will show up in the ruller that looks like: a L for a left tab stop - the text will be typed to the right of the tab stop a backwards L for a right tab stop - the text will be typed to the left of the tab stop an upside down T for a center tab stop - the text will be centered on the tab stop

Formatting a Clip Art

Click on the INSERT tab. Click on the Clip Art button Type in a key word that represents you or the occupation you are applying for. Choose a clip art from the list of graphics that comes up. Open the Picture Tools Click on the Size Dialog Box Launcher Make sure the check box for LOCK ASPECT RATIO is checked. Change the height to meet your requirements.

Character size, style, and color

Highlight the characters you want to format. Click on the FONT SIZE drop down menu. Choose a font size. With the characters highlighted click the drop down menu for font style. When you mouse is hovering over a font style, the characters in your document will reflect the change. To make the change permanent click on the font style.

To change the color, click the A that is underlined with the color bar. When you click on the drop down, only those colors that are associated with the theme you have selected are displayed. When you mouse is hovering over a color the document will reflect the change. When you are satisfied with the color click on it.

Building Block

A BUILDING BLOCK is a few words to a complete paragraph that can be entered into a document by typing just a few characters. In our letter example here we will build a building block for the company's name, Brown and Company Inc..

When you are entering the company name for the first time in the inside address stop and create the building block.

Highlight the company name, but not the paragraph mark. Go to the Insert tab. Click on the Quick Parts button. From the Quick Parts menu click on SAVE SELECTION TO QUICK PART GALLERY. The Create New Building Block dialog box will show up. Type "bci" in the NAME field. Click on OK.

When you want to insert the company name again just type "bci" and then press F3.

Non-breaking Spaces

Sometimes you will want a group of words to act more like one word with respects to word wrapping. That means that you want the entire group of words to remain on the same line together. In this example we are going to keep the news paper name together with non-breaking spaces. When you go to type in the name of the news paper type it as follows:

Type: Pittsburgh Press CTRL+SHIFT+SPACEBAR Type: Post Press: CTRL+SHIFT+SPACEBAR Type: Gazette

The three words of the news paper title are now put together as one block.

SIDE NOTE: You can also do this with the hyphenated words by pressing CTRL+SHIFT+HYPHEN instead of just a regular hyphen.

Tables

A TABLE is a way to organize your work into rows and columns. Each block in the table is called a CELL. When you first create a table you need to specify the number of rows and columns. This is called the DIMENSIONS OF THE TABLE. The dimensions are usually listed as columns x rows. So a table that is 3 x 5 is three columns by five rows. (a column goes up and down, and a row goes side to side)

To create a table go to the INSERT tab. Click on the TABLE button and then position the mouse over the correct number of rows and columns in the graphical display. As you hover over the rows and columns graphic the sample table is displayed in the document. Once you click your mouse the table will actually be created in your document.

Type in the text of your table.

Notice that the ruler at the top of the page will show the boundaries of the table. You can click and drag the boundaries in the ruler to adjust your table in the document.

If you need to add another row to your table at the bottom, you can just press TAB from the last cell in the lower right and a new row will be created.

Use TAB to move from cell to cell in the table and SHIFT+TAB to move backwards up the table cells.

Once you have all your data typed into the table you can format the table style and colors. At the top of the word window there is a TABLE TOOLS button that will open up the table editing ribbon.

Click on the Design tab. Click on the more button in the table styles gallery. You should see the same table style in each row, and the columns are the colors from your theme you selected earlier. Staying in the same color theme, choose a style that fits your personality and matches the content of your table.

Once you have the table style and color set you can change the table to fit the size requirement of the content.

Click ont he Layout tab Click the Autofit button Click on the AutoFit Contents menu option

Next hover your mouse over the table to see the table move handle in the upper left corner. Click on the handle to choose the entire table. Then press CTRL+E to center the table on the page.

Letter Head

LETTER HEAD is usually preprinted on stationery that is used by everyone in the company. This is the first item that the reader will notice. It should not take up much space, should reflect the personality of the company, or individual, and should be business professional.

When you are preparing a professional document and you are not part of a company, you can create your own letterhead. Letterhead usually contains the legal name of the company (or your legal name if you are self employed), your complete address including your building, room, suite, or post office box, your phone number, your fax number (if you have one), and your email address. If you have a web address this also can be placed as part of your letterhead. It can also contain a graphic that represents you, or your business.

We will use text, graphics, and a border to create the appearance of a professional letterhead that will display your personality.

Type your name. Change the font to size 18. Pick a font style that matches your personality. Press enter to the next line.

Pick a clip art that matches your career interest. Change the size to 0.5 x 0.5, or close to that.

Set the font size to 11 and change the style to Arial or Times New Roman. Set a tab stop at 6.5 that aligns right.

Press TAB. Type in your address, then type a symbol. Type in your phone number, then type a symbol. Finally type in your e-mail address.

OR

Press TAB and then type in your address. Press ENTER. Press TAB and then type in your phone number. Press ENTER Press TAB and then type in your e-mail address. Press ENTER Set the clip art to in front of text so that the clip art is on the left and the text is on the right.

With your cursor at the end of the last line of text, after your email address and before the paragraph marker, click on the bottom border button in the HOME ribbon.

Press ENTER

Click on the NORMAL button to move the bottom border up below the letterhead. This is will CLEAR FORMATTING and let you start the next part of the document with the default character and paragraph formatting.

Remove hyperlink

When you type an email address or other web address Word will automatically create the hyperlink for you. If you do not want the text to be a hyperlink you need to remove that coding. The easiest way is to put your cursor immediately after the hyperlink text and press BACKSPACE one time. You can also highlight the hyperlink text and then right click and click on remove hyperlink.

Insert Current date

To enter the current date into a document you have two popular ways. You can type in the current month, and when you finish typing the month and press space, the current date will appear in a box. Pressing enter will accept the text into your document. The other method is to go to the INSERT TAB and click on the DATE & TIME button. From here you can pick the format you would like today's date to take when it is placed in the document. Just double click on the format, or click on the format and then click OK.

When typing in a business letter the DATE LINE should be just under the letter head, and on the right side of the page. You can create a left tab stop at 3.5, then press tab and select the format of the date. The format (September 28, 2009) is a standard used in business letters. The date line should be the date you are sending the letter, which is usually the date you are writing it.

Inside Address

The INSIDE ADDRESS is the address of who you are sending the letter. This is placed two lines below the date line, or so that it fits in the window of the envelope once your letter has been folded. You might need to play with this a little depending on the envelope style, and your letterhead you have created. The address block should contain the persons name whom you are contacting, their official title, the name of the company, the street address or po box, and the city, state and zip code.

EXAMPE: Mr. John Smith Human Resources Director Brown and Company Inc. 100 Main Street, Suite 3 No Name, CO 81601

Salutation

The SALUTATION of a letter is the line that greets who you are contacting. There should be one blank line between the address block and the salutation. Usually the salutation looks like:

Dear Mr. John Smith:

You can use a : or a ; depending on the letter style you are using.

Letter Style

There are three main letter styles:

block every part of the letter lines up on the left margin

modified block every part of the letter lines up on the left margin except the date, close, and signature. Those three elements are aligned just to the right of center.

modified semi-block every paragraph is indented the date, close and signature are just to the right of center the rest of the letter lines up on the left margin

Body

Where you heard about the opening

Write a paragraph that is about two or three sentences long that describes how you heard about the job opening. Remember to use the non-breaking space on the Pittsburgh Post Gazette, and to use the building block to enter the company name. (type "bci" then press F3)

SAMPLE

I am responding to the advertisement for a high school technology teacher that you recently placed in the Pittsburgh Post Gazette. After reading the requirements for the technology teaching position, I know that I am very well qualified to join your team. You will find that my enclosed resume outlines how I can be a valuable asset to Brown Carole Institute.

Educational background

Write a paragraph that is three or more sentences discussing your training and education. List any degrees or certifications that are relevent to this specific job. If you have received training in several key areas and would like to spot light that, a table might be useful here.

SAMPLE

This past December I graduated from Slippery Rock University with a bachelor's degree in mathematics, a minor in computer programming, and a minor in education. The following table outlines the hours of study in each area:

Course Hours

Mathematics	32 Semester credits
Computer Programming	20 Semester credits
Mathematics Education	20 Semester credits

Work experience

It is important to list any experience you have in the field you are applying. Volunteer work counts, as does any community service, or actual on the job training. This paragraph should be at least three sentences long and outline any experience that is specific to the job for which you are applying. You might want to include a bulleted list.

SAMPLE

While I was attending college to earn my degree I was also working to gain the following teaching experience.

• Worked at Sylvan Learning Center for two years.
• Worked with the on campus tutoring service for three years.
• Volunteered with the on campus special needs classes to help teach sign language.

Contact them

The last paragraph of the cover letter should let them know when you are planning on following up with a phone call to check on the status of the position and to make an appointment to visit with them in person. It should give a time frame, and include your contact information in case the have any questions.

Remember to use the building block when you type in the sentence to enter in the company name.

Type: bci

Press F3

If you want the phone number to stay together on one line try using the non-breaking hypen.

SAMPLE

I will call you later next week to set up a time when we can meet to discuss my career opportunities at Brown Carole Institute. If you have any questions or need more information please call me at 970-555-5555, or you can email me at contactme@domain.com.

Complimentary close

When you are done writing your letter you need to close the letter and then sign it. Prior to your name you need to add the COMPLIMENTARY CLOSE.

Press ENTER twice to leave a blank space.

Press TAB (the left tab stop should still be set at 3.5)

Type: Sincerely,

Press ENTER

Signature block

You need to leave a place to sign your name after you print your letter out.

Press ENTER three times

Press TAB (there should still be a left tab stop at 3.5)

Type in your full legal name.

Press ENTER

Enclosures

When you send a letter with multiple pages or documents you need to communicate that to the recipient. This is done by placing the word ENCLOSURES at the bottom of the page. If you have only one extra page or document then you can just write the word enclosure to signify that the person should find another document other than the main one that they are currently reading. If you are sending multiple documents you should have a title ENCLOSURES and then list the names of the other documents that they should find.

Project

Do a job search on www.monster.com for a job that fits an area of interest from your interest surveys. Create a cover letter for that job.

1. Use your information to create the letter head.

2. Use www.yellowbook.com to help find the address for the company to fill in the inside address.

3. If there is no contact person use Personnel Director, or Human Resources Director for who's attention to address the letter.

4. Include some of the words from the job description on monter.com to help fill in the body of the letter. Include where you heard about the job (monster.com), your educational qualifications, and your job experience.

Rubric

Cover Letter Rubric

Standards	1 point	2 points	3 points	4 points	5 points
Creates a letterhead	Is missing four or more items	Is missing three items	Is missing two items	Is missing one items	Includes your name, address, city, state, zip, phone number, and email address and is formatted correctly
Creates the date line, salutation, close and signature	Is missing or formats four or more items incorrectly	Is missing or formats three or more items incorrectly	Is missing or formats two or more items incorrectly	Is missing or formats one or more items incorrectly	Places the date appropriately on the page, has today's date, formats the salutation correctly, places and formats the close correctly, places and formats the signature block correctly
Creates the inside address	Is missing or formats four or more items incorrectly	Is missing or formats three or more items incorrectly	Is missing or formats two or more items incorrectly	Is missing or formats one or more items incorrectly	Includes the name (if available), the job title, company name, address, city, state, and zip. All is formatted correctly
Includes where you heard about the job opening	Is missing all four elements	Is missing three elements	Is missing two elements	Is missing one element	In a two or more sentence paragraph, lists where you heard about the job opening, and includes their company's name.
Includes your education	Is missing all the elements of training	Is missing three elements	Is missing two elements	Is missing one element	In a three or more sentence paragraph, outlines the formal training that qualifies you for this position.
Includes your experience	Is too short and does not list any qulifications for this position	Is missing three elements	Is missing two elements	Is missing one element	In a three or more sentence paragraph, outlines the job experience that qualifies you for this position.
Includes when you will contact them	Does not discuss contacting the prospective employer	Is missing three elements	Is missing two elements	Is missing one element	In a two ore more sentence paragraph include when you will contact them and how they can contact you.

Resume

Vocabulary

• Template
• Content Control
• Office Clipboard
• Source Document
• Destination Document
• Line Break
• Sorting

What Makes a Good Resume

• Include all necessary information
  • contact information
  • career objective
  • educational background
  • experience
  • Professional organizations
  • recognitions and awards
  • skills
  • community service
• Presentation
  • use a laser printer with good quality ink
  • print it on resume paper
  • use high quality envelopes
  • email it as a pdf file type
  • place it on your myspace or facebook as a pdf file format

Two types of resumes

Chronological

Places elements into the resume according to time with the most recent items first. This type of resume is used to show the growth of the individual.

Functional

Places elements by skill or accomplishment. This type is used to show specific skills or qualifications.

NOTE: If your inexperienced, it is recommended to use a chronological resume.

What is a Template

A document that has already been created with some of the content filled in. It has all the formatting done, and leaves a place called a CONTENT CONTROL for entering the unique information into the document.

There are many different types of documents that have been templated and comes with Microsoft Word. Some of these are:

• letters
• fax cover sheets
• reports
• resumes

Select a Template

Click on the office button

Click NEW

Click Installed Templates

Look at the installed templates to determine what one best fits your needs

For this example we will use the ORIGIN RESUME

This creates a template with four tables.

Delete the top and bottom blank tables by using the Delete Row feature below

DELETE A ROW OF A TABLE

Click the LAYOUT tab

Click the DELETE button

Click DELETE ROWS

Content Controls

The template comes up with many CONTENT CONTROLS. These are blocks of text with a note telling you what you should type there. The first one on this template will be one called USER. You should replace the text with your name.

Under your name you need to fill in the content controls for your address (street or po box plus your city, state, and zip code), phone number, e-mail address, and web address if you have one (like your my space or facebook).

The easiest way to fill in this information is to open the cover letter you created and copy and paste the information to the Office Clipboard.

Copy and Paste with the Office Clipboard

Open up your cover letter you created in the last module.

In the resume document open the OFFICE CLIPBOARD by clicking on the Clipboard Dialog Box Launcher button in the upper left of the screen below the HOME tab. (It just says Clipboard)

From your cover letter highlight and then copy the complete address

From your cover letter highlight and then copy the phone number but not the word PHONE

From your cover letter highlight and then copy the e-mail address but not the word E-mail

As you copy each you should see them show up in the Office Clipboard of your resume.

Go to your resume document.

Highlight the content Control for the address.

Click on the address in the Office Clipboard.

Do the same to fill in the rest of the contact information in the resume

Delete the line for a web address if you don't have one.

NOTE: in this example the cover page is the SOURCE DOCUMENT, meaning that is where the information is coming from, or the source where you copy it from. The resume is the DESTINATION DOCUMENT, meaning where you are pasting the information or the destination where the information needs to go.

What sections should you have in a resume?

1. Contact information - the information we just filled in at the top.
2. Objective - What you want for your career goal.

   SAMPLE:

   To obtain a full-time graphics design position with a leading advertising agency in the New York area.

3. Eductation - This is a list of your formal training with the most recent at the top.

   SAMPLE:

   Assoc. Graphic Design (Colorado Mountain College, May 2009)

   • Full scholarship for 2 years
   • Outstanding Art Student, May 2009
   • Winner best cover design contest for Art Awards Night
4. Experience - List all work and volunteer experience you have related to your career objective.
5. Skills - List special skills you have obtained that might not have been from formal training.
6. Professional Organizations - List all organizations you have a membership with or had a membership with.
7. Community Service - List any activities you have been involved with that have helped your community, or any non-profit organization.

Use Line Breaks for Layout

There are times when you will want to go to the next line on the page and not be considered a new paragraph, this is called a LINE BREAK. One of the more frequently used times for this is when you are in a list (bulleted or numbered) and you do not want bullets in front of the additional lines, but you do want the same indentation. To move down a line without pressing ENTER, press SHIFT+ENTER. You will be presented with a line break character that looks like an arrow going down to the next line.

Under the Education section add a new line item for: Areas of Concentration

Press SHIFT+ENTER to go to the next line.

Type: Pencil

Press SHIFT+ENTER

Type: Chalk

Press SHIFT+ENTER

Type: Computer Graphics with Photoshop

Adding a new Building Block

You need to have one Building Block for each Work Experience you need to list. The listings should start with the most current first and then go reverse chronological order down the section.

Fill in the content controls for the Experience section.

SAMPLE:

Experience

Layout Design Editor (September 2008 - May 2009)

• Colorado Mountain College News(Rifle, CO)

• Created the template for the newspaper each week of production.

You will need to add a new BUILDING BLOCK for additional experience to be listed.

• Place your cursor before the paragraph marker after the first experience you have just entered.
• Click on the INSERT TAB
• click on the QUICK PARTS button
• Scroll down the list to the EXPERIENCE SUBSECTION
• Click on the EXPERIENCE SUBSECTION to have it placed into the document.
• Delete any extra paragraph markers that are inserted with the building block.

Fill in the second experience and any further that you need to fill your actual experience.

Format Paragraph Spacing and Indentation

To move the line under your experience job title in you can place your cursor at the end of the line and prior to the paragraph marker. Then click on the INCREASE INDENT BUTTON on the HOME tab. It is a bunch of black line with a blue arrow pointing right, and is located in the paragraph section of the ribbon.

Skills

In a full sentence format type in the skills you have mastered that are not specifically listed elsewhere on the resume and that make you a better candidate for the job.

SAMPLE:

Adobe products:

• Photoshop
• Illustrator
• In-Design
• After Effects

Professional Organizations

Add a building block for References, as we did before going to insert - quick parts - references.

Change the title References to Professional Organizations

List any organizations that you belong to.

SAMPLE:

• National Honors Society, two years
• FBLA, four years
• PBL, two years

Community Service

Add a building block for References, as we did before going to insert - quick parts - references.

Change the title References to Community Service

List any projects that you did, or helped with

SAMPLE:

• Raised money for MDA
• Picked up trash after the homecoming parade
• Decorated the windows of local businesses for the holidays
• Collected used coats for those without

Sorting a Paragraph

It is nice to have SORTED lists that have no chronological order listed alphabetically. To do this highlight the items like the community service items you have listed. From the HOME TAB under the PARAGRAPH section, click on the AZ button to sort the list. Leave the defaults and the list will be organized for you.

Change the Views of a Document

Once you are done with the document you can change THEME COLORS. This can be done by going to the HOME tab - Change Styles Button - Colors - hover over the colors to see the affect and then click on the one you like.

It is also nice to look at the document as a whole by zooming out, or changing the VIEW. To change the VIEW go to the VIEW tab. You can change from print layout to full screen reading. Or you can zoom in from the zoom section. Also if this is a multiple page document you can pull up the document in one page or two.

Print Preview and Page Setup

Before you print a document is always a good idea to PRINT PREVIEW to see what is going to look like. Many errors can be caught and fixed at this point. To view the document in PRINT PREVIEW to to the OFFICE BUTTON - PRINT - PRINT PREVIEW

What to never put on a Resume

• Do not include
  • social security number
  • marital status
  • age, height, weight, gender, other physical appearance items
  • citizenship
  • previous pay rates
  • reason for leaving prior position
  • references

Project

Pick a different template, or start from scratch to design your resume. Create your resume with your current accurate information. Gear the resume to a career you have an interest in based on your career surveys you have done.

Rubric

Resume Rubric

Standards	1 point	2 points	3 points	4 points	5 points
Contact information	Is missing four or more items	Is missing three items	Is missing two items	Is missing one items	Includes your name, address, city, state, zip, phone number, and email address and is formatted correctly
States your objective	Poor	Weak	Fair	Good	Exemplary
Has a section for Education	Poor	Weak	Fair	Good	Exemplary
Has a section for Experience	Poor	Weak	Fair	Good	Exemplary
Has a section for Skills	Poor	Weak	Fair	Good	Exemplary
Has a section for Professional Organizations	Poor	Weak	Fair	Good	Exemplary
Has a section for Community Service	Poor	Weak	Fair	Good	Exemplary
Format is professional	Does not follow standards of a professional resume and is missing more than two sections	Is missing sections and formatting	Includes all but one section and/or has formatting issues	Includes all sections but has some formatting issues	Uses a resume template or follows standards for a professional resume

Opening a Word File as Read Only

Open a file as read only with the main menu

1. Click on File -> Open...
2. Select the Word file
3. Click on the arrow on the "Open" button
4. Click on "Open as read only"

Open a file as read only from Windows Explorer with Shift key

1. In Windows Explorer, hold down the Shift key and right click your file.
2. You can now "Open as Read-Only" or "Open in Protected View"

Open a file as read only from Windows Explorer without Shift key

1. In Windows Explorer, click on Tools -> Folder options -> File types
2. Select the file type "DOC"
3. The buttons "Modify" and "Advanced" should appear (if the buttons "Modify" and "Restore" appear, you need to restore the default configuration)
4. Click on "Advanced"
5. Click on "New"
6. In action, write Open (read-only)
7. In application used to perform action, write "C:\Program Files\Microsoft Office\OFFICE11\WINWORD.EXE" %1 /h /n /dde (adapt the path to your installation folder)
8. Check "Use DDE"
9. In DDE message, write [AppShow][REM _DDE_ReadWriteOnSave][FileOpen .Name="%1",.Revert=0,.ReadOnly=1]
10. In DDE Application Not Running, empty the field
11. In topic, write System
12. Click on "OK"
13. Click on "OK" again
14. Click on "Close"

Now, to open the file in read-only mode, right-click on the file and select Open (read-only)

Create a Proposal with collaboration

Vocabulary

• Planning Proposal - Offers improvements to a situation or solutions to a problem
• Research Proposal - Requests funding for a project
• Sales Proposal - Sells a product, service or idea
• View Page Width - Zoom the document so that the width of the page matches the width of the window
• Title Page - Gives the main idea of the document and should attract the readers attention
• Table - a group of rows and columns used to organize data in a document
• Shadow - a light gray duplicate image that appears on the edge of a character or object.
• Hidden Text - text that does not print but is part of a document
• Section - a group of the document that is formatted the same
• Section Break - the end of one section and the beginning of another so that you can change formatting, such as changing from one column to two columns and then back to one column again.
• Header - text that prints at the top of every page in a document
• Footer - text that prints at the bottom of every page in a document
• Nonadjacent Text - this is also called non-contiguous text and means text that is not next to each other.
• Datasheet Window - This is just like Access where the data for a graph is stored in a datasheet, or spreadsheet like window
• Draw Table Feature - The ability to use a pencil and eraser to draw a table to certain specifications
• Watermark - text or graphic that is placed behind the text of the document, this can be words such as DRAFT, or SOLD OUT, or a company logo.

Lesson

• Open Word to a new document
• Do a save and place it in your folder for this class under Word

Display Formatting Characters

It will be essential for this lesson that you use formatting characters, have them on and know what each one means.

From the HOME Tab click on the backwards P, it is also the paragraph mark, to turn on formatting characters. Leave this turned on for the entire lesson.

Create a title Page

Change Font Style/Color/Size

Go to the VIEW tab an click on the PAGE WIDTH button.

• Click on the Center Button on the HOME TAB
• Change the font style to a font that is easy to read, stick to a block style, sans, or sans serif
• Change the font size to 48 point
• Change the style to BOLD by pressing SHIFT+B
• Change the color to a Blue on the color palette
• Type - Vote Yes
• Press Enter
• Type - For an Improved
• Press Enter
• Type - Meeker

Border and Shade Text

• Select the text you have typed
• Go to the Insert Tab
• Click on the Table button
• Click Insert Table

Each line will be a separate row in a table. The table tools ribbon should be displayed.

• Click on the Eraser tool on the far right in the Draw Borders group
• Click on the interior borders to your table so that all the text is in the same cell
• Click on the eraser tool again to turn it off
• With the entire table selected click on the border button drop down
• Click on Borders and Shading
• Change the Width to 4 1/2
• Change the style to a shaded edge
• Click on the shading tab
• Change the fill color
• Click OK to accept the changes
• From the Table Tools ribbon, you can also change the fill color with the BUCKET or fill tool directly above the border button. Hover over different colors to see the preview.

NOTE: if you change the fill color to a dark color you will probably want to change the text color to a light color so it can be easily read.

Clear Formatting

When you are in a table and need to get below the table, but do not have any spaces there yet, you can do one of the following:

• Press enter
• Go to the home tab
• Go to the Styles
• Click on the drop down
• Click on Clear Formatting

OR

• Double click below the table

Modify Character Spacing

• Press enter one time
• Press Cap Locks
• Type: MEEKER
• Press Enter
• Type: PARK DISTRICT
• Press Enter
• Type: REFERENDUM
• Click and drag over the text to select it
• Click the button in the corner of the FONT group on the HOME tab to show the font format dialog box
• Change the font type to: Baskerville Old Face
• Check the box for Shadow under Effects
• Change the font color to match the font from the table
• Click on the Character Spacing TAB
• Change the Spacing to Expanded by 2
• Click OK

NOTE: you can make your text expanded, or condensed

Insert Clip Art

• Click after the last line
• Press enter twice to leave one blank space
• Click on the INSERT tab
• Click on Clip Art
• Find clip art for voting, or ballot box
• Use the resizing handles to make the graphic the "right" size for the page

NOTE: the corner handles keep the picture proportional

Zooming

• Click on the VIEW TAB
• Click on Page Width
• Resize your document window and watch what happens to your document
• Click on One Page
• Resize your document window and watch what happens to your document

Center your Page vertically

• Press CTRL+HOME to move the cursor to the top of the document
• Click on the Page Layout Tab
• Click on the corner of the page setup group to launch the page setup dialog box
• Click on the Layout Tab
• Click on the drop down under Page - Vertical Alignment and change it to Center
• Click on OK
• Save your document

Insert an Existing Document into your Current Open Document

Open a file saved to a web page

• Click on the link for the file Meeker Rec Center
• Click on the file named: Meeker Rec Center
• "Save As" to your storage space
• close that document

Insert a Section Next Page Break

• Go to your document with the title page
• Press CTRL+END to move to the end of your document
• Go to the Page Layout TAB
• Click on the Breaks Drop down
• Under the Section Breaks, click on Next Page

Change Page Alignment

• Go back to the Page Setup Dialog Box
  • Page Layout Tab
  • Page Setup Group
  • Dialog Launching Button
• Click on the Layout Tab
• Click OK

Insert text from file

• Click on the Insert Tab
• Click on the Object Drop Down
• Click on Text from file
• Browse to find the document you saved earlier
• Double click the document name
• Save your document

Zoom and View your Document

Use the Zoom bar at the lower right of the word window to zoom in on parts of your document, or zoom out.

Use the View Tab at the top to view the document as two pages, one page, or Page Width. Change the view to page width and then change the size of the window to see what happens.

Deleting

Page Break

• Make sure the formatting marks are on
• Scroll to the bottom of page 2
• Place the mouse to the left so it is a white arrow pointing up and right
• Click to choose the Page Break
• Press DELETE on the keyboard

Text

Go to the end of the document, click and drag over "very much" and then press DELETE on your keyboard. You never use informal language in a proposal.

Headers and Footers

Create Headers

1. Set the first page different from the rest
   • Go to the Page Layout Tab
   • Go to the Page Setup Group
   • Click on the More button to launch the Page Setup Dialog Box
   • Click on the Layout Tab
   • Go to the section for Headers and Footers
   • Check the box for Different First Page
   • Click OK
2. Add a Header from page 2 to the end of the document
   • Go to page 2 of your document
   • Double click in the margin above the text
   • Double click on the right side of the paper in the header
   • Make sure you are in the Header Footer Tools on the DESIGN tab
   • Click on the Page Number Drop Down at the far left
   • Click on Top of page
   • Scroll Down and click on PLAIN NUMBER 3 - so it says "2"
3. Change what page number it starts counting from
   • Click on the Page Number Drop Down
   • Click on Format Page Number
   • Go to the Page Numbering section
   • Click on Start At
   • In the box type a 1
   • Click OK

Create Footers

• Scroll to the bottom of the page and click in the footer section
• Click in the middle of the page in the footer to center you text
• Type in "Vote YES!"
• Double click in the main part of your document to exit out of the headers and footers

Working with Tables

Delete a Column

• Go to the table on page 2
• Place your mouse on the second column at the top so it is a black arrow pointing down
• Click once to choose the column
• Right click on the second column
• Click on Delete Columns

Insert a Row

• Select the top row of the table
• Right click the row
• Click on Insert Rows

Using Table Tools

• Click on the table
• Click on TABLE TOOLS at the top of the window
• Go to the Layout Tab
• Add and remove rows and columns from the Rows & Columns group

Table Editing with the Short Cut Menu

• Right click the top row of the table and click on Insert Row
• Right click the new top row and click on MERGE cells
• Type in the title: Annual Tax Impact - ESTIMATED

Add a row with the TAB key

• Place you cursor in the very last cell of the table - lower right
• Press TAB
• Type TOTAL in the first cell of the new row

Using AutoSum

• Click on the second cell in the last row
• Click on the Table Tools
• Click on the Layout Tab
• Go to the Data Group
• Click on the Formula button
• Click on the drop down for Paste Function
• Scroll and click on SUM
• Fix the formula so it reads =SUM(ABOVE)
• Click OK
• Place the total at the bottom of each column

Format a Table

• Select the table
• Press CTRL+E to center it on the page
• Choose the data in the table - NOT the Headings
• Press CTRL+R to right align the contents of each cell
• Select the ST for 1st bond issue
• Hold down CTRL and select the ND and RD for 2nd and 3rd
• Go to the HOME tab
• Go to the Font group and click on the dialog box launcher
• Click on Superscript
• Click on OK
• Select the whole table
• Select the Table Tools - Design tab
• From the Table Styles select a style for your table
• NOTE: Use the Borders button to edit it as needed

Create a Chart from a Table

• Select the second through fourth rows in the table
• go to the Insert tab
• Click on Chart in the Illustrations group
• Select a type of chart

I am confused here on how to get it to go into the document correctly

• Cut the Chart out of the table
• Paste it below the table
• Edit the Excel sheet so that the data reads correctly in the chart
• Play with the Chart Tools and Chart Layouts to get a look that matches your document

Format Bulleted Lists

Find a Formatting

• Press CTRL+F
• Click on MORE
• Click on FORMAT
• Click on FONT
• Fill in Times New Roman - 12 - Bold
• Click on Find Next

Create Character Style

• Highlight the words 1st Bond
• Go to the HOME tab
• Click the drop down for the styles
• Click - Save Selection as new Quick Style
• Call it BOND
• Click OK
• Apply the style to the rest of the bonds

Change bullets to pictures

• Highlight the bulleted list
• Go to the HOME tab
• Go to the Paragraph group
• Click on the drop down next to the bullet list button
• Click on Define New Bullet
• Click on Picture button
• Click on one of the pictures
• Click on OK
• Click on OK

Draw a Table

Draw the Table Outline

• Place your cursor before the last paragraph
• Go to the INSERT tab
• Click on the table drop down
• Click on the Draw Table option
• Move your mouse down to the document and draw a rectangle that is about 5 inches wide and about 2 1/2 inches high
• Use the pencil to continue drawing the table that will look like the one below
• Use the eraser to erase any lines accidentally drawn

Distribute rows evenly

• Click in the table somewhere
• Go to the Table Tools
• Go to the Layout tab
• Go to the Cell Size Group
• Click on - Distribute Rows button

NOTE: do the same thing to distribute the columns evenly

Single Space the Table Contents

• Choose the entire Table
• Press CTRL+1

Fill in the data for the table

Fill in the data as below:

Format the Table

• Change alignment of text in a cell
  • Click in the cell that has the word - VOTE
  • Go to the LAYOUT tab
  • Go to the Alignment group
  • Click on the Text Direction button until the text is facing the direction you want - twice is what I would choose
• Format the words VOTE and YES
  • Select the word cell for VOTE
  • Hold down CTRL and select the word cell for YES
  • Change the font to size 24
  • Change the font to bold
  • Change the font style to match your scheme
  • Center the text
  • Shade the cells to match your color scheme
  • Choose the ST, ND, and RD for the bonds and superscript them
• Format the title
  • Select the text in the title
  • Change the shade color to a darker color for the same color scheme
  • Change the font size to 28
  • Center the text
• Add totals to the end of each row
  • Click in the last cell in the third row - the total for the bond issue 1
  • Go to the Layout tab
  • Go to the alignment group
  • Click on the formula button
  • the formula should read =SUM(LEFT)
  • Add the formula to each of the total cells - make sure the formula stays the same and continues to add left.

Add a Watermark

• Click on the Layout tab
• Go to the Page Background group
• Click on Watermark drop down
• Click on Custom Watermark at the bottom
• Click on Text Watermark radio button
• Type in the TEXT field - VOTE YES!
• Click on apply
• Click on Close

Preview your document

• Go to the VIEW tab
• Go to the ZOOM group
• Click on TWO PAGES
• Scroll to see your document

Project

Your school has many different facilities and services that it offers you. In the next few years there are going to be major budget issues. Write a proposal that is either to persuade the administration what area(s) need to be cut, OR what area(s) need to be saved from the budget changes to come. This proposal will be shared with the administration.

The Proposal should include the following:

• Title page that catches the eye
• An introduction paragraph
• Bulleted items of each proposed change - at least three
• A paragraph explaining each of the bulleted items
• Table of how this will help the district financially with totals
• Graph of the table without totals
• A conclusion paragraph
• A header with page number
• A footer with message summary
• A text Watermark
• One ClipArt or picture
• Text that is bordered and shaded

You will work with one other student to complete this assignment, you may not talk in class to one another, you must use some electronic means of communication. This project is to simulate collaboration of two people that are in different locations. You can use e-mail or IM.

Rubric

You will be graded on only those line items that you were responsible for.

Please clearly mark what group members are responsible for each line item.

The last item will be added into everyones grade in the group

Proposal Individual Rubric

Standards	1 point	2 points	3 points	4 points	5 points
Title Page with clip art and text that is shaded and bordered	The title page is missing more than one item.	The title page is missing one item.	The title page only has consistent colors, or is missing one item.	The title page is attractive and has consistent colors. It has a title, sub-title, clipArt, and text that is shaded and bordered.	The title page is exceptionally attractive and catches the eye. It contains a snappy title, sub-title, ClipArt, and has text that is shaded and bordered.
Introduction Paragraph	There is no clear introduction of the main topic or structure of the paper.	The introduction states the main topic poorly, and does not adequately preview the structure of the paper nor is it particularly inviting to the reader.	The introduction states the main topic, but does not adequately preview the structure of the paper nor is it particularly inviting to the reader.	The introduction clearly states the main topic and previews the structure of the paper, but is not particularly inviting to the reader.	The introduction is inviting, states the main topic and previews the structure of the paper.
Bullet List of 3 items	Many details are not in a logical or expected order. There is little sense that the writing is organized.	Some details are not in a logical or expected order, and this distracts the reader. The bullets are changed from the standard circle. Formatting was done on the text.	Some details are not in a logical or expected order, and this distracts the reader. The bullets are changed from the standard circle. Formatting was done on the text. Formatting is consistent.	Details are placed in a logical order, but the way in which they are presented/introduced sometimes makes the writing less interesting. The bullets are changed from the standard circle. Formatting was done on the text to make key parts stand out. Formatting is consistent.	Details are placed in a logical order and the way they are presented effectively keeps the interest of the reader. The bullets are changed to add eye appeal. Formatting was done on the text to make key parts stand out. Formatting is consistent.
Paragraph for item 1	Sentences lack structure and appear incomplete or rambling. Sentences rarely vary in length. Writer makes more than 4 errors in grammar or spelling that distract the reader from the content. NO facts are reported OR most are inaccurately reported.	Most sentences are well-constructed but have a similar structure. Some sentences vary in length. Writer makes 3-4 errors in grammar or spelling that distract the reader from the content. Most supportive facts are reported accurately.	Most sentences are well-constructed but have a similar structure. Some sentences vary in length. Writer makes 2 errors in grammar or spelling that distract the reader from the content. Almost all supportive facts are reported accurately.	Most sentences are well-constructed with varied structure. Almost all paragraphs have sentences that vary in length. Writer makes 1 error in grammar or spelling that distract the reader from the content. Almost all supportive facts are reported accurately.	All sentences are well-constructed with varied structure. Every paragraph has sentences that vary in length. Writer makes no errors in grammar or spelling that distract the reader from the content. All supportive facts are reported accurately.
Paragraph for item 2	Sentences lack structure and appear incomplete or rambling. Sentences rarely vary in length. Writer makes more than 4 errors in grammar or spelling that distract the reader from the content. NO facts are reported OR most are inaccurately reported.	Most sentences are well-constructed but have a similar structure. Some sentences vary in length. Writer makes 3-4 errors in grammar or spelling that distract the reader from the content. Most supportive facts are reported accurately.	Most sentences are well-constructed but have a similar structure. Some sentences vary in length. Writer makes 2 errors in grammar or spelling that distract the reader from the content. Almost all supportive facts are reported accurately.	Most sentences are well-constructed with varied structure. Almost all paragraphs have sentences that vary in length. Writer makes 1 error in grammar or spelling that distract the reader from the content. Almost all supportive facts are reported accurately.	All sentences are well-constructed with varied structure. Every paragraph has sentences that vary in length. Writer makes no errors in grammar or spelling that distract the reader from the content. All supportive facts are reported accurately.
Paragraph for item 3	Sentences lack structure and appear incomplete or rambling. Sentences rarely vary in length. Writer makes more than 4 errors in grammar or spelling that distract the reader from the content. NO facts are reported OR most are inaccurately reported.	Most sentences are well-constructed but have a similar structure. Some sentences vary in length. Writer makes 3-4 errors in grammar or spelling that distract the reader from the content. Most supportive facts are reported accurately.	Most sentences are well-constructed but have a similar structure. Some sentences vary in length. Writer makes 2 errors in grammar or spelling that distract the reader from the content. Almost all supportive facts are reported accurately.	Most sentences are well-constructed with varied structure. Almost all paragraphs have sentences that vary in length. Writer makes 1 error in grammar or spelling that distract the reader from the content. Almost all supportive facts are reported accurately.	All sentences are well-constructed with varied structure. Every paragraph has sentences that vary in length. Writer makes no errors in grammar or spelling that distract the reader from the content. All supportive facts are reported accurately.
Table and Graph	The table shows numeric data related to the topic. The chart has been edited to reflect the data in the table.	The table shows numeric data related to the topic. The chart has been edited to reflect the data in the table. Student choose a color scheme that matches the rest of the document. Purpose/content of the table is not clear from the title.	The table shows numeric data related to the topic. The chart has been edited to reflect the data in the table. Student choose a color scheme that matches the rest of the document. There is a legend for the chart that describes the data (not series 1, ...) Title tells the purpose/content of the table, but is not located at the top of the table.	The table shows numeric data related to the topic. The chart has been edited to reflect the data in the table. Student choose a color scheme that matches the rest of the document. There is a legend for the chart that describes the data (not series 1, ...). Title tells the purpose/content of the table and is printed at the top of the table.	The table shows numeric data related to the topic. The chart has been edited to reflect the data in the table. Student choose a color scheme that matches the rest of the document. There is a legend for the chart that describes the data (not series 1, ...). Title tells the purpose/content of the table, is clearly distinguishable as the title (e.g. larger letters, underlined, etc.), and is printed at the top of the table.
Conclusion Paragraph	There is no clear conclusion, the paper just ends.	The conclusion is recognizable, but does not tie up several loose ends.	The conclusion is recognizable and ties up almost all the loose ends.	The conclusion is strong and leaves the reader with a feeling that they understand what the writer is "getting at."	The conclusion is strong and leaves the reader with a feeling that they understand what the writer is "getting at." It has a call to action statement.
Putting Paper together with header, footer, watermark, color schemes, and font styles.	There is a header and footer some of the pages. There is a watermark on every page. The colors and font size/style change throughout the document.	There is a header and footer every page. There is a watermark on every page. The colors and font size/style match by section through the document.	The paper has the appearance that one person created it all. There is a header and footer every page. There is a watermark on every page that goes with the theme of the proposal. The colors and font size/style match through most of the document.	The paper has the appearance that one person created it all. There is a header and footer on the second and every page after that. There is a watermark on every page that goes with the theme of the proposal. The colors and font size/style match through most of the document.	The paper has the appearance that one person created it all. There is a header and footer on the second and every page after that. There is a watermark on every page that goes with the theme of the proposal. The colors and font size/style match through the entire document.

Edit your Cover Letter into a form letter with mailing labels

Objectives

The Student Will Be Able To (TSWBAT):

   * Use the Mail Merge Wizard to create a form letter
   * Use a letter template to help format the form letter
   * Create and edit a data source (data tied to an access database)
   * Insert and edit merge fields
   * Use an IF statement in a document
   * Merge and Print form letters
   * Address and print mailing labels
   * Use reviewing features to peer edit a document
   * Merge reviews into a document

Vocabulary

• Business Form Letter - Letter used by a business to announce sales to customers, confirm a sale, or communicate with employees.
• Personal Form Letter - Cover letter for a job, invitations to participate in a sweepstakes give away, or a holiday letter.
• Merging - combining two or more objects together. In this case it is combining a data source with the contents of a main document.
• Main Document - This is the text that does not change and the place holders for the information that will be pulled from the data source.
• Data Source - A database that holds records of data that will be placed into the document, such as names, addresses, job titles, ...
• Mail Merge - To take the main document and a data source and combine them into a set of similar documents.
• Template - A document that contains text and formatting that is similar for all documents of this type, such as with letters.
• Placeholder Text - Text that you select and replace to personalize a document. <<First Name>>, or <<City>>
• Modified Block Style Letter - The date line, complimentary close, and signature blocks are slightly to the right of center and all the rest is flush with the left margin.
• Letter Head - Company Name and Logo at the top of your document, or your personal information - It could just be a graphical design.
• Record - Each row in a data source. Each record contains one set of related data - the information for Mr. Smith
• Header Record - The first row in a data source, this is the label for each field, or the name of each column.
• Data Record - Each row below the header is the text that will vary from each copy of the merged document. Row 2 could be Mr. Smith's information and row 3 could be Mrs. Doe's.
• Data Field - Each column is a data field and represents a group of similar data - the street address
• Field Name - Each column of data is identified by a name such as LAST NAME
• If Field - A field in a document that allows for a condition. If the statement is true use A and if the statement is false use B.
• Condition - This is the statement that is being tested in an IF Field. If Money Owed > 0, State money due, Else State Paid In Full.
• Field Results - The value that will be displayed after Word evaluates the If field instructions
• Field Codes - The instructions of the IF field

Lesson

People are more likely to open and read a letter that is addressed to them, and their name is spelled correctly. Typing a group of letters that are identical except for a few minor changes can be very time consuming. For this we use a form letter. As students this will be very useful for you to use to send out cover letters to each prospective employer or college.

Use a Template to Format your Letter

For this sample we are going to create a personal form letter thanking people for a gift.

• Format the page
  • Open Word to a blank document
  • Go to the HOME Tab
  • Click on the Show formatting marks button (the backwards P)
  • Go to the View tab
  • Click on Page width
• Use a Template
  • Click on the Office Button
  • Click on NEW
  • Click on Letters
  • Double Click on Thank You Letter for Personal Gift

This is a Personal letter in block style. Everything is lined up on the left side.

Create a Letter Head

• Click in font of the place holder for your name
• Press Enter twice
• Fill in the place holders for
  • Name
  • Address
  • City, State, and zip code

Create and Format an AutoShape

• Go to the Insert Tab
• Go to the Illustrations Group
• Click on the Shapes Drop Down
• Click on a banner style you like that goes across, not the first one that goes down.
• Click in the header area of your document and drag down one inch and over 6 inches
• Right Click on the banner
• Click on Format AutoShape
  • Change the fill color
  • Change the line color
  • Change the line wight to 2pt

Add Text to an AutoShape

• Right Click on the banner
• Click on Add Text
• Type in THANK YOU
• Go to the HOME Tab
• Center the Text
• Use the Grow Text button to find a "good" Size
• Change the font

Edit the Date field

• Click on the Data Field
• Click on the HOME tab
• Go to the Paragraph Group
• Click on the Dialog Box Launcher
• Change the spacing before to 6pt

Create a Data Source

• Click on the MAILINGS tab
• Click on Start Mail Merge drop down
• Click on Step by Step Mail Merge Wizard
• Step 1
  • Make sure the Letters radio button is selected at the top of the task pane
  • Click on Next: Starting Document at the bottom of the task pane
• Step 2
  • Make sure Use The Current Document radio button is selected
  • Click on Next: Select Recipients at the bottom of the task pane
• Step 3
  • Click on the Type a New List radio button at the top of the task pane
  • click on CREATE in the middle of the task pane
  • click on Customize Columns at the bottom of the New Address List Window
  • click on Title and click on Rename
  • Change to NickName
  • Click on Company Name and click on Delete
  • Click on Country or Region and Click on Rename
  • Change the name to Family or Friend
  • click on Home Phone and Click on Delete
  • Click on Work Phone and click on Delete
  • Click on ADD
  • Type in Present
  • Click on OK
  • Type in five friends and family members - make some of each
  • Click on OK
  • Specify where to save the list- put it in your HOME storage spot
  • Leave all recipients chosen and click on OK
  • Click on NEXT: Write Your Letter at the bottom of the task pane

Compose the Main Document

• Step 4

Edit Data Line

• Click in the date line
• Right Click the Date
• Click on Edit Field
• Choose the format you would like the date to display with
• Click OK

Edit the Recipient Address

• Select the Recipient Name down to the City, State and Zip
• Delete them
• Go to the Task Pane
• Click Address Block
• Go to the bottom of the document and place your name there as well.

Edit the Greeting Line

• Change the DEAR to Hi, make sure you have a space after it.
• Select the Recipient Name and delete it
• Go to the Mailings Tab
• Go to the Write&Insert fields Group
• Click on Insert Merge Field drop down
• Choose Nick Name

Preview the Letter

• Go to the Mailings Tab
• Go to the Preview Results Group
• Click on Preview Results button and view the document
• You can scroll through the documents with the arrows in the Preview Results group
• When you are done click the Preview Results button again

Type the Body of the Letter

• Read the document
• Edit the second sentence to fit your personality

Insert a Merge Field into the Main Document

• Find the word Flowers in the body of the letter
• Delete it
• Click on Insert Merge Field drop Down
• Click on Present

Insert an IF condition into a form

• Go to the closing of the letter
• Select the word Warmly
• Delete the word Warmly
• Go to the Mailings Tab
• Go to the Write&Insert Fields Group
• click on Rules drop Down
• Click on If..Then..Else
• Fill in the form
  • Field Name - NickName
  • Comparison - Equal to
  • Compare To - Family
  • Insert this Text - It is nice to have you in our family
  • Otherwise insert this text - Warmly
  • Click on OK
• Preview the final document and scroll through watching the changes

Display Field Codes or Field Results

• Click on the closing
• Right Click
• Click on Toggle Field Codes
• Do this again to turn them back off and see the actual text

Print Field Codes

• Click the Office Button
• Click PRINT
• Click Options
• Go to the Advanced group on the left
• Scroll down Show Document Content section
• Click the check box for Show Field codes Instead of the Values

Merge and Print

• Step 5
  • Click NEXT: Preview your letters in the task pane
  • From this pane you can limit what recipients you want to print a letter for by clicking on the Exclude this Recipient button in the middle of the task pane.
• Step 6
  • Click NEXT: Complete the Merge

Merge the Form Letter to Print

• Click PRINT from the middle of the task pane

Merge the Form Letter to E-Mail

• Go to the MAILINGS tab
• Go to the Finish group
• Click on the Finish&Merge drop down
• Click on Sent Email Message
• Fill in the form - NOTE this only works if you have Microsoft OUTLOOK set up.
  • To: leave this email_address
  • Subject Line - fill in what you want the subject of your email to be
  • mail Format - choose if you want this as part of the email, or as an attachment
  • Choose what records to sent
  • click OK

Print Mailing Labels

• Start a new blank document
• Go to Mailings Tab.
• Go to Start Mail Merge Group
• Click on Start Mail Merge Drop Down
• Click on Step By Step Mail Merge Wizard
• Step 1 - Select Document Type
  • Click on Labels
  • Click Next: Starting document
• Step 2 - Select Starting Document
  • click on Label Options
  • Find the type of label you are using from your box of labels - Standard is Avery 5160
  • click OK
  • Click NEXT: Select Recipients
• Step 3 -Select Recipients
  • click Use an Existing List radio button at the top of the task pane
  • click Browse in the middle of the task pane
  • Find the list of recipients you created for the thank you letter
  • Click NEXT: Arrange your labels
• Step 4 - Arrange your labels
  • Click on Address Block at the top of the task pane
  • Click on OK
  • Click on Update all Labels in the middle of the task pane
  • click on NEXT: Preview your labels at the bottom of the task pane
• Step 5 - Preview your labels
  • Click on NEXT: Complete the merge
• Step 6 - Complete the merge
  • Print the mailing labels
  • Peel the mailing labels off and put them on envelopes

Print Envelopes

• Open a new blank document
• Start a mail merge wizard
• Choose Envelopes
• Follow through the wizard
• When you go to print carefully place your envelopes in the printer

Create return mailing labels

• Open a new blank document
• Go to the Mailings tab
• Go to the Create group
• Click on the Labels button
• Fill in the Envelopes and Labels dialog box
  • Fill in your name and address in the Address block at the top of the window
  • Make sure the Full page of the same label button is checked
  • Place your mailing labels in the printer
  • Click Print - you never get to see them they just go straight to the printer
  • If you want to see them click new document instead of print

Peer Reviewing a Document

• Save the document in a way that another student can open it, or E-mail it to them.
• Open the document and read it
• Review the Document
  • Go to the Review Tab
  • When you make a change it should place the change off to the side
  • If you want to make a comment, click the New Comment button in the Comments Group
  • When you are done with your edits save the document and get it back to the author
• Go over the edits
  • Open the edited document
  • Read the edits
  • Right click on the edit and either accept or reject the edits

Project

1. Use the internet to find a Cover Letter for a college application, or a group of jobs that fit your interest areas from our job surveys
2. Find recipients
   • Use Monster.com to find openings in your career area and yellowpages.com to find their addresses
   • Use the Internet to find address to universities that interest you
3. Use the Mail Merge Wizard to create a form cover letter
4. Requirements:
   • Five letters in proper letter format - use a template
     • Letter head
     • Date Line
     • Return Address Block
     • Recipient Address Block
     • Greeting Line
     • Body of Letter
     • Closing
     • Signature Area
   • Letters tied to a data source
   • Complete addresses for each recipient
   • One merge field within the body of the letter
     • In the one to a university you might want it to by your major
     • In the one for a career it would be the position you are applying for
5. Formatted letter head area - be creative
6. Peer review someone else's document and have someone else peer review yours with the use of the reviewing tab.

Rubric

Form Letter Rubric

Standards	1 point	2 points	3 points	4 points	5 points
Letter Format	Missing 4 parts or more	Missing 3 parts	Missing 2 parts	Missing 1 part	Document contains all parts of a standard letter: date line, return address, recipient address, greeting line, body, closing, and signature.
Body of letter	Many sentence fragments or run-on sentences OR paragraphing needs lots of work.	Most sentences are complete and well-constructed. Paragraphing needs some work.	All sentences are complete and well-constructed (no fragments, no run-ons). Paragraphing is generally done well.	Sentences and paragraphs are complete, well-constructed and of varied structure.	Sentences and paragraphs are complete, well-constructed and of varied structure. Hire this person or accept them to the college.
Use of Merged Fields	Fields that are changing are set up as merged fields to the data source.	Students have developed a plan for the information into a final letter. Fields that are changing are set up as merged fields to the data source.	Students have developed a plan for organizing the information into a final letter. Most fields that are changing are set up as merged fields to the data source.	Students have developed a clear plan for organizing the information into a final letter. Most fields that are changing are set up as merged fields to the data source.	Students have developed a clear plan for organizing the information into a final letter. All fields that are changing are set up as merged fields to the data source.
Data Source used for letter	A data source is used for the letter.	All fields are created for the data source that are needed in the letter.	All fields are created for the data source that are needed in the letter. New fields are added.	All fields are created for the data source that are needed in the letter. New fields are added. Fields have a descriptive name.	All fields are created for the data source that are needed in the letter. Extra fields are deleted, and new fields are added. Fields have a descriptive name.
Letter Head	The letter head is messy or poorly designed and is distracting.	The letter head is messy or has typos.	The letter head is acceptable in terms of design, layout, and neatness. Title is creative.	The letter head is exceptionally attractive in terms of design, layout, and neatness. Text creative.	The letter head is attractive in terms of design, layout, and neatness. Text is quite creative.
Peer Review	Rarely provides useful ideas. May refuse to participate. Provides work that needs to be checked/redone by others to ensure quality.	Rarely provides useful ideas. Provides work that usually needs to be checked/redone by others to ensure quality.	Sometimes provides useful ideas. A satisfactory group member who does what is required.Provides work that occasionally needs to be checked/redone by other group members to ensure quality.	Usually provides useful ideas. A strong group member who tries hard! Provides high quality work.	Routinely provides useful ideas and contributes a lot of effort. Provides work of the highest quality.
Five Contacts	Found 3 or less contacts for the letter to be sent to. Has addresses and has filled most fields so that the letter could be delivered.	Found 4 contacts for the letter to be sent to. Has addresses and has filled most fields so that the letter could be delivered.	Found 4 contacts for the letter to be sent to. Has complete addresses and has filled in all fields so that the letter could be delivered.	Found 5 contacts for the letter to be sent to. Has addresses and has filled most fields so that the letter could be delivered.	Found 5 contacts for the letter to be sent to. Has complete addresses and has filled in all fields so that the letter could be delivered.
Mailing Labels	Created 3 or less mailing labels for the letter to be sent to. Has addresses and has filled most fields so that the letter could be delivered.	Created 4 mailing labels for the letter to be sent to. Has addresses and has filled most fields so that the letter could be delivered.	Created 4 mailing labels for the letter to be sent to. Has complete addresses and has filled in all fields so that the letter could be delivered.	Created 5 mailing labels for the letter to be sent to. Has addresses and has filled most fields so that the letter could be delivered.	Created 5 mailing labels for the letter to be sent to. Has complete addresses and has filled in all fields so that the letter could be delivered.

Create a Newsletter

Vocabulary

• Nameplate - The top portion of the first page of the document, contains the name of the newsletter and issue information.
• Issue Information Line - A line of text across the top of a newsletter that give what volume, month/year, or issue this newsletter is for.
• Ruling Lines - Horizontal lines in a document. You have seen this as in college or wide ruled paper.
• Vertical Rule - Lines you place in a document that go vertically
• Subhead - Names given to different parts of a document such as UPCOMING EVENTS, or NEXT ISSUE
• Pull-quote - Text that is pulled from the main part of a document and placed in a text box or other graphical emphasis to draw a readers attention.
• Wrap Around Text - Text that wraps around an object such as a graphic or text box
• Run-Around - The space that is between the text and the object
• Gradient - Using two colors, or two shades of the same hue and having them blend into one another. Example of turning from blue to green.
• Inline Object - An object such as a graphic that is part of a paragraph. The text is inline with the graphic and a large space is left.
• Floating Object - An object such as a graphic or text box that can be positioned anywhere on a page, in front of text, or behind it.
• Justified - When a paragraph is lined up at the right and left margin and spaces are added between words as needed. This is most often seen on the edges of a newspaper, or magazine column.
• Drop Cap - The first character of a paragraph that is larger and Capital. It usually spans two or three rows. It can also be of a different font.
• Column Break - A break that tells word that the document in that column will continue on the next page. This is generally used with a next page section break.
• Source Object - The object that is being copied
• Destination Object - The object being pasted
• Paste Special Command - Allows you to link the source and destination objects together. If the source object changes the destination object will change as well.
• Diagram - A graphic display of information such as an organizational chart, pyramid, radial chart, target chart, or Venn diagram

Lesson

Set Margins

• Go to the Page Layout Tab
• Go to the Page Setup Group
• Click on the Margins drop down
• Click on NARROW 0.5 for top, bottom, left and right

Create a Nameplate

Using Word Art

• Create the title
  • Go to the Insert tab
  • Go to the Text group
  • Click on the WordArt drop down
  • Click on one of the straight types in the gallery
  • Type in Mouse Madness for the name of the newsletter
  • Pick a style of font
  • Click on B for bold
  • Click OK
• Format the title
  • Right Click on the WordArt
  • Click on the Fill Effects button
  • Play with the colors, shading styles, and variants
  • Click on OK
  • Click on the Size tab
  • Change the Rotation
  • Click ok and look at your masterpiece
• Change the shape of the WordArt
  • Go to the WordArt Tools
  • Go to the Format Tab
  • Go to the WordArt Styles
  • Click on the Change Shape Drop Down
  • Hover over the shapes in the gallary and choose one you like

Adding Ruling Lines

Click on the WordArt title you just created to select it.

• Go to the HOME tab
• Go to the Paragraph group
• Click on the border drop down
• Click on the Borders and Shading option
• Choose Style of border you want
• Choose the color of border you want
• Choose the thickness of border you want
• Click on the edges of the preview diagram to choose where the border should be, for this part we want it above the WordArt.

Adding an Issue Information Line

• Double click on Equation
• type: Monthly Newsletter

Setting Tab Stops

• Go to the HOME tab
• Go to the Paragraph group and click on the dialog box launcher
• Click on the TAB button in the lower left of the dialog box
• In the Tab Stop Position type: 7.5
• Click on the radio button for alignment - RIGHT
• Make sure the radio button for Leader is NONE
• Click on SET
• Click on OK
• Press Tab and type: Vol. 1

Placing a Symbol in a document

• Go to the INSERT Tab
• Go to the SYMBOLS group
• Click on the Symbols drop down
• Click on More Symbols
• Scroll until you find a symbol you want to place for a spacer between your volume and issue numbers.
• Click on the Symbol
• Click on Insert
• click on CLOSE

Finish typing the volume and issue, type: Issue 1

Working with a Floating Graphic

• Go to the INSERT tab
• Click on the Illustrations Group
• Click on Clip Art
• Search for a computer mouse that has a tail
• Click on the mouse you want from the gallery of clip art
• Use the resize handles to make it a smaller size and fit with your Word Art
• Right Click the mouse
• Click on Text Wrapping
• Click on Behind Text
• Click and drag the mouse so that the cable looks like it is plugged into one of the letters of the WordArt

Place a Bottom Border

• Select the Monthly Newsletter Issue Line
• Go to the HOME tab
• Go to the Paragraph group
• Click on the border dropdown
• Click on Borders and Shading
• Use the same type of border from above the WordArt but place it below the diagram

Format the Main Article of a Newsletter

Add an Article Title

• Click twice on the line after the bottom border
• type: What Is A Virus
• Press Enter

Add a Continuous Section Break

• Go to the PAGE LAYOUT tab
• Go to the PAGE SETUP group
• Click on the Breaks drop down
• Go to the Section Breaks group
• Click on Continuous

Place the text of the article

• Copy the article below:

A virus is a malicious computer program or programming code that replicates by infecting files, installed software or removable media. A virus usually carries a destructive payload, which varies depending on the virus author’s intentions. A typical virus infects, corrupts or deletes files and folders, damages the system, drops other dangerous parasites, steals or discloses user sensitive information. Extremely dangerous viruses can also wipe out all the data from hard disks and even severely damage certain computer hardware devices.

By replication approaches viruses are divided into three main categories:

1. parasites called file infectors are designed to propagate by infecting or corrupting various files;
2. threats known as boot record infectors spread through removable media containing infected executable code and insert themselves into the master boot record (MBR) on hard disks;
3. widely spread macro viruses affect certain applications such as Microsoft Word or Microsoft Excel and infect documents that can contain macros.

Some viruses do not belong to any of these categories, as they combine features and functions specific to more than one virus type. Such threats, sometimes called hybrid viruses, can infect both files and master boot record and replicate by attaching malicious code to user documents. These parasites are very difficult to completely get rid of, as they usually consist of several components, which automatically reinstall each other after the user have found and removed few of them.

Many viruses have extra features, which allow them to escape detection by antivirus software. Such threats use several approaches to stay hidden. Some of them, known as stealth viruses, monitor antivirus software activity and intercept its requests to the operating system. When the antivirus attempts to check an infected file, the virus immediately passes the original clean variant of that file, so the antivirus is unable to find any malicious code in it. Other parasites, called polymorphic viruses, are able to mutate continuously modifying their code, so that two files infected by the same pest have no common parts. Polymorphic viruses are extremely difficult to detect.

Change the number of columns

• Select the text of the articles
• Go to the PAGE LAYOUT tab
• Go to the PAGE SETUP group
• Click on the COLUMNS drop down
• Click on THREE

Justify a Paragraph

• Select all the text of the article
• Go to the HOME tab
• Go to the PARAGRAPH group
• Click on the JUSTIFY button

Format a Drop Cap

• Select the A from the first letter of the first paragraph
• Go to the Insert tab
• Go to the Text group
• Click on the Drop Cap tab
• Select Dropped
• Change the font style and color from the HOME tab

Insert a Column Break

• Send the rest of the document to the next page
  • Click in front of "Many", the first word of the last paragraph of column two.
  • Go to the PAGE LAYOUT tab
  • Go to the PAGE SETUP group
  • Click on the BREAKS drop down
  • Go to the SECTIONS group
  • Click on NEXT PAGE
• Go to the next column
  • Go to the PAGE LAYOUT tab
  • Go to the PAGE SETUP group
  • Click on the BREAKS drop down
  • Go to the PAGE BREAK group
  • Click on COLUMN

Insert a file into a column

• Copy the following document into the third column

Next Meeting:
Feb 20th in the Business Lab
3:30 – 4:30

Special Meeting:
Feb 15 in the Multi-Purpose Room

A light snack will be provided, and a guest speaker will come to take about and answer all your questions to do with what happens now that I have a virus?
If you plan on attending please send an email message to tec_club@gmail.com. The cost of the session is $10 per person to be paid at the door.

Next issue
What a virus does.
Examples of viruses

Project

Pick a category for a newsletter: Vet Office, City Council, Horse Training, Snowboarding, ...
The newsletter must contain:

• two pages
• three columns
• pull quote
• diagram/organizational chart
• page border
• separate column on front page for current or upcoming events
• at least one article that fills the text part of the document, use more if you need to fill up the space

Rubric

Newsletter Rubric

Standards	1 point	2 points	3 points	4 points	5 points
Creates a Nameplate with an appropriate title for category	Is missing four or more items	Is missing three items	Is missing two items	Is missing one items	Includes newsletter name, Monthly Newsletter, Vol #, Issue #, a graphic, and is visually appealing
Creates a heading for each article and matches fonts	Four sections do not match fonts, or is missing a heading	Three sections do not match fonts, or is missing a heading	Two sections do not match fonts, or is missing a heading	One section does not match fonts, or is missing a heading	All fonts match for regular text and for headings. Each article in the newsletter has a heading, and each section in the current events section on the first page has a heading.
Creates a Pull Quote	Finds a quote in one of the articles. Creates a text box for the quote.	Finds a quote in one of the articles. Creates a text box for the quote. Formats the text box to match the style or format of the rest of the newsletter.	Finds a quote in one of the articles. Creates a text box for the quote. Formats the text box to match the style and format of the rest of the newsletter.	Finds a quote in one of the articles that would draw the readers attention. Creates a text box for the quote. Formats the text box to match the style and format of the rest of the newsletter.	Finds a quote in one of the articles that would draw and catch the readers attention. Creates a text box for the quote. Formats the text box to match the style and format of the rest of the newsletter.
Formatted in three columns with current events section on front page and a page border	Creates a newsletter with three columns.	Creates a newsletter with three columns and has a page border.	Name plate is one column. Creates the newsletter with three columns. All the pages have a page border.	Name plate is one column on both pages. Creates the newsletter with three columns but all of the breaks might not be correct. All the pages have a page border.	Name Plate is one column on both pages. Creates section, page, and column breaks correctly to format the newsletter with three columns. The current events section is on the front page. Both pages have a page border that matches the style and format of the rest of the newsletter.
Creates a heading section on page 2	Page two has a name plate section with some of the name of the newsletter, monthly newsletter, issue # and volume #.	Page two has a name plate section with most of the name of the newsletter, monthly newsletter, issue # and volume #.	Page two has a name plate section with the name of the newsletter, monthly newsletter, issue # and volume #.	Page two has a name plate section with the name of the newsletter, monthly newsletter, issue # and volume #. Formatting matches page one's name plate.	Page two has a name plate section with the name of the newsletter, monthly newsletter, issue # and volume #. All formatting matches page one's name plate.
Creates a diagram or organizational chart	Chart is used to bring attention to some aspect of your newsletter. The formatting fits the rest of the newsletter.	Chart is used to bring attention to some aspect of your newsletter. The formatting fits the rest of the newsletter. The fonts are big enough to read easily.	Chart is used to bring attention to some aspect of your newsletter. The formatting fits the rest of the newsletter. The fonts are big enough to read easily. Everything is spelled correctly.	Chart is used to bring attention to some aspect that your article is discussing. The formatting fits the rest of the newsletter. The fonts are big enough to read easily. Everything is spelled correctly.	Chart is used to bring attention to some important aspect that your article is discussing. The formatting fits the rest of the newsletter. The fonts are big enough to read easily. Everything is spelled correctly.
Peer Editing - Has someone peer edit the document and accepts or denies changes as necessary	Rarely provides useful ideas. May refuse to participate. Provides work that needs to be checked/redone by others to ensure quality.	Rarely provides useful ideas. Provides work that usually needs to be checked/redone by others to ensure quality.	Sometimes provides useful ideas. A satisfactory group member who does what is required.Provides work that occasionally needs to be checked/redone by other group members to ensure quality.	Usually provides useful ideas. A strong group member who tries hard! Provides high quality work.	Routinely provides useful ideas and contributes a lot of effort. Provides work of the highest quality.

Excel Terminology

Column A selection

• Workbook vs. Worksheet – when you open Excel, a new file is created called Book 1 (until you name it differently). It is called “Book” because it is a Workbook that is initially made up of three Worksheets. Think of a three ring binder with three sheets of paper in it. As with a binder, you can add sheets to your Workbook (Insert > Worksheet), delete them, and re-arrange them. Double click on the Worksheet title to rename it.
• Row – rows travel horizontally and are numbered.
• Column – columns travel vertically and are assigned letters.
• Cell – cells are the basic rectangular building blocks of a spreadsheet. They are assigned an address, generally referred to as a cell reference, according to their column and row (e.g. the cell in column B at row 3 is referenced as cell B3).
• Formula – a mathematical formula used to calculate a result based on data from one or more other cells. Often they consist of some combination of the standard mathematical operators ( +, -, *, /) (e.g.: =(A1+A5)/B13). But they may also include functions (see below). When you type a formula into a cell, that cell will generally display the result obtained by the formula, rather than the formula itself.
• Functions – pre-written formulae that perform common (and not so common) calculations, such as summation and averaging. You can combine many functions and operators in a single formula to obtain more complex results (e.g.: =SUM(A1:A13)).

Navigating Cells

To select	Do this (If you are left-handed use Right click in place of Left
a single cell	Left click on it
a range of adjacent cells	Drag from the first cell to the last cell, or click on the first cell, hold the Shift key and click on the last cell (scrolling if necessary)
non-adjacent cells or ranges	Hold CTRL (Windows) or Command (Mac) key and click or drag
an entire row or column	Left click on the row or column heading
all cells	Left click on the blank header in the upper left corner
the next cell to the right	Use the Tab key
the next cell down the column	Use the Enter key
the A1 Home cell	Press Ctrl + Home keys together
the last cell in a sheet	Press Ctrl + End keys together
the cells around the active cell	Press Ctrl + Shift + 8 keys together (Select Region)

Working with Data

Editing, Moving, and Copying Data

To edit data, select the cell(s) to edit then either type data in the active cell or click the I-beam cursor in the cell to edit.

To move or copy data, select the cell or cells that you want to copy or move. From the toolbar or the Edit menu choose Copy or Paste.

To transfer the formatting from one cell to another cell, use Paste Special. For example, after using a formula, you may want to copy the numeric result rather than the formula. To do this, simply click on the cell that you want to copy and select Edit > Copy. Then, click on the cell where you would like the number pasted and click Edit > Paste Special. A box will pop up with a number of different options. Under the Paste section, click on the bubble that says Values and then click OK.

Inserting, Deleting, and Clearing Data

In Excel, always select then do. Select the cell you want the data to go into and then enter the data (or the formula).

• To delete or clear data, select the cell or the row or column and press Delete on your keyboard.
• To delete a whole row or column, click on the row number or column letter and Edit > Delete.

Using Autofill

Autofill allows you to quickly fill data in a series (e.g. months, days of the week, or a numeric series) into adjacent cells.

Using Autofill with a text series

To use the Autofill function with text, type in the first word of the series (e.g. January), rest your mouse on the bottom right corner of that cell, and you should see the Autofill cross (right).

Click and drag the series down or across the appropriate number of cells.

Excel knows how to Autofill months (January or Jan) and days of the week (Monday or Mon). You can teach Excel to Autofill other text series by going to the Tools menu, choosing Options, and then clicking on Custom Lists and defining your own text series.

Using Autofill with a numeric series

You can also use Autofill to quickly enter numeric patterns (e.g. 1 2 3 or 10 20 30) into adjacent cells.

To use Autofill with numeric patterns, enter the first two values in the series, one value in one cell and the next in the cell immediately below or to the right. Now select (highlight) both cells, release your mouse button, then rest your cursor on the bottom right corner of the selected area so you see the Autofill cross.

When you see the Autofill cross, click and drag down or across the cells you want to fill, then release the mouse button. Double-clicking the Autofill cross will result in an automatic filling of the cells below until it reaches a blank row.

Autofill becomes important again when constructing formulas.

Controlling Your View of the Data

Auto Size: You may have noticed that sometimes data extends beyond the width of the column. In order to be able to see all of the data, you must widen the column. To do this, go to the header row and place your mouse in between the columns (e.g. on the line that separates the letters A and B). You will notice that your mouse becomes a black double arrow. If you double click on this arrow, the column will automatically size itself to fit the longest string of data in a cell. However, this does not adjust automatically so you may have to Auto Size again after adding more data.

Freeze Panes: Freeze Panes is a useful feature when you are working with a large document that has many rows. By freezing a certain row (usually the header), you make that row visible wherever you are in the document. For example, if you have a document with 100 rows, you can’t see the header row when you’re at cell A100. To solve that problem, click on the row below the one that you would like to freeze. To freeze the header row, click on the cell in the second row and first column. Then go to Window > Freeze Panes. [It’s important to note you can freeze columns as well as rows. If you just click at an arbitrary point in the second row, not only will you freeze the top row, you’ll also freeze all columns to the left of the cell you’ve selected.]

Reveal Formulas: If you want to see and/or print all of the formulas in a spreadsheet (as opposed to the values) there is a really handy shortcut. Simply press Ctrl + ~. To return the view to displaying values, press Ctrl + ~ once again.

Sorting Data

You can order your data from the Sort window in ascending or descending order as well as based on multiple header values.

Sorting data is simply a way of automatically re-ordering rows on a spreadsheet to put them in a more useful order. For instance, you might sort an address book alphabetically by last name, or a list of items you’d like to buy from most expensive to least expensive.

Start by selecting the data you want to sort. It’s important to select all the columns in the data, not just the column you want to sort by. For instance, if you have a list of items in one column, with the prices in the next column, you would select both columns before running the sort. If your sheet has a “header row” at the top with labels for your columns (like “name,” or “price”) it’s a good idea to include that row in your selection as well, as you’ll see in a moment.

Once you have your data selected, click Data > Sort. You’ll see the sort window, pictured at right. If your data has a header row, be sure the “header row” bubble is filled in at the bottom. Excel will then use your labels in the “Sort by” boxes instead of the usual (unhelpful) “Column A,” “Column B,” etc. Now you can use the “Sort by” box to select the column you’d like to sort the data by (if that column contains text, it will sort alphabetically; if the data is numeric it is sorted in number order). The two “Then by” boxes select what column to use next if there is a tie in the “Sort by” column.

But what if you want to sort by more than three columns? Excel only allows sorting by (up to) three columns at a time, but fortunately preserves the logic of the previous sorts if a new sort is applied, with the most recent sort setting the precedent. Let's say that we have a spreadsheet which contains 20 columns and we want to sort it by 10 columns, with Sort Column 1 being the broadest sort and each succeeding Sort Column organizing the data strictly within the subsets defined by its predecessor. Here are our 10 Sort Columns, presented in logical order:

    Apple
    Banana
    Clementine
    Dangleberry
    Elderberry
    Fig
    Grape
    Hazelnut
    Ichang
    Jicama

The first step is to divide this set of 10 into groups of three, counting from the top:

    Apple
    Banana
    Clementine
    ----------------
    Dangleberry
    Elderberry
    Fig
    ----------------
    Grape
    Hazelnut
    Ichang
    ----------------
    Jicama

We're left with 4 groups. In order for the final sorting to reflect a logical flow from Apple through Jicama, we need to work backwards. The first time we select the Data > Sort option, we will enter the LAST group starting from its TOPMOST value:

    Sort by: Jicama
    Then by: leave blank
    Then by: leave blank
    ---------------------------
    Click ""OK""

Next, begin the with the 2nd-to-last group:

    Sort by: Grape
    Then by: Hazelnut
    Then by: Ichang

Next, begin the with the 3rd-to-last group:

    Sort by: Dangleberry
    Then by: Elderberry
    Then by: Fig

And finally:

    Sort by: Apple
    Then by: Banana
    Then by: Clementine

This type of sort is frequently used to present detailed hierarchies in a comprehensible manner.

Alternatively, you can use formulas to CONCATENATE (join) your groups together into a single field. For example, if you had Index1 in column A, Index2 in column B, Index3 in column C, and Index4 in column D, you could create a field called "Sort" in column E using this formula: =A2&B2&C2&D2

Filtering Data

Another way to organize data is to filter it. A filter only displays data that meet a certain criteria, such as all records for a certain day. To do this, go to Data > Filter > AutoFilter. A dropdown arrow will appear in the header cell of each column of your worksheet. Click on the arrow and select the criteria that you would like to use as a filter (e.g. April 10th). The worksheet will now display only the records from that day. You can also use the AutoFilter tool to display the top or bottom 10 numbers in a column, only records with blank spaces in that particular column, records that have a value greater than a certain number in the column that you are filtering, etc. These options are all available under the dropdown arrow that you will see after going to Data > Filter > AutoFilter.

An alternative to filtering is to use IF/THEN functions/formulas to select records or groups of records. For example, if column E contains information on "Country", you could write a formula such as =IF(E1="Mexico","Spanish","English") to fill column F with information on "Language".

Working with Formulas

Select the cell that the formula result is going to be displayed in. The formula can be constructed in the formula bar. Always need to put the = sign before a formula. That is how Excel recognizes what you are entering as a formula.

Sign	Operation	Example
+	Addition	=A1+B1+C1+D1
-	Subtraction	=A1-A2
*	Multiplication	=C4*C5
/	Division	=C4/D4
(…)	Combination	=A1*(B1+C1)

Click on the green checkmark to enter the formula, the red x to cancel the formula.

Autofill

Autofill helps you fill in formulas quickly once you have constructed one in a cell. In order to Autofill, select the cell with the formula. Place your cursor so the small black cross appears in the lower right corner of the cell (+). Once that cursor is visible, simply drag your formula down the column (or across the row as the case may be). Autofill will change the cell references accordingly.

Note that cell references can also change automatically when you copy and paste a formula using the clipboard, unless you use an absolute cell reference (see below).

E.g.: If the formula in A3 is =A1+A2, when you drag that formula over to B3 then the formula becomes =B1+B2.

Cell References

• Relative Cell References: cell references that change when the formula is autofilled into different cells, as in the example above.
• Absolute Cell References: if you don't want a certain part of your cell reference to change when you copy the formula to a new cell, you need to put a $ in front of the row and/or column part of the reference (see example below).

E.g.: If the formula in A3 is =$A$1+A2, when you drag that formula over to B3 the formula becomes =$A$1+B2.

To quickly add the dollar symbols use the F4 key, which toggles four ways. First press gives both row and column absolute, $A$1, second press gives row absolute, A$1, third press gives column absolute, $A1 and finally fourth press returns to fully relative, A1.

AutoSum

The AutoSum button on the standard toolbar allows you to quickly insert the SUM function. Select the cell where you want to put the total and then click on the AutoSum button ( ). Excel will insert the SUM function and take a guess as to what cell range you’d like to sum. Check to make sure the cell range is correct, then press enter to accept the function.

Functions

The formula listed here is: (B4+25)/SUM(D5:F5) . B4 is a cell reference. 25 is a numeric constant. The slash is a division operator. SUM is a function. D5:F5 is a range reference.

Excel has created hundreds of functions that prevent you from having to write out complex or repetitive formulas yourself. The cells that you want to perform the function on are either listed, separated by commas, or included in a range of numbers, indicated by a colon in between the first number in the range and the last number in the range.

Functions can be inserted by using the Insert menu, by clicking on the Paste Function icon on the Standard toolbar, or by typing = in the formula bar and choosing the function from the drop down list on the left.

Functions can also be 'nested'; that is, inserted into larger functions, by using the appropriate amount of brackets. =AVERAGE(SUM(B2:F2), SUM(B3:F3))

The Function Wizard

You can quickly generate valid functions using the function wizard. To open it, click the function wizard button in the formula toolbar ( ). It provides a description of the function you select (in this case AVERAGE), a space for you to enter in the range or numbers (in Number1) and previews the Formula Result. You can select a range from your sheet rather than typing it in manually by clicking on the little button with the red arrow to the right of the Value box.

The function wizard allows you to build custom functions to suit your specialized needs. It also offers many built in functions which you can utilize.

AutoCalculate

Excel will automatically perform calculation on a set of cells that you select and display the results on the status bar. The default calculation is the SUM function, but you can change the calculation by right-clicking (Win) or Command + clicking (Mac) on the AutoCalculate result.

Making Beautiful Charts

Changing the Color Palette

The first problem in creating beautiful charts is the choice of colors. Excel only offers 64 colors, and most of them are hideous.

Fortunately, you can change them, and any changes you make travel with the workbook. This means that when your client opens it up, it's going to have your changed colors in it. It also doesn't affect any other workbooks - their colors will be unchanged.

Here is the standard Excel color palette, with very few light colors, which are important for attractive graphics. Alongside is shown an alternative, with many more light colors, arranged in sequence instead of all jumbled up.

So how do you change them? Click on Tools, Options, then the Color tab. You should see the color palette below.

Click a color you want to change, and then the button labeled Modify on the right hand side.

Either of the two dialogs below will then come up. You'll see you can flick between them by choosing the Standard or Custom tabs.

The Standard colors let you choose from a much larger number of colors by simply clicking on them. This is all you need, most of the time.

Suppose, however, that you like a color but it is little too dark. Select it, then click the Custom tab. You can then use the slider bar at the right of the rainbow in the middle, to make it lighter.

If you are feeling creative, you can play with the numbers at the bottom to create the perfect color.

If you've gone to the trouble of changing colors and you want to copy them into another workbook, or if you want to use the palette I've created for you in this workbook, it's quite easy.

1. Open both workbooks (the one you want to copy from, and the one you want to copy to)
2. Activate the one you want to copy into.
3. Select Tools, Options, and then the Color tab. You'll see a dropdown labeled "Copy colors from". Click this and pick the workbook you want to copy from.
4. All done. Save.

Applying Formatting

Standard Excel charts use strong colors and strong lines. This is probably so they show up clearly in all sorts of situations.

While this approach is understandable, the problem is that it results in charts lacking visual appeal. The best way to correct this is to see the effect of making some changes. Please note that there are many ways to do this, and these changes are not necessarily the best.

The chart should now look like this. Let's change those awful colors next...

The chart should now look something like this…those colors are probably too light, but there's a nice trick we can use to give them some body.

Let's keep it fairly simple, so we'll click the One color button alongside

The chart should now look something like this…the colors have more body

Note: beauty is in the eye of the beholder. You may prefer to use different colors to start with and not go to all this trouble with special effects.

There is no suggestion that you do all this for every chart. But the more you know, the more choices you will have when you build your next chart.

Now, perhaps, some subtleties

Examples

Here is a line chart.

• the lines have been made thicker, with no markers (which is OK as long as it will be printed in color - if it is printed)
• the colors are a little softer
• the legend has been removed & each line has been directly labeled, which makes it easier to read. (You can do this by selecting the last point of each series and showing the series name as the label - when you've selected the point, select Format Data Point, then Data Labels tab, Series Name checkbox).
• a vertical grid has been added on the X axis, for every second point.

Here is a single series bar chart. Note how on the left, the gaps between bars are wider than the bars, which distracts your eye. If you narrow the gap, as on the right, it improves the look of the chart. (Do this by selecting the series, click Options tab, and reduce the gap width). If the bars get too fat when you reduce the gap, you can make the chart narrower until the bars look right.

Printing in Excel

The page breaks can be changed on an Excel spreadsheet so that it prints a bit nicer. This can also help to reduce the number of pages that get printed.

1. First, check out how it is currently set to print with File > Print Preview.
2. Second, if you want to change the page breaks, at the top of the print preview, click the “Page Break Preview” button.
3. Now you will see the spreadsheet broken up by dashed lines with a faint page number in each region.
4. You can move these dashed lines to change the page breaks.
5. Do File > Print Preview again and you will see how the change affects the printing.
6. You will also see “Page Break Preview” option has changed to “Normal View”
7. You can also find “Page Break Preview” and “Normal View” under View in the menu bar.

What if you want to print only part of the spreadsheet?

1. Highlight the area that you want to print.
2. From the menu bar, select File > Print Area > Set Print Area
3. Now when you go to File > Print Preview, you will see only the area that you highlighted.
4. To go back to printing the entire document, use File > Print Area > Clear Print Area

Not able to highlight the region you wanted to print?

1. Go to File > Page Setup
2. Click the Sheet tab
3. In the box marked “Print Area” you can click the tiny spreadsheet icon.
4. This will reduce the box and allow you to highlight what you want (or something close to it).
5. You will see the cell range appear in the “Print Area” box.
6. Edit the string to get it to cover what you want, and click on the icon at the end of the box.
7. Now hit File > Print Preview
8. Remember to remove these settings if you want to print something else.

Create a Workbook to Analyze your Field of Interest with Embedded Chart

Vocabulary

Workbook - The Excel File that contains the group of spreadsheets

Worksheet - One sheet of the Excel File

Charts - The graphic display of the data (a graph such as a column, bar, line, or scatterplot)

Tables - The set of numbers and formulas with headings that represents the data

Sheet tab - at the bottom of the page is a set of tabs that takes you from one sheet in the workbook to the next

Column Heading - the letter above each column

Row Heading - the number at the beginning of each row

Cell - One rectangle in the spreadsheet, A1, C7. One place in a row and column. A basic unit of a worksheet into which you enter data.

Cell Reference - The name of the cell you are in - A15

Active Cell - The cell you are in and editing

Gridlines - The light lines that separate the cells

Normal View - The default view of a spreadsheet

Page View - The view of the document that lets you see how the pages would look.

Scroll bar - The bar to the right and at the bottom that lets you move around on the document window

Scroll Arrows - the arrows at the top and bottom of the scroll bar that let you move around the document window

Scroll Box - The box on the scroll bar that displays where you are in the document

Status Bar - Bar at the bottom of the window that shows information about the worksheet, the functions of the buttons the mouse is pointing to, or mode of excel you are in. If you right click on the status bar you can add and take away items that are displayed there. If you choose a range of cells it can tell useful information like the total and average. Check out what all is available.

• READY - Excel is ready to accept data
• ENTER - Excel is processing data

Ribbon - The list of commands across the top of the screen. They are different depending on the tab you are on.

Tab - The sets of commands that are listed across the top of the screen. When you click on them it changes the available commands in the ribbon.

Group - In the ribbon the commands are in sections called groups. There is a group for formatting text, one for page set up, and many others.

Home Tab - The default tab that opens when you open Excel. It has the most commonly used commands.

Active Tab - The tab you are currently using. Its commands are what are displayed in the ribbon.

Contextual Tabs - Additional tabs that are added to the ribbon when certain tasks are performed. If you enter a chart you will see the Chart Tools Tab and a new contextual tab DESIGN will be added to the ribbon.

Gallery - a graphic list of commands such as styles of charts.

Font - How characters are displayed

• Font Type - the appearance and shape of the letters
• Font Style - How the characters are emphasized (bold, underline, italic, ...)
• Font Size - The size of the characters on the screen
• Point Size - How fonts are measured - the size number corresponds to 1/72 of an inch. So size 72 would be 1 inch and size 36 would be half an inch
• Font Color - The color of the characters

Live Preview - When you are hovered over an item in a gallery you will see the effects live in your document. Those affects are not permanent until you click on the gallery item.

Formula Bar - the bar across the top that begins with Fx. This will show you the formula, function or contents of the active cell.

Name Box - in the top left, it give the cell reference, or name. You can navigate to different cells by typing a cell name and pressing enter.

Number - Excel allows you to format numbers to some pre-set number formatting

• General - a number that has by default one decimal place and no commas
• Number - You can set how negative numbers are displayed, add commas, and set the number of decimal places
• Currency - You can set how negative numbers are displayed, there will be commas and two decimal places with a floating dollar sign.
• Accounting - Negative numbers are in parenthesis, You can set the number of decimal places, there is a fixed dollar sign.

AutoSum - A function in Excel that allows you with one click to add up all the numbers in a row or column. It looks like a sigma.

Range - the set of cells you have choosen. If you click and drag from A1 down to C4 then the range is A1:C4 and all of those cells will be shaded.

Relative Reference - When you use the copy or fill handle to copy Excel adjusts the formula for the correct row or column. The adjustments are making a relative reference for the formula or function.

Format - Changing fonts borders and colors to emphasis different parts of the worksheet and make it easier to read.

Cell Styles - From the HOME tab you can clickon Cell Styles to pick predefined formatting that fits your theme for standard formats such as:

• Red means bad or negative
• Green means good or positive
• Yellow means neutral
• Italic for explanatory notes
• Headings
• Totals
• Accent colors that fit your theme

Merge - To take multiple cells and make them one. This is commonly used for titles across the top of a table. There is a merge and center button that can be used for this purpose.

Embedded Chart - A chart of the table of data that is on the same sheet as the data.

Portrait - When your paper is taller than it is wide.

Landscape - When your paper is wider than it is tall.

Fill Handle - The little black square in the lower right corner of a cell that allows you to smart copy the contents of the cell across a row or down a column.

Floating Dollar Sign - The dollar sign that appears immediately to the left of the first digit in the cell, with no spaces.

Fixed Dollar Sign - The dollar sign that always appears to the far left of the cell no matter how wide the cell is.

vocabulary quiz

http://www.yacapaca.com EX1 - Career Analysis

Lesson

Save versus Save As

When you want to save a document for the first time you want to click on SAVE. You can name the document, and tell the document where to save (what folder). Also if you have already saved the document and just want to make a change and save it back to the same place, then you would use SAVE.

When you already have a document and want to make copy of it then you would use SAVE AS. You can put the copy in the same folder, or in a different one. You should rename the file to remove any confusion between the original one and the copy.

Workbook and Worksheet

When you open an excel workbook it opens by default with 3 work sheets. You can see the three worksheets at the bottom of the screen as tabs: Sheet1, Sheet2, and Sheet 3. You can rename these tab, delete them, create more, and change the color of them. Information from the three tabs can interact if you use global names. (global names will be covered in a later lesson)

Type in the following Table into Excel

Data collected from The Movie Times

Top 10 in Movie Sales

	A	B	C	D	E	F	G
1	Gross Movie Sales
2	Top 10 Movies
3	Movie	10/18/2009	10/25/2009	11/01/2009	11/08/2009	11/15/2009	Totals
4	WHERE THE WILD THINGS ARE	$32,625,407	$20,863,574	$9,091,398	$4,177,000	$2,418,000
5	LAW ABIDING CITIZEN	$21,039,502	$18,969,201	$11,476,577	$6,004,000	$3,796,000
6	PARANORMAL ACTIVITY	$33,171,743	$28,408,845	$23,046,784	$8,279,000	$4,043,000
7	COUPLES RETREAT	$62,617,735	$15,119,150	$9,289,395	$6,129,000	$4,165,000
8	THE STEPFATHER	$11,581,586	$8,501,066	$4,473,149	$1,800,000	$836,000
9	CLOUDY WITH A CHANCE OF MEATBALLS	$108,201,645	$6,653,232	$3,749,201
10	Cirque du Freak: The Vampire's Assistant		$6,293,205	$4,516,770
11	Michael Jackson's this Is It			$34,442,926	$13,158,000	$5,079,000
12	Zombieland	$108,201,645	$6,573,067	$3,968,172	$1,296,000	$475,000
13	Totals

General Data Analysis Questions

1. Why do some of the movies have such a high value in the first week on the chart?
2. Why do some of the movies not have a value in the first week on the chart?
3. Why do you think some of the movies drop off in value quickly as the weeks move on?
4. What do the totals in column G represent?
5. What do the totals in row 13 represent?

Worksheet Window

Same as Word

You will notice that many of the parts of the EXCEL Window are the same as the WORD Window. The right side and the top have scroll bars, with scroll arrows and scroll boxes. The top of the window has the Ribbon with the Tabs and Groups. Many of the Groups on the HOME tab are the same such as Font and Alignment. But some are different such as Number and Cells. The status bar is still at the bottom and gives information on the document as well as the active cell or range of cells you have chosen. Excel still has the Key Tip Badges, Enhanced Screen Tips, Screen Tips, Dialog Box Launcher and Task Panes. The menus for Mini Toolbar, Shortcut Menu, and Quick Access Toolbar are still available as well.

Different from Word

The Excel document contains different sheets, where as the Word document contains different pages.

In Excel you have column headings and row headings to display a grid that you can name the active cell or range of cells you are working with. The column headings are letters like A, B, C. The row headings are numbers like 1, 2, 3.

Each place that a row and a column intersect is called a CELL. The Active Cell is the cell you are currently editing, or have chosen. The name of the cell is something like: A1, B12, or F92, and this is also called the cell reference.

Dividing each cell is a faint line called a gridline. These lines do not print by default, but guide you to help you better see what cell, row, or column you are in.

In the upper left corner is the Name Box and it will display the name of the cell you have active. You can navigate to a specific cell by typing the name of the cell and pressing enter.

Next to the Name Box is the Function Box. This contains the actual contents of the active cell. If you have a formula then it will display that formula there. If you have a number of text that is what will be displayed there.

Adjust your column widths and row heights

• Column Widths
  • Place your mouse between the column headings (the letters such as A and B)
  • click and drag your mouse to the right to increase the column width
  • click and drag your mouse to the left to decrease the column width
  • double click to make the column width the BEST FIT for your data
• Row Heights
  • Place your mouse between the row headings (the numbers such as 1 and 2)
  • click and drag your mouse to down to increase the row height
  • click and drag your mouse to the up to decrease the row height
  • double click to make the row height the BEST FIT for your data

Use AutoSum to compute the Totals row

• Make the cell B13 active by clicking on it. This should be the first cell in the totals row.
• Click on the AutoSum button in the HOME tab in the EDITING group. (It looks like a sigma or a fancy E)
• Make sure the contents of the cell reads: =SUM(B4:B12), if it does not just click and drag over B4 to B12 and then press enter. Notice that the numbers are different.
• Do the same steps in cell C13 making sure the contents reads =SUM(C4:C12)and press enter

Use the Fill Handle to smart copy with a relative reference

• Click on cell C13
• Place your mouse over the tiny black box in the bottom left of that cell. This is the fill handle. When your mouse is on the fill handle it turns into a black plus sign.
• Click and drag the fill handle over to the right to cell G13

Use AutoSum to compute the totals column

• Click on cell G4 to make it active
• Click on the AutoSum button in the HOME tab in the EDITING group.
• Make sure it reads =SUM(B4:F4) Notice that now the letters are different and the numbers are the same.
• Do the same in cell G5
• Do the same in cell G6 - notice that it does not do it right and you will need to click on B6 and drag to F6
• Use the Fill Handle and drag it down to fill the rest of the column.

Create a title with the Merge and Center

• Click and drag from A1 to G1
• Click on the merge and center button in the HOME Tab in the ALIGNMENT group.
• This will take all of those cells and merge them into one cell called A1.
• Click and drag from A2 to G2
• Click on the merge and center button

Format the document using Cell Styles

• Format the titles
  • Click on cell A1
  • Click on the Cell Styles button in the HOME tab in the STYLES group.
  • Choose TITLE for the style
  • Click on cell A2
  • Click on the Cell Styles button
  • Click on the HEADING 4 style
• Format the headings
  • Click and drag to choose the range A3:G3
  • Click on Cell Styles button
  • Click on HEADING3
• Format the totals
  • Click and drag to choose the range A13:G13
  • Click on Cell Styles button
  • click on TOTAL style

Format the numbers

• Format the top row of dollars for standard accounting style
  • Click and drag to choose the range B4:G4
  • Click on the $ in the HOME tab in the NUMBER group - this applies the Accounting number style with a fixed dollar sign.
• Format the rest of the block of dollars for standard accounting style
  • Select the range B5:G12
  • Click on the Comma in the HOME tab in the NUMBER group
• Format the totals for accounting style
  • Select the range B13:G13
  • Click on the $ for accounting style

Create a Column Chart

NOTE: An EMBEDDED chart is one that is on the same sheet as the data table it is graphically displaying.

• Select the range A3:F12 - notice that we do not select the TOTALS
• Discuss why you would not select the totals for a column chart
• Click on the INSERT tab
• Click on the COLUMN button in the CHARTS group
• Choose a chart style from the gallery
• Discuss why you would want one from the first or fourth columns and what the ones in the second and third columns represent.
  • the second column in the gallery add the data together and give one column per group
  • the third column in the gallery gives a percent of the group for one column
• Click and drag the chart below your table of data so you can see both
• Make sure your legend is filled in with your row labels, if it is not, then you did not choose them when creating the chart
• Make sure your X-axis has labels, if not you did not choose row 3 (the column labels)when you created the chart

Formatting a Chart

• Click on the CHART TOOLS button at the very top of your window
• Click on the DESIGN tab
• Click on the Gallery More Button
• Choose a style for your chart, hover over different ones and look at the live preview before you select one
• Select a style that matches the title colors you created earlier

Project

Create a Worksheet that compares the Job Outlook and Earnings for your top five occupationsyou selected with the career surveys.

Remember you created these from the web site http://www.collegeincolorado.org

Use the web site http://www.bls.gov/oco

The work sheet should have:

• Word Art Title at the top
• Columns for
  • Job Title
  • Employment in 2006
  • Projected Employment in 2016
  • Change in number
  • Percent Change
  • Lowest Earnings
  • Highest Earnings
• Create a column chart that compares employment rates between 2006 and 2016.
• Create a second chart of your choosing (that is appropriate for the data) that compares high and low earning rates.
• Printed on one piece of paper

Rubric

Career Analysis Rubric

Standards	1 point	2 points	3 points	4 points	5 points
WordArt Title	Has a title	Has a title for the topic	Explains the topic of the table or charts	Explains the topic of the table and charts	Completely explains the topic of the table and charts
Lists 5 careers under the title column	Lists 1 career	Lists 2 careers	Lists 3 careers	Lists 4 careers	Lists 5 careers
Column for employment rate in 2006	Missing 4 or more items	Missing 3 items	Missing 2 items	Missing 1 item	Filled in information for all five careers
Column for employment rate in 2016	Missing 4 or more items	Missing 3 items	Missing 2 items	Missing 1 item	Filled in information for all five careers
Column for change in number of job openings	Missing 4 or more items	Missing 3 items	Missing 2 items	Missing 1 item	Filled in information for all five careers
Column for Percent change in job openings	Missing 4 or more items	Missing 3 items	Missing 2 items	Missing 1 item	Filled in information for all five careers
Column for lowest earnings in career	Missing 4 or more items	Missing 3 items	Missing 2 items	Missing 1 item	Filled in information for all five careers
Column for highest earning in career	Missing 4 or more items	Missing 3 items	Missing 2 items	Missing 1 item	Filled in information for all five careers
Projection Chart	Missing 4 or more items	Missing 3 items	Missing 2 items	Missing 1 item	Includes x and y axis lables, includes a key, contains all required information
Earnings Chart	Missing 4 or more items	Missing 3 items	Missing 2 items	Missing 1 item	Includes x and y axis lables, includes a key, contains all required information

Create a Personal Budget with functions

Vocabulary

Function - pre-written formula built into Excel that takes different values and performs an operation: SUM, AVERAGE, MAX, MIN

Order Of Operations - PMDAS (Please Excuse My Dear Aunt Sally)

• P - parentheses
• E - exponents
• M & D - Multiplication and Division taken left to right
• A & S - Addition and Subtraction taken left to right

Point Mode - selecting cells for a formula by clicking on them with the mouse

Smart Tags - Dates, financial symbols, people's names, indicated by a small purple triangle in the cell. Clicking on this purple triangle gives a list of actions you can perform for that cell

Smart Tag Indicator - the small purple triangle itself is the indicator that a smart tag is available

Arguments - The values that you plug into a function are called the arguments. In a SUM function it would be the list of cells you want to add together: =SUM(C2:C15) This means to add the values in the cells from C2 down to C15.

Average - Sums the numbers in a range and then divides by the number of cells in that range.

Max - A function that will display the highest value in a range of cells

Min - A function that will display the lowest value in a range of cells

Range Finder - A helping function in Excel that checks which cells are referenced in a formula. Very convenient if you have accidentally clicked on the wrong row or column for one of the values.

Theme - a set of predefined colors, fonts, chart styles, cell styles,a nd fill effects in an Excel Workbook.

Accounting Number Format - displays the numbers as a dollar value with the dollar sign to the left of the numbers, a comma every three digits and rounded to the nearest hundredth.

fixed dollar sign - the dollar sign in a cell is to the far left

floating dollar sign - the dollar sign is just to the left of the numbers

Comma Style Format - uses commas but no dollar signs in the numbers

Conditional Formatting - Formatting that changes depending on the value in the cell, or what condition the value meets. You could change the color of numbers to blue for any number over 1,000,000 for example.

Condition - A relation between at least two values. If a number is less than 7 then ..., this is a condition.

Pixel - The smallest element of a graphic. It represents on dot of color on a screen. The size of the dot is based on your screen resolution. A resolution of 1024X768 means that you have 1024 pixels across and 768 pixels down the screen.

Spell Checker - An operation in Excel that looks for possible spelling errors in a worksheet, comparing the words against a standard dictionary of words.

Page Layout - a view of the worksheet that lets you edit the worksheet while seeing how it will look in printed form.

Normal View - the default view of Excel allowing you to adjust the margins, header or footer.

Previewing the Worksheet - When you print an Excel Worksheet you should ALWAYS print preview before printing. This allows you to check to see what is being cut off and placed on a separate page. It also lets you see if you have a stray character that will cause you to print 100 pages instead of just 1.

Values Version - this is the default view and lets you see the results of the formulas and functions in each cell.

Formulas Version - this lets you see the functions and formulas in each cell instead of the value from the computation.

Debugging - the process of checking your formulas or functions to find errors.

In-cell editing - Double clicking on the cell and making the changes in the cell itself.

Best fit - Double clicking between two rows or columns and letting Excel choose the best width or height depending on the largest contents of a cell in that row or column.

Vocabulary Quiz

http://www.yacapaca.com

EX2 - Personal Budget

Lesson

Double click on Sheet1 Tab and change the name to Portfolio Summary

Type in the following Table into Excel

All data was retrieved from: Nasdaq.com/

Stock Analysis as of 10/20/09

	A	B	C	D	E	F	G	H	I	J
1	Personal Stock Anaylsis
2	Portfolio Summary
3	Stock	Symbol	Date Acquired	Shares	Initial $/Share	Initial Cost	Current Price/Share	Current Value	Gain/Loss	Percent Gain/Loss
4	Apple Inc	aapl	10/22/07	400	$174.36		$198.76
5	Hasbro Inc	has	11/04/05	1515	19.21		28.11
6	Texas Instruments Inc	txn	03/29/00	400	85.25		23.66
7	Coca-Cola Co	ko	10/01/03	600	43.89		54.07
8	Yahoo! Inc	yhoo	12/09/03	200	20.785		17.17
9	Google	goog	11/10/05	540	391.10		551.72
10	eBay Inc	ebay	07/03/03	1150	27.5325		25.06
11	Sprint Nextel	s	02/14/05	652	21.075824		3.29
12	McDonald's Corp	mcd	10/27/99	700	39.50		58.92
13	Totals
14	Average
15	Highest
16	Lowest

Fill in the cells with formulas

Fill in the empty columns

• Initial cost = (shares) * (Initial price per share)
  • Go to cell F4
  • Type and click on: =D4*E4
  • Use the fill handle to fill down the column
• Current value = (Shares) * (Current Price/Share)
  • Go to cell H4
  • Type and click on: =D4*G4
  • Use the fill handle to fill down the column
• Gain/Loss = (Current Value) - (Initial Cost)
  • Go to cell I4
  • Type and click on: =H4-F4
  • Use the fill handle to fill down the column
• Percent Gain/Loss = (Gain/Loss) / (Initial Cost)
  • Go to cell J4
  • Type and click on: =I4/F4
  • Use the fill handle to fill down the column

Fill Cells in with Functions

Fill in the rows 13 - 16

• Total =SUM(D4:D12)
  • Go to cell D13
  • Click on the Sigma (AutoSum)
  • Check that the correct range is chosen (D4:D12) and press enter
  • Use the fill handle to fill across the row
• Average =AVERAGE(D4:D12)
  • Go to cell D14
  • Click on the drop down menu with the Sigma (AutoSum)and choose AVERAGE
  • Check that the correct range is chosen (D4:D12) and press enter
  • Use the fill handle to fill across the row
• Highest = MAX(D4:D12)
  • Go to cell D15
  • Click on the drop down menu with the Sigma (AutoSum)and choose MAX
  • Check that the correct range is chosen (D4:D12) and press enter
  • Use the fill handle to fill across the row
• Lowest = MIN(D4:D12)
  • Go to cell D16
  • Click on the drop down menu with the Sigma (AutoSum)and choose MIN
  • Check that the correct range is chosen (D4:D12) and press enter
  • Use the fill handle to fill across the row

Verify a formula with the range finder

Once you have your formulas into excel you might need to go back and verify that you have entered them correctly. If you single click on a cell that has a formula then you will see the formula in the formula bar and the value from the formula in the cell.

If you double click on the cell with a formula the formula will show up in the cell and the RANGE FINDER feature will be turned on. This will outline the different cells that are used in the formula to make it easier to verify that you have entered the formula correctly.

This is very handy in the case where you accidentally click on a cell in a row lower than you meant to.

Apply a Theme to a workbook

So far the spreadsheet is considered to be mostly unformatted. An easy way to format a worksheet is to apply a THEME. You can change the theme of a worksheet from the Page Layout tab. In the far left there is a themes button. Play with the themes for the worksheet until you find one that you like.

Format the headings using the Cell Styles button from the HOME tab. The options here are dependent on the THEME you chose earlier. You should format each heading (row A and B) separately to give the main heading more emphasis than the sub heading.

Change the background color by selecting the cells you want to change and then clicking on the Fill color button from the HOME tab (it looks like a paint bucket).

Add a border around the headings from the BORDERS button in the HOME tab. Chose both headings at one time, then click on the Thick Box Border.

Put a bottom border under row 3 using the border button.

Put a total Style on the total row.

Bold the row headings: Totals, Average, Highest, Lowest

Number Styles

Accounting Number Format - When working with number such as stocks it is good to use Accounting Number Format. This will place the dollar sign to the left of the number (as a fixed dollar sign). It will also insert commas every third position to the left of the decimal, and put the decimals to two places (hundredths). A negative number will be displayed in parenthesis.

Comma Style Format - This will place a comma every third position to the left of the decimal.

Percents - Will move the decimal two places and place a percent sign at the end.

If you want to change the number of decimals then click on the increase or decrease decimal buttons in the NUMBER group.

Apply conditional formatting

Conditional formatting lets you change how a cell is formatted based on the values in the cell. In this case we will use the example of changing negative percent gain/loss to red so we can see them more easily.

• Click and drag over the range J4:J12
• From the HOME tab click on CONDITIONAL FORMATTING
• Click on NEW RULE
• Click on FORMAT ONLY CELLS THAT CONTAIN
• In the edit rule section at the bottom set FORMAT ONLY CELLS WITH: to Cell Value
• change the next drop down to LESS THAN
• type in the value 0 in the last field.
• Click on the FORMAT button
• Go to the FILL tab
• Click on a color for a background color
• Go to the FONT tab
• Change the color of the font so it will show up with your background color
• Click OK

Page Layout View

The current view you are in is NORMAL VIEW. Change to page layout view by clicking the page layout button in the lower right of the window. Notice what is going to be cut off when you print and placed on a separate page.

I usually do my page layout from the print preview screen.

• Go to the Office Button
• Click on PRINT
• Click on PRINT PREVIEW
• Click on Page Setup
• Click on Landscape because this table is wider than it is tall
• Click on Fit to -1- page wide by -1- page tall
• Click on OK

I then usually print from the Print Preview screen because it is a WYSIWYG (What you see is what you get).

NOTE - If you go to the SHEET tab from the Page Set up dialog box you can turn on grid lines to make your spreadsheet easier to read.

Rename a sheet tab

Go to Sheet 2

Double click on the sheet tab and rename it to real-time Stock Quote

Make cell A1 the active cell

Import stocks from a Web Source

Go to the DATA Tab

Click on Existing Connections in the Get External Data group

Click on MSN MoneyCentral Investor Stock Quotes

Click on Existing Worksheet

Click on OK

check the box for Use this value/reference for future refreshes

click OK

be patient as the spread sheet is created for you with all the current values of your stock.

Project

Create a personal budget for the next year. Pick one of the occupations that you have been looking at, or if you have a job currently use that.

Include rows for:

• Income
• auto gas
• groceries
• Eating out
• going to games
• going to movies
• movie rental
• music downloading
• other expenses
• totals

Include columns for each month in the year.

Fill in the data as best you can, know that you do more of certain things in the winter versus the summer and certain things in the summer more than the winter.

Add columns at the right for Total, Average, Highest, Lowest

For the Total at the bottom be aware that income is money coming in and all the rest is money going out - hint hint hint

Format the spreadsheet

Create a Pie Chart to show the expenses you incur FOR THE WHOLE YEAR only.

Use Microsoft HELP to add a title to the chart.

Rubric

Personal Budget Rubric

Standards	1 point	2 points	3 points	4 points	5 points
Chooses a Career and find salary per month	Chooses a career - can't find salary	Chooses a career with a salary	Chooses a career with an annual salary	Chooses a career with an annual salary and figures salary per month	Chooses a career with an annual salary and correctly figures salary per month
Fills in budget items for each month	Misses 4	Misses 3	Misses 2	Misses 1	Includes information for all budget items lists in directions
Includes total money at the end of each month	Inclues column	Includes column with values	Includes column and gets formula wrong	Includes column and gets formula mostly correct	Includes column and computes formula correctly
Includes row for total at the bottom	Creates row for total	Creates row for total and uses values	Creates row for total and uses function	Creates row for total and uses function correctly	Creates row for total and uses function correctly for all applicable columns
Includes row for average at the bottom	Creates row for total	Creates row for total and uses values	Creates row for total and uses function	Creates row for total and uses function correctly	Creates row for average and uses function correctly for all applicable columns
Includes row for Highest at the bottom	Creates row for total	Creates row for total and uses values	Creates row for total and uses function	Creates row for total and uses function correctly	Creates row for total and uses function correctly for all applicable columns
Includes row for Lowest at the bottom	Creates row for total	Creates row for total and uses values	Creates row for total and uses function	Creates row for total and uses function correctly	Creates row for total and uses function correctly for all applicable columns
Includes a conditional formatting on the total for each month	Does not understand conditional formatting	Attempts to use conditional formatting	Uses conditional formatting	Uses conditional formatting on the total for each month to change formatting	Uses conditional formatting on the total for each month to make negative numbers a different format.
Formats the spreadsheet to look professional	Missing 4 or more items	Missing 3 items	Missing 2 items	Missing 1 item	Follows standard layout and formatting for an accounting style spreadsheet: titles, borders, fonts, number styles
Creates a Pie chart to represent expense for the year	Pie chart created for something	Pie chart created for the data	Pie chart created for the expenses	Pie chart created for the entire year for expense	Pie chart created for just the total expense for the entire year by expense.
Prints 2 copies of spreadsheet correctly to one page each	Missing one view	Prints more than one page for both views	Prints more than one page for a view	Prints one page for each view	Uses Print Preview and page set up to format the document to fit on one page each. Prints the formula view and value view.

Create a College Expense Analysis Workbook

Vocabulary

#REF! - An error message stating that there is an error with the formula, this can occur if you have a formula and delete one of the cells that the formula referred to.

Format Symbol - Symbols that are entered with numbers such as $, %, or a comma. When you type one of those in with a number Excel will automatically assume the formatting that goes with it.

Freeze the Titles - Taking a row or column and having Excel display it on the screen, no matter how far down or to the right you scroll.

Date Stamp - Shows the date a file was created. This is very important in Business Financial Reports.

NOW Function - This will always display today's date, or the date the computer holds as the system date. The date is actually stored as a number representing a day starting January 1, 1900

Absolute Cell Reference - Keeping a cell constant when copying it. This is done by adding $s. $C$15 is an absolute cell reference and when you copy a formula that contains it, =$C$15 +C16, down a column the $C$15 will stay the same and the C16 will change with the row.

Relative Cell Reference - A cell reference that changes as the formula is copied. In the equation =$C$15 + C16, the C16 is the relative cell reference.

Mixed Cell Reference - You can tell Excel to keep part of the cell reference from changing by only using one $. C$15 and $C15 are both called mixed cell references. In C$15 the row will stay the same but the column letter can change. In $C15 the Column letter will stay the same but the row number can change.

IF Function - A condition that allows you to assign a value to a cell based on a test. =IF(B7>$B$2, $C$2, 0) This means that if the value in cell B7 is greater than the value in Cell B2 then keep the value in cell C2. If it is equal to or less than the value in B2 then keep the value 0.

Logical Test - The test in an IF statement such as B7>$B$2.

Value If True - This is the value that is displayed if the condition tested is true. =IF(B7>$B$2, $C$2, 0) in this example it is the $C$2.

Value If False - This is the value that is displayed if the condition tested is false. =IF(B7>$B$2, $C$2, 0) in this example it is the 0.

Comparison Operators - There are six tests you can perform:

• < is the first one less than the second
• > Is the first one greater than the second
• >= Is the first one greater to or equal to the second
• <= Is the first one less than or equal to the second
• = Are the two values equal to each other
• <> Are the two values NOT equal to each other

Nested IF function - You can place one IF statement inside another IF statement. The second if statement would be in the area for value if true, or value if false.

Chart Sheet - A sheet in Excel that contains only a chart

Pie Chart - A graphical representation of part of a whole. You would use a pie chart only if you want to show how the percentage of something is made up. Each pie wedge shows a percentage of the whole.

Exploded Pie Chart - A pie chart where one or more wedges is pulled out from the rest.

Offsetting - The piece of the pie chart that is pulled out is called offsetting. Sometimes you only want one piece pulled out to show the area you are discussing.

What-If Analysis - This is sensitivity analysis, this just means that Excel will recalculate every formula and redraw every chart for any cell that you change the data in.

Goal Seeking - If you want to find what has to happen to give your desired results in a cell you can use this function to determine the values of a cell on which the formula depends.

Vocabulary Quiz

http://www.yacapaca.com

EX3 - Expense Analysis

Lesson

Beginning Data

Enter the following table into Excel

Semiannual Projections

	A	B	C	D	E	F	G	H
1	Pizza R Us
2	Semiannual Projected Gross Margin, Expenses, and Operating Income
3		January						Total
4	Sales	$2,212,105.98	$4,845,562.56	$6,721,100.32	$3,025,430.35	$3,001,272.68	$5,987,384.14
5	Cost of Goods Sold
6	Gross Margin
7
8	Expenses
9	Bonus
10	Commission
11	Marketing
12	Research and Development
13	Support, General and Administrative
14	Total Expenses
15
16	Operating Income
17
18	What-if Assumptions

Save the Workbook

Click the Office Button to save the workbook

Click the Office Button - Prepare to add document properties for your name and other relevant information

Pick a Theme for the worksheet

Format by Rotating Text

Rotate the names of the month so that they are at a 45° angle

Click on the Row Heading 3 to select the entire row.

Click on the HOME tab to make it active

Click on Alignment Group Dialog Box Launcher

Change the Orientation to 45°

Click OK

Create a Series with the Fill Handle

Click on cell B3, the month JANUARY

Click and drag the fill handle to the right, to cell G3

Notice that Excel automatically updates the month to the next on in the series.

Also notice the new options button that appeared - Auto Fill Options Menu

• Copy Cells
• Fill Series
• Fill Formatting Only
• Fill Without Formatting
• Fill Months

Copy and Paste

In Excel there are many different ways to copy and paste.

• Copy and Paste button in the HOME tab in the CLIPBOARD group
• Right click and copy and paste
• CTRL+C to copy and CTRL+V to paste
• Another option is to cut and paste if you want to move the data

Use one of these methods to Copy the range A9:A13 to A19:A23.

Just like with the Fill Options Menu, there is a Paste Options menu that offers the same options.

Insert and Delete Cells

You can insert a single cell, range of cells, or whole rows and columns. The different methods are:

• Right click on a row header and click INSERT to insert a Row
• Right click on a column header and click INSERT to insert a column
• Click on a row header and click in the HOME tab in tyhe CELLS group on INSERT
• Click on a column header and click in the HONME tab in the CELLS group on INSERT
• You can do the same for just one cell

You can also delete a cell, range of cells, or whole rows and columns. When you delete a cell you are asked if you want to shift cells up, or over.

Insert cell A21 and place the text Margin in the new cell A21.

Insert cell A24 and place the text Revenue for Bonus in the new cell A24.

NOTE: if you delete a cell and it is used in a formula the formula result will show as REF! and you will need to fix the formula.

Type in the What-If Assumptions Data

B19 = 150,000.00

B20 = 4.5%

B21 = 58%

B22 = 8%

B23 = 6.25%

B24 = 5,250,000.00

B25 = 16.5%

Freeze Cells

Sometimes when you are working with a large spreadsheet it would be nice to see the column and row headings. There is an option in Excel that allows you to Freeze rows or columns on your screen. This will allow you to still see the headings, and scroll to see the rest of the data.

To freeze a cell

• Click in the cell that boarders the areas you want to freeze. In this example that would be cell B4
• Click the VIEW tab
• Click the FREEZE PANES button
• Click Freeze Panes, this will freeze column A and row 1 - 3

Display the System Date

When working with data it is very important to know how recent the data is. For this reason we place the date in our table.

• Go to cell H2
• Click the Insert Function box in the formula bar
• Select Date & Time
• Click on NOW in the Select a Function box.
• Click OK
• Right Click cell H2
• Click on Format Cell
• Click on the Number tab
• Click on DATE
• Click on the format that you like (I will choose 10/27/09)
• Click OK

This date is actually stored as the number of days since December 31, 1899.

Make Reference to Cells both Absolutely and Relatively

On the below formulas, make sure you determine what references are absolute and what are relative prior to using the fill handle.

College Expense Projections

	A	B	C	D
1	Cell	Row Title	Formula	Comment
2	B5	Cots of Goods Sold	=B4*(1-B21)	This is how much it costs for all the items that you sell.
3	B6	Gross Margin	=B4-B5	The profit from the Sales without any additional expenses figured in.
4	B9	Bonus	=IF(B4>=B24, B19, 0)	If your sales(B4) are greater than or equal to the Revenue for Bonus (B24) amount then give the Bonus(B19), otherwise give them nothing.
5	B10	Commission	=B4*B20	Figure the percent commission (B20) of the sales (B4).
6	B11	Marketing	=B4*B22	Figure the percent spent on Marketing (B22) of the sales (B4).
7	B12	Research and Development	=B4*B23	Figure the percent spent on Research and Development (B23) of the sales (B4).
8	B13	Support, General, and Administrative	=B4*B25	Figure the percent spent on Support, General and administrative(B25) of the sales (B4).
9	B24	Total Expenses	=SUM(B9:B13)	Total of all expenses for January
10	B16	Operating Income	=B6-B14	This is your actual profit after you subtract out all your expenses.

When you enter in a formula you have three options for how the formula will interact with the cells you have referenced:

• Relative Cell Reference - This is what you have been working with. When you use the fill handle the cell reference changes. If you use the fill handle on a cell with the formula =A3+A5 and drag right the new formula would be =B3+B5. If you use the fill handle and drag down the new formula would be =A4+A6. Depending on what direction you drag would change either the row number or the column letter.
• Absolute Cell Reference - Sometimes you would want the cell in the formula that you referenced to not change when you drag the fill handle. (Like in the formulas above.) If you Take the formula for cell B5, =B4*(1-B21). The B4 is the sales for that month. When you drag right you would want that to change so that each column refers to the month it is in. But the cell B21 does not have anything next to it. So you would want that cell to be absolute. To do this place a $ in front of the B and the 21 so it would be $B$21, or the formula would read =B4*(1-$B$21)
• Mixed Cell Reference - If you do not need to block a cell from changing references in both the column and row direction, like in this case you could just put the $ on the part that you don't want to change. $B21 so the formula would read =B4*(1-$B21)Then the reference would always be to the B column and when you drag right the 21 would not change anyway.

Create Conditional IF Function Statements

The format of an IF statement:

= if (B4>=$B$24,$B$19,0)

This is read - If the contents of B4 is greater than or equal to the contents of B24 then use the value in B19, otherwise use 0.

or

IF(logical_test, Value_if_True, Value_if_False)

You can test for:

• = Equal to
• < Less than
• <= Less than or equal to
• > Greater than
• >= Greater than or equal to
• <> Not equal to

Total on Non-adjacent Cells

Choose the cells in Column H that should have a total. If the cells are not next to one another then use CTRL.

Click on the AutoSum button to get totals.

Use the Format Painter

The format painter works the same here as it did in Microsoft Word. Click on a cell with the formatting you want to copy, then click on the format painter (paintbrush tool) then click on the cell you want formatted the same.

Create a Pie Chart

Select B3:G3 and B16:G16

Insert Tab

Pie Chart - 3-D Pie

Click on the Move Chart button in the Chart Tools - Design Tab

Click on the NEW SHEET radio button

Type "Semiannual Financial Projector"

Click OK

Notice the new chart tab at the bottom of the window.

Add a chart title - "Semiannual Financial Projector"

Underline it

Turn the Legend off - From the LAYOUT tab click on LEGEND and click on NONE

Add data labels - From the LAYOUT tab click on Data Labels - Check Category Name and Percentage

Rotate the Chart - From the LAYOUT tab click the 3-D rotate button - Choose the degrees

Change the Format of the Chart - Right click on the pie chart and go to Format Data Series - Explore and make sure you visit the 3-D Format to add contour, surface or material.

Try to change the colors of the pie wedges manually from the Format Data Series window.

Rename and Change Color of Sheet Tabs

Right Click on the sheet tab and click on RENAME to change the name from SHEET 1 to "SemiAnnual Financial Projection"

Right Click on the sheet tab and click on TAB COLOR to change the color of the tab, change active tabs to see the color change.

Click and drag a sheet tab to the right or left to change the order they are listed at the bottom.

Previewing

ALWAYS PRINT PREVIEW BEFORE YOU PRINT!!!

Office button - print - print preview

I use the Page Setup button to format and then I print from the preview to ensure I will get what I want.

From the PAGE tab you can change from landscape to portrait, and fit the document to one page.

From the MARGINS tab you can change the margins of the page and center horizontally and vertically.

From the HEADER/FOOTER tab you can add either a header or a footer to the page.

From the SHEET tab you can set a print area, set the row or columns that will print at the top or side of every sheet, turn on gridlines for printing, print only in black and white, or print in a draft quality that takes less in.

Printing a WorkBook

If you have a multiple sheet workbook, you can print the entire workbook at one time. Choose all the sheet tabs that contain data or charts, then print preview and print as normal. In the print preview you will need to use the scroll bar to see the other pages.

Using What-If Analysis

Since all of your data is linked to the What-If Assumptions section you can change your assumptions and watch the changes through out the entire workbook, chart and all.

Zoom your worksheet so you can see the entire data table.

Enter 72,000 in cell B19 - What does this change, and why? What does it mean?

Enter 3.0 in cell B20 - What does this change, and why? What does it mean?

Enter 15.25 in cell B25 - What does this change, and why? What does it mean?

What happens in cell H16 and why?

Goal Seeking

This is very useful if you know the results you want but are unsure how to get there. This feature of Excel allows you to put in the end result and it will help calculate the dependent variables.

click on the Virtical Split box and split the screen after column E.

Adjust the right side to show column H

Click cell H16

Click the DATA Tab

Click the What-If Analysis button

Click Goal Seek

Fill in the box as follows:

• Set Cell - H16
• To Value - 11,000,000
• By Changing Cell - $B$25

Click OK

What happened?

Play with the values

When you are done, click Cancel

Project

College Expense Projections Attending college in today's economy is tough. Planning ahead is the key to relieve the stress of finding the finances needed for this large expense. Create the following table:

College Expense Projections

	A	B	C	D	E	F
1	University of Northern Colorado
2	Expense Projections
3	Expenses	Freshman	Sophomore	Junior	Senior	Total
4	Tuition		Formula #1	Formula #1	Formula #1
5	General Fees		Formula #1	Formula #1	Formula #1
6	Medical		Formula #1	Formula #1	Formula #1
7	Book Allowance		Formula #1	Formula #1	Formula #1
8	Room/Board		Formula #1	Formula #1	Formula #1
9	Personal/Misc		Formula #1	Formula #1	Formula #1
10	Transportation		Formula #1	Formula #1	Formula #1
11	Total Expenses
12
13	Resources	Freshman	Sophomore	Junior	Senior	Total
14	Savings	Formula #2	Formula #2	Formula #2	Formula #2
15	Parents	Formula #2	Formula #2	Formula #2	Formula #2
16	Job	Formula #2	Formula #2	Formula #2	Formula #2
17	Loans	Formula #2	Formula #2	Formula #2	Formula #2
18	Scholarships	Formula #2	Formula #2	Formula #2	Formula #2
19	Total Resources
20
21	Assumptions
22	Savings
23	Parents
24	Job
25	Loans
26	Scholarships
27	Annual Rate Increase	5.3%

Formula #1 = Prior Year's Expense * (1+Annual Rate Increase)

Formula #2 = Total Expenses for Year * Corresponding Assumption

The Assumptions are your best guess on what percent you will need from each category to pay for college

Create a Pie chart of total expenses.

Create a Pie chart of total resources.

EXTRA CREDIT - Use an if statement for notifying you if the total savings is higher than you know you have saved.

Rubric

College Expense Rubric

Standards	1 point	2 points	3 points	4 points	5 points
Accurately creates the College Expense Table	Missing 4 item	Missing 3 item	Missing 2 item	Missing 1 item	100% accurate - nothing missing
Fills in Assumptions with logical Guesses	Missing 4 item	Missing 3 item	Missing 2 item	Missing 1 item	100% accurate - nothing missing
Uses Formula #1 accurately	4 mistakes	3 mistakes	2 mistakes	1 mistake	No mistakes
Uses Formula #2 accurately	4 mistakes	3 mistakes	2 mistakes	1 mistake	No mistakes
Fills in the totals accurately	4 mistakes	3 mistakes	2 mistakes	1 mistake	No mistakes
Creates a Pie Chart of total Expenses	4 mistakes	3 mistakes	2 mistakes	1 mistake	No mistakes - Includes data labels, and title
Creates a Pie Chart of Total Resources	4 mistakes	3 mistakes	2 mistakes	1 mistake	No mistakes - Includes data labels, and title

Troubleshooting Excel Errors

Common sources of errors are parentheses that don’t match or missing arguments for functions. If your formula is free from those errors, here are some error values you might get:

Error	Description
######	There is nothing wrong with your formula; the cell simply isn’t big enough to display the result. Widen the column. or set "Shrink to fit" under Alignment.
#DIV/0	You are trying to divide by zero Correct the divisor If the divisor is a cell reference, check to make sure the cell isn’t empty
#NAME?	There is a name in the formula that Excel doesn’t recognize. If you used a natural language name, check the spelling If you typed in a function, check the spelling or verify that the function exists. If you are performing operations on text, enclose the text in double quotation marks
#REF!	A cell reference is not valid. Reenter the formula.
#VALUE!	The formula uses the wrong type of operand or argument. Check to see that you’re not performing math operations on labels or that arguments of functions that need to numeric are not referring to cells containing labels.

A handy function to avoid errors is ISERR. For example, to divide one column by another, where blanks and zeros are present, use this formula (in cell C1): =IF(ISERR(A1/B1),"",A1/B1)

Loan Payment Amortization Schedule

Objectives

The Student Will Be Able To (TSWBAT):

• Format sections of a worksheet for easy readability
• Create Global references and use them in a formula
• Determine the monthly payment of a loan with the PMT function
• Determine the present value of a loan using the PV function
• Analyze data in a worksheet
• Create an Amortization schedule
• Create hyperlinks in a worksheet
• Set a print area and print part of a worksheet

Vocabulary

Hyperlink - links from one document to another, web page to another, or part of a document to another part of the same document

Global Cell Name - A name given to one cell in a workbook, that can be used in any sheet

PMT Function - determines the monthly payment on a loan based on the rate, payment amount and loan amount.
-PMT(Monthly_interest_rate/12, 12*years, loan_amount)

Data Table - a range of cells that shows the answers to a formula

• one-input data table - one value in the formula varies
• two-input data table - two values in the formula vary
• Input Values - The values that are put into the formula for a data table

Amortization Schedule - A table of values that shows the beginning balance of a loan, ending balance for a loan, amount paid toward the principle, and amount paid toward the interest at the end of each year.

PV Function - determines the present value of a loan, or how much you still need to pay at the end of a given amount of time

Annuity - a series of fixed payments such as your monthly payments on a loan

Lesson

Payment Calculator

Titles

• Titles
  • Type "Payment Calculator" in cell B1
    • Merge and Center cells B1:E1
    • Format the font as you like - size 16pt
  • Type "Date" in cell B2
  • Type "Item in cell B3
  • Type "Price in cell B4
  • Type "Down Payment" in cell B5
  • Type "Loan Amount" in cell B6
  • Type "Rate" in Cell D2
  • Type "Years" in cell D3
  • Type "Monthly Payment" in cell D4
  • Type "Total Interest" in cell D5
  • Type "Total Cost" in cell D6
• Formatting
  • Make each column as wide as it needs to be for the labels
  • Bold all label text
• Change the sheet name to "Car Loan"
• Save the spreadsheet to your storage space calling it "Loan Calculator"

Function for Today's Date

In cell C2 type:
=now()

Formatting

Format the first section of your spreadsheet with:

• an outside border
• column dividers
• a fill color
  • Go to the home tab
  • go to the font group
  • click on the borders drop down button
  • choose more borders
• make sure all colors go together and you can read the text

Format the data as the proper type

• Dollars
  • Price
  • Down payment
  • Loan amount
  • Monthly payment
  • Total interest
  • Total cost
• Percent
  • Rate
• General number
  • Years
• Date
  • Date

Enter Loan Data

Find a Car you want to purchase and the price

• go to Kelley Blue Book
  • Find a NEW car you want to buy
  • Find the price
• go to BankRate.com
  • Find the interest rate for a car loan
  • Remember the number of years for that interest rate
• Determine if you have any money to put as a down payment NOW
• Type in the data you have collected into your spreadsheet
  • C3 = name of car you are purchasing
  • C4 = Price for the car
  • C5 = down payment if you have one, 0 if you don't
  • E2 = interest rate for the car loan
  • E3 = number of years for the loan

Create cell Global Names

• Click on the cell you wish to name - C4
• Go to the Formulas tab
• Go to the Define Names group
• Click on Define Name
• Make sure the name is what you want and click OK
  • C4 = Price
  • C5 = Down_Payment
  • C6 = Loan_Amount

etc...

Compute loan amount

The amount of a loan you need to take out is the price of the car minus the amount you will pay when you purchase the car or the down payment.

• go to cell C6
• = price - down_payment

or

• = C4 - C5

Determine the monthly payment

The monthly payment is how much you will pay each month to pay off the price of the car. This is computed with the interest rate of the loan, the years you will take to pay off the loan, and the total loan amount.

• go to cell E4
• -pmt(rate/12, 12*years, loan_amount

or

• -pmt(E2/12, 12*E3, C6

Determine the total interest

The total interest is the total amount the bank is going to make on loaning you the money to purchase your car. For this computation you will need to know how much the loan amount was, and how much you paid in total over the years. That is figured by taking the years times 12 to find the months, and then multiplying by the monthly payment.

• go to cell E5
• =12*years*monthly_payment-loan_amount

or

• =12*E3*E4-C6

Determine the total Cost

The total cost of the car is how much you paid including the down payment and interest. This is figured by adding the price of the car with the total interest.

• go to cell E6
• =price+total_interest

or

• =C4+E5

Analyze your data

• Go back to kelly blue book
• find the price of the same car that is one year old
• Go back to BankRate.com
• find the interest rate for a used car
• Change your data
• Analyze the difference in how much total you pay for the used car versus a new car

Interest Rate Schedule

Titles

• B7 = Interest Rate Schedule
  • format to same fonts as B1
  • center across B7:E7
• B8 = Rate
• C8 = Monthly Payment
• D8 = Total Interest
• E8 = Total Cost

make all titles bold

Create a series with the fill handle

• B10 = 4.00%
• B11 = 4.25%
• select cells B10:B11
• use the fill handle to drag down until you reach 8.00%

Add Initial Values

• C9 = E4
• D9 = E5
• E9 = E6

Define the Data Table

• select B9:E26
• go to the Data tab
• go to the Data Tools group
• click on the what-if analysis drop down
• choose Data Table
• click in the column Input Cell
• click on the rate from cell E2
• click on OK

Format

• select cells B8:E26
• shade and border similar to the section above but a different color

Conditional Formatting

• select B10:B26
• go to the HOME tab
• go to the styles group
• click on the conditional formatting drop down
• click on Highlight cell rules - equal to
• click in the format cells that are EQUAL TO
• click on E2
• click on OK

Change the rate in cell E2 and watch the color section change.
Notice the color rate changes, but the values in the data table does not. You would have to re-select the cells and re do the data table steps.

Amortization Schedule

Titles

• G1 = Amortization Schedules
  • center across G2:K2
  • same font style and color as the other two sections
• G2 = year
• H2 = Beginning Balance
• I2 = Ending Balance
• J2 = Paid on Principle
• K2 = Interest Paid
• Wrap text for H2:K2

Year Series

• G3 = 1
• use the fill handle to fill down to G26
• click on the smart button - auto fill options
• click on fill series

Beginning Balance Formula

• H3 =C6

Ending Balance Formula

• I3 =PV($E$2/12, 12*($E$3-G3), -$E$4)
• I4 =IF(H4<=0,0,PV($E$2/12, 12*($E$3-G4), -$E$4))

Paid on Principle Formula

• J3 =H3-I3
• J4 =H4-I4

Interest Paid Formula

• K3 =12*$E$4-J3
• K4 =IF(H4<=0, 0, 12*$E$4-J4)

Fill Table with formulas

• Select I4:K4
• fill down

Finish the Beginning Balances

• H4 = I3
• fill down

Amortization totals

• I28 = Subtotal
• I29 = Down Payment
• I30 = Total Cost
• J28 = auto sum column J
• K28 = auto sum column K
• K29 = C5
• K30 = J28 + K28 + K29

Formatting

format the same as the other sections just a different color

Hyperlinks

Place a Graphic

• go to cell H25
• Find a picture in clip art or the internet that represents your car
• Go to the insert tab
• go to picture or clip art depending on what you are using
• Find your picture

Create a Graphic as a link

• Right Click on the picture and go to Hyperlink
• In the address place the URL for the web site - http://www.kbb.com
• Find the web page for the car you are purchaseing

Set a print area

• Select the area you want to print - click and drag
• Go to the Page Layout Tab
• Go to the Page Set up group
• Click on the drop down for Print Area
• Click on Set Print Area
• Print Preview and you will see only that area will print

You can clear the print area or change the print area from this same drop down.

Project

1. Edit the spreadsheet you created to buy your dream home you find on http://www.remax.com/
2. Find the loan percentage from a bank such as http://www.boxhomeloans.com/
3. Decide what type of mortgage you will take out to pick the correct loan interest rate
4. Change the graphic on the page and have it link to your dream home from remax.com

Create a Pivot Table

A pivot table is a great reporting tool that sorts and sums independent of the original data layout in the spreadsheet. It is an interactive representation of a data table. One can rearrange the data and choose what to display and what to hide. The best way to find out what a pivot table can do is to create one.

First, set up categories and create some data (See Table 1):

Table 1: Original Data

Who	Week	What	Amount Spent
Joe	3	Activities	$18
Beth	4	Food	$17
Janet	5	Activities	$14
Joe	3	Food	$12
Joe	4	Activities	$19
Janet	5	Car	$12

Now choose any cell in this table and choose Data > Pivot Table wizard. Pivot Table should be selected. Click Next. Excel asks for the data source and suggests this table. Click Next. Finally Excel asks if the table should be placed in a new worksheet. Select new sheet. Click Finish.

Arranging data on the table

Drag the headers Who, Week and What into the ROW area, and the Amount header into the Data area. (Leave the Column area blank for now.) To change the placement of an item, drag the header title to the desired area. If the Amount tag does not show "Sum of Amount", double-click it and choose the Sum option. Double-clicking the headers gives options of showing/hiding specific data (like Empty and Activities, may come in handy) and removing subtotaling for this column. Right-clicking gives other options, among them Hide and Show Detail for reading totals only.

Here comes another useful pivot made from the same list. Select any item in the original data list and choose Pivot Table wizard again. This time, drag Who into the Row field, What into the Column field and Amount into the Data field. This table is also very useful for graphing.

Pivot tables are very flexible. The one being built here has four variables: Who, Week, What, and Amount, all of which can be dropped in header or data areas represented by the blue borders.

Create a spreadsheet with lists

Objectives

• Create lists in a spreadsheet
• Validate Data
• Add Computational Fields to a list
• Create a VLOOKUP function
• Examine Data with the Toggle Total Row
• Print a list
• Sort a list
• Organize a spreadsheet with groups and outlines
• Query a list
• Create a COUNTIF function
• Save to different file formats

Vocabulary

• List - a database or collection of organized data
• Computational Field - displays results based on other fields
• VLOOKUP - determine the value of one cell based on a table of data, you are looking up the value in a different table
• Data Validation - the data you enter into a cell is within the limits you set. Example: gender can only be M or F and not male or female.
• Table Array - A smaller table used in a spreadsheet to store data you will look up later - like a grade to percentage look up table.
• Table Arguments - the left most column in the array that lists the limiting values - 0%, 60%, 70%, 80%, 90%
• Table Value - the value that is returned so if you look up 63% it would return a value of D.
• major sort key - the first item that a table is sorted by - Sorted By
• Intermediate sort key - The second item that a table is sorted by - Then By
• Minor sort key - The last item that is sorted by - Then By
• Outlining features - Gives the user the ability to hide and then unhide detail rows
• Row Level Symbols - Numbers at the top that signify what level of detail should be displayed
• Show detail symbol - a plus sign is used to expand the list and show the details
• Hide detail symbol - a minus sign is used to collapse the list and hide the details
• Row Level bar - a vertical bar is displayed to connect items of the same level
• Wildcards - characters that allow you to find records that contain certain characters in a field.
  • Asterisk - used to represent any number of characters - A* means any set of characters that starts with A
  • Question mark - used to represent one character - A? means any set of two characters where the first one is an A
• AND Operator - used to mean that both parts of the criteria are true
• OR Operator - used to mean that one or both parts of the criteria are true
• Extract Range - When you use a set of criteria to only look at one set of the data, the location where those records are sent is called the extract range.

Lesson

Create a List/Table

Titles

• A7 = Dogs R Us - Breeding Service
  • Center from A7:J7
• A9 = Owner
• B9 = Breed
• C9 = Gender
• D9 = Welp Date
• E9 = State
• F9 = Region
• G9 = Prize Money won
• H9 = Potential Money Earned
• I9 = % Potential
• J9 = Grade

Create a List/Table

• Highlight the titles
• Go to the Insert Tab
• Go to the Tables Group
• Go to TABLE

Set Data Validation

• Go to cell C10
• Go to the Data Tab
• Go to the Data Tools Group
• Click on Data Validation
• Go to the Settings Tab
• Go to the ALLOW field
• Choose LIST
• The only check box checked should be IGNORE BLANK
• Go to the SOURCE field
• Type F, M
• Go to the ERROR ALERT tab
• Check the box - SHOW ERROR ALERT AFTER INVALID DATA IS ENTERED
• Go to the STYLE field
• Choose STOP
• Go to the TITLE field
• Type GENDER INVALID
• Go to the ERROR MESSAGE field
• Type: GENDER CODE MUST BE AN F OR M.
• Click on OK

Type in the data

Type in the data from the following table

Dogs R Us - Breeding Service

	A	B	C	D	E	F	G	H	I	J
9	Owner	Breed	Gender	Welp Date	State	Region	Prize Money	Potential	% of Potential	Grade
10	Bull, S	Golden Retriever	F	11/14/2007	CO	NW	$10,000	$12,000
11	Welsh, T	Manchester Terrier	F	5/22/2006	PA	NE	$2,000	$4,000
12	Highland, W	Miniature Schnauzer	M	5/17/2007	FL	SE	$6,000	$8,000
13	Clark, M	Samoyed	F	5/1/2006	MD	NE	$54,000	$50,000
14	Westminster, K	Standard Poodle	M	11/1/2006	NY	NE	$20,000	$40,000
15	Hampton, H	Tibetian Mastiff	F	11/15/2005	CO	NW	$7,000	$15,000
16	Ford, L	Scottish Terrier	M	5/4/2006	CA	SW	$250,000	$200,000
17	Pedier, S	Whippet	M	11/30/2005	WY	NW	$500	$2,000
18	Bistle, V	Toy Poodle	M	5/27/2007	MT	NW	$11,500	$20,000
19	Miller, B	Brittany Spaniel	F	4/30/2003	PA	NE	$65,000	$70,000
20	Baker, N	Puli	M	11/8/2007	NM	SW	$1,000	$3,000

Create special Fields

Computational Fields

• Create an Age column
  • Click on the E column Header
  • Go to the HOME Tab
  • Go to the CELLS group
  • click on Insert
  • click on cell E9
  • Type: Age (years)
  • Click on cell P2
  • Use the NOW function for today's date =now()
  • Click on cell E10
  • Do a subtraction from today's date and the welp date to find out how old the dog is
    • =($P$2-D10)/365
    • notice the absolute reference
    • we divide by 365 to get number of years
    • Notice the column fills in for you
    • format as a general number with two decimal places
• Fill in the % of Potential column
  • Go to cell J10
  • divide the money earned by the estimated amount
    • =H10/I10
    • Notice the column fills in for you
    • Format as a percent with no decimal places

Create a Lookup table

type in the table below:

Newsletter Rubric

	M	N
1	Grade Table
2	% of Quote	Grade
3	0%	F
4	60%	D
5	70%	C
6	80%	B
7	90%	A

Create a VLOOKUP field

• Go to cell K10
• enter the function for a VLOOKUP
  • =vlookup(I10,$M$3:N$7,2)
  • This uses the %of Potential from column J, then goes to the table M3:N7, and takes the second column value to fill into the field.
  • Notice you do not need to fill down

Creating a Total Row

Turn on the Total row

• Click in the table anywhere
• Go to the TABLE TOOLS - Design tab
• Go to the Table Style Options group
• Click the check box for TOTAL ROW

Create totals for Columns

• Prize Money
  • Go to cell H21
  • Click the drop down arrow
  • Choose SUM
• Potential
  • Go to cell I21
  • Click on the drop down arrow
  • Choose SUM

Create Averages for columns

• Age(years)
  • Click on cell E21
  • Click on the drop down arrow
  • Choose Average

Create Count for number of items in a column

• Grade
  • Go to cell K21
  • Notice that the number 11 is there
  • Click on the drop down arrow
  • Notice that the COUNT is choosen

Viewing Records in the Table

Convert to Normal Spreadsheet

• Click anywhere in the table
• Go to the TABLE TOOLS - Design tab
• Go to the Tools group
• Click on Convert to Range

Sorting

Sorting means putting the records in order. This can be done from the drop down arrows at the top of each column.

• Sort Alphabetically by Owner
  • Go to cell A9
  • click the drop down arrow
  • Choose SORT A TO Z
• Sort Numerically by Age
  • Go to cell E9
  • click the drop down arrow
  • Choose SORT LARGEST TO SMALLEST
  • This will put the dogs in order from oldest to youngest.
• Sort by multiple items
  • Click anywhere in the table
  • Go to the DATA tab
  • Go to the SORT & FILTER group
  • Click on the SORT button
  • Fill in the first row
  • Click on the Add level to add more items to filter on

Limiting

Limiting is a feature that lets you look at only a small set of the data.

• Limit to all dogs from the NW
  • Go to cell G9
  • click on the drop down arrow
  • Click the Select All check box to deselect all
  • click the check box for NW
  • Click on OK
  • Undo that when you are done
• Limit by prize money won at or over $20,000
  • Go to cell H9
  • click the drop down arrow
  • Click on Number Filters - Greater than or equal to
  • In the blank box in the top right of the window click the drop down and choose $20,000
  • click OK
• Clear filters - click the drop down and choose clear filters.

Creating Subtotals

Subtotals

• Sort by Region
  • Click on cell G9
  • Click the drop down
  • Click on SORT A TO Z
• Take the data out of a table
  • click in the table anywhere
  • Go to the DESIGN tab
  • Go to the TOOLS group
  • Click on Convert to Range
  • Click on YES
• Select the range for the data cells A9:K20
• Add Subtotals
  • Go to the DATA tab
  • Go to the OUTLINE group
  • click on Subtotal
  • At each change in: choose REGION
  • Use function: SUM
  • Add subtotal to: PRIZE MONEY and POTENTIAL
  • check REPLACE CURRENT SUBTOTALS
  • check SUMMARY BELOW DATA
  • Click on OK

Notice there is a new row under each change in region that gives the subtotals

Outline View

Look at the far left of the spreadsheet and you should see the outline groups.

• Row level Symbols
  • Go to the upper left of the window below the cell name and function bar
  • See the boxes with the numbers 1, 2, 3
  • click on the 1 and notice you only get the grand total
  • Click on the 2 and notice you get each region and the grand total
  • Click on the 3 and notice you get all the detail
• Row level bars
  • See the brackets [ on the far left side of the window
  • These show items that are at the same level
    • the set to the right are for each region
    • the set to the left is for the grand total
• show detail symbol
  • When the details are hidden there should be a + on the far left. click on that to expand that section.
• Hide detail symbol
  • When the details are being shown you can click on the - sign to hide the detail for that section.

Remove subtotals

• Go to the Data tab
• Go to the Outline group
• Click on the Subtotal Button
• Go to the bottom of the popup window
• Click on the REMOVE ALL button

Querying Records

Finding Records

Find all the male dogs.

• Go to cell C9
• Click on the drop down arrow
• Check only the box for M
• Click on OK

Find the dogs that are between 2 and 3 years old

• Clear the filter for gender
  • Go to cell C9
  • Click the drop down arrow
  • Click CLEAR FILTER FROM 'GENDER'
• Go to cell E9
• Click on the drop down arrow
• Click on the Number Filters
• Click on BETWEEN
• Fill in 2 in the top field
• Change the bottom Criteria to IS LESS THAN
• Fill in 4 in the bottom field
• Make sure the radio button is set for AND
• Click OK

Clear the filter when you are done.

Forms

Create a Form

• Add the form button to the quick access toolbar
  • Click the more button on the quick access toolbar
  • click on MORE COMMANDS
  • Under Choose Commands From - choose ALL COMMANDS
  • Click on Form
  • Click on ADD
  • Click on OK

Setting a Criteria Range

Titles

• Copy A8:J9 The headings
• Paste in cell A1
• Change the title to Criteria Area

Create Criteria

In row three place criteria to filter the table by.

• Age >3
• Gender = M
• Grade >C

Advanced filter Command

• Click in the table to activate it - Cell A10
• Go to the DATA tab
• Go to the Sort & filter group
• Click on Advanced
• make sure the Action radio button is FILTER THE LIST, IN PLACE
• Make sure the list range is the whole table
• Click in the field for CRITERA RANGE
• Click and drag from A2:J3
• Click OK

Extracting Records

• Copy A8:J9 The headings
• Paste in cell A26
• Change the title to Extract Area
• Click cell A10 to activate the table
• Go to the DATA tab
• Go to the Sort & filter group
• Click on Advanced
• Make sure the Action radio button is COPY TO ANOTHER LOCATION
• Make sure the list range is the whole table
• Make sure the Criteria Range is from A2:J3
• Click in the COPY TO field
• Click and drag A27:J27
• Click OK

Create a spreadsheet with multiple worksheets

Objectives

• Create a Template
• Create and define a new style
• Research to find synonyms
• Create multiple worksheets
• Create Cylinder Charts
• Create headers and footers
• Link data from multiple sheets to a summary page

Vocabulary

• Consolidate - Summarizing data from multiple worksheets on to one worksheet.
• Dummy Data - Sample data used in place of actual data to verify formulas. Usually you check simple numbers and numbers where you expect a change to occur.
• Date Series - Excel will list dates in order for you - Jan, Feb, Mar, ...
• Auto Fill Series - Excel will create a set of the same number over and over again - 1, 1, 1, ...
• Linear Series - Excel will create a set of numbers in order - 1, 2, 3, ... or 2, 4, 6, ...
• Growth series - Excel can create a series that is multiplying by a number from one cell to the next - 2, 4, 8, 16, ....
• Round Function - you can set the number of decimal places by using the round function. =ROUND(number, number of digits) Example: =round(2.34567, 3) would give the value of 2.346
• Format Codes - a series of format symbols that defines how a cell entry assigned a format will appear. Example: [blue]#,##0.00; will make the number appear blue and will round to the closest hundred.
  • '# - digit placeholder Example: ###.## would give the format of any number of digits before the decimal, and only two digits after.
  • 0 - functions like a #, except places a 0 in the ones place if the number is less than 1.
  • . - Make it certain that excel will place a decimal point. Example: #0.00 forces two decimal places.
  • % - Displays numbers as a percent
  • , - Displays a comma to separate the numbers into thousands, millions, ...
  • ( ) - Places parenthesis around negative numbers
  • $ - places a dollar sign in front of numbers making them currency
  • '*' - Makes the dollar sign a fixed dollar sign "$*"
  • [color] - changes the color of the text or numbers
  • "text" - places text in with a number Example "Week" 1 would count as just 1 so you could use it in a formula
  • _ - places a space after the character that follows the underscore.
• Drilling an Entry - Enter a number once in the first worksheet and have it fill into all selected worksheets into the same cell.
• 3-D Range - A range of cells that spans multiple worksheets
• Sheet reference - The name of the sheet you are working with
• Annotate - Add text to define a part of the worksheet or chart, usually with a text box and arrow.
• Comments - Text attached to a cell that describes the function of the cell, they are not printed and should include:
  • worksheet title
  • author's name
  • Date created
  • Date last modified
  • Template used
  • Description of the purpose of the worksheet or cell or formula

Lesson

Create a Template

Titles

• Titles
  • A1 = SSWF Nursery
  • A2 = Nursery Profit Potential
  • Center both of these from A to G (remember you have to do them one at a time)
  • Format to look nice with
    • font style
    • color
    • size
    • shade color
• Column headings
  • A3 = Tree
  • B3 = On Hand
  • C3 = Average Unit Cost
  • D3 = Total Cost
  • E3 = Average Unit Price
  • F3 = total Value
  • G3 = Profit Potential
• Row Headings
  • A4 = Colorado Blue Spruce
  • A5 = Arkansas Amsonia
  • A6 = Angle Trumpet
  • A7 = Hybrid Poplar
  • A8 = Dawn Redwood
  • A9 = Weeping Willow
  • A10 = Lombardy Poplar
  • A11 = Pink Dogwood
  • A12 = flowering Crab
  • A13 = Red Maple
  • A14 = White Birch
  • A15 = Dwarf Apple - Red Delicious
  • A16 = Dwarf Peach

Using Dummy Data

Dummy data is used when you want to check your formulas to see if they are computing correctly. Usually you pick easy numbers to work with to do the math in your head.

• B5 = 1
• B6 = 2
• Select B5:B6
• Fill down to B16
• C5 = 2
• C6 = 4
• Select C5:C6
• Fill down to C16

Enter Formulas

• D5 = B5*C5
• E5 = ROUND(C5/(1-.57),2)
• F5 = B5*E5
• G5 = F5-D5
• Select D5:G5
• Fill down to row 16

Total the columns - AutoSum

Use AutoSum at the bottom of columns: B, D, F and G

Formatting Fonts

• Borders
  • Place a border between rows 4 and 5
  • Make the totals in row 17 formatted like totals from the styles
• Date
  • G3 = now()
  • format the date in G3 to be Day-Month-2010
• Currency
  • select C5:G5 press CTRL D17:G17
  • Right click
  • Choose FORMAT CELLS
  • Choose Currency
  • Make sure it is set to 2 decimal places
  • Choose $ for the symbol (English - US)
  • Choose the fourth format down
  • Click OK

Custom Format Codes

• Select cells C6:G15
• Right click
• Click on Format Cells
• Make sure you are on the NUMBER tab
• Click on Custom under category
• Scroll down and look at all the custom choices
• Click on #,##0.00_);[Red](#,##0.00)
• Change the color (Blue, Green, Yellow) in the TYPE field
• Click OK
• Type in a negative number and see what happenes

Using Research Functions

• Double click in cell A1
• Select the word Nursery
• Go to the REVIEW tab
• Go to the Proofing group
• Click on Thesaurus
• Change the name of the company by choosing a different synonym.
• Type in the new name.

Using Templates

Saving a new Template

• Go to the office button
• Click on SAVE AS
• Change the SAVE AS TYPE to Excel Template
• Find the local drive to save to
• Name the template NURSERY

Applying a Template to a new workbook

• Close out of the workbook
• Open Excel
• Click on new
• Click on My Templates
• You should find the template to open
• Do a Save As and name it SSWF Nursery

Adding new worksheets

At the bottom of the window next to the SHEET 1, SHEET 2, SHEET 3 click on the sheet with the starburst to add a new sheet.

Copying contents from one worksheet to another

• Click in the square in the upper left at the top of the row headers, and to the left of the column headers
  • This should select everything
• Press CTRL+C to copy
• Click on Sheet 4 tab
• Hold down SHIFT and click on SHEET 3 tab
  • this should choose all three tabs
• Press CTRL+V
  • Scroll through the worksheets and notice that they are all the same.

Drilling an entry

This is when you enter data into one sheet and it is automatically entered into all of the sheets in the same cell.

• Click on sheet 1
• click on cell C4
• Hold down Shift
• Click on sheet 3
• Type .99
• press down arrow

Enter in all the date for the Average Unit Costs using this drilling method.

• C5 = 2.99
• C6 = 9.99
• C7 = 3.99
• C8 = 2.97
• C9 = 1.99
• C10 = .99
• C11 = 7.99
• C12 = 19.99
• C13 = 1.50
• C14 = 2.34
• C15 = 9.99
• C16 = 9.99

Modifying the worksheets

• Go to sheet 4
  • Right click the sheet number
  • Click Rename
  • Change the name to: Pittsburgh
  • Go to cell A2
  • Change the title to: Pittsburgh Nursery Profit Potential
  • Enter the date for the Unit on Hand.
• C4 = 546
• C5 = 341
• C6 = 189
• C7 = 624
• C8 = 758
• C9 = 398
• C10 = 826
• C11 = 67
• C12 = 21
• C13 = 418
• C14 = 279
• C15 = 82
• C16 = 57
• Go to sheet 1
  • Right click the sheet number
  • Click Rename
  • Change the name to: Missoula
  • Go to cell A2
  • Change the title to: Missoula Nursery Profit Potential
  • Enter the date for the Unit on Hand.
• C4 = 435
• C5 = 431
• C6 = 209
• C7 = 576
• C8 = 890
• C9 = 417
• C10 = 782
• C11 = 79
• C12 = 35
• C13 = 399
• C14 = 302
• C15 = 91
• C16 = 65
• Go to sheet 2
  • Right click the sheet number
  • Click Rename
  • Change the name to: Grand Junction
  • Go to cell A2
  • Change the title to: Grand Junction Nursery Profit Potential
  • Enter the date for the Unit on Hand.
• C4 = 497
• C5 = 402
• C6 = 170
• C7 = 563
• C8 = 798
• C9 = 416
• C10 = 794
• C11 = 82
• C12 = 41
• C13 = 427
• C14 = 268
• C15 = 107
• C16 = 67

Create a summary sheet

titles

• Go to sheet 1
• Change the name of the tab to COMPANY
  • Right click on the tab
  • Click on Rename
  • Type in the correct name
• Change the color of the tab
  • Right click
  • Go to Tab Color
  • Pick a color
• Change the sub-Title to Nursery Potential Profit

Create 3-D References

Create a 3-D reference for Units On Hand and

• Go to the summary tab
• Go to cell B4
• Click the AutoSum button
• Click on the tab for Pittsburgh
• Hold down SHIFT
• Click on the tab for Grand Junction
• Click on cell B4
• Press Enter

Create a 3-D Cylinder Chart

Create a chart

Make a 3-D cylinder chart of the trees potential profit

• Choose the items to be charted
  • Go to the Summary Sheet
  • Select A3:A16
  • Hold Down CTRL
  • Select G3:G16
• Select the Chart Type
  • Go to the Insert Tab
  • Go to the Chart Group
  • Click on Column
  • Click on the first one to the left under Cylinder
• Move the Chart to its own sheet
  • Click on the Chart
  • Go to the Chart Tools/Design tab
  • Go to the Location Group
  • Click on Move Chart
  • Click on the radio button for own sheet
  • Name the sheet Potential Profit
  • Click on OK

Create WordArt

Add a title to the Chart with Word Art for a special look

• Go to the Insert Tab
• Go to the Text Group
• Click on the WordArt button
• Select the style of Word Art you want
• Type Potential Profit
• Click off the WordArt box
• Place the title at the top of the chart but not over top of any of the columns.
• Go to the Drawing Tools/Format tab
• Play with the WordArt Styles

Format the Chart

Delete the legend

Change the Y-Axis Units

We would like to have less horizontal units lines, so we need to format them.

• Click on the Y-Axis so that each horizontal line has end marks (circles)
• Right click on the Y-Axis
• Click on Format Axis
• Change the Major unit from Auto to Fixed
• Type in 2000 in the units
• Click Close

Change the background color

• Change the side wall
  • Click on the side wall (Y-Axis)
  • Right Click on the front edge of the side wall
  • Click on Format Side Wall
  • Change the colors
• Change the back wall
  • Click on the back wall
  • Right click on the back wall
  • Click on Format Back Wall
  • Change the colors
• Change the Floor
  • Click on the floor
  • Right click on the floor
  • Click on Format Floor
  • Change the colors

Make sure you can still see the columns and the WordArt when you are done.

Create an Annotation

We want to highlight what tree has the most potential.

• Create a Text Box
  • Go to Insert tab
  • Go to the Text Group
  • Click on the Text Box Button
  • Click and drag to draw a text box in the upper left corner of the chart
• Add Text to the text box
  • Type: Greatest Potential Profit
  • Click out somewhere
• Format the Text box
  • Resize the text box to fit the words with the resize handles
  • Right Click on the Text box
  • Click on Format Shape
  • Change the fill color
  • Change the line width and color
  • Change the shadow and angle of shadow
  • Center the text from the HOME tab
• Add an arrow
  • Go to the INSERT tab
  • Go to the Illustrations Group
  • Click on Shapes drop down
  • Click on an arrow
  • Click and drag to draw an arrow from the text box to the tallest column
• Format the Arrow
  • Right click on the arrow
  • Click on Format Shape
  • Change the fill color
  • Change the line color
  • Change the lien style
  • Change the shadow

Insert Comments

Sometimes it is good to leave comments about why a certain cell contains that value. Other times it is good just to leave a comment to the purpose of the whole spreadsheet, but you do not want it to print. In these cases it is good to use comments.

• Add a comment for the markup on price
  • Go to the Summary Tab
  • Go to cell E4
  • Right Click on the cell
  • Click on Insert Comment
  • Type: This is the same as a 232% markup on the price.
  • Click off somewhere

Formatting the workbook

Headers and Footers

To add a header to each page of a document you need to go to page set up. Headers and Footers are different in Excel than in Word.

• Go to the Page Layout Tab
• Go to the Page Set up Group
• Click on the Page Set up More button
• From the page setup dialog box click on the Header/Footer tab
• Click on Customer Header
• Left Section
  • Type in Your Name
  • Press Enter
  • Type Potential Profit
• Center Section
  • Click on the Insert Sheet Name button
• Right Section
  • Type Page and a space
  • Click on The page number button
  • Type OF
  • Click on the number of pages button
• Click OK

Margins

From the page layout tab you can set the margins for your page. If you click on the Custom margins button you can set them as you need.

• Top to 1"
• Bottom to 1"
• Right to 0.5"
• Left to 0.5"
• Click on the center horizontally and vertically check boxes

Print Layout

From the PAGE tab you can set up how the page will layout.

• Click on the PAGE tab
• Select the Landscape radio button
• Click on the fit to 1 page wide by 1 page tall

Sheet layout

• Click on the SHEET tab
• Click on the Grid lines check box
• If you want to print the comments out, you can choose AT END OF SHEET from the comments drop down.

Print Area

If you only want to print a part of a page you can set that up.

• click and drag over the section you want to print
• Go to the Page Layout Tab
• Go to the Page Setup Group
• Click on Print Area
• Click on Set Print Area

Print Multiple Sheets

If you want to print multiple sheets at one time that can also be set up.

• Click on the first sheet tab you want to print.
• If the tabs are next to each other then press SHIFT and click on the next one.

Print Non-Adjacent Worksheets

• If the tabs are NOT next to each other then press CTRL and click on the next sheet you want to print.

Test this out with the print preview.

Excel Basic Exam

Practice

VOCABULARY

Use the web site: http://www.scsite.com/ex2007/learn to practice the vocabulary words.

Play the following games for chapters 1, 2, and 3:

• flash cards
• Who Wants to be a computer Genius?
• Wheel of Terms

The Crossword Puzzle for each chapter is extra credit if you can get the whole puzzle filled in.

PROJECT 1

Use the web site: http://lmigateway.coworkforce.com/lmigateway/analyzer/searchAnalyzer.asp?cat=OCC&session=OCCPROJ&subsession=99&time=&geo=&currsubsessavail=&incsource=&blnStart=True to create a spreadsheet that analyzes

• Colorado
• 2008-2018
• Pick 5 -10 occupations (hold down the CTRL key to pick occupations not next to one another in the list).

1. Create a spreadsheet for the employment and openings data for:
   • Employment 2008
   • Employment 2018
   • Use a formula to compute change in employment from 2008 to 2018
   • Openings due to growth
   • Openings due to replacement
   • Use the AutoSum function to create total openings
2. Create a bar or column graph for openings due to growth and replacement for the occupations you have chosen.

PROJECT 2

Pick 5 stocks from the web site: http://www.nasdaq.com/

1. Create a spreadsheet for the following:
   • List the stock's name
   • List the stock's symbol
   • Choose how many stocks you wish to buy (200 - 1000 of each)
   • Give the date from around 4 years ago that you would have purchased these (pick a date from the history)
   • List how much it cost then as the initial cost
   • Compute the initial cost
   • Give the current cost of the stock
   • Compute the current value of the stock
   • Compute the gain or loss
   • Find the percent gain or loss by taking the gain/loss and dividing by the initial cost.
   • Use conditional formatting to change the color on any percent gain/loss that is negative.
   • Add a row at the bottom for totals
   • Add a row at the bottom for average
   • add a row at the bottom for highest
   • add a row at the bottom for lowest
2. Create a Pie Chart for current value of your stocks
   • do NOT have a legend
   • Have a title
   • Use data labels on the wedges for catagories
   • Use data labels on the wedges for percents

EXAM

VOCABULARY

http://www.yacapaca.com

>Excel Basics Test

PROJECT 1

College Admission Requirements Instructions

1. Visit http://www.collegeincolorado.org
   • Log in
   • Go to the PLAN tab
   • Click on the link for Higher Education Opportunities and Admission Requirements
   • Click on High School Coursework Planner
   • Click on High School Planner Subjects
   • Click on Add Higher Education Institution (add at least four colleges or universities)
   • Click on Summary
2. Create a Spreadsheet of the data for Required and Recommended courses
3. Format the spread sheet professionally, not necessarily how it is shown here
4. Fill in the Total Row
5. Fill in the Average Row
6. Fill in the Total Column
7. Create a Column Chart for the Schools Required and Recommended courses
   • 1 column chart
8. Place the Column Chart on the same sheet as the table of data
9. Create a Pie Chart for each school showing Required Courses
   • 4 pie charts
   • Do not include column A
   • Make sure Row 3 has no merged cells
   • Think about what the pie wedges represent and the value they have.
10. Place the Pie Charts all on one sheet and separate from the table of data - NOT A NEW SHEET

Sample Table for Required Course Analysis

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	College Enrollment Course Requirements
2	Your Name
3		Social Science		English		Math		Science		Foreign Lang.		Other
4		Required	Recommended	Required	Recommended	Required	Recommended	Required	Recommended	Required	Recommended	Required	Recommended	Total
5	School 1
6	School 2
7	School 3
8	School 4
9	Total
10	Average

College Admission Requirements Rubric

admission requirement rubric

Standards	1 Points	2 Points	3 Points	4 Points	5 Points
Format a Spreadsheet Professionally	Missing four or more elements	Missing three elements	Missing two elements	Missing one element	User has taken into account color scheme, cell styles, spacing, and font size and style.
Use of AutoSum Correctly	Major mistakes were made	3 minor mistakes were made	2 minor mistakes were made	1 minor mistake was made	User has used the AutoSum function in the correct cell(s), with the correct range, and with the correct format
Use of Average Function Correctly	Major mistakes were made	3 minor mistakes were made	2 minor mistakes were made	1 minor mistake was made	User has used the Average function in the correct cell(s), with the correct range, and with the correct format
Research Data on the Internet	User needed major help to get data from the web site	User needed assistance three times	User needed assistance twice	User needed assistance on one step	User has found the proper web site, followed the directions to fill in the form correctly to access the data, and can bring up necessary information.
Create Column Chart	Chart is missing four or more items	Chart is missing three items	Chart is missing two items	Chart is missing one item	Column chart contains the proper x-axis labels, y-axis labels, legend, bars, and title
Create Pie Charts	Chart is missing four or more items	Chart is missing three items	Chart is missing two items	Chart is missing one item	Four Pie charts are created with the proper data in the wedges for each one, Data labels were used correctly, a chart title was added.
Make Chart Embedded and Sheet Tab Chart	None of the charts are where they belong	Three charts are not where they belong	Two charts are not where they belong	One chart was not where it belonged	Column chart was embedded with data table, four pie charts are on a separate sheet (not a new sheet)
Print as Two Pages	Many pages were printed - no evidence this person used print preview	There is a fifth page printed	There is a fourth page printed	There is a third page printed	Data table and column chart are printed on one page, four pie charts are printed on one page.

PROJECT 2

Create a Projected Profit Spreadsheet - Instructions

The FBLA is going to purchase a screen printing machine to start a business of creating shirts for the various teams and clubs of the school. They will need to find an organization to put up the initial cost of $8000 for the machine. They proposal will be to pay back this organization the full amount by making payments of 50% of the profit on each batch/sale. Analyze the data to figure out about how long it will take to pay back the loan.

Create the following Table:

Table for Tracking Profits

	A	B	C	D	E	F	G	H
1	FBLA SCREEN PRINTING
2	Meeker High School
3		Year 1	Year 2	Year 3	Year 4	Year 5	...	Total
4	Volleyball
5	Football
6	Golf
7	Cross Country
8	Boy's Basketball
9	Girl's Basketball
10	Wrestling
11	Track
12	Baseball
13	Rec. Center
14	Total Shirts Sold
15	Total Profit
16	Amount Paid Back to Donator
17	Running total Paid Back
18	Total left on Loan
19
20	ASSUMPTIONS
21	Original Cost of Equipment	$8,000.00
22	% Profit back to Donator	50%
23	Markup per Shirt	$5.00
24	Sales Growth	2%

Italic text

1. Fill in the estimated number of shirts each group would sold for year 1.
2. Compute the number of shirts sold for year 2 = prior year *(1+sales growth)
   • Compute the rest of the years sales with the same basic formula
   • You might have to have more than 5 years to pay this off
   • Make sure you look at your absolute and relative references on your formulas
3. Find the total shirts sold for each year
4. Compute the Total Profit = total shirts sold * Markup per shirt
   • Make sure you look at your absolute and relative references on your formulas
5. Compute Amount Paid back to Donator = 50% of Profit
   • Make sure you look at your absolute and relative references on your formulas
6. Compute Running Total Paid Back = Running Total Paid Back previous year plus Amount Paid Back this year
   • You will need to think on your own
   • Logically how much do you have paid back the first year?
   • Logically how can you find out how much is paid back the first two year?
   • What can your formula be so you can use the fill handle?
   • Make sure you look at your absolute and relative references on your formulas
7. Compute Total left on Loan = Original cost of the Equipment - Running Total Paid Back
   • Make sure you look at your absolute and relative references on your formulas
8. Create a Conditional Formatting on the Left on Loan to change the color of font and fill color when the value goes negative.
9. Print the spreadsheet on one page
10. Print the Formula View to one page
11. Adjust the Assumptions to figure out how the loan could be paid off in one less year.
    • You may not change that the equipment costs $8,000.00
12. Print the spreadsheet on one page

Create a Projected Profit Spreadsheet - Rubric

What-If Analysis Rubric

Standards	1 Points	2 Points	3 Points	4 Points	5 Points
Format a Spreadsheet Professionally	Missing four or more elements	Missing three elements	Missing two elements	Missing one element	User has taken into account color scheme, cell styles, spacing, borders, fill colors, and font size and style.
Creates Spreadsheet Correctly	Major mistakes were made	3 minor mistakes were made	2 minor mistakes were made	1 minor mistake was made	Recreates the spreadsheet correctly with no errors
Makes a LOGICAL guess on numbers of shirts sold	Major mistakes were made	3 minor mistakes were made	2 minor mistakes were made	1 minor mistake was made	User makes a logical guess for our high school on the numbers of shirts we could sell.
Uses Sales Growth Correctly to compute future years sales.	User needed major help to get the formula to work	User needed assistance three times	User needed assistance twice	User needed assistance on one step	User can create the formula to compute sales growth and figure future sales for each year accurately using absolute and relative references.
Computes total profit using assumptions	User needed major help to get the formula to work	User needed assistance three times	User needed assistance twice	User needed assistance on one step	User can create the formula to compute total profit for each year accurately using absolute and relative references.
Compute amount paid back to donator	User needed major help to get the formula to work	User needed assistance three times	User needed assistance twice	User needed assistance on one step	User can create the formula to compute amount paid back to donator for each year accurately using absolute and relative references.
Compute the running total paid back	User needed major help to get the formula to work	User needed assistance three times	User needed assistance twice	User needed assistance on one step	User can create the formula to compute running total paid back for each year accurately using absolute and relative references.
Computes the total left on loan	User needed major help to get the formula to work	User needed assistance three times	User needed assistance twice	User needed assistance on one step	User can create the formula to compute the total left on the loan for each year accurately using absolute and relative references.
Creates Conditional Formatting	User needed major help to get the formula to work	User needed assistance three times	User needed assistance twice	User needed assistance on one step	User can create conditional formatting on total left on loan to show negative values as a different color font and fill color.
Uses What-If Analysis	User needed major help to get the assumptions to work	User needed assistance three times	User needed assistance twice	User needed assistance on one step	User can use the assumptions to adjust the data in the spreadsheet to answer questions for projection data.
Prints the spreadsheet	Only printed one page and it is not fit to one page	Missing two and/or and not fit to a page	Missing a page and more than one is not fit to a page	Missing a page, or not fit to one page	Print the original spreadsheet filled in, prints in regular and formula view. Then prints again with the what-if assumptions filled in. A total of three pages should be printed forcing each to fit to one page.

Create a basic two table database with reports

Vocabulary

• Database - a collection of organized data that allows you to look up, edit or report on it.
• Table - Stores information about one specific kind of item
• Record - The rows in a table that contain information about a given person, product, or event.
• Field - The columns in a table that contain a specific piece of information like the city, state, or phone number.
• Query - The ability to access the data by asking questions. Getting out all the companies that are in a certain city, or all the clients with the last name of Smith.
• Forms - Attractive layouts used for entering or looking up records in a table.
• Reports - Attractive layouts used for printing out the data with sophisticated presentation.
• Web Page - A SharePoint for information that is stored in Access so it can be seen on the web.
• Primary Key - a unique identifier for a record. There can be only one. Every record must have a different value in a field that is set up to be a primary key. If it is a table of products and the PRODUCT ID is the primary key, no two records can have the same ID.
• Data type - Specifying the type of data a certain field will hold. Access needs to know if you are going to work with a number, text, a time/date or a hyperlink.
• Field Size - Specifying how large the field will be. Access needs to know how large a text box needs to be created to hold text type data.
• Design view - A view that allows you to manipulate how data will be created, stored, and displayed on the screen and in reports.
• Datasheet view - A view of a table that is much like a spreadsheet. It allows you to enter in your information quickly.
• Report View - Once you create a report, you need to turn it to report view to preview it on the screen.
• Form View - Once you create a form, you need to turn it to form view to enter data, or use if for lookup.
• Form Layout - Once you create a form, you need to turn it to layout view if you wish to move items around on your form.
• Report Wizard - A tool that allows you to create a report by simply choosing what table and fields in the table you want to display. The report is built for you very quickly.

Lesson

What is a database?

A database is a collection of organized tables. Each table is a collection of related records. Each record gives information about one item and is made up of many fields. Each field is one specific piece of information.

In this figure you can see the Clients Table is open in the Access work area. It contains 10 records, one for each client. There are nine fields: Client number, Client name, Street Address, City, State, Zip Code, Amount Paid, Type of Advertisement, and Employee.

Guidelines

Before you start to create a database, there needs to be a lot of planning. The first question should be: What is the purpose of the database? I usually want to know, what do I want to be able to pull back out of the database. So I guess I work backwards from the reports to figure out what will need to be put in.

1. What is the purpose of the database
2. Who all will use the database
3. What will each type of user need to be able to do
   • What reports does each user need
   • What forms does each user need
   • What transactions will each user need to document
4. Determine the tables that you will need
5. Determine the primary key fields
6. Determine all other fields that you will need in each table
7. Determine data types for each field
8. Determine field size for each field
9. Determine the relationship between the tables
10. Determine what fields will need to link the tables together
11. Remove any redundancy in the tables

Requirements

The yearbook staff need to keep track of their yearbook ad sales to the businesses in the community. In order to do this they will need to track the businesses of the community and what businesses have purchased ads, what types of ads they have purchased and if they have paid. They will also need to keep track of each student, in this case employee, to determine who has sold the ads. The staff will need to keep track of two basic groups of data, the businesses and the yearbook staff members. For this example we will call the businesses - Clients, and the staff members - Employees. For the clients we will need to know:

• Client Number
• Client Name
• Street Address
• City
• State
• Zip Code
• Amount Paid
• Type of Advertisement
• Employee Number

For the Employees we will need to know:

• Employee Number
• Last Name
• First Name
• Street Address
• City
• State
• Zip Code
• Number of Ads Sold
• Dollar Value Sold

Name the tables and fields

• They must not be longer then 64 characters
• You can use letters, integers, spaces, and some punctuation marks
• They cannot contain: periods (.), exclamation points(!), asterisks(*), question marks(?), accent marks(`) or square brackets([])
• The same name cannot be used twice
• Pick a logical naming convention so you can find your information quickly
  • Use upper cases to start words - Client Name
  • Use no space between words - ClientName
  • Use an underscore between words - Client_Name

We will call the two tables: Clients and Employees

Primary Keys

Each table needs to have a primary key. This is the one value in each table that can not be repeated, it is considered the look-up value. This field is used to easily access all the data for one record and usually has a short field length. For this example we will use the:

• Employee Number
• Client Number

Relationships between tables

When you design a database you need to be able to tie the data together. In most databases you have one table that is used once and tied to many records in another table. In this example the Client is used once, and each client is tied to an employee. So, each record in the employee table is used many times. This is called a One to Many relationship. Employee A works with

• Client 1
• Client 2
• Client 3
• Client 4

Data types

When you are creating the fields you also need to think about what type of data you are collecting in each field. Here is a run down of each data type:

Structure of the Client Table

Data Type	Description
Text	255 Characters of any type of character including numbers
Memo	Used when you want more than 255 characters of space and can contain any type of character
Number	Only numbers that are used for some type of arithmetic manipulation. If you do not plan on adding, subtracting, multiplying or dividing the number it is safer to make it type text.
Currency	Used for money and will auto-format to contain the dollar sign, commas and a two digit decimal.
Date/Time	Used when you are going to store a date or a time
AutoNumber	This field will start at 1 for the first record and each additional record will be one higher. This is great for use with invoice numbers.
Yes/No	This field is a check box that stores either yes/no, true/false, or on/off.
OLE Object	An object linked or embedded in the table
Hyperlink	Text that can be used as a hyperlink address
Attachment	This field will contain an attached file. This is handy if you are doing an on-line application and want have the user attach a resume.

Redundancy

The point to using a database instead of a spreadsheet is the ability to link tables together. This is useful for reducing redundancy. Redundancy is the repetition of the same data over and over again. For example if you are tying clients to employees, you would not want to have to tie the same employee name, address, city, state, and zip to every record. Instead you can link them both together with an employee number and store the data in separate files. By storing the data in separate file it reduces the risk that data will not be updated properly. Think about if an employee moves and the employee data is in the same table as the clients. You would have to go through all the records that had that employee and change the same data for each record that employee works with.

Client Table

From the Access Window click on the:

• Create Tab
• Go to the TABLE Group on the far left of the ribbon
• Click on the TABLE button

Click on View and Design View Click on OK Then fill in the fields on the design view of the form to follow the structure of the table below. You will need to enter the field name, data type, and field size as the arrows above show.

Structure of the Client Table

Field Name	Data Type	Field Size	Primary Key
Client Number	Text	4	Yes
Client Name	Text	25	No
Street Address	Text	30	no
City	Text	20	no
State	Text	2	no
Zip Code	Text	5	no
Amount Paid	Currency		no
Type of Advertisement	text	20	no
Employee Number	text	2	no

Once you are done putting in the field definitions then you will need to put in the data.

• Click on VIEW
• Click on Datasheet View

Then you can enter the data just like a spreadsheet. HINT: If you do not like entering the data this way you can click on FORM and it will create a form for you to enter the data into a form.

Client Table Data

Client Number	Client Name	Street Address	City	State	Zip Code	Amount Paid	Type of Advertisement	Employee Number
ROLA	Rosken LLC Accountants	592 Main St Suite 1	Meeker	CO	81641	$40.00	Page Sponsor	KA
COAV	Coulter Aviation	921 Market Street	Meeker	CO	81641	$50.00	Business Card	DW
MEAI	Meeker Airport	921 Market Street	Meeker	CO	81641	$40.00	Page Sponsor	WS
BRLA	Brooks Laurie J Appraiser	889 Main Street	Meeker	CO	81641	$250.00	Full Page Ad	EJ
ROMO	Rocky Mountain Bowstrings	696 Main Street	Meeker	CO	81641	$50.00	Business Card	WS
ANTO	Ann Toney PC Attorney	PO Box 1022	Meeker	CO	81641	$40.00	Page Sponsor	EJ
ZABR	Zagar-Brown Trina K Attourney	685 Main Street Suite 5	Meeker	CO	81641	$150.00	Half Page Ad	KA
BOKE	Borchard Kent A Attourney	335 6th St #1	Meeker	CO	81641	$50.00	Business Card	KA
MECO	Meeker Collision Center	43904 Hwy 13	Meeker	CO	81641	$40.00	Page Sponsor	DW
NOAU	Northwest Auto	485 Market Street	Meeker	CO	81641	$50.00	Business Card	EJ

After you are done putting in all the data for the Clients you are ready to create the table for the employees and put in that data. You will follow the same steps as above.

1. Go to Create - Table
2. Go to View - Design View
3. Name the table Employees
4. Set up the fields as list in the table below

Employee Table

Structure of the Employee Table

Field Name	Data Type	Field Size	Primary Key
Employee Number	Text	2	Yes
Last Name	Text	20	No
First Name	Text	20	No
Street Address	Text	30	no
City	Text	20	no
State	Text	2	no
Zip Code	Text	5	no
Number of Ads Sold	number	2	no
Dollar value of Ads Sold	Currency		no

Once you have created the fields for the table then go to VIEW - Datasheet View and type in the data as listed below.

Employee Table Data

Employee #	Last Name	First Name	Street Address	City	State	Zip Code	# of Ads sold	$ value of Ads sold
KA	Kippers	Amanda	123 Malle Street	Meeker	CO	81641	3	$240.00
DW	Downing	Will	312 Howe Drive	Meeker	CO	81641	2	$90.00
EJ	Eston	Jim	213 Pawnee Way	Meeker	CO	81641	3	$340.00
WS	Wilson	Sophie	332 Wilbert Way	Meeker	CO	81641	2	$90.00

Create a form

The easiest way to create a form is to have the table open that you want the form to populate.

• Click on the Create Tab
• Go to the Forms Group
• Click on FORM

The from is created and you are in layout view. You can move items around from this view, but when you are ready to enter data you will need to change to Form View.

• Go to the HOME Tab
• Click View
• Click Form View

Start entering data. If you want to look through the records then use the buttons at the bottom of the screen they are just like those on all the electric devices you all use today. If you want to enter new data then click on the button that is the blue arrow pointing right with a star. Enter in the new data for the first field

• Press TAB to go to the next field
• Press SHIFT+TAB to go to the previous field
• Press TAB at the end of the fields on the screen will save that record and bring you to the next empty record.

Print a table

To print a table have the table open:

• Go to the Office Button
• Click Print arrow
• Click on Print Preview
• Change it to landscape if needed
• Adjust the margins if needed

NOTE: You do not have the printing control like you did with Excel, if you need a better format that is when we create a report.

Create a report

• Close all the tables and forms.
• Click on the CREATE tab
• Go to the REPORTS group
• Click on REPORT WIZARD

• Set the table to Clients
• Click on the greater than sign to move the selected field to the right (fields on the right are put into the report)
• Move the following fields to the report:
  • Client Number
  • Client Name
  • Amount Paid
  • Type of Advertisement
  • Employee Number
• Click on NEXT

• For this report we will not do any groupings and will leave this for a more advanced topic
• Click NEXT

• Click the Drop down and choose the Employee Number (This will sort the report by employee number giving a list of clients for each employee.)
• Click Next

• Leave this set to tabular, you are welcome to play with this and see what each one will do.
• click Next

• Look at the different styles and choose the type that you like the best
• Click on NEXT

File:AC1-Report Wizard Title.jpg

• Type in the name of the report
• This is the name that will print across the top of the report so watch your typos
• Click FINISH

Print a report

Go to the office button and click on PRINT

Project

Create a database to keep track of a business of your choosing. You must have:

• multiple tables - most of you will need four tables
  • Client table (with at least 10 records)
    • Client number
    • First name
    • Last name
    • Street address
    • PO Box
    • City
    • State
    • Zip Code
    • Phone number
    • Employee number - depending on your business
    • other fields that fit your business
  • Employee table
    • Employee number
    • Last name
    • First name
    • Street address
    • PO Box
    • City
    • State
    • Zip Code
    • Phone number
    • Pay Rate (if this fits your business)
    • Other fields that fit your business
  • Inventory or Services table (depending on your business)
    • Item Code
    • Item Description
    • Item Cost
  • Sales or Appointment table (depending on your business)
    • Invoice number - auto number
    • Client Code
    • Employee Code
    • Time
    • Date
    • Service or item
    • Quantity of item or length of time on service (if this fits business)
• You can have more tables to keep track of your business if you need
• Create a form for entering data and looking up data
• Create a report that matches your business such as:
  • Invoice
  • Billing Summary
  • Appointment Schedule
• Turn in a print out of:
  • each table
  • each report

Rubric

Create a Business Database

Standards	1 point	2 points	3 points	4 points	5 points
Choose a business that fits your interests	Could not identify a business, teacher assigned one.	Identified a business that could not be investigated or one that did not merit investigation.	Identified, with adult help, a business which could be investigated.	Identified, with adult help, a business which was interesting to the student and which could be investigated.	Independently identified a business which was interesting to the student and which could be investigated.
Creates Tables for Business	Some tables in the database had a function and clearly served to illustrate some aspect of the business.	Each table in the database had a function and clearly served to illustrate some aspect of the business. Some tables had most fields that were correctly labeled with name, data type and size.	Each table in the database had a function and clearly served to illustrate some aspect of the business. Most tables had most fields that were correctly labeled with name, data type and size.	Each table in the database had a function and clearly served to illustrate some aspect of the business. All tables had most fields that were correctly labeled with name, data type and size.	Each table in the database had a function and clearly served to illustrate some aspect of the business. All tables had fields that were correctly labeled with name, data type and size.
Creates Forms for Business	Creates a form for a table	Creates a form with adult assistance that allows for easy data entry and retrieval from a table.	Creates a form with no adult assistance that allows for easy data entry and retrieval from a table.	Accurately creates a form with some assistance that allows for easy data entry and retrieval from a table.	Accurately creates a form with no adult assistance that allows for easy data entry and retrieval from a table.
Creates a Report for the Business	Creates row for total	A report is created for the sales or appointments table.	A report is created for the sales or appointments table that shows understanding of why the report is needed. The title is descriptive.	A report is created for the sales or appointments table that clearly shows understanding of why the report is needed. The title is descriptive, and the data is sorted.	A report is created for the sales or appointments table that clearly shows understanding of why the report is needed. The title is descriptive, and the data is sorted in an appropriate manner.
Prints Tables and Reports for the Business	Missing 4 or more items	Missing 3 items	Missing 2 items	Missing 1 item or the Report prints on more than one page	All tables and forms are printed correctly, stapled with the report on top, and name on the back.

Create queries for a database

Vocabulary

• Parameter
• Wildcards
• Compound Criteria
• Sort Key
• Major Key / Primary Sort Key
• Minor Key / Secondary Sort Key

Lesson

What is a Query

A query is a way of asking the database some questions.

• What clients' name begins with "M"?
• What clients are located on Main Street?
• What clients have paid more than $100.00?
• What clients belong to a certain employee?
• What client names begin with "M" and has paid more than $100.00?

Simple Query Wizard

• Go to the CREATE Tab
• Go to the OTHER group on the far right
• Click on Query Wizard
• This is just like creating a report
  • Pick the table you want to query
  • Pick the fields you want to look at
  • Click NEXT
  • Type in the title of the Query
  • Click FINISH

Use the drop downs at the top of each column to sort, or limit the data.

Criteria Query

• Click on View
• Click on Design View

• Go to the Criteria for the Client Number and type in BOKE
• Click the exclamation point for RUN at the top
• The query will return only the records with an employee number of BOKE.

Wildcards in Query

You use a wildcard to fill in for characters that you don't know. If you wanted to find a client whose name began with B you could do a query for B* like below.

Then when you run the query you get the results:

You can also use a question mark for a wildcard. A question mark replaces one character only. The asterisk replaces a group of characters. If you had employees Tom and Tim and wanted to list all of their clients you could do this by typing in T?M into the criteria field.

AND Criteria Query

You can fill in multiple criteria from one column to the next and this will take on an AND relationship.

Here we are querying companies that are on Main Street and paid more than $100.00

OR Criteria Query

If you move one of the query criteria down one row to the OR field you create an OR Criteria.

This query will list all the companies that are on Main Street OR paid more than $100.00

Parameter Query

A parameter query is one that lets the user answer the question each time it is run to get to the records that they want. This is very useful if a help desk person needs to access the records of a client each time they call to look up information quickly.

To set up this query you will need to type [] into the criteria field.

The program will then prompt the user to enter the Employee number when it is run and the following Pop-up will be displayed.

The user would type in an employee number such as EJ and click OK

Access would only display the records for employee number EJ

Top Values Query

A top value query is a query that sorts your fields from ascending to descending.

• Go to Design View on your Query
• Go to the Design Tab
• Change the Return box to the number of records you want
• Change one of the items to be sorted
• Run the query

Join Query

A join query is a query that contains data from multiple tables, those tables are all inter-connected through various relationships.

• Click the Create Tab
• Go to the Other Group
• Click on Query Design
• Click on the tables and then ADD, one at a time
• Make sure the tables are RELATED - have a line joining them
  • If they do not go to the Database Tools Tab and fix the relationship
  • Or make sure the names are typed exactly the same
  • Make sure the data types are the same
  • Make sure the field size is the same
• Click and drag fields from each table into the query
• Click on RUN

Calculation Query

Sometimes you will want to calculate a value from values that you already have.

• Create a query that has more than one either number, or currency field
• Go to Design View
• Go to the first column after all the fields you have already created
• Right click in the Field row
• Click on Zoom
• Type in the name of the column and then a colon
• Type in the field names in [] and then type the operation between the fields, do not put in any spaces
  • total cost:[amount owed]-[amount paid]
• Click OK
• Run the Query and then save the file in the required location

Calculate Statistics

• Create a query of Employee Number and Amount Paid from the client table
• Go to Design View
• Click on the TOTAL button at the top in the ribbon
• In the total row under Employee Number make sure it says Group By
• In the total row under Amount Paid change it to SUM or AVG
• Run the Query

Crosstab Query

A crosstab query is a query that lets you specify which fields contain row headings, which field contains column headings, and which field contains values to summarize.

• Click Create TAB
• Click Query Wizard
• Choose Crosstab Query and OK
• Click the table you want to work with - Client and click OK
• Move the Type of Advertisement to the Selected Fields side
• Click Next
• Click Employee Number
• Click Next
• Click Amount Due under field
• Click SUM under Function
• Click Next
• Type in a name for this query
• Click Finish

Sorting

There are two ways to sort your query:

From the query you can click the drop down and choose ascending or descending.

From the Design View you can choose different fields. The field to the left is the primary sort key.

In this shot this will sort by the Clients and then by how much they have paid

Sort and Omit Duplicates

To omit duplicates, 1st. You will need to click the box or field right of the row that you wish to omit duplicates, then you will need to open the Properties Sheet from the ribbon. Then go to the Unique Values item and change that to YES.

Project

Project 2 Creating Queries: Create the following Queries and print the results for your business:

1. Create a simple query on your table that contains the 10 records.
   1. Use the drop down to limit the data displayed
   2. Print the results
   3. Write on the paper what you did, in a full sentence so I can read and understand it.
2. Use the Query you created for #1
   1. Do an AND criteria query
   2. Print the results
   3. Write on the paper what you did, in a full sentence so I can read and understand it.
3. Use the Query you created for #1
   1. Do an OR criteria query
   2. Print the results
   3. Write on the paper what you did, in a full sentence so I can read and understand it.
4. Create a parameter query
   1. Show the teacher that it works
5. Create a Top Value Query from one of your tables
   1. Print the results
   2. Write on the paper what you did, in a full sentence so I can read and understand it.
6. Use the Query you created for #1
7. Create a join table query that uses your sales/invoices/appointments table
   1. Pull in all data from the different tables to show what is needed for the document
   2. Add a calculation for the total
   3. Create a report from the query
   4. Print the report
8. Staple all the items together
9. Make sure you name is on it

Rubric

Create Queries for Business

Standards	1 point	2 points	3 points	4 points	5 points
Create a Simple Query	Creates a Query on the 10 record table. It is printed with the students name on it.	Creates a Query on the 10 record table that uses a drop down to limit the data. It is printed with the students name on it.	Creates a Query on the 10 record table that uses a drop down to limit the data. It is printed with the students name on it and an attempt to explain what their query represents.	Creates a Query on the 10 record table that uses a drop down to limit the data. It is printed with the students name on it and an explanation of what their query represents.	Creates a Query on the 10 record table that uses a drop down to limit the data. It is printed with the students name on it and an accurate explanation of what their query represents.
Creates a Query that uses AND logic	Creates a Query on their data and then tried to use the criteria and AND logic to limit what is displayed. The query results are printed with the students name on it.	Creates a Query on their data and then uses the criteria and AND logic to limit what is displayed. The query results are printed with the students name on it.	Creates a Query on their data and then uses the criteria and AND logic to limit what is displayed. The query results are printed with the students name on it and an attempt to explain what the data represents.	Creates a Query on their data and then uses the criteria and AND logic to limit what is displayed. The query results are printed with the students name on it and an explanation of what the data represents.	Creates a Query on their data and then uses the criteria and AND logic to limit what is displayed. The query results are printed with the students name on it and an accurate explanation of what the data represents.
Creates a Query that uses OR logic	Creates a Query on their data and then tried to use the criteria and OR logic to limit what is displayed. The query results are printed with the students name on it.	Creates a Query on their data and then uses the criteria and OR logic to limit what is displayed. The query results are printed with the students name on it.	Creates a Query on their data and then uses the criteria and OR logic to limit what is displayed. The query results are printed with the students name on it and an attempt to explain what the data represents.	Creates a Query on their data and then uses the criteria and OR logic to limit what is displayed. The query results are printed with the students name on it and an explanation of what the data represents.	Creates a Query on their data and then uses the criteria and OR logic to limit what is displayed. The query results are printed with the students name on it and an accurate explanation of what the data represents.
Creates a query that uses a parameter	Student was able to create a parameter query did not ask for help and had issues. They query results are printed with the students name on it.	Student was able to create a parameter query with help that ran. They query results are printed with the students name on it.	Student was able to create a parameter query with help that ran correctly.They query results are printed with the students name on it and an attempt to explain what the data represents.	Student was able to create a parameter query with minor help that ran correctly. They query results are printed with the students name on it and an explanation of what the data represents.	Student was able to create a parameter query independently that ran correctly. The query results are printed with the students name on it and an accurate explanation of what the data represents.
Create a Top Value query	Student was able to create a top value query did not ask for help and had issues. They query results are printed with the students name on it.	Student was able to create a top value query with help that ran. They query results are printed with the students name on it.	Student was able to create a top value query with help that ran correctly.They query results are printed with the students name on it and an attempt to explain what the data represents.	Student was able to create a top value query with minor help that ran correctly. They query results are printed with the students name on it and an explanation of what the data represents.	Student was able to create a top value query independently that ran correctly. The query results are printed with the students name on it and an accurate explanation of what the data represents.
Create a Joined Table query	Student was able to create a Joined Table query did not ask for help and had issues. They query results are printed with the students name on it.	Student was able to create a Joined Table query with help that ran. They query results are printed with the students name on it.	Student was able to create a Joined Table query with help that ran correctly.They query results are printed with the students name on it and an attempt to explain what the data represents.	Student was able to create a Joined Table query with minor help that ran correctly. They query results are printed with the students name on it and an explanation of what the data represents.	Student was able to create a Joined Table query independently that ran correctly. The query results are printed with the students name on it and an accurate explanation of what the data represents.
Add a Calculation to the Joined Table Query	Student was able to create a Calculation did not ask for help but had issues. The query results are printed with the students name on it.	Student was able to create a Calculation with help that ran. The query results are printed with the students name on it.	Student was able to create a Calculation with help that ran correctly. The query results are printed with the students name on it and an attempt to explain what the data represents.	Student was able to create a Calculation with minor help that ran correctly. The query results are printed with the students name on it and an explanation of what the data represents.	Student was able to create a Calculation independently that ran correctly. The query results are printed with the students name on it and an accurate explanation of what the data represents.

Using and updating a database

Microsoft Access is a Relational Database Management System (RDBMS) From Microsoft Corporation. It is suitable for small to mid-sized database projects, or as a front end to large DBMS, like MS SQL and MySQL.

Access Basic Exam

Vocabulary

http://www.yacapaca.com Go to the web page and take the Access Basics Test.

Project 1

1. Open the Database found at this web site: Bob's Bike Rental
2. Save As the database to your drive so you can edit it
3. Create a Form for entering a new Client
4. Enter yourself into the database as a client
5. Create a new table called RENTALS that contains the following fields
   • Employee ID - DataType: Text - Size 4
   • Client ID - DataType: Text - Size 4
   • Bike ID - DataType : Text - Size 4
   • Time Out - DataType: Date/Time
   • Time In - DataType: Date/Time
6. Add 4 records to the Rentals table - check for IDs from the other tables
7. Create a Simple Query on the Clients Table
   • Pick all the fields in the table for the query
   • Run a query to find all Client IDs that have an A in them.
   • Print the Query
8. Create a Simple Query on the Rentals Table
   • Run an AND query to find all the clients who left after 10:00 AM and came back before 12:00 PM
   • Change or add a record that fits this query so it is not empty
   • Print the Query
9. Create a Joined Table Query called RENTAL INVOICE QUERY that includes:
   • Employee ID from the Rental Table
   • Employee Last Name from the Employee Table
   • Client ID from the Rental Table
   • Client First Name from the Client Table
   • Client Last Name from the Client Table
   • Client Phone Number from the Client Table
   • Bike ID from the Rental Table
   • Bike Description from the Bike Table
   • Bike Rental Cost from the Bike Table
   • Time out from the Rental Table
   • Time In from the Rental Table
   • Compute the amount of time the bike was rented - Total Time:([Time In]-[Time Out])*24
   • Compute the total amount due for the rental - Total Cost:[Total Time]*[Bike Rental Cost]
10. Create an invoice Report from the Joined Table Query
    • Print the Report
    • For Extra Credit Make the Report Look Better - columns wider to fit the data and other changes that make it look better
    • For Extra Credit Make the Report Group by Client

Project 1 Rubric

Bike Rental Rubric

Standards	1 Points	2 Points	3 Points	4 Points	5 Points
Create a Form	Needs teacher assistance to create a form	Can create a form but is missing three key elements	Can create a form independently but is missing two key elements	Can Create a form independently but is missing one key element	Can Create Form completely on their own for entering a new client.
Add a Record to a Table	Needs teacher assistance to create records into the table.	Has some troubles in creating records into a table.	Can create records into a table but much of the data does not fit with other tables	Can create records into a table but the data does not fit with another table	Can create records into a table that fit with the other tables completely
Create a Table	Can create a table that meets the criteria of the database, but is missing four of the elements for the field definitions.	Can create a table that meets the criteria of the database, but is missing three of the elements for the field definitions.	Can create a table that meets the criteria of the database, but is missing two of the elements for the field definitions.	Can create a table that meets the criteria of the database, but is missing one of the elements for the field definitions.	Can create a table that meets the criteria given for the database. All field names, DataTypes and field sizes are accurate.
Create a Query	Is missing four or more elements, or needed teacher assistance to create the query	Is missing three elements	Is missing two elements	Is missing one element	Can create a simple query on one table of a database with all fields in the query.
Limit a Query with Criteria	Needs teacher assistance to limit the query by a criteria	Can independently limit a query	Can independently limit a query to return values	Can independently limit a query to return the desired values	Can accurately and independently limit a query to return the desired values
Run a Query with AND Criteria	Needs teacher assistance to limit the query by a criteria	Can independently limit a query with AND logic	Can independently limit a query to return values with AND logic	Can independently limit a query to return the desired values with AND logic	Can accurately and independently limit a query to return the desired values with AND logic
Create Joined Table Query	Teacher assisted creating the joined table query	Joined Table query is missing three elements	Joined Table query is missing two elements	Joined Table query is either missing one field, or it is from the wrong table	Joined Table Query includes all of the fields from the correct table
Create Calculations in a Query	Teacher gives assistance to create calculations	Independently creates the calculation	Independently creates the calculation accurately	Independently creates the calculation accurately and it returns values	Independently creates the calculation accurately and it returns the correct values
Create a Report from a Query	Report is missing four or more elements	Report is missing three elements	Report is missing two elements	Report is missing one element	Report is created from the joined table query, prints to one page, and includes all information.

PowerPoint Terminology

• Ask A Question Box - Provides quick access to help.
• AutoContent Wizard - A wizard that produces a presentation with a general structure and suggested topics based on options you choose regarding the presentation output.
• AutoCorrect - Corrects capitalization, grammar, and spelling errors automatically as you type.
• AutoShapes - Ready-made shapes you can draw on the slides in your presentation by choosing a shape from the AutoShapes drop-down menu.
• Clip Art - Professionally designed images that you can add to documents. You can change the size, appearance, and location of clip art after it has been inserted into a document.
• Design Template - The background, fonts, bullets, formatting, and color scheme that you can apply to define the look of your presentation.
• Expanded Menu - A menu that displays both common and less-common commands after you pause the mouse pointer over it.
• Floating Toolbars - Toolbars that are not attached to the edge of the program window.
• Indents - Used to align bullets and text on a slide.
• Line Spacing - The vertical distance between lines of text.
• Normal View - The view you use to create and edit your presentation. It consists of three working areas: the Outline and Slides tabs, the Slide pane, and the Notes pane.
• Notes Pane - In Normal view, the Notes Pane is used to create notes for each slide in your presentation.
• Outline and Slides Tabs - One of the panes in Normal view, you use the Outline and Slides tabs to toggle between an outline of the text in your presentation and a thumbnail view of each slide.
• Presentation - An interaction between a speaker and an audience. A presentation usually includes one or more visual aids: in the case of PowerPoint, these are slides.
• Sizing Handles - Small circles or squares that appear along the border of a selected slide or object.
• Slide Pane - In Normal view, the area in the center of the application window where you create and format your slide content. It displays the current slide.
• Slide Sorter View - All slides in the presentation are displayed at once on the screen. In this view, you can rearrange, copy, and delete slides.
• Task Pane - Provides quick access to commands related to your current task.
• Transition Effects - In a slide show, a transition determines how the display changes as you move from one slide to the next.
• WordArt - A tool you can use to create unique text effects. Text objects you create with read-made effects to which you can apply additional formatting options.

Strategies for College Success

Vocabulary

• Presentation
• Slide Show
• Slide
• Layouts
• Title Slide
• Landscape Orientation
• Placeholders
• Mouse Pointer
• Vertical Scroll Bar
• Horizontal Scroll Bar
• Scroll Box
• Scroll Arrow
• Status Bar
• Document Theme
• View
• Normal View
• Slides Tab
• Outline Tab
• Slide Pane
• Notes Pane
• Notes Page View
• Splitter Bar
• Line Wraps
• Paragraph
• Level
• Multi-Level Bulleted List
• Promoting
• Demoting
• Slide Indicator
• Slide Show View

PowerPoint Features

• Word Processing
• Outlining
• Charting
• Drawing
• Inserting Multimedia
• Saving to a Web Page
• E-mailing
• Collaboration
• Preparing Delivery

Vocabulary Quiz

http://www.yacapaca.com

PP1-College Success

Lesson

Guidelines

PowerPoint is a presentation tool, not for writing research papers.

• Never put more than 7 bulleted items on a page
• Never put more than 7 words in a bulleted item
• Do not write in full sentences
• These are notes to prompt what to talk about next
• The viewer should not get full information from your slides
• The viewer should not pay more attention to your slides than to you
• The slides should give general topics
• The slides should entertain they eye so the ear will listen better
• Don't use lots of different colors - keep it simple
• Make the focus on the slide on one item
• Each slide is for one topic
• A topic can take up as many slides as needed
• Keep fonts to easy to read ones - stay away from scripts and fancy fonts

Ideas to keep in mind

• Don't type in all capital letters, other than titles
• Keep font sizes larger than 24 point
• Choose colors carefully
  • Colors represent emotions - see http://en.wikibooks.org/wiki/Microsoft_Office_2007/Cover_Letter under theme colors
  • Color blind people can not distinguish certain colors
  • It is safest to stick with theme colors as they are

The Document Window

Slides Tab: Outlines Tab:

Create a title Slide

• Start in a new document
• The first slide that appears is a title slide. It has a place for a title and a subtitle. Usually the title is the subject of your presentation, and the subtitle would be the presenters name.

Text on Slide 1

• Type in the title for this presentation: CLASSROOM SUCCESS
• Click into the Subtitle place holder and type in: Presented by
• Press enter and type in: your name

Selecting a Document Theme

Themes add color and style to every slide in your presentation in a consistent and unified fashion.

• Go to the Design Tab
• Find the Theme group
• Click the MORE button to show the Theme Gallery
• Hover over a theme to see a preview
• Click on a theme that matches the content of your presentation

NOTE: you can change the theme at any time. It is also a good idea to get your content in first and then apply the theme after so you are not distracted by the colors. The content is the most important thing.

Adding New Slides

Once you have the first slide created you are ready to add more slides.

• Go to the HOME tab
• Click on the NEW SLIDE button
  • The top part will create a new Text and Content slide
  • The bottom will allow you to choose the style of slide to create.
• Click on the top or choose Text and Content for a standard slide.

Create a Bulleted List

By default the Text Slide has a bulleted list in the bottom portion.

If you needed to create a section with a bulleted list you would:

• Go to the Insert tab
• Go to the TEXT group
• click on TEXT Box
• Click on your slide
• Go to the HOME tab
• Go to the PARAGRAPH group
• click on the BULLETS button

Type in your text for the first line item in your list

• Press enter to go to the next line
• Press TAB to Increase the level of the indent
  • TAB increases the indention level or demotes it or makes it a lower-level paragraph
  • SHIFT+TAB decreases the indention level or promotes it or makes it a higher-level paragraph

Text on Slide 2

Get Organized

• Time management
• Scheduling
• 2 to 1

Notes on Slide 2

Time management skills help balance academic, work and social events

Create a Schedule each week that accounts for all activities

Study 2 hours for every 1 hour you are in class

Text on Slide 3

Listen Actively

• Sit in front
  • No distractions
• Summaries
• Be Prepared
  • Review
  • Preview

Notes on Slide 3

Sit in front to focus your attention

No distractions – don’t tolerate anyone distracting you

Make mental Summaries of material

Be Prepared for class

Review – notes from your book, and from the previous class

Preview material covered that day

Text on Slide 4

Exam Strategies

• Review for a week
  • No cramming
    • Short-term memory
• Read Whole Test
  • Start what you know
    • Think positively
    • Stay focused

Notes on Slide 4

Review for a week – that is 20 minutes a day for 7 days

No cramming this is not effective – you will not remember, it causes stress and sleep deprivation

Short-term memory

Read Whole Test from cover to cover

Start what you know the best and move to what you know lesser

Think positively if you know your going to fail – you will

Stay focused, don’t daydream, answer the question asked.

Scope creep – talking about more than what the question asks you to

Add Graphics to the Slides

Graphics are a nice way to show the listeners what you are going to talk about. They should be used to enhance the meaning and bring attention to the main focus. They should not be used just because you can.

To add a clip art

• Go to the INSERT tab
• Click on the CLIP ART button to open the task pane
• Type in a keyword in the Search For box and press enter.
• Click on the clip art you want to place into the slide.
• Use the re-size handles and move the clip art so it looks "nice" on the page
  • The page is balanced
  • It is not on top of any of the text
  • It is big enough to see, but not so big it overpowers the text

To add a picture

• Go to the INSERT Tab
• Click on the PICTURE button
• Browse to find the picture where you saved it
• Double click on the picture
• Use the re-size handles and move the clip art so it looks "nice" on the page
  • The page is balanced
  • It is not on top of any of the text
  • It is big enough to see, but not so big it overpowers the text

Spell Checking

Once you have created all the slides for your presentation you need to go back and look at each one. This will let you see inconsistencies. You should also read each slide out loud, you will find more grammatical errors that way. Finally you should run a spell checker on it.

• Go to the REVIEW tab
• Click on the ABC Spelling button and it will spell check the entire document.

Display Presentation in Grayscale

If you are going to print the presentation out on a black & white printer, you should view the presentation in grayscale first to see how it will print. There are times you will place graphics behind your text and it will look fine in color, but in grayscale you will not be able to read it.

• Go to the VIEW tab
• Go to the COLOR/GRAYSCALE tab
• Click on the Grayscale button

You might want to check out the Pure Black and White view as well.

Printing A Presentation

Like with any document it is a good idea to print preview your document prior to printing.

Print Preview

• Click on the Office Button
• Hover over Print
• Click on Print Preview
• Go to the Page Setup group
• Set the Print What to the style you want to print.

SLIDES

Prints one slide per page and the one slide take up the entire page. This is a good view for final editing if you have placed items on the slide with detail.

HANDOUTS

• 1 per page - Shows one slide per page with a blank section at the bottom
• 2 per page - Shows two slides per page and takes up most of the page
• 3 per page - Shows three slides vertically on the left with a set of lines on the right for a person to take notes in.
• 4 per page - Shows four slides per page and takes up most of the page
• 6 per page - Shows six slides per page and takes up most of the page
• 9 per page - Shows nine small slides per page and take sup most of the page

NOTES PAGES

Prints one slide per page at the top, and then prints all the notes you have entered for the slide at the bottom of the page. This is a great view to print for when you are giving your presentation.

OUTLINE

Prints the same outline that shows up on the Outline Tab.

Project

Create a Presentation on your favorite (Car, Shoe, Computer, ...) Use the techniques learned from the in class practice PowerPoint to create the slide show.

1. Pick a school appropriate topic as exampled above.
2. Slide 1 - Title slide
3. Include at least three text slides with bulleted lists (at least 4 line items per page). Here are some ideas on what you can put on each page.
   • Slide 2 – What is so special about this – Special features
   • Slide 3 – Why you picked it, Why is it important to you
   • Slide 4 - Where you can get it – How much it costs
4. Include a multi-level bulleted list
5. Include clip art or a picture on each text slide that fits the topic of the slide.
6. Add notes to each slide page
7. Pick a Theme
8. Format with colors, bolding, and italics as needed
9. Spell Check
10. Print as handouts - 3 to a page

You will be giving your presentation next class period. You must speak!!!

Rubric

Presentation on YOUR FAVORITE ...

Standards	1 point	2 points	3 points	4 points	5 points
Creates a title Slide - slide 1	Missing 4 item	Missing 3 item	Missing 2 item	Missing 1 item	Slide is formatted as a title slide, has a title and subtitle. Fonts are easy to read with size, style, and color choice.
Creates a Text Slide - slide 2	Missing 4 item	Missing 3 item	Missing 2 item	Missing 1 item	Slide is formatted as a text slide, has a title and text placeholders. Fonts are easy to read with size, style, and color choice. Slide follows the rules of 7.
Creates a Text Slide - slide 3	4 mistakes	3 mistakes	2 mistakes	1 mistake	Slide is formatted as a text slide, has a title and text placeholders. Fonts are easy to read with size, style, and color choice. Slide follows the rules of 7.
Creates a Text Slide - slide 4	4 mistakes	3 mistakes	2 mistakes	1 mistake	Slide is formatted as a text slide, has a title and text placeholders. Fonts are easy to read with size, style, and color choice. Slide follows the rules of 7.
Presentation covers the 5W's and 1H	Missing 4 elements	Missing 3 elements	Missing 2 elements	Missing 1 element	The presentation covers Who, What, Where, When, Why, and How
Applies a Theme	No theme applied	Poor Choice	Average Choice	Good Choice	Theme makes sense for topic. Excellent choice
Uses graphics to enhance presentation.	no graphics	Missing graphics on most slides and/or poor choices for your topic.	Missing graphics or several are poor choices for your topic.	Missing graphic, or poor graphic choice.	Includes graphics on all text slides. Graphics enhance the topic of the slide. Graphic is placed to not distract from text. Graphic is of a size that can been easily seen.
Adds Notes to the text slides	no notes, or poor notes on all slides	Notes missing on two slide or notes are not complete on three slide	Notes missing on one slides or notes are not complete on two slide	Notes are not complete on one slide	Notes are added to all text slides and the notes enhance the presentation. It is clear what the creator wanted to discuss on this slide.
Printed handouts for slides	Outline was printed instead of handouts	slides were printed instead of handouts	Notes pages were printed instead of handouts	Handouts were printed for the slides	Handouts were printed for the slides, there are three slides per page.

Creating and Editing a Presentation

The PowerPoint Window

1. Title Bar - Displays the name of the application followed by the title of the presentation
2. Formatting Toolbar - Provides quick access to commands you need for formatting
3. Outline and Slides Tab - The slides tab gives you a thumbnail view of all the slides in the presentation and allows to rearrange their order; the outline tab adds textual content to the slides in an outline format
4. Slide Pane - Area where you build the slides for your presentation
5. View Buttons - Change the way you view the presentation; the Normal view (left button) is the default, the Slide Sorter view (center button) shows you only the thumbnails and is used to sort and rearrange the presentation, and the Run view (right button) runs the presentation from the current slide
6. Drawing Toolbar - Provides all the tools you need to draw and format objects
7. Notes Pane - Adds notes for yourself for each slide in your presentation
8. Task Pane (Windows version) - Varies based on what you are currently working on; when you first start PowerPoint, you see the New Presentation task pane; other possible tasks include Slide Layout, Slide Design, and Effects
9. Menu Bar - Includes all of the PowerPoint menu choices
10. Placeholders - Designate the space that will be filled with titles, text, or other objects such as graphics or charts
11. Application Close Button (Windows Version) - Exits PowerPoint
12. Presentation Close Button (Windows version) - Closes the current presentation

Create a New Presentation

Using a Design Template

1. If necessary, choose File > New to display the New Presentation pane
2. On the New Presentation pane, click on the From Design Template link
3. The Slide Design pane will display on the right side of the screen with a variety of different templates to choose from
4. Select the design of your choice from the Slide Design pane
5. Click OK to begin working with the first slide in the Normal View

Using a Blank Presentation

1. If necessary, choose File > New to display the New Presentation pane
2. On the New Presentation pane, click on the Blank Presentation link

This will open a new presentation with no template. You will provide the content, background, color scheme, text format, etc. This method gives you the most freedom, but also requires the most amount of time to complete.

Adding a new slide

Once you have opened a new presentation, the next step is to add and format the content. PowerPoint provides a selection of pre-defined slide layouts based on different types of content that you can use to quickly add content to the slides. For each of the 27 Slide Layouts provided, PowerPoint combines the four types of placeholders in different combinations; each placeholder will be replaced with the following type of content:

Slide Layout Placeholders

Placeholder:	Replaced with:
Title	A title
Subtitle	A subtitle
Text	A bulleted list
Content	Clipart, a diagram, a chart, a table, a media clip, or a picture

Using a Slide Layout ensures that the text and other elements you enter into the placeholders will have consistent spacing and be optimally arranged.

How to Add Slides to a Presentation

1. Click the New Slide button on the Formatting toolbar.
2. From the list of Slide Layouts, select the layout you want to apply to the new slide.
3. You may now begin adding content using the placeholders in the layout.

How to Change the Layout for any Slide

PowerPoint will try to guess what layout you want to use for new slides that are added to the presentation. If you want a different layout for the slide you can quickly change the layout for any slide.

1. Display the slide that you want to change in the Slide Pane (work area in the center of the window).
2. Choose Format > Slide Layout to display the Slide Layout task pane.
3. Click on the layout you want to apply to the slide.
4. PowerPoint will attempt to fit existing content into the new layout, but you will probably have to make additional changes.

How to Add Slides in the Outline Tab

You can also create new slides while working in the Outline tab. By default the Outlining toolbar should display, but if it does not, select View > Toolbars > Outlining.

1. Display the Outline tab by clicking on Outline in the pane on the left.
2. Place the cursor at the end of the text in the slide you wish the new slide to follow.
3. Click the New Slide button to insert a new slide.

Entering Text on a Slide

Enter Text on a Slide Using Placeholders

1. Click on the Title, Subtitle, or Text placeholder.
2. Type the text you want.
3. If necessary, press [Return] or [Enter] to move to a new line.
4. Click anywhere on the slide outside of the placeholder to deselect it.

Enter Text Using the Outline Tab

Working in the Outline Tab allows you to type and edit text for the presentation in a more word processing-like environment than the Slide pane. The Outline tab displays in the pane on the left side of the screen when you are working in the Normal view.

Information in the Outline tab is arranged by levels. The Title of each slide appears as the first, left-most level next to a numbered icon of the slide. Bulleted text is indented one to four levels to the right of the title. The Outline tab has an Outlining toolbar that displays to the left of the pane.

Tip: If the Outline and Slides panes are not displaying in the Normal view, select View > Normal (restore panes) and it will display on the left side of the screen.

1. After adding a new slide, Type the slide title and press [Return] or [Enter].
2. To change the slide text to a first level bullet, press [Tab] or click the Demote button on the Outlining toolbar.
3. Type the text for the first bullet and press [Return] or [Enter] to move to the second bullet.
4. To create a sub-bullet, press [Tab] and type the text.
5. Continue to enter text for bullets and sub-bullets until the slide is complete.

• • Use [Return] or [Enter] to create a new instance of the same level you are on. For example, if you are typing a level one bullet, pressing [Return] or [Enter] will create another level one bullet.
  • To demote a line of text, use [Tab] or the Demote button. This will make a level one bullet into a level two sub-bullet.
  • To promote a line of text, press [Shift] and [Tab] together or click on the Promote button. This will turn a level one bullet into the Title of a new slide.

Adding Clip Art to a Slide

Effective visuals emphasize the key content points in a presentation. PowerPoint provides a selection of professionally designed pictures, or clip art, that you can use in your presentations. These clip art images include many different themes such as animals, people, buildings, food, holidays, business, and more.

How to Insert a Clip Art Image

1. Move to the slide on which you want to place clip art.
2. Apply a Slide Layout that includes a content or clip art placeholder.
3. Open the Select a Picture dialog box by:

• • Clicking on the Clip Art button on the content placeholder OR
  • Double-clicking on the clip art placeholder

1. In the Search box, type a word or phrase that describes the clip you want.
2. Click Search. PowerPoint displays the search results in the Select Picture List.
3. Click on the clip art image you want and click OK.

How to Resize a Clip Art Image

Once you have added a clip art object to your slide, you can resize it to make it fit better into your presentation.

1. Click on the Clip Art object to select it.
2. Put the arrow on one of the resize handles at the corner of the picture until the cursor changes to a double-headed arrow.
3. Depress the mouse button and drag the handle toward or away from the center to make the image larger or smaller. The corner handles resize the image proportionally and the handles on the sides of the image increase or decrease the height or width of the image. When you release the mouse button, the object appears in its new size.

Editing Slide Text

You know how to enter text into your presentation, but what happens if you decide you want to change the text? PowerPoint allows you to navigate to a specific slide and change the text.

Navigate in a Presentation

To Move to:	Do this:
The last slide in the presentation	Drag the scroll box to the bottom of the scroll bar or press [Ctrl] and [End]
The first slide in the presentation	Drag the scroll box to the top of the scroll bar or press [Ctrl] and [Home]
The next slide in the presentation	Click in the scroll bar below the scroll box or press [Page Down]
The previous slide in the presentation	Click in the scroll bar above the scroll box or press [Page Up]
To a specific slide	Drag the scroll box up or down until the scroll indicator displays the slide you want

Selecting Text

Knowing how to select text is a critical skill in all Microsoft Office applications. Selecting text is a necessary step for many procedures such as deleting blocks of text or formatting.

Selection Method	Technique
Drag	To create a highlighted selection, point at one end of the text to be selected. Press and hold the mouse button while dragging the pointer to the other end of the text, then release the mouse button.
Select a word	Double-click anywhere on the word you want to select
Select a bullet item	Press [Ctrl] and click anywhere inside the bulleted text. You may also triple-click anywhere on the word you want to select.
Deselect	Make another selection or click the mouse button in the text area.

How to Edit Text in a Slide Pane

You can edit text or move bulleted text in the Slide pane or the Outline tab. To edit text in the Slide pane:

1. Select the bulleted text you want to change.
2. If necessary, edit the text by:

• • Pressing the [Delete] key to delete the text; or
  • Typing new text to replace the selected text.

1. If necessary, move the bulleted item by:

• • Selecting the entire bulleted item; and
  • Dragging the item up or down to move it to its new location.

Move Bulleted Items in the Outline Tab

You can change the order of bulleted items and slides in the Outline tab:

1. Select the slide or bulleted item you want to move.
2. Click the Move Up or Move Down button on the Outlining toolbar until the slide or bulleted item appears where you want it.

Formatting Text Slides

Apply Character Formats

You can use character formatting to add interest to presentations, but do so sparingly. Keep in mind that adding too much character formatting can detract from your message or make it confusing.

Format Text

You can change the appearance of text by changing its font, size, style, and color. You can format text in the Outline tab or the Slide pane. To format text:

1. Select the text you want to format.
2. To change the font, select a new font from the Font drop-down list on the Formatting toolbar.
3. To change the size, select a new size from the Size drop-down list on the Formatting toolbar.
4. To change the style, click the Bold, Italic, or Underline button on the Formatting toolbar.
5. To change the color, select a new color from the Font Color button's drop-down palette. (The Font Color button is on the Drawing toolbar).

Repeat Formatting

When you format text in a slide, you can press the [F4] key to repeat that format for newly selected text. If you use the toolbar to apply more than one format, PowerPoint will repeat only the last format you applied.

Align Text

Alignment determines the position of the text within its text object box on a slide. Text can be left-aligned, right-aligned, centered, or justified, as follows:

1. Select the text you want to align.
2. Choose Format > Alignment to display the Alignment sub-menu.
3. From the sub-menu, select the alignment option you want:

• • Align Left - aligns text at the left edge of the text object box.
  • Center - aligns text between the left and right edges of the text object box.
  • Align Right - aligns text at the right edge of the text object box.
  • Justify - begins text at the left edge and ends the text at the right edge of the text object box.

You can also use the Alignment buttons on the Formatting toolbar to left-align, center, and right-align text.

Line Spacing

Line spacing is the vertical distance between two lines of text. By default, line spacing is set to single-line spacing, which means that the amount of space between lines of text depends on the size of the font used. For example, if the font size is 12, then single-line spacing will be 12 pts between lines of text. If you applied double-line spacing, then the lines would be separated by 24 pts of space.

Setting Line Spacing

1. Place the cursor in a paragraph, or select the paragraphs you want to affect.
2. Choose Format > Line Spacing.
3. In the Line Spacing dialog box, click on the arrows to increase or decrease line spacing options, or enter an exact setting.
4. Click OK.

Line Spacing Options

In the Line Spacing dialog box, there are three options for changing the amount of space between lines and bullet items on a slide.

• Line Spacing - the amount of space between selected lines of text. By default, line spacing is set to 1.
• Before Paragraph - increase or decrease the spacing before the first line of each selected paragraph.
• After Paragraph - the amount of space after the last line of each selected paragraph

Indents

In PowerPoint, indents are used to align bullets and text at a set distance from the margins. When the horizontal ruler in PowerPoint is displayed, you will find one set of the following indent markers on the left edge of the ruler for each level of bullets on the slide:

Indent Marker Types:

Marker Name	Marker Function
Bullet Indent	Controls the left boundary for the bullet.
Text Indent	Controls the left boundary of text for a bulleted item.
Left Indent	Controls the left boundary for both bullets and text.

How to Change Indents

1. Select the text object that you want to affect.
2. Choose View > Ruler to display the ruler.
3. To adjust the first-line indent, click and drag the upper marker for that indent to a desired position on the ruler.
4. To adjust the subsequent lines in a paragraph, click and drag the upper marker for that indent to a desired position on the ruler.
5. To maintain the relationship between the first line and the rest of the paragraph (to move both markers at once), click and drag the bottom half of the lower marker to a desired position on the ruler.

Using Graphics and Animations

Lesson

Picture as Background

1. Have the picture you want to use as a background saved in a folder where you know where it is.
2. Go to the Design Tab
3. Go to the Background Group
4. Click on Background Styles drop down menu
5. Click on Format Background
6. Click the radio button for Picture or texture fill
7. Click on the FILE button and browse to the picture
   • Click on CLOSE to apply the picture to just the current slide
   • Click on APPLY TO ALL to apply the picture to all slides in the presentation

Creating graphics with transparent backgrounds

1. Click on the INSERT Tab
2. Click on either Picture or Clip Art
3. Find the picture you want and bring it into the slide
4. Click on the PICTURE TOOLS button at the top of the window
5. Click the RECOLOR button in the ADJUST group
6. Click on the Set Transparent color button at the bottom of the dialog box
7. Click on the one color you want to be transparent

NOTE: if you want more than one color transparent you will need to do this in a photo editor and save the picture as a PNG, GIF, or TIFF

Transitions

Transitions are the motion of how to get to the current slide.

1. Go to the Animation tab
2. Go to the TRANSITION TO THIS SLIDE group
3. Choose a transition for how this slide will enter
   • if you want the presentation to run automatically then on the far right change the Advance Slide to Automatically and set the amount of time.
   • If you want to have to click to get the slides to advance click the ON MOUSE CLICK check box
4. You can also set the speed of the transition
5. You can also add sounds for the transition

Animations

One of the more fun things you can do in PowerPoint is to make things move. One thing to keep in mind is for formal presentations, don't overdo the animations. Let your audience focus on your content and not be distracted with all the motion. If the motion adds to the meaning or content, then it belongs.

Under the Animation Tab you have several choices for how you will add animation to your show.

• Text can be animated with these custom entrances to add flair:
  • Whip
  • Swish
  • Flip
  • Unfold
  • Grow & Turn
  • Color Typewriter
  • many others
• Pictures can be animated simply
  • Fade
  • Wipe
  • Fly In
• Slide Transitions
  • Fades
  • Cuts
  • Dissolves
  • Wipes
  • Uncovers
  • Wheels
  • Many variations of each

Custom Animations

Pictures can be animated more dramatically with Custom Animations. To get there go to the:

Animations Tab - Animations Group - Custom Animation Button

There are four main groups of animations:

• Entrance
  • Appear
  • some other affect to enter onto the screen
• Emphasis
  • spin either direction and any set number of degrees
  • Many other motions
• Exit
  • Disappear
  • Many other ways to remove off the screen
• Motion Paths
  • Draw Custom Path - Scribble
  • Many other ways to move on the screen

The best thing to do is take a graphic and try some of the animations out.

Once you have several affects added to your picture or text you might want some of them to occur at the same time. In the drop down under the Custom Animations you can set them to:

• on Click
• with Previous
• after Previous

With these settings and the speed settings you can get many dramatic affects.

Try to take a picture of a skier and have them fall down a hill by adding a spin and a motion path that occur with previous.

Sounds

Sounds are another way to add a special touch to a presentation. But here to you can go overboard. Only add sounds that really need to be there for the effect you want.

To get to sounds go to:

Insert Tab - Media Clips Group - Sounds icon

You can add sounds from the following sources:

• Sounds from file
• Sound from the clip organizer
• Play CD Audio Track
• Record sound

If you have a folder of Royalty Free sounds you can add any of them to your PowerPoint by browsing to it. If you use the clip organizer it is just like adding a clip art. If you play with the CD then you need to make sure the CD is in the CD player anytime you want to view the presentation. If you want to create something like a kids book where the book reads along with the kid, you can record your own sounds. You must have a microphone hooked up for this option to work.

Once you have the sounds in the presentation they will appear in the Custom Animation list and you can set when in the presentation you want them to play. You can set them to play automatically or after you click or after some other action. You can also set it to repeat the sound, and to hide the sound icon during the presentation.

Rehearsal Timing

A finishing touch to a presentation is to have practiced your presentation and have the slide change automatically for you when you are ready for them. This can be done easily with the rehearsal timings. To get to this go to the:

• Slide Show Tab - Set up Group - Rehearse Timings button
• Give your presentation as you would normally
• Press space bar as you need
• Re-play the slide show and it will keep the timings you just set.

Project

Create a mini movie on a school appropriate topic of your choosing.

Examples:

• snowmobile safety
• chemistry lab safety
• Newtons laws of motion
• World War II
• Frogs in their natural habitat
• A video game prototype

Requirements:

1. Custom animations on every page
2. Slide transitions between every slide
3. One slide needs to have a picture for a background
4. Individual creativity
5. Creating graphics with transparent backgrounds
6. A Sound must be added to at least one slide for emphasis

Helpful hits to make this extrodinary:

• Use a tool such as Photoshop or Gimp to edit your graphics
• Use layers with some layers semi-transparent to make it look like you are behind glass or under water
• Use layers so you can go behind different items
• Bring in a CD to add music in the background
• Work with the custom animations and have several of them "start with previous". This works really well with a motion path and an emphasis.

Rubric

PowerPoint Movie Rubric

Standards	1 point	2 points	3 points	4 points	5 points
Uses Custom animations on every Slide	Custom Animation is not done or is so incomplete that the message is lost or ineffective. Custom animation reflects very little planning of the visuals.	Custom Animation has omissions in scene planning. Animations reflects attempts at planning and organization for the visuals.	Custom animation is considered for each scene, with special effects. Presentation reflects some planning and organization for the visuals.	Custom animation is complete for each scene, with effective special effects. Presentation reflects effective planning and organization for the visuals.	Custom animation is complete for each scene, with outstanding special effects. Presentation reflects outstanding planning and organization for the visuals.
Uses a Picture for a background	The student does not appear to be able to apply most design principles with choosing a backgound for the slides.	Student applies a backgound that adds to the content and show unity, contrast, balance of movement, emphasis, and center of interest but the overall result is not pleasing.	Student applies a backgound that adds to the content and show unity, contrast, balance of movement, emphasis, and center of interest but the overall result is poor skill.	Student applies a backgound that adds to the content and show unity, contrast, balance of movement, emphasis, and center of interest with fair skill.	Student applies a backgound that adds to the content and show unity, contrast, balance of movement, emphasis, and center of interest with great skill.
Uses Slide Transitions on Every slide	No attempt has been made to apply transisions in a manner that is consistent with the technique or theme of the production.	Little attempt has been made to apply transisions in a manner that is consistent with the technique or theme of the production.	An attempt has been made to apply transitions in a manner that is consistent with the technique or theme of the production, but it is not effective.	Transitiosn are applied in a manner that is reasonably consistent with the technique or theme of the production.	Transitions are applied in a manner very consistent with the technique or theme of the production.
Uses sounds to enhance movie	Student needs to work on learning sound relationships and using that knowledge in his/her work.	Choice and application of sound shows knowledge of sound relationships. Sounds are, however, NOT appropriate for the idea being expressed.	Choice and application of sound shows knowledge of sound relationships. Sounds are, however, NOT the best choice for the idea being expressed.	Choice and application of sound shows knowledge of sound relationships. Sounds are appropriate for the idea being expressed.	Choice and application of sounds shows an advanced knowledge of sound relationships. sound choice enhances the idea being expressed.
Presentation shows Creativity	Student has not made much attempt to meet the requirements of the assignment.	Student has a little attempt to show his/her best effort in meeting the requirements of the assignment.	Student has copied some of the production from source material. There is little evidence of creativity, but the student has done the assignment.	Student has taken the technique being studied and has used source material as a starting place. The student's personality comes through in parts of the production.	Student has taken the technique being studied and applied it in a way that is totally his/her own. The student's personality/voice comes through.
Concept	They spent little effort on brainstorming and started creating with no goal in mind.	They have spent little effort on brainstorming and refining a concept. They are unclear on the goals and how to reach the goal.	They have brainstormed their concept, but no clear focus has emerged for the final product.	They have a fairly clear picture of what they are trying to achieve. They can describe what they are trying to do overall but has trouble describing the final product.	Has a clear picture of what they are trying to achieve. Can describe what the final product will look like and include.

Creating a Custom Design Template

While PowerPoint offers a nice variety of design templates, it is also useful at times to create your own. A design template, once created, can be used over and over again. For instance, if your department wanted all presentations to include your company's logo in one corner, with its colors as the background, you could create a design template with these features and reuse it again and again.

Master Slides

A master slide is one that is a part of every presentation that controls certain text characteristics such as font type, size, and color, as well as background color and style. Masters can affect all the slides in a presentation. There are masters that control the title slide, notes pages, and handout pages. When you apply a template to a presentation, you apply a new set of masters that control the presentation's look and format. There are four types of masters used in PowerPoint, as described below.

Type of Master	Description
Slide Master	The Slide Master is an element of the design template that stores information about the template, such as font styles, placeholder sizes and locations, background design, and color schemes.
Title Master	The Title Master is used to make changes to slides in your presentation that use a Title Slide layout. This enables you to give a title slide a different look from the rest of your presentation.
Notes Master	The Notes Master is used to set the formatting for your notes pages. You can set headers, footers, and the Notes Body area.
Handout Master	The Handout Master is used to set the formatting of your handouts pages. You can set headers, footers, and the size and positioning of the number of handouts per page.

Slide Background

A slide background is a design element that appears behind the contents of the slide. The slide background is made up gradient, texture, patterns, or a picture. To change the slide background:

1. Select the slide you want to change:
   1. Select a slide in Normal view.
   2. Select the Slide Master.
2. Choose Format/Background.
3. In the Background Fill section, click on the drop-down arrow and choose Fill Effects.
4. On the Fill Effects dialog box, select the tab that contains all the options you want to set and click OK.
5. Click Apply to All.

The following table describes the options you can set in the Fill Effects dialog box.

Tab	Description
Gradient	Enables you to set the color, transparency, shading style, and variants.
Texture	Enables you to select a texture for the background.
Pattern	Enables you to set a pattern, as well as the foreground and background color for the pattern.
Picture	Enables you to select a picture as a fill for the background.

Inserting Graphics

One element you can add to every slide is a graphic of some sort. To add a graphic to one or all slides:

1. Display an individual slide or the Slide Master for all slides.
2. Choose Insert > Picture > From File.
3. Navigate to the folder that contains the picture that you wish to insert.
4. Select the picture file.
5. Click Insert.

PowerPoint allows you to insert most popular graphic formats into your presentation.

Adding Footers

Footers serve many uses in PowerPoint presentations. You can use them to provide information like slide numbers, footer text, and date. All of the information goes at the bottom of each slide in your design template.

Components of a Footer

A footer in PowerPoint is text that you create once, but it appears on the bottom of each slide. It can consist of text, slide numbers, and a date. To add a footer:

1. Display the Slide Master.
2. Choose View > Header and Footer.
3. In the Date and Time section, select the options that you want.
4. Check the Slide Number box to add a number to each slide.
5. Under the checked Footer check box, click in the text box and enter the footer text.
6. Click Apply To All.

Formatting a Footer

You can change the way a footer looks at any time. On the Slide Master, select the placeholder that contains the information you want to change and format the text as you would any normal text. You can also drag the placeholder around if you wish to change its location.

Modify the Slide Master Font

PowerPoint allows you to change the way that the text in each individual part of your presentation looks. If you have a very long presentation, for example, but decide that you no longer like the font for each title, PowerPoint makes it easy to change the font for each title at once. To change the appearance of the Slide Master text:

1. View the Slide Master.
2. Select the text you want to change.
3. Change the font to a different font.
4. Change the font size.
5. Apply a text effect, such as bold.
6. Return to Normal view.

Some Tips for Effective Slide Shows

Here are a few tips that will help your PowerPoint slide shows look professional and promote effective visual clues to help you get your points across:

1. Limit animation.
2. Stay with one slide transition.
3. Use sans-serif fonts.
4. No tiny font sizes; remember the folks in the back row.
5. Keep your slides simple and uncluttered
6. If you are presenting in a light room, use a light slide background.
7. If you are presenting in a dark room, use a dark slide background.
8. Make sure to use high-contrast text and slide backgrounds.

Preparing to Deliver a Presentation

After adding all of the content to a presentation, it is time to put the finishing touches on it. There are multiple ways to check for spelling errors, inconsistencies, or other problems. There are also several ways to enhance the presentation.

Spell Check a Presentation

AutoCorrect

Autocorrect corrects many common spelling errors automatically as you type. It is similar to the version used in Microsoft Word. Autocorrect fixes spelling errors as well as instances such as double capital letters at the beginning of a word, capitalizing the days of the week and the first words of a sentence. Common typing mistakes such as letter transposition are also automatically corrected as you type.

There may be times when you do not want Autocorrect turned on. To access options for Autocorrect, go under the Tools menu and choose Autocorrect Options. Each function in AutoCorrect can be selected or deselected by clicking in the check box associated with each function.

Spellchecking a PowerPoint Presentation

PowerPoint provides you with a powerful spellchecker that works in the same fashion as the one in Microsoft Word. As with that spellchecker, it's always a good idea to proofread your presentation yourself; however, the spell checker can sometimes be a lifesaver.

To check an entire presentation, don't select any text. If you run the spellchecker with specific text selected, it will only check that text. You don't have to click the insertion point at the beginning of a presentation to begin spellchecking, as the spellchecker will wrap around until it has checked the entire presentation.

There are four ways to access the spellchecker:

• In the Tools menu, choose Spelling.
• Click the Spelling button on the Toolbar.
• Press [F7].
• Right-click on a flagged spelling error and choose Spelling.

Once the spellchecker is running, you can use the various options to help you find and correct spelling errors in your presentation. PowerPoint's spellchecker compares words with its internal dictionary. If PowerPoint does not recognize a word, there are several options you can have it perform, as described below:

• If the correction you want is already highlighted in the Suggestions list box, click Change.
• If the correction you want is in the Suggestions list box but not highlighted, select that word and click Change.
• If the correction you want is not suggested, you can type the correction in the top text box and click Change.
• You can click Change All to automatically correct any further occurrences of the particular spelling error.
• If the spellchecker comes across a word that has been repeated, you can click Delete to delete on of the instances of a word.

PowerPoint can check your spelling as you type. If it thinks you've misspelled a word, PowerPoint lets you know by drawing a wavy red line under the word. To correct one of these errors, right-click on the erroneous word and either choose one of the suggested changes or open the Spell Check dialog box and make the change there as described above.

View the Slide Show

While putting together a PowerPoint slide show, it is often useful to actually run the show to see what it will really look like when it is presented to an audience. This can provide a "reality check" and give a better idea of how the show is actually going to look.

Slide Show View

Use the Slide Show view to see the slide show on your computer screen one slide at a time, using the full screen, as you will when actually presenting the show. You can move the subsequent slides by either clicking your mouse button, clicking the [Page Up] and [Page Down] buttons, or by using the left and right arrow keys on your keyboard.

To view the Slide Show:

1. When you use the Slide Show view button, PowerPoint starts the show at the currently selected slide. So, go to the first slide in your presentation.
2. Click the Slide Show view button.
3. To move to the next slide, click the mouse button or push the right arrow key. At the end of the show, PowerPoint will display, "End of slide show, click to exit".
4. You can exit a slide show at any point by pressing the [Esc] key.
5. When you exit a slide show, PowerPoint returns to Normal view.

Arrange Slides in a Presentation

The ability to reorder slides in a presentation after you've created them enables you to easily reorder slides after you've created them, as well as giving you the freedom to organize a new presentation out of an older one. This can be essential in using the same material for different audiences without having to recreate and entire slide show presentation. PowerPoint also lets you hide selected slides in a presentation, in case you want to reuse a slide show for an audience and omit certain parts of the presentation that don't apply to that particular presentation.

Slide Sorter View

Choose View/Slide Sorter, or click on the Slide Sorter view button. PowerPoint changes to the Slide Sorter view and opens the slide sorter toolbar.

In Slide Sorter view, you see the thumbnail representations of the slides comprising your slide show. This is a great way to see the whole presentation at once, in the order in which they will appear in the slide show. After you are finished creating and editing your presentation, you can come to Slide Sorter view to shuffle slides around, and copy, delete, or hide slides, until you've got it right. Slide Sorter view also allows you to set up special effects to the slides in the presentation.

As you look through your slides in Slide Sorter view, and find a slide that you need to further edit, just double-click on that slide and it will be displayed in Normal view.

Rearranging Slides in Slide Sorter View

You can often make a presentation better by playing with the order in which the slides appear. Sometimes, you may wish to entirely hide a slide that doesn't apply to your audience. All of this happens in Slide Sorter view. To rearrange slides in Slide Sorter view:

1. Click on the Slide Sorter view button (or choose View/Slide Sorter).
2. Click on the slide you wish to move, copy, delete, or hide to select it.
3. Now move, copy, delete, or hide the slide:

• • To move a slide, press and hold the mouse button, drag the slide to where you want it to go, and release the mouse button.
  • To copy a slide, press and hold the [Ctrl] key and drag the slide to where you want it to go.
  • To delete a slide, press [Delete] or [Backspace].
  • To hide a slide, right-click in the slide thumbnail and choose Hide Slide.

Adding Transitions to a Slide Show

One way to add a nice touch of professionalism to a slide show is to add transitions to the slides. Transitions affect the way one slide goes to the next slide. Transitions add interest to your slide show, but care should be taken to be sure they do not become a distraction from the show itself. It is often best to choose one transition you like and use it for the entire slide show.

Slide Selection in Sorter View

In Slide Sorter view, you can select one slide, two or more slides that are next to each other, or two or more slides that are not next to each other.

To Select	Do This
One single slide	Click on the slide.
Two or more contiguous slides	Click on the first slide, press and hold [Shift], and then click on the last slide.
Two or more non-contiguous slides	Click on the first slide, press and hold [Ctrl], and then click on all the other slides.

How to Set Transition Effects

For each slide in a presentation, you can set a different transition effect and determine how fast the transition will happen. To set transition effects:

1. Switch to Slide Sorter view.
2. Select the slides to which you want to assign a transition.
3. Choose Slide Show/Slide Transition or click the Transition button on the Slide Sorter toolbar to open the Slide Transition task pane.
4. From the Apply to Selected Slides list box, select the transition effect you want to apply. PowerPoint previews the effect on the selected slides and displays a Preview Animation button beneath each selected slide.
5. In the Modify Transition section, select a speed (Slow, Medium, or Fast) for the transition effect. Once again, PowerPoint previews the effect on the selected slides.
6. To preview the effect again, click the Animation Preview button beneath the slide on the left side.
7. Close the Slide Transition pane.

Animating Text

Normally, when you have a slide with multiple bullets, PowerPoint will display all the text bullets at once when you go to that slide during a slide show. One nice effect, however, is having the different bullets appear as you speak about them, one at a time. This can be accomplished by using PowerPoint's animate text feature.

Animation of text in PowerPoint refers to the manner in which individual text or other objects appear as they enter or exit a slide. Normally, all objects on a slide appear at the same time when you display the slide. However, you can have different objects appear and exit at different times under your control as the show proceeds. To add animation to text in a presentation:

1. In Normal view, select a line of bulleted text.
2. Choose Slide Show/Custom Animation to open the Custom Animation task pane.
3. Click the Add Effect button to display the drop-down list.
4. From the Entrance sub-menu, choose the animation effect of your choice. You can choose More Effects if the effect you want isn't listed. PowerPoint displays the animation effect in the list box on the Custom Animation task pane.
5. In the Modify section, set the direction and speed.
6. Close the Custom Animation task pane.

Printing Slides, Speaker Notes, and Handouts

PowerPoint enables you to easily print handouts, slides, and your own speaker notes that you can reference while giving a presentation.

Handouts

You can use PowerPoint to create handouts of the slides in your presentation. You can decide how many slides you wish to appear on a page. Usually, it is best to have no more than 4 in order that they remain readable. You can choose layouts from the Print dialog box right before you print. PowerPoint automatically formats everything for you.

Print Options

There are many options for printing your presentation. You can print slides, notes pages, handout pages, or outlines. You can print the current slide, or select a range of slides to print. You can also select other print options. To print various things from PowerPoint:

1. Choose File > Print to display the Print dialog box.
2. Under the Print What heading, select the type of printout.
3. Select any other options you want.
4. Click OK.

PowerPoint Keyboard Shortcuts

Introduction

PowerPoint keyboard shortcuts allow users to perform common tasks in Microsoft PowerPoint without using a mouse or touchpad. Keyboard shortcuts in PowerPoint serve multiple purposes, particularly benefiting users who prefer efficient workflows or have mobility challenges. According to Microsoft documentation, many users find that using shortcuts helps them work more efficiently. Keyboard shortcuts can be an essential alternative to using a mouse for users with mobility or vision disabilities, as they can be easier than using a touchscreen.

PowerPoint keyboard shortcuts can be categorized into four main classes:

1. General Windows and Office Shortcuts
   • These shortcuts are common across Windows and Office apps, such as CTRL+C and CTRL+V
2. PowerPoint-Specific Shortcuts
   • Shortcuts specific to PowerPoint functionality such as formatting shapes, moving objects, and managing layers
3. Combined Mouse+Keyboard Shortcuts
   • Mouse and keyboard combinations that makes basic functionality such as duplicating objects easy
4. Ribbon Navigation Shortcuts
   • A hierarchical shortcut system designed to let the user access elements from the Ribbon

Examples

Here are 20 of the most common PowerPoint shortcuts:

1. Ctrl + N - Create new presentation
2. Ctrl + M - Insert new slide
3. Ctrl + B - Bold text
4. Ctrl + I - Italic text
5. Ctrl + U - Underline text
6. Ctrl + X - Cut selected item
7. Ctrl + C - Copy selected item
8. Ctrl + V - Paste
9. Ctrl + Z - Undo
10. Ctrl + Y - Redo
11. F5 - Start slide show
12. Esc - End slide show
13. Ctrl + P - Print
14. Ctrl + S - Save
15. Page Up - Go to previous slide
16. Page Down - Go to next slide
17. Ctrl + K - Insert hyperlink
18. Alt + F5 - Switch to Presenter view
19. Ctrl + D - Duplicate selected object
20. Ctrl + G - Group selected objects

PowerPoint Shortcuts on Mac

On Mac systems, PowerPoint shortcuts follow a similar pattern to Windows, with the primary difference being the use of the Command (⌘) key instead of the Control key. The Option key (⌥) typically replaces the Alt key, and the Delete key functions differently on Mac keyboards. Microsoft maintains separate documentation for Mac-specific shortcuts, as some combinations may differ due to operating system conventions and keyboard layouts

PowerPoint Tips and Tricks

Inserting SVG

To insert SVG to PowerPoint:

1. Convert the SVG file to EMF.
   • Option: Open the SVG file in OpenOffice/LibreOffice Draw and save it as EMF via File > Export... menu.
   • Option: Open the SVG file in Inkscape and save it as EMF; this has been reported to lead to loss of quality or parts of the image.
2. Insert the resulting EMF file to PowerPoint by dragging the EMF file onto a slide.

Create a Greeting Card

Lesson

1. Open Publisher
2. Click on the Office Button
3. Click on NEW
4. Scroll through the templates and find greeting cards, or holiday cards
5. Pick one that you like, notice how they fold
6. Click on the content place holders to edit them
7. Click on a content place holder and press DELETE on your keyboard to remove unwanted items
8. Use the navigation at the bottom to move from one page to the next

Project

1. Pick a reason for giving a greeting card - Holiday, Thank You, Invitation
2. Pick a template that is close to what you want on your greeting card
3. Edit the content in the place holders
4. Place the person's name to whom you are giving the card on the front cover
5. Change the graphics
6. Create a logo and signature on the back cover (this is where your name will go)
7. Have a classmate read the card and proofread for spelling and grammar
8. Print only one copy of the card
9. Fold it
10. Turn it in, you will get them back next class to take home or send off

To add a special touch you can use Photoshop on your graphics.

Rubric

Greeting Card Rubric

Standards	1 point	2 points	3 points	4 points	5 points
Picks a reason for the card	It was difficult to figure out the purpose of the card.	The purpose on the front cover does not match the content within the card.	The purpose is somewhat clear but many apects of the card seem only slightly related.	Establishes a purpose at the beginning, but occasionally wanders from that focus.	Greeting card establishes a purpose at the beginning and maintains that focus throughout! Cohesive card.
Creativity and Originality	Uses other people's ideas, but does not give them credit.	Uses other people's ideas (giving them credit), but there is little evidence of original thinking.	Product shows some original thought. The creator's voice shows in places.	Product shows some original thought. Work shows new ideas and insights.	Product shows a large amount of original thought. Ideas are creative and inventive.
Spelling and Proofreading	Several spelling errors in the card.	No more than 3 spelling errors remain after one person other than the typist reads and corrects the card.	No more than 2 spelling errors remain after one person other than the typist reads and corrects the card.	No more than 1 spelling error remains after one person other than the typist reads and corrects the card.	No spelling errors remain after one person other than the typist reads and corrects the card.
Attractiveness and Organization	The card's formatting and organization of material are confusing to the reader.	The card's formatting and organization are distracting to the reader.	The card has well-organized information.	The card has attractive formatting and well-organized information.	The card has exceptionally attractive formatting and well-organized information.
Graphics	Graphics do not go with the accompanying text or appear to be randomly chosen.	Some Graphics do not go with the accompanying text or appear to be randomly chosen.	Graphics go well with the text, but there are too few and the brochure seems "text-heavy".	Graphics go well with the text, but there are so many that they distract from the text.	Graphics go well with the text and there is a good mix of text and graphics. Everything is school appropriate.
Requirements	More than two requirements were not completely met.	Two requirement were not completely met.	One requirement was not completely met.	All requirements are met.	All requirements are met and exceeded.