If you use Smartsheet to manage several project plans, you sometimes need to create a combined Gantt chart that reports on multiple projects at once:
To build your own multi-project Gantt Chart from Smartsheet, download a free trial and follow one of these sets of instructions:
1. Easy Method: Direct import of Smartsheet data
2. Legacy Method: Import of Smartsheet data through Excel
Direct Smartsheet Method
OnePager connects directly to Smartsheet and imports the data from multiple projects all at once. Here's how to create a multi-project Gantt Chart from Smartsheet:
-
Before importing from Smartsheet into OnePager, we want to decide which Smartsheet rows from each project should be included in the Gantt Chart. You can do this by adding a checkbox column in each Smartsheet project that you plan to import. Right-click on any existing column, and choose Insert Column Right. Here, we will create a Checkbox column called "Add to OnePager":
- Once the "Add to OnePager" column has been inserted, check the box for any rows that you want to include in your Gantt chart:
Once you've finished selecting rows in the first Smartsheet project, save it, and then repeat these first two steps for all of your remaining Smartsheet projects.
-
Now, go to your desktop or Windows Start menu, and launch OnePager:
-
On the OnePager start screen, choose New to start building a new multi-project Gantt chart from Smartsheet:
-
On the import wizard, choose Select > BROWSE Smartsheet:
-
If you've never logged into Smartsheet from OnePager before, Smartsheet will ask you for your username and password:
After logging in, Smartsheet will ask you if you want to Allow OnePager to access your Smartsheet data. Once you choose Allow, OnePager will connect to Smartsheet automatically in the future.
-
Next, select all of the Smartsheet projects that you want to import. You can select multiple projects at once by holding down the Ctrl key. Once you have selected all of the projects from Smartsheet, click OK:
-
Once all of your Smartsheet projects are selected, tell OnePager to filter your Smartsheet rows based on the Add to OnePager checkbox field, and then click Next:
Here, we are using OnePager's Multi-Project Gantt Chart - Detailed for Smartsheet template, which is specifically built for multi-project Gantt charts from Smartsheet. This is one of our standard templates that comes with your OnePager license, but you are welcome to use your own custom template if you prefer.
-
Double-check that the Smartsheet columns OnePager has selected are the ones that you want to use, then click Create new chart. OnePager will import all of your selected Smartsheet projects at once, and create a multi-project Gantt chart like this:
That's it! From here, you can customize colors and layouts, or add another set of swimlanes if you want to subdivide your Gantt chart into phases. Download a free trial to test things out with your own Smartsheet projects.
15-DayFree Trial
Excel Method
OnePager Express can take several Excel exports from Smartsheet (one per project), and combine them into a single Gantt chart that summarizes multiple project plans. Here's how:
Log into Smartsheet and open your first project plan, which in this case is called the "Alpha Project". We recommend including Smartsheet's Row ID column in your Excel export. To do this, we'll right-click on the Task Name column in Smartsheet, and choose Insert Column Left:
When the Insert Column window appears, choose the Auto-Number/System for Row ID:
Before clicking OK, we want to make sure that the Row ID is unique across all of your different projects. By default, Row ID is a simple number, so it's likely that you'll have the same values in each of your plan. To make the Row ID unique, add your project name as a prefix. This way instead of having a Row ID of "001", it will be "Alpha-001" instead. This makes it much easier to combine several Smartsheet projects into a multi-project Gantt chart later.
With the Row ID set up to include your project name, click OK to see how those IDs look in Smartsheet:
If you plan on grouping/sorting your Gantt chart based on each subproject, or assigning each subproject a different color, it's also a good idea to have a Project Name column that clearly states which project each task belongs to. To do this, we'll right-click on any existing Smartsheet field and insert another new column. This time, it will be a Text/Number column called Project Name:
Once the Project Name column has been inserted, make sure that each row in your Smartsheet plan includes the project name, like this:
Last but not least, decide whether you want your multi-project Gantt chart to include every single row from all of your Smartsheet projects or just certain highlights. If you only want to include a few rows, insert a Checkbox column into Smartsheet called Add to OnePager and then use it to select the tasks to include and exclude from your Gantt chart:
"Project Alpha" is ready to export from Smartsheet to Excel. Go to File > Export > Export to Microsoft Excel:
Repeat these steps for all of your other Smartsheet projects that you want to combine into your multi-project Gantt chart. For example, if we have second project called "Project Beta", it should look similar to how we've already set up "Project Alpha", but with different values for Row ID and for Project Name:
Once all of the Smartsheet project plans have been exported to Excel, you should have a set of Excel files--one for each project:
- Open the first "Project Alpha" file, and then go to Excel's Add-Ins tab. Click the the OnePager Express button:
When OnePager's start screen appears, click NEW to build a new multi-project Gantt chart.
- On the import wizard, you'll see that "Project Alpha" has already been imported, but we need to add the other Smartsheet projects. To do this, click on Add/Remove:
When the Data Source Selection window appears, click the Add button and then BROWSE FILES to attach as many additional Smartsheet projects as you'd like. There is not a limit to the number of projects you can include in a OnePager Gantt chart.
When you've finished adding multiple projects, click OK
- In the Task Selection section, choose the Add to OnePager field that you set up as a checkbox column back in Smartsheet. This will filter your rows so that only important information makes it into your Gantt chart.
- Click Next. This screen lets you decide which columns from Smartsheet you want to use in your OnePager Gantt chart. Most of the time, OnePager will auto-detect the correct fields, but it's a good idea to make sure that OnePager's Unique ID is mapped to Smartsheet's "Row ID". We've also chosen to assign a different color to each Smartsheet project so it's easy to tell them apart.
Once things look good, click Create New Chart to build your Gantt chart.
- Initially, your OnePager chart will look like this:
It's a good start, but the tasks from the two different projects are jumbled together and we'll want to fix that.
- To group and sort the tasks based on their Smartsheet project name, we can create swimlanes in OnePager. Go to Home > Chart Properties > Rows/Swimlanes. Turn on swimlane groupings in OnePager and set the grouping to look at the Project Name field form Smartsheet:
- Click OK, and OnePager will automatically group and sort your Gantt chart based on the different projects that you've exported from Smartsheet:
Build your Smartsheet Multi-Project Gantt Chart today by downloading a free trial of OnePager Express.
15-DayFree Trial
Last Updated: March 11, 2022