Microsoft Excel

[kphoger]

Does anyone else here spend most of their work life in Microsoft Excel?

If so, then what do you think are the most powerful tools it has to offer?

Several years ago, I learned how to use conditional formatting and–more useful still–conditional formulas.  Then, maybe two years ago or so, I learned how to use pivot tables, and that has proved to be the most important thing in Excel by far that I've learned.  Then, last week, I finally got around to figuring out how to use lookup functions.

[GaryV]

Excel is one of the worst things unleashed upon us by Microsoft.  I detest working with it.

If you want to do database work, use a database.  If you want to transfer data, use XML, delimited text files or proprietary file formats.

[kphoger]

Excel is one of the worst things unleashed upon us by Microsoft.  I detest working with it.

If you want to do database work, use a database.  If you want to transfer data, use XML, delimited text files or proprietary file formats.

I do balancing and reporting.  Quite useful for my purposes.

[Scott5114]

The reports I send out every week at the casino are in Excel. They don't use anything more advanced than basic addition and subtraction formulas, though.

The reports I compile for the two businesses I own are done in LibreOffice Calc. Not only does this let me avoid the exorbitant MS Office license costs, it doesn't have the obnoxious ribbon interface that all of the recent Offices have. (Most of the time, when I try to do anything halfway advanced in Excel, I end up hunting around the various ribbons for 30 minutes, give up, Google it, then find that feature isn't enabled on the ribbon by default so I have to manually force it to show up.)

I was pretty impressed when I got to use the Flash Fill feature in Excel for the first time. This detects when string data is changing by some identifiable pattern, and attempts–it's not perfect–to fill in other columns based on it. So far this is the only Excel feature that I miss in LibreOffice.

Excel is one of the worst things unleashed upon us by Microsoft.  I detest working with it.

If you want to do database work, use a database.  If you want to transfer data, use XML, delimited text files or proprietary file formats.

Spreadsheets existed before Excel was a thing. If Microsoft had never been founded, you'd be saying the same things about Lotus 1-2-3.

I'm a fan of relational databases as much as the next guy, but there is some degree of setup work to establishing them. Some things just aren't important enough to bother spinning up an SQL server, thinking up a solid database schema, or putting together a comfortable interface. And do you want to be the one to teach middle manager Sherry what an SQL query is?

You should never use a proprietary file format, especially if an open one that does the same thing exists. To use a proprietary format is to invite data loss by the responsible company breaking the associated product, not updating it for newer OSes, or going out of business.

[jeffandnicole]

I use Excel every day.  There's probably over 30 files I need to look at in my normal workday.  My largest files have about 160 or so tabs, or individual spreadsheets.  My formulas can sometimes be up to 6 lines long (I drag the formula time up top down to see the entire thing at one time.  A few of my larger spreadsheets have over 250 columns, representing each business day of the year. 

I use multiple imbedded if/then formulas.  You mentioned lookup formulas.  What I found much more useful for my doing is Index/Match formulas.  They work like lookup formulas, but usually you don't need the data to be in ABC or 123 order, so it's a lot more versatile.  Hyperlinks - both the option and the formula - are a standard feature in my files.  Conditional formatting, once it works right, is so useful there's very important information I don't even look at because the formatting highlights exactly what I need to know.

GaryV hinted at using other programs as a Database, but that's how I utilize Excel the best.  Databases would probably work better, but here's the thing:  I don't know how to use a database program.  Other than Access, I don't have another program available to me at work.  And I'm not going to try to learn how to use Access...only for me to need to teach up to 60 others that will need to view the databases.  However, everyone I work with has a general knowledge of Excel, and I can show them the few additional things they need to know to utilize my Excel files.  Many of my files are either emailed to numerous people, or utilized directly within my section.  Very few of my files are only used by me, so I have to make sure others know how to use the data I present.

Even with all of this...I've never used Pivot tables.  Some day, I'll learn them.  But if you want to know how to write a single formula that includes iferror, if-or-and-then, and index-match statements, let me know!

[kphoger]

I was pretty impressed when I got to use the Flash Fill feature in Excel for the first time. This detects when string data is changing by some identifiable pattern, and attempts–it's not perfect–to fill in other columns based on it. So far this is the only Excel feature that I miss in LibreOffice.

What I love is knowing how to override it.  Click on a cell that has the number 303, then flash fill-down, and Excel will likely assume you want to increase by one each row:  303, 304, 305, 306...  But, if you want it to be 303 for every row instead, then simply hit Ctrl+D after flash filling-down, and they all instantly change to 303.

[SEWIGuy]

Not only do I use it a lot for work, but I have five files that have all of our personal financial information.  Easy and intuative program.

[Scott5114]

I have five files that have all of our personal financial information

yikes

[SEWIGuy]

I have five files that have all of our personal financial information

yikes

Yikes what?  I don't have account numbers or personally identifible information in them or anything.

1. Our budget.  Details income, expenses and savings targets for the next few months.  I have that mapped out right now through December.
2.  Credit card charges
3.  Charitable contributions for the year
4.  Retirement & Investment Balances
5.  Estimated federal and state income tax obligations and payments.  (This one is the coolest since I have formulas estimating our income and tax obligation based on marginal tax rates, witholding, estimated tax payments, deductions, etc.  It's fun to play around in.)

[Scott5114]

Storing all of that information in an unencrypted Excel file on your computer is still a security risk. If someone gains access to the computer, they now know your total financial picture and where you keep your accounts. Chances are they can get personally identifiable information on you through some other file on your computer, since they're not going to look at just those Excel files. (Do you have zero files on your computer containing your name or address? Are you sure? Not even in your browser cache/autofill? Or your email client?)

From there it's a simple connect-the-dots process to social engineer access to the accounts themselves.

[jeffandnicole]

Storing all of that information in an unencrypted Excel file on your computer is still a security risk. If someone gains access to the computer, they now know your total financial picture and where you keep your accounts. Chances are they can get personally identifiable information on you through some other file on your computer, since they're not going to look at just those Excel files. (Do you have zero files on your computer containing your name or address? Are you sure? Not even in your browser cache/autofill? Or your email client?)

From there it's a simple connect-the-dots process to social engineer access to the accounts themselves.

I keep Excel files of my stuff as well.

People have mailboxes outside their home.  Anyone can walk up, take it, walk away, and they'll have account numbers, names and addresses of everyone in that house.

People write checks.  That is a laughingly insecure document.  It has your name, address, bank name, your checking account number, and the bank's routing number.  It has everything you need to wipe your account dry.

The security risk on computers is very low.  If they're able to break into your computer, chances are they will easily bypass the encryption feature.

The Equifax breach had more to do with IDs being stolen than random personal computer break-ins.  And talk about knowing one's entire financial history...it's nearly all right there!

[SEWIGuy]

Storing all of that information in an unencrypted Excel file on your computer is still a security risk. If someone gains access to the computer, they now know your total financial picture and where you keep your accounts. Chances are they can get personally identifiable information on you through some other file on your computer, since they're not going to look at just those Excel files. (Do you have zero files on your computer containing your name or address? Are you sure? Not even in your browser cache/autofill? Or your email client?)

From there it's a simple connect-the-dots process to social engineer access to the accounts themselves.

I will take my chances. 

[RobbieL2415]

I almost like Access better. More automation and aggregation tools. If I need something in a XLSX format I can have Access export a copy.

[Scott5114]

I almost like Access better. More automation and aggregation tools. If I need something in a XLSX format I can have Access export a copy.

Excel is a math/statistics tool; Access is a database tool. Lots of people use Excel for things it isn't meant for, because the interface is more intuitive than Access, or they don't understand what Access is for.

[Max Rockatansky]

I use it quite often at work but I don't build the data sheets that I once did years ago.  I spend most of my time picking apart other people's Excel documents for the data that I want which fortunately is incredibly easy to do.

[ozarkman417]

My father claims he used the software almost every day at his banking job and still uses it to keep track of budgeting. Online school as a result of the pandemic has been very reliant on the Google apps suite, particularly Google Docs and Slides. Google's equivalent to Excel is Google Sheets, which is used much less than the other two. If an assignment requires a table or a graph/cart, sheets is the go-to app. The rationale for using Google over Office is the district's usage of cheap Google Chromebooks, as well as its integration with Google Drive (district accounts have no storage cap, though relying on it as a long-term storage method is a bad idea, as the data will be wiped in two years).

[SEWIGuy]

The sharing of Google Docs and the ability to have multiple people working on it together is much better than Word. Extremely convenient.

[jeffandnicole]

I almost like Access better. More automation and aggregation tools. If I need something in a XLSX format I can have Access export a copy.

Excel is a math/statistics tool; Access is a database tool. Lots of people use Excel for things it isn't meant for...

Heck...i used it to design my kitchen/utility room area when I was remodeling it. I resized the rows and columns so the cells were squares. Each cell equaled 1 square inch of the room.  It's a small area, roughly 10'6" by 13'10", and I really needed to maximize the space. I went thru about 50 various designs. Some were nearly identical with minor tweaks. Other designs were unworkable. But with Excel I could constantly save a design, open a new tab, copy the design over and play with it some more.

[kalvado]

There is an interesting book "Humble Pi: When Math Goes Wrong in the Real World"  by Matt Parker with a whole chapter dedicated to excel.
Fair use quote:

In 2012 JPMorgan Chase lost a bunch of money; it's difficult to get a hard figure, but the agreement seems to be that it was around $6 billion. As is often the case in modern finance, there are a lot of complicated aspects to how the trading was done and structured (none of which I claim to understand). But the chain of mistakes featured some serious spreadsheet abuse, including the calculation of how big the risk was and how losses were being tracked. A Value at Risk (aka VaR) calculation gives traders a sense of how big the current risk is and limits what sorts of trades are allowed within the company's risk policies. But when that risk is underestimated and the market takes a turn for the worse, a lot of money can be lost.
Amazingly, one specific Value at Risk calculation was being done in a series of Excel spreadsheets with values having to be manually copied between them. I get the feeling it was a prototype model for working out the risk that was put into production without being converted over to a real system for doing mathematical modelling calculations. And enough errors accumulated in the spreadsheets to underestimate the VaR. An overestimation of risk would have meant that more money was kept safe than should have been, and because it was limiting trades it would have caused someone to investigate what was going on. An underestimation of VaR silently let people keep risking more and more money....
...
Specifically, after subtracting the old rate from the new rate, the spreadsheet divided by their sum instead of their average, as the modeler had intended. This error likely had the effect of muting volatility by a factor of two and of lowering the VaR.

[jeffandnicole]

One thing I learned about spreadsheets, especially complicated, formula-intense spreadsheets: try not to make any changes that will take effect on Friday, or before taking time off. If there's an error that's not discovered until late in the day, the effects can last all weekend, or you have to deal with it on your time off.

In the example kalvado posted above, those issues can be incredibly hard to detect. But it also speaks to the limitations many people have with Excel. It's like driving a car. Some people may say they're good drivers, but are they?  Some people may say they're good Excel users, but when you find out the basic sum formula confuses them, it's too late.

[qguy]

I almost like Access better. More automation and aggregation tools. If I need something in a XLSX format I can have Access export a copy.

Excel is a math/statistics tool; Access is a database tool. Lots of people use Excel for things it isn't meant for, because the interface is more intuitive than Access, or they don't understand what Access is for.

I haven't used Access for a long while because I haven't needed to, but even when I might have, I learned to use Excel if I could, because for me it's so much more intuitive.

If I learn something in Excel, it's usually like riding a bike. I never forget it, even though I may get a little rusty and have to brush up a tad. With Access, for me, it's use-it-or-lose-it. If I didn't use Access for a week I'd forget practically everything I knew about it and need to learn it all over again. I just don't need that kind of aggravation if I can help it.

[NJRoadfan]

The Humble Pi example is true in other top companies in the US. A friend of mine works at one and has built elaborate business processes around macro heavy pivot table filled Excel spreadsheets. I shake my head at that mess, much of it can be replaced with REAL code with no manual cutting and pasting of data coming from reports. Although I'm partly to blame when I showed him what that Macro button was for. Most people use the program as a flat file database though, something it wasn't meant for.

A real relational database with full SQL query capabilities is much better suited for that, but there is the setting up curve and the learning curve that most won't deal with. Access technically can do all this, but becomes annoyingly limited in other areas. Its likely best being used as a front end to something hosted on MS SQL Server and not much else!

Google kinda "bridged the gap" with Sheets though.

[webny99]

Fun thread!

Microsoft hierarchy: Excel > Word > Power Point

I use Excel a lot. I took an Excel course in school, so I know quite a bit about it, and would call myself a better-than-average Excel user with some degree of confidence - although one can never be too sure about that.

When I first saw this thread, I was going to say I mostly use Excel for fun. But the more I think about it, I use it quite a bit at work, too. Probably at least 5-6 times a day: nothing too intense, but it's definitely an essential tool for data entry, reporting, and staying organized. Auto Sum is a fan fave, of course - that goes without saying. I'd go crazy without the ability to drag formulas down columns. And the ability see the sum and average of highlighted cells is super handy, too.

As for the fun stuff: there's the old history of me starting stats-related threads on this forum, which I would have used Excel to compile at the time. Least said, soonest mended on that subject, but that's really the least of my Excel adventures: I have many other sheets going at any given time. They're mostly road or population-related in some way. The biggest one has about 12 sheets, comparing 2000, 2010, and (soon) 2020 census population data for every region, county, and town in New York State. I got all the prep work done earlier in the year, so it's just been sitting around waiting for the 2020 census results for several months now.

One other quirk: I almost always use the "borders" feature and add my own borders to whatever cells I'm using, rather than just using "print gridlines" if needed. I often have several distinct tables on a single sheet, so border options like "outside border", "thick outside border", and "bottom double border" really come in handy.

I resized the rows and columns so the cells were squares.

Guilty as charged. I have done that many more times than is healthy! 

[Scott5114]

I almost like Access better. More automation and aggregation tools. If I need something in a XLSX format I can have Access export a copy.

Excel is a math/statistics tool; Access is a database tool. Lots of people use Excel for things it isn't meant for...

Heck...i used it to design my kitchen/utility room area when I was remodeling it. I resized the rows and columns so the cells were squares. Each cell equaled 1 square inch of the room.  It's a small area, roughly 10'6" by 13'10", and I really needed to maximize the space. I went thru about 50 various designs. Some were nearly identical with minor tweaks. Other designs were unworkable. But with Excel I could constantly save a design, open a new tab, copy the design over and play with it some more.

Worst spreadsheet abuse I've done was during the pandemic, when I did a small pixel art icon for a friend and we were going back and forth over changes that needed to be done. Rather than trying to communicate which pixel needed to be moved where (which got frustrating), it ended up being easier to set up a Google Sheets spreadsheet with square cells, use cell background colors to recreate the icon, then set it as editable so we could both tinker with it at the same time.

For a room design project, I'd probably reach for Inkscape before I considered using Excel.

[NJRoadfan]

Creating art in Excel is a thing: https://mymodernmet.com/excel-art/