Oz du Soleil
Data Management and Excel Specialist. Your Data Marshall!
@EscobarMiguel90 Ok! Your book is advertised in the back of my book! COOL! It's on my list of books to grab when it's released.
Power Query is wonderful. I've used it to append documents, and set up a system where it's pointed to a folder full of timesheets. When new timesheets are added, Excel/PowerQuery just needs to be refreshed to integrate the new data. No need to even open the new documents.
I haven't done much with Power Pivot. There just doesn't seem to be a need for it in the work I do. But when there's 500 rows of complicated data, Power Query and data models have become my buddies.
6 days, 23 hours ago on Comparing R and Excel Makes No Darn Sense
@EscobarMiguel90 thanks for jumping in. I'd love to see you do a blogpost offering something balanced because you're familiar with the tools, and sound like you're discerning in when to use what. I welcome a lesson on that.
You're right that the internet is itself part of the problem. I don't think that Isaac and @FFAnalyticsNet were purposefully malicious. I do think that because the internet is so wide open, we should be careful in how we communicate.
1. I've been looking for a tool or trying to figure out what coding language would suit my needs best. Research gets difficult when a person has to weed through all the fanboi articles articles and sales pitches.
2. We can be encouraging but also should be responsible. I would rather see someone get their work done with a "good enough" tool than have them distracted with the false possibility that their existing tools are insufficient.
When is your M book coming out? I'm getting more into Power Query and M is a language I want to know. Also, you should write a blogpost that addresses DAX. You're more qualified than I. :)
1 week ago on Comparing R and Excel Makes No Darn Sense
@Jeff Weir thanks so so very much fro dropping by. I agree with you. In 15 years of working with Excel I've never had a situation where I had to say "no" this can't be done in Excel. But there was ONE project that I joined and it had been built extremely poorly and the client wasn't open to an overhaul. My partner and I eventually just had to pull out of the project.
That is an example of another point you make: because someone lives in Excel, they aren't necessarily living well. Whomever built that aforementioned spreadsheet wasn't living well. And I love that point because the next question to ask is: how can we expose people to opportunities to be more efficient and build better spreadsheets?
When a person has their job and they're the resident Excel magistrate, they need to look up and outside of their territory to see what's out there as far as skills and techniques.
1 week, 2 days ago on R, Excel and Data in the PDX
@JordanGoldmeier Time to pull out the big megaphone and spread the word.
3 weeks, 1 day ago on 5 Reasons For Building Apps in Excel
Excellent blogpost. As @juliacsmith says: you do you. Marriage and parenthood aren't one-size-fits-all propositions. Fortunately you're in a situation in life where you can choose.
I think back to high school frolics and am thankful not to have become someone's father. Then, last August during my drive from Chicago to Portland, I felt so blessed to have had the opportunity to have made my own decision to not only move, but to drive and make it a 16-day road trip.
Like the guy in the Geico commercial says to the lady as he prepares to ride off on his horse: "I'm a loner, and a loner's gotta be alone." LOL!
It's best to know such things about ourselves and govern ourselves accordingly.
1 month ago on How a lovely relationship has closed my womb
@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.
1 month, 2 weeks 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?
2 months 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.
2 months, 1 week 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.
2 months, 3 weeks 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.
3 months 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?
3 months, 1 week 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.
4 months, 2 weeks 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 months, 2 weeks 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.
5 months, 4 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.
6 months 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.
6 months, 1 week 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:
6 months, 3 weeks ago on Shop FAB: Reinvent Your Work Wardrobe with Unique Accesories
Keidra, @kdc it'd be interesting to get your perspective on this.
6 months, 4 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!
9 months 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.
10 months, 3 weeks 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.
11 months 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.
11 months 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.
1 year 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/
1 year 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.
1 year 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.
1 year 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.
1 year 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.
@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'? :-)
1 year 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/
1 year, 1 month 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, 1 month 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, 2 months 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, 3 months 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, 3 months 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, 3 months ago on Steve Harvey, Expertise & Experience