Learn Best Practices with Excel and Google Sheets - David Benaim

I find Excel is very weak at spreadsheet protection. Google Sheets actually has more functions than Excel. Personally, I tend to go towards Power Bi. Once I'm using Power Query and Power Pivot, I usually tend to switch to Power Bi on the supply. In terms of I do like the new feature that Excel implemented for check performance. I think Excel has done a really good job with that. Google has got most of what makes tables great, but not everything that Excel does. And with Google you just use the most up to date stuff and you don't have to even worry about that. If I have any need for simultaneous editing, then I will just use Excel.

Will Excel ever die?

No.

Do you think AI will build the models for us in the future?

There are many, many, many things that Google does better than Excel and there are many, many things Excel does better than Google. For me, when I'm picking up a client project that's to do with spreadsheeting, I will foreign.

Welcome to Financial Modelers Corner where we discuss the art and science of financial modeling with your host, Paul Barnhurst. Financial Modeler's Corner is sponsored by Financial Modeling Institute. Welcome to Financial Modeler's Corner. I am your host, Paul Barnhurst. This is a podcast where we talk all about the art and science of financial modeling which distinguish 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 Benheim. 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.

I met Kate Moss once. That's maybe not 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 do with Power Query. But any one of those I'm just immediately like, no, yeah.

I, 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 one's.

Yeah. 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.

Yeah, I know what you're talking about. Yeah, I hate that when you get every, every sheet month has 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?

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.

Yeah, it'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?

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 10 years and my parents are from France and Morocco. So when I did my passport application, passport renewal form a few years ago, it spans 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 want 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. It's kind of like Doctors Without Borders, but instead of saving lives, we saved 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. Accelerating, essentially.

Okay, got it. So we obviously like Cambodia, if We've been there 11 years now and sounds like it's home these days.

Yeah, yeah, yeah, yeah, it's pretty good. It's pretty good. It's not somewhere I ever imagined I'd be, but when I moved here, I really enjoy the lifestyle, the expat community and multiple other factors about it. And 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.

Yeah, that obviously plays a role, 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. No, see, Deloitte. Deloitte, right, Deloitte, yeah.

Correct, yeah.

So what interested you in the field of accounting? Why. Why did you start there?

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 I did that. And then after the internship, they offered me a job. So I just stayed in that and took it from there.

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.

Yeah, yeah, yeah, yeah. Pretty much, yeah.

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 was, how was that experience? What was that like?

I guess at the beginning it was a little bit intimidating because when you work through one of the big guys like Deloitte and 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 lookup. I. I'd heard of the tool, but I didn't know how to use it. I couldn't do a sumif. 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 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 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.

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.

Yeah, exactly.

I need to pay the bills. And then as you get in a little while you start to get a little more selective. Like I don't have to accept everything.

Yeah, yeah, exactly. Yeah.

I'm curious.

Soon after.

Yeah, exactly. So did you implement like a certain accounting system or just multiple different ones or.

QuickBooks was the one I was primarily focused on, but I was doing others as well like Xero or Sage. But yeah, okay. But 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 it. And data modeling stuff. Yeah.

Okay. 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?

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 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 comparison article between the two products that has ever been written in the entire world. And I'm probably. I'm an Excel vp, 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 MVPs, I'm definitely the one that produces most Google Sheets content and is the most up to date with what Google is doing as well.

Yeah, and 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 is different 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.

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 70 in each one, Google's are better than Excels. They 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.

Yeah, 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, 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 otherwise I'm going to use Excel. What's. What's your take? Having obviously spent a lot of time with both.

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 like en masse 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. Are definitely benefits outside of the collaboration space where Google Sheets is better than Excel.

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.

Yeah, yeah, yep, yep, agreed.

What are some of those functions that are different between the two? You had 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?

Oh, 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 got countuniqueifs. Why not? Also great. You also have the flatten function which kind of emulates the unpivot in power query. That's as a function you do if you are using array formulas. You do have to wrap them inside an 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 lambda, 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 no one in the world knows every single function that Excel does, 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 name 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's 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. 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 regextract, 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 equals. Sparkline is a function in Google but not Excel which has a lot of benefits. For example, you can have the 10 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.

Yeah, that, that does sound cool. Sounds like there's some, you know, great use cases. The count unique. I'm fully with you. You know I, I get to teach all the time. Count day rapid and unique versus if there was just account unique or you know, account ifs. Yeah, similar to an average ifs or a min or a max. Yeah, counties with the unique. Right. A unique count or whatever. Those would all be th. 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.

Yeah. And also a little known fact, but you know everyone 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. Google also has 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, but 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 even more complicated. Whereas Google can do all those things in one function with quick query, which is really, really nice.

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'd built and took me a day or two. It wasn't bad. I like, I mean on the whole I like 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 sure as we talk about functions, what about data entry? What are some of kind of the differences there between the features you have in Excel and Google Sheets?

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 do 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 to 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 OK and then people will press OK every now and again. Because often, even for me, like sometimes I forget what stuff I'm not supposed to enter. So I will just put those to block myself from accidentally doing it and then I can click OK to overwrite it when I need to. But I find that stuff is so much better. Data validation, way better. In Google you have autocomplete data validation list. Excel just implemented that last year. But Google's had it for ages. Google sheets as 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, but I kind of like data validation for things like place names and files. That is you. 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 p people's names and then we'll 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 data entry, on mass data entry in particular.

Yes. So I'm curious talk 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. 90, 95% 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?

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, is Power Query. And for me, for the last 10 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 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 Power Pivot 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 Power Pivot, I usually tend to switch to Power Bi unless the client 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 that will kind of push me towards Google. 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 the man on the moon. So just they should auto update. They just should and various things like that. I find the charting engine particularly pivot charts and slices 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, 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'd have to go through a pivot table to before you build your chart. And you also got card visuals and table visuals which are similar to Power BI for any Power BI users out there. Then Google has those built in charts whereas Excel you have to do workarounds to get that working FP and 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 FM 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 are a qualified financial modeler and one of the best ways to do that is through the FMI's 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 www.fminstitute.com podcast and use code PODCAST to save 15% on when you enroll in an accreditation today. Yeah, what about size limitations? I've heard if you're dealing with large data that Google tends to struggle. Is that what you found?

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 cell 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, but no, that is, that is a point worth mentioning that it does slow down.

Yeah, 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. 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.

Yeah, yeah.

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 you're not going to go into Power Bi or Look 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. We have this many rows and columns. Nobody uses all of them because your data can't. Yeah. Process all that.

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 put some formatting in. Therefore it makes your file sizes 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.

Yeah, I can tell you definitely know the new. The new features and you have fun with those. And 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. This was my view. I didn't care for it really. Tables never really cared for them. 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 they're. Maybe they're different.

Yeah, 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. I mean, myself, I mean, I think the tables themselves just have a terrible name because as I, as I was saying before, like, I, I went from being a, 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, but I cover everything. I cover Teams, PowerPoint, Word, 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 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 release table. I am delaying the webinar because the, the, I'm delaying the podcast because this is game changing. So I finally brought up, you're like.

Can I please wait till this comes out? Because I want to talk about it. Yeah, let's do that. Because that's big.

Yeah, 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, 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, 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, et cetera. So Google took it away 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 dropdown. 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. So this is kind of 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 the things and then the other group. And of course, our spreadsheet super users, us data modelers, financial modelers, know that that's going to come with issues. Excel doesn't know that if something's 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 two 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, is subtotals on the group by view. I imagine that's something that will come with time, but that. That's something that I would have naturally imagined they did. Here's something I love. Dynamic arrays don't break inside tables and Google sheets. Yay.

Yes. Amen to that one. I hate that about table in Excel.

And I kind of get it like if you're using like a filter function or 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 or the. The transpose function like there. There's loads of use cases for it.

You know the one I want the most, which is really funny and 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, oh 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 one I use the most, which I'm going to guess 99% of users. That's not the formula they want for table, right?

Yeah, yeah, no, I'm with you. I use, I use RAND between more than RAND array because of those limitations, I think.

But yeah, yeah, and I probably should use RAND between. But I've just gotten the habit of using RAND 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.

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 I guess it's not that dynamic. But definitely for the use cases I would use dynamic arrays in a table. I think they should work in Excel data.

Yeah. What could you do? So that is helpful. Sounds like they definitely did some good things. There's some opportunities to improve. 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 vs Excel for a project? 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?

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 Autocomplete for data validation lists came out in Excel, which was about a year and a half ago. What that means is that you can start typing and then it will give you the thing you're typing. So 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 in a lot of the systems that I build. So. So Google would win over that. Now that Excel has. Autocomplete has made me choose Excel 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 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 that 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 actual projects.

I rarely ever use Excel Online 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, 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, 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?

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 and 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, 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 and Excel doesn't, notifications. So I can set my Google Sheets to send me an email every time someone edits, edits certain cells or something like that.

Okay, great. Well, no, 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 a side. 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 one or two, 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 the year, your life. Which one you picking?

Excel. Okay, because Power Query.

Circular references or no circular references in models?

No. No.

Secular VBA or no vba?

No vba.

Horizontal or vertical model?

Vertical model.

Okay. Dynamic arrays in your models. Yes or no?

Yes.

External workbook links? Yes or no?

Not with equals, but through Power Query. Yes.

Yeah. I had that same answer from Ken Pulse the other day. He's like, 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. Named ranges versus no named ranges.

No, I don't use name ranges much.

Okay. Will Excel ever die?

No. No.

Do you think AI will build the models for us in the future?

No. Judging by how Copilot is right now, probably not.

Alrighty. So use of sheet cell protection in your models?

No, because it's too limited and I, I find myself being angry about it.

So it sounds like if you're in Google, you'd use their version of it.

Yeah.

Okay. And then do you believe financial models are the number one corporate decision making tool?

Yes.

Okay.

Yeah.

When people say no, I always ask what is? And one person said, politics.

Yeah, fair enough, fair enough.

And I was like, I can't argue with that one.

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.

Yeah. And we got the US politic election 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 X match.

Xlookup from the list. I'll say xlookup for sure. No brainer. But I would say power query merge queries.

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.

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 off that pivot table. Named ranges as I said named functions I like in Google but I use table structured references but I don't to name my range ranges. I find it's very clunky when you copy and paste across workbooks. Will AI 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 it's implemented in Excel and PowerPoint and Word I just think is just not very good and they're 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 sheet protection here's what I do as a workaround. I put data validation and then I set the data validation for all my is go to special select. Let me start that one again. Use of sheet 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. Now 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 then I'll press OK. The reason 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 a notification. They can't do it unless they so happen to guess the password. However, it means that 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.

Well, I will remember 62.714 if I get one of your files.

No.

You'Ve now let everybody know. So I'm sure everyone who uses your files is listening and they're going to be. I'm going to mess with David here. So we're, 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 it 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? Maybe use it for some of their modeling?

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. Except because once you're familiar with all the overlaps, it's good to know what the differences are. Also, where Google does really well is that if you save an Excel file on Google Drive, then you can edit it in Google Sheets, but it still keeps 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. Appreciate that. Appreciate you joining us today. Really enjoy getting to chat with you. And I definitely learned a few things I didn't know about Google Sheets. Some of them I are 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.

Yeah.

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 or they're more right for that job, whatever you want to call it. I appreciate you kind of sharing some of those differences and helping people understand better. And 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?

So yeah, come to Cambodia. I'll show you around. You can follow me on LinkedIn. So yeah, find me on LinkedIn. Dave Benayam. And my YouTube channel is under my name, David Benayam 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 of spreadsheets, but also other stuff, Power bi as well. So you can follow me on that. And then I, I, 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, 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.

Cool. Well, thanks for the invite. Yeah, it was, it's really exciting to, to be here on this podcast with you and yeah, yeah.

Thank you for joining me. I appreciate you being on the show.

Nice to meet you and the beard. 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@www.fminstitute.com podcast and use code podcast to save 15% when you enroll in one of the accreditations today.