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.
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.
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 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.
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.
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 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.
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.
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:
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:
To give meaningful labels to cells on your worksheet:
To adjust the column width to fit the text you have typed:
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:
If cell D5 does not display 5500, check and correct the formula. To do this:
Now calculate the Tax Paid (30% of the Taxable Income D5), using '*' as the multiplication sign and '/' for division, i.e.:
If you wish, now format the cells containing monetary figures to show currency. To do this:
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:
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
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:
Edit cell contents
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
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
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
Change row height
Define the default column width
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.
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.
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.
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
Returns the average (arithmetic mean) of the arguments:
Number1, number2, ... are 1 to 30 numeric arguments for which you want the average.
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.
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.
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.
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.
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.
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.
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:
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.
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.
Logical1,logical2,... are 1 to 30 conditions you want to test that can be either TRUE or FALSE.
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.
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
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.
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.
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()
Function_num is the number 1 to 11 that specifies which function to use in calculating subtotals within a list.
Ref1, ref2, are 1 to 29 ranges or references for which you want the subtotal.
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.
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.
Excel Functions GOAL SEAK
Find a specific result for a cell by adjusting the value of one other cell