•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Is it possible to limit the date range on a pivot graph?
Our take
Are you facing challenges with your pivot graph displaying unnecessary months? If you're filtering by customer sales per month and need to visualize past data accurately, limiting the date range can enhance clarity. While 'show items with no data' is useful for displaying all months, it may lead to misleading interpretations of stagnant sales.
I have a pivot graph that I'm filtering by customer to show sales per month. I have 'show items with no data' selected in field settings for date, because I need to see all the previous months in the graph, whether or not sales were made in that month.
My only issue now is, is that it shows all the months of the year, so it looks like sales have flatlined at the end of the graph, when really I just don't have that data included yet.
Is there any way to force the pivot graph to end at February 2026 rather than December 2026? Or is it a compromise I have to make to have 'show items with no data' selected?
Maybe I could make a new column for mmm yyyy, but then how can I ensure it's in order? (And if possible, I'd also like it to look more "chunked" into years and months, rather than just Jan 26, Feb 26, etc.)
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Pivot Table top bar date filter doesn't update in chronological orderI have a an excel file which I had been building from scratch from a downloaded report every month and am trying to better automate since I do the same type of data filtering each month. I decided to create a table with the data that is linked to a pivot table which sorts and filters it into the format that I need as long as I manually adjust a couple things. However, whenever I add new data to the table, the date filter at the top of the pivot table does not show in chronological order. (Accountant working on April data and need to remove anything that was posted in May.) Theoretically I could use the timeline feature, but I'd rather not. I have resorted the source table in chronological order; I have removed the filter and re-added it in various formats. I've changed the data source, played with various settings. Is there a quick "fix" for this without manually creating the pivot table each time? I have several of these pivot tables on a sheet extracting different data sets sourced from the same table. In one of them, I need to see ONLY the items with a May date, so having to scroll through to check or uncheck the correct dates if they are out of order could lead to errors. Additionally, assuming that this can be corrected. Is there a way for an excel sheet to automatically add or remove row (by hiding or deleting, don't care which) as a pivot table gets longer or shorter so I don't have to guess how many rows I need to add before it cuts into other data below it? Sometimes I need 70+ rows and others only 30. I'm sure there's an overall better way to do what I'm doing. Thanks in advance. https://preview.redd.it/56optogx0qzg1.png?width=406&format=png&auto=webp&s=551180d29effbcfedb7038f60612e33f4e897d3d submitted by /u/inez6453 [link] [comments]
- Unable to Group by Month on Pivot Tables.I have a weird situation that I can't find a solution to online. If I export my Power Query* query into a table then make a pivot table out of that, the option to group by months automatically comes out. But if I export my query directly into pivot table, Excel won't let me group my dates into months - group field, group selection, all those don't work. Any idea why this is happening? It can't be the format cause query>table>pivot table works. The second pivot table is loaded into the data model.* Could this be the reason why? *edited Edit2: using DATEVALUE() on a Pivot Table date gives a #VALUE error. Is there any info how Pivot Table formats dates? Wrapping the date in a TEXT(,"m-dd-yyyy") fixes it. The left indent on the right PT could be a clue but I don't know how to interpret that. submitted by /u/monxstar [link] [comments]
- How to apply mmm date format to datamodel pivot chart axis?I'm using pivot charts to display data by month. All of my data is loaded into the data model with powerquery. I need to show the date as mmm, but also preserving the chronological sort order. So I have a table with a record for each month with the first day of the month as a date value. I don't have any grouping on this field or in any other tables. The data model doesn't have an option for the mmm format, and doesn't seem to do anything to the pivotables anyway. I can't find an option to set a number format for the field in the field settings. Defining the number format for the column of cells on the pivot table doesn't change the pivot chart. I tried to change the number format of the chart axis, but cant get it to apply. Somehow I was able to do this with one pivot table and chart, but I couldn't replicate it. Removing the date from this pivot table and re-adding it also removes the mmm format. Is there a known proper way to do this? I was eventually able to set the number format in a table and chart with vba. ActiveSheet.PivotTables("PivotTable1").CubeFields("[fPeriodRef].[Day1]").PivotFields(1).NumberFormat="mmm" submitted by /u/MJ0865 [link] [comments]
- How organize data to January - December instead of A to Z?How to organize data from January to December instead of A to Z? Right now, I am trying to create a pivot table, and it is showing April as the first month. When I want January instead. https://preview.redd.it/be7oi5uldeyg1.png?width=576&format=png&auto=webp&s=a0dbc6ae30bd324512bfe94e764fb3545c669881 submitted by /u/heyitsmereddit [link] [comments]