Coding

Excel – Nice to know – CHARTS & SLIDERS

  • 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