Ensure a Single Check Box Selection
Check boxes are supposed to be used to allow multiple selections. But since they are now so easy to insert, you might want to use them to allow a single selection. Here’s one way to do that.
(more…)Different Colours for Positive and Negatives in an Excel Column Chart
A recent webinar query requested different colours for the columns if they were positive or negative. Here is one technique to achieve that.
(more…)Excel Pie in Pie Workaround
I did a post on the Pie in Pie chart a few weeks back, but it can be a bit clunky to use. I thought about using a few dynamic array functions to make it a bit more interactive and flexible. The source data is in a formatted table called Table1. In a separate sheet there are two input cells. One for the state to report on and the other to enter the number of “main” segments to use. The…
Pie Chart Other Segment in Excel
Pie charts often get a bad rap in data visualisation circles (sorry) but here is a technique to avoid those multiple small segments.
(more…)Adding a Dotted Line for the Budget in Excel
When you are charting the whole year in a line chart you might want to show the actuals up to a point and then the budget from then on. The budget could also be a dotted line. Here’s a way to do that.
(more…)Macro to Set Sparkline Axis to Zero
When creating sparkline charts in Excel one of the problems is that the vertical axis doesn’t always start at zero. It is a manual process to reset the vertical access to zero and a macro can make it much quicker and easier.
(more…)Using Colours Effectively in Excel Charts
When I reviewed the charts in the CPA Australia 2024 Annual Report I found another series of charts that could be improved.
(more…)Column vs Bar Chart in Excel
As I was reading the CPA Australia 2024 Annual Report I was interested in the presentation of charts (graphs). A lot of money had been spent on the presentation of the report. There were lots of high-quality photos and other images. I was disappointed with much of the data visualisation.
(more…)Excel Objects and Ctrl + A
Selecting all objects on a sheet in Excel is easy with Ctrl + A. Objects are things like textboxes, images, charts and slicers. If you select one object and press Ctrl + A all the objects on the sheet will be selected. When you select the object make sure the line around it is not dotted. Some objects like text boxes have a dotted line when you are editing the object. Ctrl key After you have selected all the objects…
Convert Line Chart to Dynamic Step Chart
Excel and Chart legend and MVP Jon Peltier has blogged about how you can convert a line chart into a step chart. I have used the new TRIMRANGE functionality and range names to make a dynamic solution.
(more…)Highlighting a Column in a Chart in Excel
I was reading the book Data Story by Nancy Duarte recently and in it she mentions highlighting a column to draw focus to it. Here’s one way to do that in Excel.
(more…)Sparkline Horizontal Axis Technique
Excel Sparkline charts don’t have a horizontal axis. Here is a technique that creates one in the cell above or below the Sparkline. This works best for the Column Sparkline.
(more…)Excel Dashboards #1 Guidelines & Techniques
In this recording of a live webinar I ran in March 2023 you will learn about guidelines and technique for building dashboards in Excel. Use the button below the video to download the materials. https://vimeo.com/807771690 Download Materials This is the first session is a series of five webinars on Excel dashboards. The other four sessions are paid sessions. In this free session we will focus on chart and dashboard guidelines plus some techniques used to create better dashboards. We will…
Sparklines and Dates in Excel
A Sparkline chart is a single data series chart (graph) that exists in a cell. Usually, you only need to select one range to create a Sparkline. Sometimes you may need to handle missing dates.
(more…)Sparklines and Merged Cells in Excel
I am not a fan of Merged Cells but there may be a case to use them when creating a larger Sparkline chart.
(more…)One Minute to Excel #29 – Nine Names in a Minute
In this short video I demonstrate how to create range names quickly based on labels. Range names are a powerful formula feature. I also demonstrate their use. https://vimeo.com/739173957
One Minute to Excel #28 – Eight More Charts in a Minute
OK in the last video I cheated and used Sparkline charts to create 8 charts in a minute. This time I set myself a real challenge to create 8 real charts in a minute. Its close - check out the video and learn a few useful techniques. https://vimeo.com/728813019
One Minute to Excel #27 – Eight Charts in a Minute
Let's see if I can create 8 charts in a minute - one for each state and territory in Australia. I cheat a little bit and use Sparkline charts. If you haven't seen Sparklines before check out the video. https://vimeo.com/728808839
Missing Chart Data in Excel
The default setting for charts in Excel is to hide the data on the chart if it is hidden on the sheet. I forgot that recently when I created a few charts using a workings area to hold the chart data. I later hid the workings with column grouping. Oops – when you hide the data in the charts go blank.
(more…)Dashboard Webinar feedback
It is always nice to receive feedback - this was for the recording of the Dashboard #1 session.
Percentage Area Chart in Excel
You can use a pie chart to display a percentage, but it wastes a lot of space. An alternative that takes up less space is an area chart.
(more…)Excel Chart Title Hack
I learned something new recently watching a recorded webinar by Jon Peltier. That’s not unusual, he is a charting legend.
(more…)
Free Webinar Recording – Excel Chart Tips and Tricks
In November 2019 I re-ran my Excel Chart Tip sand Tricks session. The detailed pdf manual and example file can be downloaded using the button below. Content listed below the video. Download Materials CPD note - if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances. https://vimeo.com/374068298 This webinar is focused on showing you how to create and modify charts in Excel…
Top 5 reports with PivotTable and PivotCharts
When you create a top 5 sorted report with a PivotTable, the Pivot Chart isn’t always what you expect, there is an easy solution. (more…)
Free Webinar Recording – Interactive Excel Chart
In September 2019 I re-ran my very first webinar that was based on an August 2012 INTHEBLACK feature article. I have provided a detailed pdf manual and example files which you can download at the link below. This session covers using controls with charts to make the user experience easier tips for using controls on spreadsheets flexible formula techniques to make reports more dynamic a technique to stop figures plotting on charts using range names with controls and reports general…
Free Excel Webinar Recording – Financial Functions Part 1
In June 2019 I explained and demonstrated a number of Excel's financial functions - see below for more details. CPD note - if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances. Download Webinar Materials https://vimeo.com/341487244 These functions take three or more arguments, but there are a few things you need to know to use them correctly. Most involve loans or calculations…
Free Excel Webinar Recording – Dashboard #1 Guildelines & Techniques
In May 2019 I examined and demonstrated some guidelines and techniques for creating charts for dashboards. CPD note - if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances. Download Webinar Materials https://vimeo.com/334821126 In this session I focused on chart and dashboard guidelines plus some techniques used to create small charts for dashboards. I also looked at creating a bullet chart, which…
Switching Rows and Columns in a PivotChart
Switching between rows and columns in a normal chart is easy via a button on the Design tab. But how do you do it with a PivotChart? (more…)
Moving Images in Excel
When you are moving images, charts or other objects that float above the grid you can use the Shift key to make it easier.
(more…)