Rose Business Solutions Dynamics GP

Good day to all, here is another post that comes from working with budgets and excel spreadsheets.

Recently I had a case where the customer was trying to import budgets into Dynamics GP. All spreadsheets seemed normal, they uploaded correctly and we were under the impression that everything was ok for all the years we imported.

But to our surprise the customer called in and said “It appears that the sign is reversed, we verified and we entered the budgets with the wrong sign instead of (52,250.00) should be 52,250.00 can you change it? And by the way we deleted the budgets can you import again?.

Ok so my coworker started adding columns to the left and multiplying the numbers by –1 to reverse the sign, I saw that this process was a little painful and slow considering there were many spreadsheets to work on.

So here is how I helped them out. The following is a made up sample of the budget sheet

I take an empty cell, and type –1, then I select that cell and copy the value to clipboard or I press Ctrl-C

After I have the value copied I select the range of values to change. Notice that the copied cell is still marked.

Then I right click on the selection and choose Paste Special

The paste special window has a section called operations, this section is able to Add, Subtract, Multiply and Divide, so I select Multiply

And after selecting the operation I click ok and my values are updated with the reversed sign.

Just imagine the potential and time saving opportunity here, if you want to subtract a fixed value to all columns or add a fixed value, this makes it relatively easy and fast.

Another example will be when someone asks you to take the numbers from last year and add 25% its so simple to just add a 1.25 to a cell, copy the value, select your range and select multiply. Try it with variations and you will experience this hidden gem from Excel.

I would like to point out that its not just for budgets but for many tasks in real life. Why I added to a Dynamics GP blog? because it originated from working with the budgets.

Have a nice day and until my next post.

Francisco G. Hillyer

2 Responses to “Excel–Changing Values and reversing sign”