How to Build a Simple IT Asset Register in Excel or Google Sheets

If you’re running a small business, startup, or you’re the “accidental IT person” at a growing company, you don’t need an expensive IT asset management platform to get started. A well-structured spreadsheet in Excel or Google Sheets can give you 80% of the benefits with almost no cost and very little setup time.

This guide walks you through building a simple, practical IT asset register you can implement in under an hour.

1. What Is an IT Asset Register – and Why Bother?

An IT asset register (or IT asset inventory) is a single place where you track:

• All your hardware (laptops, desktops, servers, phones, network gear, etc.)
• All your software and licenses (installed apps, SaaS subscriptions, license keys)
• Who is using them
• Where they are
• Key dates (purchase, warranty, renewals)

Who needs this?

• Small companies and startups with growing device counts
• Solo IT admins or “techy” founders
• MSPs or small IT service providers just starting to formalize their support

Benefits

A simple register helps you:

• See what you own – Avoid “mystery laptops” in cupboards and forgotten SaaS subscriptions.
• Track lifecycle – Know when devices are bought, in use, in repair, retired, or disposed.
• Improve security – Quickly identify who has which device and software.
• Stay license-compliant – Avoid over-using licenses or paying for unused ones.
• Budget better – Plan replacements, renewals, and upgrades.

2. Deciding What to Track

Start small. You can always add more fields later.

Hardware assets

Typical items:

• Laptops and desktops
• Servers (physical or key virtual hosts)
• Mobile devices (phones, tablets)
• Network devices (firewalls, switches, access points, routers)
• Key peripherals if they matter (projectors, high-end monitors, printers)

Software assets

Track major, business-relevant software:

• Installed applications (e.g., Office suite, security tools)
• SaaS subscriptions (e.g., Microsoft 365, Google Workspace, CRM, HR tools)
• License-based tools (per-user or per-device licenses)

Don’t forget people and location

For each relevant asset, track:

• Assigned To – the person responsible (employee or contractor)
• Department / Team – who the asset supports
• Location – office name, city, rack, remote address, etc.

This makes it easy to answer: “What devices does Alex in Marketing have?” or “What’s in our Singapore office?”

3. Designing the Spreadsheet Structure

You’ll create at least two sheets:

1. Hardware – all physical IT assets
2. Software – all software and licenses

Optionally, add:

1. Lookup_Lists – dropdown values (Departments, Locations, Status, Asset Types)
2. People – list of employees/users (Name, Email, Department, Manager)

This structure keeps your main sheets clean and helps with data validation.

4. Recommended Columns for Hardware Assets

Create a sheet named Hardware. Use these columns (left to right):

1. Asset ID – Unique code you assign (e.g., LT-0012, SRV-0003).
2. Asset Type – Laptop, Desktop, Phone, Switch, Firewall, Server, etc.
3. Make – Manufacturer (e.g., Dell, HP, Apple, Lenovo, Cisco).
4. Model – Product model (e.g., Latitude 5520, iPhone 13).
5. Serial Number – Device serial number from sticker or system info.
6. Hostname / Device Name – Computer name on the network.
7. Assigned To – Employee name (match your People sheet where possible).
8. Department / Team – Marketing, Sales, Engineering, etc.
9. Location – Office name, remote, rack location (e.g., SG Office, Home – Alex).
10. Purchase Date – Date you bought the device.
11. Supplier / Vendor – Where you bought it (reseller, online store).
12. Purchase Cost – Amount paid (numeric).
13. Currency – e.g., USD, SGD, EUR.
14. Warranty Expiry Date – End of hardware warranty.
15. OS Version – e.g., Windows 11 Pro, macOS Sonoma, iOS 17.
16. Status – In use, In storage, Repair, Retired, Disposed.
17. Notes – Free text (e.g., “Screen slightly scratched”, “Assigned during remote work”).

You can copy-paste this header row directly:

Asset ID | Asset Type | Make | Model | Serial Number | Hostname / Device Name | Assigned To | Department / Team | Location | Purchase Date | Supplier / Vendor | Purchase Cost | Currency | Warranty Expiry Date | OS Version | Status | Notes

Example hardware table (sample rows)

Note: You don’t have to use these exact IDs or values—adapt them to your naming conventions.

5. Recommended Columns for Software / Licenses

Create a sheet named Software. Use these columns:

1. Software Name – e.g., Microsoft 365 Business Standard, Adobe Acrobat Pro.
2. Vendor – Company providing the software.
3. License Type – Per-user, Per-device, Subscription, Perpetual.
4. License Key / Subscription ID – Product key or subscription ID.
   • If storing full keys, consider protecting this sheet or storing keys in a separate secure location (password manager, secure vault) and only noting a reference here.
5. Number of Licenses Purchased – How many seats/devices you’re licensed for.
6. Number of Licenses In Use – How many are currently assigned.
7. Assigned To / Linked Asset – E.g., “User list” or “See Hardware tab – Asset ID LT-0001”.
8. Renewal Date / Expiry Date – Subscription renewal or support expiry.
9. Cost per License – For subscriptions, cost per user/device per period.
10. Total Cost – Simple formula: Number of Licenses Purchased * Cost per License.
11. Contract URL / Portal URL – Link to admin portal or contract file.
12. Status – Active, Expired, To be renewed, Cancelled.
13. Notes – Any extra context (e.g., “Marketing only”, “Security-critical”).

Copy-paste header row:

Software Name | Vendor | License Type | License Key / Subscription ID | Number of Licenses Purchased | Number of Licenses In Use | Assigned To / Linked Asset | Renewal Date / Expiry Date | Cost per License | Total Cost | Contract URL / Portal URL | Status | Notes

Example software table (sample rows)

Again, this is just an example – adjust vendors, prices, and types to your environment.

6. Building the Register in Excel or Google Sheets (Step-by-Step)

Step 1: Create the workbook and sheets

1. Open Excel or Google Sheets.
2. Create a new blank workbook.
3. Rename Sheet1 to Hardware.
4. Add a new sheet and name it Software.
5. Optional: add Lookup_Lists and People sheets.

Paste the header rows you copied earlier into row 1 of the relevant sheets.

Step 2: Freeze header rows and apply filters

Excel:

1. Click on row 1 in the Hardware sheet.
2. Go to View → Freeze Panes → Freeze Top Row.
3. Select row 1 again and click Data → Filter to add filter dropdowns.

Google Sheets:

1. Click row number 1.
2. Go to View → Freeze → 1 row.
3. Click Data → Create a filter.

Repeat for the Software sheet.

Step 3: Set up lookup lists (for dropdowns)

In Lookup_Lists sheet, create columns like:

• Column A: Status – values like In use, In storage, Repair, Retired, Disposed.
• Column B: Asset Type – Laptop, Desktop, Phone, Server, Switch, Firewall.
• Column C: Department – Marketing, Sales, Engineering, Finance, Operations.
• Column D: Location – SG Office, US Office, Remote, Rack A1.

You can store each list in its own column (e.g., A2:A10 for Status options).

Step 4: Use data validation for dropdown lists

Excel:

1. Select the Status column cells (e.g., Hardware!P2:P500).
2. Go to Data → Data Validation.
3. Choose List, and in the source box, reference your lookup range, e.g.:
   =Lookup_Lists!$A$2:$A$10
4. Repeat for Asset Type, Department / Team, Location.

Google Sheets:

1. Select the column cells.
2. Go to Data → Data validation.
3. Criteria: List from a range, choose the lookup range.
4. Set to Reject input that doesn’t match to keep data clean.

This prevents typos like “Marketting” or inconsistent values like “in-use” vs “In use”.

Step 5: Add useful formulas

Counting assets by status

Example: Count how many devices are “In use”.

• In a summary cell (e.g., Hardware!A100), type a label: Total In Use.
• In B100, use:
  =COUNTIF($P:$P, "In use") (assuming Status is column P).

Counting assets per user

• Formula example:
  =COUNTIF($G:$G, "Alex Tan") (if Assigned To is column G).

Software total cost

In the Software sheet:

• Set Total Cost (column J) to:
  =E2*I2 and copy it down (E = # purchased, I = cost per license).

Step 6: Conditional formatting

Flag hardware out of warranty

Goal: Highlight rows where Warranty Expiry Date is earlier than today.

Excel:

1. Select the range of Warranty Expiry cells (e.g., Hardware!N2:N500).
2. Home → Conditional Formatting → New Rule → “Use a formula”.
3. Use formula: =N2<TODAY()
4. Choose a red fill.

Google Sheets:

1. Select N2:N500.
2. Format → Conditional formatting.
3. “Format cells if”: Less than → =TODAY().
4. Apply red text or background.

Flag software renewals within next 60 days

Select the Renewal Date column in Software (e.g., H2:H500):

Formula logic: Date is >= TODAY() AND <= TODAY()+60.

In Google Sheets, for a custom formula:

• =AND(H2>=TODAY(), H2<=TODAY()+60)

Apply yellow or orange fill to indicate “renew soon”.

In Excel, you can either:

• Use two rules (greater than or equal to TODAY, and less than or equal to TODAY+60), or
• Use a formula rule with:
  =AND(H2>=TODAY(), H2<=TODAY()+60)

Excel vs Google Sheets tips

• Excel:
  • Great for local files and heavy data.
  • Can use OneDrive/SharePoint for sharing and versioning.
• Google Sheets:
  • Easier real-time collaboration.
  • Built-in version history and comments.
  • Works well if your team is already in Google Workspace.

Choose whichever your team is more comfortable with; both work fine for a simple register.

7. Processes to Keep the Register Accurate

The biggest risk with any spreadsheet is it becoming outdated. Simple, clear processes help.

When a new device is bought

• Who adds it?
  Usually the IT lead, office manager, or whoever orders equipment.
• Mandatory fields:
  • Asset ID
  • Asset Type
  • Make / Model
  • Serial Number
  • Purchase Date
  • Supplier / Vendor
  • Purchase Cost & Currency
  • Warranty Expiry Date (estimate if unknown, then update after confirming)
  • Status (often “In storage” until assigned)

When onboarding a new employee

1. Assign a laptop/desktop + peripherals.
2. In Hardware:
   1. Set Assigned To, Department / Team, Location, Status = In use.
3. In Software:
   1. Increment Number of Licenses In Use where relevant.
   2. Add their name or Asset ID to Assigned To / Linked Asset.

When offboarding an employee

1. Collect physical assets – Laptops, phones, tokens, etc.
2. In Hardware:
   1. Change Assigned To to blank or “Unassigned”.
   2. Set Status to “In storage” or “Retired”, depending on plan.
   3. Update Location to where the device is stored.
3. In Software:
   1. Remove their access (disable user, reassign license).
   2. Decrease Number of Licenses In Use.
   3. Update Assigned To / Linked Asset.

Regular reviews (monthly or quarterly)

• Filter Hardware to show:
  • Missing Serial Number or Assigned To.
  • Status = In use but Location is blank.
• Review Warranty Expiry Date:
  • Plan replacements for devices that are out-of-warranty or close.
• Check Software:
  • Filter by Status = To be renewed or Renewal Date within next 60–90 days.
  • Identify unused licenses (Purchased > In Use) and consider reductions at renewal.

Even a 30-minute review each month can keep your data reliable.

8. Basic Reporting and Use Cases

Here are common questions and how to answer them with filters and simple formulas.

“Show me all laptops assigned to the Marketing team.”

In Hardware:

1. Filter Asset Type = Laptop.
2. Filter Department / Team = Marketing.

You’ll see all Marketing laptops and who holds them.

“List assets whose warranty expires in the next 90 days.”

In Hardware:

1. Create a helper column (e.g., “Warranty due in 90 days?”).
2. In that column (say column Q), use:
   =AND(N2>=TODAY(), N2<=TODAY()+90)
   (Assuming N is Warranty Expiry).
3. Filter the helper column for TRUE.

Or use filter conditions directly if your spreadsheet tool supports date filters (e.g., “is between”).

“Total hardware value by department.”

In Hardware:

Use a Pivot Table (recommended) or SUMIF.

Pivot Table approach (works in both Excel and Sheets):

1. Select the Hardware data range (including headers).
2. Insert → PivotTable (Excel) / Data → Pivot table (Sheets).
3. Rows: Department / Team.
4. Values: Sum of Purchase Cost.
5. Optional: Add Filter on Status = In use to see only active equipment.

SUMIF approach example (for Marketing):

=SUMIF($H:$H, "Marketing", $L:$L)
(H = Department, L = Purchase Cost)

“List software subscriptions renewing in the next 60 days.”

In Software:

1. Add a helper column (e.g., “Renew in 60 days?”).
2. Formula:
   =AND(H2>=TODAY(), H2<=TODAY()+60)
   (assuming Renewal Date is column H).
3. Filter helper column to show TRUE.

You now have a quick view of upcoming renewals.

9. Limitations and When to Upgrade to an ITAM Tool

A spreadsheet is a great starting point, but it has limits:

• No automated discovery
  It won’t automatically scan your network; you rely on manual updates.
• Scalability issues
  Managing hundreds is fine; thousands of assets across multiple sites gets painful.
• Risk of inconsistent data
  If many people edit without clear rules, you’ll get duplicates, typos, and missing fields.
• Limited security and audit trails
  While Excel/Sheets offer some protection, they’re not full audit/compliance systems.

Consider moving to a dedicated IT asset management tool when:

• You regularly manage hundreds to thousands of assets.
• You need automated discovery of devices and software.
• You must meet strict regulatory or audit requirements.
• Multiple teams (IT, Finance, Security) all need different views and reports.
• You want integrations (e.g., with helpdesk, HR, security tools).

Until then, a well-maintained spreadsheet is usually more than enough for small teams.

10. Conclusion and Template Idea

By now, you should have:

• A Hardware sheet with clear columns for devices, people, and locations.
• A Software sheet tracking key licenses, renewals, and costs.
• Basic data validation, filters, formulas, and conditional formatting to keep things accurate and visible.
• Simple processes for adding, updating, and reviewing your IT assets.

You can get started immediately by:

1. Creating the Hardware and Software sheets.
2. Copy-pasting the header rows from this guide.
3. Adding a Lookup_Lists sheet for dropdown values.
4. Entering your current assets and licenses.

What a downloadable template would include

If you turned this into a downloadable template, it would likely have:

• Pre-built Hardware and Software sheets with:
  • Headers already in place.
  • Data validation dropdowns (Status, Asset Type, Department, Location).
  • Conditional formatting rules for warranty and renewal dates.
• A Lookup_Lists sheet with example values for quick customization.
• A Summary sheet with:
  • Total number of assets.
  • Assets per department.
  • Upcoming renewals and expiring warranties.

You can recreate that structure in your own Excel or Google Sheets file in under an hour. Start simple, keep it updated, and you’ll have far better control and visibility over your IT assets than most small teams.