top of page
Image by Jonathan Velasquez

Turnkey 365 Podcast - ERP Academy

Turnkey 365 ERP Academy - Using Excel in Microsoft GP and BC

Introducing the Turnkey 365 ERP Academy! We're setting out to teach you anything and everything you'll need to know about how to make the most of all of your Microsoft Dynamics ERP's features and capabilities. In our first episode, host Chuck Coxhead is joined by Turnkey team members Kurt Quiggle, GP Consultant, and Bob Richardson, BC Consultant, to discuss the various advantages and drawbacks of using Excel in GP and BC, and its ability to provide business owners and staff great insights and clear reports.

See Below for the full transcription of this episode!

Chuck Coxhead (00:00):

What strikes me as really cool about this is that the fact that office is in the cloud and the fact that BC is in the cloud is what really enables this seamless native integration. Just as if when you're in an on-premise installation with gp, you're in a similar, you're in the same environment, you have a desktop version of Excel, you have a desktop version of gp, and then the cloud is able to replicate that through essentially your browser. And it is that, it is that native whole ecosphere that Microsoft has created, which makes all this possible and seamless and slick. 


Hey, welcome to the turnkey 365 ERP Academy. I am joined by Kurt Quiggle and Bob Richardson. There are a couple of the folks from our talent pool here at Turnkey Technologies, and we're going to get into something good and basic, but so fundamental and so important to organizations. And that is we're going to take a look at using Excel with Microsoft Dynamics, both from the GP perspective and also from the business central perspective. It really is an indispensable tool for helping folks to prepare reports and so much more. And so I thought, what better thing to do than bring the experts in here to show you how it works, and also to talk about any advantages and disadvantages. Gents, welcome. Thanks.

Kurt Quiggle (01:20):

Hey, thanks for having us.


Chuck Coxhead (01:22):

Well, hey, it's your company too, right? We're just here to have a little fun and teach the folks something new and thanks for your time. So I guess there was, I remember when I first started in ERP, I started on Man, man, actually it was MRP back in the Stone age. I think we used to chisel it out of Stone and Greenstone, so it came up on the green screen there. But certainly back in that time using Excel was, I mean, that was like Voodoo man. I'm not even sure. I think we were on Lotus 1, 2, 3 at that point, let alone Excel. Now fast forward, we have this amazing tool where we can export data, use it, and particularly if you're in the finance world, I mean turn it into amazing reports. Tell me your experiences on some of that. Kurt,


Kurt Quiggle (02:13):

Going back all the way, when I started going both directions, whether it was data out or data in, at the end of the day, people I was working with would always say, well, if it's in Excel, we should be able to do something with it because it's already in a structured format.


Chuck Coxhead (02:30):



Kurt Quiggle (02:31):

They'd ask for a report at the end of the day, just get it into Excel. And that evolved into, okay, I've got this journal entry, or I've got this information from another system or et cetera. Why can't I just put that into gp? Why can't I put that into my ERP system? And I'd be able to say, well, we can, we'll integrate it in, or we'll write a macro to bring it in. And then as time went on, Microsoft, again, sister products, hey, we now have a PACE functionality within GL and AP and payroll within gp, and they're there, they're functional, and they definitely make life better for our users.


Chuck Coxhead (03:19):

And are there any particular limitations?


Kurt Quiggle (03:24):

Limitations being They are very template driven. I'm only able to put in certain amount of information in each of those areas, and there's not a lot of flexibility within how that information can look in my spreadsheet. So I end up manipulating the spreadsheet in order to put it into the ERP system.


Chuck Coxhead (03:46):

Okay. Okay. And is this typically, I mean, what areas do we typically see this in? So finance, obviously, have you seen anybody do it in any other areas of the ERP?


Kurt Quiggle (03:55):

Again, there's some AP transaction capabilities, payroll transactional capabilities. Again, enough to meet the window characteristics in those modules to put in a transaction. And there's some other areas as well, but those are the three that we see utilized the most within


Chuck Coxhead (04:14):

Gp. I can imagine bills of material maybe, or gosh knows some things in the supply chain and supplier management and all sorts of potential things, but the mind can wander across all the functional processes. So Bob, what are some of the folks looking for nowadays? I mean, you're taking a look at Business Central. What are some of the areas where people are wanting to use Excel from Business Central?

Bob Richardson (04:45):

Most of our clients have a strong background in Excel, and they want easy imports of what they're working on into BC with easy posting from that. And that's really something that's strong and Business central, it can export a template for you to work in. You cut paste, re-import, and your data is in, and it can be personalized for any data that you want. Essentially, you're not restricted to what the system forces you into.


Chuck Coxhead (05:22):

Well, that's an interesting set of flexibility. I can remember, and I haven't done it in a while, but doing some work in NetSuite where we were importing records for an implementation and the template was so lengthy and so exacting and so intolerant of small differences that it was just frankly a nightmare. Have they made improvements? I mean, as Kurt mentioned, it's Microsoft Native Products, business Central and Microsoft Office. I mean, have they made improvements to make it more intuitive and easier?


Bob Richardson (06:02):

They have edit an Excel feature from most tables inside Business Central just makes it a three or four click operation to export the template, put in your data, publish it back, and your data's in within a matter of minutes. I've had clients import whole spreadsheets worth of bank accounts into a new system in a matter of 15, 20 minutes.


Chuck Coxhead (06:37):

Well, now you've wet my whistle edit in Excel, edit in Excel. This isn't exactly an Avengers movie, but if you're doing this stuff every day, it is pretty exciting. It sounds to me like it's going to make things less cumbersome. And ultimately our goal is to save people time and money. I just got off a call this morning speaking with someone who is working in, believe it or not, Microsoft Solomon still, and they're looking at going to Business Central and the amount of time that this person spends daily pulling data and making reports in Excel and weekly doing billing manually. So tracking services and doing manual billing and all these different things and re-keying data because the system is incomplete and they have to bring things over to Sister systems that aren't integrated. It's just hours and hours and hours and it's very quick. You can do a very quick calculation just on time saved to see what return investment is to make the move from something where you have that much time you can save. So that edit Excel sounds to me like it's a winner, but the real value in what we're doing today is to show you the differences. So Kurt, why don't you show the folks what it looks like in gp?


Kurt Quiggle (08:01):

Sure, I'd love to. So Microsoft Dynamics, gp latest, greatest, we're focused on transaction entry for general ledger and pretty simplistic in the sense of what's required in order to enter a transaction. Along came the paste button up here at the top looks pretty fantastic. All we do is we hop into our transaction entry window, enter the information we need, and get our cursor to that first field within the transaction line items. And from there we can then copy the information we want to paste that has to be in this exact order, a description, an account number, our debits and our credits. We don't need to have zeros, PS, placeholders, et cetera, just as it looks is fine. And I just copy that with my magic keys, hop over to gb, hit paste, and away we go. We're all in. There's a small amount or there's some good error checking, I shouldn't say small amount of error checking in the sense that again, if we were to bring this in and there was a bad GL account number, oh look there, got to make a new entry. Let me do that real quick.


Kurt Quiggle (09:41):

We'll try this again just to show what that looks like. And we get a report this time. So the system went and pre-read everything and it's going to tell us that that new account number doesn't exist in the system. Nothing happens. If I had a thousand records and it parsed through and it found it on record, 9 99, I sat and waited to find out I had a bad account number. The last thing I'd like to point out, and this Bob and I chuckled about this when we talked about it earlier, if I happen to have an error in my Excel document where maybe I don't exactly balance, oh, let's fix that account number. I chuck you were supposed to remind me of that,


Bob Richardson (10:32):

You're welcome


Kurt Quiggle (10:34):

And we'll copy that again and let's slam that in. And lo and behold, we just put in a transaction that's out of balance. Of course we can't post it. The system's going to check it at the next level, so that's why the error checking doesn't happen. But this has been a lifesaver for customers. Just you've got that spreadsheet you're working on month end entries, a lot of accounting is done off system and then brought into GP for that final reportability. And kind of that last piece really is a lot of my customers I work with, they'll take this spreadsheet and then they'll embed it. They'll take the whole document and they'll attach it here on the transaction. So


Bob Richardson (11:19):



Kurt Quiggle (11:19):

That's an audit finding or something that they're making this entry for that work spreadsheet can be attached. So that's what it looks like with N G L.


Bob Richardson (11:30):

Very cool. Very cool. Well, Mr. Bob, let's take a look and see what it looks like in Business Central. All right. I brought you up to a test or demo environment that I use for working on projects and cases in Business Central. So personalized it with my name. We talked a little bit about personalizing, and one of the nice things about Business Central is that we can personalize the role or the screen that we're on by dragging in new fields if that applies to the work we're doing. And I'll show you a little bit more about that. But the other thing we can look at is my settings. And I am logged in right now as a business manager evaluation role, and that brings up a certain set of hyperlinks that take me throughout the system to accomplish my task. But it's a very simple matter of changing my role. If I want to be a manufacturing manager that'll give me access to the production module or projects for other screens that are available warehouse. There's just different ways to log in and make it easier to use.


Chuck Coxhead (13:05):

And I don't think we can underscore that point enough. If you're involved in financial transactions, one of the dangers of importing a large number of transactions is you can load an awful lot of garbage really, really fast.


Bob Richardson (13:20):



Chuck Coxhead (13:20):

So those roles and permissions, particularly when it comes to finances, are really crucial. So thanks for that little extra credit bit of information there.


Bob Richardson (13:30):

You're welcome. And again, there's a lot of quick links. There's tiles here that tell you what you need to do today. There's two sale quotes that need attention, seven sales invoices that need attention, et cetera. So getting to the job at hand is pretty quick and it's there in the system ready for you to interact with. You don't have to necessarily bring it in from a spreadsheet.


Chuck Coxhead (14:03):

And I see that Excel reports button right there, nice and prominent.


Bob Richardson (14:08):



Chuck Coxhead (14:08):

I don't need to go and hunt and pack, do I?

Bob Richardson (14:11):

No, you don't. I mean, these are the reports that come from Business Central based on your financial activity.


Chuck Coxhead (14:17):

So what does the import look like?


Bob Richardson (14:20):

Well, I kind of got that Kurt was making a payment or registering a customer payment with his import, but with this payments tab, you can access all the open records that are in the system and create those payments. And what it's going to do is show you the list of open entries and here they are and we can go create payments on these. So let's just keep this one highlighted and it'll take me to a payment journal. It might be a couple questions on the way, but that's okay. Here we go. So here it took me to the payment journal that I was talking about, and if I want to look for entries, I can suggest vendor payments just that quick. It goes through the system looking for any outstanding vendor payments. There's filters that you can use, filter it by vendor, how they want to get paid if you're just trying to make a check run, et cetera. And it's going to populate my spreadsheet. So these are payments ready to go, but if you don't want to necessarily go off the information that was in the system, here is where we can use Excel to make some quick edits or bring in some other information. So I just clicked on edit it in Excel and you see that it's downloading this file onto my computer for me, I'm going to open, it may have to drag it into this window depending on where it gets sent.


Kurt Quiggle (16:22):

That's an interesting point, downloading, right? So BC not on his machine, I was working on a local environment, local Excel local vp. Bob is in the cloud and he's able to interact with his office seamlessly.


Bob Richardson (16:46):

Alright, here in a second it's going to ask me to enable editing. It's going through the process of checking the data that's coming in, enable edit, and it's going to bring in all the entries that were in that journal for me.


Kurt Quiggle (17:10):

And Bob, is this actually creating sort of that template that we were talking about in order to interact with that data?


Bob Richardson (17:18):

It is. It is. This is the journal represented in a spreadsheet. So here are all the payments that were set up that got brought in and I can add lines, cut and paste from another spreadsheet, add lines, change the account numbers that I want to post with et cetera. All right here. And once I'm done I can just come down here and hit publish and it's going to bring those changes back in. So this is what was exported from the journal when I clicked edit in Excel


Chuck Coxhead (17:57):



Bob Richardson (17:58):

These were the entries that were on that line. It opens up the extension that allows me to publish this information back. So now I have the flexibility to copy and paste more lines, make column changes to account numbers, et cetera. And by making those changes, I just click publish. It's analyzing down here, and then it's going to go through the publishing steps.


Kurt Quiggle (18:38):

So you didn't have to go back to BC and click a paste button or find data button. It's all within it's captured within this template. That's very nice.


Chuck Coxhead (18:49):

Yeah, and what strikes me is really cool about this is that the fact that office is in the cloud and the fact that BC is in the cloud is what really enables this seamless native integration. Just as if when you're in an on-premise installation with gp, you're in the same environment.


Kurt Quiggle (19:09):



Chuck Coxhead (19:09):

Have a desktop version of Excel, you have a desktop version of gp, and then the cloud is able to replicate that through essentially your browser. And it is that native whole ecosphere that Microsoft has created, which makes all this possible and seamless and slick.


Bob Richardson (19:27):

Yes. Alright, here I am back in the journal that I edited in Excel and I noticed that Kurt might have to make the run before it detects errors. Well, there's a feature in Business Central that lets you preview post to where it's going to go through and analyze what you've entered and tell you if there's anything that needs addressed. And yes, check printed must be equal. So I haven't gone through and addressed the check issue, but yes, it won't let me post until I've actually printed these checks and that's what that dialogue was about. So if I go in here and print the checks,


Kurt Quiggle (20:32):

Bob, were you able to manipulate every field on that window within that spreadsheet?

Bob Richardson (20:38):

Yes. I can go in and out of anything and change and not only cut and paste, but most of them are drop menus. You're just picking something that's already set up in the system.


Kurt Quiggle (20:52):

So that would've been in the spreadsheet as well. That would nice. That's awesome. So I had to memorize the account numbers so I could key 'em in my blank spreadsheet in the correct format so I could paste them into gp. That's amazing.


Chuck Coxhead (21:10):

Well, that's why we have you that you can just memorize all the account numbers that might be


Kurt Quiggle (21:14):



Chuck Coxhead (21:14):

A client's database.


Kurt Quiggle (21:17):

Awesome, awesome.


Bob Richardson (21:19):

And I've met those accountants that do have them all memorized. We all have something I want to do. It is, well, they're in and out of 'em so much. They know exactly why they're there. But anyway, it's bringing up a standard check form that goes right to your desktop printer if you'd like. You've got pre-printed checks, it'll do that


Kurt Quiggle (21:43):



Bob Richardson (21:44):

Pretty standard check run. So all I did was print the first one so it's clicked off and I could void it if I didn't like the way it printed, et cetera. And it probably still wouldn't let me.


Kurt Quiggle (22:02):

That looks easy enough for you to teach me how to use.


Chuck Coxhead (22:07):

Well stay out of the line after we're done.


Kurt Quiggle (22:09):

Yes sir.


Bob Richardson (22:10):

I've been working with it for a little while and I've learned a lot of it. And just in the short time that I've been working with Business Central, the updates that have come through have made it even easier. And there's more on the way they understand the tedious of working with business software and they're making it intuitive and easy to use.


Chuck Coxhead (22:38):

Well that's amazing. And we are going, believe me, we are going to cover all those new features and we're going to cover all the different things that are coming down the pike as they come in future installments of this ERP Academy. Is there anything else, Kurt, you want to leave with the folks before we sign off?


Kurt Quiggle (22:55):

Oh, I'm just impressed. Everything I know I'm going to have to learn over again, but it looks like it's going to be easy enough to do.


Chuck Coxhead (23:03):

Very cool. How about you, Bob? Any closing thoughts?


Bob Richardson (23:06):

I appreciate you bringing me into this and letting me give you a little demonstration of Business Central. I know there's a lot of modules to cover, but I look forward to showing them


Chuck Coxhead (23:16):

To, you can't wait to do it. And honestly, it looked easy enough that you could even teach a salesman like me how to do it. And I think that's really saying something. So thank you all for joining me today and look forward to the next time.

bottom of page