Creating a New Calculation in an Excel Pivot Table

Ever since I published the article on how to display both values and percentages in a pivot table & How to Get Running Totals in Pivot Tables, I am getting more and more emails asking about the other features and tricks within Pivot Tables in Microsoft Excel.

One such useful feature is the ability to add a new calculated field, which is not even present in the base data set, but can be derived from the existing fields.

We can call such a field as Adding a New Calculated Field in the Pivot Table. Once added, it is available to you in a pivot table as a regular field, and can be summed, counted, averaged, etc. with ease.

To Add a Calculated Field, go to the Pivot Table Options Menu, and Find the “Fields, Items & Sets” drop down menu in the ribbon.

Calculated Field Menu in Excel 2010, Excel 2013
Calculated Field Menu in Excel 2010, Excel 2013

Click and select the Calculated Field from the drop down menu.

Type a field name in the Name: field.

We will key in the formula in the next blank cell. Do note that Excel already provides you with a Zero there. You need to remove the Zero first. And then begin to type your formula.

Calculated Field in Excel Pivot Table
Calculated Field in Excel – Popup Screen

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


All the existing Fields are listed in the Field List below the formula.

Let’s say we have to calculate Bonus, which is 10% of the Sales Commission.

So the field Name we would give would be called Bonus.

The Formula would be =Sales Comm * 0.10

Click OK to close this popup menu.

You will now notice that a new field called Bonus is active in the Field List.

Double Click or Drag it to the Values Section of the Pivot Table Layout.

Voila! You have now created an new calculation called Bonus, based on existing values within the Pivot table, and it is now available for analysis in the Pivot Table.

You can use this simple technique to create as many pivot table fields, and enhance your analysis.

If you would like more such tips, you may subscribe to the ExcelChamp Excel Tips Newsletter or Excel Tips RSS Feed.

Cheers,
Vinai Prakash

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

PS: If you liked the tip, please do Like us, Stumble Upon on the Left Socials Bar, or Digg it!

How to Replace Blanks With Any Value or Zeros in Excel

This is a common problem. You import some text file, and half of it seems blank. The problem is that if it is showing blanks, you can’t sum the values.

To replace each blank with a zero is time consuming, boring and stupid. We want a fast method that works in just a few clicks, and saves us loads of time for our families 🙂

There are many ways to achieve this task of replacing Blank with Zeroes, or Replacing Blanks with Any Other Values in Excel.

Method 1: Use the GoTo Method to Find & Replace

Click on Home > Find & Select > GoTo Special. This will open up a new popup window.

Go To Special Popup in Excel
GoTo Special menu – Select Blanks

Choose Blanks Radio Button, and Click OK.

All the Blank Cells will get highlighted. Do not worry. Simply type a Zero or any other value that you want to put in the blank cells.

Do this in the currently active cell. Press

together.

Voila! The keyed in Values is showing in all the Blank Cells now.

Method 2: Use an IF condition

For example, if you want to selectively key in 0 for blanks, key in this formula below.=IF(A2=””,0,A2)

This formula checks if the cell A2 is blank or not. IF it is blank, it will replace it with a 0, or else it will not change it.

Once done, you can drag the formula to the selected cells. Works beautifully!

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


Method 3: Variation of the IF Method, using the ISBLANK function

Now we have an in-built function that can check if the cell ISBLANK or not.

=IF(ISBLANK(A2),0,A2)

Same Magic!

If you have any other favorite method, do share it with us below in the comments!

If you’d like to learn more Excel Shortcuts, Tips &  Tricks, here are some additional articles:

Questions About Any Feature in Excel?

If you have any questions regarding any feature in Excel, simply post a comment below and I’ll help you with it. All the best!

And Subscribe to our Weekly Excel Tips Newsletter, so we can send you more Short cuts & some Pivot Table Techniques by email. Only useful stuff, without the hype. And we won’t spam you. So don’t worry. Just subscribe!

Cheers,
Vinai Prakash, PMP, ITIL, GAP, Six Sigma
Founder of ExcelChamp.Net

Vinai conducts 1-2 day seminars and Training Sessions on Microsoft Excel all over the world, helping companies maximize their productivity in using Microsoft Excel. If you would like to find out more, simply contact us.

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

Do You Use These Features of Microsoft Excel?

Most people hardly use the most useful features available in Microsoft Excel. Many are just using Excel as a calculator to do basic calculations like adding or subtracting numbers. This is a gross under use of Excel’s vast potential and feature rich functionality.

Do a quick check, and see if you use these advanced features of Microsoft Excel in your day to day work to improve your productivity and efficiency.

  1. Finding the Top 10 Customers or Finding the Bottom 5 Performers in the organization
  2. Give Names to Range of Cells, and then use them in formulas for easy referencing and decoding – like =Salary – CPF + Allowance
  3. Exploit Pivot Tables to Summarize the data and slice & dice it in any way – like finding sales by product groups, or calculating productivity by department, or calculating sales by department by product by area.
  4. Write Macros to automate routine things that save you a huge amount of time – example creating different pivots, charts, tables, and doing complex calculations, or compiling the cost benefit ration, cash flow statement automatically.
  5. Highlight values that are above or below a certain threshold – like all sales above $25,000 to be highlighted, or all products with a net profit margin of less than 5% be highlighted.
  6. Sort the values in Ascending, Descending or any Customized sorting order – like sorting in order of Manufacturing, Accounts, Sales departments.
  7. Use advanced filtering conditions, and be able to filter data using multiple different criteria – like salary > 50,000 and sales <100,000 or Zone in East, West.
  8. Create fantastic charts that portray the given business situation perfectly. There are over 50 different types of charts to choose from, and each has its edge, advantages and a reason. Do you know when to use which type of chart – like two axis charts, stacked column and line charts, bubble charts, pie, slice of a pie etc.
  9. Create management dashboard that are dynamic, and provide a complete snapshot of the key business KPIs in the company – change the chart values at the click of a checkbox or change in a dropdown value
  10. Use Excel’s advanced What-If analysis to do projections for future, forecasting, trend analysis etc. with ease
  11. Use Lookup tables to find any value or corresponding value from a table using advanced functions and formulas – find values that in the left of the looked up value
  12. Protect certain cells from data entry, and hide the formulas from other cells. Ensure that only certain values are keyed in the cells to avoid data discrepancies.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


This is just the tip of the iceberg… These are common things that can be done easily with Excel’s in-built features.

So how many of these did you know? And how many would you like to learn more about? Even if you learn one new trick in Excel, it will save you immense time and improve your productivity. Investing in Microsoft Excel Training bears fruit in no time, and is a no brainer.

Microsoft Excel is really extremely powerful. Each version of Microsoft Excel – be it Excel 2007, or Excel 2010 or Excel 2013 adds more and more features to the already powerful dynamite of a package.

At ExcelChamp, we teach people how to leverage the maximum power out of Microsoft Excel in short training courses.

So what are you waiting for? If you would like to learn any one or more of such useful features of Microsoft Excel, just let us know. We provide online training through videos, which will show you each click, step by step, so you can master the new techniques easily, with exercises, and practical examples.

Go ahead, equip your team with the right skills. Get everyone on board to learn the basic and advanced features of Microsoft Excel, and Be Awesome in Excel!

Email to us to know more about Online Excel Training at ExcelChamp.Net.

Cheers,
VInai Prakash, PMP, ITIL, GAP, Six Sigma, Excel Guru

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!