Charts Archives - Page 3 of 3 - Excel Campus

Archive

Category Archives for "Charts"
4

Review of the Excel Dashboard Course from My Online Training Hub

Are you interested in learning how to improve your charting skills and create world class dashboards in Excel?

My friends Phil and Mynda over at My Online Training Hub have just opened a new session of their highly acclaimed Excel dashboard course, and enrollment is only available for a limited time.

I have personally paid for and taken this course on my own, and believe it is outstanding. I am a firm believer in continually learning and developing my Excel skills, and I feel that this course was a great investment for me.

Video Review of the Course

Checkout my video review of the course below.  I take you into the members area of the course to show you exactly what you get.  I also explain all the benefits of the course and how it helped me.

Why are Dashboards Important to Learn?

The amount of data we collect is growing at a very fast rate, and organizations are looking for employees that have the ability to analyze and present this data in a clear and simple manner. As an Excel user it is extremely important that you know how to utilize the charting functions that Excel offers.  The default charts in Excel don’t always make this easy and there is quite a bit of skill and knowledge required to create a successful chart or dashboard.

The Dashboard Course will help make you an expert at working with charts and creating interactive dashboards that will impress your boss, colleagues, and audience.

What’s Included in the Dashboard Course?

The course is a self-paced online course with videos and sample workbooks to download.  You will get access to over 10 hours of video!  It’s a huge amount of training material, but don’t let this overwhelm you.  The videos are divided by topic and each video is around 5 minutes or less.  The course syllabus is organized nicely, making it easy for you to find a video on a specific topic.  You can watch a few videos during your lunch break, and become an Excel and dashboard wizard in just a few weeks.

The 10 hours of video includes 6+ hours on dashboards and 3+ hours on general Excel topics that will help you build a foundation for the more advanced dashboard techniques.

There are many great benefits to taking this course, and you can read all the details at the following link.

My Favorite Features of the Course

I have taken quite a few different Excel courses, and here are some of the reasons the course from My Online Training Hub stood out to me.

  • 10 hours of video that is divided into small 5 minute increments by topic.  This allows you to focus on learning and mastering one topic at a time.  Long videos can drag on and lose your attention quickly, but Mynda’s videos are quick and to the point.
  • Watch the videos any time and in any order you chose.  You might want to watch a video on a specific topic for a project you are working on, and the course syllabus makes it easy to see all the topics that are included in the course.
  • Excel workbook files are included with each topic so you can follow along and immediately apply what you’ve learned.  This gives you a true hands-on experience in an online course.
  • Learn advanced formulas and functions needed to organize your data for many types of charts.  The foundation of any good chart is its data, and Mynda does an excellent job of explaining some of the advanced techniques needed to create charts that will display your findings.
  • Professional quality throughout the entire course keeps you focused on learning.  Having produced online content myself, I have a great appreciation for how much hard work and attention to detail goes into this course.  It is really well organized and contains some of the highest quality content I’ve seen in online training courses.
  • Learn how to structure your data for use in charts.  If you read my last post on structuring data for pivot tables, you know that it is extremely important to understand how to structure your data before you can analyze it properly in Excel.  The same methodology applies with creating charts, and can get even more complex.  Mynda does an excellent job of explaining this throughout the course with various types of charts.

No matter what you current Excel skill level is, I believe you will find this course extremely beneficial for your career.  Personally, I consider myself to be at an advanced level, but I definitely don’t know it all.  I take every opportunity I can to develop my skills and learn.  I’m helping promote this course because I believe it is well worth your money and will provide a tremendous value to you.

FREE Bonus Just For You

Right now I’m giving away my PivotPal Add-in as a free bonus when you register for the course. That’s a $37 value.

PivotPal will help you work with pivot tables and save you time when creating dashboards.  You will also get my video series on how to create interactive dashboards using Power Query.

So there are a ton of great bonuses for you!

To get the bonuses:

  1. Click this link to go to the Dashboard Course registration page.
  2. Select one of the course bundles.
  3. At checkout, enter the promotion code: tabs
  4. I will send you PivotPal and the bonuses a few days after you register.

Key dates for the course:

  • 20% Discount ends this Thursday, February 18th at 8pm PST
  • Registration closes Thursday, February 25th at 8pm PST

The course will be closing registration soon, so get signed up before it’s too late.  I also have an article where I answer some frequently asked questions about the course.

Excel Dasboard Course

Click Here to Register for the Dashboard Course

 

*I want to let you know that I am an affiliate partner with MyOnlineTrainingHub, which means I make a commission if you click the links above and purchase the course. I promote this course because I feel that it really helped me improve my Excel skills, and it’s an excellent value.

12

Move Excel Charts with Arrow Keys

This post and video contain lots of tips for moving your charts and shapes with the arrow keys.  This is very useful when you need to arrange and align multiple shapes on your worksheet.  I also have a free Chart Alignment add-in that allows you to move the objects inside a chart (titles, labels, legends) with the arrow keys and alignment buttons.

Video

Move Chart with Arrow Keys

Typically when you select a chart with your mouse, the box surrounding the chart looks like the following.

Chart Single Click No Arrow Keys

When the chart is selected like this, nothing happens when you press the arrow keys to try and move the chart around the worksheet.  Instead, the objects within the chart are cycled through and selected.  Not what we want.

Solution #1

Hold Ctrl and Left Click the chart –  This selects the shape object that the chart resides in.  Circles will appear on each corner of the chart.

Chart Bounding Box Ctrl Click

The chart can now be moved around on the sheet with the arrow keys, making it easy to align it with other shapes, charts, or cells in your worksheet.

Solution #2

Select multiple charts or shapes – Hold the Ctrl key and select at least two charts using left click.  The bounding box will still look like the same as when you only have one chart selected, but you are able to move the charts with the arrow keys.

Select Multiple Charts

Move or Create a Shape Inside a Chart

Moving shapes inside a chart can get a bit confusing.  Shapes, such as a text box or rectangle, can reside in two places:

  • either inside the chart object,
  • or on the worksheet (outside of the chart).

The confusing part is that a shape residing outside the chart on the worksheet can be placed on top of the chart.  This makes it look like the shape is inside the chart even though it is not.  In this case, when you move the chart, the shape will NOT move with it unless you have both the shape and the chart selected.

A shape located inside the chart will move with the chart.  This makes it easier to move and align the chart because you don’t have to worry about selecting multiple objects when doing the alignment.

There are two ways to get a shape inside a chart:

  1. Create the shape inside the chart – First select the chart so the bounding box appears around it, then draw the shape inside the chart object area (Insert tab
    > Shapes menu).
  2. Paste a shape inside the chart – Cut or Copy an existing shape on your worksheet, then select the chart and paste the shape.  This will put the shape inside the chart.

Move Shape Inside Chart with Arrow Keys

Now that your shape is residing inside your chart, you can use the arrow keys to move it around and align it with the other objects in your chart (title, axis, legend, etc.)  See instructions below the screencast.

Move Shape Inside Chart with Arrow Keys

To move a shape inside the chart with arrow keys:

  1. Ctrl+Left Click the border of the chart so you get the circles on the corners.
    Ctrl Select Chart with Shape
  2. Then Ctrl+Left Click the shape inside the chart.  You will notice that the circles remain on the top left corner of the chart.
    Ctrl Select Shape Inside Chart
  3. Keyboard arrows are now enabled so you can move and align your shape (text box) within the chart.

Conclusion

You should now be able to get all your charts and shapes lined up to perfection using the incremental movements of the arrow keys.  This is very useful if you read my post on Panel Charts as an alternative to Stacked Charts and want to create panel charts.  In a future post I will share more tips and shortcuts for aligning and viewing your charts.

What are some shortcuts you use to align your charts?

17

Stacked Column Bar Chart Alternatives – Find the Missing Trends

Stacked bar or column charts are used a lot by the media and corporate world.  I believe they are popular because they display a lot of information in one chart, and are relatively easy to create in Excel.  However, I’m not a big fan.  I’m going to explain why I consider them to be “bad charts”, and present some alternatives.

Stacked Column Chart - BAD CHART

Bad Charts

I consider the stacked column chart above to be a “bad chart” because it doesn’t do a good job of displaying the trends in the data.  There are a total of six data series displayed here; the five regions plus the total.  But we are really only able to see trends in two of the six series.  We can see trends in the North America and Total (height of entire column) series because the baseline for these series is flat.  The baseline is basically the x-axis at $0.  This is very important when creating a chart that is displaying a trend.  Our eye is able to distinguish the baseline as the starting point for each column, and then compare the top point of each column moving from left to right.  This works great for the North America and Total series, but it is very difficult to see the trends for the other four regions.

Stacked Column Chart - Baseline Example

Uneven Baseline

Stacked Column Chart - Uneven Baseline Example

The image above shows the difficulty in quickly seeing the trend in sales for Asia because the baseline is uneven.  I emphasize “quickly” because we want our chart to clearly and easily communicate a story or trend.  It would probably be easier to just look at the actual numbers to see a trend versus trying to determine if the bars are increasing or decreasing in size over time.  And these bars for Asia get hidden when you consider all the other regions that are also stacked above and below it.  It’s an absolute mess! 🙂

Here is a line chart that shows the actual trend for Asia.  It’s much easier to quickly see that sales are declining over the last three quarters of the year.  This would probably draw attention to the performance of the region, and spark some action to be taken to correct the problem.

Asia Line Chart

When to Use Stacked Charts

Stacked charts can work if there are drastic changes in your data over time, and you want to only display the trend of one or possibly two series.  It’s best to highlight these series in a color that stands out, and add some text to describe the trend.  You also want to move the series to the bottom of the chart so it sits on the baseline, making it easy for the reader to see the trend.

Acceptable Stacked Column Chart

Alternative 2 below also makes use of the stacked chart by giving it a dynamic baseline to quickly analyze trends.

Solutions

Now that we have an understanding of when NOT to use stacked charts, we can start thinking about alternative methods that clearly and quickly tell the story.  The use of these will depend on what trends you find in your data, and how you want to convey the message.

Alternative #1: Panel Charts

Panel charts are a group of small charts organized together in a panel.  This is a good way to break out each region into its own chart.

Panel Chart - Alternative 1

Now we can start to see trends within each region.  These trends are not possible to see in the stacked chart because of the uneven baselines, and scale of the chart.  Sales in North America and Europe are much greater than Asia and Africa, so the trends get lost.  There are some important trends getting lost if you don’t look at them closely.  You can highlight those trends in a panel chart as I did above.  We can see that sales in Asia are declining, while sales in Africa have almost doubled over the year.  The dollars are small compared to total sales, but there might be great opportunities being missed if this is only analyzed in a stacked chart.

If you really want to impress your boss, you could add zoom buttons to each panel using the Zoom on Charts Macro (available for free download).  This is a great feature to add to an interactive dashboard.

Zoom on Panel Charts

Alternative #2: Dynamic Baseline Stacked Column or Bar Chart

You might like this solution if you want to keep the stacked chart, or don’t want to freak out your audience with something drastically different than they are used to seeing.  I call this one the dynamic baseline stack chart because it allows you to quickly choose the series to display at the bottom using a drop-down menu.  It is pretty easy to implement.  NO macros or VBA are required.  Just two different formulas and a drop-down form control.  The workbook that contains the form is available for free download below.  Instructions on how to create this chart are included in the file.

Dynamic Stacked Chart - Alternative 2 GIF

When the West region is at the top of the column stack it is difficult to see any trends.  But when the West is moved to the baseline (bottom) series, we see that there is a steady decline in the last half of the year.

There are also some correlating trends between South and West in the second half of the year.  This is easier to see when we move East to the top of the stack.  This might be another way to present your data depending on the message you want to convey to the readers.

Dynamic Baseline - West Example

Alternative #3: ???

I’d love to hear about some alternatives from you.  Make it as simple or complex as you like, there’s no right or wrong answer.  Just remember that the goal is to clearly and quickly tell the story to the reader.

Thanks!

Download

Dynamic Baseline Stacked Column Chart.xls (130.6 KB)

Additional Resources

If you’re looking to learn more about charting, I highly recommend the dashboard course from My Online Training Hub.  I have taken this course and it really helped me improve my charting skills.  Checkout my video review of the course to learn more about it.

8

Comparative Distribution Chart – Histogram or Box Plot Alternative

It can be difficult to create visualizations that compare one segment against an entire population of data while displaying the distribution of the entire population.  In this post I’m going to explain how to create the following chart in Excel.  

Comparative Distribution XY Scatter Chart

In this case we want to see pricing distribution for several products by customer segment.  So the data values are average price, and the categories are the products and customer segments.  But this same technique could be used for any combination of data value and categories; sales by product and region, headcount by department and country, etc.

The Challenge

I was recently doing analysis on product pricing data and the goal was to determine how one customer segment was performing against all the rest.  How does the the average price of each product in Segment 1 compare to the rest?  Like all good charting or data visualization projects, it took many iterations to come up with a chart that clearly communicated the story without too much explanation.  

Alternative 1: Box Plot or Quartile Plot

I first started with the box plot or quartile plot.  This is a great way to see the distribution of your data and compare it to other segments or categories.  The major issue I had with the box plot is that not everyone understands it.  So the use of a box plot depends on your audience.  If the audience is familiar then it is a great solution.  However, trying to explain it can be time consuming and not worth the effort.

Box Plot or Quartile Plot Product Comparison

Box plots also work well if you have a large number of segments/categories.  In the comparative distribution chart we are only looking at 5 different customer segments.  If you had hundreds or thousands of segments, then the box plot is probably a better solution.  I will explain how I created it in a separate post.  If you want a hint, it’s actually a line chart turned on its side.

Alternative 2: Histograms

Histograms are a good alternative for a single category, but comparing multiple categories doesn’t really work.  You could combine several histograms into a panel chart, but it is hard to identify trends between categories.

Histogram Panel Chart

How to Create the Comparative Distribution Chart

 

There are two files you can download below that will help guide you through creating this type of chart.  

  • The “Comparative Distribution Chart Guide.xls” file contains a detailed step-by-step guide.  
  • The “Comparative Distribution XY Chart.crtx” file is a Chart Template file that you can use to change the chart type to resemble the comparative distribution chart.  This will save you a lot of time in formatting the chart.

Step 1 

Your original data should look similar to the format below, with products in each row and columns for each segment.  Using a pivot table to summarize your raw data would be an easy way to get the data in this format.  

Comparative Distribution - Original Data

Once you have the data table, then you need to add a few columns that will be used to plot the points in the XY Scatter chart.  I’ve added cell notes in the guide file that give more detail on the calculations in each column. Comparative Distribution - Range Bar Table

Step 2

Create the XY Scatter chart and add all the data series.  It’s best to select a blank cell and then insert the “Scatter with Only Markers” chart type.  Then add each data series individually.  Excel has a tough time trying to automatically figure out the X and Y values for each series if you try to select the whole table and create the chart.  So it’s best to add each series one-by-one.

Comparative Distribution Chart - Step 2

Note: You can skip steps 3 and 4 below by applying the Comparative Distribution XY Chart template.  This will automatically do all the formatting for you.

Step 3

Now that you have all the series plotted on the chart, you need to format the marker options and line colors/styles for each series.  You’ll want each series to have the same marker style and color except for the series you are comparing.  In this case we want Segment 1 to have blue circle markers, and all other segments to be gray.  The Range Bar series is the light gray background bar that shows the range from min to max for each product.  For this series, set the markers to None, and change the line style width to 8.5pt.  This will create a thick line in the background.  You may also have to rearrange the order of your series if the background bar is on top of the other points.

Comparative Distribution Chart - Step 3

Step 4

The chart axes need to be changed so the data points are plotted between the horizontal grid lines.  The vertical axis needs to be changed by starting the minimum axis at 0.5 and changing the major unit to 1.0 on the vertical axis.

You can also change the major units on the horizontal axis to reduce the clutter.  We really only need to see the min and max values and maybe a few points in between to give some scale to the chart.

Comparative Distribution Chart - Step 4

Step 5

Add labels for the product and Segment 1 price.  The fastest and easiest way to do this is by using the XY Chart Labels add-in.  It’s available for free download and very easy to use.

http://www.appspro.com/Utilities/ChartLabeler.htm

XY Chart Labeler - Add Labels

Step 6

Finally, put some finishing touches on your chart to make it look presentable.  Tuck in its shirt and comb its hair. 🙂  And basically remove all the unnecessary chart junk that is not needed to tell the story.  We are trying to clearly show how Segment 1 compares to the other segments across all product lines.  

Comparative Distribution Chart - Step 6

Pros and Cons

Pros

The comparative distribution chart combines a little bit of both the box plot and simple histogram.  With the added bonuses of being easy to explain, and allowing for comparison of one data point against the whole data set.  It’s use will depend what trends or messages the chart clearly conveys to the reader.  In this case the Segment 1 prices are lower than the others for almost every product.  That would be a clear indication that Segment 1 has some defining characteristics that create this behavior.  Possibly, Segment 1 customers always use coupons that other segments don’t have access to.  

Cons

This chart is best for small number of segments.  If we had 50 customer segments instead of 5, then it would be difficult to see the distribution of all the data points in the range for each product.  A box plot would be better suited for this.

Download

Comparative Distribution Chart Guide.xls (233.0 KB)

Comparative Distribution XY Chart Template.crtx (5.5 KB)

This model could be further enhanced by adding a drop-down to select the segment you want to compare to the others.  I’m sure you will find many possibilities for modifying it.

Please let me know if you have any questions.  I’d like to hear how you could use this or improve on it.  Thanks!

77

Zoom on Excel Charts

When you have a dashboard with small panel charts it is nice to be able to zoom in on the charts to see the trends better.  The ‘Zoom_Chart’ macro included in the workbook allows you to add a zoom button (shape) over the top left corner of the chart to zoom in on the chart.  It’s available for free download below.  The macro actually resizes the chart to enlarge it, then returns it to it’s original size when the zoom button is pressed again.  See the animated screen capture below.

Zoom on Excel Charts

Zoom on Excel Charts with VBA Macro

You can control the zoom amount by changing the percentage values for Zoom Width and Zoom Height in cells P5:P6 on the worksheet.  These can also be hardcoded in the code if you don’t want the user to change the size of the zoom.

Video Overview & Basic Tutorial

Implementation

The video above contains a detailed tutorial on how to add the zoom buttons to your own workbook.  It’s really a matter of copying and pasting the macro (VBA code) and buttons into your workbook, and then assigning the macro to the buttons.  You should still be able to implement this even if you aren’t familiar with VBA or macros.  It is important to line up the buttons on the chart correctly and give each button a unique name.  So it’s best to watch the video to make sure you don’t miss anything.  The steps covered in the video are listed below.

The zoom feature works really well with dashboards and reports where your screen area is limited.  If you would like to learn more about charting and dashboards I highly recommend the dashboard course from My Online Training Hub.  Checkout my full video review of the course and learn how to become an Excel Superhero! 🙂

Installation Guide

  1. Open the Zoom on Charts workbook and your workbook.
  2. Open the VBA Editor and drag & drop the code module into your workbook.
  3. Save your file as a macro enabled workbook.
  4. Copy the zoom button into your workbook.
  5. Place the zoom button on the top left corner of a chart.
  6. Assign the macro to the button.
  7. Change the zoom references in the code.
  8. Duplicate the zoom button.
  9. Give the button a unique name.

Enhancements

The VBA can be further enhanced to add data labels, legends, axis labels, and any additional chart components when the zoom in button is pressed.  Please leave a comment with some of the enhancements you made, or would like to see.

Download

 

Zoom on Excel Charts.xls (82.9 KB)

Zoom on Excel Charts - Bottom Right.xls (84.5 KB)

Zoom On Excel Charts - Top Right.xls (85.0 KB)

The “Bottom Right” file contains modified code that allows you to place the zoom button in the bottom-right corner of the chart.  The chart zooms from the bottom-right and expands up and to the left.  Zooming from the top-left or bottom-right are the only two options for button placement with this code.

The “Top Right” file contains code that allows you to place the zoom button in the top-right corner of the chart.  This option can cause problems if the chart is too close to column A and tries to expand beyond the left side of the worksheet.  The location of the chart will move and the chart will need to be manually resized and moved back to align with the zoom button.

Questions

Please leave a comment below with any questions or modifications you have made.