Simple visual tool to help you understand more about IPv4 Subnets, in Excel Spreadsheet

Summer Biking. From 20010616 花蓮

I created this simple tool to visualize bit-wise relation among IPv4 address, mask, and prefix. I hope it helps beginners to understand more about IPv4 subnet calculation.

How to use this tool

You can download the original Microsoft Excel document and open it.

If you do not have the software, you can also open it by using Google Docs. You just create a Doc by “Use this template”, then you can start to play with it. See  below link for this Google Docs template. You will need a free Google Docs account to use this. If you already using Gmail, then you can start Google Docs right away.

 

 

After you open this Spreadsheet, you will see similar screen output like below.

 

Use 1: Calculation practice of IPv4 subnets and prefix length

In the first tab, you fill in the blue fields about IPv4 address, mask, and prefix length. The system will check for you about mask and prefix length.They both have to match each other so the sheet will not complain.

 

 

If somehow you do now know why they do not match, you can examine the “gray characters” as hints about the correct matching masks in bits. For example in above diagram, the correct mask for prefix length 26 is “255.255.255.192”, not “255.255.0.0”.

 

Use 2: List of 4 typical neighbor addresses in the same subnet

If your mask and prefix length is correct, then 4 neighbor addresses will be listed below. They are: “Subnet address”, “First Uncast address”, “Last Unicast address”, and “Broadcast address”.

 

 

For beginners, I would like you to observe more about the following key facts.

 

Key Fact 1: There are only 32 possible subnet masks for any IPv4 subnets

You can observe all these 32 subnet masks in the second tab.

 

(Totally there are only 32 combinations, and fewer than the number of hot keys for PC games you play. It is easy, isn’t it!)

 

Key Fact 2: The shorter the prefix length is, the larger the IPv4 address space

You can create two copies of this spreadsheet. The IPv4 addresses are irrelevant here. You play with two different lengths of subnet masks to observe this fact.

By the way, this tool is just a normal Spreadsheet, you can make as many copies as you like and save as different files. I believe this is a special advantage over other software-base IPv4 subnet tools.

We always find “shorter” prefix to summarize several smaller subnets, and calculate “longer” prefixes when we are breaking down one large subnet. You can visualize them in this tool.

 

Key Fact 3: If two IPv4 networks have common prefix, then the network with “shorter”prefix length will cover the whole smaller one

You can create another two copies of this spreadsheet. You should pick the same IPv4 address in this observation. You can now play with two different lengths of subnet masks to observe this fact.

 

Cheers!