Microsoft Excel and Features - Microsoft Office

	Microsoft Excel and Features

A spreadsheet is used to manipulate rows and columns of numbers and perform calculations on these (which can be simple arithmetic or use complicated formulae). Spreadsheet packages are also very good at producing stylish charts and graphs of the data in a spreadsheet. Excel worksheet divided into rows and columns. A worksheet contains maximum 255 columns (A..IV) and 65536 rows. The editable area of worksheet is known as cell for example (a1,b1,c2 etc).

About workbooks and worksheets

In Microsoft Excel, a workbook is the file in which you work and store your data. Because each workbook can contain many sheets, you can organize various kinds of related information in a single file. Use worksheets to list and analyze data. You can enter and edit data on several worksheets simultaneously and perform calculations based on data from multiple worksheets. When you create a chart, you can place the chart on the worksheet with its related data or on a separate chart sheet. The names of the sheets appear on tabs at the bottom of the workbook window. To move from sheet to sheet, click the sheet tabs. The name of the active sheet is bold.

Workspace

A workspace file saves information about all open workbooks, such as their locations, window sizes, and screen positions. When you open a workspace file by using the Open command (File menu), Microsoft Excel opens each workbook saved in the workspace. The workspace file does not contain the workbooks themselves, and you must continue to save changes you make to the individual workbooks.

Open the workbooks you want to open as a group.
Size and position the workbook windows as you want them to appear the next time you use the workbooks.
On the File menu, click Save Workspace.
In the File name box, enter a name for the workspace file.

To open the workbooks each time you start Microsoft Excel, save the workspace file in the XLStart folder in your Microsoft Excel folder. Save only the workspace file, not the workbook files, in the XLStart folder.

Types of data available in Excel

Before you enter data you need to know how excel handles it. Excel recognizes five different types of data: numbers dates, times, text/label, and formulas.

Numbers

Numbers are values that can be calculated. They can consist of the numerals 0 to 9, with a decimal point (a period) as a separator for decimal places and with commas as separators for thousands. Numbers can start with a dollar sign ($) or other currency symbol, or with a + or – sign. They can end with a % sign; They can also enclosed in parenthesis (as an alternative to the –sign, for indicating negative numbers).

You control the display of numbers by formatting the cells that contain them. For example, you could format a cell to display currency amounts with two decimal places.

Date

Excel uses slashes when displaying dates that need them, but you can use hyphens when entering dates . for example, birth 11/28/1999 and 11-28-1999 will be stored correctly.

Time

Click the cell where you want to enter data. Type the data and press ENTER or TAB.

Use a slash or a hyphen to separate the parts of a date; for example, type 9/5/96 or Jun-96.To enter a time based on the 12-hour clock, type a space and then a or p after the time; for example, 9:00 p. Otherwise, Microsoft Excel enters the time as AM.

Formulas

Formulas are mathematical formulas telling excel to perform calculations on data in cell., for example , to add the data in the cells A1,B2, and C3 and display the result in cell D4, You would enter the formula +a1+b2+c3 in cell d4.

Text/Label

Excel considers any data that it does not recognize as number , date, time, or formula to be text. This is a wide brief; in practice, it means that data containing letters (other than cell addresses, A.M, or P.M., and so on.) will be treated as text. For example, if you enter a list of employees’ names, positions, and work histories, excel will treat them as text.

About Toolbars

Toolbars allow you to organize the commands in Microsoft Excel the way you want so that you can find and use them quickly. For example, you can add and remove menus and buttons, create your own custom toolbars, hide or display toolbars, and move toolbars. In previous versions of Microsoft Excel, toolbars contained only buttons. Now toolbars can contain buttons, menus, or a combination of both.

The menu bar is a special toolbar at the top of the screen that contains menus such as File, Edit, and View. The default menu bar contains menus and commands for working with worksheets. If you're working with a chart sheet or an embedded chart, the chart menu bar is displayed instead. You can customize the menu bars just like any built-in toolbar; for example, you can add and remove buttons and menus.

Some menu commands have images next to them so you can quickly associate the command with the corresponding toolbar button. If you want easier access to a command, create a toolbar button for it by using the Customize dialog box (Tools menu).

When you quit Microsoft Excel, changes you made to the menu bar and built-in toolbars, any custom toolbars you created, and the toolbars currently displayed are saved in a toolbars settings file in your Windows folder. This settings file is saved as username

8.xlb, where username is your Windows or network log-in name. If your computer is not connected to a network or not set up with a log-in prompt, the settings file is saved as excel8.xlb. The toolbar configuration saved in this file is used by default each time you start Microsoft Excel.. If you frequently use a particular set of toolbars, you can save the configuration in a separate toolbars settings file so that you don't have to redisplay and arrange the toolbars each time. Toolbars you create or customize are available to all workbooks on your own system. To ensure that a custom toolbar is always available with a specific workbook, you can attach the toolbar to the workbook.

To Start Excel

On the Start menu's Programs menu. To start Excel:

Click the Start button.
Point at Programs and, from the Programs menu click on Microsoft Excel.

Constructing a simple Excel worksheet

You are now going to design a very simple Excel spreadsheet to calculate your net income after tax has been deducted from your gross income. This exercise shows you the basic principle behind using spreadsheets, using a formula to make calculations. You need do this section only if you think you may want to set up your own spreadsheet (as opposed to simply using a spreadsheet someone else has set up for you).

As a brief illustration:

First, open a new blank worksheet:

To give the new worksheet a meaningful name:

Double-click on the Sheet1 tab and type Tax.

To give meaningful labels to cells on your worksheet:

Click in cell C3 (the cell reference C3 appears at the top left of the screen).
In cell C3 type Gross Income. (Don't worry that the text spills over into Column D).
Move to cell C4 and type Tax Free Allowance.
In C5 type Taxable Income.
In C6 type Tax Paid
In C7 type Net Income.

To adjust the column width to fit the text you have typed:

Select (highlight) column C, by clicking anywhere on the grey area of the cell containing the column heading 'C'.
From the Format menu, choose Column and then AutoFit Selection.

Now set up the formulae to calculate your net income. Gross Income minus Tax Free Allowance gives the Taxable Income. Tax is calculated at a certain rate (say 30% of the Taxable Income) and the Tax Paid is then deducted from the Gross Income to give the Net Income. To translate this into Excel's terms:

In D3, type as your Gross Income 9000 (don't worry at the moment that this doesn't have a pound sign).
In D4, type as your Tax Free Allowance 3500.
In D5 type the formula +D3-D4 and then press Enter. (The initial plus sign denotes that the cell contains a formula rather than a literal value). This particular formula causes the value in cell D5 to become that of D3 minus D4, so D5 should now contain 5500.

If cell D5 does not display 5500, check and correct the formula. To do this:

Click in D5 and notice that the formula appears in a box underneath the Formatting Toolbar (just to the right of the box that shows the cell reference).
To correct a formula, click on the formula and edit it. When ready, press Enter.

Now calculate the Tax Paid (30% of the Taxable Income D5), using '*' as the multiplication sign and '/' for division, i.e.:

In D6 type +D5*(30/100).
If D6 does not calculate the correct tax, 1650, correct the formula (in the way described above).
Now calculate the Net Income in D7 (as Gross Income minus Tax Paid). The answer should be 7350.

If you wish, now format the cells containing monetary figures to show currency. To do this:

Select (highlight) the whole of column D.
Click the Currency Style button. If the values in the cells are too large to display they will show as ###### and you need to increase the width of column D.
Now try changing your Gross Income and Tax Free Allowance.

This spreadsheet is rather inflexible because if the Tax Rate changes you will have to provide a new formula in D6. It can be improved as follows:

In A1 type Tax Rate (%).
In B1 type 30.
Alter the formula in D6 so that it reads +D5*(B1/100).
Experiment with changing the value in B1.

The above illustrates a common use of spreadsheets - to ask 'what if' questions (eg 'What would happen to my income if the Tax Rate went up to 50%'). The spreadsheets you have seen are very simple. Complex systems (eg in economics or physical sciences) can be modeled using enormous spreadsheets and complex calculations and hypotheses can be tested or predictions made by changing the values of variables on the spreadsheet.

Types of series that Microsoft Excel can fill in for you

You can automatically fill in several types of series by selecting cells and dragging the fill handle or by using the Series command (point to Fill on the Edit menu, and then click Series). To select the type of series from a shortcut menu, select the starting values for the series; then hold down the right mouse button as you drag the fill handle.

Time: A time series can include increments of days, weeks, or months that you specify, or it can include repeating sequences such as weekdays, month names, or quarters. For example, the initial time selections in the following table result in the series shown.

Initial selection Extended series

9:00 10:00, 11:00, 12:00

Mon Tue, Wed, Thu

Monday Tuesday, Wednesday, Thursday

Jan Feb, Mar, Apr

Jan, Apr Jul, Oct, Jan

Jan-96, Apr-96 Jul-96, Oct-96, Jan-97

15-Jan, 15-Apr 15-Jul, 15-Oct

1994, 1995 1996, 1997, 1998

AutoFill:

The AutoFill feature extends several types of series as shown in the following table. The fourth example shows how Microsoft Excel can extend part of a selection (Product 1) and copy another part (On backorder). The last example is a best-fit trend.

Note: Items separated by commas are in adjacent cells.

Initial selection Extended series

Mon Tue, Wed, Thu,...

1-Jan, 1-Mar 1-May, 1-Jul, 1-Sep,...

Qtr3 (or Q3 or Quarter3) Qtr4, Qtr1, Qtr2,...

Product 1, On backorder Product 2, On backorder, Product 3, On backorder,...

text1, textA text2, textA, text3, textA,...

1st Period 2nd Period, 3rd Period,...

Product 1 Product 2, Product 3,...

1, 2 3, 4, 5, 6,...

1, 3, 4 5.66, 7.16, 8.66,...

Linear and Growth series

When you create a linear series by dragging the fill handle, Microsoft Excel increases or decreases values by a constant value that is based on the selected starting values. When you create a growth series by selecting the Growth Trend command from the shortcut menu, Microsoft Excel multiplies values by a constant factor. For information about linear and growth series that you can create with the Series command, click .

Initial selection Extended linear series

1, 2 3, 4, 5

1, 3 5, 7, 9

100, 95 90, 85

Initial selection Extended growth series

1,2 4, 8, 16

1,3 9, 27, 81

2,3 4.5, 6.75, 10.125

Auto Formatting Worksheets

For formatting tables quickly , try Excel’s AutoFormat Features-which , like word’s table auto-format feature. Offers sundry predefined table formats encompassing all formatting from fonts through borders and shading. To use AutoFormat on selected cells or on a range of cells surrounded by blank cells:

Choose Format --> AutoFormat to display The AutoFormat dialog box.
Choose a format from the Table format list box. Watch the sample box for a preview of how your table will look.
If you want to apply only some of the formatting characteristics , click the option button to display the six options in the formats to apply group box at the bottom of the AutoFormat dialog box. Clear the check boxes for the options you do not want to apply.
Click the ok button to close the AutoFormat dialog box and apply the Auto Formatting you choose.

Edit cell contents

Double-click the cell that contains the data you want to edit Or Press F2 key.
Make any changes to the cell contents.
To enter your changes, press ENTER.

To cancel your changes, press ESC.

Clear or delete cells, rows, or columns

When you delete cells, Microsoft Excel removes them from the worksheet and shifts the surrounding cells to fill the space. When you clear cells, you remove the cell contents (formulas and data), formats, or comments, but leave the blank cells on the worksheet.

Starting Excel and opening the example spreadsheet

Clear contents, formats, or comments from cells

Select the cells, rows, or columns you want to clear.
On the Edit menu, point to Clear, and then click All, Contents, Formats, or Comments.
If you click a cell and then press DELETE or BACKSPACE, Microsoft Excel removes the cell contents but does not remove any comments or cell formats.
If you clear a cell, Microsoft Excel removes the contents, formats, comments, or all three from a cell. The value of a cleared cell is 0 (zero), and a formula that refers to that cell will receive a value of 0.

To remove all comments from a worksheet, click Go To on the Edit menu, click Special, and then click Comments. Then point to Clear on the Edit menu, and click Comments

Delete cells, rows, or columns

Select the cells, rows, or columns you want to delete.
On the Edit menu, click Delete. OR press delete key

Change column width and row height

You can adjust the width of columns and the height of rows. You can also define the default width of columns for a worksheet. Defining the default column width adjusts all columns to the same width, except columns that have previously been changed.

Change column width

Drag the boundary on the right side of the column heading until the column is the width you want.
The displayed column width is the average number of digits 0-9 of the standard font that fit in a cell.
To change the column width for multiple columns, select the columns you want to change. Then drag a boundary at the right of a selected column heading. To change the column width for all columns on the worksheet, click the Select All button, and then drag the boundary of any column heading.
To make the column width fit the contents, double-click the boundary to the right of the column heading.

Change row height

Drag the boundary below the row heading until the row is the height you want.
To change the row height for multiple rows, select the rows you want to change. Then drag a boundary below a selected row heading. To change the row height for all rows on the worksheet, click the Select All button, and then drag the boundary below any row heading.
To make the row height fit the contents, double-click the boundary below the row heading.

Define the default column width

To define the default column width for all worksheets in a workbook, select all worksheets.
On the Format menu, point to Column, and then click Standard Width.
Type a new measurement.

The number that appears in the Standard column width box is the average number of digits 0-9 of the standard font that fit in a cell. To define the default column width for all new workbooks and worksheets, create a workbook template and a worksheet template.

Copying and Moving Data

You can copy and move data in excel by using Cut, Copy, and paste (as discussed in previous) or drag-and-drop. There are two quick points to note here :

When pasting a-range of data, you need only select the upper-left anchor cell of the destination, but be sure excel won’t overwrite any important data in the other cells that the range will cover.

To use drag & drop, select the cell or range to move or copy, and then move the mouse pointer to one of its borders.

Go To (Edit menu)

In Microsoft Excel, scrolls through the worksheet and selects the cell, range, or cells with special characteristics you specify. Press Ctrl+G or Choose Alt+E+Goto and then type the cell address and press enter key.

Cells Formatting

Applies formats to the selected cells. This command might not available if the sheet is protected. To see a complete list of built-in number formats, click Cells on the Format menu. The Number tab provides number formats not found on the Formatting toolbar, including accounting, date, time, fraction, scientific, and text formats. The Special category includes formats for ZIP Codes and phone numbers. You can also customize these formats. To change the way numbers, dates, and times are displayed, you can change the number format of selected cells. Changing the number format does not affect the actual data values used in calculations. You can apply some number formats by using the number formatting buttons on the Formatting toolbar. For example, click the Currency Style button to display 35561 as $ 35,561.00.

Excel Functions

Syntax: SUM(number1,number2, ...)

Adds all the numbers in a range of cells.

Number1, number2, ... are 1 to 30 arguments for which you want the total value or sum.

Numbers, logical values, and text representations of numbers that you type directly into the list of arguments are counted. See the first and second examples following.
If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored. See the third example following.
Arguments that are error values or text that cannot be translated into numbers cause errors.

Examples

SUM(3, 2) equals 5:SUM("3", 2, TRUE) equals 6 because the text values are translated into numbers, and the logical value TRUE is translated into the number 1.Unlike the previous example, if A1 contains "3" and B1 contains TRUE, then:

SUM(A1, B1, 2) equals 2 because references to nonnumeric values in references are not translated. If cells A2:E2 contain 5, 15, 30, 40, and 50:SUM(A2:C2) equals 50

SUM(B2:E2, 15) equals 150

Excel Functions(Average)

Returns the average (arithmetic mean) of the arguments:

Syntax

AVERAGE(number1,number2, ...)

Number1, number2, ... are 1 to 30 numeric arguments for which you want the average.

Remarks

The arguments must be either numbers or names, arrays, or references that contain numbers.
If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.

When averaging cells, keep in mind the difference between empty cells and those containing the value zero, especially if you have cleared the Zero values check box on the View tab (Options command, Tools menu). Empty cells are not counted, but zero values are.

Examples

If A1:A5 is named Scores and contains the numbers 10, 7, 9, 27, and 2, then:

AVERAGE(A1:A5) equals 11

AVERAGE(Scores) equals 11

AVERAGE(A1:A5, 5) equals 10

AVERAGE(A1:A5) equals SUM(A1:A5)/COUNT(A1:A5) equals 11

If C1:C3 is named Other Scores and contains the numbers 4, 18, and 7, then:

AVERAGE(Scores, Other Scores) equals 10.5

Excel Functions (Max)

Returns the largest value in a set of values. Syntax: MAX(number1,number2,...)

Number1,number2,... are 1 to 30 numbers for which you want to find the maximum value.

You can specify arguments that are numbers, empty cells, logical values, or text representations of numbers. Arguments that are error values or text that cannot be translated into numbers cause errors.
If an argument is an array or reference, only numbers in that array or reference are used. Empty cells, logical values, or text in the array or reference are ignored. If logical values and text must not be ignored, use MAXA instead.
If the arguments contain no numbers, MAX returns 0 (zero).

Examples

If A1:A5 contains the numbers 10, 7, 9, 27, and 2, then:

MAX(A1:A5) equals 27

MAX(A1:A5,30) equals 30

Excel Functions (Min)

Returns the smallest number in a set of values. Syntax: MIN(number1,number2, ...)

Number1, number2,... are 1 to 30 numbers for which you want to find the minimum value.

You can specify arguments that are numbers, empty cells, logical values, or text representations of numbers. Arguments that are error values or text that cannot be translated into numbers cause errors.
If an argument is an array or reference, only numbers in that array or reference are used. Empty cells, logical values, or text in the array or reference are ignored. If logical values and text should not be ignored, use MINA instead.
If the arguments contain no numbers, MIN returns 0.

Examples

If A1:A5 contains the numbers 10, 7, 9, 27, and 2, then:

MIN(A1:A5) equals 2

MIN(A1:A5, 0) equals 0

MIN is similar to MAX. Also see the examples for MAX.

Excel Functions (Round)

Rounds a number to a specified number of digits. Syntax ROUND(number,num_digits)

Number is the number you want to round.

Num_digits specifies the number of digits to which you want to round number.

· If num_digits is greater than 0 (zero), then number is rounded to the specified number of decimal places.

· If num_digits is 0, then number is rounded to the nearest integer.

· If num_digits is less than 0, then number is rounded to the left of the decimal point.

Examples

ROUND(2.15, 1) equals 2.2

ROUND(2.149, 1) equals 2.1

ROUND(-1.475, 2) equals -1.48

ROUND(21.5, -1) equals 20

Excel Functions if()

Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.Use IF to conduct conditional tests on values and formulas.

Syntax 1 :IF(logical_test,value_if_true,value_if_false)

Logical_test is any value or expression that can be evaluated to TRUE or FALSE.

Value_if_true is the value that is returned if logical_test is TRUE. If logical_test is TRUE and value_if_true is omitted, TRUE is returned. Value_if_true can be another formula. Value_if_false is the value that is returned if logical_test is FALSE. If logical_test is FALSE and value_if_false is omitted, FALSE is returned. Value_if_false can be another formula.

Remarks

Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. See the following last example.
When the value_if_true and value_if_false arguments are evaluated, IF returns the value returned by those statements.
If any of the arguments to IF are arrays, every element of the array is evaluated when the IF statement is carried out. If some of the value_if_true and value_if_false arguments are action-taking functions, all of the actions are taken.

Examples

In the following example, if the value in cell A10 is 100, then logical_test is TRUE, and the total value for the range B5:B15 is calculated. Otherwise, logical_test is FALSE, and empty text ("") is returned that blanks the cell that contains the IF function.

IF(A10=100,SUM(B5:B15),"")

Suppose an expense worksheet contains in B2:B4 the following data for "Actual Expenses" for January, February, and March: 1500, 500, 500. C2:C4 contains the following data for "Predicted Expenses" for the same periods: 900, 900, 925.

You can write a formula to check whether you are over budget for a particular month, generating text for a message with the following formulas:

IF(B2>C2,"Over Budget","OK") equals "Over Budget"

IF(B3>C3,"Over Budget","OK") equals "OK"

Suppose you want to assign letter grades to numbers referenced by the name AverageScore. See the following table.

If AverageScore is Then return

Greater than 89 A

From 80 to 89 B

From 70 to 79 C

From 60 to 69 D

Less than 60 F

You can use the following nested IF function:

IF(AverageScore>89,"A",IF(AverageScore>79,"B",

IF(AverageScore>69,"C",IF(AverageScore>59,"D","F"))))

In the preceding example, the second IF statement is also the value_if_false argument to the first IF statement. Similarly, the third IF statement is the value_if_false argument to the second IF statement. For example, if the first logical_test (Average>89) is TRUE, "A" is returned. If the first logical_test is FALSE, the second IF statement is evaluated, and so on.

Excel Functions count()

Counts the number of cells that contain numbers and numbers within the list of arguments. Use COUNT to get the number of entries in a number field in a range or array of numbers Syntax:COUNT(value1,value2, ...)

Value1, value2, ... are 1 to 30 arguments that can contain or refer to a variety of different types of data, but only numbers are counted.

Arguments that are numbers, dates, or text representations of numbers are counted; arguments that are error values or text that cannot be translated into numbers are ignored.
If an argument is an array or reference, only numbers in that array or reference are counted. Empty cells, logical values, text, or error values in the array or reference are ignored. If you need to count logical values, text, or error values, use the COUNT function.

Examples

In the following example,COUNT(A1:A7) equals 3,COUNT(A4:A7) equals 2

COUNT(A1:A7, 2) equals 4

Excel Functions or()

Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.

Syntax: OR(logical1,logical2,...)

Logical1,logical2,... are 1 to 30 conditions you want to test that can be either TRUE or FALSE.

The arguments must evaluate to logical values such as TRUE or FALSE, or in arrays or references that contain logical values.
If an array or reference argument contains text, numbers, or empty cells, those values are ignored.
If the specified range contains no logical values, OR returns the #VALUE! error value.
You can use an OR array formula to see if a value occurs in an array. To enter an array formula, press CTRL+SHIFT+ENTER in Microsoft Excel 97 for Windows or +ENTER in Microsoft Excel 97 for the Macintosh.

Examples

OR(TRUE) equals TRUE

OR(1+1=1,2+2=5) equals FALSE

If A1:A3 contains the values TRUE, FALSE, and TRUE, then:

OR(A1:A3) equals TRUE

Excel Functions and()

Returns TRUE if all its arguments are TRUE; returns FALSE if one or more arguments is FALSE. Syntax:AND(logical1,logical2, ...)

Logical1, logical2, ... are 1 to 30 conditions you want to test that can be either TRUE or FALSE.

The arguments must evaluate to logical values such as TRUE or FALSE, or the arguments must be arrays or references that contain logical values.
If an array or reference argument contains text or empty cells, those values are ignored.
If the specified range contains no logical values, AND returns the #VALUE! error value.

Examples

AND(TRUE, TRUE) equals TRUE

AND(TRUE, FALSE) equals FALSE

AND(2+2=4, 2+3=5) equals TRUE

If B1:B3 contains the values TRUE, FALSE, and TRUE, then:

AND(B1:B3) equals FALSE

If B4 contains a number between 1 and 100, then:

AND(1<B4, B4<100) equals TRUE

Suppose you want to display B4 if it contains a number strictly between 1 and 100, and you want to display a message if it is not. If B4 contains 104, then:

IF(AND(1<B4, B4<100), B4, "The value is out of range.") equals "The value is out of range."

If B4 contains 50, then:

IF(AND(1<B4, B4<100), B4, "The value is out of range.") equals 50

Sorting

If you previously sorted a list on the same worksheet, Microsoft Excel uses the same sorting options unless you change them.

1 Click a cell in the column you would like to sort by.

2 Click Sort Ascending .

Note In a PivotTable, Microsoft Excel uses the selected field to sort items in ascending alphabetic order. Numbers are sorted from lowest to highest value.

Create a chart

You can display Microsoft Excel data graphically in a chart. Charts are linked to the worksheet data they are created from and are updated when you change the worksheet data.You can create charts from cells or ranges that are not next to one another.

You can create either an embedded chart or a chart sheet.

Select the cells that contain the data that you want to appear in the chart.
If you want the column and row labels to appear in the chart, include the cells that contain them in the selection.
Click Chart Wizard .
Follow the instructions in the Chart Wizard.

If your worksheet has multiple levels of row and column labels, your chart can also display those levels. When you create the chart, include the row and column labels for each level in your selection. To preserve the hierarchy when you add data to the chart, change the cell range used to create the chart.

Select A Different Chart Type

For most 2-D charts, you can change the chart type of either a data series or the entire chart. For bubble charts, you can change only the type of the entire chart. For most 3-D charts, changing the chart type affects the entire chart. For 3-D bar and column charts, you can change a data series to the cone, cylinder, or pyramid chart type.

Click the chart you want to change.
To change the chart type of a data series, click the data series.
To change the chart type of the entire chart, don't click anything on the chart.
On the Chart menu, click Chart Type.
On the Standard Types or Custom Types tab, click the chart type you want.

To apply the cone, cylinder, or pyramid chart type to a 3-D bar or column data series, click Cylinder, Cone, or Pyramid in the Chart type box on the Standard Types tab, and then select the Apply to selection check box.

Note If you clear the Apply to selection check box, Microsoft Excel changes the chart type for the entire chart even if a single data series is selected.

Returns a subtotal in a list or database. It is generally easier to create a list with subtotals using the Subtotals command (Data menu). Once the subtotal list is created, you can modify it by editing the SUBTOTAL function.

Excel Functions subtotal()

Syntax:SUBTOTAL(function_num,ref1,ref2,…)

Function_num is the number 1 to 11 that specifies which function to use in calculating subtotals within a list.

Function_Num Function

1 AVERAGE

2 COUNT

3 COUNTA

4 MAX

5 MIN

6 PRODUCT

7 STDEV

8 STDEVP

Function_Num Function

9 SUM

10 VAR

11 VARP

Ref1, ref2, are 1 to 29 ranges or references for which you want the subtotal.

Remarks

If there are other subtotals within ref1, ref2,… (or nested subtotals), these nested subtotals are ignored to avoid double counting.
SUBTOTAL will ignore any hidden rows that result from a list being filtered. This is important when you want to subtotal only the visible data that results from a list that you have filtered.
If any of the references are 3-D references, SUBTOTAL returns the #VALUE! error value.

Example

SUBTOTAL(9,C3:C5) will generate a subtotal of the cells C3:C5 using the SUM function

Display a subset of rows in a list by using filters

You can apply filters to only one list on a worksheet at a time.

Click a cell in the list you want to filter.
On the Data menu, point to Filter, and then click AutoFilter.
To display only the rows that contain a specific value, click the arrow in the column that contains the data you want to display.
Click the value.
To apply an additional condition based on a value in another column, repeat steps 3 and 4 in the other column.

To filter the list by two values in the same column, or to apply comparison operators other than Equals, click the arrow in the column, and then click Custom. For information about displaying rows by comparing values.

Notes

When you apply a filter to a column, the only filters available for other columns are the values visible in the filtered list.
You can apply up to two conditions to a column with AutoFilter. If you need to apply three or more conditions to a column, use calculated values as your criteria, or copy records to another location, you can use advanced filters.

Excel Functions GOAL SEAK

Find a specific result for a cell by adjusting the value of one other cell

On the Tools menu, click Goal Seek.
In the Set cell box, enter the reference for the cell that contains the formula you want to resolve.
In the To value box, type the result you want.
In the By changing cell box, enter the reference for the cell that contains the value you want to adjust.


MS WORD	MS EXCEL	MS POWER POINT