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.

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).

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

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.  
• 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.) View all posts by Belinda Allen