Show All

Hide All

By Curtis D. Frye

One of an office manager's toughest challenges is keeping track of computers, furniture, and other equipment. Microsoft Office Visio Professional 2003 is a program that you can use to capture your office's physical arrangement and represent your office equipment within that layout. However, it may be difficult to view information about all of your equipment at one time in Visio.

Fortunately, you can use Microsoft Office Excel 2003 to manage data from your Visio office equipment lists. In this article, you'll learn how to combine the equipment management capabilities of Visio with the data and list manipulation capabilities of Excel to manage your office equipment effectively.

Decide which data to keep in Visio

You should decide early on which information you want to store in Visio and which you want to store in Excel. A good rule of thumb is that you should store information about the object itself, such as serial numbers, manufacturer's product numbers, and purchase dates, in Visio.

Information relating to your organization's policies, such as the date that the item is due to be replaced, should be kept in Excel. Suppose that your company's policy is to replace personal computers after using them for three years. You could create a custom property in Visio and store the equipment retirement date, but what if your organization changes its policy so that computers are kept around for four years? In Excel, you would just need to change a single formula to reflect the new policy.

Manage equipment data in Visio

The first step in using Visio and Excel to manage your office equipment is to type asset-tracking information about each piece of equipment into the corresponding shape in Visio. Later, you can export the data to Excel for further analysis.

Assign custom properties in Visio

You type the detailed asset tracking information about each piece of equipment in your office in the Visio Custom Properties dialog box.

Custom Properties dialog box

To assign custom properties to a Visio drawing shape

Export Visio data to Excel

After you enter the custom properties for your office equipment and furnishings, you can export the data to Excel. After you have the data in Excel, you can view information about your equipment on a single worksheet, arrange the data to answer questions you have about it, and change the data's formatting to highlight equipment that needs to be replaced.

To export Visio objects' custom properties to an Excel workbook

Managing office equipment data in Excel

After you have your Visio object data in Excel, you can create formulas and formatting that you can use to manage data effectively. One powerful tool at your disposal is conditional formats, which change the appearance of Excel data based on the data's value.

As an example, consider the following equipment-tracking worksheet, which lists the purchase date and replacement date of each personal computer in your office. It's not easy to see which computers need to be replaced.

The worksheet contains all of the information you require to order new personal computers, but you need to look hard to see which computers have passed their replacement date. However, it would be easy to see which computers need to be retired from service if dates in the past were displayed in red text.

To create a conditional format that signals when a piece of equipment is overdue for replacement

Conclusion

Visio and Excel are powerful programs on their own, but you can combine them to manage your office equipment effectively. By storing the purchase date, physical location, and tracking information of your office equipment in Visio, you can manage your physical inventory. When you export your equipment's information to Excel, you can track replacement dates much more easily.

About the author   Curtis D. Frye is an industry analyst and author of Microsoft Office Excel 2003 Step by Step and several other books from Microsoft Press.