Explore Excel’s remarkable rise

Garreth Hanley:
This is INTHEBLACK, a leadership strategy and business podcast, brought to you by CPA Australia. Today, we're talking to Neale Blackwood, CPA. Neale is an Excel expert who spends most of his time showing businesses how they can use Microsoft Excel better. He also writes monthly Excel tutorials for INTHEBLACK, which he's been doing since 2002, and he's the host of CPA Australia's Excel Tips podcast. In my conversation with Neale, we talk about the history of spreadsheets, how a program designed for the Apple Mac became synonymous with Microsoft Windows and became the de facto name for all spreadsheets. And we talk about the evolution, new integrations, and the future of Excel. Okay, Neale, so let's start at the beginning. From my understanding, spreadsheets started with a program called VisiCalc. Is that right?

Neale Blackwood:
Yep. I think that came out, might have been, in the late seventies.

Garreth Hanley:
I think it was 1979.

Neale Blackwood:
Well, there you go. Okay. Yep. So it came out in the late seventies and then eventually it handed over to Lotus.

Garreth Hanley:
So VisiCalc, from my understanding, was released on the Apple Macintosh, and it was one of the main reasons why people started to buy personal computers.

Neale Blackwood:
Correct. Yeah, I think they were just buying the computer just to get a spreadsheet.

Garreth Hanley:
And before the spreadsheet, what were people doing?

Neale Blackwood:
Accountants used to have their big open books that would have multiple columns in it, and I used to do it too. I was in a university press, University of WA Press, and they were selling books, and I had the huge opening book. So I'd be writing on the left hand side, I'd be writing the book title, and then across all of the columns, I would have things written in and so the spreadsheet was just mimicking that and just making it a whole lot easier to make a change because I used to write in pencil until everything balanced, and then I'd write over it in pen once it did balance.

Garreth Hanley:
Wow.

Neale Blackwood:
So the spreadsheet was replacing the old paper, multi column, huge opening books that you'd have.

Garreth Hanley:
And when did you move from the books into using spreadsheets Neale? Did you start early on?

Neale Blackwood:
Yeah, I started with Lotus and that was my first... Oh, actually, no. I don't want to tell a lie. The first time I used a spreadsheet was, again, I went to what was called University Theatres at UWA and they... Because the university had a policy. They pretty much bought Macintosh's, and so they had the very first incarnation of Excel. So they had the Excel on their Macintosh, and so I was using Excel on a Mac in the late eighties, I think it was, and that was my first exposure, if you like, to a spreadsheet, was Excel on a Mac.

Garreth Hanley:
And Excel was first released on the Mac, yeah?

Neale Blackwood:
Yeah. So Excel was actually developed by, I think it was Office, was developed by Microsoft for the Mac initially, and they had a moratorium, so they couldn't actually release it on Windows because Windows was coming in then on the PCs, and there was a moratorium, I think, of about a year before they could release Excel on Windows. But the problem was, the Mac kept getting delayed. So in the end, I don't think there was much of a difference between Excel getting released on the Mac compared to getting released on Windows. So obviously Windows is where Excel's really taken off now. But yeah, it was actually developed for the Mac, so it had the graphical user interface, which, obviously, again, Windows was mimicking back in the day. So it was written for the Mac, and then it obviously made the leap into Windows as well.

Garreth Hanley:
That's interesting. I think most of us associate Microsoft with Excel and Windows these days.

Neale Blackwood:
That's right. And you sort of think of Bill Gates and Steve Jobs at loggerheads, whereas at that stage, they're actually working together building the software for the Mac, which, again, the Mac was the first computer that was more like a graphical user interface rather than the old DOS, which was what Lotus was on. So Lotus, the spreadsheet of choice in the eighties, was on DOS, which was the old operating system, and it didn't really make the leap into Windows for quite a few years. So that gave the foot in the door, if you like, of Excel, because Excel was on Windows, and so people were buying Windows machines. You could still run DOS on the Windows machine, but it was better to use the Windows interface with Excel.

Garreth Hanley:
Is it Lotus 123? Was that the spreadsheet?

Neale Blackwood:
It was Lotus 123, that's correct. That was the really popular one that was, again, in the late eighties I think, but by the mid-nineties, Lotus was pretty much gone and pretty much everyone was using Excel. So since then, Excel has been the spreadsheet of choice. I know Google Sheets has come in recently, and a lot of people use Google Sheets, which is okay for personal spreadsheets, but when it comes to business, Google Sheets doesn't really cut it. I know one fairly large business went to the Google suite for everything except the spreadsheets. For their Word, they used the Google version, but for the spreadsheets, they kept Excel simply because Excel has, for business, just has so many features that everyone uses that they couldn't actually go to Google Sheets. Google Sheets is getting better, but again, so is Excel, so it's an interesting race.

Garreth Hanley:
And is it just the two of them?

Neale Blackwood:
Pretty much. There's no other real spreadsheet that people are using. It's pretty much now between Excel and Google Sheets.

Garreth Hanley:
What are your thoughts on Google Sheets?

Neale Blackwood:
I used it early in the days, but I haven't really got back to it. My specialty is Excel. I'm pretty sure I could jump onto Google Sheets and do a pretty good spreadsheet.

Garreth Hanley:
Are the features pretty similar?

Neale Blackwood:
Google Sheets actually has some features that Excel doesn't yet. So Excel is sort of in catch up mode there because Google Sheets has always been a better collaborative effort, which, that's something that Microsoft is working to get Excel better at, and Excel is improving in that area now.

Garreth Hanley:
And I guess a big difference is that Excel is software, whereas Google is all online, yeah?

Neale Blackwood:
Yeah, it's interesting. They're also developing a web-based browser version of Excel, so it's more collaborative. It's interesting as well, they're using the browser version to do some testing for new features as well. So for example, there's a feature that has been requested, in Excel, for many, many years. In a cell, you can create a dropdown list and one of the problems with that, on the desktop version, is you have to basically scroll down that list to get anything. A lot of dropdown lists, you can type a letter and then the list will filter by that letter so it just makes it easier to basically start typing. Yeah, so it's like a auto complete, if you like. But the Excel doesn't have that in a desktop, but they've built it for the browser version. So in the browser version, you can now type a letter and the dropdown changes. So fingers crossed that's going to jump across to the desktop version in the near future. It is interesting that they're using the browser version to do a little bit of development and also sort of testing, I guess, because it's probably an easier testing environment for them.

Garreth Hanley:
That's a pretty big change, shifting from on computer to, I guess, a cloud-based service. Are there any other major changes that you've seen over the years that have affected the product and how people use it?

Neale Blackwood:
Yeah, well, basically they're shifting away. So back in the old days, you would buy what's called a full version of Excel. So you'd get a CD back in the old days. So you'd actually get discs in the old days, and then it's gone from CD to DVD, and now you just download the version and basically you own it. They've shifted away from that to, now, a subscription version. In the old days, you basically had to buy the next version, so Excel 2000, Excel 2003. Every two or three years you'd get a new version, and to get the new features, you had to buy that full version. Now, if you get the subscription version, which is like a monthly fee or an annual fee, pretty much monthly or six monthly, you can get all of the updates. So you get new features, new functions. Lots of new functions have been added recently to Excel, so that's been fairly exciting. And that's the advantage with going with the subscription version is you get all of the latest features straight away. You don't have to buy another version. The last full version, I think was Excel 2021, they usually put the year on them these days, and I don't know that there'll be another full version. I think they're going to try and just keep pushing the subscription version.

Garreth Hanley:
The 365.

Neale Blackwood:
Yeah. So it used to be called Office 365, and now I think they've re-badged it to Microsoft 365.

Garreth Hanley:
Okay. And it's included as part of that?

Neale Blackwood:
Yeah.

Garreth Hanley:
That subscription?

Neale Blackwood:
Yeah, I think there's a couple of different subscription levels, as in how many apps you get. So obviously you get Excel, Word, PowerPoint and Outlook, and then I think if you pay a little bit more, you can get things like Access. Power BI, which is another Microsoft product, is included in that, and that's pretty much free, but I think in the subscription version, you might get some extra parts to it.

Garreth Hanley:
I guess a big difference, Neale, between the old days of having books on a shelf and ledgers in books and using computers is that you can, I guess, link those books together using things like relational databases. For our listeners who might not be familiar with the term, can you explain what a relational database is and how they interact with Microsoft Excel?

Neale Blackwood:
Yeah, so relational databases are things like Access and SQL, which are both Microsoft products, and they allow you to take two dimensional tables. So most tables have rows and columns, and the rows represent the records. So you might have a sales record for a particular invoice, a row, and then the columns break up that by maybe the product code, the amount, the quantity, things like that. So that's a two-dimensional table. And relational databases allow you to take that two dimensional table and basically put a third dimension in by getting the tables to talk to each other. Let's say you've got a sales table which has all of the invoice records on it, and then you might have a product column in that sales table, so the product code is in both. In a relational database, that allows you to report on sales and, for example, bring in the description of the product, which might not be in the sales table, it's in the product table. Now, that was pretty much very difficult to replicate in Excel. Now, things have changed, and Excel has what's called a data model, and it has the ability to relate tables together within Excel, which is reasonably powerful. And there's a feature called Power Pivot that allows you to use those relationships and to build reports that basically pull data from multiple tables. There's also the ability to extract data from relational databases. Power Query is the importing feature in Excel, and Power Query works really well with relational databases and can work with multiple tables as well. Relational databases are an area that does take a little bit of work to get your head around and to figure out how to report. And so, that's an area that accountants should look at if they're not already familiar with relational databases. It is a skill that you need to learn because so much data is held in relational databases. Things like Power BI has sort of democratised the data, if you like, because Power BI works really well with relational databases as well. Even the Power Query and the Power Pivot, which is built into Excel, those two features are a part of Power BI. And the big thing that Power BI has is the dashboarding and reporting abilities, which are far above what Excel has. So the things you learn in Excel and Power Query and Power Pivot can be directly transferred over to Power BI.

Jackie Blondell:
We hope you're enjoying INTHEBLACK. If you are interested in the latest news, analysis, policy updates, and business insights, you should check out CPA Australia's With Interest podcast. Join us as we dive into the news and delve into the business issues of the day. Each week, we talk to thought leaders from across the accounting, finance, strategy, economic and business spectrum, and you get their expert opinions. Now, back to INTHEBLACK.

Garreth Hanley:
Can you explain a bit more about what Power BI is and how that's changed Excel?

Neale Blackwood:
Okay. So Power BI and Microsoft like to put power in front of things like Power Pivot, Power Query and Power BI. The BI stands for Business Intelligence, and it's basically allowing you to access your data without going through the database admin person. In the past, let's take Access as an example, you might have had a database admin person who handled your Access database. Now, that Access database probably had tens of tables in it, and you want to report out of that same database. Now, what would typically happen is, they would create what's called a view, which is a report that brings together all of that data into a single table, which used to be what Excel could only work with, a single table. But now, Excel, and then Power BI, has developed over the last seven or eight years, I think, it can work with a relational database, and you don't really need to involve the database admin. You can directly get into your SQL database. You might need some passwords and a path and things like that, but once you're in, then you can then work with the data. Now, having said that, you might still need to involve the database admin just to understand some of the columns. So there may be situations where you'll need to touch base with the database admin just to get an understanding of the column headings and things like that. But basically, it allows you to do your own reporting and you can import the data using Power Query and then the Power Pivot side of things allows you to then create reports. And then there's a few powerful things in Power BI. One is the ability to create these interactive dashboards. You can create a dashboard and a person can click on a bar in a bar chart and, for example, that can filter the whole dashboard. And so it's totally interactive, so it's really, really powerful. And built into Power BI is the ability to share the data. Now, that's something that Excel has always struggled with. Typically, you'd send a report to someone, the whole Excel file, for example, and now, Power BI has the ability to what's called Row Level Security, so you can build it so that it's easy to share the reports and also get that security, which, again, a lot of businesses want to make sure that people are only seeing what they should see. And so, that's built in to Power BI, where, again, in Excel, that's really hard to do, whereas Power BI, it's a lot easier to do that.

Garreth Hanley:
And is Power BI a completely different application to Excel? Is that something that people have to purchase separately?

Neale Blackwood:
Yes, so Power BI is totally different to Excel in that it's a totally different application. Over the last couple of years, Power BI and Excel now talk to each other a lot better so you can import data from Power BI into Excel. So you can do a lot of the modelling, the data modelling, in Power BI. And data modelling is a new concept. In Excel, you used to write formulas in a cell, or you still do write formulas in a cell, in Power BI, you create formulas that are called measures, and they can, for example, give you total sales or total cost of sales, and that's one formula that you write, it's called a measure, and that can be used across the database. And so, that's really, really powerful because it's only one formula that you need to maintain there. Whereas in Excel, you'd create a formula in one cell and copy it to other cells. And this is called the DAX language, so DAX, and that's where the formulas are written. And DAX is also in Excel in the Power Pivot feature. And again, any skills you learn in Excel, in DAX, can be applied directly to the DAX function language in Power BI.

Garreth Hanley:
And if people want to learn Power BI, what's the best thing for them to do?

Neale Blackwood:
So there's a free desktop version. In that desktop version, you can pretty much learn and create reports. Sharing is a bit harder. For the sharing side of things to work, you do need licences. So you can teach yourself Power BI in the desktop version, and then there's an upgrade which then allows you to share within your organisation, so you get an organisation licence. The other difference with Power BI compared to Excel is, in Excel, pretty much everyone was like a developer, so people would create their own reports. In Power BI, there's sort of a bit of a demarcation. You'll probably have sort of super users in Power BI who create your reports, and then everyone else is probably going to be a consumer of the reports. So even though they can create them, you'll probably find that there'll be a few individuals in each organisation that are really good at Power BI and they'll create the reports, and then everyone else will just consume the reports, which, as I mentioned, is a little bit different to Excel, where everyone tends to be a little bit of a developer with their spreadsheets.

Garreth Hanley:
And you can't share the Excel sheets the same way as you can with BI.

Neale Blackwood:
Yeah. Correct. It's a lot harder to share Excel... Well, when I say harder, it's harder to control how people have access to it. You sort of got to share the whole file. Obviously you can do some stuff with the macros to limit what people do, but that's, again, an extra complexity level that you'd have to add, whereas in Power BI, it's just built into it.

Garreth Hanley:
And Excel also has Power Query and Power Pivot, yeah?

Neale Blackwood:
Yeah. So Power Query, I've written quite a few articles over the years on Power Query. Power Query is the best way to import data into Excel. It's also part of Power BI. What it does, it allows you to access pretty much any file or any database and bring the data in to Excel, then you can use Pivot tables and things like that to report on it. It's the skill, I think, most accountants should have. And even if they don't have the skill, they should know that it exists. So that, for example, if you're a manager, you know that the people under you could use Power Query to get or build reports based on data. So let's say you take a download from your accounting system every month and you get the transactions for each month into a file, and that could be a CSV file because most systems will drop into a CSV file. What you can do is, you can save each month's file into a folder, and Power Query will seamlessly bring all of those files together and create one single table. So it basically appends, puts each data underneath the previous one, and you'll get one single table from, let's say, 12 monthly reports, and that's really, really powerful. Well, I used to build macros to do using a visual basic in Excel, and that was quite an effort to do that, whereas with Power Query, it's basically just clicking a few buttons and you can import all 12 files into a single table, and then you can run a report off in a Pivot table or formula based report. So it offers a lot of time saving abilities, it also can clean the data. One of the things in the real world that you find is that the data that comes out of systems can have some issues in Excel, and Power Query has buttons, pretty much options, to fix all of those issues.

Garreth Hanley:
Are you still using macros or have these tools replaced macros?

Neale Blackwood:
Okay. There were a lot of macros written to do what Power Query does now. So yes, there were a subset of macros that were just about bringing data in. So those, pretty much, are redundant now. You can jump over to Power Query and pretty much do everything. But the thing with macros is, macros can control everything in Excel. So you can build automation into Excel using macros, and this is a lot more than recorded macros. So most people might have tried recording a macro and Excel writes the code for you. So you press record, you do the steps, Excel will write the code, writes the VBA code for you, and then you can rerun it. Now, recorded macros are really easy to create, but they have a lot of limitations. And pretty much all of those limitations can be removed by doing a little bit of editing and coding in those recorded macros, and you can write code from scratch as well. So the VBA code can control all of Excel. You can even create an app, pretty much, using Excel and you don't even know Excel is in the background. So it's a pretty much a fully-fledged programming language that's in the background of Excel. So macros aren't dead, but in terms of bringing data into Excel, macros are not required anymore because Power Query's taken over.

Garreth Hanley:
What about the future? Have you heard about any other big changes or updates that might be on the horizon?

Neale Blackwood:
One thing that I have heard is coming is something called Co-Pilot. I've done a couple of articles recently about AI, so artificial intelligence and Excel, and I think Co-Pilot is something that's going to be coming to Office that allows you to get the AI in the background to do a lot of the legwork.

Garreth Hanley:
Right. Using natural language.

Neale Blackwood:
Eventually, I guess you'll talk to it, but initially, I think you'll type it. And you can, for example, get Co-Pilot to create a slide or PowerPoint deck for you and to build charts and things like that. So it's going to be across Office, so it'll be in Word so you can ask it to write a report and things like that. So that's the idea behind Co-Pilot is it's going to be leveraging off Microsoft's AI development, and that's going to be built into Office. I covered the AI, in the analyse data option in Excel. It's sort of an extension of that and taking that even further.

Garreth Hanley:
Interesting. Now, we're going to have to wrap up soon, Neale, but before we do, I need to ask one question. Look, businesses do use a lot of other software other than Excel, and I'm wondering if you've ever found a piece of software that businesses or accountants might use that doesn't integrate with Excel?

Neale Blackwood:
There's an old joke that goes through. So ERP systems, so enterprise, resource processing systems, these cost hundreds of thousands of dollars, and in some cases millions of dollars to implement. And typically, what people ask is, is there a download to Excel button? And that tends to be the most used button in these expensive ERP systems. So Excel's not going anywhere. Excel's getting developed by Microsoft so I think they see Excel as the bridge between people and their data, and Power BI, I think, is part of that bridge as well. So I think the accountants will need some skills in Power BI as well as Excel, and the fact that Power BI and Excel are very well integrated as well so Microsoft see them going together, I think, into the future. There is the idea that maybe they could get combined, but they do do different things in terms of how they look at the data, so I don't think they could combine Power BI and Excel. I think they will remain separate. Well, I've got a couple of websites. I've got A-4 Accounting, so the letter A, the number four, accounting.com.au where I do a blog. So I do a weekly blog post there. I also have my training website, which is excel yourself.com.au, and that has lots of free resources. There's lots of free resources on A-4 accounting as well. The A-4 accounting is my sort of blogging website, and then the Excel Yourself is my online training website.

Garreth Hanley:
And it sounds like it's been a long time since you've been sharpening pencils, Neale?

Neale Blackwood:
Yes, it has been, but I did have my pencil sharpener on the desk because I was using it all the time.

Garreth Hanley:
Well, it's good to stay sharp, Neale. Thanks for joining us on INTHEBLACK today. If you've enjoyed this episode, help others discover INTHEBLACK by leaving us review and sharing this episode with colleagues, clients, or anyone else interested in leadership, strategy and business. To find out more about our other podcasts, check out the show notes for this episode, and we hope you can join us again next time for another episode of INTHEBLACK.