Excel Tips & Tricks, Part 2
Published: June 19, 2012
Author: Jaime Sikora
As I am sure you all remember (right?), I wrote a post a while ago on some of my favorite Microsoft Excel tips and tricks to get you through the workday. I have a couple more tips hiding up my sleeve that I thought could be of help for those in the world of paid search (and, well, for everyone else too).
1) Freeze panes
I figure we can start basic. Let’s say, for example, one has a very large amount of data to work with. I’m sure you can imagine this happening; it happens to me about once an hour. There’s a helpful, quick, easy to way to keep either the top row or side row (or both) still while scrolling through large amounts of data. One can go to the ‘view’ tab on the ribbon, then, around mid-screen, select ‘freeze panes.’ From there, one can choose which area to, well, freeze.
Now that you’ve frozen the top row, you can scroll down and keep the headers visible…note the row numbers below.
Let’s say, for example, you need to substitute something within a cell. The immediate example that comes to mind for me is a to replace a space with an underscore (you can see them in the highlighted box below if you look closely). In case you don’t want to use the good old-fashioned find/replace, you can use the substitute formula (found up in the function bar). Or, for simplicity, start typing =substitute. Then, throw on an open parenthesis and select the cell where the offending material lives. Next, add in a comma, quotation marks containing the material one wants to replace, another comma, the material one will be replacing the offending material with, and a closed parenthesis. A visual probably makes this explanation much more bearable:
3) Identifying the day of the week
Now, let’s say, for example, you’re doing something along the lines of day-parting. Or, really, anything involving days. Let’s say you’ve pulled some data that involves a numerical day of the week (“mm/dd/yyyy”), but what you REALLY want to know is what day of the week you’re dealing with. If one types in “=text(cell, “dddd”)” – this will yield the day of the week.
4) Parsing characters within cells
In the instance you need only a part of the data in a cell but don’t want to go through a backspacing mess, there’s a quick way to parse out only a portion of the characters. For example, if there’s a list of social security numbers, and you want only the last four numbers for obvious privacy purposes, you can type “=right(cell, 4)” for the last four characters in the cell. If you want the first characters, choose left. And, of course, the four is a complete and total variable – 1,2,3, 18 – as long as it doesn’t exceed the number of characters within the cell.
This can also be helpful with parsing out initials from a list of names.
5) Finding maximums and minimums
And, finally, one last tip: in the instance you have a long list of numbers and are looking to find the max value quickly, you can type in =max, then highlight the range of numbers you are looking at, then hit enter. As always, a visual is helpful:
And yes, of course, there is a counterpart – the min function for the minimum value.
I’ll let you digest all this data for now. As you can imagine, we’ve really only scratched the surface on Excel tips. Does anyone else have some favorites to share? Anything you’d like me to cover in a future Excel post? Please let me know.
– Jaime Sikora, Account Coordinator