10.5 C
New York
Saturday, January 28, 2023

Buy now

Export a Microsoft Excel table to Microsoft SharePoint

The Microsoft SharePoint app sign-in page is displayed on a smartphone.
Image: Tada Images/Adobe Stock

If you’re the author of Microsoft Excel data that others in your organization need to view and perhaps even update, you have many options, such as sharing an Excel file or creating a list using Microsoft Lists or Microsoft SharePoint.

If all you’re looking for is a small portion of the Excel file, sharing the entire workbook is overkill and potentially dangerous. Microsoft Lists and SharePoint are similar, but Microsoft Lists is a good choice when interacting with the list through Teams.

SharePoint lists are also a solid choice and a bit easier to implement than Microsoft lists. Once the data is available as a SharePoint list, you can decide who can access and update the list. The only requirement is that you format the data as an Excel table.

SEE: Get the most out of Excel with these 13 courses (TechRepublic Academy)

In this tutorial, I’ll show you how to export an Excel table to a SharePoint list. I will also share a lot of basic information about SharePoint lists along the way.

I use Microsoft 365 Business Premium, including OneDrive for Business and SharePoint. You must have Microsoft 365 Business Premium, Standard or Basic. However, in earlier versions you can export from Excel to SharePoint through Excel 2007.

You can follow using this Microsoft Excel demonstration file.

What lists are there in SharePoint

If you are familiar with Microsoft Lists, you will find that SharePoint lists are similar. They are an easy way to share data with others in your organization when they need to keep track of the same data.

Technically, a SharePoint list is a collection of related data in table format, similar to Microsoft Excel. From one perspective, lists are a way to share data on a SharePoint site. You can also add attachments, such as documents and images. But lists are not a simple list of data. Lists can also take the form of calendars, contacts, announcements, links, surveys, and more. You can even integrate lists with Microsoft Flow, Microsoft Power Apps, and Power Automate.

If the data is already in Excel, you may be wondering why you would use a SharePoint list instead of sharing the Excel workbook. It’s a valid question, and here are a few things to consider when choosing between the two:

  • A SharePoint list can act as a master list of the most current data. While you can share an Excel file, users can save it locally and reference it instead of the online version. Who keeps track of the different versions? That’s not a problem if everyone works with a SharePoint list instead.
  • Even though you can protect parts of an Excel file, it’s still too easy for someone to get lost and change or even delete important data. A SharePoint list, on the other hand, only contains the relevant data, so there is no confusion.
  • You can set permissions and implement data validation to protect the data in SharePoint, ensuring the integrity of your data. These options are available in Excel, but they complement the purpose of the workbook. With a SharePoint list, the list is its own goal. There is nothing else to distract you. In addition, SharePoint displays the support version history. This may or may not be enabled locally for your Excel file.
  • SharePoint lists support multiple view types for the data, which you cannot easily duplicate in an Excel file.

A SharePoint list is a great way to manage data when many people view and edit that data on a regular basis. Now let’s create a SharePoint list with Excel data.

Create a SharePoint list with Excel

After you make the decision to share Excel data as a SharePoint list, you need to format the data as an Excel table. To format the data as a table, click anywhere in the data set and press Ctrl + T, or choose Table from the Tables group on the Insert tab. When Excel asks you to confirm the conversion, write down the header question and then click OK.

Image A shows two table objects in the same Excel sheet. We want to create a SharePoint list of the rate table in H2:I8 because a number of managers refer to these rates regularly. They also have permission to change these, but they all require the most current rates. This is a good example for using a SharePoint list. It’s fine for managers and even employees to see the rate table. It’s quite another thing for those same people to see the actual sales and commission values ​​for everyone in the organization.

Image A

Let's export the rate table to a SharePoint list.
Let’s export the rate table to a SharePoint list.

Now you are ready to start the export:

In the rate table, in the Table Design contextual tab, click Export in the External Data Table group.

Choose Export table to SharePoint list from the drop-down list. If you are not currently signed in to your Microsoft account, the wizard will prompt you to do so at some point.

In the resulting dialog box, enter the SharePoint site address. Enter the full link, even if it is extremely long. To find the URL, sign in to your Microsoft account and choose SharePoint from the list of apps. Then choose the site where you want to create the list and copy the URL from the browser into the dialog box.

Give the list a name and add a meaningful description (Figure B). Then click Next.

Figure B

Add a description to your table.
Add a description to your table.

Verify that the wizard correctly maps the correct data type to each column (Figure C) and click Finish.

Figure C

Confirm the data types.
Confirm the data types.

Click the link in the confirmation message that appears in Figure D to access the new list. It is a good idea to bookmark this page.

Figure D

Click the URL to open the new SharePoint list.
Click the URL to open the new SharePoint list.

As you can see in Figure E, the SharePoint list contains the same rate records as the Excel workbook. At this point, you can edit the new list if needed. To share it, click the Share link in the top right corner. You can enter individuals or groups.

Figure E

The SharePoint list contains the same records as the Excel table.
The SharePoint list contains the same records as the Excel table.

You get full access to the data and structure. Those you share the list with can view and edit the data. But they can’t edit permissions or list structure.

If you’ve used Microsoft lists, you can see how much easier the SharePoint list is to create. The wizard does everything, while Microsoft Lists, due to its improved social interaction functionality, requires more work on your part.

Be prepared to get addicted to SharePoint lists. They are easy to implement and provide a lot of useful flexibility to others in your organization.

Stay tuned

The only thing missing in this scenario is a live link between the SharePoint list and the original Excel workbook. As it is, anyone can change the list and that change will not update the original data. In a future article, I’ll show you how to update the original data.

Source link

Related Articles


Please enter your comment!
Please enter your name here

Stay Connected


Latest Articles