Insights

Excel Instructions for MozCon

For those of you who are at MozCon and heard me speak <fistbumps>, I have prepared an overview of the steps I took to make some really ugly data sexy. I'll share screenshots for both PC and Mac (Excel 2010 and 2011, respectively) where there are significant differences between the two operating systems. If you weren't there, it might not make a lot of sense, but who knows ... You might be able to catch some good nuggets.

Download Excel File

If you'd like to follow along, you can download the Excel file I used in the presentation.

Setup

PC

The Quick Access Toolbar (QAT) is a lifesaver! Okay, so that's a little dramatic. It is at least a big time saver. I add to it everything I access on a regular basis.

Step 1: Find the feature you want to add to the QAT.

Step 2: Right-click and choose Add to Quick Access Toolbar.

Step 3: To customize, right-click the QAT and choose Customize the Quick Access Toolbar.

Step 4: Choose to move items up or down with the arrow provided.

My QAT

Quick Access Toolbar in Excel

Mac

Sadly, there is no Quick Access Toolbar for the Mac.

#bteam #lamesandwich

Table Formatting

Note: I wrote a comprehensive post on Search Engine Land that explores how to rock formatted tables for both the PC and Mac. If you don't know how to use table formatting to create simple databases that can be sorted, filtered, added to with ease (and update in charts automatically!), you are missing out. Besides all those functional benefits, they beautify your data in less than 30 seconds of work.

PC

Setup

Step 1: Click anywhere inside the data set.

Step 2: Click Home > Styles > Format as Table

Step 3: Choose your formatting option and follow the prompts.

Gridlines / Headings

Step 1: View > Show > Gridlines / Headings

Step 2: Or turn it off from Quick Access Toolbar, if you have it set up.

Turning off gridlines and headings in Excel

Column Width

Step 1: Fit column to content: Select all columns you want to adjust.

Step 2: Double-click any one of the dividers (the borders between the column letters).

Step 3: If you have a column that's too wide, right-click column > Column Width > Try cutting that number in half.

Freeze Panes

Step 1: To freeze a row(s) and column(s): View > Freeze Panes > Freeze Panes

Step 2: To freeze one or the other: View > Freeze Panes > Freeze Top Row / Freeze First Column.

Sort / Filter

Step 1: Click the down-facing triangle.

Step 2: Choose to sort or filter from the drop-down menu.

Wrong cell formatting in Excel Step 3: You'll get a text filter if there's anything in the column that Excel interprets as text. You can find these values by looking for any cells that are left-aligned.

Text in a number column

Step 4: To silence these signals, you can hide those rows.

Special Formatting Tips for Webmaster Tools Data

One nuance about tables is every column of data needs to have a unique title. So when you export the Search Queries report from Google Webmaster Tools, because you have several columns that are all labeled Change, when you format as a table, they change to Change1, Change2, and Change3. Groce. So I change the column to say whatever the column label + delta symbol to offset this and ensure intuitive headers. (I use the delta symbol to keep the headings from becoming ridiculously long.)

To get the delta character, type "D" and change the font to Symbol. Then change the font color to match the rest of your headings. BOOM. Much prettier column headers:

Pretty headers in Excel

Mac

 

Setup

Step 1: Click anywhere inside data set.

Step 2: Click Tables > Table Styles. <shakes fist at Microsoft for needlessly adding a new tab (Tables) to the ribbon in the Mac version>

Step 3: Choose your formatting option and follow the prompts.

Gridlines / Headings

I have an almost irrational loathing for gridlines. It is, without exception, the first thing I turn change with any spreadsheet that is handed to me. When you use table formatting, your data will be cradled by beautiful borders that coordinate with your header row, so there's no need to litter your entire worksheet with gridlines.

When I'm all finished with a worksheet, I will frequently turn off headings too. It really cleans things up. However, if there's any chance that viewers of your worksheet will need to adjust the rows or columns, hide them, or reference them for formulas, I wouldn't turn them off. I also include instructions for the recipient on how to turn them back on.

You can access these settings under Layout > View > Gridlines / Headings.

Excel for Mac gridlines

Column Width

Step 1: Fit column to content: Select all columns you want to adjust.

Step 2: Double-click any one of the dividers (the borders between the column letters).

Step 3: If you have a column that's too wide: right-click column > Column Width > try cutting that number in half.

Freeze Panes

Step 1: To freeze a row(s) and column(s): Layout > Window > Freeze Panes > Freeze Panes

Step 2: To freeze one or the other: Layout > Window > Freeze Panes > Freeze Top Row / Freeze First Column.

 

Sort / Filter

Step 1: Click the down-facing triangle in the header of the column you want to filter by.

Step 2: Choose to sort or filter from the drop-down menu.

Step 3: You'll get a text filter if there's anything in the column that Excel interprets as text. You can find these values by looking for any cells that are left-aligned.

 

Text in a number column

Step 4: To silence these signals, you can hide those rows.

Step 5: To learn advanced filtering techniques using wildcard characters, check out my Search Engine Land on table formatting in Excel.

Navigating a Worksheet

Delete a Row / Column

Step 1: Select row or column.

Step 2: Right-click > Delete

Hide a Row / Column

Step 1: Select row or column.

Step 2: Right-click > Hide

Unhide a Row / Column

Step 1: Select rows or columns that straddle hidden row(s) or column(s).

Step 2: Right-click > Unhide OR drag one of the borders out a hair.

Moving Around

Step 1: Go to the last item in a column or row: Ctrl-Arrow (Mac: Command-Arrow)

Step 2: Select all those cells: Ctrl-Shift-Arrow (Mac: Ctrl-Shift-Arrow)

Cell Formatting

Number Formatting from Ribbon

Step 1: Home > Number

Step 2: Choose one of the icons for a quick fix.

Step 3: Choose from the drop-down in the ribbon.

Number Formatting from Menu

Step 1: To access, press Ctrl--1.

Step 2: If none of the options work, go to Custom.

  • Jul 26, 2012 --> mmm dd, yyyy
  • Thursday --> dddd
  • Thu. 07.26.12 --> ddd. mm.dd.yy
  • 354-49-0934 --> 000-00-0000
  • 25   25   0 --> [Green]0;[Red]0;[Black]0
  • 25.3%  25.3%  0% --> [Color 10]0.0%;[Red]0.0%;[Black]0% (Color 10 gives you a darker green, which I prefer.)
  • $1500 Profit  $1500 loss  Break Even --> $0" Profit";$0" Loss";"Break Even"
  • < 1000 = Low   #, > 2000 = High   #, 1000 < x < 2000 = Medium   # --> [<1000]"Low"* 0;[>2000]"High"* 0;"Medium"* 0

To see more colors available to you in Excel, check out this table.

Format Painter

You can use the Format Painter to save yourself some work and "paint" any kind of formatting you've applied to another cell. This includes color, cell alignment, text orientation, borders, number formatting, conditional formatting, etc.

Excel's Format Painter

Conditional Formatting

Note: There aren't enough differences between the PC and Mac to warrant separate instructions. If something is really different on the Mac, I'll make note of it in context.

Duplicate Values

Step 1: Select column and navigate to Home > Styles > Conditional Formatting > Highlight Cell Rules > Duplicate Values.

Conditional formatting for duplicate values

Step 2: You can use one of the preset formats or choose a custom one. I'm going to highlight duplicate hash values (indicating duplicate content) using yellow highlighting but clicking the yellow swatch from the Fill tab.

Conditional formatting yellow fill

Step 3: Now you can sort by fill color to make duplicate values float to the top. Just click the down-facing triangle in the column heading, then choose Sort by Color and click the yellow swatch.

Sort

Step 4: To group the duplicate values together, go back into the filter and choose Sort by Color > Custom Sort. In the Sort dialog click the Add Level button to choose a secondary sort criterion. Then sort the hash value alphabetically.

Step 5: Here's a sample of the final result:

Data Bars

Step 1: Select column and choose Conditional Formatting > Data Bars. I like the gradient bars and use them a lot with data sets that I need to sort and filter in different ways. And the awe-to-effort ratio this modification makes is hard to pass up.

Step 2: Here is the result:

 

Note: I try to avoid using "negative" colors like red for positive metrics, like visits, revenue, conversions, etc. I keep it for more negative metrics, like bounce rate, cart abandonment rates, and things like that.

Highlight Values

Let's say you want to highlight every title that has more than 70 characters. Here's what you would do:

Conditional Formatting > Highlight Cell Rules > Greater Than. Then choose your formatting.

The result:

Icon Sets

I love using icon sets for rank reports to show visually if a keyword is ranking on page 1, 2, or 3 and beyond. Here's how to set it up:

Step 1: Conditional Formatting > Icon Sets > Set of your choice. I used the 3 Traffic Lights (Rimmed) format.

Step 2: Go back to Conditional Formatting > Manage Rules, and in the Conditional Formatting Rules Manager dialog, click Icon Set to select it and click the Edit Rule button. (Don't ask me why Excel closes out of the dialog like that. So rude.)

Step 3: By default, green is assigned to the larger values, working its way down to red. In the case of a rank report, the smaller the value the better, so you'll want to click the Reverse Icon Order button. Then follow the steps in the screenshot below:

CORRECTION:  Step 3 in the above screenshot should have been: Change Type to Number by choosing it from the drop-down menu. By default, Excel wants to split your data set into thirds even Steven, so this value will be set to Percent. Don't let it push you around.

Step 4: Then you can sort by icon.

 

Step 5: Or what I often do in a real rank report (this sample is just from GWT) is sort by search volume and then icon to show me the keyword opportunities. Here's what the icons look like in this GWT report:

 

Charts

Step 1: Select the columns you want to include by holding down the Ctrl key. Make sure you get the headings too. Then click Insert > Charts > Column > Clustered Column.

Chart setup in Excel

Step 2: We're going to move bounce rate to its own axis since it's such a small number, it doesn't show up. Normally, you can select a series by clicking on any of the columns (click again to just choose just that one), but in this case, they're very hard to select. The easiest way to select them is to go to Chart Tools (you only see this option when you have a chart selected) >  Layout > Current Selection > Bounce Rate.

Choose series in Excel

 

Step 3: We're going to change the chart type just for this series to a line chart: Chart Tools > Design > Type > Change Chart Type > Line > Line with Markers — or just plain Line (first option)  if you don't want the markers.

Combination Chart in Excel

 

Step 4: We're going to pop the line chart onto its own axis. First, with the series still selected, press Ctrl-1 to pull up formatting options, then choose Series Options > Secondary Axis.

Secondary axis

Step 5: I don't like the thickness of the default line or how large the markers are, so I adjust them using the Line Style and Marker Options settings in the Format Data Series dialog.

Chart line options

Step 6: You can easily change the color of any column by clicking on the column and choosing a new color from the paint bucket icon under the Home tab.

Change column color

Step 7: Now it's time to clean up. First, there's no reason to have two decimals in the percentages, so we're going to get rid of them.

Chart percentages

Step 8: I don't like the clutter on the secondary axis, so I'm going to change the maximum value to 100% and the major unit to 20%, which will give us 20% intervals.

Change axis options

Step 9: Sometimes I'll add in a baseline value to help establish what bounce rate a client should shoot for. To do that, just add a column to the table (it will expand automatically), enter the value in the first cell, and use the fill handle to drag those values down the column.

Chart fill values

Step 10: Change those values to percentage (Home > Number > Percent Icon), then copy the entire column — including the heading — to the clipboard and paste it into the chart. The heading will show up in the legend and a new series will be added to the chart.

Copied series

Step 11: Format that line however you'd like by selecting it and pressing Ctrl-1, I turned off markers under Marker Options, set the Dash Type to Square Dots, and the Line Color to gray.

Excel target line

Step 12: Now we'll add a chart title (Chart Tools > Layout > Chart Title > Above Chart).

Chart title

Step 13: As the final step, I moved the legend to the bottom of the chart by pressing Ctrl-1 and choosing Legend Options > Legend Options > Top. (I usually move it to the bottom, but there's a lot going on down there.) And I like to bump the font size up for good measure, leaving to this final chart:

Final chart in Excel

Mac

Step 1: Select the columns you want to include by holding down the Command key. Make sure you get the headings too. Then click Charts > Column > Clustered Column.

Step 2: We're going to move bounce rate to its own axis since it's such a small such a small number, it doesn't show up. Normally, you can select a series by clicking on any one of the columns (click again to just choose just that one), but in this case they're very hard to select. Easiest way to select them is to go to Charts (you only see this option when you have a chart selected) >  Chart Layout or Format > Current Selection > Bounce Rate.

Select series

Step 3: We're going to change the chart type just for this series to a line chart: Charts > Line > Marked Line — or just plain Line (first option)  if you don't want the markers.

Line chart

 

Step 4: We're going to pop the line chart onto its own axis. First, with the series still selected, press Command-1 to pull up formatting options, then choose Axis > Secondary Axis.

Step 5: I don't like the thickness of the default line or how large the markers are, so I adjust them using the Line Style and Marker Options settings in the Format Data Series dialog.

Chart line style

Step 6: You can easily change the color of any column by clicking on the column and choosing a new color from the paint bucket icon under the Home tab.

Step 7: When I retraced my steps to go through all the steps on my Mac, I forgot to replace the decimals. But you'll find the option under Home > Number > Now it's time to clean up. First, there's no reason to have two decimals in the percentages, so we're going to get rid of them.

Reduce decimals

Step 8: I don't like the clutter on the secondary axis, so I'm going to change the maximum value to 100% and the major unit to 20%, which will give us 20% intervals. The weird thing with the Mac UI (one of many) is that when you customize one of these options, the checkboxes become deselected.

Chart axis formatting

Step 9: Sometimes I'll add in a baseline value to help establish what bounce rate a client should shoot for. To do that, just add a column to the table (it will expand automatically), enter the value in the first cell, and use the fill handle to drag those values down the column.

Adding a column

Step 10: Change those values to percentage (Home > Number > Percent Icon), then copy the entire column — including the heading — to the clipboard and paste it into the chart. The heading will show up in the legend and a new series will be added to the chart.

Chart add series

Step 11: Format that line however you'd like by selecting it and pressing Command-1, I turned off markers under Marker Style. Then I changed the line color to gray under Marker Line > Solid and the line to dashes under Marker Line > Weights & Arrows > Dashed to Square Dot. set the Dash Type to Square Dots, and the Line Color to gray.

Adding a new series to a chart

Step 12: Now we'll add a chart title (Chart >Chart Layout > Chart Title > Title Above Chart).

Chart title

Step 13: As the final step, I moved the legend to the bottom of the chart by pressing Command-1 and choosing Placement > Top. (I usually move it to the bottom, but there's a lot going on down there.) And I like to bump the font size up for good measure, leaving to this final chart:

Final chart

 

 

 

 

 

 

We love helping marketers like you.

Sign up for our newsletter to receive updates and more: