I could barely think of using any spreadsheet other than Microsoft Excel. This was before Google decided to release Sheets as part of its office suite of web apps. Google Sheets has helped me to be platform and computer independent. Since I've had a great time using it, I've compiled some of the best and most used Google Sheets tips and tricks.
Contents1. How to Lock a Row in Google Sheets2. How to merge cells in Google Sheets3. How to make a chart in Google Sheets4. How to wrap text in Google Sheets5. How to sum a column in Google Sheets6. How to add columns and rows in Google Sheets7. How to Lock Cells in Google Sheets8. How to Create a Dropdown Menu in Google Sheets9. How to Label Caption in Google SheetsSpread Well
Although Excel is much more powerful, Google Sheets comes packed with features and packs a lot of punch. You need to know what to do and how to do it. That's where we come in with a bunch of helpful ways to help you use Sheets like a pro.
Let's start.
Locking a row or column will lock it in the grid. This means that it (row or column) will stay on top when you scroll down or sideways. In a Google Sheets document, select the row you want to freeze by clicking the corresponding number or alphabet.
Select Freeze from the View menu and you'll see a bunch of options.
The No rows option will unfreeze the selected rows. Then one line will freeze the selected line while two lines will also freeze the line below it. Up to current row (1) will freeze all rows up to where your cursor is on the sheet. Below are the same options but for columns instead.
Select the rows and columns you want to merge in the sheet.
Click Format and select Merge Cells to view all available options.
You can merge horizontally or vertically. Merge all will do both. When you select an option, you will see this popup.
Depending on the option you selected, merging cells will retain the leftmost value. Always.
In our case, only cells A1 and A2 are kept while the rest have been deleted. So keep that in mind before merging cells.
Data can be difficult to interpret, so we use graphs. Google Sheets comes with many different chart types. Before choosing one, make sure your data is ready. Select all the cells you want to graph and click Graphs under Insert.
You should now see a default bar chart with some options on the right. You can change chart type from pie bar or other, color, select cells, add title and legend, and much more here.
I suggest you use dummy data and play until you get it right. Creating charts and tables isn't that hard anyway.
You write a long string of text and it overflows into the next cell, obscuring the value there, or it just disappears into the cell wall. Select the cell and click Text Wrapping under Format. This is 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 screenshot above.
Google Sheet can perform mathematical calculations easily. Addition is a formula that most people use often. Select the column or row cells you want to total and select the Sum option under the math icon.
Notice all the other plans out there? Google Sheets can be a very good calculator.
When you are managing a lot of data on a single sheet, just moving it around to add a single row or column is not a good idea. Fortunately, there is an easy way. Simply select the row or column where you need a new blank row/column, and select the Insert 1 Above or Below option.
You should see the blank line now. The index/alphabet number will automatically change on the sheet.
You can also use the same method to add columns.
Locking a cell will prevent accidental changes to it. Do not confuse it with a security measure. People can still take a screenshot, print, or even copy-paste the cell contents.
Select the cells you want to lock and select Protected Sheets and Ranges under Data.
Not only can you lock cells here, but also the whole sheet. Enter a description that will make it easier to recognize locked cells in the future in case you have more than one. Select Set Permissions when you're done. Now you can select who all have permission to access locked cells.
Drop-down lists save a lot of space, making it easier to select values and fill out forms or play with variables. Select the cell where you want to create a drop-down list and click 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, checkboxes, or items. You can then choose a range and determine what happens when invalid data is entered.
I selected a number from 10 to 100. So when I enter 5 in that particular cell, Google Sheets shows a red mark to indicate an error.
Creating a list with multiple optional values will insert a down arrow to signify the drop-down list.
You can easily add text and format captions in Google Sheets. The process is simple and easy to make your data easily identifiable, especially if you share the sheet with several other people.
Google Sheets is powerful, versatile, and free for life with no strings attached. The fact that it is available on all platforms and works directly from a browser makes it even more popular. Share your tips and tricks with us in the comments section below.
Then: Constantly working with Google Sheets? Here are some of the Google Sheets templates to save time and increase productivity.
