How to Create a Named Region in Excel 2010 using SharePoint

I have two sections I will prepare. One is a list of instructors and the other is a list of courses. First, I will highlight the range of cells that includes the instructors data.

With the instructors highlighted (including the header row) I right click and choose Define Name.

In this case, Excel 2010 used the header row to guess the name for my region, and in this case, it was close. I will simply add an “s” to Instructor so that my Named Region is Instructors. When I’m done, I’ll click OK.

With the region highlighted, I can see that the Name of my named range appears in the small text box in the upper left section of the Ribbon so it worked.

I highlight the second range, in this case I don’t have a header row.

With the range of cells I want to include in the named range selected, I right click and choose Define Name.

Because I had no header row, the name is currently blank.

With the region still highlighted, I’ll name the range InterfaceCourses and click OK.

I can stop here after hitting save but if I wanted to get some or all of this into SharePoint, one way to do it would be to hit File

When you click Save and Send and Save To SharePoint a button will appear in the upper right side of the window that says Publish Options. I click that button to get the Publish Options Dialog box.

By default, the Entire Workbook will be selected, but I use the drop down menu and choose Items in the Workbook.

In this case, I selected Instructors and then OK. I would still have to provide a location in SharePoint to save this to but this is how you prep a workbook with Named Regions. This skill is important when working with SharePoint. It is used in many areas including, Excel Services, Excel Web Access, the creation of lists from a worksheet or portion of a worksheet and others.

That’s it!