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 Tables

	98.1 % recommend us
	More live stats...

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

Training information:

Pivot Tables

Resolved · Low Priority · Version 2007

Reply Fri 1 Jul 2011, 15:13Delegate Julie said...

Julie has attended:
Excel Advanced course

Pivot Tables

I have played with the daily sales spreadsheet in the Unit 4 practice activity. I have inserted a column where I have put Budget and Actual and copied all the cells and altered the budget amounts to be higher than the actuals and then done % diff from Budget. It works fine but when I filter the report filter by product code and just choose one product I get #DIV/0!

I presume this is because for example this product was not sold in Jan and March so it causes this error.

How do I get round this.

All Product codes AG07

Sales Values
Jan Feb Mar Apr
Central Actual #DIV/0! -9.09% #DIV/0! 33.33%
Budget
South Actual -33.33% #DIV/0! -9.09% #DIV/0!

For upcoming training course dates see: Pricing & availability

Reply Fri 1 Jul 2011, 15:42Trainer Rodney said...

RE: Pivot Tables

Hello Julie,

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

Thank you for your question regarding the Unit 4 exercise. I have followed what you have done and also inserted a column for budget items with figures higher than the actual. I added a % Diff column and calculated the difference between budget and actual which of course gives me negative results.

I then created a PivotTable from this data and didn't get any errors. I added autofilter buttons to the main data sheet and tried all sorts of filters and still did not get any errors.

Please will you send me an email with the file attached so that I can see what you have done and I'll come back to you as soon as possible. My email is:

rl

I look forward to your reply, and wish you a great weekend.

Regards,

Rodney
Microsoft Office Specialist Trainer

Reply Fri 1 Jul 2011, 21:34Delegate Julie said...

RE: Pivot Tables

I have now solved this by supressing error messages in pivot table options which means I can have blanks instead of DIV#0.

Thanks

Julie

Excel articles

· Why Excel Has More Cloud Coverage
· Share And Share Alike - Use Excel To Collaborate As A Winning Team!
· Correct In, Garbage Out
· Create Buttons To Navigate Between Worksheets
· The Key Settings In Excel 2003's Options Panel

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

Excel tip:

Highlighting a data range

Attempting to use a mouse to highlight a large range of cells with data in Excel can make the mouse to have a life of it's own!

Use keyboard strokes instead.

Step 1. Place the cusor in the cell where the highlighting should begin.

Step 2. Select keystroke, CTRL+SHIFT+END

This will take the cursor to the furthermost bottom corner of the data range found in that worksheet. And highlight that range of cells at the same time

View all Excel hints and tips