Sort Pivot Table Values largest to Smallest, by Names, Dates and More!

Download Link

Download the practice file by clicking on the link below if you would like to practice along with me.

https://learnaccountingfinance.files.wordpress.com/2021/03/pivot-table-sorting-tutorial-data-shared.xlsx

Learn how to sort pivot table data from largest to smallest values and vice versa for multiple columns including Customer names, months and Values. In this video, I will explain the basics and advanced uses of Sorting Pivot Table Data.

Sort Pivot Table values from Largest to Smallest:

We start with very simple sorting of Customer Names based on the largest value of Sales amount. Then we start adding other fields in the pivot table and see how it impacts the Sorting.

Sort Pivot Table Manually

E.g. we add the countries in the Pivot Table report, and manually change the order resulting in Manual Sorting of the Data.

Sort Pivot Tables by Subtotals and Grand totals

We can also sort Pivot Table based on subtotal values or Grand Total values. Just click on the Cell for the field you are looking to sort and then click the sort ascending or Descending button for this to work.

Checking Pivot Table Sort Settings

We can also check current Pivot Table Sort settings by right clicking on any field and Value and then clicking on Sort à More Sort Options. This shows exactly how the current sorting is setup in the Pivot Table.

Sort Pivot Table by Months and Dates

We also take a look at how we can sort dates or months in Pivot Tables. By default, if the dates or months are entered in correct format, the Pivot Table will sort them based on Oldest to Newest. However, we can change that setting and Sort based on values for each date as well as sort from Newest to Oldest date or month.

Issues with Sorting, Errors in Sorting data

Sometimes, you may not be able to sort values when there are no subtotals or Grand Totals to click and Sort on. In this case, you can still sort Pivot tables data even though you do not add the subtotals. You can do this by right clicking on the Field you want to sort by values, and then click on Sort, and then More Options. Then Choose Ascending or Descending Sort option. Finally, select the value field you want to sort based on, from the Drop down selection available.

Connect:

https://www.instagram.com/learnaccountingfinance

https://www.learnaccountingfinance.com

Subscribe: http://www.youtube.com/c/LearnAccountingFinance?sub_confirmation=1

Course: If you would like to learn in detail, how to calculate sales variances and the impact they have on sales $, profit $ and profit margin %, and how to explain performance vs budget and prior periods, click on the link for a detailed video course (at a special price). You will also learn how to analyse and present the results of the variances to management and will be able to download solved variance calculation Excel templates. https://www.udemy.com/course/learn-financial-analysis-of-variances-in-profit-and-sales/?couponCode=YOUTUBE10

Hope you find the information in the video helpful. If you like to watch more videos in accounting, financial analysis and controller ship, videos that help you directly in doing your job, subscribe to my channel. If you liked the video, I would love if you could LIKE it and leave a comment. If you have any questions or feedback, again leave a comment. Lets stay connected at #learnaccountingfinance.

Pivot Table Calculated Field and Calculated Item (Automate Pivot Table Reporting Calculations)

Download the practice file by clicking on the link below if you would like to practice along with me.

Download Link:

https://learnaccountingfinance.files.wordpress.com/2021/02/pivot-tables-calculated-fields-and-calculated-items-practice-file.xlsx

Apply Calculated Field and Calculated Items to your Pivot Tables and automate calculations for reporting and analysis. This is such a time saver for Excel users and something every Excel user should learn. In this advanced tutorial of pivot tables, you will learn the use and examples of apply Pivot Table Calculated Field and Calculated Items options. Here is the link to the video tutorial:

What are Pivot Table Calculated Fields? Calculated Fields are calculations within the Excel Pivot Tables based on the fields or columns already available in the data. You can create many calculations inside the pivot table so that when you slice and dice or update the pivot table, the calculations also get updated with it.

What are Calculated Items? Calculated items represent Pivot table calculations of the data inside each field. So for example, if you have Calendar month, Sales and Standard margin as three fields available in the data set. Any calculation at the filed level such as std margin % calculated from Sales and Std margin field is done by clicking on “Calculated Fields” on the Fields, Items and Sets button of the Pivot Table Analyze menu. However, any calculations at the level of the data itself, for example calendar month Feb minus Jan is done by clicking on the Calculate Item selection of the same menu.

Why is Calculated Item grayed out or turned off sometimes? It matters what field or cell you have selected on the Pivot Table as it impacts turning On or Off of the Calculated Item feature. If you have selected a Values cell, then Calculated Field will be available but Calculated Item will be greyed out. In order to make the Calculate Item option available you will need to select one of the Cells from the Rows section of the Pivot Table.

Can we use Formulas when Calculating Fields and Items? Yes. this is exactly what the Calculated Fields and Calculated Items are for. However, the application of formulas is not entirely the same as the formulas in regular excel cells. Some formulas do not work at all and some formulas do not work the same way as they would work in a regular spreadsheet cell.

How to use IF statement in Calculated Fields and Items You can use the IF Statement with the syntax as normal in the Calculated Fields and Items calculation. Look at the video where I shared how I used the IF function to calculate sales commission %, and then nested AND function with the IF function to calculate a conditional bonus calculation.

What does List Formula do in a Pivot Table Analyze Menu? The list Formula option creates a separate spreadsheet and shows all the Calculated Fields and Calculated items used in a pivot table. This helps you to see any time the calculated fields which are not originating from the raw data set. This is also very helpful when you are reviewing a pivot table file that was prepared by someone else who included calculated items and calculated fields in the file.

I think using advanced pivot table functions such as Calculated Fields and Calculated Items help the Excel users significantly in being productive and creating and refreshing reports quickly with automated calculations. This saves time and avoids chances of mistakes when formulas are maintained outside of the pivot table calculations.

Connect:

https://www.instagram.com/learnaccountingfinance

https://www.learnaccountingfinance.com

Subscribe: http://www.youtube.com/c/LearnAccountingFinance?sub_confirmation=1

Course: If you would like to learn in detail, how to calculate sales variances and the impact they have on sales $, profit $ and profit margin %, and how to explain performance vs budget and prior periods, click on the link for a detailed video course (at a special price). You will also learn how to analyse and present the results of the variances to management and will be able to download solved variance calculation Excel templates. https://www.udemy.com/course/learn-financial-analysis-of-variances-in-profit-and-sales/?couponCode=YOUTUBE10

Hope you find the information in the video helpful. If you like to watch more videos in accounting, financial analysis and controller ship, videos that help you directly in doing your job, subscribe to my channel. If you liked the video, I would love if you could LIKE it and leave a comment. If you have any questions or feedback, again leave a comment. Lets stay connected at #learnaccountingfinance.