MS Word & Excel - Notes

MS Word & Excel - Notes

Parts of Ms-Word application window

1. Office Button – Click the Office Button to find a drop down menu containing options, such as: open, save, and print. Also shows previously opened files, which you may choose to “pin” them to make them “permanent” choices.

2. Ribbon – The Ribbon is the strip of buttons and icons located above the work area in Word 2007. The Ribbon replaces the menus and toolbars found in earlier versions of Word. Each ribbon contains groups of command buttons with common purpose.

3. Tab Selector button – You can easily set tab stops by clicking on the desired position on the ruler. This button allows you to determine which type of tab will be set. Clicking on this button will allow you to change the tab style.

4. Rulers – Gives you an idea of where you are on the page.

5. Document – This is what you are typing/what will print out.

6. Status Bar – This row can be customized by right-clicking and selecting desired options. Desired options may include page number or number of total page, word count, insert/overtype mode, caps lock, and zoom slide.

7. Task Bar – Shows open programs.

8. View Shortcuts – These four buttons allow you to change the way you view your document on the screen. From left to right they are: print layout, full screen reading, web layout and draft. These can be added/removed by right clicking anywhere on the status bar and checking/ unchecking View shortcuts.

9. Zoom Slide – Allows you to increase/decrease the amount of the document you see on the screen.

10. View Ruler Button – Allows you to view/hide the rulers.

11. Screen Split Button – At the top of the vertical scroll bar is a new button. Just below the double arrow is a tiny button that looks like a minus sign that lets you split your screen in two when double-clicked.

12. Scroll Bars – Allows you to view entire workbook by moving it up, down (vertical scroll bar), left or right (horizontal scroll bar).

13. Right Indent – Slide this triangle to the left of the margin to limit the right side of a paragraph to that point. Move the triangle to the right of the margin to allow the right side of the paragraph to extend beyond the margin. The triangle at the margin will keep the right side of the paragraph with the margin.

14. Group – Command buttons with a common purpose are clustered together. Each ribbon contains several groups. Some groups, but
      not all, contain a quick launch bar (dialogue box launcher) in the bottom right hand corner.

15. Quick Launch Bar/Dialogue Box Launcher – It is the arrow in the bottom right hand corner of some groups. When clicked, it will bring up a dialog box where additional options/changes can be entered.

16. Title Bar – Shows name of program and open document. Also contains minimize, maximize and close buttons.

17. Quick Access Toolbar – This customisable toolbar allows you to add frequently used commands. Click on the down arrow at the end of the toolbar to add/remove command buttons – or - right-click on any command button and choose Add to Quick Access Toolbar.

18. Tab – The ribbon is broken down into 7 tabs. Each tab has a common purpose and consists of several groups. To select a tab, simply click on it and the appropriate groups will be displayed.

19. First Line Indent – This triangle controls where the first line of a paragraph begins. Moved to the left of the margin, will allow the first paragraph to be in the left margin. Can be moved to the right of the margin to indent your paragraph. THIS IS NOT SETTING TABS!

20. Hanging Indent – The opposite of a first line indent. It is often moved to the right of the first line indent, which allows the remaining lines of a paragraph to be indented according to placement of the triangle.

Meaning and features of word processing:

Using a to create, edit, and print documents. Of all, word processing is the most common. To perform word processing, you need a computer, a special called a word processor, and a. A word processor enables you to create a document, store it electronically on a disk, display it on a display screen, modify it by entering commands and characters from the keyboard, and print it on a printer.

The great advantage of word processing over using a typewriter is that you can make changes without retyping the entire document. If you make a typing mistake, you simply back up the cursor and correct your mistake. If you want to delete a paragraph, you simply remove it, without leaving a trace. It is equally easy to insert a word, sentence, or paragraph in the middle of a document. Word processors also make it easy to move sections of text from one place to another within a document, or between documents. When you have made all the changes you want, you can send the file to a printer to get a hardcopy.

Features of Ms-Word

Word processors vary considerably, but all word processors support the following basic features:

Full-featured word processors usually support the following features:

· File Management system: Many word processors contain file management capabilities that allow you to create, delete, move, and search for files.

· Font specifications: Allows you to change fonts within a document. For example, you can specify bold face, italics, and underlining. Most word processors also let you change the font size and even the typeface

· Footnotes and cross-references: Automates the numbering and placement of footnotes and enables you to easily cross-reference other sections of the document.

·  graphics: Allows you to embed illustrations and graphs into a document. Some word processors let you create the illustrations within the word processor; others let you insert an illustration produced by a different program.

·  Header , footer, and page numbering: Allows you to specify customized headers and footers that the word processor will put at the top and bottom of every page. The word processor automatically keeps track of page numbers so that the correct number appears on each page.

· Layout: Allows you to specify different margins within a single document and to specify various methods for indenting paragraphs.

· Macro: A macro is a character or word that represents a series of keystroke. The keystrokes can represent text or commands. The ability to define macros allows you to save yourself a lot of time by replacing common combinations of keystrokes.

· Merges: Allows you to merge text from one file into another file. This is particularly useful for generating many files that have the same format but different data. Generating mailing labels is the classic example of using merges.

· Spell-Checker: A utility that allows you to check the spelling of words. It will highlight any words that it does not recognize.

· Tables of contents and indexes: Allows you to automatically create a table of contents and index based on special codes that you insert in the document.

· Thesaurus: A built-in thesaurus that allows you to search for synonyms without leaving the word processor.

· Windows: Allows you to edit two or more documents at the same time. Each document appears in a separate window. This is particularly valuable when working on a large project that consists of several different files.

· WYSIWYG (what you see is what you get): With WYSIWYG, a document appears on the display screen exactly as it will look when printed.

Mail merge basics

Mail merge is for simplifying repetitive documents and tasks. Mail merge can be used for creating many documents at once that contain identical formatting, layout, text, graphics, etc., and where only certain portions of each document varies. Mail merge is also used for generating mailing labels, envelopes, address lists, personalised training handouts, etc. As well as hard copy mailshots, it can be used to generate multiple emails and electronic faxes. And it can even be used to create a “friendly” front-end to spreadsheet or database information.
Whenever you need to assemble similar data, mail merge can be used:
Mail merge primarily consists of two files, the Main Document and the Data Source.
Main Document:
          The Main Document contains the information that will remain the same in each record

The Data Source.
The Data Source contains all the variable information, in the form of fields. This is the information that will change in the Main Document when the merge is completed.
Along with the information that remains the same, the Main Document also contains merge fields, which are references to the fields in the Data Source.
When the Main Document and Data Source are merged, Microsoft Word replaces each merge field in the Main Document with the data from the respective field contained in the Data Source
The end result is a third document, a combination of the Main Document and Data Source

1.     On the Mailings tab, click Start Mail Merge, and then click Step by Step Mail Merge Wizard.

Select document type

1.     In the Mail Merge task pane, click Letters. This will allow you to send letters to a group of people and personalize the results of the letter that each person receives.

2.     Click Next: Starting document.

Select the starting document

1.     Click one of the following options:

o    Use the current document: Use the currently open document as your main document.

o    Start from a template: Select one of the ready-to-use mail merge templates.

o    Start from existing document: Open an existing document to use as your mail merge main document.

2.     In the Mail Merge task pane, click Next: Select recipients.

Select recipients

When you open or create a data source by using the Mail Merge Wizard, you are telling Word to use a specific set of variable information for your merge. Use one of the following methods to attach the main document to the data source.

Method 1: Use an existing data source

To use an existing data source, follow these steps:

1.     In the Mail Merge task pane, click Use an existing list.

2.     In the Use an existing list section, click Browse.

3.     In the Select Data Source dialog box, select the file that contains the variable information that you want to use, and then click Open.

Note If the data source is not listed in the list of files, select the appropriate drive and folder. If necessary, select the appropriate option in the All Data Sources list. Select the file, and then click Open.

Word displays the Mail Merge Recipients dialog box. You can sort and edit your data if you want to.

4.     Click OK to return to the main document.

5.     Save the main document.

When you save the main document at this point, you are also saving the data source and attaching the data source to the main document.

6.     Type the name that you want to give to your main document, and then click Save.

Create a database of names and addresses

To create a new database, follow these steps:

1.     In the Mail Merge task pane, click Next: Select Recipients.

2.     Click Type a new list.

3.     Click Create.

The New Address List dialog box appears. In this dialog box, enter the address information for each record. If there is no information for a particular field, leave the box blank.

To proceed to the next step, click Next: Write your letter.

Write your letter

In this step, you set up your main document.

1.     Type or add any text and graphics that you want to include in your letter.

2.     Add the field codes where you want the variable information to appear. In the Mail Merge task pane, you have four options:

o    Address block: Use this option to insert a formatted address.

o    Greeting line: Use this option to insert a formatted salutation.

o    Electronic postage: Use this option to insert electronic postage.

o    More items: Use this option to insert individual merge fields. When you click More Items, the Insert Merge Field dialog box appears.

o    In the Insert Merge Field dialog box, click the merge field that you want to use, and then click Insert.

Edit individual letters: Select this option to display the merged document on your screen.

When you click Edit individual letters, the Merge to New Document dialog box appears. In the Merge to New Document dialog box, you can choose which records to merge. When you click OK, the documents are merged to a new Word document.

Ms-Excel

Microsoft Office Excel 2007 is a powerful and widely used tool that helps people analyze information to make more informed decisions. Using Office Excel 2007 and Excel Services, you can share and manage your analysis and insight with coworkers, customers, and partners with greater confidence

A spreadsheet is a computer application with tools that increase the user's productivity in capturing, analysing, and sharing tabular data sets. It displays multiple cells usually in a two-dimensional matrix or grid consisting of rows and columns (in other words, a table, hence "tabular").

Spreadsheets developed as computerized simulations of paper accounting worksheets. They boost productivity because of their ability to re-calculate the entire sheet automatically after a change to a single cell is made (which was a manual process in the days of paper ledgers).

A modern spreadsheet file consists of multiple worksheets (usually called by the shorter name sheets) that make up one workbook, with each file being one workbook. A cell on one sheet is capable of referencing cells on other, different sheets, whether within the same workbook or even, in some cases, in different workbooks.

Active Cell

In a spreadsheet program such as Excel, the active cell is identified by a black border or outline surrounding the cell. The active cell is also known as the current cell or the cell that is in focus. When an action takes place in the spreadsheet - such as data entry, formatting, or deleting data - it is the active cell that is affected.

The active cell can be moved using the arrow keys on the keyboard or by clicking on another cell with the mouse pointer.

Column Letter

In Excel, the column header is the greyish - colored row containing the letters used to identify each column in the worksheet. The column header is located above row 1 in the worksheet.

Formula Bar

The formula bar in Excel is located above the work area of the spreadsheet. The formula bar displays the data or formula stored in the active cell. The formula bar can be used to enter or edit a formula, a function, or data in a cell.

The Name Box:

The Name Box is located next to the formula bar above the worksheet area. The Name Box displays the cell reference of the active cell. It will also show the name assigned to a cell or range of cells. The Name Box can also be used to assign names to cells or ranges of cells. Rows run horizontally in an Excel worksheet. They are identified by a number in the row header. In Excel 2003, there are 65,536 rows in each worksheet. In Excel 2007, there are more than one million rows. The intersection point between a row and a column is a cell. Cells are the basic storage unit for data in a spreadsheet.

Rows run horizontally in an Excel worksheet. They are identified by a number in the row header. In Excel 2003, there are 65,536 rows in each worksheet. In Excel 2007, there are more than one million rows.

A worksheet is a single page or sheet in a spreadsheet program such as Excel. By default, in Excel there are three worksheets per file. Switching between worksheets is done by clicking on the sheet tab at the bottom of the screen.

Rename an Excel Worksheet

1.     Right click on the tab of the worksheet you want to rename to open the drop down menu

2.     Click on Rename in the menu list to highlight the current worksheet name and switch to edit mode

3.     Type the new name for the worksheet such as May Expenses

4.     Press the Enter key on the keyboard to complete renaming the worksheet and to exit edit mode

5.     The new name should be visible on the worksheet tab

• A second option for renaming a worksheet is to double click on the current name in the worksheet tab which highlights the current name and switches to edit mode as in step 2 above.

Cells

A "cell" can be thought of as a box for holding a datum. A single cell is usually referenced by its column and row (A2 would represent the cell below containing the value 10)

Cell Pointer:

A cell pointer in excel is just the cell where you point the cursor in which its row and column can be seen is called a cell pointer.

Cell Address:

A cell address directs to a certain cell by stating the column and row. The cell address for the cell in fifth column and seventh row would be: E7

Enter Data:

First, place the cursor in the cell in which you want to start entering data. Type some data, and then press Enter. If you need to delete, press the Backspace key to delete one character at a time.

Delete Data

The Backspace key erases one character at a time.

1.     Press the Backspace key until Jordan is erased.

2.     Press Enter. The name "John" appears in cell A1.

Edit a Cell

After you enter data into a cell, you can edit the data by pressing F2 while you are in the cell you wish to edit.

Wrap Text

When you type text that is too long to fit in the cell, the text overlaps the next cell. If you do not want it to overlap the next cell, you can wrap the text.

Delete a Cell Entry

To delete an entry in a cell or a group of cells, you place the cursor in the cell or select the group of cells and press Delete.

Delete a Cell Entry

1.     Select cells A1 to A2.

2.     Press the Delete key.

Save a File

This is the end of Lesson1. To save your file:

1.     Click the Office button. A menu appears.

2.     Click Save. The Save As dialog box appears.

3.     Go to the directory in which you want to save your file.

4.     Type Lesson1 in the File Name field.

5.     Click Save. Excel saves your file.

Close Excel

Close Microsoft Excel.

1.     Click the Office button. A menu appears.

2.     Click Close. Excel closes.

Features of Ms-Excel

1. Live Preview:

Live Preview works with all the style galleries (see Number 3 in the list) as well as Font and Font Size pull-down menus in the Font group on the Home tab. It enables you to see how the data in the current cell selection would look with a particular formatting, font, or font size before you actually apply the formatting to the range. All you have to do is mouse over the thumbnails in the drop-down menu or gallery to see how each of its styles will look on your actual data.

2. The Ribbon:
The Ribbon is the  new Excel 2007 user interface. Based on the standard tabs to which various so-called contextual tabs are added as needed in formatting and editing of specific elements (such as data tables, charts, pivot tables, and graphic objects), the Ribbon brings together most every command you are going to need when performing particular tasks in Excel.

3. Style Galleries:
Excel 2007 is full of different style galleries that can be applied  (and, in many cases, very colorful) like formatting to tables and lists of data, charts, and various and sundry graphics you add to your worksheets.

4. Page Layout View:
Page Layout View in the Excel worksheet is used to visualizing the paging of printed reports. When you turn on this view by clicking the Page Layout View button on the Status bar, Excel does not just show the page breaks as measly dotted lines as in earlier versions but as actual separations. In addition, the program shows the margins for each page including headers and footers defined for the report (which you can both define and edit directly in the margin areas while the program is in this view) and it is also coupled with zoom slider

5. The Zoom Slider:
So, how'd we ever get along without the new Zoom slider that is now always there to use on the Excel 2007 Status bar? Instead of having to select a new magnification percentage for the worksheet from a drop-down menu on some obscure Zoom tool (something you can still do with the Zoom button on the View tab, if you really want to), you can zoom in and out on the spreadsheet

6. Format As Table:

By formatting a table of data with one of the many table styles available on the Table Styles drop-down gallery, all new entries to the table are automatically considered as part of the table when it comes to formatting, sorting, and filtering. By the way, filtering the table's data is made real easy by the automatic addition of filter buttons to the top row of column headings.

7. Charts right from the Insert tab:
Charts have been a part of Excel since in 1993 but it feels like not until Excel 2007 did they come into their own. Excel 2007 retires the Chart Wizard and in its place offers you direct access to all the major types of charts on the Ribbon's Insert tab. Simply select the data to be charted, click the command button for the chart type on the Insert tab and then select the style you want for that chart type.

8. Formatting and Editing from the Home tab:
The Home tab of the new Excel Ribbon brings home all the commonly-used formatting and editing features.

9. Cell Styles:

Excel 2007 you finally have cell styles. Moreover, these are styles you can preview in the worksheet to see how they look on the data before you apply them and which you apply to the cell selection by quickly and easily clicking its thumbnail in Cells Styles gallery

10. Conditional Formatting:

In addition to giving you the ability to define formatting when the values in cells meet certain conditions, you can now instantly apply one of many different Data Bars, Color Scales, and Icon Sets to the cell selection merely by clicking the set's thumbnail in their respective pop-up palettes. When you apply a set of Data Bars to a cell range, the length of each bar in the cell represents its value relative to the others.

Formatting in Ms-Excel

Use AutoFormat

• Start with data in a list format.
• Select any cell in the list.
• From the menu, select Format - AutoFormat
• In the AutoFormat dialog, 6 formats are displayed. Use the scrollbar to slide down to the third page of formats. Select the format called and Click OK.

Method 2: Use Conditional Formatting

• Select all the cells in your list.
• From the menu, select Format > Conditional Format
• In the Conditional Format dialog, change the first dropdown from "Cell Value Is" to "Formula Is".
• Enter this formula in the Formula box:
  =MOD(ROW(),2)=0
• Click the Format... button
• In the Format Cells dialog, Click the Patterns tab
• Choose a light green (or light blue or light yellow) pattern. Click OK.
• Click OK to close the Conditional Formatting dialog

Working with AutoFill

•AutoFill copies content and formats from a cell or range into an adjacent cell or range

•Select the cell or range that contains the formula or formulas you want to copy

•Drag the fill handle in the direction you want to copy the formula(s) and then release the mouse button

•To copy only the formats or only the formulas, click the AutoFill Options button and select the appropriate option

or

•Select the cell or range that contains the formula or formulas you want to copy

•In the Editing group on the Home tab, click the Fill button

•Select the appropriate fill direction and fill type (or click Series, enter the desired fill series options, and then click the OK button)

 

Using the AutoFill Options Button

•By default, AutoFill copies both the formulas and the formats of the original range to the selected range

•You can specify what is copied by using the AutoFill Options button that appears after you release the mouse button

 

 

Filling a Series

AutoFill can also be used to create a series of numbers, dates, or text based on a pattern

 

Cell Referencing

Type of cell referencing

Excel supports three types of cell referencing:

•Relative Referencing

•Absolute Referencing

•Mixed Referencing

Relative Reference

•In Excel and other spreadsheets, a relative cell reference identifies the location of a cell or group of cells.

•By default, a spreadsheet cell reference is relative. What this means is that as a formula or function is copied and pasted to other cells, the cell references in the formula or function change to reflect the function's new location.

Example (Relative Reference)

In cell B2, you need the following formula:

= A1 + A2

Copy  and paste the formula from B2 to cell B3?

•Click inside cell B2 to highlight it

•Click on cell B2 with your right mouse button, and select Copy from the menu that appears

•Now click into cell B3

•Again, right click the cell to get the menu and click Paste

 

•With cell B3 still highlighted, look at the formula bar at the top of Excel. You should see this formula:

= A2 + A3

•Click into B2, however, and the formula is this:

= A1 + A2

The problem is due to cell referencing. When you clicked Copy from the menu, Excel didn't only copy the formula.

The first cell reference:

 

The second cell reference:

 

Absolute Cell Referencing

•In Excel and other spreadsheets, an absolute cell reference identifies the location a cell or group of cells.

•An absolute cell reference is used when you want a cell reference to stay fixed on a specific cell.

•This means that as a formula or function is copied and pasted to other cells, the cell references in the formula or function do not change.

Example(Absolute Referencing)

 

•Click inside of cell B2 on your spreadsheet,         and change the formula to this:

= $A$1 + $A$2

•Now copy and paste it over to cell B3 again.

•If you need to copy and paste formulas, use Absolute cell references

•Absolute referencing means typing a dollar symbol before the numbers and letters of each cell reference (You can mix absolute and relative cell references, though).

Mixed Cell Referencing

•A mixed cell reference then, is a combination of relative and absolute cell references.

•As with absolute cell references, the dollar sign ( $ ) is used in mixed cell references to indicate that a column letter or row number is to remain fixed when copied from one cell to another.

•Examples of a mixed cell reference would be $E4 or F$6.

Templates

•Excel 2007 provides predesigned templates, worksheets for common uses. Templates contain standard or boilerplate text, but you can modify the templates to fit your needs.

•The following templates are automatically installed when you start using Excel 2007: Billing Statement, Blood Pressure Tracker, Expense Report, Loan Amortization, Personal Monthly Budget, Sales Report, and Time Card.

Using the installed templates

•Follow these steps to use one of the templates that comes installed with Excel 2007:

•Click the Office button and then click New.

•The New Workbook dialog box appears.

•Click Installed Templates under Templates in the pane on the left.

•The middle pane displays thumbnails for each of the installed templates. A preview of the selected thumbnail appears in a preview pane on the right.

 

• Select the desired template from the middle pane and click Create.

• Excel closes the dialog box and opens the selected template.

 

Entering a Formula

•A formula is an expression that returns a value

•A formula is written using operators that combine different values, returning a single value that is then displayed in the cell

–The most commonly used operators are arithmetic operators

•The order of precedence is a set of predefined rules used to determine the sequence in which operators are applied in a calculation

 

Entering a Formula

 

•Click the cell in which you want the formula results to appear

•Type = and an expression that calculates a value using cell references and arithmetic operators

•Press the Enter key or press the Tab key to complete the formula

Copying and Pasting Formulas

With formulas, however, Excel adjusts the formula’s cell references to reflect the new location of the formula in the worksheet

 

Introducing Functions

•A function is a named operation that returns a value

•For example, to add the values in the range A1:A10, you could enter the following long formula:

          =A1+A2+A3+A4+A5+A6+A7+A8+A9+A10

          Or, you could use the SUM function to accomplish the same thing:

          =SUM(A1:A10)

Entering a Function

 

Categories Of Excel

 

Inserting a Function

•Click the Formulas tab on the Ribbon

•To insert a function from a specific category, click the appropriate category button in the Function Library group. To search for a function, click the Insert Function button in the Function Library group, enter a description of the function, and then click the Go button

 

Typing a Function

As you begin to type a function name within a formula, a list of functions that begin with the letters you typed appears

 

Charts:

Charts allow you to present information contained in the worksheet in a graphic format. Excel offers many types of charts including: Column, Line, Pie, Bar, Area, Scatter and more.  To view the charts available click the Insert Tab on the Ribbon.
Create a Chart
To create a chart:

• Select the cells that contain the data you want to use in the chart
• Click the Insert tab on the Ribbon
• Click the type of Chart you want to create

To modify the labels and titles:

• Click the Chart
• On the Layout tab, click the Chart Title or the Data Labels button
• Change the Title and click Enter

Graphics:
Adding a Picture
To add a picture:

• Click the Insert tab
• Click the Picture button
• Browse to the picture from your files
• Click the name of the picture
• Click Insert
• To move the graphic, click it and drag it to where you want it

Adding Clip Art
To add Clip Art:

• Click the Insert tab
• Click the Clip Art button
• Search for the clip art using the search Clip Art dialog box
• Click the clip art
• To move the graphic, click it and drag it to where you want it

Data Sorting and Filtering:
Sorting and Filtering allow you to manipulate data in a worksheet based on given set of criteria.
Basic Sorts
To execute a basic descending or ascending sort based on one column:

• Highlight the cells that will be sorted
• Click the Sort & Filter button on the Home tab
• Click the Sort Ascending (A-Z) button or Sort Descending (Z-A) button

Custom Sorts
To sort on the basis of more than one column:

• Click the Sort & Filter button on the Home tab
• Choose which column you want to sort by first
• Click Add Level
• Choose the next column you want to sort
• Click OK

Filtering
Filtering allows you to display only data that meets certain criteria. To filter:

• Click the column or columns that contain the data you wish to filter
• On the Home tab, click on Sort & Filter
• Click  Filter button
• Click the Arrow at the bottom of the first cell
• Click the Text Filter
• Click the Words you wish to Filter

• To clear the filter click the Sort & Filter button
• Click Clear

Macros:

Macros are advanced features that can speed up editing or formatting you may perform often in an Excel worksheet. They record sequences of menu selections that you choose so that a series of actions can be completed in one step.
Recording a Macro
To record a Macro:

• Click the View tab on the Ribbon
• Click Macros
• Click Record Macro
• Enter a name (without spaces)
• Enter a Shortcut Key
• Enter a Description

• Perform the Macro
• Click Macros
• Click Stop Recording

Running a Macro
To run a Macro from the Keyboard shortcut, simply press the keys that you have programmed to run the Macro.  Or you can view all macros and run by:

• Click Macros
• Click View Macros
• Choose the Macro and click Run

Deleting A Macro

• Click Macros
• Click View Macros
• Choose the Macro and click delete

Data validation in Excel

If you want to validate a cell to restrict data entry to values in a drop-down list follow these steps:

1. Select the cell you want to validate.
2. On the Data tab, in the Data Tools group, click Data Validation.

·  In the Data Validation dialog box, click the Settings tab.

·  Click on the Allow box then select List from the drop-down list.

·  Click the Source box and then type the valid values separated by the appropriate list separator character depending on your installation( usually a comma “,” or semicolon “;”). For example if the cell is for a color of a car then you can limit the values by entering : Silver, Green, Blue.

·  Instead of typing your list manually, you can also create the list entries by referring to a range of cells in the same worksheet or another worksheet in the workbook.

·  To specify the location of the list of valid entries, do one of the following:

·         If the list is in the current worksheet, enter a reference to your list in the Source box, for example enter: =$A$1:$A$6.

Grouping Cells Using the Subtotal Command

Grouping is a really useful Excel feature that gives you control over how the information is displayed. You must sort before you can group. In this section we will learn how to create groups using the Subtotal command.

To Create Groups with Subtotals:

• Select any cell with information in it.
• Click the Subtotal command. The information in your spreadsheet is automatically selected and the Subtotal dialog box appears.

• Decide how you want things grouped. In this example, we will organize by Category.
• Select a function. In this example, we will leave the SUM function selected.
• Select the column you want the Subtotal to appear. In this example, Total Cost is selected by default.
• Click OK. The selected cells are organized into groups with subtotals.

To Collapse or Display the Group:

• Click the black minus sign, which is the hide detail icon, to collapse the group.
• Click the black plus sign, which is the show detail icon, to expand the group.
• Use the Show Details and Hide Details commands in the Outline group to collapse and display the group, as well.

To Ungroup Select Cells:

• Select the cells you want to remove from the group.
• Click the Ungroup command.
• Select Ungroup from the list. A dialog box will appear.
• Click OK.

 To Ungroup the Entire Worksheet:

• Select all the cells with grouping.
• Click Clear Outline from the menu.