Data Tables Review
In this article we will give a quick introduction to Data Tables in Excel.
Excel is an incredibly flexible tool. One of the nicest parts of Excel is that you can type in any value into any cell at any time. You can lay out your data however you like.
However, when it comes to data analysis the fact that you can do that do that is not ideal. When doing data analysis it's very important that the data has some structure to it.
The best structure for the data to have can be summarized with the following rules:
- The data is laid out into rows and columns.
- Each row of data is used for a single 'item' to be analyzed. This row may be representative of the sale of a product or a ticket added to a call centre.
- Each column of the row contains a specific property of that item (for example the date of the sale or the support person associated with the ticket).
- A column contains the same type of information for each item in the list.
If the data to be analyzed follows this basic set of rules then analysis of the data becomes much simpler. Various things about the data can then be assumed by the tools that are used for analysis. For example:
- Every column in the table has a data type and this type will be the same for all rows. The tool can then determine, based on the type of data in the column, whether or not it should be able to perform summaries on that column.
- Some data types need to be converted to other types. If you have a column that contains a shipping date, and you want to know the month of the shipment, that information can be extracted from the date.
In Excel, the way to add structure to your data is to take a range of data and convert it into a data table.
For example, suppose we have the following data range:
To turn this range of data into a data table follow the these steps:
- Highlight the range you want to turn into a table
- From the Home tab on the Ribbon select Format As Table
- Pick a style for the table
- Choose whether or not the first row of the data specifies the column names
Choose if your table already has the column names you want:
What will happen is:
- The range of data is given a name that can be referred to
- If the first row of the data has headers then these headers become the names of the columns. If the data does not have headers then a new row of data will be prepended to the table and the generated names become the column names
- Excel attempts to determine the data type for each of the columns in the table. You can change the data type yourself using Excel cell formatting.
Excel will give the table a default name (Table1, Table2, ...). It's a good idea to give the table a more descriptive name so that when you are using it in Chartnado you will be familiar with which data comes from which table. To do that, with the table selected, navigate to the Table Design tab of the ribbon:
Chartnado requires Data Tables because it takes advantage of the structure defined by the rows, columns and data.