Google Sheets Tips and Tricks

Organization is a beautiful thing. It allows us to observe trends that are initially intangible in order to make decisions that shape our day-to-day processes in business. We see the troughs and crests in the waves of AOR client requests, interpret the workload of an over-stressed team, complete a post-mortem to determine how our time was used, and so much more.

But before we can witness these revelations, we must organize. Raw, improperly organized data can cripple as much as good data can empower. So how do we organize? In the wide world of web applications, Google Sheets comes through as a very powerful tool in organizing that monstrous spreadsheet you just exported from your project management software. 

As an example, I'd like to walk you through the setup of a frequently requested hours report that we generate here at Mission. 

Some things we'll learn:
1. Basic document setup
2. Organizing your data through: 
    ARRAYFORMULA
    VLOOKUP
    MONTH
    Pivot Tables
I've set up a Google Sheet for you to follow along. You can view that here. 

Basic Document Setup
You've exported your data that you'd like to organize, so what's next? As a general rule, there are several sheets in my workbook that I like to set up in preparation for organizing any data. These sheets can be grouped into two types: referencing and working. Below is a list of the sheets we'll be using in our example. 

Reference Sheets
1. datasource_ref:  It is a good idea to keep an unmolested copy of the raw data that has been exported. You are preemptively saving yourself from accidentally corrupting the data with an errant keystroke, or deleting something you believe is not needed...only to find out an hour later that it would have been useful.
2. vlookup_month_ref:  If I intend to use the VLOOKUP function in my workbook, then I will create a sheet with the required data beforehand (or pull it from previously created workbooks). In the instance of our example, you will be using this function to generate secondary keys.
3. vlookup_team_ref:  This is similar to the above, except the key type here is the team an employee is a part of, as opposed to a month of the year.
4. organized:  There is usually only one organized sheet in my workbook. If you need to alter any data within the datasource_ref, this is where you would do that. In our example worksheet, this is where you translate the duration value of hours worked to decimal, and add the secondary keys of team and month, so that the original data can be organized by these newly associated values. For the purpose of this exercise, I've created several organized sheets so we can work through the steps! 

Working Sheets
The working sheets can be the end game, and for our purposes, they are. This is the organized data that we have been searching for. These are the numbers we send up the chain to show what someone has been working on and for how long. The only reason you may want to push beyond working sheets is to polish the data and make it more visually appealing with pretty colors.

Organizing Your Data
Your raw data has been placed in your datasource_ref sheet and you’ve created your blank organized reference sheet...so what is next? First, you will want to identify what columns of data you’d like to include from your datasource_ref. It’s my general rule of thumb to export every piece of data for the datasource_ref, and then select choice columns for the purpose at hand.

In this instance, we will be needing every column and every line. Since I know that I will want to insert separate columns for the secondary keys, I want to pull each column individually. The best way I currently know to execute this is to use our first function, ARRAYFORMULA.

ARRAYFORMULA
Enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays.

In layman’s terms, it allows you to execute a function that is normally used on a cell-by-cell basis across multiple cells. However, initially we will use it to transpose the data from datasource_ref sheet to our organized sheet.

You can view the example in organized_1, A1. I want to pull all values from column A (Employee) in datasource_ref. Therefore, I use [=arrayformula(datasource_ref!A:A)]. Since I want all of the data from datasource_ref, I duplicate this function across the next three columns to also pull over columns B:D, Project, Date, and Hours.

Since I want everything transposed from datasource_ref, you may ask why I didn’t:
1. Use [=arrayformula(datasource_ref!A:D)], which would pull everything at once with one formula
OR
2. Use the FILTER or QUERY functions.
Well, to the first point, I know I want to add secondary key columns between the original columns from datasource_ref. If I used the formula in A and then tried to insert columns between, it would break! To the second point, we would encounter the same issue. Also, those functions are designed to sift through data and pull specific entries based on specific content within the search. Additionally, they are a bit more complicated than what we need, and simpler is always better...when it works.

Before we start adding secondary keys, let’s take a look at our data. It looks like our hours format is duration. Wouldn’t it be so much easier to work with it in decimal form? Let’s alter our ARRAYFORMULA in D1 to take care of that. Move to organized_2 to see the result!

First off, you will notice that the ARRAYFORMULA in D1 is now restricted to only pull datasource_ref!D1. We cannot multiply a word, so that would end in an error, which we don’t want. So we add another ARRAYFORMULA to D2 [=arrayformula(datasource_ref!D2:D*24)]. This is where we see ARRAYFORMULA truly come into its own. We are telling it to pull data from the datasource_ref and multiply each value by 24 (24 just happens to be the number you multiply a duration by to translate it to decimal.) Then, it is simple as formatting that column to display as a number rather than a decimal, and that’s it.

Next, let’s add our first secondary key. While I have the employee listed in A1, I want to be able to organize data based on department. In order to do this, we will be using our next function, VLOOKUP, in combination with ARRAYFORMULA.

We don’t want a numeric value, which is why we’ll be combining it with VLOOKUP, but for now, let’s get the basics set up. We’ll start by adding a new column to the left of E, and give it a header of Month. Then, we’ll enter the start of our formula in E2 with [=ARRAYFORMULA(MONTH(D2:D))]. We have a pretty solid understanding at this point of ARRAYFORMULA; it’s now applying MONTH to every cell in D2:D and returning that value in E2:E.

So how do we translate that numeric value to the full name of the month? VLOOKUP sounds good! Enter our second vlookup_ref, vlookup_month_ref. Take a look. Here, we’re associating the numeric value with the name of the month. How do we apply it to the formula we’ve already created? That would look something like this [=ARRAYFORMULA(VLOOKUP(MONTH(D2:D),vlookup_month_ref!A1:B12,2,False))]. In this formula, we’re telling VLOOKUP to look at the numeric value generated by MONTH and search against the table in vlookup_month_ref, all at once. Voila! You can see the final result on the organized_complete sheet.

And now, we get to our working sheets. All of your data is compiled and ready to be organized. How? Through the wonders that are Pivot Tables.

PIVOT TABLES
Are used to narrow down a large data set or see relationships between data points.