Basic Visualization and Graphing Using Excel

Excel provides many options for generating graphs and other visualizations to assist in data science investigations.

The menu in Figure A9 shows some of the many options available within Excel for graphing.

Figure A9 A Menu of Various Graphs Available within MS Excel (Used with permission from Microsoft)

Graphically Represent Data Using Excel

Graphical representation of data, or data visualization, can be done easily with Excel. Let’s draw a bar chart as an example. You can start by simply clicking Insert > Bar chart > Clustered Column icon (Figure A10).

Figure A10 MS Excel’s Bar Chart Icon (Used with permission from Microsoft)

Once the icon is clicked, you will see an arbitrary bar chart. It could be just a plain whitespace too—it really depends on what cell was selected when you clicked the icon.

Now click the chart area and it will reveal two new tabs—Chart Design and Format. Go to the Chart Design tab and click Select Data (Figure A11).

Figure A11 Select Data Menu on MS Excel (Used with permission from Microsoft)

In the Select Data window, define Chart data range by highlighting A1:F13 (i.e., the entire table). This will auto-fill the rest of the pop-up window (Figure A12).

Figure A12 Select Data Pop-Up Window on MS Excel (Used with permission from Microsoft)

Three entries—Height, Weight, Age—refer to each group of bars. Each group will have 13 bars, each of which represents different baseball players. For now, let’s plot only Height. You can do so by removing the other two by clicking the “-” button under the entry list.

Click Ok and the bar chart is generated as Figure A13. Notice that columns A through C are displayed as the x-axis labels, as indicated on the pop-up window (see the Horizontal [Category] axis labels part in Figure A12).

Figure A13 The First Bar Chart with Three Kinds of x-Axis Labels (Position, Team, and Name)

If you wish to show only the player names, go back to the Select Data window and update the Horizontal axis labels to be just A2:A13 (Figure A14).

Figure A14 Keeping Only Name on the x-Axis Label (Used with permission from Microsoft)

The resulting plot will only have player names along the x-axis (Figure A15).

Figure A15 The Second Bar Chart with Only Name Displayed on the x-Axis

Changing the formatting of different chart elements is easy in Excel as well. You can 1) click the element to change the text or drag to move the position of the element or 2) double-click the element in the corresponding pop-up menu. Figure A16 shows the pop-up menu for the chart title element. Notice that you can change different aspects of the presentation with the title element, such as Fill and Border.

Figure A16 Changing the Formatting of the Bar Chart (Used with permission from Microsoft)

EXAMPLE A.5

Problem

Using MS Excel, draw a scatterplot between Weight (x-axis) and Height (y-axis). A scatterplot is a plot that depicts each data point as a dot. The location of a dot is typically determined by the x- and y-axis.

Solution

There is an icon for scatterplots under Insert (see Figure A17). On the Select Data pop-up window, select D1:E13 since the weights and heights are in column D and E, as shown in Figure A17 and Figure A18.

Figure A17 Scatterplot Icon (Used with permission from Microsoft)

Figure A18 Select Data for Drawing a Scatterplot (Used with permission from Microsoft)

The resulting scatterplot is shown in Figure A19. Notice that the numbers along the x-axis are heights, while those along the y-axis are weights.

Figure A19 The Initial Scatterplot

You can change the chart title and the axis titles by double-clicking the title text and the axes themselves, as in Figure A20.

Figure A20 Changing the Title of the Scatterplot (Used with permission from Microsoft)

Once either the x– or y-axis is double-clicked, a different menu will show up and there you can add axis titles, as illustrated in Figure A21.

Figure A21 Changing the Axis Title of the Scatterplot (Used with permission from Microsoft)