Learn Excel from Bill Jelen with this daily 2 minute video podcast.
Mike from Arizona asks how to create a revenue bridge chart. This is similar to a waterfall chart, except some of the points are negative. Episode 1093 will show you how to create this chart.
Labels: charting
posted by Bill at 9:00 AM
Craig added an arrow to a line chart. Every time the data changes, the arrow needs to be repositioned. Episode 1092 shows how to add a dynamic XY series to redraw the arrow.
Labels: charting
posted by Bill at 9:00 AM
Jeff from Akron asks how to create a column chart where two series are stacked, but a third series is clustered. Episode 1091 shows you how.
Labels: charting
posted by Bill at 9:00 AM
Sparklines are tiny, word-sized charts that can appear in a cell. Excel 2010 makes it easy to create sparklines. In Episode 1070, Mike & Bill show you several examples in Excel 2010.
posted by Bill at 9:00 AM
In Episode 1038 - a better way to create the multiple scatter charts from Episode 1031 and 1032. Thanks to everyone who wrote in with this better way to set up the data.
posted by Bill at 9:00 AM
Tom and Thor send in better ways to force the area chart under a portion of a line chart to drop to zero. Episode 1036 shows you how.
Labels: charting
posted by Bill at 9:00 AM
Yesterday's podcast showed a tedious method for creating a chart with 5 XY series. Today, Episode 1032 shows a method for streamlining the creation of this chart.
Labels: charting
posted by Bill at 9:00 AM
Pierre wants to create a chart with multiple XY series on the same chart. While this is possible, it is not extremely easy. Episode 1031 shows you one method for doing this.
Labels: charting
posted by Bill at 9:00 AM
Mario sends in an UGLY solution to episode 1016. Episode 1026 will show you how to highlight one segment of a line chart in Excel 2003. This episode wins an award for the ugliest workaround.
Labels: charting
posted by Bill at 9:00 AM
How to highlight part of a line chart using a second area chart series. This trick works in Excel 2007, but not 2003. Episode 1016 shows you how.
Labels: charting
posted by Bill at 9:00 AM
Marcelo asks how to replicate all of the formatting on an existing chart. In Excel 2003, this was Chart, Custom Types, User Defined, Add. In Episode 1008, we take a look at the equivalent method in Excel 2007.
Labels: charting
posted by Bill at 9:00 AM
Glenn has a series of line charts showing monthly data for 2009. Most charts work fine, but the total chart shows the line dropping to zero for future months. Episode 1004 shows how to solve this problem, then how to solve the problem from the solution to the original problem!
Labels: charting
posted by Bill at 9:00 AM
In Episode 994, another solution to Ally's problem of how to make a chart expand when you type new data in the worksheet. Today's method shows an easier way to set up a dynamic chart range in Excel 2003 and newer.
posted by Bill at 9:00 AM
Ally asks how to have a chart automatically extend as you type new data. The classic solution is to use a pair of dynamic named ranges using OFFSET. While this can be painful, Episode 993 shows how to set it up. Be sure to watch Episode 994 tomorrow for an easier way in Excel 2003-2007.
Labels: charting, Dynamic, Dynamic Range, Offset
posted by Bill at 9:00 AM
While creating a scatter chart, Don is trying to change the marker color in Excel 2007. This was relatively easy to find in Excel 2003. In Excel 2007, the equivalent dialog was split into several panes, but there is a much easier way to go. Episode 988 discusses the three charting tabs and shows you the solution.
posted by Bill at 9:00 AM
Another Dueling Excel podcast: How to best represent data on a chart. Mike and Bill offer their favorite solutions in Episode 985.
Labels: charting
posted by Bill at 9:00 AM
You need to chart two different orders of magnitude in a chart. I always end up creating a 2-axis combo chart, with a column series and a line series. Today in Episode 979, Bill writes in with a cool way to solve this problem with both series shown as columns.
Labels: charting
posted by Bill at 9:00 AM
Changing the chart background in Excel 2003. Episode 918 shows you how.
Labels: charting
posted by Bill at 9:00 AM
Add a picture as a background for a chart in Excel 2007. Episode 917 shows you how.
Labels: charting, Excel 2007
posted by Bill at 9:00 AM
While I've shown how to drag and drop data on a chart, that method doesn't work if your chart is on a different worksheet than the data. Today in Episode 897, a trick to add new data to an existing chart.
Labels: charting
posted by Bill at 9:00 AM
Chuck sends in a great question about charts that use a time-series along the x-axis.
Labels: charting, time series
posted by Bill at 9:00 AM
Tom from Cincinnati asks how to create a chart which can show either monthly or quarterly data. Episode 881 shows how to manually set up group and outline symbols to achieve the result.
posted by Bill at 9:00 AM
Episodes 801 and 815 suggested many ways for getting the total of 3 series in a stacked column chart to appear above the column. Both Ute and John wrote in with yet another method, a method that is probably the easiest. Episode 845 will show you how.
Labels: charting, Excel 2007
posted by Bill at 9:00 AM
This was an intriguing question...how can you set up a pie chart in Excel that appears to divide a dollar coin into wedges?
Labels: charting
posted by Bill at 9:00 AM
I was pretty happy with my tricky chart back in Episode 801, but couldn't find a way to be tricky in Excel 2007. Today, both Eddie and Ricardo send in brilliant ideas to get the sum of three stacked columns to appear on top of the stack of columns. Episode 815 shows you how.
Labels: charting, Excel 2007
posted by Bill at 9:00 AM
Johann from Mexico City sends in a cool tip for creating charts that seem like they use conditional formatting to color the columns. Episode 800 will show you how.
Labels: charting
posted by Bill at 9:00 AM
Back in Episode 767, the pod dealt with the problem of using =IF(ISBLANK(A2),"",A2) to hide zero values in the results table. Today, Rene and Denis both write in with a far simpler solution. Episode 783 shows you how.
Labels: charting, Interactive Chart
posted by Bill at 9:00 AM
One of my favorite tricks is to fill a textbox with the result of a formula in the worksheet. When you try this as the title in an embedded chart, you need to change the formula to point back to the original worksheet. Episode 778 shows you how.
posted by Bill at 9:00 AM
Lucy asks how to make a chart interactively change from red to yellow to green based on an external selection. In today's Episode 776, we take a look at the basics of setting up such a chart. Tomorrow's episode will take a look at the Options button on the Form toolbar to enhance the chart.
Labels: charting, Interactive Chart
posted by Bill at 9:00 AM
Chart a moving average in Excel. Episode 770 will show you how.
Labels: charting, forecasting, moving average, trendline
posted by Bill at 9:00 AM
Lorin asks if there is anything new in Excel 2007 charts. While Microsoft rewrote the charting engine in Office 2007, you still have the same basic chart types. However, there are new data visualization tools in conditional formatting that allow you to add bar charts to every cell. Episode 737 discusses these new features.
Labels: charting, Excel 2007
posted by Bill at 9:00 AM
In a second attempt to solve Odelia's question from yesterday, I use a bar of pie chart to automatically move all of the pie wedges less than 4% to a secondary chart. Episode 684 shows you how.
Labels: bar of pie chart, charting, charts
posted by Bill at 9:00 AM
Ute from Germany sends in a better way to show negative points in a column chart in a red color. His cool trick works for Excel 97-2003. Episode 674 will show you how.
Labels: accounting, business, charting, charts, Excel, MrExcel, spreadsheets, technology, tutorial
posted by Bill at 9:00 AM
When you create a chart from data that might have missing dates, you might want Excel to plot the missing dates along the horizontal axis. In Episode 591, we take a look at why Excel sometimes chooses to use a time scale and sometimes does not.
posted by Bill at 9:00 AM
Say that you need to show a rolling 6 month chart. Usually, I show how to drag the blue outline around the source data. However, in today’s podcast, Kirk offers an alternative tip – include all of the columns in your chart and hide/unhide the columns that you want to appear in your chart. Episode 590 shows you how.
posted by Bill at 9:00 AM
Our guest host today is Jon Peltier. Jon maintains an excellent set of charting examples at http://peltiertech.com/. In today’s podcast, Jon discusses the best way to organize your data for charting. Episode 469 shows you how.
Labels: charting
posted by Bill at 9:00 AM
Dan from Akron notes that when he inserts new columns in Excel, Excel is automatically extending the size of any charts that happen to span the new column. With most objects, you can right-click and indicate that you don’t want to move or size with cells. However, charts don’t seem to have the ability to change the resizing options. Episode 456 will show you the bizarre solution.
Labels: charting
posted by Bill at 9:00 AM
In a recent charting challenge, Jerome Sullivan sent in an interesting chart where the budget and actual numbers were actually plotted on top of each other. Episode 444 shows the non-intuitive steps required to create this type of chart.
posted by Bill at 9:00 AM
How to create a combination chart that includes both stacked columns and a line chart. Episode 442 shows you how.
posted by Bill at 9:00 AM
Sometimes you want a chart to show two different kinds of data - for example, revenue, and gross profit percentage. If you create such a chart, the size of the revenue bars makes it impossible to see the detail in the profit percentage bars. The trick is to plot one of the series along a secondary axis. Episode 373 shows you how.
posted by Bill at 9:00 AM
In Episode 349, learn a cool way to add new data to an existing chart. My book usually shows the drag and drop method to add data, but in Episode 349, you can learn how to drag the selection handle to add or remove data from a chart.
Labels: charting
posted by Bill at 9:00 AM
