Pivot Table on data with empty rows

[G.Bregvadze]

Hi,

I am writing regarding the Pivot table creation with data which contains empty rows.

Recently, I work on a project with a data which contains empty rows. I will attach the file for sample to see what sort of data contains my sheet.

As you can see the data is divided according to some criteria, and it is constantly updated by users. sometimes a new criteria is added at the end of the sheet, that is why I need to create a pivot table, to be able to just changing Source, to update the table and manipulate table in order to get desired results, but I can’t remove these empty rows rom the data as far as some unexperienced users have to fill the information and it is more comfortable to have the data separated from each other for them.

However, when creating the Pivot table on this type of data, the pivot table will include the blanks Row Labels there.

It can be removed by excluding them using filtering, but can someone provide any better way? Can I build a pivot table on this type of data without spaces?

Any suggestions are welcomed.

Thank you in advance.

The sample file:

data_sample.xlsx

[Richard Buttrey]

You need to separate in your own mind the two tasks of ease of data capture and the data format that Pivot tables need in order to work. And also understand that data capture requires different treatment to final reports.

The data for a pivot table would need to be in a different layout. i.e. columns for:
Product, Part, Date, Type, Value

If your users can't be trusted to enter information into such a 5 column table, (and that doesn't seem to onerous a task) then you'll need to create a data entry form that they can work with. Every time they add a new record they would need to click a button. This would run a macro that would take the data in the form and add it to the 5 column table. The macro would finish by clearing the entry form ready for the next record. The form could also make use of data validation drop down lists so that Products, Parts and Types could be picked rather than needing to be typed.

[G.Bregvadze]

I forgot to mention that product, part and type are fixed, the only data entered is the numbers according to months and , sometimes the users add new product table if the new product is added.

[Richard Buttrey]

I forgot to mention that product, part and type are fixed, the only data entered is the numbers according to months and , sometimes the users add new product table if the new product is added.

Hi,

That may well be the case, but maybe you are missing my point. Your existing method of capturing data is a rigid table where I now understand all the Products and Parts are pre-listed. My point though is that a) there's an awful lot of wasted white space since only certain products/parts will be entered in a particular month, and b) as you've identified your current layout doesn't lend itself to a Pivot Table. In fact even if there were no blank rows and you eliminated the Totals rows, it still would not be in the right format for a PT. The months need to be in a single column. Which is why I mentioned the 5 column table.

This would be a table with no blank rows and each row would be a particular record. You will need to have this type of table layout if you want to be able to analyse your data easily.

You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it, Yours exhibits all those features.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.

[G.Bregvadze]

Thank you for your information, I agree with you regarding the format of the data and so on. But unfortunately I am too restricted in terms of modifying this forms. If I had the possibility I would definitely move periods to the one column and remove any spaces there...

It seems that I would have to create some tables for information using simple methods with functions Sumifs and so on..

Thanks a lot for information and support..