VLOOKUP is dead (not really). Long live VLOOKUP
September 25, 2019 9:42 AM Subscribe
Microsoft introduces a new, more powerful Excel lookup function called XLOOKUP.
One of the first functions most Excel users learn (and one of the most frequently used) is VLOOKUP (previously).
XLOOKUP combines VLOOKUP, HLOOKUP and some uses of INDEX/MATCH. Two of the most immediate differences: XLOOKUP allows you to return values to the left of the lookup column, instead of only to the right. It also allows you to return an array or range for further manipulation. Both of these are possible with combinations of other functions, but Microsoft has folded them into a single function that should be easier to use.
Support page
Some fawning
Note: XLOOKUP isn't widely available yet, see the announcement for details.
One of the first functions most Excel users learn (and one of the most frequently used) is VLOOKUP (previously).
XLOOKUP combines VLOOKUP, HLOOKUP and some uses of INDEX/MATCH. Two of the most immediate differences: XLOOKUP allows you to return values to the left of the lookup column, instead of only to the right. It also allows you to return an array or range for further manipulation. Both of these are possible with combinations of other functions, but Microsoft has folded them into a single function that should be easier to use.
Support page
Some fawning
Note: XLOOKUP isn't widely available yet, see the announcement for details.
From the announcement: "In fact, it’s so fundamental to spreadsheeting that most users can recall the sense of achievement they felt when they first mastered VLOOKUP, myself included."
I remember way back when, thinking "there has to be a way to match these columns", and the day I finally figured out how to do it with VLOOKUP (using the built-in help files, since online support wasn't....great), I ran up and down the halls of my office, shouting joyfully.
posted by Gorgik at 9:50 AM on September 25, 2019 [23 favorites]
I remember way back when, thinking "there has to be a way to match these columns", and the day I finally figured out how to do it with VLOOKUP (using the built-in help files, since online support wasn't....great), I ran up and down the halls of my office, shouting joyfully.
posted by Gorgik at 9:50 AM on September 25, 2019 [23 favorites]
This is of great interest to me, and my workflow. VLOOKUP is not the first function I learned (that would have been much simpler things, like "SUM" or "AVERAGE" or even "COUNTIF"), but it's been the most powerful for creating ad hoc databases (e.g. contacts for projects too small to bother with CRM software).
HLOOKUP is new to me - my data is not often laid out in a way that makes this work (because I designed the tables to work for VLOOKUP), but I did have a situation where this might have been useful.
posted by jb at 9:51 AM on September 25, 2019 [1 favorite]
HLOOKUP is new to me - my data is not often laid out in a way that makes this work (because I designed the tables to work for VLOOKUP), but I did have a situation where this might have been useful.
posted by jb at 9:51 AM on September 25, 2019 [1 favorite]
Also, I'm minorly ashamed to admit: I had no idea what "VLOOKUP" might mean, nor did I care. All I knew is that how it works. It's like a secret incantation, some magic that matches my data.
posted by jb at 9:52 AM on September 25, 2019 [4 favorites]
posted by jb at 9:52 AM on September 25, 2019 [4 favorites]
So I'm recording my diet in Google Sheets and I have to manually SUM every meal to see if I am reaching my meal subtotals. Is there a way to do this automatically? Right now I have to select a column, say the Calories eaten for all the entries I had at lunchtime, and use Sheets Explore to drag SUM into a new cell. I don't eat the same number of items every meal every day, so manually selecting the ranges is very tedious.
posted by polymodus at 9:55 AM on September 25, 2019 [2 favorites]
posted by polymodus at 9:55 AM on September 25, 2019 [2 favorites]
HOT
posted by Young Kullervo at 9:58 AM on September 25, 2019 [2 favorites]
posted by Young Kullervo at 9:58 AM on September 25, 2019 [2 favorites]
Ok, but tell me what this means on the SQL side (if anything) so I can sound all smarty pants when I talk to my DBA buddy on chat later...
posted by RolandOfEld at 9:59 AM on September 25, 2019 [4 favorites]
posted by RolandOfEld at 9:59 AM on September 25, 2019 [4 favorites]
This is easily the biggest news of the day.
posted by borborygmi at 10:02 AM on September 25, 2019 [16 favorites]
posted by borborygmi at 10:02 AM on September 25, 2019 [16 favorites]
Polymodus - I think you can use Sumif in google sheets, and that should work? So, you can sumif and the criteria is the date and meal.
posted by dinty_moore at 10:02 AM on September 25, 2019 [1 favorite]
posted by dinty_moore at 10:02 AM on September 25, 2019 [1 favorite]
@polymodus: You should be able to do this with a Pivot Table.
Your spreadsheet should look something like this:
Item | Meal | Calories
Eggs | Breakfast 9/25 | 180
Sandwich | Lunch 9/25 | 400
Soup | Lunch 9/25 | 200
(You might want the meal and date to be separate columns, depending on your reporting needs)
Then, your pivot table would have the meals listed in the row and the sum of the calories in the values.
posted by philosophygeek at 10:04 AM on September 25, 2019 [1 favorite]
Your spreadsheet should look something like this:
Item | Meal | Calories
Eggs | Breakfast 9/25 | 180
Sandwich | Lunch 9/25 | 400
Soup | Lunch 9/25 | 200
(You might want the meal and date to be separate columns, depending on your reporting needs)
Then, your pivot table would have the meals listed in the row and the sum of the calories in the values.
posted by philosophygeek at 10:04 AM on September 25, 2019 [1 favorite]
Right up there with pivot tables in the "why isn't this considered mandatory Excel training?" category.
posted by Yowser at 10:04 AM on September 25, 2019 [14 favorites]
posted by Yowser at 10:04 AM on September 25, 2019 [14 favorites]
But yes, I am weirdly excited about this also, but also wondering if this is going to stop the vlookup vs. index match nerd fights, or just give it another contender.
posted by dinty_moore at 10:04 AM on September 25, 2019 [7 favorites]
posted by dinty_moore at 10:04 AM on September 25, 2019 [7 favorites]
Your scientists were so preoccupied with whether or not they could, they didn’t stop to think if they should
posted by Damienmce at 10:06 AM on September 25, 2019 [11 favorites]
posted by Damienmce at 10:06 AM on September 25, 2019 [11 favorites]
Ok, but tell me what this means on the SQL side (if anything) so I can sound all smarty pants when I talk to my DBA buddy on chat later...
Maybe they’ll integrate it into the Lookup function in SSIS?
posted by Young Kullervo at 10:08 AM on September 25, 2019 [1 favorite]
Maybe they’ll integrate it into the Lookup function in SSIS?
posted by Young Kullervo at 10:08 AM on September 25, 2019 [1 favorite]
Simply knowing VLOOKUP elevated me at work to a level of Excel Godliness so I'm so excited about this. I learned about it via Chandoo - I learn so much through his site.
posted by kimberussell at 10:11 AM on September 25, 2019 [19 favorites]
posted by kimberussell at 10:11 AM on September 25, 2019 [19 favorites]
I thought I was hot shit using vlookup. I then only just recently came across index/match, and all but wept for the wasted years of my life. I'm still furious tbh.
posted by ominous_paws at 10:15 AM on September 25, 2019 [14 favorites]
posted by ominous_paws at 10:15 AM on September 25, 2019 [14 favorites]
So I'm recording my diet in Google Sheets and I have to manually SUM every meal to see if I am reaching my meal subtotals. Is there a way to do this automatically? Right now I have to select a column, say the Calories eaten for all the entries I had at lunchtime, and use Sheets Explore to drag SUM into a new cell. I don't eat the same number of items every meal every day, so manually selecting the ranges is very tedious.
Admittedly, with a lifetime of programming computers, I'm not the average use case, but I started with a Google spreadsheet for my weight tracking/calculation of rate of weight change, but soon got to the point where the real solution was a database, so I ended up writing a webapp with a mariadb backend on a linode vps instance.
posted by mikelieman at 10:19 AM on September 25, 2019 [3 favorites]
Admittedly, with a lifetime of programming computers, I'm not the average use case, but I started with a Google spreadsheet for my weight tracking/calculation of rate of weight change, but soon got to the point where the real solution was a database, so I ended up writing a webapp with a mariadb backend on a linode vps instance.
posted by mikelieman at 10:19 AM on September 25, 2019 [3 favorites]
AYYYYYYYYYY
posted by nixon's meatloaf at 10:21 AM on September 25, 2019
posted by nixon's meatloaf at 10:21 AM on September 25, 2019
I learned about pivot tables last year, and I think it was the most useful thing I learned all year. I'm so happy. I used to have to tediously use SUMIF and SUMIFS, and pivot tables make everything so much better. And they're straightforward! (I use VLOOKUP to assign letter grades to my students based on their numerical scores, though.)
posted by leahwrenn at 10:26 AM on September 25, 2019 [7 favorites]
posted by leahwrenn at 10:26 AM on September 25, 2019 [7 favorites]
This...has me extremely delighted and I don't know how I should feel about that.
posted by nikaspark at 10:27 AM on September 25, 2019 [1 favorite]
posted by nikaspark at 10:27 AM on September 25, 2019 [1 favorite]
Right up there with pivot tables in the "why isn't this considered mandatory Excel training?" category.
1) It's a shame that Excel isn't more broadly recognised as a "real" programming language/platform (at least as real as Matlab -- stow it, fellow programmers, I know we all dismiss that, too) and taught as such.
2) If it were, it's possible that most of Excel's current users would have either been too intimidated to learn it in the first place or else gatekept out of learning it by the people who currently dismiss it as not-a-"real" programming language.
posted by tobascodagama at 10:27 AM on September 25, 2019 [8 favorites]
1) It's a shame that Excel isn't more broadly recognised as a "real" programming language/platform (at least as real as Matlab -- stow it, fellow programmers, I know we all dismiss that, too) and taught as such.
2) If it were, it's possible that most of Excel's current users would have either been too intimidated to learn it in the first place or else gatekept out of learning it by the people who currently dismiss it as not-a-"real" programming language.
posted by tobascodagama at 10:27 AM on September 25, 2019 [8 favorites]
So when do I get it? when when wheeeeeeeen
posted by ThePinkSuperhero at 10:34 AM on September 25, 2019 [1 favorite]
posted by ThePinkSuperhero at 10:34 AM on September 25, 2019 [1 favorite]
What I'd like is to be able to enter some kind of contract with Excel where I promise that a specified range can be treated like a database table, and then run SQL queries against it. I guess you'd also have to specify a range for the result set, of unspecified length.
Excel wizards can eventually accomplish things that would be so simple with just a little SQL, via pivot tables and tortured formulas with nested VLOOKUPS and SUMIFS and so on, but it would be nice to not have to go through all that.
posted by thelonius at 10:36 AM on September 25, 2019 [5 favorites]
Excel wizards can eventually accomplish things that would be so simple with just a little SQL, via pivot tables and tortured formulas with nested VLOOKUPS and SUMIFS and so on, but it would be nice to not have to go through all that.
posted by thelonius at 10:36 AM on September 25, 2019 [5 favorites]
So is nobody even going to mention the elephant in the room? The complete Merrick Garlanding of WLOOKUP, the Excel function that was next in line for that role?
posted by Naberius at 10:36 AM on September 25, 2019 [12 favorites]
posted by Naberius at 10:36 AM on September 25, 2019 [12 favorites]
Yeah, VLOOKUP and XLOOKUP are great but as others have noted, do you try to convert every single thing you do in life into a pivot table? If not, are you really living life?
PTREAM - PIVOT TABLES RULE EVERYTHING AROUND ME
posted by GuyZero at 10:39 AM on September 25, 2019 [17 favorites]
PTREAM - PIVOT TABLES RULE EVERYTHING AROUND ME
posted by GuyZero at 10:39 AM on September 25, 2019 [17 favorites]
> It's a shame that Excel isn't more broadly recognised as a "real" programming language/platform
The best take I've heard on it is that "regular" languages are logic up front, data in the back; Excel is data up front, logic in the back. Excel inverts the standard mental model.
posted by postcommunism at 10:40 AM on September 25, 2019 [10 favorites]
The best take I've heard on it is that "regular" languages are logic up front, data in the back; Excel is data up front, logic in the back. Excel inverts the standard mental model.
posted by postcommunism at 10:40 AM on September 25, 2019 [10 favorites]
88% of spreadsheets have errors. More recently, Is the evidence for austerity based on an Excel spreadsheet error? And the classic Gene name errors are widespread in the scientific literature.
I like spreadsheets! They're accessible, great for certain types of data exploration, etc. The problem is that when you get to the level of functions like VLOOKUP, you're effectively writing computer software. Not doing a little arithmetic. And software has bugs. But spreadsheet writers don't think of what they're doing as programming and don't write their code defensively or test it anywhere near to the level they need to. It makes a mess.
posted by Nelson at 10:41 AM on September 25, 2019 [24 favorites]
I like spreadsheets! They're accessible, great for certain types of data exploration, etc. The problem is that when you get to the level of functions like VLOOKUP, you're effectively writing computer software. Not doing a little arithmetic. And software has bugs. But spreadsheet writers don't think of what they're doing as programming and don't write their code defensively or test it anywhere near to the level they need to. It makes a mess.
posted by Nelson at 10:41 AM on September 25, 2019 [24 favorites]
Facebook has had XLOOKUP forever.
The output is almost never what you want.
posted by srboisvert at 10:41 AM on September 25, 2019 [23 favorites]
The output is almost never what you want.
posted by srboisvert at 10:41 AM on September 25, 2019 [23 favorites]
wait, wait, slow down, i forgot to learn index/match!
posted by mittens at 10:42 AM on September 25, 2019 [3 favorites]
posted by mittens at 10:42 AM on September 25, 2019 [3 favorites]
oligatory XKCD
oh wow, Google Sheets has what I want?
posted by thelonius at 10:43 AM on September 25, 2019 [2 favorites]
oh wow, Google Sheets has what I want?
posted by thelonius at 10:43 AM on September 25, 2019 [2 favorites]
. It also allows you to return an array or range for further manipulation.
oh my god finally
posted by Think_Long at 10:44 AM on September 25, 2019 [1 favorite]
oh my god finally
posted by Think_Long at 10:44 AM on September 25, 2019 [1 favorite]
But spreadsheet writers don't think of what they're doing as programming and don't write their code defensively or test it anywhere near to the level they need to. It makes a mess.
There's an enormous google docs spreadsheet we use at work that has extensive tests, google calendar integration and several sub-sheets of tables that drive lookups.
it's for scheduling d&d games.
posted by GuyZero at 10:44 AM on September 25, 2019 [28 favorites]
There's an enormous google docs spreadsheet we use at work that has extensive tests, google calendar integration and several sub-sheets of tables that drive lookups.
it's for scheduling d&d games.
posted by GuyZero at 10:44 AM on September 25, 2019 [28 favorites]
the real solution was a database
per the xkcd comic, then you should still stick with a spreadsheet
posted by GuyZero at 10:45 AM on September 25, 2019 [1 favorite]
per the xkcd comic, then you should still stick with a spreadsheet
posted by GuyZero at 10:45 AM on September 25, 2019 [1 favorite]
This is eye-opening thread is giving me so many things to look into like pivot tables and index/match, also I noticed yesterday that Sheets has scripts so maybe if push comes shove I could bang out the program to do exactly what I want it to do, but I guess that kind of defeats the spirit of spatial programming using the formulas.
posted by polymodus at 10:46 AM on September 25, 2019 [3 favorites]
posted by polymodus at 10:46 AM on September 25, 2019 [3 favorites]
oh wow, Google Sheets has what I want?
Google Sheets also has GOOGLETRANSLATE, GOOGLEFINANCE and IMPORTXML.
posted by bdc34 at 11:04 AM on September 25, 2019 [3 favorites]
Google Sheets also has GOOGLETRANSLATE, GOOGLEFINANCE and IMPORTXML.
posted by bdc34 at 11:04 AM on September 25, 2019 [3 favorites]
So is nobody even going to mention the elephant in the room
you mean the "word" "spreadsheeting"?
posted by Dr. Twist at 11:05 AM on September 25, 2019 [3 favorites]
you mean the "word" "spreadsheeting"?
posted by Dr. Twist at 11:05 AM on September 25, 2019 [3 favorites]
I am perturbed by how excited I am by this announcement.
Also, can anyone recommend a good learning resource for pivot tables? I know how to use them, but I want to know what they are for and when I should choose to use them instead of VLOOKUP and/or index/match? Coz I can already do what I want to do using those tools, but all the love here makes me think that that I'm missing out on the joy of pivot tables.
Or maybe pivot tables are a distraction from the One True Path of VLOOKUP and you're all heretics, idk.
posted by happyinmotion at 11:15 AM on September 25, 2019 [1 favorite]
Also, can anyone recommend a good learning resource for pivot tables? I know how to use them, but I want to know what they are for and when I should choose to use them instead of VLOOKUP and/or index/match? Coz I can already do what I want to do using those tools, but all the love here makes me think that that I'm missing out on the joy of pivot tables.
Or maybe pivot tables are a distraction from the One True Path of VLOOKUP and you're all heretics, idk.
posted by happyinmotion at 11:15 AM on September 25, 2019 [1 favorite]
I am legit excited here!
posted by Chrysostom at 11:23 AM on September 25, 2019
posted by Chrysostom at 11:23 AM on September 25, 2019
Basically if you can make a single table with de-normalized data then you can do basic queries with a pivot table. That's how I think of it.
And by denormalized, I mean that a real database would use an integer index into a subtable for labelling/grouping stuff but in a spreadsheet you just use the label directly to make life easier. You'll see quickly if you have a rogue label in there somewhere.
posted by GuyZero at 11:24 AM on September 25, 2019
And by denormalized, I mean that a real database would use an integer index into a subtable for labelling/grouping stuff but in a spreadsheet you just use the label directly to make life easier. You'll see quickly if you have a rogue label in there somewhere.
posted by GuyZero at 11:24 AM on September 25, 2019
Right up there with pivot tables in the "why isn't this considered mandatory Excel training?" category.
Oh man, when I figured out pivot tables... well, I was still sitting in front of a computer, but I was so excited!
posted by Automocar at 11:35 AM on September 25, 2019 [7 favorites]
Oh man, when I figured out pivot tables... well, I was still sitting in front of a computer, but I was so excited!
posted by Automocar at 11:35 AM on September 25, 2019 [7 favorites]
On the one hand, I generally regard it as a good sign when a piece of software is sufficiently flexible that it gets used in surprising ways.
On the other, using a spreadsheet like this seems like it is so far over the line of "stunt programming" that light from that line will take several weeks to reach it. I mean, yeah, you could probably do the same thing with a three-state, two-symbol Turing machine. Implemented in Minecraft. Using chickens as logic gates or something. It would make about as much sense.
But is that really the best use of your time, or anyone else's?
Things like relational databases and popular scripting languages with names that start with 'P' exist for a reason.
I guess what I'm saying is that if you find yourself VLOOKUP in a non-trivial way, that should trigger a gut-check as to whether you are using the most appropriate software tool available.
posted by sourcequench at 11:52 AM on September 25, 2019
On the other, using a spreadsheet like this seems like it is so far over the line of "stunt programming" that light from that line will take several weeks to reach it. I mean, yeah, you could probably do the same thing with a three-state, two-symbol Turing machine. Implemented in Minecraft. Using chickens as logic gates or something. It would make about as much sense.
But is that really the best use of your time, or anyone else's?
Things like relational databases and popular scripting languages with names that start with 'P' exist for a reason.
I guess what I'm saying is that if you find yourself VLOOKUP in a non-trivial way, that should trigger a gut-check as to whether you are using the most appropriate software tool available.
posted by sourcequench at 11:52 AM on September 25, 2019
I don’t know how to do those things, and anyway I work for a company and the company probably won’t let me install things. Access is almost definitely not any better.
I know how to use Excel, so I do stuff in Excel. Plus I can make charts.
posted by Huffy Puffy at 12:05 PM on September 25, 2019 [18 favorites]
I know how to use Excel, so I do stuff in Excel. Plus I can make charts.
posted by Huffy Puffy at 12:05 PM on September 25, 2019 [18 favorites]
I guess what I'm saying is that if you find yourself VLOOKUP in a non-trivial way, that should trigger a gut-check as to whether you are using the most appropriate software tool available.
But when asking that question, also remember that "most appropriate software tool available" also means that it has to be actually available - and that also means:
- approved by sometimes conservative IT departments
- already included in expensive office suites they have already paid for
- widely available for other users as well
I was once using an amazing set of data - only it had all been entered into a proprietary database format that the collectors of the data believed would be the "next big thing". It wasn't. I couldn't even get a copy of the database program. But I (eventually) was able to get it to read in a spreadsheet program. That said, I lost some key fields that made my life more difficult.
If they had put the data in a simple spreadsheet in the first place, that wouldn't have been a problem. (It was a single, albeit very large, table of data - essentially a catalogue to a set of records.) But they had to go for a "better" software solution - only no one else could read the file.
There is also a big barrier to moving people off Excel into other programs - all programs have a learning curve. A lot of people are learn Excel by bits and pieces, something which is much harder to do with a database program like Access. If you have no experience in programming, learning Excel formulae is still baby-steps compared to out-right coding - and half the time you're still selecting things.
posted by jb at 12:07 PM on September 25, 2019 [11 favorites]
But when asking that question, also remember that "most appropriate software tool available" also means that it has to be actually available - and that also means:
- approved by sometimes conservative IT departments
- already included in expensive office suites they have already paid for
- widely available for other users as well
I was once using an amazing set of data - only it had all been entered into a proprietary database format that the collectors of the data believed would be the "next big thing". It wasn't. I couldn't even get a copy of the database program. But I (eventually) was able to get it to read in a spreadsheet program. That said, I lost some key fields that made my life more difficult.
If they had put the data in a simple spreadsheet in the first place, that wouldn't have been a problem. (It was a single, albeit very large, table of data - essentially a catalogue to a set of records.) But they had to go for a "better" software solution - only no one else could read the file.
There is also a big barrier to moving people off Excel into other programs - all programs have a learning curve. A lot of people are learn Excel by bits and pieces, something which is much harder to do with a database program like Access. If you have no experience in programming, learning Excel formulae is still baby-steps compared to out-right coding - and half the time you're still selecting things.
posted by jb at 12:07 PM on September 25, 2019 [11 favorites]
I guess what I'm saying is that if you find yourself VLOOKUP in a non-trivial way, that should trigger a gut-check as to whether you are using the most appropriate software tool available.
Flashback to the two year long fight one of my coworkers had with corporate management to allow us to buy a cheaper SQL server that'd save a ridiculous number of processing hours. He lost.
Also, people are generally encouraged to use their home-grown excel solutions, even if they're self-taught. Not so much with other programming options.
posted by dinty_moore at 12:08 PM on September 25, 2019 [4 favorites]
Flashback to the two year long fight one of my coworkers had with corporate management to allow us to buy a cheaper SQL server that'd save a ridiculous number of processing hours. He lost.
Also, people are generally encouraged to use their home-grown excel solutions, even if they're self-taught. Not so much with other programming options.
posted by dinty_moore at 12:08 PM on September 25, 2019 [4 favorites]
I know my organization won't let me install things. I couldn't even get them to let me install an awesome freeware transcription program I'd been using on my private machine (Express Scribe Basic Free Version - I would definitely recommend it for people who need that sort of thing). Instead, they had me set up VLC shortcut keys, which was only ever a clunky imitation.
posted by jb at 12:11 PM on September 25, 2019 [3 favorites]
posted by jb at 12:11 PM on September 25, 2019 [3 favorites]
OK, not to get all yourfavoritebandsucks on you, but:
I've had to transform large, complex excel files (for instance one used by one of Chile's largest retirement fund administrators to calculate how much money people will retire with) into Python, and OMG the Verbosity!!!! So much boilerplate inside a tiny spreadsheet cell, with precisely no accomodation for the person using it to program. And the program is spread out over many, many cells, on different sheets! And there are no debugging tools! At all!
I get that a lot of people learnt to use this and use it every day and are the local excel-ninjas, etc., but, think about it, would you subject your child to this? A spouse? Any kind of loved one? I think not.
posted by signal at 12:16 PM on September 25, 2019 [7 favorites]
I've had to transform large, complex excel files (for instance one used by one of Chile's largest retirement fund administrators to calculate how much money people will retire with) into Python, and OMG the Verbosity!!!! So much boilerplate inside a tiny spreadsheet cell, with precisely no accomodation for the person using it to program. And the program is spread out over many, many cells, on different sheets! And there are no debugging tools! At all!
I get that a lot of people learnt to use this and use it every day and are the local excel-ninjas, etc., but, think about it, would you subject your child to this? A spouse? Any kind of loved one? I think not.
import pandas as pd
df = pandas.read_excel('data_not_logic.xlsx')
posted by signal at 12:16 PM on September 25, 2019 [7 favorites]
A lot of people are learn Excel by bits and pieces, something which is much harder to do with a database program like Access.
I was thinking about Office once, and it occurred to me that there isn't a good user metaphor for Access the way there is for other Office suite programs. Word is a blank sheet of paper. Anyone can relate to that - start typing. Powerpoint is a deck of slides. Excel is a sheet of graph paper. Access is what, a filing cabinet? A database is the opposite of a filing cabinet, where multiple copies of information are stored in different places, like with those old fashioned multi-colored carbon paper forms.
A naive user does not have any background in relational databases, and is almost inevitably going to treat them like they are spreadsheets, at best. They will store their data in the way it makes sense for them to think of presenting it, which will be totally denormalized and very awkward,or impossible, to query. This is one reason Access has such a bad reputation - people use it to make atrocities that the IT department then has to take over. (The main reasons are that it is a file based database and handles multiple users poorly and isn't ACID and doesn't have triggers, etc, I know) .
posted by thelonius at 12:16 PM on September 25, 2019 [4 favorites]
I was thinking about Office once, and it occurred to me that there isn't a good user metaphor for Access the way there is for other Office suite programs. Word is a blank sheet of paper. Anyone can relate to that - start typing. Powerpoint is a deck of slides. Excel is a sheet of graph paper. Access is what, a filing cabinet? A database is the opposite of a filing cabinet, where multiple copies of information are stored in different places, like with those old fashioned multi-colored carbon paper forms.
A naive user does not have any background in relational databases, and is almost inevitably going to treat them like they are spreadsheets, at best. They will store their data in the way it makes sense for them to think of presenting it, which will be totally denormalized and very awkward,or impossible, to query. This is one reason Access has such a bad reputation - people use it to make atrocities that the IT department then has to take over. (The main reasons are that it is a file based database and handles multiple users poorly and isn't ACID and doesn't have triggers, etc, I know) .
posted by thelonius at 12:16 PM on September 25, 2019 [4 favorites]
Though really, if you are using vlookup for non-trivial amounts of data, you should consider switching over to index-match; due to the way both formulas look up data, index-match uses a lot less processing power and will give you faster results.
posted by dinty_moore at 12:17 PM on September 25, 2019 [3 favorites]
posted by dinty_moore at 12:17 PM on September 25, 2019 [3 favorites]
On the other hand, vlookup is much easier to verify than giant index-match tables. I mostly check others' work these days and index-match breaks tracing dependents. Here's to xlookup killing it.
posted by I claim sanctuary at 12:25 PM on September 25, 2019 [1 favorite]
posted by I claim sanctuary at 12:25 PM on September 25, 2019 [1 favorite]
It still surprises me to see (biological) scientists use Excel, which will still irretrievably damage biologically-relevant information, and which adds gunk that clogs up Unix pipelines that are used by other analysts and automated pipelines. Learn Python or R, folks!
posted by They sucked his brains out! at 12:26 PM on September 25, 2019 [4 favorites]
posted by They sucked his brains out! at 12:26 PM on September 25, 2019 [4 favorites]
TSHBO: Did you see the most excellent BioExcel April Fool's satire?
posted by foxfirefey at 1:06 PM on September 25, 2019 [2 favorites]
posted by foxfirefey at 1:06 PM on September 25, 2019 [2 favorites]
Admittedly, with a lifetime of programming computers, I'm not the average use case, but I started with a Google spreadsheet for my weight tracking/calculation of rate of weight change, but soon got to the point where the real solution was a database, so I ended up writing a webapp with a mariadb backend on a linode vps instance.
I'm not actually kidding that this sort of thing is basically how I became a software engineer. I was an accountant who kept winding up with more and more elaborate spreadsheets that eventually I started going "I wish I could make an app out of this" and now here I am. I feel a little bad that I left my last employer with some Excel spreadsheets that I don't think he had any reasonable way of maintaining, but at least I stuck with using that for work stuff until I made the real jump.
On the other hand, now I find myself looking at my company's suite of internal applications and thinking that like 50% of them would be better off as spreadsheets, and making this sort of lookup logic easier doesn't help that impression.
posted by Sequence at 1:08 PM on September 25, 2019 [4 favorites]
I'm not actually kidding that this sort of thing is basically how I became a software engineer. I was an accountant who kept winding up with more and more elaborate spreadsheets that eventually I started going "I wish I could make an app out of this" and now here I am. I feel a little bad that I left my last employer with some Excel spreadsheets that I don't think he had any reasonable way of maintaining, but at least I stuck with using that for work stuff until I made the real jump.
On the other hand, now I find myself looking at my company's suite of internal applications and thinking that like 50% of them would be better off as spreadsheets, and making this sort of lookup logic easier doesn't help that impression.
posted by Sequence at 1:08 PM on September 25, 2019 [4 favorites]
For those of you who have converted Excel "applications" into true webapps - what is an easy way to get started?
posted by philosophygeek at 1:49 PM on September 25, 2019
posted by philosophygeek at 1:49 PM on September 25, 2019
There are extremely good use cases for Excel which use moderately complex formulae. It's best used for one-off prototyping or exploration of data, true, but I'll very often work out how to process (or even more usefully, to reconcile 2 datasets) my data in Excel because doing the calculations on the data in the table in front of you is great.
If I had Jupyter notebooks available to me, I might feel different, but Excel does a very good job, and does for lots of other people, too.
The canonical reference on this is Felienne Hermans, previously. Such a worthwhile skill to develop, and much more accessible than outright coding.
posted by ambrosen at 1:50 PM on September 25, 2019 [4 favorites]
If I had Jupyter notebooks available to me, I might feel different, but Excel does a very good job, and does for lots of other people, too.
The canonical reference on this is Felienne Hermans, previously. Such a worthwhile skill to develop, and much more accessible than outright coding.
posted by ambrosen at 1:50 PM on September 25, 2019 [4 favorites]
But back on topic, XLOOKUP looks great, and I hadn't seen it can return ranges (and therefore be nested) when I read about before.
Much better than an array formula full of {= INDEX( resultarray,SMALL(IF(criterion1,IF(criterion2,IF(criterion3,ROW(lookuparray))))))}
posted by ambrosen at 1:54 PM on September 25, 2019 [3 favorites]
Much better than an array formula full of {= INDEX( resultarray,SMALL(IF(criterion1,IF(criterion2,IF(criterion3,ROW(lookuparray))))))}
posted by ambrosen at 1:54 PM on September 25, 2019 [3 favorites]
Next up: "count unique" as an option for a value in pivot tables. And also averaging the number of TRUE/FALSE values as if they were 1s and 0s.
Yes, I am aware of the workarounds available, but these should just plain work. It's not too much to ask. I think.
(Also, I get a lot of mileage out of =QUERY on google sheets and wish I'd learned about it way sooner.)
For the record, I also use databases and R and whatever, but Excel has undeniable advantages for certain things in my workplace environment. Not least of which is that everyone has it. I think folks are underestimating the learning curve/effort involved to establish a database that folks from multiple organizations with multiple tech policies in place can easily access and edit. Although I'm very game for suggestions on that front.
posted by mosst at 1:56 PM on September 25, 2019 [5 favorites]
Yes, I am aware of the workarounds available, but these should just plain work. It's not too much to ask. I think.
(Also, I get a lot of mileage out of =QUERY on google sheets and wish I'd learned about it way sooner.)
For the record, I also use databases and R and whatever, but Excel has undeniable advantages for certain things in my workplace environment. Not least of which is that everyone has it. I think folks are underestimating the learning curve/effort involved to establish a database that folks from multiple organizations with multiple tech policies in place can easily access and edit. Although I'm very game for suggestions on that front.
posted by mosst at 1:56 PM on September 25, 2019 [5 favorites]
Counting unique values in a pivot table is possible now:
https://stackoverflow.com/questions/11876238/simple-pivot-table-to-count-unique-values
Is one example I can find quickly. I have never done this myself and I just became aware of the option recently.
I'm a DBA. I certainly still use Excel to do things that would be better done in a database but it is usually because I am only doing it one time and don't want to bother importing my data. Once someone wants data refreshed regularly or the process repeated, then I tell them we need to go into an MSSQL database. I started in Access and I don't think it is a bad way to move from spreadsheets to databases. If you want interactive apps, I would not recommend Access as a step.
posted by soelo at 2:02 PM on September 25, 2019 [5 favorites]
https://stackoverflow.com/questions/11876238/simple-pivot-table-to-count-unique-values
Is one example I can find quickly. I have never done this myself and I just became aware of the option recently.
I'm a DBA. I certainly still use Excel to do things that would be better done in a database but it is usually because I am only doing it one time and don't want to bother importing my data. Once someone wants data refreshed regularly or the process repeated, then I tell them we need to go into an MSSQL database. I started in Access and I don't think it is a bad way to move from spreadsheets to databases. If you want interactive apps, I would not recommend Access as a step.
posted by soelo at 2:02 PM on September 25, 2019 [5 favorites]
Counting unique values in a pivot table is possible now:
Yeah, it's possible, but I would firmly categorize that as a "workaround". Now you have a whole helper column that you have to ensure nobody (incl. yourself) screws up, plus it's ugly (especially if you have a bunch of them) and distracting and just seems like it should be unnecessary for such a straightforward operation.
posted by mosst at 2:23 PM on September 25, 2019
Yeah, it's possible, but I would firmly categorize that as a "workaround". Now you have a whole helper column that you have to ensure nobody (incl. yourself) screws up, plus it's ugly (especially if you have a bunch of them) and distracting and just seems like it should be unnecessary for such a straightforward operation.
posted by mosst at 2:23 PM on September 25, 2019
In Excel 2013 count distinct doesn't need helper columns, just enabling a bundled plugin, and a couple of clicks.
posted by ambrosen at 2:33 PM on September 25, 2019
posted by ambrosen at 2:33 PM on September 25, 2019
> A database is the opposite of a filing cabinet, where multiple copies of information are stored in different places, like with those old fashioned multi-colored carbon paper forms.
A database should never have multiple copies of the same information stored in different places, if that's happening, the db wants to be normalized.
---
Excel's ecosystem isn't as rich as more traditional programming languages, and the array formulas fall short (and if I start using them, it's a sign to switch tools - a database, or more recently
AppSheet's (no affiliation) whole business is basically taking excel sheets and turning them into full blown apps. I've never used them so I can't speak to how well it works in practice but there's definitely a market for that sort of thing.
posted by fragmede at 2:46 PM on September 25, 2019
A database should never have multiple copies of the same information stored in different places, if that's happening, the db wants to be normalized.
---
Excel's ecosystem isn't as rich as more traditional programming languages, and the array formulas fall short (and if I start using them, it's a sign to switch tools - a database, or more recently
pandas in an ipython notebook. Googling for help with Excel (or more recently, Google Sheets) returns many a blog giving magic incantation formulas, which is either a good sign, for those that believe in trade school, or a bad sign, for those preaching that a deeper understanding of the fundamentals are requisite before being allowed to work in the industry.AppSheet's (no affiliation) whole business is basically taking excel sheets and turning them into full blown apps. I've never used them so I can't speak to how well it works in practice but there's definitely a market for that sort of thing.
posted by fragmede at 2:46 PM on September 25, 2019
It occurred to me from this discussion that a spreadsheet frontend for R would be a good thing. Dear LazyWeb!
posted by sjswitzer at 3:10 PM on September 25, 2019
posted by sjswitzer at 3:10 PM on September 25, 2019
Let me tell you, if you're an attorney and can do VLOOKUP, you are like unto a god amongst your brethren.
posted by praemunire at 3:14 PM on September 25, 2019 [10 favorites]
posted by praemunire at 3:14 PM on September 25, 2019 [10 favorites]
bdc34: "Google Sheets also has GOOGLETRANSLATE, GOOGLEFINANCE and IMPORTXML."
Not to mention Bananawrite, Bananadraw, Bananafile, and Bananamanager.
posted by Chrysostom at 3:15 PM on September 25, 2019 [2 favorites]
Not to mention Bananawrite, Bananadraw, Bananafile, and Bananamanager.
posted by Chrysostom at 3:15 PM on September 25, 2019 [2 favorites]
A horrible part of my industry uses Excel as a word processor, for project managers who resist opening Word, because Word is for women.
I’m not joking, I rarely open a spreadsheet that actually has numbers in it. Doing a sort by column is unheard of occultism. Risk management sheets, project tracking, document version control tables, another spreadsheet to control the document version control (draft_v2_FINAL.xlsx), assessment policy frameworks, statutory condition tables, hey, listen, can you just put your response here in this extremely elaborate textual matrix I’ve done up with colours and headings? Can we close that out?
Excel in my industry is used to make lists and electronic paper forms.
posted by Fiasco da Gama at 3:20 PM on September 25, 2019 [9 favorites]
I’m not joking, I rarely open a spreadsheet that actually has numbers in it. Doing a sort by column is unheard of occultism. Risk management sheets, project tracking, document version control tables, another spreadsheet to control the document version control (draft_v2_FINAL.xlsx), assessment policy frameworks, statutory condition tables, hey, listen, can you just put your response here in this extremely elaborate textual matrix I’ve done up with colours and headings? Can we close that out?
Excel in my industry is used to make lists and electronic paper forms.
posted by Fiasco da Gama at 3:20 PM on September 25, 2019 [9 favorites]
Oh yeah there was an old "Joel On Software" column about that....MS did a user study back in the Office 97 days, and they found that most people used Excel to layout reports, not to do financial calculations, like they had thought.
posted by thelonius at 3:23 PM on September 25, 2019 [2 favorites]
posted by thelonius at 3:23 PM on September 25, 2019 [2 favorites]
hey, listen, can you just put your response here in this extremely elaborate textual matrix I’ve done up with colours and headings?
Oh my Blog/Cob/Gob, people who think of Excel as a way to 'design' "pretty" """layouts""" would definitely have their own ring in hell if Dante had heard of them.
posted by signal at 3:26 PM on September 25, 2019 [4 favorites]
Oh my Blog/Cob/Gob, people who think of Excel as a way to 'design' "pretty" """layouts""" would definitely have their own ring in hell if Dante had heard of them.
posted by signal at 3:26 PM on September 25, 2019 [4 favorites]
Word is for women
what the hell industry are you in?
posted by Dr. Twist at 3:29 PM on September 25, 2019 [4 favorites]
what the hell industry are you in?
posted by Dr. Twist at 3:29 PM on September 25, 2019 [4 favorites]
Please tell me the verb form is, like, "Ok, and then you vlook it up..."
posted by nebulawindphone at 3:32 PM on September 25, 2019 [3 favorites]
posted by nebulawindphone at 3:32 PM on September 25, 2019 [3 favorites]
Oh my Blog/Cob/Gob, people who think of Excel as a way to 'design' "pretty" """layouts"""
....hoping the new iPhone has ANSI graphics
posted by thelonius at 3:36 PM on September 25, 2019
....hoping the new iPhone has ANSI graphics
posted by thelonius at 3:36 PM on September 25, 2019
Excel in my industry is used to make lists and electronic paper forms.
Extremely same, at least in the company I'm at, and "my industry" is software documentation where you might think we'd know better.
Excel is for making forms for people to fill out, for laying out text in tables, and for drawing charts. Page layout fancier than a table happens in Powerpoint, whether or not it will ever be given as a presentation. Word's formatting and layout features do not exist; it is entirely for typing plain text. Calculations are the developers' problem.
posted by nebulawindphone at 3:44 PM on September 25, 2019 [3 favorites]
Extremely same, at least in the company I'm at, and "my industry" is software documentation where you might think we'd know better.
Excel is for making forms for people to fill out, for laying out text in tables, and for drawing charts. Page layout fancier than a table happens in Powerpoint, whether or not it will ever be given as a presentation. Word's formatting and layout features do not exist; it is entirely for typing plain text. Calculations are the developers' problem.
posted by nebulawindphone at 3:44 PM on September 25, 2019 [3 favorites]
It's a shame that Excel isn't more broadly recognised as a "real" programming language/platform (at least as real as Matlab -- stow it, fellow programmers, I know we all dismiss that, too) and taught as such.
One of the researchers I have to support has an Excel spreadsheet where he reimplemented and expanded on a piece of software that is no longer supported.
It takes about 15 minutes to open, and he's fried the memory on his machine a couple of times.
Excel has its limitations as a programming language, and the way to fix it, usually, is "you shouldn't have done this in Excel". Which helps no-one, but it's true.
posted by Merus at 4:21 PM on September 25, 2019
One of the researchers I have to support has an Excel spreadsheet where he reimplemented and expanded on a piece of software that is no longer supported.
It takes about 15 minutes to open, and he's fried the memory on his machine a couple of times.
Excel has its limitations as a programming language, and the way to fix it, usually, is "you shouldn't have done this in Excel". Which helps no-one, but it's true.
posted by Merus at 4:21 PM on September 25, 2019
I gotta admit, as much as I rag on Office and Microsoft as a whole, Excel is the one piece of software included in Office that is genuinely the best out there at what it does.
Until someone decides to use it as a database.
posted by DoctorFedora at 4:24 PM on September 25, 2019 [5 favorites]
Until someone decides to use it as a database.
posted by DoctorFedora at 4:24 PM on September 25, 2019 [5 favorites]
I refrain from Excel stories to maintain my "don't know shit about Windows" facade. I have done the Excel as design thing. It was glorious. I took a report that took 4 people working on a stack of printouts in their spare time for days to make that weekly report. Turned it into a floppy disk, some awk, a spredsheet and 5 minutes. I put the report in the boss' mailbox, and he came into my cubicle the next day and plopped it down on my desk and said "use the color printer". Data fuckery godhood achieved.
I can totally see Excel as a data viewer, or data entry, or decent futz around with tables tool. It's usually totally easier to just export the spreadsheets as CSV (or use a languages ReadXLSX sort of thing), write a bit of code, and write CSV (or WriteXLSX) the results. By the time you get to well organized tables across multiple sheets... what's the point of coding in a language that only works for that thing vs a language that works across multiple domains.
I am sorry for those in environments where they just can't for reasons. I'd never make it there. Maybe VB macros?
posted by zengargoyle at 5:09 PM on September 25, 2019 [1 favorite]
I can totally see Excel as a data viewer, or data entry, or decent futz around with tables tool. It's usually totally easier to just export the spreadsheets as CSV (or use a languages ReadXLSX sort of thing), write a bit of code, and write CSV (or WriteXLSX) the results. By the time you get to well organized tables across multiple sheets... what's the point of coding in a language that only works for that thing vs a language that works across multiple domains.
I am sorry for those in environments where they just can't for reasons. I'd never make it there. Maybe VB macros?
posted by zengargoyle at 5:09 PM on September 25, 2019 [1 favorite]
Everybody at the office has Excel. Nobody has any other domains. The most cross-functional format (after PowerPoint) is Excel.
posted by Huffy Puffy at 5:24 PM on September 25, 2019 [8 favorites]
posted by Huffy Puffy at 5:24 PM on September 25, 2019 [8 favorites]
wept for the wasted years of my life.
This is normal in my experience from people who have used Microsoft office products because they had to, or didn’t know about the alternatives.
posted by SaltySalticid at 5:40 PM on September 25, 2019 [6 favorites]
This is normal in my experience from people who have used Microsoft office products because they had to, or didn’t know about the alternatives.
posted by SaltySalticid at 5:40 PM on September 25, 2019 [6 favorites]
As a proud evangelist of Index/Match, this is, well, I'm happier about this than I should be. This looks good, simple and easy. And they kept the fuzzy and precise options for implementation, which is great.
I'm in a MS for data science, trying to get away from Excel. But I still remember my roots.
posted by Hactar at 5:58 PM on September 25, 2019 [2 favorites]
I'm in a MS for data science, trying to get away from Excel. But I still remember my roots.
posted by Hactar at 5:58 PM on September 25, 2019 [2 favorites]
Excel is the one piece of software included in Office that is genuinely the best out there at what it does.
But what it does isn't very nice...
posted by signal at 6:20 PM on September 25, 2019 [2 favorites]
But what it does isn't very nice...
posted by signal at 6:20 PM on September 25, 2019 [2 favorites]
I remember how happy I was when they added SUMIFS and COUNTIFS functions (which google sheets didn't have for a long time). I like this a lot.
I've moved most of my heavy-lifting over to the Power Pivot data model (what fun cube functions are), but this will still be very, very useful.
posted by tclark at 6:23 PM on September 25, 2019 [1 favorite]
I've moved most of my heavy-lifting over to the Power Pivot data model (what fun cube functions are), but this will still be very, very useful.
posted by tclark at 6:23 PM on September 25, 2019 [1 favorite]
That's a godsend, but also almost a decade too late. Any sufficiently complicated spreadsheet contains an ad-hoc, informally-specified, bug-ridden, slow implementation of XLOOKUP.
posted by rufb at 6:31 PM on September 25, 2019 [1 favorite]
posted by rufb at 6:31 PM on September 25, 2019 [1 favorite]
Sooner or later, all the ways of programming a computer turn into perl.
posted by jenkinsEar at 7:06 PM on September 25, 2019 [3 favorites]
posted by jenkinsEar at 7:06 PM on September 25, 2019 [3 favorites]
wept for the wasted years of my life.If I had a dollar for every time I encountered work by someone who thought that Word was acceptable for page layout…
This is normal in my experience from people who have used Microsoft office products because they had to, or didn’t know about the alternatives.
posted by SaltySalticid
I've actually encountered a fair few cases of people using PowerPoint for page layout, which is understandable inasmuch as it is maybe the least worst page layout software that the average Word-Excel-PowerPoint-and-Outlook installation of Office will have available. Pray for their poor souls.
posted by DoctorFedora at 8:13 PM on September 25, 2019
It never ceases to amaze me that despite more companies than I have digits having tried to make creating databases and apps based on them as easy or easier than "just doing it in Excel," people continue to use Excel. Sometimes I get the impression that the entire reason is that Access purports to be such a tool, giving people the impression that databases must be hard or not discoverable. Lotus Approach was actually pretty decent, although some newer stuff came along in the later 90s that had better visual query builders and such. At the time, it amused me that we could buy both SmartSuite and WordPerfect Office for less than half the cost of MS Office. Now, it's just sad.
(I agree that for one off tasks or things that are actually spreadsheets, Excel and its ilk are somewhere between fine and the correct tool for the job)
posted by wierdo at 9:59 PM on September 25, 2019
(I agree that for one off tasks or things that are actually spreadsheets, Excel and its ilk are somewhere between fine and the correct tool for the job)
posted by wierdo at 9:59 PM on September 25, 2019
I am sorry for those in environments where they just can't for reasons. I'd never make it there. Maybe VB macros?
Every Windows box can still run .js scripts without needing anything else installed AFAIK. PowerShell's promise:frustration ratio is also starting to get to reasonable levels.
One PowerShell annoyance that's almost universally encountered in corporate environments is that the default administrative restriction against launching PowerShell scripts via double-click or drag-and-drop remains in place. My standard workaround for that is to top and tail any PowerShell code with JScript. The result can be edited, tested and debugged like any other PowerShell script, exactly as if the JScript part wasn't there, and then put into production simply by changing the filename extension from .ps1 to .js.
posted by flabdablet at 11:19 PM on September 25, 2019 [5 favorites]
Every Windows box can still run .js scripts without needing anything else installed AFAIK. PowerShell's promise:frustration ratio is also starting to get to reasonable levels.
One PowerShell annoyance that's almost universally encountered in corporate environments is that the default administrative restriction against launching PowerShell scripts via double-click or drag-and-drop remains in place. My standard workaround for that is to top and tail any PowerShell code with JScript. The result can be edited, tested and debugged like any other PowerShell script, exactly as if the JScript part wasn't there, and then put into production simply by changing the filename extension from .ps1 to .js.
posted by flabdablet at 11:19 PM on September 25, 2019 [5 favorites]
Someone go trademark Y and ZLOOKUP
That way we can look forward to AALOOKUP
posted by snuffleupagus at 11:33 PM on September 25, 2019 [4 favorites]
That way we can look forward to AALOOKUP
posted by snuffleupagus at 11:33 PM on September 25, 2019 [4 favorites]
Pff... SUMPRODUCT is totally where it's at. You can abuse it in amazing ways.
posted by Hal Mumkin at 3:34 AM on September 26, 2019 [7 favorites]
posted by Hal Mumkin at 3:34 AM on September 26, 2019 [7 favorites]
i learned pivot tables a few years back and have since saved myself so much time. last year my boyfriend taught me vlookup and i am now an excel god at work. i do not know what index/match is, but will apparently be teaching myself that starting soon.
i'm 39. this stuff could have helped me for the last 20 years.
posted by misanthropicsarah at 6:48 AM on September 26, 2019 [6 favorites]
i'm 39. this stuff could have helped me for the last 20 years.
posted by misanthropicsarah at 6:48 AM on September 26, 2019 [6 favorites]
Learn Python or R, folks!
I mean, ideally, you will have both the time and support to do that. But a lot of time these skills are things that you're expected to learn on your own time, which is limited if you have a full-time job. Excel monstrosities are a symptom of a larger problem, that workplaces do not want to spend money on training employees.
I mean, I'm already working long days. I put some work into learning Python but ended up just forgetting a lot of it because I didn't have the time to tinker around with it, and it wasn't immediately useful for what I needed to do.
And like, I'm a graduate student, I can make an argument that learning Python is a benefit to myself, primarily; my university isn't asking me to do things for them with Python. I'm not spending my free time learning a skill to benefit my boss. I think there's some value in employees drawing a line here, and letting their workplace suffer unless they're being fairly compensated for the training.
posted by Kutsuwamushi at 7:03 AM on September 26, 2019 [9 favorites]
I mean, ideally, you will have both the time and support to do that. But a lot of time these skills are things that you're expected to learn on your own time, which is limited if you have a full-time job. Excel monstrosities are a symptom of a larger problem, that workplaces do not want to spend money on training employees.
I mean, I'm already working long days. I put some work into learning Python but ended up just forgetting a lot of it because I didn't have the time to tinker around with it, and it wasn't immediately useful for what I needed to do.
And like, I'm a graduate student, I can make an argument that learning Python is a benefit to myself, primarily; my university isn't asking me to do things for them with Python. I'm not spending my free time learning a skill to benefit my boss. I think there's some value in employees drawing a line here, and letting their workplace suffer unless they're being fairly compensated for the training.
posted by Kutsuwamushi at 7:03 AM on September 26, 2019 [9 favorites]
I've actually encountered a fair few cases of people using PowerPoint for page layout,
could they not at least use publisher? that is what i have to resort to at my job.
posted by misanthropicsarah at 7:03 AM on September 26, 2019
could they not at least use publisher? that is what i have to resort to at my job.
posted by misanthropicsarah at 7:03 AM on September 26, 2019
Also, I've used PowerPoint to create conference posters and it worked fine - actually, it was more reliable and seamless than when I've made posters with LaTeX. Convert to .pdf, send to printers, easy.
(Is Publisher still only for PC?)
posted by Kutsuwamushi at 7:08 AM on September 26, 2019 [1 favorite]
(Is Publisher still only for PC?)
posted by Kutsuwamushi at 7:08 AM on September 26, 2019 [1 favorite]
Publisher is typically an extra add on to the typical microsoft office suite (excel + word + powerpoint + outlook). Even with Office 365 adding a bunch of smaller programs to their usual bundle - planner, flow, onedrive, sharepoint, teams - you'd have to make a business use case to get Publisher, Access, or PowerBI.
posted by dinty_moore at 7:48 AM on September 26, 2019 [2 favorites]
posted by dinty_moore at 7:48 AM on September 26, 2019 [2 favorites]
Excel is probably responsible for most of the advances in computer hardware:
VP: I want that data NOW
George: but it takes 30 minutes to open the spreadsheet
VP: Go buy a bigger computer, NOW
posted by sammyo at 8:16 AM on September 26, 2019 [11 favorites]
VP: I want that data NOW
George: but it takes 30 minutes to open the spreadsheet
VP: Go buy a bigger computer, NOW
posted by sammyo at 8:16 AM on September 26, 2019 [11 favorites]
nothing about what people can manipulate Excel to do, now matter how bad an idea it is, surprises me anymore
Excel was my late father's tool of choice for making painstakingly intricate stock charts on paper. He never used its numerics capability at all as far as I could tell; he'd worked out how to make grid cells display as square, and then just used the resulting grid like graph paper. Every single mark he put on his spreadsheets got there not via Excel's inbuilt charting, which I don't think he ever worked out how to use, but by filling cells with X or O and adding trend lines with the freeform drawing tools. He'd then print the results, usually many times each until he'd found and fixed enough errors to satisfy him, after which he'd tape multiple sheets together to make these huge wall charts.
It took him countless hours to make each of these, like some kind of man-cave cross-stitch. I never really understood why he didn't just use actual graph paper, which would have been way way faster.
Given that I was making my living writing code at the time, there was an awful lot of wincing and lip-biting going on when Dad wasn't looking, but what's a boy to do? It was obviously making him happy, and who am I to criticize an artist's choice of medium?
posted by flabdablet at 9:36 AM on September 26, 2019 [5 favorites]
Excel was my late father's tool of choice for making painstakingly intricate stock charts on paper. He never used its numerics capability at all as far as I could tell; he'd worked out how to make grid cells display as square, and then just used the resulting grid like graph paper. Every single mark he put on his spreadsheets got there not via Excel's inbuilt charting, which I don't think he ever worked out how to use, but by filling cells with X or O and adding trend lines with the freeform drawing tools. He'd then print the results, usually many times each until he'd found and fixed enough errors to satisfy him, after which he'd tape multiple sheets together to make these huge wall charts.
It took him countless hours to make each of these, like some kind of man-cave cross-stitch. I never really understood why he didn't just use actual graph paper, which would have been way way faster.
Given that I was making my living writing code at the time, there was an awful lot of wincing and lip-biting going on when Dad wasn't looking, but what's a boy to do? It was obviously making him happy, and who am I to criticize an artist's choice of medium?
posted by flabdablet at 9:36 AM on September 26, 2019 [5 favorites]
I look forward to being able to use this to impress my coworkers; but mostly I'd like to jump on the bandwagon of bragging about being the excel guru. The apotheosis of my work is a weekly report summarizing half a million cells of data.
This monster uses VBA to run SQL against an access database, and direct data query to another spreadsheet; then uses powerpivot to combine 3 data tables and 3 reference tables into 2 large pivot tables, 3 small ones, with dependent tables (with vlookup and plenty of conditional formatting), 10 pivot charts, and 5 slicers - all shuffled around with a listbox hooked up to more vba for hide/unhide commands.
The end result being actually faster than the clunky one it has replaced, and only requires a 'Get Data' and a 'Finalize' button for the user. When xlookup is available, I'll crowbar that in there too somehow.
posted by Marticus at 3:29 PM on September 26, 2019 [1 favorite]
This monster uses VBA to run SQL against an access database, and direct data query to another spreadsheet; then uses powerpivot to combine 3 data tables and 3 reference tables into 2 large pivot tables, 3 small ones, with dependent tables (with vlookup and plenty of conditional formatting), 10 pivot charts, and 5 slicers - all shuffled around with a listbox hooked up to more vba for hide/unhide commands.
The end result being actually faster than the clunky one it has replaced, and only requires a 'Get Data' and a 'Finalize' button for the user. When xlookup is available, I'll crowbar that in there too somehow.
posted by Marticus at 3:29 PM on September 26, 2019 [1 favorite]
In my experience, Publisher only exists in a theoretical sense, in the same way that the existing Mario/Wario bros. imply the existence of a third set of blue and orange brothers who have not yet been discovered
posted by DoctorFedora at 3:49 PM on September 26, 2019 [5 favorites]
posted by DoctorFedora at 3:49 PM on September 26, 2019 [5 favorites]
sammyo: "Excel is probably responsible for most of the advances in computer hardware:
VP: I want that data NOW
George: but it takes 30 minutes to open the spreadsheet
VP: Go buy a bigger computer, NOW"
People bought Apple IIs solely for access to VisiCalc.
posted by Chrysostom at 8:28 AM on September 27, 2019 [3 favorites]
VP: I want that data NOW
George: but it takes 30 minutes to open the spreadsheet
VP: Go buy a bigger computer, NOW"
People bought Apple IIs solely for access to VisiCalc.
posted by Chrysostom at 8:28 AM on September 27, 2019 [3 favorites]
So much of the world depends on thrown-together Excel spreadsheets. This looks like a great new tool to add longevity to the legacy of Excel, the best product Microsoft ever made. (Seriously, it does pivot tables, charts and graphs, it adds numbers, I've seen people make MAPS in it, it's so flexible it gets used for things that it really shouldn't be able to do...)
And like the blog author, mastering
posted by fifteen schnitzengruben is my limit at 10:13 AM on September 27, 2019 [2 favorites]
And like the blog author, mastering
VLOOKUP gave me a real sense of accomplishment. That and CONCATENATE powered a lot of my data transformations a couple of jobs ago.posted by fifteen schnitzengruben is my limit at 10:13 AM on September 27, 2019 [2 favorites]
Excel in my industry is used to make lists and electronic paper forms.
When I was working in information architecture (by the terminology, you can guess how long ago that was), one of my pet notions was rewriting an excel-like program that was actually designed to make lists and forms and would do it really, really well. It's clearly a need but not one that's being well met.
Also, I once received a wireframe done in excel.
posted by stet at 11:31 AM on September 27, 2019
When I was working in information architecture (by the terminology, you can guess how long ago that was), one of my pet notions was rewriting an excel-like program that was actually designed to make lists and forms and would do it really, really well. It's clearly a need but not one that's being well met.
Also, I once received a wireframe done in excel.
posted by stet at 11:31 AM on September 27, 2019
Are you talking wireframe as in UI mockup or wireframe as in animation?
posted by flabdablet at 6:07 AM on September 28, 2019
posted by flabdablet at 6:07 AM on September 28, 2019
« Older Soviet sci-fi bittersweet nostalgia, and more... | Vox Media Acquires New York Magazine Newer »
This thread has been archived and is closed to new comments
posted by selfnoise at 9:47 AM on September 25, 2019 [24 favorites]