Hello, It's Matt. Welcome to Collaboration Coach.
In this video I'm going to show you how to create a list in SharePoint from an Excel spreadsheet.
This is perfect for those of you who have an existing spreadsheet
and you want to import the data into SharePoint.
And if you stick around to the end I'll give you some tips on how to format your new list
to make it look great just like this one.
Before we get started, please make sure you subscribe to the channel
and hit the bell icon because we don't want you to miss anything.
So here we go.
I have a spreadsheet here that has data and tables created in it.
You can see I have one table that shows me all the data - showing me the product sales that I've made
and then I have another table here
showing me the list of top selling States.
What I want to do is import the data into SharePoint
so I can see the overall sales of my products and the rating for each of them.
So as you know, we store data in SharePoint in lists
so let's go and create a new list.
From my SharePoint site, to create a list based on a spreadsheet,
I'll go to the settings wheel at the top right up here
and choose "site contents".
Then I go to the "new" button and choose "list".
On the "create list" page I can see the "from Excel" button on the left hand side
so I choose "from Excel" and then I type in the name of my list
and I'm just going to call this one "sales".
I can also choose to add this list to the navigation which displays on the left hand side
and I'm going to keep that checked so it does.
Next thing I need to do is tell SharePoint where my spreadsheet is.
So I can either upload it from my computer
or I can choose one that's already in SharePoint.
So for this demonstration I'm going to upload a file that I've got on my computer.
So I hit the "upload file" button, browse to the file and select it.
Mine's called "sales data".
It reads the spreadsheet and the data within it
and then displays the tables and data.
So I can choose and customise how the data is imported.
So you can see I can switch between the tables in the spreadsheet
and it will display the data in different ways.
The table I want to import is called "sales data", so I choose the sales data
and I see the columns and data in them
and then, notice that SharePoint has decided what type of columns they all are.
And if I want to I can change the column types here.
The most important column is the "title" column.
In SharePoint, the title column is the one that you click on and is usually on the left hand side.
When I choose to import my spreadsheet,
I can choose which column in the spreadsheet will be my title.
In this case, SharePoint has chosen the "Year" column as my title
but I actually want the product name to be the title column.
So to change that I come to the drop down above the product name column
and choose "title".
This also changes the "year" column to a number
and I actually want that to be a single line of text
because if it's a number in SharePoint, it's going to put a comma in it
and I don't want that.
When I'm happy with the data I choose "create" and that will create my SharePoint list.
Sometimes this can take a while, particularly if the spreadsheet is very large.
So once my data is imported, I can see it listed here in columns and rows
- so so far, so good.
But I can also make changes to the way it looks in SharePoint
and here's the few tips that I promised you on how to do that.
For example, the "sales" column has numbers with lots of decimal places
and I want to simplify that by not allowing any decimal places.
To do that I can go to the top of the "sales" column,
choose the drop-down, choose "column "settings and then "edit".
On the "edit column" pane on the right hand side here
I can choose "number of decimal places" drop down and then choose "0", and then "save".
Now the "sales" column is looking much better.
So the other thing I want to do is turn my rating column into a percentage.
So to do that I just go to the top of the ratings column
choose the drop-down, choose the "column settings" and then "edit"again.
This time I'm going to check the "show as percentage" box and press "save".
Now my "ratings" column is shown as a percentage.
I also want to see my ratings represented as data bars so I can easily see which product has the highest rating.
To do that I come to the top of the column again, choose "column settings",
and this time "format column".
Then I select "data bars" and choose "save".
Now I can see my ratings represented as data columns.
Okay so that's how you import your Excel spreadsheet into SharePoint
and format the columns so they look awesome.
Thank you so much for watching and I'll see you next time.
[Music]