You probably don’t think of Excel as graphics or drafting software—because it isn’t. But you can make do with Excel and forego the expense and learning curve that goes along with new software if all you need is a simple outline. In this article, we’ll use Excel’s border options to create a simple floorplan, which isn’t something you’d ordinarily turn to Excel to accomplish.
As we explore the border feature, you’ll also learn Excel’s default cell measurements and how to use the Name Box to help select large areas of cells. Once you’re familiar with these features, you’ll be better prepared to apply them to your own work, which probably won’t be a floorplan.
I’m using Office 365 on a Windows 10 64-bit system, but you can use older versions. You’ll need only basic formatting skills to work through this article. You can apply borders in the browser edition, but you have fewer options.
LEARN MORE: Office 365 Consumer pricing and features
First, let’s distinguish the difference between gridlines and borders. By default, gridlines are enabled, as shown in Figure A. They’re a basic tool that you barely notice, but you rely on them to denote cells. They aren’t, however, borders.
Borders are graphic lines—that’s it. You’ll apply them to make your sheets more readable and to enhance usability. Sometimes they’ll align with gridlines, and sometimes they won’t. To access basic border features, click the Borders dropdown in the Font group (on the Home tab). As you can see in Figure B, the options are self-explanatory.
Most of the time, you’ll find what you need on the Borders dropdown. When you don’t, choose More Borders to display the Format Cells dialog, where you’ll find interesting tools for displaying specific border styles and colors. In addition, you can customize the border positions. Let’s work through a quick example using several of these options:
- Select any small group of cells.
- From the Borders option, choose More Borders.
- From the Color dropdown, choose purple, or any color that will show up well.
- From the Style list, choose one of the dash styles.
- Apply those choices to the selection. Click the Outline and Inside presets.
- Click both diagonal border options and click OK to see the results shown in Figure C.
Granted, you wouldn’t apply this type of border often, if at all, but we’re just exploring. Using these options will allow you to customize selections quickly.
Before you can layout a floor plan, you need cells that are applicable to square footage. To accomplish this, we’ll reset cell width and height to one inch; each one-inch cell will represent one square foot. This can seem a bit complicated because cell width and height measurements are based on the default font size, which is 11; the default row height is 15, and the default column width is 8.38. That’s really no help.
There are a number of ways to change the height and width of cells, and you can use the method you prefer. Or, use the Format dropdown in the Cells group (on the Home tab) to reset the default width to 12 and the default height to 72, which is approximately one-inch square.
Watch for a future article on reconciling real-world measurements with Excel’s cell measurements.
The floor plan
Now that you know the basics and a bit beyond, let’s get to that floor plan. You can work with any plan you like, or you can follow along with mine. If you’re lucky, it’s a simple rectangle, say 40 feet by 20 feet. That would be the same as 40 columns wide by 20 rows deep. Let’s try something more difficult: 35 feet wide by 20 feet deep, with a recessed covered porch in the bottom-right corner that’s 10 by 10 feet.
First, let’s restate these measurements in Excel terms: 35 columns by 20 rows. We’ll work out the porch in a bit. Begin by selecting B2 and drag 35 columns to the right and then 20 rows down. You can do the math in your head as you drag but using the Name Box is easier; as you drag, this control updates with the number of columns and rows in the selection. If you scroll off-screen, Excel displays the dimensions in a tooltip near the cursor, as shown in Figure D. Or, you can select the entire area at once by entering B2: AK21 in the Name Box. This method requires knowing the two opposite cells, but if you know them, it’s certainly easier than the drag-select method.
With the area selected, use the Borders dropdown to add a perimeter border using Outside Borders or Thick Outside Borders. After doing so, you might want to zoom out to see the entire border (Figure E). Unfortunately, when zooming out, the display loses the gridlines.
Now, let’s add that 10 by 10 porch by changing the perimeter line in the bottom-right corner to a dashed line. To do so, select AK21 and using the Name Box as a guide, select 10 rows up and 10 columns to the left, as shown in Figure F.
Next, choose More Borders from the Borders dropdown to display the Format Cells dialog. The dialog shows the solid-line border–change it to a dashed line to indicate an outside perimeter line. To do so, select any dashed line in the Style list and then click both solid borders (Figure G). We still need to display the recessed borders. To do so, click the solid line in the Style list and click the top and left borders, where presently no border is indicated. Click OK to see the detail in Figure H. If the contrast between the lines isn’t great enough, change the dashed line to another one or change its color.
Now, let’s add an entrance that’s three feet wide on the left side of the porch that’s also two feet from the porch’s back wall. Go to the far-left corner of the porch (AB12) and go down two cells (AB14). Then, select three cells down. Select More Borders from the Borders dropdown list. Remove the solid line on the left (simply click it). Choose a bright color from the Color dropdown. Choose a dashed line (not the same one you just used) and then click the diagonal line to the right (Figure I).
At this point, you have the outside perimeter, the porch, and the front entrance. You’ve used most of the features so you should be ready to finish the floor plan any way you choose, or create a new one of your own. You could change the cell size to represent five feet or six. Use different borders and colors to indicate windows and inside walls. You can even type inside cells to identify each room. With a little practice, you’ll be able to apply what you’ve learned to your own work with little effort.
Send me your question about Office
I answer readers’ questions when I can, but there’s no guarantee. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at firstname.lastname@example.org.