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 2002 (XP)||65,536||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).
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.