15 ms·

Show HN: Gridmaster – A Code School for Learning Spreadsheets

188 points by bryanbraun 10y ago

0xCMP 10y ago
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)
Bootvis 10y ago
Looks interesting, which features do you intend to cover? How are you going to onboard people with previous (extensive) experience using spreadsheets but not with feature X?
- bryanbraun 10y ago
  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.
no_protocol 10y ago
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. Also, I really like the name.
- bryanbraun 10y ago
  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. (edit: typo)
  - deleted 10y ago
    [deleted]
  - SonOfLilit 10y ago
    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.
    - cm2187 10y ago
      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.
- arkitaip 10y ago
  Is there any money to be made on mastering Excel? Like are businesses ready to pay for Excel related services?
  - skaplun 10y ago
    CRM administration for example. Excel mastery is generally the entry point into those teams (from my experience)
  - Ecio78 10y ago
    Many investment firms heavily rely on Excel
  - wtvanhest 10y ago
    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.
  - satysin 10y ago
    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.
  - DanBC 10y ago
    If you find a way to audit spreadsheets to find fraud and error you'd make a fortune. Speadsheets are scary. http://www.eusprig.org/2006/spreadsheets-in-clinical-medicine-warning.pdf http://www.eusprig.org/2006/spreadsheets-in-clinical-medicin...
    - blahi 10y ago
      This spreadsheet "shaming" is getting tiresome. The sheer amount of spreadsheets means that more than a few mistakes will come to light. Let's not forget the fMRI software with bugs. Was it done in Excel? Sure, Excel has some problems. No, they are not even close to what is implied in HN threads like this one.
del82 10y ago
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?
- pacbard 10y ago
  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.
  - del82 10y ago
    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?
    - ZenoArrow 10y ago
      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.
- chmelynski 10y ago
  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.
primitivesuave 10y ago
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!
beastcoast 10y ago
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. Now if only Excel had good Regex built in.
- auxym 10y ago
  VBA has access to "VBScript Regular Expressions 5.5", which is quite functional. I use it from time to time. http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops#22542835 http://stackoverflow.com/questions/22542834/how-to-use-regul... https://msdn.microsoft.com/en-us/library/ms974570.aspx https://msdn.microsoft.com/en-us/library/ms974570.aspx
goatherders 10y ago
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.
- ZenoArrow 10y ago
  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.
analog31 10y ago
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.
- no_protocol 10y ago
  > 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.
- fovc 10y ago
  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.
debaserab2 10y ago
Learn to use VLOOKUP and you'll have a job forever.
- analog31 10y ago
  In other words, you can never escape having to understand pointers. ;-)
abreu 10y ago
Nice design, I must say. Clearly made with "<3".
tsumnia 10y ago
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.
nhorob67 10y ago
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!). https://www.harvestprofit.com/blog/excel-for-farmers-course https://www.harvestprofit.com/blog/excel-for-farmers-course
kccqzy 10y ago
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.
satysin 10y ago
I know a lot of people hate him but Martin Shkreli is an Excel wizard. Check out his videos on YouTube https://www.youtube.com/watch?v=9vAgzPp3oI4 https://www.youtube.com/watch?v=9vAgzPp3oI4
- ZenoArrow 10y ago
  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. https://m.youtube.com/user/ExcelIsFun https://m.youtube.com/user/ExcelIsFun
  - satysin 10y ago
    Thanks, will check it out.
fiatjaf 10y ago
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.
- bryanbraun 10y ago
  It's based on an open source project, Handsontable. Details here: https://handsontable.com/ https://handsontable.com/
j_m_b 10y ago
I'd like to learn how to be a spreadsheet wizard who doesn't need to use the mouse. What are the major shortcut keys that the pros are using?
- ZenoArrow 10y ago
  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.
  - cm2187 10y ago
    CTRL+SHIFT+L is faster to set filters. A few other useful ones: 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: ={SUM(A1:A10*A1:A10*(B1:B10>C1:C10)*(D1:D10="A")*(MONTH(E1:E10)=ABS(F1:F10)))} 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: ={SUM(1*(A1:A10<>A2:A11))-1} Array formulas are slow but very powerful.
nekopa 10y ago
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.
- throwaway729 10y ago
  > 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.
- posterboy 10y ago
  >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.

188 points by bryanbraun 10y ago

0xCMP 10y ago

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)

Bootvis 10y ago

Looks interesting, which features do you intend to cover? How are you going to onboard people with previous (extensive) experience using spreadsheets but not with feature X?

bryanbraun 10y ago
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.

no_protocol 10y ago

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. Also, I really like the name.

bryanbraun 10y ago
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. (edit: typo)
- deleted 10y ago
  [deleted]
- SonOfLilit 10y ago
  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.
  - cm2187 10y ago
    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.
arkitaip 10y ago
Is there any money to be made on mastering Excel? Like are businesses ready to pay for Excel related services?
- skaplun 10y ago
  CRM administration for example. Excel mastery is generally the entry point into those teams (from my experience)
- Ecio78 10y ago
  Many investment firms heavily rely on Excel
- wtvanhest 10y ago
  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.
- satysin 10y ago
  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.
- DanBC 10y ago
  If you find a way to audit spreadsheets to find fraud and error you'd make a fortune. Speadsheets are scary. http://www.eusprig.org/2006/spreadsheets-in-clinical-medicine-warning.pdf http://www.eusprig.org/2006/spreadsheets-in-clinical-medicin...
  - blahi 10y ago
    This spreadsheet "shaming" is getting tiresome. The sheer amount of spreadsheets means that more than a few mistakes will come to light. Let's not forget the fMRI software with bugs. Was it done in Excel? Sure, Excel has some problems. No, they are not even close to what is implied in HN threads like this one.

del82 10y ago

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?

pacbard 10y ago
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.
- del82 10y ago
  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?
  - ZenoArrow 10y ago
    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.
chmelynski 10y ago
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.

primitivesuave 10y ago

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!

beastcoast 10y ago

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. Now if only Excel had good Regex built in.

auxym 10y ago
VBA has access to "VBScript Regular Expressions 5.5", which is quite functional. I use it from time to time. http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops#22542835 http://stackoverflow.com/questions/22542834/how-to-use-regul... https://msdn.microsoft.com/en-us/library/ms974570.aspx https://msdn.microsoft.com/en-us/library/ms974570.aspx

goatherders 10y ago

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.

ZenoArrow 10y ago
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.

analog31 10y ago

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.

no_protocol 10y ago
> 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.
fovc 10y ago
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.

debaserab2 10y ago

Learn to use VLOOKUP and you'll have a job forever.

analog31 10y ago
In other words, you can never escape having to understand pointers. ;-)

abreu 10y ago

Nice design, I must say. Clearly made with "<3".

tsumnia 10y ago

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.

nhorob67 10y ago

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!). https://www.harvestprofit.com/blog/excel-for-farmers-course https://www.harvestprofit.com/blog/excel-for-farmers-course

kccqzy 10y ago

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.

satysin 10y ago

I know a lot of people hate him but Martin Shkreli is an Excel wizard. Check out his videos on YouTube https://www.youtube.com/watch?v=9vAgzPp3oI4 https://www.youtube.com/watch?v=9vAgzPp3oI4

ZenoArrow 10y ago
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. https://m.youtube.com/user/ExcelIsFun https://m.youtube.com/user/ExcelIsFun
- satysin 10y ago
  Thanks, will check it out.

fiatjaf 10y ago

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.

bryanbraun 10y ago
It's based on an open source project, Handsontable. Details here: https://handsontable.com/ https://handsontable.com/

j_m_b 10y ago

I'd like to learn how to be a spreadsheet wizard who doesn't need to use the mouse. What are the major shortcut keys that the pros are using?

ZenoArrow 10y ago
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.
- cm2187 10y ago
  CTRL+SHIFT+L is faster to set filters. A few other useful ones: 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: ={SUM(A1:A10*A1:A10*(B1:B10>C1:C10)*(D1:D10="A")*(MONTH(E1:E10)=ABS(F1:F10)))} 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: ={SUM(1*(A1:A10<>A2:A11))-1} Array formulas are slow but very powerful.

nekopa 10y ago

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.

throwaway729 10y ago
> 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.
posterboy 10y ago
>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.