The Importance of Store Cleanliness
25 February 2021
The Best Digital Channels for NGOs - Part 2
23 February 2021
5 Tips to Boost Employee Engagement During the Pandemic
20 February 2021
12 Eco-Friendly Ways to Transform Your Decking for Summer
18 February 2021
Trending Music Hashtags To Get Your Posts Noticed
24 August 2018
Trending Fashion Hashtags To Get Your Posts Noticed
05 April 2018
Trending Wedding Hashtags To Get Your Posts Noticed
18 September 2018
Trending Beauty Hashtags To Get Your Posts Noticed
05 July 2018
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.
- Select the whole column or range you would like to
- From the menu, select Format; conditional formatting. The conditional formatting toolbox will open which looks like this:
- From the drop-down menu under "Format Cell If", select "Custom Formula is" and add the following condition:
* Where B is the column you are trying to dedup.
- Choose a formatting style so you can quickly identify duplicated cells.
- 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.
- 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:
- Visit the Chrome Webstore and install the Sort by Colour plugin for sheets.
- Once installed, visit open your sheet with data.
- Select the column you need to sort
- From the menu, select Add-ons > Sort by Color
- Select your sort method, by
fontor cell colour
- 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:
- Select your cell
- From the menu select "Data" and then "Split text to columns".
- A modal popup will appear next to the cell, from which you can choose the
deliminatoror create custom criteria for the split.
- 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
- 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.
If you have any tips to take your Google Sheets experience to the next level, simply comment below or contact us here.