Excel pivot table not responding

Hi,

I'm puzzled by your description of trying to enter data into a pivot table. That's not the way they work. You 'Refresh' a pivot table (either automatically with a macro or manually) after you've changed the data range on which the PT depends.

Would you upload a cut down copy of your workbook (we don't need the full fat ~ 1 Mb version) that shows a representative sample of data and describe what actions you are trying to perform.

1. Your description is confusing and I think it is because you are calling the table from which the Pivot reports are created a Pivot table. You don't enter data into a Pivot table. You can have a table or range into which you enter data and the Pivot table is updated from that. If you enter data into a table and the Pivot tables are derived from the data in the table then all you have to do is to refresh each Pivot table and they are brought up to date.

2. The matter of being slow is probably due to the computer having to do too many calculations. This could be because of the use of full column references where not necessary or your workbook is calculating ranges that are not actually in use at present but have formulae in them.

3. You don't say what tool you downloaded to re-set the last cell and to clear excess formats. There is supposed to be a built-in tool in your version of Excel to do this. This article from Microsoft describes the tool and its use. https://support.office.com/en-us/art...9-4874F7474738

Hi

I'm guessing that you may have some rogue formulae perhaps down on the last row. If you press {End}{Home} where does the cursor end up.

Is there any formatting in place since that can often cause problems like you describe.

What happens when you select rows 6:1048576 (assuming you have Excel 2007 or later) and delete them and then do the same with columns U:XFD then immediately save the workbook close and reopen it. Does that reduce the size?

Can you upload the file? I would like to take a "crack" at it. Something just isn't right as a 1MB file is really nothing and normally shouldn't be a problem if the computer is of any power at all.

Just triggered a thought... have you cleaned out the temporary files on your computer lately? A utility like Ccleaner from www.piriform.com can do a decent job of cleaning out the crap in a computer that can slow it down. There is a free version and it works well.

Originally Posted by newdoverman

Something just isn't right as a 1MB file is really nothing and normally shouldn't be a problem if the computer is of any power at all.

I agree ND. What's even stranger is that a 20 column 5 row sheet would be as large as 1.2 Mb in the first place

100 cells (5x20) shouldn't be anywhere near 1MB. 15K might be a more reasonable figure unless there are large graphics involved or something else like that not mentioned.

I copied the table to a new workbook and renewed the names in the Name Manage. The file size is now 16 KB down from 1 MB
The workbook seems to work ok so there must be something corrupted or hidden in the file you uploaded. I could find nothing but the copy to a new workbook was greatly reduced.

....The file did indeed appear corrupted since it took ages to load, and after it finally opened it was as you found very unresponsive. After deleting all rows and columns so that to all intents and purposes the workbook was empty it still took ages to save.

For peace of mind it would be useful if you could establish when it last worked correctly and then try and understand what events took place after that before it became corrupted so that you can avoid the problem occurring again.

Did the server crash during a save for instance? I'd also be inclined to set the file to automatically backup so that should you encounter the situation again you will at least have the previous uncorrupted version.

There is a lot of rounding not taken into account by your check formulae. Figure how much rounding is taking place and you should find the differences.

I haven't checked all the monetary columns but columns M to R you have formatted as currency (two decimal places). If you change the formatting of those columns you will see that the real values are somewhat different than what currency displays.
You are testing using values that you entered and not calculated values. That will certainly explain some of the differences. To find all of the differences, all the names would have to be converted to ranges to see what is happening. In addition, you are using percentages and that is another area where errors in precision can occur.

	M	N	O	P	Q	R
1	Super	Insurances	Payroll Tax	Cost	Margin	Hourly Margin
2	2.850000	0.410625	1.626075	34.886700	4.103300	4.103300
3	61.750000	8.896875	48.754875	769.401750	180.598250	22.574781
4	2.850000	0.410625	1.790325	35.050950	7.889050	7.889050
5	3.325000	0.479063	2.088713	40.892775	7.107225	7.107225

The check values that you are using can't be entirely relied upon as they are not calculated values.
Try this for the error in row 5. You are using 7.11*150 and the answer is $1066.50 If you use the actual value in Q5 which is 7.1072 and multiply by 150 the answer is $1066.08.....there is the 42 cents difference. The .0028 difference in the displayed value and the calculated value doesn't look like much but in this case it is 42 cents.

The same thing happens if you use the values in row 4 only the difference is 14 cents.

As an after thought; all of the defined names are for entire columns. Change the references to start with the first row of data in the table to the last row of data in the table. The formulae will adjust as you add rows to the table and you are not calculating the whole length of the worksheet. I made the changes here and the calculation speed increase was rather dramatic.

To get the values that you want. I'm not sure that you should do this because it results in values that I would not consider to be accurate.

Formula:

Please Login or Register  to view this content.

Some of the slowness of the worksheet is due to the left to right to left order of the calculations. In other words the order of the calculations are back and forth instead of a progression from left to right and top to bottom. If you use the auditing tools under the Formulas tab Formula Auditing you will be able to see the arrows showing where the Precedent and Dependant values for the formulae are coming from and going to.

Capture 2.JPG

Originally Posted by Deesta

Ah, this has been an issue for a long time but I am not sure how I do this. Can you provide an example?

I'm assuming you are referring to the defined names.
Find the last row of the table and make note of the number of the row. This will be used to re-define each name.
Go to the Formulas Tab, click on the Name Manager, click on each defined name in turn and change the definition in the Refers to Bar. If you hit F2 each time before editing you will be able to edit without trouble..

Capture.JPG

I'm glad that you have the workbook working to your satisfaction. Thank you for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.