7 steps to analyze data in Excel or
Google Sheets

ANKUSH CHOPRA

Are you looking for incisive insights from data, but do not know where to begin your analysis?

Are there too many thoughts in your mind and you are feeling overwhelmed by the complexity and enormity of the task ahead?

Discover a simple, yet powerful framework for analysis of data and extracting meaningful insights.

A picture of steps with an orange circular background

7 steps to analyze data in Excel or Google Sheets

ANKUSH CHOPRA

Are you looking for incisive insights from data, but do not know where to begin your analysis?

Are there too many thoughts in your mind and you are feeling overwhelmed by the complexity and enormity of the task ahead?

Discover a simple, yet powerful framework for analysis of data and extracting meaningful insights.

A picture of steps with an orange circular background

How to analyze data and write a report using Excel or Google Sheets

Excel and Google Sheets are powerful data analysis tools used to make sense of large amounts of data. The quality of analysis and insights that you can generate from the data can really set you apart from the rest.

This article will explain how to use Excel or Google Sheets for data analysis and report writing. When you have data in front of you, it could be challenging to begin analyzing the data. Even if you are familiar with tools and formulas available in Excel or Google Sheets, you could still find it challenging to make a start.

This article will share a simple framework for analyzing data in Excel or Google Sheets. This framework will be helpful in most scenarios as it is.

Girl in front of a computer and wondering

Step 1: Create your hypothesis list

A man writing in a notebook

Often the most challenging part of any data analysis is agreeing on what to look for in any data set. To overcome this, the first step that we recommend is creating a set of statements or hypotheses that you would like to test. To arrive at these, you should imagine yourself making a presentation to your audience about the data. Imagine the kind of statements you would want to make to them if you presented a report after analyzing the data.

As you make these statements to the imaginary audience, you should note them down. These are the statements that you should validate from your data. If the data supports them, you can make these statements with confidence. If it does not, you will need to find other insights from the data.

Step 2: Create Pivot Tables for a quick summary

Once you have listed your hypotheses, start validating them. The simplest place to start is through Pivot Tables (See our course, Data Analysis Using Pivot Tables).

Pivot tables help in quickly arranging and re-arranging data in just a few clicks. You can validate a large number of your hypotheses quickly. You can also come across some insights you weren't seeking as part of your hypotheses.

Pivot Tables allow you to add multiple levels of data, thus throwing up insights that would otherwise require you to apply complex formulas or would require you to spend considerable time forming those combinations.

Step 3: Create Charts to find insights hidden below the surface

Charts is an under-utilized utility of Excel for data analysis. Most people use charts to represent data. However, Charts & Dashboards (See our Course, Charts & Dashboards in Excel) are excellent ways of analyzing data. Human beings process visual information much faster than raw data. Charts can easily show you trends, outliers, and where the bulk of data lies. You do not have to apply formulas to get this information; you get it instantly through charts.

Charts give deeper insights if you plot multiple related data on the same chart. The data shown below is for a call centre-based helpdesk. The data contains information on:

  1. How long does the call centre take to answer a call?
  2. How long does it take to resolve the query once the call centre responds?
Wait time, average number of callers and average call time shown for Alpha Call Centre
Wait time, average number of callers and average call time shown for Alpha Call Centre

It isn't easy to get any insight if you look at the data. However, a crucial insight is immediately visible if you plot the data together on a chart.

Looking at the circled area, we can see that the callers waiting for the longest also face a longer query resolution time. This insight can help the organization configure its caller allocation system so that the callers waiting beyond a threshold always get connected to the top-performing agents to resolve their queries faster.

A chart showing that caller of Alpha Call Centre who take time for their calls to be answered, also take time for their calls to be resolved

Step 4: Start creating a list of insights

As you analyze data and reach this stage, you will start getting valuable insights. You should write down these insights and categorize them into three categories:

  1. Validation insight: Insights that validate one of the hypotheses that you wrote down before starting the analysis
  2. A new insight: These are insights that you weren't really looking for but are helpful to you
  3. Contradictions: These are insights that are in contradiction with your overall goals

The validation and new insights are welcome and support your goals; however, you need to process the contradictions carefully. For each contradiction you should check the following:

  1. Is this true only for a particular combination of data? If yes, you can isolate the behaviour from the overall analysis.
  2. If the contradiction is core to your thought process, you need to dig deeper. You could start by validating your assumptions. You may also like to validate the authenticity of the source of data.

Step 5: Revisit your hypotheses list

Based on the kind of results you receive till step 4, you may need to revisit your hypotheses. You could add more areas to test or alter the goals based on the data obtained from the quick analysis.

Pivot tables and charts allow you to analyze data quickly. These are tools for quick analysis. After this comes the phase of deep data analysis.

Step 6: Write formulas to get deeper insights

Men working on Computer
The next step is writing formulas for data that you could not analyze substantially through pivot tables and graphs.

Many people begin from this step when you ask them how to analyze data using Excel. To start your data analysis journey from this step is complicated and overwhelming. The thought of applying so many formulas to a large set of data can be daunting. It is also not the most efficient way to analyze data.

However, it is very effective when you use this step to dig deeper insights after the initial analysis.

It is simpler and more productive when you apply formulas to only a limited set of data and have absolute clarity in your objective.

Step 7: Validate your insights through additional analysis

Once you have the insights, you should substantiate them with additional analysis. You could use statistical analysis if you are looking at trends or predictive analysis. You could also consider analyzing the scenarios where you would expect an opposite response. If your data can confirm that, then it strengthens your analysis further.

Additional analysis lends more credibility to your data and helps you eliminate any surprises that may pull down your analysis's credibility.

If you are keen to learn different methods and techniques to analyze data, you could learn these techniques through our Excel Masterclass course. This course helps you harness the power of Excel 365 and make your data analysis faster and more efficient.

Next steps: Evaluate the scope for automation

The above section answers the question of how to analyze data using Excel. This section will look at what to do once the data analysis is complete. The next step you should consider is creating a report that captures the essence of the analysis.

Usually, people select the option of creating reports in PowerPoint. However, you can design similar style reports using Excel as well. Once you have designed the report, you could consider automating it if you need to generate this report regularly or if you need to create this report for multiple people, months, objects, or any other identified. You can find more details about automation in our article, 5 Steps to Create Automated Reports using Excel or Google Sheets.

Alternatively, you can know more by checking the details for our course Magic Reports. In this course, we work with a minimal set of data, create a beautiful report, carry out data analysis, and then automate the entire report creation process. This course is a one-of-a-kind course, and you can join students worldwide in learning this unique concept, level up your data analysis, and save hours generating reports. We encourage you to watch the free preview videos to get more clarity.

Let us know if you implement this method and get success with it. Write to us at info@qling.net and we may even feature your story on our website.

Course Card Image for Magic Reports

LinkedIn Logo
Facebook Logo
Instagram Logo
Pinterest Logo