I am a huge fan of Microsoft Excel. It’s my second favorite application, after Photoshop. However, I’ve been attempting to move all of my work to Google Apps for Education and I felt like Google Sheets was just not as robust a program as Excel. That being said, I’ve been working with Sheets exclusively for several months now and am finding many things that are making me love it. Not as much as Excel, but close.
Here are my top 5 coolest things I’ve learned over the past months:
EXPLORE
Next time you open up a spreadsheet in Google Sheets, notice the little icon in the lower right corner
. If you click it you get a flyout panel called Explore. This panel gives you a fantastic overview of your data in chart form and allows you to easily add the charts to your document with one simple click. I loved this and I don’t believe there is an equivalent in Excel.
How can you use this?
If you create a form, in Google Forms, your data will be collected in a Google Sheet. The charts in Explore give you the overview you need to get started on your data analysis
You can upload any Excel spreadsheet into Google Sheets and it will convert it to Sheets format so you can get this overview on data NOT collected in Google Drive.
PIVOT TABLES
A pivot table is a tool that allows you to summarize and explore data interactively and is particularly useful for large data sets. I use them primarily to count or average things but they can be used to extract all types of date from sets. Google Sheets now allows you to easily create these pivot tables. Here’s an example of a quick table created from the data we looked at above. This is a simple pivot table but they can be more complicated depending upon your needs. Just like in Excel, they update in real time, as the data in the Sheet changes. You can find pivot tables under Data > Pivot Table
ADD AND DELETE CELLS INSTEAD OF ONLY ROWS OR COLUMNS
There are many times that I want to delete or add a few cells in a spreadsheet and have the rest of the spreadsheet shift to accommodate those cells. In Sheets you can only add or delete and entire row or column which isn’t very helpful. With the help of an Add-On called Insert and Delete Cells by Karl.kranich.org you now can. In Sheets go to Add-ons in the menu bar then choose Get add-ons In the search area, type in Insert and Delete Cells. Click on Free. A pop up window will appear so make sure pop-ups are not blocked. From that window click Allow.
To add or delete cell(s) just click on the appropriate cell(s) and choose Add-on again. You will see it in the menu a new option to allow you to shift the cell after adding or deleting.
How can you use this?
I use this feature all the time. Here’s an example: I paste or import data into a spreadsheet and for some reason, one line is offset just one cell. This happens if there is a wayward space in the paste. Now I can just select that cell and shift the rest of the columns one cell to the left to line all the data back up.
FINDING UNIQUE AND DUPLICATES
As much as I love Excel I still struggle with filtering duplicates from a dataset. This is also something that I use all the time. For instance, for our training stats, I like to see all the individuals that attended TLT training in one year. For this report, I only want each individual counted once. For this I use an add-on called Remove Duplicates by ablebits.com. Just like earlier you can get it from the Add-ons > Get add-ons menu and search for Remove Duplicates. Once it’s installed you just select the data then choose Remove Duplicates from the Add-ons menu and follow the instructions. It’s just four easy steps to locating all the unique or duplicate entries in your data and I think it’s 10x easier than the filter feature in Excel.
PREVENT PRINTING, COPYING, AND DOWNLOADING
Did you know that you can share a spreadsheet with people without giving them the ability to print it, copy it, or download it? Well you can. Just open your Sheet and click on the Share button in the upper right corner. Now click on Advance in the bottom right of the new window. Make sure your normal settings are set correctly depending upon the level of security you want. Then at the bottom click on Disable options to download, print, and copy for commenters and viewers. This actually works surprisingly well, especially with a large spreadsheet. Could some take a screenshot of the data? Sure, but the only way to stop that is to not let them see it in the first place.
How can you use this?
This came to my attention when a faculty member wanted to share a large list of internship options but didn’t want that list to be shared with those outside her class. Again, while not foolproof, it provides enough of a deterrent to meet the needs.
REMEMBER: DO NOT STORE ANY STUDENT SENSITIVE INFORMATION ON GOOGLE DRIVE/SHEETS.
I hope these tips will get you to reconsider Google Sheets as a viable alternative to Excel.
