excel training – STL

In life as in business, we always strive to find the easiest ways of getting things done. Sometimes, however, the simplest methods involve cutting corners, obtaining short-term results but long-term headaches.

One of a long line of Excel features, the PivotTable is the best way to break your information down into more manageable chunks. In this post we’ll outline simple uses for the PivotTable along with a few tips to help you get the most out of your data.

excel pivottable — This data was converted into the PivotTable in 7 clicks

Uses for PivotTables

Anyone with a need to break down large data sets will find a use for the PivotTable. Sales managers, IT professionals, financiers and even marketers can save time and the get most from their information with this popular Excel feature.

A fantastic tool for summarising your data, the PivotTable has the ability to find hidden trends or relationships between data. Ok, so they’re not really hidden, but they may as well be surrounded by all that information. Sales managers rejoice: these complex tables can outline sales performance of team members over specified time periods, even down to products sold and of course much more.

We empathise that the PivotTable has an off-putting name, but in truth, they are really easy to create and don’t even require a single formula to be written. To get started, just click any cell on your Spreadsheet and select PivotTable in the top navigation bar. Follow the prompts, tick a few boxes and complex tables will be created in front of your eyes.

As we touched on earlier, data can be easily transported, helping you to recognise trends within trends and look at your data more laterally. Again, for sales managers, one minute you’ll be able to see which team member has sold more coal to Newcastle in the past month, then you’ll be able to switch a few variables and see the trend of all products sold to Newcastle over the past few months.

Time saved is one of the major selling points for the PivotTable. These easy to create, complex tables become a powerhouse reference point for your every analysis requirements. From these tables, you’ll be able to create graphs and charts to better visualise your information. Ideal for presenting to colleagues and clients, you’ll look like a pro with just a few clicks of your mouse.

PivotTable Tips

One benefit of grouping your data is that you can extract a subset of the grouped data onto a new worksheet. It’s really easy to do this too, just locate the group and double click in the total cell containing the data you’re interested in. Then all of the data that contributed to that total will be extracted onto a new worksheet.

Replace blanks cells with zeroes. When the PivotTable doesn’t have data for part of a row, you’ll get blank cells. It’s easy to get around this by right clicking any cell in the PivotTable and choosing options. In the layout and format tab in the format section, type 0 next to the field labelled “for empty cells show“.

Automatic updates mean that as soon as you change data in your original Spreadsheet, all you need to do is hit the refresh button and your PivotTable data will be bought up to date. Saving you time having to create a new table each time, Excel intuitively recalculates your figures. The larger the company or those with collaborative documents, the more useful this feature becomes. Imagine how many new PivotTables would need to be created if sales figures were updated daily.

Excel has some pretty good table styles and customisation options that help your data stand out and make it clearer to digest, not to mention brightening up your Spreadsheet. Change the colour and layout of your table using pre-set templates found in the top navigation bar.

Change the PivotTable summary function by right clicking inside the table and selecting “summarise data by” option. This allows you to look at the same data at a different angle. Quickly creating dynamic tables allows you to find those trends and even summarise them with a chart or graph for better reporting.

Sort your data by timescales quickly. Right click a date in the row field to group by months, years or quarters. Again this is a useful feature for measuring sales revenue and data change over time.

By employing these hints and tips, you’ll be able to save time and effort in reporting. What’s your favourite tip for helping you get the most out of your PivotTable?

Want to become an Excel expert? Attend one of Best STL’s training courses available London and UK wide.

It’s important to stay ahead of the game and decent spreadsheet software such as Microsoft Excel is still regarded as one of the most empowering and flexible ways to track your SEO campaigns. In conjunction with important tools such as Google Analytics & Keyword Tools, Excel is able to help provide unrivalled analysis to aid the decision making processes for your business.

This article will provide you with some simple tips and tricks to help you reap rewards in terms of visitors, CTR’s, conversions and conversations.

Keywords

When discussing SEO, it’d be impossible not to mention keywords. And with this, there are two sides to the story. Firstly, which keywords to settle upon and secondly, tracking their effectiveness.

Deciding which keywords to run with is a big decision. It needs to fit in with both your business goals and marketing strategy, leaving no room for error. Using an Excel spreadsheet as a tool to track your keyword ideas in conjunction with a combination of free and paid online tools, you’ll be able to save both time and money in the long term.

Two of the most popular tools include, Google Keyword Tool & Google Trends. Use the aids to provide insight as to the search volumes of your terms (both local and worldwide) and also to help with variations upon your keywords (e.g. if your main term is “Light Bulbs” you could find alternatives such as “Halogen Bulbs”, “Energy Saving Light Bulbs”).

Add your findings to your spreadsheet using columns

Keyword
Local Search Volume
Worldwide Search Volume (if relevant)
Competition (Low, Medium, High)
Trend (You can calculate this however you wish)
Additional Notes

This will give you a quick indicator as to which keywords your company should target. By using sorting and filtering you should start to see some patterns. Put simply, high competition and low search volume is more often than not, worth being left alone.

Once you’ve decided upon your specific search terms, you’ll need to track these too. Again, free tools are great. Rank Checker will provide you with a quick breakdown of where you appear in the three major search engines. Create a new spreadsheet, with the columns;

Keyword
Month
Google Rank
Yahoo Rank
Bing Rank

I’d recommend doing a separate spreadsheet for each month, then you can always cross reference these in the future to analyse trends specific to your website. Alternatively, you could have one page for each, Google, Yahoo & Bing. Knowing which keywords are effective will provide you with insight as to where to channel your paid and organic campaigns.

Directories

If you’re a business with an emphasis on SEO (which to be honest, who isn’t these days?), you’re likely interested in spreading a wide net for your content. Submitting your posts to directories is a great way to increase the reach of your content, but merely submitting them shouldn’t be the end of it.

Measuring the effectiveness of an action or process is key, and there is no simpler or more effective method than creating an Excel spreadsheet. First, you’ll need to find those directories, if you haven’t already. A quick Google search will provide you with a comprehensive list for your specific needs. Once you’ve got the list, create an Excel document with columns for:

Directory Name
Date Link Submitted
Date Link Confirmed
Pricing
Additional Info

Knowing where and when you submitted a post to a directory will save you time (and money) in the future, reducing the occurrence of duplication and ineffective listings. (If you are worried you have entered something twice, have a look at this post describing how to deduplicate data). It always pays to include an additional info column to remember login details and the like.

Expenses

Tracking your spend makes simple accounting sense. Excel is a fantastic way to note down where your SEO budget is going. From paid directories to PPC campaigns and everything in between. Your spreadsheet may include the following columns:

Expense
Average Monthly Cost
Annual Cost
Additional Notes

Now, this document in itself provides little more insight than where your money is going. However, when analysed in conjunction with conversation rates for example, Excel will allow you to decipher the exact effectiveness of a campaign per cost. It’s also a great “go to” document when making budgeting decisions.

Competitor Analysis

Of course, in order to stay one step ahead of the competition, you need to know what they’re up to. That’s just common sense, right?

Again, there are plenty of tools out there to help you track the numbers of backlinks to your site, paid and unpaid. Open Site Explorer is a handy tool to quickly scan a webpage and offers insight to domain authority, page rank and the links from other sites to your page.
Further to this, you can do exactly the same for your competitors. Where are their links coming from? Why aren’t these sources linking to you? You’ll soon be able to see what their site offers which yours doesn’t.

To make things easier to analyse trends, use your spreadsheet. See if there’s one domain that favours your content across any number of posts. For this you can add as many or as little columns as you desire, but you may wish to include;

URL
Total Links
Linking Root Domains
Page Authority
Domain Authority
Facebook Shares
Tweets
Google +1’s

It goes without saying that a link from a site with a higher domain authority will increase the likelihood of your content reaching a greater audience. The more links and shares that you receive can only be beneficial too for spreading your word.

To conclude, there’s a world of data out there which you can analyse. Excel can help you put these into graphs, charts, columns, rows and more to ensure your findings are digestible. It can also help you decide whether a particular SEO campaign has been successful in terms of return on investment and much more. We’ll discuss more of these in further posts, so stay tuned.

Interested in sharpening your own Excel skills so you can build your own SEO spreadsheet management dashboard? Attend an Excel training course from Best STL where you’ll learn about formulas across worksheets and how to manage large lists of data.

Blog Categories