Excel Tips & Tricks

Be Sociable, Share!

    This is a live page where I add Excel tips and tricks as I come across or develop them. If you have a great tip for using Excel to share, please leave it in a comment below or email to hello@jacknorell.com. Any published tip will be fully credited.

    Working with Data

    Handy Excel formulas

    Sort number values into ranges in Excel

    Value in C2 will be bucketed corresponding to position 1, 2-3, etc:

    =IF(C2=1, 1, IF(AND(C2>1, C2<4), "2-3", IF(AND(C2>3, C2<6), "4-5", IF(AND(C2>5, C2<11), "6-10", IF(AND(C2>10, C2<21), "11-20", IF(AND(C2>20,C2<31), "21-30", IF(AND(C2>30, C2<51), "31-50", IF(C2>50, "50+", "Not Ranked"))))))))

    Turn text values into camelCase string in Excel

    A camelCase string is often needed for things like URL tagging, where spaces and different upper and lower case spellings can create multiple tags all tracking the same thing. This makes reporting and analysing difficult, as the campaign names “Great Christmas Giveaway”, “great Christmas giveaway”, and “Great Christmas giveaway” would each come up as a separate campaign name in Google Analytics.

    Spaces can break URLs, or cause duplication in the search engine index.

    Since Excel doesn’t have a built in function for changing text into camelCase, here’s an Excel formula that works reliably. This will also work in Google Sheets without changes.

    =SUBSTITUTE(SUBSTITUTE(PROPER(TRIM(B7)), LEFT(TRIM(B7), 1), LOWER(LEFT(TRIM(B7), 1))), " ", "")

    Be Sociable, Share!

      Leave a Reply

      Your email address will not be published. Required fields are marked *