3 Great Excel Questions — Business Management Daily: Free Reports on Human Resources, Employment Law, Office Management, Office Communication, Office Technology and Small Business Tax Business Management Daily
  • LinkedIn
  • YouTube
  • Twitter
  • Facebook
  • Google+

3 Great Excel Questions

Get PDF file

by on
in The Office Tech Pro

Some people ask why I keep teaching these applications? "Don't I get bored?" Not with great questions like these! One of the following questions I had solved awhile back for my own use. The other two started out as "I don't know, let's see..." An attendee at a webinar recently asked, “Can I show Sum, Count and Average for the same field in Subtotals?” While my instinct had me head for Pivot Tables, I didn’t know if I could accomplish that particular goal. Then, lo and behold, I simply dragged the same field down to the Values section two additional times and used Value Field Settings to change the value type.
Multiple Values on Same Pivoted Field

Multiple Values on Same Pivoted Field

At a recent class, a question came up around sharing macros. “How would I share my macros that go with multiple workbooks, with multiple users?” First, when beginning the record macro process, I suggested selecting the New Workbook option. This records the macro in a workbook other than the one in which you are performing the operations. Name the file something like, MonthEndMacros. Instruct the users to open that workbook when working on the process. Another alternative would be to create an Excel Workspace to automatically open all necessary files, including the special macro workbook you created.
Save Macro in New Workbook

Save Macro in New Workbook

One more great question from the past weeks classes: “What can I do to get rid of #DIV/0 errors in my Pivot Table?” In this particular case, using the new function, IfError did the trick. For example, if you desire an average for the value in your pivot table, no reported results may yield a #DIV/0 error. Rather than just changing the Value Field Settings to Average, create a Calculated Field, wrapping the Average function in an IfError function, showing a zero in place of the error. One caveat! Be sure that the error condition is predictable, the IfError value-if-error will show no matter what error condition results. If in doubt, use a specific If statement in a calculated field to take care of the targeted error condition.
IfError in Calculated Field

IfError in Calculated Field

If, like me, you learn from other peoples questions, consider subscribing to Office Technology Today, Business Management Daily's "Everything Office Technology" newsletter!

{ 1 comment… read it below or add one }

Orlando Mezquita June 24, 2012 at 3:54 pm

Another way of getting rid of the errors on Pivot Tables is to go to:
Pivot Tables Options -> Layout & Format -> For Error Values Show: 0


Leave a Comment

Previous post:

Next post: