I could hardly think of using a spreadsheet program other than Microsoft Excel. That was before Google decided to release Sheets as part of its office suite of web apps. Google Sheets has helped me be independent of the platform and computer. Since I've spent a decent time using it, I have compiled some of the best and most used Google Sheets tips and tricks.
Though Excel is far more powerful, Google Sheets comes with a lot of features and packs plenty of punch. You need to know what to do and how to do it. That's where we step in with bunch of useful ways to help you use Sheets like a pro.
1. How to Lock a Row in Google Sheets
Locking a row or column will grid-lock it. That means it (row or column) will stay at the top when you scroll down or sideways. In a Google Sheets document, select the row you want to freeze by clicking on the corresponding number or alphabet.
Select Freeze under the View menu, and you will see a bunch of options.
The No rows option will unfreeze the selected rows. Then, one row will freeze the selected row while two rows will also freeze the row below that. Up to current row (1) will freeze all rows till where your cursor is on the sheet. Below that are the same options but for columns instead.
2. How to Merge Cells in Google Sheets
Select the rows and columns that you want to merge in the sheet.
Click on Format and select Merge cells to view all available options.
You can merge horizontally or vertically. Merge all will do it both. When you select an option, you will see this pop-up.
Depending on the option you selected, merging cells will preserve the top-leftmost value. Always.
In our case, only A1 and A2 cells are preserved while the rest have been deleted. So keep that in mind before you merge any cells.
Also on Guiding Tech
3. How to Make a Graph in Google Sheets
Data can be difficult to interpret, which is why we resort to graphs. Google Sheets comes with many different chart types. Before you pick one, make sure you have your data ready. Select all the cells you want to represent graphically and click on Charts under Insert.
You should now see a bar chart by default with some options on the right. You can change the chart type from bar to pie or other, color, select cells, add title and legend, and even more here.
I would suggest you use some dummy data and play around until you get the hang of it. Creating graphs and charts is not that difficult anyway.
4. How to Wrap Text in Google Sheets
You write a long string of text, and it is overflowing into the next cell, obstructing the value there, or it simply disappears into the cell wall. Select the cell and click on Text wrapping under Format. That’s where you choose the Wrap option.
Selecting the Overflow option will let the text continue into the next cell while Clip will cut it short, as we saw in the above screenshot.
5. How to Sum a Column in Google Sheets
Google Sheet can perform mathematical calculations easily. Addition is one formula that most people use often. Select the cells in the column or row you want to make a total of and select Sum option under the math icon.
Noticed all the other formulas available there? Google Sheets can act as a pretty good calculator.
Also on Guiding Tech
6. How to Add Columns and Rows in Google Sheets
When you are managing a lot of data on a single sheet, moving them around just to add a single row or column is not a good idea. Fortunately, there is an easy way. Just select the row or column where you need a new empty row/column, and select Insert 1 above or below option.
You should see the empty row now. The index number/alphabet will change across the sheet automatically.
You can use the same method to add columns too.
7. How to Lock Cells in Google Sheets
Locking a cell will prevent accidental changes to the same. Don’t mistake it for a security measure. People can still take a screenshot, printout, or even copy-paste the contents of the cell.
Select the cells you want to lock and select Protected sheets and ranges under Data.
You can not only lock cells here but also the entire sheet. Enter a description that will make it easier to recognize locked cells in the future in case you have more than one set. Select Set permissions when done. You can now select who all have permission to access the locked cells.
8. How to Create a Drop-Down in Google Sheets
Drop-down lists save a lot of space, making it easier to select values and fill forms or play around with variables. Select the cell where you would like to create a drop-down and click on Data validation under Data.
You can now choose the criteria for the cell in question. For example, you can create a list of dates, numbers, names, tick box, or items. You can then choose a range and determine what happens when invalid data is input.
I have selected a number ranging from 10 to 100. So when I input 5 in that particular cell, Google Sheets shows a red mark to denote an error.
Creating a list with multiple optional values will insert a downward arrow to signify the drop-down list.
9. How to Label Legend in Google Sheets
You can easily add text and format legends in Google Sheets. The process is simple and easy to make your data easily identifiable, especially if you are sharing the sheet with several other folks.
Spread It Well
Google Sheets is powerful, versatile, and free for life with no strings attached. The fact that it's available on all platforms and works right out of a browser makes it even more popular. Do share your tips and tricks with us in the comments section below.
Next up: Working with Google Sheets all the time? Here are some of the Google Sheets templates to save time and boost productivity.