A Beginners Guide to Excel (Or Maybe Just a Handy Reference)

Prior to working at QueryClick, I thought I knew how to use Excel. I had used to it track information and plan advertising distribution and while I was aware there were other functions, they never really cropped up in my every day work.

After becoming search analyst, my eyes were opened. Where Google AdWords and Analytics let you implement PPC advertising and optimisation, Excel lets you manage all of the information quickly and is an essential part of simply getting things done.

Over the last few weeks I’ve encountered a number of different techniques that, while only scraping the surface of what Excel can actually do, have become essential functions I use on an almost daily basis. They have simplified many projects, making life so much easier and I’m going to go through a few of them for you below.

Let's start with navigation.

Ctrl+[arrow key]
This will send you directly to the last filled cell of any row or column you’re currently highlighting (relative to the direction you're pressing). For instance, if you’ve clicked cell A1 on a sheet of data that has cells filled all the way to A875, then pressing ctrl+down will take you directly to cell A875. Similarly (if the cells between are filled), pressing ctrl+right will take you to some far away cell to the right. If there are gaps in your table of data the movement will stop at the empty cell.

Ctrl+Shift+[arrow key]
This does exactly the same as above, but with the bonus of selecting each and every cell that you travel through. So, if you have cells filled from A1 to H450, then to quickly select them all you would just start at A1 and press ctrl+shift+right then ctrl+shift+down.

Ctrl+A
This selects all of the cells on a sheet. The thing to note is that if you use Ctrl+A without selecting a cell it will select the entire spreadsheet; if you click a filled cell first (within your table of data), it will make a selection of all the cells that are touching.

Now a little on data management.

New window (View -> new window)
This will open a duplicate version of the excel workbook you’re currently using which might not sound that useful, but if you reduce their window size, you can view them side by side. This is handy if you want to view 2 different sheets in a workbook at the same time without switching back and forth between them.

Split Panes
If you hover your mouse over the right hand side of the horizontal scroll bar or the top of the vertical one you'll notice your cursor change. This means you've found the “split panes” tool. If you click and drag that little bar you'll create a page split across the spreadsheet you're looking at and this (like the New Window tool) will let you see two simultaneous, navigational views of the same spreadsheet.

Ctrl+shift+L
This is the short-cut for filters. This is the feature I use the most for PPC. It allows you to set a selected row to act as a filter for the cells below it which can then be used to sort and search through the data contained in the column below it. This is very useful for organising and sorting tables of information or restructuring keywords and ad groups for PPC campaigns.

Length (=LEN(cell)) This is the function for finding out how many characters are being used in a cell. Due to the limited number of characters allowed by AdWords, I use this to help me write PPC ad copy and write it as “=25-LEN(cell)” and “=35-LEN(cell)” so that it lets me know what my remaining character limit is.

Vertical Lookup (=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup))
This daunting looking function is not as complex as it might seem. It's basically a function that lets you grab information from one table of data and display it in another even if the source is in a completely other spreadsheet file.

While straightforward to use, it is quite lengthy to explain and as such I’ll link you to a very well written guide on it over at howtogeek.com.

Concatenate (CONCATENATE( text1, text2, ... text_n ))
This function lets you take text information from multiple cells and join them all together in a single cell. For example, if you have three different cells containing the words “This is”, “how” and “concatenate works”, then using the function with those cells will produce “This ishowconcatenate works”. What you have to remember is that it only includes what's in the cell so if you've not formatted the spacing properly, it will come out like the example above. It's proven quite useful in the creation of meta descriptions and page titles.

Fill handle
When you click a cell, you'll see a little black square in the bottom right. This is the Fill Handle and it is very useful. At the basic level, it will let you create sequences of numbers. So if you highlight two cells containing the numbers 1 and 2, dragging the fill handle down will continue that sequence and fill the rest of the cells with 3,4,5,6,7,8...

It also lets you do something similar for formulas. So if you have a cell referencing data from an adjacent column and you want every cell below it to do the same thing, then you just drag the fill handle down and excel will copy the function but change the cell reference so that it grabs the data in the appropriate row.

There are actually a whole range of things that you can do with the Fill Handle and a good list of them can be found in the Fill Handle Tutorials over at spreadsheets.about.com.

Finally, let's take a look at formatting.

Ctrl+B; Ctrl+I; Ctrl+U
Like most text based applications these are short cuts to bold, italicise and underline text respectively.

Proper function (=PROPER(cell))
This takes a string of text and then capitalises the first letter of each word. So a cell with “lower case” will become “Lower Case”.

Upper/lower (=UPPER(cell);=LOWER(cell))
These will change all of the characters in a cell to be either upper case or lower case.

Trim function (=TRIM(cell))
When using the above functions (especially VLOOKUP which relies on data accuracy) in conjunction with keywords, sometimes you'll come across strings of text that contain a few extra spaces which are hard to spot. What the Trim function will do is take the string of text in a cell and remove all spaces before and after it.

So there we have it, a few handy features that really are just the tip of the iceberg when it comes to the overall program. Here are a few references that have helped me so far which also contain a lot more information on what excel can really do:

• Excel Shortcuts
• About.com’s spreadsheet guide
• Tech on the Net’s Excel section

Long Live Excel!