How to Create an Excel Graph from Publication Year Ranges
Question details
The user needs to visualize the number of active magazines per year based on overlapping publication date ranges (e.g., 1812–1818).

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Visualizing historical publication data containing overlapping year ranges and titles with multiple distinct publication intervals.
- Observed behavior
- Needs to correctly count and chart active titles per year while properly accounting for pre-1900 dates and overlapping periods.
Ensure your dataset lists the start and end years for each publication. Since standard spreadsheet software does not natively support date calculation prior to January 1, 1900, historical years must be formatted as standard numbers or text.
Expand Data and Use PivotCharts to Count Active Years
Convert historical year ranges into a flat list of active years per title, then summarize the data using a PivotTable and PivotChart.
To accurately chart overlapping periods, you cannot simply plot the start and end dates. The data must first be transformed so that every single year a publication was active has its own row. Once transformed, a PivotTable can easily count the frequency of each year.
Select the columns containing your historical years. Right-click, select 'Format Cells', and choose 'Number' (with 0 decimal places) instead of 'Date' to avoid pre-1900 calculation errors.
Create a new table that lists every active year for each magazine title. For example, if a magazine ran from 1812 to 1815, you must create individual rows for 1812, 1813, 1814, and 1815 alongside the magazine's title.
Select your newly expanded dataset, navigate to the 'Insert' tab on the top ribbon, and click 'PivotTable'. Choose to place it on a new worksheet.
In the PivotTable Field List pane, drag the 'Year' field into the 'Rows' area. Drag the 'Magazine Title' field into the 'Values' area and ensure it is set to 'Count of Magazine Title'.
Click anywhere inside your populated PivotTable. Go to the 'Insert' tab and click 'PivotChart'. Select a Line or Column chart to visualize the total number of active magazines during each year.

Create Year Range Charts Seamlessly in WPS Spreadsheet
WPS Spreadsheet provides powerful PivotTable and PivotChart functionalities, allowing you to easily process historical data, summarize expanded date ranges, and visualize overlapping publication periods with zero hassle.
- 1. Open your dataset: Launch WPS Spreadsheet and open your workbook containing the expanded publication year data.
- 2. Insert a PivotTable: Highlight your data range, go to the 'Insert' tab, and click the 'PivotTable' button.
- 3. Configure your fields: Drag the 'Year' column into the Rows section and 'Title' into the Values section to calculate the count per year.
- 4. Insert the PivotChart: Click 'PivotChart' under the Analyze tab to instantly generate a graph of your historical publication data.

Frequently Asked Questions
Why does Excel show an error or strange text for years before 1900?
Excel's native date system begins on January 1, 1900. Any date entered before this year is not recognized as a valid date format and is treated as text. To perform calculations or charting on pre-1900 dates, you must enter them as standard numerical values.
How do I quickly expand a year range like 1812-1818 into individual rows without typing them manually?
You can use Power Query to automate this. Load your data into Power Query, add a Custom Column with the formula { [Start Year] .. [End Year] } to create a list of numbers, and then click the expand icon on the new column header to 'Expand to New Rows'.
Can I create a Gantt chart to show overlapping publication periods instead of a count?
Yes. While a PivotChart is best for counting active items per year, you can create a Gantt-style chart using a Stacked Bar chart. Plot the 'Start Year' as the first series (formatted with no fill color to hide it) and the duration (End Year minus Start Year) as the second visible series.




