Estimating and Tracking

Excel is the general purpose spreadsheet tool which ships with the Microsoft Office suite, and is already available on all Lotech Solutions' documentation work stations. We use it to calculate our project estimates and to track our project status.

Anyone who uses the other Microsoft Office suite applications like Word, FrontPage, Outlook or PowerPoint should become proficient in Excel relatively quickly. This topic will discuss and describe the finer points of how to do some of the trickier things with Excel.

• Linking between Worksheets
• Exporting data to Outlook

Outlook is really flexible in the way it presents data. (We should try and make our software offerings this flexible.) You can view, sort, filter, store, and archive your email, and you can compose, send, copy, forward, and delete them. Outlook also has a calendar which can be used to schedule your time. You already use it when booking or accepting a meeting, and as a reminder for meetings, tasks and events. Or perhaps you should be.

Click on the image to view a larger size.

Outlook has a nifty feature called Outlook Today, which displays a summary of your calendar, tasks and messages for the current day. This is a particularly useful tool for planning your day and should be your first port of call in the morning. At a glance, you can see what you have to do, where you have to be, and who's said what to you. Click the Outlook Today node in the Folder List tree to view your's.

 

---

Linking between Worksheets

First thing to note about Excel is that for it to link between spreadsheets, both sheets must be open at the same time. Short of running multiple instances of Excel at the same time, the easiest way to achieve this is to have both sheets exist in the same workbook.

Secondly, to be able to link to data contained in a bookmark, or label as Excel calls them, both sheets must be in the same workbook. You cannot link to labels in other workbooks.

---

Exporting data to Outlook

It is possible to export an Excel file which can be imported by Outlook to automatically generate tasks and events which display in your Outlook tasks list and calendar. The benefits of such an activity would be that this enables you to use Outlook to plan your workload and schedule your time. As we leave Outlook open and running during working hours to monitor email and remind us of meetings and such, we can (and should) use its other features to improve our daily grind. (No not coffee, but that'll be nice if it could! But I digress.) See Using Outlook.

 

---

Excel - Tips and tricks

Automatic hyperlink creation

PROBLEM:

Excel automatically converts data which contains an "@" sign into an email address hyperlink.
For example, when I type "Jones@34.15" in a cell, Excel insists on turning it into an email link.

How do I stop Excel from "helping" me in this way.

SOLUTION:

To disable this functionality in Excel, (or any MS office product), and depending upon the particular product and version, the following menu item location and name will be similar to the example provided.
 

In Excel 2002 (Office XP):

1. Select Tools | AutoCorrect Options
2. On the Autoformat as you type tab, uncheck Internet and network paths with hyperlinks
3. Click OK

---

Perform maths when pasting data

Did you know that you can perform simple maths between the cell data and the clipboard data when performing a paste operation into the cell?

Note

The Paste Special menu item can be used to perform simple math between the data of the selected cell and the Windows clipboard.

In Excel 2002 (Office XP):
(When there is already appropriate data stored on the Windows clipboard.)

1. Right-click the cell to receive the paste operation.
2. Select Paste special.

   This will display the 'Paste Special' dialog:

   

3. In the Operation section, select Multiply | Add | Divide | or Subtract as appropriate.
4. Click OK.

 

---

See Also

Lotech Solutions' Tips, Tricks, and Procedures | Using Outlook

---

email webmaster

Copyright Lotech Solutions. All rights reserved.