|
Get Rid of Those Pound Signs
If you enter a formula and receive a plethora of pound signs (#), things aren't as bad as
you might think. This return simply indicates that the calculation for your formula is too
large for the column. Drag the column to a larger width until your numerical value
returns.
Squint Less by Zooming In
Diminish eyestrain by magnifying your spreadsheet display. From the Zoom box, select the
size you want or enter a number from 10 to 400. The higher the number you choose is, the
bigger your display will become. Adjusting the magnification will make the display easier
to read, but will not affect the size in which the spreadsheet is printed. If you wish to
change the actual print size, adjust the scaling by selecting File/Page Setup and
accessing the Page tab in the pop-up window.
Use Trendlines to Forecast With Your Data
Trendlines help you forecast a trend using the data you've already collected. You can
create a trendline from the data you already have and extend it forward or backward to
forecast a range of numbers (hours, sales, or quantities) over a defined period of time.
Here's how.
1. Select the range of cells in your table that you want included in the forecast.
2. Click the Chart Wizard icon. Leave all the options as they are and click Finish to
create a default column chart.
3. Right-click any data column in the chart and select Add Trendline. Click Linear, then
select the Options tab.
4. On the Options tab under Forecast, click the up arrow until it shows the range of
periods you want included. For example, if your chart is set up for the periods to equal
quarters and you want to forecast for the next six quarters, you would click Forward six
Periods. Click OK.
The trendline will appear on the chart.
Using these basic instructions, you can experiment with other types of trendlines to
determine which yields the most effective forecast.
Create Exponential Values
There's more than one way to conjure up numerical
powers. To create exponential values in Microsoft Excel,
you can use a caret before the power you wish to use.
For example, four to the second power (four squared)
would be denoted by 4^2. You can also create
exponential figures with the POWER value, with the
primary number and its power listed as an ordered pair.
In this method, you would denote four to the second
power simply with the formula line =POWER(4, 2).
Use the Excel Sample Files
Excel 2000 includes a handy sample file, samples.xls,
that contains information about worksheet functions,
macros, conditional formatting, and other useful options.
It also contains demonstrations that you can run to test
the options for yourself. You'll find the samples directory
file in your Microsoft Office directory.
Change the Cursor's Default Direction
By default, Excel moves your cursor down one cell
whenever you press the Enter key. To change the
direction the cursor moves when the Enter key is
pressed, select Options from the Tools menu. Click the
Edit tab and select the direction you want the cursor to
move from the pull-down menu labled Move Selection
after Enter. Finally, click OK to save your settings.
Microsoft Excel Tips Newsletter
Center Your Worksheet
Position your worksheet front and center on the printed page. To have Excel
print your spreadsheet in the center of the page, select Page Setup from the File menu.
Click the
Margins tab and check the boxes labeled "Horizontally" and
"Vertically," which are located under the "Center on Page" heading.
Click OK to save the settings and print your spreadsheet with a centered look.
Clean Up Extra Spaces
Eliminate extra spaces from imported text in Excel with the Trim function. To
use, simply enter the following formula ("cell" is the cell with the extra
spaces you wish to eliminate):
=TRIM(cell)
The cell in which you entered this formula will display the targeted cell's data without
extra spac
Color Your Cells
Excel 2000
Color your cells any way you want with shading. First, select the cells you want to color.
Once they are highlighted, click the arrow next to the Fill Color icon and choose the
color you wish the selected cells to be. The highlighted cells will fill with your
favorite color.
Conditional Formatting
Excel 97
Color-coding your data lets you tell at a glance whether your numbers are up or down.
Using Excel's conditional formatting features, you can set up your sheet so that cells
change colors depending on what their values are--a great way to make your cells easy to
read.
1. Select the cells that you want to color-code. To select noncontiguous cells, hold down
the Ctrl key as you click.
2. Select Format/Conditional Formatting. In the Conditional Formatting dialog box, you'll
set the conditions for the cells. In the first field on the left, decide whether you want
the condition based on the value of a cell or formula. In the second field, select which
conditional terms apply. In the third field, fill in the value for the cell, or use the
third and fourth to fill in a range. For example, if you want to create a conditional
format for a Total cell, you can select Cell Value Is for the first field, Greater than or
equal to for the second, and enter
1,000 in the third field as the value.
3. In the same dialog box, click Format to set the cell's appearance when the condition is
met. You can set options related to font, border, and pattern. If you just want to set the
color, choose the Patterns tab and select a color for the cell. For our example, you might
select green, so that the Total cell will glow green when it hits the 1,000 mark.
4. Click OK to complete the first condition.
5. If you want to add another condition to the cell, click Add and repeat the steps to
fill in Condition 2. Otherwise, click OK to put the conditional format in place.
Count the Days
Excel 2000
Measure time by counting the days with Microsoft Excel. To do so, simply use the DAYS360
function with the two dates you want to span enclosed in quotes and separated by a comma
within parentheses. For instance, if you wish to count the days between Christmas of 1997
and Independence Day of 1998, simply enter the formula =DAYS360("12/25/97",
"7/1/98") to retrieve your date count. Keep in mind that the order in which you
place these dates within the parentheses controls the direction in which the days are
counted. That is, if your second date value occurred before the first date value, your
result will be a negative number.
Digitally Sign Your Macros
Excel 2000 supports digital signing of macros so you can set them to run without a warning
appearing. To create a digital certificate, you must have the Digital Signature for VBA
Projects option installed (install it using the Custom Install option). Use Windows
Explorer to locate the file selfcert.exe, and run it to set up your certificate. Apply the
certificate by opening a file containing a macro and selecting Tools/Macro/Visual Basic
Editor. In the Project Explorer, select the module containing the project to
"sign" and select Tools/Digital Signatures. Select your certificate and click
OK. When you next open the file, the Security Warning dialog will
appear; check the "Always trust macros from this source" box and click Enable
Macros. Now any macros you sign with your digital signature will run on your computer
without warning.
Drag and Drop Web Data
Excel 2000
Excel 2000 lets you select and drag data from a table on a Web page and drop it with its
formatting intact into any Excel worksheet. Open the Web page in IE 5 and highlight the
table. Drag the data off the browser and drop it onto your open workbook, or hold your
mouse over the Excel icon on the Taskbar, wait while the Excel window appears, and drop
the table in position on your worksheet.
Enter More Than One Line of Data in a Cell
Microsoft Excel 2000
Excel provides two ways for you to display multiple lines of data in a cell. Press
Alt-Enter to start a new line while you're typing or editing data. Or you can select the
cell, go to Format/Cells, 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.
Fractions or Decimals?
Excel 2000
If Excel's practice of reporting decimals as fractions out of 100 fails to satisfy you,
raise or lower its accuracy standard. You can have Excel convert decimals to numbers with
a specific number of denominator numerals. To do so, click the cell(s) for which you want
a more detailed decimal and select
the Cells option from the Format pull-down menu. Select the Fraction option from the
Numbers tab. In the Code text box, enter a pound sign (#) followed by a space and the
question marks representing the accuracy of fraction you desire. For instance, if you
wanted your decimal to be converted to a three-digit fraction, you would enter # ???/???
in the Code text box. Make sure the question marks representing the numerator are equal in
number to those representing the denominator.
Function Efficiently with Excel's Formula Palette
The Formula Palette is a great tool for building formulas with functions you may not be
familiar with. To access the Formula Palette, just click the equal sign (=) in the Formula
Bar and select the function you need to work with from the drop-down list to the left of
it. You can then use it to define the arguments for the function. However, one drawback to
the Formula Palette is that it may obscure the range of cells you're working
with--particularly if the function has many arguments. Fortunately, the palette isn't
locked in place.
Simply drag it with your mouse to a section of your worksheet that's out of the way of
your data.
Hide Excel Values with a Null Format
Sometimes you may want to hide values in individual cells. You can do this by assigning a
null number format to the cells. To do so, select the appropriate cells. Then, choose
Format/Cells from the menu bar. Select Custom from the Category list box. Then, in the
Type text box, enter three semicolons (;;;). Finally, click OK.
Keep an Eye on Excel Ranges with a Custom Zoom
You'll often want to zoom out the view of your worksheet to get a better look at your
spreadsheet's layout. Unfortunately, your bird's-eye view of the data can make keeping
track of the information an optometrist's nightmare--even at just a 50 percent zoom.
Ironically, you might be able to prevent some eyestrain by zooming out even more. To do
so, you'll first need to use named ranges in your worksheet. Also, you must
be in Normal view, not Page Break Preview. When you're ready to zoom out your view, select
View/Zoom from the menu bar. Select the Custom option on the Zoom dialog box, enter 39 in
the percentage text box, and then click OK. The range names appear in faint blue lettering
in the appropriate places. Note that the range names only appear on the screen; they won't
print with your data.
Lay Out Text Smoothly
Excel 97
Merge cells in order to display blocks of text in a spreadsheet smoothly.
1. Select the group of cells you'd like to merge, and right-click to bring up the Shortcut
menu.
2. Choose Format Cells.
3. From the Alignment menu, check the Wrap Text and Merge Cells boxes. Click OK.
Make Ideal Indentations
Excel's indent feature opens many formatting options, including outline-style multiple
indentation, allowing you to indent as many as 16 levels. To indent a range of cells,
select the cells and click the Increase Indent button on the Formatting toolbar. To indent
one cell in the range more than the others, forming a hanging indent, select that cell and
click the Increase Indent button again.
Open Specific Spreadsheets at Start-Up
Excel 2000
Instantly access a frequently used spreadsheet each time you open Excel. To do so, select
Save As from the File menu while in the desired spreadsheet. When the Save As dialog box
appears, locate the Xlstart folder, located in the Office directory. Click the Save button
to save the file to this folder. The file you have selected will open automatically each
time you start Excel.
Print Selectively
If you want to print part of your worksheet rather than the whole thing, select the
portion you want to print and select File/Print. In the Print What area of the dialog,
choose the Selection option. Click Print to print the selection, or click Preview to view
it.
Resize Cell Contents With Ease
Excel 2000
Resize cell contents without constantly dragging column and row boundaries. To do so, use
Excel's Shrink to Fit feature. Select the cells you wish to resize and then select Cells
from the Format menu. Click the Alignment tab and check the box labeled "Shrink to
Fit." Click OK to automatically resize all rows and columns to functional dimensions.
Keep in mind that you might encounter difficulty using this option if you are using the
Wrap Text, Justify, or Fill options.
Rotate Column Headers
Excel 97
Column headers never seem to fit the way you want them to, but you can solve this problem
using Excel 97's new Rotate Text tool.
1. Select the cells containing the column headers.
2. Right-click the selection, then choose Format Cells from the shortcut menu.
3. Click the Alignment tab, and choose 45 degrees in the Orientation box.
4. Click OK to apply your settings.
See Excel's Page Breaks (
Excel 2000)
Excel 2000's page break preview shows you exactly where a large worksheet will break into
different pages when printed. To see this, pull down the View menu, and select Page Break
Preview. You'll see the pages marked on your worksheet with blue borders and the page
numbers in gray. You can move the page breaks by dragging them with your mouse. Choose
View/Normal to return to the standard editing screen.
Share Files With Older Excel Versions (
Excel 2000)
If you're working with people using older versions such as Excel 5.0 or 95, you can share
Excel 2000 files with them without losing your formatting by saving them in dual file
format. In the File menu, choose Save As. In the list labeled Save as type, select
Microsoft Excel 97-2000 & 5.0/95 Workbook (*.xls). Note that you’ll lose any
options not supported by 5.0 or 95 if you alter and save the file in an earlier version.
Sharing Custom Number Formats
When you create a custom format, those formats are only available in the workbook in which
they were created. There are several ways you can make a custom format available to other
workbooks.
In the workbook that contains the custom formats (the source), you can copy a value that
has the custom format applied to it to another workbook (the destination). The custom
format is added to the destination workbook's list of custom formats. This is one of the
easiest ways to share a custom format with an existing workbook. Another option is to
create a style for the custom formats and then share the style with another workbook. The
disadvantage to this approach is that every time you want to use the custom formats in a
new workbook, you have to open a file that contains the formats (the source workbook) and
copy the styles to the destination workbook. This is inconvenient to say the least.
Likewise, the first time you store a macro or VBA procedure in the Personal.xls file, the
file is created and opened each time you open Excel. You could conceivably open the
Personal.xls file and create the custom number formats there--thus making them available
to all workbooks you open. The difficulty here is that in order for the Personal.xls file
to exist, you have to create and store a macro/VBA procedure in it.
Another alternative is to use Autotemplates. What is an autotemplate? Glad you asked! When
you create a new workbook or insert a new worksheet, the settings for the book or sheet
come from the autotemplates. So if you want to adjust some of Excel's settings (like the
default print header or custom number formats), you can do so in the autotemplates. Every
time you create a new book or insert a new sheet, the custom settings you defined are
there. In the case of custom numbers, since they are associated with the book and not
independently customized per sheet, you only have to create a book autotemplate.
To create a book autotemplate for custom number formats, start a new workbook and create
the custom formats. Then save the book as BOOK.XLS in the XLSTART folder. In Windows 98,
this folder is located at C:\Program Files\Microsoft Office\Office\XLStart. The drawback
to this option is that it only shares the formats with new workbooks. You will want to use
the copy/paste method to share custom formats with existing workbooks.
Use AutoFill for Autoformatting
Excel 97
Excel's AutoFill option makes it easy to add series of numbers--dates, month names, and so
on--into a worksheet. AutoFill copies formatting as well as the text, which also makes it
a great option for one-step column headings. For instant months of the year, for example,
type January in a cell, and format it as you like. Then click in the cell, hold your mouse
pointer over the cross in the lower right corner, drag it
horizontally until the ToolTip says "December," then release your mouse button.
Use Comments and Reminders
-
Excel 2000
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.
Use Plain English for Formulas
- Excel 97
Want to create a formula but avoid tinkering with cell references? In Excel 97, you can
use regular words and let Excel figure out the formula.
1. Revenues and Cost. To figure out profit or loss, type the word-based formula
cost-revenues in a new cell, and hit Enter.
2. Double-click the cell with the formula to bring up Excel's Range Finder, which makes
it simple to see where your cell references are coming from. The natural-language formulas
are "smart," so you can change column and row headings and still maintain the
correct cell references.
3. To copy this formula down the entire column, use the mouse to grab the AutoFill
handle in the formula
cell, and drag it down to fill all the cells next to data columns.
Using the End Key
- Excel 2000
The End key doesn't move you to the end of a horizontal line or row in Excel as it might
in other programs. Because Excel rows extend endlessly, the End key needs a lit tle
direction. When you press the End key and follow the keystroke with the right arrow key,
your cursor will move to the last cell that contains data in the row. Similarly, following
the End keystroke with the left, up, or down arrow key will move you in the corresponding
direction of the cell most extremely positioned containing data.
When Two Axes are Better Than One
- Excel 97
It's impossible to successfully plot large and small values such as sales figures and
percentages along the same axis. You just can't see the small values. However, you can
resolve this problem by using a second axis. Highlight one element in the small value
series, right-click, and select Format Data Series from the pop-up menu. Click the Axis
tab, then the Secondary Axis option button, and click OK. To change the way this series is
plotted, highlight one element in the series, right-click it, select Chart Type, select a
different chart type, and click OK. If you find it hard to select an element in the small
value series, select any series on the chart and use the left or right arrow keys to move
until the correct data series is selected.
Working With Decimals
- Excel 2000
Customize your decimal placement with Excel. To do so, select Options from the Tools menu
and click the Edit tab. Check the box labeled "Fixed Decimal." Next, click the
arrows alongside the Places box to specify where you want the decimal point to appear.
Click OK to enforce automatic decimal placement.
Automate the Month
Quickly enter the months of the year across a row or down a column by typing Jan or
January into a cell. Click the cell and position your mouse pointer over the small box in
its bottom right corner; the mouse pointer will change to a small cross shape. Click
and drag to the right or down the worksheet, and you'll see a small tip box appear,
displaying the month
that will be entered into the cell you have selected. Continue moving until
"December" (or the last month you want to include) appears, and let go of
the mouse. The names of the months will appear in the cells automatically. You can also do
this with days of the week.
For more info, please fill the info below.
| 
