Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.

Get a Professional Answer

Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.

100% Satisfaction Guarantee

Rate the answer you receive.

Ask Jess M. Your Own Question

Jess M., Computer Support Specialist

Category: Microsoft Office

Satisfied Customers: 5963

Experience: Computer Software Specialist for more than 10 years

49766785

Type Your Microsoft Office Question Here...

Jess M. is online now

I am using Excel within Office 365 and using a macro to give

Customer Question

I am using Excel within Office 365 and using a macro to give me the results i need, Within one column of numbers how can I see the 6 highest or 6 lowest numbers in that column. What would the formula be??

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.

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.

Thanks, ***** ***** AE -- I need to see the 6 highest figures, can I do this in the same column? and in other circumstances the same will apply to column BC.

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.

Thanks Jess, Using another is ok, what would the formula be if I wanted that info in say column BR? How can i colour code the numbers in the AE column? Thanks

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.

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:

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.