Formatting a Value Field in Pivot Tables in Microsoft excel 2010 / 2013

Pivot tables is the most powerful feature in excel. It hardly takes much time in organizing the fields correctly in order to make meaning result out of the data.

There are many people who think that pivot table is complicated & time taking process but in my advice it is the best tool in excel that allows you to do analysis from various point of view.

Pivot tables allow various built in features like formatting a value. You can format a value of your choice in pivot tables.

Whenever you add numbers to pivot table, you want to format them.

Pivot table uses general formatting by default. Select cells directly in the pivot table and apply a format manually.
 

Let us format the below pivot table to number of counts

We have a sales report for five products.
 
img1
 
Pivot table report has been generated using sales data.
 
img2
 

  • To format a value field, click on Sum of Sales in Values section & select Value field settings.

 
img3
 

  • The following value field dialog box will appear.

 
img4
 

  • For numeric values, default option will be selected as sum.
  • Select various options like Count, Average, Max, Min, Product, etc. depending on the requirement & result will be updating accordingly.

 
img5
 
How to format numbers to currency

We have pivot table in general format, we can select the sales values in this pivot table and apply a currency format with no decimal places.
 
img6
 
To change the format to currency we have to follow the below steps:

 

  • Click on Value Field Settings

 
img7
 

  • Click on Number Format

 
img8
 

  • The Format Cells window will open
  • Select Currency from Category& enter 0 in Decimal places
  • Click on ok twice

 
img9
 
The format of values field in pivot table is changed to currency format.
 
img10
 
 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube