Search Not Just Numbers

Tuesday, 20 August 2013

Excel Tips - 5 of the best

Not Just Numbers has been going almost 5 years now and I thought it might be a good time to link to some of the most popular posts on the site. These are posts that are consistently in the top five to ten every month, although some of them were written years ago.

By far the most popular post of all time on the blog is my post on the use of the dollar sign to fix rows and columns in Excel:

When you are dealing with imperfect data, it is often handy to be able to deal with errors that make your reports look messy, this post deals with an approach to never needing to see an error message on your reports again:

5 comments:

Hi Glen, We arrived at a VBA / Versus formula debate a while ago. Today I discovered a little challenge. Colleagues of mine are using Excel 2007 which delivers filtering by colour.Unfortunately my team/corporate group standard does not allow the use of 2007 only 2003.

Are there any formulas I can use for filtering a set of 600 rows by there cell background colour??

Not to tease ....but I do have a VBA solution for us.

Hope all is going well for you and look forward to our resulting communications !

"Provided that the colours are not the result of conditional formatting, it could be done by adding a column to contain a formula that returns the colour codes and then filtering by that column :-

1. Go to Insert>Names>Define.2. In the NamesInWorkbook box type Clr3. In the RefersTo box type =GET.CELL(38,INDIRECT("rc[1]",FALSE))4. Click OK5. Insert a column immediately before the column with the colours6. In the first cell of the inserted column type =Clr 7. Fill down as far as required. This returns the colour codes.8. Filter by the inserted column.

NB :Do not copy/paste to a different worksheet any cells that contain the formula =Clr , since it will cause XL to crash."

I've tried this and it works, however if the colours change after entering =Clr in the cells, the number will not update until next time the spreadsheet recalculates, so it is safer to do an F9 before you filter.

If the colours are the result of Conditional Formatting, then just create an IF or VLOOKUP using the same rules as for the conditional formatting and filter on that.

This is a reference to the cell to the right of the current cell written in R1C1 style. It means current row (r), one column to the right (c[1]). There must be square brackets around the 1 and quotes round the full reference, i.e.

Hi, I'm Glen Feechan. Welcome to my Excel blog - Not Just Numbers.

I aim to provide practical Excel tips to those using Excel in their day-to-day jobs. I am a Chartered Accountant who has used Excel for too many years to remember, and now focus my efforts on helping others to get the most out of this powerful tool.

If you have any Excel requirements, I provide both on-site and remote consultancy and development services, of which you can find out more here.

You can sign up (below) and get a free report and regular updates of new posts to the blog.
Also have a look at the freebies section for free training videos, etc.

I hope you find the content useful and that every now and again it makes you smile.