Using MS Excel to Plan DIY Projects

Posted by John on May 23rd, 2012

Time for me to put my geek face on and talk about a software program that I use a LOT for work and home projects, Microsoft Excel.  If you don’t use this program and it’s seems intimidating, it’s fairly simple to learn and I encourage you to give it a try.  It’s perfect for shopping lists or tracking monthly spending.  I’ve used it quite a bit over the last two months on a couple recent projects so I know from experience that using Excel to plan DIY projects makes life a ton easier.

The Dining Room Project

For our raised panel project we have going on in our dining room, I used an excel spreadsheet to add up all the lumber I would need and I generated a shopping list I took to Lowes with me.  You probably could have guessed that part.  More importantly though, I used Excel to actually help me design the layout of the raised panels.  How does Excel help you design a wainscoting project?  Good question.  I’ll show you.

In addition to all the stylistic choices Lisa and I had to make for this project, the hardest part was trying to determine what panel size to use and how many per wall.  Now, the short walls are easy since I only can really fit one panel there.  It’s the looong walls that are tricky.  We have two walls that are fairly long (not counting the window wall) and ideally we wanted all the panels on these two walls to be almost exactly the same dimension or at least appear to be the same.  I needed a way to calculate differing panel sizes for varying panel numbers so we could make a decision.  Enter Excel…

So the first thing I do was measure the length of the wall then subtract an eighth of an inch (1/8″) to give me some play.  I use Excel’s math commands to do this calculation, so if I change the first number, the rest auto-update.  The command is really simple, but I’ll spare you the boring command how-to’s and let you figure that out on your own.  Trust me though, very easy stuff.

Next, I start entering material dimensions.

I’m entering these values manually, I’m not calculating them or whatever.  I chose 3.5 inches for the stile width since that’s a nice size and it’s a standard width from the hardware store!  Nothing special about that.  The end stiles are slightly wider if they are located on an end that gets covered by an ajoining panel.  They are 3/4″ longer to make up for the 3/4″ that gets covered.

Next, I enter a number of panels into the yellow box and then I add additional calculaton boxes below to figure out how many end stiles and how many 3.5″ stiles are needed.  The number of stiles will always be one more than the number of panels. 

So at this point, I know how long the wall is, I know how many stiles I have and how thick they are. The rest of the wall will be panel space. So, I can add up my stile widths and subtract that from the wall length. I end up with a total amount of space that’s reserved for the panels.

With my total panel space figured out, I just divide that number by the number of panels I have and I end up with my individual panel width of roughly 26.75.”  Now, if I want to see what the width will be for 5 or 7 panels, I just change the number in the yellow block and the green block will instantly change.  Pretty sweet.

The really nice feature is I can do this same thing for the second long wall and make a comparison to the first.  Here’s the results from the other wall…

You can see the panel width is about 1.25″ less than the other wall, which we think is fine.  Anything less than 2″ and you shouldn’t be able to tell from one wall to the next!!  But, don’t tell anyone!!

Oh, and I even used the Excel blocks to help visualize the layout in case I got a little off track.  I just highlighted a group of cells and added bold borders to them.  Pretty useful trick ay?

With the layout done for these two walls, I repeat this process for all the remaining wall sections.  Then I can calculate the heights of the panel, overall board lengths and come up with shopping lists that have the number of boards and 4×8 sheets of MDF I need to buy for the entire project.

If you’re ever interested in doing a larger scale home project, you certainly don’t have to use Excel or be some weird engineering nerd like myself.  You can plan a project like this with a pen and paper.  Using Excel can take the aggrivation out of making changes on the fly though, that’s for sure!!  Plus, it sure beats standing in Lowes staring at the lumber and wondering if you need 6 boards or 7, because I can never remember.

Do you have any Excel experience??  Do you make elaborate shopping lists or do you wander around aimlessly?  Sometime it’s more fun to not have a plan, isn’t it?


Posted in Carpentry,DIY Projects. Tagged in ,, ,

  • That's just genius! I usually figure everything out in my head (double checking with a calculator) but this way is definitely better. Thanks for the tip!

    • John_OHFScratch

      Thanks Ainhoa!

  • I make a shopping list. The cut list lives in my head or sometimes on a scrap board. Currently I’m trimming out a bunch of windows so every piece is either height or width of the window plus a predetermined amount depending on which piece it is. Head is width plus 7.5, stool is width plus 9.5, etc. I keep all those on the backside of the trim profile mock-up I made. <— Another important tool, prototyping. Always helps with wifey approval when she can get an idea of what it will look like.

    • John_OHFScratch

      Selling your wife on the idea is crucial! That\’s not a bad system.

  • Mike D.

    I use Excel or the Numbers program on the Mac for pretty much everything. Liked the use of cells for a visual layout. Did you scale the cells?

    • John_OHFScratch

      I actually did scale them when I first drew it, but then I added cut location numbers below it and they went all ### on me so I had to unscale it.

  • You are no punk! Never, ever will we be this organized!!

    • John_OHFScratch

      Haha. That\’s funny.

  • casadechristine

    Wow- this is super smart. I never would have thought to use Excel for that kind of thing. I regularly use it for budgeting, quoting customers, etc but never would have thought of it for this. I usually end up drawing things out by hand because it helps me figure it all out in my head and go through all the steps of how it will be built- we get into some pretty intricate things doing custom cabinetry sometimes!

    • John_OHFScratch

      Thanks! It definitely helps to through it first in your head for a while. I had the basic idea nailed down for some time before I used Excel to dimension it out.

  • Whitney

    At any given time at work, I've got approximately 7 excel spreadsheets open haha. My eyes get squirly sometimes! Your calculation is handy dandy! Good work!

    • John_OHFScratch

      Haha, nice! Thanks!

  • Kristen

    This is so smart but it reminds me so much of my dad. When I applied to college, he made me make a spread sheet of all the schools I was considering (including: location, size, price, ave. SAT score, etc). I don't think I could handle Excel past that!

    • John_OHFScratch

      Haha that\’s funny!! I don\’t blame you for avoiding it now. I picked my college based on a gut feeling mostly. I\’ll probably keep it loose for our daughter. Objective measurements are nice, but can be annoying.

      • Kristen

        Oh, I definitely went with my gut when it came time to choosing! That spreadsheet was just to organize all the contenders for when I started the application process 🙂

  • Todd

    I'm interested in the command, can you send it to me?

    • What command?

      • Todd

        The excel spreadsheet commands, for some reason I can't figure out how to you got your numbers. Thx

        • Gotcha. I\’ll email you my excel file later today or tomorrow.

  • nick

    I like your project. one question, what is the width of the lower portion of the poplar frame? I assume to top rail is 3.5 like the middle stiles. thanks

    • Nick, the top rail is 4" wide and the bottom rail is 7.5" wide (a 1×8). Hope that helps.