- 00:00:00 welcome back to the nice – no serious
- 00:00:03 I'm Manuel and in this video I would
- 00:00:06 like to show you how you can easily add
- 00:00:08 a slider to your chart in Excel and with
- 00:00:10 that slider you will then be able to
- 00:00:12 switch between different data that
- 00:00:14 should be displayed in your chart in our
- 00:00:16 case this is going to be some stock data
- 00:00:19 now what do we need to do this well we
- 00:00:22 need Excel and we need source files
- 00:00:25 well Excel is something that you
- 00:00:27 hopefully have already and the link to
- 00:00:29 the source files can be found down there
- 00:00:31 in the video description and if you open
- 00:00:34 that source file then then you should
- 00:00:36 see something like this right here if
- 00:00:38 that's the case then you're ready to
- 00:00:40 start right now
- 00:00:44 so what do we have right here well as I
- 00:00:47 said we have some stock data so stock
- 00:00:49 prices for the Microsoft the Apple and
- 00:00:52 the Sony stock and we have the data for
- 00:00:55 different dates so it ends on the 30th
- 00:00:58 of June 2017 and it starts on the 4th of
- 00:01:01 January 2016 so that's basically these
- 00:01:04 source data that we want to visualize
- 00:01:06 now if you look at the prices you can
- 00:01:09 see well the prices are kind of
- 00:01:11 different so we have 69 for Microsoft
- 00:01:14 144 for Apple and 38 for Sony so we
- 00:01:18 should maybe define a starting base for
- 00:01:21 these prices like 100 for example
- 00:01:23 because this makes the visualization in
- 00:01:25 the end kind of easier and better to
- 00:01:27 read now what do I mean by that well we
- 00:01:30 can say that we have for the price a
- 00:01:32 starting point like that and let's now
- 00:01:37 link that to the Microsoft stock for the
- 00:01:39 beginning now if we go to the last line
- 00:01:42 right here this one then we could say
- 00:01:46 that our starting point right here
- 00:01:47 should be 100 well and then we could
- 00:01:51 simply calculate we can say that well
- 00:01:53 the change in the first day is change of
- 00:01:56 course so the price at the end of the
- 00:01:59 second day in our case divided by the
- 00:02:01 price at the starting point multiplied
- 00:02:04 with 100 right here like that and as you
- 00:02:08 can see we now see how our price changed
- 00:02:10 compared to this 100 as a starting point
- 00:02:13 now what you should do right here is we
- 00:02:15 should fix the role right here and also
- 00:02:19 right there
- 00:02:20 so for the starting point and for the
- 00:02:22 100 if we did that then we can simply
- 00:02:25 copy that here up to there paste it well
- 00:02:30 as we can see we now have the prices for
- 00:02:34 the Microsoft stock always compared to
- 00:02:36 this 100 starting point now this is nice
- 00:02:39 but we don't only have the Microsoft
- 00:02:42 stock we also have apple and Sonya of
- 00:02:44 course so we can simply select all the
- 00:02:46 data we have right here and well now
- 00:02:49 simply drag it to the right by pressing
- 00:02:52 ctrl R and with that we can now see that
- 00:02:55 we have our stock prices again but
- 00:02:58 is that common starting point of 100
- 00:03:00 let's maybe add some formatting right
- 00:03:02 here you may have seen it already I like
- 00:03:04 things to be formatted so let's do it
- 00:03:07 like this make this bold and maybe also
- 00:03:10 align that to the right now in case you
- 00:03:13 wonder why the colors are different when
- 00:03:15 compared to your Excel we have our well
- 00:03:17 at least that's what I think beautiful
- 00:03:19 academic schema right here that's why we
- 00:03:20 have that purple and that orange right
- 00:03:22 there but we are not talking about do
- 00:03:24 formatting actually we want to create a
- 00:03:26 chart so let's do this now and first we
- 00:03:28 need the chart input right here
- 00:03:32 let's place it right there now what
- 00:03:33 could be a potential input now in the
- 00:03:36 end we want to create such a slider as I
- 00:03:38 said in the beginning and this means we
- 00:03:41 want to keep things kind of dynamic
- 00:03:43 right here and whenever we talk about
- 00:03:45 dynamic things in Excel it might make
- 00:03:47 sense to at least think about the choose
- 00:03:49 function if you don't know what that is
- 00:03:51 actually just take a look at another
- 00:03:53 video that I have here on the channel in
- 00:03:55 there I talked about the details behind
- 00:03:57 the choose function right now we can
- 00:04:00 simply apply it so how can we apply the
- 00:04:02 choose function right here well we first
- 00:04:04 need Excel this one maybe which is kind
- 00:04:07 of the field that defines which value is
- 00:04:10 selected and then we can simply write
- 00:04:12 choose like that and the index num is
- 00:04:15 now simply that cell right here well we
- 00:04:18 can fix that cell of course immediately
- 00:04:19 and now we can say that if this cell
- 00:04:22 displays 1 then J 2 so the cell we are
- 00:04:27 working right now in should display
- 00:04:29 Microsoft if we have two right here in
- 00:04:32 our input cell right there
- 00:04:33 then we want to have Apple and if this
- 00:04:36 is equal to 3 then it should be Sony
- 00:04:38 like that let's press enter and as you
- 00:04:41 can see if I change it to 2 its Apple if
- 00:04:43 I change it to free its Sony this is
- 00:04:45 nice but that's only well the stock that
- 00:04:49 we select but we also have to make sure
- 00:04:52 that the right data is selected well but
- 00:04:55 the great thing is that we can simply
- 00:04:57 apply that formula and kind of copy it
- 00:04:59 down so let's go right here jump to the
- 00:05:01 last line and I'll copy it and with that
- 00:05:04 well as you can imagine exactly that
- 00:05:07 happens so if you take a look at the
- 00:05:08 data for example right here we have 129
- 00:05:11 this is this place right there if I
- 00:05:13 change it to 2 we have 148 so the apple
- 00:05:17 value and fries is of course the Sony
- 00:05:19 value right there so this works nice
- 00:05:22 actually and that's all the input that
- 00:05:24 we need for our chart again let's maybe
- 00:05:27 apply some formatting before we jump to
- 00:05:29 the next point I guess that I don't know
- 00:05:33 if you like that style so as always you
- 00:05:36 are free to design that just as you
- 00:05:38 prefer bigger a little bit so that's the
- 00:05:41 chart input and this means right now
- 00:05:43 well we also need to create a chart so
- 00:05:46 let's select that column right here so
- 00:05:48 we start in j2 and select the entire
- 00:05:50 range and now go to insert add here and
- 00:05:54 now select well maybe the simple line
- 00:05:57 chart right there we can now select the
- 00:05:59 chart and cut it and scroll up again and
- 00:06:02 maybe paste it right here that's not the
- 00:06:06 best way probably because you cannot see
- 00:06:08 it maybe like that I hope that's ok for
- 00:06:13 you however we first to make sure that
- 00:06:15 we also include the date in our chart
- 00:06:17 so let's right-click it go to select
- 00:06:20 data and now press edit right here and
- 00:06:22 now select the label range which should
- 00:06:25 be well the date actually so let's
- 00:06:27 select the entire column right here so
- 00:06:30 we get that and now we can press ok ok
- 00:06:33 second time and if we now scroll up
- 00:06:36 again well then we see that the chart
- 00:06:39 looks kind of correct so we start at the
- 00:06:41 4th of January at the 4th of January
- 00:06:43 sorry and end right here at the end of
- 00:06:45 June
- 00:06:45 this is totally fine and of course if we
- 00:06:48 now change that right here's our choose
- 00:06:50 field that we created well then the
- 00:06:52 stock price changes this is awesome this
- 00:06:55 is nice because that's actually what we
- 00:06:57 want to have but the problem is that
- 00:06:59 well I cannot see a slider in here so we
- 00:07:02 always have to change it right here in
- 00:07:03 that cell and that's not really user
- 00:07:06 friendly
- 00:07:07 so let's now add a slider and see how
- 00:07:09 this might change the way we can
- 00:07:11 interact with that chart and to add such
- 00:07:13 a slider we need the developer ribbon
- 00:07:15 this is the ribbon right here now in
- 00:07:18 case you wonder because you may not have
- 00:07:20 that ribbon just take a look at the
- 00:07:22 video description down there you can
- 00:07:24 find a link which'll
- 00:07:25 guide you through the activation process
- 00:07:26 of this developer ribbon it's really
- 00:07:29 easy so no worries and as soon as you
- 00:07:32 cut this ribbon well the new can
- 00:07:33 selected of course and if you now go to
- 00:07:35 insert right here then you see different
- 00:07:38 form controls and we will select this
- 00:07:41 one right here so let's select it and
- 00:07:43 now press right here into the chart area
- 00:07:46 and now you can see well this doesn't
- 00:07:48 look really good so we can play around
- 00:07:50 with it make it smaller
- 00:07:52 like that now it looks better already
- 00:07:54 right here there and like this no not
- 00:08:00 the most beautiful way but I think it's
- 00:08:02 fine well now we have the slider and now
- 00:08:04 we can click it but well nothing changes
- 00:08:07 not good so let's make sure that this
- 00:08:10 slider works now so we right click it go
- 00:08:12 to format control and now it gets
- 00:08:16 interesting
- 00:08:17 because now we see that we have our
- 00:08:19 current value well we can change that to
- 00:08:21 1 actually and now we can specify a
- 00:08:23 minimum value and if you think back
- 00:08:26 about our choose function right here
- 00:08:27 well then we have three values actually
- 00:08:30 this means the minimum value should be 1
- 00:08:32 and the maximum value well this
- 00:08:35 shouldn't be 100 this should be 3 the
- 00:08:38 incremental change and the page change
- 00:08:40 that is totally fine so we don't have to
- 00:08:42 change this the only thing we have to
- 00:08:44 add right here is the selling well and
- 00:08:47 as you can imagine we have the minimum
- 00:08:49 and the maximum value so we need to link
- 00:08:51 that sell link by clicking right here to
- 00:08:54 this cell right here that we created and
- 00:08:57 that is again our choose input now we
- 00:09:00 got that so if we click right here and
- 00:09:02 press ok then we can unselect that
- 00:09:05 slider now and as you might imagine now
- 00:09:09 if you click on to this arrow right here
- 00:09:10 then the charge changes dynamically
- 00:09:13 depending on what we select right here
- 00:09:16 as you can see it all the changes from 1
- 00:09:18 to 2 to 3 and we can go back to
- 00:09:21 Microsoft just as we want you and that's
- 00:09:24 actually it that's what I wanted to show
- 00:09:26 you because this is in my opinion and
- 00:09:28 easy way how to make your chart more
- 00:09:30 interactive and by that more user
- 00:09:32 friendly I hope this helps you when you
- 00:09:35 work with Excel and if you want to
- 00:09:37 display something like that
- 00:09:38 and be able to kind of display different
- 00:09:40 types of information in one single
- 00:09:42 visual maybe think about that solution
- 00:09:45 and with that we are not done with that
- 00:09:47 video and as always I can only say
- 00:09:50 thanks a lot for watching and hope to
- 00:09:52 see you in one of the next videos bye
- 00:09:54 bye
