This post is 3rd sequence of our Excel Dashboard. If you have missed the post, please click on below link to go through that first.
https://dailypro123.blogspot.com/2020/03/a-quick-vba-enabled-excel-data.html
We will look at Automatic Sorting Hack in Excel - without the usage of manual sorting command.
Have a look at below video on how it works
Countries are ranked from top to down order based on their numeric values in the related column. Once an option button is selected whole data will be sorted according to that column's values.
STEPS
Excel provided the manual option to sort the table/data. Just select the data which you want to sort and go to home > Sort & Filter > Custom Sort. This work well when we are in working mode while in the presentation mode we need something automatic.
Another option to sort is through array formulas. Array formulas work ok but sometimes these increase worksheet size so i prefer to stay with the normal formulas unless i don't find the solution with normal options.
My this hack shall let you get in hand this automatic sorting tool for your complex financial and other data presentations and dashboards.
STEP NO.1
Copy all the database in a separate working sheet. Just a full replica.
Now add the columns headings in parallel to your data replica. You have to add as many columns as you want to show in the presentation.
In the below picture, Data with the blue heading is the main database replica while data in the green is our working area.
You are looking some data under the green headings. This is actually the ranking of the main data.
Each original data column has a parallel column in the green section. Below is the formula of the cell U7.
=+RANK(H7,$H$7:$H$203,0)+COUNTIF($H$7:$H7,$H7)-1
Above yellow highlighted part in the formula is the critical part of it. I have not freeze it while other part is freeze. This is to count only the values from the top rows and to avoid any duplicated numeric number.
It is creating a numeric number based on the values in the column H.
Now do this for the other columns of the data to create unique ranking numbers as is indicated in the above picture.
STEPS NO 2
Go to presentation area where you want to show your data.
Go to Developer tab > Insert > Option button (form control) and copy past as many you need. Here we have 9 data columns so i have copied it 8 times.
STEP NO 3
Right click on the first option button, go to format control and then go to control tab and fill the properties as indicated in the below picture.
It will asj you a cell link. Give it a link in your workings sheet where you have generated the workings data. In return it will show the option button number i.e. this is the first option button so in return it will be adding 1 in the selected cell.
Do the same with the remaining 8 option buttons and give them the same cell reference number as indicated above.
STEP NO 4
You have now the required ranking in the working area along with option button number in the Cell R4 of the working sheet.
Click of the option button shall change the number in the Cell R4. This is telling us that how data should be presented according to which column.
In the column E, we will return the ranking which the option button is telling us so insert the below formula in all the cells of the column E.
=+HLOOKUP($R$4,$U$6:$AC$249,$F7+1,0)
Having done above steps, now you can see how changing the numbers in the Cell R7 shall change the ranking in the column E.
STEP NO 5
Come to presentation area of the dashboard where you want to present your data and add below formula to fetch the data from the working area. Adjust this formula for the next columns by changing the column number in the formula bar and your data presentation with the auto sorting is ready. Now you can add conditional formatting as per your requirement from the excel conditional formatting option.
Below is how it shall be working.
Hope you like the post.
Shall you need any assistance or you want to suggest any recommendation, please feel free to let us know by commenting in the comment section.
https://dailypro123.blogspot.com/2020/03/a-quick-vba-enabled-excel-data.html
We will look at Automatic Sorting Hack in Excel - without the usage of manual sorting command.
Have a look at below video on how it works
Countries are ranked from top to down order based on their numeric values in the related column. Once an option button is selected whole data will be sorted according to that column's values.
STEPS
Excel provided the manual option to sort the table/data. Just select the data which you want to sort and go to home > Sort & Filter > Custom Sort. This work well when we are in working mode while in the presentation mode we need something automatic.
Another option to sort is through array formulas. Array formulas work ok but sometimes these increase worksheet size so i prefer to stay with the normal formulas unless i don't find the solution with normal options.
My this hack shall let you get in hand this automatic sorting tool for your complex financial and other data presentations and dashboards.
STEP NO.1
Copy all the database in a separate working sheet. Just a full replica.
Now add the columns headings in parallel to your data replica. You have to add as many columns as you want to show in the presentation.
In the below picture, Data with the blue heading is the main database replica while data in the green is our working area.
You are looking some data under the green headings. This is actually the ranking of the main data.
Each original data column has a parallel column in the green section. Below is the formula of the cell U7.
=+RANK(H7,$H$7:$H$203,0)+COUNTIF($H$7:$H7,$H7)-1
Above yellow highlighted part in the formula is the critical part of it. I have not freeze it while other part is freeze. This is to count only the values from the top rows and to avoid any duplicated numeric number.
It is creating a numeric number based on the values in the column H.
Now do this for the other columns of the data to create unique ranking numbers as is indicated in the above picture.
STEPS NO 2
Go to presentation area where you want to show your data.
Go to Developer tab > Insert > Option button (form control) and copy past as many you need. Here we have 9 data columns so i have copied it 8 times.
STEP NO 3
Right click on the first option button, go to format control and then go to control tab and fill the properties as indicated in the below picture.
It will asj you a cell link. Give it a link in your workings sheet where you have generated the workings data. In return it will show the option button number i.e. this is the first option button so in return it will be adding 1 in the selected cell.
Do the same with the remaining 8 option buttons and give them the same cell reference number as indicated above.
STEP NO 4
You have now the required ranking in the working area along with option button number in the Cell R4 of the working sheet.
Click of the option button shall change the number in the Cell R4. This is telling us that how data should be presented according to which column.
In the column E, we will return the ranking which the option button is telling us so insert the below formula in all the cells of the column E.
=+HLOOKUP($R$4,$U$6:$AC$249,$F7+1,0)
Having done above steps, now you can see how changing the numbers in the Cell R7 shall change the ranking in the column E.
STEP NO 5
Come to presentation area of the dashboard where you want to present your data and add below formula to fetch the data from the working area. Adjust this formula for the next columns by changing the column number in the formula bar and your data presentation with the auto sorting is ready. Now you can add conditional formatting as per your requirement from the excel conditional formatting option.
Below is how it shall be working.
Hope you like the post.
Shall you need any assistance or you want to suggest any recommendation, please feel free to let us know by commenting in the comment section.
Learn Continually - There is always "One more thing" to learn. Steve Jobs
Hi please share on below email id
ReplyDeletepaulo.henrique56@aol.com
Hi! Please share on below email id
ReplyDeletepaulo.henrique56@aol.com