Formula bar

Do not press Return, but instead click the mouse in the formula bar and place the cursor at the end of the entry. 

You can display or not display most components of the Excel window, such as menubars, scrollbars, the formula bar, gridlines, row and column headers. 

To turn off the display of the scrollbars, the formula bar, gridlines, row and column headers, or to restore them if they are missing, choose Options... (Excel for Windows) or Preferences... (Excel for the Macintosh) from the Tools menu and choose the View tab then check or imcheck the appropriate box. 

Click on the cell in one comer of the range, hold down the mouse button and drag to the cell in the opposite corner of the range. The range of cells will be highlighted. The size of the selection (e.g., lOR x 3C) is displayed in the Reference Area of the formula bar. 

To enter a value in a worksheet cell, select the cell with the mouse pointer, which appears as a large open cross when it passes over cells. Clicking on the desired cell highlights it, indicating that this is heactive cell, the cell in which you can now enter a value. As you type in a value, the characters appear in the formula bar and the active cell. You can complete the entry in several ways. 

Press the Enter button in the formula bar. The cell remains selected. 

Type Entered displayed in Cell Displayed In Formula Bar... 

Excel has a remarkable ability to recognize the format of the value that you have entered as a number, a percent, a debit value, as currency, in scientific notation, as a date or time, or even as a fraction. The number will be displayed in the cell in the proper format, but the number equivalent of the value will appear in the formula bar. Figure 1-6 illustrates number formats recognized by Excel. 

If you enter a fraction less than 1, such as 1/3, it will be interpreted as a date ("3-Jan"). To prevent Excel from converting the fraction to a date, enter a zero and a space before the fraction (0 1/3). The zero indicates that the entry is a number, and the value will appear in the formula bar as 0.333333333333333. 

Excel stores numbers with 15-significant-figure accuracy. These are displayed in the formula bar and used in all calculations, no matter what number formatting has been applied. Thus the fraction 1/3 appears in the formula bar as 0.333333333333333, and n as 3.14159265358979. 

You can edit cell entries in one of two ways — either in the formula bar or by using the Edit Directly In Cell feature. When you select a cell that contains an entry, the contents of the cell appear in the formula bar. As soon as you begin to enter a new value, the old value disappears. To make minor editing changes in the old entry, place the mouse pointer in the text at the point where you want to edit the entry. The mouse pointer becomes the vertical insertion-point cursor. You can now edit the text in the formula bar using the Copy, Cut, Paste or Delete commands or keys. Complete the entry using the Enter button in the formula bar, or by pressing the Enter key on the keyboard. 

To use the Edit Directly In Cell feature, double-click on the cell. The text can now be edited in the cell in the same way as in the formula bar. 

You can format individual characters in a cell using Bold, Italic, Underlined, etc., or with different fonts, by highlighting the character(s) in the formula bar, then applying the formatting. 

You can also Copy or Cut text in the formula bar and Paste it in a worksheet cell. Select the text to be copied or cut, then press the Copy or Cut toolbutton or choose the appropriate command from the Edit menu. Complete the operation by clicking the Enter button in the formula bar. Then Paste in the desired cell. 

The best way to copy a formula to a different row and column without altering relative references is to Copy it from the formula bar, click the Enter box to complete the Copy operation, then Paste in the destination cell. 

Select a particular chart element by clicking once on it with the mouse pointer. The selected element will be indicated by the appearance of "handles" in addition, the name of the chart element will appear in the Name Box (the cell reference area of the formula bar). It s a good idea to look at the text in the Name Box when you choose a chart element, just to make sure that you ve selected the correct chart element. 

Excel Tip. Sometimes it s difficult to select a chart element by clicking on it (for example, if two chart elements are almost superimposed). Instead of selecting with the mouse pointer, you can use the up and down arrows on the keyboard to select chart elements. This allows you to select each chart element in turn (Chart, Plot, Axis, Series 1, etc.) the name of the selected chart element is displayed in the reference area of the formula bar. By using the left and right arrows, you can select related chart elements within a group (e.g.. Series 1 Point 1, Series 1 Point 2, etc.). 

Excel Tip. You can use F4 (Windows) or COMMAND+T (Macintosh) to toggle between cell reference types. Select the cell reference by double-clicking on it in the formula bar (or just put the insertion point cursor anywhere in the reference), then press F4 to cycle through the formats, in the sequence relative (e.g., A1), absolute (e.g., A 1), mixed (e.g.,A 1), mixed (e.g., A1). If you are typing a formula, you can use F4 after typing the cell reference Excel converts the reference to the immediate left of the insertion point. 

To edit a formula in a cell, you can simply select the cell and then edit the formula in the formula bar. 

If complicated formulas contain terms identical to those used in other cells, you can Copy that part of the formula and Paste it into the new formula. Here s one method before beginning to type the new formula, select the cell containing the formula you want to copy. In the formula bar, highlight the part of the formula you want to copy. Copy it, then click the Check Box in the formula bar. Now select the cell into which you want to type the new formula, type the new formula until you reach the part that you ve copied, then Paste in the formula fragment. 

Excel Tip. To select (highlight) a word or reference for editing in the formula bar, double-click on it. 

You can also define a name by using the Name box (the cell reference area in the formula bar). Simply select the cell or range on the sheet (the range will be displayed in the Name box), click the cursor in the Name box (the typing area will be highlighted), type the name, then press ENTER. (Excel does not propose a name based on a text label above or to the left of the selected range, as it does when you use Define Name or Create Names you have to type in the name yourself.)... 

A sheet-level name appears in the Define Name dialog box only when that sheet is the active sheet, with the sheetname shown on the right side of the list box, as in Figure 3-12. The name appears in the drop-down Name list box (in the formula bar) for that sheet, but does not appear when the drop-down list is selected in other sheets. 

As you ve already seen, dates and times can be entered into worksheet cells using any one of several convenient formats July 1 can be entered as 7-1, 7/1, July 1, Jul 1 or 1 July, among others. All these date entries produce the date 7/1/xxxx in the formula bar (xxxx is the four-digit year) and the displayed date 1-Jul. Excel enters the current year unless a different year is specified. If you enter the year, either as a two-digit or four-digit number. Excel displays the date in a different format, as e.g., 7/1/xx. 

Times are also recognized by Excel. If you enter 10 00 in a cell, it will be recognized as a time, and 10 00 00 AM will appear in the formula bar. Excel assumes a 24-hour clock (military time) unless you indicate differently. You can use AM/PM or am/pm designations with times. Even "2 p" can be used to enter 2 00 PM in a cell. 

Because Excel provides such a wide range of functions, it is sometime difficult to remember them, or to enter their arguments correctly. You can use Excel s Insert Function to paste a function in a cell, or within a formula that you re typing in the formula bar. To access Paste Function, press the button or choose Function... from the Insert menu to display the Paste Function dialog box (Figure 3-18). 

Finally, combine the formulas first. Copy the formula in cell D4 from the formula bar (don t include the equal sign) and press the Enter button then select cell E4 in the formula bar, select D4 in the formula and Paste the formula fragment. You can now delete the formula in cell D4. Repeat the process for the formulas in cells C4 and B4, pasting them into the formula in E4. The final megaformula is... 

Excel Tip. To view the current value of a variable or function in a statement in the formula bar, highlight it (e.g., by double-clicking) and press F9 (Windows) or COMMAND+=- (Macintosh). The value of the selected portion of the formula will be displayed. Click on the Cancel box in the formula bar or press Undo to restore the statement otherwise the selected portion of the formula will be permanently replaced by the numerical value. 

To edit an array formula, simply select any cell in the array. Then edit the formula in the formula bar. When you begin to edit, the braces surrounding the formula will disappear. To re-enter the edited formula, press CONTROL+SHIFT+ENTER. The formula will be entered into all of the cells originally selected for the array. 

To use a worksheet formula that returns an array result, you must first select a suitable range of cells, with dimensions (R x C) large enough to accommodate the returned array, then type the formula in the formula bar, and finally enter the formula by pressing CONTROL+SHIFT+ENTER. Excel will indicate that the formula is an array formula by enclosing it in braces and will enter the array formula in all the selected cells. 

Well begin the development of our formula by creating a formula to count the number of students whose year of graduation is 1996. Enter the formula =YOG in any worksheet cell. Only one value can be displayed in the cell, but if you highlight YOG in the formula bar and press the F9 key (Windows) or COMMAND+= (Macintosh), the array of values will be displayed (only the first portion of the array is shown) ... 

Don t forget to press the Cancel button in the formula bar or use Undo before continuing, to revert back to the original formula. 

EDITING THE SERIES FUNCTION IN THE FORMULA BAR (FOR EMBEDDED CHARTS OR SEPARATE CHART SHEETS)... 

First, switch to the chart sheet or activate an embedded chart by clicking on it. Click on the desired data series in the chart. The definition of the data series, in the format =SERIES(name, x-values ref, y-values ref, p ot ordex, for example =SERIES("Series2", Sheet4 A 1 A 11, Sheet4 C 1 C 11, 2) will appear in the formula bar. Edit the references for both X and Y values to include the additional data points. Sometimes this is the fastest way. 

In the formula bar, type = worksheet namefab otute CGtI reference ... 

Alternatively, you can have Excel supply the worksheet name and cell reference. In the formula bar, type Activate the worksheet by clicking on it or by selecting it from the Window menu. The external reference to the worksheet will appear in the formula bar. Select the desired cell in the worksheet. The absolute reference to the cell will appear in the formula bar. 

Click on the desired data series in the chart. The series formula wiU appear in the formula bar, e.g.,... 

Copy the Euler s method formula from the formula bar and Paste... 

The current iteration number is displayed in the formula bar. To terminate calculations, press COMMAND+(period) or ESC (Macintosh) or ESC (Windows). 

ENTER/ RETURN w Enter the contents of the formula bar in the selected range (references are adjusted) Enter the contents of the formula bar in the selected range (references are adjusted)... 

---