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. To begin receiving this free, valuable resource every Wednesday, visit our e-newsletter subscription center.
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
Quickly setting a print area 8
Counting the days via cell subtraction 8
Quickly 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
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 11
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 13
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
Take a few shortcuts 16
Analyzing external data in Excel 17
Take advantage of the Personal Macro Workbook 17
Clear out all comments 17
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:
Select the cell range that contains the data to be published.
Select Save As HTML from the File menu.
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:
Choose Open from the File menu.
In the File Name text box, type the URL of the Web file to open. Note: Be sure to include the prefix http://.
From the Files Of Type drop-down list, select HTML Documents (*.html, *.htm).
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