A Cheat Sheet to get more out of Microsoft Excel
Ever wanted just the Cliff Notes version of Excel? We've got you covered with the Excel Power Users Quick Guide - and it's free.
Brad Edgar of BradEdgar.com joins Excel TV host Rick Grantham and cohosts Excel Author Szilvia Juhasz and Excel MVP and author Jordan Goldmeier to talk about Power Query and Dashboards.
Rick: Welcome Brad Edgar, our special guest from Canada. Brad Edgar of BradEdgar.com where you can go to learn dashboard and dashboard products. How does a guy from Canada making his living on the technology side of an ERP system develop Brad Edgar.com?
Brad: I had a lot of people asking for me for help. I realized I was getting pretty good at Excel. I used my name and my own domain and started BradEdgar.com. I want to be able to help people understand how businesses work and use data essentially to answer questions we have on business problems. I realized Excel has all the tools to do that. When I started using Pivot Tables, I realized you could summarize any kind of information anyway you want. From that realization, I wanted to learn everything about Excel. Now I’m using it and I’m training other people on how to use dashboards. I’m also diving into other topics, such as becoming an Excel guru, and using different tools with the Internet. The Internet has brought information and learning opportunities. And I’m enjoying that.
Rick: There’s kind of a similar story we hear from people coming on the show. When I realized I might be good at it, it was a surprise. I was in finance about 15 years ago, in those Lotus 1-2-3 days, spending 40 hours a week doing spreadsheets. After a while you realize people think you’re pretty good at it. Bill Jelen said similar things, “I looked to my left and I looked to my right and I know Excel better than these people so I might as well be Mr. Excel.”
Szilvia: That’s why people become surgeons!
Rick: I can cut better than that person.
Brad: I don’t think a lot of people focus on getting the right information index and multiple sources of data to bring together and build a dashboard. I haven’t used a lot of power query but everybody loves a good VLookup. I was using that index match to merge tables and information together to build answers to common business questions. What I realized in the past couple months after I started playing with Power Query is that Power Query is amazing. My website will elaborate more on the topic. We have Salesforce, Oracle, and we work in multiple different systems, such as homegrown manufacturing execution systems, and with Power Query you can pull that information together and build enormous data sets and answer any questions.
Rick: How does someone good at Excel parlay that into dashboards?
Brad: Getting information is the big part, but once you get data you have to learn what to do with it. Once I learned how to get data, I had to learn Pivot Tables to summarize data. Then I learned from people like Chandoo and great websites how to build a front end dashboard from a calculated sheet. Three sheets including the data, calculated sheets, and the front end dashboard tying it together is basically what we’ve done; selling dashboards, Simplifying processes for people trying to build dashboards and for people that are using them.
Jordan: When I wrote my Dashboard for Excel book, I was upset with the decision that a dashboard had to be one page. But I evolved and stopped caring. I wanted to give people what they needed. Did you quit caring? What are your thoughts about that?
Brad: I think that’s up to the user. If you can get to the ultimate person who you’re doing the dashboard for, you want to make sure you’re covering all your bases for what they are requesting. If it works and you’re offering up the solution, then it is OK. Whatever is the easiest way is the best way to get the data across. Where people lose is trying to make it too pretty and too nice. There is a happy medium. You have to make sure you focus on the right key points of information.
Szilvia: Brad I’m curious as someone who is getting into Power Query and building dashboards from back end databases, were you an Access guy before you got into this? And, where do you stand on doing this in Access versus Power Query? Do you have a preference when you have to shape data?
Brad: I’m definitely into Power Query now. Access has become more limited unfortunately. Power Query is going into the future integrating and making data actionable for the people looking at it.
Szilvia: Do you find that peoples’ minds are blown when you say no more VLookup when you’re relating tables?
Brad: I think there’s a place for VLookup, but the nice thing about Power Query is that it allows you to merge data successfully each time. I still use index match and VLookup, but I’m realizing that a lot of that work is not necessary.
Rick: Ken Puls says Power Query will change your life. I have some questions from your bloggers, such as Chris Newman of Thespreadsheetguru.com. Where do you get your design inspiration from while creating your dashboard?
Brad: I started using software called Canva. When I started using it, I realized that there were a lot of different templates and I drew inspiration off of a lot of people’s work that’s out there — Color schemes you could use. How to mix and match fonts. I think it adds to the look and feel and the vibe of the spreadsheets.
Rick: Next question comes from Amey Dabholker from ExploringExcel.com. What would you prefer for dashboards? A dynamic or static dashboard for small and big data analysis? Which do you prefer and which scenario?
Brad: Obviously to me, static would be the route for smaller dashboards. I’m always the advocate for dynamic dashboards because I want to make sure I don’t have to do anything after I set it up. If you want to put new data in, you can do a data dump. Whenever you can use Excel tables when you’re building your data models, do it because it makes life easier. It is good if you can merge your data inside a spreadsheet using a tool like Power Query as well.
Jordan: My two cents: Whether to do static or not depends on the client. With small data it is easy to do static because there is not much to change. But even then, I have clients that ask me if I can change things. I don’t think there is a clean answer except for asking what the client wants. Interactive is great, of course.
Excel Data Visualization: Presidential Approval Ratings with Slicers & Power Query – Chart Tricks
Advanced Excel Tutorial – VLOOKUP() Speed in Excel | Excel TV, Episode 54
Excel Tutorial Concat Function in Excel with TEXTJOIN – Excel.TV Episode: 53
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.
Get the FREE Excel Power User Guide