frequency function

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Frequency function

Frequency function

resolvedResolved · High Priority · Version 365

Florentin has attended:
Excel Intermediate course

Frequency function

How to find most frequent items/word/sku appearing in table (like top 10 items sold)?

RE: Frequency function

Hi Florentin,

Thank you for the forum question.

The best and easiest way of doing this is by using PivotTables. You can see an example attached. It took me 1 minute to create the example.


We didn't do PivotTables on the course. If want to learn more about PivotTables, please come back and have our Excel Advanced course.

I found two videos on YouTube, which will help you getting you top SKUs.




https://www.youtube.com/watch?v=UsdedFoTA68& ;t=142s

https://www.youtube.com/watch?v=_0-fXurdC1Y

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Attached files...

sku top 10.xlsx

RE: Frequency function

Hi Florentin,

This video is spot on for what you want.


https://www.youtube.com/watch?v=AM2cxn_372o


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Frequency function

Thank you Jens.

That is interesting.

Although our report is on a different spreadsheet and we use online Excel in our business, so I am unable to apply the pivot table on a different online spreadsheet.

Or perhaps there is a way?

Kindly,

Florentin.

RE: Frequency function

Hi Florentin,

You can do it with Excel functions, but it is more complicated.

You will need a list all you products (SKU). Then you will have to first count number of sales and sales totals for each product and the lookup top 10.

Please find attached Excel file. I have used Countifs, Sumifs, Large, Index, and Match functions. You can find the functions in the columns with red headers.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Attached files...

sku top 10.xlsx

Sat 6 May 2023: Automatically marked as resolved.


 

Excel tip:

Closing Multiple Open Worksheets At Once

When multiple Excel worksheets are opening, rather than performing a File > Close menu option multiple times, hold down the the SHIFT key and select the File > Close All menu option.

Close All menu option is only displayed when Shift key is down

View all Excel hints and tips


Server loaded in 0.09 secs.