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.