For this lesson, you need to open the sample file: Transactions-13, from your Sample Files folder.

I'm going to begin this lesson, by simplifying the Pivot Table by removing columns C and D. And to do this, I click inside the Pivot Table, to activate it. And at the bottom of the Field list, I'll take the average value and drag it away from the Pivot Table, and the max value, and drag that away from the Pivot Table.

I now have a very simple Pivot Table, simply showing the sales by employee.

Now, my challenge, for this lesson, is to add a Bonus column, to column C. That will show 3% of the sales value, in column B. And we're going to do this, by adding a calculated field to the Pivot Table.

Now, adding a calculated field isn't always the best approach. Often, it's simpler to add the field to the underlying data.

In the case of this Pivot Table, we have access to the Data worksheet. So a simple way of adding bonus, would be simply add a new column to the table, calculate the total sales, multiplied by 3%.

And then, return to the Pivot Table, refresh the Pivot Table (to see the new field). That's: PivotTable Tools: Analyze: Refresh. And then, the Bonus field will appear in the PivotTable Field list. And I can show it on my Pivot Table.

But that's not the approach I'm going to use in this lesson. I'll just return to Data, now, and I'll delete that column.

I'll return to my Pivot Table. Of course, I'm still seeing Bonus, because I've not refreshed. So: PivotTable Tools: Analyze: Refresh, and Bonus has gone again.

Now, why would you not want to do it like that? Well, in Lesson 5-4 you learned how to connect a Pivot Table to an Access database. And when working with external data sources, it's often not possible to add calculated fields to the data source itself.

Calculated fields provide a useful solution to this problem. So let's add a calculated field to this Pivot Table.

To do that, I click anywhere in the Pivot Table, to activate it. Then: PivotTable Tools: Analyze, on the Ribbon. And in the Calculations group, notice there's a Fields, Items and Sets dropdown.

If I click: Fields, Items and Sets, you can see that one of the options is a Calculated Field. We'll be covering Calculated Items and Sets later in this course.

So I'll click: Calculated Field, and I'll give my Calculated Field a meaningful name: Bonus. Then, I'll click to the right of zero, in the Formula box. I'll Backspace, to remove the zero.

And in the Field list, you can see I have a Total field. So I'll select: Total, and click: Insert Field. I could have just typed Total, but doing it this way means it's impossible to make a spelling mistake.

And for my 3%, multiplied by 3%.

I then click: OK, and you can see that my new Calculated field has appeared in the Field list. And it's even been checked, by default. So it's been added to the Pivot Table, as well.

I think I'll tidy up the formatting of Bonus, too. By right-clicking in one of the cells: Number Format: Number, in category: two decimal places and a thousand separator.

That looks better. And let's do a little bit more work on the appearance of this Pivot Table. Sum of Total, in cell: B4, is a little long-winded. Why don't I change that to: Sales.

And Sum of Bonus, I'll change that to: Bonus Due.

Now, to an experienced Excel user, this Pivot Table is really useful. But an inexperienced user could be a little intimidated by all of the artifacts on screen. They might wonder what on earth the Pivot Table Field list is. And what that Filter button means, in cell: A4.

So when you create Pivot Tables to distribute to inexperienced Excel users, it's often useful to hide some of the Pivot Table artifacts. So that they don't become confused. And to do that, I'll click anywhere in the Pivot Table, to activate it: PivotTable Tools: Analyze, on the Ribbon.

And notice there are three options, in the Show group: Field List, +/- buttons and Field Headers. These are all Toggle buttons that allow you to switch off Pivot Table features.

A novice Excel user might be confused by the PivotTable Field List that appears every time they click within the Pivot Table, and activate it. So I'm going to click: Field List, to switch that off. And now, even when the Pivot Table is activated, the user will never see that PivotTable Field List.

The option +/- Buttons will let you hide those Collapse/Expand +/- icons, when your Pivot Table has several fields, within it.

Now, in this case, we don't have subsidiary fields, so there's no Expand/Collapse buttons. But if there were, you could switch them on and off with this Toggle button.

The last option (Field Headers) will let us hide that Filter button, in cell: A4. Let's click: Field Headers, now, and the Filter button has been switched off.

But notice that a side-effect has been to also remove the column header (Employee), from cell: A4. And, unfortunately, we can't just click in cell: A4, and type it in again, because you can't type any values into that part of the Pivot Table.

So what about if, for presentation reasons, you really wanted that column header, in cell: A4? Well, you can around the limitation like this.

Select cell: A4 to C4: Copy: Paste, into the row above. And then, hide row 4. You can then type: Employee, into the column header.

But let's put things back the way they were, again now. So I'll click back, inside the Pivot Table, to activate it: PivotTable Tools: Analyze, on the Ribbon. And in the Show group, I'll show everything.

Of course, I can't see row 4, at the moment, because I hid it. So let's select rows 3 and 5, right-click, and: Unhide. And then, I can delete row 3.

Well, all that remains, for this lesson, is to save your work. And I'm going to save with the new name: Transactions-14. And, as usual, I'll save to the folder above my Sample Files folder.

I click the Save button, and you've now completed Lesson 5-17: Add a Calculated field to a Pivot Table.