Introduction
This blog is intended for analysts who have little to no experience using Excel and new analysts who are getting familiar with what tools are out there.
It’s the year 2025—why am I talking about Excel as an analytical tool? Microsoft Excel 1.0 was released on September 30, 1985 for the Apple Macintosh and Windows users had to wait until 1987 to get their hands on it.
A program that has been around for this long, must surely have been replaced with something better by now. The answer is both yes and no. Yes, there are more advanced programs to visualize data and even analyze it, like Tableau and Power BI (to mention a few).
No, there isn’t a tool that almost everyone already has on their work (or home) computer, that is easy to learn without having to know SQL or Python. As a user, there are two ways to get your data:
- You have access to the raw data, and you can get it yourself
- You have a department that takes your request and provides the raw data to you
I can get the data myself, and the next step of the analytical process is to ask myself two questions.
Is the analysis a one-time deal or something that will be ongoing and needs to look nice for management to review? If this is an ongoing report of insights, the time will be better used to create a nice report in either Tableau, Power BI, Looker, or whatever the company uses.
But often enough, you have to do a quick analysis on what is happening to a certain KPI. And with “quick”, that usually brings me to Excel.
Sidenote: (In a perfect world, you have existing reports that you have created in the past, and you can do a deep dive on that without having to pull additional data. But most people don’t live in that world. We deal with restrictions of time to build out these reports, or sometimes don’t get the money allocated to buy fancy tools. I’ve worked for one of the largest companies in the US or even the world, and both of those issues applied to my department/position.)
Excel’s Evolving Role in the Data Analysis Environment
This brings me to the biggest limitation that I have faced with Excel: you can’t process more than 1 million rows (1,048,576 to be exact). To make it work, you will need to filter the data and do some aggregation in the data pull. Other ideas are several data pulls. For example, if you review three months of data and that puts you over the limit, do one data pull per month and then combine the findings after you have analyzed and aggregated it further in Excel.
But I am not writing a “how-to” data analysis guide, but about the advantages of Excel in today’s time. As stated before, most work laptops will include Microsoft Office, and even if not, the cost for Office is far lower than getting Tableau or Power BI (yes, there are probably several open-source tools for free, but there will be a learning curve and sharing files won’t be as easy).
Let’s look at some of the reasons why Excel remains a valid and widely used analytical tool today, and what the key reasons are:
1. Ubiquity and Familiarity
Excel is available on most personal and professional computers, and many people are already familiar with it. This lowers the learning curve, making it accessible for users across different industries and roles, from analysts to executives.
2. Flexibility and Versatility
Excel can handle various data types, calculations, and analyses, from simple tabulation to complex financial models. It supports functions like pivot tables, charts, and conditional formatting, which can be applied to diverse analytical tasks, such as budget forecasting, data summarization, and trend analysis.
3. Advanced Features
- Data Analysis and Visualization: Excel has powerful built-in tools like pivot tables, Power Query, and Power Pivot for data manipulation and reporting. With its charting options, users can create a variety of visual representations of their data.
- Formulas and Functions: Excel has a rich library of built-in functions for statistical analysis, financial modeling, and conditional logic, enabling users to perform complex analyses quickly.
4. Integration with Other Tools
Excel integrates well with other software, including databases, Business Intelligence (BI) platforms (like Power BI), and programming languages such as Python and R. This interoperability makes it a versatile companion tool for data professionals who want to blend traditional spreadsheet functionality with more advanced analytics.
5. Cost-Effectiveness
Compared to specialized software, Excel is affordable, particularly for smaller businesses or individuals who might not require the advanced capabilities of more expensive tools like Tableau, SAS, or SPSS.
6. Automation and Extensibility
Excel allows automation through VBA (Visual Basic for Applications) and can also work with tools like Power Automate. This extends its capabilities beyond basic analysis, allowing users to create custom workflows, reports, and even applications within Excel.
7. Quick Prototyping and Collaboration
For teams, Excel is often used for quick prototyping and sharing insights, as it supports easy collaboration through cloud services like OneDrive and SharePoint. It’s also a go-to tool for rapid calculations before scaling up to more sophisticated platforms.
8. Improved Power BI Integration
For users like yourself who work with Power BI, Excel serves as a complementary tool, especially for quick data manipulation before importing it into Power BI for more advanced analysis and visualization.
While there are more specialized analytics tools today, Excel’s accessibility, ease of use, and widespread familiarity make it indispensable for many use cases in day-to-day data analysis.
New Excel Features That Make a Difference
2024 also brought some new features to Excel, including one that everyone is talking about these days: AI. Microsoft’s AI assistant is called Copilot.
- In April 2024, Copilot was updated to generate multiple formula columns from a single prompt. It can also create complex formula columns that span multiple tables.
- In August 2024, Copilot can also create custom charts and PivotTables and summarize textual data. In July 2024, Copilot can provide step-by-step instructions, including formula examples.
New functions to manipulate text on a large scale:
- Regular expression (REGEX) functions: In May 2024, new REGEX functions were introduced, including REGEXTEST, which checks if any part of supplied text matches a regex pattern.
- New formulas: In May 2024, new formulas were added, including TEXTSPLIT, TEXTBEFORE, TEXTAFTER, TAKE, DROP, CHOOSECOLS, and CHOOSEROWS.
In addition to the functions mentioned above, here are some more new additions:
- Python Editor: In July 2024, the Python Editor became available as an in-the-box option.
- Pride theme: In June 2024, a special Pride theme was added for Mac and iOS users.
When to Use Excel vs. Other Tools
Now that we talked about some of the key features and new updates released this year, it shows that Excel can do more than just be a check list on who paid their portion for the next Christmas party.
You are able to design graphs that easily compete with Tableau, and you can automate reports just like in Power BI.
But before I wrap this up, one of the main features that makes Excel so amazing to use for analysis is the fact that you can manually manipulate the data.
Sidenote: (Manually manipulating data can also be the number one reason for false reports, but let’s assume that we know what we are doing.)
When working with data, there are times when you just quickly want to remove a column or row. After reviewing the data output, you notice that some data is just not needed for your analysis, and you can simply remove the entire column with a click of the mouse (if you are dealing with a larger file size, deleting unwanted columns and rows lowers the size and can help speed up sorting, etc.).
Editing a column is also something that makes your life easier. Let’s assume our company surveyed all employees on their mode of transportation to work. The answer column is a free-type column. We just did a pivot chart on the results. On the chart, it shows several columns for the mode of transportation, and it shows bike 15% and bicycle 10%. You can easily group the two into one column. Also, editing spelling mistakes that otherwise present an outlier in your chart (i.e. byke 2%).
Final Thoughts: Excel’s Place in Modern Analytics
Lastly, Copilot has the ability to significantly enhance the experience for the average user by offering a variety of helpful features:
1. Data Insights
It can analyze your data and provide insights, trends, and summaries, helping users make informed decisions without deep data analysis skills.
2. Formula Assistance
Copilot can suggest formulas based on the context of the data, making it easier to perform calculations and automate tasks.
3. Natural Language Queries
Users can ask questions in plain English, and Copilot can generate the corresponding Excel functions or visualizations.
4. Data Visualization
It can recommend the best types of charts and graphs to represent your data effectively, helping users create more engaging presentations.
5. Automation
Copilot can automate repetitive tasks, like data cleaning and formatting, saving time and reducing errors.
6. Collaboration Features
It can assist in generating comments or notes for shared workbooks, enhancing communication among team members.
7. Template Suggestions
Copilot can recommend templates based on the user’s needs, making it easier to start new projects.
Conclusion
Overall, Copilot can empower users to work more efficiently and confidently with their data, regardless of their Excel proficiency.
Microsoft continues to improve and update Excel after 40 years — that’s why Excel is considered to be relevant in the future due to its adaptability and continuous evolution.
Excel skills can help with analytical and forecasting skills, strategic planning, risk assessment, and more.
For your next analysis, I encourage you to use Excel if you aren’t already, and see how much power this old tool has still in it.
