0

I have a headcount dashboard which looks at data for the last 13 months, which is about 26000 rows by 55 columns for each month.

Currently I have a table in its own tab for each month and use formula to analyse this data.

In terms of times to calculate and flexibility in what the spreadsheet can achieve, is it better to combine these tables into a single large table and add a column for month/year? And why?

I currently use a pivot table and slicers to filter the data, and use a formula to get the filter and apply it across all tabs in my calculations

Nathan Burt
  • 3
  • 3
  • Not an answer to your question, but I'm curious as to why this data lives in Excel and not a database, such as Access? – TotsieMae Oct 11 '17 at 08:54
  • Smaller number of objects less memory usage i would have thought as a rule of thumb. Same goes for keeping formulas on one sheet etc. On the balance side, what are the items the end user will see? They will need a comprehensible view of the data. – QHarr Oct 11 '17 at 08:55
  • TotsieMae - It started off as a simple dashboard with 1 dataset. Apparently it was really good and has expanded since. I also don't have any experience with Access at this level nor how to integrate a dashboard. I stuck with what I know. – Nathan Burt Oct 12 '17 at 10:28
  • QHarr - There is one sheet for vlookup tables, 1 sheet for formula and a final dashboard sheet to show tables/charts/slicer filters. – Nathan Burt Oct 12 '17 at 10:29

1 Answers1

0

I admit I dislike structured referencing but am frequently dealing with higher volumes than you have and will always opt for the single sheet (for data) solution. If nothing else it makes month on month comparisons easier. Unless I do something stupid (which could mean having to revert to a saved version and redoing some work as that can be quicker than waiting for Excel) it is fast. Partly because I barely touch the source data (other than to populate a few extra columns) but work mostly from PivotTables and with filters.

I do though ensure that I convert most formulae results (achieved much more quickly in a single sheet than in 13, or sometimes 24 for me) to values only, once computed. For example I add an index and extract month and year flags from a date in the source.

The choice is not "60/40" or even "90/10", single sheet wins hands down for data that combined fits into 'portrait' orientation.

pnuts
  • 58,317
  • 11
  • 87
  • 139