Microsoft Dynamics GP with Excel Tip: Filter Unreconciled Bank Transactions using Pivot Tables and Slicers

Microsoft Dynamics GP with Excel Tip: Filter Unreconciled Bank Transactions using Pivot Tables and Slicers

Microsoft Dynamics GP was built to work with Microsoft Dynamics Excel. In this short video you will learn how to:

  • Create a PivotTable
  • Quickly reproduce Pivot table background data
  • Insert and use Slicers to filter data from Microsoft Dynamics GP

Transcription:

This is a list of unreconciled bank transactions.  You can take this list, put it into Excel, and then share it with others in your company.  Before you do that, it’s good to use a pivot table because that will not only summarize the information, but it will also allow you to remove any unnecessary information as well.  I’m going to show you some tricks with that now. 

To insert a pivot table I click inside the dotted area, go to the insert tab, pivot table, and okay.  I have a blank pivot table on the left there.  On the right hand side my fields list, I’m going to select paid to, received from, just for demonstration purposes, and instantly I have a summary of each of those unique items, so already it summarized that information.  I haven’t got duplicates running down that column.  And then if I take checkbook amount, I have the totals for each of those items.

 The good thing about this, is if I do want to see more data, for example, for Advanced Office Systems, this total here, if I want to know what items are involved in that total, all I have to do is to double click on that total, and instantly I have a new sheet with each of the items that contribute to that total.  This might be for viewing purposes only, or you might choose to use that information further as a stand -alone report, even representing it with a chart. 

Now if we return to our original pivot table in Sheet 1, I’ll show you an easy and appealing way to filter this information down.  If I go to the insert tab, and choose slicer, it brings up my fields.  I’m going to check posting date and source document just as an example.  You’ll notice it’ll bring up separate slices for each of those fields, and what I can do is use these to filter out the information.  I might want to view transactions from a certain date.  I can use my slicer for this. 

For example, if I want to view transactions from the 31st of January, 2014 all I need to do is click on that date in my slicer list and there you have it.  If I hold down control, I can select more than one at a time, and then it is very easy to clear, just click on the button in the top right hand corner. 

So, it’s the same as using your filter arrows, only it’s a lot easier for people that might not be as familiar with using pivot tables, and also it looks a lot snazzier than what your filter arrows do. 

For more great Microsoft Dynamics GP tips and tricks, visit www.calszone.com/tips and sign up for the newsletter.  For more great Microsoft Excel tips and personalized training by Skype visit www.savvytrainingaustralia.com

More Microsoft Dynamics GP with Excel Tips

By CAL Business Solutions , www.calszone.com

By |2014-10-23T10:08:43+00:00October 23rd, 2014|CAL ERP Tips & Tricks Blog, Dynamics GP Tips, GP with Excel/Word Tips, Microsoft Dynamics GP, Video Tips|Comments Off on Microsoft Dynamics GP with Excel Tip: Filter Unreconciled Bank Transactions using Pivot Tables and Slicers