Like, whatever you want it to do.
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."
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?"
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. ❤️