Become a Creator today!Start creating today - Share your story with the world!
Start for free
00:00:00
00:00:01
PolicyViz Podcast Episode #10: Dave Bruns
137 Plays10 years ago
When it comes to creating visualizations with your data, it’s not the tool that “makes” things, it’s the user. I’ve heard lots of people say that Excel makes terrible data visualizations. It’s true that 3D cones and 3D exploding pie...
The post PolicyViz Podcast Episode #10: Dave Bruns appeared first on PolicyViz.
Recommended
Transcript
Introduction and Guest Welcome
00:00:11
Speaker
This is a policy of his podcast. I'm your host, John Schwabisch. Welcome to the episode. I'm really happy to have with me today, Dave Bruns from exceljet.net. Dave, how are you? I'm good. Thank you, John. Thanks for coming on the show. I'm excited. We're going to pivot a little bit in this, no pun intended, because we are going to talk about pivot tables. But we're going to pivot on this episode
Dave's Journey to Exceljet
00:00:31
Speaker
a little bit. I've been talking to folks who are really good with color with Rob Simmons, doing some interesting work on algorithms with Nick Diakopoulos from the University of Maryland.
00:00:40
Speaker
teaching data visualization with Scott Murray and so we're moving ahead with some teaching topics here because Dave
00:00:48
Speaker
And his site is doing some really interesting work with teaching Excel, with lots of different videos on the site, all these sort of bite-sized videos that you can use to learn all about the tool and the software. So Dave, I wanted to ask you to start.
Exceljet Resources Overview
00:01:05
Speaker
Maybe talk about how you got into this, how you started this site, because I know this isn't, you sort of took a different route to sort of get to this place.
00:01:14
Speaker
Yeah, so I was a manager for a long time kind of in the corporate world and the last company I worked at did online training and I just wanted to, and I knew a lot about Excel and I had built a bunch of websites and so I did that as a hobby.
00:01:33
Speaker
So I wanted to try to put that together and build a site, a resource site for Excel that had both training but also just resources like shortcuts and formulas and functions and so on. And really I kind of got into it naively. I was kind of
00:01:50
Speaker
You know to turn 50 and I was like thinking what's next having a midlife crisis and it seemed like something that would be fun to do and You know, and so I just kind of went for it naively and Been at it now for about two and a half years
Favorite Excel Keyboard Shortcuts
00:02:07
Speaker
Yeah, it's great. And so you've got a bunch of different products on the site. You've got your small videos. You've got the longer videos that are sort of these classes that people can take. And then, of course, you have the Excel shortcut PDF and the sort of laminated card. And that was my first introduction. I was actually out for coffee with a friend, and she had just gotten a Mac. And I said, have you seen this Excel shortcut card? And she said, oh, that's Dave's.
00:02:30
Speaker
And so that was our first introduction. The shortcut card is great. So let me start by asking you then, what is your favorite Excel keyboard shortcut?
00:02:41
Speaker
Well, there's so many, of course, it's hard to know. I like Control Enter. It's something that's a little unusual, but you can use it to enter the same value into multiple cells. And you can do that with a value or with a formula. I also like the shortcut F9. If you don't know it, it allows you to select anything in a formula and click F9 on a Mac. It's a function F9.
00:03:07
Speaker
It will it will solve that part of the formula for you. So I use that constantly when I'm trying to debug a formula or figure out why it's not working. So I'd recommend that people don't use that. They look into that one. That's a good one. My favorite is control one or command one on a Mac gives you into the formatting menu of anything.
00:03:25
Speaker
numbers, charts. That's my favorite. So if Yeah, that's the nice thing about control one, two is you don't have to worry about what the rip what state the ribbons in right? Yeah, beyond any tab, right. But you can always get to the same format and controls without even thinking about it. Not even thinking about it. Yeah, it's great.
Creating Excel Instructional Videos
00:03:41
Speaker
So let's talk about before we sort of get into other sorts of excel shortcuts and skills that people should know I want to talk I want to ask you a little bit about your process of creating these videos because you've got I mean dozens of these of these videos they're all like three to four minutes and then of course the longer ones and they're all really.
00:04:03
Speaker
You're really enthusiastic in them, and you get these nice bite-sized pieces of Excel tips that you can use, and I use them all the time. But I want to talk about your process for doing that. Do you storyboard the talk? Do you then record and then narrate while you're doing it? Or do you narrate after you're done recording the screenshot? And what are the tools that you use? Do you use WebEx? Do you use QuickTime? What's your process all the way through?
00:04:28
Speaker
Sure, so I've tried a couple of different things and I do some longer format videos, you know, like say nine, ten minutes where I'll walk through three examples or six examples or twelve examples of something and I do those real time with, you know, where I practice a bunch of times ahead of time and if they seem like they're too long, a lot of times I'll come back and
00:04:51
Speaker
and speed up certain sections where I'm just doing something repetitive, going into dialogues and doing the same thing that I just showed you a few seconds ago. And I use for that, I use ScreenFlow on a Mac. I record everything, almost everything.
00:05:04
Speaker
using actually on the Windows side, so I actually run VMware on the same Mac, and I run Windows 7 with Excel 2010. And so if I do a real-time thing, I record it using ScreenFlow with the built-in recording that's there, and I use a nice mic on a mic stand, or on a mic arm, so I don't have it on my desk, so you don't hear any hum or vibration or anything like that.
00:05:27
Speaker
And then I do most of the videos for actual course instruction and I think we have now right about 300 total. I think of that 300 maybe there's like 50 or 60 that are public. Just open to anybody and the rest of them are in courses that we sell. But like we're right about 300 and for that process
00:05:48
Speaker
My normal process is to outline the course in sections and try to name the videos that I want to create in some way that I understand and that I hope somebody else would understand. And then stick to one topic in each video. So generally speaking, try to keep that to three minutes. And I found that for me personally, I can voice probably
00:06:10
Speaker
uh... you know three hundred or four hundred words and around three hundred around three minutes and if it gets up above four hundred words too much and it's not going to be will definitely be more than three minutes three minutes is nothing magic i just have this idea that it's around then that people start to lose their focus sometimes if it goes on too long so i record everything in advance and i use i just use actually in that case i use audacity which is a free application
00:06:35
Speaker
Again, I use a nice mic and once I have it recorded, I edit the audio to make sure that, because I usually just, when I screw things up, I just keep, I just do another take, another take until I get it right. Come back, take all that out.
00:06:51
Speaker
And then I put in pauses if I need the pauses where I know I'm going to be doing a lot of different things in Excel and then I come back and I start ScreenFlow and I start the audio at the same time and I then record the video part and then I bring it back together in ScreenFlow and then we do a final edit to take out parts that didn't work or problems, which actually is usually I practice enough so that I don't make that many mistakes.
00:07:16
Speaker
Anyway, that's how I do it. The one problem with doing it in advance is you lose the kind of real-time authentic sound in your voice, which bothers me actually. I'd like to do it the other way where it was real-time, but then you hear all the mouse clicks, all the mistakes go in there, and I think you have more post-editing.
00:07:36
Speaker
Yeah, and then you're like, oh, and by the way, I've done it this way, but if you did this other thing, this other way, and you sort of can trail off, it sounds like you're actually scripting out each episode. Yeah, and for me, like, I am terrible at getting into tangents, right? So even if I go into it thinking, okay, I'm going to stick to this one topic, it's quite likely that I'll end up, if I'm doing it, if I'm showing you how to do something, or if I'm not scripted, it's quite likely that I'll end up getting it off into at least one and maybe more tangents.
00:08:05
Speaker
And sometimes you get into an area where you think exactly, you're like, oh, let me show this one other thing, and then you go look at it, it doesn't work. Or you forgot that there's a setup thing you didn't do. And I just found that the only way for me to really keep it short and sweet is to script it ahead of time. And of course, the nice thing then is I can look over the text and I can adjust the vocabulary, like sometimes
00:08:24
Speaker
you'll call something in one place, and then you'll refer to it some other different way in a different part of the video. So that allows me to clean that up before I voice it. But like I said, the one thing you end up with then is a little bit more of a scripted sound. It sounds kind of more corporate, and that's something to work on. Right. So you have 300 or so videos.
Popular Excel Topics and Audience
00:08:46
Speaker
What have you found are the most popular videos, or popular topics, I should say?
00:08:52
Speaker
Yeah. I mean, everybody likes VLOOKUP. And I think struggles with it to some extent because of the way the arguments are set up in the function. Like, people run into problems with matching all the time because the last argument is something. Yeah, the last argument.
00:09:07
Speaker
Index match, of course, some productivity tips like, for example, if you have some raw data and you've got 10,000 rows but you've got 1,100 rows that are blank, there's some nice tricks for deleting all those rows using GoToSpecial. You can do GoToSpecial, then blanks, then you delete everything and you can clean up data like that really fast with some kind of
00:09:33
Speaker
approaches that you wouldn't normally think of and those people really like those too. Conditional formatting as well because I think it's great when people see the, when they see Excel help them find what they're looking for, that's really powerful because I think a lot of times people feel like Excel's not helping them, it's actually making them suffer. So conditional formatting is another area where I think once people kind of get it, they really like it.
00:09:58
Speaker
And so are most of your folks who are buying the services or logging on, do you have a sense of what fields they're in for the most part? Are they mostly sort of business, corporations, marketing? Are they sort of junior analysts? Do you have a sense of who the folks are? Well, I do only to say that they're all over the map. That's partly because
00:10:23
Speaker
When I started off, there's a lot of advice out there if you're going to build something, some web property that you should be as focused and focus on one niche as possible. In some ways, the more narrow the niche, the better. I didn't really know exactly where I wanted to end up, and I was more interested in general resources for the average person, and so I kept it
00:10:46
Speaker
I didn't specialize in a niche and because of that, I have analysts, I have consultants, I have people that just work at an insurance company or whatever and use Excel all the time. They're really all over the map and also because of that, I started with foundational stuff like here's how you insert columns and rows, here's how the worksheet works, here's how the grid works, here's how references work, all these basic things which you have to know to be productive in Excel but they're honestly boring to go through.
00:11:16
Speaker
I'm happy to say I'm done with that part now. Well, what's interesting is I find in the data visualization community, especially, people say, oh, Excel is terrible because it creates terrible data visualizations to which I always say, Excel doesn't actually create anything. You as a user create something and you have to sort of override a lot of those defaults. But your content is mainly focused on the increase in the productivity of people who are actually working with the data in Excel as opposed to the visualization piece.
00:11:46
Speaker
Yeah, so far I haven't even, I've done six or seven videos related to charting and I've done about 25 related to conditional formatting or so right around that number. But I haven't actually done anything really extensive on visualization, like specifically or on charting. They're actually on my list and I hope that the summer
00:12:06
Speaker
spend some more time doing that. Like you said, so far it's been more like how does the average person just work faster and more productively in Excel. Really I had the kind of idea that just be nice to be able to get your work done, feel like Excel is doing more work for you as opposed to you doing all the work for Excel and then be able to go home on time or else just go
Excel Productivity Tips
00:12:28
Speaker
on and do something else. Right, instead of fighting with it all the time.
00:12:34
Speaker
So you've worked with a lot of people, you've been creating these videos. Are there things people, are there things you think people should know about Excel that they don't or they don't seem to do very well?
00:12:44
Speaker
Well, I think that, yeah, I mean, there's some things that, you know, like the referencing topic is a big problem for a lot of people. They just never really, you know, never really learned the difference between a relative and an absolute reference. And it is, it's kind of an annoying topic because it's sort of abstract. But I think if you spend a little bit of time with it, you'll get it. And once you get it, you kind of never do forget it. Related to that is, I use named ranges all the time.
00:13:11
Speaker
I use named ranges instead of making references absolute because they basically are equivalent that way. One of the great benefits of naming something is you can make formulas that are way easier to understand. I really think that's something if you don't name ranges, in a lot of cases, even just one or two ranges on a worksheet can really make your formulas a lot easier to understand.
00:13:30
Speaker
If you work with data, I would highly recommend that you become at least pretty familiar and at least moderately productive with pivot tables because they're kind of hard to make them do what you want initially. But if you spend some time with them, you can really save a lot of time. And once you understand how they work and what they're good at, you can really use them productively. And somebody can give you
00:13:54
Speaker
50,000 rows of data and you can understand it in 10 minutes, which is really an amazing capability. And finally, I would always recommend if you don't use conditional formatting, it's something you should totally look into because having Excel highlight certain cells for you that you're interested in seeing is really powerful. And for example, you can do things like if you have a lot of data,
00:14:16
Speaker
You can put a little search box at the top of that data, have it so that when you type in some text, it highlights the rows in that table below that have that text in it. And that's a great way to quickly find information in a big list. I like it better than filtering because you can see all the data. You're not hiding the data by just filtering on only the stuff you're looking for. And that's the kind of thing you can do with conditional formatting that I think is really useful.
00:14:45
Speaker
Yeah, and one of your latest videos is how to apply, how to use a formula with conditional formatting, which I think is a, in my experience, it's a hugely underutilized feature of the conditional formatting. And it's not so simple to sort of implement, so I think that is an incredibly valuable video that you put up.
00:15:05
Speaker
Yeah, it's funny because Microsoft, they're kind of a marketing company I think in a lot of ways and under the conditional formatting menu there's all these presets that already exist and they do all sorts of stuff like top 10, bottom 10, above average, below average, text contains and so on and so forth.
00:15:24
Speaker
But in a way, what happens when people use those is they don't really understand how to use a formula. And really, the most useful way to use conditional formatting is with a formula, because then you can make it do whatever you want. And a great example would be if you've got a list, two different lists, and you want to highlight the things that are different in those lists, conditional formatting is a great way to do it. But you're not going to easily be able to do it using the built-in stuff. You're going to have to use a formula. And it's not a complicated formula. But you'll need to use a formula in that case.
00:15:54
Speaker
I would say on the named ranges, my problem always with named ranges is I tend to want to name all of my ranges like data or table. So things always end up crashing on me because I haven't brought over this sort of mindset of variable names into Excel where I'm sort of, you know, these named ranges need to be thought more of variable names as opposed to just a label. Yeah, that's true. There's some kind of weird, if you watch like somebody who's more of a developer, like a programmer,
00:16:23
Speaker
They will always prefix all their names with like, you know, if it's a list, it'll be like LST something. If it's a table, it'll be TBL something. But yeah, you're right. Like if you choose some really generic names, you can run into some conflicts in Excel. Yeah. And what about pivot tables? I think, I mean, there's a lot of videos on the site about pivot tables.
00:16:46
Speaker
I guess, are there places where people are not using pivot tables that they should and vice versa? Are people using pivot tables where maybe that's just sort of over-engineering their worksheets and maybe should just try a different approach?
00:16:59
Speaker
well i think you can if you have well structured data i don't really think you can go wrong with the pivot table uh... in most cases because it's you know there's a lot of this a couple reasons one of them is that you don't need to use any formulas at all generally and you know so excels doing that work for you and uh... as long as you got your data set up right you're gonna get really clean results without any errors and it's going to be really fast like so basically it's you know i've actually did a repeat the video recently where i
00:17:29
Speaker
built three different reports manually using things like SUMIFS and COUNTIFS, those sort of functions. And then I built the same reports using pivot tables. And even if you're really good at Excel and know what you're doing, there's just no way you can do it anywhere near as fast as you can do with a pivot table, as long as the data is in good
Historical Excel Errors Discussion
00:17:47
Speaker
shape.
00:17:47
Speaker
And it's, I think, in general, a lot more accurate because it's really easy to have a reference, get out of whack somewhere in Excel, and never notice it. In fact, that huge economic problem, I don't know how many years ago it was, you know, where the, that was caused just by somebody missing it, clipped off five rows of data accidentally, nobody noticed it.
00:18:08
Speaker
Yeah, I mean, I think as much as I sort of dislike this idea that Excel makes bad data visualizations, I'm also not a huge fan of doing all of my data work in Excel, because for that exact reason, especially if you're just highlighting and not using formulas, I'm always surprised when people don't use a lot of the formulas you've mentioned, like the index and match and VLOOKUP, because they're not reading in their data, like copying from the screen and just pasting it into Excel, and that just leads to a lot of problems.
00:18:35
Speaker
Yeah.
Closing Remarks and Listener Engagement
00:18:37
Speaker
Well, this has been really interesting. This is great. I really enjoy the videos, and I hope other folks are checking them out. So thanks so much for coming on the show. Oh, you're welcome. And thanks, everyone, out there for listening. If you have any comments or suggestions, please let me know on the website, policyvis.com, or hit me up on Twitter. And I am John Schwabisch, and this has been the Policy Vis Podcast. Thank you for listening.
00:19:12
Speaker
Hey, listener, if you're still there, thanks so much for staying tuned. Dave and I are really happy to offer a discount on both of our shortcut cards. Dave is offering 20% off of his Excel shortcut worksheet, and I'm offering the same 20% off my PowerPoint shortcut cheat sheet. So if you go to either of our sites and enter the discount code policyvizpodcast, all one word, you'll receive 20% off. Thanks again for listening.