Oz du Soleil
Data Management and Excel Specialist. Your Data Marshall!
@sayahillman @billstreeter Saya, I agree. It's all relative. There isn't any universal truth about any city being universally "bad." It depends on what a person wants in their own life. For 19 years I was happy in Chicago--snow and all. My decision to move to Portland wasn't a Chicago-Bad, Portland-Good dichotomy. It really was a decision about what I wanted and where I could best find it.
5 days, 4 hours ago on Words I never thought I’d say: is it time for me to leave Chicago?
@Zack Barresse Thanks for the reply and I appreciate the perspective from not only the employer side but also deeper insight into the reality that a lot of companies aren't clear on what they need.
Sure, I did only suggest another form of testing. Realizing that some kind of testing needs to happen, I figured a more pragmatic approach.
And this whole expert-thing and guru-thing ... LOL! That kind of talk needs to go away.
In a situation where everyone should have learned the same things; e.g., a college class, then, a one-size-fits-all test makes sense. But with a professional skill, I don't see how a standardized test can help. At least, in my experience, when it's programmed to nudge a person in certain directions and take specific features away, those exams are like the uncle who comes over and invites the kids to pull his finger.
How have the standardized tests been helpful in your experience?
3 weeks, 2 days ago on REDUX: Interviewing for Excel Skill
@Jeff Weir that's a smart solution. I'd never thought to just go ahead and include work samples. Very smart. It's a proactive approach that can cut through to whatever needs to be done. Show, don't tell, and don't take those darned automated exams. LOL!
I took one of those tests and felt both embarrassed and enraged. As you describe, sometimes the actual question wasn't clear. Also, the test seemed to be rooted in a particular work style. The experience was more like a dirty prank than a forthright skills assessment.
Your response to the situation is really good.
3 weeks, 3 days ago on REDUX: Interviewing for Excel Skill
@Jeff Weir that's an interesting addition to the conversation. No one is immune. I guess we've gotta help each other remember the task.
1 month, 1 week ago on The Anti-VLOOKUP Crowd Is Out In The Streets Again!
@Zack Barresse Yes! you paint more context around the discussion and the answer to "which is better or worse is "it depends."
That VLOOKUP "a-ha!" moment for beginners is a big deal. They deserve that. And you bring up another interesting point: building a spreadsheet for someone else vs. a one-time comparison of lists. So many things have to be considered in our tasks and it's good to know that Excel provides so many tools.
@Chris Macro Thanks for the comment and sharing your blogpost.
You raise a very interesting perspective: people who need to be able to follow what you did in a spreadsheet. I was surprised a few years ago when I started meeting people who don't develop spreadsheets but have to understand spreadsheets that are given to them.
It's courteous to consider those folks during our development.
VLOOKUP always seems to be a significant threshold function that Excel users cross. Even if they're doing it paint-by-numbers style it's new territory until they do get to INDEX/MATCH and more complex techniques.
@Sumit Bansal No no. You're no monster. LOL! I appreciate what you do. I'm glad you wrote this article and generated the lively comments at the end of your blogpost. We needed this.
@kapler thanks for dropping by and commenting! You taught us something. I wasn't aware of the onEdit bug with Google Spreadsheet, and I appreciate you pointing out the data array workaround and how it's not sustainable. For someone who's really managing data quality with any level of complexity, you're describing a real horror.
1 month, 3 weeks ago on The Pinocchio Theory & 3 Basics Where Excel Whoops Google
@maximemanuel Thanks for dropping by and commenting.
Do you spend time playing with Excel?
Can you add any other ides for people who want to learn Excel but don't have immediate use?
1 month, 4 weeks ago on 5 Suggestions for: “HELP! Teach Me Excel”
@Rick Grantham Excellent points and examples. They are more evidence that the mop and broom need to come out of the closet and be discussed in ways that empowers people to deal with being janitors. For people whoa aren't formally trained, this stuff is a real shocker.
And I'm glad that you bring up the idea of data that's an amalgamation from multiple sources. That's certainly not going to go away, and that's some of the most powerful juice when you can handle the PITA that it is to compile and turn into something useful.
3 months ago on Getting to the Sexy Data
@Rick Grantham Thank YOU for being on of the ones who have it ... helping keep this world's data clean.
3 months ago on What Made My Trainee Cry?
@maximemanuel @OzData brother! LOL! Sometimes good solutions are ugly ugly ugly. "Amazing" is nice when you can get it.
I look forward to seeing what you do with arrays.
4 months, 2 weeks ago on Array Formula Fun: Calculate the Closest Due Date
@maximemanuel thanks for the comment. I'm glad this offers some insight. That invisible data extraction piece has been the crazy part ... and the most powerful part. Now it's your turn! LOL! Let's see your array formula video or blogpost.
4 months, 3 weeks ago on Array Formula Fun: Calculate the Closest Due Date
@Mike Alexander that's hilarious! I actually lost a little weight. Staying in, skipping fancy dinners and barbecues ... it was probably good for me.
4 months, 4 weeks ago on Guerrilla Data Analysis Has Been Re-Written!
Hey hey hey!!!! That's a pre-tied bowtie! I'm throwing the penalty flag! After 12 years old, a fellow should tie his own bowtie. Pre-tied bowties and green bell peppers are 2 things that just need to stop.
However, I tip my hat to you for highlighting the bowtie. MORE (self-tied) BOWTIES.
Here: I show how to tie a botwie:
5 months, 1 week ago on Shop FAB: Reinvent Your Work Wardrobe with Unique Accesories
Keidra, @kdc it'd be interesting to get your perspective on this.
5 months, 2 weeks ago on What Happened to the Conversation of Twitter?
I was thinking about this recently. There are folks whom I miss from tweeting during television shows--particularly, Sweet Genius and Chopped.
Sweet Genius has been canceled, and I've grown tired of the format on Chopped. We had good conversations and a few of those relationships have moved over to Facebook where it's possible to have more extended conversations without all the clutter that's on Twitter. And Twitter moves so doggone fast, I recall trying to start conversations with people, and didn't know if their lack of response was due to disinterest or just missing my tweets.
Also, I've had to get some perspective on this whole social media thing. For a while I was doing way too much and should probably consider deactivating dormant accounts.
So, yes. In my own social media life, my Twitter usage has also taken a nosedive.
@maximemanuel thank YOU for being part of the commitment to keep data safe!
7 months, 3 weeks ago on IgniteChicago: The Power of Data
@BEyond_INSOFE Thanks for dropping by! I'm glad you agree. I never thought to add interactivity to the discussion but you're right. Especially with IFERROR, we don't have errors disrupt our interactivity if we can plan for errors that really aren't errors.
9 months, 1 week ago on Top 5 Excel Functions
@awolfe57 Andy, thanks for the comments.
VBA does take us into a whole different world, as I described in "6 Reasons Not To Use VBA" http://datascopic.net/5-against-vba/
Where were you when everyone was beating on me for that? LOL!
I use VBA as a last resort for the reasons you describe. However, more and more over the past few years VBA has been a regular part of my projects. People are trying to free themselves of reliance on IT and they start creating their own desktop solutions in Excel. Now, the problem is that many of them are still beholden to their companies to get them upgraded from 2003 and 2007 so that they can use things like PowerPivot (2010), Data Models (2013), etc.
I'm also with you, in that, I love to teach and empower. VBA has a vast learning curve and is really only for the folks who are going to be using it a lot.
9 months, 2 weeks ago on 3 Reasons To Use VBA: It Tames Beasts!
@CHatmaker Thanks so much for jumping into the conversation. I haven't heard form you in a while.
You see that even I agreed with RStadther--except for coming onto someone else's turf and calling something "dumb."
Referring back to the "or be careful" piece in the blogpost title, I think we all agree with each other. There are a lot of reasons to be careful about using VBA. In no way do I suggest VBA is the devil.
#3 is a warning because there's no UNDO. Yup. We're only human. And every time I get ready to test some VBA I get everything set up in case the VBA code is wrong, as a last resort, I can close the document, "do not save", reopen, and be back where I was before running the wonky code.
As you, I and RStadther agree, there are ways of dealing with all the #2 thru #5 things that I mention. There is "responsible coding." And there's a helluva learning curve to get there.
With #1. I fully agree that we shouldn't refuse to do something just because someone might leave. My warning is that freelance VBA coders don't grow on trees. I've seen people whose spreadsheets have been broken for years because the VBA coder is long gone, something broke, and the spreadsheet skill kinda works but not like it did. And there are 2 problems:
1. Finding another VBA coder who can take on the project of fixing someone else's code. Most of the VBA coders whom I know have day jobs and aren't looking for weekend work.
2. Coders all write in their own different styles (not only in VBA, but any kind of code: CSS, Java, Python, etc.). So, a developer following behind another developer means following someone else's style, or re-writing in one's own style. It's not the end of the world, but it's an added challenge, and unnecessary grief if a native solution was possible.
Reading someone else's formulas is one thing. But it can be torture tracing through someone else's code, their error-handling methods, their way of writing loops, their non-existent comments, etc.
Still, I agree with you 100%: if native XL can't do the work, definitely go the VBA route. New VBA developers, need to just be careful, though. And I think that some VBA developers overuse VBA and make things unnecessarily hard for users.
9 months, 3 weeks ago on 6 Reasons Not To Use Excel VBA (Macros)
@Rick Grantham Thanks for commenting.
Rob's comment strikes a nerve with me, as well. For me, validation and user-interface aren't addressed enough. Those are things that I look for in "killer spreadsheets." Beyond functionality, are they easy to break?
What's the in-print book that you refer to?
I'm looking forward to the Dashboards book by @Option_Explicit. It should be out in a few months.
10 months, 3 weeks ago on Where are Excel & Google Spreadsheet Headed? Part 3 of 6
@ejazahmed1989 @OzData My man! Yes yes yes! You're talking my language. Sounds like you're pretty good with Excel, indeed.
I'm planning a blogpost where I show how to use INDIRECT so that you can pull data from whatever table you want, on the fly, with dependent dropdown lists.
Hey! I know who you are. I was at your blog earlier today.
Good material there. Keep going with it! It looks like you've just been at it since November 2013. I added you to my list of Badass Bloggers: http://datascopic.net/badass-bloggers/
11 months ago on #2 of the Top 5 Excel Functions: VLOOKUP
@ejazahmed1989 what's your experience with Excel? Do you use INDEX/MATCH?
I do appreciate you dropping by and commenting.
@ejazahmed1989 OH LORD! I'm very aware of that conversation.
There are definite benefits for using INDEX/MATCH, and I've used it. This comes down to using the right tool at the right time. In a lot of instances VLOOKUP works. And especially for people who didn't even know that VLOOKUP existed, it explodes their minds when they realize they've spent years manually comparing lists and shaping data. VLOOKUP is a turning point.
INDEX/MATCH is a next level of expertise. It's 2 functions, one nested in the other, both with their own syntax. For beginners, I say, let's first learn VLOOKUP because it does work. It might mean rearranging some columns because VLOOKUP only works left-to-right, but we can do it. If we can't move the columns, it's time to jump into INDEX/MATCH.
@Mike Rudzinski Very bold statements, Mike.
I think you set up a very specific context to make your case. Some corporations may have effectively killed off spreadsheets. But every time one of these Excel-killers (e.g., SalesForce) is touted we have to have a conversation about For Whom, How and Why?
SalesForce is outstanding on the front-end. However, the back-end is pure hell to configure and manage. It's like comparing a helicopter to a Toyota Camry. The helicopter may be "better" as in "quicker." But who's gonna get licensed to fly it? Who's gonna maintain it? Where is it going to be parked? When there's an emergency to go somewhere, how long do we wait for the licensed pilot?
Is that anti-helicopter, anti-SalesForce or an apologia for spreadsheets? Hardly. It's a statement that not every entity is in a position to take advantage of something that we may agree would generate the best output and highest quality. It may cost too much in terms of just getting the darned thing off the ground.
Hey, if you have the wherewithal to ditch spreadsheets completely, more power to you. There are plenty of folks who've bragged to me that they manage data and haven't used a spreadsheet in 5 or 10 years. One person said, "When things get too complicated, I just program something in C++." I'm not mad at him. Good for him. But for every 1 of him, there's probably 50 people running a small to medium-sized business on spreadsheets.
Being a freelancer has put me in touch with business' concerns of:
1. Can we afford it?
2. Can we be without person X while they go train?
3. Can we risk trying something out and discover that it doesn't work for our needs?
4. Can we survive the upheaval of integrating something new?
5. Will people use the solution once it's been launched?
At $90 most businesses can get Excel working good enough and if someone needs support, they've got a friend, an aunt, 3 cousins, a co-worker, or an online forum for help.
11 months, 1 week ago on Where are Excel & Google Spreadsheet Headed? Part 2 of 6
@JordanGoldmeier Point taken, and I do pay $7/month for ProPlus. (And that's another frustration with Excel with their endless packages of products. In another blogpost I describe the week-long hassle it was to upgrade from Home Premiun to ProPlus.)
The free version of Tableau is on a public server. A Tableau rep was at a hackathon that I participated in and he told us that the public server is open for anyone to search and see your data. I saw Tableau and was blown away by it. I had a particular customer in mind. But, oh ... I don't think he'd like a public dashboard of his quotas, performance, commissions, customers' addresses, etc.
Tableau was great for the hackathon because the research and results were all public. Tableau is a great tool, generally. But I think this get us back to questions about For Whom? and For What?
Still you're 100% right to put Tableau into the mix because it's an app that takes a common spreadsheet use and does it better.
11 months, 1 week ago on Where are Excel & Google Spreadsheet Headed? Part 5 of 6
@xszil @Zack Barresse @OzData @amg49 ExcelWebApp was a huge addition. Mac users and users of ancient versions of Excel can have access to things like slicers and timelines ... as long as someone with Excel 2010 or 2013 does the initial development.
I'm loving ExcelWebApp.
The fact that you don't feel an urgency to load VBA on the web is a theme that's coming through to me in these interviews: our needs are so so so vastly different. After 15 years of working with Excel, I've rarely touched databases. My clients want apps and dashboards. Every time I go to teach myself databases, the phone rings and it's someone wanting a web-scraper or an inventory system.
It's fascinating and beautiful that our community has such a wide range of needs and skills.
@xszil @OzData @MrExcel Yeah! @MrExcel with a monster afro like Sugarfoot. That'd bring some "cool" to Excel.
11 months, 2 weeks ago on Where are Excel & Google Spreadsheet Headed? Part 1 of 6
@davidhinckley It'd be good to hear what @MrExcel would say about that directly. I've done very little with Apps Script and gadgets but I can't speak with any kind of authority. What I did discover was some basic things that I could do in Excel very easily but would have to program in Google spreadsheet.
One example was cell validation where F5 cannot be greater than B5. Easy to do in Excel. At the time it wasn't part of native GS. So, I left it alone.
11 months, 2 weeks ago on Where are Excel & Google Spreadsheet Headed? Part 2 of 6
@Zack Barresse @OzData @amg49
The on-the-fly updating is where I kinda understand the subscription model. It's a hassle but I liked waking up one morning and suddenly there were dropdown lists available in ExcelWebApp. One alternative was an HTML form hooked into a MySQL database via PHP. YUK!
So, it's nice to have the updates happen when they happen, as opposed to the major-release rigmarole that you describe.
@Zack Barresse Thanks for commenting.
I like how you describe Excel as an app. I detail that more in Part 3 http://datascopic.net/excel-and-google-spreadsheet-3/
But I wonder what "web spreadsheets" would look like. There are some that claim to be web-based Excel-killers but they're woefully weak and/or expensive.
ExcelWebApp is hugely promising in that regard. They've added the droplists and other validation features, and slicers are available, too. Do you think VBA will translate over, as @amg49 questions? Can it?
@amg49 Amy, thanks for commenting. This conversation has exploded in a lot of directions.
About 30 minutes from now part 3 will be posted and Rob Collie of @powerpivotpro mentions PowerPivot. So, you're right on.
@sgeren can you say more about the "socialization" of data? That sounds interesting.
@gregjkram Thanks Greg. One thing about Excel users is that we do have a community. All the contributors to the blogpost are way smarter than I am, maybe I can make up for it by facilitating more community. :-)
@MrExcel that'd definitely add some heat to your book covers. LOL!
@gregjkram thanks for dropping by and commenting.
@kdc care to clarify 'web analytics boo'? :-)
11 months, 2 weeks ago on Three Things I Learned About Digital Analytics From Teaching About Digital Analytics
Glad to meet you here again, @suzemuse!
My words for 2014: Vision, Choose! and Immodesty.
And here's the explanation: http://datascopic.net/2014-3-words/
11 months, 3 weeks ago on My Three Words for 2014
@sgeren thanks for commenting, fellow information evangelist. I didn't know you also teach martial arts.
Yes! 1:1 has had great results because we can get into nuances that don't make sense in large classes. That 30/30/30 rule isn't one I've heard of but it makes sense. Specialized classes tend to be smaller, and I wonder if the 30/30/30 still holds or gets any better.
1 year ago on 4 Insights From My Excel Students
@gcoulto Very good input!
I'm taking an improv course partly because it's a routine activity that gets me out and interacting with people. It can be spooky when you've been indoors for several days, then go out and get reminded that there's a whole world out there.
1 year, 1 month ago on 3 Tips for Solopreneurs
I'm learning that my strengths and weaknesses have to be really clear and that I can't physically do everything even if I had no weaknesses. Some things have to be outsourced. Some things have to be "good enough." Otherwise, you get stretched too thin:
Prospecting, selling, delivering the product, networking, blogging, acting as webmaster, trying to collect on unpaid invoices, driving around, sending out invoices, keeping on top of what's new in your industry, ... it's a lot for one person to do alone, effectively.
Thanks for commenting! I don't know how I missed the alert. :-( Any way ...
Brother, let's help get the word out about Excel and that it is far more powerful than is known. It can make a lot of lives easier when it's used well, and it doesn't take much.
1 year, 1 month ago on 5 Reasons For Building Apps in Excel
@Rob Collie thanks for jumping in!
Maaaaan! You hit 'em all: unorthodox, creative, elegant, able to play multiple positions and a heart that refuses to lose.
Thanks for what you're doing at http://www.powerpivotpro.com. We'll get the message out that Excel is an All-Star Team. I'm glad you opened the conversation about a professional title because that'll help get not only the public, but also Excel developers thinking beyond the tool. In the comments section people are already wrestling with "so, let's define what we do and what we add." Lets see where it goes.
BTW, I'm looking forward to a PowerPivot Group starting here in Chicago. Any updates on that?
1 year, 1 month ago on Excel: The Dennis Rodman Of Business Intelligence Tools
@Rick Grantham thanks for commenting. I really appreciate you dropping by.
I think the quote came from Rob Collie @powerpivotpro. He should take credit. :-)
YES! ISG, you're getting even deeper into the concern that motivated this blogpost.
There's a responsibility to be upfront about what can be taught, by whom, to whom and at what level of expertise. In your point #2 about solutions not being as easy as imagined, that's really key, and this is when experts also need to be consultants. I think "prerequisites" is the word. There could be a lot more stated about prerequisites, and less "everybody pile in."
And yes, that's part of my business philosophy. I want to provide solutions that will stick. Aside from teaching, it's also meant turning down gigs when I've noticed a potential client has a challenge with processes and not with software.
1 year, 2 months ago on Steve Harvey, Expertise & Experience
@RamonAndrews you are so welcome! And thanks again to @XLCalibre because this remains a popular article.
1 year, 2 months ago on The Seven Deadly Sins of Data Entry (or how NOT to use Excel)
Great examples and I agree 100% that we need more data-savvy people. And Excel is a fantastic tool because it doesn't have a steep learning curve like C++, PHP or R.
The question you ask allows me to make a distinction that I now see isn't clear.
In my example I wanted to highlight specifically data-scrubbing, as opposed to teaching something like pivot tables or IF statements. I do teach a pivot tables workshop. However, data-scrubbing isn't for someone who doesn't regularly use Excel. And that's what I'm trying to get at. When a person does sales (or something else) in 80% of their professional life, can I teach basic pivot tables ... yes. Let's do that. Teach them to scrub 2000 rows of sales and inventory data? No. Not for someone who spends 10 hours/month in Excel and wants to learn in a single 2-hr workshop.
I'm thinking expertise vs. "good enough" and setting responsible expectations.
But even larger than Excel, I'm concerned about a lot of workshops, membership sites and other products where someone is pushing "buy this and you can do what I do." I'm calling BS because years of contextual experience can't be taught. A person really needs a mentor--like you describe as the people who were patient with you (and me) and helped us learn along the way.
@iSalesGirl thanks so so much for chiming in and thanks for your interest and support.
Your diligence warning adds another dimension. Some so-called experts are people who may know a lot but haven't proven successful. I was at a camp for bass players and we were warned: DO NOT take bass lessons from a bassist who doesn't have a gig. Again, that speaks to the context and not the how-to.
The bassist may have mind-blowing skill but ... they never think they're good enough to play publicly or, they have a righteous attitude and have proven to be lousy bandmates. Mind-blowing skill inside a warped context is a disaster waiting to happen.
In that 10,000-hours guideline there's room for lots and lots of contextual, real-life experience. If the so-called expert isn't there, skip it. If the learner is interested in expertise and doesn't have 10,000 hours, let it go.
@DaliaTendziagolskyte @OzData @MrExcel No problem Dalia!
Keep in touch and let us know if we can be of help again.
Question: how do you use Excel? What's your role?
1 year, 2 months ago on Excel Interactive View 2: Sample Data
@MrExcel @DaliaTendziagolskyte Dalia, thanks for the question. Mr Excel, thanks for jumping in and responding. Dalia didn't have to wait for me to wake up and eat brreakfast. :-)