 through winter's frail and biting nights and summer's fleeting escapades you've been there with me by my side a bond that cannot be betrayed we've made each other laugh and cry we've seen the world through rosy shades you've guarded me throughout it all your columns built like palisades you are my true and deepest love your name is like my own heart's beat I hear it when I close my eyes spreadsheet spreadsheet spreadsheet I know without a single doubt I'll be with you till end of time we'll travel all across the world from cell a1 to z 9 9 I'll roll you down the river Sen and say two one eleven eight and wear the rest but numbers here you'll know it is in fact a date the sum of you and I is love our souls tuned like an orchestra they make up more than just their parts it's provable by formula and as the years begin to fly time's hand will guide me to my grave for while we are both made of cells mine sadly do not auto-save but do not fret my gridded friend for in this bitter sweet farewell I'll know it's thanks to only you that in this life I did excel thank you everyone I'm Graham and I'm Ashkan and we're here giving this talk because we are in love with spreadsheets and it's a wonderful kind of love one that doesn't involve jealousy fighting or divorce except on some rare occasions loving spreadsheets is like loving duct tape it's the love of an unexpectedly useful tool that with enough creativity and dedication you can do almost anything with now I know there are some people out there who believe that love between humans and spreadsheets is unnatural but we're here to make a stand and say that it's one of the most natural things in the world in fact we believe that humans and spreadsheets are meant to be with each other and this love goes far back into the history of human civilization there was a point in human history when things as basic as writing numbers and three-dimensional stacked area charts didn't exist all humans had back then was a dream a dream that has been present in human beings since we could call ourselves human the dream of spreadsheets the earliest records of counting go back 40 to 75 thousand years and even before we had numbers we could still compare marks on an object or drawing with the number of animals in a herd or track the number of magic mushroom trips that you accidentally went on written language came around 5500 years ago etched into stone and clay and with this came tax forms which actually make up a humongous portion of early writings that we've discovered with some hymns laws and crude jokes thrown in there as well here you can actually see early papyrus from about 4600 years ago and oh wait does something there look a little familiar this early spreadsheet shows detailed accounting for the construction of the pyramid of Giza one of hundreds of sheets discovered proving once and for all that its spreadsheets and not aliens responsible for the pyramids if you're an ancient record keeper and you're in charge of collecting and making sense of all this information it's hard to imagine that you didn't dream of some kind of magic grid that can tabulate its own numbers from plotting stars to making crop charts to organizing large projects to of course taxes there's likely not a single human in charge of all this data who didn't fantasize of something that in all likelihood looks a lot what our modern like what our modern spreadsheets look like mainly because throughout history human still did all the things that we currently do in spreadsheets they just did them a lot slower and a lot worse even if we look just at the very basic spreadsheet functions like adding and subtracting we can see how primitive our options have been over time the abacus was the best we had for a long time pretty much 4,000 years literally the only other mathematical tool that we used for longer was our fingers it wasn't until the 1600s that the slide rule finally ended the abacus's reign of mathematical terror and from there it wasn't long before these clunky calculating machines were created with wooden and metal gears and dials not commonly in use until the 1800s and then after that you might be surprised to know that the next real revolution and calculation wasn't until we got big honking computers in the 1940s only available to specially trained people who were deemed worthy of access most of us were still using slide rules or doing math longhand like chumps 20 years later in the late 1960s the first handheld electronic calculators were made and just look at those beauties and within just a decade and a half they had dethroned slide rules personal computers for hobbyists were also around at that same time in the 1970s but they were still pretty technical actually had a little paper printout and no screen not a lot of people had the resources or the interest to purchase one and 1977 is what actually marked the birth of the age of personal mass market computers like the apple to two years later in 1979 humans produced what is to this day considered to be one of the pinnacles of human achievement visit calc the first ever spreadsheet program it's considered to be the first killer app like literally the phrase was invented and then applied to it before visit calc people shop for computers based on technical specifications and after visit calc came out people shop for computers based on whether or not they could run visit calc through the 80s and into the 90s ever improving spreadsheet applications continue to be one of the largest driving forces behind computer sales including the introduction of Microsoft excel in 1985 and from there things just kept getting more and more advanced to the point that you could be sitting at a conference watching a presentation and not even realize that the entire thing is in fact a spreadsheet and this is enough just glorifying the impact of spreadsheets because we happen to love them this is actually a well-established history there have been two real explosions that have propelled the industry forward the first one really happened in 1977 and it was the spreadsheet I remember when Dan filestra who ran the company that marketed the first spreadsheet walked into my office at apple one day pulled out this disk from his vest pocket and said I have this incredible new program I call it a visual calculator became visit calc and that's what really drove propelled the apple to to the success it achieved and beyond just moving apple forward visit calc was one of the early reasons that businesses and individuals purchased computers at all check out this clip of a panel with the developers where they're talking about one of the many numerous awards that they got for developing visit calc together they were honored by the western society of engineers washington award and I want to read to you as I change the citation from the award letter where they were recognized for the invention of the first computer spreadsheet software program which led to the proliferation of the computer industry which in turn led to the economic expansion of the late 20th century perhaps living as you do in modern luxury with a plethora of free and paid spreadsheet apps for simply strewn about your hard drive this reaction to their initial launch might be surprising to others of us it's the result of the millennia's old dream of a traceable ancestral desire finally come to fruition and now one of the most powerful and sophisticated tools humans have ever designed lies waiting for you to wield and as you start to use it you'll feel yourself becoming more and more powerful like a sorcerer slowly learning to harness the abilities that they possess so let's take you through the journey of learning to wield the might of a spreadsheet and explore what sort of new powers you'll unlock along the way before you've ever started using spreadsheets you're just a normal human nothing more than a skin sack full of meat popsicles who has yet to realize their full potential but now you're ready to take your first step towards that potential and the first step is when you realize that you can use spreadsheets to simply take information and display it nicer than you could in a normal text document some information is just better suited to being laid out in a table and putting it into a table does more than just make it easier to digest it can also make it more functional for example here's a list of some of the float-on owners and a couple of interesting facts about each of us not only can you easily compare specific pieces of information between us but if I need to quickly get a list of just our favorite colors I can highlight just this column over here or if I wanted to look at information for just one of us I could highlight that row good so now you've had your first sip from this wellspring of spreadsheet etude but instead of finding yourself quench you feel parched your lips ever thirsty for the ambrosia that you have just tasted as you keep pushing forward the next thing you learn is how to effectively use how effectively spreadsheets can help you stay organized the columns of a spreadsheet make it simple to easily track a person or thing and track its progress through a series of different steps it basically becomes a simplified CRM system it can really help you stay on top of large projects we've used spreadsheets to help organize things like our art program our music program and when we were planning the conference it was basically nothing more than a big series of spreadsheets with a little layer of Steven Johnson thrown on top so you didn't notice at this point creating a spreadsheet is basically the first move we make when we start in on almost any project and the key here is to have absolutely no faith in your ability to remember anything once you give up that delusion and instead start treating spreadsheets as your external brain your mind will become unburdened and allow itself to spend its time doing what it really wants to do wondering if a pet giraffe would love you the same way that you would love it you're now starting to feel more powerful in your spreadsheet training but you know there's so much more thus far you've only been exploring the innate powers within you but a vast world of abilities is simply waiting at your fingertips it's time to learn your first spells and begin to use spreadsheet formulas need to sum up sales numbers to get a total figure out the average price paid of a certain retail item count how many of your customers phone numbers have an eight in them so that you can see if your numerological chart says that you're going to be blessed with extra sales this moon cycle no problem spreadsheets can eat these tasks for breakfast for example here's a spreadsheet full of phrases we're trying to bring back in fashion we can easily count how many of them include dogs with this formula which should be a pretty reliable indicator for how successful we're likely to be and the thing is once you learn some of the basics in terms of how to use spreadsheet formulas you unlock the ability to access a tremendous amount of data about your business now lots of software that you use for business is going to have built-in reporting but there's always a thousand different ways to cut and slice data and you don't really know if that program is going to give you the information exactly as you wanted fortunately almost all of that data can be exported to raw spreadsheets and from there you can do almost anything with it a little spreadsheet know how lets you figure out pretty much whatever metrics you want about your business which means you don't have to keep waiting for your software company to build in that new report you asked about which is constantly getting delayed because the owners are too busy trying to find the perfect outfit for their spreadsheet talks at this point there's no looking back a faint but noticeable grid of lines has started to overlay itself on everything you see numbers out in the world look like they're just begging to be tabulated summed and rounded but you know the well of spreadsheet knowledge is deep and right now you're about treading on the surface the next step of your journey begins when you start to use spreadsheets for actually modeling different scenarios let's say you're considering doing a price raise or you want to want to expand your hours but you're not sure at what point the extra payroll cost makes it not worth it you can easily set up factors you want to adjust in their own sales and then reference those sales using formulas to do your other number crunching for you automatically this allows you to easily model as many different scenarios as you want by simply changing the variables and seeing how it affects every other number along the way at this level you also gain the ability to drive yourself completely insane by over analyzing every possible version of every possible scenario so you've now completed a substantial amount of your training and you're ready to take your skills outside of the academy it's at this point that your grasp of spreadsheets is so powerful you begin to use spreadsheets just as a method to get through your everyday life you discover that spreadsheets have a myriad of purposes in your leisure activities for example here's a spreadsheet I use to make a sandwich now I don't always accompany my lunches with a spreadsheet but this particular sandwich was a concept I came up with where a single sandwich changes toppings every couple inches freeing you from the tyranny of needing to choose just a single type of sandwich to eat for lunch one day I decided to make one that fully transitioned between 24 different types of sandwiches which is simply more sandwich than a man can keep in his head but spreadsheets were there to help me accomplish my dreams allowing this beautiful sandwich to be born and the use of spreadsheets for fun goes beyond just inventing groundbreaking evolutions in the field of sandwich three this is a painting by Tatsuo Horiyuchi a Japanese artist who creates his artwork entirely using Excel and this is a game by Tyler Robertson that is a playable dungeon crawler that is entirely built in a spreadsheet you can click on the buttons up here to control the character collect keys and progress through a series of levels of course your hunger to harness the power of spreadsheets does not stop here you're no longer satisfied with the spells written by the Council of Excelsior's you're ready to take things in your own hands and tap into the pure energy at the source of spreadsheets itself it's at this point that you start to use macros and scripts programmatic tools that allow you to go beyond the scope of what you can do within the UI of the spreadsheet itself allowing you to have interactive elements sophisticated workflows automation and essentially endless control of what the say the spreadsheet is able to perform and at this level you can honestly just start to do some truly amazing things here's a video by Dylan Talche for he built an entire music creation program using Excel this is a fully 3d rendered Doom like game that blogger C bell built entirely inside of a spreadsheet as you can see it has active lighting effects and even has enemies that you can fight and that can hurt you the entire game is actually able to run without any macros only spreadsheet formulas but you have made a version with macros that allows you to use your keyboard as a controller to move around so at this stage your spreadsheet prowess has elevated you beyond the realm of mere mortals and you're ready to compete against other Titans of your stature that's when you decide to enter the financial modeling world cup which is a real competition that can really be watched online or on ESPN 8 the Ocho which really airs obscure sports grand masters of spreadsheets compete head-to-head by being given complicated scenarios that are modeled as data sets inside of a spreadsheet they're then asked to figure out the answers to various questions that take sophisticated spreadsheet manipulation to calculate and the gain points for every answer they get correct in a given amount of time it truly is the sport of kings at the final level of spreadsheet knowledge you're now a pseudo divinity able to use spreadsheets to manipulate the very fabric of reality around you peering deeper and deeper into the cell based structure of the universe while slowly phasing in and out of this material world you also know how to use pivot tables now I'm sure some of you have been on board with us the entire time and you probably already have a spreadsheet of all the times that spreadsheets have meaningfully impacted your life for others of you spreadsheets can seem intimidating and it makes sense when you're faced with such raw unmitigated power it's hard to know where to start but getting proficient at spreadsheets is easier than you probably think we think the best way to learn how to use spreadsheets is through a mix of faith and Google the faith part is having confidence that anything that you want to do in a spreadsheet is possible is how you figure out how to actually do it and as you start playing around with spreadsheets the best way to get better is just to resist the temptation to do anything by hand even if it would be faster to just go through and manually count something or clean up data in a column one by one spend the time and search the internet for how to do it with a formula or how to automate it if you do this every time you're faced with a problem you'll constantly be learning more and more formulas moving yourself higher and higher up the levels of spreadsheet mastery and bringing yourself one step closer to competing in the financial modeling World Cup and look we know what some of you out there are thinking though grandma Nash gone there's like a million pieces of fancy software out there nowadays why wouldn't I just use one of those instead of one of these smear sheets or whatever you guys are talking about yeah that's what you sound like pretty embarrassing listen we know there's a lot of software out there nowadays and that there are things that are much more user-friendly and slick than a spreadsheet and most of them will give you stats and graphs on your information faster and easier than a spreadsheet but the problem is the more you add slick user interfaces and pre-made graphs the more you're boxed into what that specific piece of software is designed to show you the beauty about spreadsheets is how many different things you can do with them it's sort of like how there are special tools for cutting all sorts of individual types of fruit does a pineapple slicer cut a pineapple more easily and evenly than you would be able to with a knife yeah sure but what happens when you want to cut a strawberry you need to go buy one of those fancy strawberry quarters and then what happens when you want some watermelon and you have to go buy this specialized watermelon cuber it's a little harder to cut any fruit with a knife than with a specialized tool but the knife gives you the freedom to cut any piece of fruit that comes your way and saves you from having a kitchen gadget or a drawer full of these crazy gadgets in your kitchen and at the end of the day the more you use the knife the better you get at cutting things with it which makes you that much more proficient and making an excellent fruit salad and if you add some fresh mint and a little bit of lemon juice I mean now we're really talking people wouldn't even take a bite of Cindy's famous artichoke dip at this year's fourth of July party the point here is that using spreadsheets lets you become that much better at using spreadsheets which then makes using spreadsheets that much more useful but like many things the most intimidating step is just knowing how to start so let's show you that's right it's time for a live spreadsheet demo which we're pretty sure will go down in history is the most exciting thing that has ever happened in this arena first let's collect some data so if everybody out there doesn't mind taking a second we have prepared a quick survey for you which you can get to by just scanning this qr code from your phone this is only for existing float centers so apologies to everyone else out there and this qr code itself is actually something that we generated using a spreadsheet formula so we'll give you guys a second if you don't know the exact answers to the questions that's fine put in your best guesses this isn't like the industry report or something which you should all feel out by the way industry report for people watching virtually you can scan this and fill it out too and while we wait for everyone to fill it out let's enjoy some footage from the financial modeling World Cup so you can experience the thrill firsthand you start out with 10,000 points each time you land on a cell you deduct some points but the red cells your points get cut in half the green cells your points are multiplied in two and the ultimate goal is to figure out how many points you have when you get to the finish line so just a lot a lot to model so Michael is on the board now let's look at Michael okay named ranges with those directions you know I did bring it into power three split columns into roles boom lookup table right there yeah just two columns just two columns so it was just an X lookup and no two-way lookup needed right oh look at that oh we have dear mid at 311 points which is bad news he should have 320 points if he completed three right yeah one of those one of those is an error if there's anything that had commas in it and didn't get copied correctly you know like sell online is you know really coming along it's getting much better but there's still with little weird bugs between win 32 and excel online so it's getting there yeah and those down in the corner there dims 8 p.m. actions per minute 195 which is pretty fast for a case that is this difficult so he's he's currently working level 4 this is worth 200 points 20 answers that are 10 points each and in general you know once you get the logic to build one answer you should be able to copy that logic down so generally thrilling truly all right so now we have some data let's start getting our hands dirty first thing we want to do is convert all these answers that people said are in kilograms over into pounds because we're in the United States and we'll just make a quick equation here to say that hey if people entered it in kilograms go ahead and convert it over into pounds using the convert formula and otherwise you can just keep it as the number was entered in pounds and that'll put everything into the same unit so we can actually compare them side by side and it'll make sense and once we have one cell done it's as easy as highlighting everything or sometimes even just hitting enter on Google sheets then we hit command enter boom fill out the entire column immediately and with selecting that column in the bottom right get rid of this one here that doesn't seem very realistic so once we select the entire column we can easily see things like the sum and the average we can also see the min and the max which someone said the minimum order was one pound of salt okay thank you and 10,000 it looks like was the max that someone entered over there but all of this is just child's play as far as spreadsheets are concerned new tab suppose what we really want to see isn't just individual float centers and how much salt they ordered but a visual of how much salt the entire industry is ordering on a month-by-month basis so we're going to take all these orders and move them over into this new tab and we're actually going to transpose the data so that it shows up horizontally along the top there as opposed to vertically and then we're just going to take a little styling make sure this sheet doesn't get too out of hand over here and that we can navigate and see things easily and then for the left-hand side we're going to mark chart months going along there so we'll start with the current month put in the next month and then use spreadsheet magic to just click and drag and it'll automatically in infer what months we're going to put after that so we'll drag it out for 10 years here so now we've made is a grid of everyone and all of the salt orders along the top and months going forward for 10 years along the left-hand side and the fun begins what we're going to do from here is to plot out everyone salt orders in the month that they made them so on months where they ordered nothing we're going to put zeros and on months where we forecast that they're going to order salt according to their numbers they put in or you guys put in we're going to put that exact number of salt in there so to start out we're just going to find when the last salt order they did is which we have the info for and then start plotting that forward on a graph with the order frequency that you have so we're going to use some fancy equations we use mod to divide those with each other and find out if there's a remainder which tells us what months you actually ordered salt in don't worry too much about the specifics and we're going to go back and just set some of these as constants using a dollar sign so that when we click and drag this over the entire sheet it's not going to get out of hand so now that we have that in one cell we select every other cell for 10 years and hit an error would you do and hit command enter there we go and it automatically fills in all of the salt orders projected for the next 10 years for everyone who filled this out blamo but thank you very much let's not stop there now that we have this we can sum up the rows of salt orders to get a total for every with every salt order that people are projected to order in the month that is ordered take all of those and then graph it out into its own graph then we can take this straight to Wall Street and start trading on Epsom salt futures being hailed as the most prodigious financial forecasters of the century and very very likely to be interviewed on mad money with Jim Kramer by the end of the week and as a final touch we can put a little photo of myself and Ash con in the corner giving us a thumbs up for a certified job well done and our project is complete we're also going to make this image available so that you can put it on all of your own internal spreadsheets which is highly recommended by pretty much all spreadsheet professionals out there so now you that you've seen some real life spreadsheet wizardry in action we wanted to wind things down by leaving you with your own little starter kit or spell book if you will it's a little something we call Graham and Ash con's super neat spreadsheet cheat sheet and it's available if you go over to Graham and Ash con super neat spreadsheet cheat sheet dot com just download it or save it to your own Google Drive to get it to work it contains a bunch of the most useful keyboard shortcuts and formulas just general tips that we found are the things we use most commonly when we're going to use which is ourselves which is a lot it also includes a little game that we made in spreadsheets to help you settle any small arguments that you get in during the conference so if you're trying to decide who's buying the next round of lobster rolls or who has to wait for the elevator just click over here and decide who's player one and two click this little button to duke it out and as an extra extra bonus there's a sheet in here with the recipe that we like to use when we're making Taiwanese style dumplings and you can actually just go up here adjust how many dumplings you want to make and with a little spreadsheet magic it'll automatically adjust all the different amounts of ingredients for you we like to make these in big batches and freeze them because we love them so much but not as much as we love spreadsheets thank you