First and foremost, almost every Excel formula will work in Google docs and vice versa. Google docs hasn’t re-invented many wheels, but there are some subtle differences. Some of you reading this page might hate Google docs because it can make things slightly harder at times, but it’s going to be more powerful once you learn and adapt. Note: this tutorial assumes you have experience using Microsoft Excel.
1) Removing duplicates
In Excel, there are no built-in formulas for de-duplication, it’s a case of highlighting columns, clicking a button and it’s done.
Google docs has a standard formula (function) called Unique. Using the UNIQUE formula is going to be much more powerful when you want to automate processes for single column de-duplication.
In the example below, we want to de-duplicate our data column, we use as =unique(a1:a5)
Check out the spreadsheet to see the formula work.
However, you can always create or run your own scripts to remove duplicates in any way you like. Google offers a starter tutorial to remove duplicates using Google Apps Script – don’t worry if this seems a bit daunting at first, Scripts are covered thoroughly in Introduction to GAPS section.
2) Pasting pre-formatted data
Depending on what you’re trying to paste into Excel, you’ll either end up with a text import wizard or pre-formatted text. You can match the destination formatting when pasting by clicking on the bottom right menu.
Google docs is slightly different, like most Google products, you can remove formatting when pasting by pasting with CTRL + SHIFT + V (cmd + shift + v on Mac)
3) Text to columns
Excel uses the Text to Columns feature which is triggered by highlighting a column, clicking on the Text to Columns in the top ribbon and then proceeds to ask you how you want the column split
Google Sheets uses a formula called SPLIT to do the same thing. It ends up being more powerful since you can let pre-set formulas automatically split the data using whichever delimiter you like.
In column B where we split by single split, the formula is =split(a1,”/”) where the second argument is the delimiter we want to split by. If we wanted to split both A1 & A2, we can do this by wrapping =arrayformula() around the Split() formula:
Check out the spreadsheet to see how this works.
4) Pivot tables
Google docs does things slightly differently, but the Pivot tables are just as powerful as Excel. Here’s the official tutorial: https://support.google.com/docs/answer/1272900?co=GENIE.Platform%3DDesktop&hl=en