
IP Address and VLAN Planner
IP Address Planner Spreadsheet, Subnet Calculator, VLSM Planner and VLAN Register for Network Engineers, Excel and Google Sheets
- Subnet calculator and VLSM planner
- Subnet, VLAN and IP address registers
- Flags overlaps, duplicates and bad gateways
- Worked two-site example
- 40 self-marking subnetting drills
What you get
- ip-address-vlan-planner.xlsxExcel workbook · 315 KB
An IPv4 address planning workbook for network engineers, IT admins and networking students. Size subnets with VLSM, keep one register of subnets, VLANs and fixed IP addresses, and let the formulas catch overlapping subnets, wrong gateways, DHCP pool clashes and duplicate addresses before they reach the network.
What you download
One .xlsx workbook with 11 tabs. Opens in Excel 2010 or later, Google Sheets and LibreOffice Calc. Standard formulas only: no macros, no add-ins, and nothing connects to your network. English. Digital download only; no physical item ships.
Inside
- Subnet Calculator: type any IPv4 address and prefix length to get the network address, first and last usable host, broadcast, subnet mask, wildcard mask, total and usable hosts, previous and next subnet, address class, address type (private, shared, link-local, loopback, documentation, multicast and more), binary and hexadecimal. Plus mask-to-prefix and hosts-to-prefix converters - VLSM Planner: list up to 30 segments with the hosts each needs and a growth percentage. It picks the smallest prefix for each, sorts them largest first, allocates them inside your parent block with no gaps and shows what is left - Block Splitter: split a block into equal subnets (up to 128 listed) - Subnet Register, 200 rows: type the site, VLAN, network and prefix; the mask, host range, broadcast, usable hosts, DHCP pool size and logged IPs fill in. It flags overlapping subnets, addresses that are not a network boundary, gateways and DHCP pools outside the subnet, and a gateway inside its pool. Summary rows hold each site's aggregate block - VLANs, 150 rows: each VLAN finds its subnet and gateway in the register; duplicate IDs and IDs outside 1 to 4094 are flagged - IP Address Log, 500 rows: hostname, device type, MAC, assignment, owner and date. Each address finds its subnet and VLAN; duplicates, network and broadcast addresses and static addresses inside a DHCP pool are flagged - Overview: totals, problems to fix on every tab and a summary per site - Worked Example: a two-site office planned step by step. Its sample data fills every tab so you can see the workbook working, with two deliberate mistakes that show what the checks catch - 40 subnetting practice drills that mark themselves, with a hidden answer key - Reference: prefix table /0 to /32, special-use IPv4 ranges and VLAN ID ranges
How it works
Yellow cells are for typing; everything else calculates. Clear the yellow cells of the sample to start your own plan. Vendor-neutral: it suits any make of switch, router or firewall. IPv4 only; IPv6 is not covered.
Every result was checked against an independent IPv4 calculation before listing.
For your own use or your organization's internal use; do not resell or redistribute the file. Designed by seller.
Designed by OEFR Digital with AI assistance. Every formula is checked before listing.
Designed by OEFR Digital with AI assistance. Every formula is checked before listing.


