New Page 2
New Page 1
 |
|
Free Cell Phones
|
|
Take your pick from our
featured cellular phone deals by AT&T Wireless, T-Mobile, Cingular,
Sprint PCS, and Verizon!
|
|
|
 |
|
|
New Page 1
|
Microsoft
Excel Tips
|
Reformat your dates |
|
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] |
Make a
Copy That Excludes Hidden Cells |
|
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]
|
How
to Change Default Colors |
Excel uses
Windows colors for several of its elements. You can change these colors
for Excel by changing the Windows colors.
-
Click
Start, point to Settings, and then click Control Panel.
-
Double-click the Display icon in Control Panel, click the Appearance
tab, and click the element you want to change in the Item box.
-
You can
then click the color and font color you want for each item:
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.
Selected
Items:Sets the color of
the highlighting Excel uses to indicate which cells are selected. This
highlighting is a muted version of the color you specify.
ToolTip:Sets
the default background color for worksheet comments. The font color sets
the default color for comment text. You can also change the background and
font colors for individual comments. For more information about changing
comment colors, type format comments in the Office Assistant or on the
Answer Wizard tab in the Excel Help window, click Search, and then click
the topic "Change the appearance of a comment."
Window:Sets
the sheet background color. The font color sets the default color for data
you enter in cells, and the color of the text on the active sheet tab. You
can also change font and background colors for individual cells or an
entire sheet. For more information about changing colors of cells and
worksheets, type format worksheets in the Office Assistant or on the
Answer Wizard tab in the Excel Help window, and then click Search.
Changing
the gridline
color:
You can set this color in
Excel. On the Tools menu, click Options, click the View tab, and, under
Window options, click the color you want in the Color list.
[Top]
|
Set
High-Contrast Selection |
|
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]
|
Enter More Than One Line in a
Cell |
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]
|
Enter URLs As Text |
|
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]
|
Switch Rows of Cells to Columns or Columns to Rows |
|
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...
|
Dairy |
|
Meat |
|
Beverages |
|
Produce |
...into a row
of data.
|
Dairy |
Meat |
Beverages |
Produce |
Follow these
steps to transpose a row of data into a column or data, or vice versa:
-
Select the
cells that you want to switch.
-
Click
Copy on the Edit menu.
-
Select the
upper-left cell of the paste area. The paste area must be outside the
copy area.
-
On the
Edit menu, click Paste Special.
-
Select the
Transpose check box.
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]
|
Calculate the Amount of Time Between Two Dates |
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]
|
Create a Formula to Keep a Running Total |
|
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.
| |
A |
B |
|
1 |
100 |
100 |
|
2 |
200 |
300 |
|
3 |
300 |
600 |
|
4 |
400 |
1000 |
|
5 |
500 |
1500 |
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:
-
Enter the
formula=SUM($A$1:A1) into cell B1, as shown here.
| |
A |
B |
|
1 |
100 |
=SUM($A$1:A1) |
|
2 |
200 |
|
|
3 |
300 |
|
|
4 |
400 |
|
|
5 |
500 |
|
| |
A |
B |
|
1 |
100 |
=SUM($A$1:A1) |
|
2 |
200 |
|
|
3 |
300 |
|
|
4 |
400 |
|
|
5 |
500 |
|
-
Select
cells B1 through B10.
-
On the
Edit menu, click Fill Down.
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:
| |
A |
B |
|
1 |
100 |
=SUM($A$1:A1) |
|
2 |
200 |
=SUM($A$1:A2) |
|
3 |
300 |
=SUM($A$1:A3) |
|
4 |
400 |
=SUM($A$1:A4) |
|
5 |
500 |
=SUM($A$1:A5) |
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]
|
Finding Worksheet Functions
|
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.
-
On the
worksheet, select the cell you want to insert the function into.
-
On the
Insert menu, click Function.
-
If the
Office Assistant does not appear with a balloon asking if you want help,
click the Office Assistant button in the
lower-left corner of the Paste Function dialog
box.
-
In the
Assistant balloon, click Help with this feature
or Yes, please provide help.
-
In the
Assistant balloon, enter a brief description of what you want to do,
such as amortize a loan, and then click
Search.
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]
|
Forgot a Function's Arguments
while entering it? |
|
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]
|
Hide
Results When Formulas Calculate Error Values |
|
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:
-
Select the
cell or cells that contain formulas that may calculate error results
that you don't want to display.
-
On the
Format menu, click Conditional
Formatting.
-
In the
Conditional Formatting dialog box, click the
Condition 1 list and click
Formula Is.
-
In the box
to the right of the Condition 1 list, enter the following formula:
=ISERROR(cell_reference)
where cell_reference is the relative reference of
the active cell in the selection. In the example shown, the active cell
in the selection is D1. When you select a range of cells to
conditionally format, the formula must evaluate each cell in the range.
However, when you enter only the relative reference of the active cell
in the selection, Excel adjusts the references to the other cells
relative to the active cell.
-
Click the
Format button. In the Format
Cells dialog box, click the white color in the
Color list.
-
Click
OK in the Format Cells
dialog box, and then click OK in the
Conditional Formatting dialog box.
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]
|
Useful Shortcut Keys
|
|
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:
|
Activity |
Shortcut Keys |
|
Select
the current column |
CTRL+SPACEBAR |
|
Select
the current row |
SHIFT+SPACEBAR |
|
Move to
the beginning of the worksheet |
CTRL+HOME |
|
Move to
the last cell on the worksheet, which is the cell at the
intersection of the rightmost used column and the bottommost used
row (in the lower-right corner), or the cell opposite the home cell,
which is typically A1 |
CTRL+END |
|
Paste a
function into a formula |
SHIFT+F3 |
|
When
you enter a formula, display the Formula Palette
after you type a function name |
CTRL+A |
|
Select
all (when you are not entering or editing a formula) |
CTRL+A |
|
Alternate between displaying cell values and displaying cell
formulas |
CTRL+`
(single left quotation mark) |
|
Calculate all sheets in all open workbooks |
F9 |
|
Calculate the active worksheet |
SHIFT+F9 |
|
Create
a chart that uses the current range |
F11 or
ALT+F1 |
|
Enter
the date |
CTRL+;
(semicolon) |
|
Enter
the time |
CTRL+:
(colon) |
|
Fill
the selected cell range with the current entry |
CTRL+ENTER |
|
Display
the Go To dialog box |
F5 |
|
Display
the Format Cells
dialog box |
CTRL+1 |
|
Copy |
CTRL+C |
|
Paste |
CTRL+V |
|
Undo |
CTRL+Z |
|
Save |
CTRL+S |
|
Print |
CTRL+P |
|
Open |
CTRL+O |
[Top]
|
Make Your Printouts Fit the
Page Width |
|
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]
|
Use comments and reminders |
|
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]
|
Fast
file switching |
|
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]
|
Create
a chart |
-
Select the
data you want to plot [in columns, preferably].
Include their titles [ie: temperature, viscosity] if you want them
to show up in the legend.
-
Make sure
your columns from Left to Right are your X and Y coordinates,
respectively.
-
INSERT>chart
or click on
Chart Wizard.
-
Choose a
chart type. Usually X-Y Scatter. Then choose a specific type of ie: X-Y
Scatter.
-
Select
radio button for data in rows or columns, whichever corresponds to yours
[usually columns].
-
TITLES
tab:
give your chart a title, X-axis label, Y-axis label.
-
GRIDLINES
tab:
varies the gridlines on your chart.
-
LEGEND
tab:
lets you not show the legend, or change its
position on the graph.
-
DATA LABELS
tab:
lets you label the data points [with %values, etc.].
-
Select
where you want this graph to go: on it's own sheet or embedded in the
spreadsheet
[Top]
|
A
quick way to round numbers |
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]
|
Change Excel's default number of worksheets |
|
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] |
Freezing Titles in Excel |
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] |
Create an Excel List |
-
Highlight
the range of data that you want make into a list.
Note: From the Create List dialog box, you can select a range of cells
to be specified as a list.
-
On the Data
menu, select List, then select Create List.
-
If the
selected data has headers, place a checkmark in the 'My list has
headers' check box, then click OK.
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.
Note: If the List toolbar is not visible, then go
to View menu>> Toolbars>> List
Once the list is created, it will now be shown in a blue border. Also,
AutoFilter drop-downs will be enabled automatically for each column in the
list and the insert row will be added as the last row of the list. By
clicking Toggle Total Row from the List toolbar, a total row will be
displayed under the insert row.
The list becomes inactive when you select a row, cell, or column outside
of the list. An inactive list is shown blue border and does not display
the insert row or AutoFilter drop-downs.
Note: If the Hide Border is clicked (List
menu>>Inactive Lists>>Hide Border), the border will not be displayed.
[Top] |
Finding An Excel File Location: |
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:
-
Open Excel
-
Go to
File>> Page Setup
-
In the Page
Setup window, select the Header/Footer tab
-
Select a
location where you would like Excel to print the header or footer
information.
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.
However, if you would like to use a Custom Footer or Header, here's how:
-
Click
either Custom Header or Custom Footer
-
Once in the
Header or Footer window, click in to one of the following labeled
sections:
-
Once you've
made your selection, click the Path and File button towards the center
of the menu (the icon that looks like a folder).
-
A command
such as: &[Path]&[File] will now appear
-
Verify that
you've successfully added this information by selecting Print Preview
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] |
View a clean worksheet |
If you are
tired of seeing the Excel gridlines and would like to view a cleaner
looking worksheet, here's how:
-
Go to
Tools>> Options
-
Once in the
Options windows, select the View tab
-
Uncheck the
Gridlines box in the Window Options section
-
Click OK
Note: The new
work created will not be affected by this change.
[Top] |
|
