Educator Tips for List Management in Google Sheets

www.synergyse.com

● As a teacher, you will most likely work with lists in various form so, it’s important to know how to manipulate data in Google Sheets.

● The subsequent slides will focus on:

o how to combine data - using the [CONCATENATE] formula

o how to split data - using the [SPLIT] formula

www.synergyse.com

1) Combining Data● [CONCATENATE] formula allows you

to combine data from 2 or more rows.

● In this example, to combine the first name (column A) and last name (column B), your formula (which be in C2) will be

=CONCATENATE(A2,B2)

Result: MartinShortBrendanFraser

Fig 1. - Our initial data set, available here.

www.synergyse.com

● In order to separate the first and last names with a space, you’ll need to put a space in double quotes.

● Correct formula: =CONCATENATE(A2,” “,B2)

● Result: Martin Short, Brendan Fraser

Fig 2. - Here’s the correct formula for combining or linking - concatenating - columns A and B to produce C. Bonus tip here for large data sets - by double clicking on the lower right hand corner of the cell (here, C2), the formula will repeat itself to the end of the data set. That way, you can spend more time enjoying your espresso, and less time on list making!

www.synergyse.com

● Challenge: Create an email based on these names (firstname.lastname) at the website canada.ca.

● Here’s what the formula might look like:=CONCATENATE(A2,".",B2,"@canada.ca")

● Remember to put each text value in double quotes and the formula will do the rest.

● The pop up dialog box guides you with the syntax

Fig 3. - See how simple linking data can be? No more manual work to compile data! The [CONCATENATE] formula is great for making lists and combining data. This is also essentially how a mail or label merge functions, and there are commands for line breaks, etc. here as well, but I’ll let you fiddle with those on your own.

www.synergyse.com

2) Breaking Data Apart● Using Google Sheet’s [SPLIT] formula, you can

easily break data apart into multiple columns.

● Unlike Excel, we can split the data from one column, and export to two or more columns to the right of the original column.

● The formula asks you to identify what data to split, and what to split it by - usually a space, comma, or something like that.

● In this example, we’ll take column C from our original data set, and break it using the space between first and last name as the identifier. Here’s what the formula would look like: =SPLIT(A2,” “)

www.synergyse.com

● Don’t go overboard with formulas, lean towards the simpler path when it comes to data manipulation, and focus on the practical aspects.

● If it’s worth spending time on, like getting a formula right, the payoff comes later - the next time you’ll know what to do. As an example, if you need to break out individual letters**, or you’re going bonkers from splitting large data sets, maybe there’s a better way.

● Excel has a few additional, detailed functions that can be used more easily than the counterpart in Google Spreadsheet. Of course, Microsoft Excel has been around for a few decades! However, while very powerful, Microsoft Excel lacks the simple collaboration feature that makes Google Spreadsheet so powerful, so if you don’t need those particular detailed functions, Google will be your best bet.

Tips

www.synergyse.com

Join the conversation and follow us:

www.synergyse.com