98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » Frequency function
Frequency function
Resolved · High Priority · Version 365
Tue 18 Apr 2023, 16:36Delegate Florentin said...
Florentin has attended:
Excel Intermediate course
Frequency function
How to find most frequent items/word/sku appearing in table (like top 10 items sold)?
Wed 19 Apr 2023, 10:05Trainer Jens said...
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.xlsxWed 19 Apr 2023, 10:14Trainer Jens said...
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
Fri 21 Apr 2023, 13:11Delegate Florentin said...
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.
Mon 24 Apr 2023, 11:51Trainer Jens said...
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...
Sat 6 May 2023: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Closing Multiple Open Worksheets At OnceWhen 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. |
