Tag: Excel

Using Excel to Calculate Repayments & Interest using PMT, IPMT & PPMT Functions

April 10, 2015 0 comments Article Easy, Excel

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 Read More

Insert an Excel Spreadsheet into a Word or Powerpoint Documents [2010,2013]

March 27, 2015 0 comments Article Excel, Intermediate, Powerpoint, Word

Can you believe that with all the articles I’ve written to this site, I’ve yet to do one specific to Word? Which is ironic, given I write and publish most of the articles from Word.

There is of course a logical reason for that – it’s just not a tool I use as much as other applications.

And, you’ll be comforted to know, this article isn’t a complete departure from my more frequent Excel related articles!

(I’ve written the article based on using Word, but the exact same approach is valid for Powerpoint as well)

Embedding Excel worksheets in Word:< Read More

Showing a Picture Based on the Value of another Cell in Excel

March 24, 2015 11 comments Article Advanced, 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 Read More

Using arrows and other shapes as columns / bars in Charts in Excel

March 20, 2015 1 comment Article Easy, Excel, Microsoft Office

Nice quick one.

If you want to use a shape on a chart, in place of columns and bars, it’s possible to do this in Excel.

It’s not obvious how to do it, but is very simple and very quick.

Create your chart as normal. Once you’re happy, elsewhere on your screen, create the shape you want to use. Read More

Creating Waterfall Charts in Excel [2007,2010,2013]

March 20, 2015 3 comments Article Advanced, Excel, Microsoft Office

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]

March 13, 2015 0 comments Article Easy, Excel

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+ Read More

Format or Remove Borders from a Slicer or Timeline in Excel

March 5, 2015 2 comments Article Excel, Intermediate, Microsoft Office

Changing the formatting, or removing completely, the borders from Excel Slicers and Timelines isn’t obvious – read on to find out how Read More

Removing Duplicate Rows / Find Unique Values in Excel

February 19, 2015 1 comment Article Easy, Excel

[Excel 2007, 2010, 2013]

Anyone who’s used Excel in earnest for any length of time – ie 2003 and earlier – probably remembers having to create a VBA add-in to tackle the removal of duplicate values – or sometimes a COUNTIF() function would suffice for smaller jobs.

I always used the superb ‘The Duplicate Master’ add-in

(Which can still be found here – though the availability of the link seems to come & go)

Whilst such add-ins still have a place, offering rich features, for basic day-to-day removal of duplicates, Excel now includes this functionality built-in – and has done since Office 2007.

It’s this built-in functionality that we’ll cover in this post. Read More

Creating a Clustered Stacked Chart in Excel – Excel 2013

February 16, 2015 7 comments Article Advanced, Excel

Learn how to create a combined Stacked, Clustered Chart in Excel. One of the trickier charts to get to create in Excel – but is certainly possible. Read More