5 Advanced PivotTable Techniques

PivotTables are one of the most useful tools in Excel. They allow you to easily summarise, examine and present a complex list of data.

This blog post explores 5 advanced PivotTable techniques.

  1. Grouping fields by month and year
  2. Calculating data as a percentage of the total
  3. Using Slicers
  4. Applying Conditional Formatting to PivotTable data
  5. Creating calculated fields

Continue reading

Add Drop Lines to a Line Graph in Excel

Add drop lines to a line graph in Excel to connect the data point to its label on the axis. This extra visual chart element can make it easier to view the data on busy charts.

Take the line graph below for instance that shows the call volume for 14 hours of a day. It can be awkward on this chart to quickly view the time of day for a specific data point.

Line graph without drop lines

Continue reading

Export All Sheets to PDF – Excel VBA

This tutorial looks at how you can create a macro in Excel to export all sheets to PDF. Each sheet of a workbook will be saved into the same folder. The name of the worksheet will be used as the filename of the PDF.

By creating a macro this seemingly repetitive task can be performed at the click of a button. Continue reading

Add a Hyperlink to a Userform – Excel VBA

If you have ever tried to add a hyperlink to a userform, you would have noticed that there is no hyperlink button on the toolbox.

You may not be able to insert a hyperlink like you would on an Excel spreadsheet, but you can create a link by inserting a label and applying the OnClick event.

Adding a Hyperlink to a Userform

In this example we will add a hyperlink so users may email an enquiries department from the userform. Continue reading

Count Unique Values in a PivotTable

When using a PivotTable to summarise a large list of data, you may need to count unique occurrences in a particular field.

For example, you may have a list of data like the one below and want to count the number of different customers in each country, or the number of different products that were sold in each country.

List of data to count uniques

If you added the Customer Name field to the Values area of a PivotTable and summarised it with a Count you would get the result below.

Counting values in a PivotTable

However this is counting all of the transactions in each country. We need to count the unique customers making those transactions in each country.

Continue reading

Format Specific Words in a Cell using VBA

In this blog post we look at how to create a macro that will look in a list and format all the instances of specific words that appear in a cell.

This macro is case sensitive and will also only apply the formatting if it is the whole word. For example, if you are formatting all instances of the word ‘red’, this macro will not format it if used in words like ‘reddish’ and ‘sacred’.

Continue reading

Advanced Techniques for Summing Values

Using the SUM function to total a column of values is an essential skill in Excel. However, it is not always that straightforward. The column of values may contain errors, or the values you want to sum may be on many different sheets.

This article looks at 4 advanced techniques for summing values including;

  1. Summing a range that contains errors
  2. Creating a cumulative total
  3. A consolidated sum from multiple sheets
  4. Summing Every Nth Value in a Range

Continue reading

Create a Battery Chart in Excel

Create a battery chart in Excel just like you see on your smartphone, tablet device or even PC. It is a great visual way of viewing the percentage remaining of a value.

In Excel a battery chart could be used to view the amount remaining of a budget, the work remaining on a task, or number of people still to attend training.

There are many reasons why you may want to visualise an amount dropping until it is empty, or complete. A battery chart provides a method that people can relate to easily.

The image below shows a battery chart being used to show the amount outstanding on a training budget.

Battery chart in Excel

Continue reading

Creating Multiple Dependent Drop Down Lists

Drop down lists can be created in Excel to assist with data entry and to prevent potential typos and misspellings. The Data Validation tool in Excel can be used to easily create lists.

However if you have many entries, you may need to break the list into multiple dependent drop down lists. By creating a cascading set of lists, the entry in one is dependent upon the selection made in another.

Continue reading

Ranking Fields of a PivotTable in Excel 2010

In Excel 2010 a feature was introduced to easily rank the fields of your PivotTables.

Yes, you can sort the fields of a PivotTable to view items in order from largest to smallest, or smallest to largest depending on what you are trying to achieve. But you may wish to keep your list of products, customers, salespersons or whatever the field is your are ranking in alphabetical order.

Take the table below for instance. This PivotTable displays the number of sales and conversion rate for the members of a sales team.

Sales team pivottable

We would like to add a rank field to the PivotTable for both fields.

Continue reading