Pivot Table Errors

PivotTable in Excel is an wonderful tool for Data Analysis. But we get few common annoying problem in PivotTable which we will solve in this article.

Few Excel problems which we encounter in PivotTable is listed below, we will discuss these points step by step.

1.   Column width changing when refreshed - prevent column width change

2.   New data not showing in pivot table

3.   PivotTable not Creating

4.   Old Items in the Drop Downs - Clear Old Items in PivotTable

5.   GetPivotData Formula - Remove GetPivotData from PivotTable

6.   Change Values from Count to Sum

7.   Show Zeros in Empty Cells

8.   Pivot Table automatically grouping dates into Year, Quarter, Month

Lets discuss...

1.   Column width changing when refreshed - prevent column width change

Steps :

1)          Right Click anywhere inside the pivot table

2)          Choose PivotTable Options

3)          In the PivotTable Options dialog, choose Layout & Format at the top, and the uncheck AutoFit column widths on update, then click OK.

Column width changing when refreshed - prevent column width change

Column width changing when refreshed - prevent column width change

2.    New data not showing in pivot table

This is because your newly added data is not selected by the PivotTable. You have to select the Range of data from PivotTable.

Steps :

1)          Select any cell in the pivot table

2)          On the Ribbon, click the Options tab

3)          Click Change Data Source select the complete data source range.

4)          Click OK

New data not showing in pivot table

3.  PivotTable not Creating

Please check you Data Source, to create PivotTable you need column name continuously - no blank column should exist between other columns.

4.   Old Items in the Drop Downs - Clear Old Items in PivotTable

Sometimes you will find that your PivotTable showing Old data in the filter although it is deleted from the source data.

To clear the old item from an existing PivotTable follow the following steps.

Steps :

1)          Right Click anywhere inside the pivot table

2)          Choose PivotTable Options

3)          Go to Data tab (as shown in the below picture)

4)          Change Automatic to None in the 'Retain items deleted from data source' section 'Number of items retain per field:' from the drop down button.

5)          Click 'OK'.

Old Items in the Drop Downs - Clear Old Items in PivotTable

5.   GetPivotData Formula - Remove GetPivotData from PivotTable

This was most annoying problem for me, you know sometimes we need to calculate beside PivotTable and for that we need to select the cells from the PivotTable - now when I select any cell after = , it always shows =GETPIVOTDATA("Target",$B$3,"Zone","North","Day","Friday").

Whereas I only need '=D7'. You can do it, you can stop GETPIVOTDATA, follow the following steps.

Steps :

1)          Select any cell in the pivot table

2)          On the Ribbon, click the Options tab

3)          Click on the drop down button in the left side Option button and

4)          Uncheck 'Generate GetPivotData'

GetPivotData Formula - Remove GetPivotData from PivotTable

6.   Change Values from Count to Sum

In a PivotTable you may need Count of items or Sum of Item. It is very easy to change a data value from Sum to Count or from Count to Sum, pleas follow the following steps.

Steps :

1)          Select any cell in the pivot table value column

2)          Right click and select 'Value field settings' from the pop up option.

3)          In the popup window you will see a list of functions, select as per your requirement and

4)          Click 'OK' to get the desired result.

Change Values from Count to Sum

7.   Show Zeros in Empty Cells

In PivotTable we get blank cells if there is blank in the source data, follow the following steps to get zeros in the blank cells in a PivotTable.

Steps :

1)      Select any cell in the pivot table

2)      Right click and select 'PivotTable Options...' from the pop up option.

3)      Enter  '0' or '-' or any other character in the box beside 'For empty cells show' as shown in the below picture.

Show Zeros in Empty Cells

8.   Pivot Table automatically grouping dates into Year, Quarter, Month - solved

If date is the in the source data and we insert PivotTable in Excel 2016 date field comes in Qtr grouping.

Solution : If you need the date field just select that cell and Right click on that cells, from the popup options select 'Ungroup' and you will see that the Grouping is broken and date appeared in the PivotTable.

Pivot Table automatically grouping dates into Year, Quarter, Month

I assume that you know how to create PivotTable in Excel, if you are not familiar with Pivot Table, you may read the following Articles .

Previous Articles : -

• Pivot Table in Microsoft Excel - Pivot Table Field List Report Functions of Filter Column Labels Row Labels Values

• Customize Pivot Table - Step by Step

• Pivot Table Calculated Field - Step by Step

• Pivot Table Calculated Field vs Calculated Item

Hope you have enjoyed ...

Keep reading & Stay blessed ...

DB Function

In this article we will discuss about DB Function, another financial function in Microsoft Excel.

Here 'DB' is not referring to Database, it is Depreciation in accounting. 

DB function  returns the depreciation of an asset for a given time period based on the fixed-declining balance method.

Before discussing DB Function let us first understand what is depreciation and how many types of depreciation in accounting.

What is Depreciation ?

By definition, 'Depreciation' is an accounting method of allocating the cost of a tangible asset over its useful life and is used to account for declines in value.

In simpler language 'Depreciation' is the value of an tangible asset after wear and tear.

What Are the Main Types of Depreciation Methods?

There are several types of depreciation expense and different formulas for determining the book value of an asset. The most common depreciation methods are...

1.   Straight-line (Its very simple in calculation, a fixed amount will be deducted every year till the 'Life')

2.   Fixed declining balance

3.   Double declining balance

4.   Units of production

5.   Sum of years digits

In this article we will discuss about Fixed Declining Balance method.

Syntax

The syntax for the DB function in Microsoft Excel is as under

=DB(cost, salvage, life, period, [number_months])

Parameters or Arguments

cost

The original cost of the asset.

salvage

The salvage value after the asset has been fully depreciated.

life

The useful life of the asset or the number of periods that you will be depreciating the asset.

period

The period that you wish to calculate the depreciation for. Use the same units as for the life.

number_months

Optional. It is the number of months in the first year of depreciation. If this parameter is omitted, the DB function will assume that there are 12 months in the first year.

How to use the DB Function in Excel?

To understand how to use DB function in Excel, let us take an example.

Example :

Suppose a machinery is purchased in this year and the related information are

1.   Cost of the machine : $100000

2.   Salvage Value : 10000

3.   Life of the machine : 5 years

4.   Period : 1, 2, 3, 4 & 5 (as the life of the machine is 5 years)

Here after implementing the formula for DB function the results are shown in the below image and the formula will be for the 1st year =DB(C3,C4,C5,C6) - the amount is $ 36900.

How to use DB Function in Excel

In next article we will discuss about the DDB function in Excel.

Note : The DB function uses the fixed-declining balance method to compute the asset’s depreciation at a fixed rate.

The formula used by DB to calculate depreciation for a period is,

=(Cost – Total depreciation from prior periods) * Rate

and the calculation of Rate is,

Rate = 1 – ((salvage / cost) ^ (1 / life)).

Different formulas are used for first and last periods.

For the first period, DB uses the formula

=Cost * Rate * Month / 12

For the last period, DB uses the following formula is 

=((Cost – Total depreciation from prior periods) * Rate * (12 – month)) / 12

Keep in mind you need to apply the same formula in excel. You don't need to calculate the above formulas.

Hope you have enjoyed ...

Keep reading and sharing ...

Take care...

NPER Function

In last article we have discussed about PMT Function to get the amount of the payment or premium or EMI per month.

But suppose we have the premium amount and other information but not the period (number of months) and we need to calculate the number of months then we need to use another formula to get the period. Microsoft Excel provides an excel function NPER Function which will help us to get the 'period'.

 Today in this article we will discuss about NPER function.

Suppose we have the following information, like Loan Amount, Interest Rate, Installment Amount and Compounding period per year and we need to derive the number of months from these information to replay the full amount with interest. In this situation we will use NPER Function.

So, let's see how to calculate the period (number of months) to repay the amount.

For Example let suppose we have the details with us,

1.   Loan Amount (suppose $7000)

2.   Interest Rate per annum (let it be 7.50%)

3.   Installment Amount ($150)

4.   Compounding period per year (suppose every month i.e., 12)

What does Compounding Periods Per Year mean?

The compounding period is the number of times that unpaid amount interest is added to the principal amount of the loan.

 In simpler words, it is the number of times a bank or lender calculates the due amount.

If  'n' be the Compounding Periods Per Year the

if interest is compounded yearly, then n = 1;

if semi-annually, then n = 2;

quarterly, then n = 4;

monthly, then n = 12;

weekly, then n = 52;

daily, then n = 365; and so forth, regardless of the number of years involved.

I think you got a clear idea about Compounding Periods Per Year, now we will move to the NPER Function.

Syntax

The syntax for the NPER function in Microsoft Excel is very simple,

=NPER( interest_rate, payment, PV, [FV], [Type] )

Parameters or Arguments

interest_rate

The interest rate for the loan.

payments

The amount of the payment for each period. This should be entered as a negative value.

PV

The present value or principal of the loan.

FV

It is optional. It is the future value or the loan amount outstanding after all payments have been made. If this parameter is omitted, it assumes a FV value of 0.

Type

It is also optional. It indicates when the payments are due. If the Type parameter is omitted, it assumes a Type value of 0. Type can be one of the following values:

0 = Payments are due at the end of the period. (default)

1 = Payments are due at the beginning of the period.

Example :

Let us calculate the amount payable in every month based on the above information.

The formula will be =NPER(C5/C7,-C6,C4).

One question may arise in your mind why we divide the interest rate by compounding periods per year ?

This is because we have taken the interest rate as per year and the lender calculates the loan payment amount per month. We need to break down the full year interest rate by the Compounding Periods per year.

The below image will clarify NPER function.

How to use NPER Function in Excel

Hope you have enjoyed ...

Keep reading & Stay blessed ...

PMT Function

In this article we will discuss about PMT function, one of the financial functions available in Microsoft Excel which calculates the payment for a loan based on constant payments and a constant interest rate. We use PMT function in Excel as formula to figure out a monthly loan payment.

Last year I have to take a loan from Bank for short period and I was trying to calculate the amount in Excel that I must pay per month for that borrowed amount.

Surprisingly, Microsoft Excel provides a very easy function, 'PMT function' which is very easy to calculate the amount payable for a loan.

Let us discuss about PMT function but before that let us first write down the information we have.

Information with us for a loan

1.   Loan Amount (suppose $7000)

2.   Interest Rate per annum (let it be 5.50%)

3.   Periods, the number of months (suppose 48 months)

4.   Compounding period per year (suppose every month i.e., 12)

Note : Out of these four available data or information, the first three are easily understandable but the fourth information needs to be clarified to some persons. Honestly speaking it is little confusing to a newbie.

What does Compounding Periods Per Year mean?

The compounding period is the number of times that unpaid amount interest is added to the principal amount of the loan.

 In simpler words, it is the number of times a bank or lender calculates the due amount.

If  'n' be the Compounding Periods Per Year the

if interest is compounded yearly, then n = 1;

if semi-annually, then n = 2;

quarterly, then n = 4;

monthly, then n = 12;

weekly, then n = 52;

daily, then n = 365; and so forth, regardless of the number of years involved.

I think you got a clear idea about Compounding Periods Per Year, now we will move to the PMT Function.

Syntax

The syntax for the PMT function in Microsoft Excel is very simple,

= PMT(interest_rate, number_payments, PV, [FV], [Type])

Parameters or Arguments

interest_rate

The interest rate for the loan.

number_payments

The number of payments for the loan.

PV

The present value or principal of the loan.

FV

It is optional. It is the future value or the loan amount outstanding after all payments have been made. If this parameter is omitted, it assumes a FV value of 0.

Type

It is also optional. It indicates when the payments are due. If the Type parameter is omitted, it assumes a Type value of 0. Type can be one of the following values:

0 = Payments are due at the end of the period. (default)

1 = Payments are due at the beginning of the period.

How to use PMT Function in Excel ?

Example :

Let us calculate the amount payable in every month based on the above information.

The formula will be =PMT(C5/C7,C6,-C4).

One question may arise in your mind why we divide the interest rate by compounding periods per year ?

This is because we have taken the interest rate as per year and the lender calculates the loan payment amount per month. We need to break down the full year interest rate by the Compounding Periods per year.

The below image will clarify PMT function.

PMT Function

I hope you have enjoyed reading this article ...

Keep reading & Sharing....
Stay blessed ...