How To Get Promotion by sharpening your Excel Skills in just 14 days.

95 Excel Tips & Tricks

To make you awesome at work

by Samir Oke

What is in this book?

 Excel Keyboard Shortcuts

 Excel Formulas for Everyday Situations

 Day to Day Excel Usage

 Excel Charting Tips & Tweaks

 Follow These 6 Steps for Better Chart Formatting

 Know these 15 Powerful Excel Formulas

 Know How to Paste Your Data – 7 Tricks

1
2
 Day to Day Excel Usage

1. To format a number as SSN, use the custom format code “000-00-0000″…

2. To format a phone number, use the custom format code “000-000-0000″…
3. To show values after decimal point only when number is less than one, use
[<1]_($#,##0.00_);_($#,##0_) as formatting code
4. To hide a worksheet, go to menu > format > sheet > hide…
5. To align multiple objects, like charts, drawings, pictures use drawing toolbar > align
and select alignment option…
6. To disable annoying formula errors, go to menu > tools > options > error checking
tab and disable errors you don’t want to see…
7. To transpose a range of cells, copy the cells, go to empty area, and press alt+e+s+e…
8. To save data filter settings so that you can reuse them again, use custom views…
9. To select all formulas, press CTRL+G, select “special” and check “formulas”
10. To select all constants, press CTRL+G, select “special” and check “constants”
11. To clear formats from a range, select menu > edit > clear > “formats”
12. To move a chart and align it with cells, hold down ALT key while moving the chart

Excel Charting Tips & Tweaks

1. To create an instant micro-chart from your normal chart, use camera tool…

2. Understand data to ink ratio to reduce chart junk, using even a pixel more of ink
than what is needed can reduce your chart’s effectiveness
3. Combine two different types of charts when one is not enough, to use, add another
series of data to your sheet and then right click on it and change the chart type
4. To reverse the order of items in a bar / column chart, just click on y-axis, press
ctrl+1, and check “categories in reverse order” and “x-axis crosses at maximum
category” options
5. To change the marker symbol or bubble in a chart to your own favorite shape,
just draw any shape in worksheet using drawing toolbar, then copy it by pressing

ctrl+c, now go to the chart and select markers (or bubbles) and press ctrl+v
6. To create partially overlapped column / bar charts just use overlap and gap
settings in the format data series area. A overlap of 100 will completely overlap one
series on another, while 0 separates them completely.…
7. To increase the contrast of your chart, just remove grayish background color that

3
 excel adds to the chart (in versions excel 2003 and prior)
8. To save yourself some trouble, always try to avoid charts like - 3D area charts (un-
stacked), radar charts, 3D Lines, 3D Columns with multiple series of data, Donut
charts with more than 2 series of data…
9. To improve comparison, replace your radar charts with tables…

4
 Excel Formulas for Everyday Situations

1. To get the first name of a person, use =left(name, find(“ “,name)-1)

2. To calculate mortgage payments, use =PMT(interest-rate, number-of-

payments, how-much-loan)

3. To get nth largest number in a range, use =large(range, n)… Get Full Tip

4. To get nth smallest number in a range, use = small(range, n)… Get Full

Tip

5. To generate a random phone number, use

=randbetween(1000000000,9999999999), needs analysis tool pack if you

are using excel 2003 or earlier… Get Full Tip

6. To count number of words in a cell, use =len(trim(text))-

len(SUBSTITUTE(trim(text),” “,”"))… Get Full Tip

7. To count positive values in a range, use =countif(range,”>0″)… Get Full

Tip

8. To calculate weighted average, use SUMPRODUCT() function

9. To remove unnecessary spaces, use =trim(text)

10. To format a number as SSN using formulas, use =text(ssn-text,”000-00-

0000″)… Get Full Tip

11. To find age of a person based on DOB, use =TEXT((NOW()-

birth_date)&”",”yy “”years”" m “”months”" dd “”days”"”),

output will be like 27 years 7 months 29 days

12. To get name from initials from a name, use IF(), FIND(), LEN() and

SUBSTITUTE() formulas… Get Full Tip

13. To get proper fraction from a number (for eg 1/3 from 6/18), use

=text(fraction, “?/?”)

14. To get partial matches in vlookup, use * operator like this:

5
 =vlookup(”abc*”,lookup_range,return_column)

15. To simulate averageif() in earlier versions of excel, use =sumif(range,

criteria)/countif(range, criteria)

16. To debug your formulas, select the portions of formula and press F9 to see the result

of that portion…

17. To get the file extension from a file name, use =right(filename,3) (doesn’t

work for files that have weird extensions like .docx, .htaccess etc.)

18. To quickly insert an in cell micro-chart, use REPT() function…

19. COUNT() only counts number of cells with numbers in them, if you want to count

number of cells with anything in them, use COUNTA()

20. Using named ranges in formulas saves you a lot of time. To define one, just select

some cells, and go to menu > insert > named ranges > define

Know these 15 Powerful Excel Formulas

Excel formulas can always be very handy, especially when you are stuck with data and

need to get something done fast. But how well do you know the spreadsheet formulas?

Discover these 15 extremely powerful excel formulas and save a ton of time next

time you open that spreadsheet.

1. Change the case of cell contents - to UPPER, lower, Proper

Boss wants a report of top 100 customers, thankfully you have the data, but the

customer names are all in lower cases. Fret not, you can Proper Case cell contents

with proper() formula.

Example: Use proper("become awesome in excel") to get Become Awesome In

Excel

Also try lower() and upper() as well to change excel cell value to lower and

UPPER case

2. Clean up textual data with trim, remove trailing spaces

6
 Often when you copy data from other sources, you are bound to get lots of empty

spaces next to each cell value. You can clean up cell contents with trim() spreadsheet

function.

Example: Use trim(" copied data ") to get copied data

3. Extract characters from left, right or center of a given text

Need the first 5 numbers of that SSN or area code from that phone number? You can

command excel to do that with left() function.

Example: Use left("Hi Beautiful!",2) to get Hi

Also try right(text, no. of chars) and mid(text, start, no. of chars) to

get rightmost or middle characters. You can use right(filename,3) to get the

extension of a file name

4. Find second, third, fourth element in a list without sorting

We all know that you can use min(), max() to find the smallest and largest numbers in

a list. But what if you needed the second smallest number or 3rd largest number in thelist? You are right,
there is a spreadsheet function to exactly that.

5. Find out current date, time with a snap

You have a list of customer orders and you want to findout which ones are due for

shipping after today. The funny thing is you do this everyday. So instead of entering

the date every single day you can use today()

Example: Use today() to get 08/13/2008 or whatever is today’s date

Also try now() to get current time in date time format. Remember, you can always

format these date and times to see them the way you like (for eg. Aug-13, August 13,

2008 instead of 08/13/2008)

6. Convert those lengthy nested if functions to one simple formula

with Choose()

Planning to create a grade book or something using excel, you are bound to write

7
 some if() functions, but do you know that you can use choose() when you have more

than 2 outcomes for a given condition? As you all know, if(condition, fetch

this, or this) returns “fetch this” if the condition is TRUE or “or this” if the

condition is FALSE

Where as choose(m, value1, value2, value3, value4 ...) can return any of

the value1,2.., based on the parameter m.

Example: Use CHOOSE(3,"when","in","doubt","just","choose")

to get doubt

Remember, you can always write another formula for each of the n parameters of

choose() so that based on input condition (in this case 3), another formula is

evaluated.

7. Repetitively print a character in a cell n number of times

You have the ZIP codes of all your customers in a list and planning to upload it to an

address label generation tool. The sad part is for some reason, excel thinks zip codes

are numbers, so it removed all the trailing zeros on the leftside of the zip code, thus

making the 01001 as 1001. Worry not, you can use rept() the extra needed zeros.

Example: Use zipcode & REPT("0",5-LEN(zipcode)) to convert zipcode 1001 to

01001

You can use REPT("|",n) to generate micro bar charts in your sheet.

8. Find out the data type of cell contents

This can be handy when you are working off the data that someone
else has created. For example you may want to capitalize if the
contents are text, make it 5 characters if its a number and leave it
as it is otherwise for certain cell value. Type() does just that, it tells
what type of data a cell is containing.

Example: Use TYPE("Chandoo") to get 2

See the various type return values in the diagram shown right

8
 9. Round a number to nearest even, odd number

When you are working with data that has fractions / decimals, often you may need to
find the nearest integer, even or odd number to the given decimal number. Thankfully
excel has the right function for this.

Example: Use ODD(63.4) to get 65

Also try even() to nearest even number and int() to round given fraction to integer just
below it.

Example: Use EVEN(62.4) to get 64

Use INT(62.99) to get 62

If you need to round off a given fraction to nearest integer you can use
round(62.65,0) to get 63.

10. Generate random number between any 2 given numbers

When you need a random number between any two numbers, try randbetween(), it is
very useful in cases where you may need random numbers to simulate some behavior
in your spreadsheets.

Example: Use RANDBETWEEN(10,100) may return 47 if you keep trying

1. Convert pounds to KGs, meters to yards and tsps to table spoons

You need not ask Google if you need to convert 156 lbs to kilograms or find out how
much 12 tea spoons of olive oil actually means. The hidden convert() function is
really versatile and can convert many things to so many other things, except one
currency to another, of course.

Example: Use CONVERT(150,"lbm","kg") to convert 150 lbs to 68.03 kgs.

9
 Use CONVERT(12,"tsp","oz") to findout that 12 tsps is actually 2 ounces.

12. Instantly calculate loan installments using spreadsheet formula

You have your eyes on that beautiful car or beach property, but before visiting the
seller / banker to findout of the monthly payment details, you would like to see how
much your monthly / biweekly loan payments would be. Thankfully excel has the
right formula to divide an amount to equal payment installments over given time
period, the pmt() function.

If your loan amount is $125,000,

APR (interest rate per year) is 6%,
loan tenure is 5 years and
payments are made every month, then,

Use PMT(6%/12,5*12,-125000) which tells us that monthly payment is $ 2,416 if

you keep trying

Also, if you want to find out how much of each payment is going for principle and
how much for the interest component, try using ppmt() and ipmt() functions. As you
can guess, even though EMIs or loan installments remain constant, the amount
contributed to principle and interest vary each month.

13. What is this week’s number in the current year?

Often you may need to find out if the current week is 25th week of this year. This is
not so difficult to find as it may seem. Again, excel has the right function to do just
that.

Example: Use WEEKNUM(TODAY()) will get 33

14. Find out what is the date after 30 working days from today?

Finding out a future date after 30 days from today is easy, just change the month. But
what if you need to know the date thirty working days from now. Don’t use your
fingers to do that counting, save them for typing a comment here and use the
workday() excel function instead.

Example: Use WORKDAY(TODAY(),30) tells that Sep 24, 2008 is 30 working days
away from today.

If you want to find out number of working days between 2 dates you can use

10
 networkdays() function,

15. With so many functions, how to handle errors

Once you get to the powerful domain of excel functions to simplify your work, you
are bound to have incorrect data, missing cells etc. that can make your formulas go
kaput. If only there is a way to find out when a formula throws up error, you can
handle it. Well, you know what, there is a way to find out if a cell has an error or a
proper value. iserror() MS Excel function tells you when a cell has error.

Example: Use ISERROR(43/0) returns TRUE since 43 divided by zero throws divide
by zero error.

Know How to Paste Your Data – 7 Tricks

11
12