Blog & Excel Tips

From Excel 2016, there is a convenient Forecasting tool in Excel which allows you to show Excel some historical data (say 24 months worth of sales) and Excel will, taking growth and seasonality into account, forecast for up to 50 months into the future.

If you use PowerQuery you may notice that in the filter, some of the options you want to use seem to be missing. So why is ' Begins with, Ends With, Contains missing ' missing from some PowerQuery filters?

GETPIVOTDATA is a brilliant formula that allows you to effectively run a VLOOKUP within a Pivot Table. It is possible to make the GETPIVOTDATA use dynamic references but the one that doesn't work so well is the Data Field.

You've built your Pivot Table in MS Excel, but the column and row labels are not showing the name of the relevant field. How can you fill the empty labels with the headings in a Pivot Table?

Conditional formatting is a useful feature but there are times that you need to remove either all the conditional formatting or else the conditional formatting on specific cells. Two ways of how to remove conditional formatting in MS Excel.

...Problem is that if ALL the workbooks aren't open, instead of seeing the numbers you see #Value! errors.  Can you address the situation where SUMIFS returns value when linked workbooks are closed?

When using Microsoft Excel you often end up with a logical answer i.e. a TRUE or a FALSE. In Excel TRUE and FALSE are actually seen as the numbers 1 (True) and 0 (False). So how can you turn TRUE FALSE into numbers in Excel?

In several of our recent public courses we have mentioned and shown a bit of what MS Excel's PowerQuery can do. Due to the amount of people that are interested we are going to run a 1 day PowerQuery in MS Excel course on the 14th June 2018 in Johannesburg. To get more information email mashooda@AuditExcel.co.za.