Up next in 10
Beginner's Guide to Tables in Excel - how to use key features
4K views
Feb 9, 2025
What You'll Learn: - How to create an Excel Table - Sorting and filtering for quick data insights - Using the Total Row for automatic calculations - Using slicers for data filtering - Convert table back into a normal range - Applying structured references in formulas (Detailed Structured References Guide: https://www.youtube.com/watch?v=xEYDDK68vC4)
View Video Transcript
0:00
hi in this video we'll dive into Excel
0:02
tables a table comes with built-in tools
0:05
for sorting filtering and formatting
0:08
unlike a regular range an Excel table is
0:10
dynamic it grows and adjusts as you add
0:13
or remove data here I have a data set
0:16
right now it's just a range let's
0:18
convert it into a table select any cell
0:21
in the data set and press contrl t or go
0:24
to the Home tab click format as table
0:26
and select any table style you like from
0:28
the list
0:34
make sure that my table has headers
0:36
boxes checked as our data includes
0:38
column headings once confirmed Excel
0:41
will automatically format your data as a
0:43
structured table when you select any
0:46
cell in the table the table design tab
0:48
appears on the ribbon offering options
0:50
to customize style and manage your table
0:53
for better data organization and
0:55
Analysis for example you can customize
0:57
the look of your table by selecting any
0:59
cell within in it then go into the table
1:01
design tab in the table Styles group
1:03
click the more button to expand the
1:05
style gallery from there you can apply
1:08
or clear a style as you hover over
1:10
different styles Excel provides a live
1:13
preview instantly updating the table's
1:15
appearance to help you choose the best
1:16
option notice that each column heading
1:19
in the table has a down arrow which
1:21
opens a menu for sorting and filtering
1:23
based on the columns values you can show
1:25
or hide these arrows by checking or
1:27
unchecking the filter button option in
1:29
the table Style options group under the
1:30
table design tab the First Column option
1:33
applies special formatting to the First
1:35
Column of the table making it stand out
1:38
similarly the last column option
1:40
enhances the last column these options
1:42
are useful when these columns contain
1:44
important information such as names or
1:46
categories the banded rows option adds
1:49
alternating row colors for better
1:50
readability updating automatically as
1:53
you add or remove rows similarly banded
1:56
columns apply shading to columns for
1:58
easier vertical comparisons using both
2:00
creates a boxed effect that may not look
2:02
great so it's best to enable either
2:04
banded rows or banded columns but not
2:07
both the total row option adds a summary
2:09
row at the bottom of the table each cell
2:12
in this row displays a down arrow
2:14
allowing you to quickly select a
2:15
built-in function like sum average or
2:18
count for numeric columns if your
2:20
desired function isn't listed click more
2:22
functions to access additional
2:26
options the header row option ensures
2:28
that the first row of the table table
2:30
remains as column headers these headers
2:32
are automatically bolded and stay
2:34
visible when scrolling if the table is
2:36
large additionally they enable built-in
2:39
sorting and filtering making data
2:41
management
2:42
easier for example you can sort your
2:45
data in a sending or descending order
2:47
just like I did in the bonus
2:53
column the header row also provides
2:56
number or text filters based on the
2:57
column's data for example in in the
2:59
bonus column we applied a number filter
3:02
by selecting greater than then entered
3:04
4,000 in the Custom Auto filter box and
3:06
clicked okay now the table displays only
3:09
records where the bonus exceeds 4,000 to
3:12
clear this filter click the down arrow
3:14
in the header and choose clear filter
3:16
from Bonus next to show records with a
3:19
bonus less than 1,000 we selected less
3:22
than entered 1,000 in the custom autof
3:24
filter box and clicked okay the table
3:26
now displays only records with a bonus
3:28
below 1,000
3:30
these number filters go beyond just
3:33
greater than or less than you can filter
3:35
data that is equal to or not equal to a
3:37
specific value display the top 10 values
3:40
show records above average or below
3:42
average or even create a custom filter
3:44
based on your needs all using the table
3:46
headers at the bottom of header menu
3:49
you'll see a list of checkboxes
3:51
representing unique values in that
3:52
column these checkboxes allow you to
3:55
quickly filter the table by selecting or
3:57
deselecting specific values for examp
3:59
example if you uncheck all but one value
4:02
the table will display only rows
4:04
containing that value you can also use
4:06
the select all option or clear filter
4:08
from to reset the filter and show all
4:10
data
4:23
again as we discussed earlier the
4:26
filters available in the header menu
4:28
depend on the columns data type for text
4:30
values Excel provides text filters
4:33
allowing you to filter data based on
4:34
conditions like contains begins with
4:37
ends with or does not contain you can
4:39
use these options to refine your data
4:41
according to your
4:49
needs the table design tab also allows
4:52
you to insert a slicer from the tools
4:54
group simply select the column you want
4:55
to filter and the slicer will appear as
4:58
a floating box with interactive buttons
5:00
enabling you to filter data with a
5:02
single click a slicer is a visual
5:04
filtering tool in Excel that allows you
5:06
to quickly filter table or pivot table
5:08
data with clickable buttons unlike
5:11
standard filters slicers provide a clear
5:13
view of the applied filters and make it
5:15
easy to switch between selections
5:30
the remove duplicates option in the
5:32
tools group of the table design tab
5:34
helps eliminate duplicate rows from a
5:36
table when you click remove duplicates
5:39
Excel opens a dialogue box where you can
5:41
select the columns to check for
5:42
duplicates if duplicate rows are found
5:45
based on a selected columns Excel keeps
5:47
only the first occurrence and removes
5:49
the rest a message then displays how
5:51
many duplicates were removed and how
5:53
many unique values remain this feature
5:56
is useful for cleaning data and ensuring
5:58
there are no repeated record records in
5:59
your table the convert to range option
6:01
changes an Excel table back into a
6:03
normal range while keeping the tables
6:05
formatting when you click convert to
6:07
range Excel removes table specific
6:09
features like structure references
6:11
automatic filters and table specific
6:13
formatting but the data remains intact a
6:16
confirmation message appears before the
6:18
conversion if you also want to remove
6:20
the table's formatting after converting
6:21
it to a range go to the Home tab click
6:24
clear and choose clear formats this will
6:26
remove all table specific formatting
6:29
leave only the raw data without colors
6:31
Borders or
6:36
Styles another table feature is
6:38
structured data which we discussed in a
6:41
previous video here's a brief
6:42
explanation when you create a table
6:45
Excel allows you to use structured
6:46
references instead of traditional cell
6:48
references notice that when we enabled
6:50
the total row Excel inserted a subtotal
6:53
formula that sums the entire column by
6:55
using the column name bonus instead of a
6:57
fix cell range like D2 to 10 here bonus
7:01
refers to the entire bonus column within
7:03
the table making the formula easier to
7:05
read and update the 109 in the subd doal
7:08
function applies the sum function while
7:10
ignoring hidden rows which is useful
7:12
when filtering data structured
7:14
references automatically adjust as you
7:16
add or remove rows keeping your formulas
7:19
Dynamic and
7:20
efficient thanks for watching if you
7:22
found this helpful please like share and
7:25
subscribe don't forget to check out the
7:27
structured references tutorial Linked In
7:29
the description see you in the next
7:30
video