Welcome to Ellan's wild world of Google searches
Feeling like I can now conquer the world with my spreadsheet knowledge.
Let's get to it... here's some cool tricks I learned in Excel this week.
1) Find and Replace Wildcard
Did you know you can use the same wildcard feature that you use in coding languages in Excel?
Because I. DID. NOT.
I wanted to find all the Bill's or Will's in this Bachelorette contestant dataset I was working on. Hello, important analysis to do...
Type into the find bar a '?' in the beginning (i.e. ?ill). Woo now you have the Wills, the Bills, the Billys, and the Williams.
2) Quick Analysis Button
Want to see the number of roses that were recieved every elimination round? We need to use the COUNT formula.
Quick way to do that... highlight the section you're interested in and that little box with a lighting strike on it in the lower right hand side of selection?
Click that icon and now you can quickly format, add a chart, add some totals or create a table.
3) Add First Zero
So I was going one by one and adding whatever personal details I could to effectively stalk them. Within these findings were some zip codes, hurray!
One guy lives in Middlesex, NJ with the zip code 08901*. But in my spreadsheet, the 0 isn't showing up.
To fix this we change the number formatting. Go to Number > Custom > Type in '00000'
*For everyone's safety, no real locations were used.
4) Filter Shortcut
Filter by season, please and thank you?
Easy peazy... Select the season column and hit CTRL+SHIFT+L
To drop down the filter menu, use the shortcut ALT+DOWN.
Then use the arrows to navigate the filter menu and select/deselect the different options by hitting the SPACE bar.
P.S. Some of my fave Bachelorette seasons are 13 (Rachel), 5 (Jillian), 8 (JoJo), and 12 (Andi).
5) New Tab Shortcut
So typically when I want to duplicate a spreadsheet within the workbook, I right click the tab, select MOVE, etc.
Found out this week though that I can just hold down CTRL while clicking on the tab I want to copy and then slide the black arrow to where I want to place the copied spreadsheet!
Now go crush that next spreadsheet you're working on Excel queens!