Authentic voices. Remarkable stories. AOL On Originals showcase the passions that make the world a more interesting place.
The story of punk rock singer Laura Jane Grace of Against Me! who came out as a woman in 2012, and other members of the trans community whose experiences are woefully underrepresented and misunderstood in the media.
Documentary shorts conceived of and directed by famous actors. Jeff Garlin, Katie Holmes, Alia Shawkat, Judy Greer, and James Purefoy
Park Bench is a new kind of "talking show" straight from the mind of born and bred New Yorker and host, Steve Buscemi.
Digital influencer Justine Ezarik (iJustine) is back. After covering the world of wearable tech last season, iJustine is expanding her coverage this year by profiling the hottest tech trends across the country.
Enter the graceful but competitive world of ballet through the eyes of executive producer, Sarah Jessica Parker. This behind-the-scenes docudrama reveals what it takes to perform on the ultimate stage, the New York City Ballet. Catch NYCB on stage at Lincoln Center.
Nicole Richie brings her unfiltered sense of humor and unique perspective to life in a new series based on her irreverent twitter feed. The show follows the outspoken celebrity as she shares her perspective on style, parenting, relationships and her journey to adulthood.
Explore what it means to be human as we rush head first into the future through the eyes, creativity, and mind of Tiffany Shlain, acclaimed filmmaker and speaker, founder of The Webby Awards, mother, constant pusher of boundaries and one of Newsweek’s “women shaping the 21st Century.”
Gwyneth Paltrow and Tracy Anderson spend time with women who've overcome hardship, injury, and setbacks to triumph in the face of adversity.
Hank Azaria’s touching, humorous, and often enlightening journey from a man who is not even sure he wants to have kids, to a father going through the joys, trials and tribulations of being a dad.
ACTING DISRUPTIVE takes viewers inside the businesses and passion projects of Hollywood’s top celebrities.
Follow Scott Schuman, the Sartorialist, from the streets of NYC to the capitals of Europe on his quest to photograph and document the best in culture and fashion.
Go behind-the-scenes with racing's hottest, young talent, 17-year-old Dylan Kwasniewski, as he aspires to make it in the #1 motorsport in America – NASCAR
Learn how to add formula to a Pivot Table in MS Excel 2007.
Tags:adding formulas,excel advance training,microsoft excel 2007,microsoft windows,pivot table,total training
Grab video code:
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.