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.
Tag Archives: charts
Percentage Area Chart in Excel
Conditional Format technique
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.
Excel Chart Title Hack
Linking to a range
I learned something new recently watching a recorded webinar by Jon Peltier. That’s not unusual, he is a charting legend.
Top 5 reports with PivotTable and PivotCharts
Switch the order of the Axis
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.
Free Webinar Recording – Interactive Excel Chart
Feedback score 93.7% based on 58 responses
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 charting tips
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.
Free Excel Webinar Recording – Financial Functions Part 2
Feedback score 87%
In July 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.
In July 2019 following on from the response and feedback from my June webinar, I covered more financial functions. These are more related to comparing and analysing cash flows. I have also included a couple of requested schedules.
This session will cover the following functions
- NPV – Net Present Value of regular cashflows (learn to trick to using it correctly)
- XNPV – Net Present Value of irregular cashflows
- IRR – Internal Rate of Return of regular periodic cashflows
- XIRR – Internal Rate of Return of irregular periodic cashflows
- Discounted Payback period schedule
- Flexible Loan schedule – handles lump sum payments
As with all my sessions, I will throw in a few other shortcuts along the way.
Related Posts
Free Excel Webinar Recording – Financial Functions Part 1
Feedback score 93%
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.
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 associated with the time value of money.
This session covered the following six functions
- PV – Present Value of future regular cash outflow
- PMT – periodic loan repayment calculation
- CUMIPMT – cumulative interest – great for loan schedules
- RATE – interest rate
- FV – future value of regular investment
- NPER – number of periods
The session finishes with a loan model that calculates the “missing” value for a loan scenario based on two out of three inputs. The IFERROR function is also discussed.
Free Excel Webinar Recording – Dashboard #1 Guildelines & Techniques
Feedback score 92%
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.
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 is an alternative to gauge, speedo or dial chart.
The session covered
- general chart guidelines
- questions to ask yourself to develop better charts
- best charts to use
- charts and formats to avoid
- using text boxes on dashboards
- how to create small charts
- chart templates
- lining up and re-sizing charts
- useful number formats for dashboard charts
- using the #N/A error with charts
- how to create a bullet chart
As always I shared a few other tips.
Switching Rows and Columns in a PivotChart
Finding the hidden option
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?
Free Excel Webinar Recording – Excel Yourself 2018
Feedback score 89%
In December 2018 I reviewed four of my articles from the INTHEBLACK magazine from 2018. Download the materials using the button below and watch the video.
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.
Includes extra content not included in the magazine.
This year’s smorgasbord webinar includes
- how to handle validations
- dynamically highlighting the current row in a table (uses a macro)
- centralising logic – how and why to do it – alternatives to the IF function
- creating a slope chart (includes a macro to speed up the process)
As always there will be a few more tips and tricks shared in the session.
Creating a Timeline Chart in Excel 2016
Error Bars plus a new feature
Timeline charts are an effective way to display events over time. You can use a new Excel 2016 feature to easily create a timeline chart.
Horizontal or Vertical Progress Bar in Excel
Conditional Format or Sparkline take your pick
In a recent webinar on conditional formatting I was demonstrating how to create a horizontal progress bar using conditional formats when someone asked an interesting question about creating a vertical progress bar. It is possible and in this blog post I will explain both techniques.
Adding a Vertical Dotted Line to an Excel Line Chart
An error bar solution
When plotting Actuals and Forecasts on a single line chart you may want to use a vertical dotted line to identify where the Actuals finish and the Forecasts begin.
An Easier Step Chart
A Line chart solution
Here’s another way to create a Step Chart. This one is quicker. I wrote previously about using a scatter plot and error bars but it required a lot of chart changes. This one hacks a line chart and requires no chart changes.
TreeMap a new chart in Excel 2016
A square pie chart
The TreeMap is like a square pie chart, but it has the added ability to show a hierarchy.
Step Charts in Excel
Hacking the error bars
The Step chart is not a standard Excel chart but it is a useful way to display values over time. You have probably seen a step chart but you may not have known what it was called. It sort of looks like the city skyline or something you would create on an Etch-a-Sketch.
Pie Charts and Negatives – an Alternative
A waterfall solution
Pie charts have a lot of drawbacks and limitations. One major limitation is they can’t handle negatives. One of Excel’s new charts can help out.
Naming Charts in Excel
Two techniques
You may have noticed that Excel gives every chart a unique number when it creates the chart. It is displayed in the Name Box in the left corner above the grid. You have the ability to change that name and make it more descriptive.
Stop Charts Resizing
Works with other graphic objects like text boxes
We’ve all been there, our charts are looking just right and then some one inserts a column or changes the column width and throws out all our perfectly proportioned charts.
Hide and Seek with Excel Charts
Hiding rows and columns
Charts have a behaviour that many people don’t realise. That behaviour can also be turned off. If you hide a row or column in the data range used by a chart, the values will also be hidden on the chart.
Chart Suggestions
When less is more
Just because you can, doesn’t mean you should. This applies to many thing in Excel and especially to charts. With charts the “less is more” philosophy works well. Have a look at the four charts in the image below.
Power BI – A Chart
The ultimate goal
Dashboard Charts are the ultimate goal of most Power BI reports, so let’s dive in.
Show Horizontal Axis Entries Below the Chart
Great for displaying negatives in a line or column chart
I learned about a chart Axis option in Excel during a recent webinar – thanks to one of the attendees. You can show the Axis entries below the chart – this is handy for column charts that display negatives.
When a Bar Chart is Better
Before and after charts
I was reading a magazine last week and a chart caught my eye. I thought I could improve it. I recreated it in Excel – its close to the original – see below – I didn’t quite match the column colour.
Excel Variance Analysis
An alternative charting technique
It is common in accounting to compare numbers. Either Actuals vs Budget or this year vs last year. There are different charts that people use for this comparison. How about plotting the variance instead?
Horizontal bullet chart in Excel
Excel Yourself article
Check out my follow up article and VIDEO on the ITBDigital website on how to convert a vertical bullet chart into a horizontal one.
Click here for full article and video.
For the original bullet chart post click here
These techniques are based on ones in the great book
Excel 2007 Dashboards and Reports For Dummies by Michael Alexander
Excel Bullet Charts [Video]
Excel Yourself Article
Check out my July 2014 article on Bullet charts on the CPA Australia ITBDigital website – click here to see the article. The video is below.
Bullet charts were developed by Stephen Few – see his pdf on bullet charts click here.
The technique is based on one used by Michael Alexander in his great book Excel 2007 Dashboards and Reports for Dummies by Wiley.
Excel and Grouping
The term grouping in Excel has many different meanings, probably more than any other term in Excel.
Re-Sizing Charts In Excel
How to get identically sized charts
When building dashboards in Excel you frequently need to have multiple charts that are exactly the same size. Luckily Excel makes this reasonably easy to achieve.
Slope Chart
How to show movement
This post is attempting to replicate a slope chart.
I took a standard Line chart as seen in the top of this image. And then used the Design Tab option Switch Row/Column to create the slope chart at the bottom of the image.