Excel 104: Pivot Tables on Mac and PC [Video]

My first impression of a Pivot Table: They are scary, confusing monsters that always make your data look funky.

Once I got over the intimidation of them: They are the magical, unicorn warriors of the Excel world.

The main thing that Pivot Tables do is … pivot … your data. Just like you would while trying to get a couch up a small staircase.

Pivot tables also allow you to automatically summarize and deduplicate your data, filter by multiple metrics, and organize the data in ways that regular tables can’t even dream about.

pivot

This time I made two tutorial videos. One on the PC and one on the Mac. I know, it’s because I love you all so much.

What I cover in the tutorial video:

  • Add in Pivot Tables
  • Format Pivot Table with branded colors
  • Importance of a Raw data tab
  • Hack for changing PT column columns
  • Filter by multiple metrics
  • Change number formatting on in a column
  • Add in Sheet titles
  • Format Pivot Table with branded colors
  • Select data with three different keyboard shortcuts

Get the practice file here. And skip to the Mac tutorial here.


Enjoy!

 

 

How to Create a Stock Photo Library for a Few Hundred Bucks a Year

Fun DogWith the web in the midst of what I call the “content explosion” it is very difficult to stand out. Images are a great way to do that and they are now more important than ever because social shares include images.

As a consultant I have talked to several businesses that feel buying images when they are needed is just too expensive and I agree it can be. I am also not a fan of some of the free images available because I see the same images all over the web or they are often low quality. To stand out you need unique and/or beautiful photos that will pull people in and I am going to give you some quick steps on how to do that for just a few hundred bucks a year.

1. Pay for a Subscription for One Month

Sites like Shutterstock allow you to pay for just one month and download a certain number of images a day. Shutterstock allows 25 a day, they allow you to download any size image and I personally love the quality of their images. Bigstock allows you to download 20 images a day, but I know nothing about their images. There are others out there and often they have free trials.

If you can download 25 images a day for 30 days you will have 750 images in your library. This amount will cover most companies for content, email newsletters and other advertising for an entire year or more.

2. Have Someone Download and Organize Images Daily

kid on hippo

To really get your money’s worth someone needs to be assigned the task of downloading and organizing the photos everyday. Have your marketing team sit down and brainstorm on the kinds of images you will need. List out topics and categories – social media, technology, business people, trees, food, cute kids, families, words – you get the idea.

Once you have some ideas of what you need tell the person that will be responsible to look for powerful images that get these ideas across.  The obvious isn’t what you are looking for; go creative. You should know that this is a tedious job that takes a good amount of time, so give the person completing the task the time to do it right.

TIP: Remember to rename images before you use them on your blog.

3. Choose Unique, But Not Too Weird Images

You want to stand out in social media feeds and the best way to do that is to choose an image that draws the eye, and of course create a title that makes people want to click. Images that are too weird can push people away from your content, so while you want to be unique you do not want to be freaky.

What do you think of this image? 

freaky eye

Does it make you feel good? Scared? (It freaks me out a bit) Does it make you want to look away? If you need an image of an “eye” which would be more unique and pleasing to your readers, the above eye or the one below?

Beautiful eye

Both of these images could work depending on the topic, but which image would be more appealing to your audiences? You have to focus on your audience and the audiences you are hoping to reach. Freaky is not always the way to go.

4. Use Dropbox For a Categorized Library and Availability

DropboxYour photo library needs to be accessible to everyone on your team. Dropbox will allow you to add all your downloaded images and categorize them. To the right you will see some of the categories in one of my image libraries. Your team can access the images from their desktop, but not fill up their hard drive with hundreds of images.

Dropbox is very affordable and setting up a library like this ensures that a loss of a computer or an employee will not result in a complete loss of images. I do recommend you back up all the images on a flash drive just in case ([ backup everything just in case ;)].

5. View the Library with Tools Like Adobe Bridge For Easy Scanning

Even if you have images categorized it can sometimes be difficult looking through a ton of images to find what you are looking for. Tools like Adobe Bridge allow for quick scanning. If someone can only see names and not image icons, depending on which type of computer they use, it will be a nightmare for people to find images. Tools like this will save time and energy and reduce frustration.

adobeBridge

See, You Can Have a Great Image Library!

IdeasIn 30 days you can have a large, nice image library! It will take some time and effort, but you can get it done. Try to think out of the box with images and really find things that others haven’t seen or used before.

Image tips: 

  • Clear images are important
  • Choose the largest size possible so you can use the images in multiple ways (resize for the web)
  • Look for colorful and/or impactful
  • Look for things that are inspiring and beautiful
  • Find images that can represent something else in a brand new way
  • Each image must fit into a category
  • When it comes to words & certain graphics you can always change the hue, so the image could be used twice
  • Different is good

If you have any additional tips we would like to see them below. Also, each image (not the screenshots) above are from Shutterstock.

Focus on the Outstanding: Write Less and Write Better

There is so much content being cranked out it is really impossible to read it all. I notice that much of the content appears to be calculated topics that I think are chosen strictly with the goal to bring in traffic. From what I see most of those articles have the same basic information I have seen several times before…essentially they offer me no value whatsoever.

I would really love it if the search and marketing community focused not just on content, but on creating outstanding content. This would most likely mean less content and better information for all of us. I don’t care if you publish just once a month; what I want is to be wowed.

#WriteLessAndWriteBetter

So How Does One Stand Out?

I could say “Be different!” or “Be unique!” (which is important), but what I really want to say is to draw off your experiences and offer advice and information you think would be helpful to multiple audiences. Show your personality and don’t try to be like everyone else, because when everyone is the same all you get is boring!

Examples of Awesomeness

Your experiences are ‘different’ and ‘unique’ and as a result they are useful. Let’s look at some articles that are relevant, different and extremely useful.

Sugarrae

Sugarrae’s article has over 1,000 shares on Facebook alone. She does SEO consulting and clearly this article is not about SEO, but because Instagram is a part of social it is relevant. She drew off of her experiences and her desire to help others and the result was a successful article. It was totally different, extremely useful and it worked.

IFTTT

The Buffer Blog cranks out a ton of articles and I know planning and strategy is involved, however the information they provide is often very unique and clearly the information comes from their experiences. The IFTTT post has almost 3,000 shares on Twitter and over a 1,000 on Google+. It was different than the typical SEO/social/content post, but as you can see from the shares different is great!

This is one blog that I personally check every single day because I look forward to getting something different and ‘outstanding’.

Ian-lurie

This piece, by the amazingly insightful Ian Lurie, immediately shows his personality in the title (unique so I am interested). The content itself is very detailed and completely based off his 20 years of experience. It isn’t like everyone else’s advice, it is personal and it is real. With over 1,000 tweets you can see that people like personal and real.

This took a lot of time to write, but it is what I said I was looking for above –> outstanding! It is not a quick read either and that is a good thing. Quality over quantity.

How to Not Suck

It is really important today to make sure you are not seen as someone that creates mediocre-crap content. Reputations are built through content everyday and you want a good rep. If I were asked to give advice to people wanting to create content it would be -> focus on creating something awesome once a month instead of writing 3 times a week.

The amazing content stands out and brings in traffic for the long term. Amazing content makes you memorable and outstanding content can make you a star.

Some tips for avoiding suckiness:

  • If your experiences are the same as everyone else’s don’t write about them.
  • If you don’t know how to write, please don’t write.
  • If you can’t back up what you say don’t give advice.
  • If you have tried a strategy once and it worked this does not mean it will work for everyone in every situation so please don’t claim that it will.
  • Don’t promise things, especially in SEO. Again, every situation is different.
  • Dig deeper – “optimize your title tags” is not deep.
  • Don’t try to be like everyone else, because 90% of what I see is boring and repetitive.
  • Provide resources and step-by-step information.
  • Always assume that the person reading has no idea what you are talking about and add in the details that will teach.
  • Focus on educating and helping.
  • Expand deeply on the basic advice – everyone has already seen the basic advice.

We Are at a Crossroads with Content

Right now it appears that the marketing industry is cranking out content like machines and the content reads like it was written by machines. I see a lot of ‘blah’ and very little value.  So, we can continue down this path of creating content to create it OR we could focus on what matters in everything, value.

Questions to consider:

  • Is there value in what we are writing?
  • Does it make people think?
  • Is it different than what everyone else is saying?
  • Is our content helpful to a large group of people?
  • Who does this content help?

It is time to stop the machine-like content and time to start focus on the outstanding.

101 Wednesday Recap: Basic Formulas in Excel

Formulas in Excel are basically the keyboard shortcuts of any program. They make operating through large data dumps 10x faster. Do you spend time copying and pasting over and over to combine or look up data? Then this tutorial will be the start of lots of time saved. You’ll be pony status at the end of this 20 minute hangout.

programming

In this tutorial I started with a simple SUM formula and went over how to use it to take the sum of two cells or a range of cells.

Next, is the the CONCATENATE formula. I used this to combine combine two cell’s contents, for one. But more importantly combine the text string “http://authoritylabs.com” with the URLs I exported from Google Analytics. (Jump ahead to CONCATENATE.)

Lastly, is the VLOOKUP. The idea behind VLOOKUP is that you can search for something in a data series and return it if it has the proper credentials. I went through this process step by step. (Jump ahead to VLOOKUPs.)

Some ProTips I covered were how to:

  • Use Find and Replace
  • Paste Special (so that you can edit a formula’s final value)
  • Keyboard shortcut for selecting whole columns of data
  • Screaming Frog shout-out
  • Formula tab in the Excel Toolbar

Here’s the document for you to practice on your own!

Do you want more awesome Excel tips? Check out my latest post Excel 103 Annotating in Excel!

Or catch up on any 101 Wednesdays you might have missed on our YouTube Channel.

Why Content Strategy Is Instrumental To The Success Of Your Business – Part 2

Why Content Strategy Is Instrumental To The Success Of Your Business

My strength coach/exercise mentor, Eric Cressey, was once asked the most dreaded of questions while giving a presentation.

“If you could choose only one exercise to do, what would it be?” He sighed, realizing the impossibility of suggesting such a thing, particularly given each person’s unique needs.

However, he didn’t run from the question, ultimately sharing a recommendation that touches everyone. “Lunges—or any single-leg exercise, for that matter,” he said.

It wasn’t the simple choice, like bicep curls or leg extensions. But single-leg work is suitable for pretty much anyone, regardless of their fitness level, and it helps correct some of the dysfunction the general population struggles with. And, not to be overlooked, it’s hard, meaning that, though everyone needs it, few adopt the approach with the vigor needed to be successful.

Maybe most important for those looking to get their “swole” on, single-leg work has tremendous carry-over to the “big rock” lifts, having a great correlation to bigger lifts and added size.

This whole line of thinking arose out of my desire to distill why content strategy is so important to businesses large and small. We’re now in an era where seemingly everyone is being told they need content strategy, but I’m not sure business owners truly get why.

During a recent talk at the Dallas-Fort Worth Search Engine Marketing Association’s (DFWSEM) monthly meeting, I shared my thoughts about the importance of content strategy by showing a graphic of a high-wire act.

“This is you,” I said, pointing to the man on the wire. “The area behind him is your business. The area in front of him is your customers. The wire is content strategy—it ensures your business’s objectives are always aligned with the needs of your customers. Most important, it ensures your focus never wavers from the course.”

Why Content Strategy Is Instrumental To The Success Of Your Business

Based on the feedback from the crowd, they got it.

Content strategy is an integral part of any business, especially yours. Since everyone is now in get-swole-for-the-beach mode, I figured I’d share three fitness-related tips that highlight why a half-hearted adoption of content strategy is dangerous and foolish.

“Look At What Everyone Else Is Doing, Then Do The Opposite”

When I first heard this quote, I thought it seemed odd. “Shouldn’t I follow the advice of folks who are also making a commitment  to their health,” I thought. Yes and no. You should have a few trusted, knowledgeable voices who can steer you down the right path, keep you healthy and ensure you meet your goals. But don’t assume everyone in the gym is doing the right things and for the right reasons.

  • Some folks will abuse drugs to get bigger
  • Others will ignore sound advice and do stupid exercises
  • A fair number are in it only for looks, not health

To be successful in the gym and in content marketing takes a commitment to doing the little things right, consistently. The same way you often see folks skipping warm ups at the gym is much the same as content marketers who start generating content with no clear goals  in mind.

Make it your priority to only create content that’s…

  • Aligned with your over-arching goals
  • Grammatically correct
  • Based on the needs of your customers
  • Geared to taking away pain points of users
  • Of a quality you can be proud of

“Do What’s Hard First”

 This quote is akin to the time-management bromide, “Swallow the biggest frog first,” which speaks to getting the biggest task off your plate early in the morning. With exercise, the first lift of the day (often called “A1”) is the most important for the day. It’s the priority, with everything else being supportive lifts.

If you’re a content marketer, content strategy is your main lift, today and every day.

Whether in a meeting, on the phone with a customer or looking at designs with your creative director, I want you to keep this question top of mind: “Does this get the business closer to our long-term goals?”

If not, you shouldn’t be doing it. If it is, forge through, even if it seems insurmountable at first.

While a consultant, my biggest client was in a hyper-competitive vertical. He needed a new website. He wanted to jump right in and start creating content. I asked him the question above, and it made things very real and very clear. We built a new website, and he’s now competing very well, showing up on the first page of Google for his main keywords.

He once said to me, “I’m glad I got the most difficult thing out of the way early.”

“Strength Is Corrective”

On my way to the airport from Eric’s facility, after my first visit, I kept replaying a quote in my head from the meeting—”If you get stronger, many of the other things fix themselves.” Those “other things” were the common ailments a lot of us desk jockeys suffer from, including stiff ankles and slumping shoulders.

I wanted to believe him, but it seemed implausible. “How the heck is getting stronger going to fix all this and assist me in gaining a little size?” I thought

I trusted his word, though, and for the next two months I really hammered getting stronger. A weird thing happened. It worked. All of a sudden, my shoulders no longer slumped, I moved better, and I gained eight pounds over my entire frame.

This serves as a great example of how the residue of your main focus can be found on everything you touch.

You might be working on an SEO strategy for your company or a client, but you keep your eyes on how the onsite content is performing relative to your goals. Or,  you ensure that your SEO, CRO and paid media teams work together when redesigning the website, developing landing pages and creating ad copy consistent with the insights gleamed from the content audit. There is no room for silos when content strategy is the main lift.

Honestly, what I love most about content strategy is how it can set a business apart from its competitors, many of whom are stuck in “create-content-for-content’s-sake” mode. If you eschew such thinking and fully accept content strategy as the guiding principle for your content marketing efforts, your business and your customers will greatly benefit.

What’s been your experience with content strategy? Have you made it a priority?

In the final post I’ll break down how your business can successfully and easily adopt a content strategy mindset.

See part 1.

Excel 103: How to Do Annotating in Excel

Reports should be concise, clear, and pretty. One big way to help your reports be more clear is to label them clearly. Some simple tricks like adding a comment box next to a chart can really help draw in the untrained-data-eye. This is especially important when you have CEOs who hate looking at spreadsheets and may rarely open your reports.

My life as a data girl

Basically my life as a data girl

Annotating can be done in several ways. This post will cover; branded colors, cell specific notes, creating/formatting callout boxes (or shapes).

Colors

My number one priority while annotating a report is keeping it branded. If your company’s colors are a specific green and blue then make sure that your boxes, charts, and tables all follow those branded colors. You should be able to glance at a report and tell what colors would be in that company’s logo. (I hope you like orange ;).

Callouts

Callouts are my absolute favorite annotation tool. There are several different shapes you can use that each have their own merit while annotating.

Here is how you add one:

For Mac: On the Home tab, under Insert, click Shape. You can change the formatting under the Format tab and then Shape Styles.

Cell Comments

One of the easiest, and simplest annotations to add is a comment on a specific cell.

To do this navigate to the Review tab and under the Comments section, select add New Comment.

Excel103_comment Excel103_comment2

For Mac: Select the cell you would like to comment. Navigate to the Review tab, under Comments, click New. When you’re finish typing the text, click outside the comment box.

That’s pretty much it! Stayed tuned for more of my Beginner Excel Series. And tune in for 101 Wednesdays where I go over simple tasks every marketer should be familiar with.