Video: Adding Pivot Table columns to enhance data analysis

Pivot Tables enable you to analyze your data efficiently, but some questions can only be answered by performing calculations on the data used to create your pivot table. To perform that type of analysis, you can create calculated fields that summarize pivot table data using a formula. Without calculated fields, you would have to copy the data from your pivot table, paste it onto another worksheet and create the formulas there. That's a pain. It's extra work that you absolutely do not have to go through. Now how to create a calculated field in an Excel 2007 pivot table isn't the most intuitively obvious thing.

Numbers and financial data drives today's business world and Excel 2007: Financial Analysis can help decode this information. The proper understanding of these numbers, and the formulas behind them, can be the gateway to corporate and personal success. Microsoft MVP (Most Valuable Professional) Curt Frye teaches basic fluency in corporate finance, enabling users to see the meaning behind essential financial calculations. Curt explains how to review formulas to ensure they have the proper inputs, and shows how to interpret formula output. He also covers how to calculate leverage ratios and amortization and depreciation schedules, as well as forecast future growth. Exercise files accompany this course.

Topics include:

Building a financial worksheet with Pivot Tables
Reviewing financial statements through common-sized balance sheets
Calculating percentage change over time in financial statements
Determining profitability ratios and return on investments
Studying liquidity and activity ratios through an average collection period
Computing the future value of an investment

Adding Pivot Table columns to enhance data analysis

Pivot Tables enable you to analyze your data efficiently, but some questionscan only be answered by performing calculations on the data used to create your pivot table.To perform that type of analysis, you can create calculated fields thatsummarize pivot table data using a formula.Without calculated fields, you would have to copy the data from your pivot table, paste it onto another worksheet and create the formulas there. That's a pain.It's extra work that you absolutely do not have to go through.Now how to create a calculated field in an Excel 2007 pivot table isn't the mostintuitively obvious thing.

So I'll just show you where it is on the user interface so you can do it later.To create a calculated field, first you select any cell in the pivot tableand then on the Pivot Table Tools Options tab of the ribbon, you go to theTools group and click Formulas and that's where you find the Calculated Field menu item.So click that and you get the Insert Calculated Field dialog box.And what you do here is create a name for your field.

The goal for this field is to calculate a company's revenue as a proportion ofthe cost of goods sold.So it will be dividing revenue by COGS.So for that one, I'll just call it Times COGS, press Tab and the formula will be equal.And then again, I want to divide revenue by cost of goods sold.So I'll click Revenue, insert that field into the formula, forward slash toindicate division and then I insert the Cost of Goods Sold field.

So there we have our calculated field and everything looks correct.So I'll click OK.The Times COGS field appeared in the Pivot Table Field List and it also appearsdown here in the pivot table values area.So at present, our pivot table has two data fields.We have the sum of revenue and we then we also have the sum of times cost of goods sold.So I'll remove the Revenue field from the data area for the moment andyou can see here, in January 2008, we had a ratio of 17:1 and the data field iscurrently configured the way it was before to display numbers as a currencyor accounting field.

So I'm going to change the data type just to make it easier to read.To do that, you right-click a cell that contains the data that you want tochange and click Number Format and from the list of available values, in this case I'll click Number and I'll put two decimal places on it.Click OK and there we have it.We have a ratio of 16.75, which again is different from 17.The rounding got taken care of when we added the decimal points.Calculated fields extends the types of analysis you can perform in Excel, whichmakes pivot tables even more useful for your financial analyses.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.

Already a member ?

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships.
Learn more

Upgrade to our Annual Premium Membership today and get even more value from your lynda.com subscription:

“In a way, I feel like you are rooting for me. Like you are really invested in my experience, and want me to get as much out of these courses as possible this is the best place to start on your journey to learning new material.”— Nadine H.

Thanks for signing up.

We’ll send you a confirmation email shortly.

Sign up and receive emails about lynda.com and our online training library:

new course releases

newsletter

general communications

special notices

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

new course releases

newsletter

general communications

special notices

Here’s our privacy policy with more details about how we handle your information.