How to Create a Named Region in Excel 2010 using SharePoint

Home > Blogs > SharePoint > How to Create a Named Region in Excel 2010 using SharePoint

How to Create a Named Region in Excel 2010 using SharePoint

Like This Blog 0 Spike Xavier
Added by June 25, 2013

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.

Highlight the range in the worksheet that will become a named region 

Highlight-the-range-in-the-worksheet-that-will-become-a-n.png

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.

Right click and choose Define Name 

Right-Click-And-Choose-Define-Name.png

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

Name the Region 

Name-The-Region.png

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.

Verify new Named Range 

Verify-New-Named-Range.png

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.

Highlight second named range 

Highlight-second-named-range.png

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

Right click and choose Define Name again 

Right-Click-and-choose-define-name-again.png

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

Name the range of cells 

Name-the-Range-Of-Cells.png

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

Name the Range 

Name-The-Range.png

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

Click File Save and Send 

Click-File-Save-and-Send.png

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

Save to SharePoint Publish Options 

Save-To-SharePoint-Publish-Options.png

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.

Choose Items in the Workbook 

Choose-Items-In-The-Workbook.png

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

Select the Desired Range or Ranges and click OK 

Select-the-Desired-Range-or-Ranges-and-Click-OK.png

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

Videos You May Like

A Simple Introduction to Cisco CML2

0 3703 0

Mark Jacob, Cisco Instructor, presents an introduction to Cisco Modeling Labs 2.0 or CML2.0, an upgrade to Cisco’s VIRL Personal Edition. Mark demonstrates Terminal Emulator access to console, as well as console access from within the CML2.0 product. Hello, I’m Mark Jacob, a Cisco Instructor and Network Instructor at Interface Technical Training. I’ve been using … Continue reading A Simple Introduction to Cisco CML2

Government Edition – Encrypting a USB Flash Drive in Windows 10

0 269 2

In this video, Security Instructor Mike Danseglio demonstrates how to use BitLocker in Window 10 to secure files on a USB Flash drive that adhere to stricter data protection requirements as found inside Government entities. BitLocker 2-day instructor-led training is now available at Interface: BITLOCK: Planning and Deploying BitLocker Drive Encryption Training Video Transcription: Hi. … Continue reading Government Edition – Encrypting a USB Flash Drive in Windows 10

OSPF Adjacency Troubleshooting Solution – Getting Close to the OSPF adj

0 247 1

In this video, Cisco CCNA & CCNP instructor Mark Jacob shows how to troubleshoot OSPF Adjacency issues by showing the distance between routers with the show ip ospf neighbor command.

Write a Comment

Share your thoughts...

Please fill out the comment form below to post a reply.