Learn Best Practices with Excel and Google Sheets - David Benaim
Welcome to the Financial Modeler's Corner (FMC), where we discuss the Art and Science of Financial Modeling with your host Paul Barnhurst.
Financial Modeler's Corner is sponsored by the Financial Modeling Institute (FMI), the most respected accreditation in Financial Modeling globally.
In this episode of Financial Modelers Corner, host Paul Barnhurst sits down with David Benaim, an Excel MVP and data consultant based in Cambodia. They delve into a comparative analysis of two of the most popular spreadsheet tools—Excel and Google Sheets. The discussion covers the strengths and weaknesses of each platform, providing listeners with insights into when and how to use these tools effectively in their financial modeling and data analysis work.
David Benaim is a chartered accountant and Excel MVP who has made a name for himself as an expert in both Excel and Google Sheets. Originally from the UK, David now runs a data consulting firm in Cambodia, where he specializes in Excel training and data solutions. With a deep passion for spreadsheets, David is uniquely positioned to discuss the nuances of both platforms, making this episode a must-listen for anyone looking to optimize their use of Excel or Google Sheets.
Key takeaways from this week's episode include:
Key differences between Excel and Google Sheets, including tables, functions and data entry features.
The strengths of Google Sheets in collaborative environments and its data validation capabilities.
How to decide whether to use Excel or Google Sheets for different types of projects.
Insights into the new table features in Google Sheets and how they compare to Excel’s tables.
David’s tips on getting started with Google Sheets for those accustomed to Excel.
Follow David:
Website - https://www.xlconsulting-asia.com/
LinkedIn - https://www.linkedin.com/in/davebenaim/
Follow Paul:
Website - https://www.thefpandaguy.com
LinkedIn - https://www.linkedin.com/in/thefpandaguy
YouTube - https://www.youtube.com/@thefpaguy8376
Follow Financial Modeler's Corner
Newsletter - Subscribe on LinkedIn-https://www.linkedin.com/build-relation/newsletter-follow?entityUrn=7079020077076905984
Sign up for the Advanced Financial Modeler Accreditation Today and receive 15% off by using the special show code ‘Podcast’.
Visit www.fminstitute.com/podcast and use the code “Podcast” to save 15% when you register.
In today’s episode:
[01:21] - Introduction to the episode and guest David Benaim.
[03:52] - David shares his unique global background and career journey from Deloitte to founding his own firm in Cambodia.
[07:00] - Discussion about David’s transition from working at Deloitte to starting his own business in Cambodia and the challenges he faced.
[14:38] - In-depth comparison of functions unique to Google Sheets, like “COUNTUNIQUE”, “FLATTEN”, and others not found in Excel.
[23:35] - Criteria and scenarios where Google Sheets is the better tool, particularly in collaborative environments and data entry tasks.
[30:25] - A look at the newly implemented table features in Google Sheets and how they stack up against Excel’s tables.
[38:54] - David shares his decision-making process for choosing between Excel and Google Sheets based on project needs.
[43:56] - Quick-fire questions where David shares his preferences on various Excel and Google Sheets functionalities.
[49:31] - Tips and resources for users looking to improve their skills in Google Sheets.
[50:47] - Final thoughts and how to connect with David Benaim.
Full Show Transcript
[00:00:00] Host: Paul Barnhurst: Tell me about that worst model you have seen in your career, that kind of horror story, so to speak.
[00:00:06] Guest: David Benaim: Every month is a different worksheet, and you have to go through like horrible steps to combine it together, which you can't do with Power Query. But any one of those, I'm just immediately like, no.
[00:00:16] Host: Paul Barnhurst: A lot of truth to that. Would you start a business? Oh, can you do this? Sure. I'll figure it out if you're offering me money.
[00:00:23] Guest: David Benaim: Yeah. I mean, this will come to a shock as a shock to a lot of people, but Google Sheets actually has more functions than Excel.
[00:00:29] Host: Paul Barnhurst: What are some of those functions you're using all the time that are in Google Sheets that are not in Excel?
[00:00:35] Guest: David Benaim: My favorite one is Count Unique. And then you've got count unique ifs. The main thing that is missing in Google Sheets, and I think there are a while off implementing something similar is Power Query. I mean, don't get me wrong, I love Google Sheets, but as I said, I'm an Excel MVP and I'm actually also the guy that like, is crazy about staying up to date with all the newest features that if there's a lot of data entry, then I will use Google.
[00:01:05] Host: Paul Barnhurst: Welcome to Financial Modelers Corner, where we discuss the art and science of financial modeling with your host, Paul Barnhurst. Financial Modelers Corner is sponsored by Financial Modeling Institute. Welcome to Financial Modelers Corner. I am your host, Paul Barnhurst. This is a podcast where we talk all about the art and science of financial modeling, which distinguished guests from around the globe. The Financial Modelers Corner podcast is brought to you by Financial Modeling Institute. FMI offers the most respected accreditations in financial modeling. I'm excited to welcome on the show David Benaim. David, welcome to the show. Thanks. We're really excited to have him here. He's joining us from Cambodia, and we're going to start, as we do with every episode. First question we like to ask. Tell me about that worst model you have seen in your career, that kind of horror story, so to speak.
[00:02:05] Guest: David Benaim: I met Kate Moss once. That's maybe what you're expecting. Anything where you've just got, like, every every month is a different worksheet and you have to you have to go through, like, horrible steps to combine it together, which you can't do with Power Query. But any one of those, I'm just immediately like, no.
[00:02:24] Host: Paul Barnhurst: Yeah, I hate when every month is a different worksheet. And then they summarize it all together. Right. Because none of the formulas drag across. You got every column has a totally different formula in the row. I hate that. So I'm I'm with you that ones.
[00:02:40] Guest: David Benaim: Yeah. And then sometimes you got different. You haven't got consistent columns in each in each worksheet either. Sometimes. So you have to do you have to match them up. And even Power Query won't pick that up sometimes.
[00:02:52] Host: Paul Barnhurst: Yeah I know what you're talking about. Yeah. I hate that when you get every every sheet month is its own sheet and then the columns don't match and you're trying to bring it all together to summarize it. And yeah, using Power Query, doing whatever to try to clean it all up. Never, never fun. So what was your kind of key takeaway or learning from dealing with those kind of models?
[00:03:16] Guest: David Benaim: Well, I've I've started when now when I take on projects like that, I will just like, you know, check that that's what they do. And if it is, I'll just start very early on by comparing the column names in each of the tabs and going running through a process to do that, to make sure they're identical. And if they're not, then before I get started with anything, I just force them, the client to give me the same. The same column types.
[00:03:43] Host: Paul Barnhurst: Yeah, that's a smart move is just get it fixed up front from the client versus having to deal with it all on your end. Makes a lot of sense. Yeah. So can you tell our audience about yourself, your background kind of how you ended up where you're at today?
[00:03:57] Guest: David Benaim: Yeah, sure. So, I'm from a variety of places. I was born in the Bahamas. I grew up in Gibraltar. I currently live in Cambodia. I lived in the UK for ten years and my parents are from France and Morocco. So when I did my passport application passport renewal form a few years ago, it spanned four continents, so I'm a little bit from everywhere. I started my career as an accountant, working in London for Deloitte, and I became a chartered accountant with the UK Accounting Institute. And then after that I decided I wanted a bit of a change. So I came to Cambodia initially on a volunteering placement. It was with an organization called accounting for International Development. So it's kind of like Doctors Without Borders. But instead of saving lives, we save balance sheets. And, I enjoyed that. So I enjoyed my time here in Cambodia. And within that I got, I got into Excel a little bit more that way and then started a, started a company here. So I've been living here for 11 years in the capital, Phnom Penh. And I run a, I run a data consulting firm and I do excel trading essentially. Okay.
[00:05:13] Host: Paul Barnhurst: Got it. So, we obviously like Cambodia if we've been there 11 years now and sounds like it's home these days.
[00:05:21] Guest: David Benaim: Yeah yeah yeah yeah it's pretty good. It's pretty good. It's, it's not somewhere I ever imagined I'd be, but when I moved here, I really enjoy the lifestyle, the the expat community and multiple other factors about it. And it's. Yeah, it's kind of quite easy for a foreigner to come and do business here. You can legally set up a company, you can legally kind of get your visa and your working permit renewed, which is a lot harder in other places.
[00:05:50] Host: Paul Barnhurst: Yeah, that obviously plays a role in being able to have that work permit and all those things that go with it. Well, good. I'm glad you enjoy it there. Sounds like it's fun. First question I wanted to ask you about is obviously you started your career in accounting. You worked for one of the big four know see Deloitte.
[00:06:07] Guest: David Benaim: Deloitte, right. Deloitte. Yeah. Correct. Yeah.
[00:06:10] Host: Paul Barnhurst: So what interested you in the field of accounting. Why did you start there?
[00:06:15] Guest: David Benaim: Ha. Well, I applied in my penultimate year of university. I applied to be an actuary because I'm from a maths background. And Deloitte were like, well, we're full in that department, but do you want to try accounting instead? And I was like, sure sounds great. And then, and then I did that. And then after the internship, they offered me a job. So I just stayed in that and took it from there.
[00:06:44] Host: Paul Barnhurst: So basically it came down to they they gave you an opportunity. You wanted to work. Sounded like you'd give it a try versus being an actuary.
[00:06:52] Guest: David Benaim: Yeah, yeah yeah, yeah. Pretty much yeah.
[00:06:55] Host: Paul Barnhurst: No, I get it. We've all been there with certain things. Fun. You've been doing your own thing now. So I think over a decade. Right. You transitioned from the corporate world to running your own business. How is how was that experience? What was that like?
[00:07:10] Guest: David Benaim: I guess at the beginning it was a little bit intimidating because when you work for one of the big guys like Deloitte in London, you're working in such a narrow, narrow field. So I was doing not only was I doing tax, but I was doing specifically corporation tax, and I was doing specifically that for the real estate sector. So I was so specialized in that after doing that for five years, that whenever friends or whoever would ask me about anything accounting related that was beyond that, I was just like, I don't know, that's that's not my thing. So going from that into what I was initially doing was implementing accounting software, and working with a wide variety of clients. I guess it was it was quite a transition to need to go into leading my own meetings and creating my own projects and trying to find organizations that did it. And at the time I shot it to say that my Excel skills were very mediocre. I could not be look up. I'd heard of the tool, but I didn't know how to use it. I couldn't do a sum if I could do pivot tables and some other formulas. But yeah, I just wasn't very good at Excel. And then it was during that transition that I, I started doing Excel more and more. Initially, what happened was I offered some Excel training courses just to get people in the market to know who I was, and then that went pretty well. And some of the people were like, hey, can you teach us some more? And we'll pay you this time? And when you're starting out in a new place, you just don't say no to any paid work. So I was like, yeah, sure. And then I just decided to buy a bunch of books based around Excel and watch a lot of YouTube channel videos, and then just started claiming to be an expert until I was.
[00:08:58] Host: Paul Barnhurst: A lot of truth to that. Would you start a business? Oh, can you do this? Sure, I'll figure it out if you're offering me money.
[00:09:05] Guest: David Benaim: Yeah, exactly.
[00:09:06] Host: Paul Barnhurst: I need to pay the bills. And then as you get in a little while, you start to get a little more selective, like, oh, I don't have to accept everything.
[00:09:14] Guest: David Benaim: Yeah. Yeah, exactly. Yeah. I'm curious. What? What you're.
[00:09:18] Host: Paul Barnhurst: After. Yeah, exactly. So did you implement, like, a certain accounting system or just multiple different loans or.
[00:09:25] Guest: David Benaim: QuickBooks was the one I was primarily focused on, but I was doing others as well, like Xero or Sage, but yeah, okay. But then I kind of fairly quickly transitioned out of doing that kind of work, and started doing like, I guess, working with data and building dashboards and systems for organizations. Really got into data modeling stuff. Yeah. Okay.
[00:09:53] Host: Paul Barnhurst: So speaking of modeling, you shared a little bit about how you learned Excel. I know you're a big fan just of spreadsheets in general, but you use both Excel and Google Sheets. You wrote an article kind of talking about the difference between them. Why? Why have you learned both kind of versus just focusing on one? What's been the, interest in sharing a lot about Google Sheets as well?
[00:10:13] Guest: David Benaim: Well, I'm a big gossip, so I love to spread sheet in whatever, whatever manner that may be. I guess I was working with a lot of smaller organizations and they were on Google infrastructure. Plus I do I do a bunch of volunteer projects, for example, stand up comedy. Hence my terrible jokes. And I organize events with a lot of people, and we have spreadsheets that we share and we use Google Sheets because especially back in 2015, Excel was it was impossible to work collaboratively with Excel. So now it's difficult but not impossible. But back then, of course we use Google and so and so then as I was using Google for various different things, I just decided to learn it at the same time as I was learning to be an Excel super user, I also became a Google super user. Yeah. And then I was really interested in the differences. As you say, I wrote an article. I even think that is the most in-depth depth comparison article between the two products that has ever been written in the entire world, and I'm probably I'm an Excel MVP, so I'm very close with the Excel team. I don't have the same status with Google. Google don't seem to have a similar program, but out of all of the Excel MVP's, I'm definitely the one that produces most Google Sheets content. And there's, you know, the most up to date with what Google is doing as well. Yeah.
[00:11:42] Host: Paul Barnhurst: And we'll, we'll put that, article in the show notes just so people can find the link to it. I know you did that with, Lance Rubin. Yes, and I remember, yeah, there's a comparison kind of the Venn diagram, as if things how many functions they have showing overlap a lot of different things to help people understand where they're similar and also where they're different.
[00:12:03] Guest: David Benaim: Yeah. I mean, this will come to a shock as a shock to a lot of people, but Google Sheets actually has more functions than Excel. Google's on 514, Excel's on 512, and moreover, the ones that are in one but not the other, of which there are 60 to 70in each one. Google's are better than Excel's. The ones are in Excel, but not Google Sheets. I very rarely use them. The ones that are in Excel but not in Google Sheets I rarely use, but the ones that are in Google Sheets but not Excel. I actually use quite a lot. And yeah, we'll talk about them in a little bit I'm sure.
[00:12:37] Host: Paul Barnhurst: Yeah, we'll we'll definitely get into that. I was talking to a friend the other day and he just joined a company that they're Google now. There's no Excel. And he's like I'm actually starting to really like modeling in Google, like the import range and some things that aren't in Excel. So we'll jump into that some more. But I'm not surprised to hear that because I've heard others say that before. And so let's start with, you know, you often hear from a lot of people that basically Excel is the superior spreadsheet outside of collaboration, right? That's typically what people say if they're using Google Sheets. Well, it's way better for collaboration. But but otherwise I'm going to use Excel. What's your what's your take having obviously spent a lot of time with both.
[00:13:22] Guest: David Benaim: Yeah. I couldn't disagree more. I think that there are many, many, many things that Google does better than Excel. And there are many, many things Excel does better than Google. So for me, when I'm picking up a client project, that's to do with Spreadsheeting, I will choose the best product for the job. And yeah, and I think that there's Google is a lot better at, at like on mass data entry. Google is a lot better for kind of working with live systems as well. And there are various other ways that Google just outshines Excel. The gap is being narrowed, for sure. Excel's recent releases have a lot of them have mimicked what Google has had for years, and equally, Google's recent releases, a lot of them have mimicked what Excel has had for years. There there are definitely benefits outside of the collaboration space where Google Sheets is better than Excel.
[00:14:19] Host: Paul Barnhurst: Yeah, and that's what I've heard, is both of them are doing a lot to close the gap between each other. You see a lot of copying, so to speak, even though they may implement it in a little different way. There's each has their own nuances, but kind of closing that gap where the functionality is different.
[00:14:35] Guest: David Benaim: Yeah. Yep yep yep. Agreed. What are some of.
[00:14:38] Host: Paul Barnhurst: Those functions that are different between the two? You'd mentioned earlier that in general, you appreciate more the differences that are in Google Sheet versus Excel. So what are some of those functions you're using all the time that are in Google Sheets that are not in Excel?
[00:14:54] Guest: David Benaim: Well, a good question. My favorite one is Count Unique. Who doesn't want to count unique function who has never in their life thought? This is something that I need. And then you've got count unique ifs. Why not also great. You also have, the flatten function, which kind of emulates the unpivot in Power Query that says a function you do if you are using array formulas, you do have to wrap them inside a equals array formula, but fairly easy to do that. And you have ways to import data. For example, import range that you mentioned earlier actually takes data from another Google spreadsheet. You can't with Google Type equals, and then click on a cell in another spreadsheet. That won't work, but use the import range function. But I actually prefer it because it gives you more robustly, like the whole range that's linked, rather than just cell by cell that breaks easily. Then you have Google's take on Lamda, which is so much better named functions they call it. So rather than how Excel does it, which is, hey, we just introduced the 514th function in our program, and I bet you're curious about it, even though you don't know what the word means And the fact that there are no one in the world knows every single function that Excel does.
[00:16:10] Guest: David Benaim: But yet you have to type this in and understand what it does, and then know that it'll result in an error in the worksheet. But if you happen to copy and paste that into our named range editor, something that's been around for decades and decades, then it will do something different. Who would ever try that? Whereas Google created something in the menu called Named Functions, which gives you a wizard and you type in the name in one box and you type in what the arguments are in one box, and type in how the function works without needing to type in lambda, because it does that for you. And and then. Yeah. And then you can save it. You can also import named functions from one worksheet to another one. So long story short, I end up using named functions in Google. But I just don't in Excel because it's too much work. Then, other stuff that we talked about, Microsoft taking what's been in Google for years and Google taking what's been in Microsoft for years. Microsoft got in beta at the moment three regex functions, which have been in Google for a while, and what they can do is they can extract something based on a string that's using pretty complex stuff.
[00:17:19] Guest: David Benaim: Like, for example, let's say that you have an email address somewhere in this sentence. It could be at the beginning, anywhere in the middle or at the end. Then you could use regex to extract that or replace it with something else. So that's regex tracked regex match and regex replace. And then you have you. And then just last week Excel announced that they were launching the two, I guess just last week. We're making this video in early July. But just last week, Excel announced that there were two translation functions equals translate and equals detect language that also have been in Google for a while. And I use them regularly in Google, particularly when I'm looking at Cambodian language spreadsheets, and I want to just translate them to English. You know, it's like Google Translate. It's not going to get it right 100% of the time, but good enough to get the gist and really, really, really useful. So those are some things that are coming. Equal Sparkline is a function in Google, but not Excel, which has a lot of benefits. For example, you can have the ten year transaction history of the US dollar versus British pound exchange rate all in one function to give you a sparkline chart. So that's pretty cool.
[00:18:35] Host: Paul Barnhurst: Yeah, that does sound cool. Sounds like there's some, you know, great use cases that count unique. I'm fully with you. You know, I get to teach all the time. Count a rapid and unique versus if there was just a count unique or, you know, a cow. Gifs. Yeah. Similar to an average IFS or a min. Or a max. Yeah. Countess with a unique right, a unique countess or whatever those would all be. Those would be helpful. So I'm with you. There's definitely some that would love to see similar between the two versus the way they've implemented them.
[00:19:09] Guest: David Benaim: Yeah. And also a little known fact. But, you know, everyone went crazy about dynamic arrays when they go released in Excel. I was using them in Google way before all of them. The unique function, the filter function, the sort function. You also have a sort end function. So give me the top five sorted by sales or something. Which is pretty good. And then Google has the query function, which is, the most powerful function that exists in either Excel or Google Sheets. Essentially, you can write SQL code inside the function because, yeah, I get frustrated with the filter function in Excel because it's it's, you know, you have to manually copy and paste your headers first, otherwise it doesn't work. I don't know who decided that there's no option to include headers. That would just be nice to have an option. It just makes it non-dynamic. If you have to copy and paste manually the headers, and then if you want to reorder the columns, then it gets you a more complicated. Whereas Google can do all those things in one function with query, which is really, really nice.
[00:20:09] Host: Paul Barnhurst: Yeah, I've seen the query function. I'm not a big Google user just because I've never had to in my career, but I've definitely heard of some of those things where it's better and I did have to do once where I had to translate a model. Took a little while to get used to because the training I was doing for the customer, their entire company was on Google, so I had to take this big model I had built and took me a day or two. It wasn't bad. I liked, I mean, I on the whole, I liked the experience. There are definitely some areas that took some getting used to for sure. Yeah, but it seemed pretty similar for the most part. So I can relate a little bit. But I haven't had to do a a lot in Google. So I'm curious. We talked about functions. What about data entry? What are some kind of the differences there between the features you have in Excel and Google Sheets?
[00:20:55] Guest: David Benaim: Yeah. So good question. I find Excel is very weak at spreadsheet protection. I know that it's been there for years, but it's so clunky. You have to it's very counterintuitive. You have to select the cells you want unlocked and mark them as unlocked, and then go to this other feature that's in a completely different place and say you want to protect your sheet, and even after you've ticked all the boxes, Excel still won't make you use grouping or use filter if you didn't have a filter there before. Just so many things are grayed out. Google's version of that it will. It has two things. The way they will spreadsheet protect is by user entry. So email address. So you know who has access to it and who doesn't, which is pretty cool. And the other one is and the one that I use a lot, it's that you don't actually want to choose exactly what to lock, etc. you just want to prevent someone from accidentally typing over a formula. So what you can do is you can just, you can just set it to give them a warning. So if they try typing over, it'll just give them a warning saying, you're not supposed to type over, but do you want to do it? Press okay. And then people will press okay every now and again. Because often, even for me, like, sometimes I forget what stuff I'm not supposed to answer, so I will I will just put those to block myself from accidentally doing it.
[00:22:20] Guest: David Benaim: And then I can click okay to overwrite it when I need to. But, I find that stuff is so much better. Data validation, way better. In Google you have auto complete data validation lists. Excel just implemented that last year, but Google's had it for ages. Google Sheets is date pickers. Why not have date pickers? They're really, really great checkboxes. Just came to Excel, last week as well. I believe they just got released. They've been Google for ages and ages and ages and they're really, really great. With Google you can also have they call them smart chips, where they're kind of like data validation for things like place names and files. That is, you just start typing and then it will search your entire Google Drive for your file name, or you just start typing a place name and it will search the entirety of Google Maps, which we all, of course use on a daily basis. And then it has that locked in or people's names, and then it will go through email addresses as well. Excel doesn't have anything that comes close to these things. So yeah. So for those reasons, I find Google is pretty good at, at data entry, en masse data entry in particular.
[00:23:32] Host: Paul Barnhurst: Yeah. So I'm curious, talked about many of those strengths of Google Sheets. Our audience is particularly people that are building a lot of financial models in general. And, you know, in the financial modeling community, I would say small companies, you see some Google, you've seen a little bit of equals more for reporting and for startups. But right, 9,095% is still Excel. What what would you say. Where does it make sense to maybe consider modeling in Google Sheets building financial models? Or do you think Excel is probably still the way to go there? What's your thoughts on that?
[00:24:08] Guest: David Benaim: Yeah, so that's a good question. The main thing that is missing in Google Sheets, and I think they're a while off implementing something similar as Power Query. And for me, for the last ten years of my career. Power Query has become something I can't live without. And, I use it in everything I use it in most kind of financial modeling. I will use that over using complex formulas, because I find it's just more robust and it's less likely to break, and it's harder to overwrite accidentally. And and so yeah. So I find that Power Query is a big thing. Power pivot is also not in Google and probably also not going to be for a while. Personally, I don't use Power Pivot as much as I use Power Query, but I still use PowerPivot and some of my financial models are dependent on that. If I am going to go that route personally, I tend to go towards power BI. Once I'm using Power Query and PowerPivot, I usually tend to switch to power BI unless the client yet doesn't want me to. But yeah, but those are. Those are things that might inhibit me from using Google Sheets in terms of obviously we talked about collaboration way better in Google than Excel. But yeah, so so that will kind of push me towards Google.
[00:25:24] Guest: David Benaim: Pivot tables automatically update in Google Sheets. That's great. Right. The rest of I get why when pivot tables were created by the Excel team in the early 1990s, the computing power probably can handle auto updating and pivots. But, you know, we have more technology in our in our pockets today than what put them out on the moon. So just they should also update. They just should and various things like that. I find the charting engine, particularly pivot charts and slicers, much better in Excel, so Google can do those, but they are better in Excel, whereas Google has a couple of charting features worth mentioning. For those of you who are doing financial models that lead to output charts with slices, which a lot of mine tend to. I wouldn't say it's the crux of the financial models that I build, but they often do. And you've got something like you can tick on the aggregate button in a chart in Google, which means that you don't have to go through a pivot table to, before you build your chart. And you've also got card visuals and table visuals, which are similar to power BI or any power BI users out there than Google Ads. Those get built in charts, whereas Excel you have to do workarounds to get that working for.
[00:26:39] Host: Paul Barnhurst: A guy here. And as you know, I am very passionate about financial modeling and the Financial Modeling Institute's mission. I have been a huge fan of the FMI for years, and I was super excited when they decided to sponsor the Financial Modelers Corner. I recently completed the Advanced Financial Modeler certification and loved the entire experience. It was top notch from start to finish. I am a better modeler today for having completed the certification. I strongly believe every modeler needs to demonstrate they are a qualified financial modeler, and one of the best ways to do that is through the FMI program. Earning the accreditation will demonstrate to your current and future employers that you are serious about financial modeling. What are you waiting for? Visit Wfmh institute.com backslash podcast and use Code podcast to save 15% when you enroll in an accreditation today. What about, size limitations? I've heard if you're dealing with large data that Google tends to struggle. Is that what you found?
[00:27:53] Guest: David Benaim: Yes, yes. Google's official limit. They increased it last year to 5 million cells. So Excel has 1 million rows and 65,000 columns, whereas Google's is a sell limit. So if you have a narrow worksheet you can go further down. However, yeah, I would I would agree with you. When you get close to very, very large data sets, then Google does slow down and struggle a bit with that. Personally, it hasn't been as much of an issue for me because I wouldn't say I have that many spreadsheets that are absolutely huge. Apart from stuff that I take into power BI, in which case it's just loading into the data model behind the scenes anyway. But no, that is that is a point worth mentioning that it does slow down. Yeah.
[00:28:42] Host: Paul Barnhurst: Yeah. And you know, when you speak of. Right, Excel advertises the million rows and the 60, 16 or 60,000, whatever it is the column. Right. The huge size. But have you ever for fun, I tried to write a sequence that would cover the entire thing. Runs out of memory, right? It's. It can't process that size even though you have the ability to work in all of that. And frankly, if you have that many columns in your data, you got a bigger problem.
[00:29:08] Guest: David Benaim: Yeah, yeah. Yeah.
[00:29:10] Host: Paul Barnhurst: Right. So there are definitely cases that I've heard of that I see that where people talk about it slowing down. So you do need if you're dealing with large data and data, you you're not going to go into power BI or looker or whatever your BI tool may be. Yeah. Then you probably do want to be in Excel, but at the same time it's a little bit of a selling point to we have this many rows and columns. Nobody uses all of them because your data can't yet process all that.
[00:29:37] Guest: David Benaim: I do like the new feature that Excel implemented, for check performance. So it just, what it does is it just checks whether you've got any cells that are out of your regular range, that someone actually gets some formatting and therefore it makes your file size is really bloated. So I think Excel has done a really good job with that. And I, I mean, don't get me wrong, I love Google Sheets, but as I said, I'm an Excel MVP and I'm actually also the guy that like is crazy about staying up to date with all the newest features that, Excel has to offer. So I'm always playing around with the new features. And yeah, I can.
[00:30:12] Host: Paul Barnhurst: Tell you definitely know the new the new features and you have fun with those and on on both platforms. And speaking of that. Yeah. And I remember I was telling a friend I didn't realize it was out yet. He was very excited. The one that used Google all the time. I know Google just implemented their new tables before their tables were very weak at best was my view. I didn't care for it really. Tables never really cared for him, but I've now heard they've implemented a more robust table. So talk about that versus Excel, because I think tables are critical in Excel. I use them all the time. Right. If you're using Power Query, Power Pivot Copilot you have to use tables. Yeah. Let's talk a little bit about them in Google Sheets and how they compare and how maybe they're different.
[00:30:55] Guest: David Benaim: Yeah. No. Yeah I'm glad you brought that up. Tables are something that have always been the thing that is not in Google. That is in Excel. Of course Power Query isn't, but there are fewer, I guess, use cases, especially for the novice users and even myself. I mean, I think the tables themselves just have a terrible name because as I, as I was saying before, like I went from being an average Excel user to a super user, and it was only when I was reading my third or fourth book about Excel. And I've read for the, you know, 60th time, and then make your data into a table that it struck me that maybe my data isn't already a table because, you know, it's like everyone assumes that it is. And when I teach Excel, that's how I build it. I'm like, so how do you make a table in Excel? They're like, sir, it's already a table. What are you talking about? But yeah, but then you realize there's this whole world of tables which has all these features that you wish you knew from day one. So anyone listening to this, if you're not using tables, this will change your life. Use tables. Definitely. Anyway, Google sheets last month implemented tables and I write a what's new in what's new across business tech blog post every three months. And there I cover mostly Excel by covering everything I cover teams, PowerPoint words, Canva. I love Canva as well. Google infrastructure, zoom. Pretty much what most people use in the tech workplace. And I and I was going through, Google Sheets new and upcoming releases, and I and I came across tables and I was like, wow, this is brilliant. Finally, Google's release tables. So, Paul, you and I were actually scheduled to do this about a month ago, weren't we? And I was like, Google's just announced they released table. I am delaying the webinar because the I'm delaying the podcast because this is game changing. So, I finally got.
[00:32:54] Host: Paul Barnhurst: The thing you brought up. You're like, can I please wait till this comes out? Because I want to talk about it. Yeah, yeah, let's do that because that's big. Yeah.
[00:33:01] Guest: David Benaim: So, so that happened and I played with them. I made a video about them. I wrote a blog about it that I just sent for submission. I just sent for, publishing yesterday and absolutely love them. So was Google done differently? So, Google has got most of what makes tables great, I think, but not everything that Excel does. For example, you have no automated total row. Doesn't bother me too much. It's kind of a nice to have, but can make it myself really easily. The way that Google does structured references in tables is that it takes a structured reference for an entire column, and for the entire table, but Excel also has structured references for the headers that I don't think I've ever used in my life. And it has structured references for, the current row in the same in a different column using the At symbol, which Google didn't take. And for a while I was like, okay, I think I missed that. Then I thought about it longer, and I feel like that is a huge barrier to entry for newcomers to tables, because as soon as you write any formula in the same row, you suddenly get a foreign language.
[00:34:16] Guest: David Benaim: And this puts a lot of people off. Whereas Google is like, let's just keep the same cell references that everyone knows and where it really counts, I think, is the column referencing a column that you want to auto expand for your xlookup functions, for your sum functions, for your pivot tables, etc.. So Google took it where it counts two things that Google implemented that Excel didn't. One of them is setting data types. So you can set a column type to be a date, and then people have to enter a date. You can have a placeholder to show people how to enter that date. You can have a drop down. Of course, these are within data validation with Google. You can actually just set it as a column. You can set a column to be a text column, and then someone can write a phone number without the leading zero being removed. You can say column type to be, as I was saying before, these places, these names, these files, and then it's just got the placeholder that you can click on it. And it will then allow you to type in the name of a place. So that's one. The other one is the group by views.
[00:35:18] Guest: David Benaim: So this is kind of when when novice users often create spreadsheets, they decide to group things together. And instead of filling down the column, they put one group and then all of the things and then the other group, and of course our spreadsheet super users as data modelers, financial modelers know that that's going to come with issues. Excel doesn't know that if something is in a different row that actually relates to the same section. So you can fix those with Power Query and other aspects. But with Google, they're like, well, if you want to have that, then let's just give you the best of both worlds, because you can just, you can just have the group by as a view and you can still edit your data in the group by view, but then you can go back to the non group by view. And it works really well what they haven't implemented. And I'm a bit surprised about this as subtotals in the group by view. I imagine that's something that will come with time, but that's something that I would have naturally imagined they did. Here's something I love. Dynamic arrays don't break inside tables in Google Sheets. Yay!
[00:36:20] Host: Paul Barnhurst: Yes! Amen to that one. I hate that about tables in Excel.
[00:36:25] Guest: David Benaim: I know, and I kind of get it. Like if you're using like a filter function or a unique function, usually you're creating another table. So sure, why would you want that to be a table? But the text split function, which I use regularly, that you definitely want to be across multiple columns inside a table, well, the, the transpose function, like there's no other use cases for it.
[00:36:48] Host: Paul Barnhurst: You know, the one I want the most, which is really funny. I think I'm probably one of the only because I'm often creating dummy data for training, and I'm using Rand array. Oh yeah, I want Rand array to work in a table because I'm always sticking it off to the side, then copying it back into the table because I'm just trying to create some dummy data or adjust the dummy data. So it makes sense because I'm like, ah, yeah, I really should add, you know, some a little more variation to this. And so I just want to do a random variation to it. And I always have to do it off to the side and copy it back into the table. So that's the one I wish worked. That's probably the one I use the most, which I'm going to guess 99% of users. That's not the formula they want for table, right?
[00:37:30] Guest: David Benaim: Yeah, yeah. No, I'm with you. I use, I use randbetween more than random array because because of those limitations I think. But yeah. Yeah.
[00:37:39] Host: Paul Barnhurst: And I probably should use randbetween, but I've just gotten the habit of using random array all the time, so I just do it off to the side. I mean, yeah, it takes an extra second or two, but it's just annoying.
[00:37:48] Guest: David Benaim: Yeah yeah yeah yeah. Now and I just don't understand why this the Excel dev team decided specifically we are going to disable this from tables. Like what? What made them come to that conclusion? You know, it was clearly like, it was clearly like a decision that they decided to do. And I don't really understand why. Now, granted, Google Google's tables don't expand if your array expands. So the table size doesn't change dynamically in number of rows in the table, which I guess maybe it could. But yeah, that doesn't happen. So so I guess it's not that dynamic but definitely for for the use cases that I would use dynamic arrays in a table, I think they should work in Excel. Bellator. Yeah.
[00:38:37] Host: Paul Barnhurst: What could you do. Right. So that that is helpful. It sounds like they definitely did some good things. There's some, you know, opportunities to improve. But so we've talked quite a bit about the differences between them. Yeah. What I'd like to know is how do you choose Google versus Excel for a project. Like like how do you think about it? How do you recommend people think about if they're thinking, hey, I'd like to use Google Sheets a little bit more, or maybe even Excel a little bit more. How do you decide which one to use for a project?
[00:39:05] Guest: David Benaim: Yeah, so good question for me. If there's a lot of data entry, then I will use Google because of the because it's just much better. As I was saying, the data validation is much stronger particularly before before, autocomplete for data validation lists came out in Excel, which was about a year and a half ago. Oh, what that means is that you can start typing and then it will give you, the thing you're typing. So, you know, we all use this on a daily basis. Can you imagine booking a flight in, in a place where you had to scroll down through every single airport in the world. Well, until last year, that's what Excel thought you wanted to do. So it just blew my mind that this was not a feature that came in on day one. So whenever I had a spreadsheet that I was building, I would always choose Google. If people had to choose from big drop down lists, which is. And a lot of the systems that I built. So so Google would win over that. Now the Excel has autocomplete that has made me choose Excel in, in more instances than that. Because of that, however, of course with Excel you have versioning. With Google, everyone's on the same version. You don't have to worry about, oh, well, Xlookup didn't exist five years ago. So if someone if someone types if someone with an older version uses it, then it'll break. No, I mean with Google you just use the most up to date stuff and you don't have to even worry about that. I know Excel Online is is is there, but Excel Online is genuinely not there. And I think that you'll struggle to find anyone that uses Excel online only for an actual project.
[00:40:46] Host: Paul Barnhurst: I rarely ever use Excel online.
[00:40:49] Guest: David Benaim: Yeah, I end up using it because some of the newest features they released first to excel online. So when I want those to come in, then I'll go to Excel online briefly, but then just come out when I want to use it for anything of of value. And I know that Microsoft is working hard on Excel online, and I'm sure that it will get there. I don't have a doubt about that. But yeah, for now, it's not something that I, that I choose regularly. And then there's a collaboration aspect. I mean, if I choose if I have any need for, for simultaneous editing, then I will just not use Excel. Yeah.
[00:41:29] Host: Paul Barnhurst: Speaking of the collaboration, just get your thoughts real quick. Do you think Excel will get caught up there? Do you think they'll become as easy to use as Google Sheets at some point or how's that going?
[00:41:42] Guest: David Benaim: Yeah. So on paper Excel can do everything for Colab that Google can. Now you can do a cell by cell edit history. That was one of the last features they implemented a year ago. You can share a link to a range that's something they implemented six months ago. I think that's really useful. Especially when people, you know, you can use Excel on mobile, Google sheets on mobile. I just find so much better than Excel mobile. Same with Word and Google Docs. So. But in theory, again, on paper, Google and Excel can do the same things. You know, you can have simultaneous editing in an Excel desktop file at the same time. But and this is a big but you have to be in the same organization. Excel is under this illusion that people don't work together if they are in different companies. So Excel and Microsoft in general just makes it very, very difficult. Just way more hurdles you have to overcome if you are working with people inside different organizations. I mean, me for my job. I work with clients all the time. The very nature of my job is I consult with people who have big organizations and they need to give me access. So Microsoft expects them to pay for a user license for me. And some organizations are willing to do that. Of course, a lot of them are not. And and so that's why, yeah, I end up finding that Google is just much better for collaboration in that respect. Another thing that this is one thing that Google has that Excel doesn't notifications. So I can set my Google Sheets to send me an email every time someone edits, at a certain cells or something like that.
[00:43:19] Host: Paul Barnhurst: Okay, great. Well, now that's really helpful. I think we got a good, general overview of differences, how you think about using them. Now, what I want to do is we call this, rapid fire, where we have some questions we ask. And the whole idea is kind of picking aside if you had to choose between one or the other. Not the typical consultant answer. It depends, because many of these you could you could say it depends to all of them. Then at the end, if you want to elaborate on 1 or 2, we'll give you a minute to elaborate. So the idea is kind of just quickly to run through them, just, you know, pick a side and move on to the next one. So the first one, this is the first time I've asked this question. I thought this would be a fun one for you if you could only use one tool, Excel or Google Sheets for the rest of your life. Which one you picking?
[00:44:08] Guest: David Benaim: Excel. Okay, because Power Query.
[00:44:10] Host: Paul Barnhurst: Circular references are no circular references in models.
[00:44:14] Guest: David Benaim: No. No circular
[00:44:14] Host: Paul Barnhurst: VBA or no VBA. No VBA horizontal or vertical model.
[00:44:23] Guest: David Benaim: A vertical model okay.
[00:44:26] Host: Paul Barnhurst: Dynamic arrays in your models. Yes or no. Yes. External workbook links. Yes or no.
[00:44:34] Guest: David Benaim: Not with equals, but through Power Query. Yes.
[00:44:37] Host: Paul Barnhurst: Yeah. I had that same answer from, Ken Post the other day. He's like, well, which which external ones are you talking about? And I agree with you. If it's power Query, that's fine. If it's the other, you should try to avoid it at all costs. If you can name ranges versus no name ranges.
[00:44:54] Guest: David Benaim: No, I don't use named ranges much.
[00:44:57] Host: Paul Barnhurst: Okay. Will excel ever die?
[00:45:00] Guest: David Benaim: No, no.
[00:45:02] Host: Paul Barnhurst: Do you think I will build the models for us in the future?
[00:45:06] Guest: David Benaim: No. Judging by how copilot is right now, probably not.
[00:45:12] Host: Paul Barnhurst: Alrighty. So use of sheet cell protection in your models?
[00:45:17] Guest: David Benaim: No, because it's too limited and I find myself being angry about it.
[00:45:23] Host: Paul Barnhurst: So it sounds like if you're in Google, you'd use their version of it.
[00:45:27] Guest: David Benaim: Yeah.
[00:45:28] Host: Paul Barnhurst: Okay. And then do you believe financial models are the number one corporate decision making tool?
[00:45:36] Guest: David Benaim: Yes.
[00:45:37] Host: Paul Barnhurst: Okay. Yes. When people say no, I always ask what is. And one person said politics.
[00:45:42] Guest: David Benaim: Yeah. Fair enough, fair enough.
[00:45:44] Host: Paul Barnhurst: And I was like, I can't argue with that one.
[00:45:47] Guest: David Benaim: Yeah. I mean I've got we've got elections in the UK today and in France on Sunday. Ironically, I can vote in the French one, but not the British election.
[00:45:56] Host: Paul Barnhurst: Yeah. And we got the US politics elections season going on. So I get it. Yeah. Politics are big. All right. So look up function of choice and you can mention something else if they come to mind. But if you had to pick just one of them that you kind of is your go to of the group, choose Vlookup index match or ex match Xlookup.
[00:46:17] Guest: David Benaim: From the list. I'll say Xlookup for sure. No brainer, but I would say Power Query match queries.
[00:46:26] Host: Paul Barnhurst: I'm with you. That would be the one I would say as well. All right, well, any of those you want to elaborate on, is there any that you're kind of like? I'd like to give a little more context.
[00:46:36] Guest: David Benaim: Yeah, yeah, I was going through them and I was thinking some stuff. Yes. So dynamic arrays, to make to make slicer dependent charts that pivot tables don't let me make. It's like Pareto charts, box and whisker charts. You can make those slicer dependent by having a dynamic array that's linked to that pivot table, and then creating a chart of that pivot table named ranges. As I said, named functions I like in Google, but I, I use table I use table structured references, but I don't like to name my ranges. I find it's very clunky when you copy and paste across workbooks. Will I build the models for us in future. Yeah, as I said, I. I'm not the biggest fan of copilot. I. I like the copilot free version for writing formulas for me, but the one that's implemented in Excel and PowerPoint and word, I just think is just not very good in that targeting the wrong things. So I think that I think that AI is I think the AI is going to be able to do some things, but not not the mass thinking that comes behind models for a while at least. Use of she protection. Here's what I do as a work around. I put data validation and then I set the data validation for all my is go to special select.
[00:48:04] Guest: David Benaim: Let me start that one again. use of sheets cell protection for my models. So here's what I do. Instead I will use go to special and select all formulas in the worksheet. And then I'll, I'll go to Data validation for all the selected formula cells. And then I'll say set value to a decimal equal to. And I'll write something like 67.27146. And And then I'll press okay. As I do, that is because it's kind of like setting a password using data validation. So it means that if someone types over it, they'll get the notification that they can't do it unless they so happen to guess the password. However, it means that I'm I don't get any of the other constraints apart from just typing in the cell. People can still recolor cells. People can still use filters, use grouping, use all that stuff. And this one I find I use quite a lot. It's not perfect. People can still cut and paste over data validation. Who knows why they made that decision. But for me, this is much better than the built in spreadsheet protection that I think is kind of weird.
[00:49:10] Host: Paul Barnhurst: Well, I will remember 62.714 by get one of your files. No.
[00:49:18] Host: Paul Barnhurst: You've now let everybody know. So I'm sure every one of you. Your files is listening. And I'm gonna mess with David here. So we're coming up on the end of our time. Just have two questions left for you. The first I would like to ask is if somebody's listening and they're like, you know, I really want to get better at Google Sheets. I've heard that there's some things that does really well. Any advice on how you'd recommend they get started? If they want to, you know, kind of be better in Google Sheets and use it for some of their modeling.
[00:49:49] Guest: David Benaim: Yeah. so I would say have a go at it for like a couple of hours and get used to the fact that things are in different places, but they're all there. And then. Yeah, I mean, I guess I guess as I said earlier in this podcast, I'm probably the one guy in the world that has written the biggest blog posts and does the most content on Excel, Google Sheets, so you can check out my YouTube channel or, the LinkedIn post that I did recently a year ago. Because once you once you're familiar with all the overlaps, it's good to know what the differences are. Also where Google does really well is they if you if you save an Excel file on Google Drive, then you can add it to in Google Sheets. But it's still keeps it as an Excel file so you can still go between. So I find it does that quite well. And so you can still kind of be using both at the same time. And yeah, it translates things quite well. Great.
[00:50:45] Host: Paul Barnhurst: Appreciate that. Appreciate you joining us today. Really enjoyed getting to chat with you. And I definitely learned a few things I didn't know about Google Sheets. Some of them I were familiar with, and I'm sure our audience will learn some as well, because at the end of the day, it's about using the right tool for the job. Yeah, and there's this idea that it should always be Excel or always Google Sheets. And the reality if you invest some time is there's just areas where both of them are better depending on what you're doing. Are there more right for that job, whatever you want to call it. But I appreciate you kind of sharing some of those differences and helping people understand better. And yeah, last question. If our audience wants to learn more about you or get in touch with you, what's the best way for them to do that outside of coming to Cambodia?
[00:51:32] Guest: David Benaim: Say I come to Cambodia, I'll show you around. you can follow me on LinkedIn. So, yeah, find me on LinkedIn https://www.linkedin.com/in/davebenaim/. And my YouTube channel is under my name, David Ben-naim https://www.youtube.com/@learnspreadsheets, as well. So those two platforms are probably the best way to do it. And I post all the stuff that I do on LinkedIn, be my I do like weekly videos on what's new in tech or spreadsheets a lot on spreadsheets, but also other stuff power BI as well. So you can follow me on that. And then I do some blogs and do a bunch of webinars as well, so you can follow me on LinkedIn and get in touch with me that way. Yeah.
[00:52:13] Host: Paul Barnhurst: Right, great. Well appreciate that. We'll put your, LinkedIn profile in the show notes. We'll stick your website in there and appreciate you carving out some time today to chat with us.
[00:52:23] Guest: David Benaim: Cool. Well, thanks for the invite. Yeah, it was, it's really exciting to to be here on this podcast with you and, Yeah.
[00:52:31] Host: Paul Barnhurst: Yeah. Thank you for joining me. I appreciate you being on the show.
[00:52:35] Guest: David Benaim: Nice to meet you. And the beard.
[00:52:38] Host: Paul Barnhurst: Thanks. Financial Modelers Corner was brought to you by the Financial Modeling Institute. This year, I completed the Advanced Financial Modeler certification, and it made me a better financial modeler. What are you waiting for? Visit FMI at https://fminstitute.com/podcast/ and use Code podcast to save 15% when you enroll in one of the accreditations today.