How to Flatten a Dimensional Data Model Built in Excel with Pivot Tables

To flatten a dimensional model built in Excel using pivot tables, follow these steps:

  1. save the original worksheet with the pivot table as a CSV (tab delimited is even better)
  2. select the range or column that contains the blanks (if you have blanks under the header, don’t select the column header in the range)
  3. select Edit -> Go to… -> Special
  4. select blanks (at this point Excel will select all the blank cells
  5. press equal
  6. point to the cell above the first selected cell
  7. press <ctrl>+Enter and Excel will copy the formula to all the blank cells
  8. to replace the formula by the values, simply save the worksheet again as CVS, or copy and paste special the cells as values
  9. that’s it, you’re done. (Don’t forget to save.)
Interested? Click here to contact us for a free consultation →