Copyright infringement

How to use Excel’s border options by creating a simple floor plan

Applying borders to papers will make them more readable and easier to use. Learn how to use borders efficiently by creating a simple floor plan.

istock-952643774.jpg

Photo: Kritchanut, Getty Images/iStockphoto

You probably don’t think of Excel as a graphics or drafting program – because it isn’t. But you can get to grips with Excel and skip the expense and learning curve that goes with new software if all you need is a simple chart. In this article, we’ll use Excel’s border options to create a simple floor plan, something you wouldn’t normally turn to Excel to accomplish.

As we explore the borders feature, you’ll also learn the measurements of default cells in Excel and how to use the name box to help define large areas of cells. Once you are familiar with these features, you will be better prepared to apply them to your own business, which probably wouldn’t be a floor planner.

I’m using Office 365 on Windows 10 64-bit, but you can use older versions. You will only need basic formatting skills to work through this article. You can apply limits in the browser version, but you have fewer options.

More information: Office 365 Consumer pricing and features

Border Basics

First, let’s distinguish the difference between gridlines and boundaries. By default, gridlines are enabled, as shown in Figure A. It’s a basic tool that you barely notice, but you rely on to refer to cells. However, it is not a limit.

Figure A

excelborders-a.jpg

Grid lines are a solid gray line.

Boundaries are graphic lines – that’s it. You will apply them to make your papers more readable and to enhance usability. Sometimes it aligns with the grid lines, sometimes it doesn’t. To access the basic border features, click the Borders dropdown in the Font group (on the Home tab). as you see in Figure BThe options are self-explanatory.

Figure B

excelborders-b.jpg

Basic border options are available quickly.

Most of the time, you’ll find what you need in the “Borders” dropdown list. 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. Additionally, you can customize the border locations. Let’s work through a quick example using several of these options:

  1. Select any small group of cells.
  2. From the Borders option, choose More Borders.
  3. From the Color drop-down list, choose purple or whatever color looks good.
  4. From the Style menu, choose one of the dash styles.
  5. Apply these selections to the selection. Click Outline and Interior Presets.
  6. Click on each of the diagonal border options and click OK to see the results displayed in Figure C.

Figure C

excelborders-c.jpg

These are just a few of the Excel border options.

You certainly wouldn’t apply this kind of boundary very often, if at all, but we’re just exploring. Using these options will allow you to quickly customize your selections.

Real world measurements

Before you can plan a floor plan, you need cells that are workable to square feet. To achieve this, we will reset the cell width and height to one inch; Each one inch cell will represent one square foot. This may seem a little complicated because the cell width and height measurements depend on the default font size, which is 11; The default row height is 15, and the default column width is 8.38. This really does help.

There are several ways to change the height and width of cells, and you can use the method you prefer. Or use the Format drop-down menu 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 square inch.

Watch a future article on reconciling real-world measurements with Excel cell measurements.

floor plan

Now that you know the basics and beyond, let’s move on to this floor plan. You can work with any plan you want, or you can follow it with My Plan. If you’re lucky, it’s a simple rectangle, say 40 feet by 20 feet. This will be the same width as 40 columns, 20 rows deep. Let’s try something more difficult: 35 feet wide by 20 feet deep, with a sunken covered porch in the lower right corner that is 10 by 10 feet.

First, let’s paraphrase these measurements in Excel terms: 35 columns by 20 rows. We’ll be working on the balcony shortly. Start 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 the screen, Excel displays the dimensions in a tool tip near the pointer, as shown in figure d. Or you can select the entire region at once by entering B2:AK21 in the name box. This method requires knowing the two opposite cells, but if you know them, it is certainly easier than the draw method.

figure d

excelborders-d.jpg

Excel displays the dimensions as you drag the selection.

With the region selected, use the Borders drop-down menu to add a bounding border with Outside Borders or Thick Outside Borders. After doing this, you may want to zoom out to see the entire limit (Figure E). Unfortunately, when zoomed out, the screen loses gridlines.

Figure E

excelborders-e.jpg

The outer boundary represents 35 feet by 20 feet deep.

Now, let’s add a 10 x 10 portico by changing the contour line in the lower right corner to a dashed line. To do this, select AK21 and use the name box as a guide, select 10 rows up and 10 columns to the left, as shown in shape and.

shape and

excelborders-f.jpg

Select the lower right corner – 10 rows by 10 columns.

Next, choose More Borders from the Borders dropdown menu to display the Format Cells dialog. The dialog shows a solid line border – change it to a dashed line to indicate an outer bounding line. To do this, select any dashed line in the Style list, then click on each of the solid borders (shape g). We still need to display recessed boundaries. To do this, click on the solid line in the Style menu and click on the upper and left borders, since there is currently no border selected. Click OK to see details in shape h. If the contrast between the lines is not enough, change the dashed line to another line or change its color.

shape g

excelborders-g.jpg

Replace the solid lines with dashed lines and add two connecting lines.

shape h

excelborders-h.jpg

Add a cozy porch area to the front right side of the house (bottom left corner).

Now, let’s add a three-foot-wide doorway 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 balcony (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 on it). Choose a bright color from the color drop-down list. Choose a dashed line (not the same one you just used) and click the slash to the right (first figure).

first figure

excelborders-i.jpg

Add a dashed diagonal line to indicate the entrance.

At this point, you have the outside perimeter, porch, and front entrance. You’ve used most of the features so you should be ready to finish the floor plan any way you choose, or make a new one of your own. You can change the size of the cell to represent five or six feet. Use different borders and colors to denote windows and interior walls. You can even write inside cells to define each room. With a little practice, you’ll be able to apply what you’ve learned to your own business with minimal effort.

Send me your question about Office

I answer readers’ questions when I can, but there is no guarantee. Do not send files unless asked to do so; Initial requests for help that arrive with attached files will be deleted as unread. You can submit screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, you likely won’t get the response “Please troubleshoot the workbook and fix the error”, but “Can you tell me why this formula isn’t returning the expected results?” Probably. Please mention the app and version you are using. I do not compensate TechRepublic for my time or expertise when helping readers, nor do I charge a fee from the readers I help. You can contact me at susansalesharkins@gmail.com.

see also