Excel Flash Fill

Excel Flash FillExcel’s Flash Fill tool is so cool that you’ll be looking for reasons to use it! Introduced in Excel 2013 to rave reviews, Flash Fill uses a form of machine-learning techniques to reformat data automatically based on one or two examples you give it. No more complex formulas or VBA, with Flash Fill you’ll be […]

Excel Fill Series

Excel Fill Series ToolThe Excel Fill Series tool is like having a bundle of tricks at your fingertips. It can help with one of the most common tasks we do in Excel, which is to create a list of dates. Whether it’s a list of days, months or years, the Fill Series tool can easily handle them all […]

Excel Copy and Paste Visible Cells Only

Excel Copy and Paste Visible Cells OnlyHave you ever wanted to only copy or paste visible cells? For example, below I have a table containing outstanding customer invoices. I want to insert a formula in column E to insert an overdue fee for invoices outstanding longer than 31 days: However, I don’t want the formula on the subtotal rows (row 5, […]

Excel PivotTable Calculated Items by Position

PivotTable Calculated Items by PositionA while back I wrote about how to create Excel PivotTable Calculated Items using the conventional approach of referencing the item name in the formula like this: But did you know you can also refer to items by their position in the PivotTable relative to the column containing your Calculated Item? Download the Workbook Download […]

Excel Sparklines

Excel SparklinesPut some spark in your chart! Excel Sparklines were introduced in Excel 2010 and are great for displaying the trend of data over time and making sense of a sea of numbers. Just take this before and after example: Before A sea of numbers that would take a long time to analyse and compare: After […]

Excel Form Data Validation

excel form data validationWhen creating Excel userforms it seems like a good idea to check the data going into the form before adding it to your sheet. Once the data is in your sheet you can use other methods like conditional formatting to highlight errors or unwanted data. But it’s good to stop the errors before they get […]

Excel Date Picker

excel date pickerIn my previous post about Excel forms the date picker I used was a 32 bit ActiveX control. If mentioning 32 bit ActiveX controls makes your eyes roll back in your head, then this post is for you This date picker is implemented as a VBA class. Which means all you need to do is […]

Excel Forms

Excel formsExcel forms, or Userforms, can be used to collect information from a user, validate it, then enter that data into your worksheet. In this post I am going to show you how to create a form in Excel that gathers data, then populates a table for tracking expenses. This is meant as a simple example […]

Excel Named Range Shortcut

Excel Named RangesAn Excel Named Range is a powerful tool and something we should all be using to make our formulas quick and easy to write and read. I’m not going to cover the basics of creating a named range as I’ve done that here, however I want to remind you of the different types of names […]