OmniDocWhat is the difference between Excel chart series and categories? How to divide data into series?
Explain clearly the meaning of series and categories in Excel charts, explain which side becomes a series when data is arranged in rows or columns, and provide steps for checking data layout and methods for correcting common errors.
Key points of this article
- A series is a set of data to be compared, has a name in the legend, and is represented by bar color, line, or pie chart sector.
- Categories are grouping labels on the horizontal axis, such as months and regions. Each category corresponds to a data point for each series.
- Excel usually determines whether the series is generated in rows or columns based on the shape of the selected area. When the number of rows exceeds the number of columns, the series is generally generated in columns.
- Numbers such as years and serial numbers can easily be mistaken for numerical series, and there may be an unwanted series in the total column.
- Before drawing the graph, check whether the table header is in the first row and the label is in the first column. This can avoid most misalignment of the horizontal axis and the legend.
You select a table in Excel and insert it into a chart. As a result, the horizontal axis becomes 1, 2, and 3, or each month becomes a separate column, and the legend does not match up. Many people subconsciously redo the table, but in fact, most of them fail to distinguish between "series" and "category". These two words often appear in chart settings, but few people explain clearly what they are and how to arrange the data to the correct position.
This article explains the difference between series and categories in Excel charts, and explains which part becomes a series and which part becomes a category when the data is arranged in rows or columns. After reading, you can first determine how the table should be arranged. After inserting the chart, use a few check items to confirm that the horizontal axis, legend, and values are all in line with expectations.
How to distinguish between series and categories? One-sentence conclusion: Series is a set of data to be compared, and category is the grouping label on the horizontal axis. Which side of the table is the indicator name and which side is the grouping name determines which side becomes the series.
Remember this sentence first, and then compare your situation with the table below.
| your situation | Recommended practices | reason |
|---|---|---|
| I want to compare the sales of several products in each month. The months are arranged in one column and the products are arranged in multiple columns. | Months as categories (horizontal axis) and each product as a series | The horizontal axis shows time changes, and the legend differentiates products, making reading the chart more intuitive. |
| Want to compare numbers from different regions in the same month, with the regions listed in one column | Region as category, month as series | Columns of several months are displayed side by side under each region, which is suitable for looking at regional differences. |
| The first column is year numbers like 2021 and 2022 | Treat years as categories and manually specify horizontal axis labels | Numbers are easily regarded as numerical series, and the horizontal axis will become 1, 2, and 3 |
| There is only one set of data and I want to make a pie chart | Only one series is retained, and the category is the name of each item | Pie charts are used to express the composition of a set of data. When there are multiple series, only one set can be seen. |
| There is a column "Total" on the right side of the table | Exclude total column from selection before inserting chart | The total column will be treated as a new series, with an extra item in the legend. |
What is a series and what is a category
Series is a group of values in a chart expressed in the same style. It usually corresponds to a column or a row in the table. It has its own name and is displayed in the legend. In a column chart, the series appears as bars of the same color; in a line chart, the series appears as a line. For example, a whole column of numbers for "Product A" in the sales table is a series.
Category is the grouping label on the horizontal axis, answering "how to group the data." It is usually words or numbers such as month, region, quarter, department. Under each category, one or more data points are placed depending on the number of series. For example, if the month is used as the category and the product is the series, there will be a column for each product under "January".
Here's where it gets confusing: in a column chart, the series determines the color and the category determines the position. The former answers "who to compare" and the latter answers "what to rank". Swap the two, and the meaning expressed by the chart completely changes.
How does Excel determine whether a series is generated in rows or columns?
When Excel inserts a chart, it does not really understand the meaning of the table, but judges it based on the shape and label position of the selected area.
1. Judge by area shape
A common rule is: when the number of rows in the selected range is more than the number of columns, Excel usually treats each column as a series; when the number of columns is more than the number of rows, Excel usually treats each row as a series. For example, if 12 months are arranged in 12 rows and 3 products are arranged in 3 columns, 3 series will be obtained by default, with 12 data points in each series.
This is just the default behavior, and deviations may occur when the number of rows and columns are close, so be sure to take a look at the legend and horizontal axis after inserting.
2. Look at the table header and label position
Excel also looks at the upper left cell and the first row and column. When the first line is text, it is often regarded as the series name; when the first column is text, it is often regarded as the category label. Leaving the upper left corner blank is a common way to tell the software "this is the header area".
Before making a graph, it is recommended to ensure three points: the first row is the indicator name, the first column is the group name, the upper left corner remains blank, the data area is continuous, and there are no empty rows or columns in the middle.
3. Misjudgment of numbers
This is a common source of errors. The year, number, and serial number themselves are numbers, and Excel may treat them as numerical values. If the first column is 2021, 2022, 2023, the software may treat it as a data series, so the horizontal axis becomes 1, 2, 3, or there is an extra meaningless column.
The solution is to explicitly tell the chart that this column is a category: in the chart's data source settings, specify the horizontal axis label as the year column, and make sure it's not in the series list.
Common misunderstandings and areas of confusion
Misunderstanding 1: The text on the legend must be the category
When you see "January" and "February" in the legend, you think that the month is a series, which is often the opposite of the ranks. The method of judgment is simple: if you want to compare a product, make the product a series, and the legend shows the product name; if you want to see the time trend, make the month a category and show the month on the horizontal axis.
misunderstanding 2: the more data you select, the better
Select the entire table, often with total columns, memo columns, and blank columns, all of which are treated as series. The aggregate column is particularly prone to problems, which is usually much larger than a single series and flattens other columns. Before inserting the chart, select only the metrics and grouping columns that really need to be compared.
# Core comparison
| Concept | Position in the chart | Typical content | Does the legend show |
|---|---|---|---|
| series | The whole of the column color, broken line, pie chart | Product A, sales in 2023 | Usually show |
| Category | Horizontal axis label (sector name in pie chart) | January, North China, first quarter | Usually not displayed, written directly on the horizontal axis |
| Numerical value | The height of the column, the point on the broken line | Specific sales amount and quantity | Do not display separately |
The division of labor among the three is as follows: the series distinguishes "which group to compare", the category distinguishes "by what grouping", and the numerical expression size. To determine which category each cell belongs to, it is not easy to make mistakes when inserting the chart.
# how to use it
When the data is already in the Excel table, you can directly follow the steps below to check and insert the chart. When the data is in the PDF report, it is converted to Excel before drawing.
- Confirm the table structure: the first behavior indicator name, the first column group name, leave blank in the upper left corner, remove redundant total columns and blank rows.
two. Judge the objects you want to compare: to compare products, make them series; to see the trend of time, make time a category.
- If the data is from PDF, you can open the file in the OmniDoc Desktop client (download.omnidoc.top) and export it to Excel using the PDF editor. The scanned version of PDF can be identified by OCR first. After identification, please check the numbers item by item, especially the decimal point and negative sign.
- Select only the desired metrics and grouping columns in the Excel and insert the chart.
- Check to see if the legend is the object you want to compare, whether the horizontal axis is the grouping you want to see, and whether the number of columns or broken lines is as expected. If the row and column judgment is reversed, switch rows and columns in the data source settings, or manually adjust the series and horizontal axis labels.
# which method is suitable for you?
-to see the change of the same index over time, put the time in the category and the object in the series. -to compare the differences of different objects at the same point in time, put objects in categories, time in series, and don't have too many legends. -just want to express the proportion of each part, use a pie chart, keep only one series, and the category is the name of each part. -there are numbers such as year, number and so on in the table. Make sure they are categories before inserting them. Do not let them become numerical series.
# frequently asked questions (FAQ)
1. Why does my horizontal axis show 1, 2, 3 instead of the year?
This is usually because the year column is a number and is treated as a series of values by Excel. In the data source settings of the chart, specify the horizontal axis label as the column of the year, and verify that it is not in the series list, and the horizontal axis will revert to the year.
2. Why is the pie chart suitable for only one series?
The pie chart uses the whole circle to represent a whole, each sector represents a part of the whole, and can only express the composition of one set of data. When you need to compare multiple sets of data, you should use column charts or stacked column charts instead.
3. Can the tables in the scanned version of PDF be made into charts directly?
The scanned version of PDF itself is a picture, and you cannot directly select a number to make a picture. Usually, you need to do OCR identification, export to Excel, and then insert the chart according to the above rules. There may be individual errors in the OCR results. Please check them one by one before drawing.
4. Why can't the totals be put in the chart?
The total column is obtained by adding up other columns, and the value is usually much larger than that of a single series. Putting it into the chart will distort the proportion of the column or broken line, and add a series that does not need to be compared in the legend.
# Summary
The key to the difference between Excel chart series and categories is that a series is a set of data to be compared, a category is a grouping label on the horizontal axis, and the row or column of the data determines which side becomes which. Before inserting the chart, confirm the position of the header, group name and value, and eliminate the miscalculation of the total column and year numbers, which can avoid the dislocation of most horizontal axes and legends.
