How to Create a Named Region in Excel 2010 using SharePoint
Frequently when getting data into SharePoint 2010 from Excel 2010 it becomes necessary to create named regions in the Excel Worksheets before getting them into SharePoint 2010. Often times when working with clients or students I find that they don’t know how to do this, so I decided to make a blog post to quickly bring everyone who wants to up to speed.
I have a workbook with several worksheets. In particular I have two sections of data I am going to want to eventually get into SharePoint 2010. In order to prepare the workbook I will create 2 named regions so that when I save and send to SharePoint or if I decide to create a list from one of the named regions the workbook is ready to go.
This skill is especially important when working with Excel 2010 and SharePoint 2010.
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!
Spike Xavier
SharePoint Instructor – Interface Technical Training
Phoenix, AZ
Agile Methodology in Project Management
In this video, you will gain an understanding of Agile and Scrum Master Certification terminologies and concepts to help you make better decisions in your Project Management capabilities. Whether you’re a developer looking to obtain an Agile or Scrum Master Certification, or you’re a Project Manager/Product Owner who is attempting to get your product or … Continue reading Agile Methodology in Project Management
An Overview of Office 365 – Administration Portal and Admin Center
This is part 1 of our 5-part Office 365 free training course. In this Office 365 training video, instructor Spike Xavier introduces some of the most popular services found in Microsoft Office 365 including the Admin Portal and Admin Center. For instructor-led Office 365 training classes, see our course schedule: Spike Xavier SharePoint Instructor – … Continue reading An Overview of Office 365 – Administration Portal and Admin Center
Creating Users and Managing Passwords in Microsoft Office 365
In this Office 365 training video, instructor Spike Xavier demonstrates how to create users and manage passwords in Office 365. For instructor-led Office 365 training classes, see our course schedulle: Spike Xavier SharePoint Instructor – Interface Technical Training Phoenix, AZ 20347: Enabling and Managing Office 365
Write a Comment
