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.
Go ahead and post your questions. I am waiting…
PS: I have enabled Disqus commenting system on the blog. This makes commenting on Chandoo.org even more awesome.
391 Responses to “Ask me anything”
When you were in the US, what was the oddest or worst food you encountered that is common here?
What was your favorite?
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.
Interesting. Thanks for the reply. Potbelly Chicken Salad Sandwich. I would never have guessed that. LOL!
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!
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
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
thank you
While building a dashboard, how to cleanup / prepare the data received in a report format easily? without involving to much manual effort. Thank you
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.
What is the future of excel (maybe VBA)? how it can evolve in post-pc era?
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 🙂
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.
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.
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!
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.
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
I am sure it is possible. Let me investigate a little and reply back with an example workbook.
yay .. thanks
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
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.
Thank you. Much appreciated.
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!
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.
What was the process for self publishing your book on Amazon?
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.
Thanks Chandoo- that was really helpful. Appreciate the info.
Have you ever created something like this? Any insights?
http://forum.chandoo.org/threads/from-a-to-b-migration-visualization-ideas.13556/#post-79984
no. My geometry and Excel skills are not that advanced. But I love the idea.
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!
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).
See this page for good discussion on formula optimization. http://chandoo.org/wp/2012/03/20/optimize-speedup-excel-formulas/
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
What is the future of 64-Bit Excel in large companies and thus the future of 64bit xll development?
Thank you!
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.
So far, which blog article on Chandoo is your favorite?
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):
http://chandoo.org/wp/2010/09/16/excel-circular-references/
http://chandoo.org/wp/2012/07/19/analyzing-20000-comments/
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!
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.
Very cool. I did not know that...
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?
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.
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.
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.
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
Do you know any tablet that can handle big excel spreadsheets? Have you looked at Microsoft PC tablet surface 2?
Thanks,
Hedieh
Unfortunately no. I have a Samsung Tablet, but I do not use it for Excel. I just play games or read books on it.
Hi! Why did you ever start with this application - MS Excel? What was the reason? I just wonder... Thank you for reply!
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.
Hi Chandoo,
Can you please explain Index Match function easily with example.......
Sure. Please see this:
http://chandoo.org/wp/2010/11/02/how-to-lookup-values-to-left/
Hi,
Thanks A lot for your help..
Can you explain me how to use offset in Excel and VBA as weel with an example
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.
As the site grew, organizing it became a big challenge too. But we are trying to make it easy for new visitors to use out site better.
Please use below links:
Welcome page: http://chandoo.org/wp/welcome
Archive of all posts: http://chandoo.org/wp/archives/
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
We will. This is something I will be talking about more in 2014 and beyond.
Hey Chandoo, I was wondering if you or one of our excel masters can post a tutorial teaching Logistic Regression in Excel...
I am not sure what this is. But I can research and put an article sometime in future. Thanks for the suggestion.
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?
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.
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?
You can use a formula to handle this. In column E you can write =if(new_calculated_payrate=current_pay, "", new_calculated_payrate)
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?
Ideally you should use a simple macro to handle this. We have an example here:
http://chandoo.org/wp/2012/04/09/consolidate-data-from-different-excel-files-vba/
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.
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.
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?
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.
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?
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.
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.
Thanks Amado...
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.
Why did you left your job? and how difficult was the decision?
@nimitgupta:disqus
Chandoo discusses this and much more at: http://startupdesi.com/
I left my job as I found working on Chandoo.org is more satisfactory and fun. Also, it gave me the freedom to be with family and watch my kids grow. See this for a brief explanation
http://chandoo.org/wp/2010/04/26/phd-startup-story/
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!
Oh yeah: Pivot Tables or Array Formulas? (or... Power Pivot?)
Power Pivot first.
Then IF(and(have time, energy, know how), Array Formulas, Pivot tables)
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 ! 😉
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.
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?
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.
@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
Daniel Ferry explains them in detail in his Excel Hero Academy http://academy.excelhero.com/excel-hero-academy-tuition/
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 🙂
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?
You can use User Forms and hidden sheets to do this. Please keep in mind the Excel protection is not fool proof.
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
Harry
You can email Chandoo or me directly
Chandoo: http://chandoo.org/wp/contact/
Hui: http://chandoo.org/wp/about-hui/
You can also post files in the Chandoo.org Forums http://chandoo.org/forum/
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?
@disqus_tJwLPRrxRT:disqus
Develop a test set of data and ask them to do a number of tasks using VBA
I'd suggest tasks:
Format the data
Sort/filter the data
Delete Rows where a cell contains a value
etc
Also, see this demo module - http://chandoo.org/wp/vba-classes/demo/
Can you point me to a how to sensitivity analysis example and possible sensitivity analysis add ins you might recommend?
@Al Delgado
I use Data Tables for sensitivity analysis all the time
and it is free and built into every Excel version.
Refer: http://chandoo.org/wp/2010/05/06/data-tables-monte-carlo-simulations-in-excel-a-comprehensive-guide/
Where it is discussed
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!
@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
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')!
Hi Chandoo,
i was wondering, if there is a way of adding extra data labels in charts without putting additional dummy data series?
@propunta:disqus
Yes, Upgrade to Excel 2013
It has a lot more functionality in that area
Also you can use Rob Bevey's XY Chart Labeler.
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.
@Pat
That is because not all the Excel functions have been properly or fully converted to 64bit
Interesting... I did not know that.
Similar issue with the 64,000 row limitations that still exist with some functions
Who can you recommend to design and build dashboards from and existing spreadsheet?
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.
thank you very much!
I would like to help/work on this. No charge!
Shailesh, Contact me and I will email you my worksheet.
Contact me via @patwardhan on twitter. I could not figure out how to message you on disqus
My email is p_shailesh @ hotmail.com
could you give some shed of lights on my existing spreadsheets on how to improve it? how can i contact you?
Hello Teh, can you contact me via @patwardhan on twitter. I could not figure out how to message you on disqus
Hi Teh, My email is p_shailesh @ hotmail.com
what is iferror and where do we use it
@Zainul
Have a look at: http://chandoo.org/wp/2011/03/11/iferror-formula/
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.
@joslbo:disqus
Have a look at the Evaluate Function
It is discussed here: http://chandoo.org/wp/2011/05/16/lost-excel-functions/
What are some of the issues and awesomenesses that we might find with the Watch Window?
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 🙂
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.
@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
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!
Hi Chandoo,
Happy Thanksgiving!!! May I ask if there is a possibility that you can offer "scholarships" in your online or masterclass excel courses?
@alphaomegabetachi:disqus
I think that is a great idea!
But seeing as it belongs to Chandoo he will need to decide
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.
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 🙂
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.
That's great Chandoo...more power to you!!!
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
Good idea... I think if we make it an annual event, it can be easily managed.
Yes, an annual event, perhaps every Thanksgiving Day... 🙂
Thanksgiving Day is only relevant to about 4.4% of the worlds population.
Another method maybe 2 months before Chandoo's next relevent course
Is there life in Uranus?
@Condorito
Yes, But not as we know it!
and... they are good at excel too!
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..
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.
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.
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?
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.
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 🙂
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.
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.
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 ?
Hi, some of this is best done via email 🙂
Hi Chnadoo,
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...
I am not sure about this. Can you check with microsoft website please.
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.
I am not sure what you mean by "restricting functions". I suggest checking out http://newtonexcelbach.wordpress.com/ for some excellent examples of structural engineering models.
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.
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.
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.
Thanks Chandoo, I did do that but no responses that are conclusive as yet...
Thanks for all your knowledge sharing, God bless...
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.
Hi Dan... Thanks for the love man. I am sure we can catch up next time I am in Ohio (probably in 2015).
Can you see this page for explanation on perpetual calendar using Excel - http://chandoo.org/wp/2008/12/04/free-excel-calendar-template-download/
Thank You. Happy Thanksgiving.
Dan
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
Hi Anil... You can try using either form controls or selectable cells (VBA) to mimick slicer behavior and trigger filters on pivot. See this for some examples.
http://chandoo.org/wp/2012/08/02/making-dashboards-interactive/
Could you guide me how to create flexible chart, waterfall chart, dashboards, and football field for building financial modelling? Thanks a lot!
sure... check out these pages.
http://chandoo.org/wp/excel-dashboards/
http://chandoo.org/wp/2009/08/10/excel-waterfall-charts/
http://chandoo.org/wp/2009/07/02/secondary-axis-combination-charts-howto/
http://chandoo.org/wp/2010/07/21/financial-modeling-introduction/
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?
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 ?
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
See this for an example - http://chandoo.org/wp/2012/02/22/design-customer-service-dashboard/
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?
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.
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?
Thanks in advance,
John Michaloudis
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.
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)!
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
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.
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 ??
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/
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.
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.
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 )
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.
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.
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.DisplayAlerts = False
Application.ScreenUpdating = False
Dim Username As String
Username = Sheet1.txtUserNameIn.Value
Dim password As String
password = Sheet1.txtPasswordIn.Value
'Check to see if data is entered into field: txtUserNameIn
If Sheet1.txtUserNameIn.Value = vbNullString Then
MsgBox "You must enter your username.", vbOKOnly, "Required Data"
Sheet1.txtUserNameIn.Activate
Exit Sub
End If
'Check to see if data is entered into field: txtPasswordIn
If Sheet1.txtPasswordIn.Value = vbNullString Then
MsgBox "You must enter your Password (case sensitive).", vbOKOnly, "Required Data"
Sheet1.txtPasswordIn.Activate
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
Set sh = wk.Worksheets("ADMIN")
Dim atsh As Worksheet
Set atsh = wk.Worksheets("Attendance")
On Error Resume Next
If Username = Application.VLookup(Sheet1.txtUserNameIn.Value, sh.Range("A3:A" & sh.Range("A65536").End(xlUp).Row), 1, 0) Then
'****************
Select Case Err.Number
Case Is > 0
MsgBox "Username not found", vbOKOnly
Exit Sub
End Select
On Error Resume Next
If password = Application.VLookup(Sheet1.txtUserNameIn.Value, sh.Range("A3:B" & sh.Range("A65536").End(xlUp).Row), 2, 0) Then
Select Case Err.Number
Case Is > 0
MsgBox "Username not found", vbOKOnly
Exit Sub
End Select
Sheets("ADMIN").Visible = xlSheetVisible
MsgBox "Password & Username Accepted"
Sheets("Attendance").Select
Sheets("ADMIN").Visible = xlHidden
atsh.Range("A1").Select
Else
MsgBox "Username & Password Combination Not Accepted, Please check"
'Unload Me
'ThisWorkbook.Close (I'm not wanting to go-live with this bit yet, so commented out)
End If
End If
Application.DisplayAlerts = True
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
Thanks for your love. I am sorry for not helping here. Can you please post this in our forum - http://forum.chandoo.org/
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
Thanks in advance!
Thanks for your support and love Randy...
What is the logic behind deciding 3rd item or 5th item as the one you want?
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 ?
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
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
@Pradeep
The format only effects how the date / time is shown
Internally it is still eg: 30:30 but will display as 8:30 am
Could you tell about Excel Plug-ins. What is and how it useful?
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.
What would you recommend as the best desk reference book for Excel? Thanks.
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/
Thanks for the tips. One for charts I think is what I need. I'll check the links!
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/
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...
@disqus_Y9NIXVdyPH:disqus
Power Pivot
http://chandoo.org/wp/2013/01/21/introduction-to-power-pivot/
Hello, I have the same problem as Shankar. Power pivot is probably the solution, but the DAX layer is not so easy to understand. Actually it is a new world... Any tips in order to start with DAX ?
Thank you
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..
Shankar Narayanan
Have a read of Sajan's answer at: http://chandoo.org/forum/threads/calculating-exact-time-taken-considering-sunday-and-working-hours.13547/#post-80025
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
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)
Are you still actively involved with Excel School? I posted a question almost a week ago, still no response.
Hi there.. yes, but for questions, I have staff who responds. We must have missed your question somehow. I will check.
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
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.
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
@brodiean:disqus
You could use a Hyper Link
They allow Hovering and can display text
Add a Hyperlink
Leave the address blank
Add a ToolTip to the Hyperlink in the Dialog
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?
thanks for reply anyways
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?
Good idea.. I will certainly do something like this in future.
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.
Thanks in advance
Neeraj Kumar agarwal
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.
Thanks in advance
Neeraj Kumar agarwal
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.
Dear Sir, Thanks for your valuable reply.
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.
Neeraj, there is a way to do this by using the PasteSpecial, Multiply option.
Basically, you need to multiply all the columns containing numbers stored as text by 1, so that Excel 'converts' those cells to numbers.
You can find more information about how to do this, here:
http://www.techrepublic.com/blog/microsoft-office/use-paste-special-to-perform-calculations-while-pasting-in-excel/
Neeraj, you can do this by using the PasteSpecial, Multiply feature.
Basically, you need to multiply all such cells / columns by 1, so that the value remains unchanged, but the text entries get converted to numbers.
You can read more about it here :
http://www.techrepublic.com/blog/microsoft-office/use-paste-special-to-perform-calculations-while-pasting-in-excel/
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
Thanks for your comments. You can use a formula like this
=SUMPRODUCT((colC)*(colD)*(colB="category name")) to get the total sales.
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
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"))
Thanks Chandoo.The 2nd formula is perfect. the 1st does not work though:)
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
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!
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/
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.
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.
Thank you. I'm trying it
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.
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.
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
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.
http://chandoo.org/wp/2012/11/27/extract-subset-of-data/ See this for Advanced filter + VBA option.
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
@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.
I was trying to avoid using VBA but I might have to go there after all. Thank you for your reply - much appreciated.
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
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
Thanks for reply.
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
Let me check and reply to your email.
Ask me anything is going be an annual event. We will close this post for new comments probably in a few days.
Thank you very much
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
Thanks Venkatesh. See this for a way to convert numbers to words
http://www.yogeshguptaonline.com/2009/07/excel-functions-convert-numbers-into.html
Thanks Chandoo, Continue your good job
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....
See this please... http://chandoo.org/wp/2010/09/10/working-hours-formula/
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.
You can use a macro to transfer payment data to general data. See this for a 5 part crash course on VBA macros
http://chandoo.org/wp/2011/08/29/introduction-to-vba-macros/
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).
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.
Thanks Chandoo. I was able to changed/increase the retrieve record/row under Data ---> Properties
hmm.. did not know that. Thanks for sharing 🙂
Thanks Chandoo. I was able to changed/increase the retrieve record/row under Data ---> Properties
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.
Please help as i am still new in vba..Thank you.
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.
What is the best route to getting a job as a financial analyst?
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
4. Understand how Excel can help you build better models
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.
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.
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
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.
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.
[…] were slow during Thanksgiving week, so Chandoo posted an "Ask Me Anything" challenge. Read through the questions, to see what people wanted to […]
I have 30 workbooks iwth a single sheet in each. I want to merge them into one work book. I know on
Please see this tutorial for a VBA solution.
http://chandoo.org/wp/2012/04/09/consolidate-data-from-different-excel-files-vba/
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
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.
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?
Why not enter the descriptions in data validation list? You can then use another lookup to fetch the code if you need it elsewhere.
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.
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
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 . . . :-/
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
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).
See this page for introduction to tables - http://chandoo.org/wp/2009/09/10/data-tables/
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
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.
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.
http://chandoo.org/forum/threads/powerpivot-measure-with-circular-reference-error.13493/#post-80203
Thanks,
Pablo
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.
Hi Chandoo,
What an overwhelming response, how are you dealing with it?
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?
Screenshot attached
Interesting question. Let me create an example with this and share it on Chandoo.org sometime in future.
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
A few days back Jeff wrote an excellent article on this at DDOE. See it here:
http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/
PS: Currently Dick's site is throwing a server error. So try it after sometime.
Chandoo
Did you post this question so you could get your post response count up ?
🙂
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 🙂
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?
=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.
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.
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
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.
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?
@Herman
Have a look at: http://forum.chandoo.org/threads/text-to-speech.13219/
I think built in "text to speach" utility is not very impressive. You can probably purchase a recording of common words (or record them yourself) and then use audio player (Active-x) to play the voice.
Can the VBA course be started in Dec. I have more time available in the end of the year
Hi Steven,
This course is available anytime for enrollment. Please visit http://chandoo.org/wp/vba-classes/ whenever you are ready and join us.
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
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.
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
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.
Thank you in advance !
@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
How to use FORECAST function?
@disqus_5j6oEdKOu2:disqus
Have a read of: http://chandoo.org/wp/2011/01/26/trendlines-and-forecasting-in-excel-part-2/
Trouble to find vbc code that can update data in next column after match two same value the open workbook from closed another workbook??
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..
@Sabir
Goto the Software section of:
http://chandoo.org/wp/about/what-we-use/
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
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.
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?
Trying to understand the Index (reference) formula.
Cameron
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?
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
@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
thank you, sorry this took so long
Hui:
As I said before but it looked confusing when I read it today, your answer was very helpful.
thank you again
Deane
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
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.
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.
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
@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.
How to fix my format for employe.
I want to learn VBA
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.
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.
@David
I would add the other policy holders as fields (new columns) in the main record rather than new records
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.
Thanks in advance for any help you can provide.
Hello Sir,
Can you please share the list of each & every books that you read till date..
Waiting for your reply.
Thanks..
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...
@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.
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.
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.
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
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)?
Thanks very much in advance!
=IF(AND(O5="Wife";O5="Mother");"Smt";IF(AND(O5="Father";O5="Husband");"Sri";"Smt"))
this is not working. pl help me
@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"))
Hi Chandoo,
I have two questions please.
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
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.
@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
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
How to add particular colourS from a data i.e. not a single colour but two or more colours at a same time.
i want to cumulate txt files (notepad) in folders in single file and import into excel with macro can u provide
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!
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
Any Similar dashboards or files on D3js
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?
@Pieter
0.695 is between 29 = 0.696 and 30 = 0.684
So you want the value higher than 0.695
Is that correct ?
Thank is correct yes. Thanks.
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
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
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
@Suhrud
Unless you have an alternative or older backup I suspect your going to have to recreate the file
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.
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.
PLEASE HELP ME
@Kiran
=NETWORKDAYS.INTL(A1,A2,11)
=13
where
A1=6/6/2015
A2=20/6/2015
11 = Sunday is only Weekend day
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
Please help...
@Pieter
Yes this can be done
Can you please ask the question at the Forums and attach a sample file
http://chandoo.org/forum/
Hi,
I would like to know how can I find minimum value of a range excluding the zero data?
Thanks a lot
@Zeynep
Use: =MIN(IF(A1:A10>0,A1:A10,FALSE)) Ctrl+Shift+enter
Likewise , I would like to know how can I find minimum value of a range excluding the Div0 # data?
Thanks a lot Hui.
Many many thanks....
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
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!!
Thanks for any help you can offer!
@Lance
Can you post the problem in the Chandoo.org Forums
http://forum.chandoo.org/
Please attach a sample file to aid a specific solution
Hui...
Formula for allowance of employees
Days. Allowance
0-10 11
11-19. 23
20-31 45
@Manoj
Change your table to
0 11
11 23
20 45
Then use a VLookup
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?
@Ashley
This sounds like I am answering a homework problem, which is not the idea of Chandoo.org
If it is a home work problem you should be doing the research to answer the question yourself
This is a good starting point:
http://chandoo.org/wp/2010/04/20/introduction-to-excel-sumifs-formula/
Could you help me describe how writing an incorrect IF statement might produce the wrong data?
@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
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?
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.
Please help?
@Pieter
Can you post the question in the Chandoo.org Forums
http://forum.chandoo.org/
Please attach a sample file to simplify the solution
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?
@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
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
How do I earn the privilege to post a question?
@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
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,
@Pieter
Have a look at: http://chandoo.org/wp/2010/11/04/analysing-large-tables/
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
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.
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
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
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
Please inform an efficient VBA macro to delete more than 64000 blank rows from the active worksheet
I am working in Excel 2013 and have the following problem :
I have two columns A and B. Column A is populated with negative and positive numerical values while Column B is populated with positive-only numerical values. I want Excel to find in Column A the first value (from top to bottom) that is greater than zero (i.e. positive) and return the value of the corresponding cell in Column B. I have tried =INDEX(ColumnA;MATCH(TRUE;ColumnA>0;0)) but it gives me #Value!
Please help me. Thanks a lot.
Hi,
I want to sum values in a column that meet a criteria in the next columnbut this column has more. Than one values assembled. How can Isend u Screenshot
i have 4 excel files. Each of these files have two sheets, one named "Branch" and the other named "HO". I want to upload these files in power bi but i want to combine all the sheets named "Branch" into one power bi table, and the other sheet named "HO" into another power bi table
You can try the approach outlined here -
https://chandoo.org/wp/combine-excel-files-using-power-query/