How to simultaneously change a number of Excel values by the same value

Modifying a range of Excel values by the same variable can mean a lot of manual updating. Or you can use this built-in feature to update all those values at once.

——————————————————————————————————————-

To sum or average a row or column of values, you enter a SUM() or AVG() function that evaluates the appropriate values. It couldn’t be simpler. However, it’s not quite so easy to change a range of values by the same value. For instance, suppose you want to multiply all of the values in a range by the value 100. You could enter and copy a formula in another column, but that works only if it doesn’t matter where the results are.

If you need to adjust the actual values, to accommodate existing formulas and functions, you might adjust each value manually, which would certainly be tedious and unnecessary. Fortunately, you can use a Paste Special option as follows:

1. Select an empty cell (not adjacent to the existing range) and enter the value by which you need to adjust each existing value. For instance, to increase all the reorder values (column I in the worksheet below) by 5, you’d enter the value 5 in a blank cell.

1. With that cell (K4) selected, press [Ctrl]+C to copy the value (5) to the Clipboard.
2. Select the range you want to adjust, but don’t include a header cell in the selection. For instance, to adjust the reorder values in the previous worksheet, you’d select cells I2:I78.
3. From the Edit menu, select Paste Special. (In Excel 2007, click the Home tab, click the Paste tool’s drop-down arrow, and choose Paste Special.)
4. Choose Add in the Operation section.

1. Click OK and Excel will add 5 to each value in the selection.

You can use this trick to adjust values by adding, subtracting, multiplying, or dividing a range of values by a single value. If you change your mind, simply press [Ctrl]+Z to undo the operation.

Automatically fill in Access text boxes based on a combo box selection

You can greatly enhance a form by having Access supply information based on the user’s selection. Here’s an example that will fill in an employee’s first name and phone number when the user chooses that person’s last name from a list.

---

Your employees would like a quick way to look up each others’ business phone numbers. They want to be able to choose an employee’s last name from a list and have Access output the employee’s first name and phone number.

To create this form, follow these steps:

1. Open the Employees database.
2. Click Forms under Objects in the Database window, click the New button, and then click OK. In Access 2007, click the Create tab and then click Forms Design in the Forms group.
3. Click the Combo box control in the Controls toolbox. In Access 2007, click the Design tab under Forms Design Tools and click Combo Box (Form Control) in the Controls group.
4. Click and drag in the form where you want to locate the control.
5. Click Next.
6. Select the Employees table and then click Next.
7. Select the Employee ID, Last Name, First Name, and Phone Number fields.
8. Click Next.
9. Click the drop-down arrow in the first text box and click Last Name.
10. Click Next.
11. Adjust fields as necessary and then click Next.
12. Enter Employee Contact Form and click Finish.
13. Click in the Combo Box label control and change the label to Last Name.
14. Click the Text Box control in the Controls toolbox (Access 2003) or the Controls group (Access 2007).
15. Click and drag to create an unbound text box control below the combo box.
16. Change the text box label to First Name.
17. Click the Text Box control in the Controls toolbox (Access 2003) or the Controls group (Access 2007).
18. Click and drag to create an unbound text control below the first text box.
19. Change the text box label to Phone Number (Figure A).

Figure A

1. Right-click the combo box and select Properties.
2. Click in the On Change property box in the Event tab and select Event Procedure.
3. Click the Build button and enter this code (Figure B):

Me.Text19 = Me.Combo17.Column(2)
Me.Text21 = Me.Combo17.Column(3)

Figure B

1. Add an error handler to the On Change event subroutine.
2. Press [Alt] + [Q].

Employees can now use this form to scroll through a list of employees sorted by last name, and Access will immediately fill in the person’s first name and business phone number (Figure C).

Figure C

---

Miss an Access tip?

Check out the Microsoft Access archive and catch up on other Access tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Move text to the next column permanently

Word may not be a full-featured layout program, but it does offer some tools that let you control the position of text on the page. See how to use a column setting to get that text where you want it (and keep it there).

---

You’ve just finished typing the text in columns for the newsletter shown in Figure A. Now you’d like to move some text over from the first column to the second column so that you can insert a picture in that space. You could use the Enter key to move the text, but any edits to the first column will likely move the text back into the first column or push it farther down the second column.

Figure A

Follow these steps to avoid having to reposition the text again:

1. Click at the beginning of the block of text you want to reposition in the next column.
2. Go to Format | Columns. In Word 2007, click the Page Layout tab and then click Columns in the Page Setup group and select More Columns.
3. Select This Point Forward from the Apply To drop-down list (Figure B).

Figure B

1. Select the Start New Column check box.
2. Click OK.

You can now insert your picture into the first column without having to reposition the text in the next column, as shown in Figure C.

Figure C

---

Miss a Word tip?

Check out the Microsoft Word archive and catch up on other Word tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Quickly replace multiple space characters with a tab character

Word processing has been around for a long time and the techniques seem ingrained in our psyche. Yet, ever once in a while, I run across a document that uses multiple spaces instead of tabs to align text. The result is often a big mess. There are three ways to fix this problem:

• Manually replace the multiple space characters with tabs.
• Use several Find and Replace tasks, which is a bit faster than doing it manually.
• Use one find and Replace task to replace each instance of multiple space characters with a single tab character.

I know which one I’d choose! To replace multiple and consecutive spaces with a single tab character, do the following:

1. Choose Replace from the Edit menu (or press [Ctrl]+H) to open the Find and Replace dialog box.
2. Click the More button.
3. In the Find What control, enter one space character and the following characters, exactly as shown: {2,}.
4. In the Replace With control, enter ^t.
5. Check the Use Wildcards option.
   
6. Click Replace All.
7. Click Close.

The {2,} component tells Word to find two or more of the literal character, which in this case is a space character. You could use this component to find other multiple characters. The ^t component represents a single tab. To replace the spaces with more than one tab, simply add one ^t component for each additional tab.

Keep in mind, that this replace task will replace every occurrence of multiple and consecutive space characters, including some that you might not want to replace with a tab. If you want to retain a legitimate occurrence of multiple spaces, select only the text that you want to run the Find and Replace task against before executing it. Or, click Find Next so you can review the occurrence to decide whether you want to replace it or not.

This replace task will work in any Office application, not just Word.

Office challenge: How can you force Excel to open a specific workbook?

Many of us use Excel a lot, but we work with only one or two workbooks. After launching Excel, we open the same workbook we use everyday and go to work. Can you omit the repetitious open task and force Excel to open a specific workbook when it launches?

Last week we asked…

“How can you get Excel to return an average that doesn’t consider 0 values?” The responses were all sound and valuable. You guys really know Excel! Most of you shared the most common solution, a combination of the SUM() and COUNTIF() functions, in the following form:

=SUM(range)/COUNTIF(range,”<>0″)

Joshua.Masson@… Offered an interesting array solution:

=AVERAGE(IF(range<>0,range,”"))

Gordon.cooper@… pointed out that the array accounts for blank cells, whereas the more traditional SUM() expression doesn’t. He’s right, but I agree with gbentley@… in this particular case. Neither expression is superior, but it is important that you know how both expressions respond to blanks and text (any value that doesn’t specifically match the COUNTIF() function’s criteria).

A few of you mentioned Excel 2007’s new AVERAGEIF() function. Interestingly, if you exclude 0 values, this function also ignores blank cells — curious.

Thanks for playing! It’s interesting to see so many different solutions to the same question.

Always open Excel workbooks using a specific zoom factor

Sharing a workbook helps you and other users work smarter, but you often have to contend with settings you don’t like that are left behind by other users. For instance, people seldom work at the same zoom percentage. Depending upon their hardware and other factors, people often reduce or increase the zoom setting.

The problem is that Excel saves the zoom factor with the workbook. Regardless of how many times you reset and save the zoom factor, it’s just as likely to be different the next time you open the workbook because other users are also saving their favorite settings.

The most efficient solution is to determine the most common zoom factor and let the workbook reset it each time someone opens the workbook. Some people will still set the zoom factor to something else, but most people will find the automatic setting adequate. Simply add the following macro to the workbook’s This Workbook module:

Private Sub Workbook_Open()

  ActiveWindow.Zoom = 100

End Sub

Be sure to save the workbook after adding the macro. You can replace 100 with whatever zoom factor is the most common. Regardless of the setting each user saves with the workbook, Excel will reset it to 100 (or whatever zoom factor you specify in the macro) each time a user opens the workbook. You can use this technique to standardize a number of common environmental settings.

New update to combat security vulnerability in PowerPoint

Microsoft Security Bulletin MS09-017 – Critical

Vulnerabilities in Microsoft Office PowerPoint Could Allow Remote Code Execution

On May 12, 2009, Microsoft published a recommendation that users download the latest security update to secure a vulnerability in PowerPoint. This vulnerability allows code, executed remotely to take complete control of the system. To get more information about the update, read Vulnerability in PowerPoint could allow remote code execution.

Free online PDF to Word conversion service

There are a number of free or inexpensive utilities that print Office documents to a .pdf file. Unfortunately, reversing the conversion isn’t so easy. That’s because a .pdf file is really a collection of images, and not text. Utilities are available, but they’re relatively expensive — until now. PDF to Word, an online service, will convert your .pdf documents to Word for free!

Simply upload your .pdf file, choose the format you want (.doc or .rtf), and wait. The service will send you an e-mail with a link to the converted document. It couldn’t be easier and the results are something you can put to use right away.

Clear an Access combo box for entering the next record

You can have Access clear the selection from a combo box in a data entry form so that the user starts fresh with each new record. See how to make this user-friendly tweak.

---

You’ve created a data entry form that uses a combo box, as shown in Figure A. However, when the user clicks the next record button, the previous record’s combo box selection is still visible.

Figure A

To clear the entry for entering the next record, follow these steps:

1. Open the form in Design view.
2. Click the Form properties button at the top left of the form.
3. In the Event tab, click the On Current property box and select Event Procedure (Figure B).

Figure B

1. Click the Build button.
2. Enter the following code at the prompt:

Private Sub Form_Current()
On Error Go to ErrorHandler
Me![cboname of box]= Null
ErrorHandlerExit
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number &": Description: " & Err.Description
Resume ErrorHandlerExit
End Sub

1. Press [Alt]+Q

Now when the user moves to the next record, the previous selection will not appear.

---

Miss an Access tip?

Check out the Microsoft Access archive and catch up on other Access tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Add data to an Excel worksheet from a Word table

If you have a workbook containing data that originates in Word, you can keep the data current with the help of Excel’s Import External Data wizard. Here’s a look at how to put this handy feature to work.

---

If you need to analyze data that exists only in a Word table, you can use Excel’s Import External Data feature to update your worksheet whenever the data within the Word table changes. For example, at the end of the month you receive an e-mail with an updated listing of all YTD invoices in a Word document (Figure A).

Figure A

You would like to use Excel’s tools for analyzing the data, but you don’t want to spend time entering the new data each month. Follow these steps to have Excel update your worksheet for you:

1. Open the copy of the Word document you received as an e-mail attachment.
2. Select the table.
3. Go to Insert | Table and click on Convert Table To Text. In Excel 2007, click the Layout Tab under Table Tools and click Convert To Text in the Data group.
4. Click OK.
5. Go to File | Save As and save a plain text copy of the file as Import_table.txt.
6. Open a blank workbook in Excel.
7. Go to Data | Import External Data and click Import Data. In Excel 2007, go to Data | Get External Data | From Text (Figure B).

Figure B

1. Navigate to and select Import_table.txt .
2. Click the Import button.
3. Click Next button twice (Figure C).

Figure C

1. Click Finish.
2. Click OK.
3. Save the workbook (Figure D) as Import_from_Word.

Figure D

Now let’s say you receive an update to the Word document (Figure E).

Figure E

Update the data in the Import_from_Word file by following these steps:

1. Open the document containing the updated table.
2. Go to Insert | Table and click on Convert Table To Text.
3. Go to File | Save As and save a plain text copy of the file as Import_table2.txt.
4. Close Word.
5. Open the Import_from_Word workbook. In Excel 2007, click the Options button and then select the Enable button and click OK.
6. Go to Data | Refresh Data. In Excel 2007, click the Data tab and then click Refresh (Figure F).

Figure F

1. Navigate to and select the Import2_table.txt document.
2. Click the Import button.

The new data has now been added to your worksheet, as shown in Figure G.

Figure G

---

Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

« Previous Page — Next Page »