Quickly fill blank cells in Excel

By Susan Harkins , TechRepublic on June 10, 2010

Microsoft Excel

---

Quickly fill blank cells in Excel

I received a refusing e-mail from a user not too long ago--they're new to Office 2007 and Access wasn't displaying the name of their database anymore.
One of the first things you should do when importing foreign data into Excel or upon receiving a legacy workbook is to check for blank cells and fill them in, where appropriate. Of course, some cells are blank on purpose and should remain that way. But blanks can be troublesome, if not downright destructive.
The sheet below is a good example of foreign data that as is, doesn't lend itself perfectly to a spreadsheet. In a report, repeating the company name from record to record might be distracting to the reader. Whereas in a sheet, a simple sort by a field other than the company name field would quickly orphan data--there's no way to attach a record to its company.

My best advice is to fill these types of blanks right away. You might consider typing the entries, but there's a quicker way:

1. First, select the range that contains blanks you need to fill. Don't select the column header cell--just the range that contains actual data. Using the example sheet above, the range is A2:A11.
2. Select Go To from the Edit menu or press [Ctrl]+G and then click the Special button. In Excel 2007, choose Go To Special from the Find and Select dropdown list in the Editing group on the Home tab.
3. Select Blanks.

4. When you click OK, Excel will select all the blank cells in A2:A11.

5. In the first selected blank cell (A3) enter an equal sign and point to the cell above. The cell is already selected, you don't have to actually click A3.

6. Press [Ctrl]+[Enter] and Excel will copy the respective formula to all blank cells in the selected range.

7. At this point, the range contains literal values (the original values) and formulas that repeat those literal values. To maintain order, replace the formulas with their results (the repeated literal values). Select the range (A2:A11) and choose Copy from the Edit menu. In Excel 2007, click Copy in the Clipboard group on the Home tab.
8. Select Paste Special from the Edit menu. Then, select Values and click OK. In Excel 2007, choose Paste Values from the Paste drop-down list in the Clipboard group on the home tab. You just replaced the formulas with literal values.

If the range contains formulas before the process, be careful: You don't want to overwrite those formulas, just the formulas you added to fill the blank cells. This technique won't work with every sheet that contains blank cells. In this case, this quick technique repeats existing data.

Microsoft Office

---

Reset Spelling and Grammar check

Inserting a comment lets you add a bit of information off to the side. In Word, comments are great for reviewing and editing. Many of us use Excel comments to store additional information about a value. When you add a comment, the comment displays a header. Word displays the user's initials; Excel displays the user's name. PowerPoint displays both.

If you use comments, but you don't like the default heading, change it. The thing to remember is this: Office uses the setting in all of its applications. In other words, don't make the change I'm about to show you in any Office application if you depend on this setting in another application.
At first, this option seems trivial. If you can't use it without affecting other applications, what good is it? Here's the good news. You can change this setting temporarily and insert comments that require a different heading. When you're done, reset the setting. Each comment will store the header text used when you created the comment.
That means you can use this feature to customize all you comments, if you want to go to the trouble of changing and resetting every time. Your needs will dictate this simple technique's worth.
Excel comments
By default, Excel stores the user name value in comments. If that's not adequate, you can change it as follows:

1. From the Tools menu, choose Options.
2. Click the General tab.
3. Change the User Name setting.

4. Click OK. The next time you insert a comment, the heading text will reflect your change.

Word comments
By default, Word displays initials. You can change this setting as follows:

1. From the Tools menu, choose Options.
2. Click the User Information tab.
3. Change the Initials setting.
4. Click OK.

Similarly to Excel, Word will save a custom header with the comment. Word does limit this setting to just nine characters.
PowerPoint comments
PowerPoint displays both the user name and initials, which adds a bit of flexibility. You'll find this setting also on the General tab:

1. From the Tools menu, choose Options.
2. Click the General tab.
3. Change the Name and/or Initials setting.
4. Click OK.

Office 2007
If you're using Office 2007, you'll find this option in the same place in all three applications:

1. Click the Office button.
2. Click the app Options button.
3. Choose Popular in the left pane.
4. Update the option in the Personalize Your Copy Of Microsoft Office section.
5. Click OK.

Microsoft Word

---

Use Word's AutoSum to perform simple calculations

Word can add! Seriously, if you're using Word and you need a few basic calculations, you don't have to insert an Excel sheet. You can use a Word table and let Word do the calculating. The feature's limited, but it's perfectly capable of the basics. (AutoSum seems to be absent from Word 2007, but you can still insert formulas.)
First, Word uses table cell coordinates to determine the values it evaluates. Notice that the table below has an empty column to the right and an empty row at the bottom. That's where we'll perform calculations on the existing values.

Simply click in a cell to the right or below a contiguous row or column and click the AutoSum button on the Tables And Borders toolbar. (To display this toolbar, choose Toolbars from the View menu,and check Tables And Borders.) For example, to sum the units sold in the north by all representatives, click the blank cell just below those values (and to the right of the Total row’s heading cell) and click AutoSum. As you can see, the total number of units for the north is 66. AutoSum automatically summed all of the values above the current cell. (It will stop at an empty cell.)

To total the units sold by Harkins (all four regions), click inside the empty cell at the far right of the Harkins row, just under Total, and click AutoSum. This time, AutoSum determined that the values were to the left.

But what happens when AutoSum encounters values to the left and above? To find out, select the empty cell at the far right of the Smith column (just below the current cell) and click AutoSum. This time, it sums the value(s) above; that's the default.

When this happens, you must tell the current formula to evaluate the values to the left instead. To do so, press [Alt]+[F9] to display codes. As you can see below, the actual formula reference is ABOVE. Simply highlight the ABOVE reference and replace it with LEFT.


Next, press [Alt]+[F9] to hide the codes and then press [F9] to recalculate. Now, the formula sums the values to the left.

If you know about AutoSum's default, you can use the Formula command on the Table menu as follows (in Word 2007, Formula is in the Data group on the Layout tab):

1. Select the cell in question. In this case, select the empty cell to the far right of the Nichols row.
2. Choose Formula from the Table menu.
3. In the resulting dialog box, Word will display the default formula, which references the values above.

4. Change ABOVE to LEFT, and click OK.

You can also use the Formula command when you want to specify individual values. That's because Word assigns an identifying value to each cell in the table. Every cell has two identifiers, a row identifier (a number) and a column identifier (a letter). For example, the first cell in the top-left corner is A1. The next cell to the right is B1. The cell just below A1 is A2, and so on. When evaluating individual values, separate them using a comma as follows:
=SUM(B2, C4,...)
In addition, you can substitute the default formula with a variety of functions. Simply delete all of the default formula, except for the equal sign, and then choose a function from the Paste Function drop-down list.
This flexible but somewhat limited feature has a lot to offer. However, you must remember to recalculate the file each time you change a value in the table. Word will not update formulas automatically. Simply select the entire table and press [F9] and Word will reevaluate all the formulas in the table. Or highlight an individual cell and press [F9] to recalculate a single formula.