How to Use Important Functions of Data Analytics in Excel and Google Sheets

If you’re an aspiring data analyst, Excel is still the most essential tool out there. It’s very important to learn different functions if you want to do data analytics in excel. But, it’s not always as easy as it may seem. There are more than 500 functions in excel. This big collection of functions help you work with big data set and do analytics. Moreover learning these functions will also help you do similar tasks using Google Sheets, which is quite similar to Excel in terms of features and formulas.

However, learning all these functions is not an easy task to do, especially if you are someone who is new in data analytics. So we filtered out some of the most important functions that will help you do data analytics in Excel.

Importance of Data Analytics

Today data is the most important asset for any business. The more data you have, the more actionable insight you can get.

In today’s world data analysis has many applications for all sorts of business. Be it an offline business or an online business, data analysis shows you how you can make intelligent data-driven decisions reducing the margin of error. Data analytics can also help stay ahead of your competition data analysis by giving you an edge over others, in terms of customer insights.

Apart from these data analysis can also help you with –

• Determining the target customers.
• Bring innovation.
• Reducing business costs.
• Help make effective business plans.

Excel & Data Analytics – Why & How Can You Do it?

Excel or spreadsheet is one of the most influential tools ever developed by Microsoft. It has varied applications and data analysis is one of the most important ones from those.

Excel has a huge collection of formulas that make it very easy to analyze, filter, sort, or make reports from a large quantity of data. Moreover, it is very easy to learn and start working with. You don’t even need to learn all the formulas that excel offers, in order to start with data analytics in excel. Starting with only a few most used formulas can get you through the initial learning stage and give you the most sought after information.

So it’s quite obvious why you should start data analytics in excel. It is quick to learn, easy to understand, and very affordable as well.

Excel vs Google Sheets: Things to Consider

When writing an article about Excel and its importance in data analysis, it is almost impossible to do it without mentioning Google Sheets. Though Excel was built long before Google Sheets, these two are now almost comparable in terms of features and popularity. So what are the major difference between Excel and Google sheets? Let’s get to know.

• Functions: Excel has an edge over Google Sheets in terms of the number of functions it has. Large number of formulas make Excel the popular choice till now, but it can change in the recent future as Google is adding new functions aggressively over the last years.
• Price: Microsoft Office 365 costs $8.25 per month. While you can use Google Sheets for free. However, there is a business subscription that costs $5 per month.
• Data Analysis & Visualization: If you are planning to mostly perform data analytics, Excel is surely a superior option given its large array of functions as well as built-in charts and visualization options. Google Sheets, on the other hand, has fewer number of formulas and only manual visualization capability.
• Ease of Use: Ease of use is where Google Sheets dominate Excel. It offers simultaneous edit from multiple people, while Excel doesn’t have that luxury. This is because Excel is an offline software where Google Sheets is a cloud-based one.

Now that you know the differences between this two spreadsheet software, it is clear that as of now Excel is only a slight bit ahead. But in because of the complete package and ease of use, more and more people are choosing Google Sheets as their primary software.

Most Important Excel Functions for Data Analysis

Now that we know the way we should use excel for data analysis, let’s learn the most important functions that are a must for data analysts. If you are a beginner, these are the formulas that you will be working with the most.

1. CONCATENATE

We’re starting with one of the most important functions, CONCATENATE. This handy function lets you combine the data of multiple cells into one single cell. Once you start your journey as a professional data analyst, you’ll realize the tur importance of this function.

Also, below is an example of how it works.

Formula: =CONCATENATE(cell1, cell2)

2. LEN

LEN is another function that is highly used in data analysis. We often need to calculate the number of characters on a cell. For example, if you analysis a product dataset for an eCommerce business, you’ll find lots of different SKUs. If you can display the number of characters, you can easily get to know it. Apart from that, you might need to set a word limit for a cell, which is quite easy with this function.

Formula: =LEN(cell)

3. SORT

SORT is one of the self-explanatory features that say it all through its name. This is not only a necessary feature but also a lifesaver. Imagine finding a certain value from thousands of disordered data? It could’ve been a nightmare for a data analyst had there not been this function.

With SORT, you can just arrange your data by highest or lowest value, and easily find out the one you need. An ordered list also makes life easy for a data analyst in many ways.

Here’s how you can use this feature.

As you can see, there is a dedicated feature for sorting a list of data. Here we have selected the Length data and sorted them starting from the smallest.

The good thing is when you sort a single column of data, it also sorts the corresponding row with it.

4. FILTER

FILTER is another super handy feature from excel. It is used when you need to show that contains one or more specific terms. When you have thousands of data to analyze, this is another feature that will help immensely.

Have a look at how you can work with one of the most used functions to do data analytics in excel. Click on the Filter option from the Sort & Filter button. You can see a downward arrow with every collum as soon as you click on the Filter.

Now you can filter out the result that matches your desired data.

And as you can see, the sheet is now showing only the rows that includes 1995.

5. VLOOKUP

Ah, the VLOOKUP! Even if you haven’t worked as a data analyst, you must know this function if you did even some small task-completion with excel. It is considered as one of the most recognized excel function.

Using VLOOKUP, you can easily find data associated with a value that the user chooses. This feature has multiple impressive applications. You can search within a single table or multiple tables.

For example, you have Name and Employee ID in one table and Name and Salary in another, you can easily add them together using VLOOKUP.

Let’s see how VLOOKUP works.

VLOOKUP formula consists of 4 different parameters, LOOKUP_VALUE, TABLE_ARRAY, COL_INDEX_NUM, [RANGE_LOOKUP

• LOOKUP VALUE is the value you want to look for.
• TABLE ARRAY is the table where you want the function to look.
• INDEX NUM is the column number of the information you need.
• RANGE LOOKUP is used to specify the type of lookup.

Here’s how we started the VLOOKUP. We specified the value we want to look at, which is Johnny in our case. Then we selected the cell where the value Johnny is.

After that, we selected the data which has the birth year related to all the name here. We put in sheet 2.

Then we also selected the column where our desired information is. Which is in B or Column 2 in our case.

Here is the end result. Efficient, right?

Formula: LOOKUP_VALUE, TABLE_ARRAY, COL_INDEX_NUM, [RANGE_LOOKUP

6. LEFT / RIGHT

Remember what CONCATENATE does? Well, the LEFT/RIGHT function does the opposite. With these two functions, you can extract data extract specific text/number from a cell. While the LEFT formula extracts these data from the beginning part, the RIGHT formula does it from the end.

Here’s how you can perform the operation with these functions. Staring with the LEFT function.

And the RIGHT.

Here is the end result.

Formula: =LEFT(cell, length of data to extract from left)

=RIGHT(cell, length of data to extract from right)

7. FIND & REPLACE

FIND & REPLACE! Does it need any extra introduction? With this feature, you can find a specific text or number within a table. Then it replaces all the data that matches your provided term with a new term that you provide.

It can be used to replace large numbers of mistaken data at once. You can also use it to slightly modify a large number of data. No wonder it is used heavily to do data analytics in excel.

Using Find & Replace is quite easy.

But it does give back some good results. You can see that the word Johnny is now replaced with Adam everywhere in the sheet.

8. IF

IF function helps automate decision making. This makes it a very important function for data analysis.

With the IF function, you can make excel do a specific task or show a result based on the outcome of another operation. Let’s see an example of what IF does and how we can automate a decision using this function.

We want to if the birth year is 1996 or not, so we selected ==1996 in the first parameter, and then set the formula to show True if the value is true and False if the value is false.

Here is the result.

Formula: =IF(logical test, value if true, value if false)

9. SUMIF

SUMIF is another excel function that you must learn to start doing data analytics in excel.SUMIF sums up all the number that matches specific criteria.

Let’s say you have many different numbers, but you want to get the sum of a specific number (i.e. 50) of that table. This function will find how many times the number is present on that table and do a sum.

Here’s how it works.

Now we can find the sum of the values that are exactly 1995. Here is the result.

Formula: =SUMIFS(sum range, criteria)

10. COUNTIF

COUNTIF function is almost like the SUMIF. But unlike SUMIF, it does not actually sum-up all the number that matches your criteria. COUNTIF calculates and shows you how many times your desired data is present on the sheet.

The operation of COUNTIF is very straightforward and goes like below.

And now you can exactly how many times 1995 is present in the sheet.

Formula: =COUNTIFS range, criteria)

Wrapping Up

The growing demand for data analysis is not a miracle. The application of data analysis is diverse. Despite the emergence of many new data analytics software, excel is still one of the most powerful tools for data analysis.

Learning excel functions is a must for any aspiring data analyst. But you can’t just learn all the functions at once and start data analytics in excel overnight.

The right way to learn them is to just start data analysis with some important functions and learn all other functions from time to time when you need them. So, make sure to learn the use of the most important excel functions and get started with your journey of being a data analyst.