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

Apps for Office In Excel

Apps for Office in ExcelI want to be clear that when I say apps for Office in Excel, what I am talking about are the apps that you insert into your worksheet from the Excel ribbon. Microsoft have stated that they are moving away from the term Apps for Office though and will be using Office add-ins instead. I’m […]

Customize Default Excel Workbook

Excel custom blank workbookDid you know that when Excel opens a new workbook it uses a template called ‘Book’ to create the new file? This means we can create a custom default Excel workbook by replacing the template file called ‘Book’ with one containing our own formatting. How to Customize the Default Excel Workbook It’s easy to do. […]

Excel Fill Value Down Rows based on Criteria

Excel Fill Down IFA few weeks ago one of our members, Kylie, sent me this question:   “How can I fill column E with the text ‘CURRENT’, if the Position Title in column D matches the first instance of ‘CURRENT’ in column E?” See example data below; Kylie wants to fill the cells bordered in orange: Note: I […]

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

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 Custom AutoFilter with Wildcards

Excel AutoFilterExcel’s Custom AutoFilter can perform a myriad of tasks that are often overlooked. Just last week we had a question from Alf: “I have a list of 7000+ words and want to search it with variables such as find all words where the third character is “u”, or where the third character is “u” and […]