[PDF]50 Essential Excel Tips

\\ Urdu Soft Books


50 Essential Microsoft Excel tips


50 essential Excel tips

Your users can save a lot of time and effort working with Excel if they know a few tricks and shortcuts.
These valuable tips originally appeared in our Excel e-newsletter, which has now been beefed up to


include productivity tips for the entire Microsoft Office suite.

Table of contents

Easy data-posting to the Web 3

Open Web pages right in Excel 3

Detect And Repair 3

User-level prevention is a magic cure 4

Opening multiple windows 4

Creating custom lists 4

Copying styles between workbooks 5

A macro that pulls source data from charts 5

Update for Excel 2000 text export 5

Creating a workspace in Excel 5

Printing formulas 6

Shifting a scatter chart's axis 6

Recovering data with the SYLK format 6

Faster access to worksheets 7

Inserting the full file path in headers and footers 7

Exceeding the 30-argument limit 7

Ask Mr. Excel 7

Catching data entry errors 7

Ouickly setting a print area 8

Counting the days via cell subtraction 8

Ouickly shuffling toolbar buttons 8

Opening a workbook at startup 8

Hiding duplicate records 8

Converting Julian dates 9

Saving time with Custom Views 9

Beware of sharing dates between Excel for Windows and Excel for Macs 9

Don't use AutoFormat on an entire worksheet 10


www.urdusoftbooks.com




\\ Urdu Soft Books


50 Essential Microsoft Excel tips


Keep track of changes 10

Conditional formatting 10

Data entry solutions with the Template Wizard 11

Customizing "division by 0" error messages 11

Debugging your code 1 1

Debugging using watches 12

Ensure proper data entry with validation 12

Hyperlink to a specific cell 12

Macro security settings 13

Express yourself with comments 13

Map your data — on real maps 1 3

Create an Outlook message from Excel 13

Repairing broken file type associations 14

Web Publishing Wizard Error with Office 2000 14

Use auditing to troubleshoot 15

Using Goal Seek 15

Using shared workbooks 15

Use the Immediate window 15

Summarize data with grouping 16

T ake a few shortcuts 16

Analyzing external data in Excel 17

Take advantage of the Personal Macro Workbook 17

Clear out all comments 17


www.urdusoftbooks.com




H Urdu Soft Books


50 Essential Microsoft Excel tips


Easy data-posting to the Web

If your organization is using an intranet to share public data — and whose organization isn't these days —
your users will want an easy way to post this information. Excel 97 offers an easy, wizard-driven system
to walk even the most apprehensive users through this process:

1 . Select the cell range that contains the data to be published.

2. Select Save As HTML from the File menu.

3. Answer the questions posed by the Internet Assistant Wizard.

The wizard walks users through four steps that define how information will be converted for Web
display. Various options include:

* Inserting the resulting HTML tables into an existing HTML page or creating an entirely new page.

* Specifying formatting options, such as rules between data.

* Saving the new HTML page as a freestanding file or inserting it directly into a Frontpage Web.

Open Web pages right in Excel

If your users need to analyze data found on a Web site, they might be in for a bunch of tedious retyping.
Under the right circumstances, however, Excel 97 can save them a lot of time by letting them open an
HTML page in Excel directly from the Web.

The procedure is just a slight modification of the standard file-opening procedure:

1 . Choose Open from the File menu.

2. In the File Name text box, type the URL of the Web file to open. Note: Be sure to include the prefix
http://.

3. From the Files Of Type drop-down list, select HTML Documents (*.html, *.htm).

4. Click Open.

Excel opens HTML pages based on the HTML table structure of the page, so some Web design
treatments may lead to some weird results in Excel. For pages that cleanly present data in a table format,
however, this approach can be a real time-saver.

Detect And Repair

Microsoft Office 2000 programs, including Excel, contain a command line called Detect And Repair, which
checks and fixes problems with Office program files, DLLs, and registry settings. (This utility does not
affect worksheets or other document files.)

Detect And Repair, which you access as a command line option of the Setup utility, performs the
following actions:

* Reinstalls Office .exe and .dll files if missing, out-of-date, or corrupt

* Reinstalls all Windows Installer shortcuts, overwriting any existing shortcuts

* Rewrites all required local machine registry values

* Rewrites all required user registry values

You can run Detect And Repair from within Office, but that won't do much good if a serious error is
preventing your user from even launching an Office application. Fortunately, you can run the utility from
the Run dialog box with the following entry:

drive : \setup . exe /focums install. msi


www.urdusoftbooks.com




\\ Urdu Soft Books


50 Essential Microsoft Excel tips


User-level prevention is a magic cure

Every support pro knows that most of their problems could be avoided if their users would just follow a
few simple, common sense precautions. If you keep getting calls on corrupted Excel documents, pass
around this list of Microsoft-endorsed tips to help users keep their data safe and make your days a little
less stressful:

* Move radios, fax machines, televisions, stereos, speakers, and other sources of electromagnetic
radiation away from computers.

* If your users are still using floppies, make sure they are not putting them on top of their monitors.

* Regularly run Norton AntiVirus or another utility to scan for viruses.

* Store floppy disks in a disk storage container.

* Don't be a slob — prevent dust from building up around your computer.

* And, most important, exit Windows before restarting or shutting down a client system.

Opening multiple windows

One of the most frustrating problems for Excel users is viewing all the information they need, especially
because it often lives in different worksheets. Let your users know about this simple way to view multiple
sheets, and they'll love you forever.

1 . Click on the tab of the worksheet you want to view.

2. Choose New Window from the Window menu.

3. Repeat this process for each of the worksheets you want to display. (Excel opens each sheet in a
window that sits on top of the previous one.)

4. To see them all at one time, choose Arrange from the Window menu.

5. Select Tiled, Horizontal, or Vertical.

6. Select the Windows Of Active Workbook check box.

7. Click OK.

Creating custom lists

Here's another timesaving tip to pass on to your users. If they often make use of the same lists of data,
you can teach them to create a custom list instead of typing the same information repeatedly.

For all versions of Excel, if the data is already in a worksheet, follow these steps:

1 . Select all of the cells containing the information you want to copy.

2. Select Options from the Tools menu.

3. Click the Custom Lists tab.

4. Click Import.

If you want to create a list from scratch in Excel 97, follow these steps:

1 . Go to the Tools menu and choose Custom Lists.

2. Click in the List Entries box.

3. Type each item for your list, pressing [Enter] after each item.

4. When you're finished, click OK to create the list.

To create a list from scratch in Excel 2000/XP, follow these steps:

1. Select Options from the Tools menu.

2. Click the Custom Lists tab.

3. Type each item in the List Entries box, pressing [Enter] after each one.

4. Click Add.

To use a custom list, type the first entry of the list. Use the fill handle at the bottom right corner of the
cell to drag the whole list across adjacent cells.


www.urdusoftbooks.com




H Urdu Soft Books


50 Essential Microsoft Excel tips


Copying styles between workbooks

Creating formatting styles is a real timesaving trick for users. So why should they waste time re-creating
these styles in multiple workbooks? If they have formatted a workbook in a particularly useful way and
want to continue to use the format in other workbooks, teach them how to copy the style from one
workbook to another:

1 . Open the source workbook and the destination workbook.

2. Make sure that the destination workbook is active, go to the Format menu, and choose Style.

3. Click the Merge button.

4. Within the Merge Styles dialog box, select the source workbook from the Merge Styles From list box.

5. Click OK.

A macro that pulls source data from charts

When building charts in Excel 97, many users reference information in other workbooks to keep
spreadsheets fairly streamlined. But what do you do if a user complains that the data source has become
corrupted or that the file is no longer available in the network?

It's a little bit of a headache to read, but Knowledge Base article 137016 includes the full contents of a
macro that can strip important data from the graphic elements of a chart.

http://support.microsoft.com/support/kb/ARTICLES/Q1 37/0/1 6. asp

After creating the simple VBA macro, you only need to run it and drop the information into a worksheet
called ChartData. Don't go volunteering this information to all of your users, but if a VP loses an important
sales worksheet, you can pull out this nifty power trick and save the day.

Update for Excel 2000 text export

Microsoft has released a patch that fixes a formatting problem when exporting Excel 2000 files to .txt
documents. On machines running a Microsoft OS earlier than Windows 2000, four-digit year entries are
automatically truncated to two-digit entries, regardless of formatting applied within Excel.

This behavior occurs only when users employ a macro or procedure to export to a text file, not during
manual saves to a text file format. If your users are using Visual Basic for Applications to export files to
.txt, .prn, .csv, or .dif formats (and you are using a pre-Windows 2000 OS), you need to install an update
released by Microsoft. For more information, read the Microsoft Knowledge Base article 247796, XL2000:
Programmatically Exporting Text File Truncates Dates:

http://support.microsoft.eom/support/kb/artides/Q247/7/96.asp

Creating a workspace in Excel

Often, your users will be working with several workbooks at a time. They will be happy to know that they
can arrange the workbooks, save them as a workspace, and then open everything at once in the viewing
configuration they prefer. Here's how:

1. With all the workbooks in the preferred positions, go to the File menu.

2. Choose Save Workspace.

3. Type a name for the file.

4. Click Save.

The next time your users want to work with those workbooks, they can go to File | Open and select their
workspace.


www.urdusoftbooks.com




\\ Urdu Soft Books


50 Essential Microsoft Excel tips


Printing formulas

If your users have invested time in writing numerous formulas, they may want to print them for
safekeeping. Although Excel doesn't include a simple "Print Formulas" button, functionality is built in.
Here's how to find it:

1. Go to the Tools menu.

2. Click Options.

3. Within the section called Window Options on the View tab, select Formulas.

4. Click OK.

There is also a keyboard shortcut for this function. Press [Ctrl]-. In both cases, the result is that the
formulas, rather than their results, will appear in the worksheet.

Shifting a scatter chart's axis

In most cases, Excel's default behavior of making the x-axis and y-axis of scatter charts intersect at 0 is
desirable. But sometimes, your users may want to change that intersection point. An obvious example is
when a user needs to highlight just a selected number of years of data but doesn't want to create an
entirely new chart.

To reset an axis, follow these steps:

1 . Activate the chart for editing.

2. Click to select the axis you want to modify.

3. Choose Selected Axis from the Format menu.

4. Select the Scale tab.

5. Type a number in the Value (X or Y) Axis Crosses At text box.

6. Click OK.

Recovering data with the SYLK format

If a user complains of being unable to save a workbook, the problem is usually user error. However,
things do go wrong. If you've tried all the common sense remedies and the workbook does seem to be
corrupted, you can try to revert to the old Symbolic Link (SYLK) format.

This approach will let your users recover most of their essential data by stripping away the more
complex information that tends to be the source of corruption. Unfortunately, this trick won't help them get
back any data, including charts and graphics, that wasn't supported back in the days of Excel 2.0.

Follow these easy steps in Excel 97:

1 . Open the file that appears to be corrupted.

2. Select Save As from the File menu.

3. In the Save As Type list, select SYLK (Symbolic Link).

4. Click Save.

5. Click OK in the warning dialog box that appears when you save in SYLK format.

6. Close the file.

7. Click No to move on past the warning that the file you are closing is not in the normal Microsoft Excel
format. (Clicking Yes will resave the file in the normal Microsoft Excel format.)

8. Select Open from the File menu.

9. Select All Files (*.*) in the Files Of Type drop-down list.

1 0. Select the SYLK file you saved in step 3 and click Open.

1 1 . Select Save As from the File menu.

12. Select Microsoft Excel Workbook (*.xls) in the Save As Type drop-down list.

13. Click Save.

For additional information, check out Microsoft Knowledge Base article 45557:
http://support.microsoft.eom/support/kb/ARTICLES/Q45/5/57.asp


www.urdusoftbooks.com




H Urdu Soft Books


50 Essential Microsoft Excel tips


Faster access to worksheets

When working with large workbooks in which all of the worksheet tabs are not visible, your users may get
frustrated with scrolling to find the sheet they need. Here's a really quick tip to pass on. Right-click on any
of the tab-scrolling buttons (to the left of the sheet tabs) and a shortcut menu will pop up. You can then
select the tab you want.

Inserting the full file path in headers and footers

Excel 97 doesn't offer you the option of displaying the full file path in worksheet headers and footers. With
a little basic VBA editing, you can add this functionality to users' systems.

1. Go to Tools | Macro | Visual Basic Editor.

2. Go to the View menu and select Code.

3. Type the following:

Sub PathFooter()

ActiveSheet . PageSetup
Lef t Footer =ActiveWorkbook
FullName
End sub

4. Go to File | Close.

Return to your workbook and save the file. You can now run your macro by navigating to Macros and
choosing the one named PathFooter.

Exceeding the 30-argument limit

Excel has a 30-argument limit for statistical functions. It's easy to get around, though, if you group some
of your arguments within parentheses.

Instead of entering AVG(A1 ,A2,A3...,A33), you can enter AVG((A1 ,A2,A3)A4...,A33), and Excel will
accept the grouped arguments as a single argument within the formula.

Ask Mr. Excel

Excel power users and support pros: Check out MrExcel.com. Mr. Excel answers one reader's question
per week, according to what piques his interest, often including full macros to be copied and put to use.

Because this is a side project for the site's operator, he can't answer every question submitted, but he
has provided a message board for other Excel users to exchange information. Other site features include
past week's tips, quick answers, book reviews, and the opportunity to find out whether you can answer
the monthly challenge.

If you work extensively with Excel and Visual Basic, this may be the one resource you've been looking
>>>