Data Analysis With Charts

Overview of Charts    Introduction Charts are used to analyze data graphically. Good created and formatted charts can help people and businesses make decisions based on the impact that their images provide to the users. Microsoft Excel is equipped with the Chart Wizard that allows you to create and format a chart to suit almost any scenario or need. To create a chart, you must first decide where data is coming from. This is mostly done with information available on a worksheet. Formatted charts come in various flavors for diverse goals, ranging from columns to pies, from lines to surfaces, etc. A chart is usually created on a two-dimension basis, which relates to data on columns and rows on a worksheet. Overall, once you have decided on the data source, you have a great deal of freedom on the design process. This means that you control what pieces of information will be available on the chart, how the chart will be displayed, whether to display the chart on the originating worksheet or a separate one. A chart is made of different sections. The main area allows users to view the graphical display of data, the legend explains the meaning of various colors on the chart, the title indicates what the chart is used for. When designing one, the charts in Microsoft Excel are organized by types including columns, bars, lines, pie, etc. When creating a chart, you will mainly be influenced by the kind of information you intend to analyze. Data that deals with scaled numbers such as monthly sales or student grades are suitable for column or bar charts. If your data is about percentage or fractions, you should use pie or doughnut charts. Microsoft Office ships with Microsoft Map, a special software product that allows you to create a mapped chart used to analyze data geographically, depending on the country or a region of the world. Creating a Chart When creating a chart, first find out what the chart will be used for, this allows you to get necessary data available. Once the originating data is available on a worksheet, select Microsoft Excel can recognize data format and act appropriately. To create a chart, you will primarily use the Chart Wizard which will take you step by step in creating the object. To start, select data intended to be analyzed, then on the Formatting toolbar, click the Chart Wizard button, and proceed. Charts come in different types for various purposes.  Types of Charts   Column Charts A column chart can render a very effective result when analyzing data of the same category on a defined scale. The column chart emphasizes high and low values. It helps to compare items. 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. Practical Learning: Create a Column Chart Start Microsoft Excel Open the Charts1 workbook Click the Summary Sales worksheet to make it active Select B5:E10 On the Standard toolbar, click the Chart Wizard button From the Chart Wizard Step 1 of 4, make sure the Column category is selected in the Chart Type. In the Chart Sub-type list, click the chart in the 1st column, 1st row: Clustered column. Compares values across Categories. Click Next In the Chart Wizard Step 2 of 4, make sure the Columns radio button is selected. Click Next > In the Chart Wizard Step 3 of 4, you don't need to change anything. Click Next. In the Chart Wizard Step 4 of 4, make sure that the As Object In radio button is selected. Click Finish. To reposition the chart, position the mouse on a white area on the chart, click, and drag to the right to move the chart in the lower right section of the screen Save the workbook

Bar Charts

Like the Column, the Bar chart is used to compare values of the same category on a common scale. This time, the chart is drawn horizontally. You create a Bar chart using the same process as the Column, except that you should select the Bar in step one.

Practical Learning: Create a Bar Chart

Click the Life Expectancy worksheet to make it active Select cells C4:F10 On the Standard toolbar, click the Chart Wizard button On the Chart Wizard Step 1 of 4, in the Chart Type list box, click Bar. In the Chart Sub-type list, click the chart on 1st column, 2nd row: Clustered Bar With 3-D Visual Effect Click Next In the Chart Wizard Step 2 of 4, make sure the Columns radio button is selected. Click Next In the Chart Wizard Step 3 of 4, click Next In step 4 of 4, click Finish Save the workbook

Line Charts

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 very helpful in comparing values of the same category over the same period. Fortunately, the Line chart can also be used to analyze values that don't share the same periodic variable. For example, you can use it to compare library attendance with regard 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 axis are used to analyze, one category will almost disappear from the chart; the alternative is to separate their axis on the same chart. To create a Line chart, select the values in the category or categories that will be involved. After launching the Chart Wizard, select the Line and proceed.

Practical Learning: Create a Line Chart

Click the Graphic Equation worksheet to make it active Click cell B5 and type =( Click cell B4 Type * Click cell B4 again  Type )-4 and press Enter Click cell B5 to make it active. Drag its Fill Handle to cell N5. Notice the subsequent values following the defined function have been appended Select cells B5:N5 On the main menu, click Insert -> Chart… In step 1, in the Chart Type list, click Line. In the Chart Sub-type list, click the first button (1st column, 1st row). Click Next. In step 2, in the Data Range property sheet, make sure that the Rows radio button is selected. Click the Series property page Click the button on the empty Category (X) box Select cells B4:N4 Click the button with red down pointing arrow on the Chart Wizard - Step 2 of 4 - Chart Source Data Click Next In step 3 of 4, click the Titles property page and click the Chart Title text box Type f(x) = x^2 - 4 Click the Legend property page Click the Show Legend check box to remove its check box and click Next In step 4 of 4, make sure that the As Object In radio button is selected. Click Finish.

Pie and Doughnut Charts

The Pie chart is used to show percentage and/or fraction values. You should choose it whenever your analysis involves values that altogether evaluate to a 100%, a 1, a 10, a 100, or a 1000 scale. To create a Pie chart, select the heading variables such as the age categories of library members, then select the range of values that representation the proportions such as the percentage of those who visit the library based on the previously selected variables.

An alternative to the Pie is the Doughnut chart. Sometimes this one can be used as a better alternative because it can include more than one series.

Practical Learning: Create a Column Chart

Click the Age Structure sheet Select cells C5:F5. Press and hold Ctrl, then select cells C9:F9 On the main menu, click Insert -> Chart… In step 1 of 4, in the Chart Type, click Pie. In the Chart Sub-type, click the chart on 1st column, 1st row. Click Next In step 2 of 4, make sure the Rows radio button is selected. Click Next In step 3 of 4, click the Legend property page (it should be selected already). Make sure that the Show Legend radio button is selected. In the Placement section, click Bottom Click the Data Labels property page. Click the Show Percent radio button Click Next In step 4 of 4, make sure that the As Object In radio button is selected. Click Finish.

 

The Cylinder, Cone, And Pyramid Charts

The Cone, Cylinder, and Pyramid charts can be used in the same scenario as the column and bar. 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 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 base. Both are constructed the same and can be used in similar scenarios. When analyzing data for geographical purposes, the Pyramid chart can render very dramatic effects.

Practical Learning: Creating a Cylindrical Chart

Click the First Qtr Sales worksheet to make it active Select cells B3:F6 On the Standard toolbar, click the Chart Wizard button In step 1 of 4, in the Chart Type list, click Cone. In the Chart Sub-type list, click the chart in 1st column, 3rd row Click Next In step 2 of 4, accept the default of Series In Rows. Click Next In step 3 of 4, click the Legend property page. Make sure that the Show Legend check box is selected. In the Placement section, click Top Click Next In step 4 of 4, accept all defaults and click Finish.

 

Chart Formatting

 

Introduction

A chart created with all defaults settings usually accomplishes its purpose of helping to analyze data and figures, but the default features are set only as starting points. All the formatting and emphasis needs are left to you. Besides the values and numbers on it, a chart is a graphic object whose characteristics can be enhanced to accentuate special important points of the whole process. When you decide to create a chart, you should specify what kinds of numbers and values you are dealing with.

These numbers ought to be formatted appropriately. Fortunately, even after creating the chart, you can reformat your numbers with new settings and the chart will be updated, provided the numbers are similarly recognizable. You can also change the type most of the time and get a new and/or better result. Almost any feature on the chart can be changed or reformatted at will.  Charts share most the characteristics of the graphic items that you have become familiar with. This allows you to change a chart background, to reformat the fonts on its axes, to enhance the color of its columns, bar, cones, etc through the Fill Effects feature.  Whenever a chart is selected on the worksheet, a Chart Menu Bar replaces the regular main menu. When a chart is selected, the Chart toolbar allows you to reformat various characteristics related to the chart. Just like any combination of a menu and toolbar, the Chart Menu Bar and the Chart toolbar work together to offer all formatted features of the chart.  It is also very useful to always have the Drawing toolbar handy. You can improve a chart's look by adding any of the usual drawing from shapes to special characters.

Change of Chart Type

The Chart Wizard is equipped with various kinds of charts. In each category, different sub-types are used to accomplish a unique purpose. Although they share a lot of characteristics, some charts in the sub-type can tremendously change or alter the intended goal. You should know what options are available, and then act accordingly.  Many charts can be changed from one kind to another, sometimes completely different. For example, you can change a chart from a flat column to a 3-D clustered. You can also change a chart from a bar to a cylinder, etc.  To change a chart type, right-click on an empty area of the chart and click Chart Type from the menu.

Practical Learning: Changing a Chart Type

Open the Charts2 workbook Right-click on a white area on the chart and click Chart Type… From the Source Data dialog, click the chart on 1st column, 2nd row (Clustered column with a 3-D visual effect.). Click OK. Click the Life Expectancy worksheet to activate it. Click the chart to make it active. On the Chart toolbar, click the arrow of the Chart Type button and select 3-D Pyramid Chart. Click the Graphic Equation sheet to activate it. Click the chart to select it. On the main menu, click Chart ª Chart Type…11. From the Chart Type dialog, click the Custom Type property sheet. In the Chart Type list, click Colored Lines. Notice the preview in the Sample section.12. Still in the Chart Type dialog, click the Standard Type property sheet. In the Chart Type list, make sure Line is selected. In the Chart Sub-type list, click the chart in the 1st column, 2nd row. Click OK.

 

Source Data

More often, you will test data when creating your charts. Your experience with trials and errors can help to enhance the effects of your analysis. Therefore, you should know how to change all or parts of your chart.  The chart keeps live track of the data used on it, this means your data is always updated to the minute if you change or delete it. Also, you can completely change the values that are controlling the chart. To change the source of data on a chart, right-click on a white area of the chart and click Source Data… If you want to, you can type the new range of data, but the safest way is to reselect data using the mouse. The Source Data property sheet is a modeless dialog; this allows you to select data behind the scenes while the dialog is opened. Once you have set the new range, click OK.  If you observe the Summary Sales chart, you find that one of the sales people, Alex, is not selling a lot, at least as compared to other employees; this could be due to anything. Let's assume that Alex is a manager and is not directly involves with sales, except that some time to time, he helps other employees, that's could be why his sales are low. If then he is not a sales person, we can just remove him from the chart and analyze the figures of those who have that profession.

Practical Learning: Create a Column Chart

Still in the Chart2 workbook, click the Summary Sales worksheet. Click the chart to activate it. Right-click on a white area on the chart and click Source Data… From the Source Data dialog, click the button on the right side of the Data Range edit box. Select cells B5:E7 Press and hold Ctrl. Select cells B10:E10. Release Ctrl. Click the button just under the Close button to get back to the Source Data dialog. Click OK

 

Map Charts

 

Introduction

The Microsoft Excel (or Microsoft Office) CD-ROM ships with Microsoft Map, a special software used to create maps that resemble particular charts. By default, this feature is not installed on a regular setup.  To get Microsoft Map, first test whether it is installed, if it were, you might see its icon on the Standard toolbar and under the Insert menu. If it is not, include its icon manually on the Insert menu just under Chart… and/or on the Standard toolbar (or the Formatting toolbar, etc; it is up to you). You can do that using the Customize dialog gotten from the Tools menu. Once its button is on the menu and/or a toolbar, click it, then click somewhere on the worksheet and drag it as if you were drawing a rectangle. The dialog that follows will let you know whether Microsoft Map is installed on your computer. If a dialog box tells you that you can't draw a map, click OK, insert the Microsoft Excel (or Microsoft Office) CD-ROM or DVD in the appropriate drive, run Setup, select Add/Remove Features, click the + on Microsoft Excel, select Microsoft Map, and click Update Now.  To use a chart map, first get some numbers that Microsoft Excel can recognize as belonging to a map; actually you can draw a map without numbers anywhere on your worksheet, but the computer will only let you draw a map of your choice without any special formatting. Once you have recognizable numbers, select them and any relevant data that obviously represents states, provinces, or countries. On the main menu, click Insert ª Map… (or click the Insert Map button on the toolbar). Click one corner of the mapped drawing, drag to the other corner, if Microsoft Excel can recognize the values selected, for example US states, it will prompt you to a particular map or a group of maps. If you can't recognize the values, it will present you with a list of maps where you can make your choice. Once you have selected an appropriate map, click OK. All you have to do is configure and format the map to enhance its intended purpose. Be careful when working with Microsoft Map. As wonderful as it is, it almost never accepts any Undo action.

Practical Learning: Create a Column Chart

Open the Maps1 workbook Select cell C4:D54 On the main menu, click Insert -> Map… Click and hold your mouse on the top left corner of cell E4. Drag down and right until you get to cell L19. On the Multiple Maps Available dialog, click United States (AK & HI Inset) and click OK. From the Microsoft Map Control dialog, position your mouse on the Category Shading button until the pointer becomes a grabbing tool . Click and drag to the button on top of Format on the right section Close the Microsoft Map Control dialog. Click cell H2 to deselect everything. To move the map, position your mouse on it until the point turns into a big + with four arrows, click and drag the map a little closet to the numbers on column D. View the map.

Map Formatting

A map in Microsoft is very valuable and can help to perform advanced data analysis; this is because the Microsoft Map software comes equipped with a lot of already formatted and recognizable features that you can simply decide to add to, or subtract from, the mapped area.  When using the Microsoft Map on data related to United States, for example, the software is ready to recognize US major cities, airports, and highways, etc. These are items you can add when formatting and customizing the map. You can also change the font and graphic color used by different parts or sections. Further more, you can add other parts of the world as the map is expanding of your study is widening.