You may be familiar with the basic concepts of Google Sheets, but Google spreadsheets have many great features you may not know. This article will introduce you to 5 useful features of Google Sheets.
Enter the data sheet from the web page into the spreadsheet
This may sound super boring but it is really useful. If a site has a table or list of information you want to track, you can use the ImportHTML function to filter information and paste it into a spreadsheet. After that, the data will automatically update when you open the spreadsheet (if there is a change on the original board). This function has a syntax of:
= ImportHTML ('URL', "table", 0)
The URL is a web page with data, 'table' is the way data is displayed on the web page (you can also use 'list' if it's a list) and '0' represents the table you want to enter if there is one. multiple tables on the site (0 is the first, 1 is the second, etc.).
The example above is a table that tracks statistics for a fantasy tournament. You can enter different statistics from the website like Baseball Reference on the spreadsheet. Of course you can bookmark this site but with the ImportHTML function you can edit and retrieve specific data (by adding Col1, Col4, etc. after '0'), as well as loading data from another table on other websites and express all in a spreadsheet.
Link data from other spreadsheets
If there are multiple spreadsheets (or multiple sheets in a spreadsheet) that are interrelated, you can link the data in these spreadsheets.
You can reference cells from other worksheets (or the entire spreadsheet). For example, there is a spreadsheet, in which a worksheet keeps spending at a grocery store and a worksheet contains spending for the month. And you have another spreadsheet that includes the expenditure of each month for different items. In this total spend spreadsheet, you can refer to the grocery store worksheet and a specific cell containing the sum. When updating the original worksheet, the value on the master sheet will automatically update.
The function will look like this:
= sheet1! B5
"Sheet1" is the name of the worksheet with the data you want to reference and "B5" is the cell you want to reference, between these two numbers is an exclamation mark. If you want to reference data from a completely different spreadsheet, you will use the following IMPORTANGE function:
= IMPORTRANGE ("URL", "sheet1! B5")
The URL is a link to another spreadsheet, which will link the cell in this spreadsheet to the cell where you entered the formula. When the cell updates with a different value, this box also updates accordingly. You can reference a range like B5: C10 with this function.
Conditional formatting
This feature seems more popular than the above features. Conditional formatting allows you to change the appearance of a cell based on the data it contains. You can access this feature by clicking on ' Fomat ' in the toolbar and then selecting the ' Conditional Formatting ' command. In the table that opens on the right, you can set parameters. For example, it is possible to fill a cell or multiple cells with green if its value is greater than 0.
In addition, you can use the IF function, although not part of a conditional format, but it allows you to add a certain value to a separate cell whenever the value in the cell is a specific number:
= IF (B4> = 63, "35", "0")
So in this example, if the cell value B4 is 63 or greater, you can automatically make the value of the current cell 35 and if not, the value will be 0.
Embed a spreadsheet on the website
If you've created a calendar or list in Google Sheets and when sharing this document with others, you'll send them email to view it. However, if you need to add other information on your blog or website, you can embed this spreadsheet on the website.
To do this, navigate to File> Publish to the Web . From here click on the ' Embed ' tab and then choose to embed either the spreadsheet or just a specific sheet. Then, just copy and paste the iFame code into the website.
Use the script with the spreadsheet
With Google Apps Script, you can use scripts with spreadsheets, discover available add-ons by going to Tools> Add-Ons or you can write scripts yourself by selecting Script Editor in the Tools menu.
For example, you can write a custom script that allows you to press a certain button to immediately add a specific value to the existing values in a cell.
See more:
ncG1vNJzZmismaXArq3KnmWcp51kgm6zzqiepZ1dqLWmsdOsZJ%2BdkanCs7HSZrCorV2otbDBy51kpKafrA%3D%3D