Ways to Manage Large Excel Files (through zip, xls, xlsx, xlsb, pivot table caching)

Managing the file size of Excel files can seem a challenge, but there are some fairly simple steps you can take to make it a bit easier.

This post will take you through file management through File Formats, File Formatting and File Content, before finishing with some file sharing tips. Read More

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.


Read More

Removing the Search / Cortana box on the Task Bar in Windows 10

When you first upgrade to Windows 10, your Task Bar will include two new components – the search box (or Cortana if you’ve enabled this) and the Task View button next to it. If, like me, you find it a little obtrusive, the good news is that it’s very easy to remove or minimise these Read More

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

All Change!

It’s been a little while since I’ve posted anything. This is because I’ve recently taken up a new role in a different company, which I’m now about 7 weeks into. As you can imagine, my focus has been on settling into my new role, so have had little chance to get any updates on here. Read More

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

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

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

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

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]

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

Read More

1 2 3 4 5 6 9