In this article, I’ll provide you with a simple IP address management spreadsheet to track network and IP usage.
Tracking IP addresses with a spreadsheet works well when you have small networks or for tracking something verify specific. When you have many networks and several DHCP pools you should really look into using IP Address Management Software.
Even if you are using an IPAM there are times when using a spreadsheet can be useful. When I was a network manager we started with a spreadsheet and eventually moved to IPAM. We still used a spreadsheet to give a quick overview of VLANs, routes, and networks.
IP Address Management Spreadsheet Template
Here is a simple template to track the IP addresses in a subnet.
You can use the link below to download the template or you can create the template in Excel by adding the following column headers.
- IP Address
- Subnet Mask
- MAC Addresses
You can of course add or remove any fields you need.
I also included a CIDR cheat sheet with the download. This comes in useful to quickly look up the subnet mask, CIDR, and the number of available addresses in a subnet.
Recommended Tool: Automate the tracking of IP Addresses and scan your subnets by using SolarWinds IP Address Manager Tool. This tool will save you time and eliminate the need to manually update spreadsheets. It will also integrate with your DNS and DHCP servers for centralized management and reporting. Customize the dashboard to display the IP Address details you need such as IP status, MAC address, last response time, location vendor, and more.Download Free Trial
If you have a large network like a /16 you might not want to list every available IP address, that would be 65,536 rows in the spreadsheet. Instead, just enter the start and end IP addresses and then only add an entry for used IP addresses. If an IP is not listed that would mean it is available.
In the above example, you can see I’ve only entered IP addresses that are in use.
I recommend creating separate templates for each network you want to track, like the screenshot below.
Each one of these networks has multiple VLANs and subnets, so trying to put all of this into a single spreadsheet would get messy.
I keep everything related to a single network in the same Excel file. For example, I keep all networks in 10.1.2.0/16 in the same spreadsheet.
Then for network 10.1.3.0/16 I’ll create a separate spreadsheet.
That is it. Just keep it simple and a spreadsheet should work just fine.
When you are ready to automate IP Address usage then check out my list of the Top 5 IP Addresses Management Tools.