Excel 2007 Tips and Tricks

---

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…. Use it to search the file number over and over, returning a different field each time. I was able to hide and unhide the rows using the tips your provided, but was unable to unhide the column.. It did not work.

What could I be doing wrong? I was able to hide the column. 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. Tapan Have a read of http: 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. Wonderful easy to understand information. I haven't seen any other site with such excellent information. Would like tips on payroll stuffs like there are about employees and need to know how many extra hours worked and on which dates should be the end result. Tips on roster and forecasting of call center.

Macros for beginners and advanced users Many thnx. Yes, there are hidden games in Excel , usually called as Estern Eggs in Excel. Please see these links for more details. 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.

Go to format cells 2 make sure the option "Locked" is checked from protection tab. 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.

Excel Tips & Resources for Everyone

Do you have such a course. If so Please let me know. The command is not working properly. After putting in the Class module, it displays Name? What will be the reasons.

Do you know these Excel 2007 Productivity Secrets (Hint: Coffee is not one of them)

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? 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: Split the window vertically, adjust both the col widths to compare to fit the screen.

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. 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 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.

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. Kalyan Have a look at: Can you please let me know what is the eligibilty criteria, how to approach and how to get train on MVP. First of all, MVP is not certification course at all.

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. Im learning excel as i recently working on excel in making reports Surebdat09 Why don't you post some questions at the chandoo. Goto the bottom of the following page: LikeBe the first to like this post. 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. Hi Chando, I am a silence reader of your website. Today it forced me to contact you. Kindly help me to fix this issue.

When you paste it into Excel, instead of Paste, Right click and select paste, are there any other options? Hi, My source data is table data from MS Dynamic I have seen someone had the same problem in the following link but no solution described. You can see my whole problem in the above link.

Thanks in advance for support. Hui, I'm not able to work the following relation in excel, would you please help me? How would i do this? Would you please help me for the above problem? Firstly, Although I assist here at Chandoo. 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.

Hui, Thanks a lot, sorry to bother you, anyway I am really appreciate your cooperation. Really Its is a good blogs we can share so many new things and refresh our old knowledge as well: How do i fill down the upper cell data in column with one keyboard function? I need to fill them down their respective blank cells. It's really great to have this kind of stuff at free of cost. In one of the no. Try the "Text to Columns" feature. This will allow you to extract strings from one value into different cells, based on a delimiter. I have got a doubt about excel calculations: 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: Can some one help in changing the Date format from: After pasting the values to excel, I have tried the below items which doesn't works still!

For changing the Date format from: Dear Mr Duggirala, I came to know your details from the site: 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 In column B I have to write like this: 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? I am fairly knowledgable in Excel, but I have no idea what to put in this formula: What do I enter there? Do I enter something there? All I get is errors when I copy this into Excel. 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. And no more words to say And for your reference, here are a few blogs [ If your numbers are random, then you will need to use more than 1 formula to shuffle between those numbers.

Numbers need not be continuous. Press F9 to have fresh random numbers every time you want to have. But you can do what you want with a formula See an example here: 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 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. 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. 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". You can use this formula assuming A1 has the word LPG or whatever else you are searching for. 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? To insert a new simple chart quickly, press F I have downloaded chadooo mp4 archiver but it asks password.

Please help me to the video lession. It is 50 mb. Virendra Can you supply a sample file or more details please Maybe ask your question at the Chandoo. I working in work sheet of excell, I got suddenly hide some columns from IV to lastg. How it will be opened. I have two cells like below. I need a automatic for wages cell , e. Mouli it can be done easily using various functions. 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.

Respected sir ; sir we require some intresting learning like excel function , pivot table, vlookup, big data work etc. 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.

Recently on Chandoo.org

I hope your views will be helpful for my career progression. 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. 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 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? 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? Thanks for all excel tips. All tips is very helpful for beginners. Great list of excel keyword shortcuts you have shared.

Hi there, Just find this place and let say: Great job, man Regard.

Awesome stuff for you

E5 as the cell range. Hi colleagues, its impressive paragraph about cultureand entirely defined, keep it up all the time. 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? Mail will not be published required. Notify me of when new comments are posted via e-mail. One email per week with Excel and Power BI goodness. Home About All posts Contact Ask a question. Login to online classes.

Blog Training Excel School 2. Thank you and see you around. January 28, at 4: January 28, at January 28, at 1: January 29, at 4: January 29, at January 30, at 2: January 31, at 2: February 6, at 5: August 15, at 8: December 20, at 9: February 18, at 4: February 19, at March 9, at 2: May 19, at June 2, at 4: May 24, at August 7, at 1: August 7, at 2: August 7, at 3: Excel shortcuts to make duplicate charts, ranges and formats Pointy Haired Dilbert: September 25, at 5: December 30, at 2: January 16, at 3: January 25, at 1: March 2, at 7: May 22, at 7: July 13, at 6: July 14, at 2: August 2, at August 5, at 3: April 26, at 3: August 23, at 3: August 19, at 9: August 25, at September 29, at October 16, at 8: October 21, at 7: November 3, at 9: November 17, at 7: November 17, at November 27, at 1: December 1, at December 23, at 9: December 23, at 1: December 23, at 3: December 27, at December 27, at 5: December 30, at 4: Just hit it to launch a friendly office ish looking dialog to make changes to the settings etc.

If you in to mochas and trying to explore macros, then this one is for you. Did you enjoy the post? Do you want more? Well, it is not coffee, so you can consume as much as you want. Start with these and see where your mouse takes you. My name is Chandoo. Thanks for dropping by. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids.

Know more about me. I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel. Written by Chandoo Tags: Ask an Excel Question. Agree, for that matter the tips "locking a tool for repeated action" and "turing on clipboard pane" work in earlier versions although they look at feel a bit different. Thanks for the Developer's Ribbon mention!

Can't believe I overlooked this one - I went the tedious route of enabling particular forms through the quick access toolbar. Coffee is not one [ By the way, I always wondered.. Is it Jing or kind of? Coffee is not one of them - More help with how to do things in Excel I wish I was only kidding. I swear by Excel Okay, I swear AT it too Have been using it for 18 months. Pivottables in are certainly worth the price of admission alone. Hopefully the next version will address usability issues like the fact that the old dialog boxes that still lurk beneath the new interface often have rediculously small input boxes.

But now, when I'm faced with earlier versions, I struggle. I developed a custom toolbar in excel version. But since I upgraded to excel I cannot turn on the custom toolbar. The macros that were enabled on the toolbar are available in excel. How do u make a row-wise report when your data is cast column wise? Typical example is a pay roll. Where the employee name, his revenues, their totals, his deductions, their totals and nett pay is cast column wise.

But you need his payslip in a row wise format. It would be appreciated if I get a reply in the email id provided. Pradeep Your values in the report are just individual queries from your data table Each one can be customised based on some criteria that suits your purpose the location of them is arbitary. You will type in a name or Employee Number and maybe a Pay No and it will retrieve all your data and place where ever you want.

But each piece of data is an individual query from your source data.

More Tips and Tricks on using Microsoft Excel

Pradeep, I had a similar situation. Making it simple, I used Paste special, values, Transpose option, coupled with a little formatting. If you wish in detailed, I can send you the work sheet. Hello Chandoo and everyone! What I need is, i want a two-word Name of the ranges Here is an description on how to find it: Dear Chandoo, I appreciated your tips on saving charts or tables as picture files that can be used at other times. I also appreciated knowing that I can add hidden features to the ribbon, and that I can create a name range quickly by adding it to the formula bar. These are great tips for all!

Just wondering if you have a blog that addresses using excel with the keyboard only. I mean even working with charts and there elements. I would really appreciate an resource like that. It would emensely help a larger community. As was trying in excel to do the total amount which is in figure numeric to convert into words. Please let me know how to do both the things? When I finally figured out that after formatting it once I could save it as a template to use as the basis for future charts and just "Select Data" the new data , I was thrilled.

Mail will not be published required. Notify me of when new comments are posted via e-mail. One email per week with Excel and Power BI goodness. Home About All posts Contact Ask a question. Login to online classes. Blog Training Excel School 2. Do you know these Excel Productivity Secrets Hint: Coffee is not one of them Excel Howtos , Learn Excel - 42 comments. Thank you and see you around. May 26, at 3:

• NaranoKaze (Japanese Edition)
• The Value of Art
• Tourism: Between Place and Performance
• Bringing Mom Back From Dementia
• Italian Villas and Their Gardens
• The Cambridge Companion to St Paul (Cambridge Companions to Religion)
• Painted Over White: A Recurring Dream