Albeit slow, Google Suite is steadily finding new takers. The free personal use makes the adoption even more effortless. And undoubtedly, Google Sheets are one of the most crucial products of Google Suite. So we thought, why not put together this resource for Google Sheets users to help do things quickly. Ergo, this is a compilation of the most frequently asked Google sheet how-tos. Let’s get rolling.
Merge cells in Google Sheets
You can merge cells vertically or horizontally in Google Sheets. Besides, a third option to merge all will leave you with the value in the top-left cell. The process is to select the subject cells and opt for the merge accordingly.
Horizontally
Vertically
Merge All
Alphabetize in Google Sheets
First, we’ll see the standard vertical sorting. For this, you can create a filter to sort values accordingly. However, things become a bit twisted for horizontal sorting. Here, you don’t have any default filter. So, we’ll use the sort and transpose in conjunction to get the desired output. Let’s take an easy case of one column. The formula used is: =transpose(sort(TRANSPOSE(A4:H4),1,TRUE)) Let’s go backward to understand this. First, we are transposing cells (TRANSPOSE(A4:H4)), resulting in a single unsorted column. Afterward, we used 1 in the formula to apply this to the first column. Up next, the True indicates sorting in ascending order. And finally, the last Transpose to have the values back in a row. Notably, both the transpose functions aren’t visible and are applied to use the sort function, which isn’t available horizontally. Similarly, one can sort two or more columns. In those cases, the selected data will be sorted based on a single column you’ll apply the filter to.
Search in Google Sheets
Searching becomes essential if you’re stuck with a huge database trying to find a single word. Manually, it can become much similar to finding a needle in a haystack. However, the search quickly gives you the results within seconds without any hassle. The simple way is to press ctrl+f (Windows) or command-f (Mac). This will open a dialogue box where you can type the search term and press enter to locate that in the spreadsheet. But there is a more powerful version where you can match cases, search between different sheets, etc. You can find this in Edit>Find and replace. You can use this for replacing or just finding the desired values.
Make a Graph in Google Sheets
Graphs are fantastic representations giving insights like no other tool. And it makes more sense when you’re a data engineer or have some serious analysis to do. Fortunately, charting is a few clicks process in Google spreadsheets. Select the data, head over to Insert>Chart, and select from the wide range of styles. You get a pie, bar, scatter, map, timeline, etc., to display the data as required.
Wrap text in Google Sheets
Text wrapping is helpful if a cell can’t accommodate complete information natively. You can wrap text in three ways:
Overflow: Lets excess data flow out to adjacent empty cells.Wrap: Adjusts cell size to fill everything inside.Clip: Cell size remains the same while showing a part of the whole text.
In the case where the immediate next cell is occupied, Overflow and Clip work the same.
Add a dropdown in Google Sheets
Dropdowns are added with the data validation in Google Sheets. Its primary use is maintaining data consistency and ensuring users fill acceptable inputs. We will take an instance where someone enters preferences (pizza or burger) to throw a small party. So how will you enforce this to a Google sheet column and avoid getting any third choice? This was a small example of data validation where we kept the entries limited to just two (pizza & burger) while rejecting anything else (like a sandwich).
Lock cells in Google Sheets
It’s a vital function to restrict your team from accidentally modifying critical data. Besides, you can also show a warning before they proceed anyway. We’ll again take the previous example, assuming that the choices are recorded, and now you want to show a warning to anyone altering them.
Freeze Columns/Rows in Google Sheets
Freezing may be a misleading word for this. Actually, we pin row(s) and column(s) so they remain visible while we scroll away. This can be useful for a vast database where you want to have the headings floating against the values. You can freeze rows and/or columns by putting your cursor in a cell and navigating to View>Freeze. Subsequently, you can select to pin row(s) or column(s) up to that active cell.
Highlight duplicates in Google Sheet
Duplicates can ruin your entire Google sheet database. Still, it’s tough to avoid these entirely in a huge one. So what we can do is apply Conditional formatting to highlight the repeats. First, head over to Format and select Conditional Formatting. Afterward, select the range and use a formula (Countif) to pinpoint the duplicates. The formula used here is =countif(H:H,H1)>1. So, this will apply to a specific column (H) and highlight any term occurring more than once. Still, highlighting duplicates isn’t limited to this. So, we will see a few more cases. This is where you have multiple columns, and you’re trying to find duplicate entries in all the columns at once: Here, you need to change the range to A1:C21. Alternatively, you can also put 1:1000 to select almost the entire spreadsheet. Afterward, the formula needed for this will be =countif($A$1:$C$21,A1)>1. Let’s see one more iteration before we close this up. For instance, how would you modify the formula if you want to highlight the entire duplicate rows and not individual cells? Here, we highlight only the first instance of the duplicates and not all. The formula will be: =countifs($A1:$A,$A1,$B1:$B,$B1,$C1:$C,$C1)>1 Take note of the function countifs (and not countif) to cater to multiple criteria. Though there are way more cases to this, we’ll stop here and head towards the next.
Remove Duplicates in Google Sheets
This is where you get rid of all the duplicates in one go. First, select the data, and then navigate to Data>Data Cleanup>Remove duplicates. However, this is based on the rows rather than columns. For instance, if you have a 3*3 table having duplicates spread out such that all rows remain unique–it won’t delete anything. In such a case, you can use this by selecting individual columns and applying cleanup.
Conclusion
So this was a short cheat sheet to get started with Google Sheets. While playing with formulas can be fun, it’s not everyone’s cup of tea. Such users can search for extensions (Extensions>Add-ons>Get add-ons) to make things easier. PS: While Google Sheets are great at what they do, you can also try these online spreadsheet solutions as alternatives.
