Even if you use Microsoft Excel regularly, there’s a fair chance you might not be getting as much out of it as you can. Improve your Excel efficiency and proficiency with these basic shortcuts and functions that absolutely everyone needs to know.
#1 Jump from worksheet to worksheet:
Ctrl + PgDn and Ctrl + PgUp. Simple!
#2 Jump to the end of a data range or the next data range:
Ctrl + Arrow. Of course you can move from cell to cell with arrow keys. But if you want to get around faster, hold down the Ctrl key and hit the arrow keys to get farther.
#3 Add the Shift key to select data:
Ctrl + Shift +Arrow will extend the current selection to the last nonblank cell in that direction.
#4 Double click to copy down:
To copy a formula or value down the length of your data set, you don’t need to hold and drag the mouse all the way down. Just double click the tiny box at the bottom right-hand corner of the cell:
#5 Use shortcuts to quickly format values:
For a number with two decimal points, use Ctrl + Shift + !. For dollars use Ctrl + Shift + $. For percentages it’s Ctrl + Shift + %. The last two should be pretty easy to remember.
#6 Lock cells with F4:
When copying formulas in Excel, sometimes you want your input cells to move with your formulas BUT SOMETIMES YOU DON’T. When you want to lock one of your inputs you need to put dollar signs before the column letter and row number. Typing in the dollar signs is insane and a huge waste of time. Instead, after you select your cell, hit F4 to insert the dollar signs and lock the cell. If you continue to hit the F4 key, it will cycle through different options: lock cell, lock row number, lock column letter, no lock.
#7 Summarize data with CountIF and SumIF:
CountIF will count the number of times a value appears in a selected range. The first input is the range of values you want to count in. The second input is the criteria, or particular value, you are looking for. Below we are counting the number of stories in column B written by the selected author:
COUNTIF(range,criteria)
SumIF will add up values in a range when the value in a corresponding range matches your criteria. Here we want to count the total number of views for each author. Our sum range is different from the range with the authors’ names, but the two ranges are the same size. We are adding up the number of views in column E when the author name in column B matches the selected name.
SUMIF(range,criteria,sum range)
#8 Pull out the exact data you want with VLOOKUP
VLOOKUP looks for a value in the leftmost column of a data range and will return any value to the right of it. Here we have a list of law schools with school rankings in the first column. We want to use VLOOKUP to create a list of the top 5 ranked schools.
VLOOKUP(lookup value,data range,column number,type)
The first input is the lookup value. Here we use the ranking we want to find. The second input is the data range that contains the values we are looking up in the leftmost column and the information we’re trying to get in the columns to the right. The third input is the column number of the value you want to return.
We want the school name, and this is in the second column of our data range. The last input tells Excel if you want an exact match or an approximate match. For an exact match write FALSE or 0.
#9 Use & to combine text strings
Here we have a column of first names and last names. We can create a column with full names by using &. In Excel, & joins together two or more pieces of text. Don’t forget to put a space between the names. Your formula will look like this =[First Name]&” “&[Last Name]. You can mix cell references with actual text as long as the text you want to include is surrounded by quotes:
#10 Clean up text with LEFT, RIGHT and LEN
These text formulas are great for cleaning up data. Here we have state abbreviations combined with state names with a dash in between. We can use the LEFT function to return the state abbreviation. LEFT grabs a specified number of characters from the start of a text string. The first input is the text string. The second input is the number of characters you want. In our case, we want the first two characters:
LEFT(text string, number of characters)
If you want to pull the names of the states out of this text string you have to use the RIGHT function. RIGHT grabs a number of characters from the right end of a text string.
But how many characters on the right do you want? All but three, since the state names all come after the state’s two-letter abbreviation and a dash. This is where LEN comes in handy. LEN will count the number of characters or length of the text string.
LEN(text string)
Now you can use a combination of RIGHT and LEN to pull out the state names. Since we want all but the first three characters, we take the length of our string, subtract 3, and pull that many characters from the right end of the string:
RIGHT(text string,number of characters)
#11 Generate random values with RAND
You can use RAND() function to generate a random value between 0 and 1. D0 not include any inputs, just leave the parentheses empty. New random values will be generated every time the workbook recalculates. You can force it to recalculate by hitting F9. But be careful. It also recalculates when you make other changes to the workbook.
This article originally appeared on Business Insider.
Comments
22 responses to “11 Excel Tricks And Shortcuts For Spreadsheet Ninjas”
Why use RAND() when RANDBETWEEN(min,max) has existed since excel 2007?
Also, true excel ninjas know the limits of VLOOKUP and are familiar with INDEX(MATCH)
Care to elaborate? I’d love to know!
http://www.randomwok.com/excel/how-to-use-index-match/
EDIT:
The main benefit I find of INDEX MATCH is that you can work backwards through a range.
VLOOKUP always have to have the search field as the first column in the range.
INDEX MATCH is more flexible, and can work with multiple separate ranges.
In my experience INDEX MATCH is also a boatload less resource hungry than V/HLOOKUP, definitely worthwhile if your index is thousands of records long!
This:
=INDEX ( Column I want a return value from , MATCH ( My Lookup Value , Column I want to Lookup against , Enter “0” ))
A quick shortcut for matching many cells, like a vlookup on multiple parameters, when you only have 1 or none valid options in your data set, you can use sumifs() instead of vlookup. It will let you lookup multiple values in multiple ranges, and return a single value (the sum of 1 member, will just be that member).
I’m totally going to use that.
Don’t forget, only works with numbers.
Combine text with CONCATENATE; & is so ugly.
but CONCATENATE is so much more time consuming to implement
Not to mention the CONCATENATE is the uglier option… And is less user friendly..,
I’m not sure I agree.
I can’t stand using CONCATENATE, but if you look at it in terms of ugliness, you have the following two possibilities:
Desired Output: “The quick brown fox jumps over the lazy dog.”
=CONCATENATE(“The “,”quick “,”brown “,”fox “,”jumps “,”over “,”the “,”lazy “,”dog.”)
=”The “&”quick “&”brown “&”fox “&”jumps “&”over “&”the “&”lazy “&”dog.”
I certainly prefer ampersands, but if you really don’t like seeing ampersands, i guess concatenate is the way to go?
Force a stubborn number (e.g 34) to be treated like a number by adding +0. Sometimes Excel refuses to have number outputs treated like numbers. It will drive you crazy until you figure out that ‘+0’ will short circuit this.
Another way is use text to column on a single column of values to force into number format.
if it’s a whole number, then a more correct way to do it would be using =INT(34) rather than =34+0
OK. Cheers.
If you are frequently resizing the Excel window, and one of you worksheet tabs holds a chart, make sure you can always see the whole chart.
Select “View” then ” Sized With Window”.
[warning: this feature was removed, to make way for the Ribbon, back in 2007. This is why I STILL use Excel 2003 at home.]
The other option to copy values down a range is use Ctrl + D. If you are in a single cell, it will copy the value/formula from the cell above. If you have a range selected, the top row of values/formulae are copies down.
Ctrl + R does the same thing to the right (rather than down).
I was going to say the same thing, i Ctrl Down on a column that goes to the last row (usually the column next to it) move to column i want, ctrl up to the cell to fill down on, ctrl d – nice and fluid.
Related to this there’s also Ctrl + Enter to input a value into multiple cells. Highlight the cells you want, enter the value and press Ctrl + Enter. The value is entered into the highlighted cells.
If you’ve got filtered results or hidden cells, Select only visible cells before copying by pressing: