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

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

Would you like Charts with that?

Excel pivot chartsI’ve always thought after inserting a PivotTable that Excel should ask “would you like a PivotChart with that?”. I think PivotCharts are Microsoft’s equivalent of McDonald’s famous upsell – “Would you like fries with that?” I’m not saying you should have Excel Pivot Charts with every PivotTable meal but they do go nicely together. However, […]