100 Excel Tips & Resources to Celebrate 2000 RSS Subscribers

Posted on January 28th, 2009 in All Time Hits , Charts and Graphs , Excel Howtos , Featured , Learn Excel - 168 comments

Hurray, PHD blog crossed 2000 RSS Subscriber base.

This is a very significant milestone for me.

To celebrate this occasion we have a mega post: 100 Excel & Charting Tips, Tricks and Resources for you.

These 100 tips & resources are organized in to the areas,

  • Keyboard Shortcuts
  • Formulas
  • Using Excel to do more
  • Charting
  • Excel Books for everyone
  • Excel Blogs & Resources

All these tips are bite sized and easy to read, digest and implement. The focus is on improving your productivity and making your day better. Wherever possible, I have included links to relevant articles on this site so that you can read and learn more.

If you like this post, I encourage you to signup for  e-mail updates or RSS Feeds.

Please spread this blog by submitting to delicious or stumbleupon, that makes me *really* happy

Delicious Bookmark this on Delicious | Stumble this page

Ok, on to the tips now…

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 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)… Get Full Tip
29. To get nth smallest number in a range, use = small(range,n)… Get Full Tip
30. To generate a random phone number, use =randbetween(1000000000,9999999999), needs analysis toolpak if you are using excel 2003 or earlier… Get Full Tip
31. To count number of words in a cell, use =len(trim(text))-len(SUBSTITUTE(trim(text),” “,””))… Get Full Tip
32. To count positive values in a range, use =countif(range,”>0″)… Get Full Tip
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″)… Get Full Tip
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… Get Full Tip
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… Get Full Tip
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… Get Full Tip
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″… Get Full Tip
47. To format a phone number, use the custom format code “000-000-0000″… Get Full Tip
48. To show values after decimal point only when number is less than one, use [<1]_($#,##0.00_);_($#,##0_) as formatting code… Get Full Tip
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)… Get Full Tip
50. To hide a worksheet, go to menu > format > sheet > hide… Get Full Tip
51. To align multiple objects, like charts, drawings, pictures use drawing toolbar > align and select alignment option… Get Full Tip
52. To freeze rows on top, select the a row and use menu > window > freeze panes… Get Full Tip
53. To disable annoying formula errors, go to menu > tools > options > error checking tab and disable errors you don’t want to see… Get Full Tip
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… Get Full Tip
55. To transpose a range of cells, copy the cells, go to empty area, and press alt+e+s+e… Get Full Tip
56. To save data filter settings so that you can reuse them again, use custom views… Get Full Tip
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… Get Full Tip
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… Get Full Tip
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.… Get Full Tip
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… Get Full Tip
69. To improve comparison, replace your radar charts with tables… Get Full Tip

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

5 Excel books for everyone

76. Excel 2007 Bible by John Walkenbach
77. Excel 2007 Formulas by John Walkenbach
78. Excel 2007 Charts by John Walkenbach
79. Excel 2007 Power Programming with VBA
80. Teach Yourself VISUALLY Excel 2007

PS: Links to Amazon, affiliate code used

20 Excellent Resources and Blogs for getting latest Excel Tips & Charting Ideas

81. PTS Blog
82. Andrew’s Excel Tips
83. Microsoft Excel 2007 Team Blog
84. Contextures
85. Junk Charts
86. Daily Dose of Excel
87. Digital Inspiration
88. Life Hacker
89. Jorge’s Charts Blog
90. DSA Inisights
91. Excel Blog @ TVM Calcs
92. Juice Analytics
93. More information per pixel
94. Newton Excel Bach
95. Presentation Zen
96. Visual Business Intelligence by Stephen Few
97. Cell Matrix
98. Allen Wyatt’s Excel Tips
99. Code for Excel and Outlook
100. All the Popular Excel Bookmarks in Delicious

Join the celebrations, share your tips & ideas

I encourage you to share your Excel Tips & Charting Suggestions through comments section. We have a vibrant reader community here and we love to learn from each other.

If you like this post, I encourage you to signup for  e-mail updates or RSS Feeds.

Please spread this blog by submitting to delicious or stumbleupon, that makes me *really* happy

Delicious Bookmark this on Delicious | Stumble this page

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

168 Responses to “100 Excel Tips & Resources to Celebrate 2000 RSS Subscribers”

  1. JP says:

    Your book list has all Excel 2007 books, do you actually use Excel 2007? Your last post with screenshots was Excel 2003 (I think).

  2. Andy Cotgreave says:

    Great post. I learnt a few great little tips in here, despite using Excel for 15 years!

  3. Chandoo says:

    @JP: I use both 2003 and 2007 (about 50% of time each). Even though excel 2003 is used for screencaps etc., I think learning excel 2007 is better than 2003 if you are buying books.

    @Andy: welcome to PHD and thanks :)

  4. Joel Thomas says:

    Congratulations!!! I enjoy your posts on excel tips…

  5. Chandoo says:

    @all.. doh! I missed the spreadsheetpage blog…
    http://spreadsheetpage.com/

  6. Rozario says:

    Chandoo, great stuff on your site! I am a keen Excel enthusiast and I do visit quite a lot of website that puts out Excel material, but I must say the info contained on your site is top class and most importantly you offer this stuff free! Thanks Chandoo, appreciate what you to do, to help guys like us.

    • Chandoo says:

      @Rozario. Welcome and thanks for the wonderful words. Learning and sharing is a very enriching experience and I am thoroughly enjoying it through this medium. I encourage you to go around the site and see what we have got here. If possible, invite your friends and colleagues to the site and help them get productive.

  7. Yusuf says:

    Great compilation …. nice work

  8. [...] 100 Excel Tips & Resources for Everyone | Pointy Haired Dilbert – Chandoo.org (tags: tips personal tutorials reference howto Microsoft Excel office) [...]

  9. Prasanta says:

    Nice shortcuts. One from me

    Alt+Ctrl+Tab to move text/content any where in the cell toward right

    Alt+Ctrl+Shift+Tab to move text/content any where in the cell toward left

  10. anand says:

    Hi Chandu ! this is the site which i am looking since long today i got it while surfing. I wish to develope some stock mkt utility in excell for self use and ready to offer it free to all if it works fine.
    I dont under stand how create data base sort of thing in excell through EOD or fetch data from NSE and put formula into it. If u have made any utility plz mail me.
    regard
    anand

  11. Chandoo says:

    @Prasanta: Thank you so much for sharing your tips.

    @Anand: Welcome to PHD and thank you so much for commenting.

    Unfortunately I havent made any utility to automate stock quote download. There are several ways in which you can do this in excel though. Check out: http://chandoo.org/wp/2008/06/24/get-stock-quotes-in-excel/ and see the comments there.

    I will let you know if we create something to automate this.

  12. carlos pando says:

    Hi,
    Great list. I don´t know why these shortcuts are always kind of hidden when they are so usefull. I think Excell should have an easy way to see them in the help or something.

    and here is another one:
    to fill down a group of selected cells (with the value of the upper selected one)

    Also you may find that if you use Excel in another language you may need to adapt a bit this shortcuts (which I find really anoying) for example:
    To insert current date, To insert current time. Worked really well in my English excel, but now I am using excel in German and they don´t work at all. I´ve discovered that in this case I only need to replace the semicolon for the period and everything is fine.
    And in the case of to fill down the sellected cells, I need to use

  13. carlos pando says:

    Sorry, used some characters that prevented some text to be displayed, here is the corrected post…

    Hi,
    Great list. I don´t know why these shortcuts are always kind of hidden when they are so usefull. I think Excell should have an easy way to see them in the help or something.

    and here is another one:
    ctrl+d to fill down a group of selected cells (with the value of the upper selected one)

    Also you may find that if you use Excel in another language you may need to adapt a bit this shortcuts (which I find really anoying) for example:
    ctrl+; To insert current date, ctrl+shift+ To insert current time. Worked really well in my English excel, but now I am using excel in German and they don´t work at all. I´ve discovered that in this case I only need to replace the semicolon for the period and everything is fine.
    And in the case of ctrl+d to fill down the sellected cells, I need to use ctrl+u

  14. Modeste says:

    Hello,
    In tip 36 To find age of a person based on DOB :

    It is not correct to use the date format “yy mm dd” to dysplay an age !!!!

    Assume an age of 60 years 0 month and 24 days
    how to display the month 0 ?

    on the other hand:
    an age of 60 years 12 months 31 days is inconsistent!

    you better use DATDIF function

    Cheers !!!

  15. Chandoo says:

    @Modeste: thanks for your tip on datedif(), it is an undocumented function. :)

    • Modeste says:

      Hi… Chandoo

      Though DATEDIF is saddly bugged when used with “md” argument !!!
      probe it :
      when recente date is leap year
      and recente date month is january
      and recente date day is lower than old date day
      i.e.
      Old date recente date =DATEDIF($A2;$B2;”y”) =DATEDIF($A2;$B2;”ym”) =DATEDIF($A2;$B2;”md”)
      Mon, Jun 28, 1954 Mon, Jan 16, 2012 57 6 132
      Wed, Feb 26, 1913 Wed, Jan 16, 2008 94 10 134
      Fri, Jan 23, 1998 Tue, Jan 06, 2004 5 11 127
      Sat, Feb 27, 1999 Mon, Jan 16, 2012 12 10 133
      Sat, Feb 22, 1930 Thu, Jan 06, 2000 69 10 128
      Wed, Apr 30, 1980 Tue, Jan 16, 1996 15 8 130

  16. Glenn says:

    Can you explain how to count only the number of red cells using the VBA code. I’m missing something when I copy and paste in the module.

    Thanks

  17. Chandoo says:

    @Glenn… can you help me understand your question? What is the module you are referring to?

  18. Glenn says:

    I’m using 2007 excel-I want to count just the red cells from a selected column. I’m very versed with key stroke macro’s, but I dont understand how to set VBA when copying and pasting a code for this function. Maybe I’m not explaning myself due to lingo barrier…

  19. [...] To clone value from above cell: press ctrl+’  This will make a twin from the above cell. (99 more tips) [...]

  20. [...] we have crossed the milestone of 2000 RSS subscribers in the Jan 2009. To celebrate that I posted one hundred excel tips. That was fun (plus Jo hated me for sitting in front of computer that [...]

  21. I have no words to explain how much your tips are useful for me

  22. Nice Shortcut, it’s really perfect.

  23. Jaganathan says:

    Nice tips!
    //
    24. To go to the next worksheet, press ctrl+shift+pg down
    25. To go to the previous worksheet, press ctrl+shift+pg up
    //
    To skip between worksheets ctrl + pg keys are sufficient.
    Holding shift key will group the worksheets.

  24. degala says:

    hai chandoo u r excellent ya i am so hppy with join ur team best of luck

  25. Donna Damron says:

    How do I print my excel spreadsheet on one page?

  26. Hui... says:

    @ Donna
    Select your data
    On the Page Layout Tab, Click the Print Area Button and select Set Print Area
    At the Bottom Right Corner of the Page Layout Tab seelct the Page Setup pullout arrow
    Adjust the Page Orientation
    and set Scaling to Print 1 Page Wide by 1 Page Tall

  27. Sarah says:

    Great tips. I especially like tip # 39 partial matches in vlookup.

  28. Raaj says:

    hi chandoo,
    really nice work, thanks.
    i would like to save patient’s personal info like name, class etc as i work in an hospital to his/her file number, so that when i enter the medical file number in one cell all the saved data should fill in the adjacent cell automatically, pls help.
    regards,
    raaj.

    • Bonnie says:

      I am just wondering if this question was answered, as I also would like to know how to do this for a smilar application.
      Thank you.

    • MURALI says:

      Here’s how to do it…
      Step 1 – Create an empty table in your another sheet with the same source table headers ( Name, class,file number etc) with file number as 1st column in both the sheets.
      Format the file number cell differently from the rest because this will be your input cell. This new table is empty, but not for long…

      Step 2 – Populate each field with a VLOOKUP

      Write a VLOOKUP for each field, except the first one (file number). Use it to search the file number over and over, returning a different field each time. Change the column numbers of each VLOOKUP accordingly, e.g. column_index_num=2 for Name, column_index_num=3 for class, etc.
      See how the VLOOKUP formula changes…

      =VLOOKUP(A2,Sheet1!$A$1:$E$5,2,0)
      =VLOOKUP(A2,Sheet1!$A$1:$E$5,3,0)
      =VLOOKUP(A2,Sheet1!$A$1:$E$5,4,0)
      =VLOOKUP(A2,Sheet1!$A$1:$E$5,5,0)

  29. Karan Variava says:

    Hello,

    thanks for post.. can u please post some think about PPT…

    warm Regard’s
    Karan Variava

  30. Tolu says:

    i am glad. i have attended excel seminars but i find this thriling. please keep up the good work and making it free it is quite rewarding. please keep up the good work

  31. Saurabh Bhatnagar says:

    Hi,
    I JUST WANTED TO KNOW THE INSTITUTE WHICH PROVIDES TRAINING FOR ADVANCE EXCEL.

  32. jayanth says:

    Hi,

    do u have any idea of how to use activex in excel, if u know please revert back on my mail.

    Thanks and regards,

    Jayanth.M

  33. sangeeta says:

    Happy Birthday Chandu
    I am new to your group and found the list just awesome! Thanks!

  34. sangeeta says:

    I was able to hide and unhide the rows using the tips your provided, but was unable to unhide the column..I pressed ctrl+shift+0. It did not work. What could I be doing wrong? I was able to hide the column. Kindly help

  35. Arno says:

    Keyboard shortcuts part – thats what i was looking for, thanks!

  36. PRAFUL VYAS says:

    IN EVERY BUSINESS ORGANIZATION BANK RECONCILATION IS TEDIOUS JOB. IT IS A EASIEST JOB BY USING EXCEL DATA SORT COMMAND.

    STEP-1 DOWNLOD BANK STATEMENT IN EXCEL FORMAT.
    STEP-2 SORT THE DATA ON AMOUNT COLOUMN

    NOW YOUR JOB BECOMES EASY TO DO RECONCILATION WORK FASTEST

  37. Manish says:

    I am planning to develop small application for fund/finance management, which module of your site/training will help me, please suggest

    manish

  38. Sunil says:

    Dear Chandoo, I tried to hide the current row & current column in excel 2003 as described in 11 and 12 but I couldnt do it….

  39. Ramesh Madamshetty says:

    Dear Chandoo,
    When we are working with MS Office Excell, we have a provision for protect the file by putting password. If we create a excell file with password, others cannot open without knowing password. But the same file opens with Star Office, no need of password only just untick the password option. What is the use of putting password. Please clarify my doubt. Ramesh M

  40. Laxman says:

    sir, when we substract from a date/month/year , to date/month/year how to get days/months/years please give a formula i am trying lot of days
    pleaseeeeeeeeeeeeeeeee……

  41. Tapan says:

    Kindly give me some tips on macros.

  42. Hui... says:

    @Tapan
    Have a read of http://chandoo.org/forums/topic/im-teaching-myself-some-vba-wanna-help
    and then start to look at small examples here or at home
    Use the Search Function at the top of the Chandoo.org web site and Google has links to nearly every imaginable problem.

  43. Rick Rothstein (MVP - Excel) says:

    @Tapan,

    Here are some Excel VBA tutorials that might help you out…

    http://www.anthony-vba.kefra.com/
    http://www.jlathamsite.com/Teach/VBA/ProgrammingInExcelVBA_AnIntroduction.pdf
    http://www.tushar-mehta.com/excel/vba/beyond_the_macro_recorder/index.htm

    The second one is quite lengthy and complete, but maybe you should start with the first one to “get oriented”. The third one is a nice follow up.

  44. suresh says:

    very nice man.please send alerts

  45. Prasad Ponkshe says:

    Wonderful easy to understand information. I haven’t seen any other site with such excellent information. Many thanks

  46. preetha says:

    Hi Chandoo, wonderful site to excel in excel :)

    1. Would like tips on payroll stuffs like there are about 200 employees and need to know how many extra hours worked and on which dates should be the end result.
    2. Tips on roster and forecasting of call center.
    3. Macros for beginners and advanced users
    Many thnx

  47. Ravindra says:

    Please advice some excel macro tips to compare two sheets

  48. Mayank says:

    Hi Sir,
    please help me. how to open hidden Game in excel 2007

  49. Chandoo says:

    @Mayank… I am not aware of any hidden game in Excel.

    @Ravindra: Visit http://chandoo.org/wp/2010/02/19/excel-consolidate-data/

  50. Jay says:

    @Mayank & @Chandoo …
    Yes, there are hidden games in Excel , usually called as Estern Eggs in Excel.
    Please see these links for more details

    http://eeggs.com/items/29841.html ( for Excel 2007 )
    http://www.askstudent.com/gaming/how-to-find-the-hidden-game-in-microsoft-excel-2000/
    ( for Excel 2000)

  51. Mohammad Mal says:

    Hello,

    I am facing a problem with Outlook email while sending and reeving emails but while with the same settings i am able to use with a data card this problem only comes when i am connecting it to lan connection i have tried reinstalling settings and Outlook but it doesn’t work if you can help for the same would be waiting for your replay.

    Regards,
    Mohammad Mal

  52. sunjay says:

    dear chandoo, tell me how to protect a particular cell or rows or column in ms-excel 2003.

  53. Chandoo says:

    @Sunjay… follow this process:

    1) select cells, rows or columns you want to protect. Go to format cells
    2) make sure the option “Locked” is checked from protection tab.
    3) right click on worksheet name and choose protection
    4) give a password and repeat it
    5) Now, your worksheet is protected and all the locked cells are not editable.

    You can do a lot more with worksheet protection. Just play with the options.

  54. Babu says:

    Hi chadoo Great work with this. I am looking for Finacial Modelling using Excel. How to work with Ratio Analysis. Work out Marginal cost of debt and equity etc would be done good with excel. Do you have such a course. If so Please let me know.

  55. Uday Kiran says:

    hi chandu, i am from vizag, i heard so much of u, and visited ur blog, its grt & amazing, keep it up and thanks a lot for sharing ur knowledge to others, catch u soon

  56. veena singh says:

    Thanks for the post.Your site is like bible for people who want to learn XL.

  57. naeem says:

    how to put trial period to an excel file/project. say like 30 days.

    I will really appreciate your help

  58. SUDIPTA DEY says:

    i need your help, in my excel workbook i have several work sheets with various names. how can i count the number sheets are there in my work book?

  59. Hui... says:

    Add a UDF

    `Function NoSheets() As Integer
    NoSheets = Sheets.Count
    End Function`

    To use just enter =NoSheets
    in any cell

    • VDS says:

      Hellow

      The command is not working properly.
      After putting in the Class module, it displays #Name?
      What will be the reasons.

      VDS

  60. Phanikumar Srishti says:

    Hi,
    I have data in many columns. I need to compare any of the two columns and so while comparison I require only the two columns to be displayed with the others hidden. Could you please suggest me any macro for this. Is this possible without using the feature of hiding columns? Please suggest.. Thanks in advance

    • MURALI says:

      Dear, you did not mention the total no of cols & rows you in your data. Also you didn’t mention, what actions you take during comparison or after comparison. Whether you want to have the data table in original format or not. There are 3 ways to my mind to use for your comparison requirements as below ( without use of any Macros, and without hiding the balance cols, but they shall not be visible):
      1. Split the window vertically, adjust both the col widths to compare to fit the screen.
      2. Have a blank row immediately on top of your data table. Put 1 and 2 in the blank row for both cols you want to compare. Select the Range of Row from 1 to 2. Then custom sort Horizontally. Now you have both the cols with 1, 2 side by side for your comparison. Adjust the width of your both cols to fit your screen. So other cols are not visible as well not hidden. After your comparison is over, click undo till both cols compared go back to their original position. Start comparison of other 2 fresh cols by repeating the steps.
      3. Have two screens(monitors) and select one col in one screen and adjust col width to fit the 1st screen width. Select other col in other screen and adjust col width to fit the 2nd screen width. Compare and then continue the process.

  61. Im Shoaib From Pakistan please email any body… I need All Formulas of Excel and how to use it….

    Thanking you in anticipation.

  62. alok says:

    hey chandoo,
    i m still waiting for my 25 page e-book, can u help

  63. Kumar.Krishna says:

    Hi Chandoo,

    Please help me to learn whole excel in and out including VBA.

    Thank you
    Kumar Krishna

  64. Unnatti says:

    Could you please tell me how to insert a check mark in excel and also how to create a short cut for the degree symbol many thanks

  65. Hui... says:

    @Unnatti
    Goto the Insert, Symbol Tab
    Change Font to suit and Find your Symbols, Insert

    You can use Keyboard Shortcuts, using the Numeric Keypad
    Deg – Alt 0176

    Check – Change Font To Wing Dings 2 and insert a Capital P

  66. Ashitosh parab says:

    oh sir thanks 4 providing me such a valuable information about excel

  67. Kunwar jee says:

    I have opend your website first day with help of my friend, it’s really very nice way to learn excel, solve the all type of formula with help of your above mention example and formulas.
    You are doing a great job to make every one aware in excel without any charges.

  68. Kalyan Chakravarthy says:

    Iam unable to understand how to select the items in pivot tables. I mean to say, i have a data in front of me, but not able to understand what to drag where to get output. Basically i want to know whole concept of Pivot tables.
    Please Advise.
    Regards,
    Kalyan Chakravarthy

  69. Kalyan Chakravarthy says:

    Thnx Chandoo! I will work once on this and let you know if i have any questions.

  70. Kalyan Chakravarthy says:

    Hello Chandoo!
    Iam very much interested to learn excel, Usually i use excel but iam not 100% perfect.
    actually i have read your profile and keen to know about MVP.
    Can you please let me know what is the eligibilty criteria, how to approach and how to get train on MVP.
    Please Advise

  71. SARAN KUMAR says:

    Very nice stuff..

    Tip 16: To select data in current region, press ctrl+shift+8

    In fact, it is Ctrl+* to select data in current region.. here shift+8 is nothing but *. Dont try Crl+shift+8 (from nums key pad..which is right side of your keyboard). If you want to use ctrl+shift+8 then you have to press 8 above the alphabet keys.

    Regards,
    Saran
    lostinexcel.blogspot.com

  72. SARAN KUMAR says:

    @ Kalyan,

    First of all, MVP is not certification course at all. MVP (Microsoft Valuable Professional) is the award which is given by Microsoft. MS will give this award based on the individual last one year contribution towards any MS Domain. Means you have to participate into different forums to answer the readers questions, or you can maintain your own blog to give guidance to the people on that particular domain. Before all these you have some skills and ready to learn new things everyday.

    Chandoo, did lot of work and gave very gud guidance to the readers through his posts. That is the reason chandoo got MVP prestigious award from MS.

    All the best Kalyan..if you want even you can…follow learn and share techniQue

    Regards,
    Saran
    lostinexcel.blogspot.com

  73. SARAN KUMAR says:

    @ Sudita @ Hui

    Your is fantastic code.

    There is one more alternative and you can try it.

    First define name by refering to =GET.WORKBOOK(1)&T(NOW())

    Then use this below formula to get the number of worksheets in the current work book

    =counta(index(defined name,0))

    Hope it works..

    Regards,
    Saran
    lostinexcel.blogspot.com

  74. SUNIL KUMAR MISHRA,ODISHA says:

    HI CHANDOO !
    I WANT PREPARE A PROJECT REPORT FOR BANK LOAN IN EXCEL FORMATE. I AM NOT UNDERSTANDING THE METHOD AND PROCEDURE OF EXCEL WHICH TO BE CODE USED IN. CAN YOU GIVE A COMPLETE CODE FOR SETUP A PROJECT FORMATE . SO THAT MY ACCOUNTANCY PROBLEMS WILL NOT COME. I AM DOING IN WORD FORMATE. BECAUSE I DON’T KNOW WHICH CODE TO BE USED IN .PLEASE GOVE SOME BASIC IDEA WITH A EXAMPLE , SO THAT I CAN DO IT.

  75. Shekar says:

    Need help, how to use vlookup with if condition, My query
    Country number VOLUME OUTPUT
    USA 1 12
    UK 2 24
    my query,
    if USA = 1 and volume 12 =100
    if uk =1 and volume 12 = 150

    • MURALI says:

      vlookup is not a calculation, but it is to obtain info from an existing table data to meet your requirements. so, you should have(create) your data table first, then, you can obtain the info as required from your data table using lookup functions.

  76. G Srinivas says:

    hi

    Can u send VB notes.

  77. s says:

    hi guys,

    can anyone help or provide some ideas about a simple database or spreadsheet ive got to create for my work
    regarding headcout or employees in each department or area split over grades?

    thanks

  78. chandu says:

    hi … nice posts , very useful

  79. Niyas says:

    I am using formula a cell and after 5 Line I want to repeat subsequently, ie, If I am using =f10 after 5 cell I want to use =f11 after 5 cell I want to use formula =f12 ,is there is anything way to use in excel if yes please help me

  80. USHA says:

    Hi chandoo….its a gr8 job by u sir…

    Im learning excel as i recently working on excel in making reports…
    i am desperate to learn more shortcuts as we have lots n lots of work as we have to know short cuts other wise ill be back in work …..so please help me more on short cuts

  81. surendar says:

    chandoo can i have your phone number, I have so many doubts in excel plz help me out.

  82. [...] excel tips Share this:TwitterFacebookLike this:LikeBe the first to like this post. Categories Uncategorized [...]

  83. [...] article called 100 Excel Tips and Resources makes me want to sing [...]

  84. Abhishek says:

    How to create a hyperlink in Excel cell

    • MURALI says:

      1. right click on the cell and select Hyperlink from the pop up box, continue the process.
      2. select the cell , click on Insert ribbon, there you get the Hyperlink button, click on that and continue the process.

  85. Eric Lentini says:

    This is quite simply the most accessible and helpful Excel resource on the web – and I believe that is saying alot. Thank you for making my life at work easier.
     
    Eric

  86. Sajid says:

    Hi Chando,
    I am a silence reader of your website. Today it forced me to contact you.
    I am using Excel 2010 but while I am pasting data into excel sheet, It’s pasting one Picture bitmap Image) not data. Even I re-installed office 2010 too also but the problem still same.
    Kindly help me to fix this issue.
    Kind Regards
     

    • Hui... says:

      @Sajid

      What is the source of your data?

      How do you select the data?

      When you paste it into Excel, instead of Paste, Right click and select paste, are there any other options?

      Can you share a sample of the data source ?

       

       

  87. Hi,
    My source data is table data from MS Dynamic 2009. I am copying whole data & when I try to paste it into excel I just get the bitmap…and no other options in paste special then ‘bitmap’. But other peoples are working fine with same operating system & office 2010 which they don’t have such difficulty. I have seen someone had the same problem in the following link but no solution described.

    http://www.justanswer.com/computer-programming/57si3-paste-special-excel-just-pastes-black-dot-bit-map-instead.html

    You can see my whole problem in the above link.
    Thanks in advance for support.
    Kind Regards

  88. Ashish says:

    It’s really helpfull, I need so animated graphs ideas…need your help in this

    Thanks & Regards,
    Ashish

  89. ANEEK says:

    Dear Mr. Hui,
    I’m not able to work the following relation in excel, would you please help me?
    If A1<40 then 34
    if (A1 and B1)<40 then 30
    if (A1 and B1 and C1)<40 then 26
    if (A1 and B1 and C1 and D1)<40 then 22 else 38.

    • Hui... says:

      @Aneek
      =IF(AND(A1<40,B1<40,C1<40,D1<40),22,IF(AND(A1<40,B1<40,C1<40),26,IF(AND(A1<40,B1<40),30,IF(A1<40,34,28))))

      You will note that the logic is backward as if you check A1 by itself first you will never get past the first If, if A1<40.

      • ANEEK says:

        Dear Mr. Hui,
        Thanks a lot it works perfectly,
        another problem is when i put A1>40 it shows the 38 others are stays <40 means B1<40,C1<40,D1<40 then its will show 26. how would i write this logic?

        • ANEEK says:

          Dear Mr. Hui,
          Actually I am trying to say that If any of the like A1 or B1 or C1 or D1 <40 it will subtracts 38-4, So it stands if A1<40 and others are >40 it should show 34 if A1 and B1 <40 it should show 30 others are >40, again  if A1>40 others are like B1 and C1<40 it should show 30. How would i do this?

        • Hui... says:

          @Aneek

          Are these new rules in addition to the existing rules or in replacement of them?

          • ANEEK says:

            Dear Mr. Hui,
            these are the new rules in addition to the existing rules.

          • ANEEK says:

            Dear Mr. Hui,
            new prob.
            if A1<40 then 201
            if B1<40 then 202
            if C1<40 then 203 else “-” ; addition with this if (A1<40 and B1<40) then “201,202″ or if (A1<40 and C1<40) then “201, 203″ or if (B1<40 and C1<40) then “202, 203″ else “-”.
            Would you please help me for the above problem?

          • ANEEK says:

            Dear Mr. Hui,
            I have been looking forward to your solution.
             

          • Hui... says:

            @Aneek

            Firstly, Although I assist here at Chandoo.org I am not employed by Chandoo.org, I receive no remuneration, reward and half the time no thanx for the responses I offer.

            If something is urgent I would suggest that you hire an Excel Consultant.

            In regards to your answer, I offer answers not just to do your work, but to assist you with solutions both to solve your immediate problem, but then also so that you can analyse the solution and learn to develop solutions yourself for future similar problems as was the case with your previous similar question.

            In regards this specific question, you may want to try :

            =IF(AND(B1<40,C1<40),"202, 203", IF(AND(A1<40,C1<40), "201, 203", IF(AND(A1<40,B1<40), "201, 202", IF(C1<40,"203", IF(B1<40,"202", IF(A1<40,"201","-"))))))

            You may need to retype all the ” marks when you copy/paste this formula in your worksheet

  90. ANEEK says:

    Dear Mr. Hui,
    Thanks a lot, sorry to bother you, anyway I am really appreciate your cooperation.
     

  91. Abhijit Kundu says:

    Hi, Chandoo,

    Very good morning,
    Please write how to not showing any formula in formula bar.

    Regards
    Abhijit

  92. Prabhat Ranjan says:

    Really Its is a good blogs we can share so many new things and refresh our old knowledge as well :).

  93. Romano says:

    Hi Chandoo and all at Chandoo.org,

    I was looking for a website such as yours and came across it today.
    Thanks for the great effort, I hope to speed up my Excel capabilities and
    surely learn a lot from you and all here.

    THANKS!   

        

  94. Sean Tan says:

    Great work you have here. Glad that I found your blog. Am recommending this site to my friends.

  95. Jaijo says:

    How do i fill down the upper cell data in column with one keyboard function? Usually the ctrl+D fills down till the next populated cell. Then again I need to select that cell and again use Ctrl+D function to fill down that cell data down the next column of cells. ?

    for eg In column A, I have  data in cell A1 then another data in A10 and A15 and A20. I need to fill them down their respective blank cells. Instead of using Ctr+D 4 times.

  96. Ishan says:

    Dear Sir,

    It’s really great to have this kind of stuff at free of cost. I enjoyed very much and learnt  a lot. 

    Its always like to surf your site.

    Sir, I have one question, and here it is:

    1. How can we fill all the cell with “0″ automatically, or with any formula, is it possible?

    2. In one of the no. format you have formatted the number cell by using $  ____. so that Symbol will continue to show even if there is no value, but when I have used that format in one of my file, but i want to show $   0 but automatically it shows $   –   only instead of $   0.

    So what is the format to be used to Show $   0 , Zero will continue to show   rather than -.

    Thanks for your wonderful tips and other valuable tips on excel.

    Thanks once again…!!!! 

  97. sachin says:

    PLEASE HELP TO SOLVE ONE QUERY
    my question is that if a cell contain so many e-mail IDs  then how we do it  separately/broken them to another cells
     

  98. Alexandra says:

    #54 for 2007 is different: use the info on this link instead:  http://voices.yahoo.com/excel-2007-tip-change-comment-shape-7419276.html?cat=15

  99. Ashok says:

    I have got a doubt about excel calculations:
    Example
    If we have around 150 to 1500 products in our store for which we have got the list, every day we use some of them either by piece/qty at the end of the month/year if we require the balance stock with us how will we calculate?
    Can we calculate on daily basis by giving their names, here i am facing one obstacle in excel as every product is not used regularly so by the name can we assort accordingly to day, month, year and so on……..

     

  100. anu says:

    please create ms word and powerpoint tricks..

  101. Naveen says:

    Can some one help in changing the Date format (from:DD-MM-YYYY to:YYYY-MM-DD in the excel cells?
    For instance – Copy pasting the list of dates from DB columns values to Excel workbook, the date formats would be mixed like “29-12-2012″ and “2010-01-26″.
    After pasting the values to excel, I have tried the below items which doesn’t works still!
    -Right Click on the Cell and change the date format,general and tried custom format as well.
    -Format painter, copy the dates from the text pad and paste it to cells.
    -only thing I could see is, when checking the data type for the cells using function [=TYPE(Cellref)], it shows both 1 and 2 values for dates in the colums which was the numeric and text.
    -even if I do a copy dates from text pad, the delimiter for the date is getting changed by auto when pasting in to excel.
    For Eg: all the dates have same delimiter in text pad like “29-10-2010″ , “2010-03-11″, etc,. but when pasting it to excel it just coverts like “29-10-2010″ , “3/11/2010″, etc,. which is not fine.
    Any suggestions/help in this regard is much appreciated!
     

    • MURALI says:

      For changing the Date format (from:DD-MM-YYYY to:YYYY-MM-DD):
      Format cells > Number > Custom > in Type box- type ” yyyy-mm-dd”, without ” marks and in lower case> click OK .

  102. [...] Get started with this post: 100 Excel Tips & Resources to Celebrate 2000 RSS Subscribers [...]

  103. Pankaj kumar says:

    great tips!!!!!!!!!!!!

  104. Ionceck says:

    “in Portuguese”
    “Você é o cara!!!!!!! ”
    a hug from Brazil.
     
     

  105. suresh says:

    i want to make a graph comparing last yr
     
    This is year graph i will make i want to show my last year in the back ground please help me

  106. rajendra says:

    Hi,
    how can i get ‘one’, ‘two’, ‘three’ in another cell, when i enter ’1,2,3,4  in different cell..?
    can u pls reply me..?

  107. Dr Indrajit Chakraborty says:

    Dear Mr Duggirala,
    I came to know your details from the site: http://chandoo.org/wp/about/……really it is amazing. A very useful site for common people in context of Excel totorial.

    I have faced a very typical problem : Say in Column some numbers are enlisted like
    10
    13
    16
    30
    56

    In column B I have to write like this:
    ONE ZERO
    ONE THREE
    ONE SIX
    THREE ZERO
    FIVE SIX

    How this job can be done by coding method (insert function)? I am a lay person in this regard. Can you please help me in this regard?

    Best Regards
    Dr Indrajit Chakraborty

  108. sleepy Williams says:

    I am fairly knowledgable in Excel, but I have no idea what to put in this formula:
    =TEXT((NOW()-birth_date)&””,”yy “”years”” m “”months”” dd “”days”””)
    What is birth_date? What do I enter there?
    What is TEXT? Do I enter something there?
    All I get is errors when I copy this into Excel.
    Thank you.

    • Modeste says:

      Hi … every body

      using date format yy mm dd to display the age of captain
      is non-sense !!!!
      what about : 21 years 12 months 24 days ???
      and
      45 years 0 month 20 days ( 0 month is not available)

      use formulas like this :

      years = TEXT(NOW()-birth_date,”yyyy”)-1900
      months = TEXT(NOW()-birth_date,”mm”)-1
      days = 1*TEXT(NOW()-birth_date,”dd”)

      ;o)
      but I prefer :
      years = INT((NOW()-birth_date) / 365.25)
      months = INT(MOD((NOW()-birth_date) / (365.25 / 12) , 12))
      days = INT(MOD((NOW()-birth_date) , (365.25 / 12)))

      cheers!!!

  109. Gaurav says:

    Hi Chandu,
    I loved reading these Excel tips in your article and find many shortcuts which I dint knew. Well, I would recommend my friends to cisit this website form more Excel tricks and knowledge..
    Thanxx for writing for the world

  110. Madhuri c r says:

    Hi
    Thank you thank you for your explanation about Excel worksheet. I’m a big user of Excel and your tips amaze me!!!!!!
    And no more words to say…thanks
    I would recommend all my friends to refer your tips for become a MASTER in excel

  111. Krishna says:

    Thanks a lot for your tips.

  112. [...] you’ve made it this far, here is a reward: 100 Excel tips to dive into from one of my favorite Excel resources. And for your reference, here are a few blogs [...]

  113. [...] you’ve made it this far, here is a reward: 100 Excel tips to dive into from one of my favorite Excel resources. And for your reference, here are a few blogs [...]

  114. Judex says:

    I cannot find the command “Shuffle” in Excel 2007.

    I want to shuffle, but not to random numbers.

    If I type in 10 numbers, and I want to shuffle those 10 numbers, what should I do ?

  115. victor says:

    Hi chandu, I would like to attend your excel class please help me and let me know the procedure esp in south india like bangalore, mangalore, hubli

  116. hkhan says:

    hi

    chandu yr work is too good. thanks

  117. shekhar badgujar says:

    Hi Sir, sir data validation use kartana ji list create hote tya madhil 1st row madhe alphabet type karun tya character chi list fakt show zhali pahije pls mala madat kara

  118. Prashant says:

    Hi Chandoo,

    I joined Your site yesterday and just want to say your site has helped me a lot to improve better at work and make reports.
    Please keep up the good work i am with you.

  119. Ritesh patel says:

    Do you have a VB or macro for generating phone calls?
    Thanks

  120. Ritesh patel says:

    How do you generate calls using a VB/MAcro?
    Thanks

  121. Jaisingh says:

    Chandoo

    I would like to use sumif or sumifs function and the criteria is search a text in the text string like searching “LPG” from “Domestic LPG”. The *LPG* star works but my problem is that I would like the search string to be referred from adjacent cell instead of typing since I have different search strings and would avoid typing each name for better flexibility. Any solution?

    • Chandoo says:

      Hi Jaisingh… You can use this formula (assuming A1 has the word LPG or whatever else you are searching for)

      =SUMIFS(sum column, criteria column, “*”&A1&”*”)

  122. Raj says:

    Hi friends,

    Hope all is well. I want to know – What is shortcut for move or copy a sheet to before or end of all sheet in same excel sheet ?

    Please help.

    Regards,
    Raj

  123. Rahul Kumar says:

    accept only PAN performa in a cell.
    How?????

  124. shrinivas says:

    How convert IST timing to AST timing in Excel ?

  125. Catina Landon says:

    Thank you for the tips they have really helped me out. Thank you!!!

  126. Hassan Pn says:

    In my idea:
    1- It’s better that tip No. 5 (To edit a cell comment, press shift

    + F2) changes to: To edit or create a cell comment, press
    shift + F2

    2- Also this tip is very useful & can be added:
    101. To insert a new simple chart quickly, press F11

    3- It’s better that tip No. 11 (To hide current row, press ctrl+9)
    changes to: To hide current row(s), press ctrl+9

    4- It’s better that tip No. 12 (To hide current column, press
    ctrl+0) changes to: To hide current column(s), press ctrl+0

  127. Pradeep Kumar says:

    There is an error pls correct it.
    No need to press shift while changing the sheet..

    24. To go to the next worksheet, press ctrl+shift+pg down

  128. Vijay says:

    I have downloaded chadooo mp4 archiver but it asks password. Please help me to the video lession. It is 50 mb.

  129. virendra says:

    i want to copy only number from “5 days”. what is the formula for that.plz reply

  130. […] has put together their 100 Excel tips & resources including some super quick keyboard shortcuts. We’re not talking advanced Excel tips, but […]

  131. Nadan says:

    Hi Chandu

    how to how to repeat the footer row on each page as like using print tiles use for header

    Rgds

    nadan

Leave a Reply