Using a pivot table on your ACH report

Owned by Brian McLaren (Deactivated)
Apr 26, 2021
2 min read

Pivot Tables can make it easier to sort and sum your ACH distributions. This is a basic skills level tutorial for how you can use off-the-shelf recommended Pivot tables in Excel.

In Netforum, we had over 3000 different reports because we had customized a basic reports in countless variations.  This often made it hard to find the report you needed.  In Fonteva, we've intentionally limited the reports to several basic reports that can serve a variety of business needs, but we've made them flexible so you can easily look at the data using tools like excel.  This gives you more control over what you're doing with your data. Remember: 

  • Everyone has different things they need to do.
  • Everyone has a unique way they do those things.
  • There is no right or wrong way to do any of it
  • We built Fonteva's reports to be flexible so that everyone can do the things they need to their data and get the result that is right for how they do their work.
  • Excel (and YouTube) is your friend!

Step-by-step guide

  1. Download your ACH report for the date range you want to analyze
  2. Make sure that any refunds are shown as negative amounts
  3. Select INSERT and RECOMMENDED PIVOT TABLES, then select SALE PRICE BY PRICE RULE from the recommended pivot tables.
  4. Drag DISTRIBUTION DATE into the COLUMNS area in the lower right of the screen - this shows you the totals by distribution date for each price rule
  5. You can swap out CONTACT with MEMBER NUMBER in the ROWS area if you would like the data detail to be shown by the member number instead. 

Google Sheets

If you don't have MS Excel, you can do the same thing in Google Sheets using almost these same steps. There will be some differences in where the areas are you drag and drop to/from, but the concepts are very similar.

In google sheets, pivot tables are found under DATA>PIVOT TABLE