Microsoft Excel has amazing features and shortcuts. In this article, we are presenting 10 basic but most useful shortcuts and functions that absolutely everyone need to know to become Excellent in Excel.
Have a look at it and if you have any query you can mail us at firstname.lastname@example.org.
1. Jump from worksheet to worksheet with Ctrl + PgDn and Ctrl + PgUp
Related article: How to edit multiple worksheets at the same time?
2. Jump to the end of a data range or the next data range with Ctrl + Arrow
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.Simple!!
Very Important: Useful Excel Tips for evaluation of formulas
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:
Want to speed-up your work?? Check this out : 10 Best Tips to Format your data quickly
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 and CounIFs 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.
See more examples here:How to Use COUNT ,COUNTIF, COUNTIFS Function?
Same way Sumif function is used to do summation of specific range with criteria.
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.
VLOOKUP(lookup value,data range,column number,type)
10. Clean up text with LEFT, RIGHT and LEN
The LEN function in EXCEL is used to return the length of the text string.
You can use LEFT function when you want to extract some characters from leftmost part of string.
LEFT(text string, number of characters)
If you want to pull the numerical values 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.
RIGHT(text string,number of characters)
11,238 total views, 6 views today