Oz du Soleil
Data Management and Excel Specialist. Your Data Marshall!
@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.
4 days, 12 hours 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.
4 days, 16 hours 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.
1 month, 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.
@Mike Alexander that's hilarious! I actually lost a little weight. Staying in, skipping fancy dinners and barbecues ... it was probably good for me.
1 month, 3 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:
2 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.
2 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!
4 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.
6 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.
6 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.
6 months, 2 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.
7 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/
8 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.
8 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.
8 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.
8 months, 1 week 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.
@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.
8 months, 2 weeks ago on Where are Excel & Google Spreadsheet Headed? Part 1 of 6
@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'? :-)
8 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/
8 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.
9 months, 2 weeks 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.
10 months, 2 weeks 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.
10 months, 3 weeks 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?
10 months, 3 weeks 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.
11 months, 1 week ago on Steve Harvey, Expertise & Experience
@RamonAndrews you are so welcome! And thanks again to @XLCalibre because this remains a popular article.
11 months, 1 week 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?
11 months, 3 weeks 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. :-)
@DaliaTendziagolskyte You got a special treat. @MrExcelArticles replied to your question via PodCast
Check it out. Hopefully it answers your question. Let us know. And thanks so much for dropping by and asking the question.
Mr Excel, thanks for jumping in!
11 months, 3 weeks ago on Excel Interactive View 1: Analyze data in a webpage
You got me looking, Suze! One of my best posts came from one of your suggestions. I'm looking for where I can go deeper and tell a story. I've got a blogpost percolating in my mind; let's see if I can present is as a story.
1 year ago on How to Find Your Stories
There's the other side of it, too. I've given big praises to companies and they've gone ignored. One of them seems to have a FB fan page that's just sitting there unmonitored, no updates from the company. I didn't take it personally when my praise went unresponded to but I DID delete my comment.
The second company is active on their fan page and my review was completely skipped over. Even a "like" would have been sufficient. Again, I deleted my comment.
I've gotten conscious of the balance of praise and complaints I put out into the world, and have made efforts to give praise when something has gone right. Can't the company just click the Like button and move on?
1 year ago on What’s the Big Deal About Online Reputation?
I'm one of these people who can have up to 30 browser windows open. A lot of them are articles that I intend to get back to. I don't want them to fall of my radar and disappear back into obscurity.
But, things get to a point where there's too much confusion. Then I take 10 minutes to close it all down:
- Write down the few items that I know I'll REALLY get back to, and let the rest go. As you say, if it's really important, it'll show up again at another time and place.
1 year ago on Zen and the Art of Not Trying Too Hard
Mailstrom is an excellent tool for helping clear out unread emails by either deleting them or archiving them. And it can even unsubscribe you from certain newsletters if you want to unsubscribe. It's beautiful!
One cool thing I discovered. Mailstrom works by providing an easy interface where you can group emails by Sender. This helped me see who blogs sporadically, infrequently or seems to have abandoned their blog. Sadly and surprisingly, there are a lot of bloggers in those categories. Some of them have/had excellent content on the rare occasions that they produced something.
1 year, 1 month ago on 5 Ways to Get Control of Your Email Today
Here's the trick with those shows: look at the commercials.
The commercials tell you the demographic that the show is targeted for. I used to get upset by a lot of television shows and then noticed the commercials: PayDay Loans, slip-&-fall lawyers, cheap auto insurance, "get debt collectors to stop calling you."
The show wasn't meant for me or general consumption. You're not going to see commercials for luxury cruises or New Zealand vacations. The targeted viewers don't have any money. LOL!
Thus, these shows are disappointing in other ways. Or, they point to a harsh reality: there are people whose lives survive with minimum auto coverage, and borrowing $200 at 700% interest rates. It's sad. And they're in enough numbers to be a legitimate demographics for television advertisers.
1 year, 1 month ago on Mainstream Media for Dummies
@hirandesilva please share your thoughts? You're an Excel visionary. How would you reply to Samii?
1 year, 1 month ago on 6 Reasons Not To Use Excel VBA (Macros)
And I think @Rob Collie (http://www.powerpivotpro.com/) is one who sees Visual Studio as a direction we should be headed.
With Bruce, JS is already in use in efforts to truly integrate Excel with the web. ExcelWebApp is an excellent start from Microsoft but it's still lacking. My point being, JS isn't being thought of from a worry that VBA is going away but as a way to "liberate Excel from the desktop" because so much is web-based. VBA or no VBA, the fear is having your work/apps isolated on your own little desktop/laptop island.
Is VBA going away? There have been warnings about that for a while. In fact, Excel for Mac 2008 didn't have VBA and people were pissed. So, it was returned in Excel for Mac 2011.
So, what do you do with Excel/VBA? How'd you get into this?
Samii Samii Samii, I didn't mean to hurt your feelings. And I think you're quite accurate. With 6 reasons to be cautious about VBA, there are indeed billions of reasons to love it. I regularly use VBA just to clear forms so that the user can start fresh. VBA can definitely be a time saver. Actually, more people should be experienced in VBA.
The blogpost was just a caution. Just some things to think about before we open that VBA editor. Excel power users are a small bunch. VBA developers are even a smaller crowd. I've seen pedestrian users in bad situations because the were given a VBA solution that either stopped working or needed to be modified, and the developer was long gone.
Sometimes the solution could have been non-VBA, sometimes not. I just want us to be cautious.