Uses of Microsoft Excel

Last Updated on August 3, 2018 by Dave Farquhar

Microsoft Excel is one of the most popular pieces of software ever. Many job descriptions cite familiarity with Excel as a requirement, or at least desirable. But no one was born knowing it. So let’s take a look at some uses of Microsoft Excel.

Calculations

Initially, spreadsheets were designed to replace ledgers. Plug numbers or formulas into cells, and it would calculate the values. And then you could easily work through what-if scenarios by changing one or more of the cells.

As a landlord, when I consider buying a house, I track all of the repairs it needs and estimate the cost. When I get home, I plug all of those numbers into Excel and use it to help myself figure out if the house is worth buying. By adjusting the numbers, I can quickly build both a best-case and a worst-case scenario to help myself make a decision.

It’s unusual for me to make a financial decision without sitting down and plugging some numbers into Excel.

Make a budget or get out of debt

Here’s a subset of this. You can find plenty of examples online of Excel-based budget and debt repayment calculators. These let you enter your monthly bills and create a budget, or create a plan for getting out of debt based on criteria you enter.

Creating a budget on your own is easy enough by entering your monthly expenses line by line, as long as you can remember them. A debt repayment plan can be a little trickier because of the interest calculations. It’s nice to be able to download and use a canned spreadsheet with the interest calculations already figured out.

Data analysis

By day I work in a field called vulnerability management. All of the tools I use can output data in a tabular format compatible with Excel. Frequently I’ll load this data into Excel and analyze it. Generally there are a handful of things I’ll search for in the data first, and if I find any of them, I’ll probably filter the data so I can quickly determine how common those problems are.

Frequently I’ll also create what’s known as a pivot table, which tells you how frequently certain values occur in the data. I can pivot on vulnerablity data to find out how many Windows systems a company has versus the number of Linux systems it has. I also like to pivot on the vulnerability count to see if the number of vulnerabilities per system is consistent, or if one type of system tends to have more problems than the other.

Pivot tables can be incredibly time consuming on large datasets, but if your data is a reasonable size, or you have a powerful enough computer, you can turn use them to turn data into insights pretty quickly.

Network management

As an IT professional, I’ve used Excel to track, manage or manipulate IP addresses. Excel isn’t really designed for IP addresses so it’s not always ideal, but it’s something we always have available so we make it work. Its advanced string handling functions can do a lot of things. I’ve also used it to extract MAC addresses embedded in other data from Qualys scan resuts.

A simple database

Databases aren’t really supposed to be among the uses of Microsoft Excel. But that doesn’t stop people from doing it. I used to work with a guy who would build elaborate databases with Excel. He’d correlate data from various sources and in some cases let you query it. He had one database that would let you plug in someone’s name and it would tell you what systems they had accounts on.

You’re not supposed to use Excel for a database because it’s slower than a real database. I’ve also seen its lookup functions stop working mysteriously. But there’s a fine line between merging a couple of datasets and creating a full-blown database. Sometimes you don’t know you’ve crossed it until long after you cross it.

Charts and graphs

The big draw for Excel in its early days was that it drew nicer charts and graphs than its competitors. Charts and graphs remain one of the biggest uses of Microsoft Excel. You can create a table of data, then use a chart or graph to visualize the data to make it easier to understand. I’ll use pie charts to break down the categories of vulnerable software on a system. I’ll use line charts to track the number of missing patches month over month. If we’re better than we were last month, I’m content. If we’re worse than we were last month, I’d better be able to figure out why. And if there’s no good reason, I get cranky.

Limitations of Microsoft Excel

Excel can only handle two million rows of data. And 32-bit Excel gets really slow when you load hundreds of thousands of rows into it, let alone millions. Most companies have a lot more data than that, and want to learn from it. That requires heavier tools than Excel. It may mean loading the data into Access, or better yet, a SQL database.

But still, the uses of Microsoft Excel are so numerous, it’s one the best things you can learn to get a better job. You’ll use it constantly.