I was having a discussion with someone the other day on the merits of using PIVOT / Cross-Tab functionality in SQL.
Back when the primary platform I used was Teradata, you didn’t have a PIVOT function, so if you wanted to do something similar to a Pivot, you had to build a series of CASE statements to build your fields up. This was fine, but you had to know beforehand what columns you wanted to have on your output
SQL SERVER (2005+) does provide a PIVOT option, but to be honest, it’s only marginally better than the CASE statement approach – you still have to know what column headings you want in your query.
So, if you want to pivot by the day of the week, that’s fine because there are a fixed number of days in the week and you can account for this.
But what if you want to have a field for each Agent/Region/date etc? You won’t always be able know what fields you’re going to need. Read More
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
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
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)
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
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
Ah yes, the obligatory link up to the upcoming Brazil 2014 World Cup – bet you didn’t expect to see one here, did you? Isn’t it nice when there’s a surprise at the end of the week? So, with no further ado, here is an Outlook holiday add-in to add the Brazil 2014 games relevant Read More
You may get the impression from some of my posts, such as this one, that I like to be well connected, and ‘always on’. That’s true, to a point. I think these days, being able to access your corporate mail whenever & wherever is being seen as increasingly useful, and often a necessary part Read More
We’ve all seen it – an email that comes out – perhaps someone erroneously replying to an entire distribution list, sparking hundreds of ‘did you mean to send this to the whole dist list’ replies, quickly followed by the obligatory ‘please stop sending me these mails’, ‘everyone – stop replying’ and of course the ‘I Read More