Collaborate on sales forecasts with Excel lists and Windows SharePoint Services

Imagine that you're the manager of a sales team and that your manager has asked you to provide a sales forecast for the coming fiscal period. Imagine that you need to deliver it by the end of the day tomorrow!

Your sales team has the information you need, but they're out in the field. How will you pull them together long enough to collaborate on a project — especially on such short notice? How are you going to get the figures you need for an accurate, useful forecast?

You're in luck. You can use Microsoft Windows® SharePoint™ Services along with the new Excel list feature in Microsoft Office Excel 2003 to collaborate with your team — even when they're not in the office.

4 simple steps

You can get the information you need by doing these four simple things:

  1. Turn a worksheet into an Excel list for your sales team to work with.
  2. Publish your list to a SharePoint site.
  3. Invite your sales team to add their figures to the SharePoint list.
  4. Synchronize the SharePoint list with your Excel list to analyze the data.

Turn a worksheet into an Excel list

To make sure you get exactly the information you need, set up an Excel worksheet that your salespeople can fill out, like this one.

An Excel worksheet for gathering sales forecasts

Sales forecast worksheet

From ordinary list to Excel list

If you had time, you could send the worksheet around in e-mail for everyone to fill out, and then pull the data together at your desk. But with the Excel list feature, it's a lot easier. You can have your sales team do that work for you.

The first step is to turn your forecast worksheet into an Excel list. Do the following:

  1. Make sure that your worksheet columns have header labels, like Customer Name, Salesperson, and Forecast in this example. You cannot create an Excel list from a worksheet that doesn't contain column headers.
  2. Add placeholder numbers in the empty Forecast column (for this example, "0") to identify the data type as a number.
  3. Click any cell within the data, point to List on the Data menu, and then click Create List.
  4. In the Create List dialog box, confirm that the My list has headers check box is selected, and then click OK.

So, what's different?

Click inside the list. You'll notice the differences right away:

  • A blue border now shows that your sales forecast worksheet has been converted to an Excel list.
  • AutoFilter arrows in the header rows are added automatically.
  • An insert row, indicated by a blue asterisk, is added at the bottom of the list. Use this row to add additional data.
  • The List toolbar appears, with new options for working with data. If you don't see it, point to Toolbars on the View menu, and then click List.

Sales forecast worksheet as Excel list

Callout 1 AutoFilter arrows
Callout 2 Blue border
Callout 3 Insert row, indicated by a blue asterisk
Callout 4 List toolbar

Publish your list to a SharePoint site

You're almost ready to put your team to work. First, you need to publish your Excel list to a SharePoint site, where everyone can make changes to the list.

 Note    You must have a working network or Internet connection to be able to publish your list to the SharePoint site. Additionally, you must have permissions to view the list on the server.

To publish a list:

  1. Make sure that the list you want to publish is active on the worksheet.


Click anywhere in the list.

  1. On the Data menu, point to List, and then click Publish List. The Publish List to SharePoint Site - Step 1 of 2 dialog box opens.

Publish List to SharePoint - Step 1 of 2

  1. In the Publish List to SharePoint Site - Step 1 of 2 dialog box, in the Address box, type the URL of your team's SharePoint site.

 Note   This must be the URL of the Web site to which you want to publish your list, not of an individual Web page. An example of a Web site URL is http://sitename/example/. An example of a Web page URL is http://sitename/example/default.aspx. If you have questions about your permissions to a particular site, contact the site administrator.

  1. Select the Link to the new SharePoint list check box.
  2. In the Name box, type the name of the list.
  3. If you want, type a description of your list in the Description text box.
  4. Click Next. The Publish List to SharePoint Site - Step 2 of 2 dialog box opens.

Publish List to SharePoint Site - Step 2 of 2

  1. In the Publish List to SharePoint Site - Step 2 of 2 dialog box, inspect the data types displayed for each of the columns in your list range, and do one of the following.
    • If the data types are as expected, click Finish.
    • If a column is associated with an incorrect data type, for example, a Data Type is showing as Text (single line) instead of Number, click Cancel, make changes to your range, and start again at step 1.

 Note    When you successfully publish a linked list, an ID column is automatically added as the first column of your list. The ID column is used to ensure that all records are unique. This column is read-only and cannot be removed from the list unless you unlink the list from the SharePoint site.

Look at the list on the SharePoint site

To view a published list on your SharePoint site:

  1. In Excel, select your published list.
  2. On the List toolbar, click List, and then click View List on Server.

Invite your sales team to add their figures to the SharePoint list

Your list is now posted on the SharePoint site, ready for action. All you need to do is send an e-mail message to your team with the location of the list, and they can fill in the information that you need — in this case, their sales forecasts.

SharePoint list with filled-in forecast numbers

SharePoint list with forecast numbers

Synchronize the SharePoint list with your Excel list

After your sales team fills in the forecasts for their customers, you can synchronize the data with your Excel list. Synchronizing updates both the published list on the SharePoint site and the Excel list on your hard disk.

To synchronize the published list with your local Excel list:

  • In Excel, on the Data menu, point to List, and then click Synchronize List.

If changes you made in your local list conflict with changes made by another user on the SharePoint site, the Resolve Conflicts and Errors dialog box opens. In this dialog box, you can choose to discard your changes or override the changes that were made to the SharePoint list.

Synchronized Excel list with forecast figures

Synchronized forecast Excel list

Fit the forecast to your needs

Now that you have the forecast figures from your sales team, you can use other helpful Excel list features to quickly analyze the numbers and give your manager a clear, useful forecast — in the quick turnaround time requested.

Be sure to check the See Also section of this article to learn about other ways to use the Excel list feature. Working with lists has never been this easy!

Applies to:
Excel 2003