Skip to main content

10 basic Excel tricks that make life easy



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



2. Jump to the end of a data range or the next data range with Ctrl + Arrow


3. Add the Shift key to select data


4. Double click to copy down


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)

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)



http://www.businessinsider.in/Here-are-11-basic-Excel-tricks-that-will-change-your-life/articleshow/46448077.cms

Comments

Popular posts from this blog

Future of oil is bleak. By 2030, 95% of people may not own private cars which would wipe off the automobile industry

A futurist and clean energy expert, Toni Seba, has predicted that electric vehicles would destroy the global oil industry after a decade. By 2030, 95% of people won't own private cars which would wipe off the automobile industry, he says.

Boeing and JetBlue Airways have announced they would begin selling a hybrid-electric commuter aircraft by 2022. Planned by start-up Zunum Aero, the small plane would seat up to 12 passengers and reduce travel time and cost of trips under 1,600 km.

Ref http://auto.economictimes.indiatimes.com/amp/news/oil-and-lubes/the-future-of-oil-is-almost-here-and-it-doesnt-look-very-pretty/60972841

Can Herbalife 'Afresh' cause insomnia(sleeplessness) and heart problems?

Here is another "great" product from Herbalife. Marketed as an ENERGY drink mix. Few people know it contains Gurana seeds which have no active compound giving artificial energy other than caffeine. Afresh also contains additional caffeine

Ingredients of Herbalife Afresh Energy Drink Mix:
Maltodextrin, Orange Pekoe Extract, Guarana Seed Extract, Acidity Regulator - 330 and Caffeine Powder.

http://mall.coimbatore.com/bnh/herbalife/afresh-energy-drink-mix.htm

http://products.herbalife.co.in/energy-and-fitness/afresh-energy-drink

Side effect include insomnia, sleeplessness and heart problems, It is especially harmful for people with High blood pressure.

http://www.medicinenet.com/caffeine_tablets-oral/article.htm

PPF interest rate cut to 7.9% but are other investment options better? Here's a comparison

The Public Provident Fund (PPF) will now offer 7.9% but experts say it is still a good option for investors. Given that consumer inflation is down to 3.65%, the real rate of return of the PPF is a healthy 4.25%. 

"This is quite impressive for an option that offers assured returns," says Amol Joshi, Founder, PlanRupee Investment Service. "Investors should continue to take advantage of this long-term tax-free product," he adds. 

Even if you compare the PPF rate with the 10-year government bond yield, the scheme is attractive. "The 10-year bond yield is a better benchmark for PPF than consumer inflation," says Manoj Nagpal, CEO, Outlook Asia Capital
Currently, the 10-year bond yield is around 6.8% and the PPF at 7.9% makes it for a premium of 110 basis points. "Historically, the average premium has been around 75 bps. So, the PPF investor is today earning a higher real return," says Nagpal. Even so, some investors may be feeling disappointed by the cu…