outlining is one of the best ways of
making your data more manageable you can
use it to automatically add subtotals to
group certain rows or columns together
and also to hide details that you don't
need to see in this example I have a
t-shirt order form and I'd like to know
how many of each size have been ordered
so I'll use the subtotal command before
you create subtotals you'll need to sort
your data and I've already sorted it by
t-shirt size
the subtotal command is found on the
data tab
and it will open up
Lockbox
here we'll select the column this is
going to be the basis for the outline in
this case I'll pick t-shirt size because
that's how my spreadsheet is sorted
then you'll need to pick the function
that you'll be using there are some
common functions like sum average and
product but we're going to use count
which will just count the number of
cells that contain each size if you had
numerical data you could select sum and
it would actually add up the values of
the cells to create a subtotal here
we'll select the column where we want
the subtotal to appear and then click OK
now a couple of things have happened
here each t-shirt size is now in its own
group which is shown by this bracket and
all of those groups are inside a larger
group and this is called an outline the
other thing that's happened is that each
group now has a subtotal under it so we
can see the number of shirts that there
are in each size and at the very bottom
we can see the total
to make the spreadsheet easier to read
you can hide the details of each group
so that you only see the subtotals and
the grand total you can hide a group by
clicking the minus sign
and to show it again click the plus sign
you can also hide or show groups by
level by clicking on these numbers
the highest level which in this case is
three will show all of your data and
level two hides the details of each size
so we can still see the individual
subtotals and then level one will hide
those so we only see the total
and I'll click on three again so I can
see everything
if you decide you don't want part of
your spreadsheet to collapse you can
ungroup it
I'd like to ungroup the small t-shirts
so I'll select the cells
and then click the ungroup command and
in this case we run grouping the rows
now remember that all of the groups are
inside a larger group so we'll need to
click on group again to completely
ungroup it
now you can see that the brackets are
gone from these rows and they won't
collapse when you change the level
you can also ungroup everything and
remove all of the subtotals by going to
the subtotal command
and clicking remove all
if you want you can create your own
groups of either rows or columns in this
example I don't really need to see
people's names or the payment dates so
I'll select those columns
and then click the group command
and now I can just hide this group so
grouping data can make your spreadsheets
less cluttered more organized and easier
to view like what you just saw rate it
add a comment subscribe to our chill the
freedom to learn
what you want when you want absolutely
free
GCF learn free.org