Paste Special Multiplier

Cast your mind... It’s the last Friday of your fiscal quarter and you have generated the Sales forecast budget for the president of the company. Just when you’re feeling good and relaxed (5 minutes before the numbers are displayed on a projector for all to see) your arch office enemy smugly tells you that the numbers you were given were the old ones. The new ones are 5% higher!

He proceeds to start measuring your desk as he is sure you will be fired and he will take over your job (and prestigious salary).

No need to panic, you just need to break out the mad crazy Excel skills that everyone in your office have grown to depend upon.

Here is some sample data that we can use for our example. It has a category and total sales. For our example we will need to change all these values by increasing them by 5%.

Equipment	Sales Total
Baseball	603.00
Basketball	485.00
Fishing	638.00
Golf	348.00
Hockey	338.00
Tennis	526.00
Grand Total	2,938.00

Start by placing 1.05 into any blank cell.

Equipment	Sales Total
Baseball	603.00	1.05
Basketball	485.00
Fishing	638.00
Golf	348.00
Hockey	338.00
Tennis	526.00
Grand Total	2,938.00

Now copy the value in that cell by Edit, Copy or press Ctrl and C. 

Next Select all the values that you need to change (our Sales Total data). The data will highlight grey.

Equipment	Sales Total
Baseball	603.00	1.05
Basketball	485.00
Fishing	638.00
Golf	348.00
Hockey	338.00
Tennis	526.00
Grand Total	2,938.00

Now select Edit, Paste Special to bring up the Past Special dialog box. Select Multiply and click the OK button.

Instantly the numbers increase by the 5%.

Equipment	Sales Total
Baseball	633.15
Basketball	509.25
Fishing	669.90
Golf	365.40
Hockey	354.90
Tennis	552.30
Grand Total	3,084.90

The day saved, you once again are the Excel hero.

You are not limited by multiplication. Notice that in the Paste Special dialog box, you have the options to add, subtract, multiply and divide.  

Break out this useful skill in crunch time and WOW your clients.

Text to Column

Today I want to address a very common Excel problem. I am often asked to break out values in cells. In the below example I have a column of data with item numbers and descriptions. What I would like it to have the item numbers in one column and the description in a second column.

	A
1	Description
2	52781-Wheel
3	60258-Square
4	62373-Triangle
5	25224-Rectangle
6	17075-Oblong
7	35136-Parallelogram
8	60509-Wheel
9	10542-Square
10	50900-Triangle
11	49043-Rectangle
12	46242-Oblong
13	25777-Parallelogram
14	22831-Wheel
15	10798-Square
16	32140-Triangle
17	55800-Rectangle
18	53789-Oblong
19	31761-Parallelogram
20	22846-Wheel

Now there are a few ways I can go about this. If the item numbers are all the same length (in this example 5 characters) I could use the formula =left(Cellreference,5). This would then break out my item number.

	A
1	Description	Result	Formula
2	52781-Wheel	52781	=LEFT(A2,5)

To get the rest of my cell I can use =Len to identify the number of characters in the cell. And then use that result to get the description using a =Right function.

	A	B	C	D
1	Description	=LEFT(cell,5)	=LEN(A2)	=RIGHT(A2,(C2-6))
2	52781-Wheel	52781	11	Wheel

Now notice that my =Right function uses 6 instead of 5. I use 6 because the item is 5 digits and the dash is a 6^th.

Another way to break out this is by identifying where the dash in the description is. Here I use the =Find function to identify the starting position of the dash. This is useful if my item number varies in size (perhaps 4 characters for some items, 5 for others).

	A	B	C	D
1	Description	=FIND("-",A2)	=LEFT(A2,(B2-1))	=RIGHT(A2,(LEN(A2)-B2))
2	52781-Wheel	6	52781	Wheel

Of course the easiest way to break out this data is to do a simple Text to columns from the Data tab.

Start by highlighting your column of data then click Text to Columns from the Data tab.

This brings up the Text To Columns wizard. You can choose to break out data by a character in your data (delimited) or by a fixed width (such as 5 characters). In this example I am going to choose Delimited and then click the next button.

I choose Other as the Delimiters and type the dash into the box. The Data preview window shows how my selection alters the data.

I click the Next button and can format my data if needed by clicking on the Column data format radio button.

When done I click the Finish button. The result is my Item numbers being broken out from my description.

Now in order to use Text To Columns you must have blank columns to the right of your data column that you are going to break out. If you are breaking your data out into 3 columns, you need 2 blank columns to the right to accommodate the information.

Look at the following data. There is now another column adjacent to the column I want to break out with Text To Columns.

Description	Value
52781-Wheel	2.00
60258-Square	4.00
62373-Triangle	5.00
25224-Rectangle	2.00
17075-Oblong	3.00
35136-Parallelogram	5.00
60509-Wheel	2.00
10542-Square	2.00
50900-Triangle	2.00
49043-Rectangle	5.00

When I complete “Convert Text to columns Wizard – Step 3 of 3” I now get the following message…

If I click yes, then my data in column B will be overwritten.

To avoid this I need to add a column between columns A and B.

It is important to also note that if you use Text To Columns you destroy the original data column (replacing it with your delimited data). If you need to maintain your original data you will need to copy the column first or use one of the above methods to break out your data using formulas.