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.

freeze panes

Now that you’ve frozen the top row, you can scroll down and keep the headers visible…note the row numbers below.

2)  Substitutions

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.

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


  1. rodnitzky June 19th, 2012

    Excel Tips & Tricks, Part 2

  2. fantomaster June 19th, 2012

    RT @toddmintz Excel Tips & Tricks, Part 2 | PPC Associates Blog

  3. newyorkbigapple June 19th, 2012

    nice—> Excel Tips & Tricks, Part 2 | PPC Associates Blog #ppc

  4. Terry Whalen June 19th, 2012

    Hi Jaime,

    Do you know a way to get Excel to default to showing numbers with no decimals, but always a thousand separator. Have you found some sort of global setting for this?

    Also, do you know of any quick and easy way to format numbers in a pivot table? The slow way is to right click somewhere in the column, choose ‘number format’, choose the number category, and then choose decimals and thousand separator, for example. And then do this for each column individually. Want formatting to stay in place even when pivot columns are re-arranged or data is updated.

    One super useful shortcut – click ‘cntrl+arrow’ to get to quickly navigate to the end of a row or column of data; add ‘shift’ to highlight as you go.

  5. Jaime Sikora June 20th, 2012


    Thanks for reading and commenting! Ctrl+arrow is definitely one of my faves. It might get a mention in the third installment of this three part series. I am also open-minded to any other suggestions you (or anyone, for that matter) would like to see in the final post.

    In regards to your questions – I don’t know the answer offhand. I reached out to my team – they said your best bet would be recording a macro for this (I’m sure you knew of this option but we’re hoping for a simpler answer). I will keep an eye out and continue to research this.

  6. rodnitzky June 20th, 2012

    Excel Tips & Tricks, Part 2 – As I am sure you all remember (right?), I wrote a post a while ago on some of my favor…

  7. seocretos June 20th, 2012

    RT @edgar_sanchez: A few Excel tips for PPC Parts 1 & 2

  8. ajkohn June 24th, 2012

    Reading: Excel Tips and Tricks (Basic, but very useful tips.) #ppc #seo

  9. Tarky7 June 24th, 2012

    RT @ajkohn: Reading: Excel Tips and Tricks (Basic, but very useful tips.) #ppc #seo

  10. KonceptWeb June 24th, 2012

    If you’re using Excel in any capacity, give this post a read and see if it makes your life a bit easier. #analytics #fb

  11. Areahouse40 June 24th, 2012

    RT @KonceptWeb: If you’re using Excel in any capacity, give this post a read and see if it makes your life a bit easier. #analytics #fb

  12. Cristian Senkai Labs July 12th, 2012

    Super! thanks for your tips! I didn’t know about a few of them like the identifying the day of the week of a certain date :)

Leave a Comment

Jaime Sikora
Jaime Sikora is a PPC & Display Specialist at Nadex. Jaime graduated from the University of Illinois at Urbana-Champaign with a degree in Advertising. Prior to joining 3Q Digital, she worked in the newspaper industry at the Chicago Sun-Times. In her spare time, Jaime enjoys reading, cooking, traveling, and spending time at the Chicago lakefront.