November 29, 2020
Complete Guide to Google Sheets – Learn Google Sheets Functions, Tips, and Tricks!

Complete Guide to Google Sheets – Learn Google Sheets Functions, Tips, and Tricks!

Using Google Sheets with your Google account allows you to create, edit, and collaborate with others on spreadsheets. Sheets are so customizable that they can fit almost any need you have, from budgeting to organizing a document with your team.

We’ll break down what you need to know to get started on a sheet, and useful tips and tricks to use when making and formatting a sheet.

You can also check out our video below for a full walkthrough and guide on how to use Google Sheets and learn the basic functions.

Getting Started & Layout

getting started layout image

Log in to your Google account to access your sheets and ensure they’re saved on your Drive.

To make a new sheet, click on the blue NEW button in the top left corner and choose Google Sheets. You can also right-click on any empty space to start a new document. You can also access sheets through the app launcher in the top right corner, or type in sheets.google.com.

A sheet looks similar to an Excel spreadsheet. On the top, you’ll be able to title your sheet and access any menu items on the top bars. A sheet is made of rows and columns, and they’ll be unformatted and empty when you open a new blank sheet.

On the bottom bar of the page, you’re able to create more sheets under that same document. If you want to create a new sheet, hit the + button on the bottom left. If you want to create a new sheet that is a completely separate document, go to File > New spreadsheet in the top left menu.

Keep in mind that any changes you make are automatically saved onto your Google Drive, so there’s no need for hitting Save every five minutes since your work is all online.

On the Sheets homepage, sheets.google.com, you can see all your sheets and create new sheets using a template. There’s a large template gallery of sheets formatted as a to-do list, calendar, schedule, grade book, and other templates as well.

Basic Formatting

basic formatting image

For basic entering of information, type any text or numbers in the empty cells by clicking on the cell and typing on the keyboard. To go from one cell to the next, you can navigate using the keyboard arrows, hit Tab to go right one cell or click using the mouse.

If you’ve put in text that’s too big for the cell, you can wrap the text using the Text Wrap feature or stretch the column width by clicking and dragging the cell’s right line.

To copy and paste multiple cells, select cells by clicking and dragging the mouse over the cells you want to copy. Then, right click to copy, choose the cell you want to copy it over to, and paste, or use the keyboard functions Ctrl+C and Ctrl+V.

formatting bar image

To change the font and color of any text, highlight the cells you want changed, and change the font style using the top bar menu.

If you want to change the color in the background of a cell, highlight the cells you want to change and select a color from the top bar menu using Fill Color.

Your sheet by default has grey-colored borders, but once you’ve exported it or printed it, the sheet would have no visible border lines. To add color to the borders of cells, highlight the cells you want changed, and using the Borders tool on the top menu, choose between the different border options.

To merge two or more cells together, highlight the cells by clicking and dragging over the cells you want changed and use the Merge Cells button on the top menu to join them together. Keep in mind that if you have something written in both of the cells, the cell to the right will lose the information. Sheets will warn you if this is about to happen, though.

A great and quick way to enhance your Sheets experience is learning the keyboard shortcuts. You can learn these by looking through the menu and seeing the different tools and its associating keyboard shortcut.

If you want the text in your cell to appear in the left, middle, or right of the cell, you can format that in sheets using the horizontal alignment tool. Select the cells by highlighting them blue and click on the alignment tool. You’re also able to align your text vertically as well with the vertical alignment tool.

You can also format your text to rotate diagonally using the rotate text tool. This is a good feature if you have longer words as headings in a table.

To change numbers to dollar amounts, highlight the numbers and format them to currency using the $ sign on the menu bar.

Formulas & Functions

formulas functions image

Formulas and functions are what make using Google spreadsheets so helpful and efficient to create your budgets. There are tons of Google Sheets formulas you can use, but to get a basic idea, let’s walk through some simple ones.

If you have a list of numbers that you want the total sum of, it’s easiest to have all of the numbers in the same letter column or numbered row. At the very bottom of the numbers, you can add them all up using the formula =(first cell location)+(second cell) and hit enter to have the sum of the cells. For example, =A1+A2+A3+A4 in that cell will give you the sum of all of those numbers.

Using a function instead of a formula can work easier if you are working with lots of numbers. If you have a large amount of numbers to add up that are in the same column/row, you can add them up by putting in the formula =SUM(first cell:last cell).

For example, =SUM(A1:A14) will add up all of those numbers. If you forget how to type in this function, you can access it in the functions > SUM tool in the toolbar.

You can also calculate averages the same way as well by typing in =AVERAGE(first cell:last cell).

If you end up with a number that has a lot of decimals, you can decrease the decimals by clicking on the cell and using the decrease decimal tool in the toolbar.

The great thing about functions and formulas is sheets will automatically update when you change any of the numbers around. You also don’t necessarily have to put your formula and function cell on the bottom of all of your numbers. The cell could be anywhere, as long as you tell it what you want to calculate.

Formatting Rows and Columns

Rows and columns are pretty versatile in Sheets and can be moved around. If you’re working on a sheet and need a column/row added or moved, highlight the column/row by clicking on the letter/number. You can drag and drop it where desired on the sheet.

formatting rows columns image

You can also add a column to the left or right, delete a column, or clear all of the info out of a column in the right-click menu. The same actions can be done with rows by right-clicking on the numbers.

Sorting and Using Freeze Rows

sorting using freeze rows image

If you’re working on a list and want to sort it in alphabetical order, some of your formatting may skew. If you want to sort a list of names in alphabetical order but keep your table headings at the top, you can do this by freezing rows.

In the top menu, navigate to View > Freeze, and you can choose if you want the first or second row frozen, or the row that you currently have highlighted. (You can also do this with columns.)

Now, if you want to sort a column or row alphabetically, go to Data > Sort sheet by column A, A → Z. Your list will be alphabetized, and your headings will remain at the top.

Unique Function

unique function image

The unique function is a handy time-saving tool that can help you look for duplicates of text or numbers. If you’ve made a long list and wonder if you have any duplicates, go to an empty cell and type in the function =UNIQUE(first cell:last cell). Sheets will provide you a list without the duplicates.

Count Function

count function image

The count function also helps to save some time and counts any long lists of something you have on your sheet. The count function is accessible through the top toolbar, but you can also type in =COUNT(first cell:last cell).

IF Function

if function image

The IF statement is great to analyze data quickly and then output any information you want to give on your spreadsheet.

Let’s use an example of grading assignments. If you want to turn students’ number grades into letter grades, you can use the IF function to quickly translate them to letter grades. In an empty cell, type =IF(number>=number, “grade letter”). For example, if a student scored 75% and you wanted to give them the letter grade A and a comment like Keep Trying!, you can type this into a cell:

=IF(C3>=80, “A”, “Keep Trying!”)

This means, if the number in the cell C3 is greater than or equal to 80, then write A. If not, say, Keep Trying!

Once you hit enter, the cell will turn into either the grade letter A or the comment Keep Trying if they scored below an A. To apply this to all of your rows, select the cell with the formula and hover over the bottom right corner with your mouse, so the black cross shows up. Then, drag it down to apply it to the other rows.

Inserting Charts

inserting charts image

Charts are an efficient way to analyze your data very quickly, and they look great. Navigate to Insert > Chart to put a chart into your spreadsheet. It will show up initially with no data, but you can edit this through the Chart Editor on the right hand side.

google sheets charts image

Using series, you can select the cells you want to use in your chart by clicking them or typing in “Sheet1!firstcell:last cell.” Once you’ve hit Ok, the numbers will automatically present itself into a bar graph. You can add as many series as you want, and they will add themselves as different colors on the bar graph. You can add an X-axis as well using the same method, and the chart will add the labels that you assign it from cells.

To change the chart type to pie graphs, line graphs, scatter plots, bar graphs, and more, click the Chart type dropdown menu in the chart editor to see the different types of charts available. Some may not work for your data, but you can change it back through the chart type menu.

In the customize editor, you can also change the way the chart looks, like colors, fonts, and a legend.

Copy and Paste Charts into a Google Slide and Google Doc

copy charts image

You might want to take your chart out of Google Sheets and insert them into a Google doc or a slideshow presentation. You’ll need to access the options in the top right corner of the chart (hover over to see the three dots) and access the chart menu. Copy chart can copy and paste your chart to wherever you’re working on Google Sheets or Google Docs. Just click Copy chart and navigate to your Google document, right click where you want it dropped, and click Paste.

When you paste the chart, it’ll prompt you and ask if you want it linked or not. Linking your chart to the spreadsheet will automatically update any numbers you change. Once you’ve made a change, there’ll be an Update button to easily refresh any changed numbers from your spreadsheet to the chart. Unlinking it will keep it as it is, and if you go to your sheet to change any numbers, the chart you have copied and pasted into your other document will not change.

Sharing and Publishing as a website in Google Sheets

google sheets sharing image

Just like anything on Google Drive, you can share Google Sheets as well with anyone. Click on the blue Share button on the top right corner of the screen and type in a name or their email addresses and hit done. If you want anyone to see the link, you can click on Get shareable link and copy and paste the link for anyone to view and access.

google sheets publishing image

To share and publish your sheet into a webpage, it can be easily done through File > Publish to the Web. Once you click it, a prompt will come up before publishing it. Publish it as a Link and indicate what sheets you want published. Once it’s published, a link will be made available to you, and you can share it with others.

Protect Sheets and Ranges

protect sheets ranges image

If you want to share a Google Sheet with somebody and you want their access limited but still able to edit, you can alter this by protecting a sheet or a range. Highlight the cells that you want protected, then go to Data > Protected sheets and ranges. The cells that you’ve highlighted will be entered into your range, and you can adjust permissions before you share the sheet with others. To protect an entire sheet, just click on the sheet and choose what sheet page you want protected.

If you don’t want your file edited by anyone other than you but still want to share your file with others, there’s no need to really protect a sheet. When you’re sharing the document, just make sure the user’s permissions settings are under View only instead of Edit permissions.

Interested in learning more about Sheets? Check out a fun way to save time using the Explore button in this tutorial:

2 comments