1. Protect your Google Spreadsheet data from accidental or malicious changes with these 3 methods.
The ability to share and collaborate on a spreadsheet in Google Apps is an excellent feature, but it may mean you need to look more carefully at protecting your data from unwanted edits. There are 3 key ways to do this.
Protect a Worksheet
Protect individual worksheets in a Google Spreadsheet. To do this, select Tools from the menu and choose Protect sheet. You have the option to set permissions, much like a Google Doc, that will give editing rights to: anyone invited as a collaborator; only you; or a list of collaborators. If you need to stop collaborators from editing certain cells, you could place those cells in a “Results” worksheet and make that read-only. Formulas can reference any worksheet so cells that need data entry can be “opened up” and cells that have a formula can be “locked” in a protected worksheet.
Set Up Notifications
Get notified of any changes in a Google Spreadsheet by setting up notification rules. To do this, select Tools from the menu and choose Notification rules. You have the option to be notified when changes are made to: the spreadsheet; a specific worksheet; a cell or cell range; collaborators; or (if the spreadsheet is joined to a form) when a user submits a form. Notifications can be sent as soon as a change is made or as a daily summary.
The first 2 options are straightforward changes that offer good options to protect most spreadsheets, by stopping editing and notifying you if changes are made. But what if you need to protect a series of cells or an individual cell? Google Spreadsheets currently doesn’t have this functionality, so a workaround is required.
Set Data Validation
Stop individual cells, or a range of cells, from being edited by setting a value that the cell must have. Choose to set a Number, Text, Date or Items from a List. Each criteria has further settings, for example: a Number cell can be a range or maximum value; a Text cell can be set to contain or not contain word(s); a Date cell can be set to be a valid date only; and a List cell can only be populated from a list. A further option enables you to either warn the editor that validation rules have not been met but allow the change, or disallow any changes that do not meet the criteria.
Data validation is a very useful method to ensure that cells containing a formula are not mistakenly edited. To do this:
- select the cell or range you want to be validated
- select the Text criteria and set the second drop down to “equals”
- in the blank box add your cell formula
- untick the Allow invalid data box.
2. Try SPARKLINE for Sheets: miniature charts in a single cell
Google have just introduced a great feature in Sheets: SPARKLINE
The SPARKLINE function allows you to create miniature charts within a single cell to rapidly provide an attractive visual representation of data and trends.
Google recently added the two new types – column and winloss – along with the existing line graph and bar chart.
Our example shows a sheet with some data (source Wikipedia) on human population by country and represents the trend in population growth/decline over the years 1990, 2008, 2025.
We show 3 types of SPARKLINE charts: line, bar & column.
The function codes for each are:
Line:=SPARKLINE(C2:E2,{“charttype”,”line”;”max”,1500})
Bar:=SPARKLINE(C2:E2,{“charttype”,”bar”; “color1”, “blue”; “color2”, “red”})
Column:=SPARKLINE(C2:E2,{“charttype”,”column”;”max”,1500; “color”, “orange”; “lowcolor”, “blue”; “highcolor”, “red”})
Here’s a link to a copy of our example sheet. Click on “Make a copy” option to take a copy for yourself to have play with.
3. The Explore function in sheets magically turns the data in your Google Sheets into charts and analysis
Google’s constant innovation continually provides Apps users with new ways to use and gain benefits from files and data.
The Sheets Explore function generates charts enabling insight and analysis on data for Sheets users who have little knowledge or confidence in using charts or just saving you some time making your own.
To access Explore, open a spreadsheet.
To work on a specific portion of your data you can select a range of cells. By default, Explore works on where your cursor is in the current worksheet.
- In the bottom right, look for the Explore icon
- When the icon is green you can click it to see recommendations and tips.
- When the icon is grey, there is no analysis to see.
- Charts and analyses will pop up, showing trends and patterns in the data in your worksheet.
- Hover over a chart to see which data in the spreadsheet is being used in that chart.
- You can change the recommendations by selecting different areas of data in your worksheet.
To close the Explore panel, click the X in the top right corner.
To add a chart or formula to your spreadsheet
- To add a chart or formula from the Explore panel to your worksheet you can it onto your spreadsheet.
- Alternatively by clicking the Insert chart icon.
Note: You must have edit permission on the file to be able to add a chart or formula to a spreadsheet.
Once a chart is added to your spreadsheet you can use the Advanced edit… option from the drop-down menu in the top right of the chart
Use the This Example Spreadsheet (Click Here) and have a play!
Hope you liked the above article. We do some amazing & unique workshops that can help you Scale-up & Automate business.
With these tools in our workshop you can automate 70% of your business:
