Blog & Excel Tips

Import all files in a folder into Excel whether they are text files (e.g. csv's) or Excel files. When you add more files, they will automatically update.

Where to look for Excel's new data cleaning tool called PowerQuery (or Get and Transform in Excel 2016)

How to find the max number ignoring errors in Excel workbooks

Working in Excel is great because by default it assumes you are working with numbers and as such does its best to display numbers correctly (neatly aligned). However, mostly when moving numbers out of Excel, you may experience that strange occurance of trying to align numbers. You spend hours adding and deleting spaces and yet the numbers never seem to align properly.

Once you learn how to build an IF statement you may think that this is the best way to build complex formula. However, there are often better ways. One example is how to avoid multiple IF statements. Commonly these are referred to as nested IF's and are a source of confusion (in building and reviewing) and errors. So is there an alternative to multiple IF statements.

When you create a column chart, you may want to change the gap size between the series, normally to remove the gap from the column chart.

You've built your chart, it all makes sense and suddenly you look at your horizontal axis, and the date axis is wrong. This happens a lot. You expect to see the dates you have in the data but different dates are showing, normally the beginning of month date instead of the end of month date.

When using the Data Filter in the newer versions of Excel, you have the ability to search for items that contain some characters. You can then, in one step choose to add the items to the existing filter using the 'Add Current Selection to filter' tick box. However it is not as obvious how to 'REMOVE current selection from data filter'.