Making Templates in Google Sheets

I was asked to create a google sheet template that every person in a department could edit.  In speaking with the group, one of the major concerns was formatting. Different columns required different things:

• Underline the name column
• Monetary Values needed to be monetary values
• Dates needed to be MM/DD/YYYY - which not everybody does.
• Some columns were date ranges (combine start and end date).

The major issue with the spreadsheet was that the formatting was an issue.  These documents are created every 2 weeks by a group of 15 people and they are made public record.  Formatting and professional look is important.  Here are some things that I was able to do to make their life easier in creating this.

Conditional Formatting

In google sheets, if you highlight a group of cells and select underline, bold, italics, etc. it will apply that feature to current text.  What if there is no text yet?  The setting does not stay with the cell.  This is where conditional formatting comes in.

I have used this for changing the cells colors when a certain condition is met.  For this, the condition was "cell not empty."

Highlight the cells that you wish the formatting to apply and click Format > Conditional Formatting.

In the box on the right, select the green box near the bottom that says default and change to custom.

You can customize the color of the cell and font, the look of the font (Bold, Underline, Italic, Strike-through).

Not something for this example, but change the condition (Format cells if ...) to something else.  You can do some pretty cool stuff with it.

Number Settings

Highlight the group of cells that you wish to apply a standard number format.  Select the number format option [123].

For me the format was a date, MM/DD/YYYY.  The option to the right doe not quite work. So I chose "More Formats" at the bottom.  From here you can customize the date to have a leading zeroes on the month and day.  This works for me.

Now that I have applied that to the cells, the format will stick.  Type in:

• Aug 2 = 08/02/2016
• 8/2 = 08/02/2016
• August 2 = 08/02/2016

I applied the same principal to monetary values as well.

Making Date Ranges

Dates are amazing, but you cannot concatenate (combine them as text with other dates to have pretty date ranges.  Maybe not the biggest deal to some, but I am slightly AR when it comes to that stuff.

First - changing a date to text.  You can do this with the text formula (yeah, I know, go figure). If the date is in A1, then the formula would look like this =text(A1, "MM/DD/YYYY") or whatever format you would like the date to be.  Just make sure it is in the quotes.

Second - to concatenate (combine things in different cells) you use the "&" sign.



Putting it together - you can see in the picture above that I have the text formula 2 times.  After the first one you see & which means I am joining that with something else.  The quotes and dash ("-") is there because I want a dash between the 2 dates.  The second & is because I then would like to add the second date to the cell.  Here is the final product.


If I change the dates in cell A2 or B2, then the date range will change as well.

Protected Ranges

One other suggestion I have for you is to protect sheets or ranges.  If there is a range that you would like to protect, highlight the cells and find Protected sheets and ranges in the Data menu.  Protecting a range or a sheet means that it is uneditable.

Not everybody understands spreadsheets.  Usually because there is so much that you can do with them and people are afraid of them.  Save yourself the frustration and lock the cells that you would like others to leave alone.

Conclusion

I know there are so many things that you can do and I am just scratching the surface.  The conditional formatting was not the first thing that I tried to format text in the template, so I figured I would share that.

For more comprehensive educational uses of Spreadsheets in education, I would consult Alice Keeler's Blog.  For more information on Spreadsheets in general, try GCFLearnfree or Mr. Excel.

PD: What are you trying to develop?

Professional Development (PD) is a tricky beast.  If you have never taught adults, then it can be daunting.  Most adults who have not, think that it is something magically different.  As if turning a certain age means that you have changed the way that you learn.  In some cases, it means teachers are more close minded to the concept, or simply that they have a life outside of school and they want to get to it.  Regardless of how you word it, these excuses sound a lot like the things that we hear from our students.

When it comes to how to train teachers, some will claim that they just want you to tell them flat out and let them figure it out, some want to learn how you are teaching it, some want to complain about the fact that the sun rose today and but there is still a dark spot following them around (OK Peter Pan, its a shadow), and some say that you should give them a handout and let them get to work.  I could use the same words to describe most of the high school classes that I have taught.

You may think that I am cynical or that I have a negative view of teachers.  On the contrary, I find them to be the most amazing and patient group of people.  We just have a different scale of thought.  I am currently a district administrator.  I would be told I am on the dark side.  I am in charge of directing the vision of the district through educational technology.  I am trying to shift instruction in the district to be more inclusive, more differentiated, more personalized, and increase the quality of technology integration in the class.  That is my world.  To do this I need to help develop 2000 teachers and get them to experience something new.  Reason being, we tend to teach the way that we were taught.  The way that we were taught 20 and 30 years ago was not with a whole lot of technology, maker-spaces, coding, and programming.

Teachers live in another world.  Their classroom.  Need I say more, it is a huge job.  An important job.  I would say the most important job up there with police, firefighters, and others that protect us so we feel safe to learn.

I recognize the job is huge. So much so that I am like room service for amazing PD.  I bring new information to you, I model great instruction, I show how what teachers are doing connects to what others are doing in other schools.

Now you know my context.

So, if you are a PD leader or attending a PD, open your mindset and ask yourself what are you trying to develop.

Is the goal of the PD to make sure that teachers can add a link to a google doc, that they can turn something in through classroom, or share a youtube playlist?  It might be, but I hope it isn't.  That will have you banging your head against a wall all day long.  You are probably trying to cover the wrong thing in your training, and too much of it.

Is your goal to model teaching strategies by engaging them in something, perhaps a technology tool?  Cover a small thing.  Something that takes no more than 3 minutes to tell a single person.  Isn't that less productive, what if the training is 1 hour, what do I do for 57 minutes?  Don't flat out tell them. Engage teachers in a learning experience.  Have them collaborate, assess their learning throughout the collaboration, get them sharing.  Teach like an innovator who expects their students to take a small, but important thing and make something huge with it. Have teachers try out the tool.  Inspire teachers to create.  We expect our students to perform at a higher DOK level.  Model that for your teachers.  Give them the experience of doing so, some not in a content or realm they are comfortable.  Make sure they are not on their email or grading papers. Treat them like professionals that you are developing, but expect professionals.

Why?

I heard a teacher-leader ask this question the other day.  Doesn't that mean that you are going to cover less?  Less of what, I replied.  Our district vision deals with student learning, rigor, and college and career readiness. Does a teacher's use of youtube or google docs complete these goals. Maybe, it depends on how they are implemented by teachers.  I feel PD should lead to a change in teaching and I am sick of PD that tells and does not show.  There is nothing less powerful than telling somebody how to do something and contradicting it with every action taken in telling them.

As a Director of EdTech my goal is to improve student learning by engaging students in rigorous activities and content, and increasing their love of learning so they are ready to work through their college and career in something they love.  I am not in front of the kids as much any more, except for when the teachers tell me "that will not work with my kids, in my class."  Then I ask them to do a lesson study, in their class, with their kids.  Not to show them up, but to show them rather than tell them.

So you might cover fewer tools, but you have surpassed what you cover in modelling teaching practice.  If we are expecting it of our teachers, then they should expect it of us. In the end, I would like to see all PD leaders modelling the best strategies.  At your next PD, when done with the training, after you have assessed your teachers on their learning through some type of performance task, discussion, or activity ask them to reflect on how you taught them (your pedagogy, your management, and your style).  Let them know that this is more important to you than whether or not they learned the tool.  If the teachers forget how to use youtube, they can google it and watch a video on how to create and share a playlist.

As PD leaders we have other responsibilities, but the main responsibility we have is to our students.  We do not teach 5 or 6 classes per day, 5 days a week.  We teach several PDs per year and do a lot of administrative stuff in between.  Save time to create kick-ass trainings because if we expect greatness from our teachers then we should be able to deliver that ourselves.  Knowing how hard that is might put us in check the next time we want to bombard them with new tools.