SEC | S20W3 | Data Analysis with Excel: (Charts, and data analysis techniques.)

Hello everyone! I hope you will be good. Today I am here to participate in the contest of @josepha about the Data Analysis with Excel. It is really an interesting and knowledgeable contest. There is a lot to explore. If you want to join then:

Join Here: Data Analysis with Excel: (Charts, and data analysis techniques.)

Explain what you understand by Excel charts and discuss three (3) types of Excel charts that you know with clear screenshots

Excel charts are the graphical representations of data in Excel worksheets. They help us to analyze and interpret the data visually. Excel charts help us to present the data trends, patterns as well as comparisons in visual form. They help us to communicate the data in efficient and effective way to the audience.

Data Analysis with Excel.png

Moreover when we convert the raw data into the excel charts it looks more organized and visually appealing. And we can easily understand all the data and then we can take an informed decision. Excel provides different types of charts. Each type is specific to present the data in unique way with specific visual representation such as comparisons, trends or distribution.

All the charts located in Excel

Here are the different types of Excel charts:

Column or Bar Chart
Hierarchy Chart
Line or Area Chart
Statistics Chart
Combo Chart
Map Chart
Pivot Chart
Pie or Doughnut Chart
Bubble Chart

Column Chart

A column chart is used to compare the values of different categories. It uses the vertical bars to represent the values of the products. Column charts are ideal for showing data which changes over a period of time. And this data continuously compares different categories.

Example: If we want to compare the sales data of different products over several months then we will use Column Chart.

Column or Bar Chart

Properties:

Column Chart is showing the orders of the products based on each month.
X-axis is showing the month.
Y-axis is showing the product orders.
Column chart is comparing the number of orders of the products.

Line Chart

A line chart is used to display the trends over time. It connects data points with the lines. It makes it easy to track the changes in the trends with respect to time. It is a perfect chart for showing the time series data such as monthly sales or stock prices over time.

Example: If we want to analyze the data for a website traffic we can simply use line chart.

Properties:

Line Chart is showing the orders of the products based on each month.
Each line is showing trend of the number of orders per month.
X-axis is showing the months.
Y-axis is showing the number of orders.
Line chart is showing the trend of the number of orders of the products month wise.

Pie Chart

A Pie Chart is a circular chart. And this chart shows the proportion of the categories according to their percentage as compared to other categories. We can relate a category with the product. It is the best option from excel chart to represent the composition or percentage of the products with respect to different categories in dataset.

Example: If we are going to represent the share of the different companies in the market then we can use pie chart.

Properties:

Each slice of the circle is representing the share of the share of the products.
It requires only 1 value of the product within a specific range.
It shows the value as well as the percentage of the share in the total.
It has an option of the meta data as well to define the data.

Verify that you can read the information regarding chart location and creation in Excel and interpret it correctly using Bar Charts

Here is the complete step by step guide to prepare a Bar Chart based on the given data:

First of all open Excel.
Input the data into two columns:

One for the Country
One for Number of Steemians.
I have added 18 rows for both the columns because we have 18 rows of data

I selected all the data in the columns and then I selected Insert. It showed different options I selected Bar Chart from the charts section. It can be seen in the below screenshot.

Then the chart was added successfully on the excel page.
The vertical axis was showing the number of steemians and the horizontal axis was showing the countries list.

Chart has been added successfully. So in this way we can make a chart.

Then I added the legends and extra axis to represent the data. I changed the design and visual representation of the chart. I increased the size of the bars and gave a dark colour to the bars. Moreover I added gradient colour to the labels.

After changing teh designa nd dding some colours and other attributes I obtained the above chart. So it is the final look of the chart for the steemians which is showing the number of steemians based on the country.

Data Analysis Techniques in Excel

Excel offers different data analysis tools to analyze manipulate as well as summarize the datasets. Here are some data analysis techniques in excel:

Sorting
Filtering
Pivot Table
Conditional Formatting

Sorting

We can sort data in ascending and descending order. This sorting is done based on the values, categories or dates. In this way we can see the trends and specific data based on the defined values, categories or dates.

Filtering

We can use filter functions to filter the data. We can filter data based on the conditions such as finding specific entries that meet the specific criteria. For example we can write a condition that will show countries with only 50 steemians.

Pivot Tables

Pivot table is another important tool for sorting data. It is used to for dynamic data summarization. We can group, sum and count large datasets. Moreover we can set the data in specific regions by dragging them to the specific fields.

Conditional Formatting

This sorting technique is helpful to sort the data based on the specific conditions. If we want to show the top steemians per country with different colour formats then we can use this technique to sort the data.

Organizing Data for Analysis

To analyze data effectively in Excel we follow these steps:

Ensure data is well organized in columns with headers.
Remove duplicates if necessary.
Use sorting and filtering to clean up and structure data.
Utilize Pivot Tables for deeper insights.

These are some steps which help us to organize the data for the analysis.

Sorting Countries Alphabetically

To arrange the countries alphabetically we have to follow these below steps:

Select the range of data that includes both the Country and Number of Steemians.
Go to the Data tab and click on Sort.
In the Sort dialog box choose Country as the column to sort by and select A to Z (ascending).
Click OK and Excel will sort the list alphabetically by country name.

By applying the above sorting technique the data will be sorted alphabetically.

Here you can see that the data has been sorted in the alphabetical form where the list of the countries is starting from A with the country name Argentina and the last country name is ending with V with country Venezuela.

It is very easy and simple to sort the data in the ascending or descending order. We just need to follow the above steps and then we need to choose our required order and the data will be sorted automatically in the columns as well as on the chart.

Conclusion

Excel charts are the visual representation of the data to convey the message to the audience effectively and efficiently. There are different types of charts which are used to organize the data. Each type of the charts can be used specifically for the specific purposes depending upon the data and requirements. We can customise the charts presentation.

Moreover we can make the charts of the data easily to make the data look more organized and appealing. There are different analysis techniques to analyze the data. We can sort and filter the data depending upon the requirements. We can organise the data for the analysis according to our requirements and needs. We can change the order of the data as well.

Disclaimer: All the pictures are mine taken from the Excel desktop version.

I invite @sergeyk, @kouba01 and @suboohi to join this contest.