![excel pivot chart how sort axis values excel pivot chart how sort axis values](https://miro.medium.com/max/1400/1*6L58rrqgopu_0iHUDo4P6A.png)
The Pivot Table will show the number of visitors by months. Select any cell within a table and go to Insert/Pivot Table Now we would like to create a Pivot Table report to see how the number of visitors is spread through the months.įirst let’s do it in Excel Case 1: Pivot Table report based on an Excel Table Date values are in one, and the other has a number of visitors on that date.
Excel pivot chart how sort axis values how to#
This article will tell you how to get the chronological sorting in Excel (Case 1) and how to do it in Power Pivot (Case 2).įor both cases, we will use a simple Excel Table. But this is not what we are used to when it comes to Pivot Tables. This is logical since the values are text and have nothing to do with dates as far as that Pivot Table is concerned. To get the month names from Dates we use the Format function in Power Pivot (it’s the Power Pivot equivalent of the Text function in Excel, both are explained further down this post), but the problem is that when you put this field in a Pivot Table, it gets sorted alphabetically. Finally you can then use Sort By Column in Power Pivot. Next you should get Month names and Month numbers by using Formulas. To Sort Months Chronologically, you need a Power Pivot Data Model and a Date field. Also, this feature does enable you to do much more than you’re used to! Sorting Months Chronologically (the short version) It’s one of those – “ it’s not a bug it’s a feature” things. The reason being that it’s really not a problem. Notice that I put the word problem in the first sentence in double quotes. In that case, you can find the video tutorial for sorting months chronologically and not alphabetically on my YouTube channel: Click Here to View Video on YouTube or watch the video embedded at the end of this post. Suppose you find it easier to learn through video. Sorting Months Chronologically will require you to do some extra work, but you also gain much more flexibility!
![excel pivot chart how sort axis values excel pivot chart how sort axis values](http://i.stack.imgur.com/DYMPY.gif)
![excel pivot chart how sort axis values excel pivot chart how sort axis values](https://blazor.syncfusion.com/documentation/pivot-table/images/sorting_grid.png)
Within a Power Pivot based Pivot Table, there is no Pivot Table setting that will sort months chronologically. But when you create a Pivot Table based on Power Pivot, grouping does not work! (This can also happen if no Power Pivot is used). When you create a Pivot Table in Excel that has a date field and is based on data in Excel, you can Group that field by month and the sort will be chronological (January, February, …).