Charting Variances in Excel

Excel variance chartsIn my previous life as an accountant one of my main tasks was to analyse budget vs actual variances and then report these variances to department heads who were less than enthusiastic about finance, despite my repeated ‘Finance is Fun’ chants! Let’s see how we can present variances in a chart that’s quick and easy […]

Excel Quick Analysis

Excel Quick AnalysisA new feature in Excel 2013 is called Quick Analysis – it’s data analysis made easy, so easy my 6 year old could use it, you just point and click and Excel creates live previews and insights into your data using: Conditional Formatting Charts and Sparklines Summarise with Totals Tables and PivotTables Just add data. […]

Give me a SIGN, Excel

Excel SIGN FunctionExcel’s SIGN function returns a 1 if the number is positive, a zero if it’s zero and a -1 if the number is negative. The syntax is simple: =SIGN(number) Here are some basic examples: So what’s it good for? Well, last week we had a request for help from Alston. He was comparing the variances […]

Excel Filters 4 Ways

Excel FiltersFilters are one of my most used Excel tools. They’re not fancy, but they sure do earn their keep. I’ve even bestowed them with some prime real estate in my QAT: Filters allow you to quickly hide rows containing data that don’t meet your criteria. You can also apply multiple filters to one or more […]

Excel PivotTable FAQ’s

pivottable_faqsAfter having thousands of people recently attend our free PivotTable webinar we found there were 3 questions that were asked again and again, which are: How do I format my data in an Excel Table so I can refresh my PivotTable and it automatically picks up any new data added? How do I change the […]

Find Missing Numbers, Really Fast

find missing numbers really fastA while ago I wrote a routine using the .Find method to find missing numbers in a sequence. So if we had 1, 3, 5, the missing numbers are 2 and 4. I had some feedback that as the sequence grew longer, the .Find method became increasingly slower, to the point that your hair grew […]

Slicers for Excel Tables

Slicers filter Excel TablesDid you know you can use Slicers to filter Excel Tables? It’s like having your cake and eating it too! Not just any cake either, it’s like the most delicious Excel cake you’ve ever eaten and once you get a taste of the ‘Table Slicer Cake’ you’ll be wanting more. The good news is it’s […]

Excel PivotTable Calculated Fields

Excel PivotTable Calculated FieldExcel PivotTable Calculated Fields are easy to add but there are a couple of ‘gotchas’ you should be aware of. Calculated Field Definition Microsoft Definition: A Calculated Field is a user-defined field in a PivotTable that can perform calculations using the contents of other fields in the PivotTable. Calculated field formulas can refer to one […]