Subnet calculator in Excel: CIDR, network, broadcast and hosts

How a subnet calculator works

An IPv4 address is 32 bits, written as four octets of 8 bits each. The prefix length n in CIDR notation, the /26 in 192.168.10.140/26, says how many leading bits belong to the network. The remaining 32 − n host bits number the addresses inside the subnet, so every subnet is a block of 2^(32 − n) addresses that starts at a multiple of its own size.

Four results follow from that. The network address is the first address of the block (all host bits 0), the broadcast address the last (all host bits 1), the usable hosts are everything in between, 2^(32 − n) − 2, and the subnet mask is the prefix written as 32 bits: n ones followed by zeros. A calculator only has to get these from the address and the prefix.

Step 1: the IP address as one number

Octets are digits in base 256, so the address becomes one integer with =B2*256^3+B3*256^2+B4*256+B5. 192.168.10.140 is 3,232,238,220. Working with one number avoids handling each octet separately, which is where hand calculations go wrong for prefixes like /21 that cut through the third octet.

If the address sits in one cell as text, split it first. In Excel 365, =TEXTSPLIT(A2,".") spills the four octets into four cells (wrap it in -- or VALUE to get numbers). In older versions, FIND and MID between the dots do the same job, as in the guide on extracting text between two characters.

Step 2: block size, subnet mask and wildcard

The block size is =2^(32-B6): 64 for a /26, 2,048 for a /21 and 4 for a /30. The mask as an integer is 2^32 − block, the n leading ones, and the wildcard mask is its complement, block − 1. To write any integer back as a dotted address, take the four base-256 digits with INT and MOD:

=INT(x/256^3)&"."&MOD(INT(x/256^2),256)&"."&MOD(INT(x/256),256)&"."&MOD(x,256)

That gives the subnet masks 255.255.255.192, 255.255.248.0 and 255.255.255.252 and the wildcards 0.0.0.63, 0.0.7.255 and 0.0.0.3, the form Cisco access lists and OSPF network statements expect.

Step 3: network and broadcast address

The network address is the address rounded down to a multiple of the block: =INT(B8/B9)*B9. The broadcast is network + block − 1, the first usable host network + 1 and the last broadcast − 1. For the branch server 10.12.37.200/21, the block of 2,048 addresses is 8 steps of 256 in the third octet, so 37 rounds down to 32: the network is 10.12.32.0, the broadcast 10.12.39.255, and the 2,046 usable hosts run from 10.12.32.1 to 10.12.39.254.

Routers find the network with a bitwise AND of address and mask. Excel can do that too: =BITAND(B8,B10) returns the same integer as the INT formula, which makes it a good check. BITAND exists since Excel 2013 and accepts numbers up to 2^48 − 1, so 32-bit addresses fit. Row 19 of the example compares both methods.

Worked example: subnet calculator for three hosts

Yellow cells are the inputs: four octets and the prefix per column. Every other cell is a formula in the same column, so a new host only needs a new column. The networks are fictional private address ranges.

Worked example: subnet calculator for three hosts

	A	B	C	D
1	Item	Office PC	Branch server	Router link
2	Octet 1	192	10	172
3	Octet 2	168	12	16
4	Octet 3	10	37	8
5	Octet 4	140	200	6
6	Prefix /n	26	21	30
7	Address / prefix	192.168.10.140/26	10.12.37.200/21	172.16.8.6/30
8	IP as 32-bit integer	3,232,238,220	168,568,264	2,886,731,782
9	Block size 2^(32 − n)	64	2,048	4
10	Mask as integer	4,294,967,232	4,294,965,248	4,294,967,292
11	Subnet mask	255.255.255.192	255.255.248.0	255.255.255.252
12	Wildcard mask	0.0.0.63	0.0.7.255	0.0.0.3
13	Network (integer)	3,232,238,208	168,566,784	2,886,731,780
14	Network address	192.168.10.128	10.12.32.0	172.16.8.4
15	Broadcast address	192.168.10.191	10.12.39.255	172.16.8.7
16	First usable host	192.168.10.129	10.12.32.1	172.16.8.5
17	Last usable host	192.168.10.190	10.12.39.254	172.16.8.6
18	Usable hosts	62	2,046	2
19	BITAND check	Matches	Matches	Matches

The formulas cover prefixes /1 to /30. /31 and /32 are special cases, see the text. IPv4 only.

Step 4: usable hosts and the /31 and /32 exceptions

Usable hosts are 2^(32 − n) − 2: 62 for a /26, 2,046 for a /21, 2 for a /30. The 2 are the network and broadcast addresses. Two prefixes break the rule. A /31 has no broadcast on point-to-point links (RFC 3021) and uses both addresses, and a /32 is a single host route. If your calculator must cover them, wrap the formula: =IF(B6=32,1,IF(B6=31,2,B9-2)).

To size a subnet the other way round, from the hosts you need to the prefix, use =32-ROUNDUP(LOG(hosts+2,2),0). 50 hosts need 52 addresses, which rounds up to 64 = 6 host bits, so a /26.

Extending the calculator: equal subnets and VLSM

Splitting a network into equal subnets uses the same pieces. To cut a /24 into 6 subnets, borrow ROUNDUP(LOG(6,2),0) = 3 bits for a /27 with blocks of 32, and subnet k starts at (k − 1) × 32. With VLSM, where every department gets a subnet of its own size, sort the segments from largest to smallest and let each start where the previous block ended, so every block stays aligned to its size. For IPv6, the same logic counts subnets rather than hosts: a /48 holds 2^(64 − 48) = 65,536 /64 networks.

Common mistakes

• Using ROUND instead of INT for the network. ROUND can land on the next block. The network is always the address rounded down.
• Counting the block as usable hosts. A /26 has 64 addresses but 62 usable hosts. Subtract the network and broadcast address.
• Doing the math on one octet only. Prefixes like /21 or /19 cut through the third octet, so the network is not always x.x.x.0. Work on the 32-bit integer.
• Mixing up mask and wildcard. The wildcard is the inverse of the mask, block − 1 as an integer. Interface settings use the mask, Cisco access lists and OSPF use the wildcard.
• Forgetting /31 and /32. The − 2 rule gives 0 and −1 for them. Treat point-to-point /31 links and /32 host routes as special cases.
• Octets stored as text. TEXTSPLIT, MID and LEFT return text. Convert the pieces with VALUE or -- before the 256^3 arithmetic, so that every function treats them as numbers.