Excel Tip: Paste Special | Multiply

Here's another trick that I've been using in preparing data files for loading into NextComp.

Many surveys display the survey result amounts in 1,000's, for example, "$54,500" is displayed as "54.5" in the Excel report from the publisher.

NextComp expects the survey data to be in hourly or annual amounts. If we load the data as it is displayed, NextComp will assume that the data represents an hourly amount of $54.50.

You can use the Copy, Paste Special | Multiply function to convert all the data in a column or a group of columns to an annual figure.

Here's how...

The image below shows a column of data before being converted to annual figures.



I entered the number 1000 in the empty cell at the top of the column, as shown below.



I copied the cell with the 1000 in it. I selected all the data in column AK. And I right clicked over the selected cells to bring up the menu shown below.



I chose the Paste Special... command from the menu. In the Paste Special dialog box, I selected the "Multiply" operation.



After clicking the OK button, the data in the selected cells is multiplied by the copied value, in this case 1000.



You can use this same method to divide data as well. Just choose the "Divide" operation in the Paste Special dialog box. This can be useful when loading data that NextComp expects to be in a percent format. Some surveys display 100% as the number 100 rather than 1 with formatting applied.