Great post! I'm an engineer and statistician who's forced to use products like Minitab and SPSS (Uggh!) but prefer the visibility of Excel. The one major limitation you failed to mention: < 1 million rows. Excel can't handle larger data. This is the only limitation that really matters.
Have you used Excel's Power Pivot feature? - You can handle hundreds of millions of rows of data- data stored and analysed from an in memory SSAS database associated with the file.
Completely disagree. Excel is the cockroach of programming languages. It deserves to die now. I can immediately tell good engineers form bad. The bad ones produce graphs made with Excel. You know, the ones that have horizontal gridlines but not vertical ones, etc. Truly dreadful. Don’t get me started on VBA. Excel users are NOT programmers. Excel UI is so bad it’s pathetic. Try moving a column, an operation that should be straightforward but isn’t (why?). Ditch Excel today for a better life!
OK, I admit that I did not know this after many years of using Excel. Thank you for the tip. I bet 95% of Excel users don't know it, either.
BTW, I find this feature emblematic of the bad IU of MS Office. Usually, Shift+extend is used to extend the selection, not to move it. Second, the feedback I get while doing this is terrible.
Here's how I would design this:
- select one or more columns
- while holding down the Command (Ctrl on Windows) key, drag the column header
- the column header animates and shows you the column being moved around as you drag it. You let go when you reach the desired position
No no no. This is not the same as swapping columns. This is copy/paste. It leaves and empty column behind. (Besides, I had troubles doing this on a Mac, because I don't know the shortcut to insert a new column, and the one in the menu changes to "Insert copied cells" after doing Cmd-X).
Of course, you are right: when I share this with an audience or a reader, it comes as a surprise. Never mind, you know it now. As for the process, I've been using it for such a long time that I don't think about it but I can remember trying your suggested method before I learned the one I posted! That was intuitive but not right!!
You may not like Excel for graphs simply because the people creating the graphs are not good storytellers of the data itself. The most impactful graphs are clean, simple, and are commonly known by everyone, all of which Excel can do. Pick up "Storytelling with Data" by Cole Nussbaumer Knaflic. In it she explains that the best presentations and graphs can all be done in Excel and in a way that makes the most impact to those reading the reports.
The keyword here is "can". Yes, you can spend time and tweak the plots to look decent, even though it is very hard to achieve what a good plotting software can easily do. But why should you? Why aren't the DEFAULTS better?
Because defaults are defaults made for general circumstances. Good data analysis requires an understanding of the underlying data. Good charting is closer to an art for that conveys that understanding to people who lack the ability to interpret numbers well.
My point is that Excel defaults are not good. Or rather, they may be ok for a certain, very limited, kind of plots (financial bar graph). Scatter plots should have both axes gridlines on by default.
You are actually making the author's point: from an evolutionary point of view, cockroaches are magnificent. They are highly successful animals, found just about everywhere on the planet, living successfully under a large range of conditions. A lot of people don't seem to like them: but that's got nothing to do with the effectiveness of cockroaches.
And who is dumber: the person who takes an hour to write the program in Python/R/C/LanguageOfChoice to produce the graph, or the one who takes 5 minutes in Excel? As in all things, it's horses for courses: for quick analyses of chunks of data Excel is hard to beat.
You didn't specify that. Exactly what I was saying (and specifically stated) that Excel is great at quick analyses. I mentioned nothing about repetition. I really don't care that you hate Excel: what is interesting is that you seem to believe that everyone should work to some mysterious ideology of a "perfect language" that you adore. The world isn't like that. And many people, who have other jobs to perform aside from worshipping at the altar of your favoured language, love Excel because it lets them do what they want to do without calling that black hole called "IT", where programming requests go to die.
And Excel is perfectly capable of automation: not in the "perfect" sense, but good enough for the average user. Fill your standard template with standard data - it works. It's how those financial analysts operate: and they love it. As the author pointed out: they are the subject-matter experts, and Excel lets them use their expertise, whilst the IT programmers are still trying to work out what a "bond" is.
Never said that Excel isn't a good tool FOR ACCOUNTANTS. The issue I have is that it is being promoted as the "be all and end all" of data analysis, for tasks where it isn't suitable, by this article and lots of other ignorami in schools, companies ("engineers? we have MS Office"), etc. Schoolchildren are forced to learn less than mediocre software only because it's a least denominator and "every business uses it". Crap! Educating does not mean training students for "businesses".
Quick analysis? Give me JMP. Or Jupyter notebook+Pandas.
But you miss the point. It isn't just good for accountants, it's good for everyone who needs to crunch numbers. Is it the "best"? How do you measure that? What it does do is let the subject-matter expert do things without relying in "IT" - which can only be good. That user ends up with responsibility for their model, no ifs or buts.
You can say this or that is "better", but your particular preferences fail to capture that Excel is now a standard, which means skills are transferable. I can go somewhere else and use the same tool. So can everyone else. The Internet is based on a standard, TCP/IP, which was DARPANET some 70 years ago. Its protocols are completely out of date as compared to what you might design today, but it has two huge benefits: it's a standard, everywhere, and it's robust (designed for a nuclear war). Many things are retro-fitted, like the web and video streaming, but they are based on that old protocol.
Excel is just the same. It's now a standard for easy number-crunching. There is now a huge ecology of add-ons that makes it even more powerful, so it's not going to go away. And crucially, it moves computing from central IT to the user: a move that will only accelerate.
Once upon a time, everyone who had a car had a chauffeur: the expert to drive them and keep the car going. As cars improved, people took responsibility for their own vehicles, and a chauffeur is now just a status symbol. IT is in that very transition now: programs like Excel are transferring responsibility from IT to the user. And it isn't going to stop: the huge IT departments we have today will shrink to be the specialist engineers producing software in software companies, with off-the-peg systems being selected and configured by the user to their needs. Just like cars.
A bit of both. On the one hand, spreadsheets are good, IMO, for creating tables and maybe dealing with (I am reluctant to use the word analyze) data that lend themselves to a tabular format. On the other hand. Excel is so gangrened that even the open source alternatives, like LibreOffice, blindly copy all its quirks 1:1, no doubt to ease the transition for the masses.
I really think Excel's unsexiness causes product folks to underappreciate how amazing it is as an all-around product success. More than 35 years since v1 rolled out, close to 15% of the global population still uses Excel on a monthly basis. Or, if you find user passion more convincing than numbers, go ahead and google 'excel e-sports'. That's right, the Microsoft Excel World Championships (not to be confused with the also-excel-based Financial Modeling World Cup) aired on ESPN only a few weeks ago with a $10,000 purse. Plus if you're hunting for product opportunities, looking out for any time your target users open spreadsheets to stitch processes together is a great place to start. I've written more about this for interested product managers and designers (but won't abuse the comment section by linking to it here)
Thank you for an excellent essay in evaluating Excel, which I have been using for say 25 years non-stop as an investment analyst/manager. Our software (digital investment solution) was first prototyped almost entirely in Excel and then codified on AWS/Google Cloud. I have to say we are inspired by Excel, too. In the age of SaaS, and as many people are dropping Excel, this is a great reminder Excel is the "bonsai tree of software!"
"We wouldn’t be surprised to see Microsoft begin to snatch up more companies in no-code." They don't really have to snatch up too many, since they already have developed in-house the biggest low-code/no-code platform out there. Microsoft Power Platform has 25 million monthly active users, out of which 7 million are categorized as monthly active citizen professional developers in this recent interview by Charles Lamanna who's running that business: https://ff.tips/CLPP7M
A major part of Power Platform is built on the legacy of Excel, naturally. Whereas tools like Power Query and Power BI are closely tied to the data analytics side of Excel, the programming part of building application logic is based on Power Fx. It's a "low code programming language" that is as close to Excel formulas as is practically possible in the app context. The tagline for Power Apps has for long been "Think Excel", even before Power Fx as an open source programming language was launched in 2021: https://powerapps.microsoft.com/en-us/blog/what-is-microsoft-power-fx/
While I don't believe that a single dominating product like Excel would ever emerge in the low-code/no-code space, the investments that Microsoft is making in the Power Platform family of products will surely have synergies to help Excel remain relevant for the next 36 years.
Excel *is* amazing! And while Google Sheets may never replace Excel, when it comes to creating a complex, multi-sheet solution that can be used simultaneously by users distributed across the interwebs, Google Sheets is really the only option. I've preferred Google Sheets over Excel for years because of it's cleaner interface (ribbon, anyone?) and it's javascript programming environment (I also used VBA extensively with Excel in a previous life), but the biggest differentiator is that a Google Sheet, by definition, is in the cloud, which opens up worlds of possibilities. How Microsoft managed to miss this boat baffles me. I still cringe when my clients insist on passing Excel spreadsheets around as attachments. Yes, Excel truly is amazing. But Google Sheets is better. ;-)
Years ago I was forced into writing a takeoff analysis for a future military aircraft. The final product (it was always evolving) was quite sophisticated; e.g. it could optimize various variables. Using Microsoft VBA was essential, as was several add-ons. But on nearly fatal flaw was a lack of version control, and saving output data.
Para excel se necesita tener razonamiento y si sabes o eres matemático te hace lo que tu le ordenes hoy en día estan a costumbrados a solo aplastar la tecla o función para que les sirvan. HAY QUE PENSAR Y RAZONAR, nunca has trabajado a fondo con EXCEL
It's fun comparing this with SQL. FWIW I like both, for different purposes. Excel is better for quick and dirty analysis, smaller data sets, and if I don't know exactly what I'm looking for. SQL is better with large data sets and seems like it'd be easier to automate (unsure, never attempted to do that in Excel). But, huge fan of Excel :).
excel is the bud light of analysis. it sells a lot but if its your favorite beer then, uh, good for you.
the first time i used a spreadsheet in college i thought it was pure genius, as is dan bricklin. however, using awk/perl/gnuplot for the first time was like tasting my first samuel smith taddy porter. a whole new world opened up.
in no way am i saying everyone should be doing everything in R or python, however, if your toolkit begins and ends with excel, uh, good for you.
Great post! I'm an engineer and statistician who's forced to use products like Minitab and SPSS (Uggh!) but prefer the visibility of Excel. The one major limitation you failed to mention: < 1 million rows. Excel can't handle larger data. This is the only limitation that really matters.
You can also use Excel's Power Query routines to extract, transfer and load millions of rows of data in memory - perform the data wrangling you need to get data in tabular form it can also perform the statistical analysis you need to do.. https://support.microsoft.com/en-us/office/introduction-to-microsoft-power-query-for-excel-6e92e2f4-2079-4e1f-bad5-89f6269cd605
Have you used Excel's Power Pivot feature? - You can handle hundreds of millions of rows of data- data stored and analysed from an in memory SSAS database associated with the file.
For the longest time, Excel could not handle more than 65,536 rows...
Completely disagree. Excel is the cockroach of programming languages. It deserves to die now. I can immediately tell good engineers form bad. The bad ones produce graphs made with Excel. You know, the ones that have horizontal gridlines but not vertical ones, etc. Truly dreadful. Don’t get me started on VBA. Excel users are NOT programmers. Excel UI is so bad it’s pathetic. Try moving a column, an operation that should be straightforward but isn’t (why?). Ditch Excel today for a better life!
If you don't know how to move a column, I think you must have very little experience using Excel.
Please enlighten me.
Select one or more columns. Press and hold the Shift key. Use the cursor to grab the left or right border of your selection to drag and drop.
OK, I admit that I did not know this after many years of using Excel. Thank you for the tip. I bet 95% of Excel users don't know it, either.
BTW, I find this feature emblematic of the bad IU of MS Office. Usually, Shift+extend is used to extend the selection, not to move it. Second, the feedback I get while doing this is terrible.
Here's how I would design this:
- select one or more columns
- while holding down the Command (Ctrl on Windows) key, drag the column header
- the column header animates and shows you the column being moved around as you drag it. You let go when you reach the desired position
Why bother with a mouse?
1. Go to any cell in relevant column
2. Press control space to select entire column
3. Ctrl+X
4. Use arrow keys to go to any cell in target column (if adding within an existing table, can add new column to right using Alt+I+C)
5. Ctrl+V
No no no. This is not the same as swapping columns. This is copy/paste. It leaves and empty column behind. (Besides, I had troubles doing this on a Mac, because I don't know the shortcut to insert a new column, and the one in the menu changes to "Insert copied cells" after doing Cmd-X).
Of course, you are right: when I share this with an audience or a reader, it comes as a surprise. Never mind, you know it now. As for the process, I've been using it for such a long time that I don't think about it but I can remember trying your suggested method before I learned the one I posted! That was intuitive but not right!!
You may not like Excel for graphs simply because the people creating the graphs are not good storytellers of the data itself. The most impactful graphs are clean, simple, and are commonly known by everyone, all of which Excel can do. Pick up "Storytelling with Data" by Cole Nussbaumer Knaflic. In it she explains that the best presentations and graphs can all be done in Excel and in a way that makes the most impact to those reading the reports.
The keyword here is "can". Yes, you can spend time and tweak the plots to look decent, even though it is very hard to achieve what a good plotting software can easily do. But why should you? Why aren't the DEFAULTS better?
Because defaults are defaults made for general circumstances. Good data analysis requires an understanding of the underlying data. Good charting is closer to an art for that conveys that understanding to people who lack the ability to interpret numbers well.
My point is that Excel defaults are not good. Or rather, they may be ok for a certain, very limited, kind of plots (financial bar graph). Scatter plots should have both axes gridlines on by default.
LOL
You are actually making the author's point: from an evolutionary point of view, cockroaches are magnificent. They are highly successful animals, found just about everywhere on the planet, living successfully under a large range of conditions. A lot of people don't seem to like them: but that's got nothing to do with the effectiveness of cockroaches.
And who is dumber: the person who takes an hour to write the program in Python/R/C/LanguageOfChoice to produce the graph, or the one who takes 5 minutes in Excel? As in all things, it's horses for courses: for quick analyses of chunks of data Excel is hard to beat.
Who is dumber: The person that effectively automates a repetitive task or keep re-doing the same actions in Excel?
You didn't specify that. Exactly what I was saying (and specifically stated) that Excel is great at quick analyses. I mentioned nothing about repetition. I really don't care that you hate Excel: what is interesting is that you seem to believe that everyone should work to some mysterious ideology of a "perfect language" that you adore. The world isn't like that. And many people, who have other jobs to perform aside from worshipping at the altar of your favoured language, love Excel because it lets them do what they want to do without calling that black hole called "IT", where programming requests go to die.
And Excel is perfectly capable of automation: not in the "perfect" sense, but good enough for the average user. Fill your standard template with standard data - it works. It's how those financial analysts operate: and they love it. As the author pointed out: they are the subject-matter experts, and Excel lets them use their expertise, whilst the IT programmers are still trying to work out what a "bond" is.
Never said that Excel isn't a good tool FOR ACCOUNTANTS. The issue I have is that it is being promoted as the "be all and end all" of data analysis, for tasks where it isn't suitable, by this article and lots of other ignorami in schools, companies ("engineers? we have MS Office"), etc. Schoolchildren are forced to learn less than mediocre software only because it's a least denominator and "every business uses it". Crap! Educating does not mean training students for "businesses".
Quick analysis? Give me JMP. Or Jupyter notebook+Pandas.
But you miss the point. It isn't just good for accountants, it's good for everyone who needs to crunch numbers. Is it the "best"? How do you measure that? What it does do is let the subject-matter expert do things without relying in "IT" - which can only be good. That user ends up with responsibility for their model, no ifs or buts.
You can say this or that is "better", but your particular preferences fail to capture that Excel is now a standard, which means skills are transferable. I can go somewhere else and use the same tool. So can everyone else. The Internet is based on a standard, TCP/IP, which was DARPANET some 70 years ago. Its protocols are completely out of date as compared to what you might design today, but it has two huge benefits: it's a standard, everywhere, and it's robust (designed for a nuclear war). Many things are retro-fitted, like the web and video streaming, but they are based on that old protocol.
Excel is just the same. It's now a standard for easy number-crunching. There is now a huge ecology of add-ons that makes it even more powerful, so it's not going to go away. And crucially, it moves computing from central IT to the user: a move that will only accelerate.
Once upon a time, everyone who had a car had a chauffeur: the expert to drive them and keep the car going. As cars improved, people took responsibility for their own vehicles, and a chauffeur is now just a status symbol. IT is in that very transition now: programs like Excel are transferring responsibility from IT to the user. And it isn't going to stop: the huge IT departments we have today will shrink to be the specialist engineers producing software in software companies, with off-the-peg systems being selected and configured by the user to their needs. Just like cars.
i agree that excel is dismal but what would you have replace it? and, is your ire directed specifically at excel or spreadsheets in general?
A bit of both. On the one hand, spreadsheets are good, IMO, for creating tables and maybe dealing with (I am reluctant to use the word analyze) data that lend themselves to a tabular format. On the other hand. Excel is so gangrened that even the open source alternatives, like LibreOffice, blindly copy all its quirks 1:1, no doubt to ease the transition for the masses.
Slow down on the memes please.
Haha you may be in the wrong place
new goal of mine: increase the amount of times i use this exact phrase with people who need to hear it :)
https://cdn-img.scalabs.com.au/uzjYWz5uFA99H9ilh_BLtPqAA1Dq0GhZ05-Iow7qAZM/aHR0cHM6Ly9zdy1o/aXQtcHJkLnNjYWRp/Z2l0YWwuaW8vbWVk/aWEvMTYyNjYvc2lk/ZS1leWUtY2hsb2Uu/anBnP3ByZXNldD1N/YWluSW1hZ2U
I really think Excel's unsexiness causes product folks to underappreciate how amazing it is as an all-around product success. More than 35 years since v1 rolled out, close to 15% of the global population still uses Excel on a monthly basis. Or, if you find user passion more convincing than numbers, go ahead and google 'excel e-sports'. That's right, the Microsoft Excel World Championships (not to be confused with the also-excel-based Financial Modeling World Cup) aired on ESPN only a few weeks ago with a $10,000 purse. Plus if you're hunting for product opportunities, looking out for any time your target users open spreadsheets to stitch processes together is a great place to start. I've written more about this for interested product managers and designers (but won't abuse the comment section by linking to it here)
Thank you for an excellent essay in evaluating Excel, which I have been using for say 25 years non-stop as an investment analyst/manager. Our software (digital investment solution) was first prototyped almost entirely in Excel and then codified on AWS/Google Cloud. I have to say we are inspired by Excel, too. In the age of SaaS, and as many people are dropping Excel, this is a great reminder Excel is the "bonsai tree of software!"
"We wouldn’t be surprised to see Microsoft begin to snatch up more companies in no-code." They don't really have to snatch up too many, since they already have developed in-house the biggest low-code/no-code platform out there. Microsoft Power Platform has 25 million monthly active users, out of which 7 million are categorized as monthly active citizen professional developers in this recent interview by Charles Lamanna who's running that business: https://ff.tips/CLPP7M
A major part of Power Platform is built on the legacy of Excel, naturally. Whereas tools like Power Query and Power BI are closely tied to the data analytics side of Excel, the programming part of building application logic is based on Power Fx. It's a "low code programming language" that is as close to Excel formulas as is practically possible in the app context. The tagline for Power Apps has for long been "Think Excel", even before Power Fx as an open source programming language was launched in 2021: https://powerapps.microsoft.com/en-us/blog/what-is-microsoft-power-fx/
While I don't believe that a single dominating product like Excel would ever emerge in the low-code/no-code space, the investments that Microsoft is making in the Power Platform family of products will surely have synergies to help Excel remain relevant for the next 36 years.
It can handle more than a million lines of data now.
Excel *is* amazing! And while Google Sheets may never replace Excel, when it comes to creating a complex, multi-sheet solution that can be used simultaneously by users distributed across the interwebs, Google Sheets is really the only option. I've preferred Google Sheets over Excel for years because of it's cleaner interface (ribbon, anyone?) and it's javascript programming environment (I also used VBA extensively with Excel in a previous life), but the biggest differentiator is that a Google Sheet, by definition, is in the cloud, which opens up worlds of possibilities. How Microsoft managed to miss this boat baffles me. I still cringe when my clients insist on passing Excel spreadsheets around as attachments. Yes, Excel truly is amazing. But Google Sheets is better. ;-)
I was diagnosed as HEPATITIS B carrier in 2013 with fibrosis of the
liver already present. I started on antiviral medications which
reduced the viral load initially. After a couple of years the virus
became resistant. I started on HEPATITIS B Herbal treatment from
ULTIMATE LIFE CLINIC (www.ultimatelifeclinic.com) in March, 2020. Their
treatment totally reversed the virus. I did another blood test after
the 6 months long treatment and tested negative to the virus. Amazing
treatment! This treatment is a breakthrough for all HBV carriers.
Good stuff man. I don't understand how you can write so much, with young kids! Kudos my man
Years ago I was forced into writing a takeoff analysis for a future military aircraft. The final product (it was always evolving) was quite sophisticated; e.g. it could optimize various variables. Using Microsoft VBA was essential, as was several add-ons. But on nearly fatal flaw was a lack of version control, and saving output data.
Para excel se necesita tener razonamiento y si sabes o eres matemático te hace lo que tu le ordenes hoy en día estan a costumbrados a solo aplastar la tecla o función para que les sirvan. HAY QUE PENSAR Y RAZONAR, nunca has trabajado a fondo con EXCEL
It's fun comparing this with SQL. FWIW I like both, for different purposes. Excel is better for quick and dirty analysis, smaller data sets, and if I don't know exactly what I'm looking for. SQL is better with large data sets and seems like it'd be easier to automate (unsure, never attempted to do that in Excel). But, huge fan of Excel :).
Excel 2003 is easier to use. Also, the ribbon is a pain & the icons are a foreign language. Try using words instead of icons.
excel is the bud light of analysis. it sells a lot but if its your favorite beer then, uh, good for you.
the first time i used a spreadsheet in college i thought it was pure genius, as is dan bricklin. however, using awk/perl/gnuplot for the first time was like tasting my first samuel smith taddy porter. a whole new world opened up.
in no way am i saying everyone should be doing everything in R or python, however, if your toolkit begins and ends with excel, uh, good for you.
Ah, Nope! $$Microsoft. No magic. You don't think think this product can be replicated for 90% of its users and turned out for free?