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

5 Reasons for Using Custom Fields in Microsoft Project

Custom Fields in Microsoft Project allow you to define your own fields (columns) for data that meets your organisation needs.

By creating your own custom fields you can display information on the project plan that is tailored to your requirements.

This tutorial looks at 5 reasons you may want to set up custom fields in Microsoft Project including creating drop down lists and using formulas in fields.

  1. Create a drop down list
  2. Display multiple fields next to task bars in Gantt Chart
  3. Sum the total duration of tasks
  4. Change colour of task bars dependent upon conditions
  5. Using graphical indicators in custom fields

Continue reading

Embed Fonts in PowerPoint File

If you are sharing a PowerPoint presentation with others, you may decide to embed fonts in the PowerPoint file. This will ensure that others can use those fonts, even if they are not installed on their machine.

If you have used a custom font that another user does not have, it will be replaced by the default font on their machine. The presentation will then have an inconsistent look to it.

Be aware that embedding fonts in a PowerPoint file will increase its file size. However this is typically a small price to pay for a consistent and reliable presentation. 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

Formatting Mail Merge Fields from Excel

When performing a mail merge from Excel to Word, your mail merge fields tend to lose their Excel formatting. This is especially common with date, time and currency fields.

This tutorial will look at how you can correctly format date, time and currency mail merge fields from Excel. Once the formatting is applied to the mail merge document it will be remembered for future use.

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