Excel in Google Sheets like a Pro!

Google Sheets are extremely handy for sharing documentation with a team and help keep everyone up to date in real-time. However, for those of us who are used to the featured packed Microsoft Excel on our local, it might be hard or sometimes impossible to recreate simple built-in features on sheets which we have come so accustomed to using in excel.

Benefits of using Google sheets:

• The price is right (its pretty much free if you have a Google account)
• Easy Real-time Collaboration
• Cloud Storage which makes its much more portable
• Enjoy Integrating with other Google products on the web
• Better Cross-Platform Support, especially on Macs
• Revision history so you changes saved can be reverted easily

Here are a few basic tips to help you bridge the gap between Excel and Google Sheets!

6 hacks to help you complete excel features in Google sheets

• Highlight Duplicate values in Google Sheets

In Excel: To highlight duplicate values in the same column in Excel is pretty simple. The feature is built-in and all you do is select your range and choose highlight duplicates in from the conditional format menu.

In Google Sheets: This feature is not built in so you will have to do a little more work.

1. Select the whole column or range you would like to dedup.
2. From the menu, select Format; conditional formatting. The conditional formatting toolbox will open which looks like this:

4. From the drop-down menu under "Format Cell If", select "Custom Formula is" and add the following condition: 

=countif(B:B,B1)>1*

* Where B is the column you are trying to dedup.

5. Choose a formatting style so you can quickly identify duplicated cells.
6. Select Done and you good to go!

If this solution is too complex, try the plugin in recommendation 2.

• Quickly Remove Duplicates and compare duplicate data

In Excel: A quick selection to remove duplicates from a column is available from the data menu.

In Google Sheets: This is not a standard feature in Google Sheets, but a free plugin is available that will help you quickly identify and remove duplicates. THis plugin gives you 6 ways to deal with the data, Highlight, remove, copy or move them to another Google Spreadsheet, or clear duplicate entries leaving the cells intact. A "Compare columns or sheets" functionality is also available with this add-on, which allows you to quickly scan two Google Sheets columns for the same values. It will take you five simple steps to compare lists and mark the results with colour or status, copy or move them, clear repeating cells, or remove duplicate rows from your main table.

Install Remove duplicates plugin here.  

• Sort Cells By colour

In Excel: This feature is standard and easy to implement, highlight the column, right click and filter by colour! This is a feature I use all the time to categorise data.

In Google Sheets: This feature does not exist with the standard product which sometimes makes it a nightmare to find colour codes in big data sheets. However, not to fret. Some clever bean who calls him/herself "Geek in the Heart of Texas" developed a nifty Add-on for Google sheets which allows you to sort by both cell or font colour. Heres how to implement:

1. Visit the Chrome Webstore and install the Sort by Colour plugin for sheets.
2. Once installed, visit open your sheet with data.
3. Select the column you need to sort
4. From the menu, select Add-ons > Sort by Color
5. Select your sort method, by font or cell colour
6. Watch the magic happen!

• Split Text into Columns

This is one of the few features that is built-into both sheets and excel, yet Sheet users serach for this all the time (hence I have chosen to add it).

In Excel: Select cell, from the data menu and choose text to columns and choose your desired format.

In Google Sheets:

1. Select your cell
2. From the menu select "Data" and then "Split text to columns".
3. A modal popup will appear next to the cell, from which you can choose the deliminator or create custom criteria for the split.
4. Select your criteria and you are done.

• Find Fuzzy Matches

This helpful feature that allows you to scans your sheet for typos and lets you correct them in seconds. However, this is not a standard feature in Excel either but is available as an add-on for Excel 2010. Of course, sheets has its own add as well! This add-on is a great assistant for correcting all fuzzy matches and removing partial duplicates from your sheet. 3 settings are available which allows you to:

• Use three simple settings to narrow your search results
• Get all typos grouped by record
• Pick or enter the correct values and apply changes

Besides comparing entire cells, you can look at every word within the cells separately. Set the number of different characters your values may have and exclude short words from the search. Once you get your results, export them to a new sheet or work with them right in the pane. Tick off the right value to change all similar records in the group; you can select any misspelling to see where it is in the sheet if you have any doubts. If all entries are inaccurate, you can type in the valid one.

In Excel: For only $19.95 you can download the Fuzzy Duplicate Finder for Microsoft Excel from Albeits.com.

In Google Sheets: A FREE add-on is available for Google Sheets! Download the Find Fuzzy Matches add-on from chrome webstore. andnbsp;

• Merge adjacent cells without losing data

The default merge function in Excel, allows you to merge cells together but does not preserve all the data in each cell. Usually, it will only keep the data in the first cell selected, the rest of the cells lose their data.

In Excel: this is not a feature available but can be done using a formula, although the cell merge doesn't occur but ensures you can merge the data of the cells. Merging data can be done with a quick concatenate formula but cells will not be merged.

In Google Sheets: A FREE add-on is available that allows you to combine columns, rows, or ranges of data without losing any of your data. Three simple settings let you decide if you want to combine multiple columns or rows, pick the delimiter for the records, and choose a place for the results.

Download the Merge Values add-on from chrome webstore

Contact us

If you have any tips to take your Google Sheets experience to the next level, simply comment below or contact us here.

You Might also find these Useful

• Excel Formulas Every Digital Marketer Should Know

Tags: Data Analysis, excel, Google sheets, How to