WHAT IS A SPREADSHEET?

Back to Top

SPREADSHEET TERMINOLOGY

Back to Top

THE WORKBOOK WINDOW

Back to Top

CREATING & EDITING A SPREADSHEET

COPYING AND MOVING DATA
To use the copy command, the area to be copied must be selected. This is called the copy area or the source.
When you cut or copy the cell contents, they are stored in the clipboard. This is the temporary storage area in a computer's memory.
The instructions displayed on the status bar ask you to select a destination or the paste area on which to copy the data.
A selection containing two or more cells is called a range. The cells in a range can be adjacent or non-adjacent. The former is a block of adjoining cells. The latter is two or more selected cells that do not adjoin each other.

Back to Top

FORMATTING CELLS

DATE & TIME ENTRY
Excel automatically recognizes certain types of data input, such as date and time, and appropriately formats the entry. It stores all dates as serial numbers with each day numbered from the beginning of the century. The date serial number 1 corresponds to January 1, 1900, and the integer 65380 is December 31, 2078. Integers are assigned consecutively beginning with 1 and ending with 65,380. They are called Date Numbers. Converting a date to a serial number allows dates to be used in calculations.
There are four basic methods that can be followed while formatting cells:
AutoFormat
Apply an existing format style
Create a new format
Copy the format of a different cell or range
AutoFormat is a built-in collection of formats that you can apply to a cell or range. Excel determines the levels of summary and details in the range falling under AutoFormat and applies the format accordingly.
In order to apply AutoFormat to a range, select the range you want to format and choose Format/AutoFormat. In the Table format box, choose the format you want. To use only selected parts of the AutoFormat, click on Options. Then clear the check boxes of the formats you do not want to apply.
To apply an existing style, select the cells you want to format and choose Format/Style. In the Style name box, click on the style you want.
In order to create a new style, select a cell that has the combination of formats you want to include in the new style. Choose Format/Style. In the Style name box, type a name for the new style. To define and apply the style to the selected cells, click on OK. To define the style without applying it, click on Add, and then click on Close. If none of the cells have the formats you require for the style, you can specify the formats when you name the style.
The last option is to copy formats from one cell or range to another. To do this, select a cell or a range that has the desired formatting you want to copy and click on Format Painter. This button is present on the standard toolbar and looks like the one shown below:
Select the cell or range you want to copy the formatting to. To copy the formatting in the selected cell or range to several locations, double-click on Format Painter. When you finish copying the formatting, click on the button again.

Back to Top

MANAGING AND ANALYZING A COMPLEX WORKBOOK

INSERTING/DELETING ROWS AND COLUMNS
To insert a single row, click on a cell in the row immediately below where you want the new row. To insert multiple rows, select rows immediately below where you want the new rows. Select the same number of rows that you want to insert and choose Insert/Rows.
To insert a single column, click on a cell in the column immediately to the right of where you want to insert the new column. To insert multiple columns, select columns immediately to the right of where you want to insert the new columns. Select the same number of columns that you want to insert and choose Insert/Columns.
When you delete rows/columns, Excel removes them from the worksheet and shifts in the surrounding cells to fill in the gaps. To delete rows or columns, select them and choose Edit/Delete.
Excel keeps formulas up to date by adjusting references to the shifted cells to reflect their new locations. However, a formula that refers to a deleted cell displays the #REF! error value.

HEADERS AND FOOTERS
A header is a line or several lines of text that appears at the top of each page just below the top margin. A footer is a line or several lines of text that appears at the bottom of each page just above the bottom margin. The text of headers and footers can be formatted just like any other text.
You can have only one custom header and one custom footer on each worksheet. If you create a new custom header or footer, it will replace any existing custom headers or footers on the worksheet.
To create a custom header or footer, click on the worksheet and choose View/Header and Footer.
To base a custom header or footer on an existing built-in header or footer, click on the header or footer in the Header or Footer box. Click on Custom Header or Custom Footer. Click on the Left, Center, or Right section box. Then click on the appropriate buttons to insert the header or footer information, such as the page number, that you want in that section.
Alternatively, you can also add headers and footers by the following method:
Preview the spreadsheet
Click on setup
Open the Header and Footer tab
The Window that appears looks similar to the screen shown below:
The Header drop down box lists the pre-defined headers that you can select to form the header of your spreadsheet. The selected Header is displayed in the Header area of the dialog box.
Similarly, the Footer drop down box lists the pre-defined footers that you can select to form the footer of your spreadsheet. The selected Footer is displayed in the footer area of the dialog box.
You can use an Excel workbook in an earlier version of Microsoft Excel or in another program by saving the workbook in a file format accessible by the other program.
To save a copy of a workbook under a different name or in a different location, open the workbook you want to make a copy of and choose File/Save As. In the Filename box, type a new name for the workbook and click on Save.
To save a backup copy of a workbook, choose File/Save As. Click on Options and select the Always create backup check box.

COPYING A SHEET
You may want to enter some data in a separate sheet and add it to the same workbook. By default, a new Excel workbook file includes three blank sheets. The first sheet is active when you open a file. To activate the next sheet, click on the Sheet2 tab at the bottom of the screen. Alternatively, Ctrl+Page Up moves to the previous sheet, and Ctrl+Page Down moves to the next sheet.
The blank or the active sheet contains the cell selector, and this is the sheet that will be affected by any action. The name of the active sheet is always bold in the sheet tab.
To copy the active sheet into a new sheet, hold down the Ctrl key while dragging the sheet tab to where you want the new sheet inserted. The mouse pointer changes to a +, as you drag the mouse from one tab to the other. The + sign indicates that the sheet is being copied. A black triangle that also appears indicates where the sheet has to be inserted.
Excel names the copy of the sheet as Sheet1 (2) where Sheet1 is the name of the sheet being copied. The new sheet is the active sheet and contains a duplicate of the data in the first sheet.
The menu equivalent to copy a sheet is Edit/Move or Copy Sheet. To create a new blank sheet, choose Insert/Worksheet.

NAMING SHEETS
Each sheet in a workbook can be assigned a descriptive name in order to make it easy to identify the contents of a particular sheet. The following guidelines are important while naming a worksheet. A sheet name should possess the following features:
Can be up to 31 characters
Can be entered in uppercase or lowercase letters or a combination of both
Can contain any combination of letters, numbers and spaces
Cannot contain the characters:? * / \
Cannot be enclosed in square brackets ([ ])
As more sheets are added to a workbook, recalling the contents of each sheet becomes a difficult task. You can even rename the sheets to make this easier.
Double-clicking on a sheet tab activates the tab and highlights the existing sheet name. The existing name disappears as soon as you begin to type the new name.

USING AUTOFILL
The AutoFill feature makes entering a series of headings easier by logically repeating and extending the series. AutoFill recognizes trends and automatically extends data and alphanumeric headings as per your specifications.
Dragging the fill handle activates the AutoFill feature if Excel recognizes the entry in the cell as an entry that can be incremented. When AutoFill extends the entries, it uses the same style as the original entry. For example, if you enter a heading for January as JAN (abbreviated with all letters in uppercase), all the extended entries in the series will be the same i.e.abbreviated and in uppercase.
Dragging the fill handle across or down increments the entries in increasing order. Similarly, dragging up or left increments in decreasing order.
A starting value of a series may contain more than one item that can be incremented, such as JAN-98, in which both JAN and 98 can be incremented. If you want only one value to be incremented, hold down the right mouse button as you drag, and then click on the appropriate command on the AutoFill shortcut menu to specify which value to increment.
When you drag the fill handle, suppose a series is created that you do not want incremented. In this case select the original values again and hold down the Ctrl key as you drag the fill handle. The entries will be copied and not incremented.

REFERENCING MULTIPLE SHEETS
A formula reference to cells in different worksheets of the same workbook allows you to use data from multiple sheets and to calculate new values based on this data. The formula contains a sheet reference as well as a cell reference. The former contains the name of the sheet enclosed in quotes. It is separated from the cell reference by an exclamation mark.
For example, =Sheet4!A7 would display the entry in cell A7 of Sheet4 which is the active cell of the current worksheet. A formula can be created using references on multiple sheets; for example, =Sheet2!B2+Sheet4!D4.
The link can also be created entering a 3-D reference in a formula. A 3-D reference is a reference to the same cell or range on multiple sheets in the same workbook. It consists of the names of the beginning and the closing sheets enclosed in quotes and separated by a colon. An exclamation mark and the cell or range reference follow this. The cell or range reference is the same on each sheet in the specified sheet range.
Any sheets stored between the starting and closing names of the reference are included. If a sheet is inserted or deleted, the range is automatically updated. 3-D references make it easy to analyze data in the same cell or range of cells on multiple worksheets.
Just like a formula that references cells within a sheet, a formula that references cells in multiple sheets is automatically recalculated when data in a referenced cell changes.
To reference a cell or range in another sheet, begin the formula or function, hold the Shift key, switch to the other sheet, and select the cell or cells. While using a keyboard, users must enter the 3-D reference by typing it out.
Name cells on more than one worksheet by using a 3-D reference
This is done by the following sequence of steps:
On the Insert menu, point to Name, and then click on Define.
In the Names in workbook box, type the name.
If the Refers to box contains a reference, select the equal sign (=) and the reference and press Backspace.
In the Refers to box, type = (an equal sign).
Click on the tab for the first worksheet to be referenced.
Hold down Shift and click on the tab for the last worksheet to be referenced.
Select the cell or range of cells to be referenced.

SPLITTING WINDOWS
A sheet window can be split into sections called panes to make it easier to view the different parts of a sheet at the same time. The panes can consist of any number of columns or rows along the top or left edge of the window. You can divide the sheet into two panes either horizontally or vertically, or four panes if you split the window both vertically and horizontally.
Each pane can be scrolled independently to display different areas of the sheet. When split vertically, the panes scroll together when you scroll vertically, but independently when you scroll horizontally. Horizontal panes scroll together when scrolled horizontally and independently when you scroll them vertically.
Dragging the split box at the top of the vertical scroll bar downwards creates a horizontal split, and dragging the split box at the right end of the horizontal scroll bar to the left creates a vertical split. The Window/Split command can be used to quickly create a four-way split in the active cell.
Panes are most useful for viewing a worksheet that consists of different areas or sections. Creating panes allows you to display the different sections of the worksheet in separate panes. It also allows you to quickly switch between the panes to access the data in different sections without having to repeatedly scroll to the areas.
Freezing panes
Freezing panes prevents the data in a pane from scrolling as you move to different areas in a worksheet. You can freeze the information in the top and left panes of a window only.
The Freeze command on the Window menu is used to freeze panes. To create two horizontal panes with the upper pane frozen, move the cell selector in the leftmost column in the window to the row below the point where you want the split to appear. Then choose the command.
To create two vertical panes with the left pane frozen, move the cell selector in the top row of the window. Then select the column to the right of where you want the split to appear.
To create four panes, click on the cell below and to the right of where you want the split.
This feature is most useful when your worksheet is organized using row and column headings. It allows you to keep the titles on the top and left edge of the worksheet in view as you scroll both horizontally and vertically through the worksheet data.
To restore a window split into two scrollable areas, double-click on any part of the split bar that divides the panes. To remove unscrolling or frozen panes, click on Unfreeze Panes on the Window menu.

MOVING A SHEET
Situations may arise when you might want to move a sheet to a different location. You should be careful that calculations or charts based on data in a worksheet do not become inaccurate when you move the worksheet. Similarly, if you move a worksheet between sheets referred to by a 3-D formula reference, data on the worksheet might be included in the calculation.
To move or copy sheets to an existing workbook, follow the sequence of steps given below:
Open the workbook that will receive the sheets.
Switch to the workbook that contains the sheets you want to move or copy, and then select the sheets.
On the Edit menu, click on Move or Copy Sheet.
In the To book box, click on the workbook to receive the sheets. To move or copy the selected sheets to a new workbook, click on New book.
In the Before sheet box, click on the sheet before which you want to insert the moved or copied sheets.
To move sheets within the current workbook, drag the selected sheets along the row of sheet tabs. To copy the sheets, hold down the Ctrl key, and then drag the sheets; release the mouse button before you release the Ctrl key.

Back to Top

FORMULAE AND FUNCTIONS

ENTERING FORMULAE
You can create a wide variety of formulas in Microsoft Excel, ranging from performing a simple arithmetic operation to analyzing a complex model of formulas.
A formula can contain functions, which are pre-defined formulas that perform simple or complex calculations. To perform multiple calculations simultaneously and then return one or more results, use an array formula.
Follow the steps given below to enter a formula:
Click on the cell in which you want to enter the formula
Type = (an equal sign)
If you click on Edit Formula or Paste Function, Microsoft Excel inserts an equal sign.
Enter the formula
Press the Enter key

COPY FORMULAE
You can enter the same formula into a range of cells by selecting the range first, typing the formula, and then pressing Ctrl+Enter. You can also enter a formula into a range of cells by copying a formula from another cell.
The formula is displayed in the formula bar. A copied formula is not an exact duplicate of the original formula. Instead, the cell referenced in the formula changes to reflect the new location of the formula. This is because the references in a formula are relative references.
Relative references
A relative reference is a cell or range reference in a formula whose location is interpreted by Excel in relation to the position of the cell that contains the formula.
When a formula is copied, the referenced cells in the formula automatically adjust to reflect the new worksheet location. The relative relationship between the referenced cell and the new location is maintained. Since relative references automatically adjust to a new location, the relative references in a copied formula refer to cells other than those referenced in the original formula. The relationship between cells in both the copied and pasted formulas is the same although the cell references are different.

ABSOLUTE REFERENCES
An absolute reference is a cell or range reference in a formula whose location does not change when the formula is copied.
To stop the relative adjustment of cell references, enter a $ (dollar sign) character before the column letter and the row number. This changes the cell reference to absolute. When a formula containing an absolute cell reference is copied to another row and column location in the worksheet, the cell reference does not change. It is a duplicate of the cell reference in the original formula.
A cell reference can also be a mixed reference. In a mixed reference, either the column letter or the row number is preceded by a dollar sign. This makes only the row or the column absolute. When a formula containing a mixed cell reference is pasted to another location in the worksheet, only the part of the cell reference that is not absolute changes relative to its new location in the worksheet.
The table shown below gives examples of relative and absolute references and the result when a reference in cell C10 to cell B28 is copied to cell E13.
Cell Contents of C10 Copied to Cell E13 Type of Reference
$B$28 $B$28 Absolute Reference
B$28 D$28 Mixed Reference
$B28 $B31 Mixed Reference
B28 D31 Relative Reference
Relative and absolute references :
You can change a cell reference to absolute or mixed by typing in the dollar sign or by using the ABS (Absolute) key F4. To use the ABS key, the program must be in the edit mode.

FUNCTIONS
Functions are pre-written formulas that perform certain types of calculations automatically.
The syntax or rules of structure for entering all functions is:
Function Name (argument1, argument2, …)
A function name identifies the type of calculation to be performed. Most functions require that you enter one or more arguments following the function name. An argument is the data a function uses to perform calculations. The type of data the function requires depends upon the type of calculation being performed. Most commonly, the argument consists of numbers or references to cells that contain numbers. The argument is enclosed in parentheses, and multiple arguments are separated by commas. If a function starts the formula, enter an = (equal to sign), before the function name.
Excel includes several hundred functions, divided into nine categories. Some of the common functions and the results they calculate are shown in the following table.
  Category Function Calculation Done
Financial PMT Payment for a loan based on constant payments and constant interest rate
 PV Returns present value of investment: total amounts that a series of future payments is worth now
Time and Date TODAY Returns serial number that represents today´s date
 DATE Returns serial number of a particular date
 NOW Returns serial number of current date and time
Math and Trig SUM Adds all numbers in a range of cells
 ABS Returns absolute value of a number
Statistical AVERAGE Returns average of its arguments
 MAX Returns largest value in a set of values
Lookup and Reference COLUMNS Returns number of columns in an array or reference
 CHOOSE Chooses a value or action to perform from a list of values, based on an index number
Database DSUM Adds numbers in the field or records in a database that match specified conditions.
 DAVERAGE Averages values in a column in a list or database that matches specified condition
Text DOLLAR Converts number to text, using currency format
 UPPER Converts text to uppercase
Logical IF Returns one value if a condition you specify evaluates to True and another value if condition is False
/ AND Returns True if all arguments are True; False if any argument is False
Information ISLOGICAL Returns True if value is logical, either True or False
ISREF Returns True if value is a reference
Common functions and their results :
Nested functions
Functions can be used as arguments for other functions. When a function is used as an argument, or nested, it must return the same type of value that the argument uses. If a nested function does not return the correct type of value, Excel will display a #VALUE! error value.
A formula can contain up to seven levels of nested functions. When Function B is used as an argument in Function A, Function B is a second-level function. If Function B contains Function C as an argument, Function C would be a third-level function.
You can use the formula palette to nest functions as arguments. For example, you can insert Function B as an argument of Function A by clicking on the drop down arrow in the formula bar. If you want to continue entering arguments for Function A, click on the name of Function A in the formula bar.
A formula palette is a tool that helps you to create or edit a formula and also provides information about functions and their arguments. The palette appears below the formula bar when you click on the Edit Formula button on the formula bar or the Paste Function button on the Standard toolbar.
A formula bar is a bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. It displays the constant value or formula used in the active cell. To display or hide the formula bar, click on Formula Bar in the View menu.

THE PASTE FUNCTION FEATURE
This feature simplifies the process of entering functions by prompting you to select a function from a list and then helps you to enter the arguments correctly.
Click on the Paste Function button on the standard toolbar.
The menu equivalent is Insert/Function and the keyboard shortcut key is Shift+F3.
Select the type of function you want to use. The Function Category list box displays the names of function categories, and the Function Name list box displays the names of the functions in the selected category. The Most Recently Used function category, the currently selected category in the diagram above, initially displays 10 of the most common functions.
Select the function of your choice. Now the Formula Palette dialog box is displayed to help you enter the arguments required for the selected function. The upper section displays the proposed argument range in the Number1 text box. The actual numbers in the selected range are displayed to the right of the text box. The lower section describes the function you chose, and the arguments it requires.
When the Formula Palette is open, the name box changes to a drop down function list.

THE IF FUNCTION
The IF function checks to see if certain conditions are met and then takes action based on the results of the check.
Syntax for the IF function:
IF(logical_test, value_if_true, value_if_false)
This function contains three arguments:
Logical_test
Value_if_true
Value_if_false
The logical_test is an expression that makes a comparison using logical operators. Logical operators are used in formulas and functions that compare numbers in two or more cells or to a constant. The result of the comparison is either True, when the condition is met, or False, when the condition is not met.
The logical operators are given in the following table:
Symbol Meaning
= Equal to
< Less than
> Greater than
<= Less than or equal to
>= Greater than or equal to
<> Not equal to
NOT Logical NOT
OR Logical OR
AND Logical AND
Commonly used logical operators:
The logical test argument asks the question, Does the entry in the cell meet the stated condition? The answer is either True(Yes) or False(No). The second argument, value_if_true, provides directions for the function to follow if the logical test result is true. The third argument, value_if_false, provides directions for the function to follow if the logical test result is false.
The steps to enter an IF function are:
Move to the cell where you want the result returned by the function to appear
Click on on the fx (paste function) button on the toolbar
Select IF
Click on OK
The following dialog box appears where you enter the three arguments of the function:
Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. 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 of the IF function:
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 containing 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 by generating a message using 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.

NAMING A RANGE
A descriptive range name can be assigned to a cell or range of cells and used in place of cell references. The name can be used any time a cell or range is requested as part of a command or in a formula or function.
Excel automatically proposes a name for the cell or range using the contents of the active cell if it contains text, or the cell above or left of the active cell if the active cell does not contain text. If the active cell or the cells above or to the left of the active cell do not contain text, or if you do not want to use the proposed name, you can type in a name of your own choice.
The name can be up to 255 characters. It can include letters, numbers, underlines, periods, backslashes and question marks. It cannot contain spaces. The first character must be a letter, underline or backslash. A name that resembles a cell reference is not allowed.
In Excel, you can name a single cell or a range of cells to make formulas easier to read and remember. If you have row and column labels on a worksheet, you can refer to them directly in a formula, or you can use them as names that represent the associated cells. You can also create a name that represents the same cell or range of cells on more than one worksheet.

LABELS AND NAMES IN FORMULAS
Worksheets often have labels at the top of each column and to the left of each row that describe the data within the worksheet. You can use these labels within formulas when you want to refer to the related data. You can also create descriptive names that are not labels on the worksheet to represent cells, ranges of cells, formulas or constants.
When you create a formula that refers to data in a worksheet, you can use the column and row labels in the worksheet to refer to the data.
For example, if a table contains sales amounts in a column labeled Sales and a row for a division labeled Support, you can find the sales amount for the Support division by entering the formula =Support Sales.
The space between the labels is the intersection operator, which designates that the formula should return the value in the cell at the intersection of the row labeled Support and the column labeled Sales.
If your data does not have labels or if you have information stored on one worksheet that you want to use on other sheets within the same workbook, you can create a name that describes the cell or range. A descriptive name in a formula makes it easier to understand the purpose of the formula.
For example, the formula =SUM(FirstQuarterSales) might be easier to identify than =SUM(Sales!C20:C30). In this example, the name FirstQuarterSales represents the range C20:C30 on the worksheet named Sales.
Names are available for any sheet within the workbook. For example, if the name ProjectedSales refers to the range A20:A30 on the first worksheet in the workbook, you can use the name ProjectedSales on any other sheet in the same workbook to refer to range A20:A30 on the first worksheet.
Names can also be used to represent formulas or values that do not change (constants). For example, you can use the name SalesTax to represent the sales tax amount (such as 6.2 per cent) applied to sales transactions.
By default, names use absolute cell references.

NAME A CELL OR A RANGE OF CELLS
To name a cell or a range of cells:
Select the cell or range of cells, or non-adjacent selections that you want to name.
Click on the Name box at the left end of the formula bar.
Type the name for the cells.
Press Enter.
You cannot name a cell while you are changing the contents of that cell.

NAME CELLS BY USING EXISTING ROW AND COLUMN LABELS
To name cells by using existing row and column labels:
Select the range you want to name, including the row or column labels.
On the Insert menu, point to Name, and then click on Create.
In the Create names in box, designate the location that contains the labels by selecting the Top row, Left column, Bottom row, or Right column check box.
A name created by this procedure refers only to the cells that contain values and does not include the existing row and column labels.

NAME CELLS ON MORE THAN ONE WORKSHEET
You can name cells on more than one worksheet by using a 3-D reference.
On the Insert menu, point to Name, and then click on Define.
In the Names in workbook box, type the name.
If the Refers to box contains a reference, select the equal sign (=) and the reference and press Backspace.
In the Refers to box, type = (an equal sign).
Click on the tab for the first worksheet to be referenced.
Hold down Shift and click on the tab for the last worksheet to be referenced.
Select the cell or range of cells to be referenced.

AUTOMATIC RECALCULATION
Automatically recalculating a formula whenever a number in a referenced cell in the formula changes is one of the most powerful features of electronic worksheets. Only those formulas directly affected by a change in the data are recalculated. This is called minimal recalculation. Without this feature, it could take several minutes to recalculate all formulas each time a number was changed in a large worksheet. This feature decreases the recalculation time by only recalculating dependent formulas.

THE WHAT-IF ANALYSIS
The What-If Analysis is a technique used to evaluate the effects of changing selected factors in a worksheet. This technique is a common accounting function that has been simplified with the introduction of spreadsheet programs. By substituting different values in cells that are referenced by formulas, you can quickly see the effect of the changes when the formulas are recalculated.

THE SOLVER
Solver is an Excel tool that answers what-if problems by determining the value of a cell. To do this the user has to change values in one or more cells in the worksheet.
Solver calculates a formula to achieve a given value by changing one of the variables that affect the formula. To do this, Solver works backwards from the result of a formula to find the numbers. The cells you select must be related to each other through formulas on the worksheet. If they are not related, changing one will not affect the other.
Use Solver to determine the maximum or minimum value of a cell by changing other cells. For example, calculate the maximum profit you can generate by changing advertising expenditures. The cells you select must be related through formulas on the worksheet. If they are not related, changing one cell will not change the other.
To define and solve a problem by using Solver, follow the steps given below:
1. On the Tools menu, click on Solver. If the Solver command is not on the Tools menu, you need to install the Solver add-in.
2. In the Set Target Cell box, enter a cell reference or name for the target cell. The target cell must contain a formula.
3. To set no limit on the size of the value of the target cell click on Max. To have the value of the target cell be as small as possible, click on Min. To have the target cell be of a certain value, click on Value of, and then type the value in the box.
4. In the By Changing Cells box, enter a name or reference for each adjustable cell, separating non-adjacent references with commas. The adjustable cells must be related directly or indirectly to the target cell. You can specify up to 200 adjustable cells.
5. In the Subject to the Constraints box, enter any constraints you want to apply.
6. Click on Solve.
7. To keep the solution values on the worksheet, click on Keep Solver Solution in the Solver Results dialog box. To restore the original data, click on Restore Original Values.

USING SPELL CHECK
Excel includes a Spell Check tool that locates misspelled and duplicate words, and capitalization irregularities and proposes the correct spelling. The Spell Check compares each word to an in-built dictionary of words. If the word does not appear in the main or custom dictionary, it is identified as misspelled.
The Main Dictionary is part of the Word program, whereas the Custom Dictionary is the one you create to contain words that you commonly use but are not included in the main dictionary.
Excel also includes an AutoCorrect feature that corrects typing errors automatically online by comparing each completed word to a list of commonly mistyped words and phrases. You can also add words and phrases you commonly mistype, to the list, and they will be corrected automatically as they are typed in.
The Microsoft Office package has the same spelling dictionary and listing of AutoCorrect entries as other office applications.
After you start the Spell Check feature, Excel begins checking the worksheet for words that it cannot locate in the main dictionary. The cell selector moves to the first cell containing a misspelled word, and the Spelling dialog box is displayed. The wrong word is displayed in the first line of the dialog box. The Change To text box displays the suggested replacement. A list of other possible replacements is displayed in the Suggestions list box. If the Change To list box is not correct, you can select from this list or type in the correct word in the Change To list box.
The various options shown in the dialog box are:
Ignore Leaves selected word unchanged
Ignore All Leaves this and all identical words in the worksheet, unchanged
Change Changes the selected word to word displayed in Change To text box
Change All Changes this and all identical words in worksheet to word displayed in Change To text box
Add Adds selected word to a custom dictionary as Excel will not question word during subsequent spell checks.
Various options shown in the dialog box:
To accept the suggested replacement, click on the Change button.
The correction is made in the worksheet and Excel continues checking the rest of the worksheet.
The Spell Check continues till such time as no further errors are located . A dialog box is displayed, indicating the entire worksheet has been checked. When it reaches the end of the sheet, if the cell selector was not at the beginning of the sheet when checking started, the program will ask you if you want to continue checking at the beginning of the sheet.
To end Spell Check click on the OK button.

Back to Top

LINKING WORKBOOKS

Back to Top

ADVANCED FORMATTING OPTIONS

CREATING CUSTOM HEADERS AND FOOTERS
In order to add a custom header or footer to a sheet, choose View/Header and Footer/Custom Header or Custom Footer.
Choose Header from the screen that appears. Now the Header dialog box that appears looks similar to screen shown below:
The Left Section text box will display the header text you entered aligned with the left margin, the Center Section will center the text, and the Right Section will right align the text. The cursor location is currently in the left Section text box. Enter the text you want to appear as the Header.
Certain other options are available with the Header or Footer window. They are shown in the diagram below:
The header, as you have entered it, appears in the sample header area of the dialog box. Click on OK after you have entered the desired text.
Create the custom footer in the same manner.

USING AUTOFORMAT
AutoFormat is a built-in collection of formats, such as font size, patterns and alignment that you can quickly apply to a range of data. Excel determines the levels of summary and detail in the selected range and applies the formats accordingly.
To format an entire list or a large range that has distinct elements, for example, column and row labels, summary totals, and detail data, you can apply a built-in table design, called an AutoFormat. The design uses distinctive formats for the various elements of the table.
To apply several formats simultaneously and ensure that cells have consistent formatting, you can apply a style to the cells. Excel provides styles to format numbers as currency, percentages, or with commas separating thousands. You can create your own styles to apply a font, font size, number formats, cell borders, shading as well as protect cells from changes.
If you have already formatted some cells on a worksheet the way you want, you can copy the formatting to other cells.
Apply AutoFormat to a range
In order to do this, select the range you want to format. Choose Format/AutoFormat. In the Table format box, click on the format you want. To use only selected parts of the AutoFormat, click on Options. Then clear the check boxes of the formats you do not want to apply.
Apply an existing style
To apply an existing style, select the cells you want to format and choose Format/Style. In the Style name box, click on the style you want.
Create a new style
To create a new style, select a cell that has a combination of formats you want to include in the new style. Choose Format/Style. In the Style name box, type a name for the new style. To define and apply the style to the selected cells, click on OK. To define the style without applying it, click on Add, and then click on Close. If no cells have the formats you require for the style, you can specify the formats when you name the style.
Copy formats from one cell or range to another
In order to copy formats from one cell or range to another, select a cell or range that has the formatting you want to copy. Click on Format Painter. Select the cell or range you want to copy the formatting to. To copy the formatting in the selected cell or range to several locations, double-click on Format Painter. When you finish copying the formatting, click on the button again.

Back to Top

WORKING WITH CHARTS

TYPES OF CHARTS
Excel 97 can produce 14 different types of charts or graphs, which may vary in their respective formats. The 14 basic types of charts are:
Area chart
Area charts show the magnitude of change over time by emphasizing the area under the curve created by each data series.
Bar charts
Bar charts display data as evenly spaced bars. The categories are displayed along the Y-axis and the values are displayed along the X-axis, placing more emphasis on comparison than on time.
Column charts
Column charts display data as evenly spaced bars. They are similar to bar charts, except that categories are organized horizontally and values vertically to emphasize variation over time.
Line charts
Line charts display data along a line. They are used to show changes in data over time, emphasizing time and rate of change rather than the amount of change.
Pie charts
Pie charts display data as slices of a circle or pie. They show the relationship of each value in a data series to the series as a whole. Each slice of the pie represents a single value in the series.
Doughnut charts
Doughnut charts are similar to pie charts except that they can show more than one data series.
Radar charts
Radar charts display a line or area chart wrapped around a central point. Each axis represents a set of data points.
XY or Scatter charts
XY (Scatter) charts are used to show the relationship between two ranges of numeric data.
Surface charts
Surface charts display values in the form of what appears to be a rubber sheet stretched over a 3-D column chart. These are useful for finding the best combination between sets of data.
Bubble charts
Bubble charts compare sets of three values. They are like scatter charts with the third value represented in the size of bubble markers.
Stock charts
A stock chart is a high-low-close chart. It requires three series of values in this order.
Cylinder charts
Cylinder charts display values with a cylindrical shape.
Cone charts
Cone charts display values with a conical shape.
Pyramid charts
Pyramid charts also display values with a conical shape.

CHART ELEMENTS
A chart consists of several elements. It is important to understand these elements so that you can identify the appropriate data to select in the worksheet.
The basic elements of a two-dimensional chart are:
Axes
An axis is a line that borders one side of the plot area, providing a frame of reference for measurement or comparison in a chart. The bottom boundary line of the chart is the X-axis. It is used to label the data being charted, such as a point in time or a category. The left boundary line of the chart is the Y-axis. This axis is a numbered scale whose numbers are determined by the data used in the chart. Typically, the X-axis line is horizontal and the Y-axis is vertical.
Plot Area
The selected worksheet data is visually displayed within the X and Y axes boundaries. This is called the plot area.
In a 2-D chart, the area bounded by the axes, including all the data series, is called a plot area. In a 3-D chart, the area bounded by the axes and including data series, category names, tick-mark labels and axis titles, is called a plot area.
Data Series
A data seriesis a group of related data points plotted in a chart originate from rows or columns on a single worksheet. Each data series in a chart has a unique color or pattern. You can plot one or more data series in a chart. Pie charts have only one data series.
Data Marker
A data markeris a bar, area, dot, slice or other symbol in a chart that represents a single data point or value originating from a worksheet cell. Related data markers in a chart constitute a data series. To distinguish one data series from another, different data markers are used.
Chart Gridlines
Gridlinesare lines you can add to a chart that make it easier to view and evaluate data. Gridlines extend from the tick marks on an axis across the plot area.
Legend
A legend is a box that identifies the patterns or colors assigned to the data series or categories in a chart.
Titles
A chart can also contain descriptive titles that explain the contents of the chart. The chart title is displayed centered above the charted data. Titles can also be used to describe the X and Y axes. The X-axis and Y-axis title lines are called category-axis title and value-axis title respectively.

CREATING A CHART
Let us create a chart that will show the sales pattern over four years-- 1997-2001. All charts are drawn from data contained in a worksheet. To create a new chart, select the worksheet range containing the data you want displayed as a chart, as well as any row or columns heading you want to use in the chart. Excel then translates the selected data into a chart based upon the shape and contents of the worksheet selection.
The first chart you create of the worksheet data will use the year labels in cells C5 through F5 to label the X-axis. The numbers to be charted are in cells C10 through F10. In addition, the label Total in cell B10 will be used as the chart legend, making the entire range B10 through F10.
Notice that the two ranges, C5 to F5 and B10 to F10 are not adjacent and are of different sizes. When plotting non-adjacent ranges in a chart, the selections must form a rectangular shape. Hence, the blank cell B5 is also selected.
Follow the steps given below to specify the range and create the chart:
Select B5 through F5
Press the Ctrl key
Select B10 through F10
Click on the Chart Wizard icon on the toolbar
Alternatively, you can choose Insert/Chart to create a chart. The following dialog box appears on the screen.
The chart wizard is an interactive program that guides you through the steps to create a chart. The first step is to select the chart type from the chart Type list box. The default chart type is a column chart. Each type of chart includes many variations. To accept the default settings and move to the next step,
Click on Next
The chart wizard Step 2 of 4 dialog box displays a sample chart that will be created using the current chart settings and data. The selected data range appears in the Data Range text box. You can specify another range if you wish to or simply accept the displayed range. The two Series In options control how Excel interprets the data series. The interpretation varies depending upon the type of chart selected. In a column chart, the default setting is Rows. Hence Excel uses the first row as the X-axis category labels and the further left column as the legend text. Changing the setting to Columns reverses this, placing the first column of the range along the X-axis and the first row of the range as the legend text. To accept the default settings,
Click on Next
The Step 3 of 4 dialog box appears.
In this step you can change the appearance of chart elements such as a legend and titles. To make the chart more readable and clear, you can add a chart title as well as titles along the X and Y axes. As you add the titles, the sample chart will reflect the changed settings.
Replace the default title in the Chart Title text box with nepc Company Sales
In the Category (X) axis text box, enter Years and in the Value (Y)- Axis text box, enter Sales
Click on Next.
The Step 4 of 4 dialog box appears, which looks similar to the following screen.
In the last step you can specify where you want the chart displayed in the worksheet. A chart can be displayed in a separate chart sheet or as an object in an existing sheet.
Note
Objects are items that can be added to a worksheet that can be sized and moved. A chart that is inserted into a worksheet is one of several different types of graphic objects. A chart is also referred to as a chart object because it is created using the Chart toolbar.
You would like the chart you have created to appear as an object in the same worksheet that contains the data. This is also the default selection.
To complete the chart,
Click on Finish.
The chart that is created looks like the following chart:
The chart with the specified settings is displayed on the worksheet. A chart object that is inserted into a sheet is called an embedded chart.

ADDING PATTERNS
You can add patterns to a chart data series. The data series are automatically displayed in different colors in a colored monitor and as varying shades of gray in a monochrome monitor. You can change the default display to black and white patterns to easily distinguish one series from another.
To add black and white patterns, you need to select each data series object and use the Format command to change the color and pattern settings. The shortcut to do this is to right-click on the data series.
Select a data series and right-click on it to format it. The formula bar displays a series formula. This formula links the chart object to the source worksheet, Sheet1.
The formula contains four arguments. They are:
A reference to the cell that includes the data series name (used in the legend)
References to the cells that contain the categories (X-axis numbers)
References to the numbers plotted
An integer that specifies the number of data series plotted
Now you can format or change the pattern used in the selected series.
The options available in the Patterns tab vary depending upon the type of data marker that is selected. In this case, since a column data marker is selected, the options let you change the border and the background area of the selected series. The current setting is displayed in the sample area.
To change the area color to black, and to add a pattern,
Click on Fill Effects
Choose the desired pattern
Change the foreground color to black
From the color palette, choose a color of your choice
Double-click on OK
The chart is redrawn with the new settings specified. The legend is also updated to reflect the new pattern and color selection.

ADDING A TEXT BOX
A text box is a rectangular object in which you type text. Text boxes can be added to a sheet or to a chart. Add a text box containing the text "Increasing Sales" to the worksheet to draw attention to the area of the chart.
A text box is created using the Text Box button on the Drawing toolbar. Draw the text box with the help of this button at the desired location and of the desired size. The text box displays the cursor pointer, where you can enter the text.
You can choose Format/Text Box to modify the format settings.

CHANGING DATA LABELS
To change data labels on the worksheet, click on the cell that contains the information you want to change, type in the new text or value, and then press Enter.
To change data labels on the chart, click once on the data label you want to change to select the data labels for the entire series, and then click again to select the individual data label. Type the new text or value, and then press Enter.
If you change the data label text on the chart, it is no longer linked to a worksheet cell.

CHANGE CATEGORY AXIS LABELS
To change category axis labels on a worksheet, click on the cell that contains the label name you want to change, type in the new name, and then press Enter.
To change category axis labels on the chart, click on the chart, and then click on Source Data on the Chart menu. In the Category axis labels box on the Series tab, specify the worksheet range you want to use as category axis labels. You can also type the labels you want to use, separated by commas.
If you type the label text in the Category axis labels box, the category axis text is no longer linked to a worksheet cell.

CREATING A PIE CHART
In pie charts there are no axes. Instead, the worksheet data that is charted is displayed as slices in a circle or pie. Each slice is labeled. A pie chart compares parts to the whole in a manner similar to a stacked-column chart. Each value in the range is a slice of the pie displayed as a percentage of the total.
The use of X (category) and data series in a pie chart is different from their use in a column or line chart. The X series label the slices of the pie rather than the X-axis. The data series is used to create the slices in the pie. Only one data series can be specified in a pie chart.
Let us use the same sample worksheet to create a pie chart. Follow the steps given below:
Select B6 through C9
Display the Chart toolbar
Click on the Chart Type button
Select Pie Chart as the option
A basic pie chart is drawn on the worksheet. It does not include a chart title or labels to clarify the slices of the pie.
Each value in the data series is displayed as wedge in the pie chart. Let us now add the chart title. A pie chart can only have a main title since it does contains axes.
Choose Chart/Chart Options
In the Chart Title text box of the titles tab, enter the title 1995 Sales
The sample chart shows the title displayed in the center of the chart area. Now you can add labels to the wedges to make the information represented in the chart more clear.
Choose Data Labels/Show Label and Percent.
The sample pie chart is redrawn again to show the data label and per cents.
To make the pie chart look more attractive, you can give it the appearance of a 3-D pie chart. To do this, change the chart type to a 3-D pie chart.
Now that the pie chart is formatted, you can separate each slice or explode it, to make the data more emphatic. Select the wedge or slice and to explode it, drag it away from the pie.

CHANGING THE WORKSHEET DATA
Since charts give a visual representation of data, they sometimes help in checking for incorrect data entries. For instance, looking at the chart you may realize that the total sales for Item D was less as compared to other items. But the graph shows that the sales figures do not vary a great deal from each other. When you check the data, you find that it has been entered incorrectly.
For example, enter the value 1400 in cell C9 instead of 14000.
The worksheet has been recalculated, and the pie chart has been redrawn to reflect the change. Item D now accounts for only 4 per cent of the total sales in the year 1997. Since the chart document is linked to the source data, changes to the source data are automatically reflected in the chart.

PRINTING THE CHART
In order to print the chart, first save the workbook. Preview all the sheets in the workbook. If the chart is embedded on a worksheet, you can adjust where it will print on the page by sizing and moving the chart using the mouse in Page Break view.
To do this, click on the worksheet outside of the chart area, and then click on Page Break Preview on the View menu.
If you are working with a chart sheet, you can size and scale the chart area, specify how it should be placed on the printed page, and then view it in the preview window. To set printing options for a chart sheet, click on the tab for the chart sheet, click on Page Setup on the File menu, and then select the options you want on the Chart tab.
To move and size the chart area of a chart sheet by using the mouse, you must click on Custom on the Chart tab and then click on OK to return to the chart sheet.

Back to Top

ADDING CONTROLS

IMPROVING THE APPEARANCE OF THE FORM
It is very essential to improve the appearance of a form. A form usually takes advantage of border lines, white space, color and shading to identify areas of form. While improving the appearance of a form, its simplicity should be of prime consideration. Although there are many format features that can be incorporated, too many tend to complicate a form and confuse the user.

HIDING ROWS AND COLUMNS
You might want to improve the appearance of a form in the window by removing information that is not needed by the user.
To hide a row or column:
Select the rows or columns you want to hide.
On the Format menu, point to either Row or Column, and then click on Hide.
To display hidden rows, select cells in the row above and in the row below the hidden rows.
To display hidden columns, select cells in both the columns to the left and to the right of the hidden columns.
On the Format menu, point to either Row or Column, and then click on Unhide.
If the first row or column of a worksheet is hidden, click on Go To on the Edit menu. Type A1 in the Reference box, and click on OK. Point to Row or Column on the Format menu, and then click on Unhide.

HIDING WINDOW ELEMENTS
To display or hide specific window elements:
On the Tools menu, click on Options, and then click on the View tab.
Select the check boxes for items you want to display, and then clear the check boxes for the items you want to hide.
To hide window elements so as to show more data on the screen, click on Full Screen on the View menu. To restore the toolbars and other hidden window elements, click on Full Screen.

WORKSHEET PROTECTION
To prevent other users from changing a sheet's contents, you can protect the entire worksheet or specified areas of the worksheet. When you protect a sheet, all cells and graphic objects on the sheets are locked.
The contents of a locked cell cannot be changed. If you want to leave some cells unlocked for editing, such as in a worksheet, you use an entry form. You can lock cells containing labels and formulas but unlock the entry fields so that others can fill them in.
An entire workbook can also be protected from unauthorized changes in two ways by ensuring that sheets cannot be moved or deleted or new sheets inserted. A workbook's windows can also be protected. This prevents changes to the size and position of the windows, ensuring they appear the same way each time the workbook is opened.
In addition, you can include a password that prevents an unauthorized person from accessing and changing the worksheet. If you use a password, you must remember the password in order to turn off the protection in future.

Back to Top

Excel Overview Workshop & Example Spreadsheets
Using Excel & MS Query

KnowledgeBase Index