Data Analysis With Charts
A chart is a technique of displaying data using pictures and graphical representations instead of numbers or simple words. It works by drawing figures that would represent numbers, adding colors and shapes to the information presented.
Good created and formatted charts can help people and businesses make decisions based on the impact that the images provide. Data analysis on charts is done using graphics that present pictures. In addition to the pictures, you can add words, also called labels to indicate what the pictures represent.
Because a chart is used to present data in a graphical format, before creating a chart, you should plan it. That is, you should prepare it. There are two pieces of information you should have before starting: The numbers that you want to represent and the type of chart you want to use.
The information used to create a chart usually come from two or more cells of a worksheet. Before creating a chart, you should prepare it so it can be easily recognizable. Data used on a chart can be made of natural numbers or percentage values. You can also present a series of repeating words and let the chart engine count the occurrences of such words before using them as numbers.
To start a chart, after selecting the cells, on the Ribbon, click Insert. In the Chart section, click one of the buttons to display the available types of charts, then select one of them. After making your selection, a chart would be created for you.
To present its information more efficiently, a chart is made of different sections. The main area allows users to view the graphical display of data. A legend explains the meaning of various colors on the chart. A title indicates what the chart is used for.
A chart cannot reside on its own. It needs a container which is a worksheet. By default, after selecting the cells and starting to create a chart, the chart is created in the same worksheet where the values were selected. If you want, you can put the chart in another worksheet. To do that, right-click the chart and click Move Chart... A dialog box would display:
To put the chart in an existing worksheet, select it in the Object In combo box. To display it in a brand new worksheet, click the New Sheet radio button and specify the name in the top text box.
To present its information, a chart is made of various sections:
Most or every one of these aspects can be hidden, displayed or changed. To perform any action on these parts, you can right-click a section or an object on the chart and click a Format... option.
As mentioned already, to create a chart, you select values from some cells of a worksheet. When analyzing data using a chart, you may want to use "What If" scenarios. For example, if you are viewing the numbers of students per gender in a school and one gender is predominant, you may want to view the tendency if the number of members were the same for both members, if the members of the predominant gender were even more, or if the members of the other gender were predominant. Therefore, during data analysis, you can change the values used by the chart.
To change the values used for a chart, click the appropriate cell on the worksheet and type the desired value. When you do this, the chart would be automatically updated.
To show what the graphics on a chart represent, a chart is accompanied by an object on a side called a legend. The legend is made of at least one small square box of the same color of at least one of the graphics on the chart:
In this case, the legend contains one item labeled Total Sales. Because the legend represents a graphic of the chart, when you make a change on that graphic, the legend is updated. Still, you can change the legend if you want.
To make changes to the legend, on the chart, right-click the legend:
Make the changes, and click Close.
If you do not want to use a legend, you can delete it. To remove the legend:
To indicate what it is used for, a chart can be equipped with a title. The title is a string that typically displays in the top section of a chart. In some (rare) cases, a title can also be positioned on the left or the right sides, above or below the chart. To move the title, on the chart, click and drag the title in the desired direction.
To change the text of the title, click inside it and edit its text. To format the title:
To make changes to the legend, on the chart, right-click the legend:
Make the changes, and click Close.
If you do not want to use a title, you can delete it. To remove the title:
To represent its numbers, a chart draws some geometric figures, depending on the type of chart. These figures can be rectangles, pie slices, triangles, cones, etc. To paint these figures, by default, the chart engine uses some colors from its own list. You can either change these colors or apply some preset drawings available. You can also design and use any custom picture to paint the chart's shapes.
To format the geometric figures of a chart, you can right-click one of them and click Format Data Series...
By default, when you have just created a chart that uses one column for its values, Microsoft Excel applies the same formatting, such as the same color, to all of its figures. You can keep that common color or change the color of each individual shape. To change the formats of a shape, right-click it and click Format Data Point. This would display the Format Data Point dialog box:
Make the changes and click Close.
By default, when a chart is drawn, it is equipped with shapes and a separate legend. If you want, you can display the value of each part and possibly its name close to it. This is done through a label. On a large chart, a label can also be used in the absence of a legend. In fact, you can delete a legend and simply make use of a label.
To add the labels to a chart, right-click a shape on the chart and click Add Data Label. To remove an existing chart label, right-click it and click Delete.
You can enhance the appearance of a chart by drawing a background wall behind it. The wall is just a graphical object. By default, the background of a chart is painted in white. You can use a different color to paint it, a design pattern or a picture to cover it.
To add background wall to a chart, right-click the chart and click Format Chart Area... This would open the Format Chart dialog box where you can make the necessary changes.
In our introduction to charts, we created one with standing rectangular boxes. This is called a column chart and is only one of the types of charts available. Microsoft Excel (indeed Microsoft Office) provides many other flavors you can use, depending on the type of analysis you want to perform. To select a type of chart, after selecting the cells on a worksheet, on the Ribbon, click Insert. In the Chart section, click one of the buttons to see its options and select from the list.
After creating a chart, to change its type, right-click the chart and click Change Chart Type. This would open the Change Chart Type dialog box where you can select a different type.
When you select the cells of a worksheet to create a graph, by default, the application counts the number of occurrences of each value, especially if you select some string-based cells. Depending on the type of chart as we will see in the next few sections, some charts can use regular numbers while some others are better with percentage values. Fortunately, instead of trying to figure out how to perform the calculations yourself, Microsoft Excel can do it for you.
The classic column chart is made of flat bars that simply illustrate maximal, minimal, and in-between values. One of the options allows you to create a 3-dimensional look of the chart and further accentuate the colors and/or other graphic effects. To enhance an effective analysis, you can create a real 3-D chart that shows data and graphics in perspective.
Another variance of the column chart is to show two columns for each sample value. For example, in our real estate application, imagine that you have the market value for each property and the value the property was sold for, one of the effects of a sale is that some properties would be sold for the same market value, some properties would be sold for a lower value (for example, the seller may want to get rid of the house and be willing to assist the buyer with a down payment and closing cost, thus lowering the price of the house), some other properties could be sold higher than the advertised value (for example, a customer may want to insist on having the house, even at a high price, or too many people could be suddenly interested in the same house, this could raise the price). At the end of the year, when doing an inventory or an evaluation of some sort, you may want to know what houses sold high and which ones sold low.
So far, we have used what are referred to as flat charts. They can be drawn on a 2 dimensional coordinate system. To enhance the appearance of a chart, you can draw it in 3 dimensional coordinate system (x, y, z). If you want to draw 3-D chart, you must select three series of cells. Two of the series should hold categories of values and the other one can hold unique values. The two series that hold categories of values should have corresponding values so that, a value from one series can have corresponding values in the second series. Here is an example. Imagine that, in a real estate database, you have been selling properties over a period of 1, 2, 3 or more years. The properties sold are categorized as single families, townhouses, and condominiums. Obviously in a particular year, you sell properties of all kinds. On the other hand, each property can have its own value. You can use these three sets of values to create a 3-D chart.
The cone, cylinder, and pyramid charts can be used in the same scenario as the column char. Their 3-D visual effect can enhance the overall analysis of data.
The cylinder chart creates long circular boxes of the same base on both ends. It can be enhanced with good formatted Fill Effects. This chart is suitable for industry, manufacturing analysis, and predictions.
The cone chart is made of a circular base topped by a higher point. When used with various data, the higher values will have the complete cone while the lower values will share portion of the geometric figure. The cone chart should be used with values that can take advantage of its graphing dimensions.
The pyramid chart resembles the cone chart with a difference on their respective bases. Both are constructed the same and can be used in similar scenarios.
When creating the chart, there are many aspects you can change for it. For example, you may have a chart where the figures in the front seem to hide those in the back:
Or there is too much room on one side. You can rotate the chart. To do this, click one of the borders of the walls of the chart to select its frame. Then click one of the handles on the frame and hold the mouse down. The actual frame of the chart would appear:
You can then rotate the chart in the direction of your choice. You can keep doing this, releasing the mouse to preview, then rotating again, until you get the desired orientation.
If you created the chart as one shape (cylinder, cone, or pyramid) but want to use another shape, you can change it.
A line chart is used to analyze ups and downs of a tendency in a range of values. You can define it with one series of values where you will judge the evolution of an item over a period. When used with more than one series, this chart can be helpful in comparing values of the same category over the same period. The line chart can also be used to analyze values that do not share the same periodic variable. For example, you can use it to compare library attendance with regards to the real population number (which could be in hundreds of thousands or millions) with the number of people attending the library. In the latter situation, if the same axes are used to analyze, one category will almost disappear from the chart; the alternative is to separate the axes on the same chart.
A trend line is a line added to a chart created as a column or else. It can be used to show the high points of the various values on a chart. A trend line is not a type of chart. It is only added to an existing chart to accentuate its tendencies.
To add a trend line to a chart, after creating a chart, right-click one of the column categories and click Add Trendline... This would add a line to the chart and open the Format Trendline dialog box.
After creating a trend line, you can change its characteristics. To do this, right-click the trend line and click Format Trendline... This would open the Format Trendline dialog box that you can use for various reasons, including specifying the color of the line.
|Previous||Copyright © 2008||Next|