Quick Links ▼

Bookmark page

Printable version

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Pivot table

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Pivot table

Resolved · High Priority · Version 2007

Reply Wed 20 Apr 2011, 14:21Delegate Emma said...

Emma has attended:
Excel Advanced course

Pivot table

When i select the data range for the pivot table, sometimes when i go to refresh i get an error - as the data range has changed to be only one line.

I then need to go and ammend the range for each of the tables.

Is there any way i can stop this from happening? or lock the data range so as it doesnt change?

Alternatively, is it something I am doing that changes the range? i.e deleting a row from the range etc?

Emma

For upcoming training course dates see: Pricing & availability

Reply Thu 21 Apr 2011, 11:02Trainer Rodney said...

RE: Pivot table

Hello Emma,

Hope you enjoyed your Microsoft Excel Advanced course with Best STL.

Thank you for your question regarding errors when refreshing your pivot table. Firstly we need to know what the error message is and are you working with multiple ranges?

If you can, please send us a sample file so that we can examine the situation first hand and come back to you with a solution.

We look forward to your reply.

Have a great day.
Regards,

Rodney
Microsoft Office Specialist Trainer

Reply Mon 9 May 2011, 15:28Trainer Rodney said...

Will be marked as resolved in 5 days

Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.

Reply Tue 10 May 2011, 11:28Delegate Emma said...

RE: Pivot table

Hello Rodney

The error i get is:-

"This command requires at least two rows of source data. You cannot use the command on a selection in only one row.
Try the following:
-If you're using an advanced filter, select a range of cells that contains at least two rows of data. Then click the Advanced Filter command again.
-If you're creating a PivotTable report or PivotChart report, type a cell reference or or select a range that includes at least two rows of data."

Then i go into the Change data source, and only one row is selected, so i reselected the whole range i require and save the workbook again. However, the next week, It happens again.

Is it because we are deleting rows from the source data?

Reply Wed 11 May 2011, 21:18Trainer Rodney said...

RE: Pivot table

Hello Emma,

I have created a Pivot Table using a data file in Excel. I then deleted a number of rows and then went to the design ribbon and refreshed the table. Unfortunately, I did not get the same result as you have.

Without having the file you are using before me, I am unable to say what is causing the problem you are having.

I'm sorry I can't be of more assistance.

Regards,

Rodney
Microsoft Office Specialist Trainer

Excel articles

· Microsoft Excel Training: Not Just for Newbies Anymore
· Want To Create A Presentation In Excel?
· Finding Your Way Around Excel 2007
· Want To Use Excel To Calculate Your Mortgage Repayments?
· How To Avoid The Embarrassment Of Dirty Data

Please browse our web site to find out more about
excel consulting and other Microsoft training courses.

Excel tip:

Change the default location for opening and saving spreadsheets

If you are always opening spreadsheets from and/or saving documents to a specific location that is not My Documents, save time by setting this folder as the default for opening files from and saving files to.

Here's how:
1. Go to Tools - Options.

2. Select the General tab.

3. Enter the pathname of the folder you wish to make the default in the Default File Location box (hint: it will be easier to use Windows Explorer to navigate to this folder, then copy and paste the pathname from the address bar at the top of the Windows Explorer screen).

4. Click OK.

You have now changed the default folder for opening and saving spreadsheets.

View all Excel hints and tips