[Music]
i've got a nice little report on screen
here which is run by slicers so how can
i get the selections from these slices
to feed text on my spreadsheet
i'll show you how in this video
[Music]
as with virtually all of my videos you
can download this spreadsheet with the
link in the description
or on the top comment
instantly down to your computer no
details asked for or required you can
then work along to what i'm doing which
make you learn better or you can just
use it as your own template to create
your own great looking reports
[Music]
when i looked into this problem i found
a lot of information online about
coding and quite significant sort of vba
programs that tried to achieve this but
actually there's a really
straightforward way of achieving it
it takes advantage of a feature of
slicers
because slicers can be connected to
multiple
pivot tables at the same time
so this slicer as well as controlling my
report for example could be controlling
another pivot table on another sheet or
just off off screen
and that allows us basically to drive
out results from these slices into a
pivot table
and then
pick up the data in the pivot table and
use it for the report titles
so let's
show you exactly how you might go about
doing that
[Music]
so all of this report is running from
this table of data here and it is an
excel table as you can probably tell
i've got the table design menu up and if
your data isn't a table i'd strongly
urge you to turn it into one you can
just push ctrl t on your data or hit on
the insert menu and hit table because
it's just going to mean that all of this
you just link it to the table you don't
need to worry about setting data ranges
and if the table gets bigger
it's all still going to work so it's a
additional bit of an aside
so
it's all working from this table here
and if i click on the table and
summarize with a pivot table
and i'm going to put this onto an
existing worksheet
and i'm going to put it on to
somewhere over the side of the report so
let's just put it kind of here for the
moment
put it there right
and hit ok
and that's going to allow me basically
to build a little list here from the
pivot table
and then use that as
data and collect that data and use it as
a title
now i would actually in real life so to
speak i would put this on a different
sheet
because if you put pivot tables on the
same sheet as a report
typically you're going to end up with
issues with like columns expanding and
contracting and
different row heights and things that
could mess up your rapport so it's not
great practice i'm just doing it because
i want to show you on the same screen
how to go about doing this
so here's the pivot table there's
nothing in it
so i'm going to want one of these slices
this key just one so i'm going to pick
product category i'm going to put it as
a row
and you'll see that we just simply get a
row of
a list here
of all the product categories that we
want um so the next thing is how do we
get it so that when we pick this slicer
options this changes
so first things on the
slicer so if i highlight the slicer and
get a slicer making
you'll see we have this report
connections button
and it says it's connected to pivot
table three which is just this one
already in the report and now we have
this pivot table for so i could
it's a poor name but we'll take that and
hit okay
and now you see it's reflecting
whatever's in the slicer
and even if i have
multiple select button on
you'll see i get more than one
item
[Music]
so
this is the basis of our technique here
because now we have this data in cells
and we're going to be able to pick it up
much more easily
now how exactly do i do that well first
off on this pivot table i need to get
rid of the grand total
so we go to sorry the pivot table design
and switch grand totals off
so there we have just our choices now
and as we do this we're going to get
them
appearing
and
now now that we've got that we can use
the text join function
now
this is uh relatively new in excel so if
you don't have it i'll show you in a
minute how you can get around it
i'm going to say text join
and it asks for
a delimiter
so i'm just going to put a comma with a
space after it for now let's see why in
a minute
ignore empty definitely true
we don't want to include sort of empty
rows in this data and then our text
we're just going to highlight
down from the pivot table heading now we
might end up with more of these options
so i'm just going to put say 10 rows on
for the moment
and fix that range with f4
so now we've got a list here
furniture office supplies and technology
and of course
we're going to want to form a title
using that information
[Music]
so i'm just going to actually put on the
headings
so we can see our sort of referencing a
bit more easily
and then i want to just repeat that
essentially
for a new for the customer segment so
right next to it i'm just going to put
copy and paste the whole thing we're
going to get a new pivot table here
but in this pivot table
we're going to put
take out product category and put
customer segment
and
now when we go to our customer segment
um
slicer
go to slicer report connections
and this time we'll connect it to
this pivot table as well and hit ok
and there we have our new list and then
this formula
shouldn't really fix the
column but i can just drag that to there
and now we have two boxes of text
with our
slicer selections and it doesn't matter
how many slicer options i pick or
whatever i do
all of it is going to appear in those
two cells if you don't have the text
join function don't worry just keep
watching because i'll show you how you
can get around it
a bit later in this video but for now
i'm going to proceed
assuming that you do have your data here
[Music]
now we've captured our slicer selections
how we're going to form them into a
title
so if we just pick somewhere else on the
screen for the moment so up here i'm
just going to start typing something
inside of quotes picking up those cells
within so we can form some kind of title
so we might want to say sales for
[Music]
product category
gorey
um that
right
and
customer segment
[Music]
now
let's try that
as a product category furniture and
customer segment consumer
and this is moving as we click different
things
and that all looks fine right you might
think
great
but
the problem is what if we use multiple
selections
you know
apart from anything it's going to get
very long and if we pick them all we've
got a really long title there so a
couple of things wrong here too so first
off it says product category where it
should say categories
and same with segment and secondly it's
just a really long
title that we probably want to do
something about
so a couple of quick sort of tips from
my experience in here
[Music]
so first off we need to know how many
um
things have been selected on the slicer
so we can use the same pivot data here
and i'm just going to put a count
here now we use count a
hit tab instantly to
an arrow keys to select these formulas
faster
and i could go down here and if we just
do 10 rows we know
in this case we're not going to get more
than 10 rows
we could go down the dynamic
named range
root and and work out exactly how that
range needs to look but
let's keep it simple here
so pick that
and hit enter on that that gives us
three entries and four entries and of
course if we switch off that hit one
this is always going to give us
the right number of entries that's all
working great so we now know
how many entries we've got but all we
really need to know is whether it's more
than one or not
but so
in here we know if it's one entry we're
going to say category and if it's more
than one we need categories and the only
actual difference is the letter y
so if we close that off there
and just put
an if statement in here saying if
that
fix that
equals one
we want that y like that
and then
if we don't have that we want
an ies with the space at the end
and there we go
so that sorts that one categories let's
test it
category
category
categories so that's all looking good
and then we need to do something similar
on the customer segment so if i copy
that
and so the this time
we just need
kind of either a t or
with a blank or or a ts so let's just
put it here
place that in and we need
sorry this is not n2 it's o2
and instead of a y we just want a
we want just a space or we want a t and
a space so we want an ascent space let's
get it right hit enter
i don't know what i've done here
i know i've done that
okay
is it working
let's clear that
so we've got categories and segments
segment segment
segments
so our
we are now grammatically accurate
[Music]
right one last touch though
i think we need to start a new line
here
so if we do
char which allows us access to
character codes and 10.
memorize that one just commit that one
to memory cha 10 means put a carriage
return into your text
so if i do that
and
it's disappeared essentially because
what we need to do
is
we need to put it in a bigger cell and
i'm going to break all my own rules and
just merge this for the moment and wrap
it so you can see what's going on and
left align it
i hate merge cells but i'm using them so
that you can see what's going on here
for demonstration purposes only
right so we now have a title that's
looking
reasonable i would say i can probably
make it
look a bit
better you know but i'm just giving you
the general gist here could probably
word it more precisely
and this and then because it's after a
cash return it doesn't need
any kind of indent there
[Music]
so i think that will do for the time
being so now we've got this inner cell i
don't need to i can unmerge it and
unwrap it it doesn't really matter
and move it wherever i like so i'm just
moving out of the way of the report now
and now all i need to do is
let's get rid of the pivot chart stuff
on click on the title it equals
that there
and now we get this ridiculously large
title that we're going to need to do
something about but we can left align it
and move it around perhaps over there
so for this title i think we need to
probably do something slightly different
because if we try and cram like a
massive title like that on the top of a
report it's just hard to read
so if we insert another row under here
and link this
to
put something like showing
and then that title
and make it
much much smaller
perhaps sort of
change the font to more of a gray or
something
a bit darker than that perhaps like that
and we will unfortunately need to merge
the cell and wrap it in this case
down and perhaps sort of indent it or
something like that
i think that looks
totally reasonable as a report
we can hide all of this stuff here
although i would have put it on another
sheet hide that row as well
using shortcut keys control nine control
zero to do that
and take off these headings
and i think now we're back to having
like pretty damn good dynamic report
and all our titles lined up so that's
how you get the data from the slicers
onto titles in your report
[Music]
so here's the bonus information for
those of you that don't have the tax
joint function because you need a way of
effectively picking up these
um
entries from the
pivot table outputs from the slicers
and we currently use text join so
so for example we could just pick up
the first cell and do and
maybe put a comma
and and do this and although it's
tedious
you're gonna get there right
i'm gonna delete that so you can see
what's going on
and the problem with that
i don't know if you can see here is that
it says
office supplies and then it's got three
commas on the end
and unless i pick free entries
even then they're gonna have a comma on
the end so that's not
brilliant um unless you know that you're
gonna
you know exactly how many entries are
and yes you could work from here and put
a load of if statements but i think it's
messy
it's probably going to be better to
insert a column next to your pivot table
and
on this one
you just put
make it equal to that and on the next
one you can put
if
that is blank
all right nothing
otherwise put a comma
and a space
and the entry
okay
and then drag that formula down
and that way
you'll get a comma in front of all the
new lines and when you change the number
of lines
you just get blanks afterwards so it's
fine
and then what you can do is instead of
linking all of these with commas
you can then
just link them
you can just have a formula that says
it's that
and that
and that
and you go down as many rows as you need
and then you will lose the commas
because you'll always not end in a comma
so that's how you can achieve the exact
same result
without the text join function
[Music]
remember that you can download this file
instant access just click the link in
the description and you can use it as a
template
for your own work or just go back
through some of the formulas that i've
created
hope that's all really helpful to you
it's just going to give you that cutting
edge in your
reports use slicers
just get those titles looking dynamic
too just that finishing touch that'll
set you apart from all the other people
that are already quite advanced
good luck see
[Music]
[Applause]
[Music]
you