SIMPLE EXCEL SOLUTIONS: Be Special - Paste Special

I would like to share with you one of my favorite tricks, “Paste Special”; sometimes this tool saves me a lot of time.

Recently a colleague of mine asked if there is a way to replace negative Sales values of a large data file to positive ones at once; he also wanted to increase range of Sales values by 5%.

Here is the solution:1. Operations. Sales records in the data file are negative. For reporting purposes a negative sign has to be replaced to a positive one.

Steps: 1) Type “-1” in cell E2, 2)     Copy “-1” (Ctrl C), 3)     Highlight area to be updated (D3:D9), 4)     Click “Paste Special” and select “multiply” or “divide” in “Operation” group. Click “OK”.

To increase range of Sales values by 5% rate, just replace -1, to -1.05 and select “paste special” – “multiply”.

Also, if the sales data is a formula result, just apply “Paste Formulas”, “Multiply” and formulas in column F will be multiplied by 1.05:

2). Transpose is another great feature. Using this tool you can quickly rearrange table below to show “month” as a column header and “product” as a row header:

- Copy range B3:E6, move your cursor to cell B8, copy (Ctrl C), and click Paste special -> Values -> Transpose. Note: for simplicity I selected “values”, but, if formulas need to be kept, just select “formulas”. This works well too:

So, now if you would like to copy the table format, then just repeat the same steps but instead of values select “Format.”

3). Paste Link is a quick way to link data source to your active worksheet. It links the pasted data on the active worksheet with the copied data.

For instance, you need to add sales summary table from other workbook or from other tab into a Dashboard report and you want it linked. Any updates to the summary table will change Dashboard report:

Steps:

1. Highlight and copy Sales Summary table (Ctrl C)
2. Open Dashboard and click the cell you want to paste into the report
3. Click Paste Special -> Paste Link

4. “Paste Linked Picture” is another great way to paste summary table into Dashboard. Copy result table -> right mouse click anywhere in your Dashboard and select “paste linked picture”. Note: “linked picture” can be resized. It is updatable too as soon as linked data source updated. This option is really great for KPI reports and Dashboards.

I hope you will find this article useful.