Rack diagram from Excel: a free template and two formulas
Updated
A rack diagram (rack elevation) only needs one row per device with the rack, the lowest U it sits in and its height in U. With those columns, one FILTER formula draws every rack as a column of U slots in Excel or Google Sheets, conditional formatting colours the occupied slots, and a second formula flags two devices in the same U. The free template below has the columns already.
What the diagram needs
A rack elevation is a lookup: for every U slot in every rack, which device covers it? The template’s Devices sheet already has the columns for it:
| Column | Header | Example | Notes |
|---|---|---|---|
| A | rack | R01 | The rack’s name, spelled the same in every row |
| B | u | 12 | The lowest U the device occupies |
| C | model | (any) | Homelab Twin takes the height and the watts from the model |
| D | name | pve-01 | What you want to see in the slot |
| F | height_u | 2 | How many U it takes: a 2U server in U12 occupies U12 and U13 |
Leave u empty for gear that isn’t in the rack (an access point, the ISP box): it stays in the list without taking a slot. For the formulas below, add one helper column L with the height, 1U when height_u is empty:
L2: =IF(F2="",1,F2)Download it here: the free template (.xlsx). It is the same file Homelab Twin imports.
Draw the rack with one formula
Make a second sheet, Rack view. Put the U numbers down column A from the top (for a 24U rack: 24 in A2, 23 in A3, down to 1 in A25) and the rack names across row 1 (B1 = R01, C1 = R02). Then, in B2:
=IFERROR(INDEX(FILTER(Devices!$D$2:$D$200,
(Devices!$A$2:$A$200=B$1)
*(Devices!$B$2:$B$200<>"")
*(Devices!$B$2:$B$200<=$A2)
*(Devices!$B$2:$B$200+Devices!$L$2:$L$200-1>=$A2)), 1), "")Copy it across the grid. Each cell shows the device whose span covers that U in that rack, or stays empty. FILTER works in Excel 365, Excel 2021 and Google Sheets.
To make it look like a rack, add a conditional-formatting rule on the grid: format cells that are not empty with a fill colour. A 2U device then shows as a two-row block with its name in both rows.
Find the clashes
Two devices in the same U is the most common mistake in a hand-made rack sheet. In the Devices sheet, column M:
=IF(B2="","",SUMPRODUCT((A$2:A$200=A2)*(B$2:B$200<>"")
*(B$2:B$200<=B2+L2-1)
*(B$2:B$200+L$2:L$200-1>=B2))-1)It counts the other devices in the same rack whose span overlaps this one and skips off-rack gear. Anything above 0 is a clash: filter the column for values greater than 0.
Plan the U before you buy
The planner below draws a small elevation as you add devices and adds up the watts, so you can try a layout before you edit the sheet:
10-inch rack planner
Keep it useful
- One sheet, one truth. Rename racks in one place; a typo in the rack column makes a device vanish from the grid.
- Add the power. A watts column next to each device turns the same sheet into a power budget: sum it per rack and compare it with your UPS and power-strip ratings.
- Add the ports. A Cables sheet (from device, from port, to device, to port) is what you need when a link goes down at 2 a.m.
Sources
- Microsoft and Google documentation for FILTER, INDEX, SUMPRODUCT and conditional formatting
- The RACKDENSITY Excel template (Devices sheet: rack, U, model required)
Questions
What is a rack elevation diagram?
A front (or rear) view of a rack with its U slots numbered, showing which device sits in which slots and which slots are free. It is the drawing you check before you buy, mount or move anything.
Is U1 at the bottom or the top?
On most racks U1 is the bottom slot and the numbers count up, but some rails are numbered from the top. Check the markings on your rails and use the same convention in the sheet.
How do I show devices mounted at the back?
Add a face column (front or rear) and build a second grid that filters on face = rear. Power strips and some switches are often rear-mounted; zero-U strips on the side don't take a slot at all.
Does this work for a 10-inch rack?
Yes. The grid does not care about the width: a 10-inch rack is just a shorter column, usually 4U to 12U. Use the planner on this page to check what fits.