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
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.
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.
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.
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:
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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: 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.
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.
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.
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.
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.
Step 12: Now we'll add a chart title (Chart Tools > Layout > Chart Title > Above Chart).
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:
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.
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.
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.
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.
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.
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.
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.
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.
Step 12: Now we'll add a chart title (Chart >Chart Layout > Chart Title > Title Above Chart).
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:
