1 00:00:00,000 --> 00:00:01,150 [MOZCON] 2 00:00:01,150 --> 00:00:06,040 Okay. Hi. You know what, I'm going to skip all the small talk 3 00:00:06,040 --> 00:00:10,410 because Richard Baxter totally inspired me last night. 4 00:00:10,410 --> 00:00:14,040 I was talking to him and I was like, God, I really want to do an intro. 5 00:00:14,040 --> 00:00:17,720 I really just like when get to it and he's like, Annie, don't do an intro. 6 00:00:17,720 --> 00:00:20,860 Nobody gives a shit. Just get to the good stuff. 7 00:00:20,860 --> 00:00:24,170 So that was very empowering. Thank you, Richard. All right. 8 00:00:24,170 --> 00:00:28,960 So what I'm going to do today is a live demo of some of my favorite 9 00:00:28,960 --> 00:00:35,360 tips and tricks in Excel, and all these tips and tricks, 10 00:00:35,360 --> 00:00:37,040 they're things that—they're very easy to do. 11 00:00:37,040 --> 00:00:39,440 I'm not going to be doing anything super advanced. 12 00:00:39,440 --> 00:00:42,610 We're not going to be using formulas. Nothing like that. 13 00:00:42,610 --> 00:00:46,140 It's an all stuff that if you're just a normal person, 14 00:00:46,140 --> 00:00:48,310 unlike some of the data whores we have here, 15 00:00:48,310 --> 00:00:53,440 and you're dealing with data all the time, but you bring it into Excel, 16 00:00:53,440 --> 00:00:57,020 and you're like, huh, what do I do now? I just want to make it sexy. 17 00:00:57,020 --> 00:01:01,540 I want to make it actionable, and it's going to be a fire hose. 18 00:01:01,540 --> 00:01:04,030 I'm going to move pretty fast through it, 19 00:01:04,030 --> 00:01:07,190 but at the end I'm going to share a link to a blog post. 20 00:01:07,190 --> 00:01:09,630 It's a monster blog post on the ??? blog. 21 00:01:09,630 --> 00:01:13,120 Don't waste your time going to it because it's password protected, 22 00:01:13,120 --> 00:01:15,940 because it's really not going to be valuable to anyone who's not here 23 00:01:15,940 --> 00:01:21,720 but in that blog post, I have written out all of the instructions for everything 24 00:01:21,720 --> 00:01:26,190 that I'm going to show you today, for both the PC and Mac, 25 00:01:26,190 --> 00:01:29,180 because their processes are quite different. 26 00:01:29,180 --> 00:01:31,310 Okay. So jumping right in. 27 00:01:31,310 --> 00:01:36,510 The first thing I want to show you is the Quick Access toolbar. 28 00:01:36,510 --> 00:01:42,230 Unfortunately, for you Mac users out there, this is a PC swim only option, 29 00:01:42,230 --> 00:01:46,420 but if you're on a PC, it's something that very few people use, 30 00:01:46,420 --> 00:01:50,440 and I think it's because it's just tucked up here away from everything, 31 00:01:50,440 --> 00:01:53,940 but I use this all the time. This is an efficiency tip. 32 00:01:53,940 --> 00:01:58,760 But the first thing I do is I move it to below the ribbon. 33 00:01:58,760 --> 00:02:02,720 Hey, because I like to have it closer to where I'm actually working. 34 00:02:02,720 --> 00:02:07,650 The other problem with this is the two options they put in there are totally lame, 35 00:02:07,650 --> 00:02:11,520 but you can add anything you want to this Quick Access toolbar, 36 00:02:11,520 --> 00:02:17,590 and in the blog post, I have a list of all the tools that I use all the time, 37 00:02:17,590 --> 00:02:20,180 and they are in my Quick Access toolbar. 38 00:02:20,180 --> 00:02:24,460 And I'll just show you really quickly how to add something. 39 00:02:24,460 --> 00:02:27,620 So I'm going to add gridlines because if anyone has ever read anything 40 00:02:27,620 --> 00:02:32,330 that I've written about Excel, you've probably got the idea, I abhor gridlines. 41 00:02:32,330 --> 00:02:36,310 I hate them, they're ugly, and they just add a bunch of static 42 00:02:36,310 --> 00:02:39,010 and noise to your data, and there's no need for them, 43 00:02:39,010 --> 00:02:41,990 and I'm going to show you other ways that will demarcate 44 00:02:41,990 --> 00:02:45,070 your data so you don't need gridlines, so just stop. 45 00:02:45,180 --> 00:02:50,570 So all you have to do is just right-click on whatever you want in the ribbon 46 00:02:50,570 --> 00:02:54,110 and choose Add to Quick Access Toolbar, and there it is. 47 00:02:54,110 --> 00:02:57,100 And with the gridlines, now I can just toggle them on and off. 48 00:02:57,100 --> 00:02:59,100 I never toggle them on. 49 00:02:59,100 --> 00:03:03,590 And I'm just also going to add this because I use this quite a bit, too. 50 00:03:03,980 --> 00:03:05,960 Okay. All right. Moving along. 51 00:03:05,960 --> 00:03:09,280 Next, I'm going to talk very, very briefly about table formatting. 52 00:03:09,480 --> 00:03:16,620 There are two things I do to any data set that I get, either that I download, 53 00:03:16,620 --> 00:03:20,820 export to CSV, or that people send me online. 54 00:03:20,820 --> 00:03:26,120 The first thing I do is that always remove the gridlines because I hate them. 55 00:03:26,120 --> 00:03:29,980 The second thing I do is I always format everything as a table. 56 00:03:29,980 --> 00:03:32,060 It makes your life so much easier. 57 00:03:32,060 --> 00:03:34,670 Now, I'm not going into a lot of detail for two reasons. 58 00:03:34,670 --> 00:03:41,290 One, I list all the steps in the blog post, but also I just last month did a blog post 59 00:03:41,290 --> 00:03:45,850 on Search Engine Land and it's very, very thorough 60 00:03:45,850 --> 00:03:48,690 and how to rock tables in Excel. 61 00:03:48,690 --> 00:03:55,150 Okay, so to format as a table, just click on any cell in your data set 62 00:03:55,150 --> 00:03:59,310 and go up here to Format as Table, and you have all these formatting options. 63 00:03:59,310 --> 00:04:03,610 So it actually makes your data pretty, but it's also highly functional. 64 00:04:03,610 --> 00:04:05,220 So I'm just going to choose this one here. 65 00:04:05,220 --> 00:04:07,910 My table has headers, so I'm just going to click OK. 66 00:04:07,910 --> 00:04:13,760 Now automatically we have taken our data from a raw static data set 67 00:04:13,760 --> 00:04:18,720 and made it both excel in form and function, 68 00:04:18,720 --> 00:04:21,250 because this is basically a database, okay? 69 00:04:21,250 --> 00:04:25,690 You have all these options up here to sort and filter. 70 00:04:25,690 --> 00:04:31,650 Excel detects that this is text and gives you text filters over here. 71 00:04:31,650 --> 00:04:36,450 It detects that this is numbers and it gives you a bunch of numbers filters. 72 00:04:36,460 --> 00:04:39,430 One thing to watch out for is this data here 73 00:04:39,430 --> 00:04:44,550 was pulled from Google Webmaster Tools, so you get a lot funky-monkey characters in there. 74 00:04:44,550 --> 00:04:50,970 And here you'll see that, although these are numbers, you get text filters options. 75 00:04:50,970 --> 00:04:55,170 That's because Excel is interpreting this as text, and you know that 76 00:04:55,170 --> 00:04:58,910 because the numbers are always right aligned, so if you ever see anything 77 00:04:58,910 --> 00:05:03,740 in a number column that's left aligned, that is text, 78 00:05:03,740 --> 00:05:05,060 even though it's a number. 79 00:05:05,060 --> 00:05:08,440 So it's set less-than sign, so just get rid of the less-than sign 80 00:05:08,440 --> 00:05:12,110 or get rid of that data altogether, and you'll be okay. 81 00:05:12,110 --> 00:05:16,950 All right, so next thing I do is I just select all of the columns, 82 00:05:17,140 --> 00:05:19,310 and if you just right-click on any of these dividers, 83 00:05:19,310 --> 00:05:22,190 it'll auto-expand the columns for you. 84 00:05:22,350 --> 00:05:24,730 A couple other little things. 85 00:05:24,730 --> 00:05:29,450 If you want to move around in a table, there are a couple of shortcuts. 86 00:05:29,450 --> 00:05:33,410 So if you want to move down to any edges of the data set, 87 00:05:33,410 --> 00:05:36,370 you just hold down Ctrl and hit the arrows. 88 00:05:36,370 --> 00:05:40,420 So Ctrl+Downarrow is going to take you to the bottom. 89 00:05:40,420 --> 00:05:45,040 Ctrl+Uparrow is going to take you back up, and right, left, so on and so forth. 90 00:05:45,040 --> 00:05:48,440 Now, if you want to actually select the whole column, 91 00:05:48,440 --> 00:05:51,680 especially if you have a large data set like you export 92 00:05:51,680 --> 00:05:53,680 from Screaming Fog or something like that, 93 00:05:53,680 --> 00:05:57,410 it's going to take a long time to click and scroll down. 94 00:05:57,410 --> 00:05:59,650 Just add Shift into the mix. 95 00:05:59,650 --> 00:06:04,750 So Ctrl+Shift+Downarrow, and that's going to select the entire column. 96 00:06:04,750 --> 00:06:09,490 Another quick thing is once you have a table 97 00:06:09,490 --> 00:06:14,220 if you just press Ctrl+A, Command+A on a Mac— 98 00:06:14,220 --> 00:06:17,400 I'm going to move this up here. That's going to select all the data. 99 00:06:17,400 --> 00:06:21,320 If you press Ctrl+A again, it's going to include the headings, 100 00:06:21,320 --> 00:06:25,140 and if you press Ctrl+A again, it's going to select your whole sheet. 101 00:06:25,140 --> 00:06:27,400 So just by knowing some of those keyboard shortcuts, 102 00:06:27,400 --> 00:06:30,440 you can just start flying through your data. 103 00:06:30,440 --> 00:06:33,640 Okay. So that's all I'm going to hit on with that. 104 00:06:33,640 --> 00:06:39,000 All right. The next thing I'm going to talk about is custom cell formatting. 105 00:06:39,080 --> 00:06:41,080 I'm going to move this over here. 106 00:06:41,080 --> 00:06:44,240 Okay, so if you have a date in a cell, 107 00:06:44,240 --> 00:06:47,820 let's just say, we have 7/26, 108 00:06:49,600 --> 00:06:53,270 most people know that you can change the formatting. 109 00:06:53,400 --> 00:06:56,450 Now, there is one little tip that I'm going to give you. 110 00:06:56,450 --> 00:06:59,240 You can use this all throughout. 111 00:06:59,240 --> 00:07:03,360 In whatever you're doing in Excel, if you press Ctrl+1, 112 00:07:03,360 --> 00:07:05,360 it's going to pull up formatting options. 113 00:07:05,360 --> 00:07:07,890 So if you're working with a cell, 114 00:07:07,890 --> 00:07:09,900 then it's going to pull up formatting options for the cell. 115 00:07:09,950 --> 00:07:13,290 If you're working with a chart and you have the whole chart selected, 116 00:07:13,290 --> 00:07:15,050 you're going to get formatting options for that. 117 00:07:15,050 --> 00:07:20,660 If you have the axis selected, it's going to pull up formatting options for that. 118 00:07:20,660 --> 00:07:24,360 So whenever you want format, just select it and press Ctrl+1. 119 00:07:24,360 --> 00:07:26,870 Okay, so I'm going to press Ctrl+1 and— 120 00:07:26,870 --> 00:07:28,870 Woops, that didn't work. 121 00:07:37,980 --> 00:07:47,640 Okay. Working with a new laptop. All right. So I just have to show it live then. 122 00:07:47,640 --> 00:07:52,510 So with this, I don't know why it did that. 123 00:07:52,510 --> 00:08:01,110 But if you want to reformat date, you have these set formats, 124 00:08:01,110 --> 00:08:05,080 but where the magic happens is this custom formatting. 125 00:08:05,080 --> 00:08:09,940 So just pretend that this like formatted date up there. 126 00:08:09,940 --> 00:08:13,110 And here's how it works. 127 00:08:13,110 --> 00:08:16,350 So if you have one M, I'll just start with month. 128 00:08:16,350 --> 00:08:18,720 Let's say the month of September--If you have one M, 129 00:08:18,720 --> 00:08:20,010 that's going to give you 9. 130 00:08:20,010 --> 00:08:22,690 If you put in 2 Ms, that's going to give you 09. 131 00:08:22,690 --> 00:08:25,630 So it's going to give you a leading 0 if it's less than 10. 132 00:08:25,630 --> 00:08:29,500 If you put in 3 Ms, it's going to give you Sep. 133 00:08:29,500 --> 00:08:32,130 If you put in 4 Ms, it's going to spell out September. 134 00:08:32,130 --> 00:08:36,659 Same with day. If you put in one D, it's going to give you just the number. 135 00:08:36,659 --> 00:08:40,210 If you put in 2, it's going to give you the leading 0 if it's less than 10. 136 00:08:40,210 --> 00:08:44,260 If you put in 3, it's going to give you the first 3 letters of the day of the week. 137 00:08:44,260 --> 00:08:50,370 So you could put January 27, 2014, and it's going to tell you, oh, that's Thursday. 138 00:08:50,370 --> 00:08:52,370 So it's going to give you Thu. 139 00:08:52,370 --> 00:08:54,940 If you put in 4 Ds, it's going to spell out Thursday. 140 00:08:55,940 --> 00:09:02,890 I'll just pull up this other file to show you what it looks like. 141 00:09:05,890 --> 00:09:10,800 Okay. So you can see it in the blog post. This just got completely borked. 142 00:09:10,800 --> 00:09:12,800 So I'm just going to move on. 143 00:09:12,800 --> 00:09:19,340 In the blog post, I also show some really advanced options. 144 00:09:19,340 --> 00:09:24,390 This sucks. It's not working. Okay, all right. Moving on. 145 00:09:24,390 --> 00:09:31,340 I will say this: You can also format by color, so you can say, 146 00:09:31,340 --> 00:09:37,120 okay, I want positive numbers to be green, 147 00:09:37,120 --> 00:09:40,320 I want negative numbers to be red, and 0 to be black. 148 00:09:40,320 --> 00:09:44,030 I do this with like Google Webmaster Tool data where I just have 149 00:09:44,030 --> 00:09:47,240 a bunch of positives and negatives like just having a minus sign 150 00:09:47,240 --> 00:09:49,730 doesn't really make the data jump out at me. 151 00:09:49,730 --> 00:09:56,730 So what I'll do is, you just do Ctrl+1, and if you can see it, 152 00:09:57,730 --> 00:10:04,500 you have these color options, and I provide links in the blog post for you to learn more, 153 00:10:04,500 --> 00:10:08,810 but you could put green and brackets, and then the next part of it 154 00:10:08,810 --> 00:10:12,750 is you just put in however you want the number formatted. 155 00:10:12,750 --> 00:10:16,080 So if it's just a straight number with no decimals, you just put 0. 156 00:10:16,080 --> 00:10:19,080 If you want a decimal, you put 0.0. 157 00:10:19,080 --> 00:10:23,750 if you want a percentage, you put 0%, so on and so forth, 158 00:10:23,750 --> 00:10:26,800 and then you separate them with a semicolon. 159 00:10:26,800 --> 00:10:30,880 So the first number is a positive number, semicolon, negative, 160 00:10:30,880 --> 00:10:33,830 however you want to format the negative number, semicolon, 161 00:10:33,830 --> 00:10:36,900 and then 0 if you have that option. 162 00:10:36,900 --> 00:10:39,440 And you can get really advanced with it. 163 00:10:39,440 --> 00:10:44,690 I wish this had worked, but you can even say 164 00:10:44,690 --> 00:10:51,930 if the number is less than 1000, I want to have the word Low in there, 165 00:10:51,930 --> 00:10:57,910 and this is how you're going to format it, and if it's greater than 2000, 166 00:10:57,910 --> 00:11:02,160 I want the word High and then some leading spaces and then 0, 167 00:11:02,160 --> 00:11:06,980 and to get these leading spaces, where you'll have Low on the left side 168 00:11:06,980 --> 00:11:10,880 and the number on the right, you just put * and then whatever you want 169 00:11:10,880 --> 00:11:13,060 repeated for the full width of the column. 170 00:11:13,060 --> 00:11:15,200 In this case, I have a space. 171 00:11:15,200 --> 00:11:22,190 So it's * and then a space and then whatever it is for 172 00:11:22,190 --> 00:11:27,350 whatever is not less than 1000 or greater than 2000. 173 00:11:27,350 --> 00:11:30,170 Okay. Moving on. All right. 174 00:11:30,170 --> 00:11:32,590 Next, I'm going to talk about links and tabs. 175 00:11:32,770 --> 00:11:36,050 So most people know that you can add a link in Excel 176 00:11:36,050 --> 00:11:38,690 to an external document, but what they don't know 177 00:11:38,690 --> 00:11:43,920 is that you can also add links to other tabs within your document. 178 00:11:44,100 --> 00:11:47,860 So if you just press Ctrl+K, Command+K on the Mac, 179 00:11:47,860 --> 00:11:50,230 and choose Place in the Document, 180 00:11:50,230 --> 00:11:53,100 then you see all the tabs that I have for the document, 181 00:11:53,100 --> 00:11:56,660 and you just either double-click or click it and click OK. 182 00:11:57,040 --> 00:11:59,750 Then that's going to give you that link, 183 00:11:59,750 --> 00:12:02,150 and when you click on it, it's going to open that tab. 184 00:12:02,340 --> 00:12:09,400 And then also with your tabs, you can also add colors to them. 185 00:12:09,400 --> 00:12:12,770 So if you just right-click, you can choose a tab color. 186 00:12:12,770 --> 00:12:14,350 You might be looking at this and going, 187 00:12:14,350 --> 00:12:18,080 huh, Annie, what kind of lame sandwich are you feeding us, like that's nothing. 188 00:12:18,080 --> 00:12:22,040 But here's what you can do with just those simple principles. 189 00:12:22,040 --> 00:12:26,300 So here is a screen shot of a dashboard I created 190 00:12:26,300 --> 00:12:29,930 and with the table of contents over here, 191 00:12:29,930 --> 00:12:33,400 I had to do a screen shot because I had to blur out client data 192 00:12:33,400 --> 00:12:38,220 but over here on the left, these are all links to the individual sheets 193 00:12:38,220 --> 00:12:44,580 and even the action items and the wins and the links below the charts, 194 00:12:44,580 --> 00:12:47,230 those all are links to those individual sheets. 195 00:12:47,360 --> 00:12:49,920 So that usually like the sea levels, 196 00:12:49,920 --> 00:12:52,610 they're just mostly going to look at the dashboard 197 00:12:52,890 --> 00:12:58,590 and see any indicators that they need to check into and then move on, 198 00:12:58,650 --> 00:13:00,500 and then down the food chain, 199 00:13:00,500 --> 00:13:03,200 they're going to be more interested in the minutiae, 200 00:13:03,340 --> 00:13:07,210 and then this dashboard actually kind of goes on ad nauseam 201 00:13:07,210 --> 00:13:11,760 and represents all the different departments of services, 202 00:13:11,760 --> 00:13:16,810 and then what I did was I color-coordinated the tabs with the different departments. 203 00:13:16,810 --> 00:13:19,710 So we had consulting, campaign management, content production, 204 00:13:19,710 --> 00:13:25,960 I mean content promotion and production, and just with these two simple things, 205 00:13:25,960 --> 00:13:31,340 you can create some really sexy data. 206 00:13:32,210 --> 00:13:34,320 I mean, basically, what you're doing is 207 00:13:34,320 --> 00:13:36,980 putting in stilettos and making it work the pool here. 208 00:13:37,050 --> 00:13:41,760 So next up I'm going to talk about charts. 209 00:13:41,760 --> 00:13:47,480 So, what I'm going to do here is I'm going to do a combination chart 210 00:13:47,480 --> 00:13:50,510 where I'm going to combine visits and bounce rate. 211 00:13:50,510 --> 00:13:54,350 Now it's easy to combine two metrics that are very similar, 212 00:13:54,350 --> 00:13:57,730 but visits and bounce rate—you have visits as high as 65,000, 213 00:13:57,730 --> 00:14:00,040 bounce rate is less than 1, because it's a decimal. 214 00:14:00,040 --> 00:14:03,200 So what we're going to do is we're going to hold down the Ctrl key, 215 00:14:03,200 --> 00:14:09,320 select bounce rate, and then go to Insert, Column. 216 00:14:09,320 --> 00:14:12,910 Just a basic column chart over here. 217 00:14:12,910 --> 00:14:20,230 Drag it down, and it's going to look really ugly before it gets sexy. 218 00:14:20,230 --> 00:14:22,230 But trust me, it'll be awesome. 219 00:14:22,230 --> 00:14:24,330 So the first thing we're going to do, 220 00:14:24,330 --> 00:14:26,930 you have all these formatting options up here. 221 00:14:26,930 --> 00:14:29,760 I'm kind of partial to this one. 222 00:14:29,760 --> 00:14:32,750 It adds a little bit of swagger without getting too distracting. 223 00:14:32,750 --> 00:14:37,570 And then next, we're going to select the bounce rate series— 224 00:14:37,570 --> 00:14:41,730 this is called a data series—and clean that up. 225 00:14:41,730 --> 00:14:45,930 Now, if you have difficulty selecting the series because it's so small, 226 00:14:45,930 --> 00:14:51,320 you can always go up here to Chart Tools and check out layout or format, 227 00:14:51,320 --> 00:14:54,850 and they have this little option up here in upper left-hand corner 228 00:14:54,850 --> 00:14:57,700 where you can select individual series. 229 00:14:57,700 --> 00:15:01,330 So I've had to do that—I used to actually dial up one of the numbers 230 00:15:01,330 --> 00:15:05,210 so I could grab it and then dial it back down, 231 00:15:05,210 --> 00:15:07,240 but this is a lot easier. 232 00:15:07,620 --> 00:15:09,710 So once you have it up again, 233 00:15:09,710 --> 00:15:14,260 I 'm going to press Ctrl+1 to get to the formatting options, 234 00:15:14,410 --> 00:15:17,010 and I what I'm going to do is I'm going to take bounce rate 235 00:15:17,010 --> 00:15:19,500 and I'm going to put it on a secondary access. 236 00:15:19,500 --> 00:15:24,160 Now, it's going to look really, really ugly and hideous for a second, 237 00:15:24,160 --> 00:15:27,500 but then what we're going to do is we're going to go the Design tab, 238 00:15:27,500 --> 00:15:34,430 change the chart type, and I'm going to make this a line graph with markers. 239 00:15:34,430 --> 00:15:40,340 Okay. So now what I tend to do, and I think that this is a good practice to follow. 240 00:15:40,340 --> 00:15:44,680 The smaller number, I always put on the secondary access. 241 00:15:44,680 --> 00:15:48,560 So I have the column chart, and then the bigger numbers are in the column charts, 242 00:15:48,560 --> 00:15:52,240 and that's represented on the primary access, and then the smaller number, 243 00:15:52,240 --> 00:15:57,150 I put off to the side, and I also like to use a line chart for smaller numbers 244 00:15:57,150 --> 00:16:00,960 and the columns for the larger numbers. 245 00:16:00,960 --> 00:16:04,460 So I don't like how thick these lines are, 246 00:16:04,460 --> 00:16:10,470 so again, I just click the series, did Ctrl+1, and we're just going to adjust that. 247 00:16:10,470 --> 00:16:13,950 So I'm going to dial back the width of that line. 248 00:16:13,950 --> 00:16:16,070 I tend to like about 2.75. 249 00:16:16,070 --> 00:16:20,250 You can change the line color. You can mess with marker options. 250 00:16:20,250 --> 00:16:22,700 You can get all kinds of slutty with it. 251 00:16:22,700 --> 00:16:28,310 So the next thing I'm going to show you is, and this is cool, 252 00:16:28,310 --> 00:16:32,150 I just discovered this like probably about 4 months ago. 253 00:16:32,150 --> 00:16:38,400 Has anyone ever tried to add a data series to a chart after you've already created it? 254 00:16:39,420 --> 00:16:42,450 It's a total pain like every time I go to do it I'm like, 255 00:16:42,450 --> 00:16:44,230 wait, what am I supposed to do here? 256 00:16:44,450 --> 00:16:47,300 Like you're supposed to do right-click, select data, and go in here, and 257 00:16:47,470 --> 00:16:49,650 it's just madness and ugly. 258 00:16:49,650 --> 00:16:52,900 But there's a little shortcut that's awesome. 259 00:16:52,900 --> 00:16:55,450 So what we're going to do, the reason I'm adding this 260 00:16:55,450 --> 00:17:00,530 is when I show data, like I like to show visits and bounce rate 261 00:17:00,600 --> 00:17:02,600 vis-a-vis together on the same chart, 262 00:17:02,600 --> 00:17:05,310 and other things like revenue and conversion rates, 263 00:17:05,430 --> 00:17:10,569 because you can just see how they coexist in the little data set there. 264 00:17:10,569 --> 00:17:16,359 But if you're showing this to someone who doesn't live and breathe data, 265 00:17:16,359 --> 00:17:21,329 they can look at this and go, okay, I get that Charlie Sheen costume. 266 00:17:21,329 --> 00:17:23,460 That's probably not good because that's really high, 267 00:17:23,460 --> 00:17:26,030 but what's an ideal bounce rate? 268 00:17:26,030 --> 00:17:30,570 So I usually vacillate between 30% and 40%. 269 00:17:30,570 --> 00:17:36,850 So what we're going to do is we're going to add this column to the chart very easily. 270 00:17:36,850 --> 00:17:43,080 All I'm going to do is select it, copy, select the chart, and just paste it in. 271 00:17:43,080 --> 00:17:46,360 Ridiculous, right? Okay. 272 00:17:46,360 --> 00:17:51,860 So then what we're going to do is, obviously, I don't like that formatting, 273 00:17:51,860 --> 00:17:55,180 and it's very hard to select that one, so I'm going to go up here 274 00:17:55,180 --> 00:17:58,520 and choose a target bounce rate. 275 00:18:01,520 --> 00:18:06,630 One little tip with this is you have to deselect before this works, 276 00:18:06,630 --> 00:18:09,460 before you can press the Ctrl+1. 277 00:18:09,460 --> 00:18:11,120 Okay. So I'm going to select this again. 278 00:18:11,120 --> 00:18:14,250 You get these chart tools anytime click the chart. 279 00:18:14,250 --> 00:18:21,460 So I'm going to go to Layout, target bounce rate, Ctrl+1, 280 00:18:21,460 --> 00:18:26,210 and I tend to like these lines. 281 00:18:26,210 --> 00:18:31,570 I definitely like them nice and thin,and I make them dashed. 282 00:18:31,570 --> 00:18:33,850 That's just my personal preference, 283 00:18:33,850 --> 00:18:39,440 and I don't really want it to be that bright green. 284 00:18:39,440 --> 00:18:42,520 So I'm just going to make a little more subtle with gray. 285 00:18:42,520 --> 00:18:47,270 Oops! Go back in, and I want to turn off the markers. 286 00:18:47,270 --> 00:18:49,930 So I go to Marker Options, None. 287 00:18:51,410 --> 00:19:02,260 And you can see here, now we have this—it's over here in the legend. All is good. 288 00:19:02,260 --> 00:19:09,540 Now, what you want always do with a chart is you want to add a title to it, 289 00:19:09,540 --> 00:19:11,500 and you want to make it as descriptive as possible. 290 00:19:11,500 --> 00:19:14,830 The way you get to that, you just go to Layout, Chart Title. 291 00:19:14,830 --> 00:19:20,360 I like to do it above the chart, and I usually make it a lot more specific, 292 00:19:20,360 --> 00:19:23,990 but I'm just going to say, for the interest of time, Top Keywords. 293 00:19:23,990 --> 00:19:27,740 Now, what I'm going to do is just kind of clean things up a little bit. 294 00:19:27,740 --> 00:19:31,870 My personal preference is I don't like the legend to be off to the right. 295 00:19:31,870 --> 00:19:35,110 I think it just kind of makes everything a little off kilter. 296 00:19:35,560 --> 00:19:38,540 So I generally move it to the bottom, 297 00:19:38,540 --> 00:19:41,570 but we have a thick and chewy cookie down here. 298 00:19:41,580 --> 00:19:43,700 So I'm not going to move it to the bottom. 299 00:19:43,700 --> 00:19:45,280 I'm actually going to move it to the top. 300 00:19:45,280 --> 00:19:48,680 So again, Ctrl+1, move this to the top, 301 00:19:48,680 --> 00:19:55,980 and I also like to bounce, dial up the font size a little bit. 302 00:19:55,980 --> 00:20:02,000 Now one other thing is, because I really want the important data to stand out, 303 00:20:02,000 --> 00:20:06,530 I don't like a lot of noise in my data at all. 304 00:20:06,530 --> 00:20:11,610 So having these decimals is definitely noisy and is not necessary. 305 00:20:11,610 --> 00:20:15,840 The reason why we have them is there are decimals in the original data set. 306 00:20:15,840 --> 00:20:18,840 If you remove the decimals from the original data set using 307 00:20:18,840 --> 00:20:22,590 the number formatting options, then it won't be in your chart, 308 00:20:22,590 --> 00:20:24,590 but we didn't do that. 309 00:20:24,590 --> 00:20:28,570 So what we're going to do is just go to Number and say Decimal Places, 0. 310 00:20:28,570 --> 00:20:30,570 I don't want any decimals. 311 00:20:30,570 --> 00:20:33,830 And then another cool thing you can do is, like in this case, 312 00:20:33,830 --> 00:20:36,750 you don't really need every 10%. 313 00:20:36,750 --> 00:20:39,600 You don't need a major marker at every 10%. 314 00:20:39,600 --> 00:20:41,600 It gets a little noisy. 315 00:20:41,600 --> 00:20:45,780 This is personal preference, but I'm just going to air that out a little bit. 316 00:20:45,780 --> 00:20:48,580 So you just go to Major Unit under Access Options, 317 00:20:48,580 --> 00:20:51,690 and I'm going to bump that up to 20%, 318 00:20:51,690 --> 00:20:54,970 and you'll see it just going to air things out. 319 00:20:54,970 --> 00:20:56,970 This is especially important 320 00:20:56,970 --> 00:20:59,920 if you're charting out data like over a period of a month 321 00:20:59,920 --> 00:21:03,670 and you have every day of the month along the x axis, 322 00:21:03,670 --> 00:21:05,950 that gets ridiculously noisy. 323 00:21:05,950 --> 00:21:12,580 So you just select it, select the axis down there, and do Ctrl+1, 324 00:21:12,580 --> 00:21:14,220 and I just do every other day. 325 00:21:14,220 --> 00:21:17,850 So there I'll say the major marker is 1, and I bump it up 2. 326 00:21:17,850 --> 00:21:23,190 So these things go long way toward just kind of cleaning things up for you, 327 00:21:23,190 --> 00:21:29,290 and then what I do is I don't like to make clients read, because they don't. 328 00:21:29,290 --> 00:21:34,130 I was an in house. I'd get long reports. I wouldn't read them. 329 00:21:34,130 --> 00:21:38,290 I would look at what would actually save my butt 330 00:21:38,290 --> 00:21:42,710 the next time I go into a meeting with the C levels 331 00:21:42,710 --> 00:21:45,370 and pull those out, take screen shots. 332 00:21:46,370 --> 00:21:54,230 So what I like to do is just take the things the that I really want to point out or explain, 333 00:21:54,230 --> 00:22:01,180 and I'll use these—this is doing this because I created these boxes ahead of time— 334 00:22:01,180 --> 00:22:04,800 and one little tip here, when you create these boxes, I'll show you how. 335 00:22:04,800 --> 00:22:07,140 Make sure you create them outside the chart. 336 00:22:07,140 --> 00:22:11,210 If you create them inside the chart, you're going to be constrained 337 00:22:11,210 --> 00:22:13,660 to stay within the perimeter of the chart. 338 00:22:13,660 --> 00:22:19,490 Okay. So you just get to these by Insert and Shapes. 339 00:22:19,490 --> 00:22:24,440 One thing most people don't know is all of these shapes, you can add text to them. 340 00:22:24,440 --> 00:22:28,430 So you just drag them out and start typing and you can add text, 341 00:22:28,430 --> 00:22:30,840 and then you have all these formatting options. 342 00:22:30,840 --> 00:22:35,450 Again, because I don't like to draw attention away from the data, 343 00:22:35,450 --> 00:22:38,000 I try to make them as subtle as possible. 344 00:22:38,000 --> 00:22:46,420 So I really like this one here. It's this option in here. 345 00:22:48,420 --> 00:22:53,760 One of these down here. This one. I don't know if you can see that. 346 00:22:53,760 --> 00:23:00,190 Because it's very, very subtle, but you can use whatever you want. 347 00:23:00,190 --> 00:23:05,330 Okay. So one other quick little tip. All right. 348 00:23:05,330 --> 00:23:08,920 So let's say you have a data set over here. 349 00:23:08,920 --> 00:23:11,470 One thing that you can do, and this a hack. 350 00:23:11,470 --> 00:23:14,910 This isn't something that Excel built in, although it should, 351 00:23:14,910 --> 00:23:21,840 you can make it look like your title updates automatically when you add data, 352 00:23:21,840 --> 00:23:25,790 and that's one more benefit to formatting as a table 353 00:23:25,790 --> 00:23:29,070 because if you add new data to a table, 354 00:23:29,070 --> 00:23:35,420 then it automatically absorbs it into the table, and when I put the number in here, 355 00:23:35,420 --> 00:23:38,100 you'll notice it's 70,000 right now. 356 00:23:38,100 --> 00:23:41,110 When I enter it, it updates up here. 357 00:23:41,110 --> 00:23:45,900 So the way that you do that is you just go ahead 358 00:23:45,900 --> 00:23:48,040 and—I'm going to hold down the Shift key 359 00:23:48,040 --> 00:23:51,070 to keep it constrained when I drag it down, 360 00:23:51,070 --> 00:23:54,590 but this is just a cell where I wrote Revenue for June, 361 00:23:54,590 --> 00:23:58,960 and then this one here just references this cell here. 362 00:23:58,960 --> 00:24:04,430 So you just put =AH36, because that's where the total is, 363 00:24:04,430 --> 00:24:09,790 and then what I did was, I just dragged the chart over top 364 00:24:11,790 --> 00:24:16,020 and did Ctrl+1 and just set it to No Fill, 365 00:24:16,020 --> 00:24:19,840 because it's usually a white fill for the chart anyway 366 00:24:19,840 --> 00:24:22,700 and your background is white, so no one's the wiser, 367 00:24:22,700 --> 00:24:24,940 and that's just being sexy. 368 00:24:24,940 --> 00:24:27,930 Okay. Moving on to conditional formatting. 369 00:24:27,930 --> 00:24:32,480 I love conditional formatting. I'm just going to show you a few things here. 370 00:24:32,480 --> 00:24:37,020 The first thing is, this data here is from Screaming Frog, 371 00:24:37,020 --> 00:24:40,050 and one of the metrics that they have is a hash value. 372 00:24:40,050 --> 00:24:42,050 I'm not going to go into the details, 373 00:24:42,050 --> 00:24:45,190 but 2 pages with the same hash value is kind of like a 374 00:24:45,190 --> 00:24:47,170 social security number for web pages. 375 00:24:47,170 --> 00:24:50,330 If they have the same hash value, it's most likely the same page 376 00:24:50,350 --> 00:24:52,600 or very close to identical, 377 00:24:52,600 --> 00:24:56,020 and Google and Bing would most likely identify it as duplicate content. 378 00:24:56,090 --> 00:25:02,760 So what I'll do is Ctrl+Shift+Downarrow, and you can find conditional formatting 379 00:25:02,760 --> 00:25:07,230 under the Home tab, and you just go to Conditional Formatting, 380 00:25:07,230 --> 00:25:10,490 Highlight Cells Rules, and we're going to choose Duplicate Values. 381 00:25:10,490 --> 00:25:14,110 Now usually I use yellow highlighting, but just for the sake of time, 382 00:25:14,110 --> 00:25:18,930 I'm just going to stick with that, and what that does is it highlights 383 00:25:18,930 --> 00:25:24,090 all of the duplicates. Then what you can do is use the sorting function. 384 00:25:24,090 --> 00:25:26,760 Sort A to Z, which is just alphabetically. 385 00:25:26,760 --> 00:25:33,890 Actually, first what I'm going to do is sort by color, and what that does 386 00:25:33,890 --> 00:25:38,910 is it moves all of the color cells that we just formatted up to the top. 387 00:25:38,910 --> 00:25:44,620 So they float to the top, and then we're going to do a custom sort here, 388 00:25:45,620 --> 00:25:53,520 and we'll say sort by color first, then we add a level and set this to hash, 389 00:25:53,520 --> 00:26:01,920 values A to Z, and now it will—first, all the pink cells will move to the top, 390 00:26:01,920 --> 00:26:06,740 and then Excel will put those in alphabetical order so you can actually 391 00:26:06,740 --> 00:26:09,470 see which 2 pages belong together. 392 00:26:09,470 --> 00:26:14,320 Okay. All right. Another thing you can do with conditional formatting are data bars. 393 00:26:14,320 --> 00:26:18,650 I love data bars; so let's say you're not going to chart something out, 394 00:26:18,650 --> 00:26:21,530 because it's just too large of a data set. 395 00:26:21,530 --> 00:26:29,700 Data bars, you get to them by going Conditional Formatting, Data Bars. 396 00:26:29,700 --> 00:26:34,970 And it's just a really easy way to kind of make your data stand out 397 00:26:34,970 --> 00:26:37,220 for like the larger values, and you can do this— 398 00:26:37,220 --> 00:26:42,460 I do this a lot with Google Analytics data, where I'll just have some data bars, 399 00:26:42,460 --> 00:26:47,360 and then you can kind of eye it out and see which values are larger and smaller, 400 00:26:47,360 --> 00:26:50,480 and if you have outliers, there are special settings 401 00:26:50,480 --> 00:26:53,360 where you can get rid of the outliers. 402 00:26:54,030 --> 00:26:58,570 One other thing that I'll just mention is— 403 00:26:58,570 --> 00:27:02,380 okay, so with number formatting, 404 00:27:02,480 --> 00:27:07,000 what I like to do is if the number is either positive or negative 405 00:27:07,000 --> 00:27:11,360 and it doesn't rely on something else, I like to use number formatting. 406 00:27:11,360 --> 00:27:15,910 Remember, you go into to Ctrl+1 and set those colors. 407 00:27:15,910 --> 00:27:21,310 Again, there is a link in the blog post where you can learn how to do that, 408 00:27:21,310 --> 00:27:25,590 and you can use conditional formatting for that, and I used to, 409 00:27:25,590 --> 00:27:28,160 but conditional formatting is a processing hog. 410 00:27:28,160 --> 00:27:31,600 So if you can use number formatting, I would recommend that, 411 00:27:31,600 --> 00:27:37,260 but if you want—one thing that conditional formatting is good for 412 00:27:37,260 --> 00:27:41,450 is, let's say you want to compare 2 values and you just want to show 413 00:27:41,450 --> 00:27:46,460 if something has gone up or gone down; that you can use conditional formatting 414 00:27:46,460 --> 00:27:49,110 for, to make the numbers red and green. 415 00:27:49,110 --> 00:27:55,100 I use this for rank reports, for analytics data, for Webmaster Tool data, 416 00:27:55,100 --> 00:28:00,060 and I added in this extra column, but you can see here, 417 00:28:00,060 --> 00:28:04,030 we have the previous average position and a current average position. 418 00:28:04,030 --> 00:28:08,090 So in this case, we want a smaller number since we're dealing with rankings, 419 00:28:08,090 --> 00:28:11,090 so what we're going to do is we're going to go into conditional formatting. 420 00:28:11,090 --> 00:28:14,010 Now, you can do this with a formula, but like I said, 421 00:28:14,010 --> 00:28:16,010 I'm going to keep all of this super simple. 422 00:28:16,010 --> 00:28:19,940 So you can go to Highlight Cells Rules as a Greater Than. 423 00:28:19,940 --> 00:28:27,280 So we're going to format this number here to be red 424 00:28:27,280 --> 00:28:29,480 if it's greater than the previous number. 425 00:28:29,480 --> 00:28:32,530 So what we do here is we just reference this cell, 426 00:28:32,530 --> 00:28:35,750 and we're going to unlock the four. 427 00:28:35,750 --> 00:28:40,200 I don't have time to go in to all the details of why we're doing that, 428 00:28:40,200 --> 00:28:44,600 but—and this back button is not working. 429 00:28:57,010 --> 00:28:59,010 Okay. Let me try this. 430 00:29:10,010 --> 00:29:14,960 Okay. Select that cell. Get rid of the dollar sign. 431 00:29:16,960 --> 00:29:21,350 And we're going to make the text red. 432 00:29:23,350 --> 00:29:28,560 Then we're going to go back in. If it's less than, that's a positive thing. 433 00:29:28,560 --> 00:29:31,170 We're going to reference that cell again. 434 00:29:34,170 --> 00:29:36,570 Get rid of the dollar sign. 435 00:29:49,570 --> 00:29:55,260 And this time, I'm going to go back, edit the rule, 436 00:29:55,260 --> 00:29:59,460 and format it as green, because that's positive. 437 00:30:21,460 --> 00:30:23,620 Okay. This isn't working. All right. Scratch that. 438 00:30:23,620 --> 00:30:29,400 I go through the details in the blog post. I don't know what's going on with that. 439 00:30:30,400 --> 00:30:33,410 All right, the last thing I'm going to touch on is data validation, 440 00:30:33,410 --> 00:30:40,160 but I'll just say, going back to that, what will happen is once you set all of that up, 441 00:30:40,160 --> 00:30:44,050 then you can either use this little format painter up here 442 00:30:44,050 --> 00:30:49,020 and apply it to the rest of the column, or you can drag the column down 443 00:30:49,020 --> 00:30:53,130 and just choose Format Only. And again, I go through all of the steps. 444 00:30:53,130 --> 00:30:57,780 I don't know what's wrong with this keyboard or what's wrong with me. 445 00:30:57,780 --> 00:31:01,580 Okay, the last thing I want to touch on is data validation. 446 00:31:01,580 --> 00:31:08,350 So data validation gives you the ability to put dropdowns in your data, 447 00:31:08,350 --> 00:31:13,150 so that, let's say you're tracking link building, and other people 448 00:31:13,150 --> 00:31:15,820 are entering data into your Excel sheet, 449 00:31:15,820 --> 00:31:19,110 and if they add an extra space or they misspell, 450 00:31:19,180 --> 00:31:22,910 like the link type or whatever, when you import that to Raven, 451 00:31:22,910 --> 00:31:25,770 if you use Raven, it's really going to muck up your records, 452 00:31:25,770 --> 00:31:27,780 and then someone's going to have to go through there 453 00:31:27,780 --> 00:31:29,940 and figure out why these are coming up 454 00:31:29,940 --> 00:31:32,450 as 2 separate link types even though they're the same. 455 00:31:32,450 --> 00:31:36,460 So one way to deal with that is to add some data validation, 456 00:31:36,460 --> 00:31:38,580 which basically restricts their options. 457 00:31:38,580 --> 00:31:42,050 Okay. So the default is Any Value. 458 00:31:42,050 --> 00:31:46,750 We're going to change that to List, and then off to the side here, 459 00:31:46,750 --> 00:31:52,290 usually with Excel, I'll just put the list that I want to use for the 460 00:31:52,290 --> 00:31:56,240 data validation way off to the side where no one will see it, 461 00:31:56,240 --> 00:31:59,580 and then reference it here, and then you'll see, 462 00:31:59,580 --> 00:32:03,750 we have a dropdown here with all of the options. 463 00:32:03,750 --> 00:32:05,870 So you can just choose from those. 464 00:32:05,870 --> 00:32:11,440 Okay. So that's all I have for the demonstration. 465 00:32:11,440 --> 00:32:14,330 Sorry about some of the mixups there. 466 00:32:14,330 --> 00:32:29,760 If you want to learn more, you can go to bit.ly/annie-mozcon 467 00:32:29,760 --> 00:32:38,340 So bit.ly/annie-mozcon, and the password is mozcon 468 00:32:38,340 --> 00:32:42,380 When you get in there, again, you'll see all of the instructions, 469 00:32:42,380 --> 00:32:47,240 and I also have a ton of links, everything that I show you. 470 00:32:47,240 --> 00:32:51,500 I give you links to learn more for how to do it in PC 471 00:32:51,500 --> 00:32:56,960 and more for how to do it in Excel, a link to my Search Engine Land post, 472 00:32:56,960 --> 00:33:02,130 and also a link to download the Excel file. 473 00:33:02,130 --> 00:33:05,030 Okay. Go. 474 00:33:12,030 --> 00:33:17,300 >>You're my hero right now, because not only are you going over all this Excel stuff, 475 00:33:17,300 --> 00:33:21,040 which I love, but you can talk about Excel 476 00:33:21,040 --> 00:33:24,580 and put down stilettos and working down the pole 477 00:33:24,580 --> 00:33:28,900 in the same conversation, or as Ian tweeted, working the pool, 478 00:33:28,900 --> 00:33:30,900 which could be interesting. 479 00:33:30,900 --> 00:33:34,870 I don't know what that means, but still trying to process that one. 480 00:33:34,870 --> 00:33:36,870 So we're going to do some Q&A. 481 00:33:36,870 --> 00:33:40,590 You guys, anyone have some questions or raise your hands also. 482 00:33:40,590 --> 00:33:50,690 Rand's got one. I can't see otherwise, but I've got tons, by the way. >>Awesome. 483 00:33:50,690 --> 00:33:53,640 We're going to have— I added those dashboards just for you. 484 00:33:56,640 --> 00:34:02,110 >>Annie, can you maybe list for us the top 3 or 4, or even just a 485 00:34:02,110 --> 00:34:06,280 a couple of the reports that you build in Excel for every client. 486 00:34:06,920 --> 00:34:10,670 Why they work so well, like what inspires about that, because I'm really curious 487 00:34:10,670 --> 00:34:15,679 about like the specific—I show rankings next to traffic, 488 00:34:15,679 --> 00:34:18,770 or I show links next to rankings, or whatever it is. 489 00:34:18,770 --> 00:34:23,790 >>Right, actually, that' s a really good question, and I was afraid 490 00:34:23,790 --> 00:34:27,030 I was going to run out of time, so didn't show this, 491 00:34:27,030 --> 00:34:31,280 but can we put the computer back up? 492 00:34:32,280 --> 00:34:44,370 So with this dashboard—whenever this goes back up—these are what I added for Laura, 493 00:34:44,370 --> 00:34:47,380 because she told that she was dealing with dashboards. 494 00:34:47,380 --> 00:34:52,960 And here are some of the reports that—like these are some of my favorites. 495 00:34:52,960 --> 00:34:57,550 So one thing that I'll do that I really, really like, let's say 496 00:34:57,550 --> 00:34:59,790 you're doing link building over a period of time, 497 00:34:59,790 --> 00:35:02,680 and I didn't touch on pivot tables, but I love pivot tables. 498 00:35:02,680 --> 00:35:05,380 I wrote a blog post just a couple of weeks ago 499 00:35:05,380 --> 00:35:07,240 in Search Engine Land on pivot tables. 500 00:35:07,240 --> 00:35:09,920 Just search for it. You guys should be able to handle that. 501 00:35:09,920 --> 00:35:12,500 I mean really you just put in Annie Cushing Excel, 502 00:35:12,500 --> 00:35:15,870 and I probably take up the first few pages of Google, 503 00:35:15,870 --> 00:35:22,150 but what I did here was I broke down all the links that we're building. 504 00:35:22,150 --> 00:35:26,540 I dump them in into a pivot table and then put them in a pivot chart 505 00:35:26,540 --> 00:35:28,540 and showed them 2 different ways. 506 00:35:28,540 --> 00:35:32,500 So I showed like by the anchor text, and I had to blur it out, 507 00:35:32,500 --> 00:35:35,390 but those were the anchors of the individual links, 508 00:35:35,390 --> 00:35:38,760 and then the months that we built for them, 509 00:35:38,760 --> 00:35:42,220 and then I also showed it like links broken down by month. 510 00:35:42,400 --> 00:35:44,470 So these were the months, and you can see 511 00:35:44,470 --> 00:35:47,000 kind of the ebb and flow of the link building, 512 00:35:47,020 --> 00:35:51,770 and then over here, represented by the colors, were all the different anchors. 513 00:35:51,770 --> 00:35:56,270 And then we also had some social reports. 514 00:35:56,270 --> 00:36:02,020 So basically, it really depends on the client. 515 00:36:02,020 --> 00:36:07,300 So, like when I'm in the kickoff call, the whole time I'm talking to them 516 00:36:07,300 --> 00:36:09,820 or listening while someone else is talking to them, 517 00:36:09,820 --> 00:36:12,450 I'm thinking like what makes this person tick. 518 00:36:12,450 --> 00:36:17,580 So if they're are like rankings for SAT prep, rankings for SAT, 519 00:36:18,580 --> 00:36:25,060 you can kind of get an idea for what like really floats their boat, 520 00:36:25,060 --> 00:36:29,450 and so I try to make a note of that and add those things to the reports. 521 00:36:30,450 --> 00:36:36,590 Another thing is definitely rankings, and one of my favorites is 522 00:36:36,590 --> 00:36:40,830 showing rankings and traffic in the same chart. 523 00:36:40,830 --> 00:36:44,700 I don't want to get too complicated, but I actually had, with one client, 524 00:36:44,700 --> 00:36:49,170 they really had to focus on 5 keywords, and they wanted to see how effective 525 00:36:49,170 --> 00:36:52,030 they were overtime because they were kind of their cash cows, 526 00:36:52,030 --> 00:36:56,850 and so what I do is I set up a pivot chart, and if you don't know anything 527 00:36:56,850 --> 00:36:59,670 about pivot charts, you can just check out for this, 528 00:36:59,670 --> 00:37:07,700 but I used a report filter for the keywords, and then in the chart, 529 00:37:07,700 --> 00:37:14,150 I had the traffic as an area chart and the rankings for Google over time, 530 00:37:14,150 --> 00:37:19,500 weekly, as a line chart, and then the rankings for Bing as a line chart, 531 00:37:19,500 --> 00:37:24,860 and then when you use a report filter, you just choose a new option 532 00:37:24,860 --> 00:37:30,990 and it's magic, and like the whole Excel chart just updates. 533 00:37:30,990 --> 00:37:35,480 And so, they could click on the individual keywords, 534 00:37:35,480 --> 00:37:38,070 and then everything in the chart would just update. 535 00:37:38,070 --> 00:37:42,850 The traffic, the rankings, and so what they would do is kind of like click 536 00:37:42,850 --> 00:37:47,770 back and forth and they had all these historical data to look at. 537 00:37:50,770 --> 00:37:53,020 >>So speaking of rankings and traffic, 538 00:37:53,020 --> 00:38:00,610 do you ever hold data in for impression count from either GA or Webmaster Tools, 539 00:38:00,610 --> 00:38:03,460 and do you think either of those are reliable? 540 00:38:03,460 --> 00:38:10,230 >>Oh, absolutely, I used to before not provided, I used to create these 541 00:38:10,230 --> 00:38:12,850 spreadsheets where I would show the keyword volume 542 00:38:12,850 --> 00:38:16,360 and then all the Google Webmaster Tool data, and then Analytics, 543 00:38:16,580 --> 00:38:18,350 So I could show all the way from 544 00:38:18,350 --> 00:38:21,850 impression to clicks to conversions and revenue. 545 00:38:22,100 --> 00:38:28,090 Now since not provided, that's kind of useless, and I actually rely 546 00:38:28,090 --> 00:38:32,330 on Webmaster Tools data a lot more. 547 00:38:33,330 --> 00:38:37,350 the impression—I'm sure it's not very accurate, but it's at least something. 548 00:38:37,350 --> 00:38:43,460 So what I tend to do is early on, before I know much about the client, 549 00:38:43,460 --> 00:38:48,580 I'll use SEMrush and just get a list of all the keywords that they're ranking 550 00:38:48,580 --> 00:38:53,110 on the first few pages of Google or Bing for, and then I'll look at their 551 00:38:53,110 --> 00:38:57,630 Webmaster Tools data and then compare those 2 data sets 552 00:38:57,630 --> 00:39:03,800 and kind of extrapolate that out, but I have to say I definitely— 553 00:39:03,800 --> 00:39:05,990 well, there's one other thing that I do. 554 00:39:05,990 --> 00:39:12,430 I'll click on the keywords and like the search queries report, drill down, 555 00:39:12,430 --> 00:39:18,720 and look to see if 2 landing pages or more are competing against each other. 556 00:39:19,720 --> 00:39:21,590 Like if they're both optimized for the same term 557 00:39:21,590 --> 00:39:25,390 or Google can't figure out which landing page to send to, 558 00:39:25,390 --> 00:39:29,980 but my focus has definitely become a lot more on landing pages. 559 00:39:29,980 --> 00:39:33,700 So I'll use keywords just because I know that's what they want to know, 560 00:39:33,700 --> 00:39:39,910 and then from there, it's just all, right, here are your cash cow landing pages. 561 00:39:40,910 --> 00:39:45,560 We know from this fire hose of data that here are the keywords 562 00:39:45,560 --> 00:39:51,280 associated with those. Now let's work on getting more conversions and more traffic. 563 00:39:53,280 --> 00:39:55,900 Any other questions? 564 00:40:00,900 --> 00:40:14,720 Oh yeah, it's bit.ly/annie-mozcon, and the password is just mozcon. 565 00:40:14,720 --> 00:40:19,890 The only reason I password-protected it it's not going to be particularly 566 00:40:19,890 --> 00:40:23,390 useful for anyone who's not here at the conference. 567 00:40:30,390 --> 00:40:32,190 That was good waving. 568 00:40:41,190 --> 00:40:46,640 >>What's up, Annie? So you and I a while ago had a Twitter conversation 569 00:40:46,640 --> 00:40:50,590 about visualizations and data visualizations, 570 00:40:50,590 --> 00:40:55,690 and you're pretty passionate about visualizing data well. 571 00:40:55,690 --> 00:41:00,200 I was wondering if you could give us like 2, 3, 4 visualizations 572 00:41:00,200 --> 00:41:03,760 that you see frequently that we should avoid. 573 00:41:03,760 --> 00:41:09,160 So like pie charts for specific kinds of data, something like that you see people 574 00:41:09,160 --> 00:41:11,900 do all the time that you don't think it's actionable, that 575 00:41:11,900 --> 00:41:14,550 doesn't get them anywhere, doesn't get buy-in from their bosses. 576 00:41:14,550 --> 00:41:20,570 >>Yeah, that's a good question. I'm not a big fan of pie charts. 577 00:41:20,570 --> 00:41:24,370 They're okay with very, very small data sets. 578 00:41:24,370 --> 00:41:30,060 I do see pie charts used a lot and especially now with the 579 00:41:30,060 --> 00:41:32,060 spate of infographics out there. 580 00:41:33,060 --> 00:41:37,730 They'll have lots and lots of like different data series in one pie chart 581 00:41:37,730 --> 00:41:41,460 and then they have like lines pointing into the pie charts 582 00:41:41,460 --> 00:41:46,180 so kind of have to follow the lines to figure out where it's supposed to be. 583 00:41:46,180 --> 00:41:49,590 So I see that as a really common mistake among marketers. 584 00:41:49,590 --> 00:41:53,600 So I don't recommend using pie charts except for very small data sets. 585 00:41:53,600 --> 00:41:59,700 I mean this is just me.This isn't God speaking here. He doesn't care about data. 586 00:41:59,700 --> 00:42:06,120 But if you do have a small data set and you're using a pie chart, 587 00:42:06,120 --> 00:42:14,270 one thing I'll do is just like you could click on like the line chart and choose that, 588 00:42:14,270 --> 00:42:17,760 you can also do that with the individual pieces of pie, 589 00:42:17,760 --> 00:42:23,100 and if you click once and well, it's going to select everything. 590 00:42:23,100 --> 00:42:26,390 Click on it again, and it's just going to select that one thing. 591 00:42:26,390 --> 00:42:31,180 What I like to do is pull it out from the rest of the pie chart. 592 00:42:31,180 --> 00:42:35,230 So you'll have like a pie chart like this, and then you have one piece of pie 593 00:42:35,230 --> 00:42:42,500 that's just pulled it out a little bit, and then I'll use like the little text boxes and stuff 594 00:42:42,500 --> 00:42:45,370 and draw attention to that. 595 00:42:45,370 --> 00:42:49,440 So that's effective. Really the biggest mistake that I see 596 00:42:49,440 --> 00:42:52,610 is just way too much junk in a trunk. 597 00:42:53,610 --> 00:42:59,540 So you look at this chart and you just see like all of this data, 598 00:42:59,540 --> 00:43:01,540 but you don't tell a story. 599 00:43:01,540 --> 00:43:05,340 So if you think about it, like we're all really, really busy 600 00:43:05,340 --> 00:43:08,370 and we try to get through things as fast as possible, 601 00:43:08,370 --> 00:43:14,250 and if you open up a spreadsheet and you just have like this huge table, 602 00:43:14,250 --> 00:43:17,280 which is definitely one of my annoyances. 603 00:43:18,280 --> 00:43:23,210 Tabular data just sucks, because you can't look at it and pull anything out. 604 00:43:24,210 --> 00:43:27,840 You can use conditional formatting and number formatting 605 00:43:27,840 --> 00:43:32,810 and make the best of it, but for the most part, I really really don't like tabular data, 606 00:43:32,810 --> 00:43:38,360 but the other thing is just trying to put too much in there without telling me 607 00:43:38,360 --> 00:43:42,600 like what is it that you want me to know? Like what is it? 608 00:43:42,600 --> 00:43:47,350 And the biggest mistake I see is not telling me what you want me to do. 609 00:43:47,350 --> 00:43:53,850 So they'll have something like oh, bounce rate is high, but maybe I'll notice that 610 00:43:53,850 --> 00:43:57,560 or maybe I won't; maybe I'll notice that the visits 611 00:43:57,560 --> 00:44:00,310 were really low for one of my pet keywords. 612 00:44:00,310 --> 00:44:06,910 So I really encourage you to like make sure if there's anything that's off, 613 00:44:06,910 --> 00:44:11,030 they have something, and don't put it like in a paragraph at the bottom 614 00:44:11,030 --> 00:44:13,130 because you don't read it. 615 00:44:13,130 --> 00:44:17,470 We don't read. We're marketers. And C levels are even worse. 616 00:44:17,470 --> 00:44:19,120 They don't read your stuff. 617 00:44:20,120 --> 00:44:22,250 So I put it out there in their face 618 00:44:22,250 --> 00:44:25,080 and try to minimize all the the other distractions. 619 00:44:25,080 --> 00:44:28,380 So that is like this is what I need you to take action on. 620 00:44:28,380 --> 00:44:31,450 This is the glacier that's dead ahead. 621 00:44:31,450 --> 00:44:37,550 Or this is an opportunity. This is your cash cow, and this is what I want you to do. 622 00:44:38,550 --> 00:44:41,770 So that's probably the biggest thing. 623 00:44:56,770 --> 00:44:58,770 >>Just a quick easy question. 624 00:44:58,770 --> 00:45:04,910 How do you present this data to your clients, and do you do it in a PDF, 625 00:45:04,910 --> 00:45:08,340 do you email it to them, or do you upload it? Okay, that answers it. (laughter) 626 00:45:08,340 --> 00:45:11,820 >>Yeah, I really don't like PDFs, 627 00:45:11,820 --> 00:45:16,970 because I like the client to be able to interact with it, like with those charts 628 00:45:16,970 --> 00:45:18,970 that I showed you, the combination charts. 629 00:45:18,970 --> 00:45:23,780 If you hover over on one of the points, it'll give you like the exact numbers. 630 00:45:23,780 --> 00:45:29,430 So what I like to do is, of course, like get on the phone with them, 631 00:45:29,430 --> 00:45:31,810 explain what it is that they're going to be looking for. 632 00:45:31,810 --> 00:45:35,230 So if it's a monthly report, I'd like to explain, hey, this is what you're going 633 00:45:35,230 --> 00:45:39,550 to be getting, and I would give clients like instructions. 634 00:45:39,550 --> 00:45:46,860 Even like with the pivot charts and pivot tables, I included a tab 635 00:45:46,860 --> 00:45:53,690 that included instructions for how they could take the data and click and drag 636 00:45:53,690 --> 00:45:58,170 and do all that stuff, and I even did a YouTube video for them 637 00:45:58,170 --> 00:46:02,190 so that they could just watch like a quick 5-minute video of 638 00:46:02,190 --> 00:46:04,280 hey, this is what you want to do 639 00:46:05,280 --> 00:46:08,040 if you want to manipulate or if you want to see it in a different way. 640 00:46:08,040 --> 00:46:12,440 So I think educating them is really important, 641 00:46:12,440 --> 00:46:21,710 and then I just send it to them as the Excel file. 642 00:46:21,710 --> 00:46:25,670 And then one caveat if you're working on a Mac, 643 00:46:25,670 --> 00:46:28,400 because I work on a PC at work and go home to a Mac. 644 00:46:28,400 --> 00:46:33,130 I hate Mac. Excel for Mac. They got their B team. 645 00:46:33,130 --> 00:46:35,000 There are quite of few options that you don't have, 646 00:46:35,000 --> 00:46:38,700 but the worst one is pivot charts don't work. 647 00:46:38,700 --> 00:46:41,770 You can't do pivot charts on a Mac, and it just blows 648 00:46:41,770 --> 00:46:43,930 big time, because I love pivot charts. 649 00:46:43,930 --> 00:46:46,810 You can create a pivot table and create a regular chart from it, 650 00:46:46,810 --> 00:46:51,010 but you can't create a pivot chart, and if you have a report filter, 651 00:46:51,010 --> 00:46:52,310 it won't work on a Mac. 652 00:46:52,310 --> 00:46:56,440 You can see it and you can select it, but you can't actually—like nothing 653 00:46:56,440 --> 00:47:00,360 will happen when you select like the keyword that I reference. 654 00:47:00,360 --> 00:47:07,490 So, I'll ask them are you using a Mac and then I just make a mental note. 655 00:47:07,490 --> 00:47:10,980 All right. I'm not going to use pivot charts with report filters. 656 00:47:10,980 --> 00:47:15,980 (applause)