Once you export your General Ledger report from Microsoft Dynamics GP to Microsoft Excel there is so much you can do with it. This video will show you a few helpful tricks around pivot tables. Pivot tables are the best way to summarize large amounts of data and focus on the information you need.
In just four minutes you will see how to:
- Create a pivot table
- Simplify data columns with the grouping feature
- Quickly format your pivot table to be easier to read.
Transcription:
I’m going to show you a couple of useful tricks that you can use once you get a list of all your General Ledger activity from Microsoft Dynamics GP into Excel. We will cover three main areas or features of Excel that you can use. One is summarizing the pivot table. The next will be grouping and simplifying fields, and the third will be quickly formatting that data. Okay, let’s get started.
See here I have a sample General Ledger transaction list. I’m going to convert that into a pivot table because pivot tables are the best way to summarize a large amount of data and to focus on the information that you really need at that time.
So to do that, I go to the insert tab at the top left of my screen. I select pivot table and okay. And you’ll see now I’ve got a blank pivot table on my screen. I’ve clicked inside that pivot table which is why I’m seeing at the top here, pivot table tools, analyze, and design tabs, and then also on the far right I have the pivot table field list.
If I click outside of that pivot table, those will disappear. So make sure you have clicked inside that. If you want to add data, you have tick boxes here and you can also click and drag these fields into the various areas of your pivot table. I’m going to just use as an example my account description. I’m going to drag that to the rows and all the unique fields from that column in my original database has been added down those rows. I’m then going to pick up my TRX date. I’m going to drop that into my columns field. And you’ll see the dates are running across the top. It’s not ideal the format that it is in yet, but we’ll fix that up in a moment. And then I’m going to grab the credit amount and I’m going to put that into the values field, and you’ll notice all credit amounts being added to those columns as well.
So we’re going to simplify this and make this a little bit easier to read. First of all, with the dates. You’ll notice if I click on a date there, we’ve got all of the unique dates running across the top. We might actually want to see only the months and the years. So we can easily do that by grouping these fields. We go to the Analyze tab at the top here, and we select this button here, group field. We choose what we want it to be grouped by, so I’m going to select months and years so you have more than one. And you notice now there are dates and not only grouped by year but also by month. Now if I only want to see the years, what I can do now is I can go up to the minus sign or the collapse field button, once again on the analyze tab. I can select that and now those columns are all grouped by year. If you want to expand those fields, it’s just a matter of clicking the expand field button or going to the individual year and selecting the plus sign next to that and that will show you any detail within that group.
Now, you’ll notice some of the numbers, for the sum of credit area, are actually quite long, and also it’s turned into scientific notations. So if you want to fix that quickly, we just need to go to the values section in the bottom right-hand corner. Click on the area next to sum of credit, value field settings, number format, and we’ll just change that to a number-style category, and that will remove any scientific notation, and then we’ll take that down to zero decimal places, just to clean that up a little bit.
And finally, if you want to make that look a little bit more professional or format that, a really quick and easy way to do that is to go up to the pivot table tools design tab, expand open the gallery, and to choose a style straight from there. You’ll notice there are plenty of options in line with the theme that you have applied to your Excel spreadsheet. You just need to select one of those and there you have it. I hope these tips will help you get the most from your Excel data, all the best at trying them out.
For more great Microsoft Dynamics GP Tips and Tricks, visit www.calszone.com/tips and sign up for the newsletter.
More Microsoft Dynamics GP with Excel Tips
By CAL Business Solutions, www.calszone.com
