|
|
|
|
|||
|
|
. | ||
| Excel
2000 has some new four-digit date formats. To reformat your dates, go to the
Format menu, select Cells, click the Number tab, and select Date from the
Category list. Scroll down the Type list to view the available four-digit
formats. You can also format dates on the fly if you enter your data using
the syntax m/d/yyyy. [Top] |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| When you use outlining or subtotals to create a small summary of a large set of data, you can make a copy of just the displayed cells in the summary, excluding the detail. First display only the summary rows or columns, and then select all of the summary data. On the Edit menu, click Go To, click Special, and then click Visible cells only. Now click the Copy button, click a cell on a blank worksheet, and click the Paste button. The copy includes only the summary data. For more information about subtotals and outlines, type inserting subtotals and creating outlines in the Office Assistant or on the Answer Wizard tab in the Excel Help window. [Top] |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Excel uses Windows colors for several of its elements. You can change these colors for Excel by changing the Windows colors.
3D Objects:Sets the background color of the Excel row numbers and column letters, and also the inactive sheet tabs. The font color sets the color of the row numbers and column letters, and the text on inactive sheet tabs. [Top] |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| When you select cells, do you have trouble seeing what's selected? If you need a higher-contrast selection display to accommodate low vision, you can set Excel 97-style selection shading. Use the Registry Editor to add a registry subkey HKEY_CURRENT_USER\Software\Microsoft\Office\9.0\Excel\Options\Options6 as a DWORD value, and set this subkey equal to 16 decimal. [Top] |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Excel provides two ways for you to display multiple lines of data in a cell. Type a line break: Press ALT+ENTER to start a new line while you're typing or editing data. Wrap text automatically: Click the cell, click Cells on the Format menu, click the Alignment tab, and then select the Wrap text check box. Data in the cell will then wrap to fit the column width. You can make the column wider or narrower to adjust the width of the data. For more information, type change column width in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search. [Top] |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| When you type an Internet address in a cell, such as www.example.microsoft.com, Excel automatically turns the address into a hyperlink. To store the address as regular text instead, type an apostrophe (') before the address. For instance, if you type 'www.example.microsoft.com, you'll see the address text in the cell, without the apostrophe, and the text won't be a hyperlink. If Excel has already turned an address into a hyperlink, you can deactivate it: Right-click the cell, point to Hyperlink on the shortcut menu, and then click Remove Hyperlink. [Top] |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Have you ever had a column of text that you wanted to move into a row so that you could use the text as column labels in a table? Or, have you ever had a row of data that you wanted to move into a table in which the data is organized in columns? It would be tedious to move the data manually, one item at a time. Instead, you can use the Paste Special command to transpose a column of data to a row of data, and vice versa. Transpose a column of data...
...into a row of data.
Follow these steps to transpose a row of data into a column or data, or vice versa:
Data from the top row of the copy area appears in the left column of the paste area, and data from the left column appears in the top row. For more information on options in the Paste Special dialog box, click the ? in the upper-right corner of the dialog box, and then click the option you want to learn more about. [Top] |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
To calculate the number of days between two dates, you can simply subtract the two dates. For example, if cell A1 contains the date 6/8/2000 and A2 contains 6/20/2000, the formula =A2-A1 calculates the number of days between these dates (12). Be sure to use number format for the cell where you enter this formula (click Cells on the Format menu, click the Number tab, and then click Number under Category). If you don't format the cell with number format, Excel assumes the result has the same format as the cells used in the calculation, and displays the result as a date instead of a number. [Top] |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
In Microsoft Excel, you can calculate a running (or cumulative) total in a column or row of cells by using a combination of absolute and relative references in a formula that uses the SUM function.
For example, to keep a running total of cells A1 through A5 in column B (for example, B1 contains the value from A1, B2 contains A1+A2, B3 contains A1+A2+A3, etc.), set up the worksheet as follows:
The $A$1 (absolute reference) will be constant in each cell, while the A1 (relative reference) will be updated in each successive cell to refer to the adjacent cell in column A, as shown here:
For more information on using absolute and relative references in Excel, type cell and range references in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search. For more information on the SUM function, type SUM worksheet function in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search. [Top] |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Can't remember the name of that Excel worksheet function you used last
year to determine your mortgage payments? You can use the Paste
Function dialog box and the Office Assistant to help you locate the
function you want.
For a list of functions to help you begin your search, see the Recommended category in the Function Category list in the Paste Function dialog box. [Top] |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Press CTRL+A on your keyboard, and the formula palette is displayed to show you a description of the function, a description of each of the arguments for the function, and even the result of your calculation. [Top] |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
You may not want the results of a formula to be displayed in your worksheet when the formula calculation results in an error value. In Excel, you can hide error values by using conditional formatting or conditional formulas. In the following example, the error value #DIV/0! is the result of a formula in cell D1 that tries to divide the value in cell C1 by the value in cell B1, which is empty:
You could delete the formula from the cell, of course. But, you want to keep it so that it calculates and displays valid results when a value is entered in B1. You can do this with conditional formatting in Excel 2000. Just follow these steps:
In versions of Excel prior to Excel 97, you could not create a custom number format to hide error values returned to the cell by the cell formula. However, you can change the formula itself to automatically hide error results by using the ISERROR function in a conditional formula. So, instead of =C1/B1 as in the example shown, you would use the formula: =IF(ISERROR(C1/B1),"",C1/B1) which returns an empty string ("") to the cell if an error value is calculated. [Top] |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Do you find that using the keyboard is sometimes quicker than using your
mouse? Shortcut keys can help you bypass menus and carry out commands
directly. You can use shortcut keys in many ways with Excel, from
accessing commands and toolbar buttons to outlining and editing
information. Shortcut keys are sometimes listed next to the command name
on menus. For example, on the Edit menu, the Copy command
shows the shortcut CTRL+C.
For a comprehensive list of shortcuts, ask the Office Assistant for help. In Excel 2000 or any of the other Office 2000 applications, press F1 to display the Assistant, and then type shortcut keys in the text box. Here are some of the most useful Excel shortcut keys:
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
When you print a worksheet, do you want it to fit the width of the
paper, and take as many sheets of paper as required to print all the data?
When you click Page Setup on the File menu, click the Page
tab under Scaling, click Fit to, and select 1 page wide.
In the second Fit to box for how tall you want the data, delete the
number so the box is blank. [Top] |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Add comments and reminders to individual
cells in Excel. Click the cell where you want to put a comment and select
Comment from the Insert menu. Enter your comment and click outside the
comment box when finished. Cells with comments are denoted by a small, red
triangle located in the upper right-hand corner of the cell. To read a
cell's comment, move the mouse over the cell. To keep a comment visible,
right-click the cell and select Show Comment from the pop-up menu. To hide
the comment, right-click the cell and select Hide Comment from the pop-up
menu. [Top] |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Excel supports quick file switching by giving
each open worksheet its own icon on the Windows Taskbar. You can switch from
one worksheet to the next by selecting its button on the Taskbar, or, as in
the past, by selecting Ctrl-F6. You can turn this option on and off by
pulling down the Tools menu, choosing Options/View, and selecting or
deselecting the "Windows in Taskbar" check box. [Top] |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Often, you'll store numbers that have several
decimal places in cells are formatted to display only one or two decimal
places. In these instances, Excel rounds the displayed result. This is
usually fine, but you may find that calculations based on the values appear
to be off because formulas use the real values, not what you see. Here's a
quick and easy way to force Excel to use the values as they appear in the
worksheet--but use it with caution. The technique involves using Excel's
Precision As Displayed feature, which affects all numbers in the workbook.
The conversion is one-way and you won't be able to retrieve the original
number value, even if you later disable the Precision As Displayed feature.
Depending on how you're using the data, permanently changing the underlying values can have serious implications, so be absolutely sure you understand the impact that altering the data will have on your application. To use the Precision As Displayed feature, choose Tools | Options from the menu bar. Then, click on the Calculation tab, select the Precision As Displayed check box, and click OK. Excel then displays a warning that the change is permanent. Simply click OK to commit the change. Note that this setting change applies only to the currently active workbook. [Top] |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
By default, Excel creates three
worksheets in every new workbook. If you find that you routinely don't use
all three or that you require more, change the default number that Excel
creates. To do so, select Tools/Options from the menu bar. Then, click on
the General tab and change the number in the Sheets In New Workbook spinner
box to the number of desired sheets. [Top] |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
When a worksheet has become too large, you may notice rows/columns that contain
headings do not fit on your screen as you scroll across the page. You can use
Excel's Freezing Titles option to keep these row or column titles on the screen
no matter where you scroll in the spreadsheet. Freezing the column titles in
their place gives you the flexibility to see additional information. Here's how: Click below the column heading or click to the right of the row title to be frozen, then go to Window>> Freeze Panes.
Go to Window>> Unfreeze Panes to turn this feature off. [Top] |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
The selected range of data is highlighted by the list indicator, and on the List
toolbar, the most common list related functionality is made available. [Top] |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
If you are like me, you have spent
plenty of time trying to locate a particular file that needs to be changed or
updated but can't remember the file name or location that you gave it. In this tip, I will share with you a quick way I have found to make Excel work better for you. Here's how:
Note: You will notice that Excel
supplies preset headers and footers contained within the drop down lists. If you
find a choice that meets your needs then click it and then click OK.
Note: If the File name is all that you really want added, then delete "&Path". Or, if the path is all that is needed, delete "&File". [Top] |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
If you are tired of seeing the
Excel gridlines and would like to view a cleaner looking worksheet, here's how:
Note: The new work created will not be affected by this change. [Top] |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
