Hello everybody. Welcome to the third dashboard. In this video we will see how to create a trendline. Using two pivot tables that we created in the previous video. I've done just minute addition to the data as we cannot put in a heading directly that is the formula. I wish to alter the record in such a way that whatever the user selects from the drop automatically the name should change like these.
If I like if I select admin, the record says average salary for admin. Now this is something it is formula driven and cannot be done into the header directly. So what I've done is I've used a formula that says whatever the reference is above shall be added into the data using ampersand. So equals to under double quotations, average salary for and whichever cell or whichever data is provided in v one. And the same rule is applicable to the next. Now let's see how to create a dashboard.
So I'll just insert a chart and then I'll use a scatter chart and Now here we do not require the size of the points, so we're not using the 3d chart, so I'll just keep it a little wider. And to add the data into it, we simply go to the Data tab, select data, and from there we will add. Now currently we do require the heading. So I'll select the heading given over here that I've created using the formula x axis it will be the parameter now I'll be selecting to the bottom I currently it says grand total Now further go down further because it in the future might happen that the data may be added. In such cases the data should not be skipped. So I'll also show how to get rid of the grand total that shows at the bottom.
Now the same goes for the numbers as well. So I'll be selecting all the numbers and starting from the first value to the last now. I click on OK and I click on panel and the data gets populated on the screen. Now to get off the grand total, I simply click on pivot table, go to the Design tab. Under grand total, I say turn off grand total, and that will in turn remove the grand total from the data. I'll do the same for the second pivot table as well.
And now I repeat the process. So again, I click into the data design select data, add another field. Now this time the field will be based on the user selection. The second pivot that we created will retrieve the data for from the user that is from the dashboard and will respectively show the results. Here again, x axis will be the field to the extreme bottom and y axis will be the average in the bottom. Once I click OK, now we do have two records.
Now currently, both the records are overlapping. Now let me show you the output. So if I expand the screen a little bit Just for clear view, currently the data gets populated cross and we do not require the axis so we just remove that. Or we can also go to the chart elements and simply untick gridlines. Now, if at all currently from all the records, we will not be touching these amb parts, the user will only be altering the records from here. So if the user selects admin only the admin records get filtered in the data and that will by default get reflected on the data screen.
So as we see there is a blue reference and there's an orange reference. Now in the next video, we will see how to make this reference a bit blurred or light and the main selection a little dark. Till then keep practicing. See you in the next video. Thank you