Free Equipment Asset Tracking & Management Spreadsheet
If you're new to equipment inventory management, using Microsoft Excel, Google Sheets, Apple Numbers or other spreadsheet software is a great way to get started. We have created a template for Google Sheets that you can download and use for free. Here is a guide on how to get started.
There are several great, dedicated equipment management and tracking solutions available that helps you manage especially expensive and important equipment in your organization. Although such a solution has many advantages over using a spreadsheet, the task of just finding out which solution to go for and learning how to use it can require a big effort.
But there is no harm in getting started with equipment management simply using a spreadsheet like Microsoft Excel, Apple Numbers - or even better, a free, online spreadsheet like Google Sheets. If you later decide to upgrade to a dedicated equipment management solution, you can always import your equipment from the spreadsheet.
The advantage of using a cloud-based spreadsheet like Google Sheets is that even with several users, your equipment database is always up-to-date. A file-based, offline spreadsheet may also work, but we strongly recommend storing it in the cloud as well (Google Drive, OneDrive, Dropbox or similar). Otherwise, you will probably end up with several copies of the same equipment spreadsheet database, not knowing which one is the current and correct one (if any). And especially if there is more than one person doing the equipment management.
To get you quickly started, we have made a Google Sheets template that you can copy for free and use as a starting point. You'll find download instructions at the end of this post.
Once you have copied and opened the file, you will see a list of items. We have added a few sample items, just so you can get a quick impression of the setup.
The spreadsheet is divided into multiple sheets:
- Items: This is the main sheet where you can put all your equipment
- Usage log
- Location log
- Condition log
- Event log
The log sheets are where you'll have the history of who has been using the equipment, where it has been, how it has been performing (condition) and any event that might have occurred.
The other sheets contain records of information you can select in the items sheet and when logging information in the logging sheets, like lists of users, types and locations.
Add your own equipment inventory
First, begin by deleting the sample records in the items sheet, then start adding items. Focus just on adding the item names. Find a way of naming items that makes sense for you, but keep it consistent. The most important is that each name is unique and that it's easy to understand which item it is. Common naming conventions are combinations of brand, model and a unique identifier, like the license plate if a vehicle. If you have multiple similar items, you can simply add #1, #2, #3 and so on after the name.
We have also added a column called UniqueID, which is simply a unique number. While most equipment management solutions use QR code labels that you can scan with an app, you can manually label your equipment with this number, so that it's easy to find the exact equipment in the spreadsheet database.
If you only have a few items, there is no need to categorize them. But once the database grows, it might be convenient to group items by type. In the equipment database, select the sheet called Types. Remove the sample types, and add your own.
Go back to the Items sheet, and start categorizing each item in the Type column (C), by simply selecting the type from the dropdown menu.
If you have different people using the equipment, you should add the people to the equipment database, so that you always know who is using it. Click the Users sheet, delete the sample names and add your own users.
You can now go back to the Items sheet and assign main responsibilities for each item to different users. This is done in the column called PPR, which is an abbreviation for Primary Person Responsible.
Do the same with the Locations sheet; Remove the sample locations, and add your own. Start with the ones where your equipment is located by default, its home location. You can add new locations later as well.
Go back to the Items sheet and begin by assigning Home Location to each item, which is where the item should be located when not in use.
You can also add each item's current location.
Asset usage tracking
Now you're ready to start tracking your equipment usage and utilization, condition and performance and change of locations.
Let's begin by tracking usage. Whenever a person is about to use an item, and when the person is handing it back, those two events should be logged as a check-out and a check-in.
Go to the Usage log sheet, and make a new entry (new row). Select the item, set the current date and time, select Check out and select the person who is about to use the equipment. You can also add remarks, like what the equipment is going to be used for.
Rember also to go back to the Items sheet and change the Usage status to In use. You and your co-workers should now be able to see:
- If the equipment item is in use or idle (in the Items sheet)
- Who is currently using the item (in the Usage log sheet, find the last entry for that item), when it was checked out, and what it is being used for.
When the person is done using the equipment, it should be returned to its home location (remember, you can find the home location in the item record in the Items sheet), and the following but be added or changed in the database:
- Change the usage status in the Items sheet to Available.
- Add a record in the Usage log sheet, with the action set to Check in.
If the equipment item is simply handed over to another person without being delivered to its home location, you can simply skip changing the Items sheet. But remember to both create a Check in record and a Check out record in the Usage log sheet, so that everything is on record.
In the Check in records, it might be useful to add a few words about the item's current condition, if there were any issues with it during usage, etc. This way you can easily look back on earlier check-in records for that item if new issues should arise.
Track asset movement and location changes
Knowing where your equipment is, might be the number one key to improve utilization. Usually, when an item is checked out, it is also being moved to another location. But there are exceptions; it might be moved several times during usage, and it might also be moved without being checked out (however, that should most likely be logged as a check-out as well).
That's why we have enabled location tracking independently from the check-out and check-in process.
Whenever an item is being moved to a new location, make sure to:
- Change its current location in the Items sheet
- Add a new record in the Location log sheet. Select the item, add the date and time, select the new location and the person that is in charge of the equipment movement. You can also add remarks telling why the equipment is being moved.
Example of equipment location changes that might not involve a checkout:
If an item is shipped to a repair shop for maintenance, that should be recorded as a change of location, in addition to a new event (explained further down). The person that is logged in the location log record is the person responsible for getting the equipment back to its home location once it's done.
If an item is being moved to a location that does not exist in the dropdown menu, go to the Locations sheet and add it there.
Tracking asset events
Whenever something happens to an equipment item, it should be tracked. This could be when it's purchased, maintenance is performed, it's being sold or whatever event that is not easy to track elsewhere.
Tracking an event is done the same way as with usage and location changes. Select the Event log sheet, add a new record, select the item, add the date (and time if relevant), select the person (user) responsible for the event and write what the event is all about under remarks.
Equipment condition tracking
In order to get an impression of how well an asset/item is performing, you can track changes in condition. For instance, if an item breaks down, you should:
- Change the condition of the item record in the Items sheet
- Add a new record in the Condition log sheet.
A record should be added to the Condition log sheet every time it changes for an item. This way you can also get an impression of downtime durations.
You can get a copy of the equipment database Google Sheets for free, and expand and improve it as you like. It's a free solution that is easy to get started with, and it might be sufficient for many.
However, a spreadsheet solution has a number of limitations, like the lack of search and filtering options (useful when the database grows), the ability to store documentation, pictures and other files, analytics and performance reports, user management and permissions, easy self-service with a mobile app and so on.
You also end up with manual operations that in a dedicated equipment management system like Itefy, is automated.
That being said, it's better to start off with a simple spreadsheet solution than no solution at all. And if you find the need for upgrading to a dedicated equipment management system, you can probably import a lot of information from your spreadsheet as well.