When I started this lesson on pivot tables, I said that pivot tables were a great way to create a summary without having to air any formulas at all. But sometimes, you are going to need to create a formula in order to make your pivot table do something else. In this particular data set, we have fields for Revenue, Cost of Goods Sold, Profit, and Quantity. What if we needed to calculate a gross profit percent? That is profit divided revenue. What is possible is to add a brand new field to our pivot table field list using a formula. Now, let us restructure our pivot table a bit. I am going to take this new customer two field and remove it from the pivot table report.

I am going to add the customer field along the left hand side. Currently, the report just has Revenue. I am going to add Profit as a second sum values column. All right, now we want to add a new formula to our pivot table. The formulas are controlled on the options tab of the ribbon using the formulas drop down. There are two kinds of formulas that you can add a calculated field and a calculated item. A calculated field adds a brand new field to the pivot table field list and that is what we want to do in this case. So I choose calculated field and Excel show me all of the fields that are available in the pivot field list.

First, I am going to give this field a name. Perhaps a name like profit percent and then I have to build a formula. Initially, the formula starts out with equal zero. I need to get rid of the zero. So, I am going to click to the right of the zero and hit the back space key to get rid of the zero. Now, my formula here is pretty simple. It is Profit divided by Revenue. I can click on the Profit field and then click on the Insert field. Next, I want to type the divide sign. This is just like building Excel formulas. The slash is used for divide and now I want to insert the revenue field.

Now, instead of clicking on revenue and then insert field, you can simply double click the revenue field. There I have a new field called profit percent. It is profit divided by revenue. I will click okay and the new field is automatically added to my pivot table. If you notice, if I could format that field as a percentage, if I select one cell in the field and then use the field settings button in the ribbon. In the lower left hand corner of this dialog is the number format button. Choose the number format that you need. In this case, percentage with either zero or one decimal places would probably be appropriate. Excel has now added a new virtual field to our data set that is a formula that is a new pivot field.

Pivot fields are very useful. I find all sorts of occasions where I could use them. The other type of formula is a pivot item and I need to warn you that pivot items could be very dangerous if you do not understand how they work. There is also a very specific limitation. You could not create a calculated item if you have any grouped fields in the pivot table. Now, we have already grouped customer and we have grouped years. The best thing to do at this point is to delete the pivot table and start over. So, I am going to delete sheet one. I am going to right click the sheet tab and choose delete. We are back to our original data set. I am going to create a pivot table again very quickly.

Remember, we choose insert and then pivot table, click okay and now we have a fresh pivot table. I am going to add the region as down the left hand side and revenue with some values. Now, let us say that in another company, there is just a rework and there is now a Vice President in charge of the central and north regions. You want to create a new item along the region field that will sum those two regions up. I want you to take a look at the total revenue in cell B7. We have $13.2 million of revenue. In order to create a calculated item, I need to choose one cell along the region field. Choose central and now, when I go to the formulas drop down, the calculated item is a valid value.

I am going to create a new formula, give the formula a name and type a formula of central plus north, click okay. Now you see what happened, Microsoft has added a brand new item along the region dimension, new VP and it happens that that $8.3 million is the $4.6 million plus $3.6 million. However, they have added that total and our company is now a $21 million company instead of a $13 million company. Very, very frustrating the way that this works, the only way that this would ever make sense is if we remove the central and north regions from the pivot table entirely. Deselect central, north and now we are back to $13.2 million. If you use calculator, please be very, very careful that you do not accidentally leave the source items and the calculated item along the same dimension.