Creating Better Google Drive Spreadsheets

If you use Google Drive Spreadsheets and work with a lot of data, or just need a better way to quickly look at the data the following tutorial is for you. Even if you are unsure if you want to use Google Drive the follow tutorial will give you an idea of some of the tools Google Drive provides in its spreadsheet editor. If you don’t wish to read through all the steps you can view the video on the bottom of the screen that will go over all of the features below and how to do them.

Creating Header Rows

Header Rows allow you to not forget which column is to what data. The header’s will always be visible even when you scroll past those top rows. A header can be set to as many rows as you want, but most of the time I set it to 1-3 rows. Other than headers you can have the first few columns always stay there when you scroll horizontal.

1. Identify on the first cell, before A1, two grey guides with white diagonal lines.
2. Grab it, turning the mouse cursor to a hand, and move it to the bottom of the rows or columns you want to always be visible.

And that is all to it. You can always see where the guides are by looking for that same grey bar with diagonal white lines. Also under that row or next to the column you will see a large grey line across the whole document. You can also always reset them to not have headers by moving them back to the original position above A1.

Colored Header Rows

Having your header a different color is a good idea to make it stand out from the data on the page. I usually make the header a darker color with white text.

1. Select your header rows by clicking and dragging down on all of the Row Numbers you want in your header (selecting the full row).
2. On the tool bar, find the “Fill” tool which looks like a bucket and pick the new background color you want. I usually go with the 3rd darkest in the presets.
3. Now before deselecting the rows, change the text color by finding the “A” icon for “Text Color”. At the time of writing this, it is one icon to the left of the Fill bucket.

Large Cells Across Full Document

Having larger cells in height and width is nice. By default the cell heights seem a little small so I usually increase them all.

1. Select all the cells by clicking on the top left cell that is before A1.
2. Under a row number (1..2..3..), get to the cursor that has the Vertical re-size, and drag it up or down to the height you want all the cells to be. You can of course select a single row and resize that row independently.
3. On the column letters (A…B…C…), move your cursor to the end of a column to get the horizontal re-sizing cursor and then drag it to the width you want. You can also re-size columns independently if you require that.

Drop Down Lists / Data Validation

Data validation makes any amount of cells, columns, or rows require specific data. And doing so also creates a drop down for those cells to easily select the data. This is great for known values that will always be there, such as Project Type, Priority, or Category in this case.

1. Select the cells you wish to have the validation on. I usually do columns. So I will select column B (to select them all).
2. Right click on the cells and click “Data Validation” to bring up the window to set the proper values.
3. You can now verify the Cell range and alter it if needed, and setup the Criteria. It can either be from an existing Sheet you have with values, such as Client Names, a list of items you specify separated by commas, a range of numbers either between, not between, less than, greater than, etc, text values such as Valid URL, Contains word, etc, and then the last is if valid date. My favorite is “List from a range” which allows me to have multiple sheets of data with Client Names, Projects, etc and use those quickly and they get updated automatically. You can then select what to do if the data is invalid, such as Reject Input, preventing it from being entered, or just show a warning on the top right of the cell.
4. Now save the validation and on those selected cells you can double click to bring up the drop down menu, or click the arrow to the right of the cell. Also if you start typing it will show you the remaining possible values that have those letters you already entered.

Removing Data Validation

At times you may of messed up the validation want to remove it for good. Good thing Google made this easy.

1. Select the cells that you don’t want to have validation any more. It can be a full column or a single cell. Right click on it and click “Data Validation”.
2. On the bottom next to “Save” you can click “Remove validation” and you will be finished. It does sometimes take a few minutes to remove the validation so be patient. The window will disappear when done.

Conditional Formatting

Having cells change their appearance based on the value is a great way to easily see what is required. Making due dates turn red when they are late, having the progress change colors to identify where you are at, and more.

1. Select the cells you wish to add Conditional Formatting to. Usually it is a full column or row. Right click and go to “Conditional Formatting” to bring up the Conditional Formatting window.
2. Pick the way you want to identify cells. Such as “Contains Text”, “Date is Before”, “Date is After”, “Text Exactly”, and a lot of other options. In the next field or drop down you can specify the identifier. Such as the text to match or the date to match.
3. Pick the color of text text and background to change. If you don’t want one of those to change, just uncheck the value and the current cell background or text color will be used.
4. You can add up to 10 rules by clicking “Add New Rule” and when finished click “Save” to close out of the window and have you rules set to the spreadsheet.

Removing Conditional Formatting

To remove conditional formatting for one or multiple cells is simple. This is often useful to remove conditional formatting from the header and keeping the formatting active on the rest of the column’s cells.

1. Select the cells you wish to remove formatting from. You can to select all the cells you want to remove formatting from. Selecting a single cell will not remove formatting from all of the cells having that conditional formatting.
2. Right Click on the cells and go to “Conditional Formatting”.
3. Next to each rule, click the “x” to the right of the rule to delete it. You have to do this to every rule.

Merge Cells

Merging cells allows you to have multiple cells, columns, or rows combined. This is often used for custom Spreadsheet formatting such as adding logos, addresses, and other data that would often take up too much space for a single cell causing all the other cells to be too large.

1. Highlight and select the rows you want to merge. For Logos I often select a 3x2 section or a 2x2 section.
2. On the top toolbar navigate to “Format” > “Merge Cells”
3. Here you have a few options, but you will most likely want to “Merge All”. Merge All will merge all of the selected cells. You can of course merge only vertically or horizontally, and also unmerge.

Clear All Formatting

At times you will want to keep all of your data, but remove all of the formatting you added. The fastest way to do this is with the “Clear Formatting” tool.

1. Select all the cells you wish to clear formatting. For the full document select the cell before A1.
2. Go to “Format” and then “Clear Formatting”.