Would you like to learn most popular Excel tips and shortcuts?
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 my free email newsletter.
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+`
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 data, use sparklines… 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 2016 Bible by John Walkenbach
77. Excel 2016 Power Programming by John Walkenbach
78. Excel 2016 All in one for dummies by Greg Harvey
79. Microsoft Excel Data Analysis and Business Modelling by Wayne Winston
80. M is for Data Monkey by Ken Puls
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 Power BI Blog
84. Contextures
85. Junk Charts
86. Daily Dose of Excel
87. Digital Inspiration
88. Life Hacker
89. Jorge’s Charts Blog
90. Data Chant
91. Excelarator BI
92. Guy in a Cube
93. More information per pixel
94. Newton Excel Bach
95. Presentation Zen
96. Visual Business Intelligence by Stephen Few
97. Spreadsheet Journalism
98. Allen Wyatt’s Excel Tips
99. Excel Guru
100. Chandoo.org on YouTube
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 my free Email Newsletter
Post updated on 20-May-2018
210 Responses to “100 Excel Tips & Resources”
Your book list has all Excel 2007 books, do you actually use Excel 2007? Your last post with screenshots was Excel 2003 (I think).
Great post. I learnt a few great little tips in here, despite using Excel for 15 years!
@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 🙂
Congratulations!!! I enjoy your posts on excel tips...
@Joel, thanks.
@all.. doh! I missed the spreadsheetpage blog...
http://spreadsheetpage.com/
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.
@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.
Great compilation .... nice work
[...] 100 Excel Tips & Resources for Everyone | Pointy Haired Dilbert - Chandoo.org (tags: tips personal tutorials reference howto Microsoft Excel office) [...]
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
This was good one
this shortcut is not working.
This brings up all the active windows from the taskbar in windows.
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
@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.
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
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
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 !!!
@Modeste: thanks for your tip on datedif(), it is an undocumented function. 🙂
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
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
@Glenn... can you help me understand your question? What is the module you are referring to?
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...
[...] To clone value from above cell: press ctrl+’ This will make a twin from the above cell. (99 more tips) [...]
[...] 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 [...]
I have no words to explain how much your tips are useful for me
Nice Shortcut, it's really perfect.
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.
hai chandoo u r excellent ya i am so hppy with join ur team best of luck
How do I print my excel spreadsheet on one page?
@ 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
Great tips. I especially like tip # 39 partial matches in vlookup.
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.
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.
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)
Hello,
thanks for post.. can u please post some think about PPT...
warm Regard's
Karan Variava
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
Hi,
I JUST WANTED TO KNOW THE INSTITUTE WHICH PROVIDES TRAINING FOR ADVANCE EXCEL.
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
Happy Birthday Chandu
I am new to your group and found the list just awesome! Thanks!
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
Keyboard shortcuts part - thats what i was looking for, thanks!
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
I am planning to develop small application for fund/finance management, which module of your site/training will help me, please suggest
manish
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....
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
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......
Kindly give me some tips on macros.
@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.
@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.
very nice man.please send alerts
Wonderful easy to understand information. I haven't seen any other site with such excellent information. Many thanks
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
Please advice some excel macro tips to compare two sheets
Hi Sir,
please help me. how to open hidden Game in excel 2007
@Mayank... I am not aware of any hidden game in Excel.
@Ravindra: Visit http://chandoo.org/wp/2010/02/19/excel-consolidate-data/
@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)
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
dear chandoo, tell me how to protect a particular cell or rows or column in ms-excel 2003.
@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.
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.
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
Thanks for the post.Your site is like bible for people who want to learn XL.
how to put trial period to an excel file/project. say like 30 days.
I will really appreciate your help
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?
Add a UDF
`Function NoSheets() As Integer
NoSheets = Sheets.Count
End Function`
To use just enter =NoSheets
in any cell
Hellow
The command is not working properly.
After putting in the Class module, it displays #Name?
What will be the reasons.
VDS
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
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.
Im Shoaib From Pakistan please email any body... I need All Formulas of Excel and how to use it....
Thanking you in anticipation.
hey chandoo,
i m still waiting for my 25 page e-book, can u help
Hi Chandoo,
Please help me to learn whole excel in and out including VBA.
Thank you
Kumar Krishna
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
@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
oh sir thanks 4 providing me such a valuable information about excel
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.
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
@Kalyan
Have a look at : http://chandoo.org/wp/2009/08/19/excel-pivot-tables-tutorial/
Thnx Chandoo! I will work once on this and let you know if i have any questions.
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
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
@ 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
@ 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
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.
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
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.
hi
Can u send VB notes.
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
hi ... nice posts , very useful
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
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
chandoo can i have your phone number, I have so many doubts in excel plz help me out.
Hi P.Surebdat09
Why don't you post some questions at the chandoo.org forums
they are generally answered within hours 24 hours a day!
Goto the bottom of the following page:
http://chandoo.org/forums/forum/ask-excel-questions
[...] excel tips Share this:TwitterFacebookLike this:LikeBe the first to like this post. Categories Uncategorized [...]
[...] article called 100 Excel Tips and Resources makes me want to sing [...]
How to create a hyperlink in Excel cell
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.
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
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
@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 ?
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
It's really helpfull, I need so animated graphs ideas...need your help in this
Thanks & Regards,
Ashish
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.
@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.
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?
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?
=IF(COUNTIF(A1:D1,"<40")=1,34,IF(COUNTIF(A1:D1,"<40")=2,30,IF(COUNTIF(A1:D1,"<40")=3,26,38)))
@Aneek
Are these new rules in addition to the existing rules or in replacement of them?
Dear Mr. Hui,
these are the new rules in addition to the existing rules.
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?
Dear Mr. Hui,
I have been looking forward to your solution.
@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
Dear Mr. Hui,
Thanks a lot, sorry to bother you, anyway I am really appreciate your cooperation.
Hi, Chandoo,
Very good morning,
Please write how to not showing any formula in formula bar.
Regards
Abhijit
Really Its is a good blogs we can share so many new things and refresh our old knowledge as well :).
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!
Great work you have here. Glad that I found your blog. Am recommending this site to my friends.
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.
@Jaijo
Double click the small black square in the lower right corner of the cell
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...!!!!
Format cells as Currency with 0(Zero) decimal places.
Nice articles..
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
Try the "Text to Columns" feature. This will allow you to extract strings from one value into different cells, based on a delimiter.
#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
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........
@Ashok
Uploading a sample file of data may assist us to assist you
Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
please create ms word and powerpoint tricks..
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!
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 .
[...] Get started with this post: 100 Excel Tips & Resources to Celebrate 2000 RSS Subscribers [...]
great tips!!!!!!!!!!!!
"in Portuguese"
"Você é o cara!!!!!!! "
a hug from Brazil.
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
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..?
use the info on this link
http://excel.tips.net/T002270_Converting_Numbers_Into_Words.html
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
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.
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!!!
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
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
Thanks a lot for your tips.
[...] 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 [...]
[...] 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 [...]
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 ?
Use Randbetween function for continuous numbers
=RANDBETWEEN(bottom, top)
If your numbers are random, then you will need to use more than 1 formula to shuffle between those numbers.
Twitter: @ExcelWordExpert
Numbers need not be continuous. Press F9 to have fresh random numbers every time you want to have.
@Judex
There is no Shuffle in Excel
But you can do what you want with a formula
See an example here: http://stackoverflow.com/questions/13112178/generating-unique-random-numbers-in-excel-using-formula-without-vba
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
hi
chandu yr work is too good. thanks
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
you can select in drop down list, or else you can type. you cann't do both at the same time in the same cell.
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.
Do you have a VB or macro for generating phone calls?
Thanks
How do you generate calls using a VB/MAcro?
Thanks
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?
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&"*")
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
Right click on the sheet name and continue the process.
accept only PAN performa in a cell.
How?????
How convert IST timing to AST timing in Excel ?
Thank you for the tips they have really helped me out. Thank you!!!
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
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
I have downloaded chadooo mp4 archiver but it asks password. Please help me to the video lession. It is 50 mb.
i want to copy only number from "5 days". what is the formula for that.plz reply
@Virendra
Can you supply a sample file or more details please
Maybe ask your question at the Chandoo.org Forums http://chandoo.org/forum/
[…] has put together their 100 Excel tips & resources including some super quick keyboard shortcuts. We’re not talking advanced Excel tips, but […]
Hi Chandu
how to how to repeat the footer row on each page as like using print tiles use for header
Rgds
nadan
hi chandu
i am facing problem with excel. that is i am crating xls template that will polt chart automatically after i entered data. this can be done by using offset(=OFFSET(reference, rows, cols, [height], [width])) function. but problems is it works only dynamic rows. but it will not working if both rows and columns are dynamic.
regards
chicha
dear sir,
how to maintain the detail in cell( ms excel ) as per following example :-
T
A
R
U
N
REGARDS
TARUN SETHI
09315350605
Formulas are very useful. Made my work easier.. Thanks..
Dear sir
one picture in so many excel sheets
how to change picture in one command
I working in work sheet of excell, I got suddenly hide some columns from IV to lastg. How it will be opened
very good informations
Sir,
To get the first name of a person, use =left(name,find(” “,name)-1)
To get the second name of a person ?
And
I want to remove the space from "Deepa Chandrasekaran", How will you do it?
Hi Deepa,
To get the second name of a person, use = RIGHT(name,LEN(name)-FIND(" ",name))
And to remove space from the name, use = SUBSTITUTE(name," ","")
=IF(LEN(B32&D32)+1>LEN(A32)," ",MID(A32,LEN(B32)+2,LEN(A32)-LEN(B32&D32)-1))
plz use it
Hi, sir, just wanted to check if you can help with the formula for seperating the text & number from a cell in excel.
Hi,
I have two cells like below.
Category Wages
O/L TAILOR -I 280
F/L TAILOR -I 305
HELPER 175
CUTTING HELPER 220
I need a automatic for wages cell , (e.g If I entered O/L TAILOR IN CATEGORY CELL AND WAGES SHOULD BE CHANGE AUTOMATIC VALUE OF 280)Some one help me out
Mouli it can be done easily using various functions. simplest would be Vlookup.
Place the actual table in an excel sheet and then use Vlookup in the cell where you want the values to change when you change the category.
Example; (Below is your table)
A1 - O/L TAILOR -I B1 - 280
A2 - F/L TAILOR -I B2 - 305
A3 - HELPER B3 - 175
A4 - CUTTING HELPER B4 - 220
Cell A5 (Your Category)
Cell B5 = Where you want the automatic value as per category
Formula in B5 =Vlookup(A5,A1:B4,2,0)
Respected sir ;
sir we require some intresting learning like excel function , pivot table, vlookup, big data work etc
we learning excel last 3 year but we not perfectly use to some tool
we require some advanced training
is it possible provide me a training
i am living ahmedabad
my cell no is 9979775633
please any opetion tell me
thanq for another good post..
hi yaar can you explain how can i apply many function in one Column and and many more conditions given
Thankzz for sharing such an awesome article.. itzz really helpful
Hi Channdo
First of all congratulation for running successful web side and helping people perfection in Excel.
Chando I need your help for choosing right path for learning, I belong to accountancy profession and want to learn MS Excel for my profession.
I hope your views will be helpful for my career progression.
Regards
Waqar
I need some help. I have a table in excel. In one column of this table I have drop down values. When I paste this table in outlook email I would like the drop down to appear in the email that I share so that the recipient can select an value and share the response. Is this possible. Can some one assist me?
Is it possible to make add operation with some value and to replace the same value in the current cell?
For example I have to take a reading for 50 projects for every 5mins,Like that I may take nearly 100 times readings over every project on a day so instead of SUM formula if there could be anything which calculates the value which I entered to previous value.
After I initially commented I appear to have clicked the -Notify
me when new comments are added- checkbox and from now on whenever a comment is added I
recieve 4 emails with the same comment. Perhaps there is a means you can remove me from that service?
Thank you!
Hi sir I want to type in excle cell.. Cell left side Rs symbol and right side only type 0( zero) how can I do ? I can try but in place 0 type - sign but I want to 0 how can I do ?
@Vishal
Can you type an example of what you want here?
eg
I want: 123.45
To look like: Rs 123.450000
How to four Excel File Add with Formula without hyperlink
Thanks for all excel tips. All tips is very helpful for beginners. Great list of excel keyword shortcuts you have shared. Awesome!
hey i want to do the validation of PAN no. i.e. in between first five char are alfabets, next four are numeric & last one is also alfabet. its total 10 digit of no. so how can I check multiple PAN nos. at one tym. please tell me the formula.
Thanks for sharing excel shortcuts with us, It will help me a lot while working on the pc
Hi there,
Just find this place and let say: this is amazing!
Great job, man
Regard
I do believe all of the concepts you’ve introduced in your post. They’re very convincing and will definitely work.
Thank you! Excel sheets and skills is a must have working is this field!
Its useful
A very helpful & Mindblowing List of excel tips.
[…] http://chandoo.org/wp/2009/01/28/100-excel-tips-resources-to-celebrate-2000-rss-subscribers/ […]
this is karthik
In excel how to represnt if a vehicle is came today and the same vehicle should come on next 4th day if condition is ok if it is not it should shift automatically+1 in a row
This is karthik
In excel how to represnt if a vehicle is came today and the same vehicle should come on next 4th day if condition is ok if it is not it should shift automatically + 1
i have a little problem which i want solution.
i have a table like this:
a,b,c,d,j on separate column
e,a,d,f,m
g,h,c,a,s
b,d,g,e,d
how will i look for the pairs in this table
because the pairs here are
a,d
c,a
d,e
and which formula will i use to find the pairs. please help me excel Gurus.
and another one is
if they are figure like
10,20,21,26,80
10,45,42,44,80
15,21,23,26,32
45,42,74,76,50
26,10,45,88,89
which formula can i use to bring out all the pairs in the list of items
using cell A1:E5 as the cell range
Hi colleagues, its impressive paragraph about cultureand entirely defined, keep
it up all the time.
[…] Chandoo.org: 100 Excel tips […]
[…] Chandoo.org: 100 Excel tips […]
Amazing things here. I'm very glad to look your post.
Thanks so much and I'm having a look ahead to contact you.
Will you please drop me a e-mail?
Thanks for the list of formula and shortcuts. I m in the process of developing Excel templates for bookkeeping and these are very useful.
Thanks for the list of formula and shortcuts. Its very usefull