Here's to You, Microsoft Excel

[rowyn]

Shout-out to Microsoft Office's Excel, the Swiss army knife of computer applications, the Spreadsheet Program That Could.

Like, whatever you want it to do.

It can.

Was this a good idea? MAYBE NOT BUT LET'S DO IT ANYWAY.

On my first exposure to spreadsheet programs, I found them confusing: "I don't understand how this works." One of my friends, Telnar, said "You know how word processors are programs for using text? Spreadsheets are word processors, but for numbers." And I thought, "Oh, that makes sense."

And Excel thought, "Cool, but WHY STOP THERE."

At some point in the 90s, Microsoft decided to watch how users actually used the various programs in the Office suite. "What kind of numbers do our users crunch in Excel?" they asked. "What sort of functions do they need?"

And the users said "Well, we don't really use this for numbers that much."

MS: "Uh. Huh. But you use it?"

Users: "OH YEAH IT'S GREAT FOR LISTS."

MS Excel developers: "That sure is a thing. So. HOW CAN WE MAKE THIS PROGRAM BETTER FOR LISTS."

Users: "Really we want to store a lot of records, like lists of our customer's names and addresses in a format that we can sort and filter and stuff."

MS Excel: "You mean like ... a database?"

Users: "What's a database?"

MS Excel: "That thing you just described. Did you know Access comes bundled with MS Office, just like Excel?"

Users: "No. Also, databases sound scary and we like you, Excel."

MS Excel: "Okay ... uh ... sure, we can pretend to be a database, I guess. I don't know if this is really a good idea ... "

Users: "WE LOVE IT THANK YOU."

MS Access: "... this was supposed to be my job, why doesn't anyone love me."

MS Excel: "WE DON'T KNOW please stop being scary so we can stop doing your job. Also, we're going to add pivot tables to give better reports on the database information that users keep putting in spreadsheets."

Users: "What's a pivot table?

MS Excel: "It gives you statistics on the data in a database. Like the total number of customers who live in Michigan and joined in 2017."

Most Users: "That sounds scary I don't think I can do that."

Jane in the back: "AW YEAH I LOVE IT."

MS Excel: "Okay, well, we already added it, so, uh, you're welcome, Jane."

Jane: *BIG THUMBS UP*

Users: "Howabout you add charts? Like pie charts, maybe, or bar graphs, or line ones perhaps?"

MS Excel: "OH YEAH LET'S JUST DO ALL OF THOSE AND ALSO TWELVE MORE KINDS IN INCREASING ORDER OF OBSCURITY."

Users: "... sure that works."

Some users: "I'd like to be able look stuff up. Like I want a formula that checks to see if Mary Watson is in my address spreadsheet and then tells me what her address is."

MS Access: "You know what is great at this? A DATABASE PROGRAM."

Some users *sidle around Access, look hopefully at Excel*

MS Excel: "We're gonna call this formula "VLOOKUP", or "HLOOKUP" if your data is stored horizontally."

Some users: "You're the best, Excel!"

John: "Oh, hey, I'd love it if I could tell Excel to do the same set of operations on a spreadsheet over and over again. Like I get this spreadsheet from my distributor every month but the columns are in the wrong order and it has a bunch of records on international sales that I don't care about, and other stuff. Anyway I do this same fifteen-step process on every file and it'd be great if I could just press a button and have Excel do all fifteen steps."

MS Access: "HAVE YOU CONSIDERED USING A DATABASE PROGRAM FOR YOUR DATABASE NEEDS."

John: *hides in terror until MS Access is gone, sneaks over to Excel, whispers* "so can you help me?"

MS Excel: "We can add that! Here, press this 'record macro' button and then do your fifteen steps, then press 'stop'. Now you can replay that macro the next time you need to do those same things on a file."

John: "Thanks!"

Most users: "What's a macro?"

MS Excel: "... we just said?"

Most users: "can we pretend it doesn't exist, it's scary?"

MS Excel: "Sure, our program works fine even if you ignore 99% of our features."

Most users: "WHEW."

Pat: "So this macro thing is great but sometimes I want to be able to tell Excel to do things automatically that are more complicated than what I can record. Like I want it to dynamically change the range of cells that it's operating on, or whatever."

MS Excel: "So, huh. You want to be able to program Excel, basically?"

Pat: "Yeah, sounds good."

MS Excel: "Okay, well, we have this programming language that records the macros, so we can just expand it and users can write programs in it."

MS Excel developers: "Are we really putting an entire programming language in our spreadsheet program?"

MS Office: "Actually, we're gonna put it in all our products."

Most Users: "TOTALLY PRETENDING THIS DOESN'T EXIST WHAT IS IT EVEN IF WE WANTED TO BE PROGRAMMERS WE WOULDN'T NEED YOUR APP."

Pat: "AW YISS PERFECT."

Robin: "Hey, Excel, can you make it so I can import stuff directly from my SQL database to a spreadsheet?"

MS Access: "WHY AM I EVEN HERE."

MS Excel: "I DON'T KNOW MAYBE IF YOU TOOK OFF THAT ZOMBIE MAKEUP AND THE VAMPIRE FANGS PEOPLE WOULD LIKE YOU ACCESS"

MS Access: *lifts vampire cloak, hisses*

MS Excel: "So, Robin, you want to do database reports in ... Excel?"

Robin: "YES."

MS Excel: "You're sure you don't want to use a SQL query builder or report writer for this instead?"

Robin: "I ONLY WANT YOU, EXCEL."

MS Excel: "... okay, we basically do anything a user has ever asked us for, so here you go."

Confession: in my 25+ years of using Excel, I have used every one of these features. I am pretty sure this means there is a WHOLE BUNCH MORE STUFF THAT EXCEL CAN DO. Does it julienne fries? PROBABLY.

Is it a good idea to build a form in Excel that uses VBA to hide and reveal parts of the form based on data entered and then send that form's data to a different spreadsheet which stores it like a database? Possibly you should have hired an actual programmer for your programming needs but EXCEL IS NONETHELESS HERE FOR YOU.

Like any Swiss army knife, Excel is not necessarily the BEST tool for any particular job, and sometimes using it for a given job turns out to be a TERRIBLE MISTAKE. But I love you anyway, Excel. You're the best. ❤️

Comments

[terrycloth]

*giggles at Access Vampire*

[rowyn]

MS Office: "Hey, Access, we're just gonna open up the curtains in here, let some light in. Users would love you if they could just see you!"

MS Access *hisses, cowers behind cloak* "NO THE LIGHT IT BURNSSSSS."

[lilfluff]

Poor Excel has to keep doing what Access should be doing partly because Access is still stuck in database training pants. My previous position at work had me doing a lot in Excel and occasionally I'd look at what i was doing and say, "This should be a database," then I'd try to do it in Access only to run into, "Oh, s-sorry, you'll need to talk to my big brother SQL Server. He says I'm not ready for complicated stuff like that." (Where the complicated thing was nearly anything beyond lesson four of an SQL 101 class, and supported by nearly any other database on the market more powerful than the old MS Cardfile or the old unmissed MS Works) After which I'd spend a minute cursing Microsoft, done dreaming about IS actually giving me access to SQL Server or allowing the installation of Server Express locally. Then I'd fire up Excel and put together an abomination of formulas, macros, and VBA. I highly suspect DBase probably could have done some of what I'd wanted.

[rowyn]

Huh, that's interesting. I've converted two "we used to use Excel for this" projects into Access, and my department has done three more, IIRC. It's a beast to set up because Access is a bear, but the end result has worked fine in four of five cases. The fifth case suffered from MAJOR scope creep.

Us: "Okay, we're going to take this spreadsheet that is so overstressed and enormous from trying to do too much and make it a database instead."
Users: "While you're doing that anyway, can you add [X]?"
Dev: "Sure."
Users: "And [Y] and [Z]?"
Dev: "Okay!"
users: "And the entire rest of the alphabet?"
Dev: *does this even though it's WAY TOO MUCH*
Dev: *quits, leaving rest of department, none of whom have database development experience, to maintain boondoggle. It is too resource-intensive for the available software or maybe hardware, we can't even tell, and fails a lot*

I am not really sure what went wrong on that database. It may actually be the hardware it's set up on, or the way it's set up, rather than the fault of Access or the database code at all. Because we had the one guy who used to be a database developer and no one else in the department with experience in it, so there's just any number of things that could have gone wrong that aren't specifically "Access is not powerful enough to do this, period, even though it will try."

My two databases do their jobs fine, but they are VERY narrow use cases, so they're not nearly as much trouble.

[lilfluff]

On reflection much of what I did was probably best done with some sort of statistical package or specialized reporting software. But I was hoping Access+Excel would work. By the time I was tempted to learn R or Power BI or smash together some Python or C# I was moving to a new department.

The one that probably annoyed my the most was temporary tables. I got so excited because the documentation implied it would work. It was only after it failed and digging deeper that I found the notes saying that what was meant by Access supporting temporary tables in queries was that it worked if the backend supported them but that the default JET did not. (And why, why is Access still using JET as it's database engine?)

[archangelbeth]

*howls with laughter*

I forget which version of Excel ze spouse did a GURPS character sheet on. Like, it could look for skills and automatically add them to the cells and bump the cells down, and calculate skills, and such.

Then they made the language and macros not work. It was sad.

That said, I wonder if MS Access would be a better place to store all my Star Trek Online duty officer stats. >_>

[rowyn]

So, seriously:

The area where Access is strongest is if you need to normalize the data in your database. What that means is "Do you find yourself repeating the same pieces of data over and over again in your table, and so if you want to update that data, you have to change it in 12 places?"

To use an example: let's imagine Amazon storing the sales information on your books. If Amazon did this on a spreadsheet, each row would list the book title, series title, price, royalties, cover, author name, author account number for payments, and author contact info.

If you normalize this data, then you'd move the author info to an author record on a different table, and the books to a product record on a different table, and the sale information would be a sale record that would link to the author table and the product table. Now when your author contact or account info changes, you just have to update the author record and not every sale.

The other place where Access can be better than Excel is in reports. Let's say you want to know "all of the times that book A, B and C sold at Y price, and also all the times that book D and E sold at Z price". Excel can't do this with just a filter -- you end up doing hacks like "copy all the records matching your first set of criteria and then all the records matching the second set" or using VBA or whatever and it's a pain. Whereas Access lives for this stuff and you can write one query that will do it for you.

So the answer probably depends on "do you often find yourself wanting to do complex searches on your STO dury officers?" and "do you often need to update the same information in multiple places because of a single in-game change?"

If you don't need to do either of those things, then treating Excel like a database works fine. And Excel genuinely is easier to use than Access. The Access UI is made of disappoint. v_v

[archangelbeth]

Ahhhhh.

Sounds like I keep it on Excel, then... Mostly I'd want to sort by "what special abilities/traits does this Doff have," "what are the names of all my [species] Doffs, because I want to mine them for naming my own captain/bridge officers/fanfic characters," and "where did this doff come from?"

So I'd be doing various random sorts on various random things, really.

[rowyn]

Yeah, the only advantage Access would have there is if you want to filter by "this OR that", like "everybody who is this species OR has this ability, and didn't sound like you need that, or at least rarely need it. :D

[archangelbeth]

I'd be more likely to want to filter by AND (species and ability or species and profession), but somewhere in there, it's just easier to do a search as if it were in Word, or sort by X and then Y (sort by: species first, ability second).

Tools aren't scary, problems are

[(Anonymous)]

You don't need to be afraid of my little buddy Access. He's good at medium sized problems and was written to be more like Word and Excel -- if not quite enough like them to make a good first impression.

If you have a problem which might get too big for Excel, you can start with me, SQL Server express. It won't cost you a penny, and I can do most of what my big brother can. So, if your data set gets too big (say, more than 2 GB), switching to software which can fight that scary data with you doesn't require any changes.

[Scurries to close the window before sunrise]

-- Telnar

Re: Tools aren't scary, problems are

[rowyn]

I wish your header was true! I am more open-minded about using new tools than most people at my workplace, and even I have to fight off the urge to keep using a long complicated manual process rather than spending the time to learn a tool that could make it easier.

In fairness to my co-workers, my bank does have a long history of choosing really bad tools. x_x (Not referring to MS Office, here, but to specialized software.)

Re: Tools aren't scary, problems are

[(Anonymous)]

I guess that I have acquired a visceral aversion to frequently repeating a long complicated manual process, so I start to think about what more powerful tools might exist the 2nd time that process comes up.

Perhaps others have had worse experiences with tools than I had (and not getting to select your own tools can certainly contribute to that), or never tried them.

— Telnar

[rowyn]

So true! XD

"MY SWISS ARMY KNIFE CAN DO ANYTHING
JUST WATCH ME BUILD THIS HOUSE WITH IT."

[archangelbeth]

You've seen https://www.boredpanda.com/73-year-old-excel-paintings-tatsuo-horiuchi/?utm_source=google&utm_medium=organic&utm_campaign=organic , right?

[green_knight]

(here via archangelbeth)

Mwahahahaha.

I'm a Filemaker developer. I've used Filemaker since Filemaker II (it's now on Advanced 18) and I've used it for every problem under the sun and I could never understand why people thought databases were scary.

And then, one night, in a darkened office, I ran into the spectre of MSAccess.

Now I know.

[technocracygirl]

I found this from a link on another DW, and this a)bleeping hilarious! and b) ridiculously true.

Thank you very much for writing this.

[tuftears]

There needs to be a way to tweet links to DW entries automagically!

[filkferengi]

The Faithful Sidekicks have a fun song, "SQL Love": https://www.youtube.com/watch?v=NQJ51DNO33s

:)

You had me at "Here's to you Microsoft"

[(Anonymous)]

I worked in IT 30 years (Server admin, Programmer Analyst, Database Admin) and now happily retired.
-
This is a great description of Access/Excel. (Had a love-hate relationship with everything Microsoft).

Browsed through a few years of your journal. Just love your writing style and choice of topics.

Denis (Ottawa, ON).

[asakiyume]

Here via Haikujaguar's recent Patreon post, and I can tell you I am **exactly** like your most-of-your-users with Excel... and also I once had to try to use Access, and it was so awful, so terrifying--I too would vastly prefer to make Excel do whatever of those jobs I need it to do.

(The chart thing made me laugh in particular... I had to quick-fast learn Excel for serious last year because I had to teach it to some students [hahaha, so funny....], and they were so dazzled by the charts--the ones that look like the tips of lances, all lined in a row!)

[rowyn]

Sometimes you gotta withhold from people that Excel can do charts, lest they spend weeks doing nothing but making charts. :D

[zetasyanthis]

Brilliant. XD

(And I'll cop to a) knowing SQL and b) giving in and using VLOOKUP finally a few months ago. I still feel dirty. >.<)