Skip to content
006: Excel Tables with Zack Barresse episode artwork

Nov 19, 2015 46 min

006: Excel Tables with Zack Barresse

from Learn Microsoft Excel with MyExcelOnline

Zack Barresse is an Excel MVP since 2005 and is an expert in Excel Tables. He is a full time Excel consultant and runs an Excel blog over at www.ExcelTables.com He has recently published the only in depth book relating to Excel Tables: A Complete Guide For Creating, Using and Automating Lists and Tables 👉 Join Our Academy Online Excel Course 👉 Show Notes: https://www.myexcelonline.com/podcast/006-excel-tables-with-zack-barresse-excel-podcast-from-exceltables/

Episode details come from the podcast's RSS feed.

Embed this episode

Ready to play

006: Excel Tables with Zack Barresse

0:00 46:25
of matches

Transcript Auto-generated, may contain errors

This is the MyExcel Online Podcast, episode number six. You would have to have an insatiable curiosity and a very high tolerance for pain. 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 Mikoludis.

Good day, guys, and welcome to the Microsoft Online Podcast with your host, John Mikalutas. In today's episode, we have an Excel guru who was an expert in Excel tables. He started using Excel in 2003 and quickly fell in love and had a strong desire to learn more. He's been contributing to the online community since 2004, and he's been awarded the Microsoft MVP title every year since 2005. He's a full-time consultant focusing on Excel and related technologies, where he makes people's lives easier and more automated. He's been a technical editor for the Missing Manual series of Excel books for the 2007, 2010, and 2013 edition.

Let's welcome to the show, Zach Barisi. Hi, Zach, how are you, buddy? Good, good. Thanks for having me, John. How are you? Good, thank you. Now, I've told our listeners a little bit about your background. Tell us more about yourself personally and your journey into becoming an Excel expert. Yeah, so I'm a husband and father of five in Oregon, here in the US. I started using Excel, just dabbling in it in some of my jobs back in 2001, 2002. Then I started using it a little more frequently and I got to a point where I needed some help and I just couldn't figure out what I needed. And so I exhausted a lot of time searching online and found MrExcel.com.

the help forum, which I got an answer to my question very simply, which was what I was looking for was the VLOOKUP function. And I didn't know how to word it. I didn't know how to say it. I just tried to explain it as best I could. And two very nice folks came and helped me. You know, it was little pieces of help like that that kind of got me started, you know, using the forums more and more. And pretty soon I was able to start answering some of those questions. And it was just kind of a slow process of learning. And, you know, to get to the point where I am today, basically started by getting help and then slowly learning to help people.

So that's kind of how I got started. And that was back in 2001, you said, when you first started? That was in 2003 when I first started online forums. Yeah, I dabbled with Excel before that. But the most I'd ever used was the sum function and I found conditional formatting, which I thought was the coolest thing since sliced bread. And do you remember the two guys that helped you out? Are they still around?

On the forums? Yep, it was a fellow by the name of Iridium and Seabrine. I never actually found out who Iridium was. It's his online moniker, but Seabrine is just a really nice guy. He's Canadian, super smart. I know Seabrine's still around. I still talk with him every once in a while. I've, of course, gone on to kind of expand my online Excel community to different forums. In 2005, I started up vbexpress.com with Antroy. There's a ton of other sites that I found, and it's really a great community. There's a lot of great people all over the place, and a lot of them from back in my early days, I do still keep in contact with. Smitty was another big one from MrExcel.com, and him and I are still very good friends today.

spoke with him just this morning. Yeah, I had Chris McGraw on the first show and he says the same thing that the way that he got into learning Excel was through forums and through MrExcel's forum. So for you guys out there, the URL is MrExcel.com and on the left-hand side, you can click on the forum section and find it there. And I think it's grown exponentially, hasn't it, since you've been there 10 years ago? Yeah, it really has. It was already big when I first joined. They had already expanded from what they called their first forum to their second forum of their forum 2.0 when they upgraded all the software. I don't remember exactly when he started it. Bill Jelen is the man responsible for starting it. Yeah.

Yeah, it's grown. There's millions of questions getting answered every year on that forum. It is a very, very busy forum. And the nice thing about it is everything's archived. So if there's any question you have, you can do a web search, even a domain search, and pretty much find whatever you're looking for if you need help. So that was your way into Excel. And now you're a full-time consultant. What does that involve? I've been doing it kind of on the side for several years. I just started doing it full-time.

A couple of years ago, my business partner, who was my coauthor on the Excel tables book, Kevin Jones, we recently just started up our own business, Tia Automation Professionals, which is so new, our website isn't even up yet. I think it depends on who you ask as far as what we do. Kevin and I started our own company because we work with various people over the interwebs and we both think very similarly. And we both hold ourselves to a very high standard and we want to make sure that we really get what our clients need rather than just what they want. A lot of people will just give the clients what they want.

and not really explore further and give them really the best solution. And Kevin and I really want to give the most and the best that we can to really show people that there's a lot more that you can do than what you normally think. And that's always the hard part, though, is knowing what the technologies can do. Awesome, awesome. I'm running another website right now, which is exceltables.com. And I started that mostly because of the book. It kind of all came together kind of at the same time. So I'd been wanting to write a book for a long time. I tech-edited a few books. I'd been a contributor on several books with some other people I knew, like for Bill Jones, the Spreadsheet of 25, for Tracy Serstad, some of her books.

and then check it out around the Missing Manual series. I wanted to author for a while, but I didn't want to do the same thing that everyone had been doing. I didn't want to just be a generic Excel help book, you know, plenty of books on pivot tables and charting and BBA. So I thought, why not do it on tables? And, you know, I've been using tables quite a bit. I did a lot of the Excel templates for Microsoft. So when you open Excel 2013 and you click File New, about half of those templates that you see were built by myself. The other half were by Daniel Ferry, who's another Excel MVP, just a hell of a nice guy. And he runs Excel Hero Academy. He's quite the genius. He kind of puts me to shame.

There are so many smart people out there, so many smart Excel guys. And, you know, I think I know about 80% of Excel's functionalities, but there's people out there that know 110%. It's just amazing how much they know. And so now, so you're an expert in Excel tables, okay? Tell us a little bit how you became an expert in Excel tables. Well, it started with using tables frequently and mostly because I didn't want to. And that was with the template work for Microsoft that I was doing. And one of the mainstays that Microsoft wanted when building these templates was...

to not only have beautiful looking templates, but have working functioning templates that kind of highlighted the newer functionalities of Excel. So they wanted to, where we could table data, they wanted us to use tables. Where we could use slicers, they wanted us to use slicers. We could use the timeline, they wanted us to use the timeline. And just things like that. It wasn't a complicated template, but it was something that featured.

you know, the functionality that Excel has. And it was, it's really, it was a really interesting process building those templates for Microsoft. But I think that's probably where I started really having to kind of knuckle down and use tables a lot. I'd use them sparingly before that. And they're really frustrating at first because there's no real clear roadmap and the rules aren't clear either as far as how you have to use tables. So after using them for quite a while, I started seeing a lot of questions online and among, you know, my peers, the other Excel experts that I associate with, you know, relating to tables and, you know, people love tables because they love how they can make the data look great. They have the, you know, the nice.

alternating row shading. There's nice built-in design styles for them. You know, you put in a formula once in one column and you're done. You don't have to put it in again. They've got great calculated column features. It's a lot of nice features, but you know, there's just, there's some things missing. And I was feeling a lot of those questions and things that I had found out in my trials and errors with using tables in the work that I was doing along with the templates that I was building. So it was just a lot of trial and error for me figuring out, you know, what tables do under certain conditions, you know, and how they save formulas and, you know, things like that. And, you know, along with answering those questions, someone, I don't remember who, I think it was Bill Jelen started calling me, I think he called me like the table king or something like that.

And it just kind of hit me. And that's when I knew that I wanted to write about tables because it's something I knew a lot of. And I thought it could really help people. You know, I was talking to a program manager at Microsoft. She told me once that in their metrics, she said that only about 1% of Excel users use tables. And this was a couple of years ago. I think it was about the release of Office 2013 or so. And it really got my attention that this really prominent feature that, you know, when you click on the insert tab on the Excel ribbon, it's like the second button from the left. You know, it's right there. There's a menu group on the home tab devoted to table styles where you can easily create, you know, a table. So they're really kind of putting this forward and, you know, you get data from SQL server and it comes back on a table.

There's a lot of places where tables are being used natively, and Microsoft's really pushing it, because it really is great technology. And just so few people are using it, because it's scary, it's unknown, and there's a lot of things that people just don't know about tables, and they don't know enough about it, so I think they stay away from it. I think really getting the information out there was, I think it was just something that I needed to do, knowing what I did about tables. And Kevin, as soon as he heard that I wanted to write a book about tables, he was all in. He'd been wanting to work with me for a long time, and he's a really great guy. He's very happy-go-lucky. He's just one of the nicest guys I think he'll ever meet. One of the things I love about him, he's very honest. If I'm going down...

a wrong path or, you know, if I was writing a chapter in the book and he would just come back and say, you know what, I don't, I don't understand a thing you're saying. You get sidetracked, don't you? You're getting into your own zone. Definitely, definitely. Oh, that's awesome. It was a, it was a really good collaborative, you know, synergistic project to work with Kevin on and he brought a lot of energy to it and a lot of the, you know, his technical expertise and it really made it really enjoyable and I think, you know, ultimately I think just made it just a better product in the end, so.

Because there's no manual out there within Microsoft that talks about Excel tables. You can press the F1 help button and there's something on there. They bring it out and they expect you to work it out. Yeah. And I always found that really interesting. And there's a few websites on Microsoft help websites for Excel that goes over tables. And, you know, they don't really go over the small intricacies that really make up a table. You know, what are the rules that govern?

how things work. And there's, there's certain functionality that if you don't know about it is either really confusing or it's really going to bite you in the butt. Like, you know, for example, unique column header names, you know, you cannot have the same, two of the same column header names. It's just one of the rules of tables. And if you duplicate it, Excel will automatically change the text and append the number two to the end of it, just like that. And it, it won't alert, it won't give you a pop-up, you know, it's just, and it's little things like that, that people hate and, you know, and I hate, and it's just, it's very frustrating when you're, when you're trying to work on something and, you know, when you don't notice something like that and you could go, you know, hours or days or weeks or months before noticing it.

So it's one of the pitfalls of tables. I mean, it's for a good reason, but it's definitely frustrating. Now for our listeners, what is an Excel table for those people who haven't used it before? This is always an interesting question. To me, an Excel table is a way to store data and it lives on the grid. So it lives on the worksheet. So it's a place to store data that has certain rules to it and affords you certain features such as styles and design, like alternating rows, alternating column, colorings, easy filtering, easy sources for pivot tables and things like that.

It's a way of storing data or managing your data on the worksheet. And you don't have to have lots of rows of data. Now, I use Excel tables with three or five rows of data just because making that data set into an Excel table by pressing Control T. And it gives me the options of all the different styles and making it look beautiful. Just that, the blue style or the red style of putting color into your data is worth using Excel tables. One of many features. And you don't have to have heaps of rows of data. You can have three or four rows of data.

put in an Excel table, it looks good, it brightens up a dull spreadsheet. Absolutely, absolutely. And that's one of the really nice features about Excel tables is you can, in short order, make your data look extremely professional with just a couple of clicks or keyboard shortcuts and you're done and your data is transformed and it's still your data, but it looks amazing. And the nice thing about table styles is they change with the themes. So, and that's always something very important when building templates or files that other people use is something that will adapt to.

everyone's computer and everyone's like if you also want to put an excel table on a presentation like a powerpoint you can just copy paste into powerpoint and it keeps those formats and it just makes it look nicer and also it doesn't have to be values you can just put words and you know putting that on a slideshow in powerpoint it looks much more professional definitely use tables whenever you can and the shortcut is control T and press OK and you get it straight up there. The other way is to click inside your data and go to insert and table. So it doesn't take that long to create a table. OK, now in what situations will you use an Excel table? So I think there's a lot of situations where I would use tables. I use tables in almost every vial I use in Excel now. For example, one of the recent projects I've been working on is a financial model for life insurance and we've tabled everything. So the client originally came to me and he said he wanted one place for universal inputs and he wanted to make his data look good.

And then in the course of the conversation, I knew it needed to be dynamic as far as the number of rows to grow and shrink. And that formatting needed to grow and shrink with that data set. And any formulas that looked at that would need to grow and shrink. So that's why we want tables for everything, because they're just so dynamic. And we could use structured referencing no matter how big it was or small. And some of the tables are very small, and some of the tables are really large. Yeah, I think I use tables for everything, from sources of data validation to sources of pivot tables to financial data, as far as tracking expenditures, revenue, things like that. I think that one of the nice things that I do like about tables is not only are they dynamic,

but they're highly portable and easily referenced across any worksheet just by naming the table. So, you know, all you gotta do is type in the name of the table and your list of columns comes up in a little dropdown, which we call IntelliSense. And just, it makes creating formulas easy. It makes formatting easy. It just makes life easier. And then I always love the one-stop shop for putting in formulas.

enter formula once in a column is to set it and forget it. I only have to put a formula in one time and I never have to change it. Exactly. And that's the beauty of it. And also the other thing I like is the total row option. So when you go into the table tools and click in the radio button, total row, you click on that and it gives you a sum row right at the end of your table. And it's got formulas in it. It's got dropdown boxes. You can drop down and it gives you different functions like sum count.

and it's dynamic as well. So you add data and it gets updated and you don't need to put a formula in there because before Excel tables, you would have to put a formula in there, wouldn't you? But now that formula is embedded in the table, correct? Yes, it is. There's a number of formulas that you can have a choice from showing. If there's numbers in the column, Excel will default it to a sum function. You can do average count, count numbers, min, max, standard deviation, variance. And then you can always choose from any of the other functions that you want as well. From the dropdown, we'll always give you, they'll put in the subtotal function, which is nice as a default behavior for tables because the subtotal function will ignore filtered rows. So if you filter a table,

the total row by default will only show you those calculations for the visible rows. Yeah, the total row is really, really handy to have. You know, you can have separate formatting from the rest of the tables. You can format it any way you want. You can customize the functions. It's really handy.

And that should be a default. I don't know why Microsoft came out and you had to select it. I think it should come out as default. As soon as you create a table, it gives you a total row and you can put in a function there and just ignore it. Yeah, that would be nice. There's a few things that tables don't do by default. I think one of the biggest bugaboos is when you first create a table, there's no way to name it at the same time. You can select the range and you can specify whether you have headers or not if Excel doesn't already recognize it. But you can't name it at the same time. You have to go back to the design tab.

and click on that table name text box in the ribbon and change the name there. And I've heard more complaints about that than I think anything when creating tables. And also when using a pivot table, I always tell people to use an Excel table because it puts it into a structured reference. And that's one of the really nice things about tables. And that lends to doing formulas with structured references. You can use meaningful names. And if you told me what the formula, what is equals A2 times B2, I would have no idea. But if I looked at the formula and I saw equals cost times quantity.

I would know exactly what that formula did. And that's one of the nice things about tables is, you know, whether it's just a formula or you're referencing it in a pivot table source is you have that meaningful name that you're given it and it means something and it makes it a little easier to use. I did a blog post once and I think it was about eight different uses of an Excel table. I'm sure there's more, but just on top of my head, there were eight different uses. I'll link that post to the show notes of this podcast. Everyone should be using Excel tables. It should be mandatory to use Excel tables when you have data in your workbook or downloaded from an external data source or from an ERP system. Click inside that data, press Ctrl T, and it automatically knows the range correct. So it captures...

everything and you have a table. Yeah, exactly. Now, what are the benefits or efficiency gains of using Excel tables? So there are a lot of benefits. I think some of the biggest benefits are where it comes to formulas. And it's almost a double-edged sword too, though. The formulas are nice because Excel saves everything into the XML of the file and it uses what they call a calculated column. That's what gives you the ability to enter a formula in one column into a cell of one column and it autofills to the entire column no matter whether it...

rows or shrinks, and that formula will always stay in that column. And it does that by saving it to the XML file. I think that's probably one of the greatest features, but there's a few downsides with it. And it can be a little confusing on how Excel treats that, the rules that it uses to either enter that in or change that. You know, and that's one of the things that we tried highlighting in the book is how those calculated columns work. Because I think once people understand how they work, they'll be less afraid of using it. But I think that's probably one of the best features of tables. The other is just a great way to organize your data and, you know, make it look professional very quickly. You can use it for a lot of different sources. And one of the things that I use it for is a dynamic named range.

to instead of, you know, before we had tables, if you wanted a dynamic list, so for example, a list that would grow and shrink as you added data to it, or like data validation, like an in-cell dropdown with a list, you know, when you click the dropdown arrow next to the cell. So I will use a table for that. And a lot of times it's a one column table and I'll name a range. I think you can get to it from the formulas tab with the name manager button, or I use control F3 as the keyboard shortcut to get the name manager up and then just click new, select the table column, name it whatever you want. It has to be a unique name, so you can't name it the same as the table.

And then you can use that named range as data validation. And you can add to that table or take away from that table, your data validation dropdown list will go with it. It just makes it really easy for creating like forms for people to fill out. Like if you're creating a template where you want to control the flow of data of what choices users have. I mean, it could be a dropdown for anything. And there's ways to do like dependent dropdown validation with tables.

The one you said with the named range and the data validation, I use that all the time. And it's awesome. Just makes the drop-down list dynamic. And what I'm going to do is I'm going to do a blog post about that so our listeners can see. Because it takes a couple of steps. It's a little bit tricky. But once you get it done, it's very dynamic. And I think your colleagues will love that. Okay. What is the weirdest thing you've seen done? Using Excel tables or Excel in general? Oh, gosh. Many things, yeah? Throughout your time?

I don't know if I could say that they're all weird. I mean, I've done, I guess, enough weird stuff in Excel from a simple grocery list to my favorite movie listings to checking account checkbook. That's nerdy than weird. How about a cool, something cool. It doesn't have to be weird. Something cool or funky, yeah. Yeah, so I met this guy who worked for Microsoft and he told me this story. I love this story. It's about how he got hired with Microsoft. It was 20 some odd years ago and he was aspiring to work at Microsoft and he had his fantasy football league built in Excel and he built this huge model solely for fantasy football.

And he showed it to them at Microsoft. And during his interview, they wanted to see some of his work. And they loved it. They absolutely loved it. And he was hired. He got the job. And he attributes it to his fantasy football template that was built solely in Excel. And I think that was probably one of the greatest stories I've ever heard about uses for Excel. Wow, wow. I know there's many stories out there. As you said, from simple things like grocery lists to fantasy football. And you add in there some VBA and you can do whatever you want. You can literally make it.

A big machine, yeah? That's what it is. It's a big machine. Now, okay, so you've talked about your book, Excel Tables, A Complete Guide, Lists and Tables. What can our listeners expect from this book? I think this book can be summed up into just a guide for tables. And I really think the audience is any Excel user. I think it can be read by the basic Excel user and they can pick up a lot of things. I think it can be read by the expert Excel user. And to everyone I've talked to, I basically said, I guarantee if you pick up the book...

There is at least one thing in that book that you can learn because there is some really wild stuff in that book. And a lot of it is just little small bits and pieces of information that very few people know. And it's very confusing for the experts, for those people who are fairly versed in Excel. Well, I think most of the book you might already know. There are definitely some tips and tricks that I would safely assume that you wouldn't know.

And then of course, for those advanced users, the BBA section, there's some great routines that we've come up with. For example, like the bulk insert into table. You know, when working with BBA, it's very limiting and confusing working with tables. There's some great routines, examples that we've got in that book and more that we were posting to the website and the blog for all the BBA lovers out there. But I think there's something for everyone in the book and it was really written to a general audience. So I don't know that there's one specific user group or user type that we kept in mind when writing it. It was just, we just wanted to get, you know, all of the tips that we could get about tables out there to everybody. And it's such a wide variety of people that use tables because it's really, it's everybody. It's from the very beginner to, you know.

The person doing secretarial work might just be formatting things or trying to make things look good. Or it might be someone who's importing and exporting from databases and someone who's trying to do dashboarding or someone who's trying to get data from criteria-based data sets and wanting to filter the data and maybe copy and paste it or do whatever they want with it or prep it for the web. I mean, there's a hundred things that people can use it for. So I think it's really written for a generalized audience. Yeah, there are many experts out there.

in Excel and they don't use Excel tables because they don't know its possibilities. And with this book, I think this is the manual into Excel tables and it's the first of its kind out there. And I think it's just going to open people's eyes on the possibilities of using Excel tables. Yeah. And that was always one of the interesting things that there isn't another book.

like it. There isn't another manual like it. There is nothing like it anywhere. And I mean, there's blog posts here and there and there's maybe a white paper here and there, but there's nothing that really condenses everything into one place like this book. And that's really kind of the goal of what we wanted. We wanted everything in one place. And we thought that a book would be the best place for that.

because it was, no one had it. And that's one of the things I really loved about it. And I didn't want to do some generic book, you know, or put something together with power tips and tricks or, you know, best keyboard shortcuts ever. You know, I wanted something relevant and tangible that people could actually use and stood out on its own. And I knew it wasn't going to be a bestseller, but I wanted it to be very target oriented and specific. And I think it is. Yeah, I think in a couple of years, it's going to become a bestseller when people get to read the book and it opens up their eyes on the possibilities of using Excel tables. What's the best way for our listeners to get a hold of this book?

I've tried to combine all of the information on the ExcelTables.com website. So if you're looking for the books, it's sold at several places. If you're looking for the cheapest, it's probably the MrExcel.com bookstore because you can get it from Amazon. You can go to Amazon.com or Amazon.co.uk. So all of those links, put it on ExcelTables.com slash book and it lists everywhere where you can go to get the book. All right. So you can find the links to this book by visiting the show notes on MyExcelOnline.com slash podcast. Now let's take a short break and we'll be back with your favorite Excel tips.

Can you analyze this spreadsheet using Excel pivot tables? Sure, if you can show me how to. Introducing the Xtreme 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.

All right, so we're back with Zach Barisi's Excel favorites. So Zach, tell our listeners, what's your favorite keyboard shortcut and what does it do? Well, for a long time, I would have to say control T because it creates tables, but I've had to modify my answer a little bit. I was giving some training courses here within the last couple of years and the most popular keyboard shortcut that I have run across that people jump out of their chairs for is control Z. To me, it's just so simple that control Z is undo. It's just amazing how many people...

don't know control Z is just so easy on a keyboard to undo. So I think it's probably a tie between control Z and control T. No, good, good answer. And control Z, I know what people like because it's very close to the control button and also people make mistakes. The opposite of control Z, the redo is control Y for people that didn't know that. All right, what's your favorite Excel tip? The multi-confirm cell entry, which is another keyboard shortcut. And it's something that I use probably every day. And it's, you know, when confirming entering data in a cell, whether it's text numbers or formulas, you would enter to confirm that. Well, if you have more than one cell selected.

If you type in your formula or entry data and you hold the control key when you hit enter, it will confirm it for all of the cells in the selection. And that has saved me countless amounts of time. And I think that's probably my favorite tip. That's an awesome tip and one that I use and definitely one that you guys should start using. All right. Now, what is your favorite function and why? And don't say VLOOKUP. My favorite function is not VLOOKUP. Good. It is very popular. I understand that. It's one of the first functions that I ever learned. But if I had to pick one, I would say would be index just because of the versatility and the amount of use it gets from me. I know it's...

simple and you know it's there's not much fanfare with it and it's you know it's not very exciting but there's there's so many ways that you can use that function it's it's ridiculous so i think i would have to pick index and it's not just index by itself you can mix and match you can match there you can do many things with it so i think that's why it's so robust yeah absolutely you know it's generally mixed with other formulas you rarely you're ever gonna see it used on its own you know usually the pairing function is the match function there's just there's so many ways and so many combinations to use that function it's just so versatile all right now what's one excel adding that you're always using i am always using actually two add-ins uh constantly well Three. I don't know if I can narrow it down to just one. That's fine. I would say, I think it depends on where you live in Excel the majority of the time. If you're living in, you know, Excel in the grid most of the time, ASAP Utilities is probably the best add-in I think I've ever seen. The creator, Bastian Minsk, is just a really, really nice guy. And it's a free tool. It's asap-utilities.com. I would recommend it for absolutely every single Excel user. It's got so many things that have saved me so much time. It's ridiculous. It is an awesome add-in. It's free. He takes donations, but it's free for everyone to use.

It's truly an awesome add-in. There's a couple other add-ins that I use for VBA development. I use MZ Tools, written by another Microsoft MVP. I think it's a Visual Basic MVP. And there's a free version for the VBA. And then Rob Bovey's Smart Indenter for the VBA. I love that thing. Both free add-ins. You can find them with a simple web search. All right. Now, what I'm going to do is have links to everything so our listeners can't get that. All right. Apart from my own blog and your blog, which is awesome, what is a great online resource for learning Excel? There's several different websites that I think are really good places to learn Excel, I think.

Where you go depends on how active you want your participation to be. The forums, I think, are an excellent resource, and there's several good forums available. I like to frequent MrExcel.com. VBAExpress.com is another good one for anything VBA. There's TechSupportGuy.com. It has another great section for business applications, which includes Excel. I frequent ExpertsExchange.com, which is Experts-Exchange.com. There's a lot of great resources there. There's also other great websites where you don't have to participate in a forum, but in the way of blogs.

The blogs that I frequent are John Acampora's blog at excelcampus.com. He's got some really great information and everything he does looks beautiful. It's ridiculous. There's John Peltier's blog, which is peltiertech.com. In fact, Kevin and I just did a guest blog post there last week also. Yeah, I read that. I read that today. It's about structured referencing. Yeah, understanding structured references. You know, it kind of goes over with formulas and...

a little bit with data validation, not much in charts because John already had a really good blog post on using tables as charting source data. So I just referenced his link, which was, it was a really expansive and really great blog post. And there's, I mean, there's several other websites. There's Deborah Dalgleish's website, contextures.com. Yep. There's logs. There's myexlonline.com. There's myexlonline.com. Absolutely. There's excelguru.ca. Yeah, Ken Pohl's blog.

He's got a lot of great stuff there. He's always keeping everyone up to date with anything Power Query. If you're looking for anything performance-wise in Excel, Charles Williams has a blog at fastexcel.wordpress.com. And Charles is just the king of getting the most out of Excel functions and speed. I mean, he's even got the speed tools added that he sells on his website. But anything for efficiency and speed, you definitely want to go to his site. He's always pushing the edge of what can be done and how to really get the most speed out of Excel.

There's a spreadsheet guru, which is Chris Mackerel. That's right, Chris Mackerel, yeah. Him and John Acker-Portable. Everything those two guys do just looks absolutely amazing. It's almost sickening to see their stuff because it just always looks so good. And it's really great information too. It is, it is. No, it's just that people have been around Excel just haven't updated their sites. They've been writing for so long and they've been busy with consulting that they haven't spent time in updating and making the website look good.

And that's because I started about 15 years ago. But now it's so easy to do that. And people like Jonathan Porra and Chris McRae from the spreadsheetguru.com, these people are coming up with nice looking websites, even Aminda at My Online Training Hub. So yeah, it's the time to get in to Excel blogging and make it look pretty. All right, now your Excel book is a great resource. It's a great manual for Excel tables. Now what's one of your all-time favorite Excel books?

Oh, gosh, that is a really, really good question. I would have to say, I mean, it really only boils down to one book for me. The all-time greatest Excel book for me has been Professional Excel Development, which I think they have a second edition now. There are a lot of Excel books out there. Pretty much anything written by either Bill Jelen or John Walkenbach or Tracy Surstad is worth the investment, in my opinion. Tom Mertis has a really good book that was just recently published. But as far as all-encompassing, the Professional Excel Development, it's about a four-inch thick book. But if you're looking to develop an Excel, it...

is literally the Bible or anything you want to do to make Excel do what you want it to do to automate it. It's not for the faint of heart. It's definitely an advanced book, intermediate to advanced. For beginner to intermediate, I wouldn't try it. And for that audience, I usually point to something like PowerXL, Bill Jelen, or PowerXL tips and tricks that has just a ton of great stuff in it. And then of course, you know, if you want to learn about tables, there's a really cool book on Excel tables that I hear is really good. Yeah, definitely. All right. So now you can find the links to this book and everything we've been chatting about today, including tutorials on Zach's favorite shortcuts and tips by visiting myxlonline.com.

podcast. All right. So we have come to the end of the show and we have a weird Excel scenario for you, Zach. Okay. So let's say that it's my first time ever seeing Excel. So I'm a beginner and I need to learn the most important features of Excel within only one week because I've got a job interview coming up. With all the Excel knowledge that you know today, what would be the quickest way for me to start learning Excel? That's a really good question. That is a really weird scenario. I would say, first of all, you would have to have an insatiable curiosity and a very high tolerance for pain.

I think some of the things that I would pass on to someone in that scenario is obviously tables because, you know, very little time investment and you can have really great data looking, you know, at the end of it, but just really explore the ribbon. You know, tables is a really good base, you know, and from there you can create charts and it's a, it's very easy to kind of a couple of clicks, create a good looking chart and then sell styles. Styles is a really good tool that's really underutilized, can be extremely beneficial. Good answers. And if I had to learn Excel using the online resources that we have available today, which way would you lead me towards? Probably a mix between forums and websites. And I could probably boil it down to about a half a dozen websites. There's MrExcel.com for good interactive. There's Experts Exchange for searching for good high-level answers. There's obviously the, you know, MyExcel Online has some great videos. Excelcampus.com and the Spreadsheet Guru.

probably some of the most informative websites that are keeping up to date with relevant Excel information. It's easy to disseminate, you know, it's easy to understand, it's easy to learn, and it's easy to take what you see and then put it into action. And then the online forums are really interactive because, you know, you make a post, you know, and it's more of a Q&A than anything else. So I think that's probably where I would start. And do you think it's easier learning Excel today than, say, 15 years ago when you started out?

Oh, absolutely not. No, I absolutely don't think so. I think it's much tougher today. There's so many more tools available. The base Excel is predominantly the same. The calculation engine hasn't, it's overgone a couple of overhauls, but it's kind of like just putting more packing on the mule. You know, there's more things that you can do in the same flavor of application. And sure, some things have gone by the wayside, some older technologies, but by and large, there's so much to know in this application. There's so many things that you can do that it's almost impossible for one person to really know it all. I would say, you know, you definitely want to get good at what you do.

And then, you know, if you want to learn more, absolutely. And there's plenty of methods for you to explore and learn more about the application. But yeah, I think it's much tougher now than it was, you know, 15 or 20 years ago to learn the application just for the sheer amount of information that, or sheer amount of features that is in the application for someone to learn. It's sometimes, you know, when people jump in, it's, you know, when they jump in with both feet, they almost get lost because there's so much going on. So it makes it tough, I think. Yeah, yeah. I hear you. I hear you. But then you have the online tools like YouTube, forums, blogs. You have more blogs now than you had 15 years ago. So, you know, it's very true. Yeah, there are.

And that's one of the really nice things about the time that we're living in is the information is so prolific. And that does make it a lot easier to learn the application because there are so many avenues where you can go to find help to kind of wade through this huge application. The amount of stuff that's online is just growing exponentially. And I think it's great because it really puts the information out there for people. It makes it easily accessible and easy to disseminate that information and for someone to understand it and actually put it to use so it's able to use for them in whatever they're doing.

which could be anything. But yeah, like the YouTube, Mike German, who has the Excel is Fun YouTube channel, he's got, I mean, thousands, I think it's over 3,000 videos on Excel help tips and tricks. So, I mean, if you want to put the time in to find it and, you know, when you find a few great resources, I think it can really make learning Excel substantially easier. And also with the online courses, we specialize on different features like formulas, pivot tables, charting, macros. Online courses for me is another avenue where our listeners can learn Excel pretty quickly. Yeah, and the nice thing about that is they're hyper-focused. And I really...

like that about training videos is, you know, you find the subject that you want and someone who puts together a good video on it, it's going to have a really, really robust amount of information on that topic that you're looking for. I always like to think you get what you pay for. Sometimes it pays to just find a good site and a good course and maybe purchase a few courses because they're so detailed. Yeah. And YouTube, the quality of the picture, the sound is not there. You don't get the support.

And you have to be online to access YouTube. But with paid online courses, you can actually download the videos and keep them on your desktop forever. And whilst you're flying on a business trip, you can actually view the videos. So you don't need to have access to the internet. It's also more structured. If you get a course, a paid course, it's more structured. If you go into YouTube, you get lost, you get all these pop-ups. And then you start in Excel and you end up watching your football team and the best goals of 2014. Yeah, yeah. And I'm watching some, you know, super minds of...

Exactly, exactly. All right, buddy. Now, what's the best way for our listeners to connect with you, Zach? Probably through my website. You know, I monitor it all the time. You can go to exceltables.com. There's a contact us. There's a web form. There's an email address. All right, fantastic. Mate, Zach, thank you for your time today. You've given lots of value to our listeners, and I think they're going to start using Excel Tables straight away. And yeah, I appreciate your time today. Yeah, thanks very much for having me, John. I really appreciate it. Cheers, Zach.

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 46 minutes long.

When was this Learn Microsoft Excel with MyExcelOnline episode published?

This episode was published on November 19, 2015.

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.