Excel Tip: Copy and Paste - avoiding unwanted baggage

First of all, it was great to see those of you that made it to last week's launch event for the ICAEW's Twenty Principles for Good Spreadsheet Practice. I hope to have a video of some of the highlights of the event to show you soon.

This week, I want to highlight an approach to copying and pasting that means you only bring across what you want, and don't bring across any nasty surprises for later.

Have you ever opened a spreadsheet and been asked if you want to update links that you didn't think were there?

...or ended up with a selection of different font sizes and formats in a range of data?

These are both signs of unwanted baggage dragged across during a copy and paste exercise, but can be easily avoided.

If you simply copy and paste, this brings across all formats and formulae from the source cells along with the contents, causing the two problems highlighted above.

Where the source cells have formulae that make reference to cells on other tabs of the source workbook, the pasted formulae will include external links back to the source spreadsheet. This causes the prompts about external links mentioned above. And, unless this is what was intended, a far greater problem is that the content of these cells will now update with changes to the source spreadsheet!

The multiple formats in a spreadsheet tend to come from pasting data from various sources with different formats.

Both can be easily avoided by some of the Paste Special options.

You can choose to bring over just the contents of the cells (no formats or formulae) by selecting to Paste Values , indicated by the clipboard showing the numbers 123. Note that the results of formulae are copied across as if they had been entered in the cell.

If you do want the formulae to be copied across, but not the formats, you can select Paste Formulas (the clipboard with fx on it).

You can also copy just the formats (leaving existing cell contents intact) by selecting Paste Formats (the clipboard with  a percentage sign and a paintbrush).

Finally, if you want to copy the cell contents and formats (but leave the formulae behind), you can select Paste Values and then Paste Formats.

There, you can now travel light when you're copying and pasting!



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Pump up the volume on your data entry screens with a simple slider control

Sometimes you have a spreadsheet that you want to look good and feel interactive, but might only have a few inputs.

A good example of this might be a loan illustration. You might want to experiment with different loan amounts, and terms for example.

You can of course just have an input cell for each of the variables (loan amount and term for example). But if you want to really jazz up the spreadsheet, what about interactive slider controls to enter the values, with the illustration responding to your movement of the sliders?

This is much easier than it sounds.

First of all, you will need to check that you have the Developer ribbon showing. If you do not have a ribbon called Developer, you can add it by clicking File, Options, Customize Ribbon and ticking Developer in the right hand column (Main Tabs).

Now go to the Developer Ribbon and click Insert (it has a picture of a toolbox above it) and you will see the following options:

Select the option I have circled above and the cursor becomes a cross-hair for you to draw the rectangle that will become your slider.

Once you have drawn it, right-click on it and select Format Control.

The screen above has all of the settings you need to make your slider work. The settings above could be for selection of Loan Amount in the example earlier. I will go through each in turn:

Current value - This is simply the value you want the slider to be set at before it is moved
Minimum value - The value when the slider is at the extreme left
Maximum value - The value when the slider is at the extreme right
Incremental change - How much you want the value to increase/decrease by when you click the arrow at either end
Page change - How much you want the value to increase/decrease by when you click the space between the selector and the arrow at either end
Cell link - The cell that you want the slider to link to. This will be the Loan Amount variable in the example described. In this case cell C7 will  show the value selected on the slider, and if you edit cell C7, the slider will move.

Have a play and see what you think.



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Let's have a drink and talk spreadsheets

Back in November, I told you about a project I had been working on as part of a team within the ICAEW IT Faculty to produce a set of Twenty Principles for Good Spreadsheet Practice.

My original article and link to the draft principles can be found here.

Well the final version is ready and I wondered if any of you could make it to the launch event.

The launch event is being held at Chartered Accountants Hall, London, EC2R 6EA on 17th June 2014 at 5:30pm, followed by a drinks reception at 6:30pm.

Speakers include Microsoft's UK Head of Technology for Partners who should give us an interesting insight into Microsoft's future plans for Excel.

To book your place, you can either book online (you don't need to be a member but you will need to register on the ICAEW web site), or contact the events team on events@icaew.com or +44 (0)1908 248159.

It would be great to see you there if you are in or around London next Tuesday. I will be around afterwards for a drink too and would love to meet any of you who can make it.


If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

Excel Tip: Referring to cells based on the contents of other cells

Obviously, one of the most wonderful things about Excel is that you can refer to the contents of any cell by reference to its sheet, column and location and use those contents in a formula.

But what if you could store the row, column or sheet information in other cells that you could refer to to create the reference?

Well, you can, and, if you've lost me, I'll tell you why you might want to!

The Excel function we can use here is INDIRECT.

This function asks Excel to treat the text between the brackets as a reference. To illustrate what I mean, let us assume that cell A1 contains the number 5 and cell A2 contains the text A1.

If in cell A3, we type the formula:

=A2

cell A3 will show the text A1 (i.e. the contents of cell A2)

If, however, we type the following into cell A3:

=INDIRECT(A2)

cell A3 will now show the number 5.

This is because we have told Excel not to return the contents of cell A2, but to treat those contents as a reference. So because A2 contains the text A1, the function now returns the contents of cell A1, i.e. the number 5!

This is most useful if you wish to build up a reference using text. For a refresher on manipulating text you might want to visit my earlier post:

Excel Tip: Manipulating text in Microsoft Excel

For example, we might want a formula to return the cell A1 from a particular tab, specified in another cell. So, say, cell B1 contains a drop-down of sheet names, our formula would be:

=INDIRECT(B1&"!A1")

This takes the sheet name from cell B1 and adds the text !A1 on the end and then uses the INDIRECT function to treat the result as a reference, so if cell B1 contained Sheet2, then the reference would be Sheet2!A1.

This needs to be slightly more complicated if the sheet name could have spaces in it. When a sheet name has spaces, Excel requires it to be enclosed in single quotes, so Sheet2 is OK, but if you want a space, it must be 'Sheet 2'. Excel will be happy with or without the single quotes when there is no space, so it is safer to include them. The formula would then be:

=INDIRECT("'"&B1&"'!A1")

There are many uses for INDIRECT, but I find manipulating the sheet name in this way to be one of the most useful.



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".