by: Arnel Lopez Cadeliña
I have been using Tableau for 5 years now but Excel has been part of my work for more than 30 years way before sophisticated Business Intelligence tools like Power BI and my favorite BI tool Tableau. Companies and people trust Excel and even though Tableau can do more in Data Visualization there are more people familiar and comfortable with Excel. So, In this post, we will learn the use of Excel for data analysis.
In all companies I worked with, Excel as a spreadsheet is used for many internal applications. We use it to budget, timeline projects, create simple dashboards, and even for project management. Simply said Excel is used to fulfill a specific need by consolidating data points and creating a cohesive narrative for data analysis.
The improvement of Excel in their almost yearly versions made the fundamental goal of data analysis easier to learn and understand the data and derive meaningful, actionable insights. Most of all Excel is affordable and widely used.
First feature: Pivot Tables
Pivot tables are used to quickly group, aggregate, filter, and visualize data with simple drag and drop. No formula is needed. A pivot table in Excel is an extraction or resumé of your original table with source data. A pivot table can provide quick answers to questions about your table that can otherwise only be answered by complicated formulas. Pivoting tables are easy to change on the need of the data analyst since the pivoting draws attention to valuable information, which is fundamental to data analysis. A simple refresh will update all values when new data is added to the table you are analyzing.
Pivot Analysis
Analyze Data
This feature helps Data Analysts understand the data using a natural query language without writing complex formulas. Visualizations are displayed for a query using the best suitable chart.
‘Analyze Data’ can also be accessed under the Home tab.
Analyze Data
Analysis Toolpak
The Analysis ToolPak is a Microsoft Excel add-in program.It is a free add-on used to perform complex statistical or complex engineering analyses. To use it in Excel 2013 or Excel 2016, you need to load it first:
From Excel 2013 or Excel 2016, click the File tab, and then click Options.
Click Add-Ins and in the Manage box, select Excel Add-ins.
Click Go… button.
In the Add-Ins available: box, select the Analysis ToolPak check box, and then click OK.
After you load the Analysis ToolPak, the Data Analysis command is available in the Analysis group on the Data tab.
There are numerous functions like correlation, descriptive statistics, smoothing, histogram, moving average, rank, regression, etc.
Analysis ToolpakExcel add-in can be used for data discovery, cleansing, transforming, and combining data from different sources. It prepares data for further analysis. In this image, you can see Returns2. Returned is a column from another table.
Power Query
Other Useful Excel Features to Analyze Data
Sort
You can sort data on multiple columns for analysis. In this example, since Salary is sorted in descending order within State sorted alphabetically. So, we can now compare data state-wise and salary-wise.
SortA Filter not only allows you to select data but also shows you the unique values, blank values if any.
Functions
Excel has many functions that help in data cleaning, sorting, analysis, and much more. Some common examples are:
| Function | Use | Syntax |
| COUNTBLANK() | Detect missing values and will give the count of blanks. | COUNTBLANK(range) |
| CONCATENATE() | Combine the values of several cells into one cell. | CONCATENATE(text1, [text2], …) |
| LEN() | Display the length of the string. | LEN(text) |
| SUMIFS(), MAXIFS(), AVERAGEIFS() | Perform sum, max, and average using a criterion. | SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) |
| RANK | Return the rank of a numerical value, which is vital during analysis to understand where the value falls in the range. | RANK(number, ref, [order]) |
| VLOOKUP/XLOOKUP | Combine data from multiple tables based on some common field. | XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_model], [search_mode]) |
| IFERROR() | Replace the #VALUE errors with any text/value. Useful when displaying data to the end-user. | IFERROR(value, value_if_error) |
Since, You can visualize data using data bars, color variations, and icon sets depending on the cell’s value. In this figure, you can see some options and data bars. When you have large amounts of data, this is helpful to emphasize unusual values and identify patterns.
For instance, You can use the built-in conditions or create your own. So, You can apply conditional formatting to a selected range of cells, an Excel table, or a pivot table.
Conditional FormattingYou have the option in Data -> Remove Duplicates, and we can also choose the column names that we want to check for duplicate values. It is an essential task in data cleaning.
Remove DuplicatesCreating data visualizations is very easy using Excel. Analyzing rows and rows of data is much easier with a chart! Excel provides a variety of charts for your analysis.
types of data analysis using ExcelExcel tables also help to manage and analyze datasets systematically with headers and alternate shading or banding of rows. since, They have structured references and dynamic ranges, which aid in data analysis. so, You can convert data in Excel to a table by clicking any cell in the data and Ctrl + T.
What-if Analysis
What-If Analysis in Excel allows you in order to compare the outcome results based on variable changes. So, The goal seek functionality helps by mathematically adjusting a single variable to reach the desired goal, and there are many other features. This option is in the Data tab.
Final Remarks
Python and R, Tableau, and Power BI are other tools for data analysis but I embraced that Excel is still relevant because it is easily available for many people. Many students and professionals use Excel as their first tool for their spreadsheet and by default the first in one's journey in business data analytics.
No comments:
Post a Comment