Ultimate Spreadsheet Guide
Jealous of others' spreadsheet skills? Want to make your own sheets? You've come to the right place. This guide contains lots of tips and explanations to help you understand Google Sheets (because it's pretty confusing sometimes)!
Content on this thread is split into three categories: Info, Skill, and Application.
Jump to a Guide!
In certain areas of this guide, I will link to an example spreadsheet. Please feel free to make a copy of it and look at it, figure it out, or modify it! All sheets here are my original work, and I give readers of this guide full permission to do whatever they want with them.
Please let me know if you spot an error or if something needs to be clarified! Feedback will help the guide become better. You can
PM me here or send it to
the form here.
Ultimate Spreadsheet Guide
Jealous of others' spreadsheet skills? Want to make your own sheets? You've come to the right place. This guide contains lots of tips and explanations to help you understand Google Sheets (because it's pretty confusing sometimes)!
Content on this thread is split into three categories: Info, Skill, and Application.
Jump to a Guide!
In certain areas of this guide, I will link to an example spreadsheet. Please feel free to make a copy of it and look at it, figure it out, or modify it! All sheets here are my original work, and I give readers of this guide full permission to do whatever they want with them.
Please let me know if you spot an error or if something needs to be clarified! Feedback will help the guide become better. You can
PM me here or send it to
the form here.
On hiatus
[center][size=6]Contributors[/size]
[/center]
[columns][url=http://flightrising.com/main.php?p=lair&tab=userpage&id=287234][img]http://www1.flightrising.com/rendern/portraits/504766/50476588p.png[/img][/url][nextcol][color=transparent]?????[nextcol][size=6]Skua[/columns]
Contributors
|
?????
|
Skua
|
On hiatus
[center][size=6]Intro to Sheets[/size][/center]
[b]Creating a new spreadsheet:[/b]
[LIST=1]
[*]Go to your Google Drive (you must be signed into your Google/Gmail account)
[*]Hit "new" and then click on the Google Sheets button with the green icon.
[*]You have a new spreadsheet!
[/LIST]
[b]Spreadsheet Tour:[/b]
Below is my terrible quality screenshot with labels to show you different parts of the spreadsheet. Opening the image in a new tab allows you to see the labels better.
[img]https://i.imgur.com/udj2Mjh.png[/img]
At the top, we have the title. New spreadsheets are automatically called Untitled Spreadsheet. Below the title are the menus. These are basically the same menus as in google docs or any word processor, but with special spreadsheet options added. Underneath that is the toolbar, which contains options like font, text color, and zoom level.
Looking at the sheet, the individual boxes are called "cells." Vertical groups of cells are columns, which are labeled by letters (A, B, C, etc.). Horizontal groups of cells are called rows, and they are labeled by numbers (1, 2, 3, etc.). Each cell has a name - to find it, go across to the column which it's located in, then go down to the row it's in. You'll get a name made of a letter (column) and a number (row). For example, the highlighted cell is C5 here.
[img]https://i.imgur.com/bIWsQko.png[/img]
[b]Basic Formatting[/b]
By putting your mouse on the border between two columns or two rows, you can click and drag to change the size.
[img]https://i.imgur.com/az4Jvya.png[/img]
[columns][img]https://i.imgur.com/dY6Xndc.png[/img][nextcol][size=4]You can also do this by right-clicking on the column or row header (the one with the letter or number on it) and hitting resize. This allows you to resize to an exact number. By using shift to select multiple columns or clicking the grey square (to the left of A and above 1), you can resize many columns at once.[/columns]
Using the text color, paint bucket, and border tools, you can change cells' colors.
[img]https://i.imgur.com/pglGS9P.png[/img]
[img]https://i.imgur.com/PUTTYcq.png[/img]
Another cool thing you can do is merge cells to make them bigger using the merge button.
[img]https://i.imgur.com/SE2XMBh.png[/img]
Notes are helpful for adding extra information. To add one, click on your target cell, then use the insert menu to add a note by clicking on "note." A black triangle will appear in the corner of the cell if it has a note. Mouse over the triangle to reveal the note.
[img]https://i.imgur.com/Nql0xWj.png[/img]
[img]https://i.imgur.com/AnePMky.png[/img]
Intro to Sheets
Creating a new spreadsheet:
- Go to your Google Drive (you must be signed into your Google/Gmail account)
- Hit "new" and then click on the Google Sheets button with the green icon.
- You have a new spreadsheet!
Spreadsheet Tour:
Below is my terrible quality screenshot with labels to show you different parts of the spreadsheet. Opening the image in a new tab allows you to see the labels better.
At the top, we have the title. New spreadsheets are automatically called Untitled Spreadsheet. Below the title are the menus. These are basically the same menus as in google docs or any word processor, but with special spreadsheet options added. Underneath that is the toolbar, which contains options like font, text color, and zoom level.
Looking at the sheet, the individual boxes are called "cells." Vertical groups of cells are columns, which are labeled by letters (A, B, C, etc.). Horizontal groups of cells are called rows, and they are labeled by numbers (1, 2, 3, etc.). Each cell has a name - to find it, go across to the column which it's located in, then go down to the row it's in. You'll get a name made of a letter (column) and a number (row). For example, the highlighted cell is C5 here.
Basic Formatting
By putting your mouse on the border between two columns or two rows, you can click and drag to change the size.
|
You can also do this by right-clicking on the column or row header (the one with the letter or number on it) and hitting resize. This allows you to resize to an exact number. By using shift to select multiple columns or clicking the grey square (to the left of A and above 1), you can resize many columns at once.
|
Using the text color, paint bucket, and border tools, you can change cells' colors.
Another cool thing you can do is merge cells to make them bigger using the merge button.
Notes are helpful for adding extra information. To add one, click on your target cell, then use the insert menu to add a note by clicking on "note." A black triangle will appear in the corner of the cell if it has a note. Mouse over the triangle to reveal the note.
On hiatus
[center][size=6]Intro to Functions[/size][/center]
Functions allow you to do lots of cool things. There's probably 100+ functions. [url=https://support.google.com/docs/table/25273?hl=en]This is a great guide to them.[/url]
Functions start with =, then the function, then in parentheses the additional info. For example, if you wanted to add some numbers, it would look like this: =SUM(3+4). You can also put cell names in a function. For example, =SUM(A3+B3).
To put functions into a sheet, click on the cell you want to have the function, then go up to the function bar, located right underneath the toolbar (it has a little fx on it). Then type in your function. If you have done it correctly, certain parts should be colored and the cell will display the result of the function rather than the formula.
If you need help with a function, click the ? box. A pop-up will help explain it.
[columns][img]https://i.imgur.com/bFM7D9p.png[/img][nextcol][img]https://i.imgur.com/KJIhk2R.png[/img][/columns]
Intro to Functions
Functions allow you to do lots of cool things. There's probably 100+ functions.
This is a great guide to them.
Functions start with =, then the function, then in parentheses the additional info. For example, if you wanted to add some numbers, it would look like this: =SUM(3+4). You can also put cell names in a function. For example, =SUM(A3+B3).
To put functions into a sheet, click on the cell you want to have the function, then go up to the function bar, located right underneath the toolbar (it has a little fx on it). Then type in your function. If you have done it correctly, certain parts should be colored and the cell will display the result of the function rather than the formula.
If you need help with a function, click the ? box. A pop-up will help explain it.
On hiatus
[center][size=6]Basic Math[/size][/center]
Sheets can do math for you! In this section, I'm only showing the 4 basic operations: add, subtract, multiply, and divide.
[b]Addition:[/b]
There are three functions you can use to perform basic addition. [b]=SUM[/b], [b]=ADD[/b], and [b]+[/b]. SUM and ADD are formatted like this: =SUM(what you want to add). + doesn't need any special formatting, just a = before it (=+).
[columns][img]https://i.imgur.com/wsTIhjt.png[/img][nextcol] You can use SUM and ADD with regular numbers, like this.[/columns]
[columns][img]https://i.imgur.com/60RVJWy.png[/img][nextcol] You can also use SUM and ADD with cell names. Using cell names means the answer in A1 will change if you change A2 or B2. If I changed B2 to 6, A1 would recalculate to become 9. You can also use a comma in place of the plus sign, if you want.[/columns]
[columns][img]https://i.imgur.com/UmtkWGr.png[/img][nextcol]+ works the same. You can use cell names, as well.[/columns]
To add a whole range of cells, use =SUM(startcell:endcell).
[img]https://i.imgur.com/NQLyVuA.png[/img]
[b]Subtraction[/b]:
The functions [b]=MINUS[/b] and [b]-[/b] work exactly the same as SUM (because it's really just adding a negative number). They can use numbers or cell names. MINUS doesn't subtract a range, unlike sum.
[b]Multiplication:[/b]
Use the functions [b]=PRODUCT[/b], [b]=MULTIPLY[/b], or [b]*[/b]. PRODUCT will allow to you to multiply a range (remember, =PRODUCT(startcell:endcell), but MULTIPLY will not. MULTIPLY will only multiply two numbers at once (divide them with a comma (,) or an asterisk (*)). * can multiply many things.
[b]Division:[/b]
Division is mostly the same, but with one little trick I'll get to in a second. Use the functions [b]=QUOTIENT[/b], [b]=DIVIDE[/b], or[b] /[/b]. Neither QUOTIENT nor DIVIDE can divide a range. Only use two numbers/cells, with a comma or / in between. / can divide multiple things if you put multiple slashes (Ex. =3/4/5/6/7). [i]The weird thing about division is that QUOTIENT will round your answer!!! This can really mess stuff up, so if you want the exact decimal, use DIVIDE or /.[/i]
[center][size=5]Want to see how these functions work?
[url=https://docs.google.com/spreadsheets/d/1fTNnaI7ZMZaoZ5n16d42_rRZnyVaRvjhPAORxMBMsME/edit?usp=sharing]Example Spreadsheet Here![/url][/size][/center]
Basic Math
Sheets can do math for you! In this section, I'm only showing the 4 basic operations: add, subtract, multiply, and divide.
Addition:
There are three functions you can use to perform basic addition.
=SUM,
=ADD, and
+. SUM and ADD are formatted like this: =SUM(what you want to add). + doesn't need any special formatting, just a = before it (=+).
|
You can use SUM and ADD with regular numbers, like this.
|
|
You can also use SUM and ADD with cell names. Using cell names means the answer in A1 will change if you change A2 or B2. If I changed B2 to 6, A1 would recalculate to become 9. You can also use a comma in place of the plus sign, if you want.
|
|
+ works the same. You can use cell names, as well.
|
To add a whole range of cells, use =SUM(startcell:endcell).
Subtraction:
The functions
=MINUS and
- work exactly the same as SUM (because it's really just adding a negative number). They can use numbers or cell names. MINUS doesn't subtract a range, unlike sum.
Multiplication:
Use the functions
=PRODUCT,
=MULTIPLY, or
*. PRODUCT will allow to you to multiply a range (remember, =PRODUCT(startcell:endcell), but MULTIPLY will not. MULTIPLY will only multiply two numbers at once (divide them with a comma (,) or an asterisk (*)). * can multiply many things.
Division:
Division is mostly the same, but with one little trick I'll get to in a second. Use the functions
=QUOTIENT,
=DIVIDE, or
/. Neither QUOTIENT nor DIVIDE can divide a range. Only use two numbers/cells, with a comma or / in between. / can divide multiple things if you put multiple slashes (Ex. =3/4/5/6/7).
The weird thing about division is that QUOTIENT will round your answer!!! This can really mess stuff up, so if you want the exact decimal, use DIVIDE or /.
On hiatus
[center][size=6]Sharing your Sheet[/size][/center]
To let people see, edit or comment on your sheet, you have to share it. This works the same as sharing a google doc.
[img]https://i.imgur.com/9qPvS5W.png[/img]
Most of these options are self-explanatory.
If you want anyone to access the sheet (ex. a raffle ticket sheet or gene cost calculator), use the shareable link to let anyone who clicks on the link to view your spreadsheet.
Sharing your Sheet
To let people see, edit or comment on your sheet, you have to share it. This works the same as sharing a google doc.
Most of these options are self-explanatory.
If you want anyone to access the sheet (ex. a raffle ticket sheet or gene cost calculator), use the shareable link to let anyone who clicks on the link to view your spreadsheet.
On hiatus
[center][size=6]Replacing Decimal with Comma[/size][/center]
I'm from the US, so my spreadsheet defaults to "." as decimals and "," as number separators. If you want them reversed, open up the spreadsheet you're working on.
[columns][img]https://i.imgur.com/LdUiN6a.png[/img][nextcol][size=5]Click file, then click spreadsheet settings.[/columns]
In the General settings, you can change your locale, time zone, and language. If your locale uses , or . differently, your spreadsheet should automatically switch once you choose the locale.
[img]https://i.imgur.com/mg5ObOC.png[/img]
Replacing Decimal with Comma
I'm from the US, so my spreadsheet defaults to "." as decimals and "," as number separators. If you want them reversed, open up the spreadsheet you're working on.
|
Click file, then click spreadsheet settings.
|
In the General settings, you can change your locale, time zone, and language. If your locale uses , or . differently, your spreadsheet should automatically switch once you choose the locale.
On hiatus
next up:
creating alternating colors and tab colors
[application] raffle ticket recorder
creating data validation drop-downs
freeze parts
hide sheets
hide columns
conditional formatting
[application] profit tracker
concatenate
checkbox if
[application] auto-pinglist
[application] auto bio coder
next up:
creating alternating colors and tab colors
[application] raffle ticket recorder
creating data validation drop-downs
freeze parts
hide sheets
hide columns
conditional formatting
[application] profit tracker
concatenate
checkbox if
[application] auto-pinglist
[application] auto bio coder
On hiatus
How to Use Enter without Exiting the Cell
Normally, when you hit enter in a textbox or word processor, your cursor will move down a line. However, in Sheets, enter will move your cursor out of the cell and into another cell. This is pretty annoying, right?
There is a solution: use the keys CONTROL and ENTER at the same time to move down a line without exiting your cell.
How to Use Enter without Exiting the Cell
Normally, when you hit enter in a textbox or word processor, your cursor will move down a line. However, in Sheets, enter will move your cursor out of the cell and into another cell. This is pretty annoying, right?
There is a solution: use the keys CONTROL and ENTER at the same time to move down a line without exiting your cell.
On hiatus
[center][size=6]Creating Alternating Colors and Tab Colors[/size][/center]
[b]Alternating Colors:[/b]
Having colors alternate on your sheet is really helpful. It makes it easier to trace where stuff is and can stop you from getting mixed up.
To create alternating colors, click the format menu, then "alternating colors."
[img]https://i.imgur.com/cSiiadv.png[/img]
This will bring up a menu with some presets.
[columns][img]https://i.imgur.com/4JyHBcK.png[/img][nextcol]Make sure the range is correct! You want all of your sheet to be alternating, so adjust the range accordingly. You can also add a header or footer with the checkboxes near the top. There are preset color combos in this menu, but if you scroll down you can create your own.[/columns]
[b]Tab Colors:[/b]
[columns][img]https://i.imgur.com/gEH7miC.png[/img][nextcol]Tab colors make it easy to tell apart different sheets. To add colors to tabs, hit the arrow by the tab name and choose a color.[/columns]
When you're done, it'll look like this:
[img]https://i.imgur.com/2qFwSYQ.png[/img]
Creating Alternating Colors and Tab Colors
Alternating Colors:
Having colors alternate on your sheet is really helpful. It makes it easier to trace where stuff is and can stop you from getting mixed up.
To create alternating colors, click the format menu, then "alternating colors."
This will bring up a menu with some presets.
|
Make sure the range is correct! You want all of your sheet to be alternating, so adjust the range accordingly. You can also add a header or footer with the checkboxes near the top. There are preset color combos in this menu, but if you scroll down you can create your own.
|
Tab Colors:
|
Tab colors make it easy to tell apart different sheets. To add colors to tabs, hit the arrow by the tab name and choose a color.
|
When you're done, it'll look like this:
On hiatus
