The image you see to the right is called a doughnut chart. Its the same exact thing as a pie chart, but its even better, because well….its a doughnut! The data displayed in this particular chart is a break down of the top 25 keywords used as anchor text to my personal blog. This type of analysis is extremely important when detecting things like Penguin, and it looks great on client reports.

In this blog post I am going to teach you how to make one of these charts in a very quick and easy way! Hopefully after reading this, you will be on your way to making donuts in no time flat!

## Ingredients

**Data** – I am using backlink data taken from Majestic SEO. But you can use any data set that has anchor text included in the export. I like Majestic though because they tend to have a larger data set to play with, which allows for more fun in your spread sheet.

**OpenOffice Calc** – You can use Excel as well, but I am a long time fan of open source software. Many of the steps in this post are almost identical to Excel, so you should be able to follow along at home if you don’t use Calc.

## Step 1: Collect Data

This is pretty straight forward. Make sure you export your data set into a format that Calc can read. To be safe I always chose CSV. Like I said before it doesn’t really matter where you get your link data, just make sure that anchor text is part of the data set.

## Step 2: Clean Up Data

For this type of analysis we are only focusing on anchor text, which means that any other data points that comes with in your data set, can be ignored. To make it super easy, I highlight the column that contains the anchor text and copy and paste it to a new sheet. This way you don’t have any other data points to distract you.

## Step 3: Create Pivot Table

After your data is cleaned up, you will need to create the pivot table. This is the hardest part of the tutorial, but I am going to try and make it as easy as possible.

- Highlight the column that the anchor text is in.
- From the menu go to Data > Pivot Table > Create
- Select “Current Selection” and then “OK”.
- In the new dialog box, you will see “Fields” with a button that says, “Anchor Text” or what ever the column highlighted, is labeled as. Drag this button to the white box called, “Row Fields”.
- Now, drag this same button to the box called “Data Fields”.
- Double click the last box you dragged (the one now in “Data Fields”), and select “Count” under function and click “OK”.
- Take a deep breath you are almost there.
- At the bottom of the Pivot Table dialog box you should see a button that says “More”, click that.
- Now select under
**Results to**“- new sheet -“. - If you have followed all of the steps above correctly, your dialog box should look like so:

If your dialog box looks like the one above, go ahead and click OK, so we can move on to the next step.

## Step 4: Sort Table

So now that you have a pivot table with Column A holding anchor text, and Column B holding the number of times that text appears in the data set. In order to create the chart in the next step we need to sort the table so the top anchor text is at the top. To do this we are going to go to the menu Data > Sort… Within this dialog select Sort By > Column B > Descending. and then click OK. This should resort your table so that the most popular anchor text is at the top.

## Step 5: Create Chart

This is the fun part! Highlight the number of rows in column A and B that you want to include in your chart. For client reports I normally don’t go over 25 because it makes the legend harder to read, but if you want to experiment with different sets, go for it. Next go to menu Insert > Chart… Now of course the chart above is a doughnut chart, to get this you need to select Pie, and then Doughnut. But, you can have fun picking what ever you like. I like clicking the “3D Look” option so the chart doesn’t look so flat, but it will depend greatly on what looks best for your report.

Well, that about wraps up this tutorial. Playing with spreadsheets and making charts is one of the best parts of my job. I hope you have enjoyed this tutorial as much as I have making it! Until next time, happy reporting!

## Comments

New @webuildpages: How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc http://t.co/gfHu2wamBw

http://t.co/t00YSwnCSK http://t.co/ViI0Ybe3eL

How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc http://t.co/XRkSDD22gZ #OnlineMarketing

How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc http://t.co/3Efdu4hgmx via @NinjasMarketing

Blogged this for you all: How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc http://t.co/C36EpCTFuq

RT @NinjasMarketing: How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc http://t.co/Yf0FXsFqfW by @joehall

How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc by @joehall http://t.co/idtHV12ogY #getyoursexyon

Like analytics and data? Learn to use pivot tables http://t.co/dzbIVuNrZY

RT @AnnieCushing: How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc by @joehall http://t.co/idtHV12ogY #getyoursex…

RT @CygnusSEO: Like analytics and data? Learn to use pivot tables http://t.co/dzbIVuNrZY

RT @CygnusSEO: Like analytics and data? Learn to use pivot tables http://t.co/dzbIVuNrZY

Like analytics and data? Learn to use pivot tables with http://t.co/BjPDjTOjbx … via @CygnusSEO Great you’re using OpenOffice !!

RT @joehall: Blogged this for you all: How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc http://t.co/C36EpCTFuq

How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc http://t.co/7QZSqRcNHU #DigitalMarketing

‘How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc’ by @NinjasMarketing http://t.co/2EApjPDAn0

RT @TheNextCorner: How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc http://t.co/y5UUpDG04f awesome post @joehall

How to get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc – http://t.co/9FITjFkVq3 #seo

How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc http://t.co/XtdDCXkQ3o by @joehall via @NinjasMarketing #SEO

RT @KnowEm: How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc http://t.co/XtdDCXkQ3o by @joehall via @NinjasMarket…

RT @KnowEm: How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc http://t.co/XtdDCXkQ3o by @joehall via @NinjasMarket…

How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc (or Excel) by @joehall http://t.co/0eOMZaipwS

‘How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc’ by @NinjasMarketing http://t.co/AhPlCrTdd2

How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc via @richardbaxter: The image you see… http://t.co/KBYpRh1EZN

‘How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc’ by @NinjasMarketing http://t.co/W67EWJ593A

“How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc” by @joehall on the @NinjasMarketing Blog http://t.co/e8ERWRkvqM

RT @aleyda: ‘How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc’ by @NinjasMarketing http://t.co/W67EWJ593A

RT @aleyda: ‘How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc’ by @NinjasMarketing http://t.co/W67EWJ593A

‘How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc’ by @NinjasMarketing http://t.co/ce0al9xhZB

How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc http://t.co/owUrCIpyKO by @joehall featuring @anniecushing

RT @RavenJon: How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc http://t.co/owUrCIpyKO by @joehall featuring @anni…

RT @RavenJon: How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc http://t.co/owUrCIpyKO by @joehall featuring @anni…

RT @RavenJon: How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc http://t.co/jAaKncV8PX by @joehall w @AnnieCushing

RT @RavenJon: How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc http://t.co/owUrCIpyKO by @joehall featuring @anni…

How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc by @joehall http://t.co/mnspWdGgh9

Its really new to me to have anchor text using Pivot Table and open office calc. Quite useful to me. Thanks for great post. Hope more useful information in future.

How To Get Anchor Text Distribution Using Pivot Tables and OpenOffice Calc http://t.co/GkaSrkE4ti