Calculated Fields

Loading...

En provenance du cours de PwC

Problem Solving with Excel

2449 notes

PwC

Problem Solving with Excel

2449 notes

Cours 2 sur 5 dans Specialization Data Analysis and Presentation Skills: the PwC Approach

This course explores Excel as a tool for solving business problems. In this course you will learn the basic functions of excel through guided demonstration. Each week you will build on your excel skills and be provided an opportunity to practice what you’ve learned. Finally, you will have a chance to put your knowledge to work in a final project. Please note, the content in this course was developed using a Windows version of Excel 2013. This course was created by PricewaterhouseCoopers LLP with an address at 300 Madison Avenue, New York, New York, 10017.

À partir de la leçon

Logical Functions & Pivot Tables

In this module you will learn about logical functions and pivot tables. We will show you how to create and use pivot tables to solve business problems. We will give you an opportunity to practice creating and using a pivot table to solve a business problem. Finally, we will share some insight on PwC’s perspectives on the impact of Excel on your career and work.

Pivot Tables4:34

Navigate a Pivot Table7:49

Sort a Pivot Table3:43

Advanced Filtering5:53

Calculated Fields3:26

Rencontrer les enseignants

Alex Mannella
Alumni / Former Principal

[MUSIC]

In this next section,

we're going to learn how to use a calculated field in a Pivot Table.

Adding a calculated field to a Pivot Table allows you to calculate an amount

using the variables already within your Pivot Table.

So you don't need to do it in a separate Excel workbook or using a calculator.

It's also easy to add in to your already formatted table.

In problem 6A, we would like to insert a calculated field for tax,

which is 7.5% of revenue.

We're also going to add in sales channel as a column.

So first, I'm going to click into the pivot table, and I'm going to move over to

our Pivot Table Field menu so we can change the variables that we're showing.

So right now, we have the sum of quantity in our table, but

it looks like we're going to be referencing revenue.

So let's change the value from quantity to revenue.

I'm going to click the arrow next to Sum of Quantity and click Remove Field.

Next, I'm going to go up here to our variables, click on Revenue and

drag it down to Values.

The question also asks us to include Sales Channel as a column.

So I'm going to go back to our Pivot Table Field menu, click on Sales Channel and

drag that down to the Columns.

The next step is actually creating our calculated field.

So first I'm going to make sure that we're clicked into our current pivot table, and

then up top in our Analyze ribbon.

I'm going to click on Fields, Items, and

here you can see an option is calculated fields, so I'm going to click that.

Be sure to name your field because if you're making many calculated fields,

you want to remember so you can refer to them in the future.

So in the name section, I'm going to type in tax.

For the formula, I'm going to delete out the zero, and

we would like tax to be 7.5% of revenue.

So, you can see in the menu below are all the different options

of what we can refer to, and we are going to look at revenue.

So, I'm going to double-click revenue to bring it into our formula function.

And then type multiplied by 7.5%,

which is 0.075 and hit OK.

Now, when we go into our pivot table fields,

we can see that there is a tax option.

So I'm going to click that.

And now we can see the revenue and

tax for each of our products separated by sales channels.

It's important to think of formatting at every step of

the process when creating a pivot table.

And since we're dealing with all dollar values here,

I'm going to highlight our pivot table.

Go to the Home ribbon, and in this Number Section, we can change it to Currency.

So now we'll add dollar signs and decimal points to our data.

Now it's your turn to try to create a calculated field.

In problem 6B, you'll be inserting a calculated field for

cost which is 50% of revenue

[MUSIC]

Explorer notre catalogue

Rejoignez-nous gratuitement et obtenez des recommendations, des mises à jour et des offres personnalisées.

Coursera propose un accès universel à la meilleure formation au monde, en partenariat avec des universités et des organisations du plus haut niveau, pour proposer des cours en ligne.

© 2018 Coursera Inc. Tous droits réservés.

Télécharger dans l'App Store

Disponible sur Google Play