How the tax burden has changed over the years – Excellent chart by NYTimes & Redoing it in Excel

Share

Facebook
Twitter
LinkedIn

If I need some charting inspiration, I always visit New York Times. Their interactive visualizations are some of the best you can find anywhere. Clear, beautifully crafted and powerful. Long time readers of Chandoo.org knew that I like to learn from visualizations in NY Times & redo them using Excel.

Today let me present you one such chart.

How the tax burden has changed over the years – Visual story by NY Times

First take a look at this story on New York times website. Go ahead and check it out, I will wait for you.

Back already. Good.

Now that you have seen a well presented story with the support of panel charts, let us learn how to re-create such charts using Excel.

Look at the tax burden Excel chart

Take a look at the excel implementation of this chart below. Read on to learn how to create this.

Tax burden over years chart - recreated in Excel

 

[click here to see larger version]

Recipe for creating this chart using Excel

We need below ingredients to make this chart using Excel

  • Raw data
  • One area chart and few lines on top
  • Simple formulas
  • One Slicer (to select an year)
  • One large cup of coffee or whatever else that you gulp

So if you are ready, lets start cooking.

Step 0: Arrange data

This is a prerequisite for any charting exercise. Although we can work with data in any shape, for quick results, arrange your data in this format:

Data for tax burden chart

In the example file you will find data for overall tax burden for all 9 tax brackets in the years 1980-2010.

Step 1: Create an area chart from all the data

Simple, select tax bracket & tax percentage rows and create an area chart. This is how it should look.

Step 1: Create an area chart from all data - tax burden chart in Excel

Step 2: Insert 2 columns after every tax bracket in your source data

Very simple, just add 2 blank columns after every tax bracket to your source data. This will change your chart to,

Step 2: Insert 2 columns after every tax bracket in your source data - tax burden chart in Excel

Step 3: Adjust data settings so that blank cells are treated as gaps

Right click on the chart, go to Select Data > Hidden & Empty cells

Specify that all blank cells should be treated as gaps. See below.

Step 3.1: Treating blank cells as gaps - tax burden chart in Excel

Now, your chart should look like this:

Step 3.2: area chart with gaps - tax burden chart in Excel

Step 4: Add a line to the chart & format it

Although our chart looks almost like NY Times chart, we still need to show a line on top. For this,

  1. Go to your data, reselect all the tax burden %s and copy them.
  2. Come back to the chart, select it and paste. (more on this)
  3. Excel will add this new data as another series to chart
  4. Right on this new series, choose Change series chart type
  5. Select Line chart
  6. Format the chart so that it looks like below.

step 4: add same data again and convert it in to a line - tax burden chart in Excel

Step 5: Remove grid lines & fake them using additional series

Excel chart’s grid lines always show up behind the data. For our chart, we want them on top. So let just delete grid lines and fake them using additional lines on the chart.

For this,

  1. In your data, add 9 extra rows at bottom (why 9? because we want to show one grid line for every 5% and the maximum we have is around 45%)
  2. Fill first row with 0.05, second with 0.1, third with 0.15… ninth with 0.45
  3. Copy all these and paste them in the chart. You should have nine lines across the chart.
  4. Now, format each line so that it looks like a dull white line with dashes.
  5. When you are done, the final output should look like this:

Step 5: Remove grid lines and fake them using additional series

Step 6: Remove horizontal axis (x-axis) labels & fake them too

Again, horizontal axis labels produced by Excel are useless for us. So we will create our own.

  1. First delete the existing axis.
  2. Then add a text box to the chart and place it where axis should be.
  3. Type the values 1980 few spaces 2010.
  4. Adjust the font size to 7pt.
  5. Now play with the text box until you are satisfied for one tax bracket.
  6. Then copy paste it 8 more times and adjust their positions.

Although we could automate this step, it felt un-necessary as the years are not going to change.

Our chart is almost ready

At this stage, our chart looks like below.

Step 6: remove x-axis labels and fake them using text box with 1980 spaces 2010

It is almost ready, but we need few more additions.

  • We need to add labels to first & last point in each tax bracket.
  • We need a mechanism so that user can select a particular year.
  • When any year is selected, we need to show that year’s tax burden %.

Adding labels for first and last points

This is done by adding one more series of values. This new series (lets call it label-first-last) will have values for only 1980 & 2010. Everything else will be NA().

The formula I used to generate this series is,

=IF(OR(year=1980,year=2010),taxburden,NA())

Once this series is added, we just format it so that only markers are shown (no line) and then add data labels. Format the labels to show in 0% format. Adjust their size and position.

Also add arrow shaped boxes on top to label each tax bracket.

 

Tax burden chart in Excel - after adding labels for first and last year

Enabling year selection thru Slicers

[This works only for Excel 2010 or above]

In a blank sheet type the years 1980 thru 2010. Select them and create a pivot.

Once the pivot is ready, insert a slicer for the years field.

For detailed steps on slicer creation see this illustration.

Creating years slicer using Excel 2010 - tutorial

Figuring out which year is selected

Once the slicer is ready, we need to figure out if user made a selection thru slicer. To do this,

  1. Use a simple formula to check how many values are shown in the pivot table (ex: COUNTA(pivot!A:A) )
  2. If only one value is shown, then extract it by referring to first row item in pivot (=pivot!A4)

Adding labels for selected year

Once we know which year is selected, we can easily create one more series that has NA() for all values except selected year. The rest you know.

Final outcome – Tax burden over the years chart using Excel

Tax burden over years chart - recreated in Excel

Download this example & Play with it

Click here to download the tax burden chart. Play with it to learn more. Examine the formulas in “Data” sheet & scroll down on “Chart” sheet for step by step instructions.

Do you like this chart?

I really loved how NY Times has been able to tell a very good story by using multiple panel charts. These are great way to examine multidimensional data and understand what is going on.

What about you? Do you like this chart? Please share your thoughts and ideas using comments.

More such charting inspiration

If you are looking for some fresh charting inspiration & ideas, you are at the right place. Check out these examples to get started:

Do you want to create powerful & insightful charts like these?

If you want to learn how to create these types of charts, consider enrolling in our Excel School program. Be warned, you will become unusually awesome in Excel by going thru our course 🙂

Click here to know more about Excel School.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

27 Responses to “How to Embed Youtube videos in to Excel Workbooks?”

  1. Vlad says:

    Thank you!!
    The trick is to get that URL for video. Some videos have this feature disabled... hmm...

  2. Fred says:

    very cool. how did you record what you do on Excel? it didn't seem like you was using a camcorders.

  3. Robert says:

    This is a great tip. Is it possible to insert a PDF on an excel worksheet? I usually receive quotations in pdf format, and would like to show the quote on a worksheet, rather than using hyperlinks. Any and all help would be greatly appreciated.

  4. Gregory says:

    Your instructions didn't work for me, ether in Excel 2010 or 2007, and I tried several times, following the instructions very carefully and to the letter.

    What I found out is that, for some odd reason, copying the YouTube URL then adding the ?fs=1&hl=en_US to the end would not work at all for me.

    But if I went to the Embed code in YouTube and copied value parameter (minus quotes) and pasted it into the movie property, the movie would play. The Embed code value parameter includes the link and the extra bit you included at the end. No need to copy that as an extra step.

    • West says:

      Best feedback on here. Worked like a charm after removing extra YT code. Thanks..

    • Sonu says:

      @Gregory,
      Thanks your instructions.
      I have carefully followed the Instructions mentioned by you and still i am unable to do embed the video in excel.
       
      Please help and your immedate reply will be higly appreceiable.
       
      Regards
      Sonu Monga
      A Chartered Accountant
       

      • HARSHUL says:

        If you are using excel 2010 or higher. then there is nothing like Shockwave flash player.
        In that case you have to use windows media player.
        There is a control naming Windows Media Player in that list.
        Insert that contol and then open properties and insert link in URL.
        It will work.

    • Sonu says:

      @Gregory,
      Thanks your instructions.
      I have carefully followed the Instructions mentioned by you and still i am unable to do embed the video in excel.
       
      Please help and your immedate reply will be higly appreceiable.
       
      Regards
      Sonu Monga
      Chartered Accountant

  5. vlad says:

    It wouldn't play because you need to indicate the exact location of flash file....by 'Embedding' the file, you get the the exact URL, so it can be played....some videos can't be 'embedded' that's why it won't work....

  6. Jason says:

    My developer tools does not list a control for "Shockwave flash object". Where/How do I find it?

  7. modeste says:

    Hi Chandoo,

    why don't use OCX Window media player ???
    you can play your own local video or music
    just indicate exact location (Full path or URL)
    i.e :
    C:\Users\Public\Music\Sample Music\Kalimba.mp3
    C:\Users\Public\Videos\Sample Videos\Wildlife.wmv

    with macro and userform :
    Sub PlayMedia()
    On Error Resume Next
    UserForm1.WindowsMediaPlayer1.URL = ThisWorkbook.Path & "/" & ActiveCell.Value
    ' ----- or
    ' ----- UserForm1.WindowsMediaPlayer1.URL = Exact location
    End Sub

  8. Very cool. I also could not get the instructions to work, but was able to use Gregory's suggestion about the embed code. Thanks for posting!

  9. Maria says:

    Thanks Chandoo. This is very cool. I was able to make it work using the embed code copying from http: up to _US as you indicated. This is a great way to provide additional training or message as you package your deliverable.

  10. [...] Trying to become a bit flashier with your dashboards? Maybe you’re just looking to include additional material. Take a look at this quick tool to embed YouTube videos in excel workbooks. [...]

  11. Clarity says:

    Hi Chandoo,

    Excellent tip. Very useful.

    Is there anyway to link the path in the properties to a cell value so that the user can select the video from a drop down and then have the video play?

  12. Nav says:

    nothing happend in Embed Youtube videos in to Excel Workbooks, it shows only white blank screen.

  13. nihal says:

    Hi Chandoo, How Do I put more than one URL in movie field?

  14. Steve says:

    Hi Chandoo,
    very good tip.
    But how do I start the embeded video in an xlsm file once the tab is selected or through VBA programming?
    Appreciating your answer
    Steve

  15. KAMALAKAR says:

    Query:
    I have created 4 sheets excel file, but when i print this file to PDF it generates two sheets one PDF and two Sheets one PDF … can i know the setting which i had to change. because i want all 4 sheets in one PDF
    while Printing i did setting as "Print Entire Workbook"
    Awaiting for your reply.

  16. Brent says:

    Help!! THis works great, but I'm trying to use VBA to change the URL, which I can do. The problem I am having is getting it to play via VBA.

    The object has both .play and .playing = true properties, but neither will actually play the video after updating the .movie url. The correct video appears within the object, but I can't get it to play from VBA... which I really need it to do.. Thoughts?

  17. biljo says:

    Hello

    Go to Developerr tab and select more control then find windows media player just click it.

    after that you have to right click on the embeded object and select properties there you need to add your video file path.(dont forget to include extension like .avi, .mov etc.) in URL field. Then Press Alt+11 and deselct design mode. Once you close the module your video start playing.....

  18. Rob G says:

    For the life of me, I cannot get this to work. I have followed Chandoos instructions to the letter and also tried using the embed code as Gregory suggested, all I get is a blank white box where the video should be. Is there any other reason that this might not work? Flash version perhaps? Quite frustrating.

  19. Mark says:

    Hi I was wondering if was a way after adding a video if one could save to HTML format and it would work?

  20. Yatin says:

    Hi Chandoo,
    This is very helpful, however, is there a way to auto-play the embedded youtube video as soon as somebody opens the excel file? Can you share the macro for the same?

  21. nonsofar13 says:

    Hi,

    Very cool, how is it possible to start automatically the video when the excel sheet is open?

    I would like to start the video when i open the worksheet :).

    Best regards.

  22. Yasser says:

    Hello
    When trying this method, I got Flash-embedded videos are no longer supported. Is there a solution for this problem?

Leave a Reply