List management in excel

Resolved · Low Priority · Version 2010

Thu 8 Feb 2018, 09:25 Delegate Samantha said...

Samantha has attended:
Excel Introduction course

List management in excel

If i have a report that u want to organise into number of time a company appears, is that possible?

Fri 9 Feb 2018, 16:58 Trainer Sarah said...

RE: List management in excel

Hi Samantha

In Excel, there is no direct function for you to sort the data by frequency of occurrence at once, you need to count the occurrence of the values first, and then apply sort function in Excel.

1. Select a blank cell beside original column, and enter this formula =COUNTIF($A$2:$A$16,A2) into it, and then drag this cell's AutoFill Handle down to the range as you need.

2. Keep selecting these formula cells, and click Data > Sort A to Z or Sort Z to A as you need

3. In the opening Sort Warning dialog box, please check the Expand the selection option, and click the Sort button.

4. And now the original column has been sorted by the frequency already. If necessary, please delete the added helper column

Hopefully this answers your question.

Regards,

Sarah
Excel Trainer

Fri 16 Feb 2018: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Excel tip:

Hiding Formulae in the Formula Bar

It is possible to protect the contents of a cell reference(s) from amendment by applying cell protection. The contents of the cell reference can also be stopped from displaying in the formula bar.

Step 1: Select Format > Cells > Protection.

Step 2: Tick Hidden option. Ensure Locked is ticked

Step 3: Select Tools > Protection > Protect Sheet

(Ensure "Protect worksheet and contents of locked cells" is ticked)

View all Excel hints and tips