You’re probably familiar with the basics of Google Sheets, but Google’s spreadsheet offering boasts loads of features that aren’t obvious at first glance. Here are some of our favorites.
Of course, you probably are already familiar with some basic formulas, like SUM and AVERAGE. And it’s likely you’ve gotten to know the toolbar fairly well, but it’s pretty amazing just how deep it all goes. I love spreadsheets, but even to this day I’m still discovering new tricks within Google Sheets.
Import Data Tables
This sounds super boring, but it’s actually really neat. If a website has a table or a list of information that you want to keep track of, you can use the ImportHTML function to essentially scrape that data and paste it into a spreadsheet. From there, the data automatically updates whenever you open the spreadsheet (if changes to the original table were made, of course). The function would look like this:
=ImportHTML(“URL”, "table", 0)
The URL is the web page where the data is located, “table” is how the data is shown on the webpage (you can also use “list” if it’s a list), and “0” represents which table it is you want to import if there are multiple tables on the web page (0 is the first one, 1 is the second one, and so on).
An example of this would be keeping track of sports stats for a fantasy league. You can import various stats from a site like Baseball Reference into a spreadsheet. Of course, you could just bookmark the site, but with ImportHTML, you can customize things like exactly which stats appear (by adding Col1, Col4, etc. after the “0”), as well as fetch data from other tables on a different webpage and have it all show up in a single spreadsheet.
RELATED: 10 Tips and Tricks for Google Docs
Reference Data From Other Spreadsheets
If you have multiple spreadsheets (or multiple sheets within a spreadsheet) that all relate to each other in some way, you may find yourself going back and forth between them often. There’s a way to make all that a bit easier.
You can reference cells from other sheets (or another spreadsheet entirely). For example, say that keep records of everything you spend on groceries in one sheet and that sheet also contains a total amount spent for the month. And, say you have another sheet that gives you a summary of what you spend each month on various categories. In your summary sheet, you could reference that grocery sheet and the specific cell that contains the total. Whenever you update the original sheet, the value in the summary sheet would update automatically.
The function would look like this:
=sheet1!B5
“Sheet1” would be the name of the sheet with the data you want to reference, and “B5” is the cell that you want to reference. The exclamation point goes in between. If you want to reference data from an entirely different spreadsheet, you would use the IMPORTRANGE function, like so:
=IMPORTRANGE("URL", "sheet1!B5")
The URL is the link to the other spreadsheet. This links the cell in that spreadsheet to the cell that you enter the above formula in. Whenever the cell gets updated with a different value, the other cell updates along with it. As the name of function suggests, you can also reference a range of cells, like B5:C10.
Conditional Formatting
This feature is a bit more well known than some of the others I’ve mentioned, but I feel like it’s still not as popular as it should be.
Conditional Formatting lets you change a cell’s appearance based on the data that cell contains. You can access the feature by clicking “Format” in the toolbar and then selecting the “Conditional Formatting” command. In the pane that opens to the right, you can set up your parameters. For example, you might want to turn a cell (or cells) green if the number they contain is greater than zero.
There’s also the IF function, which isn’t technically a part of the Conditional Formatting feature, but it can take it to the next level in a way. This lets you do things like add a certain value to a separate cell whenever the value in the active cell is a particular number:
=IF(B4>=63,"35","0")
So in that example, if cell B4’s value is 63 or greater, you could automatically make the current cell’s value 35. And then show a 0 if not. Of course, that’s just an example, as there’s a lot more you can do with it.
RELATED: How to Highlight a Row in Excel Using Conditional Formatting
Embed Spreadsheets on a Website
If you created a schedule or a list in Google Sheets that you want to share with others, you could just share the actual document with them by sending them an email invite to view it. However, if you need to supplement it with other info that’s on your blog or website, you can actually embed spreadsheets onto webpages.
All you need to do is navigate to File > Publish to the Web. From there, click on the “Embed” tab and then choose whether to publish the whole spreadsheet or just a specific sheet. After that, just copy and paste the iFrame code into your webpage.
Play Around with Scripts
For anything that Google Sheets can’t do out of the box, there’s usually a Google Apps Script that you can use alongside your spreadsheet to make pretty much anything happen.
We’ve talked about Google Apps Scripts before, and there’s a lot you can do with this kind of capability. You explore available add-ons by going to Tools > Add-Ons, or you can write your own scripts by selecting the Script Editor in the Tools menu.
For example, I have a custom script that allows me to press a single button to instantly add specific values to the existing values in a handful of cells. You can’t do this with Google Sheets out of the box, so having the script editor here gives Google Sheets a good dose of steroids.
Article Source : https://ift.tt/2JN6qh8