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.
You can simplify the creation of some formulas by using entire column or row references when appropriate. In this list of over 700 names, we might want to know the Total Salary. So, right here in column O, next to the phrase Total Salary, let's do a total here for the Salaries out of column J. Now, the AutoSum button, which is found in two locations, on the Formulas tab in the Function Library left-hand side or on the Home tab, extreme right side here in the Editing group. AutoSum, let's click this.
AutoSum always makes a guess here. It makes a bad guess. We'll simply ignore it and click column J and at the indicator J:J. We're done here; there is the answer. Fast, easy, easy to read. We didn't have to drag across or type in any complex cell references. And similarly with Average Salary, take advantage of the fact that AutoSum has a drop arrow to the right of it, let's choose Average. Same idea here. It makes a bad guess. We'll click column J. We're all set. And one more here. Average Job Rating, go to the AutoSum button, click the drop arrow, choose Average, this time column K. A bit too many decimals there in the display probably.
Let's hide some of those. Easy way here probably. On the Home tab, click Comma Style, and we probably wouldn't even want two decimals, so let's, there we go, show only one. It makes good sense. Now, occasionally you might use a row reference. Here's an example here not nearly as common. This one's already in place. This is just doing what it's simply adding up the data from row 12. It looks weird at first because somehow you're trying to figure out how it's adding 12, so it's not adding 12s at all. It's simply a reference to row number 12.
But using this for a column makes good sense. Now, certain functions might need to use a column reference as well and if we'd like to tabulate how many people work in this organization who are of a certain status, for example full time people, the function question here is Countif. Countif first in a sense asks us where we are looking. We're looking at that data in column F and rather than highlighting F2 down to F750 or whatever or typing in F2 colon whatever, let's just click column F.
Comma, and the criterion that we're looking for here is full time. We could type it in within double quotes if we wish, but since it's already set up here we'll just click right here, and we're all done, and we could double-click to copy this down, and we're all set. 54 hourly people, 198 contract people and so on. A companion to Countif is Sumif, and there's also a function called Averageif. We might want Salary Totals here, we might want Salary Averages, and we can do all these at once too.
Equal Sumif. We could have done this with Countif in the same way, but the Sumif this time Sumif left parentheses, we're looking in column F, again comma, and we want to get the status, it's Full Time, but the Sumif function has three arguments to it and once we find the full time people, then we want to go into the corresponding column J to pickup the salaries. Now, since I've got four cells highlighted here, I don't want to simply press Enter but press Ctrl+Enter, and we've got answers for all four of them and here too a quick fix on the display might be click the Comma button in the Number group on the Home tab. Probably don't need to see the decimals, and we're all set.
So, different uses of writing formulas here using entire column references. It makes the formulas easier to create and easier to read.
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.