Using the IFS() and SWITCH() Functions to test for more than one condition in Excel 2016
Learn about 2 of the exciting new functions released as part of the Excel 2016 January update
Using Ranking Functions in Excel
Master the available options in Excel for ranking you data, including the updated Rank.Eq functions.
Add Labels to Outliers in Excel Scatter Charts
Scatter charts are a great way to present the relationship between two data points
In my example, I’m using a scatter charts to monitor student test results – I want to compare their latest test score against how that score has changed from their last test.
Using “Transparent” option in SSRS, and prevent the ‘Not a Valid BackgroundColor’ Error
In SSRS, if you’re building an expression to manage, for example, the background colour of a cell / shape, it’s easy to fall into the ‘Transparent Trap’ When building an expression, it’s easy to use the expression builder to add constants and built-in values. This is particularly true of the colour pallet, given you
Using Excel to Calculate Repayments & Interest using PMT, IPMT & PPMT Functions
If you’re borrowing or saving money, and want to compare rates, perhaps perform some What If analysis, there are built in function in Excel which can quickly help you PMT() The main one is PMT() This function takes a value, a rate and a ‘number of periods’ value and gives you the repayment. There are also
Showing a Picture Based on the Value of another Cell in Excel
In this walk-through ,I will show you how to use Excel to make a specific image show depending on a cells value, or by having a drop-down list
Creating Waterfall Charts in Excel [2007,2010,2013]
Update: Feb 2016 – If you’re using Excel 2016, be sure to check out the new Waterfall Chart Type, which makes waterfall charts a breeze. Learn more here
Waterfall Charts are another one of those charts that’s much harder to put together than it should be, particularly as they’re a great way to understand the sequential impact of positive & negative values to a total.
That being said, they’re really not as bad as to put together as they’re sometime perceived to be. As with most of the chart techniques I’ve demonstrated, it’s really about tricking excel to show the bits of data you want it to show.
We’re going to go through the process to create the below chart – a simple income & expenditure chart for a shop.
I’m using excel 2013, but the approach is almost identical for previous versions of Excel
Highlighting Duplicate Values in Excel – [Excel 2007, 2010, 2013]
One of the most common questions I hear, is around how to manage duplicate values in Excel.
I’ve already covered off how to remove duplicate values in this post.
I personally feel the ability to remove and highlight duplicates should grouped together, but whilst removing duplicates forms part its own ‘feature’, highlighting duplicates forms part of the wider Conditional Formatting functionality.
Since Excel 2007, highlighting duplicates has been very straightforward, with a pre-defined option to enabling it. So it’s this approach I will cover in this post. It can still be achieved in 2003 using a simple formula, but hopefully by now everyone is using 2007+
Using SUMPRODUCT to create a Conditional Weighted Average in Excel
Follow this article to learn how to use this resourceful function to create a Conditional Weighted Average in Excel
Using Hlookups to bring back results in Excel
Today I want to do a quick page on using Hlookups in Excel. Hlookups work in much the same way as a Vlookup (H being Horiztonal Lookup rather than V being Vertical Lookup) but often people forget about it or afraid to use it, instead favouring a more complex approach to the problem. For
Most Read Articles
