I have data with months, years and profits that need to be summed per month and year and sorted from January to December per year. I have year and month in the rows and profits in the value field. I am experiencing 2 problems.
1. Excel is not calculating the negative numbers as negative and instead is turning them into positive numbers. In the data sheet there are positive and negative numbers. If I sum these numbers manually using the autosum function, excel calculates everything correctly. But once I use the data for the pivot table Excel changes the negative numbers to positive, which causes my total profits per month to be inaccurate. I have the months and years in the rows and profit in the value field. In the settings I formatted the field to number in settings, hoping this would solve my problem, but it didn't. Formatting it to accounting didn't help either. All profits are still positive, even though the data sheet shows negative profits. So for example for January I have 1, -2, 6 dollar profit which would be 5 dollar in total for January, but Excel gives me a result of 9 dollar. Why is excel doing this and how can I fix it?
2. Excel is not sorting the months correctly. I am not sure based on what it's sorting because per year it has April at the top and October at the bottom. It's not alphabetically sorted either, because after April comes September, very odd. I looked through the sort options to see if I could get it to sort from Jan-Dec, but I could only find an alphabetical sort option. How can I get excel to sort the data from Jan-Dec per year?