Microsoft Excel (A Spreadsheet tool)

 Introduction to 

Microsoft Excel 2007

Class learning objectives

1.      What is Excel?

·        Spreadsheet uses & samples

·        Touring the Excel window

·        Learning important definitions

·        Navigating around the workbook 

2.      The Basics

·        Entering & editing data in cells

·        Inserting cells, rows, & columns

·        Formatting cells

·        Renaming, adding, & reorganizing worksheets

 3.      Formulas

·        Using mathematical operators

·        Using four sum methods

·        AutoCalculate

 4.      Customizing Your Spreadsheet

·        Gridlines

 5.      Finishing Up

·        Using Excel’s help

·        More learning resources

What is Excel?

Excel is a spreadsheet program that can be used to organize, manipulate and analyze data. Excel is often used in the workplace to track statistics, create sales reports, financial modeling, scientific engineering, and making charts and graphics. However, it can also be useful at home to create budgets or even make a list of family members’ birthdays. Excel is a versatile and powerful program with a lot to offer.

 

The Excel Window

 

When you first open up Excel you will see a blank sheet that looks a lot like a grid. If you have ever used other Microsoft programs such as Microsoft Word, you will recognize several parts already such as the Title Bar. Other parts might be unfamiliar, so let’s look at the parts of an Excel window.

The Microsoft Office 2007 Ribbon

Microsoft Office 2007 uses a visual tool called the ribbon to display all of the commands that are used to edit a document. The ribbon uses two different visual elements: tabs and command groups.

1.  Each tab contains a set of groups that share a theme in common. The Home tab, for example, contains all of the commands that are used most often by most people.
2.  Within each tab are groups of command icons that share a common design element. The Font group, for instance, contains all of the commands that change the way that text looks while the Number group contains commands that change the way numbers are displayed within a cell.
3. Finally, within each group are visual representations of the commands themselves.

             Cells –The gray boxes that make up the Excel grid are called cells. Cells are arranged in rows                 and columns. They are used to store data. Data can be numbers, text and formulas, such as                     mathematical calculations.

Active Cell – The active cell is the cell you are currently working with. There is always an active cell on your worksheet. You can identify the active cell on your worksheet because it has a thicker border than the other cells and its name is listed in the cell name box. To change which cell is the active cell, simply click on it or move to it using the arrow keys on your keyboard.

 

Name Box –The cell name box, located below the clipboard group in the ribbon identifies the name of the active cell. Cells are named by giving the column letter and then the row number.  For example, B3 means the active cell is located in column B and is in the third row.

 

Formula Bar –The formula bar displays the contents of the active cell. This could be a formula, data or just text.

 

Workbook – An Excel file is called a workbook. It is helpful to think of a workbook as being like a notebook containing many sheets.

 

Worksheets –The individual pages in the workbook are called worksheets. They are often referred to as simply “sheets.” In addition to data, worksheets can also contain graphical objects, such as charts,

arrows and pictures. Each worksheet consists of a tabular grid of cells. There are more than 65,000 rows of cells, starting with number 1 along the left margin of the worksheet. There are 256 columns along the top margin of the worksheet. These columns are labeled alphabetically, using a single-digit and then double-digit alphabetization scheme.

 

Worksheet Tabs –There is a tab in the bottom left corner of the Excel window for each sheet in your workbook. Clicking on a sheet’s tab will take you to that sheet.

 

Navigating Around the Workbook

Excel has scroll bars to help you view all areas of a sheet quickly. You can click in a cell using your mouse to select a specific cell. Using the scroll bars and the mouse you can quickly select any cell in the worksheet. However, some users find it easier to move around in Excel using the keyboard instead of the mouse. The table below explains some keystrokes you can use to navigate around worksheets.

This Keystroke	Moves
TAB key	Move to the right to the next cell in the row.
ENTER key	Move to the cell in the column below.
Shift+TAB	Move to preceding cell.
Arrow keys (®¬­¯)	Move right, left, up, and down (one cell at a time).
CTRL+arrow keys	Move to the edge of the current data region (region the cell is currently in).
Home	Move to the beginning of row.
CTRL+Home	Move to Cell A1.

Microsoft Word (A Word Processing tool)

Introduction:

When you need to write any text based document like letters, memos, reports, newsletters, invoices, fax messages etc. on a computer, you look on a Word Processor. The biggest advantage in case of a word processor is ‗electronic cut and paste‘, which means that user can move the typed matter around and in other document. 

Features of MS Word:

MS Word is a word processor that supports many features, including the following –  

1. Automatic corrections for common mistakes as you type using special auto–correcting tools  
2. Wizard and templates that create and format documents for you  
3. Advanced Page layout and formatting capabilities  
4. Numbering, bulleting and shading tools  
5. Multiple document views so that you can see a rough draft of your document to look of a final printed page as you write  
6. Integrated grammar, spelling, and hyphenation tools  
7. Newsletter-style, multiple columns, header, footers and endnotes in your publications  
8. Drawing, border and shading tools that enable you to emphasize headers, draw lines and shapes around your text, and work with imported art files  
9. Web-page development for internet users so that they can turn their documents in to web-pages  
10. Tables can be added in between the text for showing data in tabular format.  

Application of MS Word:

MW Word can be used for write letters, memos, newsletters, invoices, fax messages etc. on a computer. User is editing a business letter to send to a client. 

MS Word 2007 is a powerful program that provides the ability to create and share professional documents by combining a complete set of writing tools with an easy-to-use interface. It also presents new tools to compose and publish blogs directly from Word.   

Blog Entries- These can be authored in Word itself and uploaded directly to a blog. This gives the freedom to a user to enjoy blogging and cut away mundane tasks of logging in to a new service, copying and uploading a blog. Supported blogging sites include Windows Live Spaces, Wordpress, SharePoint, Blogger, Community Server etc.  

Quick Styles- Word presents an array of all new professional style sheets (and ability to switch easily among them). This enhances user satisfaction and increase the level of changes available to each one of them.  

Word Count- This is listed by default in the status bar. This word count dynamically update as you type.  

Contextual Spell-checker- This will also catch incorrect usage of correctly spelled words.  

Translation Tool Tip Option- It is available for English (U. S.), French (France) and Spanish (International sort) languages. When selected, placing the mouse cursor over a word will display its translation in the particular language. Non-English versions have different sets of languages can be added by using a separate multilingual pack.  

Automated generation- This can be done for citations and bibliographies according to defined style rules, including APA, Chicago, and MLA. Changing style updates all references automatically. Connect to web services to access online reference databases.  

Mathematical Equation- This has been revamped completely and reengineered to look like the original text with linear input/edit language or GUI interface. It also supports the Unicode Plain Text Encoding of Mathematics.  

Page Layout- A preset gallery of cover pages with fields for Author, Title, Date, Abstract etc. is available in the page layout tab of the Word Ribbon. These follow the theme of the document intuitively.  

Document comparison engine- This has been updated to support moves, differences in tables, and also for easy-to-follow tri-pane view of the original document, new document, and differences.  

Full screen reading layout- This shows two pages at a time with maximal screen usage, plus a few critical tools for reviewing. This functionality is very helpful in reviewing a document.  

Inspector- This is a new addition in the Word scenario. It collates and strips Word documents of information such as author name and comments and other 'metadata'.  

Building Blocks- This lets you save frequently used content, so that they are easily accessible for further use. Building blocks can have data mapped controls in them to allow for form building or structured document authoring.   

Interface: The Screen:

The Ribbon in Word 2007:

The Ribbon in Word 2007 Once you get used to it, you'll find the Ribbon is far easier to use than the file menu interface used in previous versions of word. By default the Ribbon is divided into seven tabs, with an optional eighth tab (Developer) They are:

• Home: This contains the most-used Word features, such as changing fonts and font attributes, customizing paragraphs, using styles, and finding and replacing text.
• Insert: As you might guess, this one handles anything you might want to insert into a document, such as tables, pictures, charts, hyperlinks, bookmarks, headers and footers, WordArt... etc.
• Page Layout: This is where you'll change margins, page size and orientation, set up columns, align objects and add effects. There is some overlap between this tab and the Home tab. On the Page Layout tab you set paragraph spacing and indents, while on the Home tab you set paragraph alignment and can also set spacing between lines.
• References: This tab handles tables of contents, footnotes, bibliographies, indexes and similar material. It also lets you insert a "Table of Authorities," which like a security setting but is in fact is a list of references in a legal document.    
• Mailings: As the name says, this is where you'll go for anything to do with mailings, from something as simple as creating labels to the more intimidating task of mail merges.   
• Review: To check spelling and grammar use the thesaurus, track changes, review other people's changes or compare documents? This is the tab to use.   
• View: This tab allows you to change the view in any way, including displaying a ruler and gridlines, zooming in and out, splitting a window and so on. 

The Office Logo

The first thing most people will need to relearn is where to go to open a document, create a new one, save your document and print.  Clicking the Office logo at the top left of the screen will provide most of the items formerly found under the file menu including those listed above.   Beside the logo you also will find a disk icon to save your document as well as the undo and redo buttons.  More buttons can be added to this ―Quick Access Toolbar‖ through the word options mentioned in the next section. 

As you can see here the Office logo opens up listing your options for new, open etc. and also contains a list of your recent documents for quick opening.  Any of the items listed with an arrow beside them will replace the recent documents on the right with the options associated with the menu item. 

You should also see at the bottom right of this menu a button for exiting Word and changing Word‘s options. 

Save as The save as option will provide you with the most common file formats to save your document in.  The common ones are Word Document, Word Template, and Word 97-2003 document.  The last one is the option most people should be using currently, especially if they wish to share documents with others who do not have the new version of Office. 

Print Here you can choose from Print, Quick Print and Print Preview.  Print brings up the standard print dialog box, quick print will print one copy without any dialog box coming up and print preview will bring up the print preview screen. 

Prepare The prepare menu‘s most common options are Properties, Inspect Document, Mark as Final and Run Compatibility Checker.  The properties option allows you to setup metadata for the document like Author, Title, keywords, comments and others.  Inspect Document will scan the document for any hidden data like comments and annotations, and any hidden collaboration data.  This is very useful when making a previously private document public.  It will help you find any comments or changes made previously that should not be made public.  Mark as Final will mark the document as a final copy and make it read only so changes cannot be made.  The last common item, Run Compatibility Checker will scan the document for new features that were used and show you these.  This is recommended if you were creating a document in the new file format but now need to convert it back to the Office 2003 format to share with someone who does not have Office 2007. 

The Home Tab :

The home tab the basic formatting tools found in Word 2007.  You will find five sections, Clipboard, Font, Paragraph, Styles and Editing.  Clicking the down arrow beside any of the icons here will drop down more options for that tool.  Each section also contains an arrow in the bottom right corner which will open a window containing the options found in that section. 

Clipboard The Clipboard allows you to cut, copy, paste and copy formatting from one place to another.     

Font The font section of the ribbon provides a section to handle the basic text formatting.  Items such as bold, underline, strikethrough, highlight and font type can be changed here.  

Some items from this section and some items from the paragraph section are also available by highlighting text and moving your cursor slightly above the highlighted text.  This saves having to move your cursor all the way to the top of the screen for some common formatting items.  

Paragraph The paragraph section provides icons for bullets, lists, justify, line spacing, indents and borders.  

Styles 

The styles section allows you to quickly change the formatting of a section of text by choosing one of the predefined styles.  You can also create a new style based on the formatting of your selected text for use later in other sections of your document.    

Editing The Editing section of the toolbar allows you to find, replace and select items.  The select option gives you the ability to select all, select objects or select text with similar formatting.  This last option gives you the ability to quickly change everything in your document with one style to another style without having to manually find all of that text and change each area separately.  

Insert Tab :

The insert tab has seven sections for inserting most types of objects.  The sections are pages, tables, illustrations, links, header and footer, text and symbols.    

Pages The pages section is where you can go to insert an cover page, blank page or page break.  The cover page drop down offers a selection of predefined cover pages for your document that have sections for title, date and author.  You can also select text in your document and choose to save the selected text to the cover page gallery for use in future documents.  

Table The table section only has a drop down menu which offers a grid to create a new table, insert table, draw table, convert text to table for selected text, Excel spreadsheet, and some predefined ―Quick Tables‖ that have formatting already setup for you.  When working on a table you will have two additional tabs along the top of the ribbon, the design and layout tabs.  There are screenshots of both directly below. 

Table Design  

Table Layout   

Illustrations The Illustrations section allows you to insert pictures, clipart,  shapes, SmartArt and charts.  After inserting or selecting a picture you are provided with a new toolbar along the top shown here.   This toolbar gives you the ability to change the brightness, contrast, shape, position, text wrapping and other options for the picture.  Clicking off the picture or on one of the other tabs will take you back to the standard toolbars.  The Shapes option of the Illustrations section allows you to insert lines, arrows, boxes, basic flowchart shapes and a number of others.  The SmartArt option provides features like org charts, flow charts, illustrated lists and processes.  The Chart option is similar to Word 2003 but it offers more options for your charts.  

Links The links section provides options for inserting hyperlinks, bookmarks and cross-references.  Cross-references can link to figures, tables, equations, endnotes, footnotes, headers and numbered items.  

Header & Footer The header & footer section allows you to edit the header, footer and page numbering for your document.  Once you select this option it will change the toolbar along the top to include a larger number of options for the header and footer.   The header bar is shown here.  In order to stop editing the header and footer you need to click the ―Close Header and Footer‖ button on the right of the bar.  

Text Options in the text section include text box, drop cap, WordArt and a number of predefined text blocks like a signature line the date and time, and document properties like abstract, author, and title.  There are a lot of option in here to setup on your own or you can use Words predefined options.  

Symbols The last option in the insert toolbar is symbols.  Here you can insert a large number of special characters and symbols.  Choosing the ―more symbols option from the drop down will also allow you to setup shortcut keys for commonly used symbols.  One of the improved features of Word 2007 is the equation editor.  You can now insert more complicated mathematical and statistical equations.  Here there are some standard equations to choose from or you can create your own.  

Standards like (   )  ∑ (  )  ,the binomial theorem are available or,  by choosing ―Insert new equation‖ you will get the equation toolbar, which gives you a large number of options to choose from while creating your own equation.  ∫   The equations can be edited in the new Word 2007 file format however, once they are converted to a Word 2003 format they will become images which cannot be edited.  

Page Layout Tab :

The page layout tab has five sections, Themes, Page Setup, Page Background, Paragraph, and Arrange.    

Themes The themes section provides a quick way to format your document.  By choosing a theme you will have a set colour scheme, font combinations, and effects.  You can choose one of the provided themes, modify a provided them or create your own.  You can also go online in this section and browse Microsoft.com for additional themes.  Be aware that changing your theme after creating a document may require you to reformat some items as themes also include some layout options.  

Page Setup Page setup provides you with the tools to change margins, size, orientation, columns, breaks, line numbers and hyphenation in the document.    

Page Background The page background section allows you to change the background colour of the document, watermark and draft or confidential document or add borders to your document.  

Paragraph The paragraph section in the page layout tab allows changes to a paragraphs spacing and indentation.  

Arrange The arrange section is also found in the image toolbar when an image is selected.  Here you can change an images position, how text moves around the image, the alignment, grouping and rotation or the image.  

References Tab The references tab contains six sections, Table of Contents, Footnotes, Citations & Bibliography, Captions, Indexes and Table of Authorities.  

Table of Contents The table of contents section allows you to insert and modify the table of contents.  You can insert automatic or manual table of contents and change what styles will be included in the table of contents, if any.  You can also add text to the table and update the table of contents after adding or removing items from your document.  

Footnotes This section allows you to insert footnotes and endnotes and move through your existing footnotes quickly.  

Citations & Bibliography This section provides tools to insert citations, manage sources, citation style and bibliography.    

This section allows you to create an index, update the index and mark and item for inclusion in the index.   

Mailings Tab - 

The mailings tab contains five sections, create, start mail merge, write & insert fields, preview results and finish.  

Create The create section provides a window to create envelopes or labels.  This is mostly for creating single envelopes, labels or a sheet of the same label.  

Start Mail Merge- The main features of this section are start mail merge, select recipients and edit recipient list.  The method most people will likely use to create a mail merge in Word 2007 is the step by step mail merge wizard found in the start mail merge drop down list.  This option will bring up a wizard along the right side of the screen that will walk you through the process.  Recipients can come from Outlook contact or a large number of files including Access, Paradox, Excel, Word, CSV and even HTML.  

Write & Insert Fields- This section provides the tools necessary to make a document into one that will work with a mail merge.  You can Highlight merge fields, work with address block or greeting line, insert new merge fields, setup rules, match fields and update your labels.    

Preview Results- This section allows you to preview your merge before completing it.  Make sure all your recipients fit onto one page, the formatting remained the same after merging, look for specific recipients and even have Word auto check for common errors.   

Review Tab :

The review tab offers six sections which include proofing, comments, tracking, changes, compare and protect.  

Proofing- The proofing section provides the standard spelling and grammar check, a thesaurus, word count, research tools that include MSN search and Microsoft Encarta encyclopedia.  You will also find translation tools to help with single words or the whole document.  The translation of the entire document is done by an online service called Wordlingo.  

Comments- The comments section allows you to add comments to a document for easier collaboration.  You can cycle through the comments to find out what notes you left for yourself or others and you can delete a comment that was made when it is no longer relevant.  

Protect- The protect section gives you options to add a password and protect the document.  You can restrict changes to formatting and editing or the whole document.  If you use Windows Live you can also manage permissions for specific users who also have Windows Live. 

View Tab :

The view tab offers five sections which include document views, show/hide, zoom, window and macros.  

Document Views- The document views section switches you between print layout, full screen reading, web layout, outline and draft views.  Print layout is the default view.  Full screen view removes all but a couple of tools from the top of the screen and the rest of the screen is your document.  Web layout will take away the empty space on either side of the document if there is any and fill the window as if it were a web page.  Outline view changes the look of your document into an almost point form style which may help with reviewing main points.  Draft view takes away most of your formatting and images and just shows the text.  It also fills the window with your text similar to web layout.

  

Show/Hide- The show/hide section will toggle certain tools on or off the screen including rulers, gridlines, message bar, document map and thumbnails.  The rulers will show along the top and left side of the screen.  Gridlines will cover your entire document inside the margins.  They will be visible on screen but don‘t print.  The message bar can only be displayed when there is a message to be displayed.  One common reason for the message bar to display is when macros have been enabled or disabled.  The document map and thumbnails will show along the left hand side of the screen.  

Zoom- The zoom section provides tools to zoom into or out of the document.   You can choose your own zoom factor or use one of the predefined zoom factors of 100%, one page, two pages(side by side), or page width which causes the document to zoom in or out so it fills your window.  

Macros- The macros section provides the tools required to work with and create basic macros.  You can view existing macros or record your own.  Choose record macro from the drop down and then perform the functions you do often, like change the page layout, and style of the document.  Once you have done those tasks then stop recording.  You will be able to use that macro over again to shorten the steps you need to take every time you need to perform that set of tasks.      

SELF ASSESSMENT QUESTIONS 

1. Why Word is considered the most powerful word processor in the market?  

2. How Word‘s advanced AutoCorrect features help eliminate common editing tasks?  

3. How to see a preview of your printable document?  

4 How to type special characters that don‘t appear on your keyboard?  

5. How to use the spelling and grammar checker?  

6. How to insert the date, time and page numbers in your documents?  

7. How to prepare table in your documents?  

8. How to convert a single-column document into multiple columns?  

9. What are header, footer, endnotes and footnotes? 

10. Differentiate between WordArt, ClipArt & SmartArt. 

11. Differentiate between Footer and Footnotes with suitable diagram. 1

2. Differentiate between Bookmark & Watermark with diagram. 

13. How to create Hyperlink to open a home page (website) of any 5 star Hotels. 

14. Create a mail-merge to invite 5 of your friends to join your birthday party. 

15. How to protect your word document from the other user working on the same computer. 

Introduction to Microsoft Office (Word, Excel, Power Point )

Purpose

The purpose of this training module is to provide basic skills to the learners about office automation system using computer.   

Specific Outcomes

Learners will be able to work on office documents like: 

• Letters, reports, drafts, Envelops Forms, invitations, labels, newsletters (MS Word),  
• Budgets, calendars, Inventory, Schedule, invoice (MS Excel),  
• Award Certificate, Greeting Card, Plan, Presentation (MS Power Point) etc. using different features of MS Office. 

Introduction

MS Office helps you work more efficiently and effectively. Office offers integrated software tools that are powerful, yet easy to learn and use. Large and small offices can use Office – based applications for many of their day-to-day computer needs, families and home based businesses can also needs simple and analysis tools for their computers   

One of the most helpful features of Office is its capability to adjust different tools according to the users requirement, for examples, Word dose not display all menu commands in a particular Menu, it display the commands used most often by the user. So we can say that Office is fully integrated.   

Why MS Office

MS Office is a package suite of  

1. MS Word (A Word Processor),  
2. MS Excel (A Spreadsheet),  
3. MS PowerPoint (A Graphic Presentation Program),  
4. MS Access (A Relational Database),  
5. MS Outlook, 
6. MS Publisher and  
7. MS FrontPage.   

Research showed that most of the work done by most of the people could be categorized into 4 (MS Word, MS Excel, MS PowerPoint, MS Access) -7 (MS Outlook, MS Publisher and MS FrontPage) major categories. 

Features in MS Office    

1. User Interface: - The new result oriented User Interface (UI), officially known as MS Office Fluent, is featured in the core applications of MS Office: Word, Excel, PowerPoint, Access and Outlook. According to the Microsoft, the new UI centers on the principle of helping people focus on that they want to do, rather than the details of how to do it. You can check the preview effect of the command after moving on the required icon.   

2. The Office Button: - The Office Button, located on the top-left of the window, replaces the file menu and provides access to functionality common across all Office applications, including but not limited to opening, saving, printing and sharing a file. User can also choose color schemes for the interface.  

3. Ribbon: - The Ribbon, a panel that groups the command buttons and icons, organizes commands as a set of tabs, each grouping relevant commands. Each application has a different set of tabs which expose the functionality that application offers. For example, Excel has a tab for graphing capabilities but Word dose not feature the same. The Ribbon is designed to make the features of the application more accessible with fewer mouse clicks as compared to the menu based UI in earlier versions of Office.   

4. Contextual Tabs: -Some tabs appear only when certain objects are selected, called Contextual Tabs. Contextual tabs expose functionality specific only to the object with focus. For example: Selecting a picture opens the Pictures tab, which presents options for dealing with the picture.  Similarly, focusing on a table exposes table related options in a specific tab. These tabs remain hidden when the object is not selected.   

5. Mini Toolbar: - Mini Toolbar, which pops up near the selected text, provides easy access to frequent used formatting commands. When the mouse pointer is away from it, the toolbar become semitransparent. But when the mouse pointer moves over it, it become ready to use. It also appears above the right-click menu when a user right-clicks on selected text.   

6. Super Tooltip: -It can house formatted text as well as images, and is used to provide detailed description of what most buttons/icons do. For example: move your cursor on any icon or button the help tip of the same will appear bellow.   

7. Quick Access Toolbar: - The Quick Access Toolbar, place in the title bar, servers as a repository of most used functions, regardless of which application is being used, such as save, undo/redo, spelling & grammar and print.   

8. Zoom Slider: - The Zoom Slider, placed in the bottom-right corner, allow for dynamic and rapid magnification of a document.   

9. File Formats: - Microsoft Office 2007 onwards uses a new file format, called Office Open XML, as the default file format. It is based on XML (Extensible Markup Language) and uses the ZIP (compressed) file container. According to Microsoft, documents created in this format are up to 75% smaller than the same documents saved with the previous version of MS Office.  

Word documents without macro extensions are now saved using a .docx extension rather that the traditional .doc extension. Files containing macros are saved with the extension .docm. You can also save your MS Office 2007 documents in the old format so that they will still be usable in previous version of MS Office.   

10. SmartArt: - SmartArt found under the insert tab in the Ribbon in PowerPoint, Word, Access and Outlook, is a new group of easily editable and formatted diagrams. There are many SmartArt graphics layout templates in category such as list, process, cycle and hierarchy. When any item of a SmartArt is inserted, a text Pane appears next to it to guide the user through entering the text in the hierarchical levels. In addition, SmartArt graphics change their colours, fonts and effects to match the document‘s theme   

11. User Assistance System: - In MS Office, the Office Assistants have been completely replaced with the much improved help system. One feature of the new help system is Super Tooltips which explain detailed description of what most buttons/icons do.   

12. Themes and Quick Styles: - Microsoft Office, places different document themes and quick styles. The document Theme defines the colours, fonts and graphic effects for a document. The new office theme file format (.thmx) is shared between Word, Excel, PowerPoint and Outlook email messages. Quick Styles are galleries with a range of styles based on the current theme. There are quick style galleries for text, tables, charts, SmartArt, WordArt and more. The style range goes from simple/light to more graphical/darker.     

For any query please feel free to mail author.