
Show HN: Gridmaster – A Code School for Learning Spreadsheets - bryanbraun
http://gridmaster.io
======
no_protocol
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.

~~~
arkitaip
Is there any money to be made on mastering Excel? Like are businesses ready to
pay for Excel related services?

~~~
SonOfLilit
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.)

[1]
[https://www.reddit.com/r/videos/comments/4df5il/martin_shkre...](https://www.reddit.com/r/videos/comments/4df5il/martin_shkreli_doing_magic_with_microsoft_excel/)

~~~
vazamb
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?

------
nekopa
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
_> 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.

------
nhorob67
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)

------
analog31
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.

~~~
fovc
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.

------
satysin
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
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
Thanks, will check it out.

------
primitivesuave
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!

------
goatherders
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
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.

~~~
pbhjpbhj
>enter a formula which takes the number formatted as text and multiplies it by
1. //

Do spreadsheets need loose[r] typing? It's it ever necessary to operate with a
formula on numbers-as-text?

~~~
ZenoArrow
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.

------
0xCMP
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)

------
j_m_b
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
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
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.

~~~
ZenoArrow
Thanks for the tips, didn't know about Ctrl+Shift+L.

Regarding array formulas, I wouldn't recommend them. Especially as you can use
SUMPRODUCT for the same purposes as array formulas...

[http://ww2.cfo.com/accounting-tax/2010/12/spreadsheets-
hate-...](http://ww2.cfo.com/accounting-tax/2010/12/spreadsheets-hate-arrays-
then-hijack-sumproduct/)

------
tsumnia
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.

------
del82
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?

~~~
chmelynski
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.

~~~
del82
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.

~~~
ZenoArrow
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.

------
Bootvis
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
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.

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

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

------
beastcoast
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
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-
regul...](http://stackoverflow.com/questions/22542834/how-to-use-regular-
expressions-regex-in-microsoft-excel-both-in-cell-and-loops#22542835)

[https://msdn.microsoft.com/en-
us/library/ms974570.aspx](https://msdn.microsoft.com/en-
us/library/ms974570.aspx)

------
kccqzy
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.

------
fiatjaf
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
It's based on an open source project, Handsontable. Details here:
[https://handsontable.com/](https://handsontable.com/)

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

