Skip to content
014: Excel Dashboards With Jordan Goldmeier episode artwork

Feb 1, 2017 1 hr 15 min

014: Excel Dashboards With Jordan Goldmeier

from Learn Microsoft Excel with MyExcelOnline

Jordan Goldmeier is an Excel MVP and an expert in Excel Dashboards and Data Visualization. He is an internationally recognized speaker, and author of Advanced Excel Essentials and Dashboards For Excel. He has consulted with and provided training for NATO, the Pentagon and The Financial Times. Jordan is the co-founder of Excel.tv and the founder of OptionExplicitVBA.com and resides in New York, USA and in today's episode speaks with us about Excel Dashboards. 👉 Join Our Academy Online Excel Course 👉 Show Notes: https://www.myexcelonline.com/podcast/14-excel-dashboards-jordan-goldmeier/

Episode details come from the podcast's RSS feed.

Embed this episode

Ready to play

014: Excel Dashboards With Jordan Goldmeier

0:00 1:15:21
of matches

Transcript Auto-generated, may contain errors

Betway Sportsbook. Whether it's a parlay after a long day or a straight bet before bed, you've got more ways to play. Get way more with Betway. Must be 19+. Ontario only. Please play responsibly. Concerned about your gambling? Visit ConnectsOntario.ca.

This is MyExcel Online Podcast, episode number 14. We have a lot of information being thrown at us in our businesses. I mean, we have a lot of information being thrown at us all the time anyway, just living in an information age. So dashboards allow us to say, hey, these are the most important things that we need to be looking at because these are the things that are actually going to help us make a decision. Welcome to the MyExcel Online Podcast, the need to know Excel insights, knowledge, and tips brought to you by the experts that know them best. Are you ready to explore your full potential and get better at Excel? It's time to stand out from the crowd with your host, John Mikaludis.

G'day guys, and welcome to the March Online Podcast with your host, John Michaloudis. In today's episode, we have an Excel MVP who is an expert in Excel dashboards and data visualization. He is an internationally recognized speaker and author of Advanced Excel Essentials and Dashboards for Excel. He has consulted with and provided training for NATO, the Pentagon, and Financial Times, amongst others, and is the co-founder of Excel.TV and the founder of OptionExplicitVBA.com. Let's welcome from New York, Mr. Jordan Goldmeier. Hello, Jordan. How are you, buddy? I'm great. How are you? Good, thank you. How are things over in New York? I hear there's a storm coming through.

Terribly stormy. Very, very windy. But otherwise, greatest city on earth. Great place to be. Lots of great things to do here. So yeah, once the storm is gone, it'll be great here. Awesome. Now, you weren't living there before you were living somewhere else. You just moved to New York, right? That's right. So before this, I was living in Cleveland. Aha. They have one of the best basketball teams on earth. Is that from? The best. The best. Yeah. That won it last year. And I've got the jumper of yours truly. LeBron James. LeBron James, yes. I jumped with a bandwagon. So yeah. Yeah, he's awesome. He's a hero.

I don't know if you saw, there's a picture of me floating around online. In fact, I think it's actually, I know we're going to talk about our videos later, but it's on the Excel TV site. It's me right outside the stadium at the watch party when they won. And I was just really ecstatic. It was a great win. It was a great win. And you think we're going to back it up this year? Yes, definitely. Only one eyed supporter. Good to see. All right, Jordan, I've told our listeners a little bit about your background. Why don't you tell us more about yourself personally and your long journey to becoming an Excel expert?

Oh, wow. Where do I begin? Okay, so to tell everyone about me personally, let's see where it started. I was in school. Before that, really in high school, I had been programming a lot. I loved programming. But I got to college. My dad had convinced me to become an accountant. So I was studying accounting, and I landed a job as an auditor for the Air Force. And I was actually a very terrible accountant. Well, I would say, let me rephrase that. I was a terrible auditor. You know, I would do these audit reports, and I would go to the client. I'd be like, you didn't do X, Y, Z. And then they would say, yeah, we did. And I'd say, sure. You know, that sounds right. I wouldn't ask for evidence or anything like that. But, you know, so my auditing skills were not great. But I was very good at the data side of accounting. And then because, again, I wasn't really good at the day-to

I spent a lot of time fixing up the macros that they had. So this was maybe 2007, and they had macros that they hadn't updated since Office 2000. So having a programming background, I went in and I started making these macros. And really, it was just kind of a thing. I was just giving them to coworkers who gave me ideas and people who kind of knew how to use them. But over time, I started doing error fixing, and people just kept asking and started passing them around. And pretty soon, the organization, the Air Force Auto Agency, was like, hey, why don't you take all these and update them and turn them into our official toolbar? So that's what I did. And so then 700-some people around the globe.

Like, you know, I was getting emails from Japan and Korea, auditors out there asking me, like, can you add this and can you add that? And it occurred to me, I was really bad at my day job, but I was good at this program. And I really should be doing so. That's when I took a job with Booz Allen Hamilton, a big defense contractor, and that's kind of where my career took off. You know, I did work for NATO training command. I built him a dashboard, and then I even presented it in Kabul, Afghanistan. So my work is taking me all around the world, and people started asking me to speak on these things. You know, really, when I decided to concentrate on my Excel skills, and that was a marketable skill, a lot of things unfolded for me. And, you know, the rest is really commentary.

So you talked a little bit about your background and how you became to learn Excel. And how did you learn all that stuff? Was it self-taught? You bought books, online forums. How did you learn to write macros and understand Excel?

Great question. So, you know, macro writing really comes back to my early, early childhood, which I know sounds weird, but I started programming when I was 10. So I was checking out these books from the library. And the thing is that coding was not as interesting to schools then as it was now. In fact, it was just kind of considered a very nerdy thing that you would have a computer club for. But my school didn't really have one of those. So and I really at the time, these programming languages at the same time, Microsoft was starting a lot for them. So if you wanted Visual Studio, that costs a lot. So what I would do is I would go check out these books on Visual Basic. And then my friend one time.

And I was just reading them, just to read them, because I liked coding, even though I didn't really have access to Visual Basic, so I would just sit there and read it and think about how to do these things and imagine myself doing it. Knowing at a certain point in the future, I would get it. And then a friend of mine was like, you know, there is like a Visual Basic editor attached to Microsoft Word. And I said, what are you talking about? And so he showed it to me. And so I started practicing using Word, and I would adapt the stuff that was for Visual Studio, and I would just do it for Word.

In doing that, I learned how to create macros. So that was, I think my first macro was probably sixth grade. But then I didn't really touch it for many years. I knew it was there. People said, hey, what's coding and programming like? It was a very easy example because almost everyone had Word. I'd say, hey, let me show you. You can actually make your own and how to do something in Microsoft Word. But really, for many years, I didn't really touch it. Throughout that time, I kept learning to code. I went to career school for half a day in high school. And so I was doing Visual Basic coding with Visual Studio there and some other languages. And so then I became an auditor. I had seen all this stuff before. So when I say saw it all before, I saw kind of the mechanics of it before. But since it had been many years, I still kind of had to relearn.

how to do things specifically for the Microsoft Word, which was at the time 2003 and was transitioning 2007. So to get up to speed then, I used to go to blogs. But the thing is, at that point, the Air Force really didn't know what to do with blogs. So it would block half of them. It said this is not like a work-related thing. So they would block them, which, you know, nowadays it seems ridiculous. How could you block a blog or block Facebook or YouTube? But that's what they did back in those days. So half of them I couldn't get to. And then I would learn tricks to get to them. So instead of writing .com, I might change the suffix to like...

.co.uk or something like that if they had like, you know, an alternate mirrored countryside. So that was one way around it. So secure our government, right? So I would do that. And then what I started doing is I started buying a lot of books. I used to have kind of a veritable Excel library at my cubicle. People would come by asking questions and I'd go, hmm. And I'd like, you know, reach my finger across and find the book. And some books that stuck out were there's a holy macro book. It was like 101 macros you can use right now. And it was all the code is in the book itself. So like I download file with it. And I would read through that.

And I'd figure out the stuff I liked and I'd circle it or I'd tab it and then I'd reuse it in my own work. So yeah, I just read a lot of books. My favorite probably being professional Excel development. I think it's a good way to learn is to see the code first. And as you said, the Holy Macro had 101 codes. So it's good to see it, visualize it, apply it. And then, you know, and make your own code from what you've learned from all those 101. Yep. Yeah. So that's a good way to learn, isn't it? Yeah. I mean, you know, in my way, it sort of happened by accident. So I used to be, this is kind of interesting. I used to be a more religious Jewish person. So I kept...

the Sabbath so we didn't use electronics on the Sabbath so what I would do is I would check out these computer programming books and I would read them on Saturday when I couldn't even pick up or like even go to a computer and try it out so I'd read them like for hours and I would just kind of go through the logic of it and I like reading code so I would think it through and then by the time the Sabbath ended I would go on the computer and try it out but by that time I'd like you know turn it over in my head a hundred different ways so I had all these ideas of what I wanted to do and what I wanted to try out and you know that's kind of the way I learned to code I know that's a little bit different than most people but I find it very useful and that's still how I think about development today Yeah, nice story. Nice story. And that's a way to do it is to see what's out there, copy it, learn from it and apply it in your environment. And it seems like that's what you've done and in your downtime as well is the best way to learn. Now, that's a great story. And yeah, I appreciate that. Now, let's talk a little bit about where you are now. You're the co-founder of Excel TV, which you can find it on www.excel.tv. Now, can you expand a little bit about Excel.tv and what that website is all about? Sure. So I like to think of Excel TV as a community that is at the heart.

of Microsoft Excel and business analytics and business intelligence and data science. And all of that is to say that we are a community around helping practitioners and everyday users, people who want to jump into the space, people who are veterans who want to learn more. We're here to enable them to do better through sharing and growing a community. And so Excel TV really started as a TV program of all things. We call it a TV program. But in the early days of Hangouts, so about three years ago, we started Excel TV as just a, let's say, a video podcast. And we were doing 20 a year.

and we would have guests on. You were one of them, John. So we would have guests on, and I came up with the format, and I modeled it off of this cross between two famous American podcasts, Wait, Wait, Don't Tell Me, and Car Talk. And although I don't think I even got close to either of them, because those are phenomenal radio programs, it was sort of modeled after that, which is to keep the tone very casual and to have guests on to do fun things and have a challenge every week, which is what both of those shows have. And so we were doing that for many years. And this year, we took a break from it partway through the year, and there's multiple reasons for that. First is that the Google Hangouts platform just isn't like it used to be, so Google stopped supporting it.

And it became very buggy. And as we were becoming more professional, it was becoming less so. So, you know, we had to kind of take a time out and figure out what we wanted to do next. But at the same time, you know, we had so many, we built this terrific community with so many great comments. And we had requests, all of your information, it's all over these episodes, right? So if I'm an interested person and I want to learn Excel in my own casual time, you know, on my free time, it's great because I can just learn things. But wouldn't it also be great if you took a lot of the things that you talk about on the show and you packaged it up for a course? And so we thought that kind of as a Google Hangouts was going down and, you know, we were reevaluating.

the show platform that this would be a good time for us to really follow on what our viewers wanted and deliver a course. And so that's kind of this year's future of Excel TV is to kind of on top of being the community network that we already are is to start offering courses that people really need. Yeah. And you've had all these Excel experts, you know, since your inception there. Oh, yeah. We've had tons of experts and we've had lots of fun. We've had Chandu, we've had Bill Jelen. Our first five episodes we had, you know, we couldn't believe the lineup that we put together. In the early days, it was shocking. You know, we had Minda Tracy, we had Chandu, Bill Jelen all in the first five weeks. We had the community development.

partner for Microsoft explaining to people how to become an MVP, which if you've ever tried to Google that or ask an MVP, you know that it can kind of, you know, MVPs can sort of talk about it like an exclusive club. Oh, you just have to try and go out. And then we had someone from Microsoft come on and say, hey, here's what you can do. So I thought that that was like, you know, for me, that was the video that I wanted. So I thought that was very useful. And, you know, we've had tons of experts on and we had a great debate episode. Now you're making me just think about the golden age of Excel TV. Yeah, we've had some great like debate episodes, just fun, humorous things. So definitely want your viewers to check it out if they haven't seen it yet.

Yeah, definitely get on there. Check it out. There's some golden material, great information that you're going to learn from each and every one of those video podcasts here. So to check those, go on to excel.tv forward slash blog. And in there, under excel.tv guide, you have season one, two, and three, where you can go there and listen into these great podcast episodes. And it's not only yourself. There's other people on there, correct, Jordan? That's right. Yeah, so Excel TV was started with my current co-founder, Rick Grantham, and Oz Du Soleil. So that was how the TV show started. So Oz was with us for almost two seasons. Oz really brought something to the show that I think everyone will remember. You know, Oz is quite a character.

that's how we started out and we wanted to make it a fun show and i think we did and then in the later seasons um sylvia who's always a friend of the show and was always very funny and talented when she came on she replaced oz helping us out and oz will still come on when we start the show back up soon every once in a while so yeah no oz is great and you know i it's the first time i saw him i was on there and i go well this guy's great he's a character he wears his nice funny hats and very analytical as well And I recruited him. I go, come over to Microsoft Online and let's do a course together. So we created a data cleansing and power query course. And he's fantastic. He's Excel knowledge. It's just, yeah. And just his video making as well skills. Unbelievable. Just brings so much humor. And yeah, and even your other guests up there, all great people. And you guys are doing an amazing job. And you may not know it, but people from the outside do appreciate it for all the effort and time you put into it. It's all free. You don't get paid for it. And there's a lot of great material there. So thanks for that. Now, let's go on to the next section and talk about why you're here.

Because you are an expert in dashboards and data visualization. So for all the people out there that don't know what a dashboard is, can you just explain to them what an Excel dashboard is? Sure. So to answer that question, I'll give you kind of the two part of it because dashboards are a buzzword right now, and it's sort of worth being concrete on what it is. So I like to borrow from Stephen Few's definition. So Stephen Few is also a dashboard expert. He wrote information dashboard design. I think that's the name of it. I'd have to have it out in front of me, and I've met him before, and he's really an expert, but he's very rigid about the definition. So I'm going to give you his definition, and then I'll give you my take on it as it's applied to Excel. So he says a dashboard is a visual display of the most important information needed to achieve one or more objectives, consolidated and arranged on a single screen so the information can be monitored at a glance. So that is his definition of a dashboard.

That's a great summary. That's a great summary. What's your take on that? Sure. So my take is this. Most of us when we build what we call dashboards for Excel are not dashboards. So we might build a pivot table, which is a report. And no, I'm not going to, no offense to pivot tables, John. You know, what people will do is they'll create charts and they'll put them across different tabs because they think they're segmenting information. And then maybe they'll make a menu that links to it. These aren't dashboards either. So a dashboard for Excel is usually on one screen and allows you to monitor something, as he says, at a glance. Now, one of the things that I depart from him is that his view is that dashboards should not have any interactive elements on it. And so in my course and in my books, I actually talk about how to add interactive elements.

For me, a dashboard in Excel is really made up of three things, right? So one, you're monitoring a process. So you are visualizing a process. So showing results is not necessarily a dashboard. So it needs to be something that's kind of ongoing. You're monitoring something. The next thing is you have to have data, which I know sounds very weird, but you'd be surprised by how many people say to me, I want to build a dashboard and they don't have any data. And then finally, the thing that the dashboard needs to support is it needs to help make a decision. So maybe Stephen Pugh says achieve one or more objectives. I like to think of this in terms of your user, the decision maker. It needs to help support make a decision. So it can't make a decision for you, but it needs to have the information on it.

that answers a question. You should know what those are going in. So that still kind of gives you the idea of dashboards at a glance. The thing is when you build them in Excel, Excel is a great platform for them because Excel has a lot of the infrastructure already available to you. And I would argue it's even better because, you know, right now there's a lot of sales, there's a lot of BI platform dashboards that are competing with Excel and they cost a lot of money to implement in design. And by the time that they're installed two years later, you forgot what the original business problem was. So the thing about doing them in Excel is that they allow us to turn them around very quickly while that problem is still fresh. And it sort of stems our own personal bias because if I spent two years installing something and spending a lot of money,

time, effort, resources, and my job really depended on its successful installation, I won't really care what the results are. I'll just care that it was installed. So doing them in Excel will allow you to actually still judge the data as data and not just say, oh, God, this took forever. I'm just glad it's installed. Let leadership figure out the rest. So Excel is a very important platform because it's easy and it's fast and allows us to build very quickly.

Yeah, and a lot of managers out there, they don't know how to use Excel. And so they go and seek out these vendors or these big solutions. And they pay a lot of money and say, hey, you guys do the data visualization. Bring it all in there. We'll pay you $100,000 a year. And they have the data there. But you can actually get the same results. I'm convinced you can get the same result using Excel. But you need to know. You need to get to that level of knowledge. So yeah, there's a lot of wasted money out there or there's great salespeople out there that sell the product. But you can actually do it within Excel. And I think that for me personally, knowing how to do this in Excel really helped my career.

you will always be more valuable than, you know, a salesperson. You're always going to be the person that they ask for help. So I think it's really important to make dashboards for Excel or I would say in Excel. And it's an important skill to have, like I said, because, you know, you're not just going to save your organization money because of your process insight, which hopefully you will because you created this dashboard to save the money, but you're going to actually save the money because you built it in Excel over this platform that no one really understands that three years down the road, everyone's going to say, oh, what a failure and start looking for another platform. You know, you can avoid that. And that's, I think, a very big deal. All right. Now you talked about the three points before. Let's go over there just quickly. The first point that you mentioned.

Sure. So for a problem to warrant a dashboard, you really need three things, right? So you need a process. So that problem has to be represented as a process. So, you know, a financial statement is not necessarily a dashboard. Even if you make something in Excel with charts, it really has to refer to a process. So, you know, running, you can make something that's a model. And by a model, I mean like, you know, maybe I'm trying to figure out what my workforce is going to be in a few years. I can run it through something and I can get results. That isn't necessarily a dashboard either. So you really, it really refers to monitoring a process because you need to be able to make decisions in real time. So the data needs to be the most up to date.

The next thing you need is data. Again, this sounds like a simple thing, but I've seen dashboards that are sent to me, it's like, well, we don't have that data, so we took averages, which isn't necessarily wrong. But I see this a lot where people will plug in data on the back end just so they have something to show on the front end. So that is not a good idea. Your data needs to be connected to a process. So the data needs to come from the process. So the data before that, the data that comes in, it can come in from your Salesforce or from your accounting system, from IT department. We're talking about data within your business that's coming from a legitimate source.

That's right. And so, you know, there are different ways it could come in. It can come in the old method of copying and pasting. Obviously, I don't love that method. It can come in through automatic updates and VVA or perhaps you've connected it to a source or maybe you have an API you can use. You know, there's many ways it could come in. But yes, absolutely right. It has to be connected. It has to be a function of the process that you're trying. All right. And then point number three. The final point is it has to answer a question. Now, again, everyone can sit here and nod their heads and say.

Oh, yeah, mine does that. No problem here. But actually, what I found is that the opposite is very common, that most dashboards don't answer questions. In fact, or let me rephrase that. They try to answer every question. So what's very common is I'll say, well, how did you decide to put this, to design your dashboard this way? So if I'm doing consulting, it'll hear, well, you know, we couldn't agree upon what to put on here. I didn't want to deal with the political argument. So I just put everything on there and I let everyone just look at it and get what they want. And I would have to say that this is not a good dashboard because it doesn't really answer a specific question. It tries to be everything to everyone. And because of that, it becomes nothing to anyone.

What it forces us to do is really understand this information and read it like tea leaves because we have all this context that's all over the place. So dashboards must answer specific questions to help decision makers. They don't exist to just help everyone. Not everyone is making decisions. So when you're building it, you're building it for a decision maker, not just someone who is interested in what the dashboard should look like. Yeah, so this is probably one of the most common problems I see. It sounds easy when I say it, but in practice, most organizations do what I call data dumping. They just put everything on there because they don't want to deal with what that underlying question is. They don't want to do the political fights. And I know I'm talking endlessly about this, but just to harp on this point.

The thing is that this dashboard exists to model some underlying process. So when people are arguing about what it shows, it really shows that the process is in Discord right now, that people aren't really sure what the questions they're trying to answer are. So in designing and developing a dashboard, that's really your job to hone in on the most important things. Yeah, you need to have the fundamentals and the processes written down. And if you don't have that, then you're not going to know how to create a dashboard because it's a new area.

It's a new area and you are the expert and you know what is needed to create a great dashboard because you've created all these great dashboards. You've got your books out there. So you know what's needed to create a dashboard. And if you don't follow these rules that you're saying, then you're going to get lost. So you're going to have some principles that you need to follow to get out the maximum from that one screen of information. Oh, absolutely. I would agree. You know, like I said, one of the biggest problems too is I see organizations, they'll data dump, they'll put it all in one place.

Or alternatively, particularly, I see this problem in Excel, we'll try to segment it across different tabs. Really, it's kind of the 80-20 rule. 80% of our knowledge is probably going to come from 20% of the charts that we think we need to display. So, you know, in many ways, less can be more. I don't want to say that it's always more, but we should always be trying to do more with less. So if we have a chart that really answers a lot of questions, we don't need all this extraneous information. Perhaps we're just trying to fill in white space on our dashboard. But in truth, that little bit of extra information can become very confusing. So this last one, I know we said it was...

process data and answering question. I think this last one is probably the hardest one because it requires us to not make as many charts as we think we need to make or kind of take a step back and say, do we really need to fill up the entire space even though we think it might look good? Yeah, sure. Thanks for that. And I'm sure that you've opened the eyes of our listeners there. Now let's go on to the next question here. And in what situations would someone use a dashboard? That's a great question. So there's a lot of situations in which someone...

might want to use a dashboard. And so I'm not going to be able to cover them all, but I'll try to cover about 80% of them, leaving the other 20% for kind of those novel cases. Most people who would use a dashboard are executives. So they want to know, you know, what's the status of their organization? How are they doing? So a lot of them will use financial dashboards with financial metrics and maybe some prediction on them. Middle managers often have to use dashboards to understand like employee retention. They have to understand training. Am I doing basically, are the people under them doing what they need to do? So that might be utilization, training.

Things like that. Now then, the third group are specifically process monitoring dashboards. So all dashboards monitor some form of process, but there are ones that do it a lot more directly, and those ones are probably my favorite to build. So those are ones like monitoring manufacturing, monitoring, let's say, an email campaign or marketing campaign, things like that. So in that case, there's kind of an end-to-end process, and we're trying to figure out how is this process doing through all parts of it. And a good dashboard would actually show us, you know, here are the different decision points, how are we doing at each point, and would give us a signal that we need to take remedial action, or at least it would point us to something that we could bring to a stakeholder and then make a decision upon.

So those are the three main reasons we use dashboards. And I think, you know, there's this extra 20% reason that I used to be kind of a purist on this and say those are not dashboards. But over time, as I've looked at the work by you and clearly and simply.com, Robert Mundigal and Chandu and Minda, I think that there's just kind of this fourth area of dashboards, particularly in Excel. It doesn't necessarily fit neatly into that, but it's still monitoring something. It still helps us make decisions. And those tend to be a lot more interactive. So I'm trying to think of a good example might be, I think Chandu built one, built a dashboard around soccer chip. We say soccer here in the US of football.

championship a few years ago. So that would be an example to me of like a dashboard that is really there to just show something. Or another example would be, you know, we just had the election here. So we had a presidential election dashboards. So those you can go on, you get what the current status is, and then you can start clicking states and figuring out your own scenario. So Stephen Few doesn't really allow for scenario creations, but I personally think that those also qualifies dashboards. Those are the different uses of a dashboard. Well, nice. There's a lot of dashboards there. And they're great for, you know, when you're working in a company, it just gives you the alarm bells or the warnings if something is going bad or, you know, also if something is going good. It just shows you. It's like, you know, you're a parlayer and you're flying in the night sky and you're only relying on your instruments because you can't see in the front. So you have your dashboards in front because your plane is always off course.

If you don't have those dashboards, then you're going to crash. And it's the same principle or metaphor for our company. If you don't have those dashboards and you're just flying blind, then you're going to end up crashing. So they're good in a way where you can show you valuable information about a company, where it's been, where it's going, and how you can pivot to become profitable or make more money. Definitely. And one of the things...

that we see commonly on company dashboards is that they're overloaded with signals. And Excel, unfortunately, encourages you to do that with things like conditional formatting that formats everything. I talk about good data visualization principles are that you just show the most important things to make a decision. So I would say that a good portion of company dashboards are good, but the bigger portion have too many signals on it. So you hope that you don't crash. But when I come in, they usually have already crashed. So I think knowing this information up front, because when you say, hey, look, if you're using conditional formatting on all these cells and none of them stick out, you should only be highlighting the ones that require that you take action. Everyone's brain goes, oh, yeah, that makes sense. So it's worth saying here, because it's such a simple concept that I think that we

misunderstand so often. But yes, once you have it right, you can't avoid crashing. Thanks for that. So what are the benefits of using a dashboard rather than just using a profit and loss statement? Sure. So the benefits of using a dashboard in general is that there are certain things that we can only really see visually and understand in context. So you can look at a profit and loss statement, but let's say you wanted to understand the trend of your profit and loss over years. If you were looking at multiple statements, you can maybe think about that trend, but you'd probably want to see it visually. So dashboards allow us to see it visually. And when you visualize that trend, let's say you're just visualizing the net profit and the net loss.

You're taking, let's say we're doing it over five years, taking 10 data points, but you're plotting them in context. So they're up and down and you can actually really understand that together. So when we look at things like regular reports, we are often missing the overall context or the holistic context, the business context from which they came. So what we see is things by themselves. It's really hard to think about that. So dashboards are very useful for showing trends and visualizing information. And they're also very useful just sort of cutting through noise. Like I said earlier.

We have a lot of information being thrown at us in our businesses. I mean, we have a lot of information being thrown at us all the time anyway, just living in an information age. So dashboards allow us to say, hey, these are the most important things that we need to be looking at because these are the things that are actually going to help us make a decision. So that's one of the main benefits of doing it. Now, in terms of doing Excel, and I know I touched upon this earlier, is that, you know, you have rapid turnaround time and you're also able to build things very quickly. And let's say your problem that you're trying to monitor, it's still too big for Excel and you're going to end up having to buy a platform. That's fine. But.

To still prototype it, you can use Excel for that. And maybe through, this is actually very real, this has happened. Through prototyping it for an organization, they decided that they actually didn't need to buy the bigger platform, that Excel was good enough. So the great thing about building dashboards for Excel is that you can do it quickly and it's cheaper. And some organizations will look and say, hey, this is actually better for our problem. And then finally, the reason that I think building dashboards in Excel, just these skills, not just building the dashboards, but knowing these skills on building them for Excel, is that it really helped me differentiate my career.

I don't think I'm anyone special. And I've heard it helped others differentiate their careers. So I'm a big proponent of learning how to do it just for that because you basically become your office's data hero, right? You become the person everyone sort of knows that knows how to think about data just by working in it and visualizing and having so many different stakeholders and can actually provide guidance and help your organization make better decisions overall. Well, there's some great benefits there and efficiency gains. And it's not that hard, is it, to learn dashboards. It's not people think, oh, it's hard, but it's not really. It's easier than learning VBA or macros, right? I think so. So, you know, obviously you can use VBA on dashboards. In the course that we're releasing, actually, mine doesn't have any VBA. And don't get me wrong, I love VBA.

But I think that, you know, there's two really big problems with VBA is that first, the reliance on it for everything that's advanced Excel. So I know you and I were talking about this before we started recording. You know, I was teaching an advanced Excel course that the people had renamed to advanced VBA course because in their heads, advanced Excel is VBA. And I just want to point out that it's not. But the effect of that is that so many solutions in the past have been used VBA that are incredibly iterative. That person leaves. Nobody knows how they work. When they say iterative, I mean what they do is the code. Inside the code, it's doing the same repetitive thing a thousand times. That may not seem like a big deal, but often it's not necessary.

it causes Excel to churn a lot and become slow and the screen fades to white and you hope that it comes back because you're in a meeting and presenting something. So I want to try to avoid that, especially on a dashboard where you need to know that information right away. So I always argue that you don't necessarily need BBA. I show people how to do it without it because there's a lot you can do without it. And if you know how to do it without it, you can actually turn it around more quickly. So I can make a dashboard in as little as 15 minutes just using formulas. And then I don't talk about the use of pivot tables in my dashboard course. I'm not against using pivot tables for dashboards. But again, I think it becomes kind of the hammer that sees everything as a nail.

So what happens is people just make reports upon reports upon reports. And I want them to at least think about it differently and say, hey, you know, there are these other things we can do in Excel to create interactivity. Just kind of add on top of pivot table reporting that one can do. I'm looking forward to you explaining your course because, yeah, it seems like it's going to be a great one. Creating dashboards without pivot table or VBA. Whoa, that's an amazing, amazing tool and knowledge to have. And it's easily done. You just need to know someone that's an expert like yourself to show how it's done. And I'm sure our listeners are going to get a lot of value from the course. Now, just quickly.

I know you've seen a lot of dashboards. What is the weirdest dashboard or maybe the coolest one you've seen done or you've created in your time? Let's see here. I would say there's a few that I've seen that I've been very impressed with. There is one that Daniel Ferry created a long time ago. He's the guy who runs Excel Hero. I know he's not published in a while, but he created a really cool visualization with animation a while ago. I probably just sent it to you and you can show it to your readers. I don't know how to describe it. I'd have to look it up again. So that one was...

It was just kind of like a really cool dashboard that had animation on it. And even though I don't really know that it had a fundamental business problem I was trying to solve, I thought it had a lot of good components on it that one could reuse for their own work. Even though it was kind of flashy, you could adapt it and make it more business-like. So that was really cool. I also just saw, two days ago, Michael Alexander posted this really nifty trick where he used the cells as pixels to represent the United States and was able to create a heat map over the top of it. So that was a really cool dashboard trick.

that I saw that I think was really interesting. And then I guess the favorite dashboard that I made was probably the one I did one for NATO training command and modeled the training readiness. of a certain unit of troops who were sort of transitioning from the NATO forces to the on-ground Afghan forces. So I helped, I created a dashboard for that. That one was really awesome and powerful. It allowed them to drill in and drill down and drill up. I learned so many techniques from that. Many of them actually made it into my book. So in the process of building that, I learned so much. In fact, I think it was actually after that that I decided that I wanted to write a book. Unfortunately, I can't share that one, but it was definitely my favorite work. So via dashboard, you're actually controlling the outcome of the war. Yeah, well, I wouldn't go that far. I wouldn't go that far, but I would say, here's what I'll say. I'll make this pitch to you in this moment. You know, right now, organizations are putting so much emphasis on data and they're not using it.

well. So knowing these skills, you know, you could avoid, I really do believe knowing these skills can help you avoid things like a financial crisis, right? Where there were really smart people who actually knew statistics and data visualization who still created these models that were junk. So I think knowing how to do this and thinking about these problems, you know, I think you do more than win the war. You could save the world with it. So not just me, everyone out there can do it. I encourage everyone, save the world. You don't need to be a Harvard graduate to create a dashboard. You know, anyone can do it. So awesome stuff. Love it, buddy. Now, what do you see dashboards in 10 years time within Excel?

Oh, what a great question. This is a good one because the things I'm teaching, several people have come to me and they said, this is how we did it before pivot tables. And I still like this. It's just my organization. And again, this is not to harp on pivot tables because I still love pivot tables and I use them. But let me think how to say this. It's like this forgotten knowledge about how to do these things in Excel. They come up and talk to me. They're like, oh, this is how we did it in the past. And I'm so glad to see that you've updated it and showed how it's even faster and better now. So I think that the tips I'm showing, they're going to still be around. But right now, what I'm showing, anyone with Excel can do it.

Versus Microsoft is very much promoting a lot of their dashboarding things to compete with the likes of Tableau, which is like PowerMap and PowerView. And those are really cool, and I use those too, but not everyone has access to them. And not everyone can use them because of their organizational policies and because of the 32-bit versus 64-bit. And a lot of them still can't really do the same sort of things you can do manually. So the future, though, I believe is the two are going to mesh together. So whereas I'm using formulas sort of separate from PowerView and PowerMap, I think that they're going to become indiscernible, that the tips I'm using on the other side of the house, which is kind of the Power BI side, that they're all going to just become one unifying Excel experience.

Great stuff. Let's see if it becomes true in 10 years time. I'll have you back on the show. Looking forward. Yeah, good. For all the listeners out there, go for it. Dive in and it's great. I love dashboards as well. I created a dashboard for Chandu's contest back in 2010, I think it was, when I first started learning Excel in greater detail. And I was dreaming about this dashboard for days on end and the amount of things you can do and different variations. You can use a formula, you can use pivot tables, whatever you like, or different formulas. It just consumes you. And it's a wonderful thing because you produce this great, great report. And you think, well, if I can do this, I can do anything in Excel. And so, yeah, the possibilities are endless.

Guys and girls out there, go for it. Learn dashboards. And we're going to take a short break now. We'll be back with Jordan's course. And he'll talk a little bit more about his great Excel Dashboard Pro course. So come back in a second. Can you analyze this spreadsheet using Excel pivot tables? Sure. If you can show me how to. Introducing the Extreme Pivot Table Online Course, which teaches you how to analyze business data and highlight key metrics so you can make insightful business decisions. Create interactive analytical dashboards that will reach top management.

and set you apart from your peers, which will make you more important and increase your chances of a promotion or pay rise. Developed over 15 years through extensive professional use and including real business case studies is the ultimate pivot table course. With over 200 short downloadable video tutorials accessible online anywhere 24-7, 12-month personal support if you ever get stuck, and a 30-day money-back guarantee. Visit myexcelonline.com today and give your professional development the boost it deserves. Okay, we're back with Jordan Goldmayer from Excel.TV, and he's going to talk about his latest course called Dashboard Pro Course. Jordan, this is your first online course. You have a book about Excel dashboards. This is your first course, online course. Why did you decide to create this course? And is it a spin-off from your Excel dashboards book?

Sure. So one could call it a spinoff. I would say the book was a manifesto. It was my heart and soul. I put it in. It's very long. It comes in at 550 pages. And this is kind of a lesson in building dashboards, too. We can parallel this with book writing. This book, I wanted to cover so many things. I wanted to talk about theory. And for a lot of people, they really like that. But what I found out is that I kind of wrote a book for me, which is not, like I said, a lot of people like it. But I really wanted to take all that. The end goal was actually to help others. So this is like a good lesson when you build a dashboard, that you're building it for your stakeholders, not yourself. It doesn't necessarily need to be perfect. It needs to be successful. So all that is to say that I thought,

kind of the future of this material would do very well with a online course because you know it's hard to get through 515 pages even though I make jokes and I think it's fun and I enjoy even reading my own book but I think that there's a lot you can do with an online course that you can't do with a book. So with online courses, you can show people. You don't just have to write how to do it. And writing how to do something, I don't know how you are, John, but I have to kind of get in the mood for it. If I have this huge process and I have to write it down, even though people say all your explanations are good, they don't really know how much pain I go through in coming up and making them good and how I can become a perfectionist with that. But doing the online course, I can just show people. And I have fun doing it when I do demonstrations. I make jokes. And instead of saying press F4 to make it absolute, I'll say, hey, click F4 to lock it in.

You know, things like that. I kind of make it fun. And I really enjoy doing it. And I think that creating this online platform, I found that other people really like it too because they don't have to, you know, if it's in a book, you have to kind of go back. If I'm doing it live, they have to ask me to repeat it. But online personal learning, you don't have that kind of weird embarrassment of asking the instructor to repeat something. You can just scroll back. I really think that's really important. The other thing that I really like about the online delivery is that there's a comment section. So other people can help each other out. And what I found out with comment sections is a lot of people say, can I do this and this? And that becomes another lesson. So, you know, I guess what I'm saying is that the people who take the course help me come up with future courses. But there's something to be said about that because it creates this process.

of continual learning that really online platforms obviously there's lots of places you can have continued learning but online platforms which just really create the community around it that is very hard to see anywhere else yeah that's right and because each and every student you know uses excel and dashboards in different environments so there's always questions that come up that are from left field and as you said they give you ideas and they give me ideas to create new videos and you know as long as they have questions there'll be a new video out there so it's great to get feedback from the students now tell us a little bit about the course so you know the modules that it includes and explain a little bit about each of those modules and and how long the course is for Sure. So the course is 16 modules. So I think it comes in about six and a half hours. There's really three different sections to this course. So the first section is the basic development principles. So in the first section, we go through kind of the backbone of dashboards. We make a justification in the first module, you know, why Excel dashboards. A lot of the stuff I talked about here. But at the end of that, we're actually going to build one. I'm going to show you how quickly it is to build one using these methods. And you can build it very quickly and effectively. So the first few modules are really talking about the backbone, form controls, the formulas you would use, things to make your life easier, how to develop so that, you know, if you have to give your work to someone else,

They'll understand what's going on. Or I think more important, if you have to look at your work six months, two years later, you might want to know what you did. So I talk about how to develop the best way of doing that. Because I think that a lot of dashboard courses right now talk about how to build one dashboard. You do that through the whole course. But they don't really talk to you about thinking about them or maintaining them or things like that. So we really kind of master the fundamentals first. And then we move over into the middle section. And that's where we actually start building. So we build the components of a dashboard. So I have kind of these different components that I show you how to make. And I like starting with components because most dashboards, I would say, are just built of these different Excel components. And so if you know how to make them, they're kind of like swipe files. You can put them anywhere. It's just a matter of planning them out.

and then putting where they need to go. Then there are three specific dashboards that we will create in that middle section. So we'll go through how to build those. So I show you the setup, and then I show you the formatting, and then I kind of go through how to maintain it if you need to add things to it later. And then the final section is on data visualization. So it's good to know the mechanics of a dashboard, but there's also some theory around it. So I give you some data visualization theory. I talk about data visualization as we understand it, like kind of business visualization that's very much into following data visualization rules and principles. And I also talk about infographics, which I think actually have a place, not necessarily on a dashboard, but within your dashboard repertoire. You should know how to build infographics and when to use them. And we also go through charts that I say are kind of expanding Excel's chart library in the sense that these aren't native Excel charts, but you can use regular chart components to create them. And they provide a lot more of a rich context in which to understand your data. And I think these are very important.

to know how to expand Excel's typical chart library into the things that really are useful to demonstrate data. And then finally, I'll show you how to build a few infographics that you can actually use on reports. And I'll explain why building infographics in Excel is better than using like Adobe Photoshop or something else. So this course is focused within the context of dashboards and data visualization, but it's really all about making you an expert in both of those things so that you can really go back to your organization, use the tools that you learn right away. So everything is set up to, you know, I don't give you Microsoft fluff. I give you exactly what you need to know to use things right away. So it's not like you need to wait to module six.

to start using information. Module one, you're already going to be prepped with information you can use right away. All of that is to help you go back to your office and start building dashboards and using data visualization to create impact for yourself and for your company. Oh man, it looks amazing course. And you're not just learning dashboards, you're learning Excel and the different features within Excel. So, you know, if you're a beginner, you're going to learn a lot of stuff that you didn't know before coming to this course. You're going to learn like things like conditional formatting. You're going to learn how to use form controls on the developer tab. You're going to learn one of my favorite, favorite Excel features, the camera tool, one of the most obscure features. A lot of the experts know about it, but most people don't because Microsoft has tucked that button away. You know, you have to add it.

And you're going to learn just kind of how to create interactivity without any VBA using form controls and formulas and conditional formatting. And all of it can happen very automatically. Nice. Now, who is this course for? I really think of this course as being for three people. If you're a veteran in the space and you've seen the work that your office does in Excel and you think, well, I really want to take my work to the next level, this course will absolutely help you do that. The other person I see this for is the new person.

If you're just an analyst, you've just started, let's say you're working for a professional consulting firm like I used to work, or a banking organization, or you're just an analyst of any sort, and you maybe used Excel just a little bit in university, but you really need to know... how to get those skills to differentiate yourself. So you can do what the veteran did and you can learn the way they did over years and years, but I would actually recommend taking this course because I also sort of take on a lot of that veteran knowledge and say, you know, what worked in the past doesn't work now. So I think the new person, this is a moment to sort of differentiate themselves in their organization to be that big fish that stands out within the pond, you know? So I think that this is really important for new people too because this work is the future. And then finally, there are a lot of career changers, people who have been managers for a while and they see the importance of the data space as it's growing and they really want to learn. They may be, you know, they have some experience with Excel, but they really want to learn the skills that are required for an analyst to succeed so they can make that career change.

Instead of managing things that are non-technical, they can go into that technical management space and learn how to create dashboards to show their managers and also to reflect what people under them are working on. So I think the three different people are the veterans, the newbies, and the career changers. And those are all the big people who are coming into this data space right now. And also, if you're looking for a job, you should definitely take this course, learn how to create a dashboard, put it on a pen drive. And when you go to that job interview, give them the pen drive. Say, hey, this is what I created. It's a trick that I did, and it got me a job. So if you can show your prospective employer.

The things you can sell, then you can sell yourself. You can talk about it as much as you like. But if you can actually show it with a pen drive, say, hey, stick this in your laptop now. And this is what I do. And I'll show you, explain it. This is how it's done. And if you can direct them and say, hey, these guys will be blown away with that. And I'm sure that you'll have a better chance of getting a job than not having that skill.

Absolutely. And I would say for the career changer specifically, that's such great advice because I've helped some people build with their resumes who are trying to change careers and go to the space, and they've taken some online courses. But right now, organizations, they sort of hear that a lot. Oh, I can do this, I can do this. But one of the great things about dashboards in Excel is that you can show people the numbers. So you can literally show it to them right then. So that feature that's so important in general when I go to a business and I show them their dashboard, I can bring it on a thumb drive. I can also do that if I need to get a job, and I'm always talking to a prospective employer.

And it's more than just saying these skills, I can show it to them. And having something ready that you built, that really shows a lot. It shows kind of the power of Excel and also shows the power of this knowledge in advancing one's career. Yeah. Now I've seen your course, a few of the videos in there, and I've got to say they're amazing. They're fantastic. A lot of effort's gone into it. You said you're a professionalist and it comes through in your work and in your videos. So it's great. And I'm going to finish off the course. So now what are the benefits? You said there's six and a half hours of material there. Can people download the course videos, the workbooks? What are the benefits that you get for signing up to this course? Sure. So we have some awesome, awesome benefits we're doing. So the first benefit is, like you said, you can download the videos. We also have quizzes that come with it, put through at parts where I think that we should test your knowledge. And these are fun quizzes. They're not all terribly hard. We also have labs that you can complete.

You want to just take some of the demos, have labs associated with them. So if you wanted to take that information and you wanted to kind of get some step-by-step instructions to try on your own, you can do that. We also have all the files that we use are available. So if you want to watch as you go and build as you go, as I explained, you can do that too. So we definitely encourage that. So there's quizzes and labs. In addition, we also have all the download files. What I love about the download files is this. Once you've built it, it's yours. So you can go take that thing that we built, copy and paste it, put in your own work. It's all there for the taking. It's not like something you have to go and ask me, like I've patented it. No, once you've built it, it's yours forever. So we have those things and we love that. In addition, right now, at least as part of our current launch,

we're offering a few other bonuses, which is one is I have a talk that I give on building better spreadsheets that I've given for conferences all around the country that people have paid, you know, $1,000 or more to go to the conference to see me speak. I actually recorded that and you get it for free by signing up for this course. So I think that the presentation is about 40, 50 minutes, a lot of great information. It really is a distillation of things I've heard over the years, touches upon a lot of things. The next benefit that we have is that we're going to have two live question and answer sessions. So users will submit their questions before and then I'll give the answers live in a webinar. And we invite all people who sign up for the course to come join and take a look and maybe ask some questions that could go into future webinars.

And then all the course is going to be, the course is delivered online through the Excel TV platform. So we're very proud to offer it through our own platform and not through a third party platform. And I don't know how this is for you, John, but one of the things that we like about that is that if there's an issue, which we obviously hope that there isn't, but let's say there's something we didn't see, you can talk to us and we don't have to go to a third party. We can actually fix it ourselves. So that's something that we're really counting on us having a very fast turnaround time for that. But hopefully there's no issues because we've thoroughly tested everything. We also provide support. So again, all of it is hosted. So if you comment, we see that you've commented. So we have comment sections at the end of every video. So feel free to ask your questions there or reach out to us directly. So we provide support.

throughout the course. We're there to help you if you need. What I found is that, and I've been very lucky in at least piloting this course, most people haven't actually asked me questions about what I was showing, but more of them said, hey, can you do X, Y, and Z? So we're very happy about that. But if you do have questions, you can always ask us directly, and I'm here to help. And then finally, again, this is really only for this current launch. We may not be doing this in the future, but we're proud to offer, at least at this point, this lifetime access. So in thinking about this launch and thinking about whether we make access only for six months or a year, we thought for starting out, we want to give it to everyone for the life of what they're trying to do, if only because...

When I think about learning, you know, I want to be able to go back to it. You know, I have books on my shelf that have been there forever. I don't just throw them out after a year. So, you know, this is a benefit. Again, this is not common, I think, for online courses. So I don't really know if we're going to keep it around because I'm not sure that it's the right thing we're supposed to do. But, you know, in thinking about this course, I just thought I should say we because Rick and I, Rick Gratham helped a lot in the development of this course and editing it. So, you know, it's just you always want to learn. You don't just have a book for a year and get rid of it. You know, we always want you to have access to it later on. So you can say, hey.

What did I do? How did I do this even if it was two years ago? It's always good to go back and refer to it. And also, you know, if you're not, you don't want to learn dashboards now, well, you can buy the course now and then in three months down the track, you can start using it. You don't have to start using it from day one. So it's good to purchase it now that it's available and then start using it down the track. Because a lot of people, you buy a book, you might not read it on the first day. You might read it, you know, when you go on a holiday or when you're on a business trip. So yeah, Lifetime Access is great. I'll give it on all of my courses. So yeah, it's a great bonus that you have there. Now, what people need to know, how do they get access to this course? How do they get in there?

So right now, to celebrate the launch, we are doing a three-free-part video series. You can go to excel.tv forward slash training. So very easy to remember, excel.tv slash training. You can go there, put your name in, put in your email, and you're going to get access to our videos. And then the cart's going to open January 28, 2017. So once the cart opens, the course will be available for purchase, but it's not going to be open forever. So we are closing the cart February 7, 2017. So it's not going to be open forever. We are just doing a limited amount of participants for this.

first run. So if you want to be part of this cohort, you know, obviously you got to sign up sooner rather than later. All right. Now, and you have a coupon for our listeners, our lovable listeners, so they can get a discount to the course. The coupon is for 20% off and the coupon code is my Excel online. Very easy to remember. So that is 20% off. And, you know, we welcome listeners of the show to come on and use that coupon and join us for the course. Thank you very much, Jordan. Appreciate that, buddy. And all of you people that are listening in the future, if the course is closed, you can actually...

go on sell.tv forward slash training, and you can put your name there and get notified of when the dashboard course will reopen. So if you're listening in the future and it's not available, just go there, leave your name, and Jordan will notify you of when the course reopens for new students. Mate, I appreciate that. The course is great. Your book is awesome. You're an expert in this field. So I'm sure that anyone that signs up is going to get a lot of value from this here. It's going to help them out. So even myself, I'm learning new stuff just from getting a few of your videos.

Mate, thanks for creating this course. And for everyone out there, you can find the links to this course by visiting the show notes on MicrosoftOnline.com forward slash podcast. All right, Jordan, let's go on and finish our podcast with your Excel favorites. Now, can you tell our listeners, what's your favorite keyboard shortcut and why? Very keyboard shortcut and why. Okay, so I think I have two because my first favorite one, the one I most use is Control-Z, which is Undo. So obviously keep that one in your back pocket. And then I'm trying to think of one that I use a lot.

Others haven't said, I think the one that I most use because I love Excel tables so much is Control T. So, you know, select an entire table area. Obviously, you have to use Control Shift right and Control Shift down to highlight the whole thing and then Control T to insert a new table. Yeah, I love Control T and Command T for those Mac users out there. If you're using a Mac, you shouldn't be using Excel. Unless though, Command T is also what F4 is in formulas on the Mac. So just make sure you remember that too. If you're using Mac formulas and use Command T, it'll cycle you through the absolute references. Okay, I didn't know that.

You know how I learned that? You know how I learned that? I've been teaching all these Excel courses. People, I'll say, don't come with a Mac. And they'll show up with Macs. And I needed to sort of learn how to support a Mac a little bit. I was thinking, why doesn't F4 work? And I found out it was Command-T. So there you go. Good stuff. Good stuff. All right. What's your favorite Excel tip? Talked about this on your final tip of the year. But I'll try to sort of describe this without the visual aspect of it. So if you want to create a histogram in Excel very quickly. So a histogram shows the frequency of the distribution of numbers. So if you want to create one of those very, very quickly, there's this cool pivot table trick I learned, which is you select the column.

that you want to use. And then you insert a pivot table on that. And then that column, you're going to put in the rows section, and you're also going to put in the sum section. So imagine what you'll have is you'll have a series down the side. You have all the values there. And then under the summarized area, you're going to have a count, right? And so right now, all those values are going to have a count of one or two or three. You shouldn't have a lot of repeats. But this is the part where it gets really cool. You can right click.

on that list of values. So that's your rows. You can right click and you can click group. And if you click group, Excel will create some buckets for you. So it'll create kind of like from this to this group and from that to that group. So usually when you do that, it knows dates, you know, it knows how to group January through December, days, things like that. But it can also do with numbers. So you can say how many groups you want. Then you hit OK. And then it'll give you, it'll turn those row labels into group labels and it'll give you the counts that are in them. And then what you can do is you can actually highlight that whole thing and insert a pivot chart or just a regular chart on top. And there you have a very quickly made histogram. Wow, nice. I like it. Thanks for that. I love it. I love it. All right, what's your favorite function and why?

Ah, index. Definitely has to be index. I love index because it's fast. It can do so much. So a lot of people use offset and indirect. Obviously, indirect is a very easy function to understand, but it's problematic because it's volatile. So it's very slow. Having too many of them is going to grind down your spreadsheet to a halt. So index is actually kind of the savior in this case because it can do a lot of what indirect can, but it does it very quickly. We can think about index as kind of in computer programming terms. So if you have something like VLOOKUP or MATCH, right? So let's say match. When you're trying to match something, you're going through every cell and you're looking through a match.

In the worst case scenario, you have to go through all the cells at once. You have to go through all of them. So we can say if you have n cells, worst case scenario, you have to go through n items. Index allows us to get to an item quickly. So if we know where something is, now sometimes we don't, but index allows us, let's say we want to get what the fifth value is in the series, I can put a five in there, and it only reads that fifth out of there. So it doesn't go through every single one. So index only takes one instruction versus match takes multiple instructions. Now obviously, index, match have worked together like peanut butter and jelly. There are times when you need to use match or vlookup. But often...

What I find is that Index can replace both of these if we just sort of structure our data in a way, for example, that it's always sorted so that Index kind of always knows where to go. And I talk about this in the course and in my books. But Index basically allows us to do Excel calculations very quickly if we know how to use it correctly. Nice one, mate. Yeah, thanks for the explanation. It's a very powerful function. And you guys should definitely get on there. And I'll put a link of how to use an Index function in the show notes for anyone out there that doesn't know how to use it. Now, what's your favorite Excel add-in and one that you use each day?

You know what? I don't really use Excel add-ins very much. That's okay. I don't really use them. Power Query or Power Pivot, you can say. Yeah, Power Query and Power Pivot, I guess those are add-ins. Those are add-ins, okay. So I would say I use Inquire every once in a while. I think Inquire is a very powerful add-in. I don't really use a whole lot of third-party add-ins. I will say that the ones I've used a little bit, TabHound, I think is the one.

I can't remember who built that, but it's an awesome one. I thought it was Johnny. So yeah, TabHound is pretty cool. What's the Inquire one? Inquire. Yeah, let's talk about Inquire. So Microsoft added, I think, Inquire to 2013 and above. It doesn't come automatically shown. So you have to go to File, Options. I'm actually doing this now, so I get it right. I'm explaining to your audience. Add-ins.

And then under where it says, like, manage, you would go to com add-ins, you'd hit go, and then you would select inquire and hit OK. So inquire is really awesome. What it allows you to do is understand, like, your workbook, how it's connected to other workbooks so you can see all the relationships to other workbooks and you can see relationships to other cells. A lot of things that other add-ins were doing. previously. You can also clear excess cell formatting. So Microsoft became keenly aware that conditional formats that go all the way to the bottom of the sheet are just, you know, when you develop, sometimes you just have straight things. All of that increases the bloat of your spreadsheet. In some cases, in some models that I've dealt with in organizations, it can increase it drastically and negatively so. So I used to have to go through and kind of root out that stuff manually. Using Inquire, I can just click clean excess cell formatting and it does it for me. And then the other thing I love is this workbook analysis feature. It'll go through your workbook and then it'll tell you what your formulas are. It'll tell you the different types of formulas. But the best part is it's going to tell you where your errors are.

You can use the old go-to special way of finding errors. But as I talk about in the dashboard, of course, rooting out errors is fundamentally important because those spreadsheet errors like div zero and value, all of those really slow down your spreadsheet to a crawl. They're not good. Cool. A lot of functionalities there within just one adding. All right. Good. Now.

What's your favorite online resource or blog that you refer to? So the ones that I refer to, obviously, like, you know, this wasn't even on purpose, but since we just have such a corpus of material on Excel TV, I end up referring back to things I talk about on the show and trying to remember how I get them. But the ones I probably refer to most are Chandu. Obviously, Chandu just has so much material. And I remembered so much of what it is that I just end up, I remember like reading it on there that I end up going and searching for what I read. So Chandu is a great one. Obviously, your blog is terrific, datascopic. I really enjoy Oz's work just around kind of like Excel for...

everyday use that he teaches really teaches you how to be scrappy with it. John A. Campora's blog, Excel Campus, is one I really enjoy. Some of the older favorites that have been around forever, John Peltier's chart blog and textures for pivot tables, obviously yours for pivot tables too. Minja Tracy's blog is phenomenal. Chris Newman's blog is also very good. His is the spreadsheet guru. Nice one. A lot of resources there. Thank you for that. If you could have a main one Excel book for our listeners, what would it be? Am I allowed to recommend my book? Yes.

And then something else. Okay. So, you know, I wrote two books, Dashboards for Excel and Advanced Excel Essentials. I think, you know, based off of just feedback, obviously, I love Dashboards for Excel. And for the people who love it, you know, they love it a lot. But surprisingly, I've gotten a lot more feedback from Advanced Excel Essentials. So I recommend Advanced Excel Essentials. It's actually kind of a condensed version of what Dashboards is. So some of the same material that's in Dashboards, but without the theory, more focused on here's how to take this information and use it right away. So with that is a bit more of an advanced book. But it's one of these books that, you know, most Excel books are kind of aimed at the beginner. So this one is squarely aimed at the intermediate user.

to take up that work and run with it right away. And then the next Excel book that I would recommend is a classic, and that is Professional Excel Development. And that is by several authors, Bob Bovee, Dennis Wallington, Stephen Bullen, and John Green. So these are all kind of the older originalist Excel MVPs. But I think that this book is just like a phenomenal book because it really got me thinking about development in ways that I never thought about it before. So I think that this is really just a terrific Excel book. And I'll throw in a third one here. If you're just interested in just like tips and Excel fun, Bill Jelen's Mr. Excel 40th book.

It's just a fun book, lots of tips. And then we have Excel jokes in the back and there's like chart art and it's just kind of a fun read. That's a great read. Great books there, buddy. I appreciate that. Now you can find the links to all these books and everything we've been chatting about today, including tutorials on Jordan's favorite shortcuts and tips by visiting myexcelonline.com forward slash podcast. All right, let's finish up the show with an Excel learning scenario. Now let's say that I am an Excel beginner or an intermediate user and I need to learn the most important features of Excel within one week because I have an important job interview coming up. With all the Excel knowledge that you, Jordan, know today and the online and offline resources available,

What would be the quickest way for me to start learning Excel and preparing for this job interview? There's really two ways to go about this. So the quickest way is probably to join an online course like my course or like John's course that really focuses on how to get you up to speed very quickly. And I'm not saying that to promote any course. I'm just saying that learning on your own, there's a lot of trial and error. You can save yourself a lot of time really just by learning the things you need to know. So obviously, courses like mine and John's are a very good place to start. But let's say you're also kind of into getting your hands dirty, then that's what you really should be doing, which is you should go on a site like Excel TV is a great place to start. And you should be looking at how to use formulas because I think that's the most important place for you to start.

And that's the kind of thing that organizations are going to ask for. Now, it'd be great if you can show them dashboarding skills and really show them that you can go beyond. But many of them are going to ask you formula questions and formulas like if, the lookup, index match. So you must know how to use those. And you need to know how to sort and organize and understand and filter data. So I think probably a great course to do a great course for that. And I bet, John, you didn't know I was going to promote your stuff. So one of the things that you could probably pick up is the ultimate power query and data cleansing course. That's, of course, your online course, the one that you and Oz developed. And the reason I bring that up is Oz is like, you know, datastopic. His work is very geared towards.

getting you to where you need to be. So my advice is, you know, you can do the course route. Also, you can, you know, if that's not your thing, and that's certainly a respectable answer, you can go into a site like excel.tv or chandu.org or like your site, John, and really start taking a look at the things that we're all writing about because these are the skills you need to know. And I think it's also very important to take a look at the comment sections on these sites because these are the questions that people say, how do you do this in my business? These are actually going to be somewhat similar to what you're going to be asked, right? How do you adapt your skills to a new business problem? So you should have at least some understanding on how you would answer those questions, even if you don't have a solution. Nice one. And what's the Excel feature that I should focus on going into this interview?

Going into this interview the Excel feature I would say that the features it all really depends on the interview itself If you are doing, you know, if this is more of a numerical thing, the features that you need to focus on are Excel tables, filtering, things like that, showing, you know, slicing and dicing data. So knowing pivot tables, knowing slicers are very important, kind of more for that data reporting role. If this is a dashboarding role, obviously, like, you know, the stuff I talk about in my course, knowing how to build a dashboard, you know, knowing how to do data visualization is incredibly important. If you're doing kind of more of a managerial role, I would say that probably knowing both things, knowing a little bit about dashboards and knowing a little bit about reporting is also very important.

All right. Thanks for that, buddy. A lot of valuable information. Thank you. Now, what's the best way for our listeners to connect with yourself? Well, you can always connect with me on LinkedIn. I love chatting over LinkedIn. In fact, that's probably the fastest way, just given how much email I get per day. That's probably the fastest way to contact me. So you can go to LinkedIn. There's no other Jordan Goldmeyer's that I can tell that are on there. Goldmeyer being a very, you know, specific last name. So just type that in. I'm usually the first person that comes up. Feel free to connect. You can also connect through Twitter at option explicit and also at Excel TV for Excel TV.

And finally, you can always email me. I'm jordan at excel.tv. All right. Thanks for that, buddy. And then the final link for the people to receive the dashboard free training course and also get notified of the dashboard course opening. What is that? Excel.tv slash training. Okay. Thanks for that, buddy. Jordan, mate, it's been about an hour that we've been chatting. Fantastic. Great to speak to you. You have a lot, a wealth of knowledge in Excel and dashboards and data visualization and just everything, mate. And I'll be following you. And it's been a pleasure to have you on here. And I know a lot of...

My listeners are just going to have learned a lot just from listening here. And I know going through your courses are going to learn a lot more and just connecting with you because, mate, you are one of the top-notch Excel MVPs with a lot of Excel knowledge. So yeah, thanks for being on the show. Thanks for all the great stuff that you put out. And yeah, I appreciate your time today. Great. Thank you so much. Hey, guys. I hope you enjoyed this podcast show. Thank you for listening. Now, I need your help. I need you to please leave an honest review for the Microsoft Online podcast on iTunes.

Now, ratings and reviews are extremely helpful and greatly appreciated, and I read each and every one of them. So if you go onto iTunes and search for the MyExcelOnline podcast, you can leave a review and also subscribe so you can receive all the new episodes as soon as they come out. Also, if you go over to my blog at MyExcelOnline.com slash blog, you can subscribe to receive weekly Excel lessons for free. Now, these are going to be delivered to your inbox each and every Friday, containing a lesson on formulas, pivot tables, charts, and analysis. By signing up, you also get a copy of my free Excel ebook called The Ultimate Excel Resource Guide.

In this ebook, I show you and review the best Excel blogs, the best Excel YouTube channels, the must-have Excel forums for you to succeed. I handpick and review the best Excel online courses. I'll show you the various Excel books that are available. And I'll also explain the different time-saving add-ins that you must have in order to get the full potential out of Excel. Plus, I have much more information. Now, this ebook is jam-packed.

full of value, and it's free if you sign up to my weekly Excel lessons. So go over onto my Excel blog and subscribe to receive your free ebook and also get your awesome weekly lessons. All right, until the next episode, keep excelling. We hope you enjoyed this episode of the My Excel Online podcast. Be sure to subscribe and share this with your colleagues so they can reach their full Excel potential. You can also visit myexcelonline.com slash podcast for show notes as well as John's online Excel courses and free tutorials to help you stand out from the crowd. Until next time, keep excelling.

Frequently asked questions

How long is this episode of Learn Microsoft Excel with MyExcelOnline?

This episode is 1 hour and 15 minutes long.

When was this Learn Microsoft Excel with MyExcelOnline episode published?

This episode was published on February 1, 2017.

Is there a transcript available for this episode?

Yes, a full transcript is available for this episode. You can read the complete transcript on the episode page.

Can I download this Learn Microsoft Excel with MyExcelOnline episode?

Yes. Use the download control on the episode player to save the publisher-provided media file.