Excel Tips & Tricks

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.


Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.