CP015: Handling big data, Controlling model railroad sets, Overcoming Excel obsession & more – ASK CHANDOO


CP015: Handling big data, Controlling model railroad sets, Overcoming Excel obsession & more – ASK CHANDOO
Zum Original Blog Chandoo.org

In the 15th session of Chandoo.org podcast, lets answer some of your burning Excel questions.

Handling big data, Controlling model railroad sets, Overcoming Excel obsession & More - ASK CHANDOO

What is in this session?

Around last week, I invited you to ask me anything. More than 150 people responded to this call and sent in their questions. Since answering all the questions is not possible, I handpicked roughly 10 questions to answer in this episode of Chandoo.org podcast.

In this podcast, you will learn,

  1. How to fill blank cells with data from above
  2. How to work with Big data in Excel
  3. How to combine data from multiple sources & analyze it in Excel
  4. How I am managing my life after starting Chandoo.org
  5. How to create and distribute stand-alone Excel products
  6. How to control a model railroad set using Excel VBA (not fully answered)
  7. How to control a pivot report with a form control or data validation drop-down
  8. How to overcome Excel obsession
  9. How to calculate average of 4 successive numbers

 

Go ahead and listen to the show

 

Links & Resources mentioned in this session:

Filling blank cells with values from above:

Fill blank cells with values from above - quick excel tip

Here is an article that explains the process in detail.

Controlling pivot tables with form controls + VBA

Here is a detailed tutorial explaining the process.

Calculating average of 4 successive numbers

Here is an example file with solution for this problem.

Formulas & Tricks mentioned in this podcast:

Resources for working with big data / multiple data sets:

Transcript of this session:

Download this podcast transcript [PDF].

So who gets the free 25 Excel tips e-Book?

Every one. Thats right. Since I got many more questions than I could answer, the least I can do is make you awesome in a small way. So here we go.

Click here to download your copy of 25 tips eBook.

Read it, use the tips and become awesome.

Subscribe to Chandoo.org Podcast

Other options to subscribe

 

Zum Original Blog
Autor: Chandoo

Edu-Store mit bis zu 85% Rabatt

Formula Forensics No. 037 – How to Count and Sum Filtered Tables


Formula Forensics No. 037 – How to Count and Sum Filtered Tables
Zum Original Blog Chandoo.org

A few weeks ago in the Chandoo.org Forums, Jsk_Lge asked “How do I count the number of items in a filtered list?

Narayan and I helped out with a well publicised Excel formula
=SUMPRODUCT(SUBTOTAL(103,OFFSET(A2,ROW(A2:A100)-ROW(A2),0)),(A2:A100=”D”)+0)

Today were going to unravel this and see what makes it tick.

As always at Formula Forensics, you can follow along using an sample file: Download Here

Formula

The Formula:
=SUMPRODUCT(SUBTOTAL(103,OFFSET(A2,ROW(A2:A100)-ROW(A2),0)),(A2:A100=”D”)+0) applied to Jsk’s data

We will use a Similar Formula better suited to our sample set of data

=SUMPRODUCT(SUBTOTAL(103,OFFSET(C1,ROW(A2:A20)-ROW(A1),0)),–(C2:C20=G1))

FF37_1

 

 

 

 

 

 

 

 

 

 

 

We can see in the data above that there are 9 entries with a Zone of North (Orange), 7 entries with a Category of D (Yellow), including 3 Entries that have a Zone of North and a Category of D (Red)

If we filter the data so that Zone = North is selected, we will see there are three entries (Red) that match our criteria and that the three Scores sum to 172 (81+9+82).

FF37_2a

 

 

 

 

 

 

We can see the data is Filtered by the Blue color of the Row Labels, The larger Row Separator and Filter Icon at the Top of the Zone Column, all highlighted in Blue.

Jsk_Lge’s problem was how to Conditionally Count the Number of entries when the data is Filtered. eg: Category = D when the Zone is equal to North.

For this exercise please ensure the Data Table is filtered so that category North is selected

FF37_5

Solution

Count Filtered Entries:

The solution is a Sumproduct based solution.

We know from Formula Forensics 007 that Sumproduct Sums the Product of the internal arrays.

In our formula: =SUMPRODUCT(SUBTOTAL(103,OFFSET(A1,ROW(A2:A20)-ROW(A1),0)),–(C2:C20=G1))
We can see there are two internal arrays
Array 1: =SUBTOTAL(103,OFFSET(A1,ROW(A2:A20)-ROW(A1),0))
Array 2: =–(C2:C20=”D”)

Lets look at each in turn:

Array 1: =SUBTOTAL(103,OFFSET(A1,ROW(A2:A20)-ROW(A1),0))

The Subtotal() returns a subtotal of a list or database. It has the functionality to work with Filtered Tables. The Syntax of the Subtotal() function is shown below:

FF37_3

Specifically the Subtotal(103, Array) is designed to count the number of Visible entries in the array

But in our example the formula only appears to see an array involving Column A, it doesn’t look at our data column, Column C at all?
This first array is being used to specifically mark, in an array, which Rows are Visible = 1 or Hidden = 0

So what does the OFFSET(A1,ROW(A2:A20)-ROW(A1),0) part do?

The Offset() function is designed to return a range based on the criteria it is given

In this case it will return a range, which will be 1 cell high and 1 cell wide
It will be Offset from Cell A1 by a formula ROW(A2:A20)-ROW(A1) and in the same column as A1 (,0)

As the Offset formula is inside a Sumproduct Function it will be treated as an Array Formula.
This means that it will be processed for every value in the range ROW(A2:A20)

ie:
In Position 1 it will hold ROW(A2)-Row(A1) = 1
In Position 2 it will hold ROW(A3)-Row(A1) = 2
In Position 3 it will hold ROW(A4)-Row(A1) = 3
. . .
In Position 19 it will hold ROW(A20)-Row(A1) = 19

This will create a vertical array of 1..19 which can then be used by the Offset() function

The Offset Function will take this array of offset values and offsets A1 by each value in turn, in effect creating an Array of Ranges
ie:
In Position 1 it will Offset(A1, 1) = A2
In Position 2 it will Offset(A1, 2) = A3
In Position 3 it will Offset(A1, 3) = A4
. . .
In Position 19 it will Offset(A1, 19) = A5

So the Offset() function is returning an Array of Range Addresses to the Subtotal() function

Once again the Subtotal() function will be be treated as an array function as it is inside the Sumproduct() function.
This means that the Subtotal() function will be executed for every position in the Array

ie:
In Position 1 it will hold Subtotal(103, A2)
In Position 2 it will hold Subtotal(103, A3)
In Position 3 it will hold Subtotal(103, A4)
. . .
In Position 19 it will hold Subtotal(103, A20)

Now this is where the clever part kicks in!

The Subtotal(103, ) function will count the number of Visible values in the array
But as the array is an array of Single cell addresses, A2..A20

So:
if each value in A2..A20 is Visible it will be counted
if each value in A2..A20 is Hidden it won’t be counted

Lets check

In the sample file Filter Zone to show North Only

Goto cell H7 and press F2, then F9
Excel will return ={1;0;0;1;1;1;1;1;0;0;0;0;0;0;0;1;0;1;1}

This is showing a value of 1 for each Visible Row and a 0 for each hidden row

Try changing the filters and check the results

Array 2: =–(C2:C20=G1)

As we have seen in previous Formula Forensics, a simple formula like: –(C2:C20=G1) is a powerful way of adding criteria to a Formula

In this example –(C2:C20=G1) will return an Array of ={0;0;1;1;0;0;0;1;1;1;0;0;0;1;0;1;0;0;0}, but How

In the sample file goto cell H23 you will see a formula: =(C2:C20=G1)
Press F2 then F9
Excel returns: ={FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE}

This is an array of True/False where each cell in range C2:C20 is compared to the cell G1
When they match it is True, When they don’t match it is False

The – - part is used to convert this array of True/False’s to an Array of 1/0′s
In the sample file cell H26 you will see a formula: =–(C2:C20=G1)
Press F2 then F9
Excel returns: ={0;0;1;1;0;0;0;1;1;1;0;0;0;1;0;1;0;0;0}

Finally We have our two arrays

=SUMPRODUCT(SUBTOTAL(103,OFFSET(A1,ROW(A2:A20)-ROW(A1),0)),–(C2:C20=G1))
which equates to :

=SUMPRODUCT({0;0;1;1;0;0;0;1;1;1;0;0;0;1;0;1;0;0;0} , {1;0;0;1;1;1;1;1;0;0;0;0;0;0;0;1;0;1;1} )

We can now see that Sumproduct will multiply the three arrays and add up the products
Array 1 {1;0;0;1;1;1;1;1;0;0;0;0;0;0;0;1;0;1;1}
Array 2 {0;0;1;1;0;0;0;1;1;1;0;0;0;1;0;1;0;0;0}
Array 1 x Array 2 {0;0;0;1;0;0;0;1;0;0;0;0;0;0;0;1;0;0;0}

Sumproduct correctly returns 3

FF37_4

Sum Filtered Entries:

Say we wanted to sum the values from the Score field based on Criteria and the Filtered data we can simply add another field to the original Sumproduct() function.

Eg: To sum the Score field whilst using the Criteria and Filtering we simply add a field to the end of the Sumproduct

=SUMPRODUCT(SUBTOTAL(103,OFFSET(A1,ROW(C2:C20)-ROW(A1),0)),(C2:C20=G1)*(D2:D20))

We can now see that Sumproduct will multiply the three arrays and add up the products
Array 1 = {1;0;0;1;1;1;1;1;0;0;0;0;0;0;0;1;0;1;1}
Array 2 = {0;0;1;1;0;0;0;1;1;1;0;0;0;1;0;1;0;0;0}
Array 3 = {43;39;87;81;68;42;72;9;51;74;75;17;10;73;48;82;38;58;96}
Array 1 x Array 2 x Array 3 = {0;0;0;81;0;0;0;9;0;0;0;0;0;0;0;82;0;0;0}

Sumproduct correctly returns 172

Download

You can download a copy of the above file and follow along, Download Sample File.

 

A Challenge

Can you solve the Count problem another way ?

Can you solve the Sum problem another way ?

Post your solutions in the comments below.

 

Other Posts in this Series

The Formula Forensics Series contains a wealth of useful solutions and information specifically about how Array Formula work.

You can learn more about how to pull Excel Formulas apart in the following posts: http://chandoo.org/wp/formula-forensics-homepage/

 

 

Zum Original Blog
Autor: Hui…

Ask me your Excel questions & You could win an eBook


Ask me your Excel questions & You could win an eBook
Zum Original Blog Chandoo.org

Time for another round of open-mic weekend.

As you know, Chandoo.org has been running a podcast show for last few months. We have completed 14 episodes as of today. For the 15th episode, lets have an “Ask Chandoo” as the theme.

What you need to do:

Simple. Head over to ask.chandoo.org and submit your questions. You can leave me a voice-mail or post your questions

What happens next?

I will listen (or read) your questions and choose a handful for the session 15 of our podcast.

What you get:

If your question is picked up for the podcast, you will receive the 25 Excel tips e-book.

How to find out if your question is picked:

Simple. Tune in to Chandoo.org podcast next Thursday and you will know.

So what are you waiting for?

Go ahead and ask your questions.

 

PS: You are welcome to ask questions about Excel, data analysis, automation, my personal life, how I run Chandoo.org etc.

PPS: Preference will be given to interesting, amusing & useful questions.

Zum Original Blog
Autor: Chandoo

How fireworks animated chart is made [video tutorial]

Backlinkseller

How fireworks animated chart is made
Zum Original Blog Chandoo.org

On July 4th this year, I published an animated fireworks chart for you. Many of you liked it. Quite a few wanted to know how its made.

So here is a video explaining the construction of fireworks.

(You can see this video on our YouTube Channel too)

Mirror site for watching the video:

If you cannot see the video on Youtube, watch it here.

Download workbooks

Download single firework demo file.
Download complete fireworks file.

More animated charts for you

If you want to learn how to animate charts & objects in Excel, go thru these examples.

Zum Original Blog
Autor: Chandoo

CHOOSE() me, an introduction to Excel CHOOSE function

Backlinkseller

CHOOSE() me, an introduction to Excel CHOOSE function
Zum Original Blog Chandoo.org

Today lets learn about Excel CHOOSE() function.

CHOOSE eh? What does it do?

To understand CHOOSE() and appreciate its uses, lets invent an imaginary boos-subordinate pair.

Jasmine is the boss. She is, well, lets call her peculiar. She likes olives, Tuesdays & color Red. She hates potatoes.

Martin is the faithful butler of Jasmine. He is obedient, quirky and tall. He likes lotuses, Fridays & color blue. He hates potassium.

Enter Jasmine’s scarf problem:

Jasmine likes to wear a different colored scarf every weekday. She likes to wear Red colored scarf on Mondays & Tuesdays. She likes to put on the blue polka dot scarf on Wednesdays. On Thursdays, she wears her olive colored scarf. On Fridays & Saturdays, she prefers the lovely orange blue scarf. Sundays are no scarf days.

No wonder she is peculiar.

On the first day of his job, Martin understood this schedule. Although he did raise his eyebrows (in bewilderment) more than once, he knew a butler should never question.

So everyday, soon after waking up, Martin would open up the list of scarf requirements, and figure out the scarf for that day. He would then neatly lay it out on Jasmine’s bed while she is in the shower.

Soon this all got boring.

So Martin thought, “Wouldn’t it be cool if I can feed the scarf schedule to a computer so it automatically told me which scarf to choose everyday morning!.”

Martin reached out to his ninja computer friend who knew how to do this.

YOU.

Your Excel solution for Jasmine’s scarf problem

After hearing the entire story, raising eyebrows a few times and looking at Martin with eyes full of pity, you set out to create an Excel workbook that told him which scarf to pick on any given date.

It is simple & elegant.

In the Cell B3, you wrote =TODAY()

so that everyday A3 will tell what is the latest date.

In B4, you wrote =WEEKDAY(B3)

Then in B5, you wrote a lengthy nested IF formula to figure out the scarf of the day.

Scarf of the day formula:

=IF(B4=1,"No scarf",IF(B4<=3,"Red scarf",IF(B4=4,"Blue polka dot scarf", IF(B4=5,"Olive colored scarf","Orange blue scarf"))))

Martin couldn’t be happier. Now that he has an awesome Excel file telling him what scarf to pick everyday, he has one less thing to worry.

BUT….

Soon after your Excel file, Jasmine had to replace the polka dot scarf with yellow striped one (she slipped an olive on the scarf while eating and it left a permanent mark).

While at it, she also changed the schedule.

And now, Martin is back to square one.

Late that week, he explained the problem to you over a drink. You quickly modified the file to suit new scarf of the day scenario.

The formula now looked like this:

=IF(B4=1,"No scarf",IF(OR(B4=2,B4=4),"Red scarf",IF(B4=3,"Yellow striped scarf", IF(B4=5,"Olive colored scarf","Orange blue scarf"))))

Thats when you got thinking.

The nested IF formula is awfully long and clumsy to maintain. May be there is a better one?!?

Enter CHOOSE formula, built for scarf of the day & more

CHOOSE() formula works beautifully for situations like this.

Instead of the long & clumsy nested IF formula, you could simply write a choose formula.

Syntax of the CHOOSE formula:

The CHOOSE formula is simple to write.

=CHOOSE(some number, value 1, value 2, value 3....)

and CHOOSE will pick a value based on some number.

For example,

=CHOOSE(3,"Chandoo.org","makes","you","awesome")

will result in you.

Scarf of the day CHOOSE Formula:

For our scarf of the day, the choose formula looks like this:

=CHOOSE(B4,"No scarf","Red scarf","Yellow striped scarf", "Red scarf","Olive scarf","Orange blue scarf","Orange blue scarf")

(or this if you want it for schedule prior to olive accident, =CHOOSE(B4,"No scarf","Red scarf","Red scarf","Blue polka dot scarf", "Olive scarf","Orange blue scarf","Orange blue scarf") )

Okay, what else can CHOOSE() do?

CHOOSE cant make you tall, rich or beautiful yet. But, it can do few more things.

Here is one such powerful example.

Fetch one range from many using CHOOSE

We can use CHOOSE() to fetch one of the many ranges, like this:

=SUM(CHOOSE(2, A1:A10, B1:B10,C1:C10,D1:D10))

This will result in the sum of the 2nd range, ie B1:B10.

Here is an interesting example of this:

How many values can CHOOSE take?

CHOOSE() can take up to 254 different values and return one of them based on the index number (first parameter).

What if I have more than 254?

Forget 254. Anytime you want to choose one value from more than a few (say 10), CHOOSE formula becomes tedious (as you have to select individual value cells or type them).

For all such cases (ie when you have list of values more than 10), I suggest using INDEX(). It is a powerful & versatile formula designed to handle situations like this.

Example workbook:

Here is the example workbook on CHOOSE. When you click the link, it opens Excel inside browser so you can practice this anywhere.

Do you CHOOSE?

I write CHOOSE() formulas often. It is a simple formula and I find several uses for it.

What about you? Do you use CHOOSE()? What are some of your favorite uses of it? Please share your thoughts using comments.

More examples on CHOOSE

Check out these additional examples to learn more:

Zum Original Blog
Autor: Chandoo

Here is a simple solution to your forgetful, leaky brian – “Just put it in a spreadsheet”

Here is a simple solution to your forgetful, leaky brian – “Just put it in a spreadsheet”
Zum Original Blog Chandoo.org

This is a guest post written by Joel Zaslofsky, author of Experience Curating.
Experience curating - how to use Excel to curate everything and improve your memory & life

How to Make “Put It in a Spreadsheet” Who You Are (and Not Just What You Do)

It seemed like a crazy question:

Could I use my passion for Excel as motivation to transform my leaky brain from a weakness into a strength?

Sure, I already had a fifteen year love affair going with Excel.

Every other email I sent to my co-workers had an attached Excel spreadsheet. And when I wasn’t using Excel for work purposes, I was using it for grocery list templates or budget tracking.

But I had to discover the answer to my crazy question.

So I told my wife Melinda in January 2012, “Honey, this is the year I put it in a spreadsheet!”

As I reveal in my article Spreadsheets and You, Melinda shot me a puzzled look. Was I joking? Should she ask me to elaborate?

She gingerly responded, “You’re going to put what in a spreadsheet?”

I shot her back a grin and said, “Everything!”

Experiences with videos, books, recipes, quotes, songs, online content, conversations, fleeting thoughts … everything. Little did I know that I was about to experience the gorgeous love child of curating and spreadsheets.

Hold on to your hat, my friend. You’re about to see how I use Excel to curate my entire existence. It’s something I call “Experience Curating,” and this is where things gets juicy.

The Power of Excel: Formulas and Numbers Optional

Initial version of curated spreadsheet looked like this

Wait a moment.

Isn’t there a Chandoo policy against publishing posts without awesome formulas or behind-the-scenes Excel strategies?

Fortunately, there isn’t (thanks, Chandoo!).

I respect my Excel-loving friends who can run VLOOKUP and macro circles around me. But unlike most people who use Excel for data analysis and number crunching, I enjoy it for the simplicity.

In fact, most of my spreadsheets have no formulas and some don’t even contain numbers.

Spreadsheets without formulas or numbers?! Blasphemy! Why even bother … right?

However, you probably know and love a few unconventional uses for Excel like creating role playing games or playing Super Mario World or Space Invaders. I just happen to know a lot of uncommon ways to use Excel with surprising results.

The most unconventional and best way I’ve tapped into Excel’s functionality has to be Experience Curating, though. In fact, it was so powerful that I spent a year writing a popular book about it.

Experience Curating is a three-part blueprint that empowers you to recognize, capture, organize, and share your most valuable moments. The first part builds the mindset that everything can be curated to benefit yourself and others. The second part integrates the six-step FAOCAS framework that makes any experience meaningful. And the last part applies the tools and best practices to grow actual curating currency.

I can’t convince you here that spending 0.1% of your time adding value to the other 99.9% through curating is worth it. And I can’t explore the nuances of the FAOCAS framework – Filter, Archive, Organize, Contextualize, Access, and Share – on Chandoo’s platform.

What I will do is show you how to use Excel to keep your most valuable experiences tidy, accessible, and sharable. What you use your curated experiences for – making money or personal finance mastery, improving your relationships, truly useful to-do lists, or world domination (for instance) – is up to you.

Using Excel to Curate (Even Excel Resources)

Since you like Chandoo, I assume you want to rock at Excel. Actually, I bet you’ve seen many Excel-related resources that you’d like to revisit or share with this community.

But it’s time to answer some tough questions like:

  • Are you archiving those Excel-related blog posts, knowledge base articles, YouTube videos, and other experiences?
  • Have you organized your Excel resources “experience elements” – the who, what, when, where, why, and how of an experience – in a logical and meaningful way?
  • Did you preserve the context of the content creator and add your personal layers of valuable context?
  • Can you access your Excel resources when, where, and how you want?
  • Can you share your resources quickly and with attribution to the source?

It’s OK if you answered no to any of these questions. Follow these steps, or customize them for your unique needs, and you’ll be answering yes in no time.

  1. Define your curated spreadsheets’ goal(s): In this example, the goals of the spreadsheet are to capture, organize, preserve context, instantly access, and share (when necessary) your Excel-related resources.
  2. Determine how many worksheets you need. My default is just one worksheet so that I can quickly see, sort, and filter everything in one place. You, however, may want multiple tabs so you can do fancy formula and visualization stuff that I don’t even know about. The decision is up to you.
  3. Identify your sort and filter needs. Knowing how you want to sort and filter your workbook helps decide how many and what type of experience elements would be useful. Is sorting by tag or experience creator essential? Is filtering by resource topic category or subcategory important? Whatever column headers (a.k.a. experience elements) you need to slice and dice should be required columns. Everything else can be optional.
  4. Create a simple instruction manual. Each experience element should have a logical name, a clear purpose, pre-defined acceptable values (preferably with data validation), a realistic example, optional general notes, and be either required or optional. You don’t want to leave this critical foundation in your ever-changing memory or subject to interpretation. Fortunately, it’s easy to create an instruction manual in a separate worksheet. Check out my example if you want to see Experience Curating in action.Example of curation instruction manual
  5. Think about the visual formatting. My minimalist nature seeps into Excel as I use almost no color and little overall visual formatting. But I still contemplate the ideal margins, orientation, header, footer, print area, and printed paper size in case someone else might want a physical version. I also choose a column’s cell format (e.g., text, number, or date), font (I like 11 point Arial), and text alignment (e.g., wrapped or indented) that’s ideal for each experience element column.
  6. Create a pre-populated list of labels for your required experience elements. Each pre-populated list lets me turn off my brain and rely on a set of labels that I determined with intention. I especially like data validation here so I’m prevented from entering a label that’s not part of my pre-populated lists. As a best practice, I also add customized error messages that prompt me to use an existing label or add a new one to the pre-populated list.

Use data validation to make sure experiences are categorized properly

There are tons of best practices around this process in Experience Curating, but these six steps will let you curate any combination of topic and medium in Excel.

If only the millions of Evernote or Facebook users knew what they were missing when they don’t use Excel to curate!

Excel + Experience Curating = Awesome

What Chandoo does with Excel is magical.

What a master curator like Robin Good does with Scoop.it, Zeef, or hundreds of other tools is inspiring.

Now imagine combining the best of Chandoo with the best of Robin Good.

That’s what Experience Curating is all about.

It takes little energy and time. And since you already have Excel, you have no extra investment to make.

Pick a single topic you like and start curating it in spreadsheets. In fact, I’ll make it easy on you. Here’s your first resource to put in your new spreadsheet … and it’s about spreadsheets: Skilledup’s 133 Excel Resources: Tutorials, Guides, Add-ins, Templates, & Courses.

Need to expand the combination of your curated topics and mediums beyond spreadsheets and Excel? Just use another Experience Curating template, the Curated Topics and Medium Tool Decision Grid.

Spend two minutes now to curate this post so you or someone else can benefit from it later. The proven process of the FAOCAS framework can help if you need it.

Your experiences don’t just happen to you. They can make big things happen for you.

Preferably in Excel, of course.

For the comments: What other ways do you know of to use Excel unconventionally? What tweaks would you make to the Experience Curating framework to make it even more valuable with Excel?

Added by Chandoo: Thank you Joel

Many thanks to Joel for writing this article and sharing an interesting & powerful way to use Excel to make ourselves smarter, better & more awesome. Exactly the kind of stuff that gets me excited.

If you enjoyed the article & want to put everything in Excel, take a minute and say thanks to Joel. Also, check out his book for understanding more about experience curating.

Note about the links to Joel’s book: I am using my amazon affiliate link for Joel’s book. That means Chandoo.org make a few cents, if you choose to purchase it thru my link. I genuinely like Joel’s book & I think you will enjoy it too. I would have recommended it even with out the affiliate link.

About the Author

Joel Zaslofsky is the creator and author of Experience Curating. When he’s not enjoying nature, working on his Smart and Simple Matters show, or chasing his sons around the house, he’s cranking out useful stuff at Value of Simple. Stop by to download the free tools that he and countless others use to simplify, organize, and be money wise.

Zum Original Blog
Autor: Chandoo

CP014: How to create awesome dashboards – 10 step process for you

CP014: How to create awesome dashboards – 10 step process for you
Zum Original Blog Chandoo.org

In the 14th session of Chandoo.org podcast, lets figure out how to make awesome dashboards.

CP014: How to create awesome dashboards - 10 step process for you

What is in this session?

Excel dashboards are much in demand these days, thanks to advancements in Excel & growing pressure on costs. Now a days, analysts & managers are expected to quickly put together a dashboard using Excel. But how do you make a dashboard? What process you should follow? These are the questions we address in this podcast.

In this podcast, you will learn,

  • Announcements about upcoming dashboard classes
  • Ten step process for creating awesome dashboards
  • 1. Talk to your end users
  • 2. Make a sketch of the dashboard
  • 3. Validate your understanding
  • 4. Collect data
  • 5. Structure the data

Go ahead and listen to the show

 

Links & Resources mentioned in this session:

Excelapalooza Excel conference:

Advanced Excel, Dashboards & Power Pivot Masterclass:

Creating Dashboards – complete tutorials:

Dashboard Examples & Inspiration:

Creating Dynamic Charts:

Transcript of this session:

Will be uploaded soon.

What process do you follow to create dashboards?

I have been following this 10 step process for the last 8 years with great success. Not only this process is easy to follow, but also it reduces the scope of errors significantly.

So what about you?What process do you follow when creating dashboards? Please share your thoughts & experiences using comments.

Subscribe to Chandoo.org Podcast

Other options to subscribe

Zum Original Blog
Autor: Chandoo

Come join me at Excelapalooza conference in Dallas or Masterclass at Houston this September

Come join me at Excelapalooza conference in Dallas or Masterclass at Houston this September
Zum Original Blog Chandoo.org

If you listen carefully, you can hear my screams of joy. I have 2 very happy news to share with you.

  1. I am speaking at Excelapalooza Excel conference at Dallas (Texas) between September 21 to 24.
  2. I will be conducting an Advanced Excel, Dashboards & Power Pivot masterclass at Houston (Texas) between September 15 to 18.

Excelapalooza Conference

Ever since I started working as an Excel trainer & blogger for full time, I have felt one gap. There is no dedicated Excel conference (I am sure few one-off events happen here & there, but no large scale networking & knowledge sharing events).

Excelapalooza addresses this gap.

Excelapalooza is an annual Excel conference conducted by BizNet Software (they make Excel based reporting easy for us). This year is the 3rd time they are conducting the conference. And I am absolutely humbled & honored to be speaking at this event. The folks at BizNet are so kind that they made a “Chandoo track” at the conference for my sessions.

Excelapalooza - Annual Excel User Conference - Chandoo will be speaking on making awesome dashboards

I will be talking about:

  1. How To Select The Right Charts For Your Data (9:40 AM to 10:30 AM on 22nd September, repeat on 23rd)
  2. 10 Mistakes To Avoid When Making Dashboards (10:40 to 11:30 on 22nd, repeat on 23rd)
  3. Come, Design Excel Dashboards In An Afternoon (1:00PM to 3:50 PM on 22nd, repeat on 23rd)

Sessions 1 & 2 are presentation style while session 3 is a hands-on lab where all the delegates will be making dashboards along with me.

This conference is a great way to enhance your Excel skills, meet up fellow Excel users, network with them, find latest happenings in the world of Excel based BI, reporting & analysis.

If all this sounds interesting:

Please click here to register for the event. As a Chandoo.org reader, you get 10% discount on the conference fees. I am hoping to see you at Dallas & tell you how awesome you are.

Houston, we have a masterclass!

Since I am flying to Dallas all the way from India to attend Excelapalooza, it makes a lot of sense to do a little more. So I am really happy & excited to announce the one & only Advanced Excel Masterclass for this year in Houston, Texas between 15 & 18th of September, 2014.

What is this masterclass?

This 4 day masterclass is aimed at managers, analysts, reporting professionals, executives in sales, marketing, customer service or anyone who wants to learn about Data Analytics, Advanced Excel, Dashboard reporting. It has 2 parts.

Advanced Excel & Dashboard Reporting (2 days – 15 & 16 of September)

Power Pivot & Data Analytics (2 days – 17  & 18 of September)

Advanced Excel, Power Pivot & Dashboards Masterclass - Houston, Texas - September 2014

What happens in a masterclass?

Our Excel masterclasses are intense, hands-on & fun experiences aimed to sky-rocket your Excel skills (analyzing data, creating awesome dashboards & charts, building amazing Power Pivot models, using Excel efficiently & productively). The masterclasses are hands-on, it means you will be using a computer (we will provide it, but you are welcome to bring your laptop too) and following my instruction along way. Together as a class, we will build cool charts, awesome analytics & dazzling dashboards. (here are 4 dashboards we made in prior masterclasses).

You will get tons of course material (workbooks, pdfs, presentations & cheat-sheets), all your doubts will be answered and you will be well fed (lunch & snacks are provided).

What more, you get to network with fellow Excel users & learn from them.

Please download our masterclass 2014 edition brochure for more info.

How much is it?

The tuition fee for this masterclass is US $1,200 (early bird fee until 31st of July) for each of the 2 sessions (Dashboards & Power Pivot).

If you purchase both classes (Dashboards & Power Pivot), you get $200 discount, thus paying $2,200.

To register for this masterclass, please click here.

I hope to see you in our masterclass and help you become awesome in Excel. But hurry up, our masterclasses have limited enrollment & sell out fast.

Thank you

I just want to say thank you,for supporting Chandoo.org and making all of this happen. If you have not decided to visit Chandoo.org, learn from us & spread a good word about it, none of this would have happened. Thank you so much for letting me share my knowledge, ideas, mistakes & experiences with you all. You are awesome.

 

Zum Original Blog
Autor: Chandoo

Winners of state migration dashboard contest

Winners of state migration dashboard contest
Zum Original Blog Chandoo.org

Finally the wait is over. Here are the winners of our 2014 dashboard contest.

Consolation prize winner – Thiruselvan

Originally, I had planned to give only 3 prizes. But the sheer number of brilliant entries forced me to add one more prize. The 4th prize goes to Thiruselvan. He will get a choice of 3 Excel books from Amazon.

3rd Prize winner – Arnaud Duigou

Arnaud’s dashboard was ranked highly by both our judges & loved by you as well. He will receive a Samsung Galaxy Tab 3 (valued at $199) very soon.

Option 4 by Arnaud Duigou

State to state migration dashboard - by Arnaud Duigou - snapshot 1State to state migration dashboard - by 5 - snapshot 2State to state migration dashboard - by 6 - snapshot 3

2nd Prize winner – David Hoppe

David’s smartly designed dashboard is well appreciated by our judges and you. And thus he gets an iPad Mini (16GB), valued at $399.

First prize winner – Roberto Mensa

Well, when you have Roberto Mensa, the Messi of Excel charting world, the Roger Federer of visualization competing in a contest, you know what to expect. Roberto’s clever visualization, expert use of powerful Excel techniques has won our hearts and votes. He gets an iPad Air (16 GB), valued at $499 very soon.

Option 37 by Roberto Mensa

State to state migration dashboard - by Roberto Mensa - snapshot 1State to state migration dashboard - by 3 - snapshot 2State to state migration dashboard - by 4 - snapshot 3

Highly commendable entries

Apart from these 4, 8 more dashboards deserve lots of praise. You can easily learn dozens of Excel tricks, charting techniques and get loads of inspiration by observing the work of,

Jean-MarcVoyer, Matthew Waechter, Jon Schwabish, Michael Bellot, Srinivas Chilukuri, Niyaz Shaffi, Stacey Baker & Mark Weber.

Thank you.

Thank you so much for participating

Everyone who participated in this contest deserves appreciation. It is not an easy task to carve out a dashboard (or set of charts) from raw data like this. You are truly awesome for taking the first step.
Special thanks to our judges Jordan & Hui. Your valuable time & feedback is really appreciated.

Congratulate the winners

If you enjoyed this contest, say congratulations to the winners & other participants. :)

PS: Explore all the 49 dashboards & learn something new.
PPS: And just in case you missed the fireworks, you can see them in Excel.

Zum Original Blog
Autor: Chandoo

4th of July Fireworks – an Excel animation for you

4th of July Fireworks – an Excel animation for you
Zum Original Blog Chandoo.org

To all our readers & friends from USA,

I wish you a happy, fun & safe 4th of July.

For the last 4th of July (2013), we (Jo, kids & I) were in USA. We went to Washington DC to meet up a few friends for that weekend. And we had one of the most memorable evenings of our lives when we went to national mall area in the evening to watch beautifully choreographed fireworks. Kids really loved the amazing display of fire-crackers and enthusiasm. (here is a pic, taken by Nakshu, our daughter)

While we all are back in India this time, it doesn’t mean we cant celebrate 4th of July. So I made some fireworks. In Excel of course.

Here is a little Excel animation I made for all of us.

4th of July Fireworks – Excel animation

First watch this quick demo (<15 secs)

Download the 4th of July fireworks workbook

I got the Excel fireworks idea very late in the evening. So the file is not very clean. But easy to understand and play with. Download it here.

How is this made?

Lets spare the detailed tutorial for another day. Here is a quick summary.

  1. Lets assume a fire work goes in a straight line at an arbitrary angle between 75 to 105 degrees (90 being vertical) to a random height.
  2. Lets assume the firework effect creates 40 spokes of a perfect circle whose radius grows as the firework explodes.
  3. So we create a scatter plot with lines & spokes.
  4. Thru VBA, we increase the length of line from 0% to 100%, thus creating firework shooting to sky effect.
  5. Then, we increase the radius of circle from 0% to 100% to create firework explosion effect.
  6. Finally we change the previous line height & circle radius back to 0% before showing next firework.
  7. At last, we toggle the display visibility of message (“Have a fun 4th of July”)

That is all. Here are a few previous examples that detail some of these techniques.

So thats all for now. Enjoy your 4th of July weekend and lets meet Monday with something awesome.

Zum Original Blog
Autor: Chandoo