A picture is worth of thousand words; a chart is worth of thousand sets of data. In this tutorial, we are going to learn how we can use graph in Excel to visualize our data. Show What is a chart?A chart is a visual representative of data in both columns and rows. Charts are usually used to analyse trends and patterns in data sets. Let’s say you have been recording the sales figures in Excel for the past three years. Using charts, you can easily tell which year had the most sales and which year had the least. You can also draw charts to compare set targets against actual achievements. We will use the following data for this tutorial. Note: we will be using Excel 2013. If you have a lower version, then some of the more advanced features may not be available to you.
Types of Charts in MS ExcelDifferent scenarios require different types of charts. Towards this end, Excel provides a number of chart types that you can work with. The type of chart that you choose depends on the type of data that you want to visualize. To help simplify things for the users, Excel 2013 and above has an option that analyses your data and makes a recommendation of the chart type that you should use. The following table shows some of the most commonly used Excel charts and when you should consider using them.
The importance of charts
Step by step example of creating charts in ExcelIn this tutorial, we are going to plot a simple column chart in Excel that will display the sold quantities against the sales year. Below are the steps to create chart in MS Excel:
To get the desired chart you have to follow the following steps
You should be able to see the following chart Tutorial ExerciseWhen you select the chart, the ribbon activates the following tab Try to apply the different chart styles, and other options presented in your chart. Download the above Excel Template SummaryCharts are a powerful way of graphically visualizing your data. Excel has many types of charts that you can use depending on your needs. Conditional formatting is also another power formatting feature of Excel that helps us easily see the data that meets a specified condition Many chart types are available to help you display data in ways that are meaningful to your audience. Here are some examples of the most common chart types and how they can be used.
Data that is arranged in columns or rows on an Excel sheet can be plotted in a column chart. In column charts, categories are typically organized along the horizontal axis and values along the vertical axis. Column charts are useful to show how data changes over time or to show comparisons among items. Column charts have the following chart subtypes:
Data that is arranged in columns or rows on an Excel sheet can be plotted in a line chart. Line charts can display continuous data over time, set against a common scale, and are therefore ideal to show trends in data at equal intervals. In a line chart, category data is distributed evenly along the horizontal axis, and all value data is distributed evenly along the vertical axis. Line charts work well if your category labels are text, and represent evenly spaced values such as months, quarters, or fiscal years. Line charts have the following chart subtypes:
Data that is arranged in one column or row only on an Excel sheet can be plotted in a pie chart. Pie charts show the size of items in one data series, proportional to the sum of the items. The data points in a pie chart are displayed as a percentage of the whole pie. Consider using a pie chart when you have only one data series that you want to plot, none of the values that you want to plot are negative, almost none of the values that you want to plot are zero values, you don't have more than seven categories, and the categories represent parts of the whole pie. Pie charts have the following chart subtypes:
Data that is arranged in columns or rows on an Excel sheet can be plotted in a bar chart. Use bar charts to show comparisons among individual items. Bar charts have the following chart subtypes:
Data that is arranged in columns and rows on an Excel sheet can be plotted in an xy (scatter) chart. A scatter chart has two value axes. It shows one set of numeric data along the horizontal axis (x-axis) and another along the vertical axis (y-axis). It combines these values into single data points and displays them in irregular intervals, or clusters. Scatter charts show the relationships among the numeric values in several data series, or plot two groups of numbers as one series of xy coordinates. Scatter charts are typically used for displaying and comparing numeric values, such as scientific, statistical, and engineering data. Scatter charts have the following chart subtypes:
Data that is arranged in columns or rows on an Excel sheet can be plotted in an area chart. By displaying the sum of the plotted values, an area chart also shows the relationship of parts to a whole. Area charts emphasize the magnitude of change over time, and can be used to draw attention to the total value across a trend. For example, data that represents profit over time can be plotted in an area chart to emphasize the total profit. Area charts have the following chart subtypes:
Data that is arranged in columns or rows in a specific order on an Excel sheet can be plotted in a stock chart. As its name implies, a stock chart is most frequently used to show the fluctuation of stock prices. However, this chart may also be used for scientific data. For example, you could use a stock chart to indicate the fluctuation of daily or annual temperatures. Stock charts have the following chart sub-types:
Data that is arranged in columns or rows on an Excel sheet can be plotted in a surface chart. As in a topographic map, colors and patterns indicate areas that are in the same range of values. A surface chart is useful when you want to find optimal combinations between two sets of data. Surface charts have the following chart subtypes:
In a radar chart, each category has its own value axis radiating from the center point. Lines connect all the values in the same series. Use radar charts to compare the aggregate values of several data series. Radar charts have the following chart subtypes:
You can use a Map Chart to compare values and show categories across geographical regions. Use it when you have geographical regions in your data, like countries/regions, states, counties or postal codes. For more information, see Create a map chart.
Funnel charts show values across multiple stages in a process. Typically, the values decrease gradually, allowing the bars to resemble a funnel. For more information, see Create a funnel chart.
The treemap chart provides a hierarchical view of your data and an easy way to compare different levels of categorization. The treemap chart displays categories by color and proximity and can easily show lots of data which would be difficult with other chart types. The treemap chart can be plotted when empty (blank) cells exist within the hierarchal structure and treemap charts are good for comparing proportions within the hierarchy. There are no chart sub-types for treemap charts. For more information, see Create a treemap chart.
The sunburst chart is ideal for displaying hierarchical data and can be plotted when empty (blank) cells exist within the hierarchal structure . Each level of the hierarchy is represented by one ring or circle with the innermost circle as the top of the hierarchy. A sunburst chart without any hierarchical data (one level of categories), looks similar to a doughnut chart. However, a sunburst chart with multiple levels of categories shows how the outer rings relate to the inner rings. The sunburst chart is most effective at showing how one ring is broken into its contributing pieces. There are no chart sub-types for sunburst charts. For more information, see Create a sunburst chart.
A waterfall chart shows a running total of your financial data as values are added or subtracted. It's useful for understanding how an initial value is affected by a series of positive and negative values. The columns are color coded so you can quickly tell positive from negative numbers. There are no chart sub-types for waterfall charts. For more information, see Create a waterfall chart.
Data plotted in a histogram chart shows the frequencies within a distribution. Each column of the chart is called a bin, which can be changed to further analyze your data. Types of histogram charts
More information is available for Histogram and Pareto charts.
A box and whisker chart shows distribution of data into quartiles, highlighting the mean and outliers. The boxes may have lines extending vertically called “whiskers”. These lines indicate variability outside the upper and lower quartiles, and any point outside those lines or whiskers is considered an outlier. Use this chart type when there are multiple data sets which relate to each other in some way. For more information, see Create a box and whisker chart.
Data that is arranged in columns or rows on an Excel sheet can be plotted in a column chart. In column charts, categories are typically organized along the horizontal axis and values along the vertical axis. Column charts are useful to show how data changes over time or to show comparisons among items. Column charts have the following chart subtypes:
Data that is arranged in columns or rows on an Excel sheet can be plotted in a line chart. Line charts can display continuous data over time, set against a common scale, and are therefore ideal to show trends in data at equal intervals. In a line chart, category data is distributed evenly along the horizontal axis, and all value data is distributed evenly along the vertical axis. Line charts work well if your category labels are text, and represent evenly spaced values such as months, quarters, or fiscal years. Line charts have the following chart subtypes:
Data that is arranged in one column or row only on an Excel sheet can be plotted in a pie chart. Pie charts show the size of items in one data series, proportional to the sum of the items. The data points in a pie chart are displayed as a percentage of the whole pie. Consider using a pie chart when you have only one data series that you want to plot, none of the values that you want to plot are negative, almost none of the values that you want to plot are zero values, you don't have more than seven categories, and the categories represent parts of the whole pie. Pie charts have the following chart subtypes:
Data that is arranged in columns or rows on an Excel sheet can be plotted in a bar chart. Use bar charts to show comparisons among individual items. Bar charts have the following chart subtypes:
Data that is arranged in columns or rows on an Excel sheet can be plotted in an area chart. By displaying the sum of the plotted values, an area chart also shows the relationship of parts to a whole. Area charts emphasize the magnitude of change over time, and can be used to draw attention to the total value across a trend. For example, data that represents profit over time can be plotted in an area chart to emphasize the total profit. Area charts have the following chart subtypes:
Data that is arranged in columns and rows on an Excel sheet can be plotted in an xy (scatter) chart. A scatter chart has two value axes. It shows one set of numeric data along the horizontal axis (x-axis) and another along the vertical axis (y-axis). It combines these values into single data points and displays them in irregular intervals, or clusters. Scatter charts show the relationships among the numeric values in several data series, or plot two groups of numbers as one series of xy coordinates. Scatter charts are typically used for displaying and comparing numeric values, such as scientific, statistical, and engineering data. Scatter charts have the following chart subtypes:
A bubble chart is a kind of xy (scatter) chart, where the size of the bubble represents the value of a third variable. Bubble charts have the following chart subtypes:
Data that is arranged in columns or rows in a specific order on an Excel sheet can be plotted in a stock chart. As its name implies, a stock chart is most frequently used to show the fluctuation of stock prices. However, this chart may also be used for scientific data. For example, you could use a stock chart to indicate the fluctuation of daily or annual temperatures. Stock charts have the following chart sub-types:
Data that is arranged in columns or rows on an Excel sheet can be plotted in a surface chart. As in a topographic map, colors and patterns indicate areas that are in the same range of values. A surface chart is useful when you want to find optimal combinations between two sets of data. Surface charts have the following chart subtypes:
Like a pie chart, a doughnut chart shows the relationship of parts to a whole. However, it can contain more than one data series. Each ring of the doughnut chart represents a data series. Doughnut charts have the following chart subtypes:
In a radar chart, each category has its own value axis radiating from the center point. Lines connect all the values in the same series. Use radar charts to compare the aggregate values of several data series. Radar charts have the following chart subtypes:
|