Cell Formating in Excel Using the Format Painter

In this article

One of the most common tools used by real estate investors is Microsoft Excel. Investors love Excel because it is widely used for number crunching. Number crunching is important in analyzing investment deals. But how many investors are really proficient or have a passing familiarity with this powerful tool?

I love Excel. I love passing along new tips, time savers or shortcut features of this amazing application. I use Excel in my work environment on a daily basis. As a college instructor, I assist students with their first introduction to Excel and the learning process they go through to obtain a mastery of application in Computer Science and Accounting courses. I also try to show my students that Excel is just a tool. A carpenter uses a hammer as a tool to help them finish a project. Excel is a tool to be used in assisting them in reaching their learning goals.

My goal here at BiggerPockets is to assist you in reaching your real estate analyses through the use of a tool called Microsoft Excel. Your goal should be to use Excel as a tool to assist you, not hinder you, in your analyses. Some of my blog posts will be at the beginner Excel level, while others may be more advanced. My approach to Excel will not be sequential. I will try to cover the features in Excel that will be useful to real estate investors and their spreadsheets.

Since Microsoft continues to update Excel, with the latest version at Excel 2013. I will focus upon Excel 2010, which is more widely used currently. But some of the materials I will present could also apply to Excel 2007.

Creating a Basic Spreadsheet

If you are like me, my first concern when creating a spreadsheet is to arrive at a solution. Entering the correct numbers and formulas are my main focus. As long as the spreadsheet gives me the correct numbers, I am fine.

I have a wider focus when I know that someone else will be looking at or even using my spreadsheet. Now I am concerned with how the sheet looks. At a first glance, is it easy to understand? Are the main numbers prominent, does the sheet look neat and have an easy flow?

Excel’s Cell Formatting Options

Learning to use the various cell formatting options in Microsoft Excel will make your spreadsheet more visually appealing and viewer-friendly. Just as you would skim through a magazine article, looking at boldface type, underlined text or text in different colors, reading a spreadsheet is easier when cell formatting is used.

Cell formatting is found on the ‘Home’ tab, under Font, Alignment, Number and Cell Styles options. Within these tab options you can change the font; type, size and color. Bold, italic and underlining formatting are just a click away. The alignment of cell content could be top, middle and bottom. And text alignment to the left, center and right along with word wrap and merge formatting. Cells that contain numbers can be formatted in various ways from decimal to percentage formatting and more. Finally, conditional formatting and cell styles are available to make your spreadsheet legible, reader friendly and visually appealing.

Save Time with the Format Painter

Are you familiar with the ‘Format Painter’ under Clipboard? The Format Painter can save you time and help standardize your formatting of cells within your spreadsheet.
In the ‘Home’ tab, under the ‘Clipboard’ option is the Format Painter.

The Format Painter gives a quick way of transferring or copying the formatting settings from one cell to another. When you have a cell with the decided font type, color, cell border or other formatting settings you can easily copy those settings to another cell with the click of a button.

The Textbook Way to Use the Format Painter

In most Excel textbooks or manuals, they will give the steps required to use the Format Painter. Considering that this Excel feature copies the cell formatting settings from one cell to another, I wonder why Microsoft did not call it the Format Copier? Oh well, Format Painter it is and the steps to use it follows.

Here is what you would do:

1. Click the formatted cell.
2. Click the ‘Format Painter’ option in the ‘Clipboard’. Your mouse cursor will change into “format painting” mode, showing a paint brush.
3. Click the cell where you want to apply the format. As soon as you release the mouse button, the new formatting will be applied and you will exit from format painting mode.

That is the textbook instruction on applying formatting using the Format Painter. If you only have one cell that needs the new formatting, then it is quicker than individually applying each of the formatting options. But what if you have more than one cell or you have seven cells that need formatting? The textbook way would have you repeat the above three steps for each of the seven cells. Now Format Painter is not such a timesaver, it has become mindless repetitions. A better way must be found.

Try This Trick When Using Format Painter

There is a better way. Saving time is what I want to present. Remember Excel is just a tool and we want to use that tool efficiently. With that intent, we can follow these steps when we need to apply new formatting to more than one cell:

1. Click the formatted cell.
2. Double-click the ‘Format Painter’ instead of single-clicking.
3. Select all seven cells by the ‘click drag’ method for adjacent cells or for non-adjacent cells then CTRL-click each of the seven cells. The new formatting will be immediately applied.
4. To exit “format painting” mode, single-click the Format Painter.

Now that was a timesaver. The Format Painter is a great tool if you need to quickly apply all of a cell’s format settings to other cells. Excel provides a vast number of cell formatting options and I have just presented one. Of course the Format Painter is not the only formatting option. There are other options that the tool called Excel has in its arsenal. Learning to use a tool can always save you time in the end.

Try out the Format Painter on your existing spreadsheets or when you create new ones.

Photo: Sonia Belviso

Note By BiggerPockets: These are opinions written by the author and do not necessarily represent the opinions of BiggerPockets.