Converting signage in Excel

Hey there Exceptional Excel readers, in todays blog I want to give you all a time saving shortcut.

Let me give you a scenario…You receive some data from someone in your business or perhaps just get a data dump out of a system, but you notice a problem the signage of the numbers are shown the wrong way round.

We’ve all been there at some point, frantically bashing away at the keyboard trying to remove or insert minus signs as quick as you can, in order to get on with your calculation or reports.

Well cleverly in Excel there is an easier way, using the Paste Special function.

Here’s the trick:

  • In any free cell in your workbook input the value -1.
  • Now copy this cell

Screen Shot 2016-06-13 at 13.06.45 (alternatively you can use Ctrl+C Windows or Cmd+C Mac).

  • ¬†Select the range of data you wish to convert.

Screen Shot 2016-06-13 at 13.12.00

  • right click and go to the Paste Special menu.

Screen Shot 2016-06-13 at 13.13.43

  • In this scenario simply check the multiply option.

Screen Shot 2016-06-13 at 13.19.09

Now as you may have guessed what excel will do is simply multiply your selected range by the value of the copied cell.

  • Hit ok and you will see all are switched. Success!

Screen Shot 2016-06-13 at 13.20.35

  • you can now delete the -1 that you initially entered into an empty cell.

This is a quick tip that can save people a lot of time and that’s not all, many people forget the volume of options on this menu and subsequently miss out on its time saving actions.

Take a second and go back to look at the example of a Paste Special menu. Notice there are 4 functional operations Add, Subtract, Divide and Multiply.

Other uses

  • Divide – the divide option can also be a great time saver used in much the same way you can copy a cell value of 1,000 for instance and divide your range to quickly round a set of numbers to the nearest thousand.

 

Take some time to look thoroughly at the paste special menu and give some of these operations a go. This menu really is due far more credit than it is sometimes given. We will go over all the functionalities in our future online courses. To keep up to date with our courses and all future blog tips please sign up to our newsletter.