![]() For example, if you have dates (which are stored as serial numbers in the backend in Excel), apply one of the acceptable date formats. Apply relevant format to cells in the source data.That’s because Excel interprets the entire column as having text data (just because of a single blank cell). If you create a Pivot Table using this data and put the sales field in the columns area, it would show you the COUNT and not the SUM. For example, let’s say you have a blank cell in the sales column.There shouldn’t be blanks cells/rows in the source data. While you can successfully create a Pivot Table despite having blank cells or rows, there are many side-effects that can come bite you later in the day.If you have a blank column in the source data, you wouldn’t be able to create a Pivot Table. There shouldn’t be any blank columns in the source data.However, that would be an awful way to prepare and use a Pivot Table.Ĭommon Pitfalls to Avoid While Preparing the Source Data If you don’t have unique titles, you can still go ahead and create a Pivot Table and Excel would automatically make these unique by adding a suffix (such as Sales, Sales2, Sales3).Instead, give these column headers unique names such as Sales Q1, Sales Q2, and so on… For example, if you have Sales numbers for four quarters in a year, you should NOT name all of these as Sales. The Data headers are unique and are not repeated anywhere in the data set.Each row is a record that would represent one instance of the transaction or sale.For example, Column C has product data only and column D and month data only. Each column represents a unique data category.The first row contains headers that describe the data in the columns.Here’s what makes it a good source data design: Let’s have a look at an example of good source data for a Pivot Table. What is a good design for the Source data for Pivot Table? Converting a Badly Formatted Data into Pivot Table Ready Source Data. ![]() Common Pitfalls to Avoid While Preparing the Source Data. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |