Articles

Article Listing

How to Fix Slow Google Sheets

posted Nov 5, 2018, 9:04 AM by Sanjeev Jain   [ updated Nov 12, 2018, 6:09 PM ]

If your Google sheets are  taking forever to load or process data, then these 20 Optimisation strategies will be able to help you.

  1. How to know if Sheets are Slow
  2. Google Sheets Limitations. 
  3. Check size of Google Sheets
  4. Deleting un-used row\columns
  5. Converting formulas to static numbers to improve speed
  6. Using closed ranges
  7. Use volatile functions with care or eliminate them 
  8. VLOOKUP Optimization
  9. Import formula optimisation
  10. Use IF to optimise formula calls
  11. Using a Control Switch to manage extensive formulas
  12. Use Filter, Unique and Array_Constrain to create helper tables
  13. Avoid Calculation Chains
  14. Use Conditional Formatting sparingly
  15. Split & Combine your slow Google Sheets to speed them up
  16. Archive Old Data
  17. Use the power of Google Apps Scripts
  18. Other Troubleshooting Tips
  19. Know when it’s time to move to a database
  20. CONCLUSION

1. How to know if Sheets are SlowCheck for these behaviours:

  • Calculations become very slow and loading bar appears frequently every time you make a change in the sheet.

  • Sheets are slow to respond to mouse or keyboard clicks.
  • Data does not show even after you have entered the same into a cell. You need to reload the sheet sometimes to have it show.
  • Sheet becomes un-responsive. You may get an error message like the one below or you may need to re-load the page.
  • Computer heating up, using a lot of RAM & battery. 

2. Google Sheet LimitationsKnow these limitations inherent in Google Sheets -
  • Google Sheets has a limit of 2 million cells per workbook (see Details Here).
  • Google Sheets has a maximum number of columns of 18,278 columns.
  • Within a single cell, there’s a maximum string length of 50,000 characters.
  • You can add a maximum of 40,000 new rows at a time.

3. 
Check size of Google Sheets

  • Using a special  audit tool you can see the total number of cells in the Sheet, how many have data in them and then statistics for each individual sheet too, including how many volatile and array functions you have.

4. 
Delete Unused Rows\Columns

  • Google Sheets get slower as they get larger. So one of the first things to try, is to reduce the size of your Google Sheet.
  • Blank rows\columns slow down performance, dramatically so in fact. So it’s a good idea to delete them whenever you can, so you reduce the number of cells Google Sheets is holding in memory.
  • Ideally you should remove any large numbers of blank rows under your data, or columns to the right of your data.
  • Especially for pages getting input from Forms you can delete all the rows under the data as every Form Input creates a new row for itself.
5. Convert formulas to static values wherever possible

  • After you have used a set of formulas in your data, you don't need them again, you should convert them to values. This is also true for columns like S.no etc.
  • This will reduce the file size, which will help performance overall, and it’s also best practice (to reduce errors occurring if someone or something happens to break the formulas).
  • You change formulas to values by copying them and then:  Edit > Paste special > Paste values
  • Do keep a copy of a “live” formula at the top of your columns of data in your tables, so you have a record of how the calculation was performed and can also very quickly re-use it if needed:

6. 
Use closed range references

  • It's better to use Closed range references  like A1:B1000 instead of open references like A:B in your formulas wherever possible. Open references add 
     extra calculation overhead to your Sheet.
  • It might not look like there’s much data in column A, but Sheets will check every cell, including all the blank ones, if you use open ranges like A:B.
7. Remove volatile functions or use with caution

  • Remove volatile functions or use with caution - NOW(), TODAY(), RAND() and RANDBETWEEN() as they recalculate every time there’s a change to the Sheet. All dependent formulas must also be recalculated too (i.e. formulas that reference or include a nested volatile function).
  • This uses up processing power and so can negatively impact your Sheet’s performance. Best idea is to put these functions in a cell like A1 & then you can refer to the cell in any formula using $A$1 (absolute reference) where required. This will significantly increase the speed.

8. 
Vlookup Optimization

  • Use closed, rather than open, range references for your search tables where possible.

  • Reduce the size of your lookup table, if possible. For example, rather than search across everything you might use the Filter function to create a smaller, helper table for the lookup first, and then use that in your VLOOKUP.

  • Don’t nest the FILTER (or other) function inside your VLOOKUP and create the table on the fly each. It’s much, much slower.

  • For example, this formula is bad practice and will really slow down your Sheet if you have a lot of them:

    =VLOOKUP( $A1 , UNIQUE( FILTER( $K$1:$N$10000 , $K$1:$K$10000 < 100 )) , 2 , FALSE )

    whereas creating that nested Filter table as a helper table and referencing it is the preferred method:
    =VLOOKUP( $A1 , $F$1:$I$10 , 2 , FALSE)

9. IMPORTARANGE Optimization

  • The ImportHtml, ImportFeed, ImportData, ImportXml and ImportRange formulas pull data from sources external to your Google Sheet, so they require an Internet connection to function and are slower than other functions that operate entirely within your single Sheet, so they will potentially affect the performance of your Sheet.

  • For example, using ImportRange to import large amounts of data from another Sheet will take time and you may see the Loading… error message for a while:

  • The best idea is to minimize the number of times importrange formula is used. It may be best to get the entire data in one go in a hidden sheet and then use the data for reference in other sheets instead of having multiple use of import range.
     
  • Note, you’re limited to a maximum of 50 of ImportData functions in a single spreadsheet. Also note, that these functions recalculate as follows :

    ImportRange: Every 30 minutes

    ImportHtml, ImportFeed, ImportData, ImportXml: Every hour

10. Use IF statements to optimise formula calls

  • You can reduce the number of lengthy calculations being done in Sheets by using the IF Statement to check for certain preconditions. For example - only perform a Vlookup if the cell is not empty:

    = IF( A2="","", Vlookup (A2.....))

  • The same can be done for any formula. Other examples could be to check if a certain Value is larger or smaller or check for a certain text to do length calculation only if required. 
        = IF( A2>=1000,Filter("Sheet1!....),"NA")

11. 
Using a Control Switch to manage extensive formulas

  • You can actually turn Calculation on & off using a control switch cell with the IF statement. This way the sheets will not slow down as they will only start calculating the desired formula when you actually need them to. 

  • For example - Use data validation to create a drop-down menu in a single cell, the control cell, with values “Process” and “On Hold”.

    Then use an IF formula like this to only process the performance-hungry formulas in your slow Google Sheets when you want to:

    =IF( $A$2 = "On Hold" , "On hold" , your_formula_here )

  • Here’s an example of this control switch technique applied to some performance-hungry Instagram IMPORTXML formulas (for the top 25 accounts):


12. 
Use Filter, Unique and Array_Constrain to create helper tables

  • The FilterUnique and Array_Constrain functions can be used to create smaller helper tables for further data analysis work.
  • If you had a big table with 150,000 rows of data in, but were only interested in performing calculations on a smaller subset meeting certain parameters like "New/Old", "Country/City etc." , then you could use these functions to reduce the size of your table and create a new helper table for your calculations.

  • For example, you might use the Filter and Unique functions to create a helper table from a dynamic subset of your larger table:

=UNIQUE( FILTER( A1:E100000 , B1:B100000 = "India"))

        or using Array_Constrain to create a helper table from a static subset of your larger table:

=ARRAY_CONSTRAIN( A1:E100000 , 10 , 4 )

13. 
Avoid Calculation Chains

  • Avoid calculation chains of cells that reference each other in a long chain, for example a long column of cells equal to the cell above, like so:

  • It's better to reference a single cell with absolute references and then copy that down your column, e.g.

$A$2

14. 
Use Conditional Formatting sparingly

  • Conditional formatting can be very slow on large datasets because it’s implemented on a cell-by-cell basis. So it’s best to use it for small data tables and in your dashboard reports. You will see immediate improvement by removing the same from large data sets.

15. 
Split & Combine your slow Google Sheets to speed them up

  • Assuming we’re sticking with Google Sheets though, it might be time to split up your huge dataset into separate Sheets if your Sheet is so slow that it’s frustrating to use.

  • This is especially true if you are importing large amounts of data from other sheets. If you are importing data from say 16 separate files then instead to importing directly into one single file it may be an excellent idea to make 3 or 4 separate sheets and each one imports data from 3-4 sources. You can them bring the data from just 3-4 sources to your main sheet. This will reduce the calculation load on the main sheet and make things much faster.

16. 
Archive Old Data

  • Once your sheet starts getting too much data and is becoming sluggish it may be a good idea to Archive the old data into a separate sheet for future reference. Maintain the past Months, Quarter or Year's data as your need may be. 

    The best approach to split & archive your really big Google Sheets is to make new copies, label each Sheet clearly and then delete all the data apart from the data relevant to that Sheet. For example, if you’re offloading data from 2017 into it’s own Sheet, then here are the steps:
    • Make a copy of the master Sheet, with all your data in
    • Name this new Sheet and include “2017” somewhere in the title
    • Sort the data by year in this new Sheet
    • Add a filter and show everything except 2017 data (i.e. hide the 2017 data)
    • Delete all these rows
    • Remove the filter to leave only the 2017 data

    Once you’ve completed all the steps for all your archived data, you’ll want to remove it from your master Sheet. I’d advocate keeping a full copy of your master Sheet before you start deleting any rows of data though.

    You’ll end up with a master Sheet that just has your current, or live, data in it, and it’ll hopefully be much faster.

    You can also do any calculations or data analysis locally and then bring the data back together in your master Sheet using the IMPORTRANGE formula.

17. 
Use the power of Google Apps Scripts

  •  Google Apps Script can be used to extend the functionality of Sheets.

  • Apps Script is extremely effective at doing big batches of calculations, especially repetitive ones, often more quickly than thousands of regular formulas.

  • Detailed instructions on the same are out of scope of this article, however we do conduct in-dept tutorials for the same. You can send you inquiry using this LINK to us for training on Google Apps Scripts. 

18. 
Other Troubleshooting Tips 

Sometimes it might not be an issue with Google Sheets themselves, try these strategies to see if any resolve your issues:

  • Close and re-open the Google Sheet (sometimes it’s the simplest fixes that work).
  • Refresh your browser. Close other tabs, opening too many tabs will slow down your computer as each Tab uses a large amount of RAM.
  • Clear your cache and cookies. 
  • Try another browser or operating system. Make sure both browser and operating system are currently running on their latest version.
  • Disable browser plugins or extensions or try accessing the sheet in Incognito\Private mode.
  • Try replicating the issue on another computer to see if it’s the computer hardware having issues. A Google Sheet’s calculation speed depends mostly on the local resources.
  • Try replicating your issue on another network to see if it is a network/internet issue. Most functions are run locally in the browser but some, like the IMPORT formulas, require access to the Internet (as well as any Apps Script which is run on Google servers).
  • Try disabling and re-enabling offline access.
  • Try on a different or a faster computer.

19. 
Know when it’s time to move to a database

There will come a time, when Google Sheets won’t fulfill all of your data needs. There is the 2 million cell limit that may fill up pretty quickly with real-world data. Before you hit the  2 million limit you should think through the next steps beyond slow Google Sheets. You’re going to want to move your data into a database archive. You can still work in a Sheets environment but offload the data storage to a dedicated database.

Google Database Options

Within the Google ecosystem, we’re talking about BigQuery and Cloud SQL, both cloud databases. However, the integration isn’t simply drag-and-drop anymore, so you’ll need development skills to connect these services.

Google BigQuery is integrated with Google Drive, so you can use your Google Sheets as tables in BigQuery. You can also use the BigQuery Apps Script service to manage your BigQuery projects.

Connecting a Google Sheet to Cloud SQL directly requires Apps Script, although you could export data from Google Sheets (e.g. as CSV) and import that into Google Cloud SQL.

Detailed instructions on the same are out of scope of this article, however you can send your inquiry using this LINK to us for more details & support on this Topic. 

20. 
CONCLUSION

I hope that you will find a lot of value in the above article. Google Sheets are only a small part of the Tech Ecosystem  that can help you to convert technological threats into an opportunity, re-invent themselves & transform their industry.

If still find yourself struggling even after doing lengthy & expensive trainings then probably you lack the Thought Process & Tools for Exponential Growth. 

STOP trying to Re-Invent the Wheel ! 

Check out our  100% Practical & Transformational Coaching that have turned around hundreds of businesses.

CLICK HERE

Remember: You need to work Hard in Business but Struggle is optional !

Resolved - Message Blocked Gmail Google Apps Script

posted Nov 4, 2018, 7:09 PM by Sanjeev Jain   [ updated Nov 10, 2018, 6:03 PM ]

Learn how to resolve the Message Blocked issue affecting new G-Suite users when trying to send an email through Google Apps Script. Please see the video below where I have explained the issue and how to resolve the same in detail.


For any further support please contact me at sjain@ceoitbox.com. Do feel free to browse this site for more amazing Tips & Article on how to use Technology to Grow your business.

We also conduct unique Tech Workshops to help  you Grow your business ! 

 https://sites.google.com/site/ceonewsletters/capture-pages/reyourrequestforthebestwindowsshortcuts/CP1.jpg https://sites.google.com/site/ceonewsletters/capture-pages/_draft_post/CP3.png
 https://sites.google.com/site/ceonewsletters/capture-pages/reyourrequestforthebestwindowsshortcuts/CP4.png https://sites.google.com/site/ceonewsletters/capture-pages/_draft_post/CP2.jpeg

Get new Gmail for G-Suite Legacy\Paid users

posted Jul 29, 2018, 7:37 PM by Sanjeev Jain   [ updated Jul 29, 2018, 7:40 PM ]

Gmail.com gets its biggest upgrade in the service's history. There's a new Confidential Mode, the ability to set expiration dates and passcodes for emails, and other advanced security features.




If you’re using a G-Suite Legacy\Paid Account, your account administrator will have to turn on the new interface for you.

Here is the hidden link you can use from admin account: Click Here




We hope you find this interesting and valuable. 

For any support please contact us on 1 800 3060 1067  | Vandana@Ceoitbox.In  | Www.Ceoitbox.Com 

             We also conduct unique Tech Workshop to help  you build Remarkable Intranets !                 

Tech Mastery 2018


These Windows shortcuts can save you min 46 hours per year

posted Jul 15, 2018, 7:28 AM by Sanjeev Jain   [ updated Jul 15, 2018, 7:29 AM ]

When it comes to using your computer, it's keyboard shortcuts that get things done the fastest. And for every version of Windows, Microsoft add a few useful new ones to the old standbys. Here are our favorites.

New Windows 10 keyboard shortcuts

The Windows key is the key on your keyboard that has the Windows logo on it. On a regular desktop keyboard, it sits between the Ctrl and Alt keys to the left of the space bar.  Pressed by itself, the Windows key opens or closes the Start menu, but when pressed in combination with other buttons, it works wonders.

Desktop and Cortana shortcuts

  • Windows key + A This key combination opens the Action Center menu at the right side of the screen. Here you can view your notifications and access commonly used settings like Wi-Fi and Bluetooth.
  • Windows key + I If you need to modify your computer’s settings, this is the key combination to press. It opens the Settings app (known in previous Windows versions as Control Panel).
  • Windows key + C Open Cortana in listening mode with this keystroke to perform a quick search using your voice. Voice search is supported in selected countries only. If voice search is disabled or turned off, you can still use text search on Cortana.
  • Windows key + S If you want to find a file or an app installed on your computer or search for a term on the Web, this is the keystroke to press.

Task View and Virtual Desktop

Windows buttonOne of Windows 10’s new useful features is the ability to create multiple virtual desktops. They let you group related applications for more efficient multitasking. For instance, you can open all work-related apps on the first desktop, your browser for personal browsing and personal apps on the second desktop and so on. You can also move open apps from one desktop to another.

  • Windows key + Ctrl + D Create a new virtual desktop with this combination. You can open new sets of applications and programs in the new virtual desktop.
  • Windows key + Ctrl + Left Switch to the next virtual desktop on the left.
  • Windows key + Ctrl + Right Switch to the next virtual desktop on the right.
  • Windows key + Tab Opens Task View, which shows you all the currently running programs on a desktop. If you’re using more than one virtual desktop, you will see them below the switcher in Task View and you can switch between desktops.
  • Windows key + Ctrl + F4 Remove a virtual desktop without terminating the apps running within that desktop. Any open application on the removed desktop will be moved to the next available virtual desktop.

Command Prompt shortcuts

The Command Prompt (or command line terminal or DOS prompt) lets you perform many functions such as moving, copying and deleting files and folders using text-based commands without having to click and use the graphical user interface.

Windows 10’s Command Prompt has new shortcuts, but to be able to use them, you need to set some options in the Options tab. Here’s how:

  1. On the Command Prompt window, right-click on the title bar and select Properties.
  2. On the Options tab, disable Use Legacy Console.
  3. Also, enable Enable Line Wrapping Selection and Extended Text Selection Keys under Text Selection.

The following Command Prompt shortcuts have been staples in text editors and word processors, but they are new to the Command Prompt.

  • Shift + Left Highlights one character to the left of the cursor.
  • Shift + Right Highlights one character to the right of the cursor.
  • Ctrl + Shift + Left Highlights a block of text to the left of the cursor.
  • Ctrl + Shift + Right Highlights a block of text to the right of the cursor.
  • Ctrl + A Highlights all text in the Command Prompt window.
  • Ctrl + C Copies the highlighted text to the Clipboard.
  • Ctrl + V Paste the Clipboard contents to the Command Prompt.

Other helpful keyboard shortcuts

Commonly used shortcuts for Windows

  • F2 Select a file, then press the F2 function key to rename it.
  • F3 Search for a file in File Explorer. If pressed in a browser window, the Find in Page function activates instead.
  • F4 Show the Address Bar in File Explorer.
  • F5 Reload or refresh the content of the current File Explorer window. If used inside a browser, this reloads the web page.
  • F6 Cycle through the different interface elements of the active window. The first time you press this key, the Display Information button will be highlighted, then the Address Bar, and so on. To execute the highlighted button, press the Enter key. If multiple items are highlighted, use the arrow keys to navigate.
  • F10 Activate the shortcut functionality in File Explorer. When pressed, an overlay appears showing letters or numbers on top of menus and buttons. Pressing a button or letter executes the corresponding command or open the corresponding menu or dialogue.
  • Ctrl + C This is the standard Copy shortcut. It copies the selected item (text, objects, files, and so on) to the clipboard.
  • Ctrl + X This is the standard shortcut for the Cut command. When the cut item is pasted elsewhere, it is removed from its original location.
  • Ctrl + V This is the standard keystroke for the Paste function. It puts the Clipboard’s content into the current window.
  • Ctrl + Z Reverse a previous action. This is also known as the Undo shortcut.
  • Alt + Tab Switch to the next open app.
  • Alt + F4 Close a running app or program.
  • Shift + Delete Delete a file permanently, bypassing the Recycle Bin.
  • Windows Key + L Lock the computer screen.
  • Windows Key + D Minimize all running programs and shows the Desktop.

Other shortcuts using the Windows Key

  • Windows Key + E Launch File Explorer, which is used to locate and manage files saved in your computer.
  • Windows Key + G Open the Game Bar so you can take a screenshot or record a video while playing a game.
  • Windows Key + H Press this combo to open the Share charm or side popup so you can quickly share files via email, social media or other apps capable of file sharing.
  • Windows Key + K Opens the Connect Quick menu to quickly connect to a wireless display or audio device.
  • Windows Key + M Minimize all open windows.
  • Windows Key + Shift + M Restore all minimized windows.
  • Windows Key + P Toggle a presentation display mode when using multiple screens. You can choose to show the display on the main screen only, duplicate the display on the second screen, use the second display as an extension or use only the second display attached to the computer.
  • Windows Key + R Open the Run dialog box where you can execute commands, open files and launch programs.
  • Windows Key + T Cycle through apps pinned on the Taskbar. When the app you want is highlighted, press Enter to launch it.
  • Windows Key + U Open the Ease of Access Center window for configuring accessibility features.
  • Windows Key + X Open the Quick Link menu to access tools such as Command Prompt, File Explorer, Settings and Task Manager.
  • Windows Key + Pause Open the System Properties window, which contains technical information about your computer. You can also find here settings that determine how your computer behaves or functions. Requires admin access.
  • Windows Key + PrtScr Take a screenshot of the display and saves it to Computer > Pictures > Screenshots.
  • Windows Key + Arrow key (up/down/left/right) Sometimes you want to see the contents of more than one window at a time. This key combination snaps the current window to the edge of the screen, depending which cursor key you press. Up to four windows can fit on one screen. For instance, to snap a window to the lower right corner, press Windows Key + Down, then press Windows Key + Right.
  • Windows Key + , (comma) Hold down this key combination to temporarily hide open apps and windows and show the Desktop. When the keys are released, the hidden apps and windows return to their original positions.

For more news & articles visit us at www.ceoitbox.com

What are Intranets and why they are Critical for your Company

posted Jul 8, 2018, 12:29 PM by Sanjeev Jain   [ updated Jul 8, 2018, 10:27 PM ]

This is a video guide, please see the video below.

Guide to Intranets


We hope you find this interesting and valuable. 

For any support please contact us on 1 800 3060 1067  | Vandana@Ceoitbox.In  | Www.Ceoitbox.Com 

             We also conduct unique Tech Workshop to help  you build Remarkable Intranets !                 

Consolidate multiple email accounts using Gmail

posted May 13, 2018, 10:22 PM by Sanjeev Jain   [ updated Sep 12, 2018, 9:24 AM ]

It is not uncommon to have several email accounts, but checking multiple accounts in different places is tedious. Hence, many people revert to a desktop email client like Thunderbird or Outlook to manage all their mail from one place. 

However, I found that Gmail can do the same and it can do it much better. Since I switched, I no longer have to worry about backing up or moving my profile, I save a bit of RAM as my browser is running anyway, I save several gigabytes of hard drive space, and no matter where I check my mail, it’s always the same familiar inbox and setup greeting me.

Besides, Gmail has an ingenious search feature, some fun and very useful Google Labs plugins, and with browser extensions you can get even more cool features. In other words, it’s bliss and I would like to share that with you.

Let Gmail become your one account to rule them all! 

STEP1: Import Email Accounts Into Gmail

The best way I recommend is that instead of importing email from other accounts, its best that you forward the email from those accounts to your main Gmail account. For more detailed instructions, Click Here

https://youtu.be/O9vOYUjf2JE



Much like a desktop client, Gmail can handle multiple email accounts. Moreover, it makes importing mail from POP3 accounts fairly easy, by automatically filling in the required POP server information for you.

Let’s say you want to import email from an old Hotmail account, what would you need to do?

  1. Open Google Mail, click the wheel icon in top right and select Settings from the drop-down menu.
  2. In your Gmail settings, go to the Accounts and Import tab.
  3. Under the header Check mail using POP3 click Add a POP3 mail account you own.
  4. Fill in your email address and click Next Step.
  5. In the next window, add your password, check the boxes that apply, and click Add Account.
  6. For more detailed instructions, Click Here

The video below illustrates how to do this easily.

https://sites.google.com/site/sjceoitbox/Articles/consolidatemultipleemailaccountsusinggmail/Screen%20Shot%202018-09-12%20at%209.02.26%20PM.png



STEP2: Configure your Main Gmail account to send email as other account

https://sites.google.com/site/sjceoitbox/Articles/consolidatemultipleemailaccountsusinggmail/Screen%20Shot%202018-09-12%20at%209.02.35%20PM.png


If you are using the paid G-Suite account and get the following error you may need to enable this functionality from your Administrator Console.


How do I allow or disallow users to use an outbound gateway?

By default, users cannot use an outbound gateway; the G Suite mail servers deliver all outgoing mail, including mail that uses alternate From addresses.

To allow users to use an outbound gateway:

  1. Sign in to the Google Admin console.
  2. From the dashboard, click Apps, then click G Suite > Gmail > User settings.
  3. In the Allow per-user outbound gateways section, select Allow users to send mail through an external SMTP server when configuring a "from" address hosted outside your email domains.

    When this check box is selected, users who associate additional email addresses with their accounts have the option to enter connection information for an alternate SMTP server. (Learn more about the required steps for the user.) When the check box is not selected, users cannot specify an SMTP server for the additional address.
     
  4. Click Save changes.

It can take up to an hour for the changes to propagate through Google systems.


Are you using these 3 amazing Google Sheet features ?

posted Feb 9, 2018, 9:16 AM by Sanjeev Jain   [ updated Sep 12, 2018, 8:41 AM by info@ceoitbox.com ]

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 NumberTextDate 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

Flower


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.

In the examples below we’ve used this handy demo sheet with some simple data: Example Spreadsheet

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 Explore.
  • 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 analysis 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 into your spreadsheet

  • To add a chart or formula from the Explore panel into your worksheet you can it onto your spreadsheet.
  • Alternatively by clicking the Insert chart icon Insert chart.

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 into 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:


https://sites.google.com/site/sjceoitbox/upcoming-trainings



Are you using heat maps in Spreadsheets?

posted Feb 9, 2018, 9:16 AM by Sanjeev Jain   [ updated Sep 12, 2018, 8:45 AM ]

1. How to turn your data set into a heat map 

The recent update to conditional formatting in Google Sheets allows you to turn any data set into a coloured heat map with just a couple of simple steps. This is great for identifying trends and patterns and cuts out the process of having to create a separate chart or graph.

A data heat map highlights each cell with a different colour depending on the figure. So you can set the colour to a light shade for low figures and a darker colour for high figures. A great example is using the heat map to identify trends in your sales report, allowing you to see what months have been the most successful.

To create your own heat map, follow the simple steps below:

  • Select your data in the Google Spreadsheet.
  • Go to Format - Conditional Formatting (or Right click). This will open a Conditional format rules panel.
  • Select the colours for your Minpoint, Midpoint and Maxpoint
  • Adjust your Midpoint to a Percentile

To easily identify your data even further, you can select your data and click add new rule. If you want to see your top figures (e.g. above £1000) select greater than in Format cells if… add your Value into the box and you can choose to have these figures in bold for example.

2. Turn on colourful rows or columns via this neat  trick

Flower


In Google Sheets the Conditional Formatting feature provides the ability to format cells if they meet certain conditions, such as if they contain a certain word or a number.

e.g. if a cell contains the string “Yes” make the background green, or “No” make the background red.

To access Conditional Formatting in a Google Sheet:

  • Click Format > Conditional formatting (A toolbar will open to the right)
  • Create a rule via Add a new rule
  • Use Apply to a range to select the range to be formatted
  • Under the Format cells if… drop down select Custom formula is (the last option)
  • In the box Value or formula use one of the following formulae:

Formatting

Formula

Apply to Even Rows

=ISEVEN(ROW())

Apply to Odd Rows

=ISODD(ROW())

Apply to Even Columns

=ISEVEN(COLUMN())

Apply to Odd Columns

=ISODD(COLUMN())

Then select the Formatting style such as a colour.

3. Say goodbye to spelling mistakes in Google Sheets

Flower


For the majority of us fast typers Spell Check plays an important role spotting our mistakes as we type.

Luckily for us Spell Check has finally been added to the new Google Sheets. Spell Check with Google Sheets behaves just the same as it does with Docs or Slides. You can choose to check your entire Sheet, individual worksheets or even columns and cells. Here’s how to spell check your Sheet:

  • Highlight the cell range or columns you wish to check
  • Click the tools tab and select Spelling
  • Google will identify any mis-spellings
  • Click change, ignore or add to dictionary

If you are working with a Sheet created in the old version of Google Sheets, Spell Check is also available, in a slightly different format. After you choose Spelling, a pop up box will open - simply click on the identified mis-spelling and choose from a list of suggested corrections.


Hope you liked the above article. We do some amazing & unique workshops that can help you Scale up & Automate business using Free Technology that is already in front of you. Find our more using links below:

https://sites.google.com/site/sjceoitbox/upcoming-trainings





















Identify and Preventing Phishing Attacks

posted Oct 31, 2017, 6:22 AM by Sanjeev Jain   [ updated Sep 12, 2018, 5:53 AM ]

When we think of hackers and cyber criminals, we tend to think of  them as masterminds wearing hoodies, shutting down websites with  their out-of-this-world coding skills (and godly swagger of course!).

Well, that’s not always the case. Using a simple yet highly impactful technique like Email Phishing, any  Tom, Dick, or Harry can fool users to commit online fraud.

You don’t need to learn a dozen coding languages to employ phishing  scams and take control of systems. A huge part of the reason that phishing is so popular is its lack of  sophistication.

The below slides explain what is Phishing & how you can protect your valuable data from such attacks.

PhishingScams 8 Helpful Tips To Keep You Safe


Click on this to download the E book

Technology for transporters

posted Aug 7, 2017, 8:00 AM by Sanjeev Jain

Was invited to speak about leveraging technology for Transporters at the GST Convention for All India Transporters Welfare Association  on 29th July 2017.

1-10 of 33