::: Excel tips 15 :::

>> Opening a workbook at start up <<

If some of your users spend much of their day working in a specific workbook, you can teach them how to designate that workbook to open each time Excel launches. All they need to do is place the workbook (or a shortcut to the workbook) in the XLStart folder. This technique also works with network shortcuts.
You'll find the XLStart folder in the same place as the Excel program files—typically C:\Program Files\Microsoft Office\Office. After placing the file or shortcut in the XLStart folder, the user can just restart Excel to see the file pop open automatically.

--------------------------------------------------------

>> Hiding duplicate records <<

One of the most common mistakes users make in preparing a report or sorting data is including redundant entries. For example, if a worksheet keeps a running tally of purchases made by Customer 1, there's no need to report cells other than the ones keeping the composite figures. A built-in filter in Excel will take care of this problem.
Follow these steps:
Choose Data | Filter | Advanced Filter.
Drag across the worksheet to select the lists containing duplicate entries.
Select Unique Records Only.
Click OK.
Excel will now hide any duplicate records in the selected range.

::: Excel tips 14 :::

>> Counting the days via cell subtraction <<

By default, Excel 97 adjusts the display of dates to friendly displays that users can identify. But behind the scenes, Excel still thinks in numbers. So finding the number of days between two days is as easy as simply subtracting one cell that contains a date, regardless of format, from another date-carrying cell.
For example, let's suppose a user has entered the date 2/20/2000 in cell A1 and 3/10/2000 in cell A2. The formula A2-A1 will return the result 19 (2000 was a leap year). Note: You must use the date that falls later in the calendar year as the first argument of the equation, or the equation will return an error.
By default, Excel will format the result of a formula in the same format as the source cells. So in our example, it reports that the difference between 2/20/2000 and 3/10/2000 is 1/19/1900. However, simply right-clicking the formula cell, selecting Format Cells, and selecting a Number format will fix this problem.

----------------------------------------------------------

>> Quickly shuffling toolbar buttons <<

Here's a quick tip you can pass on to your users who like to modify their Excel toolbars.
Instead of right-clicking a toolbar and selecting Customize to launch the Customize dialog box, they can simply press the [Alt] key and click and drag a button to any toolbar they want. Of course, both the source and target toolbars must be visible for this procedure to be effective.
One quick word of warning: This technique moves buttons, so they will no longer be on the default toolbar. If users want to copy a button to a new toolbar while retaining its position on the original one, they can hold down [Ctrl][Alt] while clicking and dragging the button.

::: Excel tips 13 :::

>> Catching data entry errors <<

It's a fact: Shared workbooks generate more errors than workbooks with only one owner. Here's one way to flag errors as they occur:
. Select all cells in which data will be entered.
. Go to Format | Conditional Formatting.
. In the Conditional Formatting dialog box, specify which cell values to flag.
. Click Format.
. In the Format Cells dialog box, click the Patterns tab and select a color to signal errors.
. Click OK.
Now, when someone enters an error, it will be flagged with your selected color.

----------------------------------------------------------------------

>> Quickly setting a print area <<

Your users probably waste a lot of time and paper sending entire worksheets to the printer when all they need is a printout of a few columns or rows. Teach them this quick trick to set specific print areas within the Print Preview mode:
. Select View | Page Break Preview.
. Select the cells you want to print.
. Right-click on the selection and choose Set Print Area from the shortcut menu.
. Click Print.

::: Excel tips 12 :::

>> 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 for.
http://www.mrexcel.com

::: Excel tips 11 :::

>> 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.
. Go to Tools | Macro | Visual Basic Editor.
. Go to the View menu and select Code.
. Type the following:
Sub PathFooter()
ActiveSheet.PageSetup
LeftFooter=ActiveWorkbook
FullName
End sub
. 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.

::: Excel tips 10 :::

>> 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 :
. Open the file that appears to be corrupted.
. Select Save As from the File menu.
. In the Save As Type list, select SYLK (Symbolic Link).
. Click Save.
. Click OK in the warning dialog box that appears when you save in SYLK format.
. Close the file.
. 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.)
. Select Open from the File menu.
. Select All Files (*.*) in the Files Of Type drop-down list.
. Select the SYLK file you saved in step 3 and click Open.
. Select Save As from the File menu.
. Select Microsoft Excel Workbook (*.xls) in the Save As Type drop-down list.
. Click Save.
For additional information, check out Microsoft Knowledge Base article 45557:
http://support.microsoft.com/support/kb/ARTICLES/Q45/5/57.asp

::: Excel tips 9 :::

>> 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:
. Go to the Tools menu.
. Click Options.
. Within the section called Window Options on the View tab, select Formulas.
. 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:
. Activate the chart for editing.
. Click to select the axis you want to modify.
. Choose Selected Axis from the Format menu.
. Select the Scale tab.
. Type a number in the Value (X or Y) Axis Crosses At text box.
. Click OK.

::: Excel tips 8 :::

>> 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.com/support/kb/articles/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:
. With all the workbooks in the preferred positions, go to the File menu.
. Choose Save Workspace.
. Type a name for the file.
. Click Save.
The next time your users want to work with those workbooks, they can go to File | Open and select their workspace.