Excel Magic Trick 1172: Multiple Dot Plots With Single Formula, INDEX, COUNTIFS & REPT

preview_player
Показать описание

See how to:
1. (00:38) INDEX and MATCH functions to lookup a column
2. (00:50) Highlight non-contiguous ranges using Ctrl
3. (03:13)Enter Formula into non-contiguous ranges using Ctrl + Enter
4. (04:04) COUNTIFS to count with criteria
5. (05:00) REPT function to repeat dots for Dot Plot
6. (05:23) Cell Formatting For Cell Chart
Рекомендации по теме
Комментарии
Автор

Excel Magic Trick 1172: Multiple Dot Plots With Single Formula, INDEX, COUNTIFS & REPT
See how to:
1. (00:38) INDEX and MATCH functions to lookup a column
2. (00:50) Highlight non-contiguous ranges using Ctrl
3. (03:13)Enter Formula into non-contiguous ranges using Ctrl + Enter
4. (04:04) COUNTIFS to count with criteria
5. (05:00) REPT function to repeat dots for Dot Plot
6. (05:23) Cell Formatting For Cell Chart

excelisfun
Автор

INDEX to build a dynamic range - so COOL!

pmsocho
Автор

Hello Mike, What a creative application of the index/match function! Thank you

ExcelxlNl
Автор

Very cool trick. Creates a great visual.

krn
Автор

Thank You
I have a question
This formula is enough or not.
REPT("*", COUNTIF($C$6:$C$13, C18))
Then repeated

Reduce_Scan
Автор

 Thank You
I have a question
Can I remove all hyperlinks once in 2007

Reduce_Scan
Автор

Hi, I would like to learn excel properly, but when I look at your videos I'm a bit overwhelmed. Could you recommend me where to start? I mean, should I start from the oldest video and go one-by-one or should I take it one playlist at a time? what is the best way?  Thank you :) 

lukasfuniok
Автор

Good trick - but it is not really a dot plot, since the formula just repeats the dot a number of times, thus making it a bar/column. The bar chart is very clear if you substitute the "*" with a "|" and use font Playbill.
On the other hand, if you have your integer plot values in column A this formula would make you an incell dot plot:
=IF(A1=0;"";REPT(" ";A1-1)&"*")

UWillemoes
Автор

Hi,
Thanks for your videos. I have a question as below.

I have a table containing some products.
RCCO NK-3325 TRD 633 PL 1 KG NK IN
RCCO NK-3325 TRD 633 PL 2 KG NK IN
RCCO NK-5251 TRD 633 PL 1 KG NK IN
RCCO NK-5251 TRD 633 PL 2 KG NK IN
RCCO NK-6302 TRD 633 PL 1 KG NK IN

Brand is
NK-3325
NK-5251
NK-6302

NK is a brand and after NK digits are verity. Almost 20000 rows are filled with the same as above. Now i want to put a formula for getting Brand and verity only. Some times Brand and verity somewhere in middle. 

Please help me.

ankurshukla