Data transformation for Excel users

Excel is commonly used for reporting, which frequently requires some kind of data tranformation. Trivial data manipulations (e.g. formula-based calculations, lookups, pivots) are simple in Excel. However, when calculation complexity and data volume grow spreadsheets increasingly become inappropriate for data transformation due to a number of iherent limitations (addressed by EasyMorph):

Excel is not suitable for large amounts of data

The hardcoded limit for a sheet is 1 million rows, although spreadsheets are extremely rarely used for such amounts of data because Excel becomes slow and even non-responsive. While not a Big Data tool, EasyMorph comfortably deals with millions of rows of data extracted from databases and files.

Spreadsheets are poorly suited for tabular data

It might sound like herecy — aren't most of spreadsheets some kind of table, after all? Well, Excel is convenient for drawing tables, but not for transforming tabular data. The main data unit in Excel is cell. A table in Excel is just cells that are visually grouped together. Technically, there is no difference between a cell in a table and a cell outside of it. Therefore tables are effectively emulated in spreadsheets, but they are not a native abstraction.

Why does it matter? Since tables are not native to Excel it leads to multiple deficiencies when dealing with tabular data. Just some of them:

  • "Understretched" columns is a frequent cause of wrong calculations (that sometimes lead to financial losses).
  • Datasets of variable length are very hard to deal with — it's necessary to redraw tables, stretch formulas, or use VBA (later on that).
  • Filtering a table using a calculated condition (e.g. Sales > 1000) requires "hacks" and workarounds, but it's a frequently needed operation.
  • (Relational) table joins are not possible.
  • Poor interoperation with databases (which are typically the main source of business data). Importing data from databases is inconvenient. Exporting to databases is not possible at all.
  • Loading and appending multuple files at once is a problem.

Repetitive transformations require programming

Writing a VBA macro is a typical way to arrange repetitive transformations used in daily routines. However, a macro doesn't have the convenience of a spreadsheet — it's not visual, and it doesn't recalculate automatically on change. EasyMorph combines the best of both worlds: visual, interactive authoring for designing powerful, complex data trasformations.


Try EasyMorph today. No registration required.

Download