You can create and format a table, to visually group and analyze data.
To create a table, select the data and select Home > Format as Table.
Tip: You can also press CTRL + T or CTRL + L.
To make sure there are no empty columns or rows in the data, select a cell within the data, press CTRL + A, then press CTRL + . a few times to move around the data.
Create a table
Select a cell or range of cells to include in the table.
Select Insert > Table .
A Create Table box appears with the cells to include in the table. Edit if needed.
If your table has a header, check the My table has headers checkbox
Select OK. A Design tab appears in the Ribbon.
Format a table
Select a cell within the table.
On the Design tab, select a Table Style.
In the Table Style Options group, you can check and uncheck different boxes to get the look you want, for example Banded Rows or Banded Columns.
Note: If a new row or column is added in the table, it will be automatically added with the table style. Formulas are applied as you add new rows, or create a formula within a column.
We want to treat this data as an entity. Let's convert it to a table.
There are two keystroke shortcuts we could use, either Ctrl + T, think of T for table, Ctrl + L, think of L for list, or on the Insert or Home tab, I'm gonna use Insert tab here.
Now before doing this though, you want to make sure that the data you want to deal with as a unit has no empty rows in it, empty columns.
One quick way to determine that is click within the data, press Ctrl + A, and then press Ctrl + . four or five times, this will move the active cell around the corners of the range.
Ctrl + ., upper left, Ctrl + ., upper right.
Again, Ctrl + .. Now if you haven't seen the data in a while, is that really the last row?
Scroll a little bit beyond it, looks like it is. Ctrl + . a few more times, we're back up top.
You don't need to do that often when you're working with data, but occasionally it's handy.
So, this data is ready to be converted into a table.
So click on any cell, Insert tab, Table, Excel makes an educated guess as to the extent of your data.
Usually gets it right, from A1 in the upper left down to I742, lower-right. Looks good, click OK.
The first obvious visual difference, banded rows.
A certain look there, and notice in the ribbon, we've got a Design tab, a contextual tab related to features that we might be using as we work with the table.
Click outside the table over in column K for example to see how that contextual tab disappears.
Click back within the data, it reappears.
What might catch your eye at first are Table Styles.
We can click here and you just needed 61 different ways to display this, didn't you?
Slide over with the mouse, take a look at this, look at that.
Maybe I'll just pick this one for the time being. Might change my mind later.
The characteristic look here is referred to as banded rows.
Notice in that contextual tab, there's another option called Banded Columns.
If you'd like to use that, uncheck Banded Rows, check Banded Columns.
If you like that look, fine, and the table styles that you might be considering, also, at least for the moment are showing the banded column look.
Everybody probably tries this at least once. Banded rows, banded columns, not so great together, one or the other.
I'll stick with the banded rows. You can give special emphasis to the first column if you wish.
Usually what this means is it makes the data bold and also in some cases gives it a different color background.
You can do with the last column if you wish. Either or both, or none.
If the filter arrows here are something you don't use that often, if you find them annoying, if they're overlapping some of your titles and you'd prefer that not to be the case, there's a button up here for turning off the filter.
This suggests though that your data's being treated as a unit. So what happens if we add a new record at the bottom?
I'm gonna double-click the bottom edge of this cell which is active.
Double-click, down to the bottom, and start to type a new record.
Press Tab, and immediately that row becomes part of the table.
Any formulas that are here automatically get copied downward, so if there's a formula here that picks up years of service based on a date, but I haven't put in the date yet, but the formula is still really doing its job. It's coming up with an answer here.
I'll add another row and it'll get a little bit more obvious because this row's gonna be a darker color than the previous one. We see what happens there.
The same thing will happen on the right side of the data as well.
So any time you add data at the bottom of a table or on the right side.
I'm going to add a new column here, and we're going to be calculating a new compensation amount, so I'll type New Comp.
So the column is not gonna be as wide, like that.
Watch what happens when I press Enter.
That column becomes part of the table as well.
I'm going to write a formula here, equal H2 plus 2,000.
Watch what happens when I press Enter.
Automatically it gets copied down the column, and we see a $2,000 increase for everybody.
Learning doesn't stop here. Discover more expert led tutorials at LinkedIn Learning. Start your free trial today, at linkedin.com/learning.
Learn from recognized industry experts, and get the business, tech, and creative skills that are most in demand.
Get unlimited access to over 4,000 video courses.
Receive personal recommendations based on your LinkedIn profile.
Stream courses from your computer or mobile device.
Take courses for every level – beginner to advanced.
Practice while you learn with quizzes, exercise files, and coding windows.
Choose a plan for yourself or your entire team.