If you don't know who the spreadsheet wizard is at your company, become that person. This works in almost any company regardless of field. Once there are some more lessons here, I can see it being a great resource.
This looks like a good start, but you pretty much need to get either Google Sheets or Excel into the browser window for the exercises. The half-working thing currently there just isn't going to cut it. It was very hard to work with.
I don't know what plans you have for future tutorials, but I would suggest using specific business use cases as scenarios and exploring them from a basic approach along to more advanced solutions where appropriate.
Good suggestion on the business use cases. I want the exercises to be realistic documents that you build up over the course of the lesson, not just interactive quiz questions.
Agreed, the Spreadsheet needs a lot of work. It's currently just an MVP to gauge interest.
Excel has no competitors today for serious use. Google Spreadsheet lacks analysis features, LibreOffice is laughable (can't even deduplicate rows).
If you want to teach serious spreadsheet use, don't waste any time on trying to compete with Excel. Just use it. If you really need to, figure out how to make interactive lessons as VBA macros. But teach real Excel.
Also 50% of using the potential of excel is learning the shortcuts (like any good IDE). Worth making a list of the most common things you do, google the shortcut, and adding it to a cheat sheet underneath your screen.
In my experience if you are a real Excel wizard you are close to irreplaceable and yes you can demand a very good salary. A lot of the business world runs on spreadsheets and being able to do some magic will make you a very popular person with management.
Investment firms value Excel so highly (and are so good at it, see this[1] amazing video that I saw on a previous HN thread of Martin Shkreli using Excel extremely effectively) that I'm sure they have internal training that is better than anything you can get outside.
Accountants pay very good money for Excel training, at least in Israel (I tried to compete with those courses by teaching Excel automation with Python, so I did some market research and customer development.)
Could you link to a specific moment where his Excel skills show? I have seen this video pop up from time to time and by clicking through the timeline all I ever see is him copying numbers from pdf's into Excel. Surely the whole "wizardry" aspect cannot just be the fact that he uses alt-tab and arrow keys instead of a mouse?
Basically all corporate functions rely on some excel experience.
Corp Finance or FP&A typically require advanced skills. Excel is basically half the value, the other half is business acumen. You need both to be valuable.
Yeah the problem with excel based processes isn't really reliability (not the least because they are built and maintained by users who understand the domain rather than programmers writing code on specs), but lack of automation.
It's also very inefficient. Sort of a push approach where if you change an input you need to recompute every single combination of outputs instead of just the one output you want, as opposed to code which has a pull approach, where you call your output and it recalculates the steps it needs for just that output.
And as soon as you get into something you would express as a loop, or loop of loops, excel starts to be really painful to use if hardly usable.
There are a bunch of comments saying "Why not just learn python or R?"
But as a programmer, I still use excel (actually open office now I'm on Linux full time) as a brainstorming numbers based scratch pad. Somthing about the infinite grid, auto fill, not having to figure out all the parts of a 2D array allow me to chase an idea in its initial stages.
Eventually, when I have a handle on the problem and all the variables involved I will switch to jupyter notebooks or something else.
I still think we as an industry are still missing the point of why are so many people using excel, especially in areas it shouldn't be used in. There is some hidden UI/UX point we're subtlely missing.
> I still think we as an industry are still missing the point of why are so many people using excel, especially in areas it shouldn't be used in. There is some hidden UI/UX point we're subtlely missing.
I think there's a kind-of necessary trade-off between flexibility and maintainability. And a similar trade-off between flexibility and robustness. Spreadsheets are flexible, but often not very maintainable, evolvable, or robust.
Ideally you would want a tool that allows you to move along the spreadsheet <-> statically typed language continuum without much pain. So when you finish the spreadsheet prototyping, you can kind of transform that into more robust and maintainable code. But that might have the same inherent short comings as trying to convert a prototype into a production product...
If my conjecture is true -- and I'm not at all sure it is -- then we're not missing a UI/UX point. We're just building tools for people with different priorities.
>There is some hidden UI/UX point we're subtlely missing
It's not subtle, at all. Table layout is intuitive and Lua proves that tables can be a first class data type. Therefore, database normalization is important, and since it isn't enforced, easily messed up.
Spread sheets fundamentally hide the control flow, needed to grasp the normalization order of the table, in a second layer. That is a virtue, when the program is small enough, but a burden otherwise.
I recently offered an Excel for Farmers email-based video course. 279 sales. Avg price of $21.
It's been a good way to generate a bit of revenue for my bootstrapped business. But maybe more valuable as a way to generate leads for our software business (spreadsheets can't do everything!).
Looks like an interesting idea, but I have a suggestion.
I'm not a CS'ist, nor even a professional developer, but I know that CS pays a lot of attention to methods for how to write good code that can be reviewed and maintained. And I make an effort to study and apply those methods myself.
I wonder if it would be an interesting line of research in CS, and within this training, to think about how some of those methodologies can be applied to the lowly spreadsheet which, for so many people, is their main if not only programming tool.
A big difference compared to programing is that you can't really define functions and objects, so a big win in readability comes from learning the common idioms and applying them consistently (e.g., index match).
Then learning to think in units of tables (RDB theory is actually great for this) helps a lot for modularity. Once you have that, basic discipline in color-coding inputs vs. links vs. outputs and using proper headlines and comments (write them in an adjacent cell, not in the pop-up) will get you really far.
Then if you really need to be crystal clear, you can obsess about having all the inputs to a formula be in one screens worth, using good named ranges (periods are valid characters!), tables, etc.
While I'm at it, I should mention my personal pet peeve: blocks of cells where all the formulas are the same except a random handled it ed one 4 rows down. The next time you edit that formula you can be sure you're going to clobber that hard-coded adjustment.
Also, check out the work by Simon Peyton Jones on spreadsheets as functional programming for the masses.
One of my pet projects right now is an Hour of Code presentation that leverages the knowledge of spreadsheet users to learn the basics of programming in Scheme.
> write good code that can be reviewed and maintained
This is an interesting thing to think about. I just opened up a Google spreadsheet that I created in 2009 to see if I would be able to understand and update it if necessary. I managed to make a change and see some new results in under a minute.
The time to get back up to speed inside a long-lost spreadsheet is way faster than going back to a program you wrote 7 years ago. Even in complex spreadsheets, it is very easy to follow the flow of data by just clicking inside a cell to see how it was computed.
Incremental improvements are also typically easier on a spreadsheet than in a standard programming language. Part of it is because each cell or column is quite modular and I don't have to worry about extra state that isn't specified in that cell's formula.
If we're talking Excel YouTube channels, then ExcelIsFun is pretty much all you need to turn yourself into an Excel expert. I personally can't see myself ever covering it all, but I do think ExcelIsFun is worth exploring if you want to pick up some new Excel tricks.
This is a great idea, I can tell you from personal experience of running a coding school for 5 years that over 50% of inquiries we get in regards to adult classes have to do with spreadsheet skills. Some adults even think that Python will make them better at Excel macros. This is a great niche to be in, I think as you improve the UI and learning experience you'll find a lot of people who will benefit greatly from this!
Yes please. I use spreadsheets a ton but am lost when it comes to VLookup, pivot tables, linking to different sheets. Will be watching to see where this goes.
From a business standpoint I could see spending a few dollars to learn more about spreadsheets. Yes, these resources are available in other places (there may even be an organized set of Spreadsheet tutorials out there) but I didn't see that on HN this morning.
If it helps, try to think about VLOOKUP as the same method you used to use when looking up information in a phone book. To start, all you know is the name, and you want to lookup the phone number associated with that name.
There are four parts to a VLOOKUP formula...
1. The cell that contains the information you know (e.g. someone's name).
2. The grid of cells that contains the information you want to match against and the information you want to return (e.g. a table with names of people and their phone numbers). The first row of this grid should always be the list you want to match against, and the things you want to return should always be to the right of this (e.g. if the column order is 'Names, PhoneNumber' the Vlookup can work, if it's 'PhoneNumber,Names' then it won't, there are workarounds but make things easy for yourself when you're learning and set the matching column as the leftmost column).
3. The number of columns between the match column and the return column. If they're next to each other, this number will be 2. If there's another column in-between (e.g. 'Name', 'Email', 'Phone'). This number will be 3.
4. The match type. This one is easy, always use the value FALSE. If you put TRUE you can have partial matches, which isn't a good idea.
So you almost know everything you need to know about Vlookups. There's just three more things I'd recommend knowing about.
1. Absolute cell references. This is useful for VLOOKUPs when defining your match/return grid (point 2 above). What it means is the grid doesn't change as you move the formula. You can tell the grid is locked when you see 4 dollar signs (e.g. $F$2:$G$20). You can cycle between relative and absolute cell references when editing the formula by using the F4 key, but for now just type in the dollar signs if they're missing.
2. When getting your data ready to match against, there are a few useful formulas for tidying up data. TRIM is often useful (it gets rid of leading and trailing spaces). UPPER (and LOWER and PROPER) are also useful if the data is a little messy and you need to make text data have a consistent case (e.g. matching against all uppercase letters). LEFT, MID, RIGHT and FIND are all useful if you want to create a lookup column from more complex data. The only other tip worth knowing when you start out creating a lookup column is to make a mental note of the format of the data, for example a number in number format won't match against a number in text format. If you want a quick way to change numbers stored as text back to regular numbers, enter a formula which takes the number formatted as text and multiplies it by 1.
3. Sometimes you'll want to check that your lookup formula is working correctly. Other than looking for #N/A values (which means the data hasn't been found) or 0 values (which means a match was found, but the return cell was blank), a good way to test is to just create another VLOOKUP that checks the logic in reverse, e.g. If a Vlookup formula is on Sheet1 and is referencing values in Sheet2, can you create a Vlookup formula in Sheet2 that confirms the right values made it to Sheet1. To be honest this last step might be too much when you're starting out, looking for #N/A values and 0 values should be fine.
Hope this helps. Vlookups were the first thing I learned in Excel that gave me the confidence to explore more, and have proven invaluable time and time again. Any questions, please ask.
Sometimes numbers stored as text are useful. For example, if you have telephone numbers in Excel, if the numbers aren't stored as text then leading zeros are dropped (e.g. 01234567890 would change to 1234567890).
Also, it's easy enough to see when numbers are stored as text (via visual cues and the format type field).
That said, perhaps the formulas could be made more flexible. Excel formulas are somewhat Lisp-like, it'd be great if Excel had Lisp-style macros for customising formulas. Probably won't happen though. Microsoft is developing the 'Power BI' side of Excel, I could see it becoming more common to create custom functions with M or DAX when the related editing functionality improves.
It help on thinking VLOOKUPS as something of the past once you install Excel16 and get to know the DataModel thingie. It really saves you a ton of excel coding everyday.
You mean the Power BI functionality (PowerQuery, PowerPivot, etc...)? Yeah, it's useful, but I'd suggest VLOOKUPs are more beginner-friendly and better for quick data manipulation.
That said, if you want to go from an Excel beginner to an intermediate user, then the Power BI functionality is definitely worth exploring.
It's also worth knowing how to work with Excel tables, which can help with making formulas more robust. However, all of this putting the cart before the horse, VLOOKUPs are too useful to skip over, and are a fundamental part of data manipulation with Excel formulas. A grounding in Excel formulas is useful before moving on to the fancier stuff.
I find INDEX and MATCH to be more useful / flexible than VLOOKUP. The only issue is a lot of people get freaked out when they see a INDEX / MATCH statement and, instead of trying to learn what it does, claim that your model is wizardry and don't feel comfortable using it without your supervision.
Combining INDEX and MATCH is more versatile than VLOOKUP as you can do things like have the result you return to the left of the value you're looking up. However, VLOOKUP / HLOOKUP are simpler to understand when starting out, and that's a good thing when you're trying to build confidence in using Excel.
Interesting to see where this goes. I've always felt like spreadsheets could do a lot of things I never learned to do that sometimes a script or whatever is overkill (or inefficient)
I wouldn't call myself a pro, but I've had plenty of experience in Excel. Some of my most-used shortcuts aren't obvious from the current Excel interface as they're ones that follow the menu structure of Excel 2003. However, Excel 2003 shortcuts still work in the latest versions, so they're still valid, they just take a little more practice to become second nature. Here's a bunch to start with (note that commas in examples below mean you release the previous key before pressing the next one)...
Alt,D,F,F - Set filters
Alt,D,F,S - Clear filters
Alt,W,F,[Enter] - Freeze panes
Ctrl+Shift+DownArrow - Select all cells down from current cell
Alt,O,C,A - Autofit to content of currently selected cells.
To give an example of most of the above (plus a couple more)...
Ctrl+Home
Ctrl+Shift+Right
Ctrl+B
Alt,D,F,F
Alt,O,C,A
DownArrow
Alt,W,F,[Enter]
Assuming your data has column headings at the beginning of a sheet, what the above combination does is formats the column headers in bold text, sets column filters, autofits the width of the columns so that the full name of the columns are all displayed, and freezes the top row so that the column headers will still be shown when scrolling down the worksheet.
One more shortcut tip I'll pass on is for Paste Special. If you want to get the most out of Excel you should learn about Paste Special, it lets you do things like remove formulas, copy formats, transpose columns into rows (and vice versa). To use it from the keyboard, first highlight and copy the cells you're interested in, then use the 'menu key' on your keyboard (Google it if you're not sure where this key is), then look for the underlined letters in each of the menu options to choose the ones you want.
Also, these functions aren't necessarily keyboard friendly but I'd recommend taking a look at 'Text to Columns', 'Remove Duplicates', 'Evaluate Formula' and Pivot Tables, all of which I've found very useful.
F2 to edit a formula
CTRL ENTER to apply the current formula to the selected range
ALT = to insert a SUM( ) where the range is automatically selected
CTRL : to insert the current date
SHIFTLOCK F9 to evaluate a fragment of a formula while editing it
CTRL ARROW to navigate through a block of numbers (and SHIFT to select them)
But you will only be a pro if you start using array formulas.
F2 a cell (or selected range) to start editing the formula, then CTRL SHIFT ENTER to apply it as an array formula.
Array formulas are useful for two things:
1) make several cells behave as a single vector / matrix. So you can do matrix algebra. From simple things like {TRANSPOSE()} to actual maths.
2) do super flexible aggregations. Like enter in a single cell:
Which reads "do the sum of the square of elements in col A WHERE Col B greater than Col C AND Col D is "A" AND the month of Col E is the absolute value of Col F.
You can also earn money at the office, like when I bet with a colleague I could tell him how many times a column changed value in a single formula:
I am an emacs user and before that a vim user. Whenever I use a spreadsheet, it always feels real clunky to navigate. Thank you both for the shortcuts!
This is fantastic! Before going back for my PhD, I taught college, including the groan-inducing "Intro to Computers", where students basically learn Microsoft Office. I'd been a big complaint of mine that the course I was teaching was just filling a checkbox and not necessarily teaching people how to use the computer.
While I think word processing and spreadsheets are prime candidates, things like Access or the level of unnecessary detail that we'd go into on some topics (like formatting to make it look nice) were unneeded and could be better spent on different topics, like researching online and even graphics.
Its refreshing to see something like this that doesn't force any brand to teach the CORE concepts.
Honest question: for what sorts of problems and requirements are complex spreadsheet analyses the right tool? As opposed to, say, using the data analysis and visualization capabilities of something like R or Python?
I am thinking it can help people that don't have a background in computer science but are tasked to analyze data (for example, a marketing manager for a company). Spreadsheet have a lower entry point (and a lower ceiling) than a regular statistical analysis suite.
Also, using a spreadsheet has a visual component that you lose with R/python that can be off-putting if you are not comfortable with visualizing what certain lines of coding do.
That makes sense. But then I guess my question becomes: at what point are you spending so much time and effort learning advanced features of spreadsheets that you could have become proficient in one of the tools with a higher ceiling?
In many offices most employees have little to no access to programming tools, with good reason (from an IT Support point of view). However, many of these same people will have Excel, and it does what many of those users need it to do. In many companies (outside the IT department), in order for a new tool to gain traction you'd have to find a use-case that made it compelling for the average office worker that wasn't already being met by the existing tools they know how to use, both to encourage people to learn how to use it, and to encourage IT Support to roll it out.
Among other things, spreadsheets allow you to see and fix one-off errors caused by bad data. A "real" programming language is great when you have clean data, but the real world is messy and businesses generally don't devote enough effort to cleaning data. So the person doing the analysis usually has to both scrub the data and do the calculations.
I mean, you have to look at, validate, and clean your data no matter how you chose to analyze it. Any tool, whether it uses a "real" programming language or not, is going to need to support that to be useful at all, and unless your data is "fits in one or two screens of a spreadsheet" scale, I don't see how they would be better in this regard.
Spreadsheets are useful for data exploration. Can you do data exploration with other tools? Sure you can, but spreadsheets make exploring data intuitive and there's value to be had from that. I'd suggest it's not a good idea to get too hung up on any one tool, most of the time people just want to use the one that gets the job done with a minimum of fuss. Sometimes that'll be a script, sometimes not. Sometimes that'll be a spreadsheet, sometimes not.
If you want to know the answer, check out Data Smart[1]
It's a book about doing data science using Excel, until it doesn't make sense using Excel anymore. KMeans, Naive Bayes, Regression, etc. all in Excel, without totally abusing it.
Shared corporate model between half a dozen people, non of whom would know how to use it in R or Python.
A ton of inputs from different departments, monthly updates, etc.
If there's a good web-based tool/package for bringing this stuff into R/Python, I'm listening! (And I don't mean shiny- you need a way to look/edit the inputs too)
Ah yes, that makes good sense. And that helps answer the question I asked in response to pacbard's comment: Even if only one person fully understands all of the complex stuff that's happening "under the hood" of the spreadsheet, many people can contribute. Thanks!
I'm starting out with courses on fundamentals, but we can also do deep-dives on specific niche topics. Think, courses on "Performance Optimization", "VLOOKUP", "Managing Large Datasets", etc.
Nowadays I find myself using excel as a ghetto tool for data munging: compare 2 lists with match(), copying tables from web tools and filtering them in creative ways. Text to columns is your friend. Also copy stuff into Notepad++ if I need to run a quick regex.
I might add that google sheets in fact supported basic sql. It only works in google sheets (so you can't download it, load it in excel and continue to tweak things) but I still find it incredibly handy.
How did you do that in-browser spreadsheet? It is amazing. Is it React-based or what? I didn't know it was possible to set width and height for <td> elements.
This looks like a good start, but you pretty much need to get either Google Sheets or Excel into the browser window for the exercises. The half-working thing currently there just isn't going to cut it. It was very hard to work with.
I don't know what plans you have for future tutorials, but I would suggest using specific business use cases as scenarios and exploring them from a basic approach along to more advanced solutions where appropriate.
Also, I really like the name.