5 Things You Didn’t Know Excel Does

Of all the tools in the Office 365 suite, Excel is arguably the most under-utilized. Although nearly every business uses Excel in some capacity, most people just don't use the advanced features.

It might be surprising to learn that many decision-support scenarios can be implemented directly in Excel without the need for complex programming languages or expensive third-party software. Diving into Excel's advanced functionality, we find that graphical data representation, interactive components, and even database-like SQL queries are all within easy grasp.

Here are five Excel capabilities your organization may be missing out on.

Financial Modeling

Models are typically assembled by those involved in a financial analyst profession, but more diverse professionals can get into the act with the right tools. With all the fintech out there, you may be surprised to know Excel is still the dominant modelling software.

That's because many models are created as a tool for end-users. They're used decision makers to gain a grasp on an organization's future performance. By building a financial model within an Excel spreadsheet, principals can alter a data point and immediately see the results flow through the organization. It's no wonder that entire business models have been built around an Excel worksheet. The key to building a successful financial model within Excel lies in:

Make it simple to use. Clearly identifying individual cells or dialogs that should be altered to test scenarios.

Make it bulletproof. Proper use of Excel's modeling functions such as CHOOSE, DB (declining balance), and RATE. With an understanding of Excel's financial modeling functions, worksheet development can be tremendously simplified without sacrificing capability.

Make it easy to read. Cleanly formatting output with charts and graphs to create an immediate visual impact. Analysts often build models in Excel for a user to manipulate. Make the product easy to understand.

Excel might be the most popular tool for financial modelling, but it's hard to say for how long. Relatively easy to use software like TensorFlow or Python packages like Pytorch have brought neural networks to the masses. Similarly, state of the art financial modeling uses time series models, like ARIMA (autoregressive integrated moving average) and GARCH (generalized autoregressive conditional heteroskedasticity). Excel has add-ins to perform this type of modeling, but it's becoming increasingly common to see these models built in Python or R.

Write Scripts for Excel with VBA

The Excel VBA editor uses a subset of the Visual Basic language to develop powerful object-oriented scripts. With VBA, Excel gurus can automate repetitive tasks, as well as create custom user interface objects like dialog boxes and forms.

Those who have programming or scripting experience can actually hide the spreadsheet from the user, presenting a series of dialogs to guide a user through data input. The spreadsheet acts as decision-support software while abstracting users from the details.

There are other simpler uses for VBA as well, which may find everyday use in formatting and processing raw data input. For instance, VBA scripts are excellent for stripping unwanted characters (such as line breaks) from input data: MyData = Replace (MyData, Chr(10), "").

Because clean data is happy data.

Advanced Data Visualizations

Data visualization is important to anyone who needs to present data insights to a larger audience. The best visualizations not only communicate an outcome, they engage the user and stimulate their attention to the matter.

With all the talk about Tableau and PowerBI, it's easy to forget that Excel was the OG data visualization tool. Excel's combination charts can convey complex multi-axis relationships in clear terms. Using the Developer tab, it's also possible to create interactive charts with check boxes, option buttons, or drop downs.

Aside from the built-in charting ability, there are a number of add-ins that increase Excel's visualization capabilities. Tools such as PowerPivot take direct aim at business intelligence software such as Tableau and PowerBI. PowerPivot is designed to be easy enough for non-analysts to create advanced data models, whether data is contained within a single Excel sheet, or connected to a massive external datasource using Power Query.

Although powerful visualization and business intelligence software exists, you might find that it makes more sense to train up on these advanced Excel capabilities. Excel's built-in and add-on tools might just be all the visualization software your organization needs.

It's common Excel users have found themselves learning Tableau or PowerBI.

Forecasting

It's easy to get trapped in the mindset that Excel is just a tool for balancing the books or keeping track of payroll. But one of the more interesting uses of Excel is in the area of forecasting. Forecasting can help businesses spot consumer trends, allow project managers to set realistic timelines, or help warehouses stock sufficient inventory.

For a deeper look, CBT Nuggets trainer Knox Hutchinson talks about the value of forecasting in his free Intro to Business Intelligence course. He primarily teaches Tableau and PowerBI courses. But the role of data in forecasting remains the same regardless of platform.

Excel includes a number of trend and forecast functions to automate the creation of charts far beyond a manual trend line computation. Forecasting functions such as Slope, Intercept, Forecast, Trend, and Growth are designed to tackle the complexity of building forecasting models from a simple line of best fit to logarithmic or exponential relationships.

SQL in Excel

For nearly three decades Excel has served as the dominant business spreadsheet. In that time, massive worksheets full of critical company data have accumulated — so much so the dividing line between databases and spreadsheets has become blurred.

For proper data integrity, databases store large data sets, while spreadsheets are used to actually crunch the numbers. Reluctance to export these massive Excel worksheets to a database comes from a gap in the knowledge required to connect Excel to the database. Here's the secret: You don't even need to learn SQL Server to open a database connection.

It turns out that it's easy to open a database connection and perform SQL queries on the database to retrieve precisely the data that is needed. You don't need to be a database guru to build the query, as Excel contains a Query Wizard that prompts you through the process.

On the Data tab, you just select "Get Data… From Other Sources… From Microsoft Query", which activates the Query Wizard to prompt you through the process of selecting exactly the rows and columns you require.

It's Not All or Nothing

Just because Excel does all these things doesn't mean it's the best tool for every job. Sometimes it is. Sometimes it's not. Sometimes Excel can be made even more powerful with a little code — or the know-how to combine it with other technologies.

Python is absolutely the right heavy-duty data tool, but Pandas works really well with spreadsheets. Spreadsheets shouldn't replace proper relational databases. To the contrary, it's better to connect a database to an Excel sheet. Tableau and PowerBI are making business intelligence easier for everyone, and even inspiring a self-service BI model. Those programs, too, benefit from data that's been pared down in Excel.

There's one benefit of Excel over the other technologies. Excel is much easier to learn than Python, SQL, Tableau, or PowerBI, particularly when you're learning from CBT Nuggets trainer Simona Millham.