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.
Tag Archives: dashboard
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.
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.
Related Posts
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.
Vote No for Gauge Charts
They take up too much space
Gauge charts are not a standard chart in Excel and are quite complex to create. Gauge charts should be avoided for dashboards.
Power BI – A Chart
The ultimate goal
Dashboard Charts are the ultimate goal of most Power BI reports, so let’s dive in.
Creating a Report in Power BI
Converting Data into Information
In the previous post we extracted the data from a CSV file. Now let’s create a report. It won’t be the greatest report as the data is pretty basic, but at least its a start.
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.
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.
How to Improve an Excel Line Chart [VIDEO]
Before and after improvements
Line charts are frequently used in Excel but their default settings leave a lot to be desired. See the transformation of a standard line chart to a simpler and easier to read line chart.
Flexible Dashboard Technique for reports
A Camera technique in Excel
It’s easy to move and re-size charts on a sheet. It’s a different story when you want to include small reports on a dashboard. Reports are affected by the existing row heights and column widths.
Creating Dynamic Text Box entries in Excel
Flexible text
Text boxes are flexible because you can place them anywhere on your spreadsheet.
You can link a text box to the contents of a cell.
New Sparkline Charts in Excel 2010
Keep it small and simple
Excel’s Sparkline Charts
Sparkline charts are a new feature in Excel 2010. Sparkline charts are small cell-sized charts that are designed for dashboard reports. The chart sits inside a single cell and its size changes with the size of the cell, both height and width.