8 Excel functions that every Data Analyst must know

Are you a data analyst or looking for a career as a data analyst??? Well then, today’s article is dedicated to you. We know you are working on large amount of data regularly and therefore bring to you some basic but very important excel functions that will help you in your daily work.

1. Sort

When you are working on a large amount of data, there may be cases where you want to group certain type of data and want to sort it in some way and doing it manually will definitely age you by some years, instead use the sort function and group the data in few seconds. You can sort it in ascending order, descending order, alphabetical order etc.

To sort your data follow these simple steps

Select a single cell in the column containing the data you want to sort.

Select the Home tab.

Under the Editing group, press the Sort and Filtering button and select the order you want your data to be sorted.

You can also use the shortcut key ALT + H + S

2. Filter

Applying filters to your data can bring in a lot of ease and convenience. With the help of filter function you can subgroup your data as you want. It helps you to narrow down your large data and you can view only the information that is relevant to you.

To apply filters to your data follow these simple steps

• Click on any cell in your spreadsheet.

• Select the Home tab.

• Under the Editing group, press the Sort and Filtering button and select the Filter button.

• Drop-down menus will appear next to each cell heading.

• Clicking on any drop-down menu will provide you with options for sorting or filtering.

• You can also use the shortcut key ALT + H +S + F

3. SUMIF function

Adding your cells in Excel is easy, but what if you want to add only if certain condition is met. That is where the SUMIF function comes to your rescue. SUMIF function helps you to get the sum of a range after fulfilling certain mentioned criteria. When you want to add more than one condition then use SUMIFs function instead of SUMIF.

Click Here to know all about the SUMIF function

4. Pivot Tables

One of the most important functions that a data analyst must be well versed with is Pivot Table. Pivot tables allow you to quickly summarize and analyze large amounts of data in lists and tables–independent of the original data layout in your spreadsheet–by dragging and dropping columns to different rows, columns, or summary positions. To create a pivot you can use shortcut ALT + N + V + C

Know how to create Pivot Table

5. Text Formulas

Text formulas can help you a lot when you want to extract only a certain amount of text from a large amount of data. It is not necessary that you know all the text functions as some of them can be quite useless to a data analyst but knowing some basic text functions like LEFT, RIGHT, LEN, SUBSTITUTE etc.

Learn to use important text functions

6. IF formulas

When you are working as a data analyst, it is obvious that you are required to make certain decisions based on your data research. To help you make the correct and quick decisions always use IF statements. For example, if you want to calculate commission on the basis of sales of employee, you can easily calculate it through IF functions.

Learn to use IF and Nested if functions

7. Charts

Too many numbers create too much of confusion and hence using charts can be a great help. Charts simplify the data and make it look more presentable. But if you select a wrong chart to present your data then things can go haywire so always be careful while selecting the chart for your data.

Know which chart is suitable for your data.

8. Conditional Formatting

Some data is important whereas some data is less important and it is very important to distinguish your important data from the less important data. If you feel that this is confusing, your excel sheets will be even more confusing so simply use Conditional Formatting and get rid of all the confusion. For conditional formatting you can use the shortcut key: ALT + H + L

Click here if you are still confused .

We hope that all these functions will help you to simplify your data. Let us know which is your favorite feature and why in the comments box below.