An Introduction to Data Analysis Using Excel

Microsoft Excel is inarguably the most common spreadsheet program for PCs. It is used to carry out different calculations and offers statistical functions that can be used by data analysts. This guide will cover top Excel features so you can learn how to do data analysis using Excel. 

Using Excel for Data Analysis

Excel is used by data analysts to enter numbers, text, and formulas on spreadsheets. In most cases, text data is used for worksheet names and headings to label columns of data. Text data often contain numbers, letters, and other special characters. Formulas, on the other hand, are mathematical equations with data from the spreadsheet.

In data analysis, you can use this tool to format email addresses and phone numbers, concatenate separated names, separate last and first names, and extract last or first initials from names. 

Excel in Data Analysis: Pros 

There are several benefits of using Excel for data analysis. One is the ability of the tool to organize large datasets into logical and orderly sheets. You can even add pie charts and bar charts where necessary. When the data is more organized, it is easier to analyze it or use it to create graphs or other visual data representations.

Another benefit is that Excel makes batch calculations a lot easier. This popular feature crunches numbers quickly. You can use Excel to compute complex and simple equations with large datasets. However, this depends largely on your analysis skills.

Excel in Data Analysis: Drawbacks 

There are also drawbacks to using Excel. Excel can be difficult to use, especially when you want to utilize the more complex features. Anybody can start filling tables without prior knowledge of the tool, but if you are not familiar with the other features, it could be rather difficult to use.

Another drawback is the lack of error-checking features. The automatic calculation features come in handy when you want to analyze large-scale data. However, the feature is not foolproof. Excel currently lacks a tool to check for errors once the data is entered into the sheet. 

Learning Excel

It is possible to learn Excel by practicing some basic math problems. The basic math makes it easier for you to master the tool and start handling more complex tasks like types of formatting and learning functions.

How Long Does It Take to Learn Excel?

It can take around 12 to 14 hours to learn Excel. You need to continue practicing to advance from a beginner and become a data analyst. 

Can You Learn Excel at a Data Analysis Bootcamp?

Yes, you can learn Excel at one of the best bootcamps for data analytics. It is often a part of the introductory lessons, after which you might learn a more powerful data analysis tool like R. Some bootcamps with data analytics programs are Springboard, General Assembly, and Thinkful. 

Top 5 Functions of Excel for Data Analysis

There are many functions in Excel, but it can be difficult to learn how to use each formula to conduct the kind of data analysis process you need. Below, you can find useful functions that enhance your ability to organize and analyze data as a business analyst or data analyst.

VLOOKUP Function

VLOOKUP  is an excellent data analysis function for looking up information in the dataset. The VLOOKUP function helps you look for any piece of data and shows you the corresponding information about it. For example, if you’re looking for bananas, the VLOOKUP function can give you information about the price of bananas on the sheet. 

Pivot Tables

Pivot tables allow the user to organize, manage, sort, and analyze datasets in different ways. This tool is one of the most powerful in Excel when it comes to data analysis. This is why it is used by financial analysts. Behind the scenes, Excel runs a database in its built-in Pivot tables, which can allow the user to manipulate large datasets.

CONCATENATE Function

The CONCATENATE function allows the user to join two or more strings of data into a single string. Data analysts deal with data that isn’t always structured. This means they may need to combine data from different cells into one. Or they can split data from a cell into more than one cell. This is what the CONCATENATE function does.

COUNTA

This handy function is a statistical tool. Its role is to calculate the number of cells that aren’t blank in a set of values. This function is also known as the COUNTIF Not Blank formula.

SUMIFS

This analytic tool is linked to math and trigonometry. It sums up the cells that meet certain criteria. In most cases, the criteria are based on numbers, dates, and text. Also, the SUMIF function supports logical operators like >, <, <>, and =.

Data Cleaning with Excel

Data is the backbone of analysis in Excel. With data, anything can go wrong: the placement, structure, extra spaces, even the formatting. However, Excel makes it easier to clean the data in simple steps. 

1. Convert Numbers That Are Stored as Text into Numbers

It is possible to do this if your imported data from external databases are stored as text instead of numbers. Simply type 1 in any blank cell and choose the cell before pressing Control + C. Next, select the cell to convert to numbers. After that, select Paste and then Paste Special. A dialogue box will pop up. In it, select Multiply and click OK.

2. Remove Duplicates

To remove duplicates, select the data you want to remove. Click on Go to Data and Remove Duplicates. Where the data has headers, check the check box at the top right. Choose the column to remove the duplicate and then click OK.

3. Highlight Errors

To highlight errors, you can use conditional formatting by selecting the entire dataset. Click on Go to Home and Conditional Formatting next. Then click on New Rule and select Format Only Cells that Contain in the dialogue box. Select Errors in the Rule Description and set the required format. Click OK to highlight error values in the dataset you have selected. 

4. Remove Extra Spaces

The TRIM function helps you to remove extra spaces in your dataset. The syntax is TRIM(text). To remove the extra spaces, use = TRIM(A1). Simply copy and paste it into the cells and it will remove the spaces.

5. Select and Treat Blank Cells

It is also possible to select all blank cells at once. Start by selecting the whole dataset and pressing F5. It will open a dialogue box. Next click on the Special button to open the Go To Special dialogue box. Then click on Blank and then OK.

Analyzing Datasets with Excel

You can also create charts and add miniature graphs to your dataset. This makes it easier to analyze data with Excel. Other interesting things you can do include creating Pivot tables, applying a table style, applying conditional formatting, and inserting totals. We will cover each of these operations, moving from most simple to most complex. 

1. Change the Format of Numbers 

Choose the range and values to be converted. Then go to Data and Text to Columns next. Select Delimited and then click Next. Uncheck the checkboxes for delimiters and then click Next again.

2. Create a Chart

To create a graph or chart, you need to select the range of data you want to chart and then press F11. It creates an Excel chart following the default chart style. However, you can change it. If you want the chart to appear on the same worksheet, press ALT + F1 instead of F11.

3. Create a Pivot Table

To create a pivot table, click anywhere in the data list and you will see the Pivot Table Field List pane. Simply arrange them in boxes at the end of the pane.

4. Get a Random Sample from a Dataset 

Sometimes in data analytics, you want to look at a random group of data points so that you can more easily extrapolate insights about the whole dataset. To do this, you’re first going to line up the raw data you want to look at, and then designate an adjacent column for random numbers. Use the RAND function to assign each datum a random number between 0 and 1. 

Now, reorder the values in the random column from lowest to highest. This will also change the order of the non-random column. By sorting the random values, you will have indirectly unsorted the values in your actual dataset, effectively randomizing it. Pick a size n for your sample. The first n values in your reordered dataset will be your random sample. 

5. Use the Analysis ToolPak

To use the Analysis ToolPak, click Options from the File tab. Enter Add-ins and select the Analysis ToolPak. Click Go. When a pop-up window appears, check Analysis ToolPak on the options and click OK. Also, check the Data tab in the Excel sheet’s toolbar to find the Data Analysis option. You can perform descriptive statistics, correlations, or t-tests. 

Data Visualization with Excel

There are several ways to display data analysis reports in Excel. We cover a few of them below. 

1. Visualize Trends with Sparklines

To do this, select the range containing the data you want to plot. Next, locate Insert > Sparklines and select the kind of sparkline you prefer. It could be Column, Line, or Win/Loss. Click on Select Range to find the sparklines location and press Enter. Next click OK.

2. Change the Color of Markers

Do this by clicking on any cell in the sparkline to reveal the Sparkline Tools menu. Go to Marker Color, choose the one you want, and change the color. 

3. Change the Width of Lines

To do this, click on a cell within the sparkline so it can reveal the Sparkline Tools menu. In the menu, find Sparkline Color > Weight. Then change the width to the desired size. 

4. Do a Quick Analysis

The Quick Analysis button allows you to create a graph by clicking a button if you go to charts. However, if you click on Totals, you can insert a row containing the average for every column. Also, if you click on the Sparklines option, you can insert sparklines.

5. Use Pictures in Different Column Charts

To do this, click on a column. Then click on Format Data Series and select Picture. Insert the picture by providing the file name. The chosen picture will appear instead of columns in the sheet chart.

Top Excel Resources for Beginners

There are a lot of resources that beginning learners can use to learn Excel. Some are online courses and tutorials while others offer real-world data that you can work with until you perfect the skill.

Data.gov

This is an excellent resource because it allows you to find tools, data, and resources for different purposes. These resources can help you develop web and mobile apps, conduct research, and learn how to create data visualizations like pivot charts. 

Microsoft’s Excel Training Center

This training center from Microsoft contains many free videos, tutorials, and guides on Mac OS, Windows, iOS, and Android that cover old and new versions of Excel.

MrExcel.com

This resource makes it easier for everyday people with basic knowledge of Excel to reach experts for quick answers to questions. If a user posts any question, members of the expert community will comment with an answer.  

Excel Training Tutorials by Lynda.com

Lynda.com has more than 100 Excel courses. It also contains over 4,000 video tutorials that cover all versions of Excel as well as all levels of expertise. 

Excel Courses by Coursera

If you want to learn with a college-level course, then the courses on this resource can come in handy. There are over 100 courses that deal with Excel in some way, most of which are taught by professors from real universities. 

What’s the Future of Excel?

Excel is an important tool for data analysis. It is useful for people who deal with statistics or money. The skill will continue to be valuable for people in business intelligence careers because business analytics will always require customized work. Given the artificial intelligence and machine learning revolution, however, you should learn more advanced tools as well.