Excel Worksheet Protection

Excel Worksheet ProtectionExcel worksheet protection is a great way to prevent people accidently messing up the reports you’ve spent hours creating. However, it is not going to keep confidential data a secret. If someone really wants to get to your data, or see how your model was put together and all the amazing formulas you’ve used then […]

Excel Chart Vertical Axis Text Labels

text labels on chart y axisIn this tutorial we’re going to look at how we can twist Excel’s arm into putting text labels on the vertical axis of a chart. This was inspired by a question I received a while back from Juan Gabriel Aguero, who asked if we could re-create this chart in Excel: While the answer is yes, […]

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 […]