CHARTrunner allows you to create charts using data from a variety of sources. However, CHARTrunner thinks about data with a database metaphor. In a database, a table contains many rows (or records) of data. Each row will have the same fields (or columns.) For example, if row 1 contains name, address, and zip code, then rows 2, 3, 4 etc. will also contain name, address, and zip code. Spreadsheets allow such flexibility in arranging data that this assumption of all rows being alike is often not valid.
CHARTrunner treats a spreadsheet like this: the spreadsheet itself (the .xls file) is seen as the database and each worksheet within the sheet (i.e. Sheet1, Sheet2, etc.) are seen as tables. Additionally, if you have created named regions within the spreadsheet, CHARTrunner will see these as tables.
CHARTrunner will have the easiest time with a spreadsheet formatted like this:
This is how the CHARTrunner Data definition tab might look for this data:
CHARTrunner can understand this data easily. The rows are alike, there is a single row for column headings, and there are no blank rows between the data, and no extra rows above or below.
Here is an example sheet that may give CHARTrunner some difficulty:
CHARTrunner will be able to make a chart using this data. However; since the rows summarizing Qtr 1 and Qtr 2 are different than the raw data in the other rows, the chart may not give you the results you expect. In other words, the quarterly totals will be seen as data points just like all the monthly values found in the other rows. One way to avoid this problem is to separate the quarterly totals onto a different sheet within the workbook. This way, CHARTrunner can look at the raw data using the familiar database metaphor it is based upon.
Here is one possible chart from the data above. Note that including Qtr 1 and Qtr 2 may not be the desired outcome:
Here is another example:
Note that CHARTrunner uses the first row containing text as the column name. The problem will occur when you attempt to display this chart. The data in the second row of the spreadsheet contains text and yet CHARTrunner is expecting numbers. Specifically, for the Admission errors / day column. A general rule for CHARTrunner is that if the column contains numbers - such as counts or measurements - then there should be no cells in the entire column containing text except the very first row. The next example shows one way to solve this problem without reorganizing your sheet.
In the previous example, this sheet caused problems for CHARTrunner because of the multi-row column headings and also because of the intervening blank rows. To solve the problem, highlight the cells containing only the data and give them a name in Excel. This is done with the Insert/Name menu.
To make this more useful for CHARTrunner, two things have been changed. First of all, column names have been added to row 4, which describe the data in a single row. Secondly, the highlighted region has been given the name ErrorData within Excel. Now, within CHARTrunner, the data definition tab may look like this:
This definition will allow you to chart only the specific data you request. Additionally, CHARTrunner will not be confused by extra text either above or below the raw data.
The technique mentioned in the previous example can be used in many different situations. For example, here is another sheet that is summarized on a quarterly basis.
Use Excel to name the first region as Quarter1Data and the second region as Quarter2Data. Once you save the Excel file, these will be available as tables in CHARTrunner. This allows you to create two different charts; one for each quarter.
For X-bar charts and other charts that use data that is grouped into samples or subgroups, CHARTrunner expects to find one data column for each observation in the subgroup. For example, in this sheet, treating the three columns as Measurements will result in a chart where the subgroup size is 3. (n=3)
General rules for setting up Excel sheets for use with CHARTrunner:
- Separate the raw data from the summary data. If you currently have
summary data in the midst of your raw data, put the raw data on one
sheet within Excel and do the summarizing on a different sheet (tab)
within the same workbook.
- Have a single row for column headings and leave no blank rows between the column headings and the raw data.
- Don't use the space above or below your raw data for other
spreadsheet data. Instead, move that information off to the side of the
raw data range OR put it on a different sheet within the workbook. If
you must put other spreadsheet data below your raw data, then highlight
your raw data and create a "Named Region" so that CHARTrunner can use
only the raw data contained in this region and ignore the other
spreadsheet data below your raw data.
- Don't mix data types within the same column if it is a column you
want to use in CHARTrunner. For example, don't have a cell containing
text in the middle of a column where all the other values are numbers.
- Try to keep the raw data contiguous. Avoid intervening blank rows in the midst of the raw data.
- Arrange your data so it goes down the sheet rather than across the
sheet. In other words, as you add new data, you should be adding new
rows rather than adding new columns.
- Although CHARTrunner can handle long column names, your charts and
data definition forms will look better and be less confusing if you
name your spreadsheet columns with relatively short names.
- Always have column names. If you do not, the first row of your raw
data will be ignored as it will be used to provide the column names.
- If you have data in columns A, B, and C, and you are charting data
only from column A, be aware that CHARTrunner will use the number of
rows that it finds in the column which contains the most rows. For
example, if column A contains 25 rows of data, but column B contains 50
rows of data, when you chart column A, the chart will have 25 blank
rows at the end of the data. This may cause unexpected results. The way
around this problem is to use named regions as described above.
- See the article entitled "CR: Simultaneous Use of CHARTrunner and
Excel with a Workbook" for information on concurrent use of Excel and
CHARTrunner using the same workbook file.
- On the Data definition tab of the chart definition the SQL Settings button allows you to specify whether to "Surround table names and column names with brackets…". This option should always be checked for Excel data.
