Microsoft Office Questions? Ask a IT Expert for Support ASAP
Hi, welcome and thank you for your question. My name is ***** ***** I am glad to assist you today.
Can you send me a sample excel file with dummy data? You can use the Add Files button to attach it here.
Also, are you trying to show the 6 highest or lowest value in an adjacent column? Please let me know by replying to me here so that I can help you further.
Hi thank you for writing back. Are you saying that instead of shwlowing that entire column, you want to only show the 6 highest or lowest number?
Can you send me a copy of your file so that I can check it myself? You can "save as" the file to make a copy, remove sensitive info and attack h it when you reply.
Thank you for the sample file. Give me a moment to download the file and check your requirements. I will be back shortly.
I have checked your file. Column AE is a result of a formula therefore you cannot only show the 6 highest or lowest of the figures. However, you can use another column, enter a formula in that other column utilizing the values in AE, and will only display he 6 highest or lowest figures.
Another option if possible is to color code or highlight the 6 highest or lowest values in the column so that there will be no need to use an extra column.
Unfortunately, I see that the column is FILTERED. That is, several rows are hidden due to the filter. Formulas and color coding does not exempt those hidden values. That is, in the sample file you sent me, there are only 29 visible rows. When doing the formula and the color coding, the hidden rows are included. Thus, the color coded cells may be hidden cells and you might think that the formula or color coding is not working.
Is column AE really a filtered column? Or is the expected ranking formula allowed to use the entire column data for ranking and displaying the highest or lowest 6? Please let me know so that I can build the formula for you.
Ok. But my concern is, will the entire column be ranked for the 6 highest or lowest? If yes, then formulas and the color coding technique will surely work. But if the column is filtered, the data in the entire column will still be used and the formula method utilizing another column will work, since it will just pullout the highest or lowest 6 values. But in the the color coding approach, the color coded cells can be hidden when they are included in the filter.
Ok, and you want to work on column AE?
Ok, give me a moment. I will work on AE and on BR as you indicated showing both color coding and formula options.
By the way, using the color coding, we will utilize Conditional Formatting.
I will now work on the formula option and will send you the file later.
Thank you for patiently waiting. Due to duplicates, like the color coding results, the ranking is dominated by duplicates. Also, blank cells are treated as a 0 thus the lowest 6 are all zeros.
Here is the final file showing the formula and helper columns I used:
why? were you able to download the file I sent you? the formula is working there
Oh, a bit strange. My work was not saved. Give me a moment
By the way, I placed the info at the bottom of the column AE
Oh sorry, my work was saved and it is at the bottom of the row AE. Please look at cell AE3002 of the file I gave you
Unfortunately, that will not work. Column AE is a result of a formula and we cannot enclose these formula to make the ranking. Also, the ranking formula needs to test the entire column AE to check the ranking, thus, it should be stored in another cells or column.
The formula I created for you is a dynamic ranking, you can change it to 10, 15, or any number and the results will automatically change. But I only set it to 6 based on your request.
Here, I expanded the table to fit 15 rows. Unfortunately, they still show duplicate values since AE has a lot of duplicate data (See COUNT results in AC3005 and AC3021.