The Basics of Data Analysis with Excel

Growing businesses and personal information tracking find data analysis to be of utmost importance. Data Analysis is inspecting, transforming, and modeling data in a specific manner to find new information fruitful to the user. 

Data analysis was done manually or through expensive software back in the day. However, Microsoft Excel has now grown to become one of the most powerful Data Analysis tools out there. Not only is it accessible and affordable, but also easy to use and incredibly functional. 

If you’ve been willing to dive into the nuances of Data Entry using Microsoft Excel, you’ve come to the right place. In this article, you will learn how to use Microsoft Excel for Data Analysis.

The 4 Types of Analytics Explained
The 4 Types of Analytics Explained

12 Data Analysis Techniques that Most People don’t Know

Excel Data Analysis Functions 

Excel is essentially a Microsoft developed spreadsheet. Therefore, it is used for various functions such as data entry, calculations, pivot tables, graphic tables, and macro programming. With sufficient knowledge and practice, using Excel for Data Analysis is very convenient. 

Let us have a look at the data analysis functions served through the software.

  1. Reading Data into Excel

The very first step to data analysis is importing figures and data into the software. Excel allows data to be read in two formats:

  • Excel data file: presented with the .xlsx or .xls extension
  • Text file: presented with the .txt extension

When we look at Excel data files, the data is presented within the software, organized within various rows and columns. However, when the file is in a text format, it is presented in a text software, wherein each row is visible in a single line, without any columnar separation. Each data entry is separated through commas. 

Reading an Excel file into the software only requires you to click on the file to make it automatically open using Excel. However, to read the .txt file into Excel, you must:

  • Click on the file
  • Choose the delimited option from the Excel guide wizard. 
  • Choose comma as a delimiter in the next window. 
  • Click on Next and Finish to successfully read the text file in Excel. 

When we follow this procedure, all column sizes will be reduced to half. To restore the right column size, select all the columns and double click on any

Data Analysis with Excel
Data Analysis with Excel
Data Analysis with Excel
  1. Basic Data Manipulation with Excel 

Before we get to data analysis, knowing how to manipulate data in the software is crucial. You must know how to carry out at least the following data manipulation actions. 

  • Moving across cells
  • Formatting cells
  • Sorting columns and adding levels to them

Through Excel, you can move to the bottom of a row by pressing the Control and Down arrow keys together. You can format and manipulate several criteria to make data analysis simple. 

You can follow the given procedure to manage the number of Decimal places present in a particular column to make data analysis simpler.

  • Select the column in question. 
  • Right click and select format cells
  • Select Number from the given row and select the number of decimal places you want to see. 

You can use the format cells function to run various formatting, arithmetic, text, and scientific functions, to say the least. 

You can also sort the data in various orders according to your requirements by selecting the entire data and using the data sorting options from the functions ribbon. 

  1. Organizing Data in Excel

Organization is an important factor influencing data analysis. Using Excel, you can organize the data in some of the given ways:

  • Placing similar data in the same column
  • Keeping a range of data separate from others
  • Positioning important data around the range
  • Avoiding blank rows and columns
  • Using column labels for easy identification
  • Extending formulas and data formats 
  • Organize varying data types in separate spreadsheets

You can also use several organizational commands and functions such as the IF command, the VLOOKUP function, and the HLOOKUP function. 

The syntax for the IF command is =IF(logical test, value if true, value if false). Let us learn how and why it is used. 

Let’s suppose, a spreadsheet requires orders taken on the 1st of January to be given a single star while others can have two stars. The IF command for the same should be:

=IF(C2=”1st January”, “*”, “**”) 

  1. Simplifying Complex Data

If you’re working with humongous data spread across thousands of rows and columns, you will have to simplify it. Without simplifying, even using most data analysis tools will fail to work. You can use several organizational and visualization methods to simplify data on excel. 

  • Hyperlinks: Using hyperlinks within spreadsheets will allow you to navigate through your multiple sheets at ease. By simply naming significant cells and creating hyperlinks within the sheet will allow you and other users to navigate through and analyze data effectively.
  • Hide unwanted data: When you’re dealing with a lot of data, you’d want to simplify the representation a bit by hiding some of it. To avoid getting lost in the figures and components, you can use the built-in hide rows and columns features.
  • Highlight significant data: You can highlight important data by strategic placement. You would want to place crucial data right at the first appearance of the spreadsheet. However, you can also use the bold and highlighting tools to simplify the data organization. 
  1. Arithmetic Manipulation in Excel

When using Excel for data analysis, you will require using it for mathematical operations. Given below are a few arithmetic operators you can use to perform manipulations. 

FunctionOperator 
Add+
Subtract
Multiply*
Divide/
Percentage%
Exponential^

Before using any of these arithmetic operators, you must use a comparison operator, which is as follows. 

FunctionOperator 
Equal to=
Greater than>
Less than<
Greater than or equal to>=
Less than or equal to<=
Not equal to<>

When you want to carry out any mathematical function, you must follow a comparison operator with the cell number and the arithmetic order followed by the other cell number or value. 

You can carry out several arithmetic functions through BODMAS formulas such as the following. 

  • To divide one cell (E2) with another (F2): =E2/F2
  • To add one cell (E2) with another (F2): =E2+F2

When you want values to display in Excel, you must apply the formula on a fresh cell. You can copy the same formula through a column by selecting the cell with the formula and dragging it down to the column of your choice.

Data Analysis with Excel
  1. Data Filtering with Excel

When you want to quickly analyze similar data, filtration comes in handy. There are several filtration techniques available in Excel, but the most common and basic ones are as follows.

  • Control + Shift + L: When you press this combination, you apply a filter to the text. If you want to search for the cells only mentioning the sales of yellow mugs, you can click on any cell with ‘yellow mugs’ text in it. After pressing the shortcut combination, you will be able to find filter out only the cells you require to analyze.
  • Filter Multiple Selection: You might want to filter a few keywords together; you can do so too. Press on the above-given shortcut and click on the column’s top downward arrow. You will find a drop-down menu with the different entries in the column. You can select as many as you want. 

By exploring the filter menu, you can choose to filter out multiple column entries, filter on the base of color, and do many more things. You can refer to this video to learn more filtration options.  

Data Analysis with Excel
Data Analysis with Excel
  1. Pivot Tables

Pivot tables are so powerful that using them will allow you to analyze large data size in one go. Pivot tables are used to summarize and group data. It allows you to look at large data from various different angles to find new data analysis angles. 

There are a few things you need to be sure of before you get to using pivot tables. The data should be free of:

  • Subtotals: since Pivot tables are going to help you get them later.
  • Filters
  • Unique data entry columns 
  • Inconsistent formatting 
  • Blank cells, rows, or columns
  • Duplicate entries

There’s a lot of things you can do with a Pivot table in Excel. They allow you to increase your work productivity at a drastic rate by presenting huge data in simplified forms. 

Since pivot tables are designed for accommodating on-going updates, you only have to refresh to update your data.

Pivot tables also allow you to make charts in a better, more accurate, and productive manner. 

  1. Using Excel Charts

Once you have all your data in place, some visual representation goes a long way in increasing data understanding. You can choose the right chart type for data analysis keeping in mind the different functions it can be used for, such as:

  • Visual representation 
  • Finding relationships among data entries
  • Classifying and categorizing data
  • Understanding data composition 
  • Understanding data distribution 
  • Understanding overlapping data 
  • Determining trends and patterns
  • Predicting future trends

The most popular charts and graphs in Excel are combination charts, clustered column charts, bar charts, and pie charts. You can also use stacked area charts, Venn diagrams, histograms, and bullet charts to suit your data analysis requirements. 

To analyze the data quickly through charts, all you have to do is select the required cells and press Control + Q to activate Quick Analysis. You can then choose the chart type you want to use for your analysis. 

  1. Sorting and Analysis data with VLOOKUP

VLOOKUP is one of the tools used for advanced data analysis. It is a function mostly used by advanced data analysis professionals. It is a tool used to ‘marry’ different data entries. 

VLOOKUP allows you to bring corresponding values together in a big data set to get multiple values. You can even bring back values from larger data sets such as different workbooks and spreadsheets. The best part of using VLOOKUP is its seamless function.

Using VLOOKUP allows you to function with several different data sets without the hassle of copying them in a single spreadsheet. Therefore, data analysis becomes much easier and quicker. 

The general VLOOKUP syntax is: 

=VLOOKUP(key to lookup, source table, column of source table, if you’re okay with a relative match)

 Therefore, VLOOKUP searches for the desired value and returns it to the specified row if the right match is found. 

  1. Advanced Excel Charts 

Advanced charts are not only great for better data analysis but also for making your reports look cleaner. Even though the default charts present in Excel are great, advanced charts run the extra mile to make data analysis easier. 

Some examples of the best advanced charts are:

  • Milestone charts
  • Bell curves
  • Pareto charts
  • Step charts
  • Thermometer charts
  • Waffle charts
  • Gantt Chart

All these advanced charts will require you to do some extra work to generate. They are one of the most complicated and powerful data analysis tools in Microsoft Excel. 

Even though some advanced charts such as the thermometer chart might not be as complicated, some such as the waffle chart are highly complicated.

Using advanced charts allows the data to be represented in a much cleaner and concise manner. It also helps in making information conveying more understandable and simplified. 

Using advanced charts such as histograms and Venn diagrams allow you to have new takes on your data analysis.  

Excel Data Analysis Video Guide from ExcelIsFun YouTube Channel

ExcelIsFUN Youtube Channel

Summing Up

Data Analysis is only simple as long as you have the right tools and information to facilitate the job. With Microsoft Excel, you get several options, tools, and functions you can perform to make data analysis fun.

The most important thing to note, however, is that while using Excel, you must have good organizational skills. You should be able to organize the data as perfectly as possible in a manual manner. 

Once you’re satisfied with it, you can use the various tools and methods to make the data more presentable.

Analyzing statistics and presenting reports to follow patterns and predict future trends is simple with Excel’s visual representation. With enough practice and knowledge, there will hardly be any data size with the capability to bother you. 

Sources:

https://www.linkedin.com/pulse/vlookup-function-perform-advanced-data-analysis-lal-ca-cma-pmp/

https://www.optimizesmart.com/how-to-select-best-excel-charts-for-your-data-analysis-reporting/

https://support.microsoft.com/en-us/office/guidelines-for-organizing-and-formatting-data-on-a-worksheet-90895cad-6c85-4e02-90d3-8798660166e3

Emidio Amadebai

As an IT Engineer, who is passionate about learning and sharing. I have worked and learned quite a bit from Data Engineers, Data Analysts, Business Analysts, and Key Decision Makers almost for the past 5 years. Interested in learning more about Data Science and How to leverage it for better decision-making in my business and hopefully help you do the same in yours.

Recent Posts