Here are 75 Excel Shortcuts & tips to improve your skill
Saturday 30 November 2013

Here are 75 Excel Shortcuts & tips to improve your skill

25 Very Useful Keyboard Shortcuts
1 To format any selected object press ctrl+1
2 To insert current date press ctrl+;
3 To insert current time press ctrl+shift+;
4 To repeat last action press press F4
5 To edit a cell comment press shift + F2
6 To autosum selected cells press alt + =
7 To see the suggest drop-down in a cell press alt + down arrow
8 To enter multiple lines in a cell press alt+enter
9 To insert a new sheet press shift + F11
10 To edit active cell press F2 (places cursor in the end)
11 To hide current row press ctrl+9
12 To hide current column press ctrl+0
13 To unhide rows in selected range press ctrl+shift+9
14 To unhide columns in selected range press ctrl+shift+0
15 To recalculate formulas press F9
16 To select data in current region press ctrl+shift+8
17 To see formulas in the worksheet press ctrl+shift+` (ctrl+~)
18 While editing formulas to change the reference type from absolute to relative vice versa press F4
19 To format a number as currency press ctrl+shift+4 (ctrl+$)
20 To apply outline border around selected cells press ctrl+shift+7
21 To open the macros dialog box press alt+F8
22 To copy value from above cell press ctrl+’
23 To format current cell with comma formats press ctrl+shift+1
24 To go to the next worksheet press ctrl+shift+pg down
25 To go to the previous worksheet press ctrl+shift+pg up
20 Situations and How to Solve them using Excel Formulas
26 To get the first name of a person use =left(name,find(" ",name)-1)
27 To calculate mortgage payments use =PMT(interest-rate,number-of-payments,how-much-loan)
28 To get nth largest number in a range use =large(range,n)
29 To get nth smallest number in a range use = small(range,n)
30 To generate a random phone number use =randbetween(1000000000,9999999999), needs analysis toolpak if you are using excel 2003 or earlier
31 To count number of words in a cell use =len(trim(text))-len(SUBSTITUTE(trim(text)," ",""))
32 To count positive values in a range use =countif(range,">0")
33 To calculate weighted average use SUMPRODUCT() function
34 To remove unnecessary spaces use =trim(text)
35 To format a number as SSN using formulas use =text(ssn-text,"000-00-0000")
36 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
37 To get name from initials from a name use IF(), FIND(), LEN() and SUBSTITUTE() formulas
38 To get proper fraction from a number (for eg 1/3 from 6/18) use =text(fraction, "?/?")
39 To get partial matches in vlookup use * operator like this: =vlookup("abc*",lookup_range,return_column)
40 To simulate averageif() in earlier versions of excel use =sumif(range, criteria)/countif(range, criteria)
41 To debug your formulas select the portions of formula and press F9 to see the result of that portion
42 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.)
43 To quickly insert an in cell micro-chart use REPT() function
44 COUNT() only counts number of cells with numbers in them, if you want to count number of cells with anything in them use COUNTA()
45 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
15 Excel Tips on Improving Productivity Using Excel
46 To format a number as SSN use the custom format code "000-00-0000"
47 To format a phone number use the custom format code "000-000-0000"
48 To show values after decimal point only when number is less than one use [<1> as formatting code
49 To remove grid lines from excel worksheet go to menu > tools > options > and un-check grid lines option. (Excel 2007: office button > excel option > advanced)
50 To hide a worksheet go to menu > format > sheet > hide
51 To align multiple objects, like charts, drawings, pictures use drawing toolbar > align and select alignment option
52 To freeze rows on top select the a row and use menu > window > freeze panes
53 To disable annoying formula errors go to menu > tools > options > error checking tab and disable errors you don’t want to see
54 To change the shape of cell comments from rectangle to some other symbol select the comment, go to drawing tool bar and change the shape from there
55 To transpose a range of cells copy the cells, go to empty area, and press alt+e+s+e
56 To save data filter settings so that you can reuse them again use custom views
57 To select all formulas press CTRL+G, select "special" and check "formulas"
58 To select all constants press CTRL+G, select "special" and check "constants"
59 To clear formats from a range select menu > edit > clear > "formats"
60 To move a chart and align it with cells hold down ALT key while moving the chart
9 Charting Tips for Everyone
61 To create an instant micro-chart from your normal chart use camera tool
62 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
63 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
64 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
65 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
66 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.
67 To increase the contrast of your chart, just remove grayish background color that excel adds to the chart (in versions excel 2003 and prior)
68 To save yourself some trouble, always try to avoid charts like – 3D area charts (unstacked), radar charts, 3D Lines, 3D Columns with multiple series of data, Donut charts with more than 2 series of data
69 To improve comparison, replace your radar charts with tables
6 simple steps for better chart formats
70 Remove any vertical grid-lines
71 Change horizontal grid-line color from black to a very light shade of gray
72 Adjust chart series colors to get better contrast
73 Adjust font scaling (for versions excel 2003 and prior)
74 Add data labels and remove any axis (axis labels) if needed
75 Remove chart background colors