Learn Excel from Bill Jelen with this daily 2 minute video podcast.
Wang asks how to change part of one data label to be red. Unfortunately, the label is then broken. A macro should solve this, but it does not. Episode 1147 discusses this problem.
posted by Bill at 9:00 AM
Roger from Columbus Indiana passes along a cool trick for formatting all of the text in a chart at once. Episode 1131 shows you how.
Labels: charts, Formatting, Text
posted by Bill at 9:00 AM
John asks how to take 10 years worth of stock data and convert it to a chart showing months across the bottom and a different line for each year. Episode 1107 shows you how.
Labels: charts, Pivot Table, stock
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
The data table option in a chart can have some annoyances. Today, a question (and a solution) from the Virginia Tech seminar. Episode 972 shows yet another cool use for the camera tool.
posted by Bill at 9:00 AM
Rick asks how to set up a chart with two different orders of magnitude. This was a built-in chart type in Excel 2003, but it is harder in Excel 2007. In today's Where Is it Wednesday edition, we will take a look at how to create a chart showing series with different orders of magnitude. Episode 888 shows you how.
Labels: charts
posted by Bill at 9:00 AM
Erika from Arkansas asks how to create a dual histogram. These charts are great for comparing the skew of answers between two categories such as male vs female. Episode 821 shows you how to create this chart.
posted by Bill at 9:00 AM
How to add a the total of the stacked columns at the top of the chart. This is obscure but possible in Excel 2003, but as Episode 801 shows, it is maddeningly impossible to do in Excel 2007.
Labels: charts, 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
Odelia is trying to create a pie chart with only the values greater than 4% of the total. Episode 683 shows one method for solving this problem.
Labels: 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
Rodney asks how Excel can make a clustered column chart. While it is not immediately obvious, it is relatively easy to do. Episode 666 shows you how.
Labels: charts
posted by Bill at 9:00 AM
Chris from Long Island asks how to conditionally format the negative items in a chart. Episode 656 shows two methods for handling this.
Labels: charts
posted by Bill at 9:00 AM
Steve asks if he can build an Excel worksheet where people can change numbers by dragging a graphical element. Episode 648 shows you how you can use a chart in previous versions of Excel to solve this problem.
Labels: charts
posted by Bill at 9:00 AM
Patrick wrote in to address my answer from podcast 641. In that podcast, I used a SUMIF to create a pie chart of hours spent by department. Patrick notes that early in the month, many of the departments have 0 values and would like those excluded from the chart. In Episode 643, I set up a series of formulas to sort the data and then a named range to exclude the zeroes from the chart.
Labels: charts
posted by Bill at 9:00 AM
Patrick asks how he can summarize his monthly time sheet to create a pie chart by department. Using the SUMIF function provides the step to make this relatively easy. Episode 641 shows you how.
Labels: charts
posted by Bill at 9:00 AM
Did you know that you can change numbers in the underlying worksheet by dragging points on a chart? This might be an interesting trick to allow someone to adjust their forecast projections. Episode 634 shows you how.
Labels: charts
posted by Bill at 9:00 AM
When you hide a row, Excel will remove that series from a chart. In Episode 609, I create a hideous chart, but then use an AutoFilter to create a cool ad-hoc charting tool that allows a sales manager to see results for any one sales rep.
Labels: charts
posted by Bill at 9:00 AM
To create a true histogram from frequency information, you need to make the columns in the chart touch each other. This was difficult to do in Excel 2003 but much easier in Excel 2007. Episode 606 shows you how.
Labels: charts
posted by Bill at 9:00 AM
Do you have a chart which is too confusing? Try replacing the one chart with four smaller charts, each chart focusing on one particular statistic. Episode 604 shows you how.
Labels: charts
posted by Bill at 9:00 AM
Need to convert a report or a chart to display in thousands or millions? Episode 555 shows two different methods to achieve this result.
Labels: charts, custom format, Formatting, Number Formats
posted by Bill at 9:00 AM
Peter sends in a question that relates back to Episode 444. In that podcast, I showed how to change the gap width in Excel 2003 to have two columns stacked on top of each other. Peter wonders how to do this in Excel 2007. This is a great opportunity to show off a change in Excel 2007’s charting engine. While Excel 2003 required you to open and close the Format dialog box repeatedly, you can now format everything on the chart from the new modeless Format dialog box in Excel 2007. Episode 538 shows you how!
posted by Bill at 9:00 AM
Bob from Dallas writes in with a question. Bob has created a chart in Excel and needs to copy the chart to a slide in PowerPoint. For those of you who never leave Excel, PowerPoint is a tiny little Add-In that Microsoft created to help Excellers when they need to make presentations. Although there are 7 ways to accomplish this task, Episode 492 takes a look at the best way to get your Excel charts on to a slide in PowerPoint.
Labels: charts
posted by Bill at 9:00 AM
Another popular way for people to lie with charts is to use a cone or pyramid chart. Episode 448 shows why your radar should go up anytime you see someone presenting data using these chart types.
Labels: charts
posted by Bill at 9:00 AM
Watch out when someone tips a pie chart backwards to show a 3-D perspective. They are either unintentionally or intentionally trying to misrepresent the data. Episode 446 shows you how.
Labels: charts
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
Create an Org Chart in Excel. The org chart tool is hidden behind an obscure icon on the drawing toolbar. The details of using the tool are a bit complicated. Episode 388 shows you how to create an org chart.
Labels: charts, drawing toolbar, Icons
posted by Bill at 9:00 AM
Create a target diagram in Excel, using the business diagramming tools on the Drawing Toolbar. Episode 385 shows you how.
Labels: charts, drawing toolbar
posted by Bill at 9:00 AM
If you need to create a chart where each series contains both small and large numbers, it will be difficult to see the detail for the smaller numbers. Allow the chart to show detail of both the small and large numbers by using a logarithmic scale. Episode 374 shows you how.
Labels: charts
posted by Bill at 9:00 AM
