3.7 WORKING WITH TABLES AND CHARTS

Excel has features to help you manage and analyze related data. An Excel table stores information in a consistent manner, making it easier to format, sort, and filter worksheet data. Charts allow you to present information contained in the worksheet in a graphic format, which makes information easy to analyze.

3.7.1 Tables

Typically, an Excel table has only column headings and no row headings. Once you have converted the information into a table, you can sort and filter it as per your requirements.

3.7.1.1 Create Table 

To create a table you need to have information stored in columns:
  • Enter Column Headings for the table. Each heading should be in a different cell in a row. Column headings are also known as field names. The column headings should appear in a single row without any blank cells between the entries.
  • Start adding data in the row right below column heading. This is the first record/ row of the table.
  • Select any cell that contains the data
  • Click on Format as Table button in the Styles group of Home tab. A gallery of pre-defined styles of tables appears.
  • Choose your table style. A Format As Table dialog box appears which has the range of cells to be converted into the table. Also a marquee appears around all the cells of the table, indicating the selection to be converted into a table.
The range of cell to be converted into table in Figure 3.15 is for the Student Marks example that has been shown in Figure 3.11. 
  • Select My table has headers and click OK. The table created will look like as shown in Figure 3.16. 
By default, the table will be set up with the drop-down arrows in the header so that you can sort or filter the table, if you wish. Alternatively, after you have selected cell for table creation, you can also choose the Table command button in the Tables group of the Insert tab. This opens Create table dialog box with the range of cells to be converted into the table (similar to as in Figure 3.15). When you click OK, the table is created in the default style.

If you want to convert an existing Excel table back to a normal range of cells, select any cell in the table and then click the Convert to Range button on the Table Tools Design tab. All data and formatting is preserved. Using Table Tools Design context tab, you can change table style, add or delete table rows, resize table, remove duplicates, change table name and perform many more other functions on the table

3.7.1.2 Sort Data

Sorting allows you to reorder your data. To sort data:
  • Select a cell in the column you want to sort (for example, you can choose a cell in Total column to sort on total in our Students Marks example). 
  • Click the Sort & Filter command in the Editing group on the Home tab.
  • In the menu you can choose Smallest to Largest or Largest to Smallest order for sort.
  • For multi level sorting, you can also choose Custom Sort… and specify different columns and the order of sort for each in the dialog box.
Alternatively, you can also choose sort options from the Sort & Filter group in the Data Tab.

3.7.1.3 Filter Data

Filtering allows you to display only data that meets certain criteria. To filter:
  • Click the column or columns that contain the data you wish to filter.
  • On the Home tab, click on Filter button in the Sort & Filter group. Drop down arrows appear on column headings. These arrows would already be there if you are using an Excel table.  
  • Click the arrow in the column heading.
  • Choose the appropriate data value(s) to filter from the drop down menu. 
To clear the Filter, click the Sort & Filter button and choose Filter again. 

3.7.2 Charts

Charts allow you to present information contained in the worksheet in a graphic format. Excel offers many types of charts including: Column, Line, Pie, Bar, Area, Scatter and more. To create a chart we first need to have the data. 

3.7.2.1 Add Data 

We will use the following sample data in our example. We will create a graph to compare daily attendance of two classes.

This is the source data for our chart, since it will be based on this data. Any change in the source data will automatically be reflected in the chart. 

3.7.2.2 Create Chart

  • Select the cells that that contain the data you want to use in the chart, including the column titles and the row labels.
  • Click the Insert tab on the Ribbon. 
  • Click on one of the chart options from the Chart group. In this example, we will use the Columns option. 
  • Select a type of chart you want to create from the list. For our example, we will use a 3-D Clustered Column. The chart appears in the worksheet. Also notice Design, Layout and Format context tabs under Chart Tools:
Design Tab: has commands to control the chart type, layout, styles, and location of the chart.
Layout Tab: has commands to control pictures insert, shapes and text boxes, labels, axes, background, and analysis. 
Format Tab: has commands to modify shape styles, word styles and size of the chart. 

3.7.2.3 Apply Layout

 To apply the layout:
  • Click your chart. The Chart Tools become available. 
  • Choose the Design tab.
  • Click the Quick Layout button in the Chart Layout group. A list of chart layouts appears.
  • Select the layout. Excel applies the layout to your chart. We have chose layout 9 for our example.

3.7.2.4 Add Labels

Add appropriate Chart title and Axis title to the Chart.
  • Click on the Chart Title and Axis title labels and change the labels appropriately.
  • You can also add more labels, if required. Click on Layout Tab. Choose Textbox from Insert group. Click on Text box and place it on the chart. We have added a text box in our example to add the week (March1-7, Figure 3.18) 

 3.7.2.5 Switch Data

Switch Data rotates the data displayed on the x and y axes. It rotates row data to column data and vice versa.
  • Choose the Design tab. 
  • Click the Switch Row/Column button in the Data group. Excel changes the data in your chart.

3.7.2.6 Change Chart Type, Chart Style or Data Range

You can use the Design context tab to change Chart Type, Chart Style or Data Range.
  • Select Design Tab.
  • Choose the commands as per you requirements:
  • Click on change Chart Type from the Type group. Select the required type from the dialog box that opens.
  • Click Select Data command from the data group. You can make the required changes to the data in the dialog box that opens. 
  • You can choose any of the chart styles available in the Style group. 

3.7.2.7 Move the Chart to a Different Worksheet 

  • Select the Design tab.
  • Click the Move Chart command. A dialog box appears. The current location of the chart is selected. 
  • Select the desired location for the chart (the desired location can be an existing worksheet or a New Sheet).