Inventory lending template in Google Sheets

by Oct 9, 2019

Does a department in your company lend items internally? Perhaps you have a library in your school that lends books to students. Or an IT department that lends computer equipment to employees.

Maybe you run a hire business where you rent items to customers, such as bikes, tools or clothing?

Whatever type of business, college or organization you work in, tracking lent items is an essential task—but it’s often done manually or using cumbersome IT systems.

Keeping tabs on exactly what stock you currently have in your inventory, what has been lent out (and to whom) helps you to calculate if you have enough equipment for your business, to recognize if certain items are going unused and to help you trace missing or overdue items that have not been returned.

You could purchase specialist lending software, or ask your IT team (if you have one!) to build you a tracking system, but you can actually do it yourself using the tool you already know and use: spreadsheets. It’s possible to create an easy-to-use and reliable lending system at your desk, right now. To help you get started, we have created a free inventory lending template for you, in Google Sheets.

Why use an inventory lending template?

With our ready-to-use template you can:

  • Register all items lent and returned
  • Keep track of how many items each borrower has
  • See what items you have available— in a visual dashboard

All in one spreadsheet.

The dashboard and visual analysis tabs give you an at-a-glance view of your inventory.

See which items have been borrowed by which user:

What do I need?

We’ve created the template for you in Google Sheets so all you need is a Google account. It’s completely free.

You’ll see sample data in the template: this is provided as an example, so you can simply delete or overwrite this with your own data. The dashboard works automatically—we’ve already written the formulas for you.

Just click on the green button to get a copy of the template:

How to use the template

The template is a Google Sheets file named Inventory lending template and it saves automatically to your Google Drive. Check out the first tab: this gives you full instructions.

Step 1:

Begin by filling out the red Item Database tab. This is where you list all of the items that you currently have in your inventory. When you acquire more items, just add them to this list.

Step 2:

When someone borrows an item, enter the date, borrower name, item and quantity in the Check-out tab. 

Step 3:

When someone returns an item, register it in the Check-in tab.

That’s it! Easy.

Your inventory lending system is up-and-running.

Check out your the Dashboard tab for an automated overview of what items are available for lending and how many items have been borrowed.

You have two further analysis tabs:  Current Inventory List and Item Matrix. These are generated automatically—don’t try to edit the red or grey cells.

The Current Inventory List shows you a breakdown of data on each item:

Managing a large library or inventory? Lending items from multiple locations?

If you lend a lot of items, managing everything in one spreadsheet might become messy. Perhaps items are borrowed from one location, but returned to another.

Spreadsheets are the perfect tool for tracking lent items but when you have multiple users working on the same spreadsheet, the chance of manual mistakes increases.

If you’re responsible for managing inventory, it might make sense to give colleagues or departments their own individual spreadsheets.

With the Sheetgo add-on for Google Sheets, you can expand on the template we gave you earlier to create an automated inventory lending tracker.

Colleagues or other departments register items lent or borrowed in their own spreadsheets—and you view the data in your dashboard.

And the best part? The data flows automatically.

Inventory Lending Template Master Sheet and Check-in Check-out Sheets Icons

Automate your inventory lending system

Sheetgo is a platform to send data from one spreadsheet to another.

If you are familiar with using spreadsheets, you can use Sheetgo to manage your school or company’s inventory in an automated system. This means

  • A clear and easy-to-use system for staff
  • Reduced chance of errors—more accurate inventory data
  • You get an automated overview of items lent and borrowed

How to connect spreadsheets to create an automated inventory lending system

Step 1:

Install the Sheetgo add-on.

Step 2:

Open your inventory lending template (the one we gave you earlier). This will now function as your “master sheet”.

Step 3:

Go to the Check-In tab. Click the small arrow (or right-click on the tab) to bring up a menu. Click copy to > new spreadsheet. Select “open spreadsheet”.

Step 4:

This will now function as an “input” spreadsheet for the colleague or department responsible for checking in items. Here they register every item that is returned by a borrower.

Rename the file (e.g. Books returned) and allocate it to the colleague/s responsible, using Google Sheets’ share function.

The spreadsheet contains sample data to show you how the system works: your colleagues can simply delete this or overwrite it with their own data.

Step 5:

Repeat the same steps for the Check-out tab. Allocate this input spreadsheet to the person/s in charge of lending items. Here they register every item borrowed.

Step 6:

Now it’s time to connect the spreadsheets using Sheetgo. 

From inside your master sheet (inventory lending template) go to menu > add-ons > Sheetgo.

Click “Connect Files” and then select the Check-in input spreadsheet (e.g. Books returned) as your source file. 

Step 7:

Under “Settings” change the connection name to have the exact same name as the destination tab (Check-in).

The data from the source tab in the input spreadsheet will now flow automatically into the destination tab in your master sheet.

Click “Save Connection”.

Congrats!

You have created your first Sheetgo connection. Check out your master sheet. You will see that data is now flowing automatically from your colleague’s input spreadsheet.

In the Sheetgo add-on you can schedule updates when you want. Adjust the frequency to suit your needs.

Repeat the same steps to connect the Check-out input sheet. Remember to change the name of the connection to match the exact same tab name.

Want to automate other processes in your school? Check out our post on how to make life easier for your teachers by automating student attendance tracking in spreadsheets.

Share This