In such a case, you may have no choice but to convert the data into a Pivot friendly data format. Sometimes, you may get a dataset that is unsuitable to be used as the source data for Pivot Table. Converting a Badly Formatted Data into Pivot Table Ready Source Data There are blank cells in the data set and the quarters are spread as column headers.Īlso, the region is specified at the top, while it should be a part of every record. But it can’t be used to create a Pivot Table. This again is an output that can easily be obtained using a Pivot Table. So even if you eventually want such a look for your data, maintain the source data in a Pivot ready format and create this view using the Pivot Table. This data representation may be received well by management and the audience of PowerPoint presentations, but it’s not suitable for creating a Pivot Table.Īgain, this is the kind of summary that you can easily create using a Pivot Table. If you go ahead and create a Pivot Table using this (which you can), you will get different fields for different quarters.If you have each record available in a separate row, you can do a better analysis. But is it a single sale, or a number of sales. For example, you can see the sales for Mid West in Quarter 1 is 2924300. This is a common way to maintain data as it easy to follow and comprehend. There are two problems with this data arrangement: Let’s have a look at some bad examples of source data designs. You can simply refresh the Pivot Table and it would automatically account for the new rows added to the source data. If you add more rows to the data set, you don’t need to adjust the source data again and again. The benefit with Excel Table is that it can adjust the expanding data. Your Pivot Table would work just fine with a source data that isn’t an Excel Table as well. This is more of a good practice and not a pitfall. Always create an Excel Table and then use it as the source for a Pivot Table.Once you have the Pivot Table, you can easily get these later. Don’t include any Column Totals, Rows Totals, Averages, etc., as a part of the source data.That’s because your Pivot Table doesn’t know these are dates. It will automatically put it in the values area. Now in the Pivot Table, select the date field and see what happens. Format the dates in your Pivot Table as numbers, and then create a Pivot table using this data. If you have a couple of seconds, try this.This would help you create the Pivot Table and use Date as one of the criteria to summarize, group, and sort the data. 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.What is a good design for the Source data for Pivot Table?.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |