• LinkedIn
  • YouTube
  • Twitter
  • Facebook
  • Google+

3 Great Excel Questions

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. [caption id="attachment_31699" align="alignnone" width="315" caption="Multiple Values on Same Pivoted Field"]Multiple Values on Same Pivoted Field[/caption] 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. [caption id="attachment_31700" align="alignnone" width="315" caption="Save Macro in New Workbook"]Save Macro in New Workbook[/caption] 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. [caption id="attachment_31701" align="alignnone" width="315" caption="IfError in Calculated Field"]IfError in Calculated Field[/caption] 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

Reply

Leave a Comment