How to Prepare an Equipment List: Handling Hierarchical Data in Excel

An equipment list is the backbone of any maintenance operation. It tells you what assets you have, where they are, and how they relate to each other. Yet, in our experience working with facility management teams across various industries, we've found that most companies do not have an updated, accurate equipment list. The consequences? Missed preventive maintenance, duplicate entries, miscommunicated locations, and hours wasted trying to reconcile conflicting data.

The Problem: One Excel File Per Equipment Type

A common scenario we encounter is teams maintaining separate Excel files for different equipment categories. You might have:

• Fire_Fighting_Equipment.xlsx - listing all fire extinguishers, hose reels, and sprinkler systems
• M&E_Equipment.xlsx - covering mechanical and electrical systems like AHUs, chillers, and pumps
• Electrical_Equipment.xlsx - tracking switchboards, transformers, and distribution boards

On the surface, this seems organized. Each department or equipment category has its own file. But in practice, this approach creates serious problems.

Why Separate Files Fail

The fundamental issue is that location definitions are never centralized. Consider these real examples we've seen:

• In the fire fighting file: "Level 3 - East Wing"
• In the M&E file: "L3 East"
• In the electrical file: "3rd Floor (East)"

These all refer to the same location, but good luck running a report that consolidates all equipment in that area. You'll also encounter problems like:

• Duplicate equipment IDs: Both the fire fighting and electrical files might have an "EQ001" that refers to completely different assets
• Inconsistent equipment type naming: One file calls it "Air Handling Unit", another calls it "AHU", another "Air Handler"
• No parent-child relationships: You cannot easily see that the fan motor belongs to a specific AHU
• Version control nightmares: Which file is the latest? Who updated it last?

The Solution: A Single Table Structure

The best equipment lists are structured as a single table with reference tables for locations, equipment types, and other attributes. This approach is how proper databases work, and you can implement it in Excel too.

Here's what the structure looks like:

Main Equipment Table

This table contains all your equipment in one place, regardless of type:

• id: Unique identifier for each equipment
• name: Equipment name or tag
• locationId: Reference to the location table
• typeId: Reference to the equipment type table
• parentId: Reference to parent equipment (if this is a sub-component)

Location Reference Table

A separate sheet that defines all locations consistently:

• id: Unique location identifier
• name: Standard location name
• parentId: Reference to parent location for hierarchy

Equipment Type Reference Table

Standardizes all equipment types:

• id: Unique type identifier
• name: Standard equipment type name

Handling Hierarchical Data with parentId

The parentId field is the key to representing hierarchical relationships in a flat table. Here's how it works:

• If an equipment has no parent (it's a top-level asset), the parentId is empty or null
• If an equipment is a sub-component of another, its parentId points to the parent equipment's id

For example:

• AHU-01 (id: 1, parentId: null) - This is the main air handling unit
• AHU-01 Fan Motor (id: 2, parentId: 1) - This motor belongs to AHU-01
• AHU-01 Filter (id: 3, parentId: 1) - This filter belongs to AHU-01
• AHU-01 Fan Motor Bearing (id: 4, parentId: 2) - This bearing belongs to the fan motor

The same concept applies to locations. Building A contains Level 1, Level 2, and Level 3. Each level might contain zones or rooms. By using parentId, you can represent this entire hierarchy in a simple two-column table.

Best Practices for Equipment List Structure

• Use numeric IDs, not equipment tags: Equipment tags can change; IDs should not
• Never duplicate data: Store location names only in the location table, not in every equipment row
• Establish naming conventions early: Decide on standard names for equipment types before you start
• Document your structure: Include a sheet that explains what each column means
• Use data validation: In Excel, use dropdown lists that reference your lookup tables to prevent typos
• Plan for growth: Your structure should accommodate new equipment types and locations without restructuring

How Cerev CMMS Helps

While you can implement this structure in Excel, managing it manually becomes increasingly difficult as your equipment count grows. Cerev CMMS is built from the ground up with proper hierarchical data structures:

• Centralized equipment register: All assets in one place with proper relationships
• Hierarchical locations: Define your location tree once, use it everywhere
• Parent-child equipment relationships: Easily see sub-components and their parent assets
• Standardized equipment types: No more inconsistent naming across departments
• Import from Excel: If you have existing data, we can help you migrate it properly

Stop wrestling with multiple spreadsheets and conflicting data. A well-structured equipment list is the foundation of effective maintenance management. Whether you choose to improve your Excel structure or move to a proper CMMS, the principles remain the same: centralize your data, use reference tables, and leverage parentId for hierarchical relationships.