Start learning with our library of video tutorials taught by experts. Get started
Viewed by members. in countries. members currently watching.
In Excel 2010 Power Shortcuts, Excel expert Dennis Taylor shares tips and shortcuts to vastly increase efficiency and get the full power out of Excel 2010. There are tips for working with the Ribbon and Quick Access toolbar, navigating workbooks and selecting cells, rapid data entry and editing, working with formulas, formatting data, working with charts, sorting data, and much more. Exercise files accompany the course.
If you are trying to round data to the nearest whole number or perhaps to the nearest dollar if its currency type data, don't make the mistake of assuming that formatting buttons on the Home tab do the job. For example, maybe we haven't copied these formulas yet in column I, but this first result here, this formula, quick look at it, is taking a previous salary and incrementing it by 4.43%. If you say we really don't need the pennies, you could in the Number group on the Home tab here simply show fewer decimals.
Notice I said show fewer decimals. And look what happens. Now the value has not been changed into 37,265. The 80 cents that we saw previously is still there. This did not really go up 20 cents. We have simply changed the look of it and the overall scheme of things on the yearly salary, you might say "well who cares? 20 cents," and that's probably true. But let's show what might happen in different situation here. In column C, here is the similar kind of functions, same kind of math and everything, but it relates to a pricing item.
The previous price of this item was $8 and 62 cents. We are increasing it by 4.37%. All of these other items too. We come up with an answer of $9. Looks great. That's fine. That's the way we are seeing it. Imagine for the moment this is a box of pens or rubber bands and whatever and somebody orders 10,000 of them. So just a simple little formula here to take this amount times 10000. You can do the math in your head. It's going to be 90,000 right? Well not quite, but it's what? $33 and 6 cents off? Who cares? Well somebody does care and the money has to be accounted for somehow and so when you do run in to situations like this, what you will need is the Round function.
Now there are lots of variations and the point of this is not to show you all the variations, but just to give you some inside into it and point out a couple of things and some shortcuts coming up here. What we should do in this example here is truly round this to either the nearest penny or the nearest dollar depending on the circumstances. I don't really have to make the column wider, but I want to do this so that we keep this in view. I am going to alter the formula by putting a function Round here at the beginning, and you can round a calculation to the nearest decimal point or even to the left of the decimal.
If we want to round this to nearest penny, we put in a ,2 meaning two decimal places. Now as I press Enter here, the $9 that we saw earlier we are still going to see, but now that truly will be $9. Watch the calculation on the right change. So that really did change. So let me step back a little bit with Ctrl+Z and show what this number really had been. All along, even though it looked like $9, I am going to show more decimals. That original calculation really came up with this, and so you could have made the mistake possibly of applying a formatting or simply having the format ahead of time and say gee, this is $9.
So you go to be careful with this. Don't use these buttons to change values because they don't. They simply change the display, and as I click these buttons left and right you will notice that the calculation in column D is not changing at all. It's still the same. So, reverting here and going the other direction, what we want to be able to do here is simply again just again quickly round this to the nearest two decimals, and that's going to take care of the situation.
Now getting back over here in to the salaries, sometimes with a yearly salary you could imagine easily saying that it would make sense here to have the yearly salaries be a whole number to the current dollar and sure enough, the best choice I think for a lot of peoplw would be here just put a zero and meaning to the nearest whole number, zero decimal places. And we see the result here. Now that truly is 37265. We have altered it.
Some times you might want to round to values that are not multiples of ten. I have seen salaries that when divided by 26 or 24, in other words either a bi-weekly or bi-monthly kind of system or even a monthly system, the salaries are stated as whole numbers. Well, we could actually make them whole numbers and a not so obvious choice here would be to use a function called mROUND, and we can round this to the nearest multiple of for example 12. If we do this whatever salary we come up with will be to the nearest $12 and that means that if divided by 12, the monthly salary would be totally even.
If this is a bi-weekly, get paid every two weeks system, we might want to round this to the nearest 26th or $26 here, and that's evenly divisible by 26. So some unusual things to do there, but the key points here are that there will be times when you truly want to round results and not simply change their formats by using buttons on the Home tab.
Find answers to the most frequently asked questions about Excel 2010 Power Shortcuts.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.