Data Analysis With Charts

Charts Fundamentals

Introduction 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. Creating a Chart 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.   Practical Learning: Creating a Chart Open the Altair Realtors1.xlsx workbook Click the Sales by Types tab and select Cells B5:E6 On the Ribbon, click Insert In the Chart section, click the Column button. In the menu that appears, click the very first option on the top-left side: Clustered Column Save the workbook The Characteristics of a Chart   A Chart and its Container 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. Practical Learning: Moving a Chart Right-click the border of the chart and click Move Chart Click the top text box and replace the name with Sales by Types Summary Click OK The Sections of a Chart 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. Editing the Values of a Chart 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. Practical Learning: Editing the Values of a Chart Click Sales by Types. Click Cell D6 and type 5 Click Cell E6 and type 18 Click the Sales by Types Summary tab sheet and notice that the chart has changed Save the workbook A Chart's Legend 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: To change the font: Use the buttons on the toolbar Click Font... and use the Font dialog box To change other aspects of the legend, click Format Legend... This would display the Format legend dialog box Make the changes, and click Close. If you do not want to use a legend, you can delete it. To remove the legend: Click the legend and press Delete Right-click the legend and click Delete Practical Learning: Using a Chart's Legend Right-click Count From the toolbar that appears, click the arrow of the Font Name combo box and select Garamond Right-click Count again. On the toolbar, click the arrow of the Font Size combo box and select 20 Click the Bold button Click the arrow of the Color combo box and select Red Right-click Count and click Format Legend... In the left list, make sure Legend Options is selected. In the right list, under Legend Position, click the Bottom radio button Click Close The Title of a Chart 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: To change the font: Use the buttons on the toolbar Click Font... and use the Font dialog box To change other aspects of the title, click Format Chart Title... This would display the Format Chart Title dialog box Make the changes, and click Close. If you do not want to use a title, you can delete it. To remove the title: Click the title and press Delete Right-click the title and click Delete Practical Learning: Formatting a Chart's Title Click inside the Count label on top and edit it to display Sales Per Type Right-click the title From the toolbar that appears, click the arrow of the Font Name combo box and select Courier New. Click the arrow of the Font Size combo box and select 28 Click the arrow of the Color combo box and select Red Save the workbook Chart Figures 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. Practical Learning: Formatting a Chart's Shapes Right-click the left rectangle on the chart and click Format Data Point... In the Format Data Point dialog box, in the left list, click Border color In the right list, click Solid Line Click the Color button and click Dark Blue, Text 2, Darker 25% In the left list, click Fill In the right list, click Gradient Fill Click the Preset Color button and click Ocean In the Type combo box, select Rectangle Click the Direction button and click the From Center button Click Close On the chart, right-click the middle rectangle and click Format Data Point... In the Format Data Point dialog box, in the left list, click Fill In the right list, click Picture or Texture Fill Click the Texture button and click Granite Click Close Save the workbook Chart's Labels 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. Practical Learning: Adding Labels to a Chart On the chart, right-click the right box and click Add Data Label Save the workbook The Chart's Background 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. Practical Learning: Formatting a Chart's Walls Right-click a white area of the chart and click Format Chart Area... In the left list, make sure Fill is selected. In the right list, click Picture or Texture Fill Click the Texture button and click Newsprint Click Close On the chart, right-click a white are between two boxes and click Format Plot Area In the left list, make sure Fill is selected. In the right list, click No Fill and click Close Save the workbook Types of Charts   Introduction 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.  The Types of Values of a Chart 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. Types of Charts: Column Charts   Introduction As we have seen already, a column chart creates vertically standing rectangular boxes. Each box can be used to represent an integral, a decimal, or a percentage value. When creating such a chart, you specify the values to use. Microsoft Exccel determines the highest and the lowest values. When the boxes are drawn, each must fit in the area allocated for the chart. As a consequence, the box that represents the highest value is also the tallest while the box for the lowest value is the shortest. Microsoft Excel draws the other boxes between these extremes but proportionately. Therefore, a column graph is used to compare values in increment. Double-Column Charts 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. 3-D Column Charts 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. Bar Charts A bar chart uses the same theories and scenarios as the column chart except that its rectangular boxes are horizontal. Like the column chart, the bar chart is used to compare values of the same category on a common scale. You create a bar chart using the same process as the Column, except that you should select the Bar Chart on the Ribbon.   As done for the column chart, when specifying the values of a bar chart, use a series of cells that have frequent occurrences of the same values.   Types of Charts: Line Charts   Introduction 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.  Trend Lines 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. Types of Charts: Pie Charts   Introduction A Pie chart is used to show percentage and/or fractional values. When creating, you can choose the values as you see fit. Microsoft Excel would identify each value in the series and create categories for them. After getting the categories, the application would calculate the percentage for each category based on the sum of all the values, the total count of categories, and the fraction that each category shares. The default appearance of a pie chart is a circle with each category taking a pie in the whole. One of the variances of the chart displays in three dimensions that uses two ellipses. The top ellipse is the most visible and shows the format of each chart. Only part of the bottom ellipse is shown. Doughnut Charts A Doughnut chart is an alternative to the Pie chart as both use the same types of values. The main difference between both types of charts is that a Doughnut chart can include more than one series of values.