[email protected] +1.303.779.0344

Smartsheet Status Report

How to build a RAG status report or a stoplight chart from Smartsheet

If you use Smartsheet to manage and collaborate on projects, you need an easy way to so the status of each task. Commonly called stoplight charts or RAG (red, amber, green) charts, these visual status reports integrate seamlessly into your existing Gantt Chart or project timeline.

Smartsheet Status Report 15-Day
Free Trial

OnePager is the timeline tool that makes it easy to create Smartsheet status reports in just a few easy steps. Follow either set of instructions to get started:

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 task and status information so that you can create a RAG report very quickly. Here's how:

  1. Start with your project plan in Smartsheet, where we have a handful of tasks, each with a status. Since this is a simple project, we'll plan to import all Smartsheet rows into our report, but if your project is larger or has multiple stakeholders collaborating, you can add a checkbox column that will filter which tasks should appear in your report:

    Add a checkbox field to Smartsheet

  2. From your desktop or Windows Start menu, launch OnePager:

    Launch OnePager from your desktop.

  3. When the OnePager start screen appears, click on New to start building a new RAG status report from Smartsheet:

    OnePager start screen.

  4. On the import wizard, choose Select > BROWSE Smartsheet:

    Browse Smartsheet projects from OnePager.

  5. If you've never logged into Smartsheet from OnePager before, Smartsheet will ask you for your username and password:

    Log into Smartsheet from OnePager.

    After connecting, Smartsheet will ask you whether you want to Allow OnePager access to your Smartsheet account. Once you click Allow, OnePager will connect Smartsheet automatically going forward.

  6. Next, choose the project that you want to import from Smartsheet and click OK:

    Import from Smartsheet into a OnePager Gantt chart.

  7. Give your status report a name, and decide how you want to filter rows from Smartsheet. In our example, we're choosing to select everything since the project is pretty simple, but if you added a checkbox field, you can add it as a filter here. When you're ready, click Next:

    Decide how you want to filter rows from Smartsheet.

    The template we've selected here, Status (RAG) View for Smartsheet is pre-configured to assign colors based on the Status field in Smartsheet. If you decide to use your own template, we'll show you how to set up the color-coding in a few steps.

  8. 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 the selected rows from your Smartsheet project, and create a chart that looks like this:

    Project status (RAG) report created in OnePager from Smartsheet.

  9. The chart above has been simplified a little, eliminiating swimlanes, which aren't necessary for smaller projects. It also makes use of OnePager's patented conditional formatting to assign colors to tasks based on their status. To review the default conditional formatting rules or make adjustments, go to Home > Chart Properties > Task Bars and click on the Manage Rules button:

    OnePager's conditional formatting applies color to a Gantt chart based on the status of each task in Smartsheet.

That's it! If you're ready to try it for yourself, download a free trial and get started building RAG status reports from Smartsheet.

15-Day
Free Trial

Excel Method

If you prefer to export your Smartsheet project to Excel, you can then import it into OnePager Express. Follow these instructions to get started:

  1. Start with your project plan in Smartsheet. Here, we have a simple project with different statuses of Complete, In Progress, and Not Started for each task:

    Smartsheet project with a status assigned to each task.

  2. Right-click in your Smartsheet chart and choose the Insert Column Left option. We are going to insert an Auto-Number/System called Row ID:

    Row ID auto-number field in Smartsheet.

    Save your project, and Smartsheet will automatically number each task in your plan.

  3. Your Smartsheet plan is now ready to be exported to Excel. In Smartsheet, go to File > Export > Export to Microsoft Excel:

    Export from Smartsheet to Excel.

  4. Now it's time to bring your project plan into OnePager. Open the Excel file that you just created, go to the Add-Ins tab, and click the OnePager Express icon:

    Launch OnePager from Excel.

  5. On the start screen, click NEW to start building a new status report. On the import wizard, give your chart a name, and ensure that you are importing all tasks from Excel/Smartsheet into OnePager. Click Next:

    Name your Smartsheet Status Report.

  6. On the next screen, tell OnePager that you want to assign colors based on the Status field, and then click Create New Chart:

    Color-code based on the status field in Smartsheet.

  7. OnePager will import the tasks from your Smartsheet project and give you a status report like this, which colors are assigned based on the status of each task:

    Smartsheet status report

    Here, each different status value gets its own color. The progress of each task, or percent complete, is also shown as a yellow progress bar.

  8. If you'd like to change which colors assigns to each status, go to Home > Chart Properties > Task Bars > Manage Rules and customize the different colors that represent each status:

    Smartsheet conditional formatting for status reporting.

  9. Click OK twice, and OnePager will update your chart to reflect the new colors that you assigned to each status, and will also update the legend to reflect those colors automatically:

    Smartsheet RAG Status Report or Stoplight Chart

This status report takes the status data from your Smartsheet project plan and automatically assigns color so that you can quickly identify which Smartsheet tasks are in good shape, and which ones are in trouble.

Get started today by downloading a free trial.

15-Day
Free Trial

Related Articles

Smartsheet Project Health Report