A Comprehensive Guide on Advanced Microsoft Excel for Data Analysis

 

Overview

Data analysis provides insights from raw data which is used to support decision-making. Microsoft Excel is a simple, powerful, and one of the top tools for data analysis. This comprehensive guide will introduce you to the concepts of data analysis and present practical examples using Microsoft Excel.

Introduction

Data analysis has emerged as an important field because it provides the ability to analyze data which helps people in making better decisions. Data analysis is the process of collecting, modeling, and analyzing, and exploring data to find a pattern in it.

Table of Contents

1. What is Data analysis?
2. Data analysis with Microsoft Excel
3. Essential keyboard Shortcuts for Excel Data Analysis
4. Download data set
5. Sort, Filter, Conditional, Formatting, and Charts
6. Create a Pivot table for a data set
7. Tables, What-If Analysis and Quick Analysis
8. Analysis ToolPak
9. Other tools for data analysis
10. Conclusion
11. End Notes

What is Data analysis?

Data analysis is the process of Data Requirement Gathering (What type of data do you want to use and how do you plan to analyze it), Data Collection (Collect the data from available sources), Data Cleaning (The data we collected are not always in ideal form so we have to remove white spaces, duplicate records, null values, and basic errors. it is mandatory to clean data before uses for analysis), Data Analysis (In this step we uses data analysis software’s and other tools to interpret the data and get insights on data), Data Interpretation (We need to interpret them and give the best findings from that) and data visualization (Display your data using graph and charts so that people can read and understand it easily).

There are two main methods of Data Analysis: qualitative analysis and quantitative analysis. There are many other techniques available like Statistical analysis, Text analysis, Diagnostic analysis, Predictive analysis, Prescriptive Analysis, etc.

Data analysis with Microsoft Excel

Microsoft Excel provides powerful features and ways to interpret and analyze data. The data can be collected from various sources. Then that data can be converted and formatted in several ways according to user need. It can be analyzed with the relevant Excel commands, statistical functions, and tools – Sort, Filter, Ranges, Tables, Conditional Formatting, Date functions, Time functions, Subtotals, Quick Analysis, What-if Analysis, Solvers, Pivot Tables, Charts, and Analysis ToolPak, etc.

Essential keyboard Shortcuts for Excel Data Analysis

1. Alt + A + T or Alt + D+ F +F : To add or remove Filters

2. Ctrl + Pg Up/Pg Dn : Shift between sheets

3. Alt + A + S + S : To sort data

4. Ctrl + Spacebar : Select entire column

5. Shift + Spacebar : Select entire row

6. Ctrl + Shift + Down/Right : Select till the last non-empty cell in column/row

7. Ctrl + 0 : To hide columns

8. Ctrl + Shift + ) or Alt + O + C + U : To Unhide columns

9. Ctrl + 9 : To hide rows

10. Ctrl + Shift + ( : To Unhide rows

11. Alt + W + F + R : To freeze top row

Download data set

Download Supermarket sales data set from Kaggle using the below link:

https://www.kaggle.com/aungpyaeap/supermarket-sales

The growth of supermarkets is increasing and market competition is also high. The given dataset shows the historical sales of a Supermarket Company which has recorded data for 3 months in 3 different branches of the Supermarket. We are going to analyze this dataset with Microsoft Excel. Open supermarket_sales – Sheet1 file in MS Excel. We can also check the Attribute information of this sheet on the Kaggle site.

This data set is already clean and validated so we don’t have to perform any data cleaning and validation steps on it. Before analyzing always check that only valid values are in cells, there are no missing or null values and data is in the proper format.

Sorting in Excel:

We can sort our Excel data based on one or multiple columns. We can sort in ascending or descending order or sort data in a custom order. We can also sort data by color. We can use Alt + A + S + S shortcut to directly sort data or select the sort icon from the Data menu as shown below.

In the below example we sort data according to the Date column and the order is ascending. We can also sort data according to different columns and different orders. For example, if we want to check data rating wise we can sort data according to the rating column. From this, we can find out the highest and lowest-rated products and branches ratings.

Filtering in Excel:

We can filter our data and can only display records that we need for analysis. We can use Alt + A + T shortcut to directly Filter data or select the Filter icon from the Data menu as shown below. We can also apply a number filter to display numbers in a certain range or greater than, less than, or equal to the selected limit We can also apply a text filter to display records that begin with a specific character, end with a specific character, contain or do not contain a specific character, etc. To apply a number filter or text filter Click the arrow next to the heading row. For example, if we want to display only branch A’s data, then select only branch A from the filter box.

Conditional Formatting:

Conditional formatting in Excel allows you to identify duplicates in a dataset, highlight cells with a value greater/less than a number, highlight top/bottom 10 (or 10%), highlight errors/blanks, create Heat Maps, highlight every other row/column to increase the readability of the data, search and highlight data using Conditional Formatting. For example, if we want to highlight only members in customer type, first select the customer type column then Go to Home –> Conditional Formatting –> Highlighting Cell Rules –> Text that Contains. A dialogue box would open, in the dialogue box enter the value and select the color for highlighting cells.

Charts in Excel:

A simple Excel chart can be worth a thousand words of description. Concepts that are difficult to understand with sheets full of numbers can be easily displayed with charts or plots. To create a chart, Select data first then on the Insert tab in the Charts group click the chart symbol you want. There are different types of charts like Column Charts, Line Chart, Pie Chart, Bar Chart, Area Chart, Scatter Plot, Data Series, Axes, Chart Sheet, Trendline, Error Bars, Sparklines, Combination Chart, Gauge Chart, Thermometer Chart, Gantt Chart, Pareto Chart available in Excel.

Create a Pivot table for a data set:

The Pivot Table feature is a powerful tool that allows us to summarize huge amounts of data. You can use a PivotTable to analyze numerical data in detail (summarize data as averages, sums, or counts), and answer unanticipated questions about your data.it is mostly used to understand and find patterns in the data set.

Here, we’ll use it to find out the Gross income of each Branch for General items like Electronic accessories, Fashion accessories, Food and beverages, Health and beauty, Home and lifestyle, sports, and travel. First, select table from supermarket_sales – Sheet1, so they are highlighted. Then from the Insert menu select PivotTable. In the dialogue box that appears, make sure the Table/Range radio button is selected and the accompanying text box reads ‘supermarket_sales – Sheet1′!$A$1:$Q$1001’.At the bottom of the dialogue box, make sure the New Worksheet radio button is highlighted, then click OK.

 

Now add Branch in Columns, product line in Rows gross income in values that shows the sum of gross income and generate table shown below from which we can know the total gross income of different branch for different products.

We can also add a graph for this table. Just select the type of graph from the Insert menu. We select the bar chart. From this chart, we can easily find out the highest and lowest gross income for different branches.

From this pivot table and bar chart, we can gain general insights like food and beverages have high gross income than other categories. The amount of gross income is very similar in all Branches with a slight increase in Branch C.

Just like this pivot table, we can create different types of pivot tables based on the information we need from the data. In the below example select the columns as shown in the below figure. After creating the pivot table select a line graph. From this Pivot table and graph, we can say that Women generate higher sales when compared to men.

Tables in Excel:

Excel tables allow you to analyze your data easily and quickly. To create a table from your data click any single cell in your data and then from the Insert tab in the Tables group, click Table. It automatically selects your data. Select My table has headers and click on OK.

It directly formatted the table for you and allows you to use many powerful features like sorting, filtering, calculating the total row at the end of the table by clicking on the checkbox Total Row in the Design tab. Using this not only gives you the total but you can also calculate Average, Count, Max, Min, Sum, Std deviation, etc.

What-If Analysis in Excel:

What-If Analysis in Excel allows you to change the different values in cells to see how those changes will affect the outcome of formulas. You can use its Goal Seek feature when you are not sure what input value the formula requires to get that result. To use this from the Data tab, in the Forecast group click on What-If Analysis.

Quick Analysis in Excel:

With the Quick Analysis tool in Excel, you can quickly and easily analyze your data. To use this First select the data and then Right Click and select Quick Analysis from the menu which opens. You can directly perform Formatting like adding Data Bars, Color Scales, Icon Sets, highlight cells, etc. we can directly add pivot tables, insert sparklines, Charts, and also perform calculations like Average, Count, Max, Min, Sum, etc.

Excel Analysis ToolPak:

The Analysis ToolPak is an add-in that is included with Excel that provides data analysis tools. To add the Analysis ToolPak add-in follow the below steps:

1. Open File tab in Excel

2. Click on Options

3. In Excel, Options dialog box click on Add-ins

4. Select Analysis ToolPak from Add-ins and click on the Go

5. Select the checkbox for the Analysis ToolPak and click OK.

To access Analysis ToolPak Click on the Data tab and then from the Analysis group click On Data Analysis.

The Analysis ToolPak adds the following functionality to Excel:

· Anova: Single Factor

· Anova: Two-Factor with Replication

· Anova: Two-Factor Without Replication

· Correlation

· Covariance

· Descriptive Statistics analysis

· Exponential Smoothing analysis

· F-Test Two-Sample for Variances

· Fourier Analysis

· Histogram

· Moving Average analysis

· Random Number Generation analysis

· Rank and Percentile analysis

· Regression analysis

· Sampling

· t-Test: Paired Two Sample For Means

· t-Test: Two-Sample Assuming Equal Variances

· t-Test: Two-Sample Assuming Unequal Variances

· z-Test: Two-Sample for Means

The below figure shows the dialog box for Data Analysis.

Correlation:

If we want to find out if there is any correlation between columns Gross Income and Rating. First, select the correlation option from Data Analysis. Then in the correlation dialog box select both Gross Income and Rating columns in the Input Range. Then Click on OK. The correlation coefficient has a value between -1 and +1. From this, we can know how strongly two variables are related. Value 1 indicates a perfect positive correlation. From the output shown below, we can say that there is no correlation between Rating and gross income.

Descriptive Statistics:

If we want to calculate Descriptive Statistics for any numeric column then first select the Descriptive Statistics option from Data Analysis. Then in the Descriptive Statistics dialog box select columns (for example cogs, gross margin percentage, gross income, Rating) in Input Range. Then Click on OK.

Descriptive Statistics directly calculates Mean, Standard Error, Median, Mode, Standard Deviation, Sample Variance, Kurtosis, Skewness, Range, Minimum, Maximum, Sum, and Count for selected columns. Output for this example is shown below.

Histogram:

If you want to know the distribution of customer ratings, you can use a histogram for that. First, select the histogram option from Data Analysis. Then in the histogram dialog box select the rating column in the Input range and for the Bin range create a range according to your need. We created Bin range from 1 to 10 and select it in Bin range. Click on Chart output and then Ok. It creates a histogram display below. In this histogram add Trend Line. To add a trend line go to Design –> Add Chart Element –> Trendline. From this, we can say that customer rating has a normal distribution.

Other tools for data analysis:

There are many data analysis tools available nowadays, but which one is best to analyze our data depends on what type and how much data do we need to analyze, budget, and time available for analysis.

Here is a list of some of the best data analytics tools available Other than Excel.

1. Python:

Python is an open-source, high-level programming language. For data analysis, it provides a large set of libraries like Pandas, Numpy, SciPy, and scikit-learn, etc. It also provides many data visualization packages like Matplotlib, Plotly, and Seaborn, etc. it is the most preferred language for machine learning and data science.

2. Tableau:

Tableau is a powerful analytics and data visualization platform that allows users to create reports, interactive dashboards that represent data in the most user-friendly way. It can run on the cloud or on-premises.

3. Microsoft Power BI:

Microsoft Power BI is a top business intelligence platform it makes it easy for businesses to spot trends, patterns, and insights across large sets of data. It supports many types of data sources. It also provides reports, visualizations, and dashboards for data.

4. R:

R is an open-source programming language. It is best at exploratory data analysis, predictive analyses, and statistical analysis. Such as distributions, clustering classification, and regression analysis. It is used for building your own data analysis tools.

5. Qlik:

Qlik offers in-memory data processing for fast results and data visualization. It can also run on the cloud or on-premises. It offers helpful tools support for data exploration and discovery by technical and non-technical users alike.

6. KNIME:

KNIME stands for the Konstanz Information Miner. It is a free, open-source platform to perform data integration, processing, visualization, and reporting. It builds advanced machine learning and data mining solutions with minimal or no programming requirements.

Conclusion:

Here are some general insights about the data that we find from our data analysis.

The number of gross income is very similar in all Branches with a slight superiority in Branch C. Women use to generate sales with a higher gross income than men. Customer rating has a normal distribution. The Credit Card is the payment method less used. There is no correlation between Rating and gross income.

Always perform data cleaning and data validation before Data analysis if data are not in proper format otherwise it leads your data analysis in the wrong direction.

How Many Years Does it Take to Become a Commercial Pilot?

 Becoming a commercial airline pilot can require months and even years of intense training, including written exams and flight practice. There are ways to shorten the path to a commercial pilot’s license and get into the air sooner. By choosing the right flight school, you can achieve your goal of certification in 14 months or less.

Pilot Certification Path

Traditionally, there are multiple steps that you have to take to become a commercial pilot. First, you need to get a medical certificate that clears you for safe flight. This medical exam tests your vision, balance, heart condition and hearing. While it is not mandatory to start your training with this exam, it helps to take it first so you don’t spend hours training, only to find out that you have a condition that would prevent you from flying.

After completing your medical clearance, the next step is to start ground training. This classroom training includes learning about navigation, instruments, weather and aviation procedures and terminology. This is often described as the most demanding and intense portion of commercial flight training, and student pilots are required to pass a written test administered by the FAA in the US (Federal Aviation Administration), EASA in Europe(European Aviation Safety Agency), or other the relevant agency in what ever jurisdiction you want to fly in.

Once you have fulfilled your basic requirements and passed your exam, you can move on to in-air training. You will need 1,500 of flight hours if you are training in the United States, but only 200 flight hours if you complete your training in Europe. You will train first with a flight instructor until you are ready to fly independently. You will also train in a simulator in the later stages to simulate the larger planes like an Airbus where costs of flying are too high just to train students.

Integrated Training vs. Modular Training

One of the big questions that a lot of aspiring pilots ask is how much it will cost to complete the licensing. One of the major factors in the cost and time it takes to get licensed is whether you train in an integrated program or in a modular one. Because the cost of the pilot’s license can average from around $10,000 for a private pilot’s license (PPL) to well over $100,000 for some commercial training academies, many student pilots complete their course of study while still working full time jobs. This modular training means that student pilots are learning their craft piecemeal, making it hard for them to complete the training quickly. In addition, lapse in training time means that many student pilots will need refreshers on critical information, which slows down the process and ends up costing more money in the long run.

Another factor that will greatly increase the time and cost of your training is weather. Inclement weather like snow, rain and ice storms will prevent you from getting the number of hours of flight practice that you need to complete the training. To get the right amount of required hours, you will need clear and sunny skies for your test flights and check ride. The geography of your school makes all of the difference in how much practice you will be able to enjoy and how quickly you will be able to finish. Consistent good weather means that you will have more hours behind the flight controls and the ability to pay less overall for your training.

With the integrated EASA ATPL certification, pilots can finish their entire training in one shot, and earn their licenses in as little as 14 months at FlyBy. Schools like FlyBy that offer aggressive flights schedules and have a large stock of planes available for practice are the best bet for completing your training on time. One of the major complaints that many students have is that there are not enough sunny days and available planes for them to get enough practice time to master their skills. Our students are more likely to complain they are flying too much.

Many student pilots from the United States choose to train for their license in Europe, as lower costs of living, favorable weather conditions and lower priced training makes it easier to complete the training. In addition, the European EASA flight time requirement is much lower than the United States’ required 1,500 hours, making it easy to complete the training in less time.

After completing the training in Europe, newly licensed pilots can then return to their home countries to transfer their certifications to the new location. But they can accumulate the hours they need from their home country while being paid for a large commercial carrier instead of accumulating up to 1,500 hours on their own dime. From there they can get additional certifications and licenses and work in aviation around the world.

The first step in getting your pilot’s license quickly is choosing the right school. While all pilot training schools follow a similar curriculum and use the same global standards, some schools are better than others when it comes to quickly gaining the certifications and licenses you need. Many students find that temporarily relocating for school is a worthwhile investment. When choosing a school, find one with the best calendar of flights in an area with plenty of ideal flying conditions.

Careers in the Aviation Industry

The aviation industry is experiencing rapid growth with the emergence of new discount airlines, expansion of existing airlines and increased demand for travel. At the same time, attendance at pilot training schools has remained steady, causing experts to forecast an unmet demand for trained pilots. While there has been a slowdown in the aviation industry due to the recent pandemic, the industry has bounced back quickly after every major slowdown in the past as there is always going to be a demand for commercial and leisure travel. There are still a lot of seasoned pilots retiring and if one begins training today, there is a good chance that things are going to be getting back to normal as you graduate, potentially with pent up demand. Never before has there been a better opportunity to break into the world of aviation, with an estimated 700,000 new pilot jobs expected to open in the upcoming years.

While most pilots train to become commercial airline pilots, there are other career paths that you can take after you have completed your training and earned your license. Commercial airline pilots have been traditionally in the highest demand due to the proliferation of new airlines that are starting up all over the globe. In Asia, especially, up until the recent pandemic issues, there had been a huge demand for trained pilots who can shuttle passengers all over the globe. There are also many positions available for private pilots who run charter flights for private passengers. Other new pilots work as helicopter pilots, flying aircraft for tour companies, television stations and police stations. Still others work as pilots for shipping and delivery companies like Amazon, FedEx and UPS. There are also jobs for pilots who work as first responders, fighting fires from the air, airlifting trauma victims to hospitals and performing high-altitude rescues.

With the pilot’s license, you can also start your career as a pilot instructor. With the right amount of flight hours, experience and additional certifications, you can earn a living training new pilots in your own business or in larger training schools. Flight school instructors are always in high demand. FlyBy has established a cadet program for those who want to gain more experience as a flight instructor after you have finished achieving your commercial license. You can click here to learn more about that program.

When it comes to getting your pilot license and commercial airline certifications, it can take years or as little as 14 months depending on the type of certifications you seek and the flight school program you select. The weather in your flight school’s location, the time you can devote to training and your financial resources all play a part in how much time it will take. At FlyBy, our intensive integrated ATPL program allows you to spend more time studying with fewer breaks so that you can complete your licensing program quickly.

We offer aggressive flight practice calendars that allow you to spend more time in the air and less time waiting for an available plane or a sunny day. In fact, many of our students find they spend too much time flying. In as little as 14 months, you can be well on your way to a high flying career as a commercial pilot, for only € 69,300 including all costs most flight schools charge extra for.

Contact us today to learn more about our ATPL integrated program.