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"][/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"][/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"][/caption]
If, like me, you learn from other peoples questions, consider subscribing to Office Technology Today, Business Management Daily's "Everything Office Technology" newsletter!
Like what you've read? ...Republish it and share great business tips!
Attention: Readers, Publishers, Editors, Bloggers, Media, Webmasters and more...
We believe great content should be read and passed around. After all, knowledge IS power. And good business can become great with the right information at their fingertips.
If you'd like to share any of the insightful articles on BusinessManagementDaily.com, you may republish or syndicate it without charge.
The only thing we ask is that you keep the article exactly as it was written and formatted. You also need to include an attribution statement and link to the article.
" This information is proudly provided by Business Management Daily.com: http://www.businessmanagementdaily.com/31698/3-great-excel-questions "