Posted on November 27th, 2013 in Off-topic , personal - 386 comments

It is thanksgiving week in USA, which means our friends & readers in America are enjoying a fun long weekend with family & friends.

Happy thanksgiving day to you and your family.

Since most of our readers will be enjoying the holiday, I want to save Excel awesomeness for next week. Meanwhile, lets have our very first Ask me anything [AMA].

### Ask me anything.. what is it?

It is very simple. In the comments section, you can ask me anything. I will reply to you.

Anything from Excel to my life, SUMPRODUCT to C23.. you get the drift.

PS: I have enabled Disqus commenting system on the blog. This makes commenting on Chandoo.org even more awesome.

 Formula Forensics-No. 036: Calculating Costs that Vary by Year and Age Chandoo.org Holiday SALE, Starts on Wednesday – 4thDecember!
 Written by Chandoo Tags: ama, personal, polls Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 386 Responses to “Ask me anything”

1. Oz says:

When you were in the US, what was the oddest or worst food you encountered that is common here?

• Chandoo says:

My favorite food (in US) has to be Potbelly Chicken Salad Sandwich. I loved it.

Oddest in US: has to be a beef kebab we ordered thinking it was chicken. We (Jo & I) left it after barely tasting it. As you may guess, Jo & I do not eat beef.

Oddest food all over the world: this credit goes to a bowl of chicken noodles I had in Hongkong. It was nothing like what I expected a Chinese dish to be.

• Oz says:

Interesting. Thanks for the reply. Potbelly Chicken Salad Sandwich. I would never have guessed that. LOL!

• Claudio says:

I've done a table in excel with all my movies, divided for title, actor, genre etc. and I've added a column with hyperlinks to them so I can launch them directly from Excel.
Then I've done a pivot table to sort the movies as I like but when I add to pivot table the field with the hyperlinks these are no longer available, is there a easy way to solve this problem?

Thks a lot upfront

• My wife and I LOVE Potbelly's. Good choice!

2. ally says:

what are the steps one can make from an average user to an expert user in excel and to someone who can do modelling and vba in excel, thank you

• Chandoo says:

There are many ways one can become an expert. My favorite ways are,
- play with data and different problems often. See how else you can solve a problem
- learn every day (or week or month)
- help others solve their problems. Once you start sharing your knowledge, it just multiplies.
- Instead of aiming for perfection, aim for diversity.

- whenever you learn a new technique or feature, see how you can combine it with ideas you already know
- Pickup or copy good ideas from everywhere

• ally says:

thank you

3. haiderm says:

While building a dashboard, how to cleanup / prepare the data received in a report format easily? without involving to much manual effort. Thank you

• Chandoo says:

Excel has a lot of features to help you in cleaning up the data. My favorites are,

- remove duplicates button in data ribbon
- text import utility in data ribbon
- finding and removing duplicates thru go to special - http://chandoo.org/wp/2010/01/26/delete-blank-rows-excel/
- pivot tables (to transform the shape of data)
- formulas like TRIM(), CLEAN(), SUBSTITUTE(), FIND(), MID(), LEFT() and RIGHT()

I also use VBA for data that requires heavy cleaning.

4. shiv says:

What is the future of excel (maybe VBA)? how it can evolve in post-pc era?

• Chandoo says:

My ideal future of Excel involves these aspects...
- gesture and touch based data analysis and visualization
- plain English formula writing
- seamless integration with other apps / tools (not just MS tools)
- ability to share portions of workbook with anyone in any means (email, web, images, interactive charts...)
- strong programmability (ie VBA will live)

But I am not sure where MS will take Excel. Office is one of the biggest money earners for MS (second only to Windows). So I guess they would spend a lot of time and energy figuring out proper direction for Excel.

Whatever happens, people will continue to analyze data, understand what is going on and make decisions based on that. As long as there are such people, Chandoo.org will help them 🙂

• shivraj c says:

Thanks. I think web is the way forward for excel and office to go. Google docs is the biggest threat to Microsoft in the long run.

• Peter Merante says:

I would bet we've all seen good and bad infographics. I would like to see the ability to use data from Excel coupled with Infographic templates that leave little to nothing to interpretation and/or comprehension. Our overall ability to get our hands onto data is growing exponentially but our ability to depict the data into something meaningful is growing incrementally.

5. Rob T says:

Have you ever played around with the Navigator Utilities add in? I find it to be a much more powerful way of working with with sheets and named ranges in particular. It also has an enhanced Find function and a neat little feature to open or copy the current workbook path.

http://www.navigatorutilities.com/

I'm in no way affiliated with the developer of the add-in, but happy to spread the word, so he can continue his good work!

• Chandoo says:

I have never heard about them. That said I use very few add-ins as most of my work involves exploring how to solve a problem using what is available in Excel by default. But I think anyone working in a modeling or data analysis field would require few add-ins to speed up their work and reduce errors. Navigator utilities looks like one such add-in.

6. Arun Ghimire says:

is it possible to add another series in candlestick chart in excel..

for instance: adding bollinger bands to candle stick chart or the other way around.

thanks

• Chandoo says:

I am sure it is possible. Let me investigate a little and reply back with an example workbook.

• Arun Ghimire says:

yay .. thanks

7. Hev says:

How would I work out what date next Sunday is from any starting point? I've been asked this question and I'm struggling to come up with a simple answer - I'm sure you will have one! Thanks

• Rob T says:

If the starting date is in A1, try:
=A1+6-WEEKDAY(A1,3)
or
=A1+8-WEEKDAY(A1,1)

The difference is that if the given date is a Sunday, the first one will give you the same date and the second one will give you the following Sunday.

• Hev says:

Thank you. Much appreciated.

8. Herman Cheung says:

Hi! I have a list which is comprised of negative values, zero, and positive values. How do I list the minimum values in a column (excluding negative values and zero) in a pivot table without changing the data source? Thanks!

• Chandoo says:

If you are willing to use formulas, try this.

Assuming 'list' contains the data

the lowest value excluding negative & zero values is =SMALL(IF(list>0,list),1)
This is an array formula. So press CTRL+Shift+enter after typing.

Change 1 to 2 or 3 or... to get other lower values.

I am not sure if this can be done with pivot tables. But I would love to learn how this can be done. So if you come up with a solution, please post it here.

9. Patricia says:

What was the process for self publishing your book on Amazon?

• Chandoo says:

I am hoping to write an article describing this process. Meanwhile here is the gist.

PS: this is the book Patricia was talking about

http://chandoo.org/wp/resources/the-vlookup-book/

1. I created the book in MS word. There are some guidelines when writing for kindle. You can see them in kdp.amazon.com website. The main things to know are, all images should be inserted from INSERT ribbon (no copy pasting), do not use bullet points (Kindle does not yet support bullet points), make sure any tables in your book can be rendered on small screens of reading devices, use simple formatting, no headers & footers.

2. I went to kdp.amazon.com, registered myself as an author and completed all details including taxation etc.

3. Created a new book and uploaded the content. Tested it in their online viewer. If you have a kindle or kindle app, you can test the same on your device. They have desktop software to test your book as well.

4. I created a cover image and added other meta data (blurb, description, tags)

5. I set up the pricing & royalty details. \$9.99 seemed like the best price point for my book.

6. I published the book. It takes up to 24 hours to process this. Once it is ready, you will have a shiny Amazon page for your book.

7. I created a landing page for the book on my blog and announced it. People could purchase the book on Amazon or thru my site.

8. Optional: I signed up for amazon authors program so that I can create an author bio

9. Optional: I asked few people to review the book.

10. Optional: I visited the amazon pages of top selling technical books and tried to understand how they promoted their books. I copied few of these ideas.

• Patricia says:

Thanks Chandoo- that was really helpful. Appreciate the info.

10. League O' Robots says:

Have you ever created something like this? Any insights?

• Chandoo says:

no. My geometry and Excel skills are not that advanced. But I love the idea.

11. Paul says:

I have read that there are some formulas that are very resource intense (they bog down Excel because they are constantly recalculating - or something that really uses a lot of resources).
I like to use the Sumif and Sumifs functions when I am in need of looking up numeric values - even if I only need to lookup one value. It seems easier to to me to use it than to use index and match. It has the advantage over vlookup because you can do "left" lookups with it and your data does not need to be in sort order. So, it seems to be a good "default" formula for me to use whenever I am looking up values.
My question revolves around quickness and efficiency wise (does not bog down Excel) - is it just as efficient, more efficient, or does it bog down Excel - how does it compare to:
sumif(s) versus vlookup versus index versus match versus choose?
Thank you!

• Chandoo says:

I think sumifs, INDEX+MATCH, VLOOKUP have similar levels of performance for smaller data sets. When you are talking about large datasets, INDEX+MATCH or VLOOKUP tend to be faster (as they stop calculations once a match is found, where as SUMIFS must check for all rows).

• Jeff Weir says:

Often you can use Pivottables or sql instead of resource-intensive formulas like sumif etc. If you find that excel is so sluggish thst you switch calc to manual then you definitely wantto rrethink your approach

12. Porsche9II says:

What is the future of 64-Bit Excel in large companies and thus the future of 64bit xll development?
Thank you!

• Chandoo says:

I am unable to say anything about this. I am sure MS will not abandon corporates who rely on this. But not sure what their strategy is.

13. So far, which blog article on Chandoo is your favorite?

• Chandoo says:

Quite a few actually. I love the process of weaving a story around an Excel problem and presenting it. Since every problem is different, the articles are unique too.

That said, I particularly enjoyed these (off top of my head):

14. Michelle says:

Is there a way to set up SUMIFS and COUNTIFS such that you can list multiple criteria for one criteria range? For example, I want to sum Column C if Column A has either values "X,"Y" or ">Z". Right now, I just have to add the criteria range "Column A" three times, once for each Criteria Value (X,Y or >Z), which makes the formula very long and prone to typing error. Thank you!

• Hbboom says:

Michelle,
Try
=SUM(SUMIFS(C:C, A:A, {"X","Y",">Z"}))

This an array formula. Hence you need to enter it with Shift-Ctrl-Enter.

15. Waseem says:

Hello Chandoo

First, thanks for making our professional life easier. My question is, I am bored of using same old form controls. Is there an add on or application that can let us insert more beautiful and more useful controls?

• Chandoo says:

You can use Active-x controls. But they require more bells & whistles.

- your users will see a warning when opening the file.
- your users need to install any missing active-x controls before they can use the file.

My suggestion is mix form controls with features like slicers, timelines, hyperlinks so that your workbook looks and behaves well.

16. Everett Patterson says:

Hey Chandoo, can you do a tutorial to make an image lookup? Similar to Vlookup but I need to return images. I have a project that will need to use something similar to conditional formatting, but I will need to use custom images. I read an article about how to do it, but your tutorials are always easy to follow and understand.

• Chandoo says:

Sure. Adding this my book of ideas. Sometime in next few weeks 🙂

How can you drill down from a chart to the data that generates the chart segment? Preferably without using a pivot table.

• Chandoo says:

Simple. Set up a drill-down button (Insert > Shape > rectangle, and format it to look like a button). And then right click on the button, set up hyperlink to the source data.

Thanks for the reply, I should explain a little more. What I meant was if I have a bar chart how can I click on one of the bars in the chart and see the data that that bar represents. Thank you have a great Thanksgiving

18. HEDIEH says:

Do you know any tablet that can handle big excel spreadsheets? Have you looked at Microsoft PC tablet surface 2?
Thanks,
Hedieh

• Chandoo says:

Unfortunately no. I have a Samsung Tablet, but I do not use it for Excel. I just play games or read books on it.

19. Pán Jeskyn? says:

Hi! Why did you ever start with this application - MS Excel? What was the reason? I just wonder... Thank you for reply!

• Chandoo says:

I used to work as a business analyst when I started this. As you can guess, Excel & Power Point are 2 best friends of analysts. So I used to spend a lot of time with Excel everyday. I thought, 'why not write about some of the ideas I am learning in Excel on my personal blog...?' and that is how this whole thing came up.

20. Chaitanya says:

Hi Chandoo,

Can you please explain Index Match function easily with example.......

21. Anupam Das says:

Yours site is tremendously helpful, but I found it not organised....will you please make an index of the same in any order that is suitable, be it chronologically and topic wise. so that a new reader gets to know from where to start.

22. Shamil says:

Dear Chandoo,
Why don't you post examples of excel application in data analysis? I mean bussines forecasting or preparing reports ?

With best regards
Shamil

• Chandoo says:

We will. This is something I will be talking about more in 2014 and beyond.

23. Flavio Boss says:

Hey Chandoo, I was wondering if you or one of our excel masters can post a tutorial teaching Logistic Regression in Excel...

• Chandoo says:

I am not sure what this is. But I can research and put an article sometime in future. Thanks for the suggestion.

24. Grant says:

Hi Chandoo, I know you are a big fan of cricket. After watching an ODI today, I was wondering which bowler is the most successful power play bowler. Thought it could be an idea for a small dashboard?

• Chandoo says:

Hi Grant, I am fan of exceptional sports persons in any thing. I do not watch cricket. The last time I spent time watching was Sachin's last test match. Before that I haven't seen a single match for almost an year. So I am not sure what happened yesterday 🙂

But I will share more dashboards on the blog as I get new ideas on presenting insights.

25. Javich says:

Hi Chandoo. although I'm just a novice I can see you are awesome. I deal with excel 2007 and would like to know how to hide / not display a same value, for example; If I have in Colum "A" (current pay rate) and column "E" (new proposed pay rate) based on other condition but do not want to display it if it still the same as Column "A". How do I do it?

• Chandoo says:

You can use a formula to handle this. In column E you can write =if(new_calculated_payrate=current_pay, "", new_calculated_payrate)

26. Mdw says:

Here is a standard problem for us financial types. We have to consolidate multiple periods downloaded to different tabs of a workbook. How can I tabulate into one top sheet?

Here is more detail. I had to download month by month financials for 70 months (yes my client has a prehistoric system). 70 months = 5 years historic plus current year. Each came into a separate file that I moved into one workbook. The layout is exactly the same (Headings, columns etc) except the Account numbers down the left gradually increase as time goes on. I need to build a top sheet with the months in columns and accounts down the left in the rows. This entails going into various groups to insert rows so that each row has the same account lined up. Then copy that column across.

Ideally I would like to set up a form of look-up that will search through 70 Tabs to find the appropriate column to match to the same account description (or number). Thus my table will populate quickly and accurately.

Any ideas?

27. J D Lifsey says:

1. Is there a way to KEEP chart titles centered? When I click off
them they move either left or right requiring a manual move to
approximate re-centering.2. When copying a text box on a chart,
there is no paste on the right-click dropdown menu. Why? It is there in
other places for copy and paste. I have to use Shift-Insert to make the
paste.

• Chandoo says:

1. one easy way to center the title is to resize the title box to the same width as chart area. Then center-align the text.
2. Have you tried CTRL+V? I think that should work alright.

28. sherri says:

Encouraged by an example on this site, I put together a structured table in excel that contained the contents of my wine cellar. I have an input page where new data can be added with a click of a button. There is a search page that allows criteria to be selected, and the filtered list is provided with a click of a button just like the example provided on this site. However, when new data is added, even though the table range extends as it is supposed to, this new data is not included in the search, and so does not show up in the filtered table from a search. Any ideas?

• Chandoo says:

After new data is added, you can either manually re-apply filters by going to Data > Re-apply or press CTRL+Alt+L
You can also automate this by using a simple macro.

29. Robert says:

I have data that has a classification for Department (3 types), Group (15) Regions(60), Subregion(5), and Area (40).
I want to use Vlookup to show who should be assigned to each entry, but if I have a unique entry for each possible listing in my vlookup, that is 540,000 listings. It simply isn't necessary, as for two of the departments, Person A and Person B are going to be assigned in EVERY instance of their department. There are other instances where this occurs, such as 5 of the 15 groups not needing to be broken down further, etc....
Any advice for how to make a clean lookup, that minimizes the scenarios in my vlookup source to only what is necessary?

• Chandoo says:

I am sure there is an elegant formula solution for this. But I cant come up with one without looking at data and exact lookup problem.

I think you can also develop your own UDF to handle this.

• Robert says:

I ended up being able to put the Departments and groups into one lookup table, and each entry getting a classification. This classification was used as the lookup for the second table (which had the Regions, sub regions and area fields), which had the final "result" I wanted. By using two tables it should be easier to update, and both were less than 2000 rows, so it was not slowing down excel at all.

Hi Chandoo. Your blog is the best excel blog I know, congratulations.

One question... how can i extend automatically the conditional formatting in a table ? I apply the conditional formatting, but when i add new lines to the table, excel creates new rules when i would need that the same rule was extended to the new range.

Sorry if I'm not being clear, if necessary i could send an example.

Regards.

• Chandoo says:

When applying conditional formatting, first select the entire table column (or row or all values). This can be done by positioning the mouse pointer on column heading and when the pointer changes to black down-arrow, clicking. Once entire column is selected, any rules you apply should apply to entire table.

Even if you see new rules created for new data, it does should not slow down Excel or create problems for you.

31. Nimit Gupta says:

Why did you left your job? and how difficult was the decision?

32. Hunter Chandler says:

Chandoo - your site is one of my favorites on the net. I point everyone I know to you for their Excel questions. Grass roots, free sites like this are the true value of the internet, and I thank you for all of your time in building it.
My question is in regards to gathering data from multiple users in multiple locations that have VERY basic Excel understanding. What are some best practices you would recommend? Specifically, I report on our monthly patient volume at each of our clinics (I work for doctors). We still gather some of this data straight from location managers who keep a physical count each day and then relay that data up to me at month end. I get all of these in email format from ~10 different people and then have to manually put their data into my reports because none are formatted the same.
I want to create an "idiot-proof" way to collect this data in one shot and have that data aggregate how I design. I have created a master spreadsheet for each unique location/doctor data point by month, but it still requires manual data entry. Once I have it in my "clean" format I have a mini-dashboard that reports a variety of trends, but getting it "clean" is the pain point. Can you think of a good way to build a "form" or something that can EASILY be completed by the location manager and automatically update my report?
The root cause of the issue is our antiquated software and data collection, but if you could just comment on any experiences you have gathering data from a variety of users in an accurate way I would be very appreciative!

33. Oh yeah: Pivot Tables or Array Formulas? (or... Power Pivot?)

• Chandoo says:

Power Pivot first.
Then IF(and(have time, energy, know how), Array Formulas, Pivot tables)

34. Viswanathan Ramachandran says:

Thanks for the offer ! I simple have two questions:
1. What is the best way to retain and restore colors , borders... I mean any ormatting which is lost ... we have hundreds of worksheets with different colors specifically coded ... if something is messed up we always take the last file and restore .but work is much ..t to recheck
2. ITSM dashboards.. any plans ?
3. An ofcourse if you have any moderator role as partime ... I would be happy ! 😉

• Chandoo says:

1. If possible, use conditional formatting, styles & themes to define the colors & formatting. This way, restoring is easy. In the worst case, you can save sheet formatting in to a backup sheet (copy, paste formats) and use that to restore.

2. Sure. I will talk about these in a post sometime.

3. You are welcome. On our forum, we invite regular contributors to be forum mods all the time. Please register at forum.chandoo.org and share your knowledge often. We usually invite people to be a moderator after they add at least 1000 posts.

35. Vinny Stackhouse says:

First I want to say Thank you to all you have done to make such an incredible site over the years. I introduce anyone & everyone to your site whenever I am asked about Excel or Reporting.

My question is related to using Excel 2010 (32 bit only). I find if I actually want to make various pull downs and choices for the different measures on my reports, I could very well end up with hundreds of thousands rows of data. So what is your opinion on what the maximum amount of rows that should be used on a data tab?

• Chandoo says:

You are welcome...

I think Excel 2010 should hold good for at least 100,000 rows of data. I usually go with Power Pivot or a database for data beyond this size. This reduces the file weight and helps you focus on analysis & visualization problems.

• Ian Huitson says:

@Guest
There are advanced techniques available for filtering this sort of data using formulas that are super fast.
They are beyond the scope of Chandoo.org

36. Jayshree Bhakta says:

Hello,
I have been trying to find a solution to something and thought you might be the best person to ask for some guidance...

I am working with a client where they prepare a consolidating trial balance, and we review that. Based on our initial review, they make chances and send us second version. This can go on to 3rd or 4th version.

I wanted to build a template where on worksheet 'old' I can dump old version and on worksheet 'new' I can dump the new version, so the 'compare' tab can highlight where the numbers changed.

This would be a simple task, if the number of rows and columns would remain the same. based on our review, the client may add rows and columns. I need to compare only certain columns for each row, and unique identifier for each row and the columns I want to compare is text, not number.

Did I explain the problem correct? Let me know if you are willing to help. I could have just sent you worksheets, but I wanted to get guidance and try the solution myself, instead of just getting answer from you.

thank you so much for everything you have done at chandoo.org. You are amazing source of excel learning 🙂

37. lolatu2 says:

I developed a pretty cool workbook (that uses macros) in Excel. Is there a way to make this into an application, sort of like adding a portal to the workbook that also hides the calculations and macros?

• Chandoo says:

You can use User Forms and hidden sheets to do this. Please keep in mind the Excel protection is not fool proof.

38. Harry says:

Harry S
not to sure if the previous post was registered so maybe this is a repeat.
How to make an autoshape be in front of a user form . I can not work Zorder
I can get it as
combinergn formreg,,formrgn,,shapergn, XOR
to make a hole then position the shape to the hole so it looks on the form.
there must be an easier way ????
How to send you spreadsheets that you may find of interest to share freely with your groups.???
e.g. form to show dynamically the phases of the moon... the nice carpet patterns in Pascal's triangle

39. emma says:

Hello. my company is looking for best methods to develop competences related to VBA. Do you have any demo module so we can have a look on your method?

Can you point me to a how to sensitivity analysis example and possible sensitivity analysis add ins you might recommend?

41. grumpus23 says:

I'm struggling to get the delta symbols (up for positive down for negative) into the custom number format. I want to show delta for a value in a cell. In other words, a small triangle to the left of a value...just like a \$ but I can't copy and paste it in for some reason. Any help appreciated and thanks for all you do!

• Ian Huitson says:

@grumpus23

You can't mix fonts in a cell with a formula
and Typically you use Webdings fonts for adding these type of characters
But if you choose the Arial Font

Then apply a custom Number format like
[Red]?\$0.00;[Black]?\$0.00;\$0.00

you will get the result you seek

• Shailesh Patwardhan says:

I have used conditional formatting with 'traffic lights' to depict this. Works nicely. After inserting conditional formatting, if you want the numbers in the cell to dissappear you can change font colour to match background colour (so only the symbols are 'seen')!

42. propunta says:

Hi Chandoo,
i was wondering, if there is a way of adding extra data labels in charts without putting additional dummy data series?

• Ian Huitson says:

@propunta:disqus
It has a lot more functionality in that area

• Chandoo says:

Also you can use Rob Bevey's XY Chart Labeler.

43. Pat says:

VBA: Why, when you Transpose an array and you've defined the array as Variant, are you limited to 256 characters when you paste to cells in a worksheet? The 256 limit is not a limit unless you Transpose.

• Ian Huitson says:

@Pat
That is because not all the Excel functions have been properly or fully converted to 64bit

• Chandoo says:

Interesting... I did not know that.

• Ian Huitson says:

Similar issue with the 64,000 row limitations that still exist with some functions

44. Wilson Jones says:

Who can you recommend to design and build dashboards from and existing spreadsheet?

• Chandoo says:

I used to do this earlier, but due to excessive demand and lack of time, I have stopped taking consulting requests. I suggest posting in our forum so that any Excel experts can volunteer for this.

• Wilson Jones says:

thank you very much!

• Shailesh Patwardhan says:

I would like to help/work on this. No charge!

• Wilson Jones says:

Shailesh, Contact me and I will email you my worksheet.

• Shailesh Patwardhan says:

Contact me via @patwardhan on twitter. I could not figure out how to message you on disqus

• Shailesh Patwardhan says:

My email is p_shailesh @ hotmail.com

• Teh says:

could you give some shed of lights on my existing spreadsheets on how to improve it? how can i contact you?

• Shailesh Patwardhan says:

Hello Teh, can you contact me via @patwardhan on twitter. I could not figure out how to message you on disqus

• Shailesh Patwardhan says:

Hi Teh, My email is p_shailesh @ hotmail.com

45. zainul says:

what is iferror and where do we use it

46. José Lôbo says:

Hi, Chandoo,

I want to know if it is possible to have a UDF, where the expression (formula) could be read from a cell. This mean, to use different formulas to solve different Ordinary Differential Equations, using the Fourth-Order Runge-Kutta Method.

47. Rob Easton says:

What are some of the issues and awesomenesses that we might find with the Watch Window?

• Chandoo says:

I am not sure about the issues as I use watch window very little. But whenever I use it, I find it very powerful. If you know anything interesting about it, let me know 🙂

48. geeta2013 says:

I have a cell, and in the cell, it has 2004: 5, 600 followed by alt/enter which is followed by 2005: 87, 101.10. I need to figure out a way to strip each year into a separate column and the amounts pertaining to that year into a 2nd separate column and show them beside each other. Hope this makes perfect sense.

• Ian Huitson says:

@geeta2013:disqus
Select the column
Goto the Data, Text to Columns Tab
Delimitered, next
Tick the Other box and enter Ctrl J in the box next to Other
Apply

• Gijs says:

In these cases I typically copy the data and paste in Word, where you can do much more string replacements (eg replace ^p with ^t). Then paste back into Excel.
For pasting without formatting I use "puretext" utility - works great!

49. alphaomegabetachi says:

Hi Chandoo,

Happy Thanksgiving!!! May I ask if there is a possibility that you can offer "scholarships" in your online or masterclass excel courses?

• Ian Huitson says:

@alphaomegabetachi:disqus

I think that is a great idea!

But seeing as it belongs to Chandoo he will need to decide

• Chandoo says:

We have a holiday sales coming up next week. I have debated the idea of scholarships for a while. Although I like it, I am not sure how to execute it.

That said we follow below policies for all our courses.
- full time students in colleges get 25% discount on our courses
- Once you join a course, you get discount (\$50 usually) in all other courses we offer.
- keep prices reasonable and affordable.

I also strive to post as much information and techniques as possible on this blog for free. All you have to do is read the articles and learn. This is a big scholarship really.

• alphaomegabetachi says:

Surely, I know that the course prices are reasonable and affordable but it is still unreachable for me living in a third world country 🙁 and certainly, many thanks for the free blog as it is really the priceless scholarship I can afford 🙂

• Chandoo says:

We do offer lower fees to people in India. But unfortunately, due to various regulations in India, I am unable to offer similar discounted fee for people living outside. I understand that some of our course fees are very high for people in developing countries. I will consider offering free enrollments as part of any contests we run in future.

• alphaomegabetachi says:

That's great Chandoo...more power to you!!!

• Ian Huitson says:

Chandoo
What about a simple "Why I should get a scholarship Essay"
Limit it to 200 or 300 words
Have a panel of Judges to review

• Chandoo says:

Good idea... I think if we make it an annual event, it can be easily managed.

• alphaomegabetachi says:

Yes, an annual event, perhaps every Thanksgiving Day... 🙂

• Ian Huitson says:

Thanksgiving Day is only relevant to about 4.4% of the worlds population.

Another method maybe 2 months before Chandoo's next relevent course

50. Condorito says:

Is there life in Uranus?

• Ian Huitson says:

@Condorito
Yes, But not as we know it!

• Shailesh Patwardhan says:

and... they are good at excel too!

51. SEN KUM says:

Hi chandoo!
I want to know the formula for decision making using what if analysis to decide the best of 3 machinery based on best performer of the three.It should say accept or reject in the result coloum.Of the three only the best should be selected and the rest should be rejected.Thanks in advance..

52. Abhishek Sharma says:

Hi AwesomeChandoo.org,

First a suggestion : Please make youtube video for every topic as sometimes excel rookies have hard time understanding from just words. Videos do wonders.

Now the question : This link http://chandoo.org/wp/2013/01/09/3-upcoming-excel-courses/ says that MS Access & Databases for Excel Analysts course was supposed to be launched in Mar'13. However, nothing has come up so far. Any idea by when can we expect this.

• Chandoo says:

Thanks Abhishek for suggestions. I will try to add videos to our articles often.

I had to postpone MS Access course plan as I could not find suitable instructor for it. Although I know good deal of Access, I am not an Access expert. I am talking to few people and hopefully we will have it ready in 2014.

• Abhishek Sharma says:

Hi Chando,

I have another important question.

I am a student. One of my friends and I were thinking about signing up for your VBA classes in Jan'14, the one that allows to download the videos (Rs. 6000).

Question : If I take Rs. 3000 from him and sign up for the course (with my Rs. 3000 as well). Can I share the videos and other material with my friend?

• Chandoo says:

No. Under no circumstances, you are allowed to share the videos with anyone. If you can send a scan copy of your college ID card from your college email address, I can offer 25% discount to you.

• Abhishek Sharma says:

Thank for the clarification Chandoo.

Actually, I am pursuing Company Secretaryship (CS) and I have an Id card issued by ICSI. I am not sure if they gonna send the scanned copy of Id from their email address. If I send you the scanned copy of Id card myself (I can also send you the copy of my Dec'13 exam admit card as well as a supplementary proof that i am a student).

Can you please still offer me the discount. I really wanna do this course.

I look forward to a positive response from you 🙂

• Chandoo says:

If you are not employed (my understanding is that CS is something you do while working full time), I can extend the discount. If not, I can still offer you 10% discount. Please email chandoo.d@gmail.com for details.

• Abhishek Sharma says:

I am not employed. I am pursuing CS and also preparing for MBA entrances. My CS exam admit card will be issued after 5th Dec'13. once it is issued, I will email that along with the copy of my CS id card.

Thanks a lot Chandoo.

• Abhishek Sharma says:

I have a question regarding my career. Although I am not as awesome as you but I love working on excel and have learnt plethora of things from chandoo.org. Therefore, with an interest in Excel and analysis. I am an Economics Hons grad. Which specialization of MBA do you think will suit my interest.

And one more thing. I know you have done MBA. What was your specialization in MBA ?

• Shailesh Patwardhan says:

Hi, some of this is best done via email 🙂

53. Patra, Kotini says:

I need to register for Microsoft certified Professional course in MS-Office, Can you suggest me where i can do and how can i do...I am staying@Hyd... Please help for me...

• Chandoo says:

54. Ilyas Mansoori says:

Hi Chandoo, I am a big fan of this blog and have learnt a lot from it. Thanks a lot for this precious information.
I wanted to know the easiest method to create a database by restricting the use of functions (as they bring down the speed). I am a civil engineer by profession and to prepare bar bending schedules we need to enter many tedious formulas and as there are many entries in the sheet, it slows down the PC. So is there any way to avoid such situation. Also I wanted to know whether we can insert customized shapes using lookup formulas.

Thanks & Regards.

55. Sabir says:

I am using MS Office Professional Plus 2013 which is one of the tools required to for Power Maps, however every time I run Power Map I get an error message: "Power Map encountered and error and cannot be launched. Please check your internet connectivity and try again." My internet is working just fine, do you know of a possible work around to this, have read a few blogs but none offer a concrete solution, I know it is still in its testing phases but just thought you may know of a solution.

56. Sabir says:

I am using MS Office Professional Plus 2013 which is one of the requirements for Power Maps, however when I run Power Maps I receive an error message, "Power Map encountered an error and cannot be launched. Please check your internet connectivity and try again.", thing is my internet works just fine, I know it is in its testing phase but thought you may know of a possible work around solution, have a read a few blogs on the topic but none offer a concrete solution, hoping you have the answer.

• Chandoo says:

Hi Sabir... I am yet to play with Power Map. So I cannot comment. I suggest posting this in MS product forums or support group so that someone can check this error and fix.

• Sabir says:

Thanks Chandoo, I did do that but no responses that are conclusive as yet...
Thanks for all your knowledge sharing, God bless...

57. Dan says:

Thank you for your awesome teaching, wow. I regret not meeting you when you were in NE Ohio, next time you get back here, I hope to meet you in person.

Your teaching has improved my skills so much, that my supervisor has challenged me to create an interactive calendar that would track company holidays, shutdowns, and employee vacation schedules. Can you help me understand how to write the formulas that automatically adjust the weeks, months, and years as the year changes?

Thanks again, keep up the good job you are doing as a Dad, your kids happy faces show you are doing a good job.

58. Anil says:

we have prepared couple of dashboards using slicer, inspired by beautiful illustrations on your website :). After publishing them with the stakeholders, we realised that they are using MAC computers and that Excel 2011 doesnt support Slicers, they they work awesomely in 2010 version on windows computers. Felt dejected and have been trying for a solution from a month. Reaching out to you hoping that you can help me with a work around/ solution to my problem

59. Ho Cong Huong says:

Could you guide me how to create flexible chart, waterfall chart, dashboards, and football field for building financial modelling? Thanks a lot!

• Ho Cong Huong says:

Thanks again. I have got the links you gave, and I think it was really a gold mine of Excel techniques. Another question: How can I create the sheet "Cover" of the "Tata Steel financial model" at the link: http://www.edupristine.com/blog/financial-modeling-for-tata-steel-fpo/?
And was the sheet "Assumptions" of that model created by using form (box) control? How can I create it?

60. Abhishek Sharma says:

Hi Chandoo,

I have a question regarding my career. Although I am not as awesome as you but I love working on excel and have learnt plethora of things from chandoo.org. Therefore, with an interest in Excel and analysis. I am an Economics Hons grad. Which specialization of MBA do you think will suit my interest.

And one more thing. I know you have done MBA. What was your specialization in MBA ?

• Chandoo says:

Hi Abhishek...

I think your specialization in MBA has very little to do with what kind of work you do after passing out. For example, I did specialize in marketing and ended up in an IT company. Since you already have Economics background, spending sometime to learn Marketing, HR or IT might be a good idea. This gives you a good perspective on all aspects of a business.

I am unable to recommend anything confidently as a lot depends on what you want to do and how well your foundations are.

can u provide the score card format for Outbound Call Center

62. Gijs says:

We have an XLS template where people select text values from a dropdown menu.
But often they instead paste values that look the same but are subtly different (eg trailing space).
How can you protect a file from pasting while still allowing dropdown selection?

• Chandoo says:

I do not think you can limit pasting. But you can use formulas like TRIM() and CLEAN() to remove any spaces or invisible characters from input text.

63. John says:

Hi Chandoo,

I want to start up my own Excel website to sell downloadable tutorial videos. What advice would you give to people like me and, secondly, what is the best way to promote your website to get traffic?

John Michaloudis

• Chandoo says:

Hi John.. Good idea.

My suggestions:
- Narrow down your focus to 1-2 topics so that you can gain traffic and people associate you with those topics.
- Do not be afraid to create and sell products.
- Experiment with various price points, product options to see what works.
- Do not expect overnight success. Instead give your venture at least 18 months to establish. All the while create valuable content and encourage users to learn from (with) you.

I suggest checking out startupdesi.com for some tips & advice. That is where I write more about my business (Chandoo.org) and how I run it.

• John says:

Mate you are the best! Your first two points are spot on and it is exactly what I have planned to do. Your last two points I have to work with & have patience but that will be my learning curve.

I am sure I will reach out to you to say thanks for your help and hope we can do business together one day 🙂

Cheers,

John Michaloudis
4th placer in Chandoo.org dashboard competition (I should have won)!

64. N R K MARIMUTHU says:

excel used for preparing profit and loss account, balance sheet, project report. forms etc. These were prepared by copying existing one and by changing new fill. I think is not an effective use of excel. Please advise.
n r k marimuthu

• Chandoo says:

You are right.. I do not recommend Excel for maintaining financial accounts or ledgers. It is a poor choice. I suggest using a proper tools for such things and rely on Excel for analysis or reporting.

65. Vivek says:

Hi ,

Can u please explain how to use macros to run Pivot Table and Charts . I am getting Run time error when creating Macro for running Pivot Table . My sheet has been saved in xls format . Is there any way to do this ??

• Chandoo says:

Hi Vivek,

I am unable to comment without knowing the exact details of this error. Can you register on our forum and post your question there? Please visit http://forum.chandoo.org/

66. Sabir says:

I am using MS Office Professional Plus 2013 which is one of the requirements for Power Maps, however when I run Power Maps I receive an error message, "Power Map encountered an error and cannot be launched. Please check your internet connectivity and try again.", thing is my internet works just fine, I know it is in its testing phase but thought you may know of a possible work around solution, have a read a few blogs on the topic but none offer a concrete solution, hoping you have the answer.

• Chandoo says:

I have not yet played with Power Maps. I suggest posting this problem in MS forums or customer support group so that someone from Power Map product group can fix the bug.

67. Nandkumar says:

Hello Chandoo,

I believe you to be an inspiring figure for Indians who aspire to go from employed to self-employed. With rapid development in the IT field I would like to know your views about the sustenance of Excel in future where applications like Access are providing more flexibility and ease to manage and analyze data. I love excel even though I am an amateur but want to know if it would be prudent to hone my skills in excel or to go for products like Access. ( Note : I love data management, analysis, forecasting... in short playing with data to get maximum out of it )

• Chandoo says:

I think spreadsheets will continue to exist in one form or another for next few decades. My suggestion is, hone your skills in answering questions, understanding what is going on and presenting results. This is best done with Excel right now. Even though Access may offer some of this now (and in future), I think Excel will give you greater flexibility as an analyst.

That said, learning about databases & SQL is also very valuable.

• Nandkumar says:

Thanks for the insight Chandoo. I would also be honored to have your advice on planning a learning path to become awesome at excel. I believe your excel school is created for the exact purpose but can you just break down the learning path for me like : understanding toolbar, knowing formulas, Pivot tables, Macros, VB..... In essence I would like to know the order in which the various elements of excel should be mastered and are taught in your excel school. To understand the order in which I can optimize my learning.

68. Kaushik Dey says:

Hi Chandoo, like u I too have a big crush over Excel. Now a days I fighting with some vb macro related. Need ur help.

Synopsis as below:

1. Front Hand - Login Sheet for all Managers

2. Front Hand would be at diffrent locations.

3. if proper login happens then it should open the attendence marking sheet related only to his / her BU

4. Marking sheet should highlight only current date attendence and should not allow back date changes.

5. Once all marking have been done then at specific time via ADMIN rights all details can be pulled from diffrent location to a single database.

Below is my coding related to attendence marking tool, Point no. 1 - 3 is successfull. But not able to move further.

Sub userPass()
Application.ScreenUpdating = False

'Check to see if data is entered into field: txtUserNameIn
Exit Sub
End If

'Check to see if data is entered into field: txtPasswordIn
Exit Sub
End If

'Check to see if the Username & Password entered is a valid username in the 'User Register'
'****************
Dim wk As Workbook
Dim sh As Worksheet
Set wk = ThisWorkbook
Dim atsh As Worksheet
Set atsh = wk.Worksheets("Attendance")
On Error Resume Next
'****************
Select Case Err.Number
Case Is > 0
Exit Sub
End Select
On Error Resume Next

Select Case Err.Number
Case Is > 0
Exit Sub
End Select

Sheets("Attendance").Select
atsh.Range("A1").Select
Else
'ThisWorkbook.Close (I'm not wanting to go-live with this bit yet, so commented out)
End If
End If

Application.ScreenUpdating = True

End Sub

Sub Attendance()

Dim wk As Workbook
Dim sh As Worksheet
Set wk = ThisWorkbook
Set sh = wk.Worksheets("Attendance")
Dim fltrng As Range
Dim finrow As Integer
Dim fincol As Integer
finrow = sh.Range("A65536").End(xlUp).Row
fincol = sh.Range("HA1").End(xlToLeft).Column
Set fltrng = sh.Range(sh.Cells(1, 1), sh.Cells(finrow, fincol))
Dim xdate As Variant
xdate = VBA.Format(Date, "dd-mmm")
fltrng.AutoFilter Field:=1, Criteria1:=xdate
sh.Range("A:J").Locked = True
sh.Protect contents:=True

End Sub

69. randy says:

Hi Chandoo, i'm a big fan of yours, i regularly read post, comments,questions on this site,,

i would like to ask, how can I fetch exact date using index + match(multiple conditions)... or can i really use this index +match formula?

it is like this:

Column A Column B Column C

7-5-2013 Area 1 Branch A1-1

7-8-2013 Area 1 Branch A1-2

7-10-2013 Area 1 Branch A1-1 (i want to fetch this)

8-22-2013 Area 1 Branch A1-1

8-26-2013 Area 1 Branch A1-1(or this)

8-29-2013 Area 1 Branch A1-1

• Chandoo says:

Thanks for your support and love Randy...

What is the logic behind deciding 3rd item or 5th item as the one you want?

70. Catherine Parkinson says:

Hi Chandoo,
Early next year I'll be undertaking my first Microsoft Excel Expert Certification training/exam, do you have any advise or anecdotes about the MOS training path ?

• Chandoo says:

I have never taken MOS exams, but I assume they should be easy for someone who is using Excel for a few years and following blogs like ours. If you need some help, I suggest enrolling in Excel School - http://chandoo.org/wp/excel-school/

how to check the difference of time with a non formated long date & time in this format [H]:MM:SS

• Chandoo says:

Assuming A2 and B2 are proper Excel dates, you can use =TEXT(A2-B2, "[H]:MM:SS") to get what you want...

but chandoo the both dates are not in date format and the prob i am facing is as it is a long date so when i try to subtract the values by using the above formula it is giving me the #VALUE! error. and even tried the nonformated text to =TEXT((LEFT(A2,10)),"dd/MM/YYYY")

=TEXT(TIMEVALUE(RIGHT(A2,11)),"[H]:mm:SS")

=CONCATENATE(C2," ",CV2)

and vice verse for B2 also in D2 and tried using the above formula but still i found the same error so please suggest

• Hui... says:

The format only effects how the date / time is shown
Internally it is still eg: 30:30 but will display as 8:30 am

72. Prashanth says:

Could you tell about Excel Plug-ins. What is and how it useful?

• Chandoo says:

Hi Prashanth..

I assume you mean Excel add-ins. These are little blocks of VBA code that can enhance Excel functionality. There are thousands of such add-ins available. You can search based on your need and find right add-ins.

My suggestion is first understand what Excel can do. Installing and using add-ins without exploring Excel completely can be harmful and time wasting.

73. Mark Scott says:

What would you recommend as the best desk reference book for Excel? Thanks.

• Ian Huitson says:

Mark Scott

It really depends if you are after a generic Excel book or one dedicated to a specific eg: Charts, Power Pivot etc
Most people recommend John Walkenbachs - Excel Bible
But I'd have a look here: http://chandoo.org/wp/excel-vba/books/

• Mark Scott says:

Thanks for the tips. One for charts I think is what I need. I'll check the links!

• Chandoo says:

I agree with Hui. It all depends on what you want to learn. That said, I recommend these books.

by John Walkenbach:

Excel 2010 / 2013 bible
Excel 2010 / 2013 Power Programming
Excel 2010 / 2013 formulas

For more book recommendations, please visit http://chandoo.org/wp/shop/excel-vba-books/

74. Shankar Narayanan says:

Hi Chandoo...I work in a private bank and my job function involves lot of analysis with large dumps..Presently i perform the analysis with Excel 2010..but the response becomes very slow due to its file size...approximately more than 300 MB with more than 4 lakh rows...Kindly suggest me a way out to perform the task such as pivoting this 4 Lac rows...

75. Shankar Narayanan says:

Hi Chandoo....Please give me a VBA excel macro to calculate net working hours between 2 date & time entered...For example, the macro should calculate the exact work timing between 9.30 a.m. to 5.30 p.m and should exclude national / regional holidays and sundays...Networkingday function returns the value only in days and i need in HH:MM format..

76. haiderm says:

Hello Chandoo, Can we make a cell compulsory in a worksheet? and disable generating the output of formula's to work until that particular cell is filled with proper value and no one is allowed to proceed further without inserting the value? no vba solution
Thanks for all the awesome ideas and solutions.
Munir

• Chandoo says:

Hi Munir... you can use data validation, IF() formula and conditional formatting to handle this. But it is not fool proof. If the user wants to look at formula output they can look. That said, you can turn off the formula output with a simple IF clause like =IF(data_entered, show result, show warning)

77. BIju Chacko says:

Are you still actively involved with Excel School? I posted a question almost a week ago, still no response.

• Chandoo says:

Hi there.. yes, but for questions, I have staff who responds. We must have missed your question somehow. I will check.

78. Claudio says:

Hi Chandoo, I've done a table in excel with all my movies, divided for title, actor, genre etc. and I've added a column with hyperlinks to them so I can launch them directly from Excel.
Then I've done a pivot table to sort the movies as I like but when I add to pivot table the field with the hyperlinks these are no longer available, is there an easy way to solve this problem?
Thks a lot upfront

• Chandoo says:

Hi Claudio... I don't think you can get hyperlinks inside pivot table naturally. But you can use a macro to lookup the hyperlink based on selected movie and launch.

79. Andrew Brodie says:

Hi Chandoo,
Great website!
How do you create hover points for an object such as a shape in excel 2007? Hovering over the shape will display a message box or custom form that will display whatever information you need it to.
Thanks

• Ian Huitson says:

@brodiean:disqus
You could use a Hyper Link
They allow Hovering and can display text

• Andrew Brodie says:

Thanks Ian, however How will that work if the info I want displayed is in other cells and may be substantial. I can see how this will work if I wanted a simple phrase or single line to display but in this case I want a set of statistics that are associated with the shape?

80. You did one article on a data visualisation resume once before, but I was wondering, if you were to approach creating a resume in excel, how WOULD you do it? I'm presuming interactively to demonstrate your skills, maybe - could you consider doing a topic or series around this?

• Chandoo says:

Good idea.. I will certainly do something like this in future.

81. Neeraj Kumar Agarwal says:

Dear Sir, Firstly I would like to offer my sincere thanks and regards to you to keep offering us your ideas about excel which helps us to get awesome in excel and from the bottom of my heart I treat you my "Guru".

Please continue this as there will be countless fellows like me who read your article and love you.

Sir as you have provided us the opportunity to ask you anything than I shall take this opportunity to ask you about two things :

1) Normally we apply conditional formatting into the entire data and based on the condition some cells get colored. I want to sort that data as per the color (after applying conditional formatting) without any macros. Is it possible?

2) While downloading data other application into excel the figure formats gets changed. Manually I have to convert them into numbers. In case of a particular column I change it manually but there are instances when face the same problem with every column. Is there any way out so that I can convert the entire data into numbers without using macros.

Neeraj Kumar agarwal

82. Neeraj Kumar Agarwal says:

Dear Sir, Firstly I would like to offer my sincere thanks and regards to you to keep offering us your ideas about excel which helps us to get awesome in excel and from the bottom of my heart I treat you my "Guru".

Please continue this as there will be countless fellows like me who read your article and love you.

Sir as you have provided us the opportunity to ask you anything than I shall take this opportunity to ask you about two things :

1) Normally we apply conditional formatting into the entire data and based on the condition some cells get colored. I want to sort that data as per the color (after applying conditional formatting) without any macros. Is it possible?

2) While downloading data other application into excel the figure formats gets changed. Manually I have to convert them into numbers. In case of a particular column I change it manually but there are instances when face the same problem with every column. Is there any way out so that I can convert the entire data into numbers without using macros.

Neeraj Kumar agarwal

• Chandoo says:

Hi Neeraj... Thanks for your lovely comment. I am glad you are becoming awesome in Excel.

1. Right click on the colored cell and choose sort.

2. If you are using text-import facility (Data ribbon) to do this, you can tell Excel to convert imported values to numbers. If you are using SQL or database connection to bring data, then make sure the source columns are coming as numbers. If they are text, use an SQL function to convert it to a value or remove any extra formatting symbols (\$,. etc.)

See attached images.

• Neeraj Kumar Agarwal says:

But still I have been facing problem,

1) We apply conditional formatting into entire data. Some cells get colored say "yellow color" which might be in different columns. I want to know how can we sort all the columns which are having yellow cells .

2) I want to convert all the columns into number at a time. But while trying with text to column system is asking to select only one column at a time.

83. venkatesh says:

Hi Chandoo.So happy to see a page like this. I have a set of data in excel with Product(column a), product category(b) , Qty (c), Sale Price (d) and Costs in e. I have a specific product category for which a sum if formula together with sumproduct is needed to compute the sales and costs for that category just one figure in one line. Not in the conventional way though;Please help me

• Chandoo says:

=SUMPRODUCT((colC)*(colD)*(colB="category name")) to get the total sales.

• venkatesh says:

Hi Chandoo. Thanks for your reply. I tried your formula but the answer would come as"VALUE" FOR MY CATEGORY"VERBR" Iin my table. The formula is=SUMPRODUCT(\$C\$507:\$C\$557,\$D\$507:\$D\$557,(\$B\$507="VETBR"))
Any suggestion.
Regards,
Venkateshd

• Chandoo says:

Please check.. the formula should be,
either

=SUMPRODUCT(\$C\$507:\$C\$557*\$D\$507:\$D\$557*(\$B\$507:\$B\$557="VETBR"))
or
=SUMPRODUCT(\$C\$507:\$C\$557,\$D\$507:\$D\$557, --(\$B\$507:\$B\$557="VETBR"))

• venkatesh says:

Thanks Chandoo.The 2nd formula is perfect. the 1st does not work though:)

• Eugeny Sattler says:

Hello Chandoo, i am in the same situation which I want to solve with SUMPRODUCT. I dont want to use SUMIFS as I want compatibility with Excel 2003. SUMPRODUCT works OK as long as source range does not include header row and as long as there are no N/A in quantity column. How do I make SUMPRODUCT to exclude #N/A lines from source data? And why SUMPRODUCT does not work if I refer to all 65536rows of Excel 2003 sheet?
Thank you,
Eugeny Sattler

84. Sjeilo Sjeise says:

Hi, Is it possible to have 1 big source file, and have separate excel files for each customer with their own margin. If i change the buying source, all external files also change ?
Hope to hear from you, or you could show me were i should look to study. thx!

• Chandoo says:

Hi there,

This is possible in 3 ways.

1. If you have Excel 2013, use Power Query (or Data Explorer) and set up query such that each file extracts only a portion of data from master file as per the condition.
2. Use Excel queries or connections to connect to source file and fetch only part of the data (should work in almost all versions of Excel since 2003.)
3. Use VBA to automatically fetch data from source whenever file is opened. Here is an example.
http://chandoo.org/wp/2011/10/19/split-excel-file-into-many/

85. Win Morgan says:

What's the best way to create a multi-language workbook? One that allows a user to select (for example) English or French, and the words change automatically throughout the workbook to that language.

• Chandoo says:

If it is just a few languages that need to be shown, then you can use below approach.

For every literal (word or phrase) displayed on the screen, set up a mapping table like this:
WordID EN FR DE ES...

Then, name this entire range (from 2nd column to last column) as phrases.
Then whenever you need nth word in language in column # m, use
=INDEX(phrases, n, k)

And you can set up a master sheet where all word IDs are laid out.

As this sounds like an interesting topic, I will try to do an article on this later.

• Win Morgan says:

Thank you. I'm trying it

86. CHOOSE command says:

Is there a simpler way to add the elements of the CHOOSE command besides going to each cell clicking typing a comma clicking a new cell, etc? I have a spreadsheet that shows a listing of expenses by month. When the user changes the value of the month all the expenses are updated. However, it takes a super long time to set up the worksheet because there are many expense line items and twelve months in the year.

• Chandoo says:

Have you considered using either INDEX or OFFSET? They should work for this example very well. If you are not sure how to use them, please post a screenshot in the comments. I can help.

87. James says:

Having a raw data sheet in a table format wtih MD names, Date of surgery, Date of Birth, type of surgery, and Surgery category, I would want to have data validation boxes on the second tab that when I pick a surgeon, type of surgery and surgery category, the list below would show the date of surgery and date of birth. What is the correct array to use, vlookup, index & match or something else?
James

• Chandoo says:

Interesting question. The simplest way is to use Advanced Filter + VBA to handle this.

If that is not possible, if you have Excel 2013,

you can set up source data as a table and use slicers to filter intuitively.

If none of the above applies, then you can set up array formulas to filter down the list. The actual process & formulas depend on how many different selections you have. I will try to post an example once I can get some free time.

88. David Atwater says:

Hi Chandoo,
When creating a Gantt chart based on a Stacked bar type, is there not a way to dynamically change the Minimum (date) in the Format Axis - Axis Options dialog? It seems the only option is to select 'Auto' which is unclear what the value is based upon, or Fixed, which is a number only - no formula or range name reference is allowed.
Love your blog - thanks for sharing insights into Excel (and your life!)
David

• Chandoo says:

@davidatwater:disqus This is not possible with current features in Excel. May be in a later version of Excel MS would add support for linking a cell value to chart axis min / max.

You can use VBA to automate this though.

• David Atwater says:

I was trying to avoid using VBA but I might have to go there after all. Thank you for your reply - much appreciated.

89. Sandip says:

Hi Chandoo

is this your email ? chandoo.d@gmail.com

I am wondering whether you also reply to question from that email ?

I asked questions in that email.

Thanks

• Chandoo says:

Hi Sandip,Yes, that is my email. As we receive 100s of emails everyday, I do not reply to Excel questions in general. That said, if I find the question interesting and I have time, I try to reply. If you have questions, your best option is to post them in our forum - forum.chandoo.org

• Sandip says:

I am so happy that first time, I have got reply from Purna.

I raised many question in that email. Even I asked in that email how to reach directly to Purna. I got reply from Vijay and Ravindra who were not able to resolved my excel question. At least, Vijay and Ravindra should have suggested me the way to reach you directly.

I was expecting reply direct from you as student of your excel course. I have been little bit disappointed with reply from Vijay and Ravindra who did not suggest me right way to reach you.

I am happy to reach you via forum. I find new forum (ask me anything) very interesting so can I raise question on this forum ?

I have learnt important things just from few of your week lessons. I like the service you have offered to us. Also wondering, is forum the only way to reach you for student who joined your course ?

-Sandip

• Chandoo says:

Ask me anything is going be an annual event. We will close this post for new comments probably in a few days.

• Sandip says:

Thank you very much

90. venkatesh says:

Hi Chandoo,
Bravo for the formula you shared in the morning; it may look a tiny work; but for me it worth a million. Do you have in your inventions bank a method that converts numbers to words in excel. Kindly share if you do have one.
Regards,
Venkatesh

91. Shankar Narayanan says:

Hi chandoo....Please help me out with a macro to calculate the difference of net working hours (9.30 a.m. to 5.30 p.m) between 2 dates excluding sundays & other holidays....

92. Sly says:

How do I handle this? I have two worksheets, the first is 'payment' and the second is 'General' now how do I transfer data from payment in specific cells to a single row in general leaving the serial number increase by one in payment.

93. Terry says:

in powerpivot, how can I increase the # of rows to be displayed when clicking/selecting
a cell(in values portion of the powerpivot)? (i think the default it returns is 1000 rows only).

• Chandoo says:

I am not sure how this can be done... Power Pivot add-in is not programmable thru VBA as of now. I suggest going to Power Pivot window to explore source data.

• Terry says:

Thanks Chandoo. I was able to changed/increase the retrieve record/row under Data ---> Properties

• Chandoo says:

hmm.. did not know that. Thanks for sharing 🙂

• Terry says:

Thanks Chandoo. I was able to changed/increase the retrieve record/row under Data ---> Properties

94. Xcelnewbie says:

Hi Chandoo, I would like to ask for a simple vba code that will allow me to convert a sheet into PDF before printing it. Below are the things i would like to do:
Sheet 1 is the encoding sheet where i will put the PRINT Icon
Sheet 2 is the formatted form where info from Sheet 1 will be transferred for printing. This is also the sheet that will be converted to PDF.

• Chandoo says:

Hi there...

You can use range.ExportAsFixedFormat (xlTypePDF, "file.pdf") method to convert a range to PDF.

The actual syntax and code depends on your workbook.

95. Brian Shaw says:

What is the best route to getting a job as a financial analyst?

• Chandoo says:

The best route is obviously not taking advice from a marketing & systems major whose closest brush with financial analysis comes from inspecting his own mutual fund portfolio, scratching his head vigorously and closing the workbook - almost every week.

The second best route goes here:

1. Gain some finance knowledge thru studies or work experience.
2. Learn about specific industry / geographical market for which you want to become a financial analyst.
3. Download annual reports of large companies in that industry and try to model their finances using Excel
5. Finally understand that financial analysis is as much an art as it is a science. No one can forecast a company's future. So understand the human elements that go in to this.
6. Convince interviewers that you really enjoy analyzing finances and presenting results. Show them examples of models you have built and explain the rationale behind them.

All the best.

• Brian Shaw says:

Chandoo, thank you so much for the detailed reply. You didn't mention the financial modeling school, but I signed up for it earlier in the week and think it will be very helpful with my goal.

Thank you again, particularly for those last two, very insightful points.

96. Steven Morgan says:

Dear Chandoo,
I have been following this site for several months and picked up a few useful tips.

Thank you for your time and effort.

I have two simple questions

I have to pull text files off a central server. The server copies the file to a folder six levels deep from the root directory and I have keying in the path each time around. Could a macro be done that would allow me to enter the file name and then pull it off the server for me?
I have to do this for a couple of files at every month-end.

Then I have to use text to columns, I parse it using fixed width and I have to add, move or delete some lines to get my fields. These column widths are the same each month., Would a macro to do the fixed length be possible.

Thank you

Steve
Wilmington North Carolina, USA

• Khushnood Viccaji says:

Hi Steven, my answer to the first question about pulling the file from the server by specifying the folder name : If the folder name is mostly standard, with only the current month's folder name to be changed, then you can always build-up the full folder name easily by using formulas.

E.g. if the standard name is : \serverfolder1folder_L1folder_L2folder_L3, then
- paste this text in cell A1.
- In cell A2, you can enter the current month's folder name in the format : YYYY_MMM
- In cell A3, paste the file-name to be opened.
- In cell A4, enter the formula - =A1 & "" & A2 & "" A3

This will build-up the full-filename for you.

• Khushnood Viccaji says:

As for the text-to-columns bit, YES a macro can easily parse the data from the text files, once the full-filenames are stored in relevant cells.

However, there is another very useful feature in Excel which doesn't even require a macro.
You can use the import Data from External sources option from the Data tab (in Excel 2007 or higher).

The first time you do this, Excel will show the text import wizard dialog box in which you can specify the text to columns info as per your need. This needs to be done only once.
Then save the file with the imported data (as an XLS file).

Next month when you wish to import data from another text file with the same structure, simply open this XLS file, click on the Data tab, and click on the Refresh All button.

It will display the "Import text file" dialog box, in which you should select the appropriate file, and complete the data import with the new month's data.
It won't ask you to specify the text-to-columns settings, since the settings are already stored in this file.

97. […] were slow during Thanksgiving week, so Chandoo posted an "Ask Me Anything" challenge. Read through the questions, to see what people wanted to […]

98. Jotesh Kumar Lohia says:

I have 30 workbooks iwth a single sheet in each. I want to merge them into one work book. I know on

99. Dave says:

Hi Chandoo,

Great site and interesting idea - Ask Me Anything - certainly generated some varied discussions!

I have a lot of macros stored in my personal.xlsb file (MS Office Excel 2010 & Windows 7 Professional) and have been noticing lately that sometimes when I try to monitor some of them or modify them for a slight change that when using Step In (F8) line by line quite often while I wait for a relatively long task to complete (often deleting lines, but not always) the yellow highlighted Step In action does not come back to the next action and instead the macro runs through to the end (or stops at the next Debug issue). Is this something to do with the complexity of the macro's or the number of macros in the personal.xlsb file - is it possible multiple macros load when you open the editor? I sometimes find when I open a file or change a macro that many of my macro's flash up int he editor and seem to flip through.
I can only seem to manage stopping the macro from fully running by using other edit functions like Run to Cursor, but these are not always ideal as I need to iterate through the actions.

Would appreciate any thoughts anyone may have if this is a bug or if anyone has seen posts / solutions on the web about a similar concern.

Thanks,

Dave

• Chandoo says:

Thanks for the lovely words Dave.

Your problem seems very strange. Usually debug, step in etc. are pretty standard features and they just work. May be there is something else that is causing this behavior. I suggest disabling all other add-ins and closing all other workbooks so that you can examine this closely and point out the culprit.

100. Sandeep says:

I have a worksheet where I use data validation to input a short code (say E-CONV) and a simple Vlookup in the next column to fetch a description (say Expense-Conveyance). Now since the setup information is on another sheet and the number of short codes are many (in excess of 30), it requires one flit from one sheet to another to input the correct short code/description. One option is to have a macro display in toggle mode a dialog with short codes/descriptions within. What else can I do?

• Chandoo says:

Why not enter the descriptions in data validation list? You can then use another lookup to fetch the code if you need it elsewhere.

101. Khushnood Viccaji says:

Hi Chandoo,

I often use a UDF to get the background colour (shading) of a cell by using the Cell.Interior.ColorIndex property.
This is very useful for say, counting the number of cells with yellow shading in a range of cells.

However this UDF does not work when the shading is applied to the cell thru conditional formatting.
In such a case, the Cell.Interior.ColorIndex does not 'recognize' the displayed cell shading.

Interestingly in such a case, if I use AutoFilter to filter the column based on the cell's shading, that works just fine !

Do you know about any workaround for this ?

I could send you a sample file via email if you wish.

• Chandoo says:

Hi Khushnood... thanks for your comment buddy. My VBA around CF is still rusty. But a little google search seems to help. See this page.

http://www.cpearson.com/excel/CFColors.htm

• Khushnood Viccaji says:

This is exactly what I was looking for.
MANY THANKS !

Now I'm wondering what search terms did *I* use on Google while looking for this . . . :-/

102. Mrugesh Jayant says:

Hi Chandoo. I am a beginner with excel and this is probably a stupid question, but nevertheless i am stumped. here it goes:
I have to make a database of daily purchases for my restaurant. This will be in one inventory master sheet. To input the new purchases everyday, I want a small 4-5 line input field on another home sheet. how do i go about doing that? would be obliged if you help me out. thanks

• Chandoo says:

Hi Mrugesh... welcome to Chandoo.org and thanks for your question. There are no stupid questions, only stupid people (who never ask questions). 🙂

I am not sure I understand your requirement clearly. If your goal is to enter data everyday with ease, then you can use tables and enter data in last row (or use table user form feature built in Excel).

103. test says:

Hi Chadoo, I have been following your blog passively for quite some time and appreciate

a) Passion for the subject
b) Willingness to share and make people AWSOME!!!

Was not able to resist myself from joining in the AMA invite today. So, here goes my absolutely off-topic, off-context question (may be Startupdeshi was a better place for this).....

"What made you opt for DISQUS as a commenting add-in. In my limited understanding the Facebook commenting add-in could had been much better since it inherently helps in SMO."

T

• Chandoo says:

Hi ... thanks for the nice words.

I am testing Disqus currently as a potential commenting solution. Although we are happy with WordPress comments, I felt Disqus will create a better community feel in the comments section. I am not sure about Facebook as most of our readers browse this site from office and may not have facebook access or may not deem it right to use facebook for work related stuff like this.

104. Pablo Baez says:

Hi Chandoo,

I took your PowerPivot class and now I am putting that to work. I am creating a PowerPivot report for supplies consumption, which contains:

Beginning Inventory
Forecasted Demand
Actual Demand
Plan Receipt

Each field comes from data tables, which are provided by different people across the country. The report works fine in Excel, but it requires a lot of manual updating each week then they send the report, so the main advantage of using PowerPivot is to create the connections and then just refresh them.

One of the measures is:

[Projected on Hand] = If([Actual Demand]>0,
[Beginning Inventory] + [Plan Receipt]- [Actual Demand],

[Plan Receipt] - [Forecasted Demand] + [Prior Period Projected on Hand])

The problem is getting the [Prior Period Projected on Hand], which gives me a circular reference when used on Projected on Hand, since basically it's the same calculation, but for the prior period.

[Prior Period Projected on Hand] =
Calculate([Projected on Hand], Dateadd(Calendar[Date], -7, Day))

How do I get this to work? The period used is weekly on the columns.

I will appreciate your help. I post this in the forum and the file is uploaded there as well.

Thanks,
Pablo

• Chandoo says:

Very interesting and tricky question. I will have to reach out to my Power Pivot mentor for this. Will reply back in 2-3 days.

105. Pablo Baez says:

Hi Chandoo,
What an overwhelming response, how are you dealing with it?

106. dks345 says:

Hi Chandoo. I have an excel file and want it to be awesome but right now it is not.
The file serves as a status report for reports required for submission in my department. All reports are regularly recurring in nature but not on the same frequency. The file has the following columns: report name, report frequency (monthly or quarterly), report due date (x days after period end), period end (the end of the most recent month or quarter), when the next report is due, and if the current report submissions is current or not. I would upload an example file if I could, but I don't think comments allow that.

I am trying to figure out a way on this status report file will track if a report was submitted on time and/or alert me one of these recurring reports is past due. The difficulty I am having is caused by the fact that the report frequency is sometimes monthly and sometimes quarterly (not the same cadence for all) and that the report due date is determined by a certain amount of days after the period ends (end of most recent month or end of most recent quarter). Any thoughts on how to address this and make it awesome?

• dks345 says:

Screenshot attached

• Chandoo says:

Interesting question. Let me create an example with this and share it on Chandoo.org sometime in future.

107. Martin says:

Dear Chandoo,
I would like automatically select 5 pivot items based on the values of 5 cells.
This has given me a VBA headache, and I don't think I'm any closer to acheiving it.
Thanks

108. Ian Huitson says:

Chandoo
Did you post this question so you could get your post response count up ?

• Chandoo says:

🙂
Well, that is one of the side effects I guess. My real intention is to keep the site active and buzzing during the dull Thanksgiving season 🙂

109. Rajesh Verma says:

I am trying to understand how vlookup and choose would help me? Here is what the scenario -
1) range name=range1 (this is the range from where lookup value has to be searched)
2) range name=range2 (this is the range from where value has to be fetched)
both the ranges are different. Here is what I am trying to do -
vlookup("john",choose({1,2},range1,range2),2,false)
The above doesnt work. Any suggestion please?

• Sanjeev Sharma says:

=Vlookup(B3,Range2,2,0)
B3 -> is the Lookup value
Range2 - is your search range, where column E contains your search value and column F contains the value to be fetched.

2-> Now as you are entering 3 in the formula, I'm sure it's going to give some other value than expected. instead use 2 bcoz column F is on 2nd place.
I hope it would help.

• Rajesh Verma says:

No Sanjeev this will not work :(. Here is the data -
Lookup value will be in Col-b and named as Range1
The value to be fetched from Col-G. Name Range2 is encompassing Col-E to Col-J
Col-C & Col-D not to be considered in Vlookup

Col-B Col C&D Col-E Col-F Col-G Col-H Col-I Col-J
dont consider
John D11 D12 D13 D14 D15 D16
Mark D21 D12 D13 D14 D15 D16
Andy D31 D12 D13 D14 D15 D16
Harry D41 D12 D13 D14 D15 D16

On day-1 the vlookup table array will be Range1&Range2 & Index=3
On day-2 the vlookup table array will be Range1&Range3 (Col-K to Col-P) & Index=3
On day-3 the vlookup table array will be Range1&Range5 (Col...... to Col....) & Index=3
There is reason that on daily basis the range name will be different.
I am aware the I can select complete sheet as lookup table array but that is not solution as index value will be constant 3.

• Chandoo says:

I am not sure I understand this. Can you email me sample data and clearly explain what the lookup should return? My email address is chandoo.d@gmail.com

110. John Gamiles says:

Chandoo,

I was really intrigued with your interactive sales chart:

However, the tab the chart appears on has fixed space to display the elements and I can't figured out how change the page layout back to columns and rows. I want to be able to resize the sales chart to show more products.

111. Herman Cheung says:

Hi! Thanks for answering my question earlier. I have another question: I'm trying create an educational excel tool, where people can click on a button and hear the pronunciation of a word. Any hints?

112. Steven Yayac says:

Can the VBA course be started in Dec. I have more time available in the end of the year

113. prateek says:

Hi Chandoo,

I am an Indian student in US @ Johns Hopkins
What I dont understand is that you are giving a discount of 30 \$ for US customers and only Rs 1200 for Indian, should it not be 1800 INR
Is this fair ?

P

• Chandoo says:

Hi Prateek... Welcome to our site and thanks for the comment. We charge \$347 for the course all over the world, where as in India, we charge Rs 14000 for the same. Hence the discount is also less.

114. Sandip says:

Hi Chandoo

We had some conversation few days ago that I emailed my questions to chandoo.d@gmail.com and you would find and reply me back. Do not worry if you can not find out my question in your email and I am happy to ask question on forum. I just want to know how to ask question such a way that comes under directly your attention. Also which forum is best to raise question that is well attended and replied ? Is forum the best way for candidate who signed your excel course or there is other way ? I felt some communication gap between me and you so my concern is to raise question that you have a look on it. Could you suggest me best way? Also this forum is temporary so please advise me forum that you definitely see question being raised by candidate. I feel that I can learn a lot regarding excel from you.

Enjoy the rest of the weekend

Thanks

115. Steph says:

Hello,

I try to understand precisely the usage of brackets { } in formula in Excel 2010.

I need to calculate "averageifs", "median if" matching multiple conditions.

By the way, I saw your article about getting rid of empty rows.
But, with VBA, even if I succeed in deleting last rows of a sheet where previous content was deleted then another copy/paste, the auto filter still consider empty rows at the bottom.

• Ian Huitson says:

@disqus_VWsoOVS4lD:disqus
Excel can sue the {} brackets in two cases
1. When you store an Array

eg: =Sum({1, 2, 3})
or

2. When Excel accepts your entry of a function as an Array Function
eg: If you enter=Sum(1, 2, 3) and press Ctrl+Shift+Enter excel will put the {} brackets around the function

116. Ramachandra says:

How to use FORECAST function?

117. Teena Corrigan says:

Trouble to find vbc code that can update data in next column after match two same value the open workbook from closed another workbook??

118. Sabir says:

Hi, can you please tell me how to create a GIF video file to show the excel presentations as you always show in your website. Thank You..

119. Eduardo says:

Hi, I'm from Mexico and I find your blog absolutely amazing... after seeing what you can do with excel I think you can help me out with this one... I would like to know how to make a heatmap or a treemap in excel 2010... but not just one that changes colors, but also one that changes the size of the shapes or boxes according to their value... something like the Map of the Market of MarketWatch http://www.marketwatch.com/tools/stockresearch/marketmap

Hope you can help me on this.

Kind regards,

Eduardo

120. kanchan says:

Hi, I have two excelbook. WB1 and WB2 . I have WB1 as my gita . that is the column headers which are arranged in a specific order.
WB2 is a book that are input from different team which has same column headers but not arranged in the same order as WB1. I want to create a macro so that when i run it it looks to WB1 and gets sorted the data in WB2 and the column headers allign in same order as WB1. Please suggest . I tried some no. of times but its failing. I m new to macro and dont knw VB . i Tried recordin but its not working. coz WB2 is a varrying file.

121. Faiz says:

hi
i have table like this
HW1 29
HW2 30
HW3 35
HW4 41

I want result if below 40, is ok. if >40 show alert. what formula can I used?

122. Cameron says:

Trying to understand the Index (reference) formula.

Cameron

123. venkatesh says:

Dar Chandoo,
I have the following details on excel worksheet
Column A has Product A (only in the first row)
Column B has 4 batch numbers that belong to Product A one below the other-1st just against product A
Column C has stock balances for each of these batches
my question is I want Product A repeated on each row alongside the batch number.
Likewise for Product B, C and so on. I keep a blank row in between 2 products
This can be don by copy and paste but it is time consuming. Any innovative method you have in store?

124. Deane Davenport says:

My 13 year old daughter swims competitively and I will be darned if I can set up a format that allows me to add and subtract times from one column to the next in the format 05:05:22 meaning 5 minutes, 5 seconds and 22 hundreds of a second.

If I pace it in like that the results stink (well for me anyway)

Thanks, for any help,
Deane

• Hui... says:

@Deane
I can't get Excel to display mm:ss:00 as you require
Have you tried the number format of mm:ss.00
That works ok

• Deane Davenport says:

thank you, sorry this took so long

• Deane Davenport says:

Hui:

thank you again
Deane

125. Guilherme says:

Hi, I am a huge fan of your website. I am learning a lot, thanks for your lessons =-)

I have a list with two variables, Continent and Country. In the first column I want to filter the Continent using the List on Data Validation, and in the next column I want to filter only the Country in the selected Continent. For example, if I filter Europe in the first column, the filter in the next column will only show Italy, Spain, Germany, etc.

Can you help me?

Regards,
Guilherme

126. Wayne says:

I have set up conditional formatting for coloring a cell yellow whenever a deadline is seven days away.
=AND(N2"",N2<TODAY()+7)
My boss loves this feature, but wants the yellow fill to go away a week after the deadline has passed.

Also, can you receommend a good book for creating charts and Pivot Tables in Excel 2010?

Thanks.

127. Wayne says:

Hi Chandoo,

I have set up conditional formatting for coloring a cell yellow whenever a deadline is seven days away.
=AND(N2"",N2<TODAY()+7)
My boss loves this feature, but wants the yellow fill to go away a week after the deadline has passed.

Also, can you receommend a good book for creating charts and Pivot Tables in Excel 2010?

Thanks.

128. sonam says:

Hi Chandoo,

I find it very difficult to change the normal date format manually every time when I have to submit details of students to CBSE. So please suggest a way to change, for example 02-Jan-04 to 02012004 date format

• Khushnood Viccaji says:

@Sonam,
Assuming that you have the source date in cell A1, you can
1. enter this formula in cell B1 to get the desired date format :
=TEXT(A1,"ddmmyyyy")*1
2. apply the number format "00000000" (without quotes) to cell B1.
This will store the value of the date, as a number, in cell B1.

If however, you don't need the value of the date as a number, you need not type the '*1' in the formula above. That will store the value as TEXT only, in B1.

129. Alok Kumar Jena says:

How to fix my format for employe.

130. Ranjith says:

I want to learn VBA

131. SASI C KANDY says:

I was trying to use IF (TODAY) feature in excel. Actually I have to high light a date which is previous than today in one color and after today in another color.

Suppose today is 17-feb-14. There are dates 12-feb-14 and 10-jan-14. This I want in green colour. And 28-feb-14 and 20-apr-14 in Red color. All these are in one column. What is the formula for this.

132. David Schrickker says:

I was wondering if there is a way to sort this issue in Excel:

Say for example you have a list of insurance information going vertically down a row in excel. For each policy it starts with the policy holder, then the spouse, the children and lastly the extended family. In the first column it has the description (Policy holder, spouse, etc) and in the second column their names are stated.

I was wondering if there is a formula that if a policy holder has more than 5 Extended family members, those extra members can be converted to policy holders themselves. I.e. After the 5th extended member all other extended members of that policy will become single policy holders.

Please let me know if my description of the question has been clear. Your help would be very much appreciated.

• Hui... says:

@David
I would add the other policy holders as fields (new columns) in the main record rather than new records

133. Roy says:

Unrounding a formula

I need help with creating a formula that removes all rounding functions from another formula? For example, assume cells A1:A4 contain formulas like this:
=ROUND(45.34,0)*(MROUND(87.84,5)+ROUNDUP(792.45,2))-ROUND(810.17,0)+rounddown(471.55,0)+10000

I would like to create a formula in cell B1, which I then can replicate to cells B2:B4, that reads like this:
=45.34*(87.84+792.45)-810.17+471.55+10000

I’ve played around with combinations of SUBSTITUTE, REPLACE, FIND, SEARCH and wildcards (* and ?), as well as MID, LEFT, RIGHT, TEXT and VALUE, but I haven’t gotten there.

Note that I’d really like to do this as a worksheet formula, that is, without resorting to VBA. But if VBA is the only way, then so be it.

134. Pulkit Trivedi says:

Hello Sir,

Can you please share the list of each & every books that you read till date..

Thanks..

135. Dear Sir,
pls help if you can...
My self anamika..i m working on a project (MIS) worksheet..i want to count only color cell on filtered range...(not use filter for color)
i have two columns a & b a column data is week numbers..and column b data is amount....i want to filter by week num and count column b data only red font value in a formula cell..and update formula everytime when i chnage filter value...or weeknumbers..is it possible...

• Hui... says:

@Anamika

Can you please post a sample file with some data and an example of what you require

Hui...

• Dear Hui Sir,
i have send a sample file on your mail..plz check your mail.

136. sandeep sharma says:

Hi Hui,
I want to create a dynamic table likewise we use for range for rows or column using formula, Is that possible without using VBA. If there is any way pl suggest.

137. John says:

I am using excel 2013 and I have a table split into numbers and names. I want to lookup the number, but which is the same for each record, as in a class, and I want to have the name come up on another sheet, and then be able to replicate. I tried a lookup bu t it does not allow me to replicate, even with absolute references. It always comes up with the same name.

138. sandip says:

Question 1: What is the quickest way to convert negative number into bracket ? For example I have two number as below?
-100
-50

I want to mention above two number as (100) and (50) . I know that I can use custom format rule as #,##0;(#,##0) to get negative number into bracket ? But I am wondering any other way to do this task very quickly from custom format or only custom format rule is the option to convert negative number into bracket ?

Question 2: Is there other way to count blank cells apart from countblank formula ? Is there other way to count blank cells by mouse means we will select blank cells in certain column and can see number of blank cells ? I don’t want to use any formula to count blank cells.

Thanks

139. Thu Nathe says:

Regarding monitoring a project's progress.
In Excel document I have 2 columns: End Day and Current Date, how do I set "Current Date" cell to be: show Green color if current date is than End date (late)?

140. Raghavendra says:

=IF(AND(O5="Wife";O5="Mother");"Smt";IF(AND(O5="Father";O5="Husband");"Sri";"Smt"))

this is not working. pl help me

• Hui... says:

@Raghavendra

Cell O5 cannot be both values of Wife and Mother at the same time, or it cannot also be Father and Husband at the same time
I suspect you meant to use Or() instead of And()
=IF(Or(O5="Wife";O5="Mother");"Smt";IF(Or(O5="Father";O5="Husband");"Sri";"Smt"))

141. shazia siddiq says:

Hi Chandoo,
1.How can I create a formula that will pull out the second and third largest sales value data from a dataset?

2. How can i create a formula that add the sales of a column and using a range data set i.e from a selection of shop ine.g column B. IF the store is not present, how can I change the formula to incoporate the phrase "Not Present". Please answer to my personla email address.

Rgds,
Shaz

• Chandoo says:

1) you can use LARGE formula for this. Just search our site for examples on this.
2) You can use COUNTIF or VLOOKUP to check if the store is present or not. You can then use these formulas in an IF formula to print "not present" in your results.

• Hui... says:

@Shazia
I’d suggest posting more urgent questions in the Forums as I am pretty sure that only Chandoo and I read the Comments from the main website daily
http://chandoo.org/forum/
When posting at the forum supply a sample file for a quicker response

142. Gian Singh says:

Hi ! Chandoo ji,
I am a fresher to Excel 2007. I downloaded you excel sheet to sum nth cell . The formula and the criteria worked well in a new sheet in the same place i.e. from first row of Col. A but when I try to sum the same data from other cells say B70 onward, the the DSum formula give wrong answer . I donot know where I am wrong

143. Rajat Maheshwari says:

How to add particular colourS from a data i.e. not a single colour but two or more colours at a same time.

144. ravik says:

i want to cumulate txt files (notepad) in folders in single file and import into excel with macro can u provide

145. Uros says:

Excel 2010 question:

I have several groups of cells within one Excel sheet. Each group of cells is marked by a merged group of 10 vertical cells and the rest of the group is made of 9 by 10 single cells. I want to filter my document in the following way: if only one of the cells in each of the groups of cells contains certain value -> keep the whole group of cells , although the other cells within that group do not contain that value.

Please let me know if you know the answer to this question?

Thank you!

146. Bob says:

I have an existing table. In a column within the table I need drop down lists. But I don't seem to be able to have the drop down list functionality "grow" as the table rows "grows" as data is added. In other words, as I hit the "enter" key in the last column and move to the next row down I would like a drop down list to go with it. I would prefer not to use VBA.

Thanks all,
Bob
Seaspray, Victoria, AU

147. Ganesh says:

Any Similar dashboards or files on D3js

148. Pieter says:

I need to find a value between two number in a table and return a number based on this selection.

np,r 0 1 2 3 4 5 6 7 8 9 10
>1.295 1.218 1.154 1.099 1.051 1.009 0.971 0.938 0.907 0.880
20 0.855 0.832 0.810 0.790 0.772 0.755 0.739 0.724 0.710 0.696
30 0.684 0.672 0.660 0.649 0.639 0.629 0.620 0.611 0.602 0.594
40 0.586 0.579 0.571 0.564 0.558 0.551 0.545 0.539 0.533 0.527
50 0.521 0.516 0.511 0.506 0.501 0.496 0.491 0.487 0.483 0.478
60 0.474 0.470 0.466 0.463 0.459 0.455 0.451 0.448 0.445 0.441
70 0.438 0.435 0.432 0.429 0.426 0.423 0.420 0.417 0.414 0.411
80 0.409 0.406 0.404 0.401 0.399 0.396 0.394 0.392 0.389 0.387
90 0.385 0.383 0.380 0.378 0.376 0.374 0.372 0.370 0.368 0.366

Number of sample pairs taken = 29 -> factor from table2 0.695

I need to lookup for 0.695 and return the answer as 29.
Can you help?

• Hui... says:

@Pieter
0.695 is between 29 = 0.696 and 30 = 0.684
So you want the value higher than 0.695
Is that correct ?

• Pieter says:

Thank is correct yes. Thanks.

• Pieter says:

Just to give a better understanding. The data is in tabular format. This first row containing the header as np,r 0 1 2 3 4 5 6 7 8 9 10 with all values in columns below these headings.

The value I need to look up is 0.696, returning this as 20 in row 9 which results in the number 29.

Thank you!

Pieter

149. Manik Nag says:

Dear All,
I've an excel which contains different Queue name wise data, I want to simply divide the number of records in 5 equal parts by selecting each Queue name, for example I've a Queue called "CH_SUPPORT_JEOPARDY" which contains 20 records I want a update/allocate in the last column with Q1, Q2, Q3, Q4 and Q5, by set of 4 each (i.e. Q1 contains 4 data, Q2 contains 4 data, Q3 contains 4 data and so on).
And for odd number of records it should be distribute as even as possible.

With Regards
Manik Nag
WE Queue Name EIN OCR_Success Closures 7 OCR Target 7 OCR Actual Variance% Avg Target Avg Name Team Manager Role Quintile
1-May CH_SUPPORT_JEOPARDY 608506362 1 1 77% 100% 23% 23% 77% Sarin Balakrishnan Parthasarathi Nag Associate Q1
1-May CH_SUPPORT_JEOPARDY 608510208 1 1 77% 100% 23% 23% 77% Mohan Pradhan Parthasarathi Nag Associate Q1
1-May CH_SUPPORT_JEOPARDY 608806967 1 1 77% 100% 23% 23% 77% Arpita Mukherjee Seshadri Bhushan Associate Q1
1-May CH_SUPPORT_JEOPARDY 608807001 1 1 77% 100% 23% 23% 77% Pooja Dangi Seshadri Bhushan Associate Q1
1-May CH_SUPPORT_JEOPARDY 608808725 1 1 77% 100% 23% 23% 77% Aditya Poddar Seshadri Bhushan Associate Q2
1-May CH_SUPPORT_JEOPARDY 608882725 1 1 77% 100% 23% 23% 77% Prithwirup Ganguly Farrukh Nawaid Associate Q2
1-May CH_SUPPORT_JEOPARDY 608631125 48 55 77% 87% 10% 565% 4235% Gagan Bagga Nandini Pandit Associate Q2
1-May CH_SUPPORT_JEOPARDY 608630265 93 114 77% 82% 5% 522% 8778% Sougata Mookherjee Priyanka Banerjee De Associate Q2
1-May CH_SUPPORT_JEOPARDY 608624721 46 57 77% 81% 4% 211% 4389% Debangana Sarkar Nandini Pandit Associate Q3
1-May CH_SUPPORT_JEOPARDY 608679868 58 73 77% 79% 2% 179% 5621% Baby Sailo Amit Bose Associate Q3
1-May CH_SUPPORT_JEOPARDY 608587408 88 112 77% 79% 2% 176% 8624% Shubham Bhattacharjee Amit Bose Associate Q3
1-May CH_SUPPORT_JEOPARDY 608679707 69 89 77% 78% 1% 47% 6853% Nitin Lohia Amit Bose Associate Q4
1-May CH_SUPPORT_JEOPARDY 608631309 116 154 77% 75% -2% -258% 11858% Shradha Sambhavi Priyanka Banerjee De Associate Q4
1-May CH_SUPPORT_JEOPARDY 608679264 27 36 77% 75% -2% -72% 2772% Aritra Goswami Nandini Pandit Associate Q4
1-May CH_SUPPORT_JEOPARDY 608679547 32 43 77% 74% -3% -111% 3311% Amit Mahato Amit Bose Associate Q4
1-May CH_SUPPORT_JEOPARDY 608508960 14 19 77% 74% -3% -63% 1463% Paulomi Sarkar Parthasarathi Nag Associate Q5
1-May CH_SUPPORT_JEOPARDY 608643661 31 44 77% 70% -7% -288% 3388% Shinjini Nag Nandini Pandit Associate Q5
1-May CH_SUPPORT_JEOPARDY 608509080 1 2 77% 50% -27% -54% 154% Mridul Biswas Parthasarathi Nag Associate Q5
1-May CS_CANCELLATION_Q 608888703 2 2 81% 100% 19% 37% 163% Yasmin Ansari Farrukh Nawaid Associate Q1
1-May CS_CANCELLATION_Q 608807209 1 1 87% 100% 13% 13% 87% Debdatta Sadhukhan Seshadri Bhushan Associate Q2
1-May CS_CANCELLATION_Q 608821120 9 9 87% 100% 13% 119% 781% Ankur Bhattacharjee Bushra Williams Associate Q3
1-May CS_COT_PRIORITY_DIARY 608591429 60 67 83% 90% 7% 439% 5561% Triparna Sengupta Priyanka Banerjee De Associate Q1
1-May CS_COT_PRIORITY_DIARY 608584247 31 36 83% 86% 3% 112% 2988% Renu Chowdhury Priyanka Banerjee De Associate Q1
1-May CS_COT_PRIORITY_DIARY 608508762 34 40 83% 85% 2% 80% 3320% Meghna Chakraborty Priyanka Banerjee De Associate Q2
1-May CS_COT_PRIORITY_DIARY 608508205 19 23 83% 83% 0% -9% 1909% Pilip Krishnan Priyanka Banerjee De Associate Q2
1-May CS_COT_PRIORITY_DIARY 608519225 39 48 83% 81% -2% -84% 3984% Priyanka Basu Priyanka Banerjee De Associate Q3
1-May CS_COT_PRIORITY_DIARY 608569169 28 35 83% 80% -3% -105% 2905% Avik Mitra Priyanka Banerjee De Associate Q4
1-May CS_COT_PRIORITY_DIARY 608591580 26 33 83% 79% -4% -139% 2739% Sharmistha Ghosh Priyanka Banerjee De Associate Q4
1-May CS_COT_PRIORITY_DIARY 608517382 27 36 83% 75% -8% -288% 2988% Ritu Kumari Priyanka Banerjee De Associate Q5
1-May CS_COT_PRIORITY_DIARY 608517740 39 52 83% 75% -8% -416% 4316% Abhishek Kumar Priyanka Banerjee De Associate Q5
1-May CS_ELF_BT_SPORTS_DIARY 608814702 6 6 85% 100% 15% 90% 510% Anannya Sur Farrukh Nawaid Associate Q1
1-May CS_ELF_BT_SPORTS_DIARY 608886761 8 9 85% 89% 4% 35% 765% Proloy Dutta Atul Sinha Associate Q2
1-May CS_ELF_BT_SPORTS_DIARY 608827405 15 16 91% 94% 3% 52% 1448% Arjyesh Ray Bushra Williams Associate Q3
1-May CS_ELF_BT_SPORTS_DIARY 608630906 8 9 96% 89% -7% -64% 864% Pourab Chowdhury Priyanka Banerjee De Associate Q3
1-May CS_ELF_BT_SPORTS_DIARY 608570967 23 28 96% 82% -14% -388% 2688% Avishek Halder Priyanka Banerjee De Associate Q4
1-May CS_ELF_BT_SPORTS_DIARY 608882565 1 2 85% 50% -35% -70% 170% Chandrayee Ghosh Farrukh Nawaid Associate Q5

150. suhrud says:

I have one small problem with Excel and I would like to tell you about it, hoping to find some solution.

I have 2 files. 1. Master strategy
2. Preparation of Exam.

Few days back (on 12th May to be exact) I saved file no 2 with name of file no 1. Thus all the content of file 1 was overwritten. After that I have backed up the data so the latest copy of the backup contains the overwritten version not the older version.
whenever I try 'restore previous versions' option, it says 'no previous versions available.'
I have tried most of the tricks of the bag of many people writing about excel.
I have tried, Pandora recovery, Recuva with no success.

Would there be any chance, you might have solution to this problem?

Regards

• Hui... says:

@Suhrud

Unless you have an alternative or older backup I suspect your going to have to recreate the file

• Suhrud says:

Thanx for a prompt reply. Few minutes back I was able to recover one version of the file I am looking for. I used Recuva for it. When I try to open the file it shows the message that "it can't be opened or change the format or file may be corrupt."

Good signs are : when I check the properties of the file they loom alright. E.g. date when it was created, size of the file etc.

Once again "Can Anything be done?"

Thanx once again.

Suhrud.

151. KIRAN S N says:

OUR BOSS DECLARED HOLIDAY FOR THE PERIOD FROM 6TH JUNE 2015 TO 20TH JUNE 2015.

I WANT KNOW DIFFERENCE BETWEEN TWO DATES . IT SHOULD BE 13. (WE ARE WORKING 6 DAYS A WEEK).

I TRIED BOTH NETWORKDAYS & DAYS360 FORMULA. BUT I AM NOT GETTING RESULT 13.

• Hui... says:

@Kiran
=NETWORKDAYS.INTL(A1,A2,11)
=13
where
A1=6/6/2015
A2=20/6/2015
11 = Sunday is only Weekend day

152. Pieter says:

I have a data set and needs to extract two or three sub-sets from this, based on the value of a column. This set contains date start_time and end_time, followed by 54 other data columns. One of these contains the numerical value that identifies the sub sets to extract.

Date start_time end_time, ROI1, ROI2, ... ROI54, Residual.
Date Start Time End Time UM Nita SM AR Ash AR CV AR Vols AR Total Carbon AR Hydrogen AR Oxygen
02/03/2015 00:00:29 00:01:33 3.1280 37.33 30.82 16.37 26.04 37.33 3.45 8.25
02/03/2015 00:01:33 00:02:36 3.06 39.20 32.09 14.36 26.02 39.20 3.31 8.25
02/03/2015 00:02:36 00:03:40 3.13 36.73 33.52 15.41 27.17 36.73 3.37 8.25
02/03/2015 00:03:40 00:04:44 3.18 36.37 31.81 12.49 27.65 36.37 3.25 8.24
02/03/2015 00:04:44 00:05:48 3.15 37.31 34.10 14.72 26.46 37.31 3.38 8.24
02/03/2015 00:05:48 00:06:51 3.15 36.64 33.37 14.85 27.17 36.64 3.38 8.25

This set usually contains 85000 records with the last column, a number -1000 to +1000 and anything in between.

I need the largest + and largest - sets plus the 200 record with the smallest value or target of 0.0

153. ZEYNEP YILMAZ says:

Hi,

I would like to know how can I find minimum value of a range excluding the zero data?

Thanks a lot

• Hui... says:

@Zeynep

Use: =MIN(IF(A1:A10>0,A1:A10,FALSE)) Ctrl+Shift+enter

• Pieter says:

Likewise , I would like to know how can I find minimum value of a range excluding the Div0 # data?

154. ZEYNEP YILMAZ says:

Thanks a lot Hui.
Many many thanks....

155. Ryan Wilson says:

Chandoo,

Do you know of alternatives to sharepoint or PowerBI? I am looking to host Power Map if not Power Pivot or query.

Thanks for always being the best

-Ryan Wilson
Central Valley, CA

156. Lance says:

I'm attempting to edit a chart - adding extra data (months and percentages), using the select data source function. The first example accepts the data, but both the 'y' axis (horizontal) and the trend line run past the physical size of the chart. In the second example, using the select data source function and highlighting the appropriate range, the y axis does not update. However, when I double-click the chart, I can see additional "invisible" unfilled bars outside the physical size of the chart. Help! Need to complete the work for my boss ASAP and I can't get these two charts t update!!

157. Manoj says:

Formula for allowance of employees
Days. Allowance
0-10 11
11-19. 23
20-31 45

• Hui... says:

@Manoj
0 11
11 23
20 45

Then use a VLookup

158. Ashley says:

learning about IF statements and the SUMIF and SUMIFS, how would you describe to a novice excel user, the use for each of these and the differences in the uses?

159. Ashley says:

Could you help me describe how writing an incorrect IF statement might produce the wrong data?

• Hui... says:

@Ashley

Same comment as before

If you type =If in Excel then press F1
Excel will show you the structure and use of the Function "If"

=If(Logic expression, True Action, False Action)

If evaluates the Logic Expression and if it evaluates to True then performs whatever is in the True Action
if it evaluates to false it performs whatever is in the False Action area

If you get the Logic Expression wrong the wrong action will be implemented

160. Ashley says:

if b7(p1) is the same as b22 (a1) it needs to equal the same as d22
how would I come up with the IF statement for this equation?

161. Pieter says:

I need help with a hlookup calculating averages with IFS, on data containing Division by ZERO and no valid ZERO values. This is between dates and start and end time working off a combined date field.

Formula is :-

=AVERAGEIFS(SQL!D\$5:D\$2028,SQL!\$A\$5:\$A\$2028,'sql vs LABS'!\$G17,SQL!\$B\$5:\$B\$2028,""&'sql vs LABS'!\$H17)

Where date is in column A, values in D, time in G and H for start and end time...

I need to calculate also standard deviation, max and min using the same logic on the same data.

162. Ivan Jain says:

I have about 10000 data columns with 10000 data rows of percentage values.
I need to generate a chart where I should select any 2 columns from drop-down list as X and Y axis for the chart. What is the simple mechanism in excel?

• Hui... says:

@Ivan

I would setup two Data Validations, listing all the fields
Setup two dummy ranges which use Offsets to retrieve the Data from the selected fields
Chart it
Then the charts will update as you change the selections

163. Linn says:

I need help how to generate the formula for the variance between th net sale of the first week of sunday(May) by sale of the first week of sunday(April).

Thanks,
Linn

164. henrietta says:

How do I earn the privilege to post a question?

• Hui... says:

@Henrietta

Chandoo org is a Moderated Forum
That is you can post anything, but all posts are reviewed before they are posted

If you want to ask Excel questions Chandoo also has the Chandoo.org Forums
http://forum.chandoo.org/
Where you can ask questions that are Excel related

You have to register to post a question on the forums

165. Pieter says:

I need command in EXCEL ( no macros) to graph data with a slider, stepping through a time series of relevant data. This must work for a fixed number of data points in time and with another slider that sets the number of record to be viewed on the graph.

Data volumes are big ( 70 000 records in a tabel)

Hope you can help.

Be Blessed,

166. Nagaraju says:

Hi Friends,

In excel i want remove/delete starting with Mr ,Dr ,Ms,Smt like word .can any body guide me or provide the VB Code.

Thanks,
Nagaraju

• Sandra says:

I usually do this simply with find and replace (Find "Mr " and replace "") and replace all - remembering to put a space after "Mr" so there is no leading space before the name. I would record a Macro for each (Dr, Mr, Ms, etc) if you need to do this regularly.

167. Jeanne says:

Is there a ways to set my own default parameters for Excel's Text Import Wizard. I'd like to have it open, and click finish. Instead of every time having to select the Original Data Type & the Delimiters

168. Jeanne says:

Is there a ways to set my own default parameters for Excel's Text Import Wizard. I'd like to have it open, and click finish. Instead of every time having to select the Original Data Type & the Delimiters - this time with the correct email address

169. Sandra says:

I am using Excel 2010 and have a long list of unpaid invoices so I am trying to check the values £ for individual invoices against a payment for multiple invoices. When a payment is received it is for several invoices, so I am trying to identify which invoices these are by a formula if possible. Thank you, Sandra

 Formula Forensics-No. 036: Calculating Costs that Vary by Year and Age Chandoo.org Holiday SALE, Starts on Wednesday – 4thDecember!