Sheets Tips & Tricks
Six Things From Excel That You Didn’t Know You Could Do In Google Sheets
Over the years, Sheets has done a lot of catching up and is now a valid alternative to using Excel, with much of the same functionality and more intuitive security and collaboration features. Mastering these skills and feeling comfortable navigating and manipulating sheets can drastically improve the overall efficiency of your business and your workday. Read Now.
How To Create A Pivot Table In Google Sheets
Pivot tables help summarize and analyze large datasets by displaying the information in more digestible formats. They make it much easier to take out small pieces of data and conclusions from a large set of information. Read Now.
Five Little Known Google Sheets Functions I Use Everyday
Spreadsheet functions aren’t just for accountants and financial analysts. We don’t often find ourselves calculating annual revenue growth or building discounted cash flow models, but we can use spreadsheet functions often throughout the day. The functions here use are what can be called “everyday formulas”, because they are quick ways to solve problems that we face on a daily basis. Read Now.
How to Share Individual Tabs In Google Sheets
Sometimes you want to share one tab of a spreadsheet while keeping the other tabs private. Protecting the cells prevents collaborators from editing particular tabs but they can still view the contents, so it is not a valid solution. The ImportRange function in Google Sheets allows you to create a dynamic copy of particular tables in a spreadsheet that you can share through another spreadsheet without worrying about collaborators viewing information in the other tabs. Read now or watch a short video demonstration.
Building a Table of Contents in Google Sheets
Wouldn’t it be cool if you could create a table of contents that would link you to the specific tab you needed info from? Tables of contents are usually associated with books or large documents but they can also be implemented in Sheets to make finding info much quicker. Although this is not a Google feature, with this workaround you can add a table of contents to all your multi-page spreadsheets. Read Now.
How to do text-to-columns in Google Spreadsheets
Pasting in Google Sheets can become especially difficult when you want to paste a list or text that you want to separate into different cells. The former solution to separating text was with a script, but Google lets you can easily convert text to columns in Sheets with new paste formatting options. Read Now.
Remove Duplicates from your Sheet
The "Unique" function allows you to remove duplicate items from a single row, or several rows. This is a great tool for de-duplicating a list of contacts, email addresses, and many other types of lists. The rows returned by this function are according to the order in which they first appear in the source array.
1.) Select the cell where you would like the de-duplicated info to begin
2.) Enter the function =UNIQUE(
3.) Select the cells you would like to pull data from
4.) Close the parentheses. Your function should look something like this:
How to Rotate Text in Google Sheets
Fitting your text in your cells is a common challenge in a spreadsheet. There are easy fixes like adjusting the size of your columns or rows, but sometimes those adjustments can alter your spreadsheet view and the best option is to make you text fit is to rotate it. Read Now.
Automatically Alternate Row (or Column) Colors in Google Sheets
Applying some color to alternating rows in a spreadsheet can make it easier to read. Instead of manually changing background color of columns or rows, here's how to use conditional formatting to automatically create a colorful and attractive spreadsheet in Google Sheets. Read Now.
How to Split Text into Columns in Google Sheets
Instead of wasting time with tedious copying and pasting to separate a list of names into first and last names in two columns, here's a quick, easy way. Read Now.
Insert Images into Google Spreadsheet Cells
Rather than inserting images on top of a spreadsheet, you can actually insert them inside a cell. Here's how to do it. Read Now.
Create Dynamic Dropdown Menus in Google Sheet
When a collaborator submits a new entry, using Data Validation, make that value an option on all the drop down menus in the Sheet, without any manual updating. Also, by using the UNIQUE function, we can create dropdown menus that are automatically updated based on users are entering. That way we can choose items based on previous entries or add a new item that will automatically update our list. Read Now.
5 Tricks to Boost your Productivity in Google Sheets
Watch this brief video (less than 6 minutes) which quickly covers 5 categories of great tips to help you work on your Google Sheet way more effectively:
- Pop up a list of keyboard shortcuts and then how to use the most helpful ones
- Make quick calculations
- Use formula tricks
- Enable email notifications when your Google Sheet is updated by others
- Using named ranges