![]() Since most data has more than one or two label fields, a macro will make the job much quicker. There are instructions on my Contextures blog. In newer versions of Excel, you can use the Get & Transform tools to unpivot Excel data. That technique creates a Multiple Consolidation Range pivot table, then uses its Show Details feature to “unpivot” the data. If you have a simple data set, you can manually upivot the data, and there are instructions here. So, in this example, all the months should be in one column, and all the sales amounts in another column. Ideally, you should set up the data vertically, with data going down the worksheet. We don’t want the data set up like a pivot table – we need to “unpivot” it. The pivot table won’t be able to automatically calculate an annual total – you would need a calculated field to add all the month values together. If you built a pivot table from that data, there would be a separate field for each month. The data is set up horizontally, with data going across the worksheet. It has a separate column for each month’s sales, and a year’s worth of data in each row. In the screen shot below, the data almost looks like a pivot table already. You need to “unpivot” your data first, and you can unpivot Excel data with a macro. If there is a heading for each month’s sales, instead of just one column where all the amounts are stored, that won’t work well in a pivot table. ![]() If you plan to build a pivot table, check the source data first, to make sure you have it set up correctly. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |