As promised earlier, here is our next interview.
PHD will be interviewing John Walkenbach, prolific Excel author and MVP.
John Walkenbach has authored 50 excel books, numerous articles and has one of most popular excel related sites – spreadsheetpage. If you have ever read an excel book, chances are it is authored by John. Some of his popular books are,
John Walkenbach’s Favorite Excel 2007 Tips & Tricks
Excel 2007 Bible
Excel 2003 Bible
These are few questions I am thinking of asking him:
- Excel 2003 or 2007?
- Your 3 favorite formulas
- Which books / resources would you recommend for an excel newbie, excel intermediate user and excel pro?
- Is there a point in learning excel, in the era of web spreadsheets and powerful drag-and-drop analytical apps?
But, the interview is not about what I want to know, it is about what you would like to know and learn from the maestro.
So please chip in and give me your suggestions for this interview. Go!
Also, check out our earlier interview with Charley Kyd to get some inspiration.
28 Responses
Q for John Walkenbach:
Excel is great – we would all agree – but what is the worst use of Excel you have seen?
Thanks.
Thanks for this Chandoo…thoroughly enjoyed your previous interview, especially relating to Excel 2003 vs. 2007.
Question:
> What changes would you like to see moving forward for Excel?
Question #1. On Data/validation why is there a limit on the number of items that can be on a drop down box? ( or is there a way around this? )
Question#2 How can I display a Picture as an image on a selection from a drop down box?
Why do the PUP dialog boxes have the order of the OK & Cancel buttons reversed? Does Bill like it this way? %^)
How can I use spreadsheets to Write better emails. Make more moneys.?
Question for John:
Are you still cold-brewing coffee? If not, how come?
– giorgio
John,
Do you really use a countdown timer in Excel to cook bacon ?
Hi John,
some questions related to my Sparklines project :
– When trying to display a simple dot using shapes, I only found the “msoval” object to do this, but with poor results… is there a hidden “dot” object in VBA ?
– shapes again : circles are displayed on screen but ovals are printed, how can it be solved ?
– is there a way to add tooltips to shapes (when mouseover for example ?) ?
Thanks already for sharing your knowledge.
Since much of John’s work is related to teaching others how to use Excel and VBA, and since his coding style is so direct and clear, I have long been curious to know how John learned programming and Excel, and also how he mastered the subject. I would also be curious to know about his approach to writing.
How do you think the Role of the Excel developer will evolve as Microsoft pushes us all into the “Cloud” environment.
Hi John,
With all the Excel-based products and Add-ons on the market, including yours (i.e., PUP), can you give us developers any ideas, methods, or examples for impeding or better, eliminating, piracy of our Excel products? For example, how do you register PUP? How can you prevent use by an unregistered individual? Since Excel can address the registry, is this a way to take advantage of this? Anything you can offer on the subject would be appreciated.
$$$
My question for John is: For the Excel lovers out there who have an entrepreneurial spirit, what suggestions would you have for someone who wanted to start a business around their knowledge/passion for Excel?
@All: thank you so much. I will use as many of your questions as possible in the interview. Will post one more comment when I send him the questions. Meanwhile, keep’em coming…
What’s the most amazing, commercial Excel App you’ ve ever seen?
What’s the most amazing, non-commercial Excel App you’ ve ever seen?
John,
the CD for the Excel 2007 VBA book does NOT have the “searchable e-book (pdf)” as quoted. Do you have this up separately. It can be useful as a helpfile for me at work!
thanks
K.
John – would like to know how to have Excel “post” info in cells from a pivot table summary sheet to specific cells in a a “regular” worksheet. This would save a lot of time copying data from one sheet to another. Thanks!!
I would like to know how to increment a cell reference by a fixed amount eg a number
example f4 to f8 (the constant being 4)
@Handel, did you try using OFFSET() function ? http://chandoo.org/wp/2008/11/19/vlookup-match-and-offset-explained-in-plain-english-spreadcheats/
What are you thoughts on and solutions for mapping data with Excel?
What needs to be improved in Excel’s export to web page format?
I seem to have a problem with Excel doing sorts, without having to add a sort order number system to an outside column and then making adjustments as number are added in order to get a proper sort.
Now I have a collection of stamps and I am doing an inventory of what I have. Stamps are generally cataloged by what is known as Scott’s number system and as design or type of printing is changed or faults are found they use the same main number but add letters after it to point out which pricing is thus referred to.
Example Japan
Scott Nu (B) Thru Qty Cat Value
439 1 $35.00
457 1 $110.00
475 a 1 $4.00
509 a 1 $7.25
608 a 1 $21.00
648 651 1 $1.20
668 a 1 $4.50
685 a 1 $5.50
740 a 1 $6.00
773 a 1 $6.00
805 a 1 $4.25
805 1 $0.20
814 2 $0.25
Note the numbers 805 and 805 a, they are backwards. It gets more complex when you may have an a, b, e, and a f. I have the (B) in place so that I can also sort by 1st this then that as second, still does not work. It becomes a pain when you have 300 or 400 numbers. What also needs to be careful of that the prices don’t flip so that the 805 becomes $4.25 vs $.20.
When will we see the introduction of the “banjo” chart in Excel?
How do you write a span of dates in a formula i.e., July 1, 2008 to Sept 30, 2008. I’m trying to divide my spreadsheet by fiscal quarters; but, having no luck at all.
Hi,
I have an unique issue. I have maintained an excel file which shows the monthly house hold expenses.
I have created a tab/ sheet named “ENTRY” which holds the data for daily expenses accounting for smallest of the expenditure. The data runs from Column C thru AG (dates 1-31) and in column AH i have a summation of each row.
The 2nd tab i have is “ABSTRACT” which gets the values from “ENTRY” sheet column AH via a simple link eg: =ENTRY!AH3.
The problem i’m facing now is as follows:
The data in sheet “ENTRY” has dissappeared and hence the summation in colum AH shows blank. However, the “ABSTRACT” sheet continues to show the value of the summation in “ENTRY” sheet column AH. The link exists as is. Another problem with this file is, if i take this file to another desktop/ laptop, the “ABSTRACT” tab shows zero value in the linked cells (which is the right way).
If i have to explain the issue with an example, here it goes.
In row 5 i have the details for grocery spend each day. Cell C5 has 100, D5 200 so on till AG. AH has sum of these and lets assume this comes to 2000. Now, if the data in row 5 gets deleted, sum of the row will be 0. But the “ABSTRACT” sheet continues to show 2000.
I have not deleted the data one single time while working on the file.
Request you to advice if there’s any possibility of getting the data back which has dissapeared suddenly.
Thanks,
Prakash
Hi,
i wanna know in conditional formating or is there any formula in which if there is the text ‘DEL’ in column A the it should colour or highlight the corresponding value in column B. as
A B
1 BOM Early
2 DEL 0:30
3 CCU 0:40
then it should highlight 0:30????
Hi expret
I have excel format contain machine detail of whole year in which there is a collumn of customer name Collun D and i have date in collumn E is start date Collumn F is release date and collumn G is dispatch date.I would like do something like that if i enter start date in cell e color of Customer name cell D chnage to blue if i enter both dates start & release it change color to green and if i enter all three dates in collumn E,Fand G then cstomer name cell D change color to yellow
can u help me please?
Hi,
I want to create a macro to insert certain column
fo eg. If in column A2 value is 5 means i want next 4 rows insert automatically with the si no on B2, as below
No of Pages
SI No
5
1
2
3
4
5
Can you help me please?
Mr. Walkenbach,
I bought your book Excel VBA for Dummies, 2nd edition. There is no way to access the sample files. The URL in the book (www.dummies.com/go/excelvbaprogrammingfd2e) does not work. I would appreciate it if you would send me either the correct url or the actual sample files (to my email). I am using Excel 2010.
Thank you.
Chick