 Activating the Formula Bar With the Keyboard
Summary: Sometimes it is Useful to Activate the Formula Bar From the Keyboard. Here is how to do this:
The Formula bar, which is located just above the work area in Excel, shows the formula or value in the currently selected cell. You can click on the formula bar to edit information within a cell. What happens if you want to activate the Formula bar by using the keyboard?
The easiest way to do this is simply to press the F2 function key. By doing this you are telling Excel that you want to edit the contents of a cell. When you press F2, it is quite likely that Excel will simply expand the information in the cell and allow you to edit the information directly in the cell itself. If you don't like this behavior, but instead want F2 to activate the Formula bar, follow these steps:
- Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 - 2016 display the File tab of the ribbon and then click Options.)
- Click the Advanced option at the left of the dialog box.
- Make sure the Allow Editing Directly In Cell check box is cleared.
- Click on OK.
Now, whenever you press F2, the Formula bar is selected and you can edit the formula there.
 Finding and Replacing Error Values
Summary: In a large complex worksheet with hundreds of rows of calculated values, it is almost guaranteed that there will be a number of cells with #N/A error that you would like to be "0" (or some other value), so that the cells can be used in other formulas. Here is how to do this:
We will use the GoTo feature in Excel. Simply follow these steps:
- Press F5. Excel displays the Go To dialog box
- Click Special. Excel displays the Go To Special dialog box
- Make sure the Formulas radio button is selected
- The only check box that should be selected under Formulas is Errors
- Click OK. Excel selects all cells where the formula returned an error value
- Type 0 or whatever value you want.
- Press Ctrl+Enter.
Note that this approach results in any error values being replaced, not just those with the #N/A error.
Entering Dates in Excel
Dates are a special case in Excel. If you enter information that can be translated as a date (by any stretch of the imagination), then Excel treats it as a date. It converts your data into a serial number that is internally used to represent dates and times. For instance, any of the following entries will be translated to a date by Excel:
- 10/14
- 10-14/11
- 10 Dec
- December 10, 2016
If you enter the first example, Excel will convert it to a date and display that date as best it can determine. If you leave off the year in your entry, Excel assumes you mean the current year. You could also use dashes instead of slashes and Excel will still figure out you are entering a date.
Regardless of how you type a date, it is converted to a special serial number by Excel and stored internally in that format. How you see the date on your screen is a result of how the cell is formatted. Even though Excel stores dates in a standard format internally, they can be displayed using any number of different formats.
|
Deleting Tab Stops
Summary: To clear individual tabs in a paragraph, follow these steps:
- Make sure the insertion point is in the paragraph whose tabs you wish to clear.
- Display the Tabs dialog box. (Display the Home tab of the ribbon)
- Click the small icon at the bottom-right of the Paragraph group
- Then click Tabs in the tab list below the Tab Stop Position box and select the tab stop you want to clear.
- Click on Clear.
- Repeat steps 3 and 4 for each tab stop you want to clear.
- Click on OK.
If you have the ruler displayed on-screen, then you can also delete tabs by following these steps:
- Make sure the insertion point is in the paragraph whose tabs you wish to clear.
- Use the horizontal scroll bar to scroll left or right until you can see the offending tab on the ruler.
- On the ruler, click on the tab indicator and drag it off the ruler. When you release the mouse button, the tab should disappear from the ruler.
- Repeat steps 2 and 3 for each tab stop you wish to clear.
|
