Category Archives: Excel Training

Pull Data, Not Teeth – The PivotTable Edition

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.

Seminar

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.

old-time-clock

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“.

a

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.

Excel 2013: Use Timelines With PivotTable Data – MS Excel Training

new feature for microsoft office 2013See the timing of trends in your Excel 2013 PivotTable data with the new timeline feature. By default the data is split into months, though manipulation allows you to break this down into weeks, quarters, years or even days if you prefer.

If you make user of Slicers in your PivotTables and PivotCharts, you will be very comfortable with the timeline feature.

Timeline for PivotTables

  1. To add a Timeline, click in your PivotTable data area and from the Analyse ribbon select Insert Timeline from the Filter group.
  2. Select the date field required and your Timeline is now ready to be used to filter your date field chosen.
  3. You can change the specified dates by simply dragging each end of the timeline.

In the real world this could be a particularly handy tool on your performance dashboards. Once you’ve set everything up, you can then let users build their own view on the data.

Timeline for PivotTables

For more tips and features on Excel 2013 and other versions, browse MS Excel Training courses from Best STL, available London and UK wide. Speak to our a member of our team today to discuss the latest training offers along with your specific requirements. 

Excel 2013: 3 New Ways To Customise Your Charts – London Excel Courses

new feature for microsoft office 2013Whether you’ve decided to use a suggested chart to represent your data or already knew which one works best from the outset, a new toolbar in Excel 2013 allow you to customise your visualisation quicker.

Selecting the chart will automatically reveal two tool ribbons: Design & Format, both specifically designed to help you manipulate your Excel 2013 charts. Although the Chart Tools Layout tab no longer exists in 2013, the buttons it contained are still available, just in different places.

Excel 2013 charts

Three (new) buttons for chart formatting now appear at the top right corner of your chart; Chart Elements, Chart Styles and Chart Filters. Instead of digging through menus you can access these buttons overlaid on the chart.

Excel 2013 charts

 

Chart Elements

Add or Remove specific elements of your chart such as Data Labels and Gridlines. This way you can have as much or as little labelling and layout features as you desire.

Excel Charts 2013

 

Chart Styles

Change the colour and style of your chart with this simple formatting option. Scroll over each option to get a preview of how your new chart will look.

Excel Charts 2013

 

Chart Filters

Want to modify what data the chart includes? Previously you had to modify the data range in a fiddly way. Now you can hide and show data with the chart filters selecting them from the tick box menu, very similar to filtering data in a table. Customising your charts has never been so easy.

Excel Charts 2013

 

For more tips and features on Excel 2013 and other versions, browse London Excel courses from Best STL, available London and UK wide. With training levels ranging from beginner up to advanced and Excel VBA, there’s sure to be a course to suit your needs.

Microsoft Excel Training Courses 2013: Sparklines through Quick Analysis

new feature for microsoft office 2013Introduced in Excel 2010, Sparklines create charts within a cell. It can help to show one figure in context with others and illustrate trends. In this example a blank column is created for the sparklines before selecting the adjacent data.

In Excel 2013 the Quick Analysis feature has now brought Sparklines to the fore. Perfect for tracking performance over time, this new way of accessing them will give a quick insight into your data.

Sparklines in Excel 2013

How to: Highlight some Excel data in a table and look for the Quick Analysis tag to float over the bottom-right corner of your selection. Put your mouse over this icon to explore the options.

For more tips and features on Excel 2013 and other versions, browse Microsoft Excel training courses from Best STL, available London and UK wide. With our instructor-led training, you’ll be able to manipulate your data in ways you never thought possible, helping you save time and money. Choose your preferred course ranging from beginner to advanced level.

Microsoft Training Excel 2013: Audit your Spreadsheet with the Inquire Add-In

new feature for microsoft office 2013For users with the Office Professional Plus package, the Inquire add-in comes pre-installed on Excel 2013. Helping you to analyse, audit and review workbooks, this great new feature also has the potential to highlight errors and security concerns.

We recently discussed, how to take back control of your spreadsheets by reducing “fat finger mistakes” and auditing errors, which can have huge cost implications to businesses. The Inquire add-in is another great tool for preventing these issues from arising and escalating in Excel.

The new tool can be accessed via the Inquire Tab in the ribbon and includes a number of useful functions:

Worksheet relationship

  1. Workbook analysis
  2. Workbook relationship
  3. Worksheet relationship
  4. Cell relationship
  5. Compare files
  6. Clean Excess Cell formatting
  7. Workbook passwords

These seven functions are simple to use, many providing visualisations to help better understand the information.

We all know Excel for being data driven, however, visualising things often makes this mass data easier to understand, take for example recommended charts & graphs or all new quick analysis techniques.

A few of our favourite Inquire features include:

Cell Relationship in Excel 2013

  • Cleaning excess formatting, including formatting in blank cells which bloats file size and contributes to poor performance in Excel.
  • The ability to compare two workbooks, highlighting cells that differ. This is particularly useful during an audit.
  • Being able to visualise the relationships between cells. Understanding the audit trail of how a figure came to be is a great way of maintaining the integrity and accuracy of the data.

How to: Enable the Add-in through File > Options > Add-Ins, from the Manage drop-down choose COM Add-Ins > Go. Tick Inquire and click OK.

To use it select the Inquire Tab in Ribbon > Choose function

For more tips and features on Excel 2013 and other versions, browse Microsoft training Excel courses from Best STL, available London and UK wide.

Excel 2013: Complete data entry quickly with Flash Fill

new feature for microsoft office 2013Save time and effort with Flash Fill for Excel 2013. Just one in a long line of nifty improvements to help you work more efficiently.

Flash Fill for Excel 2013 notices patterns in your data entry and then auto completes the remaining, so there’s no need to use formulas or macros to do this. Data gets filled in automatically. Previously you would have to use variations on LEFT(), RIGHT(), MID() plus a few other supporting functions to do this.

For example the Flash Fill feature will automatically complete a list of surnames after starting to type the second surname. It recognises patterns and predicts what data to fill in for you. Start typing the initials and Flash Fill fills the list for you.

Flash Fill for Excel 2013

Top Tip: Flash Fill also recognises text case. After typing a name in upper case, clicking Flash Fill on the Data Ribbon fills all the rest of the names in upper case.

Flash Fill for Excel 2013

 

Whether you’re splitting out email addresses or stripping out surnames, Flash Fill is a very useful feature.

For more tips and features on Excel 2013 and other versions, browse One Day Excel courses London from Best STL, available UK wide.

Microsoft Excel Course 2013: Suggested Charts through Quick Analysis -

new feature for microsoft office 2013So many times people pick the wrong chart type for their data. There’s a time and a place for pies, and lines aren’t for everyone. Picking the right one takes a bit of thought.

The Charts option in the Quick Analysis gallery, a new feature for Excel 2013, allows the user to quickly view and analyse data by choosing a variety of chart types. Excel will predict which chart types will best suit your selected data. Scroll through the different chart types until you’ve found one that works for your message.

Charts through Quick Analysis

How to: Highlight some Excel data in a table and look for the Quick Analysis tag to float over the bottom-right corner of your selection. Put your mouse over this icon to explore the options.

For more tips and features on Excel 2013 and other versions, browse Microsoft Excel course from Best STL, available London and UK wide. Our instructor-led training ranges from introduction to advanced Excel and also Excel VBA.

Excel Courses Online 2013: Conditional Formatting through Quick Analysis

new feature for microsoft office 2013If you’ve got a lot of data, it’s not always easy to spot trends and to easily analyse your figures. Conditional Formatting can instantly show you patterns in your data by highlighting cells that meet certain conditions. So for example you might want to flag up sales below a certain threshold in red, or bold occurrences of a certain word. This feature has been present in Excel for years, but in Excel 2013 there’s a faster way to get to visualise patterns.

Excel 2013 has introduced Formatting through Quick Analysis. In the below example, Colour Scale Formatting is used to look for high and low spots and highlighting them as such.

Colour Scale Formatting for Excel 2013

How to: Highlight some Excel data in a table and look for the Quick Analysis tag to float over the bottom-right corner of your selection. Put your mouse over this icon to explore the options.

Icon Set is another option available under Quick Analysis formatting. For example totals meeting a target display a green arrow or a red arrow if they don’t.

Suggested options not doing it for you? You can always create your own Conditional Formatting rules.

How to: Conditional Formatting > Manage Rules (to define the target criteria)

Conditional Formatting in Excel 2013

For more tips and features on Excel 2013 and other versions, browse Excel courses online from Best STL, available London and UK wide.

Get back control of your Excel spreadsheets

Poor Excel skills are costing UK businesses millions in lost revenue. According to an article in the Financial Times more than half of financial service groups have “poorly applied or no controls for managing business critical spreadsheets”.

The article blamed losses in part due to “avoidable errors in MS spreadsheets”. So, what could thousands of UK businesses do differently?

THE PROBLEM: “Fat finger” input mistakes

fingers on keysExcel spreadsheets are an integral part of many UK businesses. Used for anything from accounting to CRM, they are a system with a low barrier to entry. Business users start one up and start recording data.

Mistakes in Excel, however, can often be harder to spot than those in other MS applications. Though error messages will appear for misspelt formulae, they won’t pick up incorrectly populated fields. Help is at hand though, here’s how to reduce the risk of “fat finger” input mistakes:

THE SOLUTIONS:

Data Validation

All but eliminate the room for error with data validation formatting. This Excel feature will allow you to:

  • Make a list of possible entries, restricting the values allowed in a cell
  • Create an automated message when incorrect data has been inputted
  • Set a range of numeric values that can be entered into cells
  • Determine if an entry is valid based on calculations of other cells

By restricting the values allowed in a cell and setting formatting properties, you’ll have tighter control over editing functions and are likely to experience fewer cases of fat finger mistakes. Sure, these will still be made, but they’ll be visible right away.

To view a step by step guide of how to tutorials, visit the Microsoft Office support centre.

data validation

VLOOKUP

vlookup

If there are two tables of data that need to be cross-referenced, or you need to pull data from one table to another, don’t copy and paste. VLOOKUP is arguably the most useful function in Excel, and mastery of it will ensure that exactly the right data ends up in exactly the right place.

IFERROR

Pre-empt the fact that your formulas may run into errors. Assume they will and use the IFERROR function in Excel to provide a ‘catch’ scenario – replacing the error with a blank or zero for instance.

IFERROR’s are a quick and easy way to see null values, often a result of human error, whether that’s a formula, data entry or another error.

iferror

THE PROBLEM: No audit trail

With poor controls over quality control being blamed for huge monetary losses, auditing things like who has edited a workbook can be a useful way of keeping track of minor changes, that could have major consequences for your business. Excel offers a number of solutions to best fit your company’s skills set.

THE SOLUTIONS:

Workbook tracking

Quickly and simply see the changes made to your shared workbooks by tracking changes. This is great for organisations with multiple editors, allowing each author to see the additions and overwritten fields upon reopening the spreadsheet.

Excel offers three methods of workbook tracking:

  • On-screen highlighting

A great way to see changes quickly, with edited boxes highlighted in a different colour for each user. Hold the cursor over the changed cell to see a brief description of the edit. It’s ideal for at a glance reviewing or for workbooks with few changes.

On screen highlight

  • History tracking

Excel can produce a separate history worksheet that provides a list of change details which you can filter and search for. This method is ideal for worksheets that have incurred a series of changes.

  • Reviewing of changes

If you’re evaluating comments from other users, this method is especially useful. Excel can step you through the changes made in sequence so that you can decide whether to accept or reject the amendment.

Utilising Excel systems

You can also use Excel systems as a way of preparing reports or standardising systems in more detail.

Creating reports using PivotTables

There’s no faster or more convenient way to generate dynamic reports. Better still you can build controls so end users can manipulate the results with ease.

pivottables in excel

Automation with macros and VBA

Performing repeated tasks in Excel can be tedious, which in turn can lead to laziness and human error. Macros capture repetitive tasks for easy playback.

The language that feeds macros is VBA. It’s a programming language that sits alongside Excel. It allows you to program and automate processes and while the learning curve is steep, it opens up the possibilities in Excel exponentially. The amount of human error reduced and time saved is staggering.

Conclusion

In training and consulting scenarios we have seen spreadsheets with errors and gaps in them that are waiting to be exploited. But with just an hour or two’s education we have been able to transform leaky models and flabby formulas into watertight, lean applications.

We’ve been training Excel for years and are proud of our 98%+ recommendation rate, delivering value with every course. Here’s what a representative of Credit Suisse had to say about an Excel Advanced course:

“There were functions in Excel that have always been there until today I had no idea what purpose they served. The trainer was very informative, knowledgeable, pleasant to work with and above made the session very well run.”

Anyone concerned that their spreadsheets are the weak point in their projections should put some research into Excel training, and stop financial losses today.

Intrigued? Get in touch with Best STL today to discuss your training needs. Our Advanced Excel Courses London are a fantastic way of getting acquainted with the higher functions of MS Excel.

How Excel can help you build a successful SEO campaign

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.

Microsoft-Excel-2013-Logo

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”).

Google Logo / Search

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.

Rank Checker SEO

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

Budget / Money

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.

Open Site Explorer

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.