|
|
By Debra Dalgleish, on March 5th, 2013 If you’re building Excel reports for other people to use, you can add a few interactive chart features, to let people customize the reports.
In this example, there is a check box beside each region name, in the sales summary table.
If you add a check mark, that region’s data is shown in the chart. If you clear the check mark, the region’s data disappears from the chart.
This is based on a technique that I learned from Jon Peltier, who creates amazing Excel charting utilities.
No Programming Required
There’s no programming required for this technique – the chart
Continue reading Click to Show or Hide Excel Chart Data
By Debra Dalgleish, on November 20th, 2012 Do you use the sparklines that were introduced in Excel 2010? Last week, I was building a dashboard, and wanted to show sparklines for expenses and revenue.
It didn't go smoothly at first, but I finally got things working, and then I ran into another hurdle!
Here's what went wrong, and how I fixed things.
Create a Sparkline Group
In this example, I had expenses and revenue in alternating columns, and I wanted expense sparklines in cells C4:C14. So, I selected those cells, and on the Ribbon's Insert tab, click the Line command in Sparklines.
In the Create Sparklines
Continue reading Show Excel Sparklines for Hidden Data
By Debra Dalgleish, on November 13th, 2012 It's week two in the free online Infographics and data visualization course, led by Alberto Cairo, and I'm working on this week's assignment.
The discussion this week is about a New York Times graphic that shows the number of times that words were used at national conventions. The words are shown in blue and red (pink?) bubbles, representing the party colours, and you can add other words to the graphic, to see how they fared.
If you click on a circle, the selected word is shown in context, below the graphic, in various speeches.
Improvements to the
Continue reading Compare Word Counts in Excel Chart
By Debra Dalgleish, on November 6th, 2012 Recently, I enrolled in an online Infographics and data visualization course, and the classes started last week.
The instructor is Alberto Cairo, who wrote The Functional Art: An Introduction to Information Graphics and Visualization.
He uploaded the first two chapters of his book for us to read during week one, and I really enjoyed it. There was some history, some theory, and plenty of graphics, to illustrate the text.
Improve the Infographic
One of the assignments this week was to suggest improvements to an infographic on Social Web Involvement. There are 16 countries in the infographic, overlaid on a
Continue reading Link Pivot Chart Title to Report Filter
By Debra Dalgleish, on October 25th, 2012 When you create a chart in Excel 2010, you can select a chart type on the Ribbon's Insert tab.
For some charts, instead of selecting one type, you might like to combine two different chart types, like the line-column chart shown below.
The Old Chart Wizard
In Excel 2003 and earlier versions, there were combination chart types available in the Chart Wizard. You could click on one of those, to quickly create your combination chart.
In Excel 2010 and Excel 2007, these combination charts, such as Line-Column and Line-Column on 2 Axes, aren't available in
Continue reading Create a Line Column Chart on 2 Axes in Excel 2010
By Debra Dalgleish, on September 18th, 2012 Today we'll create an in-cell chart that shows the latest price for a stock, compared to its low and high prices. There is a sample file available for download, and a video that shows the steps.
Simple Dot Plot Chart
You can create a simple chart on a worksheet, by using the REPT function. Most in-cell dot plot charts are for a standard scale of 0-100 points, such as test scores. In the screen shot below, the REPT function repeats the space character based on the score in column C, and displays the last character as a lower case "O",
Continue reading Excel Dot Plot Chart for Stock Prices
By Debra Dalgleish, on August 23rd, 2012 To show a concise, clear summary of data for several departments or cities, you can create a panel chart in Excel. It shows all the data in a single chart, with vertical lines separating the groups.
My chart shows sales for bars and cookies, in four cities, over the first 7 months of the current year.
I learned this technique from Jon Peltier's website, where he also sells a Panel Chart Utility, that creates dot plot and bar panel charts.
Panel Chart Steps
The instructions for making a panel chart look long and complicated, and I've avoided learning
Continue reading How to Create a Panel Chart in Excel
By Debra Dalgleish, on August 2nd, 2012 There's sample data on my Contextures website, and it's useful for testing formulas and pivot tables in Excel.
On that page, there's a link to a downloadable Excel file, with the same sample data. Or, you could just copy and paste the data into a workbook.
Now With Interaction
Now, thanks to Microsoft's Excel Services, you can play with the data in Excel Interactive View. Just click the button above the sample data table, and the interactive view will open up.
Note: I've added the same feature to my Excel Book List page.
At the left, the Filters
Continue reading Interactive Excel Sample Data
By Debra Dalgleish, on May 10th, 2012 You can add a chart in Excel, based on worksheet data, like this sales chart example.
But, if you filter the data, and rows are hidden, that data also disappears from the chart. You might like that feature, and not mind that the chart changes. In the example shown below, only the West region's data is visible on the sheet and in the chart.
However, there are some situations when you want the chart to show the data, even if the data is hidden. In the next example, columns A:C are hidden, so that they don't distract from
Continue reading Show Data From Hidden Rows in Excel Chart
By Debra Dalgleish, on April 10th, 2012 In this Excel dashboard example, you can select "Chart" or "Chart Data" from a drop down list. Magically, with no macros in the workbook, the selected item appears on the worksheet.
With this technique, you can store your data and chart on a hidden sheet in the workbook, where no one can mess with the numbers. (Not that anyone would!)
How It Works
The drop down list is created with data validation. To show the selected item, named ranges are added to the workbook, and a linked picture shows one of those ranges.
To download the sample file, you
Continue reading Show Excel Chart or Data in Dashboard With No Macros
|
|
Recent Comments