10 Amazing Excel tricks that will change your life!!

image_pdf

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 info@amazingexcel.com.

1. Jump from worksheet to worksheet with Ctrl + PgDn and Ctrl + PgUp

 

WorksheetsXX

 

 

 

 

 

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!!

NavigatingX

 

 

 

 

 

 

 

 

 

 

 

 

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:

SelectX
 

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:

DoubleClick

 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:

FormatingX

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.

F4

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. 

=COUNTIF( range, criteria )

=COUNTIFS( criteria_range1, criteria1, [criteria_range2, criteria2], … )

101614_1553_HowtoUSECOU4.png

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.

SUMIF(range,criteria,sum range)

sumif1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Learn more:How to use SUM, SUMIF, SUMIFS functions in excel?

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) 

Vlookup 

Learn VLOOKUP Basics here
9. Use & to combine text string
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:

ConcatenateX

10. Clean up text with LEFT, RIGHT and LEN

The LEN function in EXCEL is used to return the length of the text string.

len1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

You can use LEFT function when you want to extract some characters from leftmost part of string.

LEFT(text string, number of characters)

LEft+len

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)

Right+Len

How to remove first,last or certain characters from text using LEN function?

If you find this useful, you can stay connected with us on facebook to get more amazing tips and tricks of Microsoft excel.

Image Source:www.Businessinsider.in

 

10,008 total views, 8 views today

2 thoughts on “10 Amazing Excel tricks that will change your life!!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>