เพื่อนๆเคยสงสัยมั๊ยครับว่า Excel แต่ละ Version มันมีข้อจำกัด หรือ Spec ต่างกันยังไง?? เพราะบางครั้งเราต้องเข้าใจด้วยว่าคนที่เราทำงานด้วย เค้ามี Excel Version ไหนอยู่ แล้วเค้าจะเปิดไฟล์เราได้หรือไม่ เช่น Excel 2003 มีได้แค่ 65,536 แถว แต่ 2007 ขึ้นไปมีได้เป็นล้านแถว!!

เวลาไปดูที่เว็บ Microsoft ก็ดันไม่มีเปรียบเทียบกันให้อีก ผมก็เลยอาสาเปรียบเทียบให้เห็นกันจะๆ ไปเลยครับ โดยผมทำตารางเปรียบเทียบสำหรับ Version Excel 2003, Excel 2007, 2010, และ 2013 เท่านั้นนะครับ

ตัวอย่างหน้าตาในไฟล์ Excel

excel-spec-limit-comparison

เทียบข้อจำกัด Excel แต่ละ Version : Specs Limits Comparison

Excel file Download =>excel-spec-limits

Excel Specification & Limits Comparison

Worksheet and workbook specifications and limits

FEATURE MAXIMUM LIMIT
www.inwexcel.com 2003 2007 2010 2013
Open workbooks Limited by available memory and system resources
Worksheet size 65,536 rows by 256 columns 1,048,576 rows by 16,384 columns
Column width 255 characters
Row height 409 points
Page breaks 1,000 horizontal and vertical 1,026 horizontal and vertical
Total number of characters that a cell can contain 32,767 characters. Only 1,024 display in a cell; all 32,767 display in the formula bar. 32,767 characters
Characters in a header or footer 255
Sheets in a workbook Limited by available memory (default is 3 sheets) Limited by available memory (default is 1 sheet)
Colors in a workbook 56 16 million colors (32 bit with full access to 24 bit color spectrum)
Named views in a workbook Limited by available memory
Unique cell formats/cell styles 4000 64000
Fill styles #N/A 256
Line weight and styles #N/A 256
Unique font types #N/A 1,024 global fonts available for use; 512 per workbook
Number formats in a workbook Between 200 and 250, depending on the language version of Excel that you have installed
Names in a workbook Limited by available memory
Windows in a workbook Limited by available memory
Hyperlinks in a worksheet 66,530 hyperlinks
Panes in a window 4
Linked sheets Limited by available memory
Scenarios Limited by available memory; a summary report shows only the first 251 scenarios
Changing cells in a scenario 32
Adjustable cells in Solver 200
Custom functions Limited by available memory
Zoom range 10 percent to 400 percent
Reports Limited by available memory
Sort references 3 in a single sort; unlimited when using sequential sorts 64 in a single sort; unlimited when using sequential sorts
Undo levels 16 100
Fields in a data form 32
Workbook parameters #N/A 255 parameters per workbook
Items displayed in filter drop-down lists 1000 10000
Noncontiguous cells that can be selected 8,192 cells 8,192 cells 2,147,483,648 cells 2,147,483,648 cells
Maximum limits of memory storage and file size for Data Model workbooks #N/A #N/A #N/A 32-bit environment is subject to 2 gigabytes (GB) of virtual address space, shared by Excel, the workbook, and add-ins that run in the same process. A data model’s share of the address space might run up to 500 – 700 megabytes (MB), but could be less if other data models and add-ins are loaded.

 

Calculation specifications and limits

FEATURE MAXIMUM LIMIT
www.inwexcel.com 2003 2007 2010 2013
Number precision 15 digits
Smallest allowed negative number -2.2251E-308
Smallest allowed positive number 2.229E-308 2.2251E-308
Largest allowed positive number 1.79769313486231E+308 1E+308
Largest allowed negative number -1E-307 -1E+308
Largest allowed positive number via formula #N/A 1.7976931348623158e+308
Largest allowed negative number via formula #N/A -1.7976931348623158e+308
Length of formula contents 1,024 characters 8,192 characters
Internal length of formula #N/A 16,384 bytes
Iterations 32767
Worksheet arrays Limited by available memory. Also, arrays cannot refer to entire columns. For example, an array cannot refer to the entire column C:C or to the range C1:C65536. However, an array can refer to the range C1:D65535 because the range is one row short of the maximum worksheet size and does not include the entire C or D column. Limited by available memory
Selected ranges 2048
Arguments in a function 30 255
Nested levels of functions 7 64
User defined function categories #N/A 255
Number of available worksheet functions 329 341
Size of the operand stack #N/A 1024
Cross-worksheet dependency #N/A 64,000 worksheets that can refer to other sheets
Cross-worksheet array formula dependency #N/A Limited by available memory
Area dependency #N/A Limited by available memory
Area dependency per worksheet #N/A Limited by available memory
Dependency on a single cell #N/A 4 billion formulas that can depend on a single cell
Linked cell content length from closed workbooks #N/A 32767
Earliest date allowed for calculation January 1, 1900 (January 1, 1904, if 1904 date system is used)
Latest date allowed for calculation December 31, 9999
Largest amount of time that can be entered 416.6666551

 

Charting specifications and limits

FEATURE MAXIMUM LIMIT
www.inwexcel.com 2003 2007 2010 2013
Charts linked to a worksheet Limited by available memory
Worksheets referred to by a chart 255
Data series in one chart 255
Data points in a data series for 2-D charts 32000 Limited by available memory
Data points in a data series for 3-D charts 4000 Limited by available memory
Data points for all data series in one chart 256000 Limited by available memory

 

PivotTable and PivotChart report specifications and limits

FEATURE MAXIMUM LIMIT
www.inwexcel.com 2003 2007 2010 2013
PivotTable reports on a sheet Limited by available memory
Unique items per field 32500 1048576
Row or column fields in a PivotTable report Limited by available memory
Report filters in a PivotTable report #N/A 256 (may be limited by available memory)
Value fields in a PivotTable report #N/A 256
Calculated item formulas in a PivotTable report Limited by available memory Limited by available memory
Report filters in a PivotChart report #N/A 256 (may be limited by available memory)
Value fields in a PivotChart report #N/A 256
Calculated item formulas in a PivotChart report Limited by available memory Limited by available memory
Length of the MDX name for a PivotTable item #N/A 32767
Length for a relational PivotTable string #N/A 32767
Items displayed in filter drop-down lists #N/A 10000

 

Shared workbook specifications and limits

FEATURE MAXIMUM LIMIT
www.inwexcel.com 2003 2007 2010 2013
Users who can open and share a shared workbook at the same time 256
Personal views in a shared workbook Limited by available memory
Days that change history is maintained 32,767 (default is 30 days)
Workbooks that can be merged at one time Limited by available memory
Cells that can be highlighted in a shared workbook 32767
Colors used to identify changes made by different users when change highlighting is turned on 32 (each user is identified by a separate color; changes made by the current user are highlighted with navy blue)
Excel tables in a shared workbook #N/A 0 (zero)

Excel file Download =>excel-spec-limits

พวก N/A คือ ผมไม่มีข้อมูลครับ ไว้หาได้ครบจะมาปะให้อีกที

Reference