Home / Statistical Tools / Data Types / Data Types Details
Data Types Details¶
Excel does not enforce data types in cells—users can enter text, numbers, dates, or any combination in any cell. For statistical analysis, however, data types matter. You cannot calculate a mean on text values, and date grouping only makes sense for date/time data.
Quantum XL solves this by letting you specify the data type for each column. When you import data, Quantum XL automatically detects the most likely type for each column, but you can change it if needed.
Column Data Types¶
Quantum XL supports five column data types:
| Data Type | Description | Example Values | Use For |
|---|---|---|---|
| Numeric | Any numerical value (integers or decimals, positive or negative) | 1.5, -3.14, 100, -5 | Measurements, calculated values, any numerical data |
| Count | Non-negative whole numbers | 0, 1, 42, 1000 | Defect counts, occurrences, quantities |
| Integer | Whole numbers (positive or negative) | -5, 0, 42 | Discrete values that can be negative |
| DateTime | Date and/or time values | 1/15/2025, 3:30 PM | Time-series data, dates, timestamps |
| Nominal | Categorical text values | "Red", "Blue", "Pass", "Fail" | Categories, labels, names, groups |
Automatic Type Detection¶
When you select data for analysis, Quantum XL examines each column and automatically suggests a data type based on the values it contains.
How Detection Works¶
For each column, Quantum XL counts how many values can be interpreted as each type. If more than 50% of the non-empty values in a column can be parsed as a particular type, that type is selected.
The detection follows this priority order:
- DateTime — If more than half the values are valid dates or times
- Count — If more than half the values are non-negative whole numbers AND there are no negative integers
- Integer — If more than half the values are whole numbers (including negatives)
- Numeric — If more than half the values are numbers (including integers and decimals)
- Nominal — Default if none of the above conditions are met
DateTime Detection Details¶
Quantum XL recognizes dates in two forms:
- Actual date values — Cells formatted as dates in Excel (using Excel's date formatting)
- Date strings — Text that can be parsed as a date (e.g., "January 15, 2025", "1/15/2025", "2025-01-15")
Numbers Are Not Treated as Dates During Detection
Although Excel internally stores dates as numbers (serial dates), Quantum XL does not interpret plain numbers as dates during automatic type detection. This prevents numeric data like "42000" from being incorrectly detected as a date. If you have a column of Excel serial dates that appears as numbers, you should either format them as dates in Excel before importing, or manually set the column type to DateTime.
Example Detection Scenarios¶
| Column Contains | Detected Type | Reason |
|---|---|---|
| 1.5, 2.3, 4.7, 3.1 | Numeric | All values are decimals |
| 1, 2, 3, 4, 5 | Count | All values are non-negative whole numbers |
| -2, 0, 3, -1, 5 | Integer | Values include negative whole numbers |
| Red, Blue, Green | Nominal | All values are text |
| 1/1/2025, 1/2/2025, 1/3/2025 | DateTime | All values are dates |
| 8:30 AM, 2:15 PM, 11:00 AM | DateTime | All values are times |
| 1/15/2025 8:30 AM, 1/16/2025 2:15 PM | DateTime | All values are dates with times |
| 1, 2, "Cat", 3, 4 | Numeric | 80% are numbers (4 of 5 non-empty) |
| 1, "Dog", "Cat", "Bird" | Nominal | Only 25% are numbers |
Changing Column Types¶
You can override the automatically detected type by selecting a different type in the data source configuration. When you change a column's type, values that cannot be converted to the new type are filtered out (treated as empty).
Conversion Rules¶
When you change a column to a specific type, each value is validated against that type:
Numeric¶
- Valid: Any value that can be parsed as a number (integers or decimals)
- Invalid (filtered out): Text that is not a number, dates
Count¶
- Valid: Non-negative whole numbers, including decimals that equal whole numbers (e.g., 5.0 → 5)
- Invalid (filtered out): Negative numbers, text, decimals with fractional parts, dates
Integer¶
- Valid: Whole numbers (positive or negative), including decimals very close to whole numbers (e.g., 1.9999999 → 2 due to floating-point tolerance)
- Invalid (filtered out): Text, decimals with significant fractional parts, dates
DateTime¶
- Valid: Date/time values, text parseable as dates, Excel serial date numbers
- Invalid (filtered out): Text that cannot be parsed as a date, numbers outside the valid date range
Nominal¶
- Valid: Almost any value (converted to text)
- Invalid (filtered out): Null values, empty strings, whitespace-only strings
Data Loss When Changing Types
Changing a column's type may cause some values to be filtered out. For example, if a column contains "Dog", 1, 1.34, -1 and you change it to Count, only the value 1 will remain—"Dog", 1.34, and -1 will be treated as empty because they are not non-negative whole numbers.
Type Change Examples¶
Given a column with these values: "Dog", 1, 1.34, -1
| Change To | Values Kept | Values Filtered |
|---|---|---|
| Numeric | 1, 1.34, -1 | "Dog" |
| Count | 1 | "Dog", 1.34, -1 |
| Integer | 1, -1 | "Dog", 1.34 |
| DateTime | None | All values |
| Nominal | "Dog", "1", "1.34", "-1" (all as text) | None |
How Filtering Works with Multiple Columns¶
When a value is filtered out, it becomes empty in that cell only—rows do not shift up or get deleted. This is important to understand when your data has multiple columns, because other columns in the same row remain unchanged.
Example 1: Changing to Integer¶
Your original data:
| Row | Region | Value |
|---|---|---|
| 1 | North | 1.21 |
| 2 | South | 3 |
| 3 | East | 5 |
If you set the Value column to Integer, the decimal 1.21 cannot be converted and becomes empty:
| Row | Region | Value |
|---|---|---|
| 1 | North | (empty) |
| 2 | South | 3 |
| 3 | East | 5 |
Notice that "North" stays in row 1—the row does not shift up or disappear.
Example 2: Mixed Invalid Values¶
Your original data:
| Row | Category | Measurement |
|---|---|---|
| 1 | A | 10.5 |
| 2 | B | N/A |
| 3 | C | 20.3 |
| 4 | D | Error |
| 5 | E | 15.0 |
If you set the Measurement column to Numeric, the text values "N/A" and "Error" become empty:
| Row | Category | Measurement |
|---|---|---|
| 1 | A | 10.5 |
| 2 | B | (empty) |
| 3 | C | 20.3 |
| 4 | D | (empty) |
| 5 | E | 15.0 |
The Category column is completely unaffected—"B" and "D" remain in their original rows.
Example 3: Filtering in the First Column¶
Your original data:
| Row | Code | Description |
|---|---|---|
| 1 | 101 | Widget |
| 2 | ABC | Gadget |
| 3 | 102 | Tool |
If you set the Code column to Count, the text "ABC" becomes empty:
| Row | Code | Description |
|---|---|---|
| 1 | 101 | Widget |
| 2 | (empty) | Gadget |
| 3 | 102 | Tool |
"Gadget" remains in row 2, paired with an empty Code value.
Why This Matters
Understanding that filtering creates empty cells (not deleted rows) is important for interpreting your analysis results. Rows with empty values in a column are typically excluded from calculations involving that column, but may still be included in other parts of the analysis.
Data Cleaning Rules¶
Quantum XL automatically cleans data when it is imported:
Empty Values¶
The following are treated as empty (missing data):
- Null cells
- Cells containing only whitespace (spaces, tabs)
- Excel error codes:
#DIV/0!,#N/A,#NAME?,#NULL!,#NUM!,#REF!,#VALUE!
Whitespace Handling¶
- Leading and trailing whitespace is trimmed from column names
- Whitespace-only cell values are replaced with empty (treated as missing data)
- For Nominal columns, whitespace-only values are filtered out during analysis
Excel Date Handling¶
Excel has a known issue where it incorrectly treats 1900 as a leap year (February 29, 1900 did not exist). Quantum XL automatically corrects for this when converting dates from Excel, ensuring dates from January 1, 1900 through February 28, 1900 are handled correctly.
Choosing the Right Type¶
| If Your Data Represents... | Choose |
|---|---|
| Measurements (length, weight, temperature) | Numeric |
| Counts of items or events (defects, occurrences) | Count |
| Discrete values that can be negative (scores, differences) | Integer |
| Dates, times, or timestamps | DateTime |
| Categories or groups (colors, product names, pass/fail) | Nominal |
When in Doubt
If you're unsure which type to use, consider how the data will be analyzed:
- Numeric data can be used for calculations like mean, standard deviation, and regression
- Count data is appropriate when values represent discrete occurrences and cannot be negative
- Integer data is similar to Count but allows negative values
- DateTime data enables time-based grouping—see DateTime Grouping for details on grouping by year, month, week, day, hour, minute, or second
- Nominal data is used for grouping and categorical analysis