Next-Level Data Analysis in Excel

Data analysis is at the core of every successful business, whether analyzing performance, team processes, customer behavior, or market trends.

It’s the process of collecting, analyzing, and interpreting information, using various data analysis tools ‒ from data mining to business intelligence ‒ to extract value and create compelling visualizations to support decision-making.

Data analysis tools like Excel perform exceptionally well when it comes to analyzing structured data (numbers, scores, groups, etc). However, more and more businesses are starting to see the benefits of unstructured data, since it holds many valuable insights that help you understand ‘why’ something is working or not working.

To extract insights from unstructured data, you’ll need to use advanced machine learning tools in Excel to structure your data before analyzing it.

Using intuitive no-code tools like MonkeyLearn Studio, for instance, you can sort survey data, customer reviews, and social media conversations that you’ve saved in your spreadsheets and visualize the results in no time.

Read on to find out how to perform data analysis in Excel, before moving on to a more advanced analysis of your data using out-of-the-box machine learning tools.

How to Do Data Analysis in Excel

Businesses collect both quantitative and qualitative data and often export this data to Excel, ready for analysis. Excel is one of the most popular and reliable tools for managing and analyzing business data and discovering trends in large data sets.

It’s also the easiest place to start your data analysis, so let’s take a look at some of the top Excel functions:

Filter Your Data

Filters help you narrow down a large dataset by grouping data based on different criteria. Applying filters to data allows you to easily view what's relevant to you.

In this set of product reviews, for example, data in the company size column is filtered to show only reviews from small businesses.

Gif showing how to apply filters in Excel

Functions

Excel has more than 400 functions, which are predefined formulas that help you automate calculations. Some of them are particularly useful for data analysis, for example:

  • CONCATENATE: blends two or more cells together.
  • COUNTIF: counts the number of cells that meet specific criteria.
  • AVERAGEIFS: it calculates the average of cells based on multiple criteria.

Below, the formula “COUNTIF(I2:I190,“Positive”) is used to automatically count how many opinions from a set of product reviews were classified as Positive:

Gif showing how to apply COUNTIF function in Excel

Pivot Tables

Pivot tables are one Excel’s most powerful features for data analysis. Otherwise known as cross-tabulation, pivot tables are used to summarize (or slice) data so that you can focus on specific aspects that you want to explore in more depth.

You can quickly build a pivot table by dragging and dropping fields and rearranging them without needing to write any formulas. The flexibility to blend information also makes it easy to spot trends that might otherwise go unnoticed.

Before building a pivot table, decide on the final outcome: what are you trying to understand? Maybe you want to know which product was more popular during the summer season or identify segments of customers based on purchasing behavior. With a clear question in mind, you’ll have a better idea of the data to include.

In this example, below, product reviews previously classified by topic and sentiment using MonkeyLearn (we’ll explain this later), are summarized in a pivot table show the number of Positive, Negative, and Neutral opinions for each business aspect (Ease of Use, Performance, Features, and more):

Gif showing how to create a pivot table in Excel

Pivot table in Excel showing aspect-based sentiment analysis data

In the final pivot table, above, sentiment data has been summarized in columns and topic data in rows, and Excel has automatically calculated the totals.

Conditional Formatting

Conditional formatting in Excel automatically highlights cells using colors based on predefined rules, making it easy for you to identify different data. For instance, you might want to use this rule to add a color gradient to NPS scores, to easily identify promoters, passives, and detractors.

The example below, shows how to create a conditional formatting rule in a dataset of NPS scores: “If the value is greater than 8, color the cell green”.

Gif showing how to apply conditional formatting in Excel

For more granular results, you can combine this with other features such as sparklines, which are tiny line charts that display your data from a series of values. They’re useful for analyzing and spotting trends in data related to performance, like customer support and seasonal fluctuations, or to highlight high or low values.

What-If Analysis

What-if analysis allows you to create different scenarios and easily compare results. By changing values in cells, you can see what the outcome would be.

Another useful feature of this tool is Excel’s Goal Seeker, which helps you understand how to achieve the desired goal (for example, a revenue target).

In a nutshell, these tools save you time and effort when you don’t have all the data available to answer a certain question.

Charts

Visualizing your data through charts in Excel helps you communicate ideas effectively. There are different types of charts available, such as column charts (ideal for comparative data analysis), pie charts (suitable for depicting proportional data), and linear charts (great for analyzing trends over a time period).

While the above features can help you get a lot from your data, Excel isn’t that effective in complex statistical analysis.

Luckily, there’s an Excel add-in called Analysis Toolpak designed to handle more complex data.

Analysis Toolpak

Armed with a set of powerful features, you can save time and effort when using Analysis ToolPak in Excel to perform statistical, financial, and engineering calculations. Basically, you provide the data and specific parameters and the tool automatically does the calculations for you.

With the Analysis Toolpak, it can be easier for you to detect outliers in data (by calculating variance), as well as calculating correlation and regression.

Word Clouds

You can also visualize Excel data using word clouds. You'll have to upload Excel data to word cloud tools, but they're really easy to use and are a great way to get quick insights at a glance.

Take Your Data Analysis in Excel to the Next Level

There’s no doubt that Excel is a great starting point for data analysis, especially when crunching numbers. However, there are some limitations if you want to carry out more advanced tasks, like gaining insights from unstructured text data.

Traditional data analysis software is not capable of processing strings of text to extract meaning. While you can use Excel to collect open-ended responses from your latest NPS survey, you can’t use it to automatically understand what they’re about or how customers feel. For example, do they mention your product in a positive or negative way?

Manually analyzing thousands of customer responses is not an option either, as it’s far too time-consuming. If data is time-sensitive then you risk missing out on trends and up-to-the-minute insights that could improve your business.

Fortunately, machine learning technology is allowing businesses to capture value from text data in real-time, no matter how large your dataset.

Machine learning tools are trained using examples of data, which they learn from to automatically classify text or extract specific information from text data. The more data they analyze, the more accurate they become at making predictions.

To start with, you can use a keyword extractor to gain quick insights, like the most frequent and relevant words customers use in a set of product reviews:

Test with your own text

Results

TagValue
KEYWORDelon musk
KEYWORDsecond image
KEYWORDspacesuit
KEYWORDbody look
KEYWORDnew design
KEYWORDphoto
KEYWORDspacex

Once you have a better idea of the main themes mentioned by your customers, you can build a topic classifier to sort customer opinions into categories like Ease of use, Pricing, Features, and so on. Before doing this, we recommend using an opinion unit extractor to slice text into smaller units of data. So, if reviews contain more than one topic with different sentiments attached to each of them, you’ll be able to analyze each ‘unit’ of text individually.

Here’s an example of a pre-trained topic classifier for NPS feedback:

Test with your own text

Results

TagConfidence
Customer Support61.9%

For a more granular analysis of your data, you may also want to know if customers are positive or negative about each of the topics you’ve detected. The technique that combines both topic classification and sentiment analysis is called aspect-based sentiment analysis.

Here’s how your initial dataset of product reviews might look after performing aspect-based sentiment analysis:

Example of product reviews classified by topic and sentiment

Now, you’re ready to create powerful visualizations from your data.

Flowchart showing how MonkeyLearn Studio works

To make qualitative data analysis even easier, we created MonkeyLearn Studio: an all-in-one solution for text analysis and data visualization that allows you to:

  1. Choose a template based on your data type

MonkeyLearn Studio showing templates for different data types: survey, customer feedback, Twitter data

  1. Import your data directly from Excel

How to upload Excel data in MonkeyLearn Studio

  1. Analyze your data using no-code machine learning tools ‒ you can classify data by topics, sentiment, or intent, find relevant keywords, and more.
  2. Visualize your data in real-time in a customizable dashboard, like this one below showing data from a set of Zoom reviews.

Visual analysis of product reviews created with MonkeyLearn Studio, showing graphs, word clouds, charts

Get the Most Out of Your Excel Data

Excel is a powerful tool for quantitative data analysis. However, it’s unable to unlock the power of qualitative data.

Machine learning tools take your data analysis in Excel to a new level, by allowing you to make sense of text data in a fast and cost-effective way.

With a SaaS no-code platform like MonkeyLearn, you can easily classify data by topic, sentiment, intent, or find important keywords, among other text analysis tasks. Then, you can customize interactive dashboards to showcase the results and share them with your team in real time.

Explore our demo to learn how you can take your data analysis in Excel one step further.

Tobias Geisler Mesevage

October 1st, 2020