It’s more than a year since we ran our
series on Getting Started with Excel, and judging from the large number of
messages we received, it is obvious that many readers have started using
this excellent tool on an everyday basis. We are still receiving queries
from readers trying to get better performance out of Excel, and this
collection of tips is largely based on these queries. If you have missed
our Excel series, visit www.computersathome .com/apr99/excel_tutor.html.
Appearances do count
Is there any command in
Excel to automatically distinguish between cells containing formulas and
data?
Go to Tools-Options
and click on the View tab and put a check mark on the box for Formulas.
This way the spreadsheet will display all the formulas instead of their
results. Or you can apply conditional formatting if you have a
limited set of formulas that you are using. Go to Format-Conditional
Formatting and select ‘Formula is’ and enter the formula itself.
Specify a color and/or formatting to apply. You can add additional
formulas by clicking on the Add button.
How can I type a number
with leading zeros–such as 0001–into an Excel cell?
Format the cells (Select
Format-Cells-Number-Custom) with the following template: "000#".
Not only does Excel allow you to type the zeros, it also inserts the
appropriate number of zeros for you when you don’t type them.
Can you tell me a
keyboard shortcut to add a border? Also, when I delete the contents of a
cell, the border remains, how do I get rid of that?
To quickly add an outline
border to a cell or a range of cells, press Ctrl + Shift + &. Excel
will add a hairline-weight outline border to the cells. To remove all
borders from a cell or range of cells, press Ctrl + Shift + _ (the
underscore key).
Type it right the first
time
When I format a range of
cells to display the % sign, the decimals disappear and the numbers get
changed. What’s happening?
When you enter numbers that
you plan to format as percentages, enter them in the decimal format. For
example, to enter fifteen percent, type .15 because when you format a cell
as a percentage, Excel multiplies the value by 100. Therefore, .15,
formatted as a percentage, shows as 15% and 12.5 would become 1250%. But
this is applicable only if you type first and format later. If you
pre-format a column or row to show percentages, you don’t have to enter
in decimal format and can type in percentages directly.
When I type in functions
in the formula bar, Excel converts them to uppercase at times, and at
other times it doesn’t. Is there some logic behind this?
Get into the habit of
typing Excel functions in lowercase. If you type the function name
correctly, Excel automatically turns the lowercase letters into uppercase
letters. If you type the function name incorrectly, Excel leaves the
letters in lowercase, thereby tipping you off about your error.
We use Excel to maintain
details of sales invoices. As different people do the data entry at
different times, entries often get duplicated. How can we have an
automatic check for this?
This is an oft-needed, yet
simple trick that works for numbers, words and dates.
- Select the first cell in the range and
choose Data-Validation from the menu. Click the Settings tab. - In the Allow drop-down list, select
Custom. - In the Formula box, type =NOT(OR(COUNTIF
($A$1:$A$10,A1)>1)) - Now you’ll need to set an alert style.
Click the Error Alert tab. - Set Style to Stop (the alert message
with a Stop sign on it). - In the Title box, type what you want to
appear in the Error Alert’s title bar (say, "Duplicate
Data"); in the Error Message box, type the message box text (
e.g. "This entry already exists"). Click OK. - Use the square AutoFill handle to drag
and fill all cells that need to be validated.
Control your dragging
Whenever I try to drag a
range of cells to the bottom of the screen, Excel starts scrolling so fast
that I always go beyond the edge. Is there someway to slow it down?
Hold down the Alt key as
you drag. This way the Excel display doesn’t scroll as you drag. The
scrolling speed doesn’t change, but the screen remains static and you
easily position your range wherever you want.
Quick conversions
Is there some factor
that can be used for changing Fahrenheit to Centigrade and vice-versa?
Excel has a built-in
function, Convert that does all the common conversions for you, including
temperature. But you may not have installed it. Choose Tools-Add-ins.
Under Add-ins Available, make sure Analysis ToolPak is checked. To convert
a Fahrenheit temperature–such as 72 degrees–to Celsius:
- In any cell, type
=CONVERT(72,"F","C") - Press Enter. Excel returns the Celsius
temperature (in this case, 22.222).
To convert a Celsius temperature, such as
72, to Fahrenheit:
- 1. In any cell, type
=CONVERT(72,"C","F") - 2. Press Enter. Excel returns the
Fahrenheit temperature (in this case, a sweltering 161.6).
How can I get Excel to
help me only with the parameters when I type in formulas?
Excel has such a large
number of features that there’s an answer for practically every problem:
- 1. Begin entering your formula–that
is, type the name of the formula (such as "=SUM" or
"=AVERAGE"). - 2. Press Ctrl + Shift + A. The
formula’s arguments appear right in the cell. - 3. Complete the formula simply by
replacing the arguments. - 4. Press Enter when your formula is
complete.
Reclaim screen real
estate
I’ve several toolbars
open in Excel though I just use one or two buttons from each toolbar. All
the toolbars take up screen space that I could better use for viewing my
spreadsheet. Is there any work-around?
In Excel, you can customize
the toolbars by adding and moving buttons and you can create a single
toolbar with all the buttons that you use. You could also drag the buttons
that you commonly use to the end of the menu bar itself. To drag buttons
to the end of the menu bar:
- 1. Hold down the Ctrl and Alt keys.
- 2. Click and drag an oft-used button to
the end of your menu bar (right after Help).
Now, even if you remove all your toolbars
from the display, you still have access to your most important buttons.
Dr
Naval Kishore
has written a series of tutorials on Excel in C@H (see www.computersathome.com/pan/excel_tutor.html).
You can e-mail him at navalk@tendercare.com
Migrating
to Excel 2000
A change of scene, but the weather is the same
Just as you start getting comfortable with
a package, the software developer comes out with a new version and the
learning curve starts all over again. Fortunately, with Excel 2000, you
don’t really have to spend any time at all in learning it if you’ve
used Excel before. Actually, the new version isn’t a major upgrade at
all–the substance and functionality remain the same as before. Even the
file format is almost the same and except for losing some arcane links and
formulas, you can directly open an Excel 2000 spreadsheet in Excel 97. So
compatibility should not be an issue if you share your spreadsheets with
other users.
What’s new?
The major changes are in
the user-interface, which are in line with the upcoming Windows 2000.
Dynamic menus, customizable clip-art and more multi-lingual support are
common across the new Office 2000 applications. The file Open and Save
dialog boxes have become easier to use with a new Place Bar (similar to
what Outlook has had for quite a while), and the Office Assistant has
jumped out of its box to become a floating paper clip. Office 2000, like
Windows 2000 can install missing or corrupted files from the CD (or over
the network) on the fly as and when they are needed.
For the everyday user there’s hardly
anything new specific to Excel except for tighter Web integration and the
possibility to publish spreadsheets directly to the Internet if you use a
Microsoft developed platform. There are lots of new options for formatting
charts and making them look more attractive. For the power user, however,
Excel now supports Visual Basic 6. This is a major enhancement from the
previous version–software developers can now develop full-fledged
applications in Excel just as could be done with Access in the past.
And, as with every software upgrade, Excel
2000 is really space hungry and will gobble up all that extra space you
have in your new hard disk.
Should you upgrade? It isn’t really
necessary. But if you do decide to switch to Excel 2000, you don’t have
to spend much time learning it.