Microsoft Dynamics Blog

Welcome to Armanino’s Microsoft Dynamics Blog, where you’ll find information on topics across Microsoft Dynamics 365 ERP and CRM, Dynamics GP, Power BI, PowerApps, Flow and more. Use these tips, tricks and insights to get the most out of your applications! Want these articles delivered straight to your inbox? Subscribe to our newsletter below.

Thursday, September 20, 2012

Excel–Changing Values and reversing sign

Posted by Armanino Dynamics Team

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 theMe 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 sheetEX1

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

EX2

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

EX3

Then I right click on the selection and choose Paste Special

EX4

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

EX5

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

EX6

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.

 

COMMENTS

comments powered by Disqus
« | »