Spreadsheets Explained over 5 Levels of Difficulty
Google Spreadsheets explained in five levels of difficulty. From Text and Formulas all the way to Apps Script.
Watch Now: https://www.youtube.com/watch?v=UsWuxvVypZM
Transcript
If you don't think spreadsheets are complicated and complex, then you might not be using them correctly. Or rather you might not be unleashing all of the power of spreadsheets. If you still think that spreadsheets are merely just cells that you put information into, you might not be using the entire power of spreadsheets.
I walked through in this video, five different ways to explain spreadsheets. From. Almost nothing to the most complex thing, which is app script. Come with me and enjoy this ride. Five increasingly difficult ways of explaining spreadsheets.
A spreadsheet is a series of rows and columns that are demarked by their row number and they're column name, which is a, B, C, D. These are still also column one, column two. You can put text into any cell by double clicking on it. You can put some text here. , you can also copy and paste that text anywhere you want.
And what happens is that you have a relation between everything on a row and everything in a column. So you can actually move the entire column over or move an entire row by clicking and dragging on that row. , if the cursor changes to a hand you can move it, you
can also create tables with
What's interesting about a spreadsheet from Google sheets, you can create one by just going to sheet.new and you created brand new spreadsheet. That's blank. That has one tab 1000 rows and 26 columns.
Now back to our table. If we put text in here.
We see that our relation of the first one is this text. Second, this text, third, this text. the text can go beyond just words. You can actually also put in numbers
and you can format these numbers.
Menus
Which brings us to our second level of difficulty menu items. So as you start to create some tables and you put in numbers such as 67 or 28, and maybe you wanna make this revenue or money, you get, you can select this format as currency. But there are much more options for you in these menus above and in these quick select buttons here, you can change the text from a default aerial to maybe black hand sands, and you can change the size of the text.
Let's triple that size to 30. Now you see, we have hard to read text here. We. make the row bigger. We can also right. Click on that row and resize the row to a specific number instead of fitting to data. So we can actually make this 100 pixels high. We can make this one as well.
If we select a row and click the shift button or hold it down, we can select more than one row. We can. Right click and we can resize both of those rows. At the same time. We can give them both a pixel height of 100. We can do the same with columns. So right. Click shift, select right click resize columns, and we can resize them to any column size we want.
We
could also double click on the gap between rows and it'll automatically fit the row height to the data you already have in there. If you have nothing inside of the row or column, it will do nothing. We'll do nothing. But if we have say one and two here, you double click and it goes to the minimum size that it can.
Our menu items also allow us to do alignment of text inside of cells wrapping. If you get, uh, too much text inside of a cell, you can also rotate up and down and all of these amazing menu items are available to you with just a few click.
Math
Next we can do math inside of spreadsheets. Just double click on a cell, start with an equal sign and do one plus zero. Here we can do as well here equals one plus one. We can do equals one plus two, and you see we're already pre formatted. The format stayed the same from our previous format.
So we can change that format back to plain text, or we can change it to a number and it adds 0.00 to it again, our menu helps us here where we can decrease our decimal place.
You can do all sorts of math in here, you can do multiplies with the asterisks five times five. You can also do equals five to the power of five.
We can also do math inside of cells with words, but it's not exactly as you think, not with the plus sign. We can say something like math is amazing within a space here within the quotes in the. But plus sign is not going to work.
We need to use an and symbol or ampersand. The most interesting thing too, with spreadsheets and the power of spreadsheets in math is that we can reference other cells. So instead of typing the word math here, I can delete that and type in B3. B3 is. reference of the cell that is on the B column and is in the third row.
And so this changes based on what I put in the B3. So if I say history here, this D three will update automatically, and that's a power of spreadsheets, but what might be difficult is coming up with your own formulas and functions.
Formulas
Google sheets has some built in formulas and functions that we can use directly.
And it is all based on having the equal sign show up. So instead of doing something like this, where we say equals D one plus D two plus D three, which we would get seven there, we can bring up a formula. Which allows us to do some of D one and here we can reference single cells or array of cells or groups of cells that are in either one column or one row.
We use the colon sign to delineate that it is , an array, so we can do D one colon D three, and here we get the sum.
We've added up all of these numbers. We can also do many other formulas. In fact, there are over 400 formulas in Google sheets already. Pre-created for you.
here's a list of Google sheets function list. These are all of the formulas that are available just by typing in equals and then referencing what the name of the formula is. There's a lot of them, a lot of them you don't use some of my favorite are sum average, so we can take the average of , that array.
We can also take the min and find the minimum number in a range. Or a maximum number with max.
If you know all the formulas and all the functions, you've done all the math, you've done all the menus, you know, all the menu options.
Apps Script
There is one more thing that is available to us and that's app script app script allows us to create our own custom functions . Inside of Google sheets, go up to extensions app script.
Here inside of app script, we can name and create our very own functions, such as add five and in ad five,
We'll take something inside of this parenthesis, like a number and in the brackets, we'll do something to it. And then we'll return something else which will return added five, where you're gonna use variables to do this number. Plus five variable added five. Of equals.
So here we've taken a variable added five. We take the variable number that we put into our formula. We add five and then we return added five. So let's command S this orange button on the left side tells us we have not saved our project. So we save our project. And now app script is available to us here.
And now we can use our function. Add five with a number. Inside of our sheet. So we go to our D one and we wanna add five to what's in C one. So we go equals add five in parenthesis. We go C one, which we're referencing that other cell. Now it's gonna give us a little bit of a red warning here, but we've already created this.
Just hit enter and there we go. We get six. Now we can copy and paste that same exact formula because we're referencing C1. It's going to change as we copy and paste it. It's taking whatever's in C1 here adding five whatever's in C2, adding five whatever's in C3, adding five.
And now we've created our very own function inside of Google sheet.
Now that you've had a little taste of Apps Script check out this much longer, much more interesting video where I go into how I turned one line of script into $75,000. I hope you enjoy it. I hope you learn something even deeper about app script, and you'd start to love Google sheets, just like I do.