Chart & Visualization Issues

How to Display SharePoint Data in an Excel Calendar or Gantt Chart

Nimra Malik Oct 20, 2026 868 views

Question details

The user wants to import a SharePoint list into Excel and visualize the job start and end dates as a multi-month calendar, timeline, or Gantt chart.

How to Display SharePoint Data in an Excel Calendar or Gantt Chart

Product: Microsoft Excel
Device & OS: not provided
Scenario: Visualizing project schedules and tasks spanning multiple months by importing SharePoint list data into a spreadsheet.
Observed behavior: Excel imports the SharePoint data as a standard table but does not automatically convert or display it in a calendar or Gantt chart format.

Before you start

Ensure you have exported your SharePoint list data into Excel and verify that the 'Start Date' and 'End Date' columns are formatted as valid dates rather than text.

Solution 1Recommended

Create a Custom Gantt Chart Using Imported Data

Since Excel does not auto-format SharePoint lists into a calendar, building a Gantt chart from a stacked bar chart is the most effective way to visualize your multi-month timeline.

A Gantt chart provides a clear visual representation of task durations over time. By calculating the difference between your start and end dates, you can format a standard stacked bar chart to act as a project timeline.

Calculate Task Durations

In your imported Excel table, insert a new column named 'Duration'. Use a simple subtraction formula (e.g., =End_Date - Start_Date) to calculate the number of days each task takes.

Insert a Stacked Bar Chart

Select the columns containing your Task Names, Start Dates, and Durations. Navigate to the 'Insert' tab, click the 'Insert Column or Bar Chart' icon, and select 'Stacked Bar' under the 2D Bar section.

Hide the Start Date Series

In the newly created chart, click on the data series representing the Start Dates (usually the first segment of the bars). Right-click, select 'Format Data Series', and change the Fill option to 'No fill'. This makes the duration blocks appear floating, creating the Gantt effect.

Format the Date Axis

Right-click the horizontal axis containing the dates and select 'Format Axis'. Adjust the 'Minimum' bound to the numeric value of your earliest project start date to remove empty space at the beginning of the chart.

Create a Custom Gantt Chart Using Imported Data

Reverse Task Order: If your tasks appear in reverse order on the chart, right-click the vertical axis (Task Names), select 'Format Axis', and check the box for 'Categories in reverse order'.

Solution 2

Use the SharePoint Calendar View

If creating an Excel chart manually is too time-consuming, you can configure SharePoint to display your list as a calendar directly on the web.

Visualize Project Data easily

Create Stunning Gantt Charts with WPS Spreadsheet

WPS Spreadsheet provides powerful charting tools that make it simple to turn your exported project lists into clear, professional Gantt charts to track your timelines.

1. Open Data: Open your exported SharePoint list data in WPS Spreadsheet.
2. Calculate Duration: Add a Duration column by subtracting the Start Date from the End Date.
3. Insert Chart: Select Task Names, Start Dates, and Durations, then go to the 'Insert' tab and choose a 'Stacked Bar' chart.
4. Hide Base Series: Right-click the Start Dates series in the chart, go to formatting options, and set the fill color to 'No Fill'.
5. Adjust Timeline: Format the horizontal axis bounds to match your project's start date for a perfectly aligned Gantt chart.

Free and lightweight office suiteFully compatible with Microsoft Excel (.xlsx) formatsRich built-in chart types for project managementUser-friendly interface for quick chart formatting

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't Excel automatically create a calendar from SharePoint data?

Excel treats imported SharePoint data as a standard raw data table. It does not have a built-in feature to automatically map list data into a visual calendar or Gantt template upon import.

What data columns are required to build a Gantt chart in Excel?

To create a functional Gantt chart, your data table must include at least three columns: Task Name, Start Date, and Duration. If you only have an End Date, you must calculate the duration first.

Can I link a pre-made Excel calendar template directly to SharePoint data?

Pre-made calendar templates in Excel typically rely on manual data entry or highly specific cell references. To populate a template with SharePoint data, you would need to write complex lookup formulas (like XLOOKUP or INDEX/MATCH) to pull the list data into the calendar grids manually.

Excel & Data Problems