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 firstname.lastname@example.org. Any published tip will be fully credited.
Working with Data
- Combine multiple data sources in Excel with Microsoft Query
- Get more than 5,000 rows of data from GA with the GA API Query Explorer
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))), " ", "")