Here is a fun way to use Paste Special to quickly multiply everything in a range with 1.1 (why 1.1? Well, imagine you have a report with everything in US $s and your boss wants to see the numbers in Australian $s…)
But your report looks like this:
And the total column has different formulas for each row. So you can’t multiply first cell with a rate variable and drag it down. You have to manually edit each formula and add *rate at the end of it.
Oh wait…, you can use Paste Special.
Simple, use below steps:
- Write the exchange rate in a cell. Name it as rate (or whatever you fancy).
- In a blank cell write =rate
- Copy this blank cell.
- Select the range of numbers which you want to multiply with rate
- TIP: if the numbers are not together, hold CTRL and multi-select the ranges.
- Press CTRL + ALT + V to launch paste special dialog
- Select Multiply as operation type
- Select Formulas as paste type
- Click ok
- Your multiplication is done!
- When the rate changes, simply change the original rate cell
Here is a quick demo of this process. Watch it to learn more.
Do you use Paste Special operations?
I use the operations feature of paste special from time to time. But I never thought it would actually write formulas when multiplying with a cell having formula. This is really cool and could save time in some very tricky situations.
What about you? Do you use Paste Special operations feature? When do you use it? Please share your tips & experiences in the comments area.
More tips on Paste Special
If you think pasting is all about CTRL+V, you are obviously missing out on many time saving features of Excel.
Check out below articles for many cools ways to save time when copy pasting things in Excel.
8 Responses to “Introducing PHD Sparkline Maker – Dead Simple way to Create Excel Sparklines”
This looks like it could be very useful for a project I'm putting together right now, thank you so much. Quick & silly question, how do I copy & paste the sparkline as a picture?
Question answered. For anyone else:
Select chart>Hold Shift key & select Edit/Copy Picture>Paste
[...] more information about PHD Sparkline Maker, please read this article and to learn more about Sparklines, read this article from Microsoft Excel 2010 blog. Also there [...]
Am I right in thinking that the y-axis is set automatically by excel?
That makes it possible to get the column chart not to start at zero.
Andy - yes, it is currently set to 'auto', which defaults to a zero base for positive values, but you can change that by left-clicking the chart, then choosing (in Excel 2007):
"Chart Tools/Layout/Axes/Primary Vertical Axis/More Primary Vertical Axis Options"
PUBLIC SERVICE ANNOUNCEMENT: When manually editing a chart's minimum/maximum axis values, PLEASE be sure there's a valid reason and that doing so won't skew the message shown by the data (e.g. by exaggerating differences). If in doubt, go back and read Tufte. (W.W.T.D.?)
[...] gridlines, axis, legend, titles, labels etc.) and resize it so that it fits nicely in a cell [example]. This is the easiest and cleanest way to get sparklines in earlier versions of excel. However this [...]
thanks for the work creating the template!!!!
looks good