Microsoft Dynamics GP User Group (GPUG) Tech Tips Exert

Below is an exert from a future “Tech Tips” that I wrote for the Microsoft Dynamics GP User Group (GPUG) to send to it’s members.  If you are not a member, you should review membership. 

Originally Posed 1/23/13

Below is an exert from a future “Tech Tips” that I wrote for the Microsoft Dynamics GP User Group (GPUG) to send to it’s members.  If you are not a member, you should review membership.

 

What are Excel Data Limitations?

Which version of Excel should you use?

You’ll want to be on Excel Version 2007 or 2010. Why? Column and row capacities in older versions cannot accommodate some of the files. Below is a table that shows size limitations of various versions of Excel. You’ll see that Excel 2007 and Excel 2010 have much larger row and column maximums than the older versions.

You should always work with supported versions of software; this includes bothGP AND Excel.

 Version  Max. Rows Max. Columns Max Columns by letter
Excel 2010 1,048,576 16,384 XFD
Excel 2007 1,048,576 16,384  XFD
Excel 2003 65,536 256 IV
Excel 2002 (XP) 65,536 256 IV
Excel 2000 65,536 256 IV
Excel 97 65,536 256 IV
Excel 95 16,384 256 IV
Excel 5 16,384 256 IV

 

Over a million records sounds like a lot, but it still might not be able to handle your needs, so you’ll need to address your Excel needs differently. Let’s review some options:

What is the difference between an Excel Table and an Excel Worksheet

Think of a table as being an “Excel Database.” In an Excel worksheet, you can put a single record on many lines if you like (like the following).

Example of Microsoft Excel Worksheet.  See how you can use Excel as your Microsoft Dynamics GP report writer.

A table will be setup so each row is one complete record and each record is only on 1 row (like the following.)

Microsoft Excel Table, see how to use Excel as your Microsoft Dynamics GP report writer.

Both of the above examples provide the same information, but by placing the data in a table rather than a worksheet:

  • Excel will apply default formatting to any new data added.
  • Excel will automatically apply the Filter setting. The headings for filters will remain when you scroll down.
  • You can easily add totals to the bottom with the check box in the Table Tools Design tab.  Using the Microsoft Excel Table Tools tab on the ribbon for writing reports for Microsoft Dynamics GP.
  • If you create a formula in a column, new data rows will have the formulas automatically copied to the new record. (This is a powerful feature if you use a calculated column in a Pivot Table.) This also means Pivot Tables will automatically capture the new data as well.

Understanding and using Tables is an important and necessary element to Excel Reporting.

Author: Belinda Allen

Belinda Allen (Microsoft MVP) is both a Microsoft Certified Professional for Dynamics GP (MCP) and a Microsoft Certified Trainer (MCT). She is also the co-founder of Smith & Allen Consulting, Inc. (www.saci.com), a business management software consulting firm for over 24 years. Belinda began implementing ERP systems so long ago that Windows was not an operating system, but an application. Larger businesses used old Main Frames with monitors that were Green and Black and smaller business did accounting by hand. Having seen the evolution that has taken place over the years from pieces of paper to analytics, Belinda still gets excited every time she helps a business improve. Belinda is also well known for her Blog www.BelindaTheGPcsi.com. Her blog is about sharing really useful information about the product quickly and succinctly. She has earned the nickname GP CSI because she excels at reviewing GP problems and figuring what went wrong and why. With followers all over the Globe she is able to share knowledge and achieve her mission: “To Improve the Lives and Business Success of my Followers.” In her time off, Belinda enjoys sailing, woodturning, crochet/knitting, sewing/quilting, reading and playing the ukulele (Belinda often participates in Ukulele Jams in NYC.)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s