Menu
We Want You – Revisited
Huis , Posts by Hui - 128 comments
Chandoo.org Wants You
Over the past 5 years Chandoo has written about 1,500 posts on all things Excel and Hui has contributed another 80 posts mostly targeted at the application of Excel techniques to real life situations.
Two years ago we asked you “Was what we were posting actually what you wanted to see ?”
http://chandoo.org/wp/2011/06/22/we-want-your-ideas/
71 people responded with 101 ideas which were summarized here:
http://chandoo.org/wp/2011/07/22/we-want-your-ideas-results/
But that was two years ago
So again we’re opening the floor to you, with a single question:
What would you like to see discussed in future posts at Chandoo.org ?
Your ideas can be as specific or general as you like:
One Rule only: The Idea must involve the Functionality, Use or Application of Excel !
We cannot guarantee that your idea will result in a Post, But if you don’t ask, you won’t receive
We will do our best to schedule posts where most requested and suitable skills and time is available by authors.
So, What would you like to see discussed in future posts at Chandoo.org ?
Let us know what you’d like to see in future posts in the comments below:
Hello Awesome...
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.
Related articles:
|
Leave a Reply
| « A quick Excel tip while on bike… | Are you ready for 2,000 miles, 15 days & 10 Excel tips road trip? » |
At Chandoo.org, I have one goal, "to make you awesome in Excel & Power BI". I started this website in 2007 and today it has 1,000+ articles and tutorials on data analysis, visualization, reporting and automation using Excel and Power BI. 
128 Responses to “We Want You – Revisited”
dear Chandoo Team,
thanks for your all efforts i am living in Iran and couldn't join to your life Classes courses and just able to use of your social post and examples but i think if your good team increase your social post with applicably sample example will be appreciated highly.
again Tank you so much.
Mohammad from I.R.Iran
same problem here
Hi,
Firstly I like to take the opportunity for the awesome site and the content.
Over time Chandoo.org has become my one stop destination to attain Excel nirvana! keep it up.
I would be overjoyed if you can illustrate how to embed dynamic charts with all bells and whistles in presentation, though in your own words "Powerpoint is another beast" (refer : http://chandoo.org/wp/2010/08/31/dynamic-chart-with-check-boxes/)
Looking forward for more awesome posts in the coming days.
Best Wishes,
Sayan
Anything PowerPivot/PowerView is always appreciated. And thanks again, so much, for everything you do for the community!
I deal in excel about 95% of the day. I work in a manufacturing environment and we don't have an expensive ERP system. We try to do everything in excel. the challege we have is we build one off items and not 10000 widgets, so scheduling and planning are very difficult. is there a way to do a monte carlo or some system to place date requirements and have it calculate the most efficient start date according to date needed and its projected runtime?
so all in all, manufacutring tools would be highly beneficial.
Greg, that sounds like a linear programming type problem that the Excel solver add-in can help with.
I think there should be more written about using Excel for sports analytics.
As well, I would love to see more articles on implementing Operations Research methods in Excel. Excel can do some simulation and optimization, as long as the problem is scalable. Excel can even do simple neural networks. I don't believe these areas have been written about extensively.
Hi,
I'd like to see a post on linking Excel to word, instead of using mail merge, can I use an excel button which when I click will generate a Word document from data in Excel
Yes. I am using an existing word Document that gets "poked" with data collected by an Excel form.
Or similar to this would be How to generate a powerpoint/PDF presentation document from excel. I find I do alot of this using links from excel to powerpoint. I would like to then break these links(using a macro, which I have) and save the powerpoint as a pptx (or PDF) without links before sending out via email. The problem here is macros cannot be run on opening powerpoint. If you explore something like this that would be great. This on top of all the great work is done in this community.
Thanks again and keep up the good work!!
You can do this.
Sub RangeToPresentation()
' This procedure copies the selected worksheet range as a picture from an Excel worksheet,
' then pastes it into the active slide of a PowerPoint presentation
Dim PPApp As Object 'PowerPoint.Application
Dim PPPres As Object 'PowerPoint.Presentation
Dim PPSlide As Object 'PowerPoint.Slide
Dim myViewType As Object 'Save view type to restore when done
Const ppViewSlide As Long = 1
Const ppViewNormal As Long = 9
Const ppLayoutBlank As Long = 12
' Make sure a range is selected
If Not TypeName(Selection) = "Range" Then
MsgBox "Please select a worksheet range and try again.", vbExclamation, _
"No Range Selected"
Else
' Reference existing instance of PowerPoint or start a new one
On Error Resume Next
Set PPApp = GetObject(, "Powerpoint.Application")
If Err.Number Then
Set PPApp = CreateObject("PowerPoint.Application")
PPApp.Activate
Err.Clear
End If
' Reference active presentation or create new one
Set PPPres = PPApp.ActivePresentation
If Err.Number Then
Set PPPres = PPApp.Presentations.Add(True)
Err.Clear
End If
'If in SlideShow view, exit
PPApp.SlideShowWindows(1).View.Exit
Err.Clear
' Reference active slide
Set PPSlide = PPPres.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)
If Err.Number Then
Set PPSlide = PPPres.Slides.Add(1, ppLayoutBlank)
Err.Clear
End If
On Error GoTo 0
myViewType = PPApp.ActiveWindow.ViewType 'save current ViewType to restore later
PPApp.ActiveWindow.ViewType = ppViewSlide
' Copy the range as a piicture
Selection.CopyPicture Appearance:=xlScreen, _
Format:=xlPicture
' Paste the range
PPSlide.Shapes.Paste.Select
'Resize the image
With PPApp.ActiveWindow.Selection.ShapeRange
.Left = 0
.Top = 0
.Height = PPPres.PageSetup.SlideHeight
If .Width > PPPres.PageSetup.SlideWidth Then
.Width = PPPres.PageSetup.SlideWidth
End If
End With
' Align the pasted range
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
PPApp.ActiveWindow.ViewType = myViewType
' Clean up
Set PPSlide = Nothing
Set PPPres = Nothing
Set PPApp = Nothing
End If
End Sub
Could you embed your videos instead of linking to Youtube? At my workplace Youtube is blocked so I can't view the tutorials that you post.
same problem here 🙁
And me.
Same with me 🙁
Macros and the formula that would be used when the number of rows change every time a specific report is ran?
I also work in manufacturing and would love to see more on Power Pivot, and more manufacturing ERP examples (
Hi Chandoo,
I would love to see articles demonstrating Excel being used to process Internet sourced data, such as that from the UK Office of National Statistics or currency sites. All the better if they were linked through Powerpivots with time/date data and other data sources (Access,MySQL db etc).
Many thanks
LeonK
More Analytics and Real work examples would be great... Cheers
Yes, I agree. Deeper and extensive samples. Comprehensive formulas on Forecasting, Inventory Management and the likes.,,
I'd like to see more on calculated fields in pivots based on different types of source data layouts. This has been a long-standing point of pain for my team.
I love the posts and the real world stuff you do with Excel. I would like to see searches throughout the workbook that would result in populating charts or grafts.
Thanks for all you do!
Jim
How about how to use Vlook up with multiple spread sheets.
I would like to open pdf file with excel and data processing with power pivot.
thankyou. Angel
I think we could start discussions about machine learning using excel formulas and VBA. Excel is very rich with math functions and machine learning is a hot topic nowadays. That would be awsome. I could even help with some posts.
hi;
firstable i would like to thank you so much!
i would like to see some post bout the power of index;much,offset,sumproduct
and also so uses of pivot table.
thank you and best wishes.
http://www.simlaoui.blogspot.com
I would also like to learn more about the index, match formulas
Hello chandoo for israel
i love your website,facebook website and your aplication for cellphone. I am hooked in evry way to you. As our friend from Iran said i also wanted to see your live courses in the US. I would like you to take a little step back give us a data like sales threw the years or p&l of 3 years and show us how you take a large data and make awsome things and allways on the same data so we can folow along untill we can see a beautiful dashboard. I was very excited when you saw bill jelen i posted it in my facebook and in my finacial forum on excel tip of the week
best of luck
amiram dekel
Appreciate you reaching out to get some feedback but all I can add is that you are right on target with your instruction, how-to's etc, and especially the application examples. You have taken even the simpliest Excel functions and techniques and illustrated how powerful they are for certain applications. Your recent bike ride video was great and I am even using that. You are the most popular source of Excel instruction I hear people refering to. Keep it up. Quality stuff you're providing.
How about using a pivot table to do a rolling 12-month summation over a multi-year data set.
Using Sql and Excel, to speed up filtering large data files; explore VBA the most and also the use of index formulas.
We have seen all those points in your posts and they are welcome, ever.
Hey Chandoo,
Great stuff! I am quite interested in real time (or near real time) linking between excel and access. For example, linking to different source tables in access and getting excel to mix and match the data to present it in cool graphs and charts.
I'm also into macro writing tips!
Thanks for the great work,
Anthony
Hi Chandoo,
Great site, posts always looking forward to each email as tips, tricks are always useful. How about information on linking and using excel with other applications such as Access, Word and within sharepoint
Hi Chandoo,
What we really need is a step-by-step detailed tutorials about dashboards and interactive charts.
yum yum yes please
I'd like to learn more about pulling in data from other excel files. In particular about managing links and learning what Excel functions work if linked files not open.
How to calculate work week numbers when fiscal calendar starts mid year. How to take the sum of hours of tasks and figure out how many work days to completion
Hi Chandoo and Ninjas
I would like to see advanced statistical analysis using excel appear on Chandoo.org, such as Multinomial Logit Modelling.
Cheers.
Hi Chandoo,
I am a teacher in the US... much interest and effort is placed in being "data driven", teaching what the data indicates needs to be taught. That is different for each student. Would you be able to group students that have mastered particular skills and those that have not and produce reports that would indicate students that need intervention and students that have mastered the skill and may move on. 🙂 Many thanks for your selfless efforts to keep this community educated, up-to-date and awesome.
Dear Chandoo,
I would like to thank you & your team for this awesome website. I have learnt a lot from this and continue to learn and apply the learning in my work.
I would like to see Data Analysis & Forecasting using Excel’s statistical and other functions. Hooking onto the live database, extracting and analysing data, which refreshers automatically and reflects in a Dashboard.
I have done VBA, Financial Modeling & Project Modeling courses through your website. I should say the courses were very well written & presented.
Once again thanks for sharing your knowledge.
Chandoo,
I still want to attend an Access VBA school that was mentioned a couple of months ago.
Ron
Chandoo you might regret asking us all what we would like to see. That could be the making of a rod for your own back. However my two pence worth is....
As a newbie to your wonderful site I have found lots and lots of wonderful examples in your past posts on charting and dashboards. Wow the list of things I have learnt is enough to make you go dizzy. But the one thing that I would like to see is taking some of the past dashboards that have received great feedback and teaching us some of the tricks they use. For example I have been trawling through some of the dashboards from your past data visualization challenges (http://chandoo.org/wp/2010/01/04/sales-dashboards/) and learning lots of tricks but some tricks I just get lost and frustrated or the source files have now disappeared.
Whatever you bring to your posts I know we will continue to learn a lot. So keep up the great work Chandoo.
Most of what you deal with is for predetermined lists. I deal with tenders, quotes etc where the submissions can vary significantly, we can get 3 or 30 replies, with costs $50k to $150k and then they are given rankings.
I use a lot of your tutorials for charts and dashboards, but many I can't transfer to the way I need.
I guess I need a more fuzzy logic or outside the box type of thinking that can handle a non predetermined solution. Hope this makes sense.
1. TIMELINES! This is good: but could be even more awesome - e.g. ability to have multiple 'layers' to group events with common topics, use of pop-ups with links/embedded files, similar to your grammy bump chart.
2. TREE DIAGRAMS! Some add-ins are available, but I don't really like them. Example use: choosing lowest cost / highest return option for dispute resolution based on probability and financial estimates of alternatives such as negotiation, litigation, mediation, etc.
Thanks for considering, keep up the great work.
This link didn't display in my post above: http://chandoo.org/wp/2009/07/09/project-milestones-in-timeline/
Hi Both,
Thanks for Your effort .,
We need more on the basics of excel, VBA (simple macros) and every week a competition kind of things (Data analysis,Chart making, Simple automation, Pivot , simple dashboards)
Would like more on add-ins available in excel versions and how to install and use it and where like data mining in excel 2010
Thanks
Senthilkumar RM
Pls continue formula forensics. Specially the array formulae. Thanks
VBA please. Many thanks for a great website anyway.
Dear Chandoo,
Its a pleasure reading your posts. I am always waiting for your posts. I got to learn a lot of things from your posts. I wanted to learn something like there is a single entry point and on use of a single button / command the data gets saved at some other location. I would also like to know the use of drawings in excel.
Regards,
Ilyas
Hi chandoo,
I would like to see some workflow on how to physically design a dashboard. Even without the formula, just the graphical interface. I want to know what color combination to use for line charts with multiple data and the display that I can use for my dashboards. I like your color schemes by the way and adapt them to my reports.
Thanks for all the hard work.
Regards,
Norries
Hey Norries,
Check this out
http://www.perceptualedge.com/articles/visual_business_intelligence/rules_for_using_color.pdf
Also google the name Stephen Few (this is one of his) he has some great advice on this topic for the BI community
Hi Chandoo,
I am an MCT trainer and often receive alot of questions related to using excel as a front end user interface, i have done a few but there are certain limitations to excels capability as you would need to couple this sort of usage with a back end data base like SQL,
However would be great to see discussions on this and how people from around the world deal with this especially when resources are limited in an employees company...
Hi Chandoo,
Like everyone, I would also like to thank you for your more than amazing tips!
I'm so flabagasted by your tips, that I can't see any room for improvement as most topics have already been covered and applications to tips have been provided in the archive section.
Keep up the great work!!
Regards,
Raasesh Parbhoo
- South Africa -
Dear Chandoo and all excel Gurus.. thanks for making our lives easy. I use excel extensive and these days use it for dashboards as well (instead of powerpoint slides). I forward all my dashboards to senior management in excel files.
What I need more from all you Gurus is to make excel do something which visibly it cannot do. I mean I would love to have more 'flash'sort of animations in excel. I love one VBA macro that you tought us in which you click a cell and it gets special formatting. That is one of the examples of something i couldnt imagine excel could do. Similarly hiding and displaying certain graphs with VBA codes is another good feature i use a lot. So in brief, I would like to have more annimations in excel.
cheers
Good day
Like most of the other posts here, let me say that this a a great site for anyone that use excel, from beginner to advanced. I love you examples.
People think of me as an excel guru at work, this is only because of the wonder teachings on this site. My response to this is that I only know a fraction of excel and that even they can becaome GREAT @ Excel by using your site.
From my understanding this was the reason why this site was creatred. Personally I think that you are succeeding.
Keep up the great posts and examples. I have used some of your posts in ways that I dont think they were intended, they however have added a lot of value to my reports and is saving me a ton of time. This time is used to learn more about excel.
Chandoo.org! great job. Will like Chandoo to provide user friendly PM templates, particularly for scheduling and resourse allocation.
Regards
Pradeep Soni
Hello! I've been using Excel for nearly two decades, and only began to read Chandoo.org last month thanks to a work problem I had to solve, and already I'm kicking myself for not learning "this" or "that" much earlier. So thanks for everything!
I live in Finland, so I mostly use Finnish-localised Excel versions. The localisation is excellent as a rule, but there are problems... like the occasional discrepancy between the online help and the actual functioning. In Scandinavian usage, the decimal separator is a comma, the thousands separator is a whitespace, and the usual date separator is a dot. Excel works around this by separating arguments of functions with semicolons where the original was a comma. So whenever I follow English-language Excel instructions (like this blog), I mentally substitute semicolons for commas. No big deal.
But today I ran full-tilt into a problem I'd been skirting many times previously. Array constants separate items with commas and rows with semicolons, right? And that's how the Finnish-language online help has it, too. But when Finnish-language Excel sees a comma, it thinks "decimals!" and produces an error. By downloading a calendar template from this blog (which used named arrays) and looking at the conversion (automatically done), I found out that arrays break the norm by substituting backslashes for commas and keeping the semicolons. Bizarre, but there it is.
Now, after the lengthy introduction, given that this is a major Excel blog with a worldwide readership, perhaps this could be turned into a post or even a series - highlighting the differences between different localisations of the software. (Keyboard shortcuts are another major case - some shortcut localisations follow the physical location of the key and some the character produced by the keypress.) While most of the time the differences are invisible to the user, it's precisely when they aren't that helpful blogs like Chandoo.org are invaluable.
Hi ,
This link might be helpful.
https://sites.google.com/site/e90e50/vba/application-international-settings
Hello. I find the tips very useful. I'd like to see more on power pivots and how to write macros using visual basic. Some useful pre written macros would also help.
Chandoo, you're what they call round my way, "a complete legend!".
I'd be interested to see more posts around using external data sources, but I also think you should stick your neck out and start to layout some Excel theory. There are so many ways to do the same thing in Excel that I think you could save us all a lot of time. Lets start with the first rule of Excel,
Always K.I.S.S
I'll let you do the rest....
Echo all the others - this is THE site for Excel!! There are a few things I'd like to see... Big Data! Data Analytics! Handling massive piles of data - sure PowerPivot, but more importantly how to structure your data to maximize ease of queries, etc. And as well - dashboards - meaningful, concise, and based on rock solid data tables that you've structured your data into.
Love the work done here - always looking for more and greatly appreciating all I learn from the gurus here!
Cheers,
Gino
Me too! Even though it isn't efficient, my org uses Excel to manage/analyze tens of thousands of rows of data. Good design/structure is critical, as I've learned through mistakes.
I would love 'guiding principles' for efficient database design!
Hi Chandoo,
It will be great to learn best practices for VBA, like variable name conventions, coding techniques, etc.
Thanks,
Pablo
This is the site for Excel. However, I do notice that the same people get published over and over again. I wonder how many good ideas are not being shared because the person isn't deemed to be enough of an excel expert. There are alot of these sites all over the place.
@Kevin
There are only 2 regular posters at Chandoo.org (Chandoo & Hui) with occasional contributions by Sajan and Vijay
We would love it if more people would like to contribute
I love the tips. I hate the solicitations / encouragement to join / pay for things. It is fine to say here is a tip, offer the tip, and at teh bottom of the email say "Want to learn more? Here is how you can." and tell us.
More and more frequently, it feels like I see emails with NO TIPS but a solicitation.
I am a reasonably experienced Excel & VBA developer. I assemble tips & techniques (code snippets and/or descriptions & "links") in word document(s) (by type; "Range", "Lookups", "Filter", etc) both from my own code and from the web, for my ongoing reference. There are many great sites with tips, etc. (jWalk, CPearson, MrExcel, OzGrid, and YOURSELF of course.. to mention just a few) It would be nice to have a site where everyone could contribute their own items for sharing in a structured manner (not a Q&A forum, simply a lookup & search source of ideas). I guess this could turn into a huge project but possibly a interesting idea.
I do have a code management tool but find easier & faster to have word documents to browse or search through (a little night time reading).
I'd like to see posts on how to query databases using the MSQuery tool. There's not much information out there and the SQL statements that Excel uses are a bit odd.
Hi Chandoo,
I would like to see a discussion on VBA as applied to Accounting. Say, making a Balance Sheet and Income Statement extracted from a trial balance using VBA.
Thank you.
I would like to see posts on writing excel macros and User Define Functions from beginner's level onwards
I believe that its to upgrade and level up.. I would like to see topics about excel communicates to other ms office application such as
user stores data in access, then being pulled back by excel to create pivot, reports, charts, etc.
then user inputs data in excel then being stored in access.
I love to see more megaformula.
I would be delighted and highly impressed if someone could show me how to create an accounting prepayment schedule that looks at the period the invoice relates to and apportions the costs each month accordingly!
Thank you for such a wonderful website and always great ideas. I was wondering if it is possible to exclude a certain word/words from a sentence within a cell regardless of the position of the word/words within the sentence. Example:
In cell A1 = Product AX and Sub-Product BY Total
In cell A2 = Product AZ Specialty and Sub-Product TY Total
In cell A3 = Product AM Total and Sub-Product Specialty WY
I would like to see the everything in cell B1, B2 and B3, without the word 'Total' and 'Specialty':
In cell B1 = Product AX and Sub-Product BY
In cell B2 = Product AZ and Sub-Product TY
In cell B3 = Product AM and Sub-Product WY
Thank you very much in advance.
@SMR
Try: B1: =IFERROR(SUBSTITUTE(SUBSTITUTE(A1,"Total",""),"Specialty",""),SUBSTITUTE(A1,"Total",""))
Copy down
You may need to manually retype the "" marks as WordPress sometimes stuffs them up
Yahoo! It worked. There is a reason why people call you 'the man'. Thank you for the solution. Much appreciated.
Hi Chandoo,
Firstly, thanks much for providing a wonderful plattform to learn more about excel etc.
I would like to know about:
1. MS Access and linking and
2. Work with Excel as a front end and MS Access be updated as a back end etc.
3. Extract data from various sources to Excel through an excel as a frontend etc.
I will be the first person of joing MS Access when U start a batch 🙂
Regards,
Uday Kumar P
Amost same as Kiran listed, i am also looking forward to see more live examples about intergrate Excel with Access and Powerpoint.
Like get the source data from Access, Design and developed in Excel and, presented in PPT.
More automation, the better. cause that will really save us quite a lot of manual work.
Appreciated for the chandoo team great work, and yeah you are my must visit site each day.
Kiev (China SZ)
Your site is invaluable! I'd like to hear your thoughts on organizing HUGE spreadsheets. I know you're an advocate of one data sheet, but some of the reports I inherited have so much data that one sheet can't fit it all. Your ideas on an efficient design would be much appreciated!
Chandoo:
Very good material at your web site.
I am wondering if you can help with a stable design of an Excel 2010 work book to be used (1) in the shared mode with (2) worksheet protection on and (3) autofilters that can wrok with (4) vba code.
Tried the "user interface only" vba code but cannot get all the 4 components above to work together.
If possible, a sample workbook with these 4 features would be very helpful.
Thank you.
I would love some simpler, more basic issues and examples, as many of your postings are way over my head. Also, some quickie tips and reminders, such as: "control-colon" produces today's date as a static field.
Chandoo,
Your site is the bomb.com/awesome (just a funny way to say, it's super useful!). Thanks for all the hard work.
Your site covers a large amount of information already. I would like to see more Industry/Functional specific excel uses (i.e. accounting/finance, sales/marketing, ProjMgmt/Resource Mgmt) as well as an area (maybe a drop-down?) that aggregates across these categories in one place!
Obviously, excel can be used in almost any industry for many purposes, but certain areas are likely to always use excel (at least for now and the foreseeable future). So it'd be nice to have dedicated area in the site to focus on these industries/functions.
Thanks!
thx for all the effort you guys put into us, thx to you we're getting better and better!
What still puzzles me is the unexpected behavior of excel when working with pivottables and graphs and slicers. By experimenting different ways to get at the required result, we discover things that look strange at the beginning but (if we can remember what we(ve done...) help us with future similar (but slightly different, you know what I mean...) questions. For instance, making two pivottables or copying (and pasting) the same one, gives different opportunities...
I'm sure you've encountered similar oddities, and to share those (and made comprehensive to others, something I'm not very good at) would be great!
I would like to see how to use excel to make it easy to create a schedule of who is teaching what class when for our division. There could be dependent dropdown lists that help faculty know what days and times are ok to teach depending on the number of credits (hours) the class meets, the faculty list could be dependent on the program they teach in, and somewhere there would be an output that would tell you what the faculty load was (for us, it's 12 for undergrad, 9 for grad, and 15 for adjuncts) and if the person is over scheduled or under scheduled.
Love what you do! Cheryl
Something along the lines of how to prevent div/0 errors when setting up a sheet in the workbook that refers to up to 12 different user selectable options, each that change figures used in the formulas - (currently working on a workbook to handle heatloss calculations and heatloss summaries, that refers to: 1, user defined inputs - room dimensions and a number of definitions(from usually a sales person with no engineering knowlege). 2, refering to U-value tables based on input figures, and 3 generating MCS reports and certificates).
Desiging a 'front-end' that the user inputs the dimensions and a number of selectable options - Basically how to make a pretty interface for the sales monkey/surveyor generating the quote
1. How to automatically consolidate inputs from various individual excels with same columns but varying number of rows. Need to use this for creating status report sheet as a summary.
2. Can excel capture the date of data entry/ edit. Want to give a time stamp for some inputs.
3. How to assign a name to a tab using a rule. If the first tab is Acct 1 the second tab should be Acct 2 etc. Suppose someone to whom I send an excel template, adds a sheet I want it to be named in a consistent manner.
4. How to create an automated hyper-linked table of contents in a summary sheet based on the content of tables/ data in underlying sheets. For example, I want to list the open issues in a summary format in the table of contents. On clicking issues, they should lead to the relevant cells with the issue details in the correct sheet. If on the other hand, if I filter and select closed issues, the contents will be different and the links will be different. Can this be automated
I would like to understand how to add columns of data to a Pivot Table like Year Over Year % Growth without adding that to the range of data the Pivot Table is pulling the data from.
Hi,
many thanks for your this super cool site....
I want to know further on forecasting models.
I have learnt a little bit on seasonality indices and there by making forecast model using linear and simple exponential model.
I want to lean more on models where the holidays moves every year since they are linked to lunar calender based holidays. (eg Durga Puja in India/ Ramdn in middle & far east / Chinese New Year etc).
Also pls take a session on 2nd and third degree exponential forecasting model.
I would look forward for a early.
Thanks
Arindam
Thanks for the wonderful and helpful ways you have been increasing our knowledge frontiers. I would further wish to see you treat a method to practically expire a workbook after a stipulated time.
Is there a way to validate that elapsed time (as used for time card tracking) has been entered using a colon instead of a decimal (4:40 vs. 4.4)? The Excel data validation feature works up to a point (24 hours to be exact). Entering 40 hours of vacation time is one example of an entry that does not work with the time data validation in Excel.
Thanks!
Dear Chandoo
Thank you for your most interesting, useful and informative site. This is often the starting point when I have a 'how can I do this in Excel' question.
For some time I've been wanting to try a simulation of light bulb failures in Excel. The problem originated with a reliability study I did on an aircraft component which contains 70 tiny electric light bulbs, yes, the Edison type with a filament! These bulbs required replacing in a workshop thus the unit got a bad name for failing in service every few hours. The unit would be removed, light bulb replaced and refitted, only to fail again in a few hours service. My logic told me to replace all 70 bulbs once so that all start out with a new life rather than have a range of expected lives from 'about to fail' to new. The units typically had a total life of 20 to 30 thousand hours and over this lifetime each bulb would have been replaced many times.
This question has kept me puzzled for a long time and I cannot get my Excel knowledge around the simulation. My thoughts are to have a cell for total unit life (fixed for 1 simulation), a cell for expected bulb life, a cell for percentage variance of life and a row for each bulb. Each cell in the row needs to calculate a random life (expected life plus or minus variance) and add it to the previous achieved life. The total of each row must equal the unit total life. My output would be a horizontal stacked bar chart with each cell giving one chart data point.
Any thoughts or ideas would be appreciated.
Andrew
Hi Andrew ,
Since your problem seems to be an interesting one , can you visit the forum link ( http://chandoo.org/forums/forum/ask-excel-questions ) , scroll down till you find the New Topic in this Forum dialogs , and post your question there ?
Hi Narayan
Thank you for the pointer, I've posted the question with subject "Light Bulb Lifespan Model". Thank you also for the interest.
Andrew
A lot of talk about Succession Planning and Work Force Planning. Company wants to buy something, but I'm convinced we can build an organic product via Excel linked back to HRIS. Completely unrelated, but at one time I was drawing floor plans for office arrangements in Excel; clunky, but it worked at figuring out what would fit before shoving desks and file cabinets around.
Awesome site 🙂
How about the top three most innovative uses for vlookup? Or something along the lines of utilising vlookup?
And if not that, what about the top three most unusual/creative use of excel techniques in some way or another?
@DLB
have you seen this post?
http://chandoo.org/wp/2010/08/13/unusual-excel-uses/
simple but effective solutions to manage day to day works (eg) preparing of daily expenditure vouchers
Hi,
I'd like to read more about macros and integration of excel with other applications, such as access.
Stuart.
Thank you for the past post, it really helpful!!!
My opinion is can you please write more post about VBA or macro?
Most of your post for VBA & Macro are the example of outcome.
I believe it can help us to have better understand before reading the example. Thank you very much!!!
I registered with your website just 7-10 days back when I was looking for some solution in Excel-2007. I am new to this type of concept for helping out the excel problem. Had given the details as below but not
received any reply. My ID in your site is eexpert.
-------------------------------------------
https://www.dropbox.com/l/k8VTP8XKqdQRr3ivIwOgrb
above is the link of the sample file
The actual file has columns uptill M and with all records
filledup (approx 5000+) . Since this is a sample file I have shown
only records upto F. The actual delete record is from
column B to M.
sheet1
Code ProductID ItemName Type Qty Price
44678 AT1123 C POWDER D 1 12
44679 DGF34 GEL P 1 45
44680 FFR5567 OIL L 1 34
44681 GTR890 GREASE P 1 35
44682 QQNM678 B RUSH S 1 15
44683 AT1123 C POWDER D 1 12
44684 DT33 STAND A1 1 100
44685 BBG566 BAG A2 1 22
44686 GTR890 GREASE P 1 35
44687 DT33 STAND A1 1 100
what is required is when in any of the blank un-used cell
when I give just the Code number i.e 44678 and enter then its record
should get deleted but not the Code number(as shown below).Now
again in the same blank cell or next cell I give 44682 then
its record should get deleted. similarly 44685
In sheet1 day by day the entries will keep on increasing.
sheet2
Code ProductID ItemName Type Qty Price
44678
44679 DGF34 GEL P 1 45
44680 FFR5567 OIL L 1 34
44681 GTR890 GREASE P 1 35
44682
44683 AT1123 C POWDER D 1 12
44684 DT33 STAND A1 1 100
44685
44686 GTR890 GREASE P 1 35
44687 DT33 STAND A1 1 100
Hi,
thnx for the efforts that your team is taking....
I’d like to see a post on Macro, I am aware about how to create macro but dont know exactly what are the conditions where Macro dowsn't work like Can we use Filter, Pivot in Macro, or can we copy formula from other sheet while recording macro etc...
Yes, I do have same thoughts...
Hi, I have better plans for my career. AS a proactive step I wish to master excel. Its a fantastic tool and you make it quite simple.
Thanks-Hemant.
Hi,
Thx for a fine Excel learning site.
There is a lot going on with data on API, it would be nice if you will give some basis of how to get data direct in a Excel sheet from an API.
BR
Please show how to calculate distances between 2 points on Earth. If the Longitude and Lattitude are provided for 2 points on earth, then show us a way to calulate the shortest distance on the circumference of the earth.
I like to see merging of excel files as well as consolidating / merging of sheet. 2. Using index and match functions, 3. coping with different error messages, 4. simple dash board preparation and dash board functions
Please focus on the leading edge features of Excel, such as PowerPivot, Sparklines, Slicers, customizing Ribbon interface. Other things I think would help the greater Excel community are using the VBA IDE (setting breakpoints and watches, using Immediate window), using VBA between Office apps, VBA to automatically build files in PowerPoint and Word, and formatting tips for making sheets look good. I have some content I could donate to the cause if you are interested.
I would also love to figure out how to get the help to work in Office 2010. The help used to be an 11 on a scale from 1 to 10. Now it's a -1! I have found that certain topics just don't exist in the help any more, and the formatting is unreadable at times. What happened to Microsoft's quality? Or maybe I'm the only who uses help? I have found that the Offline Mode is better than Online, but I am finding I have to use Google now for almost all things were I used to be able to answer 80% of my questions with F1.
Hi Chandoo,
Like many others, I'd like to thank you for you amazing work on this website. Chandoo.org is for me the #1 reference if I'm stucked on Excel or if I want to learn new tricks.
My suggestion would be to add a "good practice" section. What I mean is that with Excel it is most of the time possible to reach a same objective using different ways (which is part of the beauty of Excel as well!), but with the time I discovered that all these different ways were not equal, regarding for example calculation efficiency or even reliability of the calculation process (i.e selecting manually the cells instead of using a sumproduct formula for the same result).
Another example where good practices could be useful: visualization of the data (graphs, tables, etc.). Excelchart.com is interesting for this thema, but it would be nice to have this covered on your website as well (maybe even with a slightly more moderate touch...)
I suggest you organize a contest to do a dashboard that can be viewed and understood by all people including colour-blind people.
As far as I know, it represents around 4% of the population.
Thanks for your site and wonderfull ideas and tutorials.
Sir please explain me about the formula of getpivotdata in excel dashboards. Thank you.
Your's faithfully
N.Vishwanath
Would love it if you could make a tutorial of elaborating more of linking excel to the internet and getting updates. (making it fun - for example getting live cricket commentary \ ball by ball update 🙂 😛 )
Hi Chandoo,
I am a big fan of your site. Analytical and Interactive charts helped me a lot. If you could show an example of how these charts can be depicted in e-mail HTML format that would be great.
Thanks
Hi Chandoo,
I am a big fan of your site. Analytical and Interactive charts helped me a lot. If you could show an example of how these charts can be depicted in e-mail HTML format that would be great.
Thanks
I am happy to follow this blog which is teaching and creating more Chandoo's on the web. 🙂
I want you to include how to include excel do Astrological Calculations. Basically a Kundli Software but embed in excel where in excel requires the inputs like date of birth and name and place and time of birth and give the general and specific predictions.
It may sound wierd but my second ask will be to make excel play music from a folder while I am working on it.
I am sure these things are possible for you....
You are Awesome,
Mohit
Another request after a recent problem that was resolved thanks to some valuable help from the forum is....
Creating complicated formulas. We all seem to have problems at one time and another and wondered if a workshop on nested formulas or how to evaluate then???
Thank you for the great job you are doing - both the training classes and the excel templates.
Hi Hui.
My excel is now at the point where I'm having to develop future-and-past-proof solutions. I'd love to see a comparison chart of what features, eg pivot tables, sumifs etc, work in which versions of excel, and which ones are discontinued or changed. Ideally as a simple matrix chart, with maybe to top 30 features, top 30 formulas?
Secondly, while we're wishlisting, how about a KISS guide to key principles (and common mistakes) of visual analytics in excel. You touched on this in your 'Gangnam' post but I'd love to see more.
Thank you
Hi Chnadoo and Hui..
I am a regular visitor on your site and like to read your weekly articles.
Thanks a lot for sharing your knowledge and Excel expertise.
I would like to hear on Excel to Access connectivity using VBA, Import and export of data from Access to Excel, Dynamic charts and Dashboard presentations.
Please also let me know if you are planning to conduct any workshop in Mumbai. I would like to be a part of it.
Cheers..
Hi Chandoo and Hui,
I am very happy to take part in this idea shearing.
I would like to see topics on "Interaction and data sharing between Internet Applications and Excel".
Regards
Tapan Bera
Hi Chandoo & Hui:
I would like to see a topic on web scraping. Taking data from a web site in different ways and transferring that data into Excel. Perhaps there is also a simple process of creating a dashboard of charts directly from web data.
Thank you,
Hal
Hello chandoo.org! I love your site. It is awesome!
What I would like to see discussed in a future post are the different ways(most general ones) you can make Excel interact with other applications(whit VBA), for instance, the simple notepad.
I am a Strucural Engineering student in Portugal, and I know that you can make excel interact with Robot Structural Analysis but I really don't know how...
topics of interest are worksheets object, sheet objects and ranges handling which causing run time error 1004 application defined or objects defined error or method range of object global failed but i was and now i am missing the vast correspondence from members of the Chandoo group discussing various topics in excel with clear explanations. and i am eager to receive these emails which helped me many times to tackle many difficulties of converting several lagacy programs to vba routines.
i also wrote to Chandoo about utilizing the blogs within the Chandoo group which are not available to me since i forgot the 'odd' password that was given to me: user jburbea which i tried to change but since i was busy handling the excel topic that was represented by HUI i forgot to change the password and now i forgot that password altogether.
please supply me with a temporary password to restore my logon to Chandoo.
thanks
joseph burbea
How to compile VBA code into .exe ?
VBA tricks and tips in Chandoo style.
The contend available is not sufficy....
Maybe a post about the inbuilt excel limitations of shared workbooks on networks and anything that can be done to overcome them; pivot tables not updating, vba not allowed in shared files, limited options that allow users to utilise locked cells, for example locking means autofilters can not be cleared and filtering on cell formatting (e.g. colour) is not possible even though on a normal shared document without locked cells autofilters are fullt functional and independent for each user.
Also someone else mentioned using vba to capture dynamically changing ranges on new data ranges - I've seen/used various methods but none seem very clean - what's the cleanest version of capturing or using CTRL A in vba? Surely some of the keyboard shortcuts should be more easily available in vba? Or maybe I'm missing something obvious.
Thanks!
Hello Chandoo,
I would like to bring a point forward. Excel is a tool for cases that we don't have lots of rows. For cases that we have a lot of rows, Excel becomes completely useless. Then we have to convert to other tools such as Microsoft Access and Microsoft SQL Server, this has been my experience.
I see in your website that you would advertise Excel for handling lots of row (millions of rows), which in reality that is not true.
The combo box in your dynamic dashboard doesn't refresh when we have lot of rows as well.
These made me to stay a way from Excel for doing my tasks in last year and using other tools along the way, however I love your site and people who contribute to it. GN0001
@GN0001
Some of the points you say are valid but should be clarified
Excel can handle 1 Million+ rows, but it is how you choose to handle these rows that determine how useful and effective Excel becomes as a tool.
If you add Columns of formula to process this data Excel will bog down and become plain awful
If you use some of the built in tools, Pivot Tables, Power Pivot/Map etc or other advanced computing techniques like Conjunctive Truth Tables, Excel can be a very useful tool
I would like to learn machine learning using excel. It's a hot topic made up of good smaller peices and formulas.