4 Steps for Quick and Easy Data Analysis with Google Sheets

The term data analysis is dreadful for most of us. We equate it with humongous data sets, complex tools, difficult to decipher charts etc. However, it is one skill that we should look to master if we want to succeed in our work.

In a 2009 interview Hal Varian, Google’s chief economist stated:

[pullquote align=”normal”]But I do think those skills—of being able to access, understand, and communicate the insights you get from data analysis—are going to be extremely important. Managers need to be able to access and understand the data themselves. [/pullquote]

The only change I will make to that statement – Everyone needs to be able to understand the data. Whether you want to climb up the corporate ladder or want repeat business, you must start speaking data.

Imagine a review meeting like this:

Client/Boss – Give us a brief of your work in last three months.

Freelancer/Employee – Of the 2 projects I have been working on:

Project 1 is 70% complete and we will be able to meet the closure deadline. We have managed to save 20% of the budget allocated to it. Project 2 is 75% complete in half the cost estimated for it. Also, the time saved will help us go live 2 weeks before schedule.

When it comes to measuring value, numbers always help. The objectivity provided by numbers is a good foundation for strong strategy, effective execution, and quantified results.

I am not saying you need to transform yourself into an analytics ninja. I am only suggesting to start quantifying your work and begin presenting it that way. If you talk about your work in numbers, it just shows that you are deeply involved in what you do.

Now, if you are convinced (even a little), let’s look into the tool and methodology to start with analysis.

A simple tool like Google Sheets can help you with analysis. Google Sheets is free, available on Cloud and can be easily shared with multiple teams.

If you are a newbie, following steps will give you an idea on how to start with analysis using Google Sheets:

  1. Gather data
  2. Identify relevant data
  3. Analyse data
  4. Create template for reports

Gather data

Whether you are a freelancer or a full-time employee, you are most likely to download data from a tool.

In the majority of the cases, you don’t need the entire set provided. Not many tools give you the flexibility to download only a part of data either. You can choose to download 3000 records but not few fields like name, date of joining, the address of employees.

IMPORTDATA function in Google Sheets will help you achieve this without wasting time on permutations and combinations to be used in the tool or downloading the file and then copying pasting the data.

For more information on IMPORTDATA refer to this tutorial.

Identify relevant data

Once LinkedIn sent me an email – “Here is something you might not know, you were XXX’s first connection on LinkedIn”. That email left me perplexed, what should I do with this information? I was the first connection for my best friend on LinkedIn, how does that information help?

Have you ever looked at your itemized cellular phone bill and wondered wow that’s a lot of data. The problem with data is that we have it in plenty. So, as important presenting insights is, as important is the data you use for those insights. A territory sales manager should be more focused on sales of his region that someone from other zip-code.

This is where FILTER function in Google sheets comes in handy.

FILTER function allows to instantly filter the source data and gives the result. What makes is so powerful is that with a simple one-line formula you can filter an entire dataset without bothering to use any other feature and it also updates automatically.

Check this post for more on FILTER function.

Analyze data

Wondering how to show your boss the number of times you have completed the work before the deadline. Also, you want to highlight that one particular case where you exceeded the accepted benchmark in project delivery.

There are two ways to do that, one inefficient way is to calculate the number of days, filter and color code. Other, productive way is to use Conditional Formatting function. Conditional Formatting is used to highlight/format cells based on the values in it.

Heat Map in Google Sheets - Example

If there is a contest of most useful functions in Google Sheets, VLookup will definitely make it to my top ten. Before discovering this gem of a function, I used to try multiple filter functions (I was young and unlearned about spreadsheets!) to arrive at results.

Let’s say you have a list of your team members which you downloaded from employee database using IMPORTDATA function.  Now, you want to find out who all live on Spring Street, so they can pool an Uber and go back home after the office party.  Using VLookup on your data, you will input search_key as “Spring Street”, specify other attributes and get the list.

Create template for reports

One challenge we often face while looking at the data is the difference in terminology. The total deal value is called revenue in our team discussions. The data export from CRM names it as deal size. If you are using values from this column to calculate monthly revenue, changing name for every update is time-consuming.

Named Ranges in Google Sheets can be used to define a cell or a range of cells and give it a name. Now, instead of using the cell references, you can use the name of the named range in calculations. Even when you make changes to a named range, all the formulas will be updated automatically.

ranges google sheets

Knowing your job in detail never hurts. If you can present the impact in objective numbers, it only goes to build your credibility as a thorough professional.

Found this article useful? Let us know in the comments.

Other Google Sheets tutorials you may like:

Spreadsheet Expert at Productivity Spot | Website | + posts

Google Sheets and Microsoft Excel Expert.