Make an AWESOME Dynamic Chart with Checkboxes in Excel ✔ (No VBA)

Рет қаралды 74,070

Күн бұрын

This is going to surely impress everyone in office. A crazy simple and powerful dynamic chart with Excel check boxes.
We can use the "Checkbox" form control in Excel to create a stunning chart. The trick is to link your check box to a cell and then build "formulas" to on/off the data. At Awesome Chocolates, we are using it to analyze sales data interactively.
What are you going to use it for? Do tell me in the comments.
IN THIS VIDEO ⏱👇
================
0:00 - Quick demo of the dynamic chart
0:22 - Enabling the Developer Ribbon in Excel
1:00 - Working with Checkboxes in Excel
2:37 - Creating the dynamic checkbox chart
5:57 - But the chart "SUCKS" - Making it "AWESOME"
Sample file 📈👉
=============
Need a practice workbook? Use this link to get it - chandoo.org/wp/wp-content/upl...
Want more Charts 📈📊📉?
=======================
I recommend watching these videos to learn more Excel Charting & Dashboarding tricks.
Top 5 Chart (to highlight important data points) in Excel - • Create a Top 5 Dynamic...
Make a traffic light chart for your project reports 🚦 - • Traffic Light Chart in...
6 Must-have chart tricks for data analysts - • 6 Must have line chart...
A simple dashboard trick to wow your audience - • Link up two charts to ...
My Excel charting playlist -
• Make impressive infogr...
Learn Excel STEP BY STEP 👩‍💻👨‍💻:
============================
Do you want to learn Excel for doing advanced data analysis and reporting? If so, you will find my Excel School program perfect for you. It can change the way you use and work in Excel.
Please visit chandoo.org/wp/excel-school-p... to sign up for my online class.
~
Why are column charts lazy?
Because they just stand 🤣
#Excel #analytics

Пікірлер: 131

@excelworx8712 2 жыл бұрын

This is great Chandoo. I am on a project that charts several indices on one graph and plan to use checkboxes

@chandoo_ 2 жыл бұрын

Wow.. do tell us how it went...

@excelworx8712 2 жыл бұрын

@@chandoo_ I am enjoying making it. But more than me, it is my client who must say what they achieved from it

@robertclarke3047 2 жыл бұрын

Details like this are what set apart presentations from being boring

@chandoo_ 2 жыл бұрын

Plus so easy to do...

@wayneedmondson1065 2 жыл бұрын

Awesome tricks and techniques Chandoo! Thanks for demonstrating. Thumbs up!!

@chandoo_ 2 жыл бұрын

You are welcome Wayne :)

@s09313 2 жыл бұрын

So simple. Yet so stunning. Thanks, Chandoo. :)

@chandoo_ 2 жыл бұрын

My pleasure 😊

@aiasaiascon3894 Жыл бұрын

You really are Awesome Chandoo!!!!

@SashaHouseOfFashion Жыл бұрын

Too good to be simple... chandoo awesome 👍👍

@vnvipindasyt Жыл бұрын

Awesome ❤.. this video gives me lot of ideas to work on check box.

@rajashekarch8267 Жыл бұрын

Next level explanation Sir 🙏👏

@senpai.5517 2 жыл бұрын

Another fantastic knowledge share, Chandoo. Kudos and thanks!

@chandoo_ 2 жыл бұрын

My pleasure!

@nirmalabolar1382 2 жыл бұрын

Thank you so much for such detailed video, it's awesome.

@chandoo_ 2 жыл бұрын

You are welcome Nirmala ... 😀

@canirmalchoudhary8173 2 жыл бұрын

Using rectangle around checkbox is nice tip

@chandoo_ 2 жыл бұрын

Glad you think so!

@Azhar_Khan383 2 жыл бұрын

This is what I was looking for. Awesome

@chandoo_ 2 жыл бұрын

You are welcome Azhar...

@mickaell3451 2 жыл бұрын

Things like this can make charts awesome. Cool idea Chandoo.

@chandoo_ 2 жыл бұрын

You are right Mickael... simple things and take less time too but the impact is 💥

@alappatel2472 2 жыл бұрын

You are one of the best Expert and great excel, Power BI videos for Data Analysis

@chandoo_ 2 жыл бұрын

Thanks Alap.. 😀😀😀

@Ringsofsweden Жыл бұрын

Very informative

@wirtualny_swiat 2 жыл бұрын

Damn... So simple and so good!

@chandoo_ 2 жыл бұрын

💥💥💥

@divyakanchi5773 Жыл бұрын

Awesome Guruji..

@sivakumarnarayanan8240 2 жыл бұрын

Thanks. Its awesome

@hooman6959 Жыл бұрын

That's good 🔥🔥

@fbullain 2 жыл бұрын

Thanks for the very helpful videos.

@chandoo_ 2 жыл бұрын

You are welcome!

@kiranmedishettitalks594 Жыл бұрын

Awesome chandu Bro

@balajithandavan6842 2 жыл бұрын

Cracking Bro. No words..., Useful ❤️❤️🔥

@chandoo_ 2 жыл бұрын

Glad to hear that

@kalaimanirajamanickam5038 Жыл бұрын

Thank you so much

@foodcity9875 Жыл бұрын

Wowowowoow 👏👏👏

@monishnagarajan5917 Жыл бұрын

This trick is awesome

@mahadihasan3783 Жыл бұрын

Awesome love u ❤️❤️ brother

@ranaganguli2892 2 жыл бұрын

Awesome... tried immediately and implement..

@chandoo_ 2 жыл бұрын

That is great to hear... 😎

@grendbelema 2 жыл бұрын

awesome, many thanks

@chandoo_ 2 жыл бұрын

You are welcome!

@salehmuhammad6811 2 жыл бұрын

Great 👍 chandoo

@chandoo_ 2 жыл бұрын

Thanks Saleh 😀

@feeldennis 2 жыл бұрын

Thanks very much, how do i move the graph and link the check boxes on a new sheet?

@Seftehandle 2 жыл бұрын

I had to comment:this s so siple yet extremely usable, we need some quick fix for reporting for maangement, this vid just did the trick. Great job, please do more vids like this in th future. May I ask how did you come up with the idea?

@chandoo_ 2 жыл бұрын

Thanks Tina. This is originally published as an article on my website in 2011!!! chandoo.org/wp/dynamic-chart-with-check-boxes/

@user-qo5dw2jz4z 9 ай бұрын

This is great and useful, thank you so much for sharing. Can we copy and paste to link with ppt?

@asimsiddiq475 2 жыл бұрын

thanks a lot, Respected sir.

@chandoo_ 2 жыл бұрын

You are welcome Asim :)

@skybadou 2 жыл бұрын

Beautiful

@chandoo_ 2 жыл бұрын

😍

@MrGreyPearl Жыл бұрын

Thank you for this great video Chandoo. But I've question; when you uncheck the Units, "secondary vertical axis" is automatically removed from the graph. How did you do that ?

@davidferrick Жыл бұрын

I've always opted out of the conditional check and multiply the values by the checkbox state. True = 1 and False = 0 so if you multiply by False you wipes out your values and hides the cart elements.

@Randomvideos-tz9vh 2 жыл бұрын

Simple yet extremely useable ...can we add such dynamic reports in mail body???

@briandennehy6380 2 жыл бұрын

Magic man 👍

@chandoo_ 2 жыл бұрын

🧝‍♂️🧝‍♂️🧝‍♂️

@YannickSOUSSOU 2 жыл бұрын

Awesome tricks

@chandoo_ 2 жыл бұрын

Thanks Stephane...

@jagatkrishna1543 2 жыл бұрын

Thanks Sir 🙏💕💲

@chandoo_ 2 жыл бұрын

Thanks Krishna.. :)

@balamira297 2 жыл бұрын

Chaala manchi trick andi!

@chandoo_ 2 жыл бұрын

Thanks chelli 😀

@taizoondean689 2 жыл бұрын

Thanks sir 🙏

@chandoo_ 2 жыл бұрын

Most welcome

@jourdango2615 11 ай бұрын

can this be applied to pivot tables too? and make work with slicers

@mkarimsify 2 жыл бұрын

Wow!

@chandoo_ 2 жыл бұрын

😎

@sowjanyakakani6441 Жыл бұрын

Great techinique,if i try to paste the chart in PPT the check boxes are not coming in ppt,is there any way to do it

@sauravlabade1366 Жыл бұрын

7:48 that fast clicking 😂😂😂🤣🤣

@angelclarke1942 2 жыл бұрын

great want to do something familar with two different tables, so each table is a chart. how do I get this done?

@chinmayee9419 2 жыл бұрын

thanks for the excellent and useful video. i was facing an issue while changing color of the check box, while sending chart back, color of the check box changes. anyway i could fix by changing color from FORM CONTROL button, colors and lines (first) tab. it worked well.... just for information

@chandoo_ 2 жыл бұрын

Thanks for the update Chinmayee...

@rrahulification Жыл бұрын

Sir is this something similar to a slicer option?

@raaz845 Жыл бұрын

As usual amazing video, but one issue i am facing. When I add data labels then even after unchecking the box still it appears in chart area which looks very ugly if data labels in form in data callout shape. Please enlighten me to rectify this issue.

@idargosidargos5170 2 жыл бұрын

Many thanks Chandoo for all. May I have a question, when you include this in Powerpoint I loose this dynamic chart. Do you have tips for that.

@chandoo_ 2 жыл бұрын

You are welcome Idargos.. I will be adding another video on PPT technique.

@kandarpdesai3854 10 ай бұрын

Great video. Can anyone help me with how to move the chart in an output sheet with all the elements that keep the chart still interactive? Or is there any tutorial already available on this channel?

@wasabi4u724 8 ай бұрын

I am looking for that too, with no luck or can't figure out the proper keywords for the search.

@sayedishaq8406 Жыл бұрын

Hi, chandoo. in my excel I don't have the option of controls in format controls.... how should I get it

@chrism9037 2 жыл бұрын

Very cool, thanks Chandoo!

@chandoo_ 2 жыл бұрын

You are welcome Chris 😎

@ghifarialif3278 Жыл бұрын

Great tutorial thanks But how to check all the box with one click?

@harrisnomikos3583 2 жыл бұрын

Love the video Chandoo. How will this work on a pivot table as all my graphs feed of them? The raw data itself feeds off a SP list so the pivot table will only expand when new entries get entered.

@chandoo_ 2 жыл бұрын

You need set up an area outside Pivot where the values are referenced to Pivot values. Then everything else is as per this video.

@dahiya8229 2 жыл бұрын

Hello Chandoo , will it still work if i copy this chart to ppt.

@JCC.M. Жыл бұрын

Is there a way to do this on a pivot table/chart?

@bentatlim 3 ай бұрын

Very nice Video - Thank you for that - I have a question. Can you please create a video, where i can choose the Period for my diagramm. The Current day or Current week or Current Month) This would be amazing. I havent find a solution - Nowhere :( Perhaps you can help :)))

@ilsefrickx3372 2 жыл бұрын

Nice video! I have a (maybe stupid) question: at minute 4:32 you drag the formula down and to the right with just one movement... how do you do that? Is this a certain shortcut? Thank you for your response!

@chandoo_ 2 жыл бұрын

Thanks Isle... I copied the formula and selected the entire range and pasted it. I edited out the steps to keep it tight.

@mraaif988 Жыл бұрын

What happen in the another Chek box I'm just curious 🙂

@minnaahamad3791 7 ай бұрын

Hi Can this be copied over to a PPT?

@kethavathvasuram7038 2 жыл бұрын

Hello Chandoo Bro, I have a question that we have one video of waffle chart and there you have use one short keys to adjust the number in cells, could you help me of providing the short key.

@chandoo_ 2 жыл бұрын

I am using SHIFT to select columns and used mouse to adjust the width.

@kethavathvasuram7038 2 жыл бұрын

@@chandoo_ ohh thanks bro for your valuable reply, I thought we have some short kye...

@Adhikari580 2 жыл бұрын

Sir would you update the tutorial for stock control by barcode and item code text format to control expiration calculations

@chandoo_ 2 жыл бұрын

Not sure what that is... can you elaborate?

@mounabenchekroun6255 2 жыл бұрын

This is really Great ! But can we do it in ppt as well?

@chandoo_ 2 жыл бұрын

Good question. You can't directly add this to PPT, but I will add a video with another approach.

@ankursharma6157 2 жыл бұрын

Token of Gratitude!

@chandoo_ 2 жыл бұрын

Hey Ankur.. Thank you for the Super 😍

@comercialtoinzinhomateriai3724 2 жыл бұрын

18 / 5.000 Resultados de tradução (congratulations) (Brazil)

@EcongeniusAI 2 жыл бұрын

Hi chandoo, Your videos was awesome! But i have a query regarding power query in excel, I'm working on a database which i need to filter it by yesterday for today, if monday then for Friday. Can you please help me with it? Seeing things but no one works

@chandoo_ 2 жыл бұрын

Thanks MrE... It is tricky to explain how to do that in a comment. But the general approach is, first create a variable (or step) that tells you what the previous working day is. Then use that in the filter.

@sethweber454 Жыл бұрын

I demand to know where you got that picture of my at 0:31 😆

@chandoo_ Жыл бұрын

😂

@royalwinautomatedsoftware2985 2 жыл бұрын

Hai, I watched ur all videos and learn so much. I have one doubt for an i declared two variables Apple and Mango. I want count one by one Apple, Mango. If continue came apple that count apple if either mango that time leave Apple count and count Mango. Please tell me formula below i mention example. Example : `````````````` Apple - 1 apple Apple - 2 apple Apple - 3 apple Mango - 1 mango Apple - 1 apple Mango - 1 mango Mango - 2 mango

@ankursharma6157 2 жыл бұрын

@Johnny Eckert 1. Write the Header in cell A1. e.g. FRUITS. 2. Cell A2 onwards . . write the fruits. e.g. Apple Apple Apple Mango Apple Mango Mango 3. In cell B2, write the formula: =IF(A2=A1,B1+1,1) 4. Drag the formula down. Hope this helps.

@sarnathpatana7889 2 жыл бұрын

I don’t have control option within check box, do I need activate some Addin

@chandoo_ 2 жыл бұрын

Check again...

@ReformPak-dt3cq 2 жыл бұрын

Mr. Chandoo My Question is not related to this Video but i will be grateful if you could help. Is there a way we can count Alpha Numeric values of a cell, i.e. cricket score card if there is a no ball and it was hit for the boundary, can we count NB 4 written in the same cell as 5 in extras cell?

@chandoo_ 2 жыл бұрын

I would use Power Query to convert such values to two columns with NB/WD/LB etc in one column and number in another and then use find / replace to replace values with numbers and finally add them up.

@ReformPak-dt3cq 2 жыл бұрын

@@chandoo_ I am grateful for your help.

@sivachaitanya6330 2 жыл бұрын

Is it possible in power bi?

@chandoo_ 2 жыл бұрын

It is.. you can use a slicer and three measures that get values or BLANK() and show / hide them based on slicer selection.

@ezazakhtar6339 2 жыл бұрын

How to learn everything one needs to learn to become a data analyst and get hired as a data analyst basically what one needs to do, in a roadmap? This is my only question! 🙏 Please Answer.

@eswar418 2 жыл бұрын

Hiii sir. Could you explain all types of vizualations.... And how to use vizual... Where to use vizual....Why to use vizual plzzz sir could explain...

@chandoo_ 2 жыл бұрын

Sure, check this article - chandoo.org/wp/chart-selection-process/

@foodcity9875 Жыл бұрын

After preparation of chart ,We can't past it PPT?😢😢😢

@foodcity9875 Жыл бұрын

Thoses are not working in PPT,😅😅 can you help to us? After made the chart in excel ,it has to work on PPT also, have any other options chandoo??? Plz i need to present through PPT

@chandoo_ Жыл бұрын

you can use this technique for PPT - kzfaq.info/get/bejne/mJeEqbZlqZ3boqc.html