Chart & Visualization Issues

How to Create an Excel Graph from Publication Year Ranges

Amos GikundaAmos Gikunda Nov 3, 2026 869 views

Question details

The user needs to visualize the number of active magazines per year based on overlapping publication date ranges (e.g., 1812–1818).

How to Create an Excel Graph from Publication Year Ranges
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.
Before you start

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.

Solution 1Recommended

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.

1
Format years as numbers

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.

2
Expand the year ranges

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.

3
Insert a PivotTable

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.

4
Configure the PivotTable fields

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'.

5
Generate the PivotChart

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.

Expand Data and Use PivotCharts to Count Active Years
Handling Multiple Intervals: If a single publication has multiple disjointed intervals (e.g., active from 1812-1815 and then revived in 1820-1825), ensure both date ranges are expanded into individual year rows so the count remains accurate.
Visualize Data with WPS Spreadsheet

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. 1. Open your dataset: Launch WPS Spreadsheet and open your workbook containing the expanded publication year data.
  2. 2. Insert a PivotTable: Highlight your data range, go to the 'Insert' tab, and click the 'PivotTable' button.
  3. 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. 4. Insert the PivotChart: Click 'PivotChart' under the Analyze tab to instantly generate a graph of your historical publication data.
Fully compatible with Microsoft Excel (.xlsx) files and existing PivotTable structures.Intuitive PivotChart creation with rich formatting and customization options.Lightweight software with incredibly fast performance for large datasets.Free built-in data processing tools to easily manage text and number conversions.
microsoft office alternative - wps office

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.