Skip to main content

TIP - 38 : Get Pivot table data in regular or normal


Get Pivot table data in regular or normal


This trick will help us where we had a pivot table data which was used to link data to any cell.

We always get value of cell with “=GetPivotData” and if we use copy and paste to below range of cells, information on the cells were looks bit strange.


To avoid that situation to normalize the Pivot table data use the following trick –

Step 1: Go to File tab and click on options

Step 2: From the dialogue box Excel Options, go to Formulas section and uncheck the “Use GetPivotdata functions for PivotTable references" option, then click on OK

Now Pivot table data used for linking as normal data

Comments

Post a Comment

Popular posts from this blog

Hide cell contents by changing the cell format (in Excel)

Hide cell contents by changing the cell format (in Excel) Useful in situations where you have a small amount of data you want to hide but still want to be able to work with (and you’re not required to hide the entire column or row). And when you print the data as well the cell hided will not be printed. Step 1 – Select the cell or cells you want to hide. In this example, cell C3 to C6 is selected. Step 2 – Select the option Format Cells or click Ctrl + 1  Step 3 – The Format Cells options will appear; select Custom Step 4 – Type a three semi-colon marks (;) under  Type Step 5 – Select  OK See below, Cell C3 to C6 will appear as hidden (and hidden for printing) but included in the formulas.
Post a Comment
Read more

TIP - 45 : Text to columns do not import (skip) option

Text to columns do not import (skip) option When we have a data for text to columns, where we need only few columns rest you do not want to publish Here comes the solution in excel - Step 1: Please select the data that we want to do text to columns, click on Text to Columns from Data Tab Step 2: From the dialogue box Text to Columns Wizard, click of your choice Delimited or Fixed width Step 3: Click on next, select the delimiters then click on next again Step 4: Now select the column which you want to skip which will be highlighted in black then click on “Do not import column (skip), finally click on Finish Only columns which are not highlighted will be published .
Post a Comment
Read more

Evaluate the formula - step by step process

Evaluate the formula - step by step process This is the best trick to debug the complex formulas, each part of the formula can be evaluated individually. Step 1: Please select a cell with formula , then click on Evaluate formula under Formulas menu or tab Step 2: Dialogue box appear, click on  evaluate  which shows the result of the formulas in step by step process Once done click on close button.
Post a Comment
Read more