PivotTable-able crosstab data entry
PivotTables and data entry butt heads with each other.
Data is easiest to enter in a crosstab format (headings on rows and columns). The row and column headings eliminate having to specify them for each value.
For example, a fuel price table:
If I want to specify $50 as the Canadian diesel price for 2013, I enter "$50" at the intersection of the correct headings.
However, to be able to use PivotTables, the data must be moved to "record" format (no row headings, only column (field) headings, and a lot of repeated information). This is usually stored in an Excel table.
and so on.
Now if I want to enter the same information as the crosstab example, I have to enter "2013", "Canada", "Diesel", "$50". The other problem is that there is no way to enforce uniqueness among the year-fuel-country combinations (compound primary key). For example, for 2013-Canada-Diesel, I could have 2 records, one for $50 and another for $40. I cannot prevent duplicate entries; I can only point them out if they occur using COUNTIFS. In SQL, you have the choice to enforce uniqueness.
We need some sort of PivotTable-able data entry crosstabs for the best of both worlds.
I suggest the following. The user can link a data entry crosstab to a table or vice versa. When that happens, the table autopopulates with all combinations of the crosstab's row and column headings, and the value to be entered is blank by default. When the user updates the crosstab, the table updates and vice versa.
Note that the data entry crosstab is like a PivotTable, but with some small differences. PivotTables are meant for summarizing, so you use an aggregate function (e.g. sum, average, max). A data entry crosstab would be meant for data entry and so requires that all fields that uniquely identify the table values to be present (these identifying fields are called a compound primary key in database terms). Both can rearrange the positions of headings freely.
Also note that crosstab data entry would also be useful in Microsoft Access. The closest that I've seen anywhere is LibreOffice Base letting you edit data through query results if no aggregate function is used. In summary, if a crosstab has the primary key entirely present by combining row and column headings, then it can and should be used for data entry and can and should be PivotTable-able.
(I realize that a PivotTable can be used to show raw values in a crosstab format by ensuring that all identifying fields of the value are present in the table. That way, the aggregate function works on only 1 value, and so returns that value. However, you cannot enter data in PivotTables.)
Andrew Schroter commented
Before Office 365 Plus excel I was able to create a table of raw entries and by Clicking on the lower right Column and Moving the cursor to the upper left cell and clicking to select the area of interest. then I would click on the Pivot table, move the right vertical bar back to the top of the work sheet, select the "Existing Worksheet" button in the Create Pivot table menu and click on the CELL I would want to be the Upper left corner of the Pivot table. Worked like a charm until Office 365 Plus Excel. Now it summarixzes by "month" (I had entries by Week end Dt - With a second column of three columns the entries would be unique. But it doesn't work that way in the Office 365 Plus release,as it automatically aggregates by Month. Any one figured out how not to summarize in Office 365Plus (Excel 2016)?
CA Vikram Shankar Mathur commented
In Access, you can make a form for data entry. You can design the form to lay the record out in a way that is more natural than a single line record. This might be your solution for Access.
Now that I have read your post, I want Excel to have forms that can be used to enter data into a table. Then the table could be summarized with a pivot.
I was wrong in my previous comments. The Power Query unpivot solution produces a refreshable table, so any changes made to the crosstab will be reflected in the flattened table after a refresh.
I've posted a new suggestion that takes this into consideration:
Hi Andreas, your code only works for tables with 1 set of row headings and 1 set of column headings. Also, if you read my previous comment, I link to the Power Query unpivot solution. Its drawbacks: it is a multi-step process and you cannot change table data through the crosstab format.
For my suggestion above, another thing is that the user should be able to easily add not just primary key values, but primary key fields. This would be good for addressing the case where the user realizes that what they thought was their primary key is actually not unique, and so they can add another field that will solve this.
Andreas Thehos commented
in former times I used a VBA-Code to Change crosstabulated data to lists. You can see the code on this site http://thehosblog.com/2014/01/28/excel-kreuztabelle-per-vba-in-liste-umwandeln/ and there is also a download possibilty for the xlsm-file.
But if you can use PowerQuery or if you use Excel 2016, there is allready a functionality included to unpivot crosstabulated data. You have to unpivot your crosstable first an then you can use it for your PivotTable.
Best regards from Germany
Currently, the closest that we have to data entry crosstabs are these:
However, they only allow you to enter data in a crosstab format once and then the crosstab is useless.
I should be clearer on some things:
The user should be able to add more values to the primary key fields (unless the primary key field is protected against that). In the example, this could be another country, another year, or another fuel type.
The user should also be able to easily change the positions of the row and column headings.
This feature may seem kind of arbitrary at first but in thinking about it, I think it's the next logical step for data entry to tables/PowerPivot.
I had just encountered this problem and came here to post the idea, but Kenneth Barber beat me to it :)
For me, I want it through the PowerPivot Linked Table function ('Add to Data Model'). I think these tables are a great idea where the user inputting data is actually adding that into a data model behind the scenes. However the user (especially the less tech savvy) is really going to need the cues provided by a crosstab 'form' to do this right.
The functionality also allows the user to declare pseudo-classes or tuples and then populate a value for each property. E.g.
MaterialClass: ID, Name
Material: ID, Name, MaterialClassID
QuantityType: ID, Name, Units, MaterialClassID
Data Entry begins with user adding records to the MaterialClass and QuantityType tables. Then in the crosstab form they can add Material records where QuantityType is a column label and Material.ID is a row label.
Obviously, you cannot work directly with a 3D table, since we see in 2D. You would need repeated headings. For an arbitrary number of dimensions, the current approach is to have a 1D raw data table from which you create a separate PivotTable. Definitely not the most elegant approach...
Very often I have the need for more than just 2D tables, especially 3D or even 4D. Would like to see the possibility to enter data in 3D tables (data on 3 axis instead of just 2).