 |
|
| TheExcelAddict.com | |
| October 29, 2020 |
|
| Hi fellow Excel Addict, | |
Thanks for
joining me today for another 'Excel in Minutes' tip. It's nice to be out of quarantine and thankfully Newfoundland isn't seeing a surge in COVID cases as are many parts of the world. We did get spoiled a bit the last few weeks with the unseasonally warm weather. However, now we are back to reality and almost-freezing temperatures. I hope you will find today's 'Excel in Minutes' tip helpful. If you do, please help share my newsletter on your social media accounts — and with your colleagues or any other Excel users you know who want to get smarter with Excel. Have a great day, keep safe and keep on Excelling, Francis Hayes (The Excel Addict) Email: fhayes[AT]TheExcelAddict.com |
|
|
|
|
|
|
|
| Missed my last newsletter? | |
Click Here to View it Online |
|
| |
|
Having a positive attitude can help us in difficult times. I hope today's quote will help you foster a positive attitude today. Quote of the Day "The choices that we make today will always bear witness to our weaknesses tomorrow." -- Frank Matobo -- |
|
|
If you have a favourite quote, send it to me
and I may post it in my newsletter.
|
|
THIS WEEK'S 'EXCEL IN MINUTES' TIP |
|
How To Sequentially Number A Filtered List |
|
When you filter a list, it is sometimes helpful to include a column with sequential numbering (i.e. 1, 2, 3, 4, etc...), for example printing subsets of the data.  However, when you filter your list, manually-typed sequence numbers don't work.  So rather than manually entering numbers in the visible rows, here's a simple 'once-and-done' solution that you can put in your list and filter away, keeping your sequence numbers always working.  Here's the formula: Insert a column for the sequence numbers and enter the following formula adjacent to the first record in the table. In this example the formula is in cell B5. =SUBTOTAL(3,C$5:C5)
Since here I'm using an Excel Table, when I enter the formula it is automatically filled down the to the end of the column. If you are using a regular range (i.e. not a Table), see additional notes below. Here's an explanation of how the formula works: This is not the normal application of the SUBTOTAL function. In this situation I am using the SUBTOTAL function as a bit of a 'hack' to do something that normally can't be done with Excel. I'm not going to explain how the SUBTOTAL function normally works, other than to say that using 3 as the first argument of the formula tells Excel to use the COUNTA (i.e. count all) function, which counts both numeric and text values in the referenced range. If you wanted to count only numeric values, you would use 2 (i.e. the COUNT function) as the first argument. An important concept here is using the dollar sign ($) in front of the first row reference (5) and no dollar sign in front of the second row reference (5). The $ creates and 'absolute reference' to row 5. That 'freezes' the top cell reference to row 5 as the formula is copied down the column, so that row reference doesn't change. Omitting the $ from the second row reference creates a 'relative reference'. This means that the row reference for the bottom of the range will adjust as the formula is copied down the column. The formula in the example above is in cell B5. So, as it is copied down to cell B6, the formula changes to... =SUBTOTAL(3,C$5:C6)
You can see that the first row reference stayed the same (5) and the second row reference changed (to 6) relative to how many rows down it is copied? Now, filter your list using any of the data columns. No matter how you filter your list, the sequence numbers in column B will always be in sequence. If for some reason, you need to explicitly 'hide' rows (i.e using Home, Format, Hide & Unhide, Hide Rows or right-click the row heading and Hide) rather than just using Filter, you will need to use 103 instead of 3 in the SUBTOTAL function. =SUBTOTAL(103,C$5:C5)
When using 3 in the SUBTOTAL function, 'explicitly hidden' rows are included in the count whereas using 103 excludes them. Sequentially Numbering a Regular Range If you want to use this technique for a range that is not in a Table, you will need to make a slight modification to the above formula. The above technique is using the SUBTOTAL function (in column B) in an unconventional way. Therefore, Excel assumes that since there is a SUBTOTAL function in the last row, that last row is not one of our rows of data. Now when we Filter our data, Excel excludes the last row from the filter range, so it doesn't get filtered (i.e. it remains visible).  Multiplication by 1 To get around this problem, we can simply multiply the SUBTOTAL function by 1 (i.e. type *1 at the end of the formula) =SUBTOTAL(3,C$5:C22)*1
This 'hack' forces Excel to include the last row of data in the filter range.  Although (in a regular range) the *1 is only necessary in the last formula, it's just much simpler to use it in the first formula/row and copy it down. |
|
|
|
|
|
|
|
 |
|
|
|
|
 |
|
| Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 16 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose. | |