Hello, everybody. Welcome back to dashboard training. In this video, we will learn how to give the labels in the tables directly from the data heading and convert these blank cells into zeros. Now in the older version, there was something we called classic view that was by default, but from 2010, the classic view has been disabled by default. Now if we wish to activate it, there's a very simple task. Just keep the cursor in the pivot table so that the voluntary tab appears.
So in the tab, Pivot Table Tools under design. To the left there is an option report layout. From the drop down report layout, select Show in tabular format. That's exactly like classic view where the headings are taken directly from the system and is populated in the place of row and column labels. Regarding the headings that can be changed manually, how before doing So first, let's fill up these blanks with zeros. Now if you try to punch in values manually, it gives us an error that says we cannot change this part of the pivot table.
That's because the pivot table is directly connected with the data. And any changes have to be made to the source data, which in turn gets reflected in the pivot table. So how do we convert it to zero then, so that our pivot table looks better. Now simply right click inside the pivot table, there's an option which says pivot table option. Click on it and a pivot table tool appears. Under layout and formatting just under formats.
There's an option that says for empty cells show currently This is blank in the place of the blank just punch in the value zero. same can be done for error in case we have any kind of formulas that is providing us an error. Once done, click on OK and done. There it is, all the blanks in the pivot table get converted to zeros. Now, to make them even more appealing, we can select all the data and from the Home tab from the drop down, we can go either with number, or the shortcut for the same might be Ctrl Shift exclamation that will convert the data into numbers. I'll adjust this column width and that should do it.
And now comes the second part. Whereas we wish to see these answers as an average, not according to the total. Now wish to see what is the average salary given an admin for basic knowledge for freshers PowerPoint non word. So let's move fresher to the end. This is fairly simple, just select hold on to the border and just pull it over. As we can see there's a thin line appearing between the columns and that's an indication that this particular field will be placed here.
Now we don't have to cut and paste. That's a very easy task. To arrange the data in a pivot table. Now let's get into average simply right click anywhere inside the data, there's a feature that says summarize the value by. So under the option it is giving us features of all the formulas that we can try out of which the default is always the summation. But do you remember if there is a blank cell in your data anywhere or any row that for that matter automatically it by default gives you count summation has to be done manually in that example.
So currently, we are looking for average, so I'll be selecting average and that should give me the relevant answers. Once done, save it just for backup. In the next video, we will see how to create a chart of this particular data. Till then keep practicing. See you in the next video. Thank you