Hello everybody, welcome to the third dashboard. In this video, we will see how to create a trendline. For the trendline, we need we do require two pivots, one that will show the overall result and the second one will show the result that the user will be selecting. So now let's get started. First, I keep the cursor on the data to make this data dynamic. I will be using the OFFSET function that we covered in a previous video.

Refer to that video for a refresher on offset. I will be doing the OFFSET function outside the sheet and then I'll be copy pasting the formula into the referencing will just speed up the video. So here I have created the formula that will automatically select all of the data for me and I simply cut the formula and then keep the cursor in data and go to the formula Name Manager. I'll delete the existing names as we will be creating a new one. And I'll name it data read or master data to be specific, and then paste the formula into the formula bar. That is refers to.

Now the best part is it automatically selects the entire data and then in the future, if the data expands, the reference will also expand automatically. Now delete this particular formula. Now to create a pivot, I just go to insert pivot table, and I will not be selecting the data rather I'll mention the name that is master data. There it is, I click OK. And the pivot table is ready, which is now dynamic. Now I create the pivot that we require and I'll be placing years of experience into the road table and the South. into value fields.

So here's the data that we have. Now this will be data that will show the overall average of the average overall salary. Let me convert it into currency, we wish to see this as an average. So we simply right click and I select summarize value by, in the older version, you'd be seeing summarized data by from here we'll be selecting average as we are looking for average based on US dollars. So I'll copy this entire data, because we need two of them, and I paste it over here. The second one is where the user selection will be taken into consideration once the two pivot tables are created.

Now the best part is both of the pivot tables are controlled with a single data. But both are different pivots. Now the user will be controlling the job types for both of them. So I'll be selecting the second which is controlled by the user and drop jobs. type into the filter box where the data will show me records for all. So for the first pivot also, I'll put in the filter box.

So the first filter box will show the data records for all. And the second will show the records selected by the or the user selection. So if the user selects admin there will be a gray data highlighter for the whole and the admin will be highlighted darker. And we'll name this data for trend chart. Hmm that should do it. Now once this data is ready, let me put it back to normal.

In the next video, we will learn how to make a trend chart out of this till then, keep practicing keep learning. See you in the next video.