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:

EXCEL TIP: The dollar sign ($) in a formula - Fixing cell references

Another much read post is my explanation of the IF statement which is one of the most useful functions to grasp in Excel:

EXCEL TIP: The IF Statement made simple

A more recent post that is proving popular is my assorted tips on tidying up text:

EXCEL TIP: Simple tips for tidying up text 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:

EXCEL TIP: Eliminating #DIV/0! and other errors automatically using ISERROR

Finally, this post gives a simple tip for learning any new function that you want:

EXCEL TIP: Use any Excel function in seconds

I hope there's something new to learn there for you.

Excel Expert Course

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

5 comments:

  1. 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 !

    Cheeers.. please keep tips coming .....some are real gems.

    Thanks again
    Kind Regards
    Peter Lewson

    ReplyDelete
  2. Peter

    I couldn't think of anything myself but came across the following solution from Ponsonby on the Mr Excel website

    "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 Clr
    3. In the RefersTo box type =GET.CELL(38,INDIRECT("rc[1]",FALSE))
    4. Click OK
    5. Insert a column immediately before the column with the colours
    6. 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.

    ReplyDelete
  3. Glen not worked for me. what is "rc[1" ? is this create problems ?

    Rgds,
    sharif

    ReplyDelete
    Replies
    1. 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.

      "rc[1]"

      It should work.

      Delete
    2. These are BRILLIANT tips Glen, I will tet this live at work tomorrow, but in theory it lookGREAT

      Thanks very much

      VBA allows me to write a function , save the xlsm spreadsheet as an XLA then utilse this XLA as an Add-in,
      Not straightforward.

      VERY VERY Effective when running


      Thanks Again
      Peter L

      Delete