Learn Excel

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.

1. Select the first cell in the range and
   
   choose Data-Validation from the menu. Click the Settings tab.
2. In the Allow drop-down list, select
   
   Custom.
3. In the Formula box, type =NOT(OR(COUNTIF
   
   ($A$1:$A$10,A1)>1))
4. Now you’ll need to set an alert style.
   
   Click the Error Alert tab.
5. Set Style to Stop (the alert message
   
   with a Stop sign on it).
6. 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.
7. 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:

1. In any cell, type
   
   
   
   =CONVERT(72,"F","C")
2. Press Enter. Excel returns the Celsius
   
   temperature (in this case, 22.222).

To convert a Celsius temperature, such as

72, to Fahrenheit:

1. 1. In any cell, type
   
   
   
   =CONVERT(72,"C","F")
2. 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. 1. Begin entering your formula–that
   
   is, type the name of the formula (such as "=SUM" or
   
   "=AVERAGE").
2. 2. Press Ctrl + Shift + A. The
   
   formula’s arguments appear right in the cell.
3. 3. Complete the formula simply by
   
   replacing the arguments.
4. 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. 1. Hold down the Ctrl and Alt keys.
2. 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.