Excel Functions You Should Know for Data Analysis

beggers February 28, 2019 0
Excel Functions You Should Know for Data Analysis

Free Excel Training

Most computer-savvy people can maneuver through a simple Excel spreadsheet. Many of the toolbar commands are self-explanatory, and anyone who has worked with other Microsoft products can quickly understand the basics of manipulating the data. But unless you have taken an Excel class or have been provided with free Excel training, you may not know some of the functions this article will be discussing. Learn more about Excel functions that are available for you to use in data analysis.

SORT

When faced with an abundance of data there are times when you need to organize the information in different ways. The sort function enables you to reorganize the data with a few strokes of your mouse. If you are trying to sort data in alphabetical or numerical order, this task will be as easy as pie. Select one cell in the column you want to sort. On the Excel toolbar, click that tab labeled Data. Click Sort A to Z. You can also choose to sort things in the opposite order by clicking Z to A.

You can also sort data based on several variables. Let’s say you want to send an email to your female customers who have October birthdays. You can first sort the data by whether or not the customer is a male or a female. The second level of sorting can be made for those who have the appropriate birthday month.

To complete this process, do the following: select the cells you wish to sort. Click on the Data tab and choose the sort button. To add the first sorting level, click on the Add Level button. Each time you would like to add another sorting level, repeat this process.

SUMIF

This function should be read, “sum if,” as in add this if certain conditions are met. This function allows you to figure out the sum of a range of cells after a particular situation is met. You can also set up this function so that several conditions need to be met before adding the data.

Let’s assume you have a list of houses available in a particular area, and you want to figure the total value of homes that cost more than $250,000. You can use the SUMIF function to tell the spreadsheet to add the number to the total only if the amount meets the criteria.

TEXT

Text formulas are incredibly versatile. They can make your spreadsheet more user-friendly and attractive. Text formulas can also allow you to retrieve information from text. Using text formulas, one can get the days, hours and minutes between dates. You can also convert dates to a Julian date format using text formulas. You can also create a data range from two dates.

CHARTS

Excel Functions

The chart function allows someone to present data in a visual format. Since everyone learns differently, being able to display data in a wide variety of ways is extremely helpful. Choose from standard bar charts, line charts, and pie charts. You can also create doughnut charts, area charts, XY or scatter charts, and bubble charts. Make sure your chart is an accurate reflection of your data before publishing it.

AVERAGE

Let’s not forget that Excel is used as a calculation tool. If you would like to find someone’s average earnings over a period of time, use the average function in Excel.

MEDIAN

If it’s been a long time since you have been in fourth-grade math, you may not remember what median means. The median number in a list is the number that is in the middle of the group. Half of the list of numbers will be higher, and half will be lower.

MIN/MAX

Staring at large spreadsheets with a ton of numbers is difficult on the eyes. If you are trying to find the smallest or largest number in a range, quickly use the MIN or MAX functions. It will save your time and your eyes.

COUNT

If you want to know how many numbers are between a certain range, ask Excel to count them for you.

LEN

Excel can even count the number of characters in a single cell. Although it’s not clear why you would ever want to do this, know that with Excel . . . you can.

 

NETWORKDAYS

We already learned that Excel could count how much time elapses between specific dates or hours. Excel is even smarter than that. Sometimes you only want to know how many workdays have passed over a particular term. Use NETWORKDAYS to calculate this.

Excel’s functions will save you time and money.

 

Leave A Response »