Once you have your data from Microsoft Dynamics GP into Microsoft Excel, there is so much more you can do with it. This short video will show you how to use formatting tools to pinpoint Item Quantities. Learn how to:
- Quickly format your database as a table
- Use Conditional Formatting to automatically draw attention to specific quantities
Transcription:
Here is an inventory listing showing Microsoft Dynamics GP items with quantities available for sale. This is a really nice report to have in Excel because you can give people access to see quantities on hand that maybe don’t have user access to Dynamics GP.
I’m going to show you how to quickly format your database. Make sure you clicked inside your database and then go to the home tab and select format this table. Choose one of the displayed formats and say okay. Not only does this instantly format your database for you but it also provides a lot of other shortcuts and instantly turns on your filter arrows as you can see at the top here.
Next, we’re going to use conditional formatting to pinpoint important pieces of information for us. For example, we have a quantity available and a quantity allocated column. I want conditional formatting to pinpoint any quantity allocated that is higher than our quantity available. To do this, we go to the home tab of our ribbon and choose conditional formatting but first, we need to select the column that we want to be formatted so I’m going to highlight the quantity allocated column and I’m going to go to conditional formatting mural. In this case, I’m going to use a formula to determine which cells to format. I’ll type my formula in the space here. You can use similar formulas for your spreadsheets as well. They always start with an equal sign, then we want our first cell reference, which is Cell E2, the quantity allocated column. You’ll notice it puts dollar signs in there. It’s by default making the cell absolute or in other words, it’s not allowing it to move around to other cells. However, we want it to move down that column so we’re going to take the dollar sign away from before the row reference. Then we’ll type a greater than symbol. In our next cell, the quantity available column, we’ll also remove the dollar sign from before the row reference.
So what we’re saying there is we want to format any cells in Column E that are greater than any cells in Column D, and the format that we want to set. We’ll click on the format button here. Within the fill tab, we’re going to choose our color, bright yellow, that’ll be easy to spot and then okay.
As you scroll down, you will notice any cells in the quantity allocated column that are higher than the cells in the quantity available column appear in yellow and the best thing about conditional formatting is that that will adapt depending on the cell content. So, for example, you see here there’s a quantity of one. If that is actually a quantity of seven and is, therefore, higher than the quantity available, it will appear in yellow.
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
