create charts in excel with scrollbar

Charts in Excel

How do we create our charts in Excel? We will do our exercise from scratch, the first thing is to have our table with sales and returns month by month. So let's create a new field and call it sales graph.

Basically in this new field, what we do is tell Excel to tell the chart that we're going to create, that it's going to be plotted. Now let's create a column at the beginning to list the months and bring the sales into the new sales chart column and start generating our lit graph Excel.

We insert a line graph and we graph sales, for which we select data, add. We mark the name of the series that will represent the sales and in the sales series we will place the new field that we have added and place the sales.

We also put the names which for this exercise are the months and which will represent us on the X axis. Then, we enter another series which will make the returns and we accept.

The returns we change to a area graphwhereby we select the series and right click on the chart and tell it to change the chart type and choose area chart in excel and customize it to our liking.

You may also like: How to create a speedometer chart in Excel type

Create dynamic Excel charts

So far we have the charts in Excelwhat follows is to create the chart we created be dynamic and interactive, that we can make sales appear and disappear with a scrollbar. Therefore, we can present the evolution of sales month by month.

We're about to add the scrollbar, we're going to Developer-Insert-Form Controls-Scrollbar and we simply arrange it to be to our liking.

We already have the scrollbar for ours graph in excel, we just need to integrate with the graph so that it performs the function we established at the beginning. The idea is that when the bar is clicked, there are 12 total clicks, which are the same months we have in our table or base.

For configure the control or integrate our scrollbarwe right click and insert the control format

We will insert the following values, in the minimum value we put 1, because the minimum we can show is one month. Maximum value 12, which corresponds to the twelve months of the year and in increment 1, which means that it increases by one month.

So if we click it goes from January to February, if we click again it goes from February to March and so on. And we have to bind it to a cell and we do it in this example with cell F$1$ and we accept.

Clicking our scrollbar again, we see that cell F1 contains the number 1, and when we hover it changes with respect to the months of the year. Now we just have to integrate the scrollbar with our graph.

How to graph in Excel with a scrollbar?

What we do is that in the field E2 which says the sales graph, we have to indicate it through a excel formula, compared to column E. For example, for the month of February, if cell F1 has a value of less than two, do not insert the data, if it is equal to 2, insert the data of January and February.

In this way we make dynamic the information that is loaded in column E and therefore the information that is downloaded in the graph.

To simplify, let's add a Conditional Si formula in Excel. What we will indicate to the formula is if the value of cell F1, i.e. the value of the scroll bar (leave it fixed), is less than or equal to the month, which is why we have added the field of months in numbers in column A; then give us the sales but give me a zero.

Our Excel formula for this exercise would look like this: =B(A2<=$F$1;C2;0)

With this formula, what we do is show us only the sales of the month we want to see or analyze.

Now, if we click on the scroll bar, it will show us in our excel chart each of the months we add by clicking on the scroll bar. We can do it both forwards and backwards.

Here we already have one excel chart fully interactive

You may also like: How to Create Barcodes in Excel for Free

Perfecting Pivot Charts in Excel

When you view our graph in excelwe see that the trend line always remains at zero, i.e. it is shown to us on the X axis, logically it is because, with the formula, we are telling it not to load information but to load us a zero .

Thus, the trend line will be shown on the X-axis and the chart will not look like we would like. What we want is that the blue line of our exercise appears only depending on the scrollbar and not that it is on the X axis.

A variation of our Excel formula. We will use an additional function that allows us to tell Excel that if the logical test evaluates to false, it will return an error value. The Excel formula we will apply is the NOD formula and we only open and close the parentheses, so we would have the following =YES(A2<=$F$1;C2;NOD())

Now it gives us the famous #N/A or not determined, but our graphic line in Excel is no longer shown lying down as before, but the graph or point appears as the scrollbar increases.

Our interactive chart is ready

At this point we have ours excel charts ready, we have already shown one interactive graph much cleaner and it's about generating them with the scrollbar and analyzing the data or information we want.

What we are left with is simply organizing our workspace so that the user can only see the table, the Excel chart and a scroll bar that allows him to navigate the chart over the different months.

With this we conclude ours Excel tutorial today, where we wanted to show you how to make graphs in excel with scroll bar, we hope it will be very useful for your exchanges and share it with your colleagues or people who may need it.